WowAce

Creating your database

The first thing you'll need is a database object. Creating one is very simple:

local database = LibSQL:New()

You'll most likely want to save your database for later use, and if so, you can always reopen your database. The following code shows how to reopen an old database or create a new if none exists:

local database = mySavedVariables.database -- get our random database object
if database then -- If we already have a saved database object
    LibSQL:Open(database) -- just open it and it'll work fine now.
else
    database = LibSQL:New() -- make a new database
end

Setting up database tables

Now that you have a working database object, you'll need to create one or more tables to work with. In any RDBMS, a table is the equivalent of a giant spreadsheet page, whereas the database would be the book that holds the sheets.

Field types

Each field (column) in your table can be set to a specific data type:

• STRING : Your garden variety string data, fx. "Hello world!".
• INTEGER : Integers (whole numbers) like 1,2,3,4...
• NUMBER : Floating point numbers like 1.25 or 7.39477
• BOOLEAN : Boolean values (true or false)
• BLOB : A binary string. Unlike the regular STRING fields, this type of string is handled with case-sensitivity.
• ENUM : A set of predefined values such as [a,b,c,d]. Only values found in the set are accepted.
  

Furthermore, you can set some field attributes for each field:

• AUTO_INCREMENT : Increments the value of the field by 1 each time a new row is inserted into the table.
• DEFAULT (...) : Sets the default value of this field to (...)
  

While it may seem cumbersome having to set a specific data type for each field, it actually serves a lot of useful purposes:

• LibSQL automatically conforms your data to the specific type so you won't have to check if a number is really a number or if a string is nil.
• some other reasons goes here...

Creating a table

Now then, after we've fiddled with whatever fields and types of fields we'd like, it's time to create a table! We do this using the database object we've already created and the Execute() function, which parses and runs any given command we send to it.

local tbl = db:Execute("
    CREATE myTable 
    (name STRING, level INTEGER, id INTEGER AUTO_INCREMENT)
    ")
-- We now have a table called myTable inside our database!

Adding rows to your table

Before we can search our table for data, we will, of course, need to add some rows to it. There are multiple ways of adding one or more rows to a table:

Conventional INSERT statement

The conventional way to insert is by executing an INSERT statement:

-- Add a single row using the default field locations:
db:Execute("INSERT INTO myTable VALUES('Abe', 85)")

-- Add a row using specified fields:
db:Execute("INSERT INTO myTable (level, name) VALUES(85, 'Abe')")

Internal insertions:

A faster (and safer) way to insert data is by using the internal Insert functions:

local mytable = db:Get("myTable") -- Fetch our table as a resource object
mytable:Insert( {name = "Abe", level = 85} ) -- Insert a single row using associative arrays
mytable:Insert( { "Abe", 85} ) -- Insert a row using just raw values
-- Or, we could insert multiple rows at once:
mytable:Insert( 
    { "Abe", 85},
    {"Ben", 80 }
);

Searching for data

To look for rows that match a given criteria, you can issue one of the following commands:

-- conventional SQL way:
local res_a = db:Execute("SELECT * FROM myTable WHERE foo = 'bar' or bar = 'foo'")
-- Internal way:
local res_b = db:Get("myTable"):Find("foo = 'bar'")

The SELECT statement tells LibSQL to select specific fields from your rows. The asterisk (*) character tells LibSQL to select all fields in each row, but you could also choose to select any specific field, for instance:

SELECT name, level FROM myTable WHERE ....
SELECT level, name, id AS smurf_id FROM myTable WHERE ....

You can also choose to sort and/or limit your results:

SELECT * FROM myTable WHERE level > 80 ORDER BY name ASC LIMIT 0,15

This would sort your results by name in ascending order and limit your results to 15 rows, beginning at the first row(0) of results.