--[[ sqlite 3 module for Lua 5.4 ]]--[[
== Why ==
There is an official lsqlite version:
https://lua.sqlite.org/
...which has a c interface and lots of features.
This module has it's home at:
https://holmeinbuch.de/repo/sqlite/
This module is created with a different mindset:
- Interface style for Lua ... not c like
- no return sqlite.OK/sqlite.DONE/... but nil, error message
- I don't want my library to error(), escpecially not, if the error is busy, locked, constraint, ...
(it's like pcall(stmt:step()) just in case - doesn't work in a for loop)
- Lua has boolean, sqlite not:
I want to INSERT true/false and SELECT that values
-> true will be 1 in sqlite
-> false will be NULL in sqlite
SELECT feature FROM table -> if feature then ... (instead of if feature == 1 then)
- NULL/nil is a valid result:
for col1, col2, ... in stmt:urows() do
...lsqlite will end, if there is a NULL value in col1
for num_of_cols, col1, col2, ... in query.values() do
...first arg is set, if there is a result
- Taking care to close iterators:
for table in query.irows() do
if table.value == found then
-- jumping out of the query will restore/close the query
break
end
end
- lsqlite has more features, this module has only query (statement) support
- lsqlite supports Lua5.x this is only for Lua5.4 - asuming int64
]]
local sqlite = require("sqlite")
print("== open() ==")
--[[
arg1: filename (emtpy is temp db, ":memory:" in memory db)
optional arg2: r[eadonly]|w[riteread]|f[ullmutex]|n[nomutex]|fr|fw|nr|nw
(default: read write and create)
return:
arg1: db
return on error: nil, message[, errno]
]]
local db = sqlite.open(":memory:")
print(db)
print("== db.query() / db.pquery() ==")
--[[
arg1: query
return:
arg1: stmt
return on error: nil, message[, errno]
creates a query (prepared statement)
pquery will be persistent and restored after each run
query will be closed
]]
local query = db.query("CREATE TABLE test(name, score)")
io.write(string.format("query: %s\n", query))
print("== query.run() / query.irun() ==")
--[[
optional arg1: table bind
return:
arg1: one result (maybe nil)
return on error: nil, message
Execute everything in one big loop
- perfect for non SELECT -> number of changes
- single row or single result (irun) -> PRAGMA
- On SELECT the return will be a table (rows) of tables (columns):
irun will be number indexed, run (column) name indexed
irun: {{c1, c2, ...}, {c1, c2, ...}, ...}
example: {{"john", 5}, {"jane", 3}, {"john", 4}, {"jane", 6}}
run: {{cn1 = cv1, cn2 = cv2, ...}, {cn1 = cv1, cn2 = cv2, ...}, ...}
example: {{name = "john", score = 5}, {name = "jane", score = 3},
{name = "john", score = 4}, {name = "jane", score = 6}}
if the SELECT returns a single row only, the result will be just that table (colums)
if the SELECT returns no rows, the result will be nil
if there is only one result in irun, it will just be the result (PRAGMA)
result, error_message = query.irun()
error_message -> error
type(result) == "table" -> one or more rows
type(result[1]) == "table" -> more rows
]]
io.write(string.format("irun (result): %s\n", query.irun()))
io.write(string.format("after run: %s\n", query))
query = db.query("PRAGMA busy_timeout")
io.write(string.format("%s -> ", query))
io.write(string.format("%d\n", query.irun()))
-- multi query is not supported
-- but sometimes we want some batch processing:
local multiquery = function(multi_query)
local result
for single_query in string.gmatch(multi_query, "([^;]+)[; \n]*") do
local query, error_message = db.query(single_query)
if error_message then
return nil, error_message
end
result, error_message = query.irun()
if error_message then
return nil, error
end
end
return true
end
print("> batch insert", multiquery([[
INSERT INTO test VALUES("john", 1);
INSERT INTO test VALUES("jane", 3);
INSERT INTO test VALUES("john", 4);
INSERT INTO test VALUES("jane", 6);
INSERT INTO test VALUES("john", NULL);
]]))
--[[ Why is multi query bad? ]]--
-- there is no way for a general error handling
-- it can be a security risk:
-- db.query(string.format("SELECT * FROM test WHERE name = '%s'", name)
-- if someone put in name = "john'; DELETE * FROM test;" ...
-- single query only executes the first one
-- it is further recommended to bind user input
-- test irun resultset
local resultset, error_message = db.query("SELECT * FROM test WHERE name = ?"):irun();
assert(resultset == nil)
assert(error_message)
resultset = db.query("SELECT * FROM test"):irun();
assert(type(resultset) == "table")
assert(#resultset == 5)
assert(type(resultset[1]) == "table")
resultset = db.query("SELECT name, score FROM test WHERE name = 'john' AND score = 1"):irun();
assert(type(resultset) == "table")
assert(not(type(resultset[1]) == "table"))
assert(type(resultset[1]) == "string")
resultset = db.query("SELECT name, score FROM test WHERE name = 'charles'"):irun();
assert(resultset == nil)
resultset = db.query("UPDATE test SET score = 5 WHERE name = 'john' AND score = 1"):irun();
assert(resultset == 1)
resultset = db.query("UPDATE test SET score = 5 WHERE name = 'charles'"):irun();
assert(resultset == 0)
print("== query.irows() ==")
--[[
optional arg1: table bind
return:
arg1: iterator -> table resultrow
return on error: nil, message
irows is like ipairs it returns an indexed table:
{"john", 5}
{"jane", 3}
{"john", 4}
{"jane", 6}
length will be the number of results, even if a table ends with nil
]]
query = db.query("SELECT * FROM test")
print(query)
for t in query.irows() do
io.write(string.format("%s -> results: %d -> %s, %s\n", query, #t, t[1], t[2]))
end
print(query)
print("== query.rows() ==")
--[[
optional arg1: table bind
return:
arg1: iterator -> table resultrow
return on error: nil, message
rows is like pairs it returns a key, value table:
{name="john", score=5}
{name="jane", score=3}
{name="john", score=4}
{name="jane", score=6}
]]
query = db.query("SELECT * FROM test")
for t in query.rows() do
io.write("{")
for k, v in pairs(t) do
io.write(string.format("%s: %s,", k, v))
end
io.write("}\n")
end
print("== query.values() ==")
--[[
optional arg1: table bind
return:
arg1: iterator -> number columns, column1, column2, ...
return on error: nil, message
]]
query = db.query("SELECT name, score FROM test")
print(query)
for n, name, score in query.values() do
if score then
io.write(string.format("%d: %s -> %d\n", n, name, score))
end
end
print("== query.rlist() ==")
--[[
optional arg1: table bind
return:
arg1: table with list of values of the first column
return on error: nil, message
]]
local pquery = db.pquery("SELECT DISTINCT name FROM test")
print(pquery)
for _, v in ipairs(pquery.rlist()) do
print(v)
end
print("== query.reset() / query.restore()==")
--[[
no args
return:
arg1: true
return on error: nil, message
reset will reset the query execution and remove all bind parameters
restore will reset the query execution and restore/keep all bind parameters
]]
pquery.restore()
print("== query.rindex() ==")
--[[
optional arg1: table bind
return:
arg1: table with key(=true) index of the first column
return on error: nil, message
]]
local names = pquery.rindex()
print("names =", pquery)
print("manes.john", names.john)
print("names.charles", names.charles)
print("== query.rtable() ==")
--[[
optional arg1: table bind
return:
arg1: table with key=value of the two columns
return on error: nil, message
]]
local query = db.query("SELECT name, max(score) FROM test GROUP BY name;")
print(query)
for k, v in pairs(query.rtable()) do
print("max", k, v)
end
--[[ On query iterators ]]--[[
irows(), rows() and values() will return an iterator function and a "closing value"
(see https://www.lua.org/manual/5.4/manual.html#3.3.5 - end of chapter (The generic for loop))
So, even if the loop is not completed, the query will be restored or closed.
If a loop is completetd and there is no more result, it will be restored or closed too.
run()/irun() and rlist()/rindex()/rtable() will also restore or close the query.
restore will be used, if the query was opened as pquery (persistent):
-> db.pquery("SELECT * FROM test")
The query can be used again.
On failure the query gets a restore
]]
print("== query.bind() ==")
--[[
arg1: table bind
return:
arg1: true
return on error: nil, message
Bind is a query, which is only needed, if query.next() is used
All other functions (run/irun, irows/rows, values and rlist/rindex/rtable) support a bind table as the first argument
If there are no bind parameters, it is obsolete, anyway
If there are parameters a bind table must be used.
bind can be called multiple times to change Parameters
All unset Parameters will be replaced by NULL
The table supports numeric and named indexes.
?, ?NNN -> use the table index
:name, @name, $name -> work with there prefix {[":name"] = "value"} or simply {name = "value"}
(More details on that topic from sqlite3: https://sqlite.org/lang_expr.html#varparam )
]]
query = db.query("SELECT * FROM test WHERE name = ? or name = :name")
print(query)
print("try to run:", query.irun())
print("bind {\"john\", name = \"jane\"}:", query.bind({"john", name = "jane"}))
print(query)
for t in query.rows() do
print(t.name, t.score)
end
print(query)
print("== query.next() ==")
--[[
optional arg1: string result type i[row]|r[ow] -> default values
return:
arg1: true (done) or table resultrow (irow|row] or number columns, column1, column2, ...
return on error: nil, message
The next function is the iterator, that rows, irows and values return
If next is used, restore/close is only called after completion.
]]
local names, scores = {}, {}
local doresult = function(count, ...)
if not(count) then
-- error
return count, ...
end
if count == true then
return nil
end
if not(count == 2) then
return nil, "Invalid column count"
end
names[select(1, ...)] = true
scores[select(2, ...)] = true
return true
end
query = db.query("SELECT name, score FROM test WHERE name IS NOT NULL AND score IS NOT NULL")
rows = 1
while doresult(query.next()) do
rows = rows + 1
end
io.write(string.format("%d results:\n", rows))
for name in pairs(names) do
io.write(string.format("name: %s\n", name))
end
for score in pairs(scores) do
io.write(string.format("score: %s\n", score))
end
print("== query.status. ==")
--[[
for queries there are fields for error information:
query.status[]
- error -> number error or nil
- error_message -> string error or nil
-- on query execution
- changes -> return number of changes
- busy -> if sqlite busy error, return number error or nil
- locked -> if sqlite locked error, return number error or nil
-- on bind
- constraint -> if sqlite constraint error, return number error or nil
- range -> if sqlite range error, return number error or nil
]]
local query_status_fields = {"error", "error_message", "changes", "busy", "locked", "constraint", "range"}
for _, status in ipairs(query_status_fields) do
io.write(string.format("pquery.status.%s = %s\n", status, pquery.status[status]))
end
print()
-- https://sqlite.org/c3ref/c_stmtstatus_counter.html
-- return -1, if query is closed
local query_status_fields = {"memory", "fullscan", "run", "sort"}
for _, status in ipairs(query_status_fields) do
io.write(string.format("pquery.status.%s = %d\n", status, pquery.status[status]))
end
-- https://sqlite.org/c3ref/c_dbstatus_options.html
print("== db.status. ==")
-- changes -> return number of changes
-- return -1, if db is closed
local db_status_fields = {"changes", "memory", "memory_schema", "memory_stmt", "lookaside", "lookaside_max", "lookaside_hit", "lookaside_miss_size", "lookaside_miss_full", "cache_hit", "cache_miss"}
for _, status in ipairs(db_status_fields) do
io.write(string.format("db.status.%s = %d\n", status, db.status[status]))
end
-- https://sqlite.org/c3ref/c_status_malloc_count.html
print("== sqlite.status. ==")
-- memory_max can be reset: sqlite.status.memory_max = true
local sqlite_status_fields = {"memory", "memory_max", "malloc", "malloc_count", "pagecache", "pagecache_max"}
for _, status in ipairs(sqlite_status_fields) do
io.write(string.format("sqlite.status.%s = %d\n", status, sqlite.status[status]))
end
print("== query.close() ==")
--[[
no args
return:
arg1: true
return on error: nil, message
]]
pquery.close()
print("== db.close() ==")
--[[
no args
return:
arg1: true
return on error: nil, message[, errno]
]]
print(db.close())
print(db)