Database.java :  » Widget » my-daily-phrases » com » googlecode » mydailyphrases » Android Open Source

package com.googlecode.mydailyphrases;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Creates and manages the application database.
 *
 */
public class Database {
    private static final String DATABASE_NAME = "MyDailyPhrases";
    private static final int DATABASE_VERSION = 3;
    
    public static final String TABLE_PHRASES = "phrases";
    
    public static final String COL_ID = "_id";
    public static final String COL_TEXT = "body";
    public static final String COL_AUTHORID = "_authorid";
    public static final String COL_SOURCEID = "_sourceid";
    
    public static final String TABLE_SOURCES = "sources";
    
    public static final String COL_TYPE = "type";
    public static final String COL_URL = "url";
    
    public enum SourceType {
        FILESYSTEM,
        URL
    }
    
    private static final String CREATE_TABLE_SOURCES = 
        "create table " + TABLE_SOURCES + " (" +
        COL_ID + " integer primary key autoincrement, " +
        COL_TYPE + " text not null, " +
        COL_URL + " text not null);";
    
    private static final String[] DEFAULT_SELECT_COLUMNS_SOURCES =
        new String[] { COL_ID, COL_TYPE, COL_URL};
    
    private static final String CREATE_TABLE_PHRASES =
        "create table " + TABLE_PHRASES + " (" + 
        COL_ID + " integer primary key autoincrement, " + 
        COL_TEXT + " text not null, " +
        COL_AUTHORID + " integer, " +
        COL_SOURCEID + " integer, " +
        "foreign key(" + COL_SOURCEID + ") references " + TABLE_SOURCES + "(" + COL_ID + ")" + ");";
    
    private static final String[] DEFAULT_SELECT_COLUMNS_PHRASES = 
        new String[] { COL_ID, COL_TEXT, COL_SOURCEID};

    private static final String TAG = DATABASE_NAME;
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private final Context mContext;
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE_SOURCES);
            db.execSQL(CREATE_TABLE_PHRASES);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("drop table if exists " + TABLE_PHRASES);
            db.execSQL("drop table if exists " + TABLE_SOURCES);
            onCreate(db);
        }
    }
    
    public Database(Context context) {
        mContext = context;
    }

    public void open() throws SQLException {
        mDbHelper = new DatabaseHelper(mContext);
        mDb = mDbHelper.getWritableDatabase();
    }

    public void close() {
        mDbHelper.close();
    }
    
    public long insertSource(SourceType type, String url) {
        ContentValues values = new ContentValues();
        values.put(COL_TYPE, type.toString());
        values.put(COL_URL, url);
        
        return mDb.insert(TABLE_SOURCES, null, values);
    }
    
    public long insertPhrase(String text, long sourceId) {
        ContentValues values = new ContentValues();
        values.put(COL_TEXT, text);
        values.put(COL_SOURCEID, sourceId);
        
        return mDb.insert(TABLE_PHRASES, null, values);
    }
    
    public void updatePhrase(long id, String newText) {
        ContentValues values = new ContentValues();
        values.put(COL_TEXT, newText);
        
        mDb.update(
                TABLE_PHRASES, 
                values, 
                COL_ID + "=" + id, 
                null);
    }
    
    public Cursor selectPhrase(long id) {
        Cursor ret =
            mDb.query(
                    true, 
                    TABLE_PHRASES, 
                    DEFAULT_SELECT_COLUMNS_PHRASES, 
                    COL_ID + "=" + id, 
                    null, null, null, null, null);
        ret.moveToFirst();
        return ret;
    }

    public Cursor selectNextPhrase(long id) {
        Cursor ret =
            mDb.query(
                    true, 
                    TABLE_PHRASES, 
                    DEFAULT_SELECT_COLUMNS_PHRASES, 
                    COL_ID + ">" + id, 
                    null, null, null, null, 
                    "1" //limit
            );
        ret.moveToFirst();
        return ret;
    }

    public Cursor selectFirstPhrase() {
        Cursor ret =
            mDb.query(
                    true, 
                    TABLE_PHRASES, 
                    DEFAULT_SELECT_COLUMNS_PHRASES, 
                    COL_ID + "> 0", 
                    null, null, null, null, 
                    "1" //limit
            );
        ret.moveToFirst();
        return ret;
    }

  public Cursor selectRandomPhrase() {
    Cursor ret =
            mDb.query(
                    true, 
                    TABLE_PHRASES, 
                    DEFAULT_SELECT_COLUMNS_PHRASES, 
                    null, null, null, null, 
                    "random()", //order by 
                    "1" //limit
            );
        ret.moveToFirst();
        return ret;
  }
  
  public int deletePhrasesBySourceId(long sourceId) {
        return mDb.delete(
                TABLE_PHRASES, 
                COL_SOURCEID + " = " + sourceId, 
                null);
    }

    public Cursor selectAllSources() {
        Cursor ret =
            mDb.query(
                    true, 
                    TABLE_SOURCES, 
                    DEFAULT_SELECT_COLUMNS_SOURCES, 
                    null, null, null, null, null, null
            );
        ret.moveToFirst();
        return ret;
    }
    
    public void deleteSource(long id) {
        mDb.delete(TABLE_SOURCES, COL_ID + " = " + id, null);
    }
}