1if 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
10
11
12
13
14
15local tonumber = tonumber
16local format = string.format
17local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime
18local random = math.random
19local concat = table.concat
20
21
22
23local sql = utilities.sql
24local tickets = { }
25sql.tickets = tickets
26
27local trace_sql = false trackers.register("sql.tickets.trace", function(v) trace_sql = v end)
28local report = logs.reporter("sql","tickets")
29
30local serialize = sql.serialize
31local deserialize = sql.deserialize
32
33tickets.newtoken = sql.tokens.new
34
35
36
37
38local statustags = { [0] =
39 "unknown",
40 "pending",
41 "busy",
42 "finished",
43 "dependent",
44 "reserved-1",
45 "reserved-2",
46 "error",
47 "deleted",
48}
49
50local status = table.swapped(statustags)
51tickets.status = status
52tickets.statustags = statustags
53
54local s_unknown = status.unknown
55local s_pending = status.pending
56local s_busy = status.busy
57
58local s_dependent = status.dependent
59local s_error = status.error
60local s_deleted = status.deleted
61
62local s_rubish = s_error
63
64local function checkeddb(presets,datatable)
65 return sql.usedatabase(presets,datatable or presets.datatable or "tickets")
66end
67
68tickets.usedb = checkeddb
69
70local 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
89local 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
104function 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
119end
120
121local template =[[
122 DROP TABLE IF EXISTS %basename% ;
123]]
124
125function 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
138end
139
140local 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
164local template_fetch =[[
165 SELECT
166 *
167 FROM
168 %basename%
169 WHERE
170 `token` = '%token%'
171 AND
172 `subtoken` = '%subtoken%'
173 ;
174]]
175
176function tickets.create(db,ticket)
177
178
179
180
181
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
209
210
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
230end
231
232local template =[[
233 UPDATE
234 %basename%
235 SET
236 `data` = '%[data]%',
237 `status` = %status%,
238 `accessed` = %time%
239 WHERE
240 `id` = %id% ;
241]]
242
243function 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
250
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
271end
272
273local 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
289function 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
312end
313
314local template =[[
315 DELETE FROM
316 %basename%
317 WHERE
318 `id` = %id% ;
319]]
320
321function 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
335end
336
337local template_yes =[[
338 SELECT
339 *
340 FROM
341 %basename%
342 ORDER BY
343 `id` ;
344]]
345
346local template_nop =[[
347 SELECT
348 `created`,
349 `usertoken`,
350 `accessed`,
351 `status`
352 FROM
353 %basename%
354 ORDER BY
355 `id` ;
356]]
357
358function 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
378end
379
380
381
382
383local template =[[
384 DELETE FROM
385 %basename%
386 WHERE
387 `accessed` < %time% OR `status` >= %rubish% ;
388]]
389
390local template_cleanup_yes =[[
391 SELECT
392 *
393 FROM
394 %basename%
395 WHERE
396 `accessed` < %time%
397 ORDER BY
398 `id` ;
399]] .. template
400
401local 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
416function tickets.cleanupdb(db,delta,nodata)
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
440end
441
442
443
444local template =[[
445 SELECT
446 `status`
447 FROM
448 %basename%
449 WHERE
450 `token` = '%token%'
451 ORDER BY
452 `id`
453 ;
454]]
455
456function 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
470end
471
472local 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
484function 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
501end
502
503local template =[[
504 SELECT
505 `id`
506 FROM
507 %basename%
508 WHERE
509 `status` = %status%
510 LIMIT
511 1 ;
512]]
513
514function 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
526end
527
528local template =[[
529 UPDATE
530 %basename%
531 SET
532 `status` = %status%,
533 `accessed` = %time%
534 WHERE
535 `id` = %id% ;
536]]
537
538function 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
550end
551
552local template =[[
553 DELETE FROM
554 %basename%
555 WHERE
556 `status` IN (%status%) ;
557]]
558
559function 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
577end
578
579
580
581
582local 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
609local 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
623function tickets.getfirstwithstatus(db,status,newstatus)
624
625 local records
626
627 if type(newstatus) == "number" then
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
663end
664
665
666
667
668local 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
689function 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
705end
706
707local template =[[
708 SELECT
709 *
710 FROM
711 %basename%
712 WHERE
713 `token` = '%token%'
714 ORDER BY
715 `id` ;
716]]
717
718function 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
732end
733
734local template =[[
735 SELECT
736 *
737 FROM
738 %basename%
739 WHERE
740 `usertoken` = '%usertoken%' AND `status` < %rubish%
741 ORDER BY
742 `id` ;
743]]
744
745function tickets.getusertickets(db,usertoken)
746
747
748
749
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
764end
765
766local template =[[
767 UPDATE
768 %basename%
769 SET
770 `status` = %deleted%
771 WHERE
772 `usertoken` = '%usertoken%' ;
773]]
774
775function 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
790end
791 |
