WowAce

LibSQL:Acquire(name, data, args)

Acquires an already existing Lua table and uses it as an internal table.
This allows for SQL manipulation of regular Lua tables without having to create a permanent LibSQL database.

Parameters

name

[string] The name of the table you wish to acquire.

data

[table] The Lua table to acquire

args

[table] Directions on how your table is set up. The blueprint consists of a table with each element using the following parameters:

field = "TYPE [FLAGS] [AS field_name] [AT position]"

Usage

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

LibSQL:Create(tbl)

Creates a table

Parameters

tbl

[string] The name of the new table to create

Return value

obj [LibSQL_Resource] A reference to the new table.

LibSQL:Drop(tbl)

Drops (deletes) a table from the database.
If the table was acquired using LibSQL:Acquire, the referenced Lua table will not be deleted or wiped.

Parameters

tbl

[string] The name of the table to drop.

Return value

true if the command was executed properly, false (with an error) otherwise.

LibSQL:Execute(...)

Executes the given SQL command and returns the appropriate LibSQL_Resource object(if requested).

Parameters

...

[string] The SQL command to execute.

Return value

res A reference to the LibSQL_Resource object created by the command.

Usage

local db = LibSQL:Open(myOldDatabase) -- open an old db or such
local res = db:Execute("SELECT name, id FROM someTable WHERE level > 80")

See also

• LibSQL_Resource

LibSQL:Get(tbl)

Fetches the specified table and returns it as a resource object.

Parameters

tbl

[string]

Return value

obj [LibSQL_Resource] A pointer to the LibSQL_Resource object containing the entire table

Usage

local db = LibSQL:Open(myOldDatabase) -- open an old db or such
local res = db:Get("someTable") -- fetch the res object.

See also

• LibSQL_Resource

LibSQL:New()

Creates a new LibSQL database object

Return value

obj A LibSQL database object

Usage

local db = LibSQL:New() -- instantiate a new db object.
db:Execute("CREATE myTable .....") -- do database stuff

LibSQL:Open(db)

Opens a saved database object for further use.
This is needed when reopening a saved database (for example, a database located in the saved variables scope).

Parameters

db

[LibSQL_Object] The database object to open.

Return value

db A reference to the new table created as a LibSQL_Resource object.

See also

• LibSQL_Resource

LibSQL_Resource()

Almost the entire LibSQL library is built around the LibSQL_Resource object, which allows for great flexibility and code security.
Every resource object is constructed with weak tables referring to the underlying databases and perform operations without requiring any real access to the database. Thus, creating resource sub-objects is easy and fast, and allows for fast internal ways of executing an SQL command without using the SQL parser, if one so desires.

LibSQL_Resource:Alter(statement)

Performs table manipulation based on the command sequence.

Parameters

statement

[string] The SQL ALTER statement used to alter the table

Return value

tbl [LibSQL_Resource] The object itself

Usage

db:Execute("CREATE myTable (name STRING UNIQUE, level NUMBER, id NUMBER AUTO_INCREMENT)")
--Is equivalent to:
local tbl = db:Create("myTable")
tbl:Alter("ADD (name STRING UNIQUE, level NUMBER, id NUMBER AUTO_INCREMENT)")

LibSQL_Resource:Fetch()

Fetches the next row in the resource list and returns it as a list.

Return value

fields [list] The fields in the row

Usage

local res = db:Execute("SELECT name, level, class FROM myTable WHERE name = ?", "JohnDoe");
for name, level, class in res:Fetch() do
   print(name, level, class)
end

LibSQL_Resource:FetchArray()

Same as Fetch, but returns the fields as an array (table) object instead of a list.

Return value

fields #[table] The fields in the row

Usage

local res = db:Execute("SELECT name, level, class FROM myTable WHERE name = ?", "JohnDoe");
for data in res:FetchArray() do
   print(data[1], data[2], data[3])
end

LibSQL_Resource:FetchObject()

Fetches the next row in the resource list and returns it as table with field names as keys.

Return value

fields #[table] The fields in the row

Usage

local res = db:Execute("SELECT name, level, class FROM myTable WHERE name = ?", "JohnDoe");
for data in res:FetchObject() do
   print(data.name, data.level, data.class)
