· 8 years ago · Jun 03, 2018, 03:38 AM
1-------------------------------
2sql = {
3 host = '127.0.0.1',
4 port = '3306',
5 user = 'mta',
6 pass = 'mtasa123',
7 database = 'mta'
8}
9-------------------------------
10local db = {}
11db.connection = dbConnect('mysql','dbname='..sql.database..';host='..sql.host..';charset=utf8',sql.user,sql.pass,'share=0')
12local dataTypes = {{'string','TEXT'},{'number','FLOAT'},{'boolean','BOOLEAN'}}
13
14function getDataType(data)
15 if data then
16 for i,v in pairs(dataTypes) do
17 if v[1] == type(data) then
18 return v[1],v[2],data
19 end
20 end
21 end
22end
23
24function convert(data)
25 local t,_,d = getDataType(data)
26 if t == 'string' then
27 return tostring(data)
28 elseif t == 'number' then
29 return tonumber(data)
30 elseif t == 'boolean'then
31 return d and 1 or 0
32 end
33end
34
35function db.query(callback,query,...)
36 if query and type(query) == 'string' and db.connection then
37 if not arg[1] then arg = {} end
38 dbQuery(callback,db.connection,query,unpack(arg))
39 end
40end
41
42function db.createTable(name,data)
43 if name and type(name) == 'string' and data and type(data) == 'table' and db.connection then
44 local s = 'CREATE TABLE IF NOT EXISTS `??` ('
45 local add = {}
46 local newT = {}
47
48 for k,v in pairs(data) do
49 table.insert(add,'`??` ??')
50 table.insert(newT,k)
51 table.insert(newT,v)
52 end
53
54 local newS = s..table.concat(add,',')..');'
55 dbExec(db.connection,newS,name,unpack(newT))
56 print(newS)
57 iprint(newT)
58 end
59end
60--db.createTable('awsomeness',{name='TEXT',number='FLOAT',bool='BOOLEAN'})
61
62function db.add(name,items)
63 if name and items and type(items) == 'table' and db.connection then
64 local s = 'REPLACE INTO `??` ('
65 local columnStrings,valueStrings = {},{}
66 local pack = {}
67
68 for k,v in pairs(items) do
69 table.insert(columnStrings,'`??`')
70 table.insert(valueStrings,'?')
71 table.insert(pack,k)
72 end
73
74 for o,e in pairs(items) do
75 table.insert(pack,convert(e))
76 end
77
78 local newS = s..table.concat(columnStrings,',')..') VALUES ('..table.concat(valueStrings,',')..');'
79 dbExec(db.connection,newS,name,unpack(pack))
80 print(newS)
81 iprint(pack)
82 end
83end
84--db.add('awsomeness',{name='Shay',number=12345,bool=true})
85
86function db.update(name,items,where)
87 if name and type(name) == 'string' and items and type(items) == 'table' and db.connection then
88 local s = 'UPDATE `??` SET '
89 local add = {}
90 local newT = {}
91 local ws = {}
92 local w = ''
93
94 for k,v in pairs(items) do
95 table.insert(add,'`??`=?')
96 table.insert(newT,k)
97 table.insert(newT,convert(v))
98 end
99
100 if where and type(where) == 'table' then
101 w = ' WHERE '
102 for e,r in pairs(where) do
103 table.insert(ws,'`??`=?')
104 table.insert(newT,e)
105 table.insert(newT,convert(r))
106 end
107 end
108
109 local newS = s..table.concat(add,',')..w..table.concat(ws,' AND ')..';'
110 dbExec(db.connection,newS,name,unpack(newT))
111 end
112end
113--db.update('awsomeness',{name='iLoay'},{name='Shay',number=12345})