Android Open Source - TaskButler Database Handler

From Project

Back to project page TaskButler.
License

The source code is released under:
GNU General Public License
If you think the Android project TaskButler listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.
Java Source Code

/*
 * DatabaseHandler.java/*from  www.j a va  2s  .co  m*/
 * 
 * Copyright 2012 Jonathan Hasenzahl, James Celona, Dhimitraq Jorgji
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */


package edu.worcester.cs499summer2012.database;

import java.util.GregorianCalendar;

import edu.worcester.cs499summer2012.task.Category;
import edu.worcester.cs499summer2012.task.Comparator;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Color;

/**
 * Creates SQLite table for storing tasks to a database. DO NOT call this class directly
 * get an instance of TasksDataSource instead.
 * @author Dhimitraq Jorgji, Jonathan Hasenzahl
 */
public class DatabaseHandler extends SQLiteOpenHelper {

  // Database Version
  private static final int DATABASE_VERSION = 8;
  private static final int RC1_DATABASE = 7;

  // Database Name
  public static final String DATABASE_NAME = "TaskButler.db";

  // Table names
  public static final String TABLE_TASKS = "tasks";
  public static final String TABLE_CATEGORIES = "categories";
  public static final String TABLE_COMPARATORS = "comparators";
  public static final String TABLE_BACKUP = "tasks_backup";

  // Column names
  public static final String KEY_ID = "id";                     // INTEGER PRIMARY KEY
  public static final String KEY_NAME = "name";                    // TEXT
  public static final String KEY_COMPLETION = "completion";              // INTEGER, indirectly boolean
  public static final String KEY_PRIORITY = "priority";                // INTEGER
  public static final String KEY_CATEGORY = "category";                // INTEGER
  public static final String KEY_HAS_DUE_DATE = "hasDueDate";            // INTEGER, indirectly boolean
  public static final String KEY_HAS_FINAL_DUE_DATE = "hasFinalDueDate";        // INTEGER, indirectly boolean
  public static final String KEY_IS_REPEATING = "isRepeating";            // INTEGER, indirectly boolean  
  public static final String KEY_REPEAT_TYPE = "repeatType";              // INTEGER
  public static final String KEY_REPEAT_INTERVAL = "repeatInterval";          // INTEGER
  public static final String KEY_CREATION_DATE = "creationDate";            // DATETIME
  public static final String KEY_MODIFICATION_DATE = "modificationDate";        // DATETIME
  public static final String KEY_DUE_DATE = "dueDate";                // DATETIME
  public static final String KEY_NOTES = "notes";                  // TEXT, can be null
  public static final String KEY_COLOR = "color";                  // INTEGER, used in category table
  public static final String KEY_UPDATED = "updated";                 // DATETIME
  public static final String KEY_G_ID = "gID";                   // STRING
  public static final String KEY_ENABLED = "enabled";                 // INTEGER, indirectly boolean, used in comparators table
  public static final String KEY_ORDER = "list_order";               // INTEGER, used in comparators table
  
  // Deprecated column names
  @Deprecated
  public static final String KEY_HAS_STOP_REPEATING_DATE = "hasStopRepeatingDate"; // INTEGER, indirectly boolean
  @Deprecated
  public static final String KEY_FINAL_DUE_DATE = "finalDueDate";           // DATETIME
  @Deprecated
  public static final String KEY_STOP_REPEATING_DATE = "stopRepeatingDate";      // DATETIME

  public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  private void createTasksTable(SQLiteDatabase db) {
    String create_tasks_table = "CREATE TABLE " + TABLE_TASKS + "("
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_NAME + " TEXT,"
        + KEY_COMPLETION + " INTEGER,"
        + KEY_PRIORITY + " INTEGER,"
        + KEY_CATEGORY + " INTEGER,"
        + KEY_HAS_DUE_DATE + " INTEGER,"
        + KEY_HAS_FINAL_DUE_DATE + " INTEGER,"
        + KEY_IS_REPEATING + " INTEGER,"
        + KEY_REPEAT_TYPE + " INTEGER,"
        + KEY_REPEAT_INTERVAL + " INTEGER,"
        + KEY_CREATION_DATE + " DATETIME,"
        + KEY_MODIFICATION_DATE + " DATETIME,"
        + KEY_DUE_DATE + " DATETIME,"
        + KEY_G_ID + " TEXT,"
        + KEY_NOTES + " TEXT)";
    
    db.execSQL(create_tasks_table);
  }
  
  private void createCategoriesTable(SQLiteDatabase db) {
    String create_categories_table = "CREATE TABLE " + TABLE_CATEGORIES + "(" 
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_NAME + " TEXT,"
        + KEY_COLOR + " INTEGER,"
        + KEY_UPDATED + " DATETIME,"
        + KEY_G_ID + " TEXT)";
    
    db.execSQL(create_categories_table);
    
    // Create first entry of categories table
    ContentValues values = new ContentValues();
    values.put(KEY_ID, Category.NO_CATEGORY);
    values.put(KEY_NAME, "No category");
    values.put(KEY_COLOR, Color.parseColor("#00FFFFFF"));
    values.put(KEY_UPDATED, GregorianCalendar.getInstance().getTimeInMillis());
    db.insert(TABLE_CATEGORIES, null, values);    
  }
  
