Tutorial: Initializing a writable SQLite database

Tutorial: Initializing a writable SQLite database

Working with SQLlite can be challenging, but one specific issue that Corona SDK developers encounter is getting a pre-existing database into a location where data can be both read and inserted/updated.

The issue originates in the fact that when you create an application bundle for iOS or a APK file for Android, it’s a self-contained “folder” of files which gets installed to the device. This happens to be read-only, so you cannot update a database contained within it. This application bundle is effectively referenced in code as system.ResourceDirectory.

In addition to system.ResourceDirectory, your application has three folders which you can write files to, including updating a database within these folders. They are system.DocumentsDirectory, system.CachesDirectory and system.TemporaryDirectory. In the case of a database that you plan to update, the logical place for these is system.DocumentsDirectory. Why? Because unlike the “caches” and “temporary” folders, this folder is a persistent folder which will exist as long as the app remains installed on the device.

Initializing the database

When a user first installs your app, the system.DocumentsDirectory folder is essentially empty. If your app needs to access a database of initial information, you first need to move that database from the read-only space (system.ResourceDirectory) into the “documents” folder where records can be inserted/updated. If it’s a very simple database, you could actually use Corona’s sqlite3.* calls to create an entirely new database within system.DocumentsDirectory and then insert your records. However, for a more complex database with multiple tables and a considerable number of records, this concept becomes more difficult and far less practical. As such, an easier approach is to copy the entire pre-existing database in one action.

Consider the following module named copyDBto.lua:

In the module’s primary function (copyDatabaseTo()), you pass in the name of the database which is assumed to be an existing database file within your app’s bundle (system.ResourceDirectory). In addition, you pass a table which contains the filename and baseDir that you wish to move the database to, in this case system.DocumentsDirectory as discussed above:

Assuming there are no errors, this will copy the entire database from your resources bundle to the writable folder you specified as the filename you defined. Of course, this action should only occur on the first run of the app so the database does not get replaced/overwritten every time.

Consider this more thorough implementation of the above 2 lines within main.lua:

On line 2, we require() the copyDBto.lua module. On lines 4 and 5, we set the name and target location of the database file and, on line 8, we create the internal path reference for it.

Lines 10-16 check to see if the database already exists where you expect it. If it doesn’t (first run), we call the function to copy the database on line 12. After that, we open the database and process it as usual — in this example, this includes a function to close the database if the user exits the app, and a test for loop to display some of the data from the database.

Conclusion

As you can see, it’s very easy to create a pre-existing and pre-populated database, bundle it within your app resources, and quickly copy it to a writable folder. Note that this functionality works equally well with text files and JSON files, both of which may also be used to track/store persistent data within an app like high scores, settings, and more.


Rob Miracle
rob@coronalabs.com

Rob Miracle creates mobile apps for his own enjoyment and the amusement of others. He serves the Corona Community in the forums, on the blog, and at local events.

1Comment
  • Ed Maurina
    Posted at 09:57h, 21 May

    Good article Rob. This should help clarify things for folks who’ve recently been struggling w/ db issues and R/W access, as well as answer the question for future developers who run into the same conundrum.