Budapest University of Technology - Mobile Software Development Android Practise #2 Persistent Data Storage (Todo application)

DatabaseHelper.java

package hu.bute.daai.mytodoapp.datastorage;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	public DatabaseHelper(Context context, String name) {
		super(context, name, null, DbConstants.DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DbConstants.TodoDbConstants.DATABASE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(DbConstants.TodoDbConstants.DATABASE_DROP);
		db.execSQL(DbConstants.TodoDbConstants.DATABASE_CREATE);
	}

}

DbConstants.java

package hu.bute.daai.mytodoapp.datastorage;

/**
 * Constants for DB management
 * (for convenience of the developer)
 */
public class DbConstants {

	// filename of the database file
	public static final String DATABASE_NAME = "data.db";
	
	// version number
	public static final int DATABASE_VERSION = 1;
	
	/* DB constants for Todo class */
	public static class TodoDbConstants {
		
		// name of the DB table
		public static final String DATABASE_TABLE = "todo";
		
		// column names
		public static final String KEY_ROWID = "_id";
		public static final String KEY_TITLE = "title";
		public static final String KEY_PRIORITY = "priority";
		public static final String KEY_DUEDATE = "dueDate";
		public static final String KEY_ISDONE = "isDone";
		
		// schema creator script of database table
		public static final String DATABASE_CREATE = 
				"create table if not exists "
				+ DATABASE_TABLE
				+ " ( "
				+ KEY_ROWID
				+ " integer primary key autoincrement, "
				+ KEY_TITLE
				+ " text not null, "
				+ KEY_PRIORITY
				+ " text, "
				+ KEY_ISDONE
				+ " integer, "
				+ KEY_DUEDATE
				+ " text);";
		
		// Drop Todo table
		public static final String DATABASE_DROP = "drop table if exists "
				+ DATABASE_TABLE + ";";
	}
	
	/* 
	 * DB constants for other classes would come here 
	 * (if there were any more classes we wanted to store in DB) 
	 */

}

TodoCursorAdapter.java

package hu.bute.daai.mytodoapp.data;

import hu.bute.daai.mytodoapp.R;
import hu.bute.daai.mytodoapp.datastorage.TodoDbLoader;
import java.text.SimpleDateFormat;
import android.content.Context;
import android.database.Cursor;
import android.graphics.Color;
import android.graphics.Paint;
import android.support.v4.widget.CursorAdapter;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

public class TodoCursorAdapter extends CursorAdapter {

	private SimpleDateFormat formatter = new SimpleDateFormat("dd/MMMM/yyyy");
	
	public TodoCursorAdapter(Context context, Cursor c) {
		super(context, c, false);
	}

	@Override
	public View newView(Context context, Cursor cursor, ViewGroup parent) {
		final LayoutInflater inflater = LayoutInflater.from(context);
		
		View row = inflater.inflate(R.layout.todo_row, parent, false);
		bindView(row, context, cursor);
		
		return row;
	}
	
	@Override
	public void bindView(View view, Context context, Cursor cursor) {
		
		Todo currentTodo = TodoDbLoader.getTodoByCursor(cursor);
		
		View leftRibbon = view.findViewById(R.id.leftColorRibbon);
		View rightRibbon = view.findViewById(R.id.rightColorRibbon);
				
		// set right&left ribbon background color according to the Todo priority
		switch (currentTodo.priority) {
			case low:
				leftRibbon.setBackgroundColor(context.getResources().getColor(R.color.priority_low));
				rightRibbon.setBackgroundColor(context.getResources().getColor(R.color.priority_low));
				break;
			case medium:
				leftRibbon.setBackgroundColor(context.getResources().getColor(R.color.priority_medium));
				rightRibbon.setBackgroundColor(context.getResources().getColor(R.color.priority_medium));
				break;
			default:
				leftRibbon.setBackgroundColor(context.getResources().getColor(R.color.priority_high));
				rightRibbon.setBackgroundColor(context.getResources().getColor(R.color.priority_high));
				break;
		}
		
		// set title
		TextView title = (TextView) view.findViewById(R.id.todoTitle);
		title.setText(currentTodo.title);
		
		if(currentTodo.isDone){
			// strikethrough effect
			title.setPaintFlags(title.getPaintFlags() | Paint.STRIKE_THRU_TEXT_FLAG);
			// set color to dark gray
			title.setTextColor(Color.parseColor("#999999"));
		}
		else{
			// no strikethrough effect
			title.setPaintFlags(title.getPaintFlags() & (~ Paint.STRIKE_THRU_TEXT_FLAG));
			// set color to light gray
			title.setTextColor(Color.parseColor("#171717"));
		}
		
		// set due date
		TextView dueDate = (TextView) view.findViewById(R.id.todoDuedate);
		dueDate.setText(formatter.format(currentTodo.dueDate));
	}
	
