util-sql-logins.lua /size: 7546 b    last modification: 2020-07-01 14:35
1if not modules then modules = { } end modules ['util-sql-logins'] = {
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
9if not utilities.sql then require("util-sql") end
10
11local sql              = utilities.sql
12local sqlexecute       = sql.execute
13local sqlmakeconverter = sql.makeconverter
14
15local format = string.format
16local ostime = os.time
17local formatter = string.formatter
18
19local trace_logins  = true
20local report_logins = logs.reporter("sql","logins")
21
22local logins = sql.logins or { }
23sql.logins   = logins
24
25logins.maxnoflogins = logins.maxnoflogins or 10
26logins.cooldowntime = logins.cooldowntime or 10 * 60
27logins.purgetime    = logins.purgetime    or  1 * 60 * 60
28logins.autopurge    = true
29
30local function checkeddb(presets,datatable)
31    return sql.usedatabase(presets,datatable or presets.datatable or "logins")
32end
33
34logins.usedb = checkeddb
35
36local template = [[
37    CREATE TABLE IF NOT EXISTS %basename% (
38        `id`    int(11)     NOT NULL AUTO_INCREMENT,
39        `name`  varchar(50) NOT NULL,
40        `time`  int(11)     DEFAULT '0',
41        `n`     int(11)     DEFAULT '0',
42        `state` int(11)     DEFAULT '0',
43
44        PRIMARY KEY                  (`id`),
45        UNIQUE KEY `id_unique_index` (`id`),
46        UNIQUE KEY `name_unique_key` (`name`)
47    ) DEFAULT CHARSET = utf8 ;
48]]
49
50local sqlite_template = [[
51    CREATE TABLE IF NOT EXISTS %basename% (
52        `id`    INTEGER NOT NULL AUTO_INCREMENT,
53        `name`  TEXT    NOT NULL,
54        `time`  INTEGER DEFAULT '0',
55        `n`     INTEGER DEFAULT '0',
56        `state` INTEGER DEFAULT '0'
57    ) ;
58]]
59
60function logins.createdb(presets,datatable)
61
62    local db = checkeddb(presets,datatable)
63
64    local data, keys = db.execute {
65        template  = db.usedmethod == "sqlite" and sqlite_template or template,
66        variables = {
67            basename = db.basename,
68        },
69    }
70
71    report_logins("datatable %a created in %a",db.name,db.base)
72
73    return db
74
75end
76
77local template =[[
78    DROP TABLE IF EXISTS %basename% ;
79]]
80
81function logins.deletedb(presets,datatable)
82
83    local db = checkeddb(presets,datatable)
84
85    local data, keys = db.execute {
86        template  = template,
87        variables = {
88            basename = db.basename,
89        },
90    }
91
92    report_logins("datatable %a removed in %a",db.name,db.base)
93
94end
95
96local states = {
97    [0] = "unset",
98    [1] = "known",
99    [2] = "unknown",
100}
101
102local converter_fetch, fields_fetch = sqlmakeconverter {
103    { name = "id",    type = "number" },
104    { name = "name",  type = "string" },
105    { name = "time",  type = "number" },
106    { name = "n",     type = "number" },
107    { name = "state", type = "number" }, -- faster than mapping
108}
109
110local template_fetch = format( [[
111    SELECT
112      %s
113    FROM
114        `logins`
115    WHERE
116        `name` = '%%[name]%%'
117]], fields_fetch )
118
119local template_insert = [[
120    INSERT INTO `logins`
121        ( `name`, `state`, `time`, `n`)
122    VALUES
123        ('%[name]%', %state%, %time%, %n%)
124]]
125
126local template_update = [[
127    UPDATE
128        `logins`
129    SET
130        `state` = %state%,
131        `time` = %time%,
132        `n` = %n%
133    WHERE
134        `name` = '%[name]%'
135]]
136
137local template_delete = [[
138    DELETE FROM
139        `logins`
140    WHERE
141        `name` = '%[name]%'
142]]
143
144local template_purge = [[
145    DELETE FROM
146        `logins`
147    WHERE
148        `time` < '%time%'
149]]
150
151-- todo: auto cleanup (when new attempt)
152
153local cache = { } setmetatable(cache, { __mode = 'v' })
154
155-- local function usercreate(presets)
156--     sqlexecute {
157--         template = template_create,
158--         presets  = presets,
159--     }
160-- end
161
162function logins.userunknown(db,name)
163    local d = {
164        name  = name,
165        state = 2,
166        time  = ostime(),
167        n     = 0,
168    }
169    db.execute {
170        template  = template_update,
171        variables = d,
172    }
173    cache[name] = d
174    report_logins("user %a is registered as unknown",name)
175end
176
177function logins.userknown(db,name)
178    local d = {
179        name  = name,
180        state = 1,
181        time  = ostime(),
182        n     = 0,
183    }
184    db.execute {
185        template  = template_update,
186        variables = d,
187    }
188    cache[name] = d
189    report_logins("user %a is registered as known",name)
190end
191
192function logins.userreset(db,name)
193    db.execute {
194        template  = template_delete,
195    }
196    cache[name] = nil
197    report_logins("user %a is reset",name)
198end
199
200local function userpurge(db,delay)
201    db.execute {
202        template  = template_purge,
203        variables = {
204            time  = ostime() - (delay or logins.purgetime),
205        }
206    }
207    cache = { }
208    report_logins("users are purged")
209end
210
211logins.userpurge = userpurge
212
213local function verdict(okay,...)
214--     if not trace_logins then
215--         -- no tracing
216--     else
217    if okay then
218        report_logins("%s, granted",formatter(...))
219    else
220        report_logins("%s, blocked",formatter(...))
221    end
222    return okay
223end
224
225local lasttime  = 0
226
227function logins.userpermitted(db,name)
228    local currenttime = ostime()
229    if logins.autopurge and (lasttime == 0 or (currenttime - lasttime > logins.purgetime)) then
230        report_logins("automatic purge triggered")
231        userpurge(db)
232        lasttime = currenttime
233    end
234    local data = cache[name]
235    if data then
236        report_logins("user %a is cached",name)
237    else
238        report_logins("user %a is fetched",name)
239        data = db.execute {
240            template  = template_fetch,
241            converter = converter_fetch,
242            variables = {
243                name = name,
244            }
245        }
246    end
247    if not data or not data.name then
248        if not data then
249            report_logins("no user data for %a",name)
250        else
251            report_logins("no name entry for %a",name)
252        end
253        local d = {
254            name  = name,
255            state = 0,
256            time  = currenttime,
257            n     = 1,
258        }
259        db.execute {
260            template  = template_insert,
261            variables = d,
262        }
263        cache[name] = d
264        return verdict(true,"creating new entry for %a",name)
265    end
266    cache[name] = data[1]
267    local state = data.state
268    if state == 2 then -- unknown
269        return verdict(false,"user %a has state %a",name,states[state])
270    end
271    local n = data.n
272    local m = logins.maxnoflogins
273    if n > m then
274        local deltatime = currenttime - data.time
275        local cooldowntime = logins.cooldowntime
276        if deltatime < cooldowntime then
277            return verdict(false,"user %a is blocked for %s seconds out of %s",name,cooldowntime-deltatime,cooldowntime)
278        else
279            n = 0
280        end
281    end
282    if n == 0 then
283        local d = {
284            name  = name,
285            state = 0,
286            time  = currenttime,
287            n     = 1,
288        }
289        db.execute {
290            template  = template_update,
291            variables = d,
292        }
293        cache[name] = d
294        return verdict(true,"user %a gets a first chance",name)
295    else
296        local d = {
297            name  = name,
298            state = 0,
299            time  = currenttime,
300            n     = n + 1,
301        }
302        db.execute {
303            template  = template_update,
304            variables = d,
305        }
306        cache[name] = d
307        return verdict(true,"user %a gets a new chance, %s attempts out of %s done",name,n,m)
308    end
309end
310
311return logins
312