easyDB by WeBuLtRa

--------------### easy Database (eDB) ###------------
require"luasql.mysql";
eDB = {};
eDB.env = luasql.mysql();

--------------### GLOBALS ###------------
eDB.Version = "1.0.0.1";
eDB.ABOUT = "easyDB is a approach to improve the luasql.mysql functions making it easier for the end user";
eDB.database = "";
eDB.con = {user="", pwd="", host="localhost", port=3306};

--------------### End user set up functions ###------------
eDB.connect = function(database, user, pwd, host, port)
    database = database or "mysql";
    eDB.db = eDB.env:connect(database, user, pwd, host, port);
    if eDB.db then
        eDB.con = {user=user, pwd=pwd, host=host, port=port};
        eDB.database = "`"..database.."`";
        return true
    else
        return nil, "Couldn't start database connection with localhost\r\nu:root without pwd";
    end
end

eDB.setDatabase = function(data, user, pwd, host, port)
    if eDB.db then
        eDB.db:close();
    end
    user = user or eDB.con.user;
    pwd = pwd or eDB.con.pwd;
    host = host or eDB.con.host;
    port = port or eDB.con.port
    eDB.db = eDB.env:connect(data, user, pwd, host, port);
    eDB.database = "`"..data.."`";
end
--
--------------### End user functions ###------------
--
eDB.execute = function(query)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    if type(query) == "string" then
        return eDB.db:execute(query);
    else
        return nil, "Invalid query";
    end
end

eDB.fetch = function(c,l, k)
    local l = l or {};
    local k = (k==nil) and "a" or (k=="n") and "n" or "a";
    return function (l)
        return c:fetch(l, k)
    end,l,c, true
end

eDB.listDatabases = function()
    return eDB.easy("show databases");
end

eDB.listTables = function()
    if eDB.database and eDB.database ~= "" then
        return eDB.easy("show tables FROM "..eDB.database);
    else
        return nil, "Please select a valid database";
    end
end

eDB.listFields = function(tbl)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    tbl = tbl or "";
    if eDB.database and eDB.database ~= "" then
        if tbl ~= "" then
            local t = {};
            local cur, e = eDB.db:execute("show fields FROM "..eDB.database..".`"..tbl.."`");
            if cur then
                for row in eDB.fetch(cur, {}, "n") do
                    table.insert(t, {Field = row[1], Type = row[2], Null = row[3], Key = row[4], Default = row[5], Extra = row[6]});
                end
                cur:close();
            end
            return t, e;
        else
            return nil, "Please select a vaild table";
        end
    else
        return nil, "You haven't selected a valid database.\nPlease use eDB.setDB()";
    end
end
--
--------------### Smart functions ###------------
--
eDB.finish = function()
    if eDB.db then
        eDB.db:close();
    end
    if eDB.env then
        eDB.env:close();
    end
end
eDB.getFieldById = function(tbname, field, id)
    id = tonumber(id) or 1;
    local c, e = eDB.db:execute("SELECT `"..field.."` FROM "..eDB.database..".`"..tbname.."` WHERE id = "..id);
    if c then
        local t = c:fetch({}, "n");
        c:close();
        return t[1] or "NULL";
    else
        return nil, e;
    end
end
eDB.getLastInsertId = function()
    local a, e = eDB.db:execute("SELECT LAST_INSERT_ID();");
    if a then
        local t = a:fetch({}, "n");
        return t[1] or 0;
    else
        return nil, e;
    end
