Meme IDE SQLite Database Interface

Simon Monk CTO of Meme IDE

 Today's release of Meme IDE 1.12.0 will include an Interface to the SQLite database bundled with Android.

We ended up with what we believe is a simple but powerful interface that just adds three new library functions to Meme Script.

To put this into context, Meme Script already has the store and retrieve commands that will persist small amounts of data contained in a variable by serialising it. However, for some applications, that is not enough and you need a proper database.

On a mobile app, databases do not tend to have a complex schema. A typical example might be of a parts database for a service engineer. There may only be one or two tables, but they might contain a few thousand records. It is quite likely that an app would be deployed with an initial database, to which occasional incremental updates would be applied.

In my experience, such apps are far more about reading of data than writing of data. As such it is important to have the ability to perform ad hoc queries in the form of SELECT statements to retrieve data, as well as operations to modify the database and efficiently bulk-load a database.

Since the Meme IDE has a user interface for defining record structures, it is important that when we run a query against the database we can put the resultant data into record structures that we have previously defined.

In our design session for Meme IDE's database interface, we explored a number of options including complex Meme Record / SQL table mapping as well as various complex functions for manipulating the data in record form. Eventually, we ditched it all in favour of what we think is the simple and elegant interface described below:

sqlScript(databaseName:String, sql:String) : void

The first function is intended for bulk loading of a database with data. The first argument is the name of the database. If the database does not exist yet, then it will be created. The string containing SQL can contain any valid SQL that is then loaded up into the database. For instance, the string would typically contain a series of table creation commands followed by a whole load of insert commands.

Typically the SQL supplied will be in an embedded file in the app that can be read using retrieve or fetched using a web request.

sqlQuery(databaseName:String, sql:String) : [](Record)

The sqlQuery function performs an SQL query on the database. This should be a SELECT statement. The sqlQuery function should always be called in an assignment. For example:

var contacts : Person[];

contacts = sqlQuery("mydatabase", "select name, tel from person");

This allows the Meme Script compiler to establish the mapping between the Meme Script record and the SQL table being used.

The mapping of field names between record and database table is by name. So in the example above, if 

the record has attributes of name and tel then these will automatically get populated.

For indirect mappings, you can use the SQL AS keyword, for example:

var contacts : Person[];

contacts = sqlQuery("mydatabase", "select name, tel AS telephone from person");

sqlExecute(databaseName:String, sql:String) : Integer

The final database function is intended for use in modifying the data. The SQL will typically be an INSERT, DELETE or UPDATE command. 

The Integer result will depend on the operation. 

For an INSERT, the return value will be the ID or the new row inserted.

For a UPDATE or DELETE will be 0 for success or -1 for failure. This should really be the number of records modified, by unfortunately SQLite does not support this.

That concludes this blog entry. It just remains to say that there will be an example app and further information made available shortly after the release.

We always welcome any comments or ideas for improvement.
Find me on twitter @theappmonk

CodeProject