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
10
11
12
13
14
15local tonumber = tonumber
16local format = string.format
17local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime
18local random = math.random
19
20
21
22
23
24
25
26
27
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 |