Tutorial: Initializing a writable SQLite database

19 May 2015

Tutorial: Initializing a writable SQLite database

Rob Miracle
Tutorials, Tips and Demos

Working with SQLlite can be challenging, but one specific issue that Corona 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:

local M = {}

function M.copyDatabaseTo( filename, destination )
    assert( type(filename) == "string", "string expected for the first parameter but got " .. type(filename) .. " instead." )
    assert( type(destination) == "table", "table expected for the second paramter but bot " .. type(destination) .. " instead." )
    local sourceDBpath = system.pathForFile( filename, system.ResourceDirectory )
    -- io.open opens a file at path; returns nil if no file found
    local readHandle, errorString = io.open( sourceDBpath, "rb" )
    assert( readHandle, "Database at " .. filename .. " could not be read from system.ResourceDirectory" )
    assert( type(destination.filename) == "string", "filename should be a string, its a " .. type(destination.filename) )
    print( type(destination.baseDir) )
    assert( type(destination.baseDir) == "userdata", "baseName should be a valid system directory" )

    local destinationDBpath = system.pathForFile( destination.filename, destination.baseDir )
    local writeHandle, writeErrorString = io.open( destinationDBpath, "wb" )
    assert( writeHandle, "Could not open " .. destination.filename .. " for writing." )

    local contents = readHandle:read( "*a" )
    writeHandle:write( contents )

    io.close( writeHandle )
    io.close( readHandle )
    return true
end

return M

local M = {}

function M.copyDatabaseTo( filename, destination )

assert( type(filename) == "string", "string expected for the first parameter but got " .. type(filename) .. " instead." )

assert( type(destination) == "table", "table expected for the second paramter but bot " .. type(destination) .. " instead." )

local sourceDBpath = system.pathForFile( filename, system.ResourceDirectory )

-- io.open opens a file at path; returns nil if no file found

local readHandle, errorString = io.open( sourceDBpath, "rb" )

assert( readHandle, "Database at " .. filename .. " could not be read from system.ResourceDirectory" )

assert( type(destination.filename) == "string", "filename should be a string, its a " .. type(destination.filename) )

print( type(destination.baseDir) )

assert( type(destination.baseDir) == "userdata", "baseName should be a valid system directory" )

local destinationDBpath = system.pathForFile( destination.filename, destination.baseDir )

local writeHandle, writeErrorString = io.open( destinationDBpath, "wb" )

assert( writeHandle, "Could not open " .. destination.filename .. " for writing." )

local contents = readHandle:read( "*a" )

writeHandle:write( contents )

io.close( writeHandle )

io.close( readHandle )

return true

end

return M

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:

local dbfunc = require( "copyDBto" )
local result = dbfunc.copyDatabaseTo( "data.db", { filename="data.db", baseDir=system.DocumentsDirectory } )

local dbfunc = require( "copyDBto" )

local result = dbfunc.copyDatabaseTo( "data.db", { filename="data.db", baseDir=system.DocumentsDirectory } )

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:

local sqlite3 = require( "sqlite3" )
local dbfunc = require( "copyDBto" )
 
local filename = "data.db"
local baseDir = system.DocumentsDirectory
 
-- Open "data.db". If the file doesn't exist, it will be created
local path = system.pathForFile( filename, baseDir )

local doesExist = io.open( path, "r" )
if not doesExist then
    local result = dbfunc.copyDatabaseTo( "data.db", { filename="data.db", baseDir=system.DocumentsDirectory } )
    assert( result, "Database failed to copy. Check the logs.")
else
    io.close( doesExist )
end

local db = sqlite3.open( path )   

-- Handle the "applicationExit" event to close the database
local function onSystemEvent( event )
    if ( event.type == "applicationExit" ) then              
        db:close()
    end
end

-- Print the table contents
for row in db:nrows( "SELECT * FROM highscores" ) do
    local text = row.name .. " : " .. row.score
    local t = display.newText( text, 120, 30*row.id, nil, 16 )
    t:setFillColor( 1, 0, 1 )
end
 
-- Setup the event listener to catch "applicationExit"
Runtime:addEventListener( "system", onSystemEvent )

local sqlite3 = require( "sqlite3" )

local dbfunc = require( "copyDBto" )

local filename = "data.db"

local baseDir = system.DocumentsDirectory

-- Open "data.db". If the file doesn't exist, it will be created

local path = system.pathForFile( filename, baseDir )

local doesExist = io.open( path, "r" )

if not doesExist then

local result = dbfunc.copyDatabaseTo( "data.db", { filename="data.db", baseDir=system.DocumentsDirectory } )

assert( result, "Database failed to copy. Check the logs.")

else

io.close( doesExist )

end

local db = sqlite3.open( path )

-- Handle the "applicationExit" event to close the database

local function onSystemEvent( event )

if ( event.type == "applicationExit" ) then

db:close()

end

-- Print the table contents

for row in db:nrows( "SELECT * FROM highscores" ) do

local text = row.name .. " : " .. row.score

local t = display.newText( text, 120, 30*row.id, nil, 16 )

t:setFillColor( 1, 0, 1 )

end

-- Setup the event listener to catch "applicationExit"

Runtime:addEventListener( "system", onSystemEvent )

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.

Tags:

Database, SQL, SQLite3, tutorial

Rob Miracle

[email protected]

Rob is the Developer Relations Manager for Corona Labs. Besides being passionate about helping other developers make great games using Corona, he is also enjoys making games in his spare time. Rob has been coding games since 1979 from personal computers to mainframes. He has over 16 years professional experience in the gaming industry.

Tutorial: Initializing a writable SQLite database