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 |