end
eDB.insert = function(tbl, tblFields, auto_rollback,...)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    local next = arg[1];
    if auto_rollback and not next then
        eDB.db:execute("BEGIN;");
    end
    if (eDB.database == "") then return nil, "You haven't set a valid database" end
    if type(tbl)=="string" then
        local aff, err, ist = 0, nil, false;
        if type(tblFields) == "table" then
            local fields, values = "", "";
            for x, y in pairs(tblFields) do
                if type(y) == "table" then
                    ist = true;
                    local a, e = eDB.insert(tbl, y, auto_rollback, true);
                    if not a then
                        err = (err==nil) and "Error executing query: "..e or err.."\r\nError executing query: "..e;
                        if auto_rollback then
                            return nil, err;
                        else
                            return aff, err;
                        end
                    else
                        aff = aff + a;
                    end
                else
                    local tmp = (type(y) == "number") and y or "'"..eDB.realescape(y).."'";
                    fields = (fields == "") and "`"..x.."`" or fields..", `"..x.."`";
                    values = (values == "") and tmp or values..", "..tmp;
                end
            end
            if ist then
                if auto_rollback then
                    eDB.db:execute("COMMIT;");
                end
                return aff, err;
            else
                local query = string.format("INSERT INTO "..eDB.database..".`"..tbl.."`(%s) VALUES (%s);", fields, values);
                local a, e = eDB.db:execute(query);
                if not a then
                    if auto_rollback then
                        eDB.db:rollback();
                    end
                    return nil, e;
                else
                    return a, query
                end
            end
        else
            return nil, "Invalid second argument";
        end
    else
        return nil, "Invalid first argument";
    end
end
eDB.returnTable = function(query, ret)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    local tb = {};
    local cur,e = eDB.db:execute(query);
    if cur then
        local n = cur:numrows();
        if n == 0 then return nil, "Zero rows returned" end
        for row in eDB.fetch(cur, {}, ret) do
            --?
            local t = {};
            for k,v in pairs(row) do
                t[k] = v;
            end
            table.insert(tb, t);
        end
        cur:close();
        return tb;
    else
        return nil, e;
    end
end
eDB.find = function(kind, props)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    if not kind or not props or type(props) ~= "table" then return nil, "Second argument must be a table"; end
    kind = kind:lower();
    local kinds = {"all", "count", "first", "list"};
    local properties = {};
    properties.From = false;
    properties.Fields = "*";
    properties.Where = "";
    properties.Limit = "";
    properties.GroupBy = "";
    properties.Order = "";
    properties.ret = "a";
    for k, v in pairs(properties) do
        properties[k] = props[k] or v;
    end
    properties.ret = (properties.ret ~= nil) and (properties.ret == "a") and properties.ret or "n" or "n";

    kind = eDB.in_array(kind, kinds);
    if not kind then return false; end
    if not properties.From then
        return nil, "Please submit a valid table to query";
    end
    properties.From = (type(properties.From) == "string") and properties.From or table.concat(properties.From, ",");
    if (type(properties.Where) == "table") then
        local s = "";
        for k, v in pairs(properties.Where) do
            local tmp = (type(v)=="number") and tostring(v) or v:lower();
            if tmp:find("^not in") or tmp:find("^rlike") or tmp:find("^like") or tmp:find("^<>") or tmp:find("^!=") or tmp:find("^in") or tmp:find("^is") then
                s = (s=="") and "`"..k.."` "..v or s.." AND `"..k.."` "..v;
            else
                s = (s=="") and "`"..k.."`='"..v.."'" or s.." AND `"..k.."`='"..v.."'";
            end
        end
        properties.Where = s;
    end
    properties.Where = (properties.Where ~= "") and " WHERE "..properties.Where or "";
    properties.GroupBy = (type(properties.GroupBy) == "string") and properties.GroupBy or table.concat(properties.GroupBy, ",");
    properties.GroupBy = (properties.GroupBy ~= "") and " GROUP BY "..properties.GroupBy or "";

    properties.Order = (type(properties.Order) == "string") and properties.Order or table.concat(properties.Order, ",");
    properties.Order = (properties.Order ~= "") and " ORDER BY "..properties.Order or "";

    properties.Limit = (properties.Limit ~= "") and " LIMIT "..properties.Limit or "";
    local query = "SELECT ";
    if kind == "all" then
        properties.Fields = (type(properties.Fields) == "string") and properties.Fields:gsub("%s*,%s*", ",") or properties.Fields;
        properties.Fields = (type(properties.Fields) == "string") and properties.Fields:gsub(",", "`,`") or table.concat(properties.Fields, "`, `");
        properties.Fields = (properties.Fields~= "*") and "`"..properties.Fields.."`" or "*";
        query = query..properties.Fields.." FROM "..eDB.database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
        return eDB.returnTable(query, properties.ret);
    elseif kind == "count" then
        if (type(properties.Fields) == "string") then
            properties.Fields = "count("..properties.Fields..")";
        else
            return nil, "properties.Fields can't be a table, must be a string";
        end
        query = query..properties.Fields.." FROM "..eDB.database.."."..properties.From..properties.Where..properties.GroupBy;
        local a, e = eDB.returnTable(query, "n");
        if a then
            return a[1][1];
        else
            return a, e;
        end
    elseif kind == "first" then
        properties.Fields = (type(properties.Fields) == "string") and properties.Fields or table.concat(properties.Fields, ", ");
        query = query..properties.Fields.." FROM "..eDB.database.."."..properties.From..properties.Where;
        local t, e = eDB.returnTable(query, properties.ret);
        return (type(t) == "table") and t[1] or t, e;
    else--list
        if type(properties.Fields) == "string" and properties.Fields == "*" then
            local fields, e = eDB.listFields(properties.From);
            if fields then
                local a, b, c = "", "", "";
                for k, v in pairs(fields) do
                    if v.Key == "PRI" then
                        a = v.Field;
                    elseif v.Field == "name" or v.Field == "title" then
                        b = v.Field;
                    elseif v.Type:find("varchar") then
                        c = v.Field;
                    end
                    if a ~= "" and b ~= "" then break; end
                    if k == #fields then
                        if a ~= "" then
                            if b == "" and c == "" then return false;
                            elseif b == "" and c ~= "" then b = c; end
                        else
                            return nil, "Haven't found a primary key to index the list";
                        end
                    end
                end
                properties.Fields = a..", "..b;
                query = query..properties.Fields.." FROM "..eDB.database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
                return eDB.easyb(query);
            else
                return fields, e;
            end
        elseif type(properties.Fields) == "string" then
            local _, _, a, b = properties.Fields:find("(.+),(.+)");
            if a and b then
                properties.Fields = a..", "..b;
                query = query..properties.Fields.." FROM "..eDB.database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
                return eDB.easyb(query);
            else
                return nil, "Please submit a valid properties.Fields string like field1,field2";
            end
        elseif type(properties.Fields) == "table" then
            if #properties.Fields == 2 then
                properties.Fields = table.concat(properties.Fields, ",");
                query = query..properties.Fields.." FROM "..eDB.database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
                return eDB.easyb(query);
            else
                return nil, "Please submit a valid properties.Fields table with 2 elements";
            end
        end
    end
