Trying out OrmLite by migrating LogMyLife #androiddev

In addition to ActiveAndroid I also took a look at OrmLite for Android. It’s a little larger than ActiveAndroid, but doesn’t dwarf my project like DB4O would. And the license is right for an open source dude like myself. Also it looks like it covers more functionality, also allowing you to get at the native DB connection (which doesn’t look possible with ActiveAndroid). And you aren’t required to subclass anything, though convenience base classes are provided. All good stuff.

When I tried out the demo project (which I also used in my TriDroid presentation), it looked reasonably straightforward. So just now I downloaded the JAR to take a look at what it would look like to convert LogMyLife to use OrmLite. What I didn’t notice is that the demo project is based on version 4.6, while the current version has jumped up to 4.10 a few days ago, which is apparently a significant release. I figured out things were different pretty quickly, though, because the first time I tried to annotate a DTO, the @DatabaseTable annotation couldn’t be found. I needed to download and link the ormlite-core JAR in addition to the ormlite-android JAR (which would be all that’s needed in previous releases). I suppose if I were using a maven POM to manage my build this would all be taken care of… but I haven’t gotten around to that.

I hope the docs are still reasonably accurate… I might be a little bit of a guinea pig for this release. We’ll see.

Bringing in the OrmLite helper

So I just went down a long rabbit trail about where exactly to put the OrmLite plumbing. The examples all have a helper that you have to keep track of per-Activity. But didn’t I just demonstrate for myself that an application-wide DB handle worked fine? So I’m thinking about just putting the plumbing in my Application subclass and seeing what happens. The complication seems to be multi-threading – the Google Group had a discussion pointing to this really interesting article on concurrent Sqlite access. I don’t really have multiple threads accessing the DB in LogMyLife, with the possible exception of my broadcast handlers – I’m not actually sure if they come in as a separate thread, or even a separate process, or what. That’s something for me to figure out soon. In the meantime, I guess I’ll go ahead with my scheme to try it in my Application class.

So first, I have to define a helper with callbacks for when I’m opening a connection and need to make sure the schema is in place, which needs to subclass OrmLiteSqliteOpenHelper (which subclasses the SqliteOpenHelper I’m used to) and define what to do in when the DB is being created or updated. Looks like it can also define some simple interactions for getting DAOs. This needs to be configured into the OpenHelperManager which is used to actually create my helper in context; the method of configuration is either to configure a string in my strings.xml and let magic happen behind the scenes, or to explicitly set a factory object on OHM that will return my helper. As it’s not much trouble and (I think) clearer, I do the latter.

Nuts and bolts

Soon I run into a little issue. OrmLite refers to SQLException all over the place, but it’s referring to java.sql.SQLException, generally used for JDBC, as opposed to the android.database.SQLException that’s usually used for Sqlite exceptions. It would be a nuisance just to have to be conscious of which is relevant. But the real difference is that the android.database.SQLException is a runtime exception, while java.sql.SQLException is a checked exception, so I have to deal with it somehow in any of my code that uses the DB. This forces me to re-think exception handling. Checked vs. un-checked exceptions is kind of an old argument, but I thought after all our experiences with Java the scales were starting to tip toward unchecked? As far as I know Java is the only major language using checked exceptions widely; the dynamic languages gaining popularity don’t really even have the ability to use them. So it’s kind of disheartening to see this throwback; although I imagine it arises from the JDBC nature of the rest of the OrmLite package.

Now I go to create a Dao by subclassing BaseDaoImpl, and I’m given a choice of four different constructors (but must include one). The most obvious-seeming one is evidently to be used with a DI container, so I look at the other ones. Eventually I realize that it’s probably best to use the one that’s like the generic DAO constructors, e.g. BaseDaoImpl.createDao(getConnectionSource(), Tracker.class).

While I’m working on the numerous errors Eclipse is reporting from my brutal refactoring, I realize that my BroadcastReceivers don’t have access to my Application subclass, so they have their own code for instantiating the DB connection. There’s no helper base class for them – reasonable, given they don’t really have a life-cycle. Suddenly just defining a string looks like a much nicer path for configuring the helper class.

An example query

And now I come up against one of my first query refactorings. I have a method like so in my existing domain code:

