Using SQLite in Corona

The usefulness of SQLite far exceeds the ability to merely save out player data, high scores, etc. to the device you’re deploying to. You can use a database for the actual mechanics of your game.
An important note: The database in this article is read only, you wont be able to write to it. Why? Check this thread at Corona’s forums for the answer!

Familiar with damage tables from RPG’s such as AD&D and GURPS? Good! Because that’s exactly where we’re gonna dive into the use of a SQLite table to handle parsing out damage to a player’s ship. Of course, this can apply to a broad range of topics and not just pigeon holed to an RPG character class table but we’ll start there for now. We are, afterall, gamers are we not? This article assumes that you have at least a basic grasp on databases, what they are and how to use them by querying them. (I’m no master of databases, but email me if you have issues!)

Get your data into a database

Instead of hard-coding your logic, databases can provide a pretty neat and flexible alternative. A great example are the aforementioned “character class tables” popular in many RPGs like AD&D, GURPS, etc.

Here’s one, let’s take a look at it:

A simple table

So let’s say you create a character to fill with data. Depending on what character type the player chooses, you’ll have to populate the data somehow.

Well, you could hard-code it like

-- Create our test character
local myCharacter = {}
-- Set some default attributes
myCharacter.strength = 0
myCharacter.dexterity = 0
myCharacter.hitpoints = 0
-- Let's say the player chose a "fighter" class for his/her character
local tempClass = "fighter"
if(tempClass == "wizard")then
	myCharacter.strength = 10
	myCharacter.dexterity = 15
	myCharacter.hitpoints = 12
else if(tempClass == "cleric")then
	myCharacter.strength = 12
	myCharacter.dexterity = 15
	myCharacter.hitpoints = 18
else if(tempClass == "fighter")then
	myCharacter.strength = 25
	myCharacter.dexterity = 18
	myCharacter.hitpoints = 35
else if
--Yada yada yada....

But that becomes a pain to code, and it’s way less flexible than we want. What happens if you start tweaking your table to adjust game play? Testers come back and give feedback, “Hey the strength to damage ratio isn’t so good here, it should be x instead of y.“….

You’d have to go back into the code and adjust the variables. BUT, if you had the data in a convenient, standalone database, you’d be able to change the data lickety split with your favorite DB editor.


Create a database and populate it with data

First off, we’ll need a database to play with. You can create a database from code in Corona per their demonstration code found here, but I personally don’t like creating my databases in-code. I like doing it externally. Grab an SQLite editor from the interwebs, I have really liked SQLite Admin and Lita. Lita is an AdobeAIR app and is cross platform, SQLite Admin is Windows. We’ll create a database.

Open whichever SQLite editor you downloaded and paste the following into it:

INSERT INTO classTable (class,strength,dexterity,hitpoints) VALUES ('cleric',12,15,18);
INSERT INTO classTable (class,strength,dexterity,hitpoints) VALUES ('fighter',25,18,35);
INSERT INTO classTable (class,strength,dexterity,hitpoints) VALUES ('wizard',10,15,12);
You should execute the above SQL query and it should create a database for you and populate it with data. Save the file into your application’s root directory, and name it “classTable.db”. Next, paste the following in to your “main.lua” file
--Include sqlite, prep it for excitement
require "sqlite3"
local path = system.pathForFile("damage.db", system.ResourceDirectory)
db = path )
--print the sqlite version to the terminal
print( "version " .. sqlite3.version() )
--Handle the applicationExit event to close the db
function onSystemEvent( event )
  if( event.type == "applicationExit" ) then

This will enable the database functionality in our app, and properly terminate the connection to the database when the app is terminated (That’s pretty important I hear, it can cause memory leaks and other whack issues if you don’t)

OK this sets the stage, but now we want to load our database into the app and start querying it for some data. Add the following code:

-- OK, let's select all of our data and print it to the console...
-- It's handled in a for-loop
for row in db:nrows("SELECT * FROM classTable") do
  -- Iterate through each of our rows of data
  -- It uses dot notation, so if you want to get a column's data, say class,strength,desterity or hitpoints
  -- you'd do something like local myVal = row.class, or row.strength. See below!
  -- OK, I'm still getting used to the special characters, but FYI the '/t'
  -- prints a tab, so that way our data lines up nicely in the output to the console
  print("class: "..row.class.."tstrength: "..row.strength.."thitpoints: "..row.hitpoints.."tdexterity: "..row.dexterity)

