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