public Alert fetchNextAlert() {
 return fetchAlert(
  db_ALERT_ENABLED + " = 1 AND " + db_ALERT_SKIP_NEXT + " != 1 AND " +
  db_ALERT_NEXT_TIME + " > datetime('now', 'localtime')",
  null, null, null, db_ALERT_NEXT_TIME + " ASC", "1"
 );
}

fetchAlert takes these raw SQL constraints and turns them into a query for a single row from the Alerts table, then converts that row into an Alert DTO. It’s all fairly ugly code, to be sure. Here’s my first crack at a replacement in my OrmLite DAO:

public Alert fetchNextAlert() throws SQLException { 
 return queryForFirst(queryBuilder()
  .orderBy(C.db_ALERT_NEXT_TIME, true)
  .limit(1)
  .where().eq(C.db_ALERT_ENABLED, true)
  .and().ne(C.db_ALERT_SKIP_NEXT, true)
  .and().gt(C.db_ALERT_NEXT_TIME, new Date())
  .prepare());    
 }

Now this is reasonably clear and concise, and in line with how other ORM libraries work. But one thing troubles me: as far as I can tell, there’s no way to get raw SQL in as part of a where clause; I couldn’t duplicate the ” > datetime(‘now’, ‘localtime’)” constraint, and in my experience, when it comes to time, you want to speak the DB’s language. But perhaps I’m wrong this time – certainly the code above captures the intent. If absolutely needed, I could run a raw query to return the ID of the Alert I want, then just use standard DAO methods to turn that into an Alert DTO; two queries instead of one, though.

I have reasonably-sized swaths of code compiling at this point; time for a break.

quick look at ActiveAndroid #androiddev

Just checking out ActiveAndroid, and while it seems to do a lot to make Android DB access easier, a few criticisms appear right up front:

  • Doesn’t having to extend the proprietary base class as your Application class seem a little… invasive? It’s like saying, this isn’t my application, this is an ActiveAndroid application!
  • I’m sure it wasn’t intended this way, but looking at the licensing terms:

The SOFTWARE PRODUCT is licensed as follows:
(a) Installation and Use.
Michael Pardo grants you the right to install and use copies of the SOFTWARE PRODUCT on your computer running a validly licensed copy of the operating system for which the SOFTWARE PRODUCT was designed [e.g., Windows 95, Windows NT, Windows 98, Windows 2000, Windows 2003, Windows XP, Windows ME, Windows Vista].

Let’s ignore for the moment the question of what OS this intended for… but isn’t this JAR going to be built into our app? Doesn’t this technically mean we can’t distribute the app?

  • This is based on reflection, right? So isn’t that gonna be kind of slow on Android?

Intercepting SimpleCursorAdapter data #androiddev

Normally when you create a SimpleCursorAdapter for a ListView, you specify a one-to-one mapping of the columns from the DB and the views where you want them to end up, and the adapter basically just does a toString() on your data and sticks it in the view.

You can, of course, modify this behavior by overriding setViewText, which lets you reformat the text or modify the view as you wish; but it doesn’t give you the DB cursor, just a String, so you can’t pull the data yourself or refer to other columns. But not to despair (or turn to the NotSoSimpleCursorAdapter)! You can modify anything you want by providing a ViewBinder to the adapter.

The main reason I’m talking about this is because I think the documentation on this is just a little vague:

An easy adapter to map columns from a cursor to TextViews or ImageViews defined in an XML file. You can specify which columns you want, which views you want to display the columns, and the XML file that defines the appearance of these views. Binding occurs in two phases. First, if a SimpleCursorAdapter.ViewBinder is available, setViewValue(android.view.View, android.database.Cursor, int) is invoked. If the returned value is true, binding has occured. If the returned value is false and the view to bind is a TextView, setViewText(TextView, String) is invoked. If the returned value is false and the view to bind is an ImageView, setViewImage(ImageView, String) is invoked. If no appropriate binding can be found, an IllegalStateException is thrown.

OK, great. This also promises that you can put any kind of data in any kind of view (not just a TextView). But I didn’t know what it meant by “if a SimpleCursorAdapter.ViewBinder is available.” Turns out it’s pretty simple:

  1. Implement the SimpleCursorAdapter.ViewBinder interface (it has only one method, setViewValue, which gives you the Cursor and the view to work with – and just return false to let the adapter’s default behavior handle the binding). I did this for LogCursorAdapter in an inner class.
  2. Instantiate your implementation and use setViewBinder on your SimpleCursorAdapter instance to set it up as the binder. This makes it “available” for the process described above.

