--[[ 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)

