· 9 years ago · May 02, 2017, 10:14 PM
1-- This file is under copyright, and is bound to the agreement stated in the EULA.
2-- Any 3rd party content has been used as either public domain or with permission.
3-- © Copyright 2014-2017 Aritz Beobide-Cardinal All rights reserved.
4
5-- I actually got off my ass and started to make ARCBank compatable with MySQL.
6-- After started, I realized that this... WILL BE HARD!
7
8local MYSQL_TYPE = 0
9
10if system.IsLinux() then
11 ARCBank.Msg(table.Random{"You know, I created a skin using LXDE that made ubuntu look like, sound like, and feel like windows 98.","GANOO/LOONIX","I <3 Linux","Linux is best","I don't like systemd."})
12 if file.Exists( "lua/bin/gmsv_mysqloo_linux.dll", "MOD") then
13 MYSQL_TYPE = 1
14 require( "mysqloo" )
15 elseif file.Exists( "lua/bin/gmsv_tmysql4_linux.dll", "MOD") then
16 MYSQL_TYPE = 2
17 require( "tmysql4" )
18 end
19 if file.Exists( "lua/bin/gmsv_mysqloo_win32.dll", "MOD") || file.Exists( "lua/bin/gmsv_tmysql4_win32.dll", "MOD") then
20 ARCBank.Msg("...You do realize that you tried to install a windows .dll on a linux machine, right?")
21 end
22elseif system.IsWindows() then
23 ARCBank.Msg(table.Random{"Windows server... >_>","I hope you aren't using a windows server by choice.","Once you learn a little bit more about computers in general, you'll hate windows server. (Unless you're an idiot.)"})
24 if file.Exists( "lua/bin/gmsv_mysqloo_win32.dll", "MOD") then
25 MYSQL_TYPE = 1
26 require( "mysqloo" )
27 elseif file.Exists( "lua/bin/gmsv_tmysql4_win32.dll", "MOD") then
28 MYSQL_TYPE = 2
29 require( "tmysql4" )
30 end
31 if file.Exists( "lua/bin/gmsv_mysqloo_linux.dll", "MOD") || file.Exists( "lua/bin/gmsv_tmysql4_linux.dll", "MOD") then
32 ARCBank.Msg("...You do realize that you tried to install a linux .dll on a windows machine, right?")
33 end
34elseif system.IsOSX() then
35 ARCBank.Msg("Is there even such a thing as an OSX server? Can it run mysqloo?")
36end
37--1.3: arcbank_account_members arcbank_personal_account arcbank_personal_account
38local arcbank_log = [[CREATE TABLE IF NOT EXISTS arcbank_log
39(
40transaction_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
41timestamp BIGINT UNSIGNED NOT NULL DEFAULT 0,
42account1 varchar(255) NOT NULL,
43account2 varchar(255) NOT NULL DEFAULT "",
44user1 varchar(255) NOT NULL,
45user2 varchar(255) NOT NULL DEFAULT "",
46moneydiff BIGINT NOT NULL DEFAULT 0,
47money BIGINT,
48transaction_type SMALLINT UNSIGNED NOT NULL,
49comment varchar(255) NOT NULL DEFAULT ""
50) DEFAULT CHARSET=utf8;]]
51
52local arcbank_accounts = [[CREATE TABLE IF NOT EXISTS arcbank_accounts
53(
54account varchar(255) PRIMARY KEY,
55name varchar(255) NOT NULL,
56owner varchar(255) NOT NULL,
57rank TINYINT UNSIGNED NOT NULL
58) DEFAULT CHARSET=utf8;]]
59
60local arcbank_groups = [[CREATE TABLE IF NOT EXISTS arcbank_groups
61(
62account varchar(255) NOT NULL,
63user varchar(255) NOT NULL,
64CONSTRAINT pk_PersonID UNIQUE (account,user)
65);]]
66
67local arcbank_lock = [[CREATE TABLE IF NOT EXISTS arcbank_lock
68(
69account varchar(255) PRIMARY KEY
70);]]
71
72ARCBank = ARCBank or {}
73ARCBank.Loaded = false
74ARCBank.MySQL = {}
75ARCBank.MySQL.EnableMySQL = true
76ARCBank.MySQL.Host = "104.153.108.136"
77ARCBank.MySQL.Username = "rp_darkrp"
78ARCBank.MySQL.Password = "redacted"
79ARCBank.MySQL.DatabaseName = "rp_darkrp"
80ARCBank.MySQL.DatabasePort = 3306
81
82function ARCBank.MySQL.Escape(str)
83 if MYSQL_TYPE == 1 then
84 return ARCBank.DataBase:escape(str)
85 elseif MYSQL_TYPE == 2 then
86 return ARCBank.DataBase:Escape(str)
87 end
88end
89
90function ARCBank.MySQL.CreateQuery(str,succfunc,errfunc)
91 if MYSQL_TYPE == 1 then
92 local q = ARCBank.DataBase:query( str )
93 function q:onSuccess( data )
94 succfunc(data)
95 end
96 function q:onError( err, sqlq )
97 errfunc(err,sqlq)
98 end
99 q:start()
100 elseif MYSQL_TYPE == 2 then
101 local function onCompleted( results )
102 if #results == 1 then
103 if results[1].status then
104 succfunc(results[1].data)
105 else
106 errfunc(results[1].error,str)
107 end
108 --ARCBank.Msg("tmysql4 query took "..results[1].time.." seconds and affected "..results[1].affected.." rows with error "..tostring(results[1].error) )
109 else
110 ARCBank.Msg("I HAVE NO IDEA WHAT TO DO WITH THE RESULT TO QUERY: "..str)
111 ARCBank.Msg("The result table was as follows:")
112 PrintTable(results)
113 ARCBank.Msg("Finished printing result to console.")
114 errfunc("ARCBank tmysql4 support error!",str)
115 end
116 end
117 ARCBank.DataBase:Query( str, onCompleted )
118 end
119end
120
121local function InitOnError( err, sql )
122 ARCBank.Loaded = false
123 ARCBank.Busy = false
124 ARCBank.Msg( "FAILED!! "..tostring(err) )
125end
126local function InitOnSuccess( data )
127 ARCBank.Msg( "Everything appears to be A-OK!" )
128 ARCBank.Loaded = true
129 ARCBank.Busy = false
130 ARCBank.Msg("ARCBank is ready!")
131 ARCBank.ConvertOldAccounts()
132 ARCBank.CapAccountRank()
133end
134
135local function InitGroupSuccess( data )
136 ARCBank.Msg( "Checking if in-use accounts table exists..." )
137 ARCBank.MySQL.CreateQuery(arcbank_lock,InitOnSuccess,InitOnError)
138end
139
140local function InitAccountSuccess( data )
141 ARCBank.Msg( "Checking if group member table exists..." )
142 ARCBank.MySQL.CreateQuery(arcbank_groups,InitGroupSuccess,InitOnError)
143end
144
145local function InitLogSuccess( data )
146 ARCBank.Msg( "Checking if account properties table exists..." )
147 ARCBank.MySQL.CreateQuery(arcbank_accounts,InitAccountSuccess,InitOnError)
148end
149
150function ARCBank.MySQL.Connect()
151 ARCBank.Msg("INITIALIZING MYSQL SEQUENCE!")
152 if MYSQL_TYPE == 0 then
153 ARCBank.Msg("No suitable MySQL module has been found. This addon supports MySQLOO or tmysql4.")
154 ARCBank.Msg("You can download MySQLOO v9 here: https://facepunch.com/showthread.php?t=1515853")
155 return
156 end
157 if MYSQL_TYPE == 2 then
158 ARCBank.Msg("Still running tmysql4? Why not try MySQLOO v9? tmysql4 was abandoned by its creator and MySQLOO v9 fixed all the memory leaks and crash bugs that MySQLOO v8 had!")
159 ARCBank.Msg("You can download MySQLOO v9 here: https://facepunch.com/showthread.php?t=1515853")
160 end
161
162 ARCBank.Msg("Connecting to database. Hopefully nothing blows up....")
163 if MYSQL_TYPE == 1 then
164 ARCBank.DataBase = mysqloo.connect( ARCBank.MySQL.Host, ARCBank.MySQL.Username, ARCBank.MySQL.Password, ARCBank.MySQL.DatabaseName, ARCBank.MySQL.DatabasePort )
165 function ARCBank.DataBase:onConnectionFailed( err )
166 ARCBank.Msg( "...SOMETHING BROKE! "..tostring(err) )
167 end
168 function ARCBank.DataBase:onConnected()
169 ARCBank.Msg( "Database connected. So far so good!" )
170 ARCBank.MySQL.CreateQuery(arcbank_log,InitLogSuccess,InitOnError)
171 end
172 ARCBank.DataBase:connect()
173 elseif MYSQL_TYPE == 2 then
174 local Database, err = tmysql.Connect(ARCBank.MySQL.Host, ARCBank.MySQL.Username, ARCBank.MySQL.Password, ARCBank.MySQL.DatabaseName, ARCBank.MySQL.DatabasePort, nil, CLIENT_MULTI_STATEMENTS)
175 if err then
176 ARCBank.Msg( "...SOMETHING BROKE! "..tostring(err) )
177 ARCBank.Busy = false
178 else
179 ARCBank.DataBase = Database
180 ARCBank.Msg( "Database connected. So far so good!" )
181 ARCBank.Msg( "Checking if log table exists..." )
182 ARCBank.MySQL.CreateQuery(arcbank_log,InitLogSuccess,InitOnError)
183 end
184 end
185
186
187end
188local ignorableErrors = {"Duplicate entry "}
189local resetErrors = {"Lost connection to MySQL server during query"}
190function ARCBank.MySQL.Query(str,callback)
191 local function onSuccess( data )
192 callback(nil,data)
193 end
194 local function onError( err, sqlq )
195 local ignoreError = false
196 for k,v in ipairs(ignorableErrors) do
197 if string.Left( err, #v ) == v then
198 ignoreError = true
199 end
200 end
201 if ignoreError then
202 ARCBank.Msg( "Ignoring MySQL Error: \""..tostring(err).."\" Query: \""..tostring(sqlq).."\"")
203 else
204 for _,plys in pairs(player.GetAll()) do
205 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQL1)
206 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQL2)
207 end
208
209 ARCBank.Msg( "MySQL ERROR: "..tostring(err))
210 ARCBank.Msg( "In Query \""..tostring(sqlq).."\"")
211 ARCBank.Msg(tostring(#err).." - "..tostring(#sqlq))
212 local resetError = false
213 for k,v in ipairs(resetErrors) do
214 if string.Left( err, #v ) == v then
215 resetError = true
216 end
217 end
218 if resetError then
219 for _,plys in pairs(player.GetAll()) do
220 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQL3)
221 end
222 ARCBank.Msg( "This error can be corrected by re-connecting to the MySQL server (which I am about to do)" )
223 ARCBank.Msg( "If you're getting errors like this very consistently (like every 5 or 10 minutes) try increasing the connection time limit on the MySQL server" )
224 ARCBank.Busy = true
225 timer.Simple(5,function()
226 if ARCBank.Loaded then return end
227 ARCBank.MySQL.Connect()
228 timer.Simple(15,function()
229 if !ARCBank.Loaded then
230 for _,plys in pairs(player.GetAll()) do
231 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQL4)
232 end
233 end
234 end)
235 end)
236 else
237 ARCBank.Msg( "REPORT THIS TO ARITZ CRACKER ASAP!!! (Unless it's your fault)" )
238 end
239 ARCBank.Loaded = false
240 end
241 callback(err)
242 end
243 ARCBank.MySQL.CreateQuery(str,onSuccess,onError)
244end
245--
246function ARCBank.MySQL.RunCustomCommand(str)
247 local function onSuccess( data )
248 print( "Query successful!" )
249 print(#data)
250 PrintTable( data )
251 if #data == 0 then
252 print( "Blank table result" )
253 end
254
255 end
256
257 local function onError( err, sql )
258
259 print( "Query errored!" )
260 print( "Query:", sql )
261 print( "Error:", err )
262
263 end
264
265 ARCBank.MySQL.CreateQuery(str,onSuccess,onError)
266end
267
268ARCBank.Commands["mysql"] = { --TODO: FINISH
269 command = function(ply,args)
270 if !ARCBank.Loaded then ARCBank.MsgCL(ply,"System reset required!") return end -- This is just to check if the ARCBank system is working properly.
271 if !ARCBank.IsMySQLEnabled() then ARCBank.MsgCL(ply,"MySQL must be enabled.") return end
272 if (IsValid(ply) && ply:IsPlayer() && !ply:IsListenServerHost()) then
273 ARCBank.MsgCL(ply,"This command cannot be used by a player.")
274 return
275 end
276 if args[1] == "copy_to_database" then
277 ARCBank.Msg(ARCBank.Msgs.CommandOutput.MySQLCopy)
278 for _,plys in pairs(player.GetAll()) do
279 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQLCopy)
280 end
281 ARCBank.Busy = true
282 timer.Simple(1,function()
283 local Queries = {}
284 table.insert(Queries,"DELETE FROM arcbank_log;")
285 table.insert(Queries,"DELETE FROM arcbank_accounts;")
286 table.insert(Queries,"DELETE FROM arcbank_groups;")
287 local logs = file.Find( ARCBank.Dir.."/logs_1.4/*", "DATA")
288 local properties = file.Find( ARCBank.Dir.."/accounts_1.4/*", "DATA")
289 local groups = file.Find( ARCBank.Dir.."/groups_1.4/*", "DATA")
290
291
292 for k,v in ipairs(logs) do
293 local line = string.Explode( "\r\n", file.Read(ARCBank.Dir.."/logs_1.4/"..v) or "")
294 line[#line] = nil --Last line of a log is always blank
295 for kk,vv in ipairs(line) do
296 local stuffs = string.Explode("\t",vv)
297
298 local transaction_id = tonumber(stuffs[1]) or 0
299 local timestamp = tostring(tonumber(stuffs[2]) or 0)
300 local account1 = "'"..ARCBank.MySQL.Escape(stuffs[3]).."'"
301 local account2 = "'"..ARCBank.MySQL.Escape(stuffs[4]).."'"
302 local user1 = "'"..ARCBank.MySQL.Escape(stuffs[5]).."'"
303 local user2 = "'"..ARCBank.MySQL.Escape(stuffs[6]).."'"
304 local moneydiff = tostring(tonumber(stuffs[7]) or 0)
305 local money = tonumber(stuffs[8])
306 if money == nil then
307 money = "NULL"
308 else
309 money = tostring(money)
310 end
311 local transaction_type = tostring(tonumber(stuffs[9]) or 0)
312 local comment = "'"..ARCBank.MySQL.Escape(stuffs[10]).."'"
313
314 table.insert(Queries,"INSERT INTO arcbank_log(timestamp,account1,account2,user1,user2,moneydiff,money,transaction_type,comment) VALUES("..timestamp..","..account1..","..account2..","..user1..","..user2..","..moneydiff..","..money..","..transaction_type..","..comment..");")
315 end
316 end
317
318 for k,v in ipairs(properties) do
319 --""
320 data = file.Read( ARCBank.Dir.."/accounts_1.4/"..v, "DATA")
321 if !data or data == "" then
322 ARCBank.Msg("Corrupted account "..v)
323 else
324 local accountdata = util.JSONToTable(data)
325 table.insert(Queries,"INSERT INTO arcbank_accounts(account,name,owner,rank) VALUES('"..ARCBank.MySQL.Escape(tostring(accountdata.account)).."','"..ARCBank.MySQL.Escape(tostring(accountdata.name)).."','"..ARCBank.MySQL.Escape(tostring(accountdata.owner)).."',"..tonumber(accountdata.rank or 0)..");")
326 end
327 end
328 for k,v in ipairs(groups) do
329 tab = string.Explode( ",", file.Read( ARCBank.Dir.."/groups_1.4/"..v, "DATA"))
330 if tab and tab[2] then
331 local account = string.sub(v,1,#v-4)
332 tab[#tab] = nil --Last person is blank because lazy
333 for kk,vv in ipairs(tab) do
334 table.insert(Queries,"INSERT INTO arcbank_groups(account,user) VALUES('"..ARCBank.MySQL.Escape(account).."','"..ARCBank.MySQL.Escape(vv).."');")
335 end
336 else
337 ARCBank.Msg("Corrupted group list "..v)
338 end
339 end
340 local recrusivecopy
341 recrusivecopy = function(num)
342 if num > #Queries then
343 ARCBank.Msg(ARCBANK_ERRORSTRINGS[0])
344 for _,plys in pairs(player.GetAll()) do
345 ARCBank.MsgCL(plys,ARCBANK_ERRORSTRINGS[0])
346 end
347 ARCBank.Busy = false
348 return
349 end
350 ARCBank.MySQL.Query(Queries[num],function(err,data)
351 if !err then
352 ARCBank.Msg(ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/#Queries)*100))..")")
353 for _,plys in pairs(player.GetAll()) do
354 ARCBank.MsgCL(plys,ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/#Queries)*100))..")")
355 end
356 recrusivecopy(num+1)
357 else
358 ARCBank.Loaded = false
359 ARCBank.Msg("[ERROR!] (%"..tostring(math.floor((num/#Queries)*100))..") Halting ARCBank. System reset required.")
360 ARCBank.Msg(err)
361 for _,plys in pairs(player.GetAll()) do
362 ARCBank.MsgCL(plys,"[ERROR!] (%"..tostring(math.floor((num/#Queries)*100))..") Halting ARCBank. System reset required.")
363 end
364 end
365 end)
366 end
367 timer.Simple(1,function() recrusivecopy(1) end)
368 end)
369
370 ARCBank.Msg(ARCBank.Msgs.CommandOutput.MySQLCopy)
371 for _,plys in pairs(player.GetAll()) do
372 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQLCopy)
373 end
374 elseif args[1] == "copy_from_database" then
375 ARCBank.Msg(ARCBank.Msgs.CommandOutput.MySQLCopyFrom)
376 for _,plys in pairs(player.GetAll()) do
377 ARCBank.MsgCL(plys,ARCBank.Msgs.CommandOutput.MySQLCopyFrom)
378 end
379 ARCBank.Busy = true
380 ARCBank.ReadTransactions(nil,nil,nil,function(errcode,progress,data)
381 if errcode == ARCBANK_ERROR_DOWNLOADING then return end
382 if errcode == ARCBANK_ERROR_NONE then
383 local datalen = #data
384 local recrusivecopy
385 recrusivecopy = function(num)
386 if num > datalen then
387 ARCBank.ReadAllAccountProperties(function(errcode,data)
388 if errcode == ARCBANK_ERROR_NONE then
389 local datalen = #data
390 local recrusivecopyy
391 recrusivecopyy = function(num)
392 if num > datalen then
393 ARCBank.Msg(ARCBANK_ERRORSTRINGS[0])
394 for _,plys in pairs(player.GetAll()) do
395 ARCBank.MsgCL(plys,ARCBANK_ERRORSTRINGS[0])
396 end
397 ARCBank.Busy = false
398 return
399 end
400 if data[num].rank > ARCBANK_GROUPACCOUNTS_ then
401 ARCBank.ReadGroupMembers(data[num].account,function(err,gdata)
402 if err == ARCBANK_ERROR_NONE then
403 local groupstr = ""
404 for k,v in ipairs(gdata) do
405 groupstr = groupstr .. v .. ","
406 end
407 file.Write(ARCBank.Dir.."/groups_1.4/"..tostring(data[num].account)..".txt",groupstr)
408 file.Write(ARCBank.Dir.."/accounts_1.4/"..tostring(data[num].account)..".txt",util.TableToJSON(data[num]))
409
410 ARCBank.Msg(ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/datalen)*50)+50)..")")
411 for _,plys in pairs(player.GetAll()) do
412 ARCBank.MsgCL(plys,ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/datalen)*50)+50)..")")
413 end
414 timer.Simple(0.0001,function() recrusivecopyy(num+1) end)
415 else
416 ARCBank.Msg("[ERROR!] ARCBank.ReadGroupMembers: "..ARCBANK_ERRORSTRINGS[errcode])
417 for _,plys in pairs(player.GetAll()) do
418 ARCBank.MsgCL(plys,"[ERROR!] ARCBank.ReadGroupMembers: "..ARCBANK_ERRORSTRINGS[errcode])
419 end
420 end
421 end)
422 else
423 file.Write(ARCBank.Dir.."/accounts_1.4/"..tostring(data[num].account)..".txt",util.TableToJSON(data[num]))
424 ARCBank.Msg(ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/datalen)*50)+50)..")")
425 for _,plys in pairs(player.GetAll()) do
426 ARCBank.MsgCL(plys,ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/datalen)*50)+50)..")")
427 end
428 timer.Simple(0.0001,function() recrusivecopyy(num+1) end)
429 end
430 end
431 recrusivecopyy(1)
432 else
433 ARCBank.Msg("[ERROR!] ARCBank.ReadAllAccountProperties: "..ARCBANK_ERRORSTRINGS[errcode])
434 for _,plys in pairs(player.GetAll()) do
435 ARCBank.MsgCL(plys,"[ERROR!] ARCBank.ReadAllAccountProperties: "..ARCBANK_ERRORSTRINGS[errcode])
436 end
437 end
438 end)
439 else
440 ARCBank.WriteTransaction(data[num].account1,data[num].account2,data[num].user1,data[num].user2,data[num].moneydiff,data[num].money,data[num].transaction_type,data[num].comment,function(errcode)
441 if errcode == ARCBANK_ERROR_NONE then
442 ARCBank.Msg(ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/datalen)*50))..")")
443 for _,plys in pairs(player.GetAll()) do
444 ARCBank.MsgCL(plys,ARCBank.Msgs.ATMMsgs.LoadingMsg.." (%"..tostring(math.floor((num/datalen)*50))..")")
445 end
446 recrusivecopy(num+1)
447 else
448 ARCBank.Msg("[ERROR!] (%"..tostring(math.floor((num/datalen)*50))..") "..ARCBANK_ERRORSTRINGS[errcode])
449 for _,plys in pairs(player.GetAll()) do
450 ARCBank.MsgCL(plys,"[ERROR!] (%"..tostring(math.floor((num/datalen)*50))..") "..ARCBANK_ERRORSTRINGS[errcode])
451 end
452 end
453 end,data[num].timestamp,true)
454 end
455 end
456 recrusivecopy(1)
457 else
458 ARCBank.Msg("[ERROR!] ARCBank.ReadTransactions: "..ARCBANK_ERRORSTRINGS[errcode])
459 for _,plys in pairs(player.GetAll()) do
460 ARCBank.MsgCL(plys,"[ERROR!] ARCBank.ReadTransactions: "..ARCBANK_ERRORSTRINGS[errcode])
461 end
462 end
463 end)
464 else
465 MsgN("ARCBank: Invalid Command.")
466 end
467 end,
468 usage = " [command(string)]",
469 description = "Copies local database to remote SQL and back.",
470 adminonly = false,
471 hidden = false}