This is arguably better than overriding setViewText because you wouldn’t even have to subclass the adapter to do it – or even create a class (it could be an anonymous implementation). And of course you can access all of the cursor columns in any way you please. Nice.

As far as my earlier data retrieval woes, this gave me the ability to pull data out the way I wanted. Sqlite seems to be storing plenty of precision in the NUMERIC column type; it was just a matter of it being retrieved as a String that caused truncation of precision. In this case the solution was just to pull it out as a Long or Double as appropriate and format it myself (I also learned about DecimalFormat which was very helpful).

Mangling data with sqlite on Android

Oof! I still haven’t recovered from the holidays.

I nearly have LogMyLife in a presentable state, but I just have this one last nit to pick: numbers don’t seem to get recorded the way I expect. I’m using a numerical column (with the intention of using the DB to manipulate numerical values later) but they weren’t storing with the precision I expected.

To explore this, I created a demo project on github. It’s a simple Android app that lets you try out storing values in different ways. It’s interesting for exploring edge cases. I’d like to say more about this but it’s late… maybe later. One answer I was looking for: when you store the value 1.23456789 into a numeric column, if you retrieve it as a String, you get 1.23457; as a Float, 1.2345679; if you retrieve it as a Double you get the full precision. When retrieved as a String it’s getting truncated with only six digits of precision – why?

Running into (minor) Sqlite limitations

Sqlite is pretty nifty and has lots of great features for such a small footprint. It’s deliberately lacking a number of things. I ran up against one of those today: you can’t drop or rename columns. (Of course, you could create a new table with the right columns, copy the data from the first table, drop the first table, and rename the copy.) Also, foreign key checks were evidently implemented fairly recently, and aren’t included in the stock sqlite shipped with Android.

Now here’s another interesting bit. You can create an index on a table and specify ASC or DESC, but unless the database is created with the right file format, the ASC/DESC won’t do anything. The default is to create the legacy file format (for compatibility). How would I create a database in the newer format? It’s not clear in Android where everything is expected to be filtered through the SQLiteOpenHelper, which doesn’t really have a lot of options. There’s some hints at this StackOverflow answer – you can use SQLiteDatabase directly to set flags when opening. I’m not sure that’s worth the trouble for me. I’m writing a personal logger, so I know things will be accessed in pretty much the same order. I don’t know how much difference it makes to order the index though. I’ll let it be for now, but would like to know if there’s an easy way to set flags via SQLiteOpenHelper.

debugging and learning by lending a hand

I start out the day with a lovely SQLite error:

ERROR/AndroidRuntime(800): Caused by: android.database.sqlite.SQLiteException: near “Group”: syntax error: , while compiling: SELECT _id, name FROM Group

Now, I did just change (almost) all of my table and column names, but I don’t see anything wrong with that SQL. Hm; surely there’s a way to get SQLite to log the exact statements it’s running? Maybe that would help. Hmm, maybe, but given the statement above looked fine, maybe I should just try it directly by firing up adb shell and checking the DB myself with sqlite3.

# sqlite3 whendidi.db
SQLite version 3.5.9
Enter “.help” for instructions
sqlite> SELECT _id, name FROM Group;
SQL error: near “Group”: syntax error
sqlite> .schema Group
sqlite> .schema
CREATE TABLE android_metadata (locale TEXT);
sqlite>

So there’s the problem – the “Group” table wasn’t created (for that matter, none were). Pretty simple, but why do I get this as a “Syntax error” rather than “Table doesn’t exist” error? Oh, duh – “group” is a reserved word in SQL. Hahahah, the wacky things I do sometimes… and I would’ve noticed it earlier if I’d paid attention the first time I tried to run the app and it failed (while creating the DB, instead of now while trying to use tables that should have been created the first time).

I got a chance to be AndroidDev support for AndroidFTW today. His problem was simple enough, needed to know how to hook up a ListView and its menu to respond to user clicks. Along the way, I noticed that there are two parallel methods for handling selections from the options menu: the one I’ve been using (probably saw it in books) – onMenuItemSelected(int, MenuItem) and the one that’s in the online guide, onOptionsItemSelected(MenuItem). Doesn’t hurt to use the former but makes more sense to use the latter.