2016-01-12 16:26:00

1 files changed, 772 insertions, 0 deletions

diff --git a/tex/context/base/mkiv/util-sql-tickets.lua b/tex/context/base/mkiv/util-sql-tickets.lua
new file mode 100644
index 000000000..5e958299d
--- /dev/null
+++ b/tex/context/base/mkiv/util-sql-tickets.lua
@@ -0,0 +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