· 8 years ago · Aug 03, 2018, 09:20 PM
1local Module = { Name = "mysql" }
2local enc, dec
3do
4 local b='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'
5
6 enc = function(data)
7 return ((data:gsub('.', function(x)
8 local r,b='',x:byte()
9 for i=8,1,-1 do r=r..(b%2^i-b%2^(i-1)>0 and '1' or '0') end
10 return r;
11 end)..'0000'):gsub('%d%d%d?%d?%d?%d?', function(x)
12 if (#x < 6) then return '' end
13 local c=0
14 for i=1,6 do c=c+(x:sub(i,i)=='1' and 2^(6-i) or 0) end
15 return b:sub(c+1,c+1)
16 end)..({ '', '==', '=' })[#data%3+1])
17 end
18
19 dec = function(data)
20 data = string.gsub(data, '[^'..b..'=]', '')
21 return (data:gsub('.', function(x)
22 if (x == '=') then return '' end
23 local r,f='',(b:find(x)-1)
24 for i=6,1,-1 do r=r..(f%2^i-f%2^(i-1)>0 and '1' or '0') end
25 return r;
26 end):gsub('%d%d%d?%d?%d?%d?%d?%d?', function(x)
27 if (#x ~= 8) then return '' end
28 local c=0
29 for i=1,8 do c=c+(x:sub(i,i)=='1' and 2^(8-i) or 0) end
30 return string.char(c)
31 end))
32 end
33end
34
35function Module:Initialize()
36 self.m_intSnapshotInterval = GAMEMODE.Config.DB.MySQLSnapshotInterval
37 local pmeta = debug.getregistry().Player
38 if game.SinglePlayer() then
39 g_OldSID = g_OldSID or pmeta.SteamID
40 function pmeta:SteamID()
41 return "singleplayer"
42 end
43 g_OldSID64 = g_OldSID64 or pmeta.SteamID64
44 function pmeta:SteamID64()
45 return "singleplayer"
46 end
47 end
48
49 function pmeta:NewGame()
50 timer.Simple(5, function()
51 Module:MarkDiffDirty( self, "GameSet" )
52 self:AddItem("money", 100)
53 self:AddItem("item_smallammo_small", 3)
54 self:AddItem("weapon_melee_metalmace", 1)
55 self:AddItem("weapon_ranged_junkpistol", 1)
56 self:AddItem("armor_shoulder_light", 1)
57 self:AddItem("book_of_beginning", 1)
58 end)
59 end
60
61 function pmeta:LoadGame()
62 if self.m_bLoadingGameData then return end
63 self.m_bLoadingGameData = true
64
65 self.Race = "human"
66 for name, stat in pairs( GAMEMODE.DataBase.Stats ) do
67 self:SetStat( name, stat.Default )
68 end
69
70 Module:LoadPlayerData( self, function( bErr, tblData )
71 if bErr then
72 if IsValid( self ) then self:Kick( "There was an issue loading your player data, please try rejoining." ) end
73 Module:Error( "Fatal Error! LoadPlayerData dropping client from game." )
74 return
75 elseif not IsValid( self ) then
76 return
77 end
78 self.m_bLoadingGameData = false
79 self.Data = tblData
80
81 --Apply data from ud_players to players
82 self:SetPlayerSQLID( tblData.ID )
83 self:SetNWInt( "exp", tblData.Exp or 0 )
84 self:SetNWInt( "SkillPoints", self:GetDeservedSkillPoints() )
85 self:SetModel( tblData.Model or "models/player/Group01/male_02.mdl" )
86 if tblData.Usergroup then
87 self:SetUserGroup( tblData.Usergroup )
88 end
89
90 --Starter data modules
91 tblData.Name = self:GetName()
92 tblData.LastSeen = os.time()
93 Module:MarkDiffDirty( self, "LastSeen" )
94 Module:MarkDiffDirty( self, "Name" )
95
96 --Applies data
97 --Applies data in a functional order (a - b)
98 for _, v in ipairs( Module:GetSaveTable() ) do
99 if tblData.SaveTables[v.LuaName] then
100 v.LoadFunc( self, tblData.SaveTables[v.LuaName] )
101 end
102 end
103
104 self.Loaded = true
105 self:SetNWBool( "Loaded", true )
106
107 if tblData.NewPlayer then
108 self:NewGame()
109 end
110 hook.Call( "UD_Hook_PlayerLoad", GAMEMODE, self, tblData.LastHealth or 100 )
111
112 for _, ply in pairs( player.GetAll() ) do
113 if ply ~= self and ply.Data and ply.Data.Paperdoll then
114 for slot, item in pairs( ply.Data.Paperdoll ) do
115 SendUsrMsg( "UD_UpdatePapperDoll", self, {ply, slot, item} )
116 end
117 end
118 end
119 end )
120 end
121
122 function pmeta:SaveGame( strDiffFlag, strSubFlag )
123 Module:MarkDiffDirty( self, strDiffFlag, strSubFlag )
124 end
125
126 function pmeta:CommitSaveGame( funcSaved )
127 if not self.Loaded or not self.Data then return false end
128 if self.m_bInSave then return false end
129 self.m_bInSave = true
130 Module:CommitPlayerDiffs( self:SteamID64(), function( bErr )
131 if not IsValid( self ) then return end
132 self.m_bInSave = false
133 if funcSaved then funcSaved() end
134 end )
135 return true
136 end
137
138 function pmeta:IsDoingSave()
139 return self.m_bInSave
140 end
141
142 hook.Add( "Tick", "Underdone_MySQLUpdate", function()
143 for _, v in pairs( player.GetAll() ) do
144 self:TickPlayer( v )
145 end
146 end )
147 hook.Add( "ShutDown", "Underdone_MySQLUpdate", function()
148 for _, v in pairs( player.GetAll() ) do
149 self:PlayerDisconnected( v:SteamID64() )
150 end
151 end )
152 hook.Add( "PlayerDisconnected", "Underdone_MySQLUpdate", function( pPlayer )
153 self.m_tblPlayerData[pPlayer:SteamID64()].LastHealth = math.max( 1, pPlayer:Health() )
154 Module:MarkDiffDirty( pPlayer, "LastHealth" )
155 self:PlayerDisconnected( pPlayer:SteamID64() )
156 end )
157 hook.Add( "player_disconnect", "Underdone_MySQLUpdate", function( tblData )
158 self:PlayerDisconnected( util.SteamIDTo64(tblData.networkid) )
159 end )
160
161 self:Connect(
162 GAMEMODE.Config.DB.Host,
163 GAMEMODE.Config.DB.Username,
164 GAMEMODE.Config.DB.Password,
165 GAMEMODE.Config.DB.Database,
166 GAMEMODE.Config.DB.Port
167 )
168 game.ConsoleCommand( "sv_hibernate_think 1\n" )
169 gameevent.Listen( "player_disconnect" )
170end
171
172-- MySQL functions
173-- ----------------------------------------------------------------
174function Module:Msg( str )
175 print( "[UD:MySQL] ".. str )
176end
177
178function Module:Error( str )
179 error( "[UD:MySQL] ".. str.. "\n" )
180end
181
182function Module:ErrorNoHalt( str )
183 ErrorNoHalt( "[UD:MySQL] ".. str.. "\n" )
184end
185
186function Module:Connect( strHost, strUser, strPass, strDBName, intPort, ... )
187 pcall( require, "mysqloo" )
188 if not mysqloo then
189 self:Error( "Fatal Error! Unable to load gmsv_mysqloo module!" )
190 return
191 end
192
193 self.m_pDB = mysqloo.connect( strHost, strUser, strPass, strDBName, intPort, ... )
194 self.m_pDB.onConnected = function( pDB )
195 self:OnDatabaseConnected( pDB )
196
197 if self.m_tblQueryBuffer then
198 for i = 1, #self.m_tblQueryBuffer do
199 self:Query( self.m_tblQueryBuffer[i][1], self.m_tblQueryBuffer[i][2] )
200 end
201 self.m_tblQueryBuffer = nil
202 end
203 end
204 self.m_pDB.onConnectionFailed = function( pDB, strErr )
205 self:OnDatabaseConnectionFailed( pDB, strErr )
206 end
207
208 self:Msg( "Connecting to database..." )
209 self.m_pDB:connect()
210end
211
212function Module:OnDatabaseConnected( pDB )
213 self:Msg( "Successfully connected to the database" )
214 self:InitTables()
215end
216
217function Module:OnDatabaseConnectionFailed( pDB, strErr )
218 self:Msg( "Fatal Error! Connection to database failed! MySQL said: ".. strErr )
219end
220
221function Module:Escape( str )
222 return self.m_pDB and self.m_pDB:escape( str ) or SQLStr( str, true )
223end
224
225function Module:GetDatabase()
226 return self.m_pDB
227end
228
229function Module:Query( strQuery, fnCallback )
230 if not self.m_pDB then
231 self.m_tblQueryBuffer = self.m_tblQueryBuffer or {}
232 self.m_tblQueryBuffer[#self.m_tblQueryBuffer+1] = { strQuery, fnCallback }
233 return
234 end
235
236 local q = self.m_pDB:query( strQuery )
237 q.onSuccess = function( q, data )
238 if fnCallback then fnCallback( false, data ) end
239 end
240 q.onError = function( q, err )
241 if fnCallback then fnCallback( true, err ) end
242 end
243 q.onAbort = function( q )
244 end
245 q.onData = function( q, data )
246 end
247
248 q:start()
249end
250
251
252-- Player save/load functionality
253-- ----------------------------------------------------------------
254Module.m_tblDiffFlags = {}
255Module.m_tblSaveTables = {}
256Module.m_tblPlayerData = {}
257Module.m_tblRunInit = {
258 --Root players table
259 [[CREATE TABLE IF NOT EXISTS `ud_players` (
260 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
261 `steamid` VARCHAR(255) NOT NULL,
262 `name` VARCHAR(25) NOT NULL,
263 `usergroup` VARCHAR(255) DEFAULT NULL,
264 `model` TEXT NOT NULL,
265 `exp` INT UNSIGNED NOT NULL DEFAULT 0,
266 `setup` INT NOT NULL DEFAULT 0,
267 `lasthealth` INT UNSIGNED NOT NULL DEFAULT 100,
268 `lastseen` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
269 PRIMARY KEY (`id`),
270 UNIQUE INDEX `ud_id_steamid` (`id`, `steamid`),
271 INDEX `steamid` (`steamid`)
272 ) ENGINE=InnoDB;]],
273}
274
275local pmeta = debug.getregistry().Player
276function pmeta:GetPlayerSQLID()
277 return self.m_intSQLID
278end
279
280function pmeta:SetPlayerSQLID( intID )
281 self.m_intSQLID = tonumber( intID )
282end
283
284function Module:GetSaveTable()
285 return self.m_tblSaveTables
286end
287
288function Module:InitTables()
289 for _, v in pairs( self.m_tblRunInit ) do
290 self:Query( v, function( b, err )
291 if b then
292 self:Error( err )
293 end
294 end )
295 end
296 for _, v in pairs( self.m_tblSaveTables ) do
297 self:Query( v.Queries.Init, function( b, err )
298 if b then
299 self:Error( err )
300 end
301 end )
302 end
303end
304
305function Module:InsertNewPlayer( pPlayer, funcOnInsert )
306 self:Query( ([[INSERT INTO ud_players SET
307 steamid='%s',
308 name='%s',
309 model='%s',
310 exp=%d,
311 lasthealth=%d,
312 lastseen=FROM_UNIXTIME(%d)]]
313 ):format(
314 pPlayer:SteamID64(),
315 self:Escape( pPlayer:GetName() ),
316 self:Escape( pPlayer:GetModel() ),
317 0,
318 100,
319 os.time()
320 ), function( bErr, tblData )
321 if not IsValid( pPlayer ) then return end
322 funcOnInsert( bErr, tblData )
323 end )
324end
325
326--Load all data that links back to ud_players `id`
327function Module:LoadPlayerData( pPlayer, fnCallback, bAutoInsertCallback )
328 local sid = pPlayer:SteamID64()
329 local errors = false
330 local dataProto = {
331 ID = -1,
332 SaveTables = {},
333 }
334
335 --Load from ud_players
336 local playerRowQuery = ([[SELECT * FROM ud_players WHERE steamid = '%s']]):format( sid )
337 self:Query( playerRowQuery, function( bErr, tblData )
338 if not IsValid( pPlayer ) then return end
339 if bErr then
340 Module:ErrorNoHalt( "Fatal Error! LoadPlayerData dropping client from game. MySQL said: ".. tblData )
341 pPlayer:Kick( "There was an issue loading your player data, please try rejoining." )
342 return
343 end
344
345 tblData = tblData[1]
346 if not istable( tblData ) or not tblData.id then
347 if bAutoInsertCallback then
348 Module:ErrorNoHalt( "Fatal Error! LoadPlayerData dropping client from game. MySQL said: ".. tblData )
349 pPlayer:Kick( "There was an issue loading your player data, please try rejoining." )
350 return
351 end
352 self:InsertNewPlayer( pPlayer, function( err, data )
353 if not IsValid( pPlayer ) then return end
354 if not err then
355 self:LoadPlayerData( pPlayer, fnCallback, true )
356 else
357 Module:ErrorNoHalt( "Fatal Error! LoadPlayerData dropping client from game. MySQL said: ".. data )
358 pPlayer:Kick( "There was an issue loading your player data, please try rejoining." )
359 end
360 end )
361
362 return
363 end
364 dataProto.ID = tblData.id
365 dataProto.Sid = pPlayer:SteamID()
366 dataProto.LastHealth = tblData.lasthealth or 100
367 dataProto.LastSeen = os.time()
368 dataProto.Level = tblData.level
369 dataProto.Exp = tblData.exp
370 dataProto.Model = tblData.model or "models/player/Group01/male_02.mdl"
371 dataProto.Usergroup = tblData.usergroup
372 dataProto.NewPlayer = bAutoInsertCallback and true
373
374 if not dataProto.NewPlayer and (not tblData.setup or tblData.setup ~= 1) then
375 pPlayer:NewGame() --Re-give the player new game items, setup failed last time and they lost these items
376 end
377
378 --Load all save tables, then call callback
379 local done, count = 0, table.Count( self.m_tblSaveTables )
380 for _, v in pairs( self.m_tblSaveTables ) do
381 v.QueryFunc( dataProto.ID, function( b, data )
382 done = done +1
383
384 if not b then
385 dataProto.SaveTables[v.LuaName] = data
386 else
387 errors = true
388 self:ErrorNoHalt( "Query Error! LoadPlayerData:".. sid.. ": ".. data )
389 end
390
391 if done >= count then
392 self.m_tblPlayerData[sid] = dataProto
393 self:ClearPlayerDiffTable( sid )
394 fnCallback( errors, dataProto )
395 end
396 end )
397 end
398 end )
399end
400
401function Module:DefineSaveTable( tblData )
402 self.m_tblSaveTables[#self.m_tblSaveTables +1] = tblData
403 self:RegisterDiffFlag( tblData.LuaName, tblData.DiffFunc )
404end
405
406function Module:GetQuery( strLuaName, strQueryK )
407 for k, v in pairs( self.m_tblSaveTables ) do
408 if v.LuaName == strLuaName then
409 return v.Queries[strQueryK]
410 end
411 end
412end
413
414function Module:CommitPlayerDiffs( strSID64, fnCallback )
415 local gameData = self.m_tblPlayerData[strSID64]
416 if not gameData or gameData.m_bInSQLUpdate then return end
417
418 local playerID = gameData.ID
419 if not playerID then return end
420
421 local diffFlags = gameData.SQLDiffTable
422 if not diffFlags or not diffFlags.Flags then return end
423
424 local transaction = self.m_pDB:createTransaction()
425 for k, v in pairs( diffFlags.Flags ) do
426 local ret = self.m_tblDiffFlags[k](playerID, gameData, diffFlags.Flags)
427 if istable( ret ) then
428 for k, v in ipairs( ret ) do
429 transaction:addQuery( self.m_pDB:query(v) )
430 end
431 else
432 transaction:addQuery( self.m_pDB:query(ret) )
433 end
434 end
435
436 function transaction:onSuccess()
437 Module:ClearPlayerDiffTable( strSID64 )
438 gameData.m_bInSQLUpdate = false
439 if fnCallback then fnCallback( true ) end
440 end
441
442 function transaction:onError( strErr )
443 gameData.m_bInSQLUpdate = false
444 self:ErrorNoHalt( "transaction error!", strErr )
445 if fnCallback then fnCallback( false ) end
446 end
447
448 gameData.m_bInSQLUpdate = true
449 transaction:start()
450end
451
452function Module:RegisterDiffFlag( strFlagID, funcUpdate )
453 self.m_tblDiffFlags[strFlagID] = funcUpdate
454end
455
456function Module:ClearPlayerDiffTable( strSID64 )
457 if not self.m_tblPlayerData[strSID64] then return end
458 self.m_tblPlayerData[strSID64].SQLDiffTable = {
459 Flags = {},
460 NextSnapshot = CurTime() +(self.m_intSnapshotInterval or 60)
461 }
462end
463
464function Module:MarkDiffDirty( pPlayer, strFlagID, strSubFlag )
465 local data = self.m_tblPlayerData[pPlayer:SteamID64()]
466 if not data or not data.SQLDiffTable then return end
467 if not self.m_tblDiffFlags[strFlagID] then return end
468
469 if strSubFlag then
470 data.SQLDiffTable.Flags[strFlagID] = data.SQLDiffTable.Flags[strFlagID] or {}
471 data.SQLDiffTable.Flags[strFlagID][strSubFlag] = true
472 else
473 data.SQLDiffTable.Flags[strFlagID] = true
474 end
475end
476
477function Module:GetPlayerDiffFlags( pPlayer )
478 if not self.m_tblPlayerData[pPlayer:SteamID64()] or not self.m_tblPlayerData[pPlayer:SteamID64()].SQLDiffTable then return {} end
479 return self.m_tblPlayerData[pPlayer:SteamID64()].SQLDiffTable.Flags or {}
480end
481
482function Module:TickPlayer( pPlayer )
483 if GAMEMODE.StopSaving then return end
484 local data = self.m_tblPlayerData[pPlayer:SteamID64()]
485 if not data or not data.SQLDiffTable or not data.SQLDiffTable.Flags then return end
486 if data.SQLDiffTable.NextSnapshot >= CurTime() then return end
487 self:CommitPlayerDiffs( pPlayer:SteamID64() )
488end
489
490function Module:PlayerDisconnected( strSID64 )
491 self:CommitPlayerDiffs( strSID64, function()
492 self.m_tblSaveTables[strSID64] = nil
493 end )
494end
495
496
497-- Update Functions
498-- ----------------------------------------------------------------
499Module:RegisterDiffFlag( "GameSet", function( intPlayerID, tblPlayerData, tblDiffFlags )
500 return ([[UPDATE ud_players SET setup = 1 WHERE id = %d]]):format( intPlayerID )
501end )
502
503Module:RegisterDiffFlag( "LastSeen", function( intPlayerID, tblPlayerData, tblDiffFlags )
504 return ([[UPDATE ud_players SET lastseen = FROM_UNIXTIME(%d) WHERE id = %d]]):format( tblPlayerData.LastSeen or 0, intPlayerID )
505end )
506
507Module:RegisterDiffFlag( "LastHealth", function( intPlayerID, tblPlayerData, tblDiffFlags )
508 return ([[UPDATE ud_players SET lasthealth = %d WHERE id = %d]]):format( tblPlayerData.LastHealth or 100, intPlayerID )
509end )
510
511Module:RegisterDiffFlag( "Name", function( intPlayerID, tblPlayerData, tblDiffFlags )
512 return ([[UPDATE ud_players SET name = '%s' WHERE id = %d]]):format( Module:Escape(tblPlayerData.Name), intPlayerID )
513end )
514
515Module:RegisterDiffFlag( "Model", function( intPlayerID, tblPlayerData, tblDiffFlags )
516 return ([[UPDATE ud_players SET model = '%s' WHERE id = %d]]):format( Module:Escape(tblPlayerData.Model), intPlayerID )
517end )
518
519Module:RegisterDiffFlag( "Exp", function( intPlayerID, tblPlayerData, tblDiffFlags )
520 return ([[UPDATE ud_players SET exp = %d WHERE id = %d]]):format( tblPlayerData.Exp, intPlayerID )
521end )
522
523Module:DefineSaveTable{ --[skillName:string] = xpAmount:uint
524 LuaName = "Skills", --Lua name
525 SQLName = "ud_skills", --SQL name
526
527 Queries = {
528 --Init table
529 Init = [[CREATE TABLE IF NOT EXISTS `ud_skills` (
530 `player_id` INT UNSIGNED NOT NULL,
531 `skill_name` VARCHAR(255) NOT NULL,
532 `skill_amount` INT UNSIGNED NOT NULL,
533 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
534 UNIQUE INDEX `ud_id_skill_name` (`player_id`, `skill_name`),
535 INDEX `player_id` (`player_id`)
536 ) ENGINE=InnoDB;]],
537 --InsertDupUpd row
538 Update = [[INSERT INTO ud_skills (player_id, skill_name, skill_amount) VALUES (%d, '%s', %d) ON DUPLICATE KEY UPDATE skill_amount = %d]],
539 --DelRow
540 Remove = [[DELETE FROM ud_skills WHERE player_id = %d AND skill_name = '%s']],
541 },
542
543 --DiffFlag function
544 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
545 if not tblDiffFlags["Skills"] then return end
546 if not tblPlayerData.Skills then return end
547
548 local query = {}
549 for skillName, _ in pairs( tblDiffFlags["Skills"] ) do
550 local skillAmount = tblPlayerData.Skills[skillName]
551 if skillAmount <= 0 then
552 table.insert( query, Module:GetQuery( "Skills", "Remove" ):format( intPlayerID, Module:Escape(skillName) ) )
553 else
554 table.insert( query, Module:GetQuery( "Skills", "Update" ):format( intPlayerID, Module:Escape(skillName), skillAmount, skillAmount ) )
555 end
556 end
557 return query
558 end,
559 --PlayerLoad function
560 LoadFunc = function( pPlayer, tblData )
561 for skill_name, skill_amount in pairs( tblData ) do
562 if not GAMEMODE.DataBase.Skills[skill_name] then continue end
563 if pPlayer:CanHaveSkill( skill_name ) then
564 pPlayer:BuySkill( skill_name, skill_amount, true )
565 end
566 end
567 end,
568 --DataLoad
569 QueryFunc = function( intPlayerID, fnCallback )
570 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_skills WHERE player_id = %d]]):format(intPlayerID) )
571 q.onSuccess = function( q, data )
572 local ret = {}
573 for k, v in pairs( data ) do
574 ret[v.skill_name] = v.skill_amount
575 end
576 fnCallback( false, ret )
577 end
578 q.onError = function( q, err )
579 fnCallback( true, err )
580 end
581 q:start()
582 end
583}
584
585Module:DefineSaveTable{ --[itemName:string] = itemAmount:uint
586 LuaName = "Inventory", --Lua name
587 SQLName = "ud_inventory", --SQL name
588
589 Queries = {
590 --Init table
591 Init = [[CREATE TABLE IF NOT EXISTS `ud_inventory` (
592 `player_id` INT UNSIGNED NOT NULL,
593 `item_name` VARCHAR(255) NOT NULL,
594 `item_count` INT UNSIGNED NOT NULL,
595 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
596 UNIQUE INDEX `ud_id_item_name` (`player_id`, `item_name`),
597 INDEX `player_id` (`player_id`)
598 ) ENGINE=InnoDB;]],
599 --Update query
600 Update = [[INSERT INTO ud_inventory (player_id, item_name, item_count) VALUES (%d, '%s', %d) ON DUPLICATE KEY UPDATE item_count = %d]],
601 --Remove query
602 Remove = [[DELETE FROM ud_inventory WHERE player_id = %d AND item_name = '%s']],
603 },
604
605 --DiffFlag function
606 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
607 if not tblDiffFlags["Inventory"] then return end
608 if not tblPlayerData.Inventory then return end
609
610 local query = {}
611 for itemName, _ in pairs( tblDiffFlags["Inventory"] ) do
612 local itemNum = tblPlayerData.Inventory[itemName] or 0
613 if itemNum <= 0 then
614 table.insert( query, Module:GetQuery( "Inventory", "Remove" ):format( intPlayerID, Module:Escape(itemName) ) )
615 else
616 table.insert( query, Module:GetQuery( "Inventory", "Update" ):format( intPlayerID, Module:Escape(itemName), itemNum, itemNum ) )
617 end
618 end
619 return query
620 end,
621 --PlayerLoad
622 LoadFunc = function( pPlayer, tblData )
623 for k, v in pairs( tblData ) do
624 pPlayer:AddItem( k, v )
625 end
626 end,
627 --DataLoad
628 QueryFunc = function( intPlayerID, fnCallback )
629 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_inventory WHERE player_id = %d]]):format(intPlayerID) )
630 q.onSuccess = function( q, data )
631 local ret = {}
632 for k, v in pairs( data ) do
633 ret[v.item_name] = v.item_count
634 end
635 fnCallback( false, ret )
636 end
637 q.onError = function( q, err )
638 fnCallback( true, err )
639 end
640 q:start()
641 end
642}
643
644Module:DefineSaveTable{ --[slotName:string] = itemID:string
645 LuaName = "Paperdoll", --Lua name
646 SQLName = "ud_paperdoll", --SQL name
647
648 Queries = {
649 --Init table
650 Init = [[CREATE TABLE IF NOT EXISTS `ud_paperdoll` (
651 `player_id` INT UNSIGNED NOT NULL,
652 `slot_name` VARCHAR(255) NOT NULL,
653 `slot_value` VARCHAR(255) NOT NULL,
654 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
655 UNIQUE INDEX `ud_id_slot_name` (`player_id`, `slot_name`),
656 INDEX `player_id` (`player_id`)
657 ) ENGINE=InnoDB;]],
658 --InsertDupUpd row
659 Update = [[INSERT INTO ud_paperdoll (player_id, slot_name, slot_value) VALUES (%d, '%s', '%s') ON DUPLICATE KEY UPDATE slot_value = '%s']],
660 --DelRow
661 Remove = [[DELETE FROM ud_paperdoll WHERE player_id = %d AND slot_name = '%s']],
662 },
663
664 --DiffFlag function
665 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
666 if not tblDiffFlags["Paperdoll"] then return end
667 if not tblPlayerData.Paperdoll then return end
668
669 local query = {}
670 for slotName, _ in pairs( tblDiffFlags["Paperdoll"] ) do
671 local slotValue = tblPlayerData.Paperdoll[slotName]
672 if not slotValue then
673 table.insert( query, Module:GetQuery( "Paperdoll", "Remove" ):format( intPlayerID, Module:Escape(slotName) ) )
674 else
675 table.insert( query, Module:GetQuery( "Paperdoll", "Update" ):format( intPlayerID, Module:Escape(slotName), Module:Escape(slotValue), Module:Escape(slotValue) ) )
676 end
677 end
678 return query
679 end,
680 --PlayerLoad function
681 LoadFunc = function( pPlayer, tblData )
682 for k, v in pairs( tblData ) do
683 pPlayer:UseItem( v )
684 end
685 end,
686 --DataLoad
687 QueryFunc = function( intPlayerID, fnCallback )
688 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_paperdoll WHERE player_id = %d]]):format(intPlayerID) )
689 q.onSuccess = function( q, data )
690 local ret = {}
691 for k, v in pairs( data ) do
692 ret[v.slot_name] = v.slot_value
693 end
694 fnCallback( false, ret )
695 end
696 q.onError = function( q, err )
697 fnCallback( true, err )
698 end
699 q:start()
700 end
701}
702
703Module:DefineSaveTable{ --[itemName:string] = itemAmount:uint
704 LuaName = "Bank", --Lua name
705 SQLName = "ud_bank", --SQL name
706
707 Queries = {
708 --Init table
709 Init = [[CREATE TABLE IF NOT EXISTS `ud_bank` (
710 `player_id` INT UNSIGNED NOT NULL,
711 `item_name` VARCHAR(255) NOT NULL,
712 `item_amount` INT UNSIGNED NOT NULL,
713 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
714 UNIQUE INDEX `ud_id_bankitem_name` (`player_id`, `item_name`),
715 INDEX `player_id` (`player_id`)
716 ) ENGINE=InnoDB;]],
717 --InsertDupUpd row
718 Update = [[INSERT INTO ud_bank (player_id, item_name, item_amount) VALUES (%d, '%s', %d) ON DUPLICATE KEY UPDATE item_amount = %d]],
719 --DelRow
720 Remove = [[DELETE FROM ud_bank WHERE player_id = %d AND item_name = '%s']],
721 },
722
723 --DiffFlag function
724 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
725 if not tblDiffFlags["Bank"] then return end
726 if not tblPlayerData.Bank then return end
727
728 local query = {}
729 for itemName, _ in pairs( tblDiffFlags["Bank"] ) do
730 local itemAmount = tblPlayerData.Bank[itemName]
731 if itemAmount <= 0 then
732 table.insert( query, Module:GetQuery( "Bank", "Remove" ):format( intPlayerID, Module:Escape(itemName) ) )
733 else
734 table.insert( query, Module:GetQuery( "Bank", "Update" ):format( intPlayerID, Module:Escape(itemName), itemAmount, itemAmount ) )
735 end
736 end
737 return query
738 end,
739 --PlayerLoad function
740 LoadFunc = function( pPlayer, tblData )
741 for _, data in pairs( tblData ) do
742 pPlayer:AddItemToBank( data.item_name, data.item_amount )
743 end
744 end,
745 --DataLoad
746 QueryFunc = function( intPlayerID, fnCallback )
747 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_bank WHERE player_id = %d]]):format(intPlayerID) )
748 q.onSuccess = function( q, data )
749 local ret = {}
750 for item_name, item_amount in pairs( data ) do
751 ret[item_name] = item_amount
752 end
753 fnCallback( false, ret )
754 end
755 q.onError = function( q, err )
756 fnCallback( true, err )
757 end
758 q:start()
759 end
760}
761
762Module:DefineSaveTable{ --[bookName:string] = true
763 LuaName = "Library", --Lua name
764 SQLName = "ud_library", --SQL name
765
766 Queries = {
767 --Init table
768 Init = [[CREATE TABLE IF NOT EXISTS `ud_library` (
769 `player_id` INT UNSIGNED NOT NULL,
770 `book_name` VARCHAR(255) NOT NULL,
771 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
772 UNIQUE INDEX `ud_id_book_name` (`player_id`, `book_name`),
773 INDEX `player_id` (`player_id`)
774 ) ENGINE=InnoDB;]],
775 --InsertDupUpd row
776 Update = [[INSERT INTO ud_library (player_id, book_name) VALUES (%d, '%s') ON DUPLICATE KEY UPDATE book_name = '%s']],
777 --DelRow
778 Remove = [[DELETE FROM ud_library WHERE player_id = %d AND book_name = '%s']],
779 },
780
781 --DiffFlag function
782 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
783 if not tblDiffFlags["Library"] then return end
784 if not tblPlayerData.Library then return end
785
786 local query = {}
787 for bookName, _ in pairs( tblDiffFlags["Library"] ) do
788 local hasBook = tblPlayerData.Library[bookName]
789 if not hasBook then
790 table.insert( query, Module:GetQuery( "Library", "Remove" ):format( intPlayerID, Module:Escape(bookName) ) )
791 else
792 table.insert( query, Module:GetQuery( "Library", "Update" ):format( intPlayerID, Module:Escape(bookName), Module:Escape(bookName) ) )
793 end
794 end
795 return query
796 end,
797 --PlayerLoad function
798 LoadFunc = function( pPlayer, tblData )
799 for bookName, _ in pairs( tblData ) do
800 pPlayer:AddBookToLibrary( bookName )
801 end
802 end,
803 --DataLoad
804 QueryFunc = function( intPlayerID, fnCallback )
805 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_library WHERE player_id = %d]]):format(intPlayerID) )
806 q.onSuccess = function( q, data )
807 local ret = {}
808 for k, v in pairs( data ) do
809 ret[v.book_name] = true
810 end
811 fnCallback( false, ret )
812 end
813 q.onError = function( q, err )
814 fnCallback( true, err )
815 end
816 q:start()
817 end
818}
819
820Module:DefineSaveTable{ --[questName:string] = questJSON:string
821 LuaName = "Quests", --Lua name
822 SQLName = "ud_quests", --SQL name
823
824 Queries = {
825 --Init table
826 Init = [[CREATE TABLE IF NOT EXISTS `ud_quests` (
827 `player_id` INT UNSIGNED NOT NULL,
828 `quest_name` VARCHAR(255) NOT NULL,
829 `quest_blob` TEXT NOT NULL,
830 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
831 UNIQUE INDEX `ud_id_quest_name` (`player_id`, `quest_name`),
832 INDEX `player_id` (`player_id`)
833 ) ENGINE=InnoDB;]],
834 --InsertDupUpd row
835 Update = [[INSERT INTO ud_quests (`player_id`, `quest_name`, `quest_blob`) VALUES (%d, '%s', '%s') ON DUPLICATE KEY UPDATE quest_blob = '%s']],
836 --DelRow
837 Remove = [[DELETE FROM ud_quests WHERE player_id = %d AND quest_name = '%s']],
838 },
839
840 --DiffFlag function
841 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
842 if not tblDiffFlags["Quests"] then return end
843 if not tblPlayerData.Quests then return end
844
845 local query = {}
846 for questName, _ in pairs( tblDiffFlags["Quests"] ) do
847 local questData = tblPlayerData.Quests[questName]
848 if not questData then
849 table.insert( query, Module:GetQuery( "Quests", "Remove" ):format( intPlayerID, Module:Escape(questName) ) )
850 else
851 local data = Module:Escape( enc(util.TableToJSON(questData)) )
852 local s = Module:GetQuery( "Quests", "Update" ):format( intPlayerID, Module:Escape(questName), data, data )
853 table.insert( query, s )
854 end
855 end
856 return query
857 end,
858 --PlayerLoad function
859 LoadFunc = function( pPlayer, tblData )
860 --for strQuest, tblInfo in pairs(tblDecodedTable.Quests or {}) do self:UpdateQuest(strQuest, tblInfo) end
861 for questName, data in pairs( tblData ) do
862 pPlayer:UpdateQuest( questName, data )
863 end
864 end,
865 --DataLoad
866 QueryFunc = function( intPlayerID, fnCallback )
867 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_quests WHERE player_id = %d]]):format(intPlayerID) )
868 q.onSuccess = function( q, data )
869 local ret = {}
870 for k, v in pairs( data ) do
871 ret[v.quest_name] = util.JSONToTable( dec(v.quest_blob or "") ) or {}
872 end
873 fnCallback( false, ret )
874 end
875 q.onError = function( q, err )
876 fnCallback( true, err )
877 end
878 q:start()
879 end
880}
881
882Module:DefineSaveTable{ --[friendName:string] = friendJSON:string
883 LuaName = "Friends", --Lua name
884 SQLName = "ud_friends", --SQL name
885
886 Queries = {
887 --Init table
888 Init = [[CREATE TABLE IF NOT EXISTS `ud_friends` (
889 `player_id` INT UNSIGNED NOT NULL,
890 `friend_name` VARCHAR(255) NOT NULL,
891 `friend_blob` TEXT NOT NULL,
892 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
893 UNIQUE INDEX `ud_id_friend_name` (`player_id`, `friend_name`),
894 INDEX `player_id` (`player_id`)
895 ) ENGINE=InnoDB;]],
896 --InsertDupUpd row
897 Update = [[INSERT INTO ud_friends (player_id, friend_name, friend_blob) VALUES (%d, '%s', '%s') ON DUPLICATE KEY UPDATE friend_blob = '%s']],
898 --DelRow
899 Remove = [[DELETE FROM ud_friends WHERE player_id = %d AND friend_name = '%s']],
900 },
901
902 --DiffFlag function
903 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
904 if not tblDiffFlags["Friends"] then return end
905 if not tblPlayerData.Friends then return end
906
907 local query = {}
908 for friendName, _ in pairs( tblDiffFlags["Friends"] ) do
909 local friendData = tblPlayerData.Friends[friendName]
910 if not friendData then
911 table.insert( query, Module:GetQuery( "Friends", "Remove" ):format( intPlayerID, Module:Escape(friendName) ) )
912 else
913 local data = Module:Escape( enc(util.TableToJSON(friendData)) )
914 table.insert( query, Module:GetQuery( "Friends", "Update" ):format( intPlayerID, Module:Escape(friendName), data, data ) )
915 end
916 end
917 return query
918 end,
919 --PlayerLoad function
920 LoadFunc = function( pPlayer, tblData )
921 for name, data in pairs( tblData ) do
922 pPlayer:AddFriend( name, data )
923 end
924 end,
925 --DataLoad
926 QueryFunc = function( intPlayerID, fnCallback )
927 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_friends WHERE player_id = %d]]):format(intPlayerID) )
928 q.onSuccess = function( q, data )
929 local ret = {}
930 for k, v in pairs( data ) do
931 ret[k] = util.JSONToTable( dec(v or "") ) or {}
932 end
933 fnCallback( false, ret )
934 end
935 q.onError = function( q, err )
936 fnCallback( true, err )
937 end
938 q:start()
939 end
940}
941
942Module:DefineSaveTable{ --[masterName:string] = xpAmount:uint
943 LuaName = "Masters", --Lua name
944 SQLName = "ud_masters", --SQL name
945
946 Queries = {
947 --Init table
948 Init = [[CREATE TABLE IF NOT EXISTS `ud_masters` (
949 `player_id` INT UNSIGNED NOT NULL,
950 `master_name` VARCHAR(255) NOT NULL,
951 `master_amount` INT UNSIGNED NOT NULL,
952 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
953 UNIQUE INDEX `ud_id_master_name` (`player_id`, `master_name`),
954 INDEX `player_id` (`player_id`)
955 ) ENGINE=InnoDB;]],
956 --InsertDupUpd row
957 Update = [[INSERT INTO ud_masters (player_id, master_name, master_amount) VALUES (%d, '%s', %d) ON DUPLICATE KEY UPDATE master_amount = %d]],
958 --DelRow
959 Remove = [[DELETE FROM ud_masters WHERE player_id = %d AND master_name = '%s']],
960 },
961
962 --DiffFlag function
963 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
964 if not tblDiffFlags["Masters"] then return end
965 if not tblPlayerData.Masters then return end
966
967 local query = {}
968 for masterName, _ in pairs( tblDiffFlags["Masters"] ) do
969 local masterAmount = tblPlayerData.Masters[masterName]
970 if masterAmount <= 0 then
971 table.insert( query, Module:GetQuery( "Masters", "Remove" ):format( intPlayerID, Module:Escape(masterName) ) )
972 else
973 table.insert( query, Module:GetQuery( "Masters", "Update" ):format( intPlayerID, Module:Escape(masterName), masterAmount, masterAmount ) )
974 end
975 end
976 return query
977 end,
978 --PlayerLoad function
979 LoadFunc = function( pPlayer, tblData )
980 for name, amount in pairs( tblData ) do
981 pPlayer:SetMaster( name, amount )
982 end
983 end,
984 --DataLoad
985 QueryFunc = function( intPlayerID, fnCallback )
986 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_masters WHERE player_id = %d]]):format(intPlayerID) )
987 q.onSuccess = function( q, data )
988 local ret = {}
989 for k, v in pairs( data ) do
990 ret[v.master_name] = v.master_amount
991 end
992 fnCallback( false, ret )
993 end
994 q.onError = function( q, err )
995 fnCallback( true, err )
996 end
997 q:start()
998 end
999}
1000
1001Module:DefineSaveTable{
1002 LuaName = "Achievements", --Lua name
1003 SQLName = "ud_achievements", --SQL name
1004
1005 Queries = {
1006 --Init table
1007 Init = [[CREATE TABLE IF NOT EXISTS `ud_achievements` (
1008 `player_id` INT UNSIGNED NOT NULL,
1009 `blob` TEXT NOT NULL,
1010 FOREIGN KEY (`player_id`) REFERENCES ud_players(`id`),
1011 PRIMARY KEY (`player_id`),
1012 UNIQUE INDEX `player_id` (`player_id`)
1013 ) ENGINE=InnoDB;]],
1014 --InsertDupUpd row
1015 Update = [[INSERT INTO ud_achievements (`player_id`, `blob`) VALUES (%d, '%s') ON DUPLICATE KEY UPDATE `blob` = '%s']],
1016 --DelRow
1017 Remove = [[DELETE FROM ud_achievements WHERE player_id = %d]],
1018 },
1019
1020 --DiffFlag function
1021 DiffFunc = function( intPlayerID, tblPlayerData, tblDiffFlags )
1022 if not tblDiffFlags["Achievements"] then return end
1023 if not tblPlayerData.Achievements then return end
1024 local data = Module:Escape( enc(util.TableToJSON(tblPlayerData.Achievements)) )
1025 local s = Module:GetQuery( "Achievements", "Update" ):format( intPlayerID, data, data )
1026 return s
1027 end,
1028 --PlayerLoad function
1029 LoadFunc = function( pPlayer, tblData )
1030 pPlayer.Data = pPlayer.Data or {}
1031 pPlayer.Data.Achievements = tblData
1032 end,
1033 --DataLoad
1034 QueryFunc = function( intPlayerID, fnCallback )
1035 local q = Module:GetDatabase():query( ([[SELECT * FROM ud_achievements WHERE player_id = %d]]):format(intPlayerID) )
1036 q.onSuccess = function( q, data )
1037 local ret = util.JSONToTable( dec((data[1] and data[1].blob or "") or "") )
1038 if not ret then
1039 fnCallback( false, {} )
1040 return
1041 end
1042 fnCallback( false, ret )
1043 end
1044 q.onError = function( q, err )
1045 fnCallback( true, err )
1046 end
1047 q:start()
1048 end
1049}
1050
1051return Module