diff options
Diffstat (limited to 'tex/context/base/util-sql-tickets.lua')
-rw-r--r-- | tex/context/base/util-sql-tickets.lua | 1544 |
1 files changed, 772 insertions, 772 deletions
diff --git a/tex/context/base/util-sql-tickets.lua b/tex/context/base/util-sql-tickets.lua index 65eb69bae..5e958299d 100644 --- a/tex/context/base/util-sql-tickets.lua +++ b/tex/context/base/util-sql-tickets.lua @@ -1,772 +1,772 @@ -if not modules then modules = { } end modules ['util-sql-tickets'] = {
- version = 1.001,
- comment = "companion to lmx-*",
- author = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
- copyright = "PRAGMA ADE / ConTeXt Development Team",
- license = "see context related readme files"
-}
-
--- TODO: MAKE SOME INTO STORED PROCUDURES
-
--- This is experimental code and currently part of the base installation simply
--- because it's easier to distribute this way. Eventually it will be documented
--- and the related scripts will show up as well.
-
-local tonumber = tonumber
-local format = string.format
-local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime
-local random = math.random
-local concat = table.concat
-
-local sql = utilities.sql
-local tickets = { }
-sql.tickets = tickets
-
-local trace_sql = false trackers.register("sql.tickets.trace", function(v) trace_sql = v end)
-local report = logs.reporter("sql","tickets")
-
-local serialize = sql.serialize
-local deserialize = sql.deserialize
-local execute = sql.execute
-
-tickets.newtoken = sql.tokens.new
-
--- Beware as an index can be a string or a number, we will create
--- a combination of hash and index.
-
-local statustags = { [0] =
- "unknown",
- "pending",
- "busy",
- "finished",
- "dependent", -- same token but different subtoken (so we only need to find the first)
- "reserved-1",
- "reserved-2",
- "error",
- "deleted",
-}
-
-local status = table.swapped(statustags)
-tickets.status = status
-tickets.statustags = statustags
-
-local s_unknown = status.unknown
-local s_pending = status.pending
-local s_busy = status.busy
-local s_finished = status.finished
-local s_dependent = status.dependent
-local s_error = status.error
-local s_deleted = status.deleted
-
-local s_rubish = s_error -- and higher
-
-local function checkeddb(presets,datatable)
- return sql.usedatabase(presets,datatable or presets.datatable or "tickets")
-end
-
-tickets.usedb = checkeddb
-
-local template =[[
- CREATE TABLE IF NOT EXISTS %basename% (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `token` varchar(50) NOT NULL,
- `subtoken` INT(11) NOT NULL,
- `created` int(11) NOT NULL,
- `accessed` int(11) NOT NULL,
- `category` int(11) NOT NULL,
- `status` int(11) NOT NULL,
- `usertoken` varchar(50) NOT NULL,
- `data` longtext NOT NULL,
- `comment` longtext NOT NULL,
-
- PRIMARY KEY (`id`),
- UNIQUE INDEX `id_unique_index` (`id` ASC),
- KEY `token_unique_key` (`token`)
- )
- DEFAULT CHARSET = utf8 ;
-]]
-
-function tickets.createdb(presets,datatable)
- local db = checkeddb(presets,datatable)
- local data, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- },
- }
-
- report("datatable %a created in %a",db.name,db.base)
-
- return db
-
-end
-
-local template =[[
- DROP TABLE IF EXISTS %basename% ;
-]]
-
-function tickets.deletedb(presets,datatable)
-
- local db = checkeddb(presets,datatable)
-
- local data, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- },
- }
-
- report("datatable %a removed in %a",db.name,db.base)
-
-end
-
-local template_push =[[
- INSERT INTO %basename% (
- `token`,
- `subtoken`,
- `created`,
- `accessed`,
- `status`,
- `category`,
- `usertoken`,
- `data`,
- `comment`
- ) VALUES (
- '%token%',
- %subtoken%,
- %time%,
- %time%,
- %status%,
- %category%,
- '%usertoken%',
- '%[data]%',
- '%[comment]%'
- ) ;
-]]
-
-local template_fetch =[[
- SELECT
- *
- FROM
- %basename%
- WHERE
- `token` = '%token%'
- AND
- `subtoken` = '%subtoken%'
- ;
-]]
-
-function tickets.create(db,ticket)
-
- -- We assume a unique token .. if not we're toast anyway. We used to lock and
- -- get the last id etc etc but there is no real need for that.
-
- -- we could check for dependent here but we don't want the lookup
-
- local token = ticket.token or tickets.newtoken()
- local time = ostime()
- local status = ticket.status
- local category = ticket.category or 0
- local subtoken = ticket.subtoken or 0
- local usertoken = ticket.usertoken or ""
- local comment = ticket.comment or ""
-
- status = not status and subtoken > 1 and s_dependent or s_pending
-
- local result, message = db.execute {
- template = template_push,
- variables = {
- basename = db.basename,
- token = token,
- subtoken = subtoken,
- time = time,
- status = status,
- category = category,
- usertoken = usertoken,
- data = db.serialize(ticket.data or { },"return"),
- comment = comment,
- },
- }
-
- -- We could stick to only fetching the id and make the table here
- -- but we're not pushing that many tickets so we can as well follow
- -- the lazy approach and fetch the whole.
-
- local result, message = db.execute {
- template = template_fetch,
- variables = {
- basename = db.basename,
- token = token,
- subtoken = subtoken,
- },
- }
-
- if result and #result > 0 then
- if trace_sql then
- report("created: %s at %s",token,osfulltime(time))
- end
- return result[1]
- else
- report("failed: %s at %s",token,osfulltime(time))
- end
-
-end
-
-local template =[[
- UPDATE
- %basename%
- SET
- `data` = '%[data]%',
- `status` = %status%,
- `accessed` = %time%
- WHERE
- `id` = %id% ;
-]]
-
-function tickets.save(db,ticket)
-
- local time = ostime()
- local data = db.serialize(ticket.data or { },"return")
- local status = ticket.status or s_error
-
--- print("SETTING")
--- inspect(data)
-
- ticket.status = status
- ticket.accessed = time
-
- db.execute {
- template = template,
- variables = {
- basename = db.basename,
- id = ticket.id,
- time = ostime(),
- status = status,
- data = data,
- },
- }
-
- if trace_sql then
- report("saved: id %s, time %s",id,osfulltime(time))
- end
-
- return ticket
-end
-
-local template =[[
- UPDATE
- %basename%
- SET
- `accessed` = %time%
- WHERE
- `token` = '%token%' ;
-
- SELECT
- *
- FROM
- %basename%
- WHERE
- `id` = %id% ;
-]]
-
-function tickets.restore(db,id)
-
- local record, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- id = id,
- time = ostime(),
- },
- }
-
- local record = record and record[1]
-
- if record then
- if trace_sql then
- report("restored: id %s",id)
- end
- record.data = db.deserialize(record.data or "")
- return record
- elseif trace_sql then
- report("unknown: id %s",id)
- end
-
-end
-
-local template =[[
- DELETE FROM
- %basename%
- WHERE
- `id` = %id% ;
-]]
-
-function tickets.remove(db,id)
-
- db.execute {
- template = template,
- variables = {
- basename = db.basename,
- id = id,
- },
- }
-
- if trace_sql then
- report("removed: id %s",id)
- end
-
-end
-
-local template_yes =[[
- SELECT
- *
- FROM
- %basename%
- ORDER BY
- `id` ;
-]]
-
-local template_nop =[[
- SELECT
- `created`,
- `usertoken`,
- `accessed`,
- `status`
- FROM
- %basename%
- ORDER BY
- `id` ;
-]]
-
-function tickets.collect(db,nodata)
-
- local records, keys = db.execute {
- template = nodata and template_nop or template_yes,
- variables = {
- basename = db.basename,
- token = token,
- },
- }
-
- if not nodata then
- db.unpackdata(records)
- end
-
- if trace_sql then
- report("collected: %s tickets",#records)
- end
-
- return records, keys
-
-end
-
--- We aleays keep the last select in the execute so one can have
--- an update afterwards.
-
-local template =[[
- DELETE FROM
- %basename%
- WHERE
- `accessed` < %time% OR `status` >= %rubish% ;
-]]
-
-local template_cleanup_yes =[[
- SELECT
- *
- FROM
- %basename%
- WHERE
- `accessed` < %time%
- ORDER BY
- `id` ;
-]] .. template
-
-local template_cleanup_nop =[[
- SELECT
- `accessed`,
- `created`,
- `accessed`,
- `token`
- `usertoken`
- FROM
- %basename%
- WHERE
- `accessed` < %time%
- ORDER BY
- `id` ;
-]] .. template
-
-function tickets.cleanupdb(db,delta,nodata) -- maybe delta in db
-
- local time = delta and (ostime() - delta) or 0
-
- local records, keys = db.execute {
- template = nodata and template_cleanup_nop or template_cleanup_yes,
- variables = {
- basename = db.basename,
- time = time,
- rubish = s_rubish,
- },
- }
-
- if not nodata then
- db.unpackdata(records)
- end
-
- if trace_sql then
- report("cleaned: %s seconds before %s",delta,osfulltime(time))
- end
-
- return records, keys
-
-end
-
--- status related functions
-
-local template =[[
- SELECT
- `status`
- FROM
- %basename%
- WHERE
- `token` = '%token%'
- ORDER BY
- `id`
- ;
-]]
-
-function tickets.getstatus(db,token)
-
- local record, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- token = token,
- },
- }
-
- local record = record and record[1]
-
- return record and record.status or s_unknown
-
-end
-
-local template =[[
- SELECT
- `status`
- FROM
- %basename%
- WHERE
- `status` >= %rubish% OR `accessed` < %time%
- ORDER BY
- `id`
- ;
-]]
-
-function tickets.getobsolete(db,delta)
-
- local time = delta and (ostime() - delta) or 0
-
- local records = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- time = time,
- rubish = s_rubish,
- },
- }
-
- db.unpackdata(records)
-
- return records
-
-end
-
-local template =[[
- SELECT
- `id`
- FROM
- %basename%
- WHERE
- `status` = %status%
- LIMIT
- 1 ;
-]]
-
-function tickets.hasstatus(db,status)
-
- local records = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- status = status or s_unknown,
- },
- }
-
- return records and #records > 0 or false
-
-end
-
-local template =[[
- UPDATE
- %basename%
- SET
- `status` = %status%,
- `accessed` = %time%
- WHERE
- `id` = %id% ;
-]]
-
-function tickets.setstatus(db,id,status)
-
- db.execute {
- template = template,
- variables = {
- basename = db.basename,
- id = id,
- time = ostime(),
- status = status or s_error,
- },
- }
-
-end
-
-local template =[[
- DELETE FROM
- %basename%
- WHERE
- `status` IN (%status%) ;
-]]
-
-function tickets.prunedb(db,status)
-
- if type(status) == "table" then
- status = concat(status,",")
- end
-
- local data, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- status = status or s_unknown,
- },
- }
-
- if trace_sql then
- report("pruned: status %s removed",status)
- end
-
-end
-
--- START TRANSACTION ; ... COMMIT ;
--- LOCK TABLES %basename% WRITE ; ... UNLOCK TABLES ;
-
-local template_a = [[
- SET
- @last_ticket_token = '' ;
- UPDATE
- %basename%
- SET
- `token` = (@last_ticket_token := `token`),
- `status` = %newstatus%,
- `accessed` = %time%
- WHERE
- `status` = %status%
- ORDER BY
- `id`
- LIMIT
- 1
- ;
- SELECT
- *
- FROM
- %basename%
- WHERE
- `token` = @last_ticket_token
- ORDER BY
- `id`
- ;
-]]
-
-local template_b = [[
- SELECT
- *
- FROM
- tickets
- WHERE
- `status` = %status%
- ORDER BY
- `id`
- LIMIT
- 1
- ;
-]]
-
-function tickets.getfirstwithstatus(db,status,newstatus)
-
- local records
-
- if type(newstatus) == "number" then -- todo: also accept string
-
- records = db.execute {
- template = template_a,
- variables = {
- basename = db.basename,
- status = status or s_pending,
- newstatus = newstatus,
- time = ostime(),
- },
- }
-
-
- else
-
- records = db.execute {
- template = template_b,
- variables = {
- basename = db.basename,
- status = status or s_pending,
- },
- }
-
- end
-
- if type(records) == "table" and #records > 0 then
-
- for i=1,#records do
- local record = records[i]
- record.data = db.deserialize(record.data or "")
- record.status = newstatus or s_busy
- end
-
- return records
-
- end
-end
-
--- The next getter assumes that we have a sheduler running so that there is
--- one process in charge of changing the status.
-
-local template = [[
- SET
- @last_ticket_token = '' ;
- UPDATE
- %basename%
- SET
- `token` = (@last_ticket_token := `token`),
- `status` = %newstatus%,
- `accessed` = %time%
- WHERE
- `status` = %status%
- ORDER BY
- `id`
- LIMIT
- 1
- ;
- SELECT
- @last_ticket_token AS `token`
- ;
-]]
-
-function tickets.getfirstinqueue(db,status,newstatus)
-
- local records = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- status = status or s_pending,
- newstatus = newstatus or s_busy,
- time = ostime(),
- },
- }
-
- local token = type(records) == "table" and #records > 0 and records[1].token
-
- return token ~= "" and token
-
-end
-
-local template =[[
- SELECT
- *
- FROM
- %basename%
- WHERE
- `token` = '%token%'
- ORDER BY
- `id` ;
-]]
-
-function tickets.getticketsbytoken(db,token)
-
- local records, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- token = token,
- },
- }
-
- db.unpackdata(records)
-
- return records
-
-end
-
-local template =[[
- SELECT
- *
- FROM
- %basename%
- WHERE
- `usertoken` = '%usertoken%' AND `status` < %rubish%
- ORDER BY
- `id` ;
-]]
-
-function tickets.getusertickets(db,usertoken)
-
- -- todo: update accessed
- -- todo: get less fields
- -- maybe only data for status changed (hard to check)
-
- local records, keys = db.execute {
- template = template,
- variables = {
- basename = db.basename,
- usertoken = usertoken,
- rubish = s_rubish,
- },
- }
-
- db.unpackdata(records)
-
- return records
-
-end
-
-local template =[[
- UPDATE
- %basename%
- SET
- `status` = %deleted%
- WHERE
- `usertoken` = '%usertoken%' ;
-]]
-
-function tickets.removeusertickets(db,usertoken)
-
- db.execute {
- template = template,
- variables = {
- basename = db.basename,
- usertoken = usertoken,
- deleted = s_deleted,
- },
- }
-
- if trace_sql then
- report("removed: usertoken %s",usertoken)
- end
-
-end
+if not modules then modules = { } end modules ['util-sql-tickets'] = { + version = 1.001, + comment = "companion to lmx-*", + author = "Hans Hagen, PRAGMA-ADE, Hasselt NL", + copyright = "PRAGMA ADE / ConTeXt Development Team", + license = "see context related readme files" +} + +-- TODO: MAKE SOME INTO STORED PROCUDURES + +-- This is experimental code and currently part of the base installation simply +-- because it's easier to distribute this way. Eventually it will be documented +-- and the related scripts will show up as well. + +local tonumber = tonumber +local format = string.format +local ostime, uuid, osfulltime = os.time, os.uuid, os.fulltime +local random = math.random +local concat = table.concat + +local sql = utilities.sql +local tickets = { } +sql.tickets = tickets + +local trace_sql = false trackers.register("sql.tickets.trace", function(v) trace_sql = v end) +local report = logs.reporter("sql","tickets") + +local serialize = sql.serialize +local deserialize = sql.deserialize +local execute = sql.execute + +tickets.newtoken = sql.tokens.new + +-- Beware as an index can be a string or a number, we will create +-- a combination of hash and index. + +local statustags = { [0] = + "unknown", + "pending", + "busy", + "finished", + "dependent", -- same token but different subtoken (so we only need to find the first) + "reserved-1", + "reserved-2", + "error", + "deleted", +} + +local status = table.swapped(statustags) +tickets.status = status +tickets.statustags = statustags + +local s_unknown = status.unknown +local s_pending = status.pending +local s_busy = status.busy +local s_finished = status.finished +local s_dependent = status.dependent +local s_error = status.error +local s_deleted = status.deleted + +local s_rubish = s_error -- and higher + +local function checkeddb(presets,datatable) + return sql.usedatabase(presets,datatable or presets.datatable or "tickets") +end + +tickets.usedb = checkeddb + +local template =[[ + CREATE TABLE IF NOT EXISTS %basename% ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `token` varchar(50) NOT NULL, + `subtoken` INT(11) NOT NULL, + `created` int(11) NOT NULL, + `accessed` int(11) NOT NULL, + `category` int(11) NOT NULL, + `status` int(11) NOT NULL, + `usertoken` varchar(50) NOT NULL, + `data` longtext NOT NULL, + `comment` longtext NOT NULL, + + PRIMARY KEY (`id`), + UNIQUE INDEX `id_unique_index` (`id` ASC), + KEY `token_unique_key` (`token`) + ) + DEFAULT CHARSET = utf8 ; +]] + +function tickets.createdb(presets,datatable) + local db = checkeddb(presets,datatable) + local data, keys = db.execute { + template = template, + variables = { + basename = db.basename, + }, + } + + report("datatable %a created in %a",db.name,db.base) + + return db + +end + +local template =[[ + DROP TABLE IF EXISTS %basename% ; +]] + +function tickets.deletedb(presets,datatable) + + local db = checkeddb(presets,datatable) + + local data, keys = db.execute { + template = template, + variables = { + basename = db.basename, + }, + } + + report("datatable %a removed in %a",db.name,db.base) + +end + +local template_push =[[ + INSERT INTO %basename% ( + `token`, + `subtoken`, + `created`, + `accessed`, + `status`, + `category`, + `usertoken`, + `data`, + `comment` + ) VALUES ( + '%token%', + %subtoken%, + %time%, + %time%, + %status%, + %category%, + '%usertoken%', + '%[data]%', + '%[comment]%' + ) ; +]] + +local template_fetch =[[ + SELECT + * + FROM + %basename% + WHERE + `token` = '%token%' + AND + `subtoken` = '%subtoken%' + ; +]] + +function tickets.create(db,ticket) + + -- We assume a unique token .. if not we're toast anyway. We used to lock and + -- get the last id etc etc but there is no real need for that. + + -- we could check for dependent here but we don't want the lookup + + local token = ticket.token or tickets.newtoken() + local time = ostime() + local status = ticket.status + local category = ticket.category or 0 + local subtoken = ticket.subtoken or 0 + local usertoken = ticket.usertoken or "" + local comment = ticket.comment or "" + + status = not status and subtoken > 1 and s_dependent or s_pending + + local result, message = db.execute { + template = template_push, + variables = { + basename = db.basename, + token = token, + subtoken = subtoken, + time = time, + status = status, + category = category, + usertoken = usertoken, + data = db.serialize(ticket.data or { },"return"), + comment = comment, + }, + } + + -- We could stick to only fetching the id and make the table here + -- but we're not pushing that many tickets so we can as well follow + -- the lazy approach and fetch the whole. + + local result, message = db.execute { + template = template_fetch, + variables = { + basename = db.basename, + token = token, + subtoken = subtoken, + }, + } + + if result and #result > 0 then + if trace_sql then + report("created: %s at %s",token,osfulltime(time)) + end + return result[1] + else + report("failed: %s at %s",token,osfulltime(time)) + end + +end + +local template =[[ + UPDATE + %basename% + SET + `data` = '%[data]%', + `status` = %status%, + `accessed` = %time% + WHERE + `id` = %id% ; +]] + +function tickets.save(db,ticket) + + local time = ostime() + local data = db.serialize(ticket.data or { },"return") + local status = ticket.status or s_error + +-- print("SETTING") +-- inspect(data) + + ticket.status = status + ticket.accessed = time + + db.execute { + template = template, + variables = { + basename = db.basename, + id = ticket.id, + time = ostime(), + status = status, + data = data, + }, + } + + if trace_sql then + report("saved: id %s, time %s",id,osfulltime(time)) + end + + return ticket +end + +local template =[[ + UPDATE + %basename% + SET + `accessed` = %time% + WHERE + `token` = '%token%' ; + + SELECT + * + FROM + %basename% + WHERE + `id` = %id% ; +]] + +function tickets.restore(db,id) + + local record, keys = db.execute { + template = template, + variables = { + basename = db.basename, + id = id, + time = ostime(), + }, + } + + local record = record and record[1] + + if record then + if trace_sql then + report("restored: id %s",id) + end + record.data = db.deserialize(record.data or "") + return record + elseif trace_sql then + report("unknown: id %s",id) + end + +end + +local template =[[ + DELETE FROM + %basename% + WHERE + `id` = %id% ; +]] + +function tickets.remove(db,id) + + db.execute { + template = template, + variables = { + basename = db.basename, + id = id, + }, + } + + if trace_sql then + report("removed: id %s",id) + end + +end + +local template_yes =[[ + SELECT + * + FROM + %basename% + ORDER BY + `id` ; +]] + +local template_nop =[[ + SELECT + `created`, + `usertoken`, + `accessed`, + `status` + FROM + %basename% + ORDER BY + `id` ; +]] + +function tickets.collect(db,nodata) + + local records, keys = db.execute { + template = nodata and template_nop or template_yes, + variables = { + basename = db.basename, + token = token, + }, + } + + if not nodata then + db.unpackdata(records) + end + + if trace_sql then + report("collected: %s tickets",#records) + end + + return records, keys + +end + +-- We aleays keep the last select in the execute so one can have +-- an update afterwards. + +local template =[[ + DELETE FROM + %basename% + WHERE + `accessed` < %time% OR `status` >= %rubish% ; +]] + +local template_cleanup_yes =[[ + SELECT + * + FROM + %basename% + WHERE + `accessed` < %time% + ORDER BY + `id` ; +]] .. template + +local template_cleanup_nop =[[ + SELECT + `accessed`, + `created`, + `accessed`, + `token` + `usertoken` + FROM + %basename% + WHERE + `accessed` < %time% + ORDER BY + `id` ; +]] .. template + +function tickets.cleanupdb(db,delta,nodata) -- maybe delta in db + + local time = delta and (ostime() - delta) or 0 + + local records, keys = db.execute { + template = nodata and template_cleanup_nop or template_cleanup_yes, + variables = { + basename = db.basename, + time = time, + rubish = s_rubish, + }, + } + + if not nodata then + db.unpackdata(records) + end + + if trace_sql then + report("cleaned: %s seconds before %s",delta,osfulltime(time)) + end + + return records, keys + +end + +-- status related functions + +local template =[[ + SELECT + `status` + FROM + %basename% + WHERE + `token` = '%token%' + ORDER BY + `id` + ; +]] + +function tickets.getstatus(db,token) + + local record, keys = db.execute { + template = template, + variables = { + basename = db.basename, + token = token, + }, + } + + local record = record and record[1] + + return record and record.status or s_unknown + +end + +local template =[[ + SELECT + `status` + FROM + %basename% + WHERE + `status` >= %rubish% OR `accessed` < %time% + ORDER BY + `id` + ; +]] + +function tickets.getobsolete(db,delta) + + local time = delta and (ostime() - delta) or 0 + + local records = db.execute { + template = template, + variables = { + basename = db.basename, + time = time, + rubish = s_rubish, + }, + } + + db.unpackdata(records) + + return records + +end + +local template =[[ + SELECT + `id` + FROM + %basename% + WHERE + `status` = %status% + LIMIT + 1 ; +]] + +function tickets.hasstatus(db,status) + + local records = db.execute { + template = template, + variables = { + basename = db.basename, + status = status or s_unknown, + }, + } + + return records and #records > 0 or false + +end + +local template =[[ + UPDATE + %basename% + SET + `status` = %status%, + `accessed` = %time% + WHERE + `id` = %id% ; +]] + +function tickets.setstatus(db,id,status) + + db.execute { + template = template, + variables = { + basename = db.basename, + id = id, + time = ostime(), + status = status or s_error, + }, + } + +end + +local template =[[ + DELETE FROM + %basename% + WHERE + `status` IN (%status%) ; +]] + +function tickets.prunedb(db,status) + + if type(status) == "table" then + status = concat(status,",") + end + + local data, keys = db.execute { + template = template, + variables = { + basename = db.basename, + status = status or s_unknown, + }, + } + + if trace_sql then + report("pruned: status %s removed",status) + end + +end + +-- START TRANSACTION ; ... COMMIT ; +-- LOCK TABLES %basename% WRITE ; ... UNLOCK TABLES ; + +local template_a = [[ + SET + @last_ticket_token = '' ; + UPDATE + %basename% + SET + `token` = (@last_ticket_token := `token`), + `status` = %newstatus%, + `accessed` = %time% + WHERE + `status` = %status% + ORDER BY + `id` + LIMIT + 1 + ; + SELECT + * + FROM + %basename% + WHERE + `token` = @last_ticket_token + ORDER BY + `id` + ; +]] + +local template_b = [[ + SELECT + * + FROM + tickets + WHERE + `status` = %status% + ORDER BY + `id` + LIMIT + 1 + ; +]] + +function tickets.getfirstwithstatus(db,status,newstatus) + + local records + + if type(newstatus) == "number" then -- todo: also accept string + + records = db.execute { + template = template_a, + variables = { + basename = db.basename, + status = status or s_pending, + newstatus = newstatus, + time = ostime(), + }, + } + + + else + + records = db.execute { + template = template_b, + variables = { + basename = db.basename, + status = status or s_pending, + }, + } + + end + + if type(records) == "table" and #records > 0 then + + for i=1,#records do + local record = records[i] + record.data = db.deserialize(record.data or "") + record.status = newstatus or s_busy + end + + return records + + end +end + +-- The next getter assumes that we have a sheduler running so that there is +-- one process in charge of changing the status. + +local template = [[ + SET + @last_ticket_token = '' ; + UPDATE + %basename% + SET + `token` = (@last_ticket_token := `token`), + `status` = %newstatus%, + `accessed` = %time% + WHERE + `status` = %status% + ORDER BY + `id` + LIMIT + 1 + ; + SELECT + @last_ticket_token AS `token` + ; +]] + +function tickets.getfirstinqueue(db,status,newstatus) + + local records = db.execute { + template = template, + variables = { + basename = db.basename, + status = status or s_pending, + newstatus = newstatus or s_busy, + time = ostime(), + }, + } + + local token = type(records) == "table" and #records > 0 and records[1].token + + return token ~= "" and token + +end + +local template =[[ + SELECT + * + FROM + %basename% + WHERE + `token` = '%token%' + ORDER BY + `id` ; +]] + +function tickets.getticketsbytoken(db,token) + + local records, keys = db.execute { + template = template, + variables = { + basename = db.basename, + token = token, + }, + } + + db.unpackdata(records) + + return records + +end + +local template =[[ + SELECT + * + FROM + %basename% + WHERE + `usertoken` = '%usertoken%' AND `status` < %rubish% + ORDER BY + `id` ; +]] + +function tickets.getusertickets(db,usertoken) + + -- todo: update accessed + -- todo: get less fields + -- maybe only data for status changed (hard to check) + + local records, keys = db.execute { + template = template, + variables = { + basename = db.basename, + usertoken = usertoken, + rubish = s_rubish, + }, + } + + db.unpackdata(records) + + return records + +end + +local template =[[ + UPDATE + %basename% + SET + `status` = %deleted% + WHERE + `usertoken` = '%usertoken%' ; +]] + +function tickets.removeusertickets(db,usertoken) + + db.execute { + template = template, + variables = { + basename = db.basename, + usertoken = usertoken, + deleted = s_deleted, + }, + } + + if trace_sql then + report("removed: usertoken %s",usertoken) + end + +end |