1if 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
10
11
12
13local tonumber = tonumber
14local format = string.format
15local concat = table.concat
16local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime
17local random = math.random
18
19local sql = utilities.sql
20local loggers = { }
21sql.loggers = loggers
22
23local trace_sql = false trackers.register("sql.loggers.trace", function(v) trace_sql = v end)
24local report = logs.reporter("sql","loggers")
25
26loggers.newtoken = sql.tokens.new
27local makeconverter = sql.makeconverter
28
29local function checkeddb(presets,datatable)
30 return sql.usedatabase(presets,datatable or presets.datatable or "loggers")
31end
32
33loggers.usedb = checkeddb
34
35local 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
42local 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
49table.setmetatableindex(totype, function() return 4 end)
50table.setmetatableindex(fromtype,function() return "info" end)
51
52loggers.totype = totype
53loggers.fromtype = fromtype
54
55local 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
67local 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
77function 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
92end
93
94local template =[[
95 DROP TABLE IF EXISTS %basename% ;
96]]
97
98function 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
111end
112
113local 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
128
129
130
131function 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
143 data = extra
144 else
145
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
163end
164
165local template =[[
166 DELETE FROM %basename% %WHERE% ;
167]]
168
169function 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
207end
208
209local 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
223local 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
238local converter = makeconverter {
239
240 { name = "time", type = "number" },
241 { name = "type", type = fromtype },
242 { name = "action", type = "string" },
243 { name = "data", type = "deserialize" },
244}
245
246function 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
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
319end
320 |