util-sql-tickets.lua /size: 15 Kb    last modification: 2020-07-01 14:35
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql-tickets
'
]
=
{
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
-- TODO: MAKE SOME INTO STORED PROCUDURES
10 11
-- This is experimental code and currently part of the base installation simply
12
-- because it's easier to distribute this way. Eventually it will be documented
13
-- and the related scripts will show up as well.
14 15
local
tonumber
=
tonumber
16
local
format
=
string
.
format
17
local
ostime
,
uuid
,
osfulltime
=
os
.
time
,
os
.
uuid
,
os
.
fulltime
18
local
random
=
math
.
random
19
local
concat
=
table
.
concat
20 21
if
not
utilities
.
sql
then
require
(
"
util-sql
"
)
end
22 23
local
sql
=
utilities
.
sql
24
local
tickets
=
{
}
25
sql
.
tickets
=
tickets
26 27
local
trace_sql
=
false
trackers
.
register
(
"
sql.tickets.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
28
local
report
=
logs
.
reporter
(
"
sql
"
,
"
tickets
"
)
29 30
local
serialize
=
sql
.
serialize
31
local
deserialize
=
sql
.
deserialize
32 33
tickets
.
newtoken
=
sql
.
tokens
.
new
34 35
-- Beware as an index can be a string or a number, we will create
36
-- a combination of hash and index.
37 38
local
statustags
=
{
[
0
]
=
39
"
unknown
"
,
40
"
pending
"
,
41
"
busy
"
,
42
"
finished
"
,
43
"
dependent
"
,
-- same token but different subtoken (so we only need to find the first)
44
"
reserved-1
"
,
45
"
reserved-2
"
,
46
"
error
"
,
47
"
deleted
"
,
48
}
49 50
local
status
=
table
.
swapped
(
statustags
)
51
tickets
.
status
=
status
52
tickets
.
statustags
=
statustags
53 54
local
s_unknown
=
status
.
unknown
55
local
s_pending
=
status
.
pending
56
local
s_busy
=
status
.
busy
57
----- s_finished = status.finished
58
local
s_dependent
=
status
.
dependent
59
local
s_error
=
status
.
error
60
local
s_deleted
=
status
.
deleted
61 62
local
s_rubish
=
s_error
-- and higher
63 64
local
function
checkeddb
(
presets
,
datatable
)
65
return
sql
.
usedatabase
(
presets
,
datatable
or
presets
.
datatable
or
"
tickets
"
)
66
end
67 68
tickets
.
usedb
=
checkeddb
69 70
local
template
=
[[
71 CREATE TABLE IF NOT EXISTS %basename% ( 72 `id` int(11) NOT NULL AUTO_INCREMENT, 73 `token` varchar(50) NOT NULL, 74 `subtoken` INT(11) NOT NULL, 75 `created` int(11) NOT NULL, 76 `accessed` int(11) NOT NULL, 77 `category` int(11) NOT NULL, 78 `status` int(11) NOT NULL, 79 `usertoken` varchar(50) NOT NULL, 80 `data` longtext NOT NULL, 81 `comment` longtext NOT NULL, 82 83 PRIMARY KEY (`id`), 84 UNIQUE INDEX `id_unique_index` (`id` ASC), 85 KEY `token_unique_key` (`token`) 86 ) DEFAULT CHARSET = utf8 ; 87
]]
88 89
local
sqlite_template
=
[[
90 CREATE TABLE IF NOT EXISTS %basename% ( 91 `id` TEXT NOT NULL AUTO_INCREMENT, 92 `token` TEXT NOT NULL, 93 `subtoken` INTEGER DEFAULT '0', 94 `created` INTEGER DEFAULT '0', 95 `accessed` INTEGER DEFAULT '0', 96 `category` INTEGER DEFAULT '0', 97 `status` INTEGER DEFAULT '0', 98 `usertoken` TEXT NOT NULL, 99 `data` TEXT NOT NULL, 100 `comment` TEXT NOT NULL 101 ) ; 102
]]
103 104
function
tickets
.
createdb
(
presets
,
datatable
)
105 106
local
db
=
checkeddb
(
presets
,
datatable
)
107 108
local
data
,
keys
=
db
.
execute
{
109
template
=
db
.
usedmethod
=
=
"
sqlite
"
and
sqlite_template
or
template
,
110
variables
=
{
111
basename
=
db
.
basename
,
112
}
,
113
}
114 115
report
(
"
datatable %a created in %a
"
,
db
.
name
,
db
.
base
)
116 117
return
db
118 119
end
120 121
local
template
=
[[
122 DROP TABLE IF EXISTS %basename% ; 123
]]
124 125
function
tickets
.
deletedb
(
presets
,
datatable
)
126 127
local
db
=
checkeddb
(
presets
,
datatable
)
128 129
local
data
,
keys
=
db
.
execute
{
130
template
=
template
,
131
variables
=
{
132
basename
=
db
.
basename
,
133
}
,
134
}
135 136
report
(
"
datatable %a removed in %a
"
,
db
.
name
,
db
.
base
)
137 138
end
139 140
local
template_push
=
[[
141 INSERT INTO %basename% ( 142 `token`, 143 `subtoken`, 144 `created`, 145 `accessed`, 146 `status`, 147 `category`, 148 `usertoken`, 149 `data`, 150 `comment` 151 ) VALUES ( 152 '%token%', 153 %subtoken%, 154 %time%, 155 %time%, 156 %status%, 157 %category%, 158 '%usertoken%', 159 '%[data]%', 160 '%[comment]%' 161 ) ; 162
]]
163 164
local
template_fetch
=
[[
165 SELECT 166 * 167 FROM 168 %basename% 169 WHERE 170 `token` = '%token%' 171 AND 172 `subtoken` = '%subtoken%' 173 ; 174
]]
175 176
function
tickets
.
create
(
db
,
ticket
)
177 178
-- We assume a unique token .. if not we're toast anyway. We used to lock and
179
-- get the last id etc etc but there is no real need for that.
180 181
-- we could check for dependent here but we don't want the lookup
182 183
local
token
=
ticket
.
token
or
tickets
.
newtoken
(
)
184
local
time
=
ostime
(
)
185
local
status
=
ticket
.
status
186
local
category
=
ticket
.
category
or
0
187
local
subtoken
=
ticket
.
subtoken
or
0
188
local
usertoken
=
ticket
.
usertoken
or
"
"
189
local
comment
=
ticket
.
comment
or
"
"
190 191
status
=
not
status
and
subtoken
>
1
and
s_dependent
or
s_pending
192 193
local
result
,
message
=
db
.
execute
{
194
template
=
template_push
,
195
variables
=
{
196
basename
=
db
.
basename
,
197
token
=
token
,
198
subtoken
=
subtoken
,
199
time
=
time
,
200
status
=
status
,
201
category
=
category
,
202
usertoken
=
usertoken
,
203
data
=
db
.
serialize
(
ticket
.
data
or
{
}
,
"
return
"
)
,
204
comment
=
comment
,
205
}
,
206
}
207 208
-- We could stick to only fetching the id and make the table here
209
-- but we're not pushing that many tickets so we can as well follow
210
-- the lazy approach and fetch the whole.
211 212
local
result
,
message
=
db
.
execute
{
213
template
=
template_fetch
,
214
variables
=
{
215
basename
=
db
.
basename
,
216
token
=
token
,
217
subtoken
=
subtoken
,
218
}
,
219
}
220 221
if
result
and
#
result
>
0
then
222
if
trace_sql
then
223
report
(
"
created: %s at %s
"
,
token
,
osfulltime
(
time
)
)
224
end
225
return
result
[
1
]
226
else
227
report
(
"
failed: %s at %s
"
,
token
,
osfulltime
(
time
)
)
228
end
229 230
end
231 232
local
template
=
[[
233 UPDATE 234 %basename% 235 SET 236 `data` = '%[data]%', 237 `status` = %status%, 238 `accessed` = %time% 239 WHERE 240 `id` = %id% ; 241
]]
242 243
function
tickets
.
save
(
db
,
ticket
)
244 245
local
time
=
ostime
(
)
246
local
data
=
db
.
serialize
(
ticket
.
data
or
{
}
,
"
return
"
)
247
local
status
=
ticket
.
status
or
s_error
248 249
-- print("SETTING")
250
-- inspect(data)
251 252
ticket
.
status
=
status
253
ticket
.
accessed
=
time
254 255
db
.
execute
{
256
template
=
template
,
257
variables
=
{
258
basename
=
db
.
basename
,
259
id
=
ticket
.
id
,
260
time
=
ostime
(
)
,
261
status
=
status
,
262
data
=
data
,
263
}
,
264
}
265 266
if
trace_sql
then
267
report
(
"
saved: id %s, time %s
"
,
id
,
osfulltime
(
time
)
)
268
end
269 270
return
ticket
271
end
272 273
local
template
=
[[
274 UPDATE 275 %basename% 276 SET 277 `accessed` = %time% 278 WHERE 279 `token` = '%token%' ; 280 281 SELECT 282 * 283 FROM 284 %basename% 285 WHERE 286 `id` = %id% ; 287
]]
288 289
function
tickets
.
restore
(
db
,
id
)
290 291
local
record
,
keys
=
db
.
execute
{
292
template
=
template
,
293
variables
=
{
294
basename
=
db
.
basename
,
295
id
=
id
,
296
time
=
ostime
(
)
,
297
}
,
298
}
299 300
local
record
=
record
and
record
[
1
]
301 302
if
record
then
303
if
trace_sql
then
304
report
(
"
restored: id %s
"
,
id
)
305
end
306
record
.
data
=
db
.
deserialize
(
record
.
data
or
"
"
)
307
return
record
308
elseif
trace_sql
then
309
report
(
"
unknown: id %s
"
,
id
)
310
end
311 312
end
313 314
local
template
=
[[
315 DELETE FROM 316 %basename% 317 WHERE 318 `id` = %id% ; 319
]]
320 321
function
tickets
.
remove
(
db
,
id
)
322 323
db
.
execute
{
324
template
=
template
,
325
variables
=
{
326
basename
=
db
.
basename
,
327
id
=
id
,
328
}
,
329
}
330 331
if
trace_sql
then
332
report
(
"
removed: id %s
"
,
id
)
333
end
334 335
end
336 337
local
template_yes
=
[[
338 SELECT 339 * 340 FROM 341 %basename% 342 ORDER BY 343 `id` ; 344
]]
345 346
local
template_nop
=
[[
347 SELECT 348 `created`, 349 `usertoken`, 350 `accessed`, 351 `status` 352 FROM 353 %basename% 354 ORDER BY 355 `id` ; 356
]]
357 358
function
tickets
.
collect
(
db
,
nodata
)
359 360
local
records
,
keys
=
db
.
execute
{
361
template
=
nodata
and
template_nop
or
template_yes
,
362
variables
=
{
363
basename
=
db
.
basename
,
364
token
=
token
,
365
}
,
366
}
367 368
if
not
nodata
then
369
db
.
unpackdata
(
records
)
370
end
371 372
if
trace_sql
then
373
report
(
"
collected: %s tickets
"
,
#
records
)
374
end
375 376
return
records
,
keys
377 378
end
379 380
-- We aleays keep the last select in the execute so one can have
381
-- an update afterwards.
382 383
local
template
=
[[
384 DELETE FROM 385 %basename% 386 WHERE 387 `accessed` < %time% OR `status` >= %rubish% ; 388
]]
389 390
local
template_cleanup_yes
=
[[
391 SELECT 392 * 393 FROM 394 %basename% 395 WHERE 396 `accessed` < %time% 397 ORDER BY 398 `id` ; 399
]]
.
.
template
400 401
local
template_cleanup_nop
=
[[
402 SELECT 403 `accessed`, 404 `created`, 405 `accessed`, 406 `token` 407 `usertoken` 408 FROM 409 %basename% 410 WHERE 411 `accessed` < %time% 412 ORDER BY 413 `id` ; 414
]]
.
.
template
415 416
function
tickets
.
cleanupdb
(
db
,
delta
,
nodata
)
-- maybe delta in db
417 418
local
now
=
ostime
(
)
419
local
time
=
delta
and
(
now
-
delta
)
or
now
420 421
local
records
,
keys
=
db
.
execute
{
422
template
=
nodata
and
template_cleanup_nop
or
template_cleanup_yes
,
423
variables
=
{
424
basename
=
db
.
basename
,
425
time
=
time
,
426
rubish
=
s_rubish
,
427
}
,
428
}
429 430
if
not
nodata
then
431
db
.
unpackdata
(
records
)
432
end
433 434
if
trace_sql
then
435
report
(
"
cleaned: %s seconds before %s
"
,
delta
,
osfulltime
(
time
)
)
436
end
437 438
return
records
,
keys
439 440
end
441 442
-- status related functions
443 444
local
template
=
[[
445 SELECT 446 `status` 447 FROM 448 %basename% 449 WHERE 450 `token` = '%token%' 451 ORDER BY 452 `id` 453 ; 454
]]
455 456
function
tickets
.
getstatus
(
db
,
token
)
457 458
local
record
,
keys
=
db
.
execute
{
459
template
=
template
,
460
variables
=
{
461
basename
=
db
.
basename
,
462
token
=
token
,
463
}
,
464
}
465 466
local
record
=
record
and
record
[
1
]
467 468
return
record
and
record
.
status
or
s_unknown
469 470
end
471 472
local
template
=
[[
473 SELECT 474 `status` 475 FROM 476 %basename% 477 WHERE 478 `status` >= %rubish% OR `accessed` < %time% 479 ORDER BY 480 `id` 481 ; 482
]]
483 484
function
tickets
.
getobsolete
(
db
,
delta
)
485 486
local
time
=
delta
and
(
ostime
(
)
-
delta
)
or
0
487 488
local
records
=
db
.
execute
{
489
template
=
template
,
490
variables
=
{
491
basename
=
db
.
basename
,
492
time
=
time
,
493
rubish
=
s_rubish
,
494
}
,
495
}
496 497
db
.
unpackdata
(
records
)
498 499
return
records
500 501
end
502 503
local
template
=
[[
504 SELECT 505 `id` 506 FROM 507 %basename% 508 WHERE 509 `status` = %status% 510 LIMIT 511 1 ; 512
]]
513 514
function
tickets
.
hasstatus
(
db
,
status
)
515 516
local
records
=
db
.
execute
{
517
template
=
template
,
518
variables
=
{
519
basename
=
db
.
basename
,
520
status
=
status
or
s_unknown
,
521
}
,
522
}
523 524
return
records
and
#
records
>
0
or
false
525 526
end
527 528
local
template
=
[[
529 UPDATE 530 %basename% 531 SET 532 `status` = %status%, 533 `accessed` = %time% 534 WHERE 535 `id` = %id% ; 536
]]
537 538
function
tickets
.
setstatus
(
db
,
id
,
status
)
539 540
db
.
execute
{
541
template
=
template
,
542
variables
=
{
543
basename
=
db
.
basename
,
544
id
=
id
,
545
time
=
ostime
(
)
,
546
status
=
status
or
s_error
,
547
}
,
548
}
549 550
end
551 552
local
template
=
[[
553 DELETE FROM 554 %basename% 555 WHERE 556 `status` IN (%status%) ; 557
]]
558 559
function
tickets
.
prunedb
(
db
,
status
)
560 561
if
type
(
status
)
=
=
"
table
"
then
562
status
=
concat
(
status
,
"
,
"
)
563
end
564 565
local
data
,
keys
=
db
.
execute
{
566
template
=
template
,
567
variables
=
{
568
basename
=
db
.
basename
,
569
status
=
status
or
s_unknown
,
570
}
,
571
}
572 573
if
trace_sql
then
574
report
(
"
pruned: status %s removed
"
,
status
)
575
end
576 577
end
578 579
-- START TRANSACTION ; ... COMMIT ;
580
-- LOCK TABLES %basename% WRITE ; ... UNLOCK TABLES ;
581 582
local
template_a
=
[[
583 SET 584 @last_ticket_token = '' ; 585 UPDATE 586 %basename% 587 SET 588 `token` = (@last_ticket_token := `token`), 589 `status` = %newstatus%, 590 `accessed` = %time% 591 WHERE 592 `status` = %status% 593 ORDER BY 594 `id` 595 LIMIT 596 1 597 ; 598 SELECT 599 * 600 FROM 601 %basename% 602 WHERE 603 `token` = @last_ticket_token 604 ORDER BY 605 `id` 606 ; 607
]]
608 609
local
template_b
=
[[
610 SELECT 611 * 612 FROM 613 tickets 614 WHERE 615 `status` = %status% 616 ORDER BY 617 `id` 618 LIMIT 619 1 620 ; 621
]]
622 623
function
tickets
.
getfirstwithstatus
(
db
,
status
,
newstatus
)
624 625
local
records
626 627
if
type
(
newstatus
)
=
=
"
number
"
then
-- todo: also accept string
628 629
records
=
db
.
execute
{
630
template
=
template_a
,
631
variables
=
{
632
basename
=
db
.
basename
,
633
status
=
status
or
s_pending
,
634
newstatus
=
newstatus
,
635
time
=
ostime
(
)
,
636
}
,
637
}
638 639 640
else
641 642
records
=
db
.
execute
{
643
template
=
template_b
,
644
variables
=
{
645
basename
=
db
.
basename
,
646
status
=
status
or
s_pending
,
647
}
,
648
}
649 650
end
651 652
if
type
(
records
)
=
=
"
table
"
and
#
records
>
0
then
653 654
for
i
=
1
,
#
records
do
655
local
record
=
records
[
i
]
656
record
.
data
=
db
.
deserialize
(
record
.
data
or
"
"
)
657
record
.
status
=
newstatus
or
s_busy
658
end
659 660
return
records
661 662
end
663
end
664 665
-- The next getter assumes that we have a sheduler running so that there is
666
-- one process in charge of changing the status.
667 668
local
template
=
[[
669 SET 670 @last_ticket_token = '' ; 671 UPDATE 672 %basename% 673 SET 674 `token` = (@last_ticket_token := `token`), 675 `status` = %newstatus%, 676 `accessed` = %time% 677 WHERE 678 `status` = %status% 679 ORDER BY 680 `id` 681 LIMIT 682 1 683 ; 684 SELECT 685 @last_ticket_token AS `token` 686 ; 687
]]
688 689
function
tickets
.
getfirstinqueue
(
db
,
status
,
newstatus
)
690 691
local
records
=
db
.
execute
{
692
template
=
template
,
693
variables
=
{
694
basename
=
db
.
basename
,
695
status
=
status
or
s_pending
,
696
newstatus
=
newstatus
or
s_busy
,
697
time
=
ostime
(
)
,
698
}
,
699
}
700 701
local
token
=
type
(
records
)
=
=
"
table
"
and
#
records
>
0
and
records
[
1
]
.
token
702 703
return
token
~
=
"
"
and
token
704 705
end
706 707
local
template
=
[[
708 SELECT 709 * 710 FROM 711 %basename% 712 WHERE 713 `token` = '%token%' 714 ORDER BY 715 `id` ; 716
]]
717 718
function
tickets
.
getticketsbytoken
(
db
,
token
)
719 720
local
records
,
keys
=
db
.
execute
{
721
template
=
template
,
722
variables
=
{
723
basename
=
db
.
basename
,
724
token
=
token
,
725
}
,
726
}
727 728
db
.
unpackdata
(
records
)
729 730
return
records
731 732
end
733 734
local
template
=
[[
735 SELECT 736 * 737 FROM 738 %basename% 739 WHERE 740 `usertoken` = '%usertoken%' AND `status` < %rubish% 741 ORDER BY 742 `id` ; 743
]]
744 745
function
tickets
.
getusertickets
(
db
,
usertoken
)
746 747
-- todo: update accessed
748
-- todo: get less fields
749
-- maybe only data for status changed (hard to check)
750 751
local
records
,
keys
=
db
.
execute
{
752
template
=
template
,
753
variables
=
{
754
basename
=
db
.
basename
,
755
usertoken
=
usertoken
,
756
rubish
=
s_rubish
,
757
}
,
758
}
759 760
db
.
unpackdata
(
records
)
761 762
return
records
763 764
end
765 766
local
template
=
[[
767 UPDATE 768 %basename% 769 SET 770 `status` = %deleted% 771 WHERE 772 `usertoken` = '%usertoken%' ; 773
]]
774 775
function
tickets
.
removeusertickets
(
db
,
usertoken
)
776 777
db
.
execute
{
778
template
=
template
,
779
variables
=
{
780
basename
=
db
.
basename
,
781
usertoken
=
usertoken
,
782
deleted
=
s_deleted
,
783
}
,
784
}
785 786
if
trace_sql
then
787
report
(
"
removed: usertoken %s
"
,
usertoken
)
788
end
789 790
end
791