1 files changed, 349 insertions, 0 deletions
diff --git a/tex/context/base/util-sql-sessions.lua b/tex/context/base/util-sql-sessions.lua
new file mode 100644
index 000000000..40556dd5e
--- /dev/null
+++ b/tex/context/base/util-sql-sessions.lua
@@ -0,0 +1,349 @@
+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 ;
+]]
+
+function sessions.createdb(presets,datatable)
+
+    local db = checkeddb(presets,datatable)
+
+    db.execute {
+        template  = template,
+        variables = {
+            basename = db.basename,
+        },
+    }
+
+    report("datatable %q created in %q",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 %q removed in %q",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