· 6 years ago · Aug 30, 2019, 08:48 PM
1-- /data/scripts/player_storagevalue.lua
2
3local function dump(o)
4 if type(o) == 'table' then
5 local s = '{ '
6 for k,v in pairs(o) do
7 if type(k) ~= 'number' then k = '"'..k..'"' end
8 s = s .. '['..k..'] = ' .. dump(v) .. ','
9 end
10 return s .. '} '
11 else
12 return tostring(o)
13 end
14end
15
16local storageEvent = GlobalEvent("znote_storages")
17
18-- Declare, load and maintain a local table of all storage values and keys
19classic_storage = {}
20storage_string_keys = {}
21storage_string_values = {}
22player_storage_string = {}
23
24-- 4000 reserved storage value range for player_storages
25-- Used when creating an integer value reference from a string key
26reserved_storage_min = 21000
27reserved_storage_max = 24999
28
29-- storage value collition detection in the reserved storage value range
30-- Do a startup check warning if any collition detected
31--[[
32 SELECT
33 `is`.`player_id`,
34 `is`.`key`,
35 `ss`.`value_key`
36
37 FROM `player_storage` AS `is`
38
39 LEFT JOIN `player_storage_string` AS `ss`
40 ON `is`.`player_id` = `ss`.`player_id`
41 AND `is`.`key` = `ss`.`value_key`
42 AND `ss`.`type` = 0
43
44 WHERE
45 `is`.`key` >= reserved_storage_min
46 AND `is`.`key` <= reserved_storage_max
47 AND `ss`.`value_key` IS NULL
48]]
49
50--
51
52storage_log_queries = {
53 player_storage = {}, -- {t=2, p=player_id, d="query row data"}
54 player_storage_string = {},
55 --storage_string_keys = {},
56 --storage_string_values = {},
57 rows = {
58 player_storage = 0,
59 player_storage_string = 0,
60 storage_string_keys = 0,
61 storage_string_values = 0
62 }
63}
64-- +55 database.cpp
65-- DBResult_ptr result = storeQuery("SHOW VARIABLES LIKE 'max_allowed_packet'");
66-- Figure out how many rows can be inserted in one query
67-- without exceeding server mysql max_allowed_packet configuration
68
69-- hook into globalevent closeserver and saveserver and shutdown to execute log queries
70
71function storageEvent.onStartup()
72 print(" ")
73 print("===============================")
74 print("= Initializing znote_storages =")
75 print("===============================")
76
77 -- Load all storage values from SQL server and store it internally
78 local resultId = db.storeQuery("SELECT `player_id`, `key`, `value` FROM `player_storage` ORDER BY `player_id` ASC, `key` ASC")
79 if resultId ~= false then
80 -- Declare SQL row parser
81 local lastPlayer = false
82 local storage = {}
83 local rows = 0
84 repeat
85 -- Load the player id from SQL
86 local currentPlayer = result.getDataInt(resultId, "player_id")
87 -- If lastPlayer is declared but not initialized, give it the current player id.
88 if lastPlayer == false then
89 lastPlayer = currentPlayer
90 end
91 -- If lastPlayer is not currentPlayer,
92 -- import local storage into classic_storage, and prepare it for currentPlayer
93 if lastPlayer ~= currentPlayer then
94 classic_storage[lastPlayer] = storage
95 storage = {}
96 lastPlayer = currentPlayer
97 end
98 -- populate local storage
99 storage[result.getDataInt(resultId, "key")] = result.getDataInt(resultId, "value")
100
101 rows = rows + 1
102 until not result.next(resultId)
103 result.free(resultId)
104
105 classic_storage[lastPlayer] = storage
106
107 print("Loaded: " .. rows .. " player storage values from database to classic_storage.")
108 else
109 print("Loaded: 0 player storage values from database to classic_storage.")
110 end
111 print("== classic storage ============")
112 print(dump(classic_storage))
113 print(" ")
114
115 resultId = db.storeQuery("SELECT `string`, `key` FROM `storage_string_keys`")
116 if resultId ~= false then
117 local rows = 0
118 repeat -- populate storage_string_keys
119 storage_string_keys[result.getDataString(resultId, "string")] = result.getDataInt(resultId, "key")
120 rows = rows + 1
121 until not result.next(resultId)
122 result.free(resultId)
123 print("Loaded: " .. rows .. " string keys from database storage_string_keys.")
124 else
125 print("Loaded: 0 string keys from database storage_string_keys.")
126 end
127 print("== storage_string_keys ============")
128 print(dump(storage_string_keys))
129 print(" ")
130
131 resultId = db.storeQuery("SELECT `key`, `string` FROM `storage_string_values`")
132 if resultId ~= false then
133 local rows = 0
134 repeat -- populate storage_string_values
135 storage_string_values[result.getDataInt(resultId, "key")] = result.getDataString(resultId, "string")
136 rows = rows + 1
137 until not result.next(resultId)
138 result.free(resultId)
139 print("Loaded: " .. rows .. " string keys from database storage_string_values.")
140 else
141 print("Loaded: 0 string values from database storage_string_values.")
142 end
143 print("== storage_string_values ============")
144 print(dump(storage_string_values))
145 print(" ")
146
147 -- Load all storage values from SQL server and store it internally
148 resultId = db.storeQuery("SELECT `player_id`, `key_key`, `value_key`, `type` FROM `player_storage_string` ORDER BY `player_id` ASC, `key_key` ASC")
149 if resultId ~= false then
150 local lastPlayer = false
151 local storage = {}
152 local rows = 0
153 repeat
154 -- Load the player id from SQL
155 local currentPlayer = result.getDataInt(resultId, "player_id")
156 -- If lastPlayer is declared but not initialized, give it the current player id.
157 if lastPlayer == false then
158 lastPlayer = currentPlayer
159 end
160 -- If lastPlayer is not currentPlayer,
161 -- import local storage into player_storage_string, and prepare it for currentPlayer
162 if lastPlayer ~= currentPlayer then
163 player_storage_string[lastPlayer] = storage
164 storage = {}
165 lastPlayer = currentPlayer
166 end
167 -- populate local storage
168 storage[result.getDataInt(resultId, "key_key")] = {v=result.getDataInt(resultId, "value_key"),t=result.getDataInt(resultId, "type")}
169 rows = rows + 1
170 until not result.next(resultId)
171 result.free(resultId)
172
173 player_storage_string[lastPlayer] = storage
174
175 print("Loaded: " .. rows .. " player storage values from database to player_storage_string.")
176 else
177 print("Loaded: 0 player storage values from database to player_storage_string.")
178 end
179 print("== classic storage ============")
180 print(dump(player_storage_string))
181 print(" ")
182end
183--result.getDataInt(resultId, "rank_id")
184--db.escapeString(result.getDataString(resultId, "guild_nick"))
185storageEvent:register()
186
187Player.oldGetStorageValue = Player.getStorageValue
188Player.oldSetStorageValue = Player.setStorageValue
189
190function Player.getStorageValue(self, key)
191 local ret = nil
192 local pid = self:getGuid()
193
194 -- player:getStorageValue(1337)
195 if type(key) == 'number' then
196 if classic_storage[pid] ~= nil then
197 ret = classic_storage[pid][key]
198 end
199 else -- player:getStorageValue("Arena_bestplayer")
200 -- -- storage_string_keys ("Znote") = {key = 13}
201 if storage_string_keys[key] ~= nil then
202
203 -- param key is string, so lets find the int representation of the string
204 local int_key = storage_string_keys[key]
205
206 -- does player have any string storage keys?
207 if player_storage_string[pid] ~= nil then
208
209 -- does player have this specific string key?
210 if player_storage_string[pid][int_key] ~= nil then
211
212 -- does this string key reference an int or string value?
213 if player_storage_string[pid][int_key].t == 0 then
214 -- int value
215 if classic_storage[pid] ~= nil then
216 ret = classic_storage[pid][player_storage_string[pid][int_key].v]
217 end
218
219 elseif player_storage_string[pid][int_key].t == 1 then
220 -- string value
221 ret = storage_string_values[player_storage_string[pid][int_key].v]
222 end
223 end
224 end
225 end
226 end
227
228 if ret ~= nil then
229 print(pid..":getStorageValue(".. key ..") = "..ret)
230 else
231 print(pid..":getStorageValue(".. key ..") = [NULL]")
232 end
233
234 return ret
235end
236
237function Player.setStorageValue(self, key, value)
238 local ret = nil
239 local pid = self:getGuid()
240
241 -- if both key and value are integers, we can use the old classic storage system
242 if type(key) == 'number' and type(value) == 'number' then
243 -- Does player exist in storage list?
244 if classic_storage[pid] ~= nil then
245 -- Does this player already have this storage key?
246 if classic_storage[pid][key] ~= nil then
247 -- Is the stored value different from new value?
248 if classic_storage[pid][key] ~= value then
249 -- update
250 classic_storage[pid][key] = value
251 table.insert(storage_log_queries.player_storage, {pid, key, value})
252 storage_log_queries.rows.player_storage = storage_log_queries.rows.player_storage + 1
253 end
254 else
255 -- insert
256 classic_storage[pid][key] = value
257 table.insert(storage_log_queries.player_storage, {pid, key, value})
258 storage_log_queries.rows.player_storage = storage_log_queries.rows.player_storage + 1
259 end
260 else
261 -- Create and insert
262 classic_storage[pid] = {key=value}
263 table.insert(storage_log_queries.player_storage, {pid, key, value})
264 storage_log_queries.rows.player_storage = storage_log_queries.rows.player_storage + 1
265 --[[
266 INSERT INTO `player_storage` (`player_id`,`key`,`value`) VALUES
267 (2,1337,500),
268 (13,1337,325)
269 ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
270 ]]
271 end
272 ret = true
273 else -- key and/or value is a string
274 local key_str = ''..key
275 local key_key = false
276
277 -- If this string key doesn't exist, create it.
278 if storage_string_keys[key_str] == nil then
279 -- We can do live SQL query here since its not player specific
280 db.query("INSERT INTO `storage_string_keys` (`string`) VALUES ('".. db.escapeString(key_str) .."');")
281 resultId = db.storeQuery("SELECT `key` FROM `storage_string_keys` WHERE `string` = '".. db.escapeString(key_str) .."');")
282 if resultId ~= false then
283 key_key = result.getDataInt(resultId, "key")
284 storage_string_keys[key_str] = key_key
285 else
286 print("Error: setStorageValue: Failed to store string and load key. \nTable: storage_string_keys. \nString: " .. db.escapeString(key_str))
287 end
288 else
289 key_key = storage_string_keys[key_str]
290 end
291
292 -- If this value is a string and doesn't exist, create it.
293 if type(value) == 'string' and storage_string_values[value] == nil then
294 -- We can do live SQL query here since its not player specific
295 db.query("INSERT INTO `storage_string_values` (`string`) VALUES ('".. db.escapeString(key_str) .."');")
296 resultId = db.storeQuery("SELECT `key` FROM `storage_string_values` WHERE `string` = '".. db.escapeString(key_str) .."');")
297 if resultId ~= false then
298 storage_string_values[result.getDataInt(resultId, "key")] = key_str
299 else
300 print("Error: setStorageValue: Failed to store string and load key. \nTable: storage_string_values. \nString: " .. db.escapeString(value))
301 end
302 end
303
304 --
305 if player_storage_string[pid] ~= nil then
306 if player_storage_string[pid][key_key] ~= nil then
307
308 local value_key = player_storage_string[pid][key_key].v
309
310 if player_storage_string[pid][key_key].t == 1 then
311 -- Value is a string
312 -- Is stored value different from current value?
313 if storage_string_values[value_key] ~= value then
314 if type(value) == 'number' then
315 print("Error: value of string key is suppose to be a number. \nkey: " .. key_str .. "\nValue: "..storage_string_values[value_key].."\nNew value: "..value)
316 else
317 -- update
318 end
319 end
320 else
321 -- value is a integer
322 -- Is stored value different from current value?
323 if classic_storage[value_key] ~= value then
324 if type(value) == 'string' then
325 print("Error: value of string key is suppose to be an integer. \nkey: " .. key_str .. "\nValue: "..classic_storage[value_key].."\nNew value: "..value)
326 else
327 -- update
328 end
329 end
330 end
331 else
332 -- insert (depending on type)
333 end
334 else
335 -- create pid + insert
336 end
337 end
338 return ret
339end
340
341
342--[[
343 CREATE TABLE IF NOT EXISTS `storage_string_keys` (
344 `string` varchar(255) NOT NULL,
345 `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
346 PRIMARY KEY (`string`),
347 UNIQUE KEY `key` (`key`)
348 ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
349
350 CREATE TABLE IF NOT EXISTS `storage_string_values` (
351 `key` int(10) unsigned NOT NULL AUTO_INCREMENT,
352 `string` varchar(255) NOT NULL,
353 PRIMARY KEY (`key`),
354 UNIQUE KEY `string` (`string`)
355 ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
356
357 CREATE TABLE IF NOT EXISTS `player_storage_string` (
358 `player_id` int(11) NOT NULL DEFAULT '0',
359 `key_key` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Table: storage_string_values',
360 `value_key` int(10) unsigned NOT NULL COMMENT 'Key reference in (player_storage) or (storage_string_values) based on type',
361 `type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0 = int (player_storage), 1 = string (storage_string_values)',
362 PRIMARY KEY (`player_id`,`key_key`),
363 FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
364 FOREIGN KEY (`key_key`) REFERENCES `storage_string_keys`(`key`)
365 ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
366]]--