  private void createComparatorsTable(SQLiteDatabase db) {
    String create_comparators_table = "CREATE TABLE " + TABLE_COMPARATORS + "(" 
        + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_NAME + " TEXT,"
        + KEY_ENABLED + " INTEGER,"
        + KEY_ORDER + " INTEGER)";
    
    db.execSQL(create_comparators_table);

    // Create all entries of comparators table
    ContentValues values = new ContentValues();
    values.put(KEY_ID, Comparator.NAME);
    values.put(KEY_NAME, "Task name");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 0);
    db.insert(TABLE_COMPARATORS, null, values);
    values = new ContentValues();
    values.put(KEY_ID, Comparator.COMPLETION);
    values.put(KEY_NAME, "Completion status");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 1);
    db.insert(TABLE_COMPARATORS, null, values);
    values = new ContentValues();
    values.put(KEY_ID, Comparator.PRIORITY);
    values.put(KEY_NAME, "Priority");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 2);
    db.insert(TABLE_COMPARATORS, null, values);
    values = new ContentValues();
    values.put(KEY_ID, Comparator.CATEGORY);
    values.put(KEY_NAME, "Category");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 3);
    db.insert(TABLE_COMPARATORS, null, values);
    values = new ContentValues();
    values.put(KEY_ID, Comparator.DATE_DUE);
    values.put(KEY_NAME, "Due date");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 4);
    db.insert(TABLE_COMPARATORS, null, values);
    values = new ContentValues();
    values.put(KEY_ID, Comparator.DATE_CREATED);
    values.put(KEY_NAME, "Date created");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 5);
    db.insert(TABLE_COMPARATORS, null, values);
    values = new ContentValues();
    values.put(KEY_ID, Comparator.DATE_MODIFIED);
    values.put(KEY_NAME, "Date modified");
    values.put(KEY_ENABLED, 0);
    values.put(KEY_ORDER, 6);
    db.insert(TABLE_COMPARATORS, null, values);
  }
  
  // Creating Table
  @Override
  public void onCreate(SQLiteDatabase db) {
    createTasksTable(db);
    createCategoriesTable(db);
    createComparatorsTable(db);
  }

  // Upgrading database
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (db.getVersion() == RC1_DATABASE) {
      // Convert Release Candidate 1 tables to current tables.
      // New: Task table, comparators table are upgraded
      // No change: Categories table do not need upgrade
      
      String createBackupTable = "CREATE TEMPORARY TABLE "
          + TABLE_BACKUP + "("
          + KEY_ID + " INTEGER PRIMARY KEY,"
          + KEY_NAME + " TEXT,"
          + KEY_COMPLETION + " INTEGER,"
          + KEY_PRIORITY + " INTEGER,"
          + KEY_CATEGORY + " INTEGER,"
          + KEY_HAS_DUE_DATE + " INTEGER,"
          + KEY_HAS_FINAL_DUE_DATE + " INTEGER,"
          + KEY_IS_REPEATING + " INTEGER,"
          + KEY_REPEAT_TYPE + " INTEGER,"
          + KEY_REPEAT_INTERVAL + " INTEGER,"
          + KEY_CREATION_DATE + " DATETIME,"
          + KEY_MODIFICATION_DATE + " DATETIME,"
          + KEY_DUE_DATE + " DATETIME,"
          + KEY_G_ID + " TEXT,"
          + KEY_NOTES + " TEXT)";
      
      String copyToBackup = "INSERT INTO "
          + TABLE_BACKUP + " SELECT "
          + KEY_ID + ","
          + KEY_NAME + ","
          + KEY_COMPLETION + ","
          + KEY_PRIORITY + ","
          + KEY_CATEGORY + ","
          + KEY_HAS_DUE_DATE + ","
          + KEY_HAS_FINAL_DUE_DATE + ","
          + KEY_IS_REPEATING + ","
          + KEY_REPEAT_TYPE + ","
          + KEY_REPEAT_INTERVAL + ","
          + KEY_CREATION_DATE + ","
          + KEY_MODIFICATION_DATE + ","
          + KEY_DUE_DATE + ","
          + KEY_G_ID + ","
          + KEY_NOTES + " FROM "
          + TABLE_TASKS;
      
      String copyToTasksTable = "INSERT INTO "
          + TABLE_TASKS + " SELECT "
          + KEY_ID + ","
          + KEY_NAME + ","
          + KEY_COMPLETION + ","
          + KEY_PRIORITY + ","
          + KEY_CATEGORY + ","
          + KEY_HAS_DUE_DATE + ","
          + KEY_HAS_FINAL_DUE_DATE + ","
          + KEY_IS_REPEATING + ","
          + KEY_REPEAT_TYPE + ","
          + KEY_REPEAT_INTERVAL + ","
          + KEY_CREATION_DATE + ","
          + KEY_MODIFICATION_DATE + ","
          + KEY_DUE_DATE + ","
          + KEY_G_ID + ","
          + KEY_NOTES + " FROM "
          + TABLE_BACKUP;
      
      db.execSQL(createBackupTable);
      db.execSQL(copyToBackup);
      db.execSQL("DROP TABLE " + TABLE_TASKS);
      createTasksTable(db);
      db.execSQL(copyToTasksTable);
      db.execSQL("DROP TABLE " + TABLE_BACKUP);
      
      db.execSQL("DROP TABLE " + TABLE_COMPARATORS);
      createComparatorsTable(db);
    }
  }
}
Java Source Code List
