util-sql-sessions.lua /size: 7608 b    last modification: 2020-07-01 14:35
1
if
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 15
local
tonumber
=
tonumber
16
local
format
=
string
.
format
17
local
ostime
,
uuid
,
osfulltime
=
os
.
time
,
os
.
uuid
,
os
.
fulltime
18
local
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 29
local
sql
=
utilities
.
sql
30
local
sessions
=
{
}
31
sql
.
sessions
=
sessions
32 33
local
trace_sql
=
false
trackers
.
register
(
"
sql.sessions.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
34
local
report
=
logs
.
reporter
(
"
sql
"
,
"
sessions
"
)
35 36
sessions
.
newtoken
=
sql
.
tokens
.
new
37 38
local
function
checkeddb
(
presets
,
datatable
)
39
return
sql
.
usedatabase
(
presets
,
datatable
or
presets
.
datatable
or
"
sessions
"
)
40
end
41 42
sessions
.
usedb
=
checkeddb
43 44
local
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 54
local
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 63
function
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 78
end
79 80
local
template
=
[[
81 DROP TABLE IF EXISTS %basename% ; 82
]]
83 84
function
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 97
end
98 99
local
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 113
function
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
}
138
end
139 140
local
template
=
[[
141 UPDATE 142 %basename% 143 SET 144 `data` = '%[data]%', 145 `accessed` = %time% 146 WHERE 147 `token` = '%token%' ; 148
]]
149 150
function
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
173
end
174 175
local
template
=
[[
176 UPDATE 177 %basename% 178 SET 179 `accessed` = %time% 180 WHERE 181 `token` = '%token%' ; 182
]]
183 184
function
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 195
end
196 197
local
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 212
function
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 235
end
236 237
local
template
=
[[
238 DELETE FROM 239 %basename% 240 WHERE 241 `token` = '%token%' ; 242
]]
243 244
function
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 258
end
259 260
local
template_collect_yes
=
[[
261 SELECT 262 * 263 FROM 264 %basename% 265 ORDER BY 266 `created` ; 267
]]
268 269
local
template_collect_nop
=
[[
270 SELECT 271 `accessed`, 272 `created`, 273 `accessed`, 274 `token` 275 FROM 276 %basename% 277 ORDER BY 278 `created` ; 279
]]
280 281
function
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 300
end
301 302
local
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 317
local
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 335
function
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 357
end
358