end
--
--------------### End user auxiliary functions ###------------
--
eDB.getDatabase = function()
    return eDB.database:gsub("`", "");
end

--
eDB.in_array = function(str, tbl)
    if type(str) == "string" and type(tbl) == "table" then
        for x, v in pairs(tbl) do
            if type(v) == "string" then
                if str:lower() == v:lower() then
                    return str;
                end
            end
        end
    end
    return nil;
end

--
eDB.realescape = function(a)
    return a:gsub("[^%w]", { ["'"] = "''"});
end

--
eDB.htmlentities = function(s)
    return s:gsub("[^%w]", { ["<"] = "&lt;", [">"] = "&gt;", ["&"] = "&amp;", ["'"] = "&apos;", ["="] = "&equals;",["/"]="&#47;", ["#"] = "&#35;" });
end
--
--------------### Internal auxiliary functions ###------------
--
eDB.listChilds = function(t, tb)
    tb = tb or {};
    for x, y in pairs(t) do
        table.insert(tb, y.id);
        if y._subs then
            eDB.listChilds(y._subs,tb);
        end
    end
    table.sort(tb);
    return tb;
end
--
eDB.easy = function(query)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    if type(query) == "string" then
        local t = {};
        local cur = eDB.db:execute(query);
        for row in eDB.fetch(cur, {}, "n") do
            table.insert(t, row[1]);
        end
        cur:close();
        return t;
    else
        return nil, "Invalid query: "..query;
    end
end
--
eDB.easyb = function(query)
    if not eDB.db then return nil, "Please connect to a database first using eDB.connect()"; end
    if type(query) == "string" then
        local cur, e = eDB.db:execute(query);
        if cur then
            local n = cur:numrows();
            if n == 0 then return false end
            local tb = {};
            for row in eDB.fetch(cur, {}, "n") do
                for k,v in pairs(row) do
                    tb[row[1]] = row[2] or "NULL";
                end
            end
            cur:close();
            return tb;
        else
            return cur, e;
        end
    else
        return nil, "Invalid query: "..query;
    end
end
------------### by WeBuLtRa ###-----------