anope- supernets anope source code & configuration |
| git clone git://git.acid.vegas/anope.git |
| Log | Files | Refs | Archive | README |
tables.cpp (12795B)
1 /* 2 * 3 * (C) 2013-2022 Anope Team 4 * Contact us at team@anope.org 5 * 6 * Please read COPYING and README for further details. 7 */ 8 9 #include "irc2sql.h" 10 11 void IRC2SQL::CheckTables() 12 { 13 Anope::string geoquery(""); 14 15 if (firstrun) 16 { 17 /* 18 * reset some tables to make sure they are really empty 19 */ 20 this->sql->RunQuery("TRUNCATE TABLE " + prefix + "user"); 21 this->sql->RunQuery("TRUNCATE TABLE " + prefix + "chan"); 22 this->sql->RunQuery("TRUNCATE TABLE " + prefix + "ison"); 23 this->sql->RunQuery("UPDATE `" + prefix + "server` SET currentusers=0, online='N'"); 24 } 25 26 this->GetTables(); 27 28 if (GeoIPDB.equals_ci("country")) 29 { 30 if (!this->HasTable(prefix + "geoip_country")) 31 { 32 query = "CREATE TABLE `" + prefix + "geoip_country` (" 33 "`start` INT UNSIGNED NOT NULL," 34 "`end` INT UNSIGNED NOT NULL," 35 "`countrycode` varchar(2)," 36 "`countryname` varchar(50)," 37 "PRIMARY KEY `end` (`end`)," 38 "KEY `start` (`start`)" 39 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 40 this->RunQuery(query); 41 } 42 } 43 else if (GeoIPDB.equals_ci("city")) 44 { 45 if (!this->HasTable(prefix + "geoip_city_blocks")) 46 { 47 query = "CREATE TABLE `" + prefix + "geoip_city_blocks` (" 48 "`start` INT UNSIGNED NOT NULL," 49 "`end` INT UNSIGNED NOT NULL," 50 "`locId` INT UNSIGNED NOT NULL," 51 "PRIMARY KEY `end` (`end`)," 52 "KEY `start` (`start`)" 53 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 54 this->RunQuery(query); 55 } 56 if (!this->HasTable(prefix + "geoip_city_location")) 57 { 58 query = "CREATE TABLE `" + prefix + "geoip_city_location` (" 59 "`locId` INT UNSIGNED NOT NULL," 60 "`country` CHAR(2) NOT NULL," 61 "`region` CHAR(2) NOT NULL," 62 "`city` VARCHAR(50)," 63 "`latitude` FLOAT," 64 "`longitude` FLOAT," 65 "`areaCode` INT," 66 "PRIMARY KEY (`locId`)" 67 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 68 this->RunQuery(query); 69 } 70 if (!this->HasTable(prefix + "geoip_city_region")) 71 { query = "CREATE TABLE `" + prefix + "geoip_city_region` (" 72 "`country` CHAR(2) NOT NULL," 73 "`region` CHAR(2) NOT NULL," 74 "`regionname` VARCHAR(100) NOT NULL," 75 "PRIMARY KEY (`country`,`region`)" 76 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 77 this->RunQuery(query); 78 } 79 } 80 if (!this->HasTable(prefix + "server")) 81 { 82 query = "CREATE TABLE `" + prefix + "server` (" 83 "`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT," 84 "`name` varchar(64) NOT NULL," 85 "`hops` tinyint(3) NOT NULL," 86 "`comment` varchar(255) NOT NULL," 87 "`link_time` datetime DEFAULT NULL," 88 "`split_time` datetime DEFAULT NULL," 89 "`version` varchar(127) DEFAULT NULL," 90 "`currentusers` int(15) DEFAULT 0," 91 "`online` enum('Y','N') NOT NULL DEFAULT 'Y'," 92 "`ulined` enum('Y','N') NOT NULL DEFAULT 'N'," 93 "PRIMARY KEY (`id`)," 94 "UNIQUE KEY `name` (`name`)" 95 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 96 this->RunQuery(query); 97 } 98 if (!this->HasTable(prefix + "chan")) 99 { 100 query = "CREATE TABLE `" + prefix + "chan` (" 101 "`chanid` int(11) UNSIGNED NOT NULL AUTO_INCREMENT," 102 "`channel` varchar(255) NOT NULL," 103 "`topic` varchar(512) DEFAULT NULL," 104 "`topicauthor` varchar(255) DEFAULT NULL," 105 "`topictime` datetime DEFAULT NULL," 106 "`modes` varchar(512) DEFAULT NULL," 107 "PRIMARY KEY (`chanid`)," 108 "UNIQUE KEY `channel`(`channel`)" 109 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 110 this->RunQuery(query); 111 } 112 if (!this->HasTable(prefix + "user")) 113 { 114 query = "CREATE TABLE `" + prefix + "user` (" 115 "`nickid` int(11) UNSIGNED NOT NULL AUTO_INCREMENT," 116 "`nick` varchar(255) NOT NULL DEFAULT ''," 117 "`host` varchar(255) NOT NULL DEFAULT ''," 118 "`vhost` varchar(255) NOT NULL DEFAULT ''," 119 "`chost` varchar(255) NOT NULL DEFAULT ''," 120 "`realname` varchar(255) NOT NULL DEFAULT ''," 121 "`ip` varchar(255) NOT NULL DEFAULT ''," 122 "`ident` varchar(32) NOT NULL DEFAULT ''," 123 "`vident` varchar(32) NOT NULL DEFAULT ''," 124 "`modes` varchar(255) NOT NULL DEFAULT ''," 125 "`account` varchar(255) NOT NULL DEFAULT ''," 126 "`secure` enum('Y','N') NOT NULL DEFAULT 'N'," 127 "`fingerprint` varchar(128) NOT NULL DEFAULT ''," 128 "`signon` datetime DEFAULT NULL," 129 "`server` varchar(255) NOT NULL DEFAULT ''," 130 "`servid` int(11) UNSIGNED NOT NULL DEFAULT '0'," 131 "`uuid` varchar(32) NOT NULL DEFAULT ''," 132 "`oper` enum('Y','N') NOT NULL DEFAULT 'N'," 133 "`away` enum('Y','N') NOT NULL DEFAULT 'N'," 134 "`awaymsg` varchar(255) NOT NULL DEFAULT ''," 135 "`version` varchar(255) NOT NULL DEFAULT ''," 136 "`geocode` varchar(16) NOT NULL DEFAULT ''," 137 "`geocountry` varchar(64) NOT NULL DEFAULT ''," 138 "`georegion` varchar(100) NOT NULL DEFAULT ''," 139 "`geocity` varchar(128) NOT NULL DEFAULT ''," 140 "`locId` INT UNSIGNED," 141 "PRIMARY KEY (`nickid`)," 142 "UNIQUE KEY `nick` (`nick`)," 143 "KEY `servid` (`servid`)" 144 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 145 this->RunQuery(query); 146 } 147 if (!this->HasTable(prefix + "ison")) 148 { 149 query = "CREATE TABLE `" + prefix + "ison` (" 150 "`nickid` int(11) unsigned NOT NULL default '0'," 151 "`chanid` int(11) unsigned NOT NULL default '0'," 152 "`modes` varchar(255) NOT NULL default ''," 153 "PRIMARY KEY (`nickid`,`chanid`)," 154 "KEY `modes` (`modes`)" 155 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 156 this->RunQuery(query); 157 } 158 if (!this->HasTable(prefix + "maxusers")) 159 { 160 query = "CREATE TABLE `" + prefix + "maxusers` (" 161 "`name` VARCHAR(255) NOT NULL," 162 "`maxusers` INT(15) NOT NULL," 163 "`maxtime` DATETIME NOT NULL," 164 "`lastused` DATETIME NOT NULL," 165 "UNIQUE KEY `name` (`name`)" 166 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; 167 this->RunQuery(query); 168 } 169 if (this->HasProcedure(prefix + "UserConnect")) 170 this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "UserConnect")); 171 172 if (GeoIPDB.equals_ci("country")) 173 geoquery = "UPDATE `" + prefix + "user` AS u " 174 "JOIN ( SELECT `countrycode`, `countryname` " 175 "FROM `" + prefix + "geoip_country` " 176 "WHERE INET_ATON(ip_) <= `end` " 177 "AND `start` <= INET_ATON(ip_) " 178 "ORDER BY `end` ASC LIMIT 1 ) as c " 179 "SET u.geocode = c.countrycode, u.geocountry = c.countryname " 180 "WHERE u.nick = nick_; "; 181 else if (GeoIPDB.equals_ci("city")) 182 geoquery = "UPDATE `" + prefix + "user` as u " 183 "JOIN ( SELECT * FROM `" + prefix + "geoip_city_location` " 184 "WHERE `locID` = ( SELECT `locID` " 185 "FROM `" + prefix + "geoip_city_blocks` " 186 "WHERE INET_ATON(ip_) <= `end` " 187 "AND `start` <= INET_ATON(ip_) " 188 "ORDER BY `end` ASC LIMIT 1 ) " 189 ") as l " 190 "SET u.geocode = l.country, " 191 "u.geocity = l.city, " 192 "u.locID = l.locID, " 193 "u.georegion = ( SELECT `regionname` " 194 "FROM `" + prefix + "geoip_city_region` " 195 "WHERE `country` = l.country " 196 "AND `region` = l.region )" 197 "WHERE u.nick = nick_;"; 198 199 query = "CREATE PROCEDURE `" + prefix + "UserConnect`" 200 "(nick_ varchar(255), host_ varchar(255), vhost_ varchar(255), " 201 "chost_ varchar(255), realname_ varchar(255), ip_ varchar(255), " 202 "ident_ varchar(255), vident_ varchar(255), account_ varchar(255), " 203 "secure_ enum('Y','N'), fingerprint_ varchar(255), signon_ int(15), " 204 "server_ varchar(255), uuid_ varchar(32), modes_ varchar(255), " 205 "oper_ enum('Y','N')) " 206 "BEGIN " 207 "DECLARE cur int(15);" 208 "DECLARE max int(15);" 209 "INSERT INTO `" + prefix + "user` " 210 "(nick, host, vhost, chost, realname, ip, ident, vident, account, " 211 "secure, fingerprint, signon, server, uuid, modes, oper) " 212 "VALUES (nick_, host_, vhost_, chost_, realname_, ip_, ident_, vident_, " 213 "account_, secure_, fingerprint_, FROM_UNIXTIME(signon_), server_, " 214 "uuid_, modes_, oper_) " 215 "ON DUPLICATE KEY UPDATE host=VALUES(host), vhost=VALUES(vhost), " 216 "chost=VALUES(chost), realname=VALUES(realname), ip=VALUES(ip), " 217 "ident=VALUES(ident), vident=VALUES(vident), account=VALUES(account), " 218 "secure=VALUES(secure), fingerprint=VALUES(fingerprint), signon=VALUES(signon), " 219 "server=VALUES(server), uuid=VALUES(uuid), modes=VALUES(modes), " 220 "oper=VALUES(oper);" 221 "UPDATE `" + prefix + "user` as `u`, `" + prefix + "server` as `s`" 222 "SET u.servid = s.id, " 223 "s.currentusers = s.currentusers + 1 " 224 "WHERE s.name = server_ AND u.nick = nick_;" 225 "SELECT `currentusers` INTO cur FROM `" + prefix + "server` WHERE name=server_;" 226 "SELECT `maxusers` INTO max FROM `" + prefix + "maxusers` WHERE name=server_;" 227 "IF found_rows() AND cur <= max THEN " 228 "UPDATE `" + prefix + "maxusers` SET lastused=now() WHERE name=server_;" 229 "ELSE " 230 "INSERT INTO `" + prefix + "maxusers` (name, maxusers, maxtime, lastused) " 231 "VALUES ( server_, cur, now(), now() ) " 232 "ON DUPLICATE KEY UPDATE " 233 "name=VALUES(name), maxusers=VALUES(maxusers)," 234 "maxtime=VALUES(maxtime), lastused=VALUES(lastused);" 235 "END IF;" 236 + geoquery + 237 "END"; 238 this->RunQuery(query); 239 240 if (this->HasProcedure(prefix + "ServerQuit")) 241 this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "ServerQuit")); 242 query = "CREATE PROCEDURE " + prefix + "ServerQuit(sname_ varchar(255)) " 243 "BEGIN " 244 /* 1. 245 * remove all users on the splitting server from the ison table 246 */ 247 "DELETE i FROM `" + prefix + "ison` AS i " 248 "INNER JOIN `" + prefix + "server` AS s " 249 "INNER JOIN `" + prefix + "user` AS u " 250 "WHERE i.nickid = u.nickid " 251 "AND u.servid = s.id " 252 "AND s.name = sname_;" 253 254 /* 2. 255 * remove all users on the splitting server from the user table 256 */ 257 "DELETE u FROM `" + prefix + "user` AS u " 258 "INNER JOIN `" + prefix + "server` AS s " 259 "WHERE s.id = u.servid " 260 "AND s.name = sname_;" 261 262 /* 3. 263 * on the splitting server, set usercount = 0, split_time = now(), online = 'N' 264 */ 265 "UPDATE `" + prefix + "server` SET currentusers = 0, split_time = now(), online = 'N' " 266 "WHERE name = sname_;" 267 "END;"; // end of the procedure 268 this->RunQuery(query); 269 270 271 if (this->HasProcedure(prefix + "UserQuit")) 272 this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "UserQuit")); 273 query = "CREATE PROCEDURE `" + prefix + "UserQuit`" 274 "(nick_ varchar(255)) " 275 "BEGIN " 276 /* decrease usercount on the server where the user was on */ 277 "UPDATE `" + prefix + "user` AS `u`, `" + prefix + "server` AS `s` " 278 "SET s.currentusers = s.currentusers - 1 " 279 "WHERE u.nick=nick_ AND u.servid = s.id; " 280 /* remove from all channels where the user was on */ 281 "DELETE i FROM `" + prefix + "ison` AS i " 282 "INNER JOIN `" + prefix + "user` as u " 283 "WHERE u.nick = nick_ " 284 "AND i.nickid = u.nickid;" 285 /* remove the user from the user table */ 286 "DELETE FROM `" + prefix + "user` WHERE nick = nick_; " 287 "END"; 288 this->RunQuery(query); 289 290 if (this->HasProcedure(prefix + "ShutDown")) 291 this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "ShutDown")); 292 query = "CREATE PROCEDURE `" + prefix + "ShutDown`()" 293 "BEGIN " 294 "UPDATE `" + prefix + "server` " 295 "SET currentusers=0, online='N', split_time=now();" 296 "TRUNCATE TABLE `" + prefix + "user`;" 297 "TRUNCATE TABLE `" + prefix + "chan`;" 298 "TRUNCATE TABLE `" + prefix + "ison`;" 299 "END"; 300 this->RunQuery(query); 301 302 if (this->HasProcedure(prefix + "JoinUser")) 303 this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "JoinUser")); 304 query = "CREATE PROCEDURE `"+ prefix + "JoinUser`" 305 "(nick_ varchar(255), channel_ varchar(255), modes_ varchar(255)) " 306 "BEGIN " 307 "DECLARE cur int(15);" 308 "DECLARE max int(15);" 309 "INSERT INTO `" + prefix + "ison` (nickid, chanid, modes) " 310 "SELECT u.nickid, c.chanid, modes_ " 311 "FROM " + prefix + "user AS u, " + prefix + "chan AS c " 312 "WHERE u.nick=nick_ AND c.channel=channel_;" 313 "SELECT count(i.chanid) INTO cur " 314 "FROM `" + prefix + "chan` AS c, " + prefix + "ison AS i " 315 "WHERE i.chanid = c.chanid AND c.channel=channel_;" 316 "SELECT `maxusers` INTO max FROM `" + prefix + "maxusers` WHERE name=channel_;" 317 "IF found_rows() AND cur <= max THEN " 318 "UPDATE `" + prefix + "maxusers` SET lastused=now() WHERE name=channel_;" 319 "ELSE " 320 "INSERT INTO `" + prefix + "maxusers` (name, maxusers, maxtime, lastused) " 321 "VALUES ( channel_, cur, now(), now() ) " 322 "ON DUPLICATE KEY UPDATE " 323 "name=VALUES(name), maxusers=VALUES(maxusers)," 324 "maxtime=VALUES(maxtime), lastused=VALUES(lastused);" 325 "END IF;" 326 "END"; 327 this->RunQuery(query); 328 329 if (this->HasProcedure(prefix + "PartUser")) 330 this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "PartUser")); 331 query = "CREATE PROCEDURE `" + prefix + "PartUser`" 332 "(nick_ varchar(255), channel_ varchar(255)) " 333 "BEGIN " 334 "DELETE i FROM `" + prefix + "ison` AS i " 335 "INNER JOIN `" + prefix + "user` AS u " 336 "INNER JOIN `" + prefix + "chan` AS c " 337 "WHERE i.nickid = u.nickid " 338 "AND u.nick = nick_ " 339 "AND i.chanid = c.chanid " 340 "AND c.channel = channel_;" 341 "END"; 342 this->RunQuery(query); 343 }