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