Tag Archives: jooq

Android Glom Experiments

Over the past few weeks I’ve been diving into Android development using a semi-realistic project to force me to learn properly. I wrote a rough first version of a read-only Glom database UI for Android, called android-glom. So now there’s a version in gtkmm (C++), Qt (C++), GWT (Java) and Android (Java). It’s a good way to really try out a framework and I’ve really enjoyed doing that with no pressure.

Here are some of my thoughts about the experience. I’d welcome feedback about the opinions I’ve formed and about the code I’ve written.

I’ve been doing this while reading O’Reilly’s Programming Android book which is pretty good. I had already read the Busy Coder’s Guide to Android Development a few years ago, which I also liked, but I didn’t realise until recently that it’s been kept up to date as a subscription, because Amazon only has the old edition.

Android Studio

I tried out Android Studio for this project. It’s not quite officially stable but I already find it preferable to Eclipse even though its UI is only slightly less cluttered than Eclipse. It has never asked me to choose a “Perspective”, which is nice. Like Eclipse, it has refactoring tools so you can move Java code around and rename stuff easily. I miss this now when I use C++.


Android doesn’t really support JDBC, making sure that you don’t even try to access an external database server on a tablet or handheld that probably doesn’t have a reliable connection. Those database servers are not meant to be exposed directly on the internet anyway. But it’s still annoying that have to use a similar-but-separate Android-specific and SQLite-specific database API (SQLiteDatabase, SQLiteOpenHelper and Cursor) instead, making code less portable.

Luckily I was still able to reuse much of my SQL-building and data-structure Java code from gwt-glom with just minor changes.

Activities and Fragments

Fragments are a fairly new way to let you rearrange your Android UI in various ways for different sized screens or different orientations. For instance a tablet UI might have one Activity that shows a list fragment at the left and a detail fragment at the right, depending on what item is selected in the list. But a handset version of that UI might open a second detail Activity to show that detail fragment, because it doesn’t have a big enough screen to show both at once.

Unfortunately, there are various official examples that use fragments to support both tablet and handet UIs in one app, but they don’t all use the same techniques or API. For instance, the “Building a Flexible UI” documentation instead suggests just replacing the one fragment in the one single activity for the handset UI and using FragmentTransaction.addToBackStack() so the back button works.

However, the Master/Detail new-activity template code used in Eclipse and Android Studio uses the multiple Activity idea (only one Activity is ever used in the Tablet UI), setting an mTwoPane boolean after detecting which XML layout file has been loaded. This works because you can specify separate layouts (or other resources) for Android to use depending on, for instance, screen size or orientation, and you just need to check what it has decided to use.

So this is the system that I’ve used, later checking that mTwoPane boolean when I want to navigate to another part of the UI, either telling the main Activity to do something with one of its fragments, putting the necessary parameters in a Bundle given to Fragment.setArguments() or just using startActivity() to start a new Activity with the appropriate parameters in its Intent. It’s slightly annoying that fragments take a Bundle but Activities take an Intent, with both having very similar but separate APIs.

Content Providers

Many parts of the Android API, such as ListView, ListFragment, and ListActivity, assume the use of a Cursor to access data, which in turn requires that you use a either a SQLiteDatabase or a ContentProvider.

The Android API documentation implicitly pushes you, via deprecation,  to use a  Content Provider, rather than just a SqliteDatabase, to separate your UI and data into separate processes even when you have no need to share your application’s data with other applications, even though the high-level documentation says “You don’t need to develop your own provider if you don’t intend to share your data with other applications.”

Specifically, you can tell your ListView, ListFragment, or ListActivity to show your SQLite database data via a CursorAdapter, such as SimpleCursorAdapter, which takes a Cursor. That Cursor can be the result of a SQLiteDatabase.query() or rawQuery(). But you’ll need to call Activity.startManagingCursor() on that Cursor and that is deprecated (probably because its not asynchronous) in favour of using CursorLoader (by implementing LoaderManager). And that means using a ContentProvider. See the “Running a Query with a CursorLoader” documentation. I wish that the documentation and examples just started off with this clear recommendation.

You can instead implement a custom CursorLoader that uses a SQLiteDatabase directly, but you then lose some functionality such as automatic ListView updating via notification when the data changes. And I think I’ve read of other ListView functionality that only works with a ContentProvider but I can’t find that documentation now. I think it was something about searching or auto-completion.

Content Providers are a bit awkard

Unfortunately a ContentProvider doesn’t provide quite as much loose binding as you’d hope. The ContentProvider API is very much like a SQL API. Most example code seems to just expose the SQLite database structure directly, sometimes with a simple mapping of column names as a thin separation. Given that this is the most common ContentProvider implementation that gets cargo-culted, it seems that it should be a lot less verbose.

(Update: I noticed that the mapping of external column names to internal columns names is useless anyway because you end up exposing the internal column names when your Content Provider’s query() returns the database query’s cursor as your Content Provider query()’s cursor. Client code will often then need those internal database column names to call Cursor.getColumnIndex() to then get the values in the columns. This is only a problem when you don’t specify specific columns, which would then be mapped by SQLiteQueryBuilder.setProjectionMap().)

I also don’t like how this forces so much implementation code to be forced into one ContentProvider API, separated only by switch statements in the query(), insert(), update() and delete() implementations. I’d like an easy way to delegate my various ContentProvider URL implementations to separate classes.

Here’s a link to my ContentProvider to show what I mean. It feels like a mess.

A database as a web service

On the other hand, it’s good that the ContentProvider provides a route to storing your data on the internet instead, maybe just caching it locally, without changing your UI code. For instance, using some RESTful service, whose API would closely match a database API.

In fact, I’d like some easy way to just expose a database on the web, with multiple user-level and table-level access control. I know that developers spend huge amounts of time implementing very specific “business logic” web APIs to hide their underlying databases, and I know that developers rightly fear anyone having direct access to their databases. But couldn’t it be done properly? I guess that most of these systems have much the same code with much the same security mistakes, just to put the names of their database tables and columns behind a few levels of programming language class names, method names and parameter names that provide just a thin sense of security and a token hint at modularity.

I’ve found a few systems that do this, but I have no idea of which ones are trusted and used much.


OnlineGlom: MySQL support

Of and on over the last couple of weeks, I have added MySQL support to OnlineGlom, like I recently added to Glom itself. Now it works and is in git master. When I have the time I’ll try it out with Google’s Cloud SQL (MySQL) in Google’s App Engine.

As with regular Glom, most of the work was getting the self-hosting tests to work with MySQL – to start the MySQL instances, create the databases, fill them with data, test them and shut them down. The rest of the support was mostly covered already by JOOQ but I had to make sure it always knew what SQL dialect to use.