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 | 192 |
1 files changed, 125 insertions, 67 deletions
diff --git a/tex/context/base/mkiv/util-sql-imp-sqlite.lua b/tex/context/base/mkiv/util-sql-imp-sqlite.lua index 390e3aa29..bb789f648 100644 --- a/tex/context/base/mkiv/util-sql-imp-sqlite.lua +++ b/tex/context/base/mkiv/util-sql-imp-sqlite.lua @@ -6,18 +6,21 @@ if not modules then modules = { } end modules ['util-sql-imp-sqlite'] = { license = "see context related readme files" } --- todo: make a converter +local next = next -require("util-sql") +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 -local rawset, setmetatable = rawset, setmetatable -local P, S, V, C, Cs, Ct, Cc, Cg, Cf, patterns, lpegmatch = lpeg.P, lpeg.S, lpeg.V, lpeg.C, lpeg.Cs, lpeg.Ct, lpeg.Cc, lpeg.Cg, lpeg.Cf, lpeg.patterns, lpeg.match +-- 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 sql = utilities.sql local helpers = sql.helpers local methods = sql.methods local validspecification = helpers.validspecification @@ -28,32 +31,21 @@ local serialize = sql.serialize local deserialize = sql.deserialize local getserver = sql.getserver -local sqlite = require("swiglib.sqlite.core") -local swighelpers = require("swiglib.helpers.core") - - --- we can have a cache - --- local preamble = t_preamble[getserver()] or t_preamble.mysql --- if preamble then --- preamble = replacetemplate(preamble,specification.variables,'sql') --- query = preamble .. "\n" .. query --- end - --- print(sqlite.sqlite3_errmsg(dbh)) +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 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 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 = { } +local cache = { } setmetatable(cache, { __gc = function(t) @@ -67,6 +59,21 @@ setmetatable(cache, { 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") @@ -84,62 +91,88 @@ local function execute(specification) report_state("no database specified") return end - base = file.addsuffix(base,"db") - local result = { } - local keys = { } - local id = specification.id - local db = nil - local dbh = nil - local okay = false + 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(base,db) - dbh = get_db(db,0) + 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 = base, + name = filename, db = db, dbh = dbh, } end end else - db = new_db(1) - okay = open_db(base,db) - dbh = get_db(db,0) + 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 keysdone = false - local nofresults = 0 - local callback = function(data,nofcolumns,values,fields) - nofresults = nofresults + 1 - local r = { } - for i=0,nofcolumns-1 do - local field = get_list_item(fields,i) - local value = get_list_item(values,i) - r[field] = value - if not keysdone then - keys[i+1] = field + 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 - keysdone = true - result[nofresults] = r - return is_okay end local okay = execute_query(dbh,query,callback,nil,nil) if okay ~= is_okay then - report_state(error_message(dbh)) + report_state("error: %s",error_message(dbh)) + -- elseif converter then + -- result = converter.sqlite(result) end - end if not id then close_db(dbh) @@ -158,20 +191,45 @@ local booleanstring = string.booleanstring %s -return function(data) - local target = %s -- data or { } - for i=1,#data do - local cells = data[i] - target[%s] = { - %s - } - end - return target +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, |