SQLightning

local sl = require("sqlightning")

Constructor

.new( [options_tbl] )

Creates a new database instance. If dp_path is omitted (or no options table is passed), the database will be created in-memory.

Example:

-- FILE BASED
local db = sl.new({ db_path = "my.db" })
-- MEMORY BASED
local db = sl.new()

Options Table:

parameter	type	description
db_path	string	The path to the database file relative to the Documents directory.
db_debug	boolean	Output all queries to the console window.

.version

Returns the current version of SQLightning.

Example:

print( db.version() )

---

Methods

:add( table_name, values_tbl )

Example:

local err = db:add("cats", {
  name = "Tribble",
  age = 5
})

:addMany( table_name, records_tbl )

Adds multiple records to a database table.

Example:

local cat_records = {
  {
    name = "Spiffy",
    age = 2
  },
  {
    name = "Ginger",
    age = 5
  }
}

local err = db:addMany("cats", cat_records)

:createTable( table_name, values_tbl )

Create a new database table with fields and types.

Example:

local err = db:createTable("cats", {
  name = {db.TEXT, db.UNIQUE},
  color = {db.TEXT},
  age = {db.INTEGER}
})

:delete( table_name, query_tbl )

Example:

local err = db:delete("cats", {
  where = { age = 2 }
})

:deleteById( table_name, id )

Delete a record based on the primary row id.

Example:

local err = db:deleteById("cats", 20)

:deleteAll( table_name )

Deletes all records in a database leaving an empty database table.

Example:

local err = db:deleteAll("cats")

:deleteLike( table_name, field, value )

Run a delete query using the LIKE modifier.

Example:

local err = db:deleteLike("cats", "color", "blue")

:get( table_name, query_tbl )

Return a set of records from a database table. Records are contained in a table based array.

Example:

local rows, err = db:get("cats", {
  where = { age = 2 },
  orderby = {
    color = db.DESC
  }
})

--Outputting
for row in rows do
  print(row.color)
end

:getAll( table_name )

Retrieve the entire record set from a database table.

Example:

local rows, err = db:getAll("cats")

:getOne( table_name, query_tbl )

Retuns a single record. Thie result is a single row object, and is not contained in a table array.

Example:

local row, err = db:getOne("cats", {
  where = { age = 2 }
})

--Outputting
print( row.color )

:getById( table_name, id )

Return a single row using an id.

Example:

local row, err = db:getById("cats", 3)

--Outputting
print( row.color )

:update( table_name, query_tbl )

Example:

local err = db:update("cats", {
  where = { id = 1 },
  values = {
    age = 5
  }
})

:updateById

:query( query_string )

Run a raw query on the database table.

Example:

local res, err = db:query("SELECT * FROM cats;")

:count( table_name )

Return the record count from a table.

Example:

local cnt = db:count("cats")

CONSTANTS

• db.TEXT
• db.INTEGER
• db.NUMERIC
• db.REAL
• db.BLOB
• db.UNIQUE
• db.NOTNULL
• db.ASC
• db.DESC

WHERE Field Modifiers

The AND modifier is used by default when no modifier is added.

Example:

{
  where = { color = "Green", ANDLT_age = 3 }
  --> WHERE color='Green' AND age<3
}

Can NOT be used on first where table entry

• AND_*

• ANDLT_*

• ANDLTE_*

• ANDGT_*

• ANDGTE_*

• OR_*

• ORLT_*

• ORLTE_*

• ORGT_*

• ORGTE_*

Can ONLY be used on first where table entry

• LT_*
• LTE_*
• GT_*
• GTE_*

-- !! NO - Use ANDLT_* or ORLT_* on second entry. !!
{
 where = { color = "blue", LT_age = 2 }
}

-- ** YES - Use LT_*, LTE_*, GT_*, or GTE_* on first entry. **
{
 where = { LT_age = 2, OR_color = "blue" }
}
--> WHERE age<2 OR color='blue'

The Query Table

Not all fields may be applicable for each method. See method docs for usage.

parameter	type	description
where	table	desc
orderby	table	desc
limit	number or table	desc
columns	table	desc
values	table	desc