LibSQL-1.0

This project is experimental.

This project is only experimental and may change drastically between now and when it comes out of experimental status. It may even be disapproved once that happens, worst-case.

Be forewarned before downloading anything from this project.

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.

You must login to post a comment. Don't have an account? Register to get one!

  • 1 comment
  • Avatar of crazedfred crazedfred Dec 01, 2010 at 11:50 UTC - 0 likes

    Seems to me that the traditional SQL syntax and the faster, internal way is a pretty straight-forward conversion. It would be a shame if people had a slower library because they were more comfortable with traditional SQL.

    Why not whip up an external script (perl, python, whatever) that would convert any instances of "db:Execute" to the correct, fastest method?

    For example, if I had some addon containing

    db:Execute("INSERT INTO myTable VALUES ('abe', 85)")
    

    Running the script would crawl my source directory and replace it with

    --Original SQL was: "INSERT INTO myTable VALUES ('abe', 85)"
    tbl:Insert( {name  "Abe", level = 85} )
    
  • 1 comment

Facts

Date created
Nov 07, 2010
Categories
Last update
Nov 28, 2010
Development stage
Alpha
License
Mine, all mine!
Downloads
197
Recent files

Authors