if not modules then modules = { } end modules ['util-sql-sessions'] = {
version = 1.001,
comment = "companion to lmx-*",
author = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
copyright = "PRAGMA ADE / ConTeXt Development Team",
license = "see context related readme files"
}
-- This is experimental code and currently part of the base installation simply
-- because it's easier to dirtribute this way. Eventually it will be documented
-- and the related scripts will show up as well.
-- maybe store threshold in session (in seconds)
local tonumber = tonumber
local format = string.format
local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime
local random = math.random
-- In older frameworks we kept a session table in memory. This time we
-- follow a route where we store session data in a sql table. Each session
-- has a token (similar to what we do on q2p and pod services), a data
-- blob which is just a serialized lua table (we could consider a dump instead)
-- and two times: the creation and last accessed time. The first one is handy
-- for statistics and the second one for cleanup. Both are just numbers so that
-- we don't have to waste code on conversions. Anyhow, we provide variants so that
-- we can always choose what is best.
local sql = utilities.sql
local sessions = { }
sql.sessions = sessions
local trace_sql = false trackers.register("sql.sessions.trace", function(v) trace_sql = v end)
local report = logs.reporter("sql","sessions")
sessions.newtoken = sql.tokens.new
local function checkeddb(presets,datatable)
return sql.usedatabase(presets,datatable or presets.datatable or "sessions")
end
sessions.usedb = checkeddb
local template =[[
CREATE TABLE IF NOT EXISTS %basename% (
`token` varchar(50) NOT NULL,
`data` longtext NOT NULL,
`created` int(11) NOT NULL,
`accessed` int(11) NOT NULL,
UNIQUE KEY `token_unique_key` (`token`)
) DEFAULT CHARSET = utf8 ;
]]
local sqlite_template =[[
CREATE TABLE IF NOT EXISTS %basename% (
`token` TEXT NOT NULL,
`data` TEXT NOT NULL,
`created` INTEGER DEFAULT '0',
`accessed` INTEGER DEFAULT '0'
) ;
]]
function sessions.createdb(presets,datatable)
local db = checkeddb(presets,datatable)
db.execute {
template = db.usedmethod == "sqlite" and sqlite_template or template,
variables = {
basename = db.basename,
},
}
report("datatable %a created in %a",db.name,db.base)
return db
end
local template =[[
DROP TABLE IF EXISTS %basename% ;
]]
function sessions.deletedb(presets,datatable)
local db = checkeddb(presets,datatable)
db.execute {
template = template,
variables = {
basename = db.basename,
},
}
report("datatable %a removed in %a",db.name,db.base)
end
local template =[[
INSERT INTO %basename% (
`token`,
`created`,
`accessed`,
`data`
) VALUES (
'%token%',
%time%,
%time%,
'%[data]%'
) ;
]]
function sessions.create(db,data)
local token = sessions.newtoken()
local time = ostime()
db.execute {
template = template,
variables = {
basename = db.basename,
token = token,
time = time,
data = db.serialize(data or { },"return")
},
}
if trace_sql then
report("created: %s at %s",token,osfulltime(time))
end
return {
token = token,
created = time,
accessed = time,
data = data,
}
end
local template =[[
UPDATE
%basename%
SET
`data` = '%[data]%',
`accessed` = %time%
WHERE
`token` = '%token%' ;
]]
function sessions.save(db,session)
local time = ostime()
local data = db.serialize(session.data or { },"return")
local token = session.token
session.accessed = time
db.execute {
template = template,
variables = {
basename = db.basename,
token = token,
time = ostime(),
data = data,
},
}
if trace_sql then
report("saved: %s at %s",token,osfulltime(time))
end
return session
end
local template = [[
UPDATE
%basename%
SET
`accessed` = %time%
WHERE
`token` = '%token%' ;
]]
function sessions.touch(db,token)
db.execute {
template = template,
variables = {
basename = db.basename,
token = token,
time = ostime(),
},
}
end
local template = [[
UPDATE
%basename%
SET
`accessed` = %time%
WHERE
`token` = '%token%' ;
SELECT
*
FROM
%basename%
WHERE
`token` = '%token%' ;
]]
function sessions.restore(db,token)
local records, keys = db.execute {
template = template,
variables = {
basename = db.basename,
token = token,
time = ostime(),
},
}
local record = records and records[1]
if record then
if trace_sql then
report("restored: %s",token)
end
record.data = db.deserialize(record.data or "")
return record, keys
elseif trace_sql then
report("unknown: %s",token)
end
end
local template =[[
DELETE FROM
%basename%
WHERE
`token` = '%token%' ;
]]
function sessions.remove(db,token)
db.execute {
template = template,
variables = {
basename = db.basename,
token = token,
},
}
if trace_sql then
report("removed: %s",token)
end
end
local template_collect_yes =[[
SELECT
*
FROM
%basename%
ORDER BY
`created` ;
]]
local template_collect_nop =[[
SELECT
`accessed`,
`created`,
`accessed`,
`token`
FROM
%basename%
ORDER BY
`created` ;
]]
function sessions.collect(db,nodata)
local records, keys = db.execute {
template = nodata and template_collect_nop or template_collect_yes,
variables = {
basename = db.basename,
},
}
if not nodata then
db.unpackdata(records)
end
if trace_sql then
report("collected: %s sessions",#records)
end
return records, keys
end
local template_cleanup_yes =[[
SELECT
*
FROM
%basename%
WHERE
`accessed` < %time%
ORDER BY
`created` ;
DELETE FROM
%basename%
WHERE
`accessed` < %time% ;
]]
local template_cleanup_nop =[[
SELECT
`accessed`,
`created`,
`accessed`,
`token`
FROM
%basename%
WHERE
`accessed` < %time%
ORDER BY
`created` ;
DELETE FROM
%basename%
WHERE
`accessed` < %time% ;
]]
function sessions.cleanupdb(db,delta,nodata)
local time = ostime()
local records, keys = db.execute {
template = nodata and template_cleanup_nop or template_cleanup_yes,
variables = {
basename = db.basename,
time = time - delta
},
}
if not nodata then
db.unpackdata(records)
end
if trace_sql then
report("cleaned: %s seconds before %s",delta,osfulltime(time))
end
return records, keys
end