end

LibSQL_Resource:Find(statement)

Searches the resource object for rows of data matching the search clause and returns a new LibSQL_Resource object containing only the rows that matched.

Parameters

statement

[string] The SQL WHERE statement to match against

Return value

res [LibSQL_Resource] A new LibSQL_Resource object containing the rows that matched the search.

Usage

local tbl = db:Get("MyTable") -- get a table ref
local res = tbl:Find("level > 80") -- Find all rows where level > 80
for name in res:Fetch() do print(name) end -- do stuff

See also

• LibSQL_Resource:Select
• LibSQL_Resource:Limit
• LibSQL_Resource:Sort

LibSQL_Resource:Flush()

Flushes the object, removing any table rows stored.

Usage

local numRows = res:Rows() -- prints fx 2 or 50.
for data in res:Fetch() do
   -- do stuff
end
res:Flush() -- flush the object
numRows = res:Rows(); -- prints 0 now.

LibSQL_Resource:Insert(...)

Inserts one or more rows of data into a resource object's originating table.

Parameters

...

[table] One or more Lua tables with either field->value pairs or an ordered list of values. SQL INSERT strings are also supported.

Usage

local mytable = db:Get("myTable")
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 }
);
-- Or we could do it the SQL way:
mytable:Insert("(name, level) VALUES ('Abe', 85)") -- boooring!

LibSQL_Resource:Limit(start, rows)

Limits the rows in the object to the specified range/size

Parameters

start

[integer] (optional) The initial row index to start at.

rows

[integer] The number of rows to limit the resource object to.

Return value

obj [LibSQL_Resource] A ref to itself.

LibSQL_Resource:Remove()

Removes all rows in the the resource object from the originating table

Usage

-- Get some rows:
local res = db:Execute("SELECT * FROM myTable WHERE id > 5 LIMIT 0,10")
-- Delete those rows from the database:
res:Remove()

LibSQL_Resource:Rewind()

Sets the internal row pointer to the first row.

Usage

for data in res:Fetch() do
   -- do stuff
end
res:Rewind(); -- go back to the first row again
for data in res:Fetch() do
   -- do stuff once more
end

LibSQL_Resource:Rows()

Returns the number of rows fetched from the database.

Return value

num [integer] The number of rows.

Usage

local res = db:Execute("SELECT * FROM myTable WHERE name = ?", "JohnDoe");
local numRows = res:Rows();
if numRows == 0 then
    print("No entries found")
else
    print("We found", numRows, "results!")
end

LibSQL_Resource:Seek(position)

Sets the internal row pointer to the position specified.

Parameters

position

[integer] The position of the row to rewind or forward to.

Usage

for data in res:Fetch() do
   -- do stuff
end
res:Seek(2); -- rewind to row 2
for data in res:Fetch() do
   -- do stuff from row 2 -> row ???
end

LibSQL_Resource:Select(statement)

Sets the resource object to return the specified fields when queried.

Parameters

statement

[string] An SQL SELECT sub-statement, fx. "*" or "name, level, id"

Return value

tbl [LibSQL_Resource] A reference to itself

Usage

local tbl = db:Get("myTable") -- get a table
local res = tbl:Select("name, level") -- fetch name and level from all rows.
for name, level in res:Fetch() do
    -- stuff
end

LibSQL_Resource:Sort(hash)

Sorts the results recursively according to the directions given.

Parameters

hash

[table] A table containing the sorting directions.

Return value

tbl [LibSQL_Resource] A reference to itself

Usage

res:Sort( {
    name = LibSQL_Ascending, -- sort by name (ascending) first
    level = LibSQL_Descending -- otherwise, sort by level
})

LibSQL_Resource:Update(hash)

Updates all rows in the resource object with the given data

Parameters

hash

[table/string] A string containing an SQL UPDATE command or a table with field -> value pairs.

Usage

-- Get some rows:
local res = db:Execute("SELECT * FROM myTable WHERE id > 5 LIMIT 0,10")
-- Update them with some info:
res:Update("name = 'smurf!', level = 85")
-- Or update them using a table:
res:Update( { name = "Smurf!", level = (80 + 5) } )