util-sql-loggers.lua /size: 7825 b    last modification: 2020-07-01 14:35
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql-loggers
'
]
=
{
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
local
tonumber
=
tonumber
14
local
format
=
string
.
format
15
local
concat
=
table
.
concat
16
local
ostime
,
uuid
,
osfulltime
=
os
.
time
,
os
.
uuid
,
os
.
fulltime
17
local
random
=
math
.
random
18 19
local
sql
=
utilities
.
sql
20
local
loggers
=
{
}
21
sql
.
loggers
=
loggers
22 23
local
trace_sql
=
false
trackers
.
register
(
"
sql.loggers.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
24
local
report
=
logs
.
reporter
(
"
sql
"
,
"
loggers
"
)
25 26
loggers
.
newtoken
=
sql
.
tokens
.
new
27
local
makeconverter
=
sql
.
makeconverter
28 29
local
function
checkeddb
(
presets
,
datatable
)
30
return
sql
.
usedatabase
(
presets
,
datatable
or
presets
.
datatable
or
"
loggers
"
)
31
end
32 33
loggers
.
usedb
=
checkeddb
34 35
local
totype
=
{
36
[
"
error
"
]
=
1
,
[
1
]
=
1
,
[
"
1
"
]
=
1
,
37
[
"
warning
"
]
=
2
,
[
2
]
=
2
,
[
"
2
"
]
=
2
,
38
[
"
debug
"
]
=
3
,
[
3
]
=
3
,
[
"
3
"
]
=
3
,
39
[
"
info
"
]
=
4
,
[
4
]
=
4
,
[
"
4
"
]
=
4
,
40
}
41 42
local
fromtype
=
{
43
[
"
error
"
]
=
"
error
"
,
[
1
]
=
"
error
"
,
[
"
1
"
]
=
"
error
"
,
44
[
"
warning
"
]
=
"
warning
"
,
[
2
]
=
"
warning
"
,
[
"
2
"
]
=
"
warning
"
,
45
[
"
debug
"
]
=
"
debug
"
,
[
3
]
=
"
debug
"
,
[
"
3
"
]
=
"
debug
"
,
46
[
"
info
"
]
=
"
info
"
,
[
4
]
=
"
info
"
,
[
"
4
"
]
=
"
info
"
,
47
}
48 49
table
.
setmetatableindex
(
totype
,
function
(
)
return
4
end
)
50
table
.
setmetatableindex
(
fromtype
,
function
(
)
return
"
info
"
end
)
51 52
loggers
.
totype
=
totype
53
loggers
.
fromtype
=
fromtype
54 55
local
template
=
[[
56CREATE TABLE IF NOT EXISTS %basename% ( 57 `id` int(11) NOT NULL AUTO_INCREMENT, 58 `time` int(11) NOT NULL, 59 `type` int(11) NOT NULL, 60 `action` varchar(15) NOT NULL, 61 `data` longtext, 62 PRIMARY KEY (`id`), 63 UNIQUE KEY `id_unique_key` (`id`) 64) DEFAULT CHARSET = utf8 ; 65
]]
66 67
local
sqlite_template
=
[[
68 CREATE TABLE IF NOT EXISTS %basename% ( 69 `id` INTEGER PRIMARY KEY AUTOINCREMENT, 70 `time` INTEGER NOT NULL, 71 `type` INTEGER NOT NULL, 72 `action` TEXT NOT NULL, 73 `data` TEXT 74 ) ; 75
]]
76 77
function
loggers
.
createdb
(
presets
,
datatable
)
78 79
local
db
=
checkeddb
(
presets
,
datatable
)
80 81
db
.
execute
{
82
template
=
db
.
usedmethod
=
=
"
sqlite
"
and
sqlite_template
or
template
,
83
variables
=
{
84
basename
=
db
.
basename
,
85
}
,
86
}
87 88
report
(
"
datatable %a created in %a
"
,
db
.
name
,
db
.
base
)
89 90
return
db
91 92
end
93 94
local
template
=
[[
95 DROP TABLE IF EXISTS %basename% ; 96
]]
97 98
function
loggers
.
deletedb
(
presets
,
datatable
)
99 100
local
db
=
checkeddb
(
presets
,
datatable
)
101 102
db
.
execute
{
103
template
=
template
,
104
variables
=
{
105
basename
=
db
.
basename
,
106
}
,
107
}
108 109
report
(
"
datatable %a removed in %a
"
,
db
.
name
,
db
.
base
)
110 111
end
112 113
local
template
=
[[
114 INSERT INTO %basename% ( 115 `time`, 116 `type`, 117 `action`, 118 `data` 119 ) VALUES ( 120 %time%, 121 %type%, 122 '%action%', 123 '%[data]%' 124 ) ; 125
]]
126 127
-- beware, when we either pass a dat afield explicitly or we're using
128
-- a flat table and then nill type and action in the data (which
129
-- saves a table)
130 131
function
loggers
.
save
(
db
,
data
)
132 133
if
data
then
134 135
local
time
=
ostime
(
)
136
local
kind
=
totype
[
data
.
type
]
137
local
action
=
data
.
action
or
"
unknown
"
138 139
local
extra
=
data
.
data
140 141
if
extra
then
142
-- we have a dedicated data table
143
data
=
extra
144
else
145
-- we have a flat table
146
data
.
type
=
nil
147
data
.
action
=
nil
148
end
149 150
db
.
execute
{
151
template
=
template
,
152
variables
=
{
153
basename
=
db
.
basename
,
154
time
=
ostime
(
)
,
155
type
=
kind
,
156
action
=
action
,
157
data
=
data
and
db
.
serialize
(
data
,
"
return
"
)
or
"
"
,
158
}
,
159
}
160 161
end
162 163
end
164 165
local
template
=
[[
166 DELETE FROM %basename% %WHERE% ; 167
]]
168 169
function
loggers
.
cleanup
(
db
,
specification
)
170 171
specification
=
specification
or
{
}
172 173
local
today
=
os
.
date
(
"
*t
"
)
174
local
before
=
specification
.
before
or
today
175
local
where
=
{
}
176 177
if
type
(
before
)
=
=
"
number
"
then
178
before
=
os
.
date
(
before
)
179
end
180 181
before
=
os
.
time
{
182
day
=
before
.
day
or
today
.
day
,
183
month
=
before
.
month
or
today
.
month
,
184
year
=
before
.
year
or
today
.
year
,
185
hour
=
before
.
hour
or
0
,
186
minute
=
before
.
minute
or
0
,
187
second
=
before
.
second
or
0
,
188
isdst
=
true
,
189
}
190 191
where
[
#
where
+
1
]
=
format
(
"
`time` < %s
"
,
before
)
192 193
db
.
execute
{
194
template
=
template
,
195
variables
=
{
196
basename
=
db
.
basename
,
197
WHERE
=
format
(
"
WHERE\n%s
"
,
concat
(
where
,
"
AND
"
)
)
,
198
}
,
199
}
200 201
if
db
.
usedmethod
=
=
"
sqlite
"
then
202
db
.
execute
{
203
template
=
"
VACUUM ;
"
,
204
}
205
end
206 207
end
208 209
local
template_nop
=
[[
210 SELECT 211 `time`, 212 `type`, 213 `action`, 214 `data` 215 FROM 216 %basename% 217 ORDER BY 218 `time`, `type`, `action` 219 DESC LIMIT 220 %limit% ; 221
]]
222 223
local
template_yes
=
[[
224 SELECT 225 `time`, 226 `type`, 227 `action`, 228 `data` 229 FROM 230 %basename% 231 %WHERE% 232 ORDER BY 233 `time`, `type`, `action` 234 DESC LIMIT 235 %limit% ; 236
]]
237 238
local
converter
=
makeconverter
{
239
-- { name = "time", type = os.localtime },
240
{
name
=
"
time
"
,
type
=
"
number
"
}
,
241
{
name
=
"
type
"
,
type
=
fromtype
}
,
242
{
name
=
"
action
"
,
type
=
"
string
"
}
,
243
{
name
=
"
data
"
,
type
=
"
deserialize
"
}
,
244
}
245 246
function
loggers
.
collect
(
db
,
specification
)
247 248
specification
=
specification
or
{
}
249 250
local
start
=
specification
.
start
251
local
stop
=
specification
.
stop
252
local
limit
=
specification
.
limit
or
100
253
local
kind
=
specification
.
type
254
local
action
=
specification
.
action
255 256
local
filtered
=
start
or
stop
257 258
local
where
=
{
}
259 260
if
filtered
then
261
local
today
=
os
.
date
(
"
*t
"
)
262 263
if
type
(
start
)
~
=
"
table
"
then
264
start
=
{
}
265
end
266
start
=
os
.
time
{
267
day
=
start
.
day
or
today
.
day
,
268
month
=
start
.
month
or
today
.
month
,
269
year
=
start
.
year
or
today
.
year
,
270
hour
=
start
.
hour
or
0
,
271
minute
=
start
.
minute
or
0
,
272
second
=
start
.
second
or
0
,
273
isdst
=
true
,
274
}
275 276
if
type
(
stop
)
~
=
"
table
"
then
277
stop
=
{
}
278
end
279
stop
=
os
.
time
{
280
day
=
stop
.
day
or
today
.
day
,
281
month
=
stop
.
month
or
today
.
month
,
282
year
=
stop
.
year
or
today
.
year
,
283
hour
=
stop
.
hour
or
24
,
284
minute
=
stop
.
minute
or
0
,
285
second
=
stop
.
second
or
0
,
286
isdst
=
true
,
287
}
288 289
-- report("filter: %s => %s",start,stop)
290 291
where
[
#
where
+
1
]
=
format
(
"
`time` BETWEEN %s AND %s
"
,
start
,
stop
)
292 293
end
294 295
if
kind
then
296
where
[
#
where
+
1
]
=
format
(
"
`type` = %s
"
,
totype
[
kind
]
)
297
end
298 299
if
action
then
300
where
[
#
where
+
1
]
=
format
(
"
`action` = '%s'
"
,
action
)
301
end
302 303
local
records
=
db
.
execute
{
304
template
=
filtered
and
template_yes
or
template_nop
,
305
converter
=
converter
,
306
variables
=
{
307
basename
=
db
.
basename
,
308
limit
=
limit
,
309
WHERE
=
#
where
>
0
and
format
(
"
WHERE\n%s
"
,
concat
(
where
,
"
AND
"
)
)
or
"
"
,
310
}
,
311
}
312 313
if
trace_sql
then
314
report
(
"
collected: %s loggers
"
,
#
records
)
315
end
316 317
return
records
,
keys
318 319
end
320