Android Open Source - journal Journal Database Helper

• Back to App/track  ↑

From Project

Back to project page journal.

License

The source code is released under:

MIT License

Java Source Code

package cochrane343.journal.contentprovider;
//from  ww w  . j  av  a2s  .co m
import static cochrane343.journal.Constants.LOGGING_TAG;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import cochrane343.journal.contentprovider.JournalContract.Category;
import cochrane343.journal.contentprovider.JournalContract.Expense;

/**
 * @author cochrane343
 * @version 1.0
 */
public class JournalDatabaseHelper extends SQLiteOpenHelper {       
    private static final int DATABASE_VERSION = 2;
    private static final String DATABASE_NAME = "Journal.db";

    /*
     * CREATE TABLE Expense (
     *      _id INTEGER PRIMARY KEY AUTOINCREMENT,
     *      description TEXT,
     *      cost INTEGER,
     *      timestamp INTEGER,
     *      category INTEGER, 
     *      FOREIGN KEY (category) REFERENCES Category (_id)
     * );
     */
    private static final String SQL_CREATE_EXPENSES_TABLE =
            "CREATE TABLE " + Expense.TABLE_NAME + " (" +
            Expense._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            Expense._DESCRIPTION + " TEXT, " +
            Expense._COST + " INTEGER, " +
            Expense._TIMESTAMP + " INTEGER, " +
            Expense._CATEGORY + " INTEGER, " +
            "FOREIGN KEY (" + Expense._CATEGORY + ") REFERENCES "
            + Category.TABLE_NAME + "(" + Category._ID + "));";
    
    /*
     * CREATE TABLE Category (
     *      _id INTEGER PRIMARY KEY AUTOINCREMENT,
     *      name TEXT,
     *      sortOrder INTEGER
     * );
     */
    private static final String SQL_CREATE_CATEGORIES_TABLE =
            "CREATE TABLE " + Category.TABLE_NAME + " (" +
            Category._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            Category._NAME + " TEXT, " +
            Category._SORT_ORDER + " INTEGER);";
    
    /*
     * CREATE VIEW CategoryTotal AS
     * SELECT Category._id, name, sortOrder, cost, timestamp
     * FROM Expense
     * LEFT JOIN Category ON Category._id = Expense.category;
     */
    private static final String SQL_CREATE_CATEGORY_TOTALS_VIEW =
            "CREATE VIEW " + Category.TOTALS_VIEW_NAME +
            " AS SELECT " + Category.TABLE_NAME + "." +  Category._ID + ", " +
            Category._NAME + ", " + Category._SORT_ORDER + ", " + Expense._COST + ", " +
            Expense._TIMESTAMP + " FROM " + Expense.TABLE_NAME + " LEFT JOIN " +
            Category.TABLE_NAME + " ON " + Category.TABLE_NAME + "." +  Category._ID + " = " +
            Expense.TABLE_NAME + "." + Expense._CATEGORY + ";";
    
    /*
     * see onUpgrade method for updated sort order
     * INSERT INTO Category ( name, sortOrder )
     * SELECT 'Housing' AS name, 1 AS sortOrder
     * UNION SELECT 'Food', 2 
     * UNION SELECT 'Drinks', 3
     * UNION SELECT 'Clothing', 4
     * UNION SELECT 'Supplies', 5
     * UNION SELECT 'Technology', 6
     * UNION SELECT 'Transport', 7 
     * UNION SELECT 'Misc', 8;
     */
    private static final String SQL_INSERT_DEFAULT_CATEGORIES =
            "INSERT INTO " + Category.TABLE_NAME +
            " ( " + Category._NAME + ", " + Category._SORT_ORDER + " )" + 
            " SELECT '" + Category.HOUSING + "' AS " + Category._NAME + ", 8 AS " + Category._SORT_ORDER +
            " UNION SELECT '" + Category.FOOD + "', 1" + 
            " UNION SELECT '" + Category.DRINKS + "', 2" + 
            " UNION SELECT '" + Category.CLOTHING + "', 4" + 
            " UNION SELECT '" + Category.SUPPLIES + "', 3" + 
            " UNION SELECT '" + Category.TECHNOLOGY + "', 5" + 
            " UNION SELECT '" + Category.TRANSPORT + "', 6" + 
            " UNION SELECT '" + Category.MISC + "', 7;";

    private final String SQL_UPDATE_CATEGORY_SORT_ORDER = 
            "UPDATE " + Category.TABLE_NAME + 
            " SET " + Category._SORT_ORDER + "=%d" + 
            " WHERE " + Category._NAME + "='%s'";
    
    public JournalDatabaseHelper(final Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void onCreate(final SQLiteDatabase database) {
        database.beginTransaction();
        try {
            database.execSQL(SQL_CREATE_EXPENSES_TABLE);
            database.execSQL(SQL_CREATE_CATEGORIES_TABLE);
            database.execSQL(SQL_CREATE_CATEGORY_TOTALS_VIEW);
        
            database.execSQL(SQL_INSERT_DEFAULT_CATEGORIES);
            
            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    }
    
    /**
     * Version 1: Original version
     * Version 2: Improved category sort order (Issue #1)
     */
    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
        if (Log.isLoggable(LOGGING_TAG, Log.DEBUG)) {
            Log.d(LOGGING_TAG, "Upgrading database '" + db.getPath() + "' from version " + oldVersion + " to " + newVersion);
        }
        
        if (oldVersion == 1) {
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 1, Category.FOOD));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 2, Category.DRINKS));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 3, Category.SUPPLIES));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 4, Category.CLOTHING));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 5, Category.TECHNOLOGY));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 6, Category.TRANSPORT));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 7, Category.MISC));
            db.execSQL(String.format(SQL_UPDATE_CATEGORY_SORT_ORDER, 8, Category.HOUSING));
        }
    }
}

Java Source Code List

• Back to App/track  ↑