· 6 years ago · Jun 07, 2019, 08:18 PM
1-- Script Made by: Aperture-Hosting, edited by Princess Celestia
2-- Web: www.Aperture-Hosting.de
3-- Contact: webmaster@aperture-hosting.de
4
5if(file.Exists( "bin/gmsv_mysqloo_linux.dll", "LUA" ) or file.Exists( "bin/gmsv_mysqloo_win32.dll", "LUA" ))then
6 MSync = MSync or {}
7 -- TODO move this into some shared config file or somewhere
8 MSync.TableNameBans = "mbsync"
9 MSync.TableNameRanks = "mrsync"
10 local ulxsql = ulxsql or {}
11 local ULXDB = ULXDB or {}
12
13 function MSync.Connect()
14 require("mysqloo")
15 MSync.DB = mysqloo.connect(MSync.Settings.mysql.Host, MSync.Settings.mysql.Username, MSync.Settings.mysql.Password, MSync.Settings.mysql.Database, MSync.Settings.mysql.Port)
16 MSync.DB.onConnected = MSync.checkTables
17 MSync.DB.onConnectionFailed = MSync.DBError
18 MSync.DB:connect()
19 end
20
21 function MSync.DBError()
22 Msg("[MSync] Connection to database failed\n")
23 end
24
25 -- TODO find usages and replace them with query:hasMoreResults()
26 function checkQuery(query)
27 local playerInfo = query:getData()
28 return playerInfo[1] ~= nil
29 end
30
31 function MSync.checkTables(server)
32 print("[MSync] Connected to database")
33 print("[MSync] Checking database")
34
35 --Create Version Table if not Exists
36 local MSync_Version_Table_create = server:query([[CREATE TABLE IF NOT EXISTS `msync_db_version` (`version` float NOT NULL)]])
37 MSync_Version_Table_create.onError = function(Q,E) print("Q1") print(E) end
38 --If query was successfull we call the rest
39 MSync_Version_Table_create.onSuccess = function(data)
40
41 --Get the Version Table
42 local MSync_Version_Table = server:query([[
43 SELECT * FROM `msync_db_version`
44 ]])
45 MSync_Version_Table.onError = function(Q,E) print("Q1") print(E) end
46 MSync_Version_Table.onSuccess = function(data)
47
48 --Alter MSync DBVersion
49 /*
50 if(MSync.Settings.DBVersion<1.5)then
51 local MSync_Version_Table = server:query([[
52 DELETE FROM `msync_db_version`;
53 ALTER TABLE `msync_db_version`
54 ADD version_index varchar(30) NOT NULL,
55 ADD UNIQUE INDEX `unq_index` (`version_index` ASC);
56 ]])
57 MSync_Version_Table.onError = function(Q,E) print("Q1") print(E) end
58 MSync_Version_Table:start()
59
60 MSync.Settings.DBVersion = MSync.DBVersion
61 MSync.Settings.RDBVersion = MSync.Settings.RDBVersion or 0
62 MSync.Settings.BDBVersion = MSync.Settings.BDBVersion or 0
63 end
64 */
65 --Check if the query returned data and if so we set our local variables
66 if MSync_Version_Table:getData()~=nil then
67 local version_tbl = MSync_Version_Table:getData()
68
69 for k,v in pairs(version_tbl) do
70 MSync.Settings[(v.version_index)] = v.version
71 print("[MSync] Version "..v.version_index..": "..v.version)
72 end
73 end
74
75
76 local transaction = server:createTransaction()
77
78 if(table.HasValue(MSync.Settings.EnabledModules, "MRSync")) then
79 if (MSync.Settings.RDBVersion < 1.0) then
80 local MRSyncCT = server:query([[
81 CREATE TABLE IF NOT EXISTS `]] .. MSync.TableNameRanks .. [[` (
82 `steamid` varchar(20) NOT NULL,
83 `groups` varchar(30) NOT NULL,
84 `servergroup` varchar(30) NOT NULL
85 )
86 ]])
87 transaction:addQuery(MRSyncCT)
88 end
89
90 if(MSync.Settings.RDBVersion < 1.1)then
91 local MRSyncUpdateDB = server:query([[
92 ALTER TABLE `]]..MSync.TableNameRanks..[[`
93 ADD UNIQUE INDEX `unq_user` (`steamid` ASC, `servergroup` ASC)
94 ]])
95 transaction:addQuery(MRSyncUpdateDB)
96 print("[MRSync] Going to update DB structure to v1.1")
97 MSync.Settings.RDBVersion = MSync.RDBVersion
98 end
99 end
100
101
102 if(table.HasValue(MSync.Settings.EnabledModules, "MBSync")) then
103
104 if (MSync.Settings.BDBVersion < 1.0) then
105 local MBSyncCT = server:query([[
106 CREATE TABLE IF NOT EXISTS `]] .. MSync.TableNameBans .. [[` (
107 `steamid` varchar(20) NOT NULL,
108 `nickname` varchar(30) NOT NULL,
109 `admin` varchar(30) NOT NULL,
110 `reason` varchar(30) NOT NULL,
111 `ban_date` INT NOT NULL,
112 `duration` INT NOT NULL,
113 UNIQUE KEY `steamid_UNIQUE` (`steamid`)
114 )
115 ]])
116 transaction:addQuery(MBSyncCT)
117 --print("[MBSync] Going to update DB structure to v1.0")
118 end
119
120
121 if (MSync.Settings.BDBVersion < 1.1) then
122 -- Add `admin_sid` column if it doesn't already exist.
123 local column = 'admin_sid'
124 local updateQuery = server:query([[
125 SET @preparedStatement = IF(
126 (SELECT COUNT(*)
127 FROM INFORMATION_SCHEMA.COLUMNS
128 WHERE table_name = ']] .. MSync.TableNameBans .. [['
129 AND table_schema = DATABASE()
130 AND column_name = ']] .. column .. [['
131 ) > 0,
132 'SELECT 1;',
133 'ALTER TABLE `]] .. MSync.TableNameBans .. [[` ADD `]] .. column .. [[` VARCHAR(20) AFTER `nickname`;'
134 );
135 PREPARE alterIfNotExists FROM @preparedStatement;
136 EXECUTE alterIfNotExists;
137 DEALLOCATE PREPARE alterIfNotExists;
138 ]])
139 transaction:addQuery(updateQuery)
140 print("[MBSync] Going to update DB structure to v1.1")
141
142 end
143
144 if (MSync.Settings.BDBVersion < 1.2) then
145 -- Rename some columns, allow NULLs, increase VARCHAR sizes and change INTs to unsigned
146 local updateQuery = server:query([[
147 ALTER TABLE `]] .. MSync.TableNameBans .. [[`
148 CHANGE COLUMN `nickname` `name` VARCHAR(32),
149 CHANGE COLUMN `admin` `staff_name` VARCHAR(32) NOT NULL,
150 CHANGE COLUMN `admin_sid` `staff_steamid` VARCHAR(20),
151 MODIFY `reason` VARCHAR(255) NOT NULL,
152 MODIFY `ban_date` INT UNSIGNED NOT NULL,
153 MODIFY `duration` INT UNSIGNED NOT NULL;
154 ]])
155 transaction:addQuery(updateQuery)
156 print("[MBSync] Going to update DB structure to v1.2")
157 end
158
159 if (MSync.Settings.BDBVersion < 1.3) then
160 -- Drop the unique on `steamid`, add auto-incrementing `id` and set it as new PK
161 local updateQuery = server:query([[
162 ALTER TABLE `]] .. MSync.TableNameBans .. [[`
163 DROP INDEX steamid_UNIQUE,
164 ADD `id` INT PRIMARY KEY AUTO_INCREMENT FIRST,
165 ADD INDEX steamid_INDEX (`steamid`);
166 ]])
167 transaction:addQuery(updateQuery)
168 print("[MBSync] Going to update DB structure to v1.3")
169 MSync.Settings.BDBVersion = MSync.BDBVersion
170 end
171 end
172 --[[if(table.HasValue(MSync.Settings.EnabledModules, "MPSync")) then
173 //Ranks
174 //Permissions
175 //Rank ID and Permission ID
176 //Servers
177 //Server id and Permission ID
178 end]]--
179
180 -- Start the transaction, if any queries were added to it
181 if transaction:getQueries() ~= nil then
182 transaction.onError = function (tr, err) print("[MSync] Database creation/update failed: " .. err) end
183 transaction.onSuccess = function ()
184 MSync.SaveSettings()
185 print("[MSync] Database upgrade successful, current DB schema version " .. MSync.DBVersion)
186 end
187 transaction:start()
188 end
189
190 local MSync_Version_Table = server:query([[
191 INSERT into `msync_db_version` (`version`,`version_index`) VALUES (']]..MSync.Settings.DBVersion..[[','DBVersion') ON DUPLICATE KEY UPDATE version=VALUES(version);
192 INSERT into `msync_db_version` (`version`,`version_index`) VALUES (']]..MSync.Settings.RDBVersion..[[','RDBVersion') ON DUPLICATE KEY UPDATE version=VALUES(version);
193 INSERT into `msync_db_version` (`version`,`version_index`) VALUES (']]..MSync.Settings.BDBVersion..[[','BDBVersion') ON DUPLICATE KEY UPDATE version=VALUES(version)
194 ]])
195 MSync_Version_Table.onError = function(Q,E) print("Q1") print(E) end
196 MSync_Version_Table:start()
197 --MSync_Version_Table:wait()
198 end
199 MSync_Version_Table:start()
200 end
201 MSync_Version_Table_create:start()
202 end
203
204 MSync.Connect()
205
206 include( "msync/mrsync_sql.lua" )
207 include( "msync/mbsync_sql.lua" )
208 include( "msync/mbsync_chat.lua" )
209 include( "msync/msync_hooks.lua" )
210
211else
212 print('[MSync] WARNING! You need MySQLoo v9 or higher for this addon to work!')
213 print('[MSync] Get it from here: https://facepunch.com/showthread.php?t=1515853')
214 print('[MSync] Here are installation instructions:')
215 print('[MSync] https://help.serenityservers.net/index.php?title=Garrysmod:How_to_install_mysqloo_or_tmysql')
216end