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