	@Override
	public Todo getItem(int position) {
		getCursor().moveToPosition(position);
		return TodoDbLoader.getTodoByCursor(getCursor());
	}
}

TodoDbLoader.java

package hu.bute.daai.mytodoapp.datastorage;

import hu.bute.daai.mytodoapp.data.Todo;
import hu.bute.daai.mytodoapp.data.Todo.TodoPriority;

import java.util.Date;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

/* 
 * This class handles DB connection 
 * and implements saving, retrieving, deleting Todo items 
 */
public class TodoDbLoader {

	private Context ctx;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase mDb;

	public TodoDbLoader(Context ctx) {
		this.ctx = ctx;
	}

	// Open the database connection
	public void open() throws SQLException {
		// DatabaseHelper object
		dbHelper = new DatabaseHelper(ctx, DbConstants.DATABASE_NAME);

		// database object
		mDb = dbHelper.getWritableDatabase();

		// create schema if not exists
		dbHelper.onCreate(mDb);
	}
	
	// close the database connection
	public void close() {
		dbHelper.close();
	}

	// INSERT, returns ID of the new Todo record in DB
	public long saveNewTodo(Todo todo) {
		ContentValues values = new ContentValues();
		values.put(DbConstants.TodoDbConstants.KEY_TITLE, todo.title);
		values.put(DbConstants.TodoDbConstants.KEY_ISDONE, todo.isDone);
		values.put(DbConstants.TodoDbConstants.KEY_DUEDATE, todo.getInsertableDueDate());
		values.put(DbConstants.TodoDbConstants.KEY_PRIORITY, todo.priority.name());

		return mDb.insert(
				DbConstants.TodoDbConstants.DATABASE_TABLE, 
				null, 
				values);
	}
	
	// retrieve one Todo object from DB
	public Todo fetchTodo(long id) {
		// query for the Todo with given ID
		Cursor c = mDb.query(
				DbConstants.TodoDbConstants.DATABASE_TABLE,
				
				// Projection (which columns are returned)
				new String[] { 
						DbConstants.TodoDbConstants.KEY_ROWID, 
						DbConstants.TodoDbConstants.KEY_TITLE,
						DbConstants.TodoDbConstants.KEY_ISDONE,
						DbConstants.TodoDbConstants.KEY_DUEDATE,
						DbConstants.TodoDbConstants.KEY_PRIORITY 
						}, 
				
				// Selection clause and arguments		
				DbConstants.TodoDbConstants.KEY_ROWID + " = ?", 
				new String[]{ String.valueOf(id) },
				
				// GROUP BY, HAVING
				null, null, 
				
				// ORDER BY
				DbConstants.TodoDbConstants.KEY_TITLE 
				);
		
		// if query returned any results
		if (c.moveToFirst()){
			// return the Todo object
			return TodoDbLoader.getTodoByCursor(c);
		}
			
		// otherwise return null
		return null;
	}
	
	
	// Returns a Todo object, pointed by the given Cursor
	public static Todo getTodoByCursor(Cursor c) {
		long id = c.getLong(c.getColumnIndex(DbConstants.TodoDbConstants.KEY_ROWID));
		String title = c.getString(c.getColumnIndex(DbConstants.TodoDbConstants.KEY_TITLE));
		TodoPriority priority = TodoPriority.valueOf(c.getString(c.getColumnIndex(DbConstants.TodoDbConstants.KEY_PRIORITY)));
		boolean isDone = (c.getInt(c.getColumnIndex(DbConstants.TodoDbConstants.KEY_ISDONE)) == 1);
		Date dueDate = new Date(c.getInt(c.getColumnIndex(DbConstants.TodoDbConstants.KEY_DUEDATE)));
		
		return new Todo(id, title, priority, isDone, dueDate);
	}

	// DELETE (return true on success, false on failure)
	public boolean deleteTodo(long rowId) {
		int affectedRows = mDb.delete(
				DbConstants.TodoDbConstants.DATABASE_TABLE,
				DbConstants.TodoDbConstants.KEY_ROWID + "= ?", 
				new String[]{ String.valueOf(rowId) }
				);
		
		return (affectedRows > 0);
	}
	
	// Returns a Cursor that points to all Todo records
	public Cursor fetchAll() {
		// TODO implement (see fetchTodo(id))
		return null;
	}
	
	// Deletes all Todos in the DB
	public void deleteAllTodos() {
		// TODO implement (see deleteTodo)
	}

	// Updates the given Todo in the DB
	public boolean updateTodo(Todo todo) {
		// TODO implement (see saveNewTodo)
		return false;
	}


}