diff options
Diffstat (limited to 'tex/context/base/mkiv/util-sql-imp-sqlite.lua')
| -rw-r--r-- | tex/context/base/mkiv/util-sql-imp-sqlite.lua | 237 |
1 files changed, 237 insertions, 0 deletions
diff --git a/tex/context/base/mkiv/util-sql-imp-sqlite.lua b/tex/context/base/mkiv/util-sql-imp-sqlite.lua new file mode 100644 index 000000000..1a960c1c3 --- /dev/null +++ b/tex/context/base/mkiv/util-sql-imp-sqlite.lua @@ -0,0 +1,237 @@ +if not modules then modules = { } end modules ['util-sql-imp-sqlite'] = { + version = 1.001, + comment = "companion to util-sql.lua", + author = "Hans Hagen, PRAGMA-ADE, Hasselt NL", + copyright = "PRAGMA ADE / ConTeXt Development Team", + license = "see context related readme files" +} + +local next = next + +local sql = require("util-sql") +----- sql = utilities.sql +local sqlite = require("swiglib.sqlite.core") +local swighelpers = require("swiglib.helpers.core") + +-- sql.sqlite = sqlite -- maybe in the module itself + +-- inspect(table.sortedkeys(sqlite)) + +local trace_sql = false trackers.register("sql.trace", function(v) trace_sql = v end) +local trace_queries = false trackers.register("sql.queries",function(v) trace_queries = v end) +local report_state = logs.reporter("sql","sqlite") + +local helpers = sql.helpers +local methods = sql.methods +local validspecification = helpers.validspecification +local preparetemplate = helpers.preparetemplate +local splitdata = helpers.splitdata +local serialize = sql.serialize +local deserialize = sql.deserialize +local getserver = sql.getserver + +local setmetatable = setmetatable +local formatters = string.formatters + +local get_list_item = sqlite.char_p_array_getitem +local is_okay = sqlite.SQLITE_OK +local execute_query = sqlite.sqlite3_exec_lua_callback +local error_message = sqlite.sqlite3_errmsg + +local new_db = sqlite.new_sqlite3_p_array +local open_db = sqlite.sqlite3_open +local get_db = sqlite.sqlite3_p_array_getitem +local close_db = sqlite.sqlite3_close +local dispose_db = sqlite.delete_sqlite3_p_array + +local cache = { } + +setmetatable(cache, { + __gc = function(t) + for k, v in next, t do + if trace_sql then + report_state("closing session %a",k) + end + close_db(v.dbh) + dispose_db(v.db) + end + end +}) + +-- synchronous journal_mode locking_mode 1000 logger inserts +-- +-- normal normal normal 6.8 +-- off off normal 0.1 +-- normal off normal 2.1 +-- normal persist normal 5.8 +-- normal truncate normal 4.2 +-- normal truncate exclusive 4.1 + +local f_preamble = formatters[ [[ +ATTACH `%s` AS `%s` ; +PRAGMA `%s`.synchronous = normal ; +PRAGMA journal_mode = truncate ; +]] ] + +local function execute(specification) + if trace_sql then + report_state("executing sqlite") + end + if not validspecification(specification) then + report_state("error in specification") + end + local query = preparetemplate(specification) + if not query then + report_state("error in preparation") + return + end + local base = specification.database -- or specification.presets and specification.presets.database + if not base then + report_state("no database specified") + return + end + local filename = file.addsuffix(base,"db") + local result = { } + local keys = { } + local id = specification.id + local db = nil + local dbh = nil + local okay = false + local preamble = nil + if id then + local session = cache[id] + if session then + dbh = session.dbh + okay = is_okay + else + db = new_db(1) + okay = open_db(filename,db) + dbh = get_db(db,0) + preamble = f_preamble(filename,base,base) + if okay ~= is_okay then + report_state("no session database specified") + else + cache[id] = { + name = filename, + db = db, + dbh = dbh, + } + end + end + else + db = new_db(1) + okay = open_db(filename,db) + dbh = get_db(db,0) + preamble = f_preamble(filename,base,base) + end + if okay ~= is_okay then + report_state("no database opened") + else + local converter = specification.converter + local keysdone = false + local nofrows = 0 + local callback = nil + if preamble then + query = preamble .. query -- only needed in open + end + if converter then + converter = converter.sqlite + callback = function(data,nofcolumns,values,fields) + local column = { } + for i=0,nofcolumns-1 do + column[i+1] = get_list_item(values,i) + end + nofrows = nofrows + 1 + result[nofrows] = converter(column) + return is_okay + end + -- + -- callback = converter.sqlite + else + callback = function(data,nofcolumns,values,fields) + local column = { } + for i=0,nofcolumns-1 do + local field + if keysdone then + field = keys[i+1] + else + field = get_list_item(fields,i) + keys[i+1] = field + end + column[field] = get_list_item(values,i) + end + nofrows = nofrows + 1 + keysdone = true + result[nofrows] = column + return is_okay + end + end + local okay = execute_query(dbh,query,callback,nil,nil) + if okay ~= is_okay then + report_state("error: %s",error_message(dbh)) + -- elseif converter then + -- result = converter.sqlite(result) + end + end + if not id then + close_db(dbh) + dispose_db(db) + end + return result, keys +end + +local wraptemplate = [[ +local converters = utilities.sql.converters +local deserialize = utilities.sql.deserialize + +local tostring = tostring +local tonumber = tonumber +local booleanstring = string.booleanstring + +%s + +return function(cells) + -- %s (not needed) + -- %s (not needed) + return { + %s + } +end +]] + +local celltemplate = "cells[%s]" + +-- todo: how to deal with result ... pass via temp global .. bah .. or +-- also pass the execute here ... not now +-- +-- local wraptemplate = [[ +-- local converters = utilities.sql.converters +-- local deserialize = utilities.sql.deserialize +-- +-- local tostring = tostring +-- local tonumber = tonumber +-- local booleanstring = string.booleanstring +-- +-- local get_list_item = utilities.sql.sqlite.char_p_array_getitem +-- local is_okay = utilities.sql.sqlite.SQLITE_OK +-- +-- %s +-- +-- return function(data,nofcolumns,values,fields) +-- -- no %s (data) needed +-- -- no %s (i) needed +-- local cells = { } +-- for i=0,nofcolumns-1 do +-- cells[i+1] = get_list_item(values,i) +-- end +-- result[#result+1] = { %s } +-- return is_okay +-- end +-- ]] + +methods.sqlite = { + execute = execute, + usesfiles = false, + wraptemplate = wraptemplate, + celltemplate = celltemplate, +} |