util-sql-logins.lua /size: 7546 b    last modification: 2020-07-01 14:35
1
if
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 9
if
not
utilities
.
sql
then
require
(
"
util-sql
"
)
end
10 11
local
sql
=
utilities
.
sql
12
local
sqlexecute
=
sql
.
execute
13
local
sqlmakeconverter
=
sql
.
makeconverter
14 15
local
format
=
string
.
format
16
local
ostime
=
os
.
time
17
local
formatter
=
string
.
formatter
18 19
local
trace_logins
=
true
20
local
report_logins
=
logs
.
reporter
(
"
sql
"
,
"
logins
"
)
21 22
local
logins
=
sql
.
logins
or
{
}
23
sql
.
logins
=
logins
24 25
logins
.
maxnoflogins
=
logins
.
maxnoflogins
or
10
26
logins
.
cooldowntime
=
logins
.
cooldowntime
or
10
*
60
27
logins
.
purgetime
=
logins
.
purgetime
or
1
*
60
*
60
28
logins
.
autopurge
=
true
29 30
local
function
checkeddb
(
presets
,
datatable
)
31
return
sql
.
usedatabase
(
presets
,
datatable
or
presets
.
datatable
or
"
logins
"
)
32
end
33 34
logins
.
usedb
=
checkeddb
35 36
local
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 50
local
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 60
function
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 75
end
76 77
local
template
=
[[
78 DROP TABLE IF EXISTS %basename% ; 79
]]
80 81
function
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 94
end
95 96
local
states
=
{
97
[
0
]
=
"
unset
"
,
98
[
1
]
=
"
known
"
,
99
[
2
]
=
"
unknown
"
,
100
}
101 102
local
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 110
local
template_fetch
=
format
(
[[
111 SELECT 112 %s 113 FROM 114 `logins` 115 WHERE 116 `name` = '%%[name]%%' 117
]]
,
fields_fetch
)
118 119
local
template_insert
=
[[
120 INSERT INTO `logins` 121 ( `name`, `state`, `time`, `n`) 122 VALUES 123 ('%[name]%', %state%, %time%, %n%) 124
]]
125 126
local
template_update
=
[[
127 UPDATE 128 `logins` 129 SET 130 `state` = %state%, 131 `time` = %time%, 132 `n` = %n% 133 WHERE 134 `name` = '%[name]%' 135
]]
136 137
local
template_delete
=
[[
138 DELETE FROM 139 `logins` 140 WHERE 141 `name` = '%[name]%' 142
]]
143 144
local
template_purge
=
[[
145 DELETE FROM 146 `logins` 147 WHERE 148 `time` < '%time%' 149
]]
150 151
-- todo: auto cleanup (when new attempt)
152 153
local
cache
=
{
}
setmetatable
(
cache
,
{
__mode
=
'
v
'
}
)
154 155
-- local function usercreate(presets)
156
-- sqlexecute {
157
-- template = template_create,
158
-- presets = presets,
159
-- }
160
-- end
161 162
function
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
)
175
end
176 177
function
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
)
190
end
191 192
function
logins
.
userreset
(
db
,
name
)
193
db
.
execute
{
194
template
=
template_delete
,
195
}
196
cache
[
name
]
=
nil
197
report_logins
(
"
user %a is reset
"
,
name
)
198
end
199 200
local
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
"
)
209
end
210 211
logins
.
userpurge
=
userpurge
212 213
local
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
223
end
224 225
local
lasttime
=
0
226 227
function
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
309
end
310 311
return
logins
312