Corona中文站

Corona includes support for SQLite databases on all platforms. This is based on the built-in sqlite support on the iPhone, and a compiled version of SQLite on Android. Note that this increases the size of the Android binary by 300K.

SQLite is available in all versions of Android, iPhone, and iPad, as well as in the Corona Simulator.

The Lua API is provided by luasqlite 3 v0.7, source available from luaforge.net/projects/luasqlite/. The documentation for luasqlite3 can be viewed at luasqlite.luaforge.net/lsqlite3.html. This documentation also includes test and sample code.

NOTE: When providing a file path to open(), make sure to use system.pathForFile(). Providing just a plain file name such as "my.db" will not work consistently across the simulator and devices, especially on Android (see system.ResourceDirectory).

Examples

The following example opens an in-memory database, creates a new table, adds some data, and finally displays the table's contents on the screen.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16
require "sqlite3"

local db = sqlite3.open_memory()



db:exec[[

CREATE TABLE test (id INTEGER PRIMARY KEY, content);

INSERT INTO test VALUES (NULL, 'Hello World');

INSERT INTO test VALUES (NULL, 'Hello Lua');

INSERT INTO test VALUES (NULL, 'Hello Sqlite3')

]]



print( "version " .. sqlite3.version() )



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

local t = display.newText(row.content, 20, 30 * row.id, null, 16)

t:setTextColor(255,0,255)

end


This example demonstrates how to open a file-based database and to check if the table exists before creating it. It also closes the database upon application exit.

--Include sqlite

require "sqlite3"

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

local path = system.pathForFile("data.db", system.DocumentsDirectory)

db = sqlite3.open( path )



--Handle the applicationExit event to close the db

local function onSystemEvent( event )

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

db:close()

end

end





--Setup the table if it doesn't exist

local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]

print(tablesetup)

db:exec( tablesetup )



--Add rows with a auto index in 'id'. You don't need to specify a set of values because we're populating all of them

local testvalue = {}

testvalue[1] = 'Hello'

testvalue[2] = 'World'

testvalue[3] = 'Lua'

local tablefill =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[2]..[['); ]]

local tablefill2 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[2]..[[',']]..testvalue[1]..[['); ]]

local tablefill3 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[3]..[['); ]]

db:exec( tablefill )

db:exec( tablefill2 )

db:exec( tablefill3 )



--print the sqlite version to the terminal

print( "version " .. sqlite3.version() )



--print all the table contents

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

local text = row.content.." "..row.content2

local t = display.newText(text, 20, 30 * row.id, null, 16)

t:setTextColor(255,0,255)

end



--setup the system listener to catch applicationExit

Runtime:addEventListener( "system", onSystemEvent )