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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: