Mad, Beautiful Ideas

I’ve been working on a few projects for the Android platform, which I intend to turn into applications. While I do plan to commercialize at least a few of these apps, I’m also looking for ways to improve the platform itself, and the body of knowledge surrounding it. One of those Body of Knowledge issues might seem fairly trivial, but it did catch me up for a few days.

As an aside, a large source of this error was due to the fact that I’ve been doing a lot of work with the LINQ-to-SQL ORM, which led me to make some assumptions about ORM design that in retrospect don’t make a lot of sense, and certainly didn’t work. But the reason they didn’t work was interesting, and worth sharing.

One of my applications is a Microblogging application with clean support for multiple services. The first version supports Twitter and and Laconica-based services, such as the TWiT Army. Currently, no such apps exist, and I’ve seen very few apps handle the multiple accounts situtation in a manner I consider to be reasonable. Because of this need to support multiple accounts, and a desire to normalize the database somewhat, I ended up with the following (basic) table structure.

+----------+ 1 -> n +---------+ n <- 1 +---------+
| accounts |------->| notices |------->| senders |
+----------+        +---------+        +---------+
     |                                      ^
     |            1 -> n                    |
     +--------------------------------------+

If SQLite supported Foreign Keys, I’d have set those up as well, but it doesn’t, and generally doesn’t need to. But these three tables should be able to handle any microblogging situation I can think of off the top of my head. However, every example of writing a SQLiteOpenHelper I was able to find only discussed creating a single table. Between this and my thought that each table should have it’s own class, I created three SQLiteOpenHelper classes, one for each table, even though they all referenced only a single database file.

Here is where everything fell apart. Android maintains Versions for databases based on the package it’s associated with, the name of the database, and the version number you provide. The package and name go into decided what the path on the device will be, while the version is stored (somewhere) on the device so that it knows when it needs to call an OpenHelper’s onUpgrade event handler. It turns out that if, in the SQLiteOpenHelper Constructor, it determines that the database already exists, it won’t call your onCreate or onUpgrade methods at all, even if the particular class which is making the call has never been called before.

This makes perfect sense, in retrospect. And, in fact, there are benefits to keeping all those methods a single class. For instance, it now makes sense to use slightly more complex SQL queries utilizing joins to get the data about a Sender from the notices table, where before I would likely have loaded the notices, and then for each record in the notices table, made a call into the senders table to get the data about the person who sent that message. This would have been far more inefficient, particularly if notices was allowed to reach a non-trivial size.

Basically, I needed to think about the SQLiteOpenHelper more like the LINQ-to-SQL Database Context than as a LINQ-to-SQL table object. One interesting idea from a software engineering standpoint (which may not be worthwhile in Android) would be to define the functions on each table in their own Interfaces, which would then be used to identify in which scope the OpenHelper is being used. I’m not intimately familiar with the sort of output Davlik would generate in this instance, so it’s possible this redirection may not be worthwhile, and most Android apps should have a good enough split between their Activities and their data layer, that it probably would save almost nothing, but it’s an interesting idea.

Hope this helps someone, I know I spent more time on this problem than it deserved.