package divestoclimb.checklist.storage;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;
import divestoclimb.checklist.data.*;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
// TODO: Split up this class between database access methods and internal
// object-relational mapping methods (Cursors to objects and objects to
// ContentValues)
public class DbAdapter {
private static final String TABLE_CATEGORY="category";
public static final String KEY_CATEGORY_ID="_id";
public static final String KEY_CATEGORY_NAME="Name";
// This is a special category which is used as a default
public static final long KEY_CATEGORY_ID_UNFILED=0;
private static final String TABLE_TEMPLATE="template";
public static final String KEY_TEMPLATE_ID="_id";
public static final String KEY_TEMPLATE_NAME="Name";
public static final String KEY_TEMPLATE_CATEGORY="Category";
public static final String KEY_TEMPLATE_VERSION="Version";
private static final String[] TEMPLATE_COLUMNS = new String[] { KEY_TEMPLATE_ID,
KEY_TEMPLATE_NAME, KEY_TEMPLATE_CATEGORY, KEY_TEMPLATE_VERSION };
// These constants are used to store the type of item for
// records in table TEMPLATEITEM and CHECKLISTITEM
public static final int ITEM_TYPE_CHECK=0;
public static final int ITEM_TYPE_NOTE=1;
// These constants are for determining the type of container for an item
public static final int ITEM_CONTAINER_TEMPLATE=1;
public static final int ITEM_CONTAINER_CHECKLIST=2;
// The KEY_ITEM_* fields are defined for items in both checklists and templates
private static final String TABLE_TEMPLATEITEM="templateitem";
public static final String KEY_ITEM_ID="_id";
public static final String KEY_TEMPLATEITEM_TEMPLATEID="Template";
public static final String KEY_ITEM_ORDER="DisplayOrder";
public static final String KEY_ITEM_TYPE="Type";
public static final String KEY_ITEM_TEXT="Text";
public static final String[] ITEM_COLUMNS = new String[] { KEY_ITEM_ID,
KEY_TEMPLATEITEM_TEMPLATEID, KEY_ITEM_ORDER, KEY_ITEM_TYPE, KEY_ITEM_TEXT };
private static final String TABLE_CHECKLIST="checklist";
public static final String KEY_CHECKLIST_ID="_id";
public static final String KEY_CHECKLIST_NAME="Name";
public static final String KEY_CHECKLIST_CATEGORY="Category";
public static final String KEY_CHECKLIST_WHENSTARTED="WhenStarted";
public static final String KEY_CHECKLIST_WHENCOMPLETED="WhenCompleted";
public static final String KEY_CHECKLIST_TEMPLATENAME="TemplateName";
public static final String KEY_CHECKLIST_TEMPLATEVERSION="TemplateVersion";
private static final String[] CHECKLIST_COLUMNS = new String[] { KEY_CHECKLIST_ID,
KEY_CHECKLIST_NAME, KEY_CHECKLIST_CATEGORY, KEY_CHECKLIST_WHENSTARTED,
KEY_CHECKLIST_WHENCOMPLETED, KEY_CHECKLIST_TEMPLATENAME,
KEY_CHECKLIST_TEMPLATEVERSION };
private static final String TABLE_CHECKLISTITEM="checklistitem";
public static final String KEY_CHECKLISTITEM_CHECKLISTID="Checklist";
public static final String KEY_CHECKLISTITEM_STATE="State";
public static final String KEY_CHECKLISTITEM_WHENCHANGED="WhenChanged";
private static final String[] CHECKLISTITEM_COLUMNS = new String[] { KEY_ITEM_ID,
KEY_ITEM_ORDER, KEY_CHECKLISTITEM_CHECKLISTID, KEY_ITEM_TYPE, KEY_ITEM_TEXT,
KEY_CHECKLISTITEM_STATE, KEY_CHECKLISTITEM_WHENCHANGED };
/*private static final String TABLE_CHECKLISTFIELD="checklistfield";
public static final String KEY_CHECKLISTFIELD_ID="_id";
public static final String KEY_CHECKLISTFIELD_ITEMID="Item";
public static final String KEY_CHECKLISTFIELD_WIDTH="Width";
public static final String KEY_CHECKLISTFIELD_VALUE="Value";
public static final String KEY_CHECKLISTFIELD_WHENSET="WhenSet";*/
// This DateFormat is kept in the default time zone. It is used to parse Date
// objects for display in local time.
public static final DateFormat mDateFormatLocal = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// This DateFormat has its time zone changed to UTC. It is used to parse and
// format Dates directly to and from the database.
protected static final DateFormat mDateFormatGMT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
static {
mDateFormatGMT.setTimeZone(TimeZone.getTimeZone("GMT"));
}
// More internal variables
private final Context mCtx;
private DatabaseInit mDbHelper;
private SQLiteDatabase mDb;
// Constructor
public DbAdapter(Context c) {
mCtx=c;
}
/**
* Open the database. If it cannot be opened, try to create a new instance
* of the database. If it cannot be created, throw an exception to signal
* the failure
*
* @return this (self reference, allowing this to be chained in an
* initialization call)
* @throws SQLException if the database could be neither opened or created
*/
public DbAdapter open() throws SQLException {
mDbHelper = new DatabaseInit(mCtx);
mDb = mDbHelper.getWritableDatabase();
return this;
}
public void close() {
mDbHelper.close();
}
/**
* Gets an instance of a DateFormat object with a time zone set to GMT, for use
* in parsing and formatting dates for the database. To display a Date object
* to the user, use mDateFormatLocal instead.
*/
public static DateFormat getDateFormatGMT() {
return mDateFormatGMT;
}
/**
* Return a Cursor over the list of all defined categories, alphabetically
* sorted
*
* @return Cursor over all categories
*/
public Cursor fetchAllCategories() {
return mDb.query(TABLE_CATEGORY, new String[] { KEY_CATEGORY_ID,
KEY_CATEGORY_NAME }, null, null, null, null, KEY_CATEGORY_NAME);
}
/**
* Return a Category object for the Category matching cat_id
*
* @param cat_id The ID of the Category to retrieve
* @return The Category object
*/
public Category fetchCategory(long cat_id) {
String catidString=(new Long(cat_id)).toString();
Cursor c = mDb.query(TABLE_CATEGORY, new String[] { KEY_CATEGORY_NAME },
KEY_CATEGORY_ID+"=?", new String[] { catidString }, null, null, null);
Category cat;
if(c.getCount() > 0) {
c.moveToFirst();
cat = new Category(cat_id, c.getString(c.getColumnIndexOrThrow(KEY_CATEGORY_NAME)));
} else {
cat = null;
}
c.close();
return cat;
}
/**
* Return a category object for the Category with the matching name
* @param name The name of the Category to retrieve
* @return The Category object
*/
public Category fetchCategory(String name) {
Cursor c = mDb.query(TABLE_CATEGORY, new String[] { KEY_CATEGORY_ID },
KEY_CATEGORY_NAME+"=?", new String[] { name }, null, null, null);
Category cat;
if(c.getCount() > 0) {
c.moveToFirst();
cat = new Category(c.getLong(c.getColumnIndexOrThrow(KEY_CATEGORY_ID)), name);
} else {
cat = null;
}
c.close();
return cat;
}
/**
* Write a newly created Category to the database
* @param c the Category object to save
* @return The ID of the newly created record
*/
public long createCategory(Category c) {
String name = c.getName();
if(name.equals("") || fetchCategory(name) != null) {
return -1;
}
return DbAdapter.createCategoryStatic(c, mDb);
}
/**
* Write a newly created Category c to database db. This method is the backend
* method behind createCategory and is also used by our DatabaseHelper.
* @param c The Category object to create
* @param db The SQLiteDatabase reference
* @return The ID of the newly created record
*/
protected static long createCategoryStatic(Category c, SQLiteDatabase db) {
ContentValues initialValues = new ContentValues();
// Assigning an ID is only used when initially populating the database. Normal
// save methods preclude this from happening normally.
if(c.getID() != -1) {
initialValues.put(KEY_CATEGORY_ID, c.getID());
}
initialValues.put(KEY_CATEGORY_NAME, c.getName());
return db.insert(TABLE_CATEGORY, null, initialValues);
}
/**
* Update a Category object's record in the database
* For now, all this does is let you rename the category.
* @param c The Category whose record should be updated
* @return true if the operation succeeded, false otherwise
*/
public boolean updateCategory(Category c) {
ContentValues newValues = new ContentValues();
String name = c.getName();
Category sameName = fetchCategory(name);
if(name.equals("") || sameName != null && sameName.getID() != c.getID()) {
return false;
}
newValues.put(KEY_CATEGORY_NAME, name);
String idAsString=(new Long(c.getID())).toString();
return mDb.update(TABLE_CATEGORY, newValues, KEY_CATEGORY_ID+"=?", new String[] { idAsString }) > 0;
}
/**
* Delete a category. Any templates or checklists in this category will be
* moved to the special Unfiled category. This function will never delete the
* Unfiled category itself because of this requirement.
* @param cat_id The ID of the category to delete
* @return true if the operation succeeded, false otherwise.
*/
public boolean deleteCategory(long cat_id) {
// Refuse to delete the Unfiled category
if(cat_id == KEY_CATEGORY_ID_UNFILED) {
return false;
}
String idAsStringArray[] = { (new Long(cat_id)).toString() };
// In order to delete a category, we have to move every Template and
// Checklist in that category to the Unfiled category.
// Templates
ContentValues newCategory = new ContentValues();
newCategory.put(KEY_TEMPLATE_CATEGORY, KEY_CATEGORY_ID_UNFILED);
mDb.update(TABLE_TEMPLATE, newCategory, KEY_TEMPLATE_CATEGORY+"=?", idAsStringArray);
// Checklists
newCategory = new ContentValues();
newCategory.put(KEY_CHECKLIST_CATEGORY, KEY_CATEGORY_ID_UNFILED);
mDb.update(TABLE_CHECKLIST, newCategory, KEY_CHECKLIST_CATEGORY+"=?", idAsStringArray);
// Now we can delete the category
return mDb.delete(TABLE_CATEGORY, KEY_CATEGORY_ID+"=?", idAsStringArray) > 0;
}
/**
* Return a Cursor over the list of all templates in the database
*
* @param sort The table key to sort results by
* @param category The Category ID to match, or -1 for templates in any category
* @return Cursor over all templates
*/
public Cursor fetchTemplates(String sort, long category) {
String where = category != -1? KEY_TEMPLATE_CATEGORY + "=?": null;
String args[] = category != -1? new String[] { (new Long(category)).toString() }: null;
return mDb.query(TABLE_TEMPLATE, TEMPLATE_COLUMNS, where, args,
null, null, sort);
}
/**
* Build a Template object based on the record in the database with ID template_id
* @param template_id The template index in the database to base the Template on
* @return The Template object
*/
public Template fetchTemplate(long template_id) {
String tempIdString=(new Long(template_id)).toString();
Cursor c = mDb.query(TABLE_TEMPLATE, TEMPLATE_COLUMNS, KEY_TEMPLATE_ID+"=?",
new String[] { tempIdString }, null, null, null);
Template t;
if(c.getCount() > 0) {
c.moveToFirst();
t = fetchTemplateFromCursor(c);
} else {
t = null;
}
c.close();
return t;
}
/**
* Build a Template object based on the record in the database with the given name
* @param name The name of the template in the database to base the Template on
* @return The Template object
*/
public Template fetchTemplate(String name) {
Cursor c = mDb.query(TABLE_TEMPLATE, TEMPLATE_COLUMNS, KEY_TEMPLATE_NAME+"=?",
new String[] { name }, null, null, null);
Template t;
if(c.getCount() > 0) {
c.moveToFirst();
t = fetchTemplateFromCursor(c);
} else {
t = null;
}
c.close();
return t;
}
/**
* Utility method to build a Template object from a record previously retrieved from
* the database.
* @param c The Cursor containing the Template fields, moved to the proper position
* @return A Template object representing the information at the current position of the cursor
*/
public Template fetchTemplateFromCursor(Cursor c) {
// Now find out what category this template belongs to
Category cat = fetchCategory(c.getInt(c.getColumnIndexOrThrow(KEY_TEMPLATE_CATEGORY)));
Template t = new Template(c.getLong(c.getColumnIndexOrThrow(KEY_TEMPLATE_ID)), cat,
c.getString(c.getColumnIndexOrThrow(KEY_TEMPLATE_NAME)),
c.getString(c.getColumnIndexOrThrow(KEY_TEMPLATE_VERSION))
);
return t;
}
/**
* Map a Template object to a ContentValues object suitable for insert or
* update to the database.
* @param t The Template to map.
* @return A ContentValues object, or null if a database constraint would
* be violated as a result of this object's values.
*/
protected ContentValues getTemplateValues(Template t) {
String name = t.getName();
Template sameName = fetchTemplate(name);
if(name.equals("") || sameName != null && sameName.getID() != t.getID()) {
return null;
}
return getTemplateValuesStatic(t);
}
/**
* Map a Template object to a ContentValues object suitable for insert or
* update to the database. This method does not check for constraints because
* it is called statically and cannot do so.
* @param t The Template to map.
* @return A ContentValues object.
*/
protected static ContentValues getTemplateValuesStatic(Template t) {
ContentValues v = new ContentValues();
v.put(KEY_TEMPLATE_NAME, t.getName());
v.put(KEY_TEMPLATE_VERSION, t.getVersion());
// TODO remove for Database v6
if(t.getCategory() == null) {
v.put(KEY_TEMPLATE_CATEGORY, KEY_CATEGORY_ID_UNFILED);
} else {
v.put(KEY_TEMPLATE_CATEGORY, t.getCategory().getID());
}
return v;
}
/**
* Add a new template to the database
* @param t The Template object to populate the new record with
* @return The ID of the new template record or -1 if failed
*/
public long createTemplate(Template t) {
ContentValues initialValues = getTemplateValues(t);
if(initialValues == null) {
return -1;
}
return mDb.insert(TABLE_TEMPLATE, null, initialValues);
}
/**
* Add a new template t to database db. Backend method for createTemplate and also
* used by our DatabaseHelper.
* @param t The Template object to populate the new record with
* @param db The SQLiteDatabase object referencing the database
* @return The ID of the new template record or -1 if failed
*/
protected static long createTemplateStatic(Template t, SQLiteDatabase db) {
ContentValues initialValues = getTemplateValuesStatic(t);
// Assigning an ID is only used when initially populating the database
if(t.getID() != -1) {
initialValues.put(KEY_TEMPLATE_ID, t.getID());
}
return db.insert(TABLE_TEMPLATE, null, initialValues);
}
/**
* Update a Template based on the current values in the object
* @param t The Template object to update in the database
* @return true if the Template was successfully updated, false otherwise
*/
public boolean updateTemplate(Template t) {
ContentValues newValues = getTemplateValues(t);
if(newValues == null) {
return false;
}
String idAsString=(new Long(t.getID())).toString();
return mDb.update(TABLE_TEMPLATE, newValues, KEY_TEMPLATE_ID+"=?", new String[] { idAsString }) > 0;
}
/**
* Delete a Template and all its items from the database
* @param template_id The ID of the template to delete
* @return True if the operation succeeded, false otherwise
*/
public boolean deleteTemplate(long template_id) {
String idAsStringArray[] = { (new Long(template_id)).toString() };
// First delete all items belonging to this template
mDb.delete(TABLE_TEMPLATEITEM, KEY_TEMPLATEITEM_TEMPLATEID+"=?", idAsStringArray);
return mDb.delete(TABLE_TEMPLATE, KEY_TEMPLATE_ID+"=?", idAsStringArray) > 0;
}
/**
* Retrieve a Cursor of items belonging to the given container (e.g. Template or
* Checklist)
* This is a backend method used by ItemList--it should not be called from
* other classes. Use new ItemList() instead.
* @param container_type The type of container. ITEM_CONTAINER_TEMPLATE or ITEM_CONTAINER_CHECKLIST
* @param container_id The ID of the Template/Checklist
*/
public Cursor fetchItemsAsCursor(int container_type, long container_id) {
String idString=(new Long(container_id)).toString();
String table, where, columns[];
if(container_type == ITEM_CONTAINER_TEMPLATE) {
table=TABLE_TEMPLATEITEM;
columns=ITEM_COLUMNS;
where=KEY_TEMPLATEITEM_TEMPLATEID+"=?";
} else {
table=TABLE_CHECKLISTITEM;
columns=CHECKLISTITEM_COLUMNS;
where=KEY_CHECKLISTITEM_CHECKLISTID+"=?";
}
return mDb.query(table, columns,
where, new String[] { idString },
null, null, KEY_ITEM_ORDER);
}
public Item fetchTemplateItem(long id) {
String itidString=(new Long(id)).toString();
Cursor c = mDb.query(TABLE_TEMPLATEITEM, ITEM_COLUMNS, KEY_ITEM_ID+"=?", new String[] { itidString },
null, null, null);
Item i;
if(c.getCount() > 0) {
c.moveToFirst();
i = fetchItemFromCursor(c);
} else {
i = null;
}
c.close();
return i;
}
public Item fetchChecklistItem(long id) {
String itidString=(new Long(id)).toString();
Cursor c = mDb.query(TABLE_CHECKLISTITEM, CHECKLISTITEM_COLUMNS, KEY_ITEM_ID+"=?",
new String[] { itidString }, null, null, null);
Item i;
if(c.getCount() > 0) {
c.moveToFirst();
i = fetchItemFromCursor(c);
} else {
i = null;
}
c.close();
return i;
}
/**
* Builds an Item object of the appropriate type based on the data at the
* current position of a Cursor.
* @param c The Cursor to reference to build the Item
* @return The Item representing the data in the Cursor.
*/
public static Item fetchItemFromCursor(Cursor c) {
// There are two independent conditions this method must deal with:
// - Template or Checklist container
// - Note or Check item type
int idIdx=c.getColumnIndexOrThrow(KEY_ITEM_ID);
int typeIdx=c.getColumnIndexOrThrow(KEY_ITEM_TYPE);
int orderIdx=c.getColumnIndexOrThrow(KEY_ITEM_ORDER);
int textIdx=c.getColumnIndexOrThrow(KEY_ITEM_TEXT);
int containerIdx, type;
if(c.getColumnIndex(KEY_CHECKLISTITEM_CHECKLISTID) != -1) {
// This is an item from the checklist item table
type=ITEM_CONTAINER_CHECKLIST;
containerIdx=c.getColumnIndex(KEY_CHECKLISTITEM_CHECKLISTID);
} else if(c.getColumnIndex(KEY_TEMPLATEITEM_TEMPLATEID) != -1) {
// This is an item from the template item table
type=ITEM_CONTAINER_TEMPLATE;
containerIdx=c.getColumnIndexOrThrow(KEY_TEMPLATEITEM_TEMPLATEID);
} else {
return null;
}
// Now build the item based on the item type
Item i;
switch(c.getInt(typeIdx)) {
case ITEM_TYPE_CHECK:
if(type == ITEM_CONTAINER_CHECKLIST) {
Date changedDate = null;
try {
String changedDateString;
if((changedDateString = c.getString(c.getColumnIndexOrThrow(KEY_CHECKLISTITEM_WHENCHANGED))) == null) {
changedDate = null;
} else {
// Dates are stored as GMT in the database. Parse using
// mDateFormatGMT so the Date object is created properly.
mDateFormatGMT.setTimeZone(TimeZone.getTimeZone("GMT"));
changedDate = mDateFormatGMT.parse(changedDateString);
}
} catch(ParseException e) {
// Do nothing, not possible
}
i = new Check(type, c.getLong(containerIdx), c.getLong(idIdx),
c.getInt(orderIdx), c.getString(textIdx),
c.getInt(c.getColumnIndex(KEY_CHECKLISTITEM_STATE)),
changedDate);
} else {
i = new Check(type, c.getLong(containerIdx), c.getLong(idIdx),
c.getInt(orderIdx), c.getString(textIdx));
}
break;
case ITEM_TYPE_NOTE:
i = new Note(type, c.getLong(containerIdx), c.getLong(idIdx),
c.getInt(orderIdx), c.getString(textIdx));
break;
default:
// Error! not good
i = null;
}
return i;
}
/**
* Map the Item i to a ContentValues structure for inserting or updating to
* the database.
* @param i The Item (either Note or Check) to read
* @return The ContentValues, or null if a validation error ocurred
*/
protected static ContentValues getItemValues(Item i) {
ContentValues v = new ContentValues();
String text = i.getText();
if(text.equals("")) {
return null;
}
v.put(KEY_ITEM_ORDER, i.getOrder());
v.put(KEY_ITEM_TEXT, text);
if(i instanceof Note) {
v.put(KEY_ITEM_TYPE, ITEM_TYPE_NOTE);
} else if(i instanceof Check) {
v.put(KEY_ITEM_TYPE, ITEM_TYPE_CHECK);
} else {
// invalid argument passed to this function
return null;
}
if(i.getContainerType() == ITEM_CONTAINER_CHECKLIST) {
if(i instanceof Note) {
// Dummy field to satisfy a constraint
v.put(KEY_CHECKLISTITEM_STATE, 0);
} else if(i instanceof Check) {
v.put(KEY_CHECKLISTITEM_STATE, ((Check)i).getState());
if(((Check)i).getStateChangedDate() == null) {
v.putNull(KEY_CHECKLISTITEM_WHENCHANGED);
} else {
// Use mDateFormatGMT to format the Date as one in GMT for database
// storage
v.put(KEY_CHECKLISTITEM_WHENCHANGED, mDateFormatGMT.format(((Check)i).getStateChangedDate()));
}
}
}
return v;
}
/**
* Add a new template item to the database
*
* @param i The Item object to populate the new record with
* @return The ID of the new Item record, or -1 if the creation fails
*/
public long createTemplateItem(Item i) {
return createTemplateItemStatic(i, mDb);
}
public static long createTemplateItemStatic(Item i, SQLiteDatabase db) {
ContentValues initialValues = getItemValues(i);
if(initialValues == null) {
return -1;
}
// Assigning an ID is only used when initially populating the database
if(i.getID() != -1) {
initialValues.put(KEY_ITEM_ID, i.getID());
}
initialValues.put(KEY_TEMPLATEITEM_TEMPLATEID, i.getContainerID());
return db.insert(TABLE_TEMPLATEITEM, null, initialValues);
}
public long createChecklistItem(Item i) {
ContentValues initialValues = getItemValues(i);
if(initialValues == null) {
return -1;
}
initialValues.put(KEY_CHECKLISTITEM_CHECKLISTID, i.getContainerID());
return mDb.insert(TABLE_CHECKLISTITEM, null, initialValues);
}
/**
* Update an existing template item in the database
*
* @param item The Item object to update. The database record will be matched using the ID of the Item
* @return true if the operation succeeded, false otherwise
*/
public boolean updateTemplateItem(Item item) {
ContentValues newValues = getItemValues(item);
if(newValues == null) {
return false;
}
String idAsString=(new Long(item.getID())).toString();
return mDb.update(TABLE_TEMPLATEITEM, newValues, KEY_ITEM_ID+"=?", new String[] { idAsString }) > 0;
}
public boolean updateChecklistItem(Item item) {
ContentValues newValues = getItemValues(item);
if(newValues == null) {
return false;
}
String idAsString=(new Long(item.getID())).toString();
boolean success = mDb.update(TABLE_CHECKLISTITEM, newValues, KEY_ITEM_ID+"=?", new String[] { idAsString }) > 0;
if(success && item instanceof Check) {
// Check if this checklist is now complete
// Find the checklist ID
Cursor c = mDb.query(TABLE_CHECKLISTITEM,
new String[] { KEY_CHECKLISTITEM_CHECKLISTID },
KEY_ITEM_ID+"=?", new String[] { idAsString },
null, null, null);
c.moveToFirst();
Long checklist_id = c.getLong(c.getColumnIndexOrThrow(KEY_CHECKLISTITEM_CHECKLISTID));
// Get a cursor of all check items with a blank state.
Cursor d = mDb.query(TABLE_CHECKLISTITEM,
new String[] { KEY_ITEM_ID },
KEY_CHECKLISTITEM_CHECKLISTID+"=? AND "+KEY_ITEM_TYPE+"=? AND "+
KEY_CHECKLISTITEM_STATE+"=?",
new String[] {
checklist_id.toString(),
new Integer(ITEM_TYPE_CHECK).toString(),
new Integer(Check.STATE_BLANK).toString()
}, null, null, null);
c.close();
Checklist list = fetchChecklist(checklist_id);
// If there are any items in the cursor, the checklist is not complete.
if(d.getCount() == 0) {
list.setCompletedDate();
} else {
list.clearCompletedDate();
}
list.save(this);
d.close();
}
return success;
}
/**
* Delete a template item from the database
* @param item_id The ID of the item to delete
* @return true if the operation succeeded, false otherwise
*/
public boolean deleteTemplateItem(long item_id) {
String idAsStringArray[] = { (new Long(item_id)).toString() };
return mDb.delete(TABLE_TEMPLATEITEM, KEY_ITEM_ID+"=?", idAsStringArray) > 0;
}
public Cursor fetchChecklists(String sort, boolean completed, long category) {
String completefilter = KEY_CHECKLIST_WHENCOMPLETED+" ISNULL";
if(completed) {
completefilter = "NOT (" + completefilter + ")";
}
String where = category != -1? KEY_CHECKLIST_CATEGORY + "=? AND "+completefilter: completefilter;
String args[] = category != -1? new String[] { (new Long(category)).toString() }: null;
return mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS, where,
args, null, null, sort);
}
public Cursor fetchChecklists(String sort, long category) {
String where = category != -1? KEY_CHECKLIST_CATEGORY + "=?": "";
String args[] = category != -1? new String[] { (new Long(category)).toString() }: null;
return mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS, where,
args, null, null, sort);
}
// TODO: write fetchChecklistsByDate after I decide how I want to group them
public Checklist fetchChecklist(long checklist_id) {
String checkIdString=(new Long(checklist_id)).toString();
Cursor c = mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS,
KEY_CHECKLIST_ID+"=?", new String[] { checkIdString }, null,
null, null);
Checklist cl;
if(c.getCount() > 0) {
c.moveToFirst();
cl = fetchChecklistFromCursor(c);
} else {
cl = null;
}
c.close();
return cl;
}
public Checklist fetchChecklist(String name, Date startDate) {
Cursor c = mDb.query(TABLE_CHECKLIST, CHECKLIST_COLUMNS,
KEY_CHECKLIST_NAME+"=? AND "+KEY_CHECKLIST_WHENSTARTED+"=?",
new String[] { name, mDateFormatGMT.format(startDate) }, null,
null, null);
Checklist cl;
if(c.getCount() > 0) {
c.moveToFirst();
cl = fetchChecklistFromCursor(c);
} else {
cl = null;
}
c.close();
return cl;
}
public Checklist fetchChecklistFromCursor(Cursor c) {
// Now find out what category this checklist belongs to
int catIdx = c.getColumnIndexOrThrow(KEY_CHECKLIST_CATEGORY);
int catId = c.getInt(catIdx);
Category cat = fetchCategory(catId);
Date startDate = null, completedDate = null;
try {
String startDateString = c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_WHENSTARTED));
startDate=startDateString == null? null: mDateFormatGMT.parse(startDateString);
String completedDateString = c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_WHENCOMPLETED));
completedDate=completedDateString == null? null: mDateFormatGMT.parse(completedDateString);
} catch(ParseException e) {
// If this ever happens it's due to mDateFormat not matching up with the
// field constraints.
}
Checklist cl = new Checklist(c.getLong(c.getColumnIndexOrThrow(KEY_CHECKLIST_ID)), cat,
c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_NAME)),
c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_TEMPLATENAME)),
c.getString(c.getColumnIndexOrThrow(KEY_CHECKLIST_TEMPLATEVERSION)),
startDate,
completedDate
);
return cl;
}
protected ContentValues getChecklistValues(Checklist c) {
ContentValues v = new ContentValues();
String name = c.getName();
if(name.equals("")) {
return null;
}
v.put(KEY_CHECKLIST_NAME, name);
// TODO remove for Database v6
if(c.getCategory() == null) {
v.put(KEY_CHECKLIST_CATEGORY, KEY_CATEGORY_ID_UNFILED);
} else {
v.put(KEY_CHECKLIST_CATEGORY, c.getCategory().getID());
}
Date start = c.getStartDate(), completed = c.getCompletedDate();
if(start != null) {
v.put(KEY_CHECKLIST_WHENSTARTED, mDateFormatGMT.format(start));
} else {
v.putNull(KEY_CHECKLIST_WHENSTARTED);
}
if(completed != null) {
v.put(KEY_CHECKLIST_WHENCOMPLETED, mDateFormatGMT.format(completed));
} else {
v.putNull(KEY_CHECKLIST_WHENCOMPLETED);
}
v.put(KEY_CHECKLIST_TEMPLATENAME, c.getTemplateName());
v.put(KEY_CHECKLIST_TEMPLATEVERSION, c.getVersion());
return v;
}
/**
* Add a new checklist to the database.
* @param c The Checklist object to populate the new record with
* @return The ID of the new checklist record or -1 if failed
*/
public long createChecklist(Checklist c) {
ContentValues initialValues = getChecklistValues(c);
if(initialValues == null) {
return -1;
}
// Assigning an ID is only used when initially populating the database
if(c.getID() != -1) {
initialValues.put(KEY_CHECKLIST_ID, c.getID());
}
return mDb.insert(TABLE_CHECKLIST, null, initialValues);
}
/**
* Update a Checklist record based on the current values in the object
* @param c The Checklist object to update in the database
* @return true if the checklist was successfully updated, false otherwise
*/
public boolean updateChecklist(Checklist c) {
ContentValues newValues = getChecklistValues(c);
if(newValues == null) {
return false;
}
String idAsString=(new Long(c.getID())).toString();
return mDb.update(TABLE_CHECKLIST, newValues, KEY_CHECKLIST_ID+"=?", new String[] { idAsString }) > 0;
}
/**
* Delete a Checklist and all its items from the database
* @param checklist_id The ID of the checklist to delete
* @return True if the operation succeeded, false otherwise
*/
public boolean deleteChecklist(long checklist_id) {
String idAsStringArray[] = { (new Long(checklist_id)).toString() };
// First delete all items belonging to this template
mDb.delete(TABLE_CHECKLISTITEM, KEY_CHECKLISTITEM_CHECKLISTID+"=?", idAsStringArray);
return mDb.delete(TABLE_CHECKLIST, KEY_CHECKLIST_ID+"=?", idAsStringArray) > 0;
}
public Checklist createChecklistFromTemplate(long template_id) {
Template t = fetchTemplate(template_id);
Checklist c = new Checklist(t.getCategory(),
t.getName()+" ("+DateFormat.getDateInstance(DateFormat.SHORT).format(new Date())+")");
c.save(this);
Cursor template_items = fetchItemsAsCursor(ITEM_CONTAINER_TEMPLATE, template_id);
if(template_items != null) {
template_items.moveToFirst();
do {
Item i = fetchItemFromCursor(template_items);
Item newi;
if(i instanceof Check) {
newi = new Check(ITEM_CONTAINER_CHECKLIST, c.getID());
((Check)newi).setState(Check.STATE_BLANK);
} else {
newi = new Note(ITEM_CONTAINER_CHECKLIST, c.getID());
}
newi.setOrder(i.getOrder());
newi.setText(i.getText());
newi.save(this);
} while(template_items.moveToNext());
template_items.close();
}
return c;
}
}
|