path: root/lib/dblib.lua

--[[

/*
 *  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 = "float", length = 11, decimal = 8, default = 42 },
    id = { options = "pri,auto" },
}

db = luadb.opendb(host, login, password, database)
t = db:opentable("foobar", ddl)
t:empty()

x = t:insert{ field1 = "test1" }              -- returns 1
x = t:insert{ field1 = "test2" }              -- returns 2
x = t:insert{ field1 = "test3" }              -- returns 3
x = t:insert{ field1 = "test4", field2 = 18 } -- returns 4
x = t:insert{ field1 = "test5", field2 = 20 } -- returns 5

r = t:restricter()

t:restrict(r:EQ("id", 3))             -- WHERE `id` = "3"
x = t:update{ field1 = "3tset" }      -- returns 1
t:restrict(r:EQ("field2", 42))        -- WHERE `field2` = "42"
x = t:update{ field2 = 28 }           -- returns 3
t:restrict(r:EQ("id", 4))             -- WHERE `id` = "4"
x = t:delete()                        -- returns 1
t:restrict(r:AND(r:LIKE("field1", "test%"), r:NEQ("field2", 20)))
                                      -- WHERE `field` LIKE "test%" AND `field2` != 20
for r in t:gselect{ "field1" } do print(r.field1) end
                                      -- prints the strings "test1" and "test2"
t:restrict(r:LIKE("field1", "test%"))
print(t:count())                      -- prints "3"
t:restrict()                          -- WHERE 1
t:select()
print(t:numrows())                    -- prints "4"
print(t:numfields())                  -- prints "3"
row = t:nextrow()
print(row.field1)                     -- prints "test1"

]]--

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() == "TIMESTAMP" then
            return "TIMESTAMP"
        elseif ttype:upper() == "FLOAT" then
            return "FLOAT"
        elseif ttype:upper() == "TEXT" then
            return "TEXT"
        else
            error("Unknow data type in ddl - " .. k .. ": " .. ttype:upper())
        end            
    end,

    generate_fields = function(db, ddl)
        local k, v
        local r, alters, keys = {}, {}, {}
        
        for k, v in pairs(ddl) do
            local ttype
            r[k] = ""
            alters[k] = {}
            
            ttype = _luadb.get_canon_type(v.type, k)
            r[k] = ttype
            
            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
                table.insert(keys, k)
                keys[k] = true
            end
            
            if options.UNIQ then
                table.insert(alters[k], "ADD UNIQUE (`@fieldname@`)")
            end

            if options.AUTO then
                r[k] = r[k] .. " AUTO_INCREMENT"
            end
            
            if v.default then
                if type(v.default) ~= "string" and type(v.default) ~= "number" then
                    error("Default value for field " .. k .. " isn't usable.")
                end
                r[k] = r[k] .. ' DEFAULT "' .. db.sql_escape(v.default) .. '"'
            elseif ttype == "TIMESTAMP" then
                r[k] = r[k] .. ' DEFAULT CURRENT_TIMESTAMP'
            end
        end
        return r, alters, keys
    end,
    
    opentable = function(db, tablename, ddl)
        local fields, alters, keys = _luadb.generate_fields(db, ddl)
        local tname = "`" .. db._.prefix .. db.sql_escape(tablename) .. "`"
        local operations = 0
        
        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
                operations = -1
                
                for k, v in pairs(fields) do
                    if not first then
                        q = q .. ", "
                    else
                        first = false
                    end
                    q = q .. "`" .. db.sql_escape(k) .. "` " .. v
                end
                
                if #keys ~= 0 then
                    q = q .. ", PRIMARY KEY ("
                    first = true
                    for k, v in ipairs(keys) do
                        if not first then
                            q = q .. ", "
                        else
                            first = false
                        end
                        q = q .. "`" .. db.sql_escape(v) .. "`"
                    end
                    q = q .. ")"
                end
                
                q = q .. ") ENGINE=InnoDB;"
                
                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
                    operations = operations + 1
                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
                                operations = operations + 1
                            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
                    operations = operations + 1
                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 .. " " .. v.stmt .. ";"
                if db:SafeQuery(q) ~= 0 then
                    error("Error altering table " .. tname .. ": " .. db:ErrNO() .. " - " .. db:Error() .. " - query run = " .. q)
                end
                operations = operations + 1
            end
        end
        
        return {
            insert = _luadb.insert,
            delete = _luadb.delete,
            select = _luadb.select,
            gselect = _luadb.gselect,
            update = _luadb.update,
            pinsert = function(...) return pcall(_luadb.insert, ...) end,
            pdelete = function(...) return pcall(_luadb.delete, ...) end,
            pselect = function(...) return pcall(_luadb.select, ...) end,
            pgselect = function(...) return pcall(_luadb.gselect, ...) end,
            pupdate = function(...) return pcall(_luadb.update, ...) end,
            numrows = _luadb.numrows,
            numfields = _luadb.numfields,
            nextrow = _luadb.nextrow,
            count = _luadb.count,
            restrict = _luadb.restrict,
            restricter = _luadb.restricter,
            empty = _luadb.empty,
            _ = {
                tablename = tablename,
                tname = tname,
                db = db,
                ddl = ddl,
                conditions = "1",
            },
        }, operations
    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
            desctype = field.Type:match "(%w+)"
        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 = -1 } )
        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 = -2 } )
        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
        return t._.db._.conn:NumAffectedRows()
    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 not 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" or type(v) == "number" 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
        return t._.db._.conn:NumAffectedRows()
    end,
    
    iselect = function(bypass, t, fields, foreign, options)
        local stmt = "SELECT "
        local first = true
        local k, v

        if fields == nil then
            stmt = stmt .. "*"
        else        
            for k, v in pairs(fields) do
                local is_foreign = false
                if foreign then
                    local _, f
                    for _, f in pairs(foreign) do
                        if f._.ddl[v] then
                            is_foreign = true
                        end
                    end
                end
                if not bypass and not is_foreign and not 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
                if bypass then
		    stmt = stmt .. v
                else
		    stmt = stmt .. "`" .. v .. "`"
		end
            end
        end
        
        if foreign then
            local foreign_conds = "1"
            stmt = stmt .. " FROM " .. t._.tname
            local found_table, fname, f
            for k, v in pairs(foreign) do
                stmt = stmt .. ", " .. v._.tname
                found_table = false
                for fname, f in pairs(t._.ddl) do
                    if f.foreign and f.foreign.tablename == v._.tablename then
                        if not v._.ddl[f.foreign.fieldname] then
                            error("Foreign key points to an unknown field in table: " .. f.foreign.tablename .. "." .. f.foreign.fieldname)
                        end
                        foreign_conds = foreign_conds .. " AND " .. t._.tname .. ".`" .. fname .. "`=" .. v._.tname .. ".`" .. f.foreign.fieldname .. "`"
                        found_table = true
                    end
                end
                if not found_table then
                    error("Foreign table " .. v._.tablename .. " specified in request didn't match any of the foreign key of table " .. t._.tablename)
                end
            end
            stmt = stmt .. " WHERE (" .. t._.conditions .. ") AND (" .. foreign_conds .. ")"
        else
            stmt = stmt .. " FROM " .. t._.tname .. " WHERE " .. t._.conditions
        end
        
        if options then
            if options.order then
                local direction = "ASC"
                if type(options.order) ~= "table" then
                    error("Wrong type for options.order; should be a table.")
                end
                if type(options.order.by) ~= "string" then
                    error("The order option has to contain one valid 'by' field.")
                end
                if options.order.dir then
                    if type(options.order.dir) ~= "string" then
                        error("options.order.dir has to be a string")
                    end
                    if options.order.dir:upper() == "ASC" then
                        direction = "ASC"
                    elseif options.order.dir:upper() == "DESC" then
                        direction = "DESC"
                    else
                        error("options.order.dir has to be either 'asc' or 'desc'")
                    end
                end
                stmt = stmt .. " ORDER BY `" .. options.order.by .. "` " .. direction
            end
            if options.limit then
                if type(options.limit) ~= "table" then
                    error("Wrong type for options.limit; should be a table.")
                end
                local start, size = 0, 30
                if options.limit.start then
                    if type(options.limit.start) ~= "number" then
                        error("options.limit.start has to be a number")
                    end
                    start = options.limit.start
                end
                if options.limit.size then
                    if type(options.limit.size) ~= "number" then
                        error("options.limit.size has to be a number")
                    end
                    size = options.limit.size
                end
                stmt = stmt .. " LIMIT " .. start .. "," .. size
            end
        end
        
        stmt = stmt .. ";"
        
        if t._.db:SafeQuery(stmt) ~= 0 then
            error("Error selecting row(s) inside table " .. t._.tablename .. ": " .. t._.db:Error())
        end
        
        return t:numrows(), t:numfields()
    end,
    
    empty = function(t, fields)
        if t._.db:SafeQuery("TRUNCATE TABLE " .. t._.tname) ~= 0 then
            error("Error emptying table " .. t._.tablename .. ": " .. t._.db:Error())
        end
    end,
    
    select = function(...)
        return _luadb.iselect(false, ...)
    end,
    
    numrows = function(t)
	return t._.db._.conn:NumRows()
    end,
    
    numfields = function(t)
	return t._.db._.conn:NumFields()
    end,
    
    count = function(t)
	_luadb.iselect(true, t, {"COUNT(*) AS count"})
	return t:nextrow().count
    end,
    
    gselect = function(t, ...)
	_luadb.iselect(false, t, ...)
        return t.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 r._.tbl._.tname .. ".`" .. field .. "` " .. op .. ' "' .. r._.tbl._.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
            return r
        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,

    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,
    
}

luadb = {
    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,
}