API tools faq
paste
Advertisement
Guest User

easyDB by WeBuLtRa

a guest
Apr 26th, 2012
245
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Lua 13.09 KB | None | 0 0
raw download clone embed print report
  1. --------------### easy Database (eDB) ###------------
  2. require"luasql.mysql";
  3. eDB = {};
  4. eDB.env = luasql.mysql();
  5.  
  6. --------------### GLOBALS ###------------
  7. eDB.Version = "1.0.0.0";
  8. eDB.ABOUT = "easyDB OOP is a approach to improve the luasql.mysql functions making it easier for the end user, but OOP oriented";
  9. eDB.con = {user="", password="", host="localhost", port=3306};
  10.  
  11. --------------### New objs ###------------
  12. function eDB.new(dat, tbl)
  13.     local self = {};--table for new objs
  14.     --LOCAL enviroment globals
  15.     local connection = {};
  16.     local database = "`"..dat.."`" or "`mysql`"
  17.     local db;
  18.     for x, y in pairs(eDB.con) do
  19.         connection[x] = tbl[x] or y;
  20.     end
  21.     --
  22.     --------------### Set up functions ###------------
  23.     --
  24.     function self:connect(data, user, pwd, host, port)
  25.         data = data or database:gsub("`", "");
  26.         connection = {user=user or connection.user, password=pwd or connection.password, host=host or connection.host, port=port or connection.port};
  27.         db = eDB.env:connect(data, connection.user, connection.password, connection.host, connection.port);
  28.         if db then
  29.             return true
  30.         else
  31.             return nil, "Couldn't start database connection with localhost\r\nu:root without pwd";
  32.         end
  33.     end
  34.     --
  35.     function self:setDatabase(data, user, pwd, host, port)
  36.         if db then
  37.             db:close();
  38.         end
  39.         connection = {user=user or connection.user, password=pwd or connection.password, host=host or connection.host, port=port or connection.port};
  40.         db = eDB.env:connect(database, connection.user, connection.password, connection.host, connection.port);
  41.         database = "`"..data.."`";
  42.     end
  43.     --
  44.     --------------### End user functions ###------------
  45.     --
  46.     function self:execute(query)
  47.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  48.         if type(query) == "string" then
  49.             return db:execute(query);
  50.         else
  51.             return nil, "Invalid query";
  52.         end
  53.     end
  54.     --
  55.     function self:listDatabases()
  56.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  57.         return eDB.easy(db, "show databases");
  58.     end
  59.     --
  60.     function self:listTables()
  61.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  62.         if database and database ~= "" then
  63.             return eDB.easy(db, "show tables FROM "..database);
  64.         else
  65.             return nil, "Please select a valid database";
  66.         end
  67.     end
  68.     --
  69.     function self:listFields(tbl)
  70.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  71.         tbl = tbl or "";
  72.         if database and database ~= "" then
  73.             if tbl ~= "" then
  74.                 local t = {};
  75.                 local cur, e = db:execute("show fields FROM "..database..".`"..tbl.."`");
  76.                 if cur then
  77.                     for row in eDB.fetch(cur, {}, "n") do
  78.                         table.insert(t, {Field = row[1], Type = row[2], Null = row[3], Key = row[4], Default = row[5], Extra = row[6]});
  79.                     end
  80.                     cur:close();
  81.                 end
  82.                 return t, e;
  83.             else
  84.                 return nil, "Please select a vaild table";
  85.             end
  86.         else
  87.             return nil, "You haven't selected a valid database.\nPlease use eDB.setDB()";
  88.         end
  89.     end
  90.     --
  91.     --------------### Smart functions ###------------
  92.     --
  93.     function self:close()
  94.         if db then
  95.             db:close();
  96.         end
  97.     end
  98.     --
  99.     function self:getFieldById(tbname, field, id)
  100.         id = tonumber(id) or 1;
  101.         local c, e = db:execute("SELECT `"..field.."` FROM "..database..".`"..tbname.."` WHERE id = "..id);
  102.         if c then
  103.             local t = c:fetch({}, "n");
  104.             c:close();
  105.             return t[1] or "NULL";
  106.         else
  107.             return nil, e;
  108.         end
  109.     end
  110.     --
  111.     function self:getLastInsertId()
  112.         local a, e = db:execute("SELECT LAST_INSERT_ID();");
  113.         if a then
  114.             local t = a:fetch({}, "n");
  115.             return t[1] or 0;
  116.         else
  117.             return nil, e;
  118.         end
  119.     end
  120.     --
  121.     function self:insert(tbl, tblFields, auto_rollback,...)
  122.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  123.         local next = arg[1];
  124.         if auto_rollback and not next then
  125.             db:execute("BEGIN;");
  126.         end
  127.         if (database == "") then return nil, "You haven't set a valid database" end
  128.         if type(tbl)=="string" then
  129.             local aff, err, ist = 0, nil, false;
  130.             if type(tblFields) == "table" then
  131.                 local fields, values = "", "";
  132.                 for x, y in pairs(tblFields) do
  133.                     if type(y) == "table" then
  134.                         ist = true;
  135.                         local a, e = self:insert(tbl, y, auto_rollback, true);
  136.                         if not a then
  137.                             err = (err==nil) and "Error executing query: "..e or err.."\r\nError executing query: "..e;
  138.                             if auto_rollback then
  139.                                 return nil, err;
  140.                             else
  141.                                 return aff, err;
  142.                             end
  143.                         else
  144.                             aff = aff + a;
  145.                         end
  146.                     else
  147.                         local tmp = (type(y) == "number") and y or "'"..eDB.realescape(y).."'";
  148.                         fields = (fields == "") and "`"..x.."`" or fields..", `"..x.."`";
  149.                         values = (values == "") and tmp or values..", "..tmp;
  150.                     end
  151.                 end
  152.                 if ist then
  153.                     if auto_rollback then
  154.                         db:execute("COMMIT;");
  155.                     end
  156.                     return aff, err;
  157.                 else
  158.                     local query = string.format("INSERT INTO "..database..".`"..tbl.."`(%s) VALUES (%s);", fields, values);
  159.                     local a, e = db:execute(query);
  160.                     if not a then
  161.                         if auto_rollback then
  162.                             db:rollback();
  163.                         end
  164.                         return nil, e;
  165.                     else
  166.                         return a, query
  167.                     end
  168.                 end
  169.             else
  170.                 return nil, "Invalid second argument";
  171.             end
  172.         else
  173.             return nil, "Invalid first argument";
  174.         end
  175.     end
  176.     --
  177.     function self:returnTable(query, ret)
  178.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  179.         local tb = {};
  180.         local cur, e = db:execute(query);
  181.         if cur then
  182.             local n = cur:numrows();
  183.             if n == 0 then return false end
  184.             for row in eDB.fetch(cur, {}, ret) do
  185.                 --?
  186.                 local t = {};
  187.                 for k,v in pairs(row) do
  188.                     t[k] = v;
  189.                 end
  190.                 table.insert(tb, t);
  191.             end
  192.             cur:close();
  193.             return tb;
  194.         else
  195.             return nil, e;
  196.         end
  197.     end
  198.     --
  199.     function self:find(kind, props)
  200.         if not db then return nil, "Please connect to a database first using eDB.connect()"; end
  201.         if not kind or not props or type(props) ~= "table" then return nil, "Second argument must be a table"; end
  202.         kind = kind:lower();
  203.         local kinds = {"all", "count", "first", "list"};
  204.         local properties = {};
  205.         properties.From = false;
  206.         properties.Fields = "*";
  207.         properties.Where = "";
  208.         properties.Limit = "";
  209.         properties.GroupBy = "";
  210.         properties.Order = "";
  211.         properties.ret = "a";
  212.         for k, v in pairs(properties) do
  213.             properties[k] = props[k] or v;
  214.         end
  215.         properties.ret = (properties.ret ~= nil) and (properties.ret == "a") and properties.ret or "n" or "n";
  216.  
  217.         kind = eDB.in_array(kind, kinds);
  218.         if not kind then return false; end
  219.         if not properties.From then
  220.             return nil, "Please submit a valid table to query";
  221.         end
  222.         properties.From = (type(properties.From) == "string") and properties.From or table.concat(properties.From, ",");
  223.         if (type(properties.Where) == "table") then
  224.             local s = "";
  225.             for k, v in pairs(properties.Where) do
  226.                 local tmp = (type(v)=="number") and tostring(v) or v:lower();
  227.                 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
  228.                     s = (s=="") and "`"..k.."` "..v or s.." AND `"..k.."` "..v;
  229.                 else
  230.                     s = (s=="") and "`"..k.."`='"..v.."'" or s.." AND `"..k.."`='"..v.."'";
  231.                 end
  232.             end
  233.             properties.Where = s;
  234.         end
  235.         properties.Where = (properties.Where ~= "") and " WHERE "..properties.Where or "";
  236.         properties.GroupBy = (type(properties.GroupBy) == "string") and properties.GroupBy or table.concat(properties.GroupBy, ",");
  237.         properties.GroupBy = (properties.GroupBy ~= "") and " GROUP BY "..properties.GroupBy or "";
  238.  
  239.         properties.Order = (type(properties.Order) == "string") and properties.Order or table.concat(properties.Order, ",");
  240.         properties.Order = (properties.Order ~= "") and " ORDER BY "..properties.Order or "";
  241.  
  242.         properties.Limit = (properties.Limit ~= "") and " LIMIT "..properties.Limit or "";
  243.         local query = "SELECT ";
  244.         if kind == "all" then
  245.             properties.Fields = (type(properties.Fields) == "string") and properties.Fields:gsub("%s*,%s*", ",") or properties.Fields;
  246.             properties.Fields = (type(properties.Fields) == "string") and properties.Fields:gsub(",", "`,`") or table.concat(properties.Fields, "`, `");
  247.             properties.Fields = (properties.Fields~= "*") and "`"..properties.Fields.."`" or "*";
  248.             query = query..properties.Fields.." FROM "..database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
  249.             return self:returnTable(query, properties.ret);
  250.         elseif kind == "count" then
  251.             if (type(properties.Fields) == "string") then
  252.                 properties.Fields = "count("..properties.Fields..")";
  253.             else
  254.                 return nil, "properties.Fields can't be a table, must be a string";
  255.             end
  256.             query = query..properties.Fields.." FROM "..database.."."..properties.From..properties.Where..properties.GroupBy;
  257.             local a, e = self:returnTable(query, "n");
  258.             if a then
  259.                 return a[1][1];
  260.             else
  261.                 return a, e;
  262.             end
  263.         elseif kind == "first" then
  264.             properties.Fields = (type(properties.Fields) == "string") and properties.Fields or table.concat(properties.Fields, ", ");
  265.             query = query..properties.Fields.." FROM "..database.."."..properties.From..properties.Where;
  266.             local t, e = self:returnTable(query, properties.ret);
  267.             return (type(t) == "table") and t[1] or t, e;
  268.         else--list
  269.             if type(properties.Fields) == "string" and properties.Fields == "*" then
  270.                 local fields, e = self:listFields(properties.From);
  271.                 if fields then
  272.                     local a, b, c = "", "", "";
  273.                     for k, v in pairs(fields) do
  274.                         if v.Key == "PRI" then
  275.                             a = v.Field;
  276.                         elseif v.Field == "name" or v.Field == "title" then
  277.                             b = v.Field;
  278.                         elseif v.Type:find("varchar") then
  279.                             c = v.Field;
  280.                         end
  281.                         if a ~= "" and b ~= "" then break; end
  282.                         if k == #fields then
  283.                             if a ~= "" then
  284.                                 if b == "" and c == "" then return false;
  285.                                 elseif b == "" and c ~= "" then b = c; end
  286.                             else
  287.                                 return nil, "Haven't found a primary key to index the list";
  288.                             end
  289.                         end
  290.                     end
  291.                     properties.Fields = a..", "..b;
  292.                     query = query..properties.Fields.." FROM "..database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
  293.                     return eDB.easyb(db, query);
  294.                 else
  295.                     return fields, e;
  296.                 end
  297.             elseif type(properties.Fields) == "string" then
  298.                 local _, _, a, b = properties.Fields:find("(.+),(.+)");
  299.                 if a and b then
  300.                     properties.Fields = a..", "..b;
  301.                     query = query..properties.Fields.." FROM "..database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
  302.                     return eDB.easyb(db, query);
  303.                 else
  304.                     return nil, "Please submit a valid properties.Fields string like field1,field2";
  305.                 end
  306.             elseif type(properties.Fields) == "table" then
  307.                 if #properties.Fields == 2 then
  308.                     properties.Fields = table.concat(properties.Fields, ",");
  309.                     query = query..properties.Fields.." FROM "..database.."."..properties.From..properties.Where..properties.GroupBy..properties.Order..properties.Limit;
  310.                     return eDB.easyb(db, query);
  311.                 else
  312.                     return nil, "Please submit a valid properties.Fields table with 2 elements";
  313.                 end
  314.             end
  315.         end
  316.     end
  317.     --
  318.     --------------### End user auxiliary functions ###------------
  319.     --
  320.     function self:getDatabase()
  321.         return database:gsub("`", "");
  322.     end
  323.     --
  324.     local mt = self;
  325.     mt.__metatable = "Nothing to see here";
  326.     setmetatable(self, mt);
  327.     return self;
  328. end
  329. --
  330. --------------### End user auxiliary functions ###------------
  331. --
  332. eDB.fetch = function(c,l, k)
  333.         local l = l or {};
  334.         local k = (k==nil) and "a" or (k=="n") and "n" or "a";
  335.         return function (l)
  336.             return c:fetch(l, k)
  337.         end,l,c, true
  338.     end
  339. --
  340. eDB.in_array = function(str, tbl)
  341.     if type(str) == "string" and type(tbl) == "table" then
  342.         for x, v in pairs(tbl) do
  343.             if type(v) == "string" then
  344.                 if str:lower() == v:lower() then
  345.                     return str;
  346.                 end
  347.             end
  348.         end
  349.     end
  350.     return nil;
  351. end
  352. --
  353. eDB.realescape = function(a)
  354.     return a:gsub("[^%w]", { ["'"] = "''"});
  355. end
  356. --
  357. eDB.htmlentities = function(s)
  358.     return s:gsub("[^%w]", { ["<"] = "&lt;", [">"] = "&gt;", ["&"] = "&amp;", ["'"] = "&apos;", ["="] = "&equals;",["/"]="&#47;", ["#"] = "&#35;" });
  359. end
  360. --
  361. eDB.finish = function()
  362.     if eDB.env then
  363.         eDB.env:close();
  364.     end
  365. end
  366. --
  367. --------------### Internal auxiliary functions ###------------
  368. --
  369. eDB.easy = function(db, query)
  370.     if type(query) == "string" then
  371.         local t = {};
  372.         local cur = db:execute(query);
  373.         for row in eDB.fetch(cur, {}, "n") do
  374.             table.insert(t, row[1]);
  375.         end
  376.         cur:close();
  377.         return t;
  378.     else
  379.         return nil, "Invalid query";
  380.     end
  381. end
  382. --
  383. eDB.easyb = function(db, query)
  384.     if type(query) == "string" then
  385.         local cur, e = db:execute(query);
  386.         if cur then
  387.             local n = cur:numrows();
  388.             if n == 0 then return false end
  389.             local tb = {};
  390.             for row in eDB.fetch(cur, {}, "n") do
  391.                 for k,v in pairs(row) do
  392.                     tb[row[1]] = row[2] or "NULL";
  393.                 end
  394.             end
  395.             cur:close();
  396.             return tb;
  397.         else
  398.             return cur, e;
  399.         end
  400.     else
  401.         return nil, "Invalid query";
  402.     end
  403. end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!