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