util-sql-tickets.lua /size: 15 Kb    last modification: 2023-12-21 09:44
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-- 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
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-- if not utilities.sql then require("util-sql") end
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-- Beware as an index can be a string or a number, we will create
36-- a combination of hash and index.
37
38local 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
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----- s_finished  = status.finished
58local s_dependent = status.dependent
59local s_error     = status.error
60local s_deleted   = status.deleted
61
62local s_rubish    = s_error -- and higher
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    -- 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
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-- 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
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-- We aleays keep the last select in the execute so one can have
381-- an update afterwards.
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) -- 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
440end
441
442-- status related functions
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-- START TRANSACTION ; ... COMMIT ;
580-- LOCK TABLES %basename% WRITE ; ... UNLOCK TABLES ;
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 -- 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
663end
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
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    -- 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
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