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" },
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
152
153local cache = { } setmetatable(cache, { __mode = 'v' })
154
155
156
157
158
159
160
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
215
216
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
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 |
