Android: Prepopulating database during build

When switching our Android app database from ORMLite to Room (see previous article), we realised this was a perfect time to fix an old issue that made the first launch of our main application slower than it should be.

We used to ship our application with a json file containing the full tree of all categories on Trade Me. On first launch, we would open that file, parse it, convert to our database model, and then insert them all. Moving this process into a gradle task that would run only once during our release build was a massive improvement.

Since we also sometimes need to update that tree from the application, we looked for a way to have some of that code shared between build process (during gradle task) and the application itself.

A project showing a basic implementation is available on gitHub:

rnpy/prepopulateDatabase

Note that while this demo is aimed at Room database, this should work for any other database engine using SQLite with minor modifications only.

Sharing logic between build and app

The only way to share code between your build and your application is to have it compiled before both of them. All shared code must be made in an independent library. For us, this meant including this in our existing shared libraries, that already support publication to in-house repository, snapshots, local testing, etc. To keep this article short, I’ll use a very simple shared library in .jar format instead.

The demo project uses this tiny library as “shared code”, compiled manually using kotlinc:

Of course, the library code is more complex in our application, but generating a very similar data structure (query + list of list of parameters). Without expanding much, here are a few hints about what to expect when writing this kind of library:

• Code is shared between Android and gradle build, many libraries are not available (we did json parsing with org.json for example).
• You need to be very careful with “standard” Java types. JsonArray is Iterable in its Java implementation, but not on Android.
• Default encoding is not always the same depending on device and where you run it. For example, our tests defaulted to a different encoding and failed on some devices when running using gradle in command line (systemProperty “file.encoding”, “utf-8” solved it).

And one major recommendation: if you have any text in there, write tests about special characters (especially accents), and do this at every possible level (after API call, after parsing, after insertion into DB, etc.). Encoding can break in many different places.

Generating a SQLite database in gradle task:

Demo code available here.

Kotlin is now our main development language, it is possible to write gradle tasks using it the same way we do in Java (by extending DefaultTask). See sample project for full code.

To generate the database file, we need a SQLite library, we used xerial sqlite-jdbc. Loading the library in a gradle task is a bit tricky, since you need to load the classes into the root classloader (see here for more details on how)

Use of GroovyInteroperability is also highly recommended to simplify use of Groovy closures from Kotlin, for example to run code in a single SQL transaction using sqlite-jdbc from gradle:

You may have noticed we use an input file in our task. To make sure the build script creates a database table that matches the exact structure Room expects, we read the schema Room writes during build, and extract the SQL create query from it:

The code generating the database file itself is very straightforward once the above issues are solved:

• open target file as SQL database
• open Room schema definition, look for table creation script, run it
• call shared code to get SQL queries, run them
• close database.

With this task defined, we can now run .\gradlew app:createDatabase, which will create the “static.db” file containing our prepopulated database.

Opening this database from Android:

Demo code available here.

The implementation of Room is very classic, nothing changes except for the first database initialisation.

The code is pretty straightforward, if the database file (DATABASE_FILE) does not exist, we open the prepopulated version (STATIC_DATABASE_FILE), and copy it to DATABASE_FILE.

Then using the Room database builder we open the newly copied file. Note that the database built during build time does not need to contain all tables or all info (indexes for example), the missing ones are added by Room during its first init.

Calling the shared code from Android:

To use the shared code again, but from Android, we need to use lower level SQL, not managed by Room. Room gives us access to a SupportSQLiteOpenHelper, using getOpenHelper.

Note that the code above is for the simplest case: simply deleting everything and inserting conflict-free data without foreign keys.

For more complex operations, you may want to temporarily disable foreign key support, or defer their constraints (Lollipop and above only). see SQLite doc for more details. You can also check the files generated by Room (DemoDatabase_Impl for demo project) to see if you need these, especially clearAllTables method, that will contain relevant code if necessary.

The demo app should answer most questions, but feel free to ask in comment if anything is unclear.