· 8 years ago · Mar 27, 2018, 07:41 PM
1--[[---------------------------------------------------------------------------
2Functions and variables
3---------------------------------------------------------------------------]]
4local setUpNonOwnableDoors,
5 setUpTeamOwnableDoors,
6 setUpGroupDoors,
7 migrateDB
8
9--[[---------------------------------------------------------
10 Database initialize
11 ---------------------------------------------------------]]
12function DarkRP.initDatabase()
13 MySQLite.begin()
14 -- Gotta love the difference between SQLite and MySQL
15 local AUTOINCREMENT = MySQLite.isMySQL() and "AUTO_INCREMENT" or "AUTOINCREMENT"
16
17 -- Table that holds all position data (jail, spawns etc.)
18 -- Queue these queries because other queries depend on the existence of the darkrp_position table
19 -- Race conditions could occur if the queries are executed simultaneously
20 MySQLite.queueQuery([[
21 CREATE TABLE IF NOT EXISTS darkrp_position(
22 id INTEGER NOT NULL PRIMARY KEY ]] .. AUTOINCREMENT .. [[,
23 map VARCHAR(45) NOT NULL,
24 type CHAR(1) NOT NULL,
25 x INTEGER NOT NULL,
26 y INTEGER NOT NULL,
27 z INTEGER NOT NULL
28 );
29 ]])
30
31 -- team spawns require extra data
32 MySQLite.queueQuery([[
33 CREATE TABLE IF NOT EXISTS darkrp_jobspawn(
34 id INTEGER NOT NULL PRIMARY KEY,
35 team INTEGER NOT NULL
36 );
37 ]])
38
39 if MySQLite.isMySQL() then
40 MySQLite.queueQuery([[
41 SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE
42 CONSTRAINT_SCHEMA = DATABASE() AND
43 CONSTRAINT_NAME = 'fk_darkrp_jobspawn_position' AND
44 CONSTRAINT_TYPE = 'FOREIGN KEY'
45 ]], function(data)
46 if data and data[1] then return end
47
48 MySQLite.query([[
49 ALTER TABLE darkrp_jobspawn ADD CONSTRAINT `fk_darkrp_jobspawn_position` FOREIGN KEY(id) REFERENCES darkrp_position(id)
50 ON UPDATE CASCADE
51 ON DELETE CASCADE;
52 ]])
53 end)
54 end
55
56 MySQLite.query([[
57 CREATE TABLE IF NOT EXISTS playerinformation(
58 uid BIGINT NOT NULL,
59 steamID VARCHAR(50) NOT NULL PRIMARY KEY
60 )
61 ]])
62
63 -- Player information
64 MySQLite.query([[
65 CREATE TABLE IF NOT EXISTS darkrp_player(
66 uid BIGINT NOT NULL PRIMARY KEY,
67 rpname VARCHAR(45),
68 salary INTEGER NOT NULL DEFAULT 45,
69 wallet INTEGER NOT NULL,
70 UNIQUE(rpname)
71 );
72 ]])
73
74 -- Door data
75 MySQLite.query([[
76 CREATE TABLE IF NOT EXISTS darkrp_door(
77 idx INTEGER NOT NULL,
78 map VARCHAR(45) NOT NULL,
79 title VARCHAR(25),
80 isLocked BOOLEAN,
81 isDisabled BOOLEAN NOT NULL DEFAULT FALSE,
82 PRIMARY KEY(idx, map)
83 );
84 ]])
85
86 -- Some doors are owned by certain teams
87 MySQLite.query([[
88 CREATE TABLE IF NOT EXISTS darkrp_doorjobs(
89 idx INTEGER NOT NULL,
90 map VARCHAR(45) NOT NULL,
91 job VARCHAR(255) NOT NULL,
92
93 PRIMARY KEY(idx, map, job)
94 );
95 ]])
96
97 -- Door groups
98 MySQLite.query([[
99 CREATE TABLE IF NOT EXISTS darkrp_doorgroups(
100 idx INTEGER NOT NULL,
101 map VARCHAR(45) NOT NULL,
102 doorgroup VARCHAR(100) NOT NULL,
103
104 PRIMARY KEY(idx, map)
105 )
106 ]])
107
108 MySQLite.queueQuery([[
109 CREATE TABLE IF NOT EXISTS darkrp_dbversion(version INTEGER NOT NULL PRIMARY KEY)
110 ]])
111
112 -- Load the last DBVersion into DarkRP.DBVersion, to allow checks to see whether migration is needed.
113 MySQLite.queueQuery([[
114 SELECT MAX(version) AS version FROM darkrp_dbversion
115 ]], function(data) DarkRP.DBVersion = data and data[1] and tonumber(data[1].version) or 0 end)
116
117 MySQLite.queueQuery([[
118 REPLACE INTO darkrp_dbversion VALUES(20150725)
119 ]])
120
121 -- SQlite doesn't really handle foreign keys strictly, neither does MySQL by default
122 -- So to keep the DB clean, here's a manual partial foreign key enforcement
123 -- For now it's deletion only, since updating of the common attribute doesn't happen.
124
125 -- MySQL trigger
126 if MySQLite.isMySQL() then
127 MySQLite.query("show triggers", function(data)
128 -- Check if the trigger exists first
129 if data then
130 for k,v in pairs(data) do
131 if v.Trigger == "JobPositionFKDelete" then
132 return
133 end
134 end
135 end
136
137 MySQLite.query("SHOW PRIVILEGES", function(privs)
138 if not privs then return end
139
140 local found;
141 for k,v in pairs(privs) do
142 if v.Privilege == "Trigger" then
143 found = true
144 break;
145 end
146 end
147
148 if not found then return end
149 MySQLite.query([[
150 CREATE TRIGGER JobPositionFKDelete
151 AFTER DELETE ON darkrp_position
152 FOR EACH ROW
153 IF OLD.type = "T" THEN
154 DELETE FROM darkrp_jobspawn WHERE darkrp_jobspawn.id = OLD.id;
155 END IF
156 ;
157 ]])
158 end)
159 end)
160 else -- SQLite triggers, quite a different syntax
161 MySQLite.query([[
162 CREATE TRIGGER IF NOT EXISTS JobPositionFKDelete
163 AFTER DELETE ON darkrp_position
164 FOR EACH ROW
165 WHEN OLD.type = "T"
166 BEGIN
167 DELETE FROM darkrp_jobspawn WHERE darkrp_jobspawn.id = OLD.id;
168 END;
169 ]])
170 end
171 MySQLite.commit(fp{migrateDB, -- Migrate the database
172 function() -- Initialize the data after all the tables have been created
173 setUpNonOwnableDoors()
174 setUpTeamOwnableDoors()
175 setUpGroupDoors()
176
177 if MySQLite.isMySQL() then -- In a listen server, the connection with the external database is often made AFTER the listen server host has joined,
178 --so he walks around with the settings from the SQLite database
179 for k,v in pairs(player.GetAll()) do
180 DarkRP.offlinePlayerData(v:SteamID(), function(data)
181 if not data or not data[1] then return end
182
183 local Data = data[1]
184 v:setDarkRPVar("rpname", Data.rpname)
185 v:setSelfDarkRPVar("salary", Data.salary)
186 v:setDarkRPVar("money", Data.wallet)
187 end)
188 end
189 end
190
191 hook.Call("DarkRPDBInitialized")
192 end})
193end
194
195--[[---------------------------------------------------------------------------
196Database migration
197backwards compatibility with older versions of DarkRP
198---------------------------------------------------------------------------]]
199function migrateDB(callback)
200 -- migrte from darkrp_jobown to darkrp_doorjobs
201 MySQLite.tableExists("darkrp_jobown", function(exists)
202 if not exists then return callback() end
203
204 MySQLite.begin()
205 -- Create a temporary table that links job IDs to job commands
206 MySQLite.queueQuery("CREATE TABLE IF NOT EXISTS TempJobCommands(id INT NOT NULL PRIMARY KEY, cmd VARCHAR(255) NOT NULL);")
207 if MySQLite.isMySQL() then
208 local jobCommands = {}
209 for k,v in pairs(RPExtraTeams) do
210 table.insert(jobCommands, "(" .. k .. "," .. MySQLite.SQLStr(v.command) .. ")")
211 end
212
213 -- This WOULD work with SQLite if the implementation in GMod wasn't out of date.
214 MySQLite.queueQuery("INSERT IGNORE INTO TempJobCommands VALUES " .. table.concat(jobCommands, ",") .. ";")
215 else
216 for k,v in pairs(RPExtraTeams) do
217 MySQLite.queueQuery("INSERT INTO TempJobCommands VALUES(" .. k .. ", " .. MySQLite.SQLStr(v.command) .. ");")
218 end
219 end
220
221 MySQLite.queueQuery("REPLACE INTO darkrp_doorjobs SELECT darkrp_jobown.idx AS idx, darkrp_jobown.map AS map, TempJobCommands.cmd AS job FROM darkrp_jobown JOIN TempJobCommands ON darkrp_jobown.job = TempJobCommands.id;")
222
223 -- Clean up the transition table and the old table
224 MySQLite.queueQuery("DROP TABLE TempJobCommands;")
225 MySQLite.queueQuery("DROP TABLE darkrp_jobown;")
226 MySQLite.commit(callback) -- callback
227 end)
228end
229
230--[[---------------------------------------------------------
231Players
232 ---------------------------------------------------------]]
233function DarkRP.storeRPName(ply, name)
234 if not name or string.len(name) < 2 then return end
235 hook.Call("onPlayerChangedName", nil, ply, ply:getDarkRPVar("rpname"), name)
236 ply:setDarkRPVar("rpname", name)
237
238 MySQLite.query([[UPDATE darkrp_player SET rpname = ]] .. MySQLite.SQLStr(name) .. [[ WHERE UID = ]] .. ply:SteamID64() .. ";")
239 MySQLite.query([[UPDATE darkrp_player SET rpname = ]] .. MySQLite.SQLStr(name .. utf8.char(8203)) .. [[ WHERE UID = ]] .. ply:UniqueID() .. ";")
240end
241
242function DarkRP.retrieveRPNames(name, callback)
243 MySQLite.query("SELECT COUNT(*) AS count FROM darkrp_player WHERE rpname = " .. MySQLite.SQLStr(name) .. " OR rpname = " .. MySQLite.SQLStr(name .. utf8.char(8203)) .. ";", function(r)
244 callback(tonumber(r[1].count) > 0)
245 end)
246end
247
248function DarkRP.offlinePlayerData(steamid, callback, failed)
249 local sid64 = util.SteamIDTo64(steamid)
250 local uniqueid = util.CRC("gm_" .. string.upper(steamid) .. "_gm")
251
252 MySQLite.query(string.format([[REPLACE INTO playerinformation VALUES(%s, %s);]], MySQLite.SQLStr(sid64), MySQLite.SQLStr(steamid)), nil, failed)
253
254 local query = [[
255 SELECT rpname, wallet, salary, "SID64" AS kind
256 FROM darkrp_player
257 where uid = %s
258
259 UNION
260
261 SELECT rpname, wallet, salary, "UniqueID" AS kind
262 FROM darkrp_player
263 where uid = %s
264 ;
265 ]]
266
267 MySQLite.query(
268 query:format(sid64, uniqueid),
269 function(data, ...)
270 -- The database has no record of the player data in SteamID64 form
271 -- Otherwise the first row would have kind SID64
272 if data and data[1] and data[1].kind == "UniqueID" then
273 -- The rpname must be unique
274 -- adding a new row with uid = SteamID64, but the same rpname will remove the uid=UniqueID row
275 local changeOldName = [[
276 UPDATE darkrp_player
277 SET rpname = ]] .. (MySQLite.isMySQL() and [[CONCAT(rpname, "]] .. utf8.char(8203) .. [[")]] or [[rpname || "]] .. utf8.char(8203) .. [["]]) .. [[
278 WHERE uid = %s
279 ]]
280
281 local replquery = [[
282 REPLACE INTO darkrp_player(uid, rpname, wallet, salary)
283 VALUES (%s, %s, %s, %s)
284 ]]
285
286 MySQLite.begin()
287 MySQLite.queueQuery(changeOldName:format(uniqueid), nil, failed)
288 MySQLite.queueQuery(
289 replquery:format(
290 sid64,
291 data[1].rpname == "NULL" and "NULL" or MySQLite.SQLStr(data[1].rpname),
292 data[1].wallet,
293 data[1].salary
294 ),
295 nil,
296 failed
297 )
298 MySQLite.commit()
299 end
300
301 return callback and callback(data, ...)
302 end
303 , failed
304 )
305end
306
307function DarkRP.retrievePlayerData(ply, callback, failed, attempts, err)
308 attempts = attempts or 0
309
310 if attempts > 3 then return failed(err) end
311
312 DarkRP.offlinePlayerData(ply:SteamID(), callback, function(sqlErr)
313 DarkRP.retrievePlayerData(ply, callback, failed, attempts + 1, sqlErr)
314 end)
315end
316
317function DarkRP.createPlayerData(ply, name, wallet, salary)
318 MySQLite.query([[REPLACE INTO darkrp_player VALUES(]] ..
319 ply:SteamID64() .. [[, ]] ..
320 MySQLite.SQLStr(name) .. [[, ]] ..
321 salary .. [[, ]] ..
322 wallet .. ");")
323
324 -- Backwards compatibility
325 MySQLite.query([[REPLACE INTO darkrp_player VALUES(]] ..
326 ply:UniqueID() .. [[, ]] ..
327 MySQLite.SQLStr(name) .. [[, ]] ..
328 salary .. [[, ]] ..
329 wallet .. ");")
330end
331
332function DarkRP.storeMoney(ply, amount)
333 if not IsValid(ply) then return end
334 if not isnumber(amount) or amount < 0 or amount >= 1 / 0 then return end
335
336 -- Also keep deprecated UniqueID data at least somewhat up to date
337 MySQLite.query([[UPDATE darkrp_player SET wallet = ]] .. amount .. [[ WHERE uid = ]] .. ply:UniqueID() .. [[ OR uid = ]] .. ply:SteamID64())
338end
339
340function DarkRP.storeOfflineMoney(sid64, amount)
341 if isnumber(sid64) or isstring(sid64) and string.len(sid64) < 18 then -- smaller than 76561197960265728 is not a SteamID64
342 DarkRP.errorNoHalt([[Some addon is giving DarkRP.storeOfflineMoney a UniqueID as its first argument, but this function now expects a SteamID64]], 2,
343 { "The function used to take UniqueIDs, but it does not anymore."
344 , "If you are a server owner, please look closely to the files mentioned in this error"
345 , "After all, these files will tell you WHICH addon is doing it"
346 , "This is NOT a DarkRP bug!"
347 , "Your server will continue working normally"
348 , "But whichever addon just tried to store an offline player's money"
349 , "Will NOT take effect!"
350 })
351 end
352
353 -- Also store on deprecated UniqueID
354 local uniqueid = util.CRC("gm_" .. string.upper(util.SteamIDFrom64(sid64)) .. "_gm")
355 MySQLite.query([[UPDATE darkrp_player SET wallet = ]] .. amount .. [[ WHERE uid = ]] .. uniqueid .. [[ OR uid = ]] .. sid64)
356end
357
358local function resetAllMoney(ply,cmd,args)
359 if ply:EntIndex() ~= 0 and not ply:IsSuperAdmin() then return end
360 MySQLite.query("UPDATE darkrp_player SET wallet = " .. GAMEMODE.Config.startingmoney .. " ;")
361 for k,v in pairs(player.GetAll()) do
362 v:setDarkRPVar("money", GAMEMODE.Config.startingmoney)
363 end
364 if ply:IsPlayer() then
365 DarkRP.notifyAll(0,4, DarkRP.getPhrase("reset_money", ply:Nick()))
366 else
367 DarkRP.notifyAll(0,4, DarkRP.getPhrase("reset_money", "Console"))
368 end
369end
370concommand.Add("rp_resetallmoney", resetAllMoney)
371
372function DarkRP.storeSalary(ply, amount)
373 ply:setSelfDarkRPVar("salary", math.floor(amount))
374
375 return amount
376end
377
378function DarkRP.retrieveSalary(ply, callback)
379 if not IsValid(ply) then return 0 end
380
381 local val =
382 ply:getJobTable() and ply:getJobTable().salary or
383 RPExtraTeams[GAMEMODE.DefaultTeam].salary or
384 (GM or GAMEMODE).Config.normalsalary
385
386 if callback then callback(val) end
387
388 return val
389end
390
391--[[---------------------------------------------------------------------------
392Players
393---------------------------------------------------------------------------]]
394local meta = FindMetaTable("Player")
395function meta:restorePlayerData()
396 if not IsValid(self) then return end
397 self.DarkRPUnInitialized = true
398
399 DarkRP.retrievePlayerData(self, function(data)
400 if not IsValid(self) then return end
401
402 self.DarkRPUnInitialized = nil
403
404 local info = data and data[1] or {}
405 if not info.rpname or info.rpname == "NULL" then info.rpname = string.gsub(self:SteamName(), "\\\"", "\"") end
406
407 info.wallet = info.wallet or GAMEMODE.Config.startingmoney
408 info.salary = DarkRP.retrieveSalary(self)
409
410 self:setDarkRPVar("money", tonumber(info.wallet))
411 self:setSelfDarkRPVar("salary", tonumber(info.salary))
412
413 self:setDarkRPVar("rpname", info.rpname)
414
415 if not data then
416 DarkRP.createPlayerData(self, info.rpname, info.wallet, info.salary)
417 end
418 end, function(err) -- Retrieving data failed, go on without it
419 self.DarkRPUnInitialized = true -- no information should be saved from here, or the playerdata might be reset
420 self.DarkRPDataRetrievalFailed = true -- marker on the player that says shit is fucked
421
422 self:setDarkRPVar("money", GAMEMODE.Config.startingmoney)
423 self:setSelfDarkRPVar("salary", DarkRP.retrieveSalary(self))
424 local name = string.gsub(self:SteamName(), "\\\"", "\"")
425 self:setDarkRPVar("rpname", name)
426
427 DarkRP.error("Failed to retrieve player information from the database. ", nil, {"This means your database or the connection to your database is fucked.", "This is the error given by the database:\n\t\t" .. tostring(err)})
428 end)
429end
430
431--[[---------------------------------------------------------
432 Doors
433 ---------------------------------------------------------]]
434function DarkRP.storeDoorData(ent)
435 if not ent:CreatedByMap() then return end
436 local map = string.lower(game.GetMap())
437 local nonOwnable = ent:getKeysNonOwnable()
438 local title = ent:getKeysTitle()
439
440 MySQLite.query([[REPLACE INTO darkrp_door VALUES(]] .. ent:doorIndex() .. [[, ]] .. MySQLite.SQLStr(map) .. [[, ]] .. (title and MySQLite.SQLStr(title) or "NULL") .. [[, ]] .. "NULL" .. [[, ]] .. (nonOwnable and 1 or 0) .. [[);]])
441end
442
443function setUpNonOwnableDoors()
444 MySQLite.query("SELECT idx, title, isLocked, isDisabled FROM darkrp_door WHERE map = " .. MySQLite.SQLStr(string.lower(game.GetMap())) .. ";", function(r)
445 if not r then return end
446
447 for _, row in pairs(r) do
448 local e = DarkRP.doorIndexToEnt(tonumber(row.idx))
449
450 if not IsValid(e) then continue end
451 if e:isKeysOwnable() then
452 if tobool(row.isDisabled) then
453 e:setKeysNonOwnable(tobool(row.isDisabled))
454 end
455 if row.isLocked ~= nil then
456 if row.isLocked ~= "NULL" then e:Fire((tobool(row.isLocked) and "" or "un") .. "lock", "", 0) end
457 end
458 e:setKeysTitle(row.title ~= "NULL" and row.title or nil)
459 end
460 end
461 end)
462end
463
464local keyValueActions = {
465 ["DarkRPNonOwnable"] = function(ent, val) ent:setKeysNonOwnable(tobool(val)) end,
466 ["DarkRPTitle"] = function(ent, val) ent:setKeysTitle(val) end,
467 ["DarkRPDoorGroup"] = function(ent, val) if RPExtraTeamDoors[val] then ent:setDoorGroup(val) end end,
468 ["DarkRPCanLockpick"] = function(ent, val) ent.DarkRPCanLockpick = tobool(val) end
469}
470
471local function onKeyValue(ent, key, value)
472 if not ent:isDoor() then return end
473
474 if keyValueActions[key] then
475 keyValueActions[key](ent, value)
476 end
477end
478hook.Add("EntityKeyValue", "darkrp_doors", onKeyValue)
479
480function DarkRP.storeTeamDoorOwnability(ent)
481 if not ent:CreatedByMap() then return end
482 local map = string.lower(game.GetMap())
483
484 MySQLite.query("DELETE FROM darkrp_doorjobs WHERE idx = " .. ent:doorIndex() .. " AND map = " .. MySQLite.SQLStr(map) .. ";")
485 for k,v in pairs(ent:getKeysDoorTeams() or {}) do
486 MySQLite.query("INSERT INTO darkrp_doorjobs VALUES(" .. ent:doorIndex() .. ", " .. MySQLite.SQLStr(map) .. ", " .. MySQLite.SQLStr(RPExtraTeams[k].command) .. ");")
487 end
488end
489
490function setUpTeamOwnableDoors()
491 MySQLite.query("SELECT idx, job FROM darkrp_doorjobs WHERE map = " .. MySQLite.SQLStr(string.lower(game.GetMap())) .. ";", function(r)
492 if not r then return end
493 local map = string.lower(game.GetMap())
494
495 for _, row in pairs(r) do
496 row.idx = tonumber(row.idx)
497
498 local e = DarkRP.doorIndexToEnt(row.idx)
499 if not IsValid(e) then continue end
500
501 local _, job = DarkRP.getJobByCommand(row.job)
502
503 if job then
504 e:addKeysDoorTeam(job)
505 else
506 print(("can't find job %s for door %d, removing from database"):format(row.job, row.idx))
507 MySQLite.query(("DELETE FROM darkrp_doorjobs WHERE idx = %d AND map = %s AND job = %s;"):format(row.idx, MySQLite.SQLStr(map), MySQLite.SQLStr(row.job)))
508 end
509 end
510 end)
511end
512
513function DarkRP.storeDoorGroup(ent, group)
514 if not ent:CreatedByMap() then return end
515 local map = MySQLite.SQLStr(string.lower(game.GetMap()))
516 local index = ent:doorIndex()
517
518 if group == "" or not group then
519 MySQLite.query("DELETE FROM darkrp_doorgroups WHERE map = " .. map .. " AND idx = " .. index .. ";")
520 return
521 end
522
523 MySQLite.query("REPLACE INTO darkrp_doorgroups VALUES(" .. index .. ", " .. map .. ", " .. MySQLite.SQLStr(group) .. ");");
524end
525
526function setUpGroupDoors()
527 local map = MySQLite.SQLStr(string.lower(game.GetMap()))
528 MySQLite.query("SELECT idx, doorgroup FROM darkrp_doorgroups WHERE map = " .. map, function(data)
529 if not data then return end
530
531 for _, row in pairs(data) do
532 local ent = DarkRP.doorIndexToEnt(tonumber(row.idx))
533
534 if not IsValid(ent) or not ent:isKeysOwnable() then
535 continue
536 end
537
538 if not RPExtraTeamDoorIDs[row.doorgroup] then continue end
539 ent:setDoorGroup(row.doorgroup)
540 end
541 end)
542end
543
544hook.Add("PostCleanupMap", "DarkRP.hooks", function()
545 setUpNonOwnableDoors()
546 setUpTeamOwnableDoors()
547 setUpGroupDoors()
548end)