Introducing MickeyDB

History

MickeyDB is essentially a fork of an open-source project called Mechanoid. Mechanoid is a productivity tool which utilises several Domain Specific Languages (DSL) to automatically generate source-code for architectural layers covering Web API integration, Sqlite database access and more for our Android consumer application.
The issue with Mechanoid is that it is an Eclipse plugin, and since the Android development community have migrated to Android Studio, Mechanoid became less feasible since it does not have the necessary language integration that it has with Eclipse, which means no syntax highlighting, no code completion and no grammar validation.
We decided to create the MickeyDB project so we can focus our efforts on a single project with a specific purpose and provide the necessary support for the Android Studio IDE experience.

MickeyDB

In a nutshell, MickeyDB allows you to define your Android sqlite database as DDL statements in *.mickey files, and the Mickey code generator will generate a SqliteOpenHelper and ContentProvider implementation with lots of useful ways to access your data as we will learn in the following sections.

Example Project

This article will build on the example project on github
https://github.com/justeat/mickeydb/tree/master/examples/android-studio
The example project is a project you can import into Android Studio. I won’t go over the details here on the gradle build as the example project demonstrates that and will reserve the project and build setup for a later article.

Initialize Mickey

The first thing you need to do is create an android application class implementation like the ExampleApplication Class implementation and initialize Mickey. This makes sure that Mickey gets the application context at the earliest opportunity and will make coding with the useful generated Mickey code and Mickey library later.

public class ExampleApplication extends Application {
    @Override
    public void onCreate() {
        Mickey.initMickey(this);
    }
}

Next we need a Mickey init file, takeaways.init.mickey which is effectively an entry point for the generator.
The contents of the file at minimum needs to contain the databases fully qualified name (FQN) which defines the java package the generated code goes into.

database com.justeat.example.db.TakeawaysDB

Finally we need to declare the content provider that will be generated for this database in the AndroidManifest.xml.

>provider
            android:authorities="com.justeat.example.db.takeawaysdb"
            android:name=".db.TakeawaysDBContentProvider" />

Notice in the example above that our provider authority is the lowercased database FQN that we defined in the takeaways.init.mickey file.

Migrations

One of the main benefits of Mickey is database schema migrations. With mickey you must specify changes to your database in migrate blocks, as in the example file takeaways.001.mickey (and below).

database com.justeat.example.db.TakeawaysDB
migrate initial {
    create table takeaways (
        _id integer primary key autoincrement,
        name text
    );
}

Breaking this down, we define the database FQN again at the top of the file. This is what ties together DDL migrations to a specific database.
Then we define one migrate block which we give the name initial. The name can be anything, by uniquely naming migrations allows us to refer to them when migrating from one to another.
Inside the migrate block we define one table takeaways with two columns id and name.
If we wish to add more columns to the takeaways we can define them either in the same file, or in a new *.mickey file. The naming convention of files is arbitrary but by using numbers, we can sequence them in the file system so they list in order, for example, create a new file takeaways.002.mickey

database com.justeat.example.db.TakeawaysDB
migrate takeaway_description from initial {
    alter table takeaways add column description text;
}

Rebuilding the project will go through the code generation once more adding this new column to our generated code and creating a new version in the SqliteOpenHelper with the necessary code to migrate from one version to another. Our new database version will become version 2 since the sum of migrations we have so far is 2.
It’s important to specify what we are migrating from, which is why we define migrate takeaway_description from initial. We are saying that the takeaway description migration is applied after initial migration.
We can define as many migrations as we like to shape our database across releases using migrations, we can add migrations to the same file, or across different files for easier management.

Working with data

Mickey generates implementations of ContentProvider, SqliteOpenHelper and Contract class for your database. The names of these generated classes are derived from the database name. For our takeaway database example this will be TakeawaysDBContentProvider, TakeawaysDBSqliteOpenHelper, TakeawaysDBContract.
We can use standard content provider queries to access and change our data, however Mickey provides several generated classes that make this easier.

Record Builders

The first useful generated code are record builders. For each generated table and view, in the case of our takeaways table, we can insert records as follows…

Takeaways.newBuilder()
    .setName("Awesome Pizza")
    .setDescription("Best pizza place in london")
    .insert();

Updating data is just as easy.

Takeaways.newBuilder()
    .setDescription("Best pizza place in the world")
    .update(123); // unique id of this restaurant (_id)

We can also provide a query object (more on querying later) to specify what to update.

Takeaways.newBuilder()
    .setDescription("Best pizza place in the world")
    .update(Mickey.query().eq(Takaways.NAME, "Awesome Pizza")); // unique id of this restaurant (_id)

In the example we say to update takeaways where the takeaway’s name is equal to Awesome Pizza, the constant Takeaways.NAME is generated.

Active Record

Sometimes working with builders is not desirable. For each Mickey table and view, a corresponding active record class is generated to make it easier to work with record.
To insert data using the generated active record we can do the following…

TakeawaysRecord record = new TakeawaysRecord();
record.setName("Awesome Pizza");
record.save();

Similarly we can fetch and update record.

TakeawaysRecord record = new TakeawaysRecord.get(123);
record.setName("Awesome Pizza");
record.save();

Query data

Mickey provides a useful Query fluent interface. A simple query to fetch a cursor would look like this…

Cursor cursor = Mickey.query()
        .eq(Takeaways.NAME, "Awesome Pizza")
        .select(Takeaways.CONTENT_URI,
        new String[] {
            Takeaways._ID,
            Takeaways.NAME,
            Takeaways.DESCRIPTION
        });

The Query API provides a convenient way to build queries which would otherwise have to be constructed using the content provider manually with string builders. Querying using the fluent query API supports most expressions such as ==, !=, >, < and more. The methods for each expression follow the naming eq, neq, gt, lt and so on. After our expressions we can use of the many terminators such as select, delete, selectFirst, firstInt, firstString and so on.
In the example above we also provide the projection as the last parameter to specify which columns we want back.

new String[] {
    Takeaways._ID,
    Takeaways.NAME,
    Takeaways.DESCRIPTION
}

Those familiar with the content provider API will recognise this.
The following example selects Active Records using a like query.

List records = Mickey.query()
    .like(Takeaways.NAME, "%Awesome%")
    .select(Takeaways.CONTENT_URI);

In this case we do not need to specify a projection since the record already has a projection of all the columns in the associated takeaways table.
Also the Query interface provides asynchronous database queries as follows.

Mickey.query()
    .like(Takeaways.NAME, "%Awesome%")
    .selectAsync(new AsyncQueryCallback() {
        @Override
        public void onQueryComplete(Cursor cursor) {
            // TODO: do something with cursor
        }
    },
    Takeaways.CONTENT_URI,
    new String[] {
        Takeaways._ID,
        Takeaways.NAME,
        Takeaways.DESCRIPTION
    });

When the query is complete the onQueryComplete callback is invoked with the loaded cursor data. At this point we can do what we want with the cursor.

Conclusion

MickeyDB is an early preview of a new open-source project we are using at JUST EAT to manage our database. It is effectively a rewrite of Mechanoid db, so those familiar with Mechanoid db should understand the concepts presented in this article.
We are waiting for xtext Android Studio support which should be available late Q2 of 2015 which will allow us to add syntax highlighting, code completion, cross-referencing support and grammar validation.
We welcome contributions to this project. We aim to provide a consistent way to perform android database migrations, and a rich generated API to access your database.
MickeyDB can be found on our github repository here https://github.com/justeat/mickeydb.