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 5e958299d..65eb69bae 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
|