· 8 years ago · Oct 03, 2017, 09:10 PM
1nut.db = nut.db or {}
2nut.util.include("nutscript/gamemode/config/sv_database.lua")
3
4local function ThrowQueryFault(query, fault)
5 MsgC(Color(255, 0, 0), "* "..query.."\n")
6 MsgC(Color(255, 0, 0), fault.."\n")
7end
8
9local function ThrowConnectionFault(fault)
10 MsgC(Color(255, 0, 0), "NutScript has failed to connect to the database.\n")
11 MsgC(Color(255, 0, 0), fault.."\n")
12
13 setNetVar("dbError", fault)
14end
15
16local modules = {}
17
18-- SQLite for local storage.
19modules.sqlite = {
20 query = function(query, callback)
21 local data = sql.Query(query)
22 local fault = sql.LastError()
23
24 if (data == false) then
25 ThrowQueryFault(query, fault)
26 end
27
28 if (callback) then
29 local lastID = tonumber(sql.QueryValue("SELECT last_insert_rowid()"))
30
31 callback(data, lastID)
32 end
33 end,
34 escape = function(value)
35 return sql.SQLStr(value, true)
36 end,
37 connect = function(callback)
38 if (callback) then
39 callback()
40 end
41 end
42}
43
44-- tmysql4 module for MySQL storage.
45modules.tmysql4 = {
46 query = function(query, callback)
47 if (nut.db.object) then
48 nut.db.object:Query(query, function(data, status, lastID)
49 if (QUERY_SUCCESS and status == QUERY_SUCCESS) then
50 if (callback) then
51 callback(data, lastID)
52 end
53 else
54 if (data and data[1]) then
55 if (data[1].status) then
56 if (callback) then
57 callback(data[1].data, data[1].lastid)
58 end
59
60 return
61 else
62 lastID = data[1].error
63 end
64 end
65
66 file.Write("nut_queryerror.txt", query)
67 ThrowQueryFault(query, lastID or "")
68 end
69 end, 3)
70 end
71 end,
72 escape = function(value)
73 if (nut.db.object) then
74 return nut.db.object:Escape(value)
75 end
76
77 return tmysql and tmysql.escape and tmysql.escape(value) or sql.SQLStr(value, true)
78 end,
79 connect = function(callback)
80 if (!pcall(require, "tmysql4")) then
81 return setNetVar("dbError", system.IsWindows() and "Server is missing VC++ redistributables!" or "Server is missing binaries for tmysql4!")
82 end
83
84 local hostname = nut.db.hostname
85 local username = nut.db.username
86 local password = nut.db.password
87 local database = nut.db.database
88 local port = nut.db.port
89 local object, fault = tmysql.initialize(hostname, username, password, database, port)
90
91 if (object) then
92 nut.db.object = object
93 nut.db.escape = modules.tmysql4.escape
94 nut.db.query = modules.tmysql4.query
95
96 if (callback) then
97 callback()
98 end
99 else
100 ThrowConnectionFault(fault)
101 end
102 end
103}
104
105MYSQLOO_QUEUE = MYSQLOO_QUEUE or {}
106
107-- mysqloo for MySQL storage.
108modules.mysqloo = {
109 query = function(query, callback)
110 if (nut.db.object) then
111 local object = nut.db.object:query(query)
112
113 if (callback) then
114 function object:onSuccess(data)
115 callback(data, self:lastInsert())
116 end
117 end
118
119 function object:onError(fault)
120 if (nut.db.object:status() == mysqloo.DATABASE_NOT_CONNECTED) then
121 MYSQLOO_QUEUE[#MYSQLOO_QUEUE + 1] = {query, callback}
122 nut.db.connect()
123
124 return
125 end
126
127 ThrowQueryFault(query, fault)
128 end
129
130 object:start()
131 end
132 end,
133 escape = function(value)
134 local object = nut.db.object
135
136 if (object) then
137 return object:escape(value)
138 else
139 return sql.SQLStr(value, true)
140 end
141 end,
142 connect = function(callback)
143 if (!pcall(require, "mysqloo")) then
144 return setNetVar("dbError", system.IsWindows() and "Server is missing VC++ redistributables!" or "Server is missing binaries for mysqloo!")
145 end
146
147 local hostname = nut.db.hostname
148 local username = nut.db.username
149 local password = nut.db.password
150 local database = nut.db.database
151 local port = nut.db.port
152 local object = mysqloo.connect(hostname, username, password, database, port)
153
154 function object:onConnected()
155 nut.db.object = self
156 nut.db.escape = modules.mysqloo.escape
157 nut.db.query = modules.mysqloo.query
158
159 for k, v in ipairs(MYSQLOO_QUEUE) do
160 nut.db.query(v[1], v[2])
161 end
162
163 MYSQLOO_QUEUE = {}
164
165 if (callback) then
166 callback()
167 end
168 end
169
170 function object:onConnectionFailed(fault)
171 ThrowConnectionFault(fault)
172 end
173
174 object:connect()
175
176 timer.Create("nutMySQLWakeUp", 300, 0, function()
177 nut.db.query("SELECT 1 + 1")
178 end)
179 end
180}
181
182-- Add default values here.
183nut.db.escape = modules.sqlite.escape
184nut.db.query = modules.sqlite.query
185
186function nut.db.connect(callback)
187 local dbModule = modules[nut.db.module]
188
189 if (dbModule) then
190 if (!nut.db.object) then
191 dbModule.connect(callback)
192 end
193
194 nut.db.escape = dbModule.escape
195 nut.db.query = dbModule.query
196 else
197 ErrorNoHalt("[NutScript] '"..(nut.db.module or "nil").."' is not a valid data storage method!\n")
198 end
199end
200
201local MYSQL_CREATE_TABLES = [[
202CREATE TABLE IF NOT EXISTS `nut_characters` (
203 `_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
204 `_name` varchar(70) NOT NULL,
205 `_desc` text NOT NULL,
206 `_model` varchar(160) NOT NULL,
207 `_attribs` varchar(500) DEFAULT NULL,
208 `_schema` varchar(24) NOT NULL,
209 `_createTime` int(11) unsigned NOT NULL,
210 `_lastJoinTime` int(4) DEFAULT NULL,
211 `_steamID` bigint(20) unsigned NOT NULL,
212 `_data` longtext,
213 `_money` int(11) unsigned DEFAULT NULL,
214 `_faction` varchar(50) NOT NULL,
215 PRIMARY KEY (`_id`)
216);
217
218CREATE TABLE IF NOT EXISTS `nut_inventories` (
219 `_invID` int(11) unsigned NOT NULL AUTO_INCREMENT,
220 `_charID` int(11) unsigned NOT NULL,
221 `_invType` varchar(24) DEFAULT NULL,
222 PRIMARY KEY (`_invID`)
223);
224
225CREATE TABLE IF NOT EXISTS `nut_items` (
226 `_itemID` int(11) unsigned NOT NULL AUTO_INCREMENT,
227 `_invID` int(11) unsigned NOT NULL,
228 `_uniqueID` varchar(60) NOT NULL,
229 `_data` varchar(500) DEFAULT NULL,
230 `_x` smallint(4) NOT NULL,
231 `_y` smallint(4) NOT NULL,
232 PRIMARY KEY (`_itemID`)
233);
234
235CREATE TABLE IF NOT EXISTS `nut_players` (
236 `_steamID` bigint(20) NOT NULL,
237 `_steamName` varchar(32) NOT NULL,
238 `_playTime` int(11) unsigned DEFAULT NULL,
239 `_address` varchar(15) DEFAULT NULL,
240 `_lastJoin` int(11) unsigned DEFAULT NULL,
241 `_data` text,
242 PRIMARY KEY (`_steamID`)
243);
244]]
245
246local SQLITE_CREATE_TABLES = [[
247CREATE TABLE IF NOT EXISTS `nut_characters` (
248 `_id` INTEGER PRIMARY KEY,
249 `_name` TEXT,
250 `_desc` TEXT,
251 `_model` TEXT,
252 `_attribs` TEXT,
253 `_schema` TEXT,
254 `_createTime` INTEGER,
255 `_lastJoinTime` INTEGER,
256 `_steamID` INTEGER,
257 `_data` TEXT,
258 `_money` INTEGER,
259 `_faction` TEXT,
260 `_background` TEXT
261);
262
263CREATE TABLE IF NOT EXISTS `nut_inventories` (
264 `_invID` INTEGER PRIMARY KEY,
265 `_charID` INTEGER,
266 `_invType` TEXT
267);
268
269CREATE TABLE IF NOT EXISTS `nut_items` (
270 `_itemID` INTEGER PRIMARY KEY,
271 `_invID` INTEGER,
272 `_x` INTEGER,
273 `_y` INTEGER,
274 `_uniqueID` TEXT,
275 `_data` TEXT
276);
277
278CREATE TABLE IF NOT EXISTS `nut_players` (
279 `_steamID` INTEGER,
280 `_steamName` TEXT,
281 `_playTime` INTEGER,
282 `_address` TEXT,
283 `_lastJoin` INTEGER,
284 `_data` TEXT
285);
286]]
287
288local DROP_QUERY = [[
289DROP TABLE IF EXISTS `nut_characters`;
290DROP TABLE IF EXISTS `nut_items`;
291DROP TABLE IF EXISTS `nut_players`;
292DROP TABLE IF EXISTS `nut_inventories`;
293]]
294
295function nut.db.wipeTables()
296 local function callback()
297 MsgC(Color(255, 0, 0), "[Nutscript] ALL NUTSCRIPT DATA HAS BEEN WIPED\n")
298 end
299
300 if (nut.db.object) then
301 local queries = string.Explode(";", DROP_QUERY)
302
303 for i = 1, 4 do
304 nut.db.query(queries[i], callback)
305 end
306 else
307 nut.db.query(DROP_QUERY, callback)
308 end
309
310 nut.db.loadTables()
311end
312
313local resetCalled = 0
314concommand.Add("nut_recreatedb", function(client, cmd, arguments)
315 -- this command can be run in RCON or SERVER CONSOLE
316 if (!IsValid(client)) then
317 if (resetCalled < RealTime()) then
318 resetCalled = RealTime() + 3
319
320 MsgC(Color(255, 0, 0), "[Nutscript] TO CONFIRM DATABASE RESET, RUN 'nut_recreatedb' AGAIN in 3 SECONDS.\n")
321 else
322 resetCalled = 0
323
324 MsgC(Color(255, 0, 0), "[Nutscript] DATABASE WIPE IN PROGRESS.\n")
325
326 hook.Run("OnWipeTables")
327 nut.db.wipeTables()
328 end
329 end
330end)
331
332function nut.db.loadTables()
333 if (nut.db.object) then
334 -- This is needed to perform multiple queries since the string is only 1 big query.
335 local queries = string.Explode(";", MYSQL_CREATE_TABLES)
336
337 for i = 1, 4 do
338 nut.db.query(queries[i])
339 end
340 else
341 nut.db.query(SQLITE_CREATE_TABLES)
342 end
343
344 hook.Run("OnLoadTables")
345end
346
347function nut.db.convertDataType(value)
348 if (type(value) == "string") then
349 return "'"..nut.db.escape(value).."'"
350 elseif (type(value) == "table") then
351 return "'"..nut.db.escape(util.TableToJSON(value)).."'"
352 end
353
354 return value
355end
356
357function nut.db.insertTable(value, callback, dbTable)
358 local query = "INSERT INTO "..("nut_"..(dbTable or "characters")).." ("
359 local keys = {}
360 local values = {}
361
362 for k, v in pairs(value) do
363 keys[#keys + 1] = k
364 values[#keys] = k:find("steamID") and v or nut.db.convertDataType(v)
365 end
366
367 query = query..table.concat(keys, ", ")..") VALUES ("..table.concat(values, ", ")..")"
368 nut.db.query(query, callback)
369end
370
371function nut.db.updateTable(value, callback, dbTable, condition)
372 local query = "UPDATE "..("nut_"..(dbTable or "characters")).." SET "
373 local changes = {}
374
375 for k, v in pairs(value) do
376 changes[#changes + 1] = k.." = "..(k:find("steamID") and v or nut.db.convertDataType(v))
377 end
378
379 query = query..table.concat(changes, ", ")..(condition and " WHERE "..condition or "")
380 nut.db.query(query, callback)
381end