And you can run the program now. The console output should show the following output:

Copyright (C) 2009-2010  A n s c a ,  I n c .
        Version: 2.0.0
        Build: 2011.484
SQLite version: 3.6.12
class: cleric   strength: 12    hitpoints: 18   dexterity: 15
class: fighter  strength: 25    hitpoints: 35   dexterity: 18
class: wizard   strength: 10    hitpoints: 12   dexterity: 15

Pretty cool eh? You’re getting SQLite database functionality implemented in your Corona app with such little effort!

Now we can get even more awesome. Let’s create a table for our player and assign the proper attributes that we’ve retrieved from the database.

Add the following code…

-- Make a table for our player
player = {}
-- Say we want to create a fighter. Note the WHERE class = 'fighter' part.
-- Let's do a print to separate our debug output
for row in db:nrows("SELECT * FROM classTable WHERE class='fighter'") do
	player.class = row.class
	player.strength = row.strength
	player.hitpoints = row.hitpoints
	player.dexterity = row.dexterity
	print("Player's class: "..player.class.."tstrength: "..player.strength.."thitpoints: "..player.hitpoints.."tdexterity: "..player.dexterity)

Now we’re taking results of our database query and assigning them to a table for a player character. No hassling with manually assigning the data via hardcoded static code, just using the database makes this so much easier! The data can be modular, you can edit the database externally. Pretty cool.


Let’s make it a little more modular…let’s make a function that will populate our table automatically and accept a text string for what class we want to create.


Paste the following code:

function createCharacterClass(passedClass)
	local SQL = "SELECT * FROM classTable WHERE class='"..passedClass.."'"
	local tempTable = {}
	for row in db:nrows(SQL) do
		tempTable = row
	return tempTable
player2 = {}
-- Try uncommenting each line to see the result
player2 = createCharacterClass("cleric")
--player2 = createCharacter("wizard")
--player2 = createCharacter("fighter")
--player2 = createCharacter("sausage")
-- Note, if you uncomment the line with the "sausage" you'll notice nothing prints
-- Thats because the "sausage" class doesn't exist, so it's a nil. The below print
-- line checks to see if the returned table is nil before printing. You can add your
-- own error handling to your code. I'm not too awesome in LUA just yet, but I believe
-- that you can add that to the "createCharacter" function itself
	print("We created a "..player2.class.."t with strength: "..player2.strength.."thitpoints: "..player2.hitpoints.."tdexterity: "..player2.dexterity)


Pretty cool eh? We’re passing variables to our query and populating a table with the results! Nice and modular the way we like it. Now let’s get even more tricky. Let’s create a table of our players, and populate them randomly. Remember the “classTableID INTEGER PRIMARY KEY AUTOINCREMENT”? Well, here’s the fun part. That auto incrementing make a numerical value for each record we insert into our database! So we can actually do “SELECT * from classTable WHERE classTableID = ” and then put a number.

-- Make a function to create a character based on the classTableID value...
function createCharacterClassID(passedID)
	local SQL = "SELECT * FROM classTable WHERE classTableID='"..passedID.."'"
	local tempTable = {}
	for row in db:nrows(SQL) do
		tempTable = row
	return tempTable
playerTable = {}
for i=1,10 do
	-- Make each one of our entries a table itself, so
	-- we can populate it with data
	playerTable[i] = {}
	-- Now, we have three total classes; wizard,cleric and fighter so
	-- let's pick a random number between 1 and 3 to populate our list
	-- of playerTable to make each a random class of player...
	local tempValue = math.random(1,3)
	-- Call the createCharacterClassID functino and pass it an appropriate value
	playerTable[i] = createCharacterClassID(tempValue)
-- And now let's list all of our players we created in the playerTable
for i=1,#playerTable do
	print("Player #"..i.." is a "..playerTable[i].class.."t with strength: "..playerTable[i].strength.."thitpoints: "..playerTable[i].hitpoints.."tdexterity: "..playerTable[i].dexterity)

The entire project can be downloaded here: CoronaSQLite


I hope I’ve managed to de-mystify some of the SQLite issues with Corona SDK. Use it for great justice!