Source Browser ?

1if not modules then modules = { } end modules ['util-sql-sessions'] = {
2    version   = 1.001,
3    comment   = "companion to lmx-*",
4    author    = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
5    copyright = "PRAGMA ADE / ConTeXt Development Team",
6    license   = "see context related readme files"
7}
8
9-- This is experimental code and currently part of the base installation simply
10-- because it's easier to dirtribute this way. Eventually it will be documented
11-- and the related scripts will show up as well.
12
13-- maybe store threshold in session (in seconds)
14
15local tonumber = tonumber
16local format = string.format
17local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime
18local random = math.random
19
20-- In older frameworks we kept a session table in memory. This time we
21-- follow a route where we store session data in a sql table. Each session
22-- has a token (similar to what we do on q2p and pod services), a data
23-- blob which is just a serialized lua table (we could consider a dump instead)
24-- and two times: the creation and last accessed time. The first one is handy
25-- for statistics and the second one for cleanup. Both are just numbers so that
26-- we don't have to waste code on conversions. Anyhow, we provide variants so that
27-- we can always choose what is best.
28
29local sql         = utilities.sql
30local sessions    = { }
31sql.sessions      = sessions
32
33local trace_sql   = false  trackers.register("sql.sessions.trace", function(v) trace_sql = v end)
34local report      = logs.reporter("sql","sessions")
35
36sessions.newtoken = sql.tokens.new
37
38local function checkeddb(presets,datatable)
39    return sql.usedatabase(presets,datatable or presets.datatable or "sessions")
40end
41
42sessions.usedb = checkeddb
43
44local template =[[
45    CREATE TABLE IF NOT EXISTS %basename% (
46        `token`    varchar(50)       NOT NULL,
47        `data`     longtext          NOT NULL,
48        `created`  int(11)           NOT NULL,
49        `accessed` int(11)           NOT NULL,
50        UNIQUE KEY `token_unique_key` (`token`)
51    ) DEFAULT CHARSET = utf8 ;
52]]
53
54local sqlite_template =[[
55    CREATE TABLE IF NOT EXISTS %basename% (
56        `token`    TEXT NOT NULL,
57        `data`     TEXT NOT NULL,
58        `created`  INTEGER DEFAULT '0',
59        `accessed` INTEGER DEFAULT '0'
60    ) ;
61]]
62
63function sessions.createdb(presets,datatable)
64
65    local db = checkeddb(presets,datatable)
66
67    db.execute {
68        template  = db.usedmethod == "sqlite" and sqlite_template or template,
69        variables = {
70            basename = db.basename,
71        },
72    }
73
74    report("datatable %a created in %a",db.name,db.base)
75
76    return db
77
78end
79
80local template =[[
81    DROP TABLE IF EXISTS %basename% ;
82]]
83
84function sessions.deletedb(presets,datatable)
85
86    local db = checkeddb(presets,datatable)
87
88    db.execute {
89        template  = template,
90        variables = {
91            basename = db.basename,
92        },
93    }
94
95    report("datatable %a removed in %a",db.name,db.base)
96
97end
98
99local template =[[
100    INSERT INTO %basename% (
101        `token`,
102        `created`,
103        `accessed`,
104        `data`
105    ) VALUES (
106        '%token%',
107        %time%,
108        %time%,
109        '%[data]%'
110    ) ;
111]]
112
113function sessions.create(db,data)
114
115    local token  = sessions.newtoken()
116    local time   = ostime()
117
118    db.execute {
119        template  = template,
120        variables = {
121            basename = db.basename,
122            token    = token,
123            time     = time,
124            data     = db.serialize(data or { },"return")
125        },
126    }
127
128    if trace_sql then
129        report("created: %s at %s",token,osfulltime(time))
130    end
131
132    return {
133        token    = token,
134        created  = time,
135        accessed = time,
136        data     = data,
137    }
138end
139
140local template =[[
141    UPDATE
142        %basename%
143    SET
144        `data` = '%[data]%',
145        `accessed` = %time%
146    WHERE
147        `token` = '%token%' ;
148]]
149
150function sessions.save(db,session)
151
152    local time  = ostime()
153    local data  = db.serialize(session.data or { },"return")
154    local token = session.token
155
156    session.accessed = time
157
158    db.execute {
159        template  = template,
160        variables = {
161            basename = db.basename,
162            token    = token,
163            time     = ostime(),
164            data     = data,
165        },
166    }
167
168    if trace_sql then
169        report("saved: %s at %s",token,osfulltime(time))
170    end
171
172    return session
173end
174
175local template = [[
176    UPDATE
177        %basename%
178    SET
179        `accessed` = %time%
180    WHERE
181        `token` = '%token%' ;
182]]
183
184function sessions.touch(db,token)
185
186    db.execute {
187        template  = template,
188        variables = {
189            basename = db.basename,
190            token    = token,
191            time     = ostime(),
192        },
193    }
194
195end
196
197local template = [[
198    UPDATE
199        %basename%
200    SET
201        `accessed` = %time%
202    WHERE
203        `token` = '%token%' ;
204    SELECT
205        *
206    FROM
207        %basename%
208    WHERE
209        `token` = '%token%' ;
210]]
211
212function sessions.restore(db,token)
213
214    local records, keys = db.execute {
215        template  = template,
216        variables = {
217            basename = db.basename,
218            token    = token,
219            time     = ostime(),
220        },
221    }
222
223    local record = records and records[1]
224
225    if record then
226        if trace_sql then
227            report("restored: %s",token)
228        end
229        record.data = db.deserialize(record.data or "")
230        return record, keys
231    elseif trace_sql then
232        report("unknown: %s",token)
233    end
234
235end
236
237local template =[[
238    DELETE FROM
239        %basename%
240    WHERE
241        `token` = '%token%' ;
242]]
243
244function sessions.remove(db,token)
245
246    db.execute {
247        template  = template,
248        variables = {
249            basename = db.basename,
250            token    = token,
251        },
252    }
253
254    if trace_sql then
255        report("removed: %s",token)
256    end
257
258end
259
260local template_collect_yes =[[
261    SELECT
262        *
263    FROM
264        %basename%
265    ORDER BY
266        `created` ;
267]]
268
269local template_collect_nop =[[
270    SELECT
271        `accessed`,
272        `created`,
273        `accessed`,
274        `token`
275    FROM
276        %basename%
277    ORDER BY
278        `created` ;
279]]
280
281function sessions.collect(db,nodata)
282
283    local records, keys = db.execute {
284        template  = nodata and template_collect_nop or template_collect_yes,
285        variables = {
286            basename = db.basename,
287        },
288    }
289
290    if not nodata then
291        db.unpackdata(records)
292    end
293
294    if trace_sql then
295        report("collected: %s sessions",#records)
296    end
297
298    return records, keys
299
300end
301
302local template_cleanup_yes =[[
303    SELECT
304        *
305    FROM
306        %basename%
307    WHERE
308        `accessed` < %time%
309    ORDER BY
310        `created` ;
311    DELETE FROM
312        %basename%
313    WHERE
314        `accessed` < %time% ;
315]]
316
317local template_cleanup_nop =[[
318    SELECT
319        `accessed`,
320        `created`,
321        `accessed`,
322        `token`
323    FROM
324        %basename%
325    WHERE
326        `accessed` < %time%
327    ORDER BY
328        `created` ;
329    DELETE FROM
330        %basename%
331    WHERE
332        `accessed` < %time% ;
333]]
334
335function sessions.cleanupdb(db,delta,nodata)
336
337    local time = ostime()
338
339    local records, keys = db.execute {
340        template  = nodata and template_cleanup_nop or template_cleanup_yes,
341        variables = {
342            basename = db.basename,
343            time     = time - delta
344        },
345    }
346
347    if not nodata then
348        db.unpackdata(records)
349    end
350
351    if trace_sql then
352        report("cleaned: %s seconds before %s",delta,osfulltime(time))
353    end
354
355    return records, keys
356
357end
358