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

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:

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

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:

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

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…

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:

 

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.

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!