diff options
| -rw-r--r-- | lib/dblib.lua | 519 |
1 files changed, 519 insertions, 0 deletions
diff --git a/lib/dblib.lua b/lib/dblib.lua new file mode 100644 index 0000000..6469d9c --- /dev/null +++ b/lib/dblib.lua @@ -0,0 +1,519 @@ +--[[ + +/* + * Baltisot + * Copyright (C) 1999-2008 Nicolas "Pixel" Noble + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, write to the Free Software + * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA + */ + +]]-- + +-- dblib - database object layer +-- Currently built-in with MySQL - TODO: support some abstraction layer with drivers. + +--[[ + +ddl = { + field1 = { type = "varchar", length = 32 }, + field2 = { type = "int", length = 11, decimal = 8, default = 42 }, + field3 = { options = "pri,auto" }, +} + +]]-- + +--local _luadb +_luadb = { + get_options = function(str) + local s = split(str, ",") + local r = {} + for k, v in pairs(s) do + r[v:upper()] = true + end + + return r + end, + + get_canon_type = function(ttype, k) + if ttype == nil then + return "INT" + elseif type(ttype) ~= "string" then + error("Wrong data in ddl - " .. k .. ".type isn't a string.") + elseif ttype:upper() == "VARCHAR" then + return "VARCHAR" + elseif ttype:upper() == "INT" then + return "INT" + elseif ttype:upper() == "BLOB" then + return "BLOB" + elseif ttype:upper() == "DATETIME" then + return "DATETIME" + elseif ttype:upper() == "NUMBER" then + return "NUMBER" + else + error("Unknow data type in ddl - " .. k .. ": " .. ttype:upper()) + end + end, + + generate_fields = function(ddl) + local k, v + local r, r2 = {}, {} + + for k, v in pairs(ddl) do + r[k] = "" + r2[k] = {} + + r[k] = _luadb.get_canon_type(v.type, k) + + if v.length == nil then + -- do nothing + elseif type(v.length) ~= "number" then + error("Wrong data in ddl - " .. k .. ".length isn't a number.") + elseif v.decimal == nil then + r[k] = r[k] .. "(" .. v.length .. ")" + elseif type(v.decimal) ~= "number" then + error("Wrong data in ddl - " .. k .. ".decimal isn't a number.") + else + r[k] = r[k] .. "(" .. v.length .. "," .. v.decimal .. ")" + end + + if v.options ~= nil and type(v.options) ~= "string" then + error("Wrong data in ddl - " .. k .. ".options isn't a string.") + end + + local options = v.options and _luadb.get_options(v.options) or {} + + if options.NULL then + r[k] = r[k] .. " NULL" + else + r[k] = r[k] .. " NOT NULL" + end + + if options.PRI then + r[k] = r[k] .. " PRIMARY KEY" + end + + if options.UNIQ then + table.insert(r2[k], "ADD UNIQUE (`@fieldname@`)") + end + + if options.AUTO then + r[k] = r[k] .. " AUTO_INCREMENT" + end + end + return r, r2 + end, + + opentable = function(db, tablename, ddl) + local fields, alters = _luadb.generate_fields(ddl) + local tname = "`" .. db._.prefix .. db.sql_escape(tablename) .. "`" + + if db:SafeQuery("DESC " .. tname) ~= 0 then + -- table doesn't exist, create it + if db._.conn:ErrNO() == 1146 then + local q = "CREATE TABLE " .. tname .. " (" + local k, v, first + first = true + + for k, v in pairs(fields) do + if not first then + q = q .. ", " + end + q = q .. "`" .. db.sql_escape(k) .. "` " .. v + end + + q = q .. ");" + + if db:SafeQuery(q) ~= 0 then + error("Error creating table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + end + + for k, v in pairs(alters) do + local _, stmt + for _, stmt in pairs(v) do + q = "ALTER TABLE " .. tname .. " " .. string.gsub(stmt, "@fieldname@", db.sql_escape(k)) + if db:SafeQuery(q) ~= 0 then + error("Error altering table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + db:SafeQuery("DROP TABLE " .. tname ";") + end + end + end + else + error("Error getting description of table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error()) + end + else + -- table exists, let's check it. + local dfields = {} + local i + local any_common = false + + for i = 1, db._.conn:NumRows() do + dfields[i] = db._.conn:FetchRow() + if fields[dfields[i].Field] then + any_common = true + end + end + + if not any_common then + -- strictly no field in common; drop the table and recursively call opentable in order to proceed with a createtable instead. + q = "DROP TABLE " .. tname .. ";" + if db:SafeQuery(q) ~= 0 then + error("Error dropping table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + end + return db:opentable(tablename, ddl) + end + + local d, k, v = {} + + for k, v in pairs(dfields) do + d[v.Field] = v + if not fields[v.Field] then + q = "ALTER TABLE " .. tname .. " DROP COLUMN `" .. db.sql_escape(v.Field) .. "`;" + if db:SafeQuery(q) ~= 0 then + error("Error altering table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + end + end + end + dfields, d = d, nil + + local deffered_alters = {} + + for k, v in pairs(fields) do + q = nil + if not dfields[k] then + q = "ALTER TABLE " .. tname .. " ADD `" .. db.sql_escape(k) .. "` " .. v .. ";" + else + local identicals, alters = _luadb.compare_fields(ddl, dfields[k]) + if not identicals then + q = "ALTER TABLE " .. tname .. " MODIFY `" .. db.sql_escape(k) .. "` " .. v .. ";" + local _, a + for _, a in pairs(alters) do + a.stmt = string.gsub(a.stmt, "@fieldname@", db.sql_escape(k)) + if a.pri < 0 then + table.insert(deffered_alters, a) + else + q = "ALTER TABLE " .. tname .. " " .. a.stmt .. ";" + if db:SafeQuery(q) ~= 0 then + error("Error altering table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + end + end + end + end + end + if q then + if db:SafeQuery(q) ~= 0 then + error("Error altering table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + end + end + end + + table.sort(deffered_alters, function(a, b) return a.pri < b.pri end) + + for k, v in pairs(deffered_alters) do + q = "ALTER TABLE " .. tname .. " " .. a.stmt .. ";" + if db:SafeQuery(q) ~= 0 then + error("Error altering table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q) + end + end + end + + return { + insert = _luadb.insert, + delete = _luadb.delete, + restrict = _luadb.restrict, + restricter = _luadb.restricter, + _ = { + tablename = tablename, + tname = tname, + db = db, + ddl = ddl, + conditions = "1", + }, + } + end, + + compare_fields = function(ddl, field) + local f = field.Field + local identical = true + local alters = {} + if type(ddl[f]) ~= "table" then + error("Internal issue with the DDL: key " .. f .. " isn't a table.") + end + -- check base type first. + local ddltype = _luadb.get_canon_type(ddl[f].type, f) + local desctype, desclength, descdecim = field.Type:match "(%w+)%((%d+)%.(%d+)%)" + if desctype == nil then + desctype, desclength = field.Type:match "(%w+)%((%d+)%)" + end + if desctype == nil then + error("Error parsing type string from database: " .. f .. ": " .. field.Type) + end + desctype = _luadb.get_canon_type(desctype, f) + + if ddltype ~= desctype then + identical = false + end + + if field.length and field.length ~= desclength then + identical = false + end + + if field.decimal and field.decimal ~= descdecim then + identical = false + end + + local options = ddl[f].options and _luadb.get_options(ddl[f].options) or {} + + if options.PRI and field.Key ~= "PRI" then + -- needs to add a primary key here, with a specific ALTER TABLE statement + identical = false + table.insert(alters, { stmt = "ADD PRIMARY KEY(@fieldname@)", pri = -2 } ) + end + + if not options.PRI and field.Key == "PRI" then + -- needs to drop the primary key here. + identical = false + table.insert(alters, { stmt = "DROP PRIMARY KEY", pri = -1 } ) + end + + if options.UNI and field.Key ~= "UNI" then + -- needs to add a unique index + identical = false + table.insert(alters, { stmt = "ADD UNIQUE(@fieldname@)", pri = 0 } ) + end + + if not options.UNI and field.Key == "UNI" then + -- needs to drop the unique index + identical = false + table.insert(alters, { stmt = "DROP INDEX @fieldname@", pri = 0 } ) + end + + if not options.AUTO and field.Extra == "auto_increment" then + identical = false + end + + if options.AUTO and field.Extra ~= "auto_increment" then + identical = false + end + + return identical, alters + end, + + insert = function(t, data) + local k, v, stmt, stmt2 + local first = true + + stmt = "INSERT INTO " .. t._.tname .. " (" + stmt2 = ") VALUES (" + + for k, v in pairs(data) do + if not t._.ddl[k] then + error("Trying to address a field which doesn't exist: " .. k .. " - inside of table " .. t._.tablename) + end + if not first then + stmt = stmt .. ", " + stmt2 = stmt2 .. ", " + else + first = false + end + stmt = stmt .. "`" .. k .. "`" + stmt2 = stmt2 .. "'" .. v .. "'" + end + + if t._.db:SafeQuery(stmt .. stmt2 .. ");") ~= 0 then + error("Error inserting a row inside table " .. t._.tablename .. ": " .. t._.db:Error()) + end + + return t._.db._.conn:InsertId() + end, + + delete = function(t) + local stmt = "DELETE FROM " .. t._.tname .. " WHERE " .. t._.conditions .. ";" + + if t._.db:SafeQuery(stmt) ~= 0 then + error("Error deleting row(s) inside table " .. t._.tablename .. ": " .. t._.db:Error()) + end + end, + + update = function(t, data) + local stmt = "UPDATE " .. t._.tname .. " SET " + local first = true + local k, v + + for k, v in pairs(data) do + if t._.ddl[k] then + error("Trying to update a field which doesn't exist: " .. k .. " - inside of table " .. t._.tablename) + end + if not first then + stmt = stmt .. ", " + else + first = false + end + stmt = stmt .. "`" .. k .. "`=" + if type(v) == "string" then + stmt = stmt .. '"' .. t._.db.sql_escape(v) .. '"' + else + error("Complex UPDATE queries are not supported yet.") + end + end + + stmt = stmt .. " WHERE " .. t._.conditions + + if t._.db:SafeQuery(stmt) ~= 0 then + error("Error updating row(s) inside table " .. t._.tablename .. ": " .. t._.db:Error()) + end + end, + + select = function(t, fields) + local stmt = "SELECT " + local first = true + local k, v + + for k, v in pairs(fields) do + if t._.ddl[v] then + error("Trying to select a field which doesn't exist: " .. k .. " - inside of table " .. t._.tablename) + end + if not first then + stmt = stmt .. ", " + else + first = false + end + stmt = stmt .. "`" .. v .. "`" + end + + stmt = stmt .. " FROM " .. t._.tname .. " WHERE " .. t._.conditions + + if t._.db:SafeQuery(stmt) ~= 0 then + error("Error selecting row(s) inside table " .. t._.tablename .. ": " .. t._.db:Error()) + end + + return _luadb.nextrow, t + end, + + nextrow = function(t) + return t._.db._.conn:FetchRow() + end, + + _restricts = { + -- All the rendering is done on-the-fly. + -- this is the leaf function; thus this is the only one which ough to get the sql_escape. + dop = function(r, field, expr, op) + if not r._.tbl._.ddl[field] then + error("Trying to restrict on a field which doesn't exist: " .. field .. " - inside of table " .. r._.tbl._.tablename) + end + + if type(expr) == "string" or type(expr) == "number" then + return "`" .. field .. "` " .. op .. ' "' .. t._.db.sql_escape(expr) .. '"' + elseif type(expr) == "table" then + error("Complex queries not handled for now") + else + error("Wrong type for expression in operator: " .. type(expr)) + end + end, + + varops = function(r, op, ...) + local exprs, first, r, k, v = {...}, true + + for k, v in pairs(exprs) do + if not first then + r = r .. " " .. op .. " " + end + r = r .. v + first = false + end + end, + }, + + restricts = { + NOT = function(r, expr) + return "NOT " .. expr + end, + + AND = function(r, ...) return _luadb._restricts.varops(r, "AND", ...) end, + OR = function(r, ...) return _luadb._restricts.varops(r, "OR", ...) end, + + EQ = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, "=") end, + NEQ = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, "!=") end, + GT = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, ">") end, + LT = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, "<") end, + GE = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, ">=") end, + LE = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, "<=") end, + LIKE = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, "LIKE") end, + IN = function(r, field, expr) return _luadb._restricts.dop(r, field, expr, "IN") end, + }, + + restricter = function(t) + local r, k, v = { _ = { tbl = t } } + + for k, v in pairs(_luadb.restricts) do + r[k] = v + end + + return r + end, + + restrict = function(t, conditions) + -- all in all, "conditions" can just be a direct string. Let the programmer do what he wants, after all. + if conditions == nil or conditions == "" then conditions = "1" end + t._.conditions = conditions + end, +} + +luadb = { + SafeQuery = function(db, str) + local r = db._.conn:Query(str) + if luadb.debugmode then + print(str) + end + + if r ~= 0 and db._.conn:ErrNO() == 2006 then -- disconnected + db._.conn = db:opendb() + r = db._.conn:Query(str) + end + + return r + end, + + opendb = function(id, user, password, base, prefix) + local db + +-- if user == nil and password == nil and base == nil then + -- get into a registry to fetch "id" +-- else +-- end + if type(id) == "table" and type(id._) == "table" then + user = id._.user + password = id._.password + base = id._.base + prefix = id._.prefix + id = id._.id + end + db = SQLConnection(id, user, password, base) + return { + opentable = _luadb.opentable, + SafeQuery = luadb.SafeQuery, + sql_escape = sql_escape, + ErrNO = function(db) return db._.conn:ErrNO() end, + Error = function(db) return db._.conn:Error() end, + _ = { + conn = db, + id = id, + user = user, + password = password, + base = base, + prefix = sql_escape(prefix or ""), + }, + } + end, +} |