· 4 years ago · Dec 17, 2020, 05:56 PM
1local Clockwork = Clockwork;
2local ErrorNoHalt = ErrorNoHalt;
3local tostring = tostring;
4local error = error;
5local pairs = pairs;
6local pcall = pcall;
7local type = type;
8local string = string;
9local table = table;
10
11Clockwork.database = Clockwork.kernel:NewLibrary("Database");
12Clockwork.database.queryQueue = Clockwork.database.queryQueue or {};
13
14MYSQL_UPDATE_CLASS = {__index = MYSQL_UPDATE_CLASS};
15function MYSQL_UPDATE_CLASS:SetTable(tableName)
16 self.tableName = tableName;
17 return self;
18end;
19
20function MYSQL_UPDATE_CLASS:SetValue(key, value)
21 if (Clockwork.NoMySQL) then return self; end;
22 self.updateVars[key] = Clockwork.database:Escape(tostring(value));
23 return self;
24end;
25
26function MYSQL_UPDATE_CLASS:Replace(key, search, replace)
27 if (Clockwork.NoMySQL) then return self; end;
28
29 search = "\""..Clockwork.database:Escape(tostring(search)).."\"";
30 replace = "\""..Clockwork.database:Escape(tostring(replace)).."\"";
31 self.updateVars[key] = "REPLACE("..key..", \""..search.."\", \""..replace.."\")";
32
33 return self;
34end;
35
36function MYSQL_UPDATE_CLASS:AddWhere(key, value)
37 if (Clockwork.NoMySQL) then return self; end;
38
39 value = Clockwork.database:Escape(tostring(value));
40 self.updateWhere[#self.updateWhere + 1] = string.gsub(key, '?', "\""..value.."\"");
41 return self;
42end;
43
44function MYSQL_UPDATE_CLASS:SetCallback(Callback)
45 self.Callback = Callback;
46 return self;
47end;
48
49function MYSQL_UPDATE_CLASS:SetFlag(value)
50 self.Flag = value;
51 return self;
52end;
53
54function MYSQL_UPDATE_CLASS:Push()
55 if (Clockwork.NoMySQL) then return; end;
56 if (!self.tableName) then return; end;
57
58 local updateQuery = "";
59
60 for k, v in pairs(self.updateVars) do
61 if (updateQuery == "") then
62 updateQuery = "UPDATE "..self.tableName.." SET "..k.." = \""..v.."\"";
63 else
64 updateQuery = updateQuery..", "..k.." = \""..v.."\"";
65 end;
66 end;
67
68 if (updateQuery == "") then return; end;
69
70 local whereTable = {};
71
72 for k, v in pairs(self.updateWhere) do
73 whereTable[#whereTable + 1] = v;
74 end;
75
76 local whereString = table.concat(whereTable, " AND ");
77
78 if (whereString != "") then
79 Clockwork.database:Query(updateQuery.." WHERE "..whereString, self.Callback, self.Flag);
80 else
81 Clockwork.database:Query(updateQuery, self.Callback, self.Flag);
82 end;
83end;
84
85MYSQL_INSERT_CLASS = {__index = MYSQL_INSERT_CLASS};
86function MYSQL_INSERT_CLASS:SetTable(tableName)
87 self.tableName = tableName;
88 return self;
89end;
90
91function MYSQL_INSERT_CLASS:SetValue(key, value)
92 self.insertVars[key] = value;
93 return self;
94end;
95
96function MYSQL_INSERT_CLASS:SetCallback(Callback)
97 self.Callback = Callback;
98 return self;
99end;
100
101function MYSQL_INSERT_CLASS:SetFlag(value)
102 self.Flag = value;
103 return self;
104end;
105
106function MYSQL_INSERT_CLASS:Push()
107 if (Clockwork.NoMySQL) then return; end;
108 if (!self.tableName) then return; end;
109
110 local keyList = {};
111 local valueList = {};
112
113 for k, v in pairs(self.insertVars) do
114 keyList[#keyList + 1] = k;
115 valueList[#valueList + 1] = "\""..Clockwork.database:Escape(tostring(v)).."\"";
116 end;
117
118 if (#keyList == 0) then return; end;
119
120 local insertQuery = "INSERT INTO "..self.tableName.." ("..table.concat(keyList, ", ")..")";
121 insertQuery = insertQuery.." VALUES("..table.concat(valueList, ", ")..")";
122 Clockwork.database:Query(insertQuery, self.Callback, self.Flag);
123end;
124
125MYSQL_SELECT_CLASS = {__index = MYSQL_SELECT_CLASS};
126
127function MYSQL_SELECT_CLASS:SetTable(tableName)
128 self.tableName = tableName;
129 return self;
130end;
131
132function MYSQL_SELECT_CLASS:AddColumn(key)
133 self.selectColumns[#self.selectColumns + 1] = key;
134 return self;
135end;
136
137function MYSQL_SELECT_CLASS:AddWhere(key, value)
138 if (Clockwork.NoMySQL) then return self; end;
139
140 value = Clockwork.database:Escape(tostring(value));
141 self.selectWhere[#self.selectWhere + 1] = string.gsub(key, '?', "\""..value.."\"");
142 return self;
143end;
144
145function MYSQL_SELECT_CLASS:SetCallback(Callback)
146 self.Callback = Callback;
147 return self;
148end;
149
150function MYSQL_SELECT_CLASS:SetFlag(value)
151 self.Flag = value;
152 return self;
153end;
154
155function MYSQL_SELECT_CLASS:SetOrder(key, value)
156 self.Order = key.." "..value;
157 return self;
158end;
159
160function MYSQL_SELECT_CLASS:Pull()
161 if (Clockwork.NoMySQL) then return; end;
162 if (!self.tableName) then return; end;
163
164 if (#self.selectColumns == 0) then
165 self.selectColumns[#self.selectColumns + 1] = "*";
166 end;
167
168 local selectQuery = "SELECT "..table.concat(self.selectColumns, ", ").." FROM "..self.tableName;
169 local whereTable = {};
170
171 for k, v in pairs(self.selectWhere) do
172 whereTable[#whereTable + 1] = v;
173 end;
174
175 local whereString = table.concat(whereTable, " AND ");
176
177 if (whereString != "") then
178 selectQuery = selectQuery.." WHERE "..whereString;
179 end;
180
181 if (self.selectOrder != "") then
182 selectQuery = selectQuery.." ORDER BY "..self.selectOrder;
183 end;
184
185 Clockwork.database:Query(selectQuery, self.Callback, self.Flag);
186end;
187
188MYSQL_DELETE_CLASS = {__index = MYSQL_DELETE_CLASS};
189
190function MYSQL_DELETE_CLASS:SetTable(tableName)
191 self.tableName = tableName;
192 return self;
193end;
194
195function MYSQL_DELETE_CLASS:AddWhere(key, value)
196 if (Clockwork.NoMySQL) then return self; end;
197
198 value = Clockwork.database:Escape(tostring(value));
199 self.deleteWhere[#self.deleteWhere + 1] = string.gsub(key, '?', "\""..value.."\"");
200 return self;
201end;
202
203function MYSQL_DELETE_CLASS:SetCallback(Callback)
204 self.Callback = Callback;
205 return self;
206end;
207
208function MYSQL_DELETE_CLASS:SetFlag(value)
209 self.Flag = value;
210 return self;
211end;
212
213function MYSQL_DELETE_CLASS:Push()
214 if (Clockwork.NoMySQL) then return; end;
215 if (!self.tableName) then return; end;
216
217 local deleteQuery = "DELETE FROM "..self.tableName;
218 local whereTable = {};
219
220 for k, v in pairs(self.deleteWhere) do
221 whereTable[#whereTable + 1] = v;
222 end;
223
224 local whereString = table.concat(whereTable, " AND ");
225
226 if (whereString != "") then
227 Clockwork.database:Query(deleteQuery.." WHERE "..whereString, self.Callback, self.Flag);
228 else
229 Clockwork.database:Query(deleteQuery, self.Callback, self.Flag);
230 end;
231end;
232
233-- A function to begin a database update.
234function Clockwork.database:Update(tableName)
235 local object = Clockwork.kernel:NewMetaTable(MYSQL_UPDATE_CLASS);
236 object.updateVars = {};
237 object.updateWhere = {};
238 object.tableName = tableName;
239 return object;
240end;
241
242-- A function to begin a database insert.
243function Clockwork.database:Insert(tableName)
244 local object = Clockwork.kernel:NewMetaTable(MYSQL_INSERT_CLASS);
245 object.insertVars = {};
246 object.tableName = tableName;
247 return object;
248end;
249
250-- A function to begin a database select.
251function Clockwork.database:Select(tableName)
252 local object = Clockwork.kernel:NewMetaTable(MYSQL_SELECT_CLASS);
253 object.selectColumns = {};
254 object.selectWhere = {};
255 object.selectOrder = "";
256 object.tableName = tableName;
257 return object;
258end;
259
260-- A function to begin a database delete.
261function Clockwork.database:Delete(tableName)
262 local object = Clockwork.kernel:NewMetaTable(MYSQL_DELETE_CLASS);
263 object.deleteWhere = {};
264 object.tableName = tableName;
265 return object;
266end;
267
268-- Called when a MySQL error occurs.
269function Clockwork.database:Error(errorText, queryText)
270 if (errorText) then
271 ErrorNoHalt("[Clockwork] MySQL Error: "..errorText.."\nQuery: "..queryText.."\n");
272 end;
273end;
274
275-- A function to queue a query.
276function Clockwork.database:Queue(query, Callback, bRawQuery)
277 self.queryQueue[#self.queryQueue + 1] = {query, Callback, bRawQuery};
278end;
279
280-- A function to query the database.
281function Clockwork.database:Query(query, Callback, flag, bRawQuery)
282 if (Clockwork.NoMySQL) then return; end;
283
284 if (MANUAL_UPDATE) then
285 Clockwork.database:Queue(query, Callback, bRawQuery);
286 return;
287 end;
288
289 if (!bRawQuery) then
290 local queryObj = self.connection:query(query);
291
292 queryObj:setOption(mysqloo.OPTION_NAMED_FIELDS);
293
294 queryObj.onSuccess = function(queryObj, data)
295 if (Callback) then
296 Callback(data, queryObj:status(), queryObj:lastInsert());
297 end;
298 end;
299
300 queryObj.onError = function(queryObj, errorText)
301 local databaseStatus = Clockwork.database.connection:status();
302
303 if (databaseStatus != mysqloo.DATABASE_CONNECTED) then
304 Clockwork.database:Queue(query, Callback);
305
306 if (databaseStatus == mysqloo.DATABASE_NOT_CONNECTED) then
307 Clockwork.database.connection:connect();
308 end;
309 end;
310
311 Clockwork.database:Error(errorText, query);
312 end;
313
314 queryObj:start();
315 else
316 local queryObj = self.connection:query(query);
317
318 queryObj:start();
319 end;
320end;
321
322-- A function to get whether a result is valid.
323function Clockwork.database:IsResult(result)
324 return (result and type(result) == "table" and #result > 0);
325end;
326
327-- A function to make a string safe for SQL.
328function Clockwork.database:Escape(text)
329 return self.connection:escape(text);
330end;
331
332-- Called when the database is connected.
333function Clockwork.database:OnConnected()
334 local BANS_TABLE_QUERY = [[
335 CREATE TABLE IF NOT EXISTS `]]..Clockwork.config:Get("mysql_bans_table"):Get()..[[` (
336 `_Key` int(11) NOT NULL AUTO_INCREMENT,
337 `_Identifier` text NOT NULL,
338 `_UnbanTime` int(11) NOT NULL,
339 `_SteamName` varchar(150) NOT NULL,
340 `_Duration` int(11) NOT NULL,
341 `_Reason` text NOT NULL,
342 `_Schema` text NOT NULL,
343 PRIMARY KEY (`_Key`))
344 ]];
345
346 local CHARACTERS_TABLE_QUERY = [[
347 CREATE TABLE IF NOT EXISTS `]]..Clockwork.config:Get("mysql_characters_table"):Get()..[[` (
348 `_Key` smallint(11) unsigned NOT NULL AUTO_INCREMENT,
349 `_Data` text NOT NULL,
350 `_Name` varchar(150) NOT NULL,
351 `_Ammo` text NOT NULL,
352 `_Cash` varchar(150) NOT NULL,
353 `_Model` varchar(250) NOT NULL,
354 `_Flags` text NOT NULL,
355 `_Schema` text NOT NULL,
356 `_Gender` varchar(50) NOT NULL,
357 `_Faction` varchar(50) NOT NULL,
358 `_SteamID` varchar(60) NOT NULL,
359 `_SteamName` varchar(150) NOT NULL,
360 `_Inventory` text NOT NULL,
361 `_OnNextLoad` text NOT NULL,
362 `_Attributes` text NOT NULL,
363 `_LastPlayed` varchar(50) NOT NULL,
364 `_TimeCreated` varchar(50) NOT NULL,
365 `_CharacterID` varchar(50) NOT NULL,
366 `_RecognisedNames` text NOT NULL,
367 PRIMARY KEY (`_Key`),
368 KEY `_Name` (`_Name`),
369 KEY `_SteamID` (`_SteamID`))
370 ]];
371
372 local PLAYERS_TABLE_QUERY = [[
373 CREATE TABLE IF NOT EXISTS `]]..Clockwork.config:Get("mysql_players_table"):Get()..[[` (
374 `_Key` smallint(11) unsigned NOT NULL AUTO_INCREMENT,
375 `_Data` text NOT NULL,
376 `_Schema` text NOT NULL,
377 `_SteamID` varchar(60) NOT NULL,
378 `_Donations` text NOT NULL,
379 `_UserGroup` text NOT NULL,
380 `_IPAddress` varchar(50) NOT NULL,
381 `_SteamName` varchar(150) NOT NULL,
382 `_OnNextPlay` text NOT NULL,
383 `_LastPlayed` varchar(50) NOT NULL,
384 `_TimeJoined` varchar(50) NOT NULL,
385 PRIMARY KEY (`_Key`),
386 KEY `_SteamID` (`_SteamID`))
387 ]];
388
389 self:Query(string.gsub(BANS_TABLE_QUERY, "%s", " "), nil, nil, true);
390 self:Query(string.gsub(CHARACTERS_TABLE_QUERY, "%s", " "), nil, nil, true);
391 self:Query(string.gsub(PLAYERS_TABLE_QUERY, "%s", " "), nil, nil, true);
392
393 Clockwork.NoMySQL = false;
394 Clockwork.plugin:Call("ClockworkDatabaseConnected");
395
396 for k, v in pairs(self.queryQueue) do
397 self:Query(v[1], v[2], nil, v[3]);
398 end;
399
400 self.queryQueue = {};
401end;
402
403-- Called when the database connection fails.
404function Clockwork.database:OnConnectionFailed(errorText)
405 ErrorNoHalt("[Clockwork] MySQL Error: "..errorText.."\n");
406 Clockwork.NoMySQL = errorText;
407 Clockwork.plugin:Call("ClockworkDatabaseConnectionFailed", errorText);
408end;
409
410-- A function to connect to the database.
411function Clockwork.database:Connect(host, username, password, database, port)
412 if (self.connection) then
413 return;
414 end;
415
416 if (host == "localhost") then
417 host = "127.0.0.1";
418 end;
419
420 if (type(mysqloo) != "table") then
421 require("mysqloo");
422 end;
423
424 if (mysqloo) then
425 self.connection = mysqloo.connect(host, username, password, database, port);
426
427 self.connection.onConnected = function(database)
428 Clockwork.database:OnConnected();
429 end;
430
431 self.connection.onConnectionFailed = function(database, errorText)
432 Clockwork.database:OnConnectionFailed(errorText);
433 end;
434
435 self.connection:connect();
436 else
437 self:OnConnectionFailed("The mysqloo module does not exist!");
438 end;
439end;