· 6 years ago · Jul 18, 2019, 08:14 PM
1--[[
2 mysql - 1.0.2
3 A simple MySQL wrapper for Garry's Mod.
4
5 Alexander Grist-Hucker
6 http://www.alexgrist.com
7--]]
8
9mysql = mysql or {};
10
11local QueueTable = {};
12local Module = "sqlite";
13local Connected = false;
14local type = type;
15local tostring = tostring;
16local table = table;
17
18--[[
19 Phrases
20--]]
21
22local MODULE_NOT_EXIST = "[mysql] The %s module does not exist!\n";
23
24--[[
25 Begin Query Class.
26--]]
27
28local QUERY_CLASS = {};
29QUERY_CLASS.__index = QUERY_CLASS;
30
31function QUERY_CLASS:New(tableName, queryType)
32 local newObject = setmetatable({}, QUERY_CLASS);
33 newObject.queryType = queryType;
34 newObject.tableName = tableName;
35 newObject.selectList = {};
36 newObject.insertList = {};
37 newObject.updateList = {};
38 newObject.createList = {};
39 newObject.whereList = {};
40 newObject.orderByList = {};
41 return newObject;
42end;
43
44function QUERY_CLASS:Escape(text)
45 return mysql:Escape(tostring(text));
46end;
47
48function QUERY_CLASS:ForTable(tableName)
49 self.tableName = tableName;
50end;
51
52function QUERY_CLASS:Where(key, value)
53 self:WhereEqual(key, value);
54end;
55
56function QUERY_CLASS:WhereEqual(key, value)
57 self.whereList[#self.whereList + 1] = "`"..key.."` = \""..self:Escape(value).."\"";
58end;
59
60function QUERY_CLASS:WhereNotEqual(key, value)
61 self.whereList[#self.whereList + 1] = "`"..key.."` != \""..self:Escape(value).."\"";
62end;
63
64function QUERY_CLASS:WhereLike(key, value)
65 self.whereList[#self.whereList + 1] = "`"..key.."` LIKE \""..self:Escape(value).."\"";
66end;
67
68function QUERY_CLASS:WhereNotLike(key, value)
69 self.whereList[#self.whereList + 1] = "`"..key.."` NOT LIKE \""..self:Escape(value).."\"";
70end;
71
72function QUERY_CLASS:WhereGT(key, value)
73 self.whereList[#self.whereList + 1] = "`"..key.."` > \""..self:Escape(value).."\"";
74end;
75
76function QUERY_CLASS:WhereLT(key, value)
77 self.whereList[#self.whereList + 1] = "`"..key.."` < \""..self:Escape(value).."\"";
78end;
79
80function QUERY_CLASS:WhereGTE(key, value)
81 self.whereList[#self.whereList + 1] = "`"..key.."` >= \""..self:Escape(value).."\"";
82end;
83
84function QUERY_CLASS:WhereLTE(key, value)
85 self.whereList[#self.whereList + 1] = "`"..key.."` <= \""..self:Escape(value).."\"";
86end;
87
88function QUERY_CLASS:OrderByDesc(key)
89 self.orderByList[#self.orderByList + 1] = "`"..key.."` DESC";
90end;
91
92function QUERY_CLASS:OrderByAsc(key)
93 self.orderByList[#self.orderByList + 1] = "`"..key.."` ASC";
94end;
95
96function QUERY_CLASS:Callback(queryCallback)
97 self.callback = queryCallback;
98end;
99
100function QUERY_CLASS:Select(fieldName)
101 self.selectList[#self.selectList + 1] = "`"..fieldName.."`";
102end;
103
104function QUERY_CLASS:Insert(key, value)
105 self.insertList[#self.insertList + 1] = {"`"..key.."`", "\""..self:Escape(value).."\""};
106end;
107
108function QUERY_CLASS:Update(key, value)
109 self.updateList[#self.updateList + 1] = {"`"..key.."`", "\""..self:Escape(value).."\""};
110end;
111
112function QUERY_CLASS:Create(key, value)
113 self.createList[#self.createList + 1] = {"`"..key.."`", value};
114end;
115
116function QUERY_CLASS:PrimaryKey(key)
117 self.primaryKey = "`"..key.."`";
118end;
119
120function QUERY_CLASS:Limit(value)
121 self.limit = value;
122end;
123
124function QUERY_CLASS:Offset(value)
125 self.offset = value;
126end;
127
128local function BuildSelectQuery(queryObj)
129 local queryString = {"SELECT"};
130
131 if (type(queryObj.selectList) != "table" or #queryObj.selectList == 0) then
132 queryString[#queryString + 1] = " *";
133 else
134 queryString[#queryString + 1] = " "..table.concat(queryObj.selectList, ", ");
135 end;
136
137 if (type(queryObj.tableName) == "string") then
138 queryString[#queryString + 1] = " FROM `"..queryObj.tableName.."` ";
139 else
140 ErrorNoHalt("[mysql] No table name specified!\n");
141 return;
142 end;
143
144 if (type(queryObj.whereList) == "table" and #queryObj.whereList > 0) then
145 queryString[#queryString + 1] = " WHERE ";
146 queryString[#queryString + 1] = table.concat(queryObj.whereList, " AND ");
147 end;
148
149 if (type(queryObj.orderByList) == "table" and #queryObj.orderByList > 0) then
150 queryString[#queryString + 1] = " ORDER BY ";
151 queryString[#queryString + 1] = table.concat(queryObj.orderByList, ", ");
152 end;
153
154 if (type(queryObj.limit) == "number") then
155 queryString[#queryString + 1] = " LIMIT ";
156 queryString[#queryString + 1] = queryObj.limit;
157 end;
158
159 return table.concat(queryString);
160end;
161
162local function BuildInsertQuery(queryObj)
163 local queryString = {"INSERT INTO"};
164 local keyList = {};
165 local valueList = {};
166
167 if (type(queryObj.tableName) == "string") then
168 queryString[#queryString + 1] = " `"..queryObj.tableName.."`";
169 else
170 ErrorNoHalt("[mysql] No table name specified!\n");
171 return;
172 end;
173
174 for i = 1, #queryObj.insertList do
175 keyList[#keyList + 1] = queryObj.insertList[i][1];
176 valueList[#valueList + 1] = queryObj.insertList[i][2];
177 end;
178
179 if (#keyList == 0) then
180 return;
181 end;
182
183 queryString[#queryString + 1] = " ("..table.concat(keyList, ", ")..")";
184 queryString[#queryString + 1] = " VALUES ("..table.concat(valueList, ", ")..")";
185
186 return table.concat(queryString);
187end;
188
189local function BuildUpdateQuery(queryObj)
190 local queryString = {"UPDATE"};
191
192 if (type(queryObj.tableName) == "string") then
193 queryString[#queryString + 1] = " `"..queryObj.tableName.."`";
194 else
195 ErrorNoHalt("[mysql] No table name specified!\n");
196 return;
197 end;
198
199 if (type(queryObj.updateList) == "table" and #queryObj.updateList > 0) then
200 local updateList = {};
201
202 queryString[#queryString + 1] = " SET";
203
204 for i = 1, #queryObj.updateList do
205 updateList[#updateList + 1] = queryObj.updateList[i][1].." = "..queryObj.updateList[i][2];
206 end;
207
208 queryString[#queryString + 1] = " "..table.concat(updateList, ", ");
209 end;
210
211 if (type(queryObj.whereList) == "table" and #queryObj.whereList > 0) then
212 queryString[#queryString + 1] = " WHERE ";
213 queryString[#queryString + 1] = table.concat(queryObj.whereList, " AND ");
214 end;
215
216 if (type(queryObj.offset) == "number") then
217 queryString[#queryString + 1] = " OFFSET ";
218 queryString[#queryString + 1] = queryObj.offset;
219 end;
220
221 return table.concat(queryString);
222end;
223
224local function BuildDeleteQuery(queryObj)
225 local queryString = {"DELETE FROM"}
226
227 if (type(queryObj.tableName) == "string") then
228 queryString[#queryString + 1] = " `"..queryObj.tableName.."`";
229 else
230 ErrorNoHalt("[mysql] No table name specified!\n");
231 return;
232 end;
233
234 if (type(queryObj.whereList) == "table" and #queryObj.whereList > 0) then
235 queryString[#queryString + 1] = " WHERE ";
236 queryString[#queryString + 1] = table.concat(queryObj.whereList, " AND ");
237 end;
238
239 if (type(queryObj.limit) == "number") then
240 queryString[#queryString + 1] = " LIMIT ";
241 queryString[#queryString + 1] = queryObj.limit;
242 end;
243
244 return table.concat(queryString);
245end;
246
247local function BuildDropQuery(queryObj)
248 local queryString = {"DROP TABLE"}
249
250 if (type(queryObj.tableName) == "string") then
251 queryString[#queryString + 1] = " `"..queryObj.tableName.."`";
252 else
253 ErrorNoHalt("[mysql] No table name specified!\n");
254 return;
255 end;
256
257 return table.concat(queryString);
258end;
259
260local function BuildTruncateQuery(queryObj)
261 local queryString = {"TRUNCATE TABLE"}
262
263 if (type(queryObj.tableName) == "string") then
264 queryString[#queryString + 1] = " `"..queryObj.tableName.."`";
265 else
266 ErrorNoHalt("[mysql] No table name specified!\n");
267 return;
268 end;
269
270 return table.concat(queryString);
271end;
272
273local function BuildCreateQuery(queryObj)
274 local queryString = {"CREATE TABLE IF NOT EXISTS"};
275
276 if (type(queryObj.tableName) == "string") then
277 queryString[#queryString + 1] = " `"..queryObj.tableName.."`";
278 else
279 ErrorNoHalt("[mysql] No table name specified!\n");
280 return;
281 end;
282
283 queryString[#queryString + 1] = " (";
284
285 if (type(queryObj.createList) == "table" and #queryObj.createList > 0) then
286 local createList = {};
287
288 for i = 1, #queryObj.createList do
289 if (Module == "sqlite") then
290 createList[#createList + 1] = queryObj.createList[i][1].." "..string.gsub(string.gsub(string.gsub(queryObj.createList[i][2], "AUTO_INCREMENT", ""), "AUTOINCREMENT", ""), "INT ", "INTEGER ");
291 else
292 createList[#createList + 1] = queryObj.createList[i][1].." "..queryObj.createList[i][2];
293 end;
294 end;
295
296 queryString[#queryString + 1] = " "..table.concat(createList, ", ");
297 end;
298
299 if (type(queryObj.primaryKey) == "string") then
300 queryString[#queryString + 1] = ", PRIMARY KEY";
301 queryString[#queryString + 1] = " ("..queryObj.primaryKey..")";
302 end;
303
304 queryString[#queryString + 1] = " )";
305
306 return table.concat(queryString);
307end;
308
309function QUERY_CLASS:Execute(bQueueQuery)
310 local queryString = nil;
311 local queryType = string.lower(self.queryType);
312
313 if (queryType == "select") then
314 queryString = BuildSelectQuery(self);
315 elseif (queryType == "insert") then
316 queryString = BuildInsertQuery(self);
317 elseif (queryType == "update") then
318 queryString = BuildUpdateQuery(self);
319 elseif (queryType == "delete") then
320 queryString = BuildDeleteQuery(self);
321 elseif (queryType == "drop") then
322 queryString = BuildDropQuery(self);
323 elseif (queryType == "truncate") then
324 queryString = BuildTruncateQuery(self);
325 elseif (queryType == "create") then
326 queryString = BuildCreateQuery(self);
327 end;
328
329 if (type(queryString) == "string") then
330 if (!bQueueQuery) then
331 return mysql:RawQuery(queryString, self.callback);
332 else
333 return mysql:Queue(queryString, self.callback);
334 end;
335 end;
336end;
337
338--[[
339 End Query Class.
340--]]
341
342function mysql:Select(tableName)
343 return QUERY_CLASS:New(tableName, "SELECT");
344end;
345
346function mysql:Insert(tableName)
347 return QUERY_CLASS:New(tableName, "INSERT");
348end;
349
350function mysql:Update(tableName)
351 return QUERY_CLASS:New(tableName, "UPDATE");
352end;
353
354function mysql:Delete(tableName)
355 return QUERY_CLASS:New(tableName, "DELETE");
356end;
357
358function mysql:Drop(tableName)
359 return QUERY_CLASS:New(tableName, "DROP");
360end;
361
362function mysql:Truncate(tableName)
363 return QUERY_CLASS:New(tableName, "TRUNCATE");
364end;
365
366function mysql:Create(tableName)
367 return QUERY_CLASS:New(tableName, "CREATE");
368end;
369
370-- A function to connect to the MySQL database.
371function mysql:Connect(host, username, password, database, port, socket, flags)
372 if (!port) then
373 port = 3306;
374 end;
375
376 if (Module == "tmysql4") then
377 if (type(tmysql) != "table") then
378 require("tmysql4");
379 end;
380
381 if (tmysql) then
382 local errorText = nil;
383
384 self.connection, errorText = tmysql.initialize(host, username, password, database, port, socket, flags);
385
386 if (!self.connection) then
387 self:OnConnectionFailed(errorText);
388 else
389 self:OnConnected();
390 end;
391 else
392 ErrorNoHalt(string.format(MODULE_NOT_EXIST, Module));
393 end;
394 elseif (Module == "mysqloo") then
395 if (type(mysqloo) != "table") then
396 require("mysqloo");
397 end;
398
399 if (mysqloo) then
400 local clientFlag = flags or 0;
401
402 if (type(socket) ~= "string") then
403 self.connection = mysqloo.connect(host, username, password, database, port);
404 else
405 self.connection = mysqloo.connect(host, username, password, database, port, socket, clientFlag);
406 end;
407
408 self.connection.onConnected = function(database)
409 mysql:OnConnected();
410 end;
411
412 self.connection.onConnectionFailed = function(database, errorText)
413 mysql:OnConnectionFailed(errorText);
414 end;
415
416 self.connection:connect();
417 else
418 ErrorNoHalt(string.format(MODULE_NOT_EXIST, Module));
419 end;
420 elseif (Module == "sqlite") then
421 mysql:OnConnected();
422 end;
423end;
424
425-- A function to query the MySQL database.
426function mysql:RawQuery(query, callback, flags, ...)
427 if (!self.connection and Module != "sqlite") then
428 self:Queue(query);
429 end;
430
431 if (Module == "tmysql4") then
432 local queryFlag = flags or QUERY_FLAG_ASSOC;
433
434 self.connection:Query(query, function(result)
435 local queryStatus = result[1]["status"];
436
437 if (queryStatus) then
438 if (type(callback) == "function") then
439 local bStatus, value = pcall(callback, result[1]["data"], queryStatus, result[1]["lastid"]);
440
441 if (!bStatus) then
442 ErrorNoHalt(string.format("[mysql] MySQL Callback Error!\n%s\n", value));
443 end;
444 end;
445 else
446 ErrorNoHalt(string.format("[mysql] MySQL Query Error!\nQuery: %s\n%s\n", query, result[1]["error"]));
447 end;
448 end, queryFlag, ...);
449 elseif (Module == "mysqloo") then
450 local queryObj = self.connection:query(query);
451
452 queryObj:setOption(mysqloo.OPTION_NAMED_FIELDS);
453
454 queryObj.onSuccess = function(queryObj, result)
455 if (callback) then
456 local bStatus, value = pcall(callback, result, true, queryObj:lastInsert());
457
458 if (!bStatus) then
459 ErrorNoHalt(string.format("[mysql] MySQL Callback Error!\n%s\n", value));
460 end;
461 end;
462 end;
463
464 queryObj.onError = function(queryObj, errorText)
465 ErrorNoHalt(string.format("[mysql] MySQL Query Error!\nQuery: %s\n%s\n", query, errorText));
466 end;
467
468 queryObj:start();
469 elseif (Module == "sqlite") then
470 local result = sql.Query(query);
471
472 if (result == false) then
473 ErrorNoHalt(string.format("[mysql] SQL Query Error!\nQuery: %s\n%s\n", query, sql.LastError()));
474 else
475 if (callback) then
476 local bStatus, value = pcall(callback, result);
477
478 if (!bStatus) then
479 ErrorNoHalt(string.format("[mysql] SQL Callback Error!\n%s\n", value));
480 end;
481 end;
482 end;
483 else
484 ErrorNoHalt(string.format("[mysql] Unsupported module \"%s\"!\n", Module));
485 end;
486end;
487
488-- A function to add a query to the queue.
489function mysql:Queue(queryString, callback)
490 if (type(queryString) == "string") then
491 QueueTable[#QueueTable + 1] = {queryString, callback};
492 end;
493end;
494
495-- A function to escape a string for MySQL.
496function mysql:Escape(text)
497 if (self.connection) then
498 if (Module == "tmysql4") then
499 return self.connection:Escape(text);
500 elseif (Module == "mysqloo") then
501 return self.connection:escape(text);
502 end;
503 else
504 return sql.SQLStr(string.gsub(text, "\"", "'"), true);
505 end;
506end;
507
508-- A function to disconnect from the MySQL database.
509function mysql:Disconnect()
510 if (self.connection) then
511 if (Module == "tmysql4") then
512 return self.connection:Disconnect();
513 end;
514 end;
515
516 Connected = false;
517end;
518
519function mysql:Think()
520 if (#QueueTable > 0) then
521 if (type(QueueTable[1]) == "table") then
522 local queueObj = QueueTable[1];
523 local queryString = queueObj[1];
524 local callback = queueObj[2];
525
526 if (type(queryString) == "string") then
527 self:RawQuery(queryString, callback);
528 end;
529
530 table.remove(QueueTable, 1);
531 end;
532 end;
533end;
534
535-- A function to set the module that should be used.
536function mysql:SetModule(moduleName)
537 Module = moduleName;
538end;
539
540-- Called when the database connects sucessfully.
541function mysql:OnConnected()
542 MsgC(Color(25, 235, 25), "[mysql] Connected to the database!\n");
543
544 Connected = true;
545 hook.Run("DatabaseConnected");
546end;
547
548-- Called when the database connection fails.
549function mysql:OnConnectionFailed(errorText)
550 ErrorNoHalt("[mysql] Unable to connect to the database!\n"..errorText.."\n");
551
552 hook.Run("DatabaseConnectionFailed", errorText);
553end;
554
555-- A function to check whether or not the module is connected to a database.
556function mysql:IsConnected()
557 return Connected;
558end;
559
560return mysql;