util-sql-loggers.lua /size: 7825 b    last modification: 2020-07-01 14:35
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-- 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
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-- 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
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            -- 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
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 -- { 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
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     -- 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
319end
320