cKcCiTHq

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