package com.rogansoft.tasksdemo.db; import java.util.ArrayList; import com.rogansoft.tasksdemo.domain.Task; 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; public class TaskDb { private static final String TAG = "TaskDb"; private static final String DATABASE_NAME = "task_db"; private static final int DATABASE_VERSION = 1; // Table names private static final String TASK_TABLE_NAME = "task"; private DatabaseHelper mDbHelper; private SQLiteDatabase mDb; private final Context mCtx; // task table column names public static String TASK_FIELD_NAME_ID = "id"; // long public static String TASK_FIELD_NAME_UPDATED = "updated"; // long public static String TASK_FIELD_NAME_TITLE = "title"; // String public static String TASK_FIELD_NAME_SERVER_ID = "server_id"; // String public static String TASK_FIELD_NAME_POSITION = "position"; // String public static String TASK_FIELD_NAME_STATUS = "status"; // String public static String TASK_FIELD_NAME_DELETED = "deleted"; // boolean //task table creation sql private static final String TASK_CREATE_QUERY = "create table "+TASK_TABLE_NAME+" (" + TASK_FIELD_NAME_ID + " integer primary key autoincrement not null," + TASK_FIELD_NAME_UPDATED + " integer null," + TASK_FIELD_NAME_TITLE + " text not null," + TASK_FIELD_NAME_SERVER_ID + " text null," + TASK_FIELD_NAME_POSITION + " text null," + TASK_FIELD_NAME_STATUS + " text null," + TASK_FIELD_NAME_DELETED + " integer null" + ");"; // task all fields String[] private final String[] TASK_ALL_FIELDS = new String[] { TASK_FIELD_NAME_ID, TASK_FIELD_NAME_UPDATED, TASK_FIELD_NAME_TITLE, TASK_FIELD_NAME_SERVER_ID, TASK_FIELD_NAME_POSITION, TASK_FIELD_NAME_STATUS, TASK_FIELD_NAME_DELETED }; private static class DatabaseHelper extends SQLiteOpenHelper { public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { Log.d(TAG, "onCreate..."); initialDbBuild(db); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.d(TAG, "onUpgrade... oldversion:"+oldVersion+" newVersion:"+newVersion); initialDbBuild(db); } private void initialDbBuild(SQLiteDatabase db) { Log.d(TAG, "initialDbBuild"); db.execSQL("DROP TABLE IF EXISTS "+TASK_TABLE_NAME); db.execSQL(TASK_CREATE_QUERY); } } public TaskDb(Context ctx) { this.mCtx = ctx; } public TaskDb open() throws SQLException { mDbHelper = new DatabaseHelper(mCtx); mDb = mDbHelper.getWritableDatabase(); return this; } public void close() { mDbHelper.close(); } public static int fieldByNameInt(Cursor cur, String fldName) { return cur.getInt(cur.getColumnIndex(fldName)); } public static String fieldByNameString(Cursor cur, String fldName) { return cur.getString(cur.getColumnIndex(fldName)); } public static long fieldByNameLong(Cursor cur, String fldName) { return cur.getLong(cur.getColumnIndex(fldName)); } public static double fieldByNameDouble(Cursor cur, String fldName) { return cur.getDouble(cur.getColumnIndex(fldName)); } public static boolean fieldByNameBoolean(Cursor cur, String fldName) { return cur.getInt(cur.getColumnIndex(fldName)) == 1; } // Task CRUD // Used by user of content provider to build Task object from a cursor row. public static Task taskFromCursorRow(Cursor cur) { Task result; result = new Task( fieldByNameLong(cur, TASK_FIELD_NAME_ID), fieldByNameLong(cur, TASK_FIELD_NAME_UPDATED), fieldByNameString(cur, TASK_FIELD_NAME_TITLE), fieldByNameString(cur, TASK_FIELD_NAME_SERVER_ID), fieldByNameString(cur, TASK_FIELD_NAME_POSITION), fieldByNameString(cur, TASK_FIELD_NAME_STATUS), fieldByNameBoolean(cur, TASK_FIELD_NAME_DELETED) ); return result; } // Used by user of content provider to setup fields for an insert public static ContentValues contentValuesFromTask(Task task, boolean includeId) { ContentValues initialValues = new ContentValues(); if (includeId) { initialValues.put(TASK_FIELD_NAME_ID, task.getId()); } initialValues.put(TASK_FIELD_NAME_UPDATED, task.getUpdated()); initialValues.put(TASK_FIELD_NAME_TITLE, task.getTitle()); initialValues.put(TASK_FIELD_NAME_SERVER_ID, task.getServerId()); initialValues.put(TASK_FIELD_NAME_POSITION, task.getPosition()); initialValues.put(TASK_FIELD_NAME_STATUS, task.getStatus()); initialValues.put(TASK_FIELD_NAME_DELETED, task.isDeleted()); return initialValues; } // create static private long createTask(SQLiteDatabase db, Task task) { ContentValues initialValues = contentValuesFromTask(task, false); long result = db.insert(TASK_TABLE_NAME, null, initialValues); Log.d(TAG, "createTask result:"+result); for(String key : initialValues.keySet()) { Log.d(TAG, "key:"+key); if (key.equals(TASK_FIELD_NAME_SERVER_ID)) { Log.d(TAG, "serverid: "+initialValues.getAsString(key)); } } return result; } public long createTask(Task task) { return createTask(mDb, task); } // delete public int deleteTask(String where, String[] whereArgs) { return mDb.delete(TASK_TABLE_NAME, where, whereArgs); } public int deleteTask(long rowId) { return deleteTask(TASK_FIELD_NAME_ID + "=" + rowId, null); } public int deleteAllTasks() { return deleteTask(null, null); } // read public Task fetchTask(long rowId) throws SQLException { Task result = null; Cursor cur = mDb.query(true, TASK_TABLE_NAME, TASK_ALL_FIELDS, TASK_FIELD_NAME_ID + "=" + rowId, null, null, null, null, null); if (cur.moveToFirst()) { result = taskFromCursorRow(cur); } cur.close(); return result; } public ArrayList<Task> fetchAllTasks() { ArrayList<Task> result = new ArrayList<Task>(); Cursor cur = mDb.query(TASK_TABLE_NAME, TASK_ALL_FIELDS, null, null, null, null, TASK_FIELD_NAME_POSITION); if (cur.moveToFirst()) { do { Task task = taskFromCursorRow(cur); result.add(task); } while (cur.moveToNext()); } cur.close(); return result; } public ArrayList<Task> fetchNonDeletedTasks() { ArrayList<Task> result = new ArrayList<Task>(); Cursor cur = mDb.query(TASK_TABLE_NAME, TASK_ALL_FIELDS, TASK_FIELD_NAME_DELETED+" IS NULL OR "+TASK_FIELD_NAME_DELETED+" = 0", null, null, null, TASK_FIELD_NAME_POSITION); if (cur.moveToFirst()) { do { Task task = taskFromCursorRow(cur); result.add(task); } while (cur.moveToNext()); } cur.close(); return result; } // update public int updateTask(Task task) { ContentValues values = contentValuesFromTask(task, true); return mDb.update(TASK_TABLE_NAME, values, TASK_FIELD_NAME_ID + "=" + task.getId(), null); } }