Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --------------### 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]", { ["<"] = "<", [">"] = ">", ["&"] = "&", ["'"] = "'", ["="] = "=",["/"]="/", ["#"] = "#" });
- 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 ###-----------
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement