Reminderer TPS Report

I’ve decided to take a break this past weekend. I just realized that in between my regular job and this project, I’ve been working +12 hour days non-stop for about a month. (Somebody throw me a pity party. Boo hoo, hoo :-)) My how time flies. Time to take a break.

Here’s a summary of the progress so far:

The alarm system is just about done. I ended up changing the design schema. Basically, I originally designed it so that tasks have a one-to-one relationship with repeatable tasks but it’s actually a one-to-many relationship:

+CAPTION: Task table
| primary key | string    | long     | boolean     | integer     |

#+CAPTION: Repeating task table
| ID          | TASK_ID_FK  | NEXT_DUE_DATE |
| primary key | foreign key | long          |
  • The task table is the main table. Every task has one entry here.
  • Repeating tasks have an entry in the task table and the repeating task table.
  • Every time a repeating task is due, it creates a new repeating task for the next due time. It stays in the database until you complete it. (Hence, tasks have a one-to-many relationship with repeating tasks.)

How do we access the data? Use joins and unions:

  • Querying individual tasks is a join.
  • Querying all tasks is a union.

Joins and Unions with ContentProviders and CursorLoaders

Speaking of joins and unions, how do you do these with ContentProviders and CursorLoaders?

The API for content providers and cursor loaders doesn’t let you do unions or joins. Fortunately, there’s a really simple workaround: map the Uris to whatever you want.

For Reminderer, the provider Uris look like this:

  • content://AUTHORITY_NAME/taskjoinrepeat
  • content://AUTHORITY_NAME/loadopentasks

We then run the appropriate SQL in the content provider depending on the Uri. For example, the query method to handle the taskjoinrepeat Uri looks like this:

//query() method...
//if URI == taskjoin repeat ...
SQLiteDatabase db = openHelper.getReadableDatabase();
String rawQuery = selection != null ?
    String.format("SELECT %s FROM %s,%s WHERE %s",
    :String.format("SELECT %s FROM %s,%s",
return db.rawQuery(rawQuery, selectionArgs);

where convertArrayToString is a helper method that maps a string array into a comma separated list.

While we can’t control the tables to be joined (it’s hardcoded in the Uri) we can control the columns queried and the selection via the projection and selection arguments. In other words, for joins, we lose no functionality and actually extend the API to handle joins.

Unions are a bit trickier. Technically, you need to pass in a different set of columns and selection arguments for each table. You can’t do that using the standard API for providers and cursor loaders. I ended up writing a generic class that takes the projection and selection argument and splits them for each table. Basically, the first half of the projection is for the first table, the second half is for the second table, both tables share the same selection arguments. This is all the functionality I need for now. If you want to see sample code, take a look at the gist.

That’s all for today. Checkout the progress in the newAlarmSystem branch over at GitHub.

Update: When using URIs this way, you need to make sure you notify registered observers correctly. See this post.


One thought on “Reminderer TPS Report

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s