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);

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.

Saving and displaying dates in a list with Android

Trying to clean up some bits that I’ve left sort of hazy. Now it’s time to wrestle with – how do I really get working dates between Java and sqlite and the user? Really two issues: how do I store it in the DB, and how do I get it from the DB and display to the user?

This is always a tad tricky with any DB. In some ways it seems sqlite treats dates like strings; and you can just stuff anything in that column, without it being valid in any sense, and sqlite accepts it without complaint. But it’s useful to notice that if you insert (or select) datetime() it puts it in the useful “YYYY-MM-DD HH:MM:SS” format (useful because at least they compare/sort properly). So I can format times that way to store them – only issue is GMT vs local time.

For displaying to the user, Android has some time formatting functions, but I’m having a heck of a time figuring them out, not being used to the Java idiom I guess. Looks like this blog post has some good pointers. The other tricky point is that I’m currently just handing off the database cursor to be displayed in a ListItem – not sure how to reformat things in between. I tried creating a custom view extending TextView just for the log output (consulting docs here). I’m not sure where I went wrong, but Android simply did not want to use that view; the view builder in ADT had an error about <init> not being defined, and I don’t remember what error I got when trying to run the app. So I asked on IRC and was advised to extend the Adapter instead. Here’s what I ended up with:

public static final String db_DATE_FORMAT = “yyyy-MM-dd HH:mm:ss”;
public static final DateFormat dbDateFormat = new SimpleDateFormat(db_DATE_FORMAT);

public void setViewText(TextView v, String text) {
if(v.getId() == R.id.ilr_itemLog && text != null && text.length() > 0) {
// try to reformat date text as a local date
try {
Date d = C.dbDateFormat.parse(text.toString());
text = d.toLocaleString();
} catch (ParseException e) {
Log.d(TAG, “Date parsing failed for ” + text);
super.setViewText(v, text);

This seems to work fine. But the real test is later when I let the user modify the log time; we’ll see.