LibSQL is a library that incorporates a simple relational database (rdbms) into whatever awesome addon you're working on. For a quick guide, see the LibSQL Primer. For in-depth documenation, see Api/LibSQL-1.0

The SQL object is available in three variants:

Conventional SQL access

The standard way to communicate with an SQL server

local db = LibSQL:New()
-- make a table
db:Execute("CREATE myTable (name STRING UNIQUE, level INTEGER, id INTEGER AUTO_INCREMENT)")

-- insert some stuff
db:Execute("INSERT INTO myTable VALUES ('abe', 85)")
db:Execute("INSERT INTO myTable VALUES ('benny', 85)")

-- search for data
local res = db:Execute("SELECT name, level, id FROM myTable WHERE level > 80 SORT BY name ASC")
print("We got", res:Rows(), "results!")
for name, level, id in res:Fetch() do
    print(name, level, id)
end

db:Execute("TRUNCATE myTable")-- Empty the table
db:Execute("DROP myTable")-- or delete it

Internal (fast) access

The internal access method is a lot faster as it requires much less parsing by the library, but it also requires some knowledge of Lua. You can mix conventional and internal access as you like.

-- create a table
local db = LibSQL:New()
local tbl = db:_Create("myTable"):_Alter("ADD (name STRING UNIQUE, level INTEGER, id INTEGER AUTO_INCREMENT)")

-- insert some stuff
tbl:Insert( {name  "Abe", level = 85} ) -- One way of inserting internally
tbl:Insert({"Benny", 85}) -- another way
tbl:Insert(
    {"Benny", 85},
    {"Dan", 83},
    {"Eric", 84}
) -- multiple inserts at once

-- find some rows
local res = tbl:Find("level > 80"):Sort({name = LibSQL_Ascending, level = LibSQL_Descending}):Select("name, level, id")
print("We got", res:Rows(), "results!")
for name, level, id in res:Fetch() do
    print(name, level, id)
end

Using your own Lua tables as storage

The LibSQL:Acquire function lets you take control of any regular Lua table and treat it as if it was an SQL table. You can search, add, edit and remove elements, and when you're done, you just throw away the SQL object again.

local db = LibSQL:New()
local someTable = { -- my own Lua table for stuff
	{name = "JohnDoe", level = 80, id = 2},
	{name = "JaneDoe", level = 85, id = 1}
}
-- Now let's use LibSQL to power up the table.
db:Acquire("myHash", someTable, {name = "STRING", level = "INTEGER", id = "INTEGER"})

local res = db:Execute("SELECT name, level FROM myHash WHERE level > 80")
for name, level in res:Fetch() do
	print(name, level) -- Outputs: JaneDoe 85 !
end

-- Another example with a simpler table:
local tbl = {
    {1234, "Smurf", 45, "some other things"},
    {4, "blahblah", 2, 12345} -- doesn't _have_ to conform to standards, but it helps.
    {4, "foobar", 4, "boofar"}
}
db:Acquire("fooTable", tbl, {"INTEGER AS field_a", "STRING AS field_b", "INTEGER AS field_c", "STRING as field_d"})
-- then do stuff here

Any changes you make with the SQL object will also change your Lua table, but you can detach the SQL "motor" at any time - your table structure stays intact.