summaryrefslogtreecommitdiff
path: root/tex/context/base/util-sql-users.lua
diff options
context:
space:
mode:
Diffstat (limited to 'tex/context/base/util-sql-users.lua')
-rw-r--r--tex/context/base/util-sql-users.lua391
1 files changed, 391 insertions, 0 deletions
diff --git a/tex/context/base/util-sql-users.lua b/tex/context/base/util-sql-users.lua
new file mode 100644
index 000000000..8147f532d
--- /dev/null
+++ b/tex/context/base/util-sql-users.lua
@@ -0,0 +1,391 @@
+if not modules then modules = { } end modules ['util-sql-users'] = {
+ 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"
+}
+
+-- This is experimental code and currently part of the base installation simply
+-- because it's easier to dirtribute this way. Eventually it will be documented
+-- and the related scripts will show up as well.
+
+local sql = require("util-sql")
+local md5 = require("md5")
+
+local format, upper, find, gsub, escapedpattern = string.format, string.upper, string.find, string.gsub, string.escapedpattern
+local sumhexa = md5.sumhexa
+local booleanstring = string.booleanstring
+
+local users = { }
+sql.users = users
+
+local trace_sql = false trackers.register("sql.users.trace", function(v) trace_sql = v end)
+local report = logs.reporter("sql","users")
+
+local function encryptpassword(str)
+ if not str or str == "" then
+ return ""
+ elseif find(str,"^MD5:") then
+ return str
+ else
+ return upper(format("MD5:%s",sumhexa(str)))
+ end
+end
+
+local function cleanuppassword(str)
+ return (gsub(str,"^MD5:",""))
+end
+
+local function samepasswords(one,two)
+ if not one or not two then
+ return false
+ end
+ if not find(one,"^MD5:") then
+ one = encryptpassword(one)
+ end
+ if not find(two,"^MD5:") then
+ two = encryptpassword(two)
+ end
+ return one == two
+end
+
+local function validaddress(address,addresses)
+ if address and addresses and address ~= "" and addresses ~= "" then
+ if find(address,"^" .. escapedpattern(addresses,true)) then -- simple escapes
+ return true, "valid remote address"
+ end
+ return false, "invalid remote address"
+ else
+ return true, "no remote address check"
+ end
+end
+
+
+users.encryptpassword = encryptpassword
+users.cleanuppassword = cleanuppassword
+users.samepasswords = samepasswords
+users.validaddress = validaddress
+
+-- print(users.encryptpassword("test")) -- MD5:098F6BCD4621D373CADE4E832627B4F6
+
+local function checkeddb(presets,datatable)
+ return sql.usedatabase(presets,datatable or presets.datatable or "users")
+end
+
+users.usedb = checkeddb
+
+local groupnames = { }
+local groupnumbers = { }
+
+local function registergroup(name)
+ local n = #groupnames + 1
+ groupnames [n] = name
+ groupnames [tostring(n)] = name
+ groupnames [name] = name
+ groupnumbers[n] = n
+ groupnumbers[tostring(n)] = n
+ groupnumbers[name] = n
+ return n
+end
+
+registergroup("superuser")
+registergroup("administrator")
+registergroup("user")
+registergroup("guest")
+
+users.groupnames = groupnames
+users.groupnumbers = groupnumbers
+
+-- password 'test':
+--
+-- INSERT insert into users (`name`,`password`,`group`,`enabled`) values ('...','MD5:098F6BCD4621D373CADE4E832627B4F6',1,1) ;
+
+local template =[[
+ CREATE TABLE `users` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `name` varchar(80) NOT NULL,
+ `password` varchar(50) DEFAULT NULL,
+ `group` int(11) NOT NULL,
+ `enabled` int(11) DEFAULT '1',
+ `email` varchar(80) DEFAULT NULL,
+ `address` varchar(256) DEFAULT NULL,
+ `data` longtext,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `name_unique` (`name`)
+ ) DEFAULT CHARSET = utf8 ;
+]]
+
+local converter, fields = sql.makeconverter {
+ { name = "id", type = "number" },
+ { name = "name", type = "string" },
+ { name = "password", type = "string" },
+ { name = "group", type = groupnames },
+ { name = "enabled", type = "boolean" },
+ { name = "email", type = "string" },
+ { name = "address", type = "string" },
+ { name = "data", type = "deserialize" },
+}
+
+function users.createdb(presets,datatable)
+
+ local db = checkeddb(presets,datatable)
+
+ db.execute {
+ template = template,
+ variables = {
+ basename = db.basename,
+ },
+ }
+
+ report("datatable %q created in %q",db.name,db.base)
+
+ return db
+
+end
+
+local template =[[
+ SELECT
+ %fields%
+ FROM
+ %basename%
+ WHERE
+ `name` = '%name%'
+ AND
+ `password` = '%password%'
+ ;
+]]
+
+local template =[[
+ SELECT
+ %fields%
+ FROM
+ %basename%
+ WHERE
+ `name` = '%name%'
+ ;
+]]
+
+function users.valid(db,username,password,address)
+
+ local data = db.execute {
+ template = template,
+ converter = converter,
+ variables = {
+ basename = db.basename,
+ fields = fields,
+ name = username,
+ },
+ }
+
+ local data = data and data[1]
+
+ if not data then
+ return false, "unknown user"
+ elseif not data.enabled then
+ return false, "disabled user"
+ elseif data.password ~= encryptpassword(password) then
+ return false, "wrong password"
+ elseif not validaddress(address,data.address) then
+ return false, "invalid address"
+ else
+ data.password = nil
+ return data, "okay"
+ end
+
+end
+
+local template =[[
+ INSERT INTO %basename% (
+ `name`,
+ `password`,
+ `group`,
+ `enabled`,
+ `email`,
+ `address`,
+ `data`
+ ) VALUES (
+ '%name%',
+ '%password%',
+ '%group%',
+ '%enabled%',
+ '%email%',
+ '%address%',
+ '%[data]%'
+ ) ;
+]]
+
+function users.add(db,specification)
+
+ local name = specification.username or specification.name
+
+ if not name or name == "" then
+ return
+ end
+
+ local data = specification.data
+
+ db.execute {
+ template = template,
+ variables = {
+ basename = db.basename,
+ name = name,
+ password = encryptpassword(specification.password or ""),
+ group = groupnumbers[specification.group] or groupnumbers.guest,
+ enabled = booleanstring(specification.enabled) and "1" or "0",
+ email = specification.email,
+ address = specification.address,
+ data = type(data) == "table" and db.serialize(data,"return") or "",
+ },
+ }
+
+end
+
+local template =[[
+ SELECT
+ %fields%
+ FROM
+ %basename%
+ WHERE
+ `name` = '%name%' ;
+]]
+
+function users.getbyname(db,name)
+
+ local data = db.execute {
+ template = template,
+ converter = converter,
+ variables = {
+ basename = db.basename,
+ fields = fields,
+ name = name,
+ },
+ }
+
+ return data and data[1] or nil
+
+end
+
+local template =[[
+ SELECT
+ %fields%
+ FROM
+ %basename%
+ WHERE
+ `id` = '%id%' ;
+]]
+
+local function getbyid(db,id)
+
+ local data = db.execute {
+ template = template,
+ converter = converter,
+ variables = {
+ basename = db.basename,
+ fields = fields,
+ id = id,
+ },
+ }
+
+ return data and data[1] or nil
+
+end
+
+users.getbyid = getbyid
+
+local template =[[
+ UPDATE
+ %basename%
+ SET
+ `password` = '%password%',
+ `group` = '%group%',
+ `enabled` = '%enabled%',
+ `email` = '%email%',
+ `address` = '%address%',
+ `data` = '%[data]%'
+ WHERE
+ `id` = '%id%'
+ ;
+]]
+
+function users.save(db,id,specification)
+
+ if not tonumber(id) then
+ return
+ end
+
+ local user = getbyid(db,id)
+
+ if user.id ~= id then
+ return
+ end
+
+ local password = specification.password == nil and user.password or specification.password
+ local group = specification.group == nil and user.group or specification.group
+ local enabled = specification.enabled == nil and user.enabled or specification.enabled
+ local email = specification.email == nil and user.email or specification.email
+ local address = specification.address == nil and user.address or specification.address
+ local data = specification.data == nil and user.data or specification.data
+
+-- table.print(data)
+
+ db.execute {
+ template = template,
+ variables = {
+ basename = db.basename,
+ id = id,
+ password = encryptpassword(password),
+ group = groupnumbers[group],
+ enabled = booleanstring(enabled) and "1" or "0",
+ email = email,
+ address = address,
+ data = type(data) == "table" and db.serialize(data,"return") or "",
+ },
+ }
+
+ return getbyid(db,id)
+
+end
+
+local template =[[
+ DELETE FROM
+ %basename%
+ WHERE
+ `id` = '%id%' ;
+]]
+
+function users.remove(db,id)
+
+ db.execute {
+ template = template,
+ variables = {
+ basename = db.basename,
+ id = id,
+ },
+ }
+
+end
+
+local template =[[
+ SELECT
+ %fields%
+ FROM
+ %basename%
+ ORDER BY
+ `name` ;
+]]
+
+function users.collect(db) -- maybe also an id/name only variant
+
+ local records, keys = db.execute {
+ template = template,
+ converter = converter,
+ variables = {
+ basename = db.basename,
+ fields = fields,
+ },
+ }
+
+ return records, keys
+
+end
generated by cgit v1.2.3 (git 2.25.1) at 2024-05-20 21:19:09 +0000