summaryrefslogtreecommitdiff
path: root/modules/irc2sql
diff options
context:
space:
mode:
Diffstat (limited to 'modules/irc2sql')
-rw-r--r--modules/irc2sql/CMakeLists.txt1
-rw-r--r--modules/irc2sql/irc2sql.cpp325
-rw-r--r--modules/irc2sql/irc2sql.h88
-rw-r--r--modules/irc2sql/tables.cpp343
-rw-r--r--modules/irc2sql/utils.cpp68
5 files changed, 825 insertions, 0 deletions
diff --git a/modules/irc2sql/CMakeLists.txt b/modules/irc2sql/CMakeLists.txt
new file mode 100644
index 000000000..781f0ef1f
--- /dev/null
+++ b/modules/irc2sql/CMakeLists.txt
@@ -0,0 +1 @@
+build_subdir(${CMAKE_CURRENT_SOURCE_DIR})
diff --git a/modules/irc2sql/irc2sql.cpp b/modules/irc2sql/irc2sql.cpp
new file mode 100644
index 000000000..a587b3f5e
--- /dev/null
+++ b/modules/irc2sql/irc2sql.cpp
@@ -0,0 +1,325 @@
+/*
+ *
+ * (C) 2013-2024 Anope Team
+ * Contact us at team@anope.org
+ *
+ * Please read COPYING and README for further details.
+ */
+
+#include "irc2sql.h"
+
+void IRC2SQL::OnShutdown()
+{
+ // TODO: test if we really have to use blocking query here
+ // (sometimes mysql get unloaded before the other thread executed all queries)
+ if (this->sql)
+ SQL::Result r = this->sql->RunQuery(SQL::Query("CALL " + prefix + "OnShutdown()"));
+ quitting = true;
+}
+
+void IRC2SQL::OnReload(Configuration::Conf *conf)
+{
+ Configuration::Block *block = Config->GetModule(this);
+ prefix = block->Get<const Anope::string>("prefix", "anope_");
+ GeoIPDB = block->Get<const Anope::string>("geoip_database");
+ ctcpuser = block->Get<bool>("ctcpuser", "no");
+ ctcpeob = block->Get<bool>("ctcpeob", "yes");
+ Anope::string engine = block->Get<const Anope::string>("engine");
+ this->sql = ServiceReference<SQL::Provider>("SQL::Provider", engine);
+ if (sql)
+ this->CheckTables();
+ else
+ Log() << "IRC2SQL: no database connection to " << engine;
+
+ const Anope::string &snick = block->Get<const Anope::string>("client");
+ if (snick.empty())
+ throw ConfigException(Module::name + ": <client> must be defined");
+ StatServ = BotInfo::Find(snick, true);
+ if (!StatServ)
+ throw ConfigException(Module::name + ": no bot named " + snick);
+
+ if (firstrun)
+ {
+ firstrun = false;
+
+ for (Anope::map<Server *>::const_iterator it = Servers::ByName.begin(); it != Servers::ByName.end(); ++it)
+ {
+ this->OnNewServer(it->second);
+ }
+
+ for (const auto &[_, c] : ChannelList)
+ {
+ this->OnChannelCreate(c);
+ }
+
+ for (const auto &[_, u] : UserListByNick)
+ {
+ bool exempt = false;
+ this->OnUserConnect(u, exempt);
+ for (const auto &[_, uc] : u->chans)
+ {
+ this->OnJoinChannel(u, uc->chan);
+ }
+ }
+ }
+
+}
+
+void IRC2SQL::OnNewServer(Server *server)
+{
+ query = "INSERT DELAYED INTO `" + prefix + "server` (name, hops, comment, link_time, online, ulined) "
+ "VALUES (@name@, @hops@, @comment@, now(), 'Y', @ulined@) "
+ "ON DUPLICATE KEY UPDATE name=VALUES(name), hops=VALUES(hops), comment=VALUES(comment), "
+ "link_time=VALUES(link_time), online=VALUES(online), ulined=VALUES(ulined)";
+ query.SetValue("name", server->GetName());
+ query.SetValue("hops", server->GetHops());
+ query.SetValue("comment", server->GetDescription());
+ query.SetValue("ulined", server->IsULined() ? "Y" : "N");
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnServerQuit(Server *server)
+{
+ if (quitting)
+ return;
+
+ query = "CALL " + prefix + "ServerQuit(@name@)";
+ query.SetValue("name", server->GetName());
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnUserConnect(User *u, bool &exempt)
+{
+ if (!introduced_myself)
+ {
+ this->OnNewServer(Me);
+ introduced_myself = true;
+ }
+
+ query = "CALL " + prefix + "UserConnect(@nick@,@host@,@vhost@,@chost@,@realname@,@ip@,@ident@,@vident@,"
+ "@account@,@secure@,@fingerprint@,@signon@,@server@,@uuid@,@modes@,@oper@)";
+ query.SetValue("nick", u->nick);
+ query.SetValue("host", u->host);
+ query.SetValue("vhost", u->vhost);
+ query.SetValue("chost", u->chost);
+ query.SetValue("realname", u->realname);
+ query.SetValue("ip", u->ip.addr());
+ query.SetValue("ident", u->GetIdent());
+ query.SetValue("vident", u->GetVIdent());
+ query.SetValue("secure", u->IsSecurelyConnected() ? "Y" : "N");
+ query.SetValue("account", u->IsIdentified() ? u->Account()->display : "");
+ query.SetValue("fingerprint", u->fingerprint);
+ query.SetValue("signon", u->signon);
+ query.SetValue("server", u->server->GetName());
+ query.SetValue("uuid", u->GetUID());
+ query.SetValue("modes", u->GetModes());
+ query.SetValue("oper", u->HasMode("OPER") ? "Y" : "N");
+ this->RunQuery(query);
+
+ if (ctcpuser && (Me->IsSynced() || ctcpeob) && u->server != Me)
+ IRCD->SendPrivmsg(StatServ, u->GetUID(), Anope::FormatCTCP("VERSION"));
+
+}
+
+void IRC2SQL::OnUserQuit(User *u, const Anope::string &msg)
+{
+ if (quitting || u->server->IsQuitting())
+ return;
+
+ query = "CALL " + prefix + "UserQuit(@nick@)";
+ query.SetValue("nick", u->nick);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnUserNickChange(User *u, const Anope::string &oldnick)
+{
+ query = "UPDATE `" + prefix + "user` SET nick=@newnick@ WHERE nick=@oldnick@";
+ query.SetValue("newnick", u->nick);
+ query.SetValue("oldnick", oldnick);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnUserAway(User *u, const Anope::string &message)
+{
+ query = "UPDATE `" + prefix + "user` SET away=@away@, awaymsg=@awaymsg@ WHERE nick=@nick@";
+ query.SetValue("away", (!message.empty()) ? "Y" : "N");
+ query.SetValue("awaymsg", message);
+ query.SetValue("nick", u->nick);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnFingerprint(User *u)
+{
+ query = "UPDATE `" + prefix + "user` SET secure=@secure@, fingerprint=@fingerprint@ WHERE nick=@nick@";
+ query.SetValue("secure", u->IsSecurelyConnected() ? "Y" : "N");
+ query.SetValue("fingerprint", u->fingerprint);
+ query.SetValue("nick", u->nick);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnUserModeSet(const MessageSource &setter, User *u, const Anope::string &mname)
+{
+ query = "UPDATE `" + prefix + "user` SET modes=@modes@, oper=@oper@ WHERE nick=@nick@";
+ query.SetValue("nick", u->nick);
+ query.SetValue("modes", u->GetModes());
+ query.SetValue("oper", u->HasMode("OPER") ? "Y" : "N");
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnUserModeUnset(const MessageSource &setter, User *u, const Anope::string &mname)
+{
+ this->OnUserModeSet(setter, u, mname);
+}
+
+void IRC2SQL::OnUserLogin(User *u)
+{
+ query = "UPDATE `" + prefix + "user` SET account=@account@ WHERE nick=@nick@";
+ query.SetValue("nick", u->nick);
+ query.SetValue("account", u->IsIdentified() ? u->Account()->display : "");
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnNickLogout(User *u)
+{
+ this->OnUserLogin(u);
+}
+
+void IRC2SQL::OnSetDisplayedHost(User *u)
+{
+ query = "UPDATE `" + prefix + "user` "
+ "SET vhost=@vhost@ "
+ "WHERE nick=@nick@";
+ query.SetValue("vhost", u->GetDisplayedHost());
+ query.SetValue("nick", u->nick);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnChannelCreate(Channel *c)
+{
+ query = "INSERT INTO `" + prefix + "chan` (channel, topic, topicauthor, topictime, modes) "
+ "VALUES (@channel@,@topic@,@topicauthor@,@topictime@,@modes@) "
+ "ON DUPLICATE KEY UPDATE channel=VALUES(channel), topic=VALUES(topic),"
+ "topicauthor=VALUES(topicauthor), topictime=VALUES(topictime), modes=VALUES(modes)";
+ query.SetValue("channel", c->name);
+ query.SetValue("topic", c->topic);
+ query.SetValue("topicauthor", c->topic_setter);
+ if (c->topic_ts > 0)
+ query.SetValue("topictime", c->topic_ts);
+ else
+ query.SetValue("topictime", "NULL", false);
+ query.SetValue("modes", c->GetModes(true,true));
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnChannelDelete(Channel *c)
+{
+ query = "DELETE FROM `" + prefix + "chan` WHERE channel=@channel@";
+ query.SetValue("channel", c->name);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnJoinChannel(User *u, Channel *c)
+{
+ Anope::string modes;
+ ChanUserContainer *cu = u->FindChannel(c);
+ if (cu)
+ modes = cu->status.Modes();
+
+ query = "CALL " + prefix + "JoinUser(@nick@,@channel@,@modes@)";
+ query.SetValue("nick", u->nick);
+ query.SetValue("channel", c->name);
+ query.SetValue("modes", modes);
+ this->RunQuery(query);
+}
+
+EventReturn IRC2SQL::OnChannelModeSet(Channel *c, MessageSource &setter, ChannelMode *mode, const Anope::string &param)
+{
+ if (mode->type == MODE_STATUS)
+ {
+ User *u = User::Find(param);
+ if (u == NULL)
+ return EVENT_CONTINUE;
+
+ ChanUserContainer *cc = u->FindChannel(c);
+ if (cc == NULL)
+ return EVENT_CONTINUE;
+
+ query = "UPDATE `" + prefix + "user` AS u, `" + prefix + "ison` AS i, `" + prefix + "chan` AS c"
+ " SET i.modes=@modes@"
+ " WHERE u.nick=@nick@ AND c.channel=@channel@"
+ " AND u.nickid = i.nickid AND c.chanid = i.chanid";
+ query.SetValue("nick", u->nick);
+ query.SetValue("modes", cc->status.Modes());
+ query.SetValue("channel", c->name);
+ this->RunQuery(query);
+ }
+ else
+ {
+ query = "UPDATE `" + prefix + "chan` SET modes=@modes@ WHERE channel=@channel@";
+ query.SetValue("channel", c->name);
+ query.SetValue("modes", c->GetModes(true,true));
+ this->RunQuery(query);
+ }
+ return EVENT_CONTINUE;
+}
+
+EventReturn IRC2SQL::OnChannelModeUnset(Channel *c, MessageSource &setter, ChannelMode *mode, const Anope::string &param)
+{
+ this->OnChannelModeSet(c, setter, mode, param);
+ return EVENT_CONTINUE;
+}
+
+void IRC2SQL::OnLeaveChannel(User *u, Channel *c)
+{
+ if (quitting)
+ return;
+ /*
+ * user is quitting, we already received a OnUserQuit()
+ * at this point the user is already removed from SQL and all channels
+ */
+ if (u->Quitting())
+ return;
+ query = "CALL " + prefix + "PartUser(@nick@,@channel@)";
+ query.SetValue("nick", u->nick);
+ query.SetValue("channel", c->name);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnTopicUpdated(User *source, Channel *c, const Anope::string &user, const Anope::string &topic)
+{
+ query = "UPDATE `" + prefix + "chan` "
+ "SET topic=@topic@, topicauthor=@author@, topictime=FROM_UNIXTIME(@time@) "
+ "WHERE channel=@channel@";
+ query.SetValue("topic", c->topic);
+ query.SetValue("author", c->topic_setter);
+ query.SetValue("time", c->topic_ts);
+ query.SetValue("channel", c->name);
+ this->RunQuery(query);
+}
+
+void IRC2SQL::OnBotNotice(User *u, BotInfo *bi, Anope::string &message, const Anope::map<Anope::string> &tags)
+{
+ if (bi != StatServ)
+ return;
+
+ Anope::string ctcpname, ctcpbody;
+ if (!Anope::ParseCTCP(message, ctcpname, ctcpbody) || ctcpname != "VERSION")
+ return;
+
+ if (u->HasExt("CTCPVERSION"))
+ return;
+
+ u->Extend<bool>("CTCPVERSION");
+ auto versionstr = Anope::NormalizeBuffer(message.substr(9, message.length() - 10));
+ if (versionstr.empty())
+ return;
+
+ query = "UPDATE `" + prefix + "user` "
+ "SET version=@version@ "
+ "WHERE nick=@nick@";
+ query.SetValue("version", versionstr);
+ query.SetValue("nick", u->nick);
+ this->RunQuery(query);
+}
+
+MODULE_INIT(IRC2SQL)
diff --git a/modules/irc2sql/irc2sql.h b/modules/irc2sql/irc2sql.h
new file mode 100644
index 000000000..00c6f033b
--- /dev/null
+++ b/modules/irc2sql/irc2sql.h
@@ -0,0 +1,88 @@
+/*
+ *
+ * (C) 2013-2024 Anope Team
+ * Contact us at team@anope.org
+ *
+ * Please read COPYING and README for further details.
+ */
+
+#pragma once
+
+#include "module.h"
+#include "modules/sql.h"
+
+class MySQLInterface final
+ : public SQL::Interface
+{
+public:
+ MySQLInterface(Module *o) : SQL::Interface(o) { }
+
+ void OnResult(const SQL::Result &r) override
+ {
+ }
+
+ void OnError(const SQL::Result &r) override
+ {
+ if (!r.GetQuery().query.empty())
+ Log(LOG_DEBUG) << "irc2sql: Error executing query " << r.finished_query << ": " << r.GetError();
+ else
+ Log(LOG_DEBUG) << "irc2sql: Error executing query: " << r.GetError();
+ }
+};
+
+class IRC2SQL final
+ : public Module
+{
+ ServiceReference<SQL::Provider> sql;
+ MySQLInterface sqlinterface;
+ SQL::Query query;
+ std::vector<Anope::string> TableList, ProcedureList, EventList;
+ Anope::string prefix, GeoIPDB;
+ bool quitting, introduced_myself, ctcpuser, ctcpeob, firstrun;
+ BotInfo *StatServ;
+ PrimitiveExtensibleItem<bool> versionreply;
+
+ void RunQuery(const SQL::Query &q);
+ void GetTables();
+
+ bool HasTable(const Anope::string &table);
+ bool HasProcedure(const Anope::string &table);
+ bool HasEvent(const Anope::string &table);
+
+ void CheckTables();
+
+public:
+ IRC2SQL(const Anope::string &modname, const Anope::string &creator) :
+ Module(modname, creator, EXTRA | VENDOR), sql("", ""), sqlinterface(this), versionreply(this, "CTCPVERSION")
+ {
+ firstrun = true;
+ quitting = false;
+ introduced_myself = false;
+ }
+
+ void OnShutdown() override;
+ void OnReload(Configuration::Conf *config) override;
+ void OnNewServer(Server *server) override;
+ void OnServerQuit(Server *server) override;
+ void OnUserConnect(User *u, bool &exempt) override;
+ void OnUserQuit(User *u, const Anope::string &msg) override;
+ void OnUserNickChange(User *u, const Anope::string &oldnick) override;
+ void OnUserAway(User *u, const Anope::string &message) override;
+ void OnFingerprint(User *u) override;
+ void OnUserModeSet(const MessageSource &setter, User *u, const Anope::string &mname) override;
+ void OnUserModeUnset(const MessageSource &setter, User *u, const Anope::string &mname) override;
+ void OnUserLogin(User *u) override;
+ void OnNickLogout(User *u) override;
+ void OnSetDisplayedHost(User *u) override;
+
+ void OnChannelCreate(Channel *c) override;
+ void OnChannelDelete(Channel *c) override;
+ void OnLeaveChannel(User *u, Channel *c) override;
+ void OnJoinChannel(User *u, Channel *c) override;
+ EventReturn OnChannelModeSet(Channel *c, MessageSource &setter, ChannelMode *mode, const Anope::string &param) override;
+ EventReturn OnChannelModeUnset(Channel *c, MessageSource &setter, ChannelMode *mode, const Anope::string &param) override;
+
+ void OnTopicUpdated(User *source, Channel *c, const Anope::string &user, const Anope::string &topic) override;
+
+ void OnBotNotice(User *u, BotInfo *bi, Anope::string &message, const Anope::map<Anope::string> &tags) override;
+};
diff --git a/modules/irc2sql/tables.cpp b/modules/irc2sql/tables.cpp
new file mode 100644
index 000000000..dc7f90088
--- /dev/null
+++ b/modules/irc2sql/tables.cpp
@@ -0,0 +1,343 @@
+/*
+ *
+ * (C) 2013-2024 Anope Team
+ * Contact us at team@anope.org
+ *
+ * Please read COPYING and README for further details.
+ */
+
+#include "irc2sql.h"
+
+void IRC2SQL::CheckTables()
+{
+ Anope::string geoquery("");
+
+ if (firstrun)
+ {
+ /*
+ * reset some tables to make sure they are really empty
+ */
+ this->sql->RunQuery("TRUNCATE TABLE " + prefix + "user");
+ this->sql->RunQuery("TRUNCATE TABLE " + prefix + "chan");
+ this->sql->RunQuery("TRUNCATE TABLE " + prefix + "ison");
+ this->sql->RunQuery("UPDATE `" + prefix + "server` SET currentusers=0, online='N'");
+ }
+
+ this->GetTables();
+
+ if (GeoIPDB.equals_ci("country"))
+ {
+ if (!this->HasTable(prefix + "geoip_country"))
+ {
+ query = "CREATE TABLE `" + prefix + "geoip_country` ("
+ "`start` INT UNSIGNED NOT NULL,"
+ "`end` INT UNSIGNED NOT NULL,"
+ "`countrycode` varchar(2),"
+ "`countryname` varchar(50),"
+ "PRIMARY KEY `end` (`end`),"
+ "KEY `start` (`start`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ }
+ else if (GeoIPDB.equals_ci("city"))
+ {
+ if (!this->HasTable(prefix + "geoip_city_blocks"))
+ {
+ query = "CREATE TABLE `" + prefix + "geoip_city_blocks` ("
+ "`start` INT UNSIGNED NOT NULL,"
+ "`end` INT UNSIGNED NOT NULL,"
+ "`locId` INT UNSIGNED NOT NULL,"
+ "PRIMARY KEY `end` (`end`),"
+ "KEY `start` (`start`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (!this->HasTable(prefix + "geoip_city_location"))
+ {
+ query = "CREATE TABLE `" + prefix + "geoip_city_location` ("
+ "`locId` INT UNSIGNED NOT NULL,"
+ "`country` CHAR(2) NOT NULL,"
+ "`region` CHAR(2) NOT NULL,"
+ "`city` VARCHAR(50),"
+ "`latitude` FLOAT,"
+ "`longitude` FLOAT,"
+ "`areaCode` INT,"
+ "PRIMARY KEY (`locId`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (!this->HasTable(prefix + "geoip_city_region"))
+ { query = "CREATE TABLE `" + prefix + "geoip_city_region` ("
+ "`country` CHAR(2) NOT NULL,"
+ "`region` CHAR(2) NOT NULL,"
+ "`regionname` VARCHAR(100) NOT NULL,"
+ "PRIMARY KEY (`country`,`region`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ }
+ if (!this->HasTable(prefix + "server"))
+ {
+ query = "CREATE TABLE `" + prefix + "server` ("
+ "`id` int UNSIGNED NOT NULL AUTO_INCREMENT,"
+ "`name` varchar(64) NOT NULL,"
+ "`hops` tinyint NOT NULL,"
+ "`comment` varchar(255) NOT NULL,"
+ "`link_time` datetime DEFAULT NULL,"
+ "`split_time` datetime DEFAULT NULL,"
+ "`version` varchar(127) DEFAULT NULL,"
+ "`currentusers` int DEFAULT 0,"
+ "`online` enum('Y','N') NOT NULL DEFAULT 'Y',"
+ "`ulined` enum('Y','N') NOT NULL DEFAULT 'N',"
+ "PRIMARY KEY (`id`),"
+ "UNIQUE KEY `name` (`name`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (!this->HasTable(prefix + "chan"))
+ {
+ query = "CREATE TABLE `" + prefix + "chan` ("
+ "`chanid` int UNSIGNED NOT NULL AUTO_INCREMENT,"
+ "`channel` varchar(255) NOT NULL,"
+ "`topic` varchar(512) DEFAULT NULL,"
+ "`topicauthor` varchar(255) DEFAULT NULL,"
+ "`topictime` datetime DEFAULT NULL,"
+ "`modes` varchar(512) DEFAULT NULL,"
+ "PRIMARY KEY (`chanid`),"
+ "UNIQUE KEY `channel`(`channel`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (!this->HasTable(prefix + "user"))
+ {
+ query = "CREATE TABLE `" + prefix + "user` ("
+ "`nickid` int UNSIGNED NOT NULL AUTO_INCREMENT,"
+ "`nick` varchar(255) NOT NULL DEFAULT '',"
+ "`host` varchar(255) NOT NULL DEFAULT '',"
+ "`vhost` varchar(255) NOT NULL DEFAULT '',"
+ "`chost` varchar(255) NOT NULL DEFAULT '',"
+ "`realname` varchar(255) NOT NULL DEFAULT '',"
+ "`ip` varchar(255) NOT NULL DEFAULT '',"
+ "`ident` varchar(32) NOT NULL DEFAULT '',"
+ "`vident` varchar(32) NOT NULL DEFAULT '',"
+ "`modes` varchar(255) NOT NULL DEFAULT '',"
+ "`account` varchar(255) NOT NULL DEFAULT '',"
+ "`secure` enum('Y','N') NOT NULL DEFAULT 'N',"
+ "`fingerprint` varchar(128) NOT NULL DEFAULT '',"
+ "`signon` datetime DEFAULT NULL,"
+ "`server` varchar(255) NOT NULL DEFAULT '',"
+ "`servid` int UNSIGNED NOT NULL DEFAULT '0',"
+ "`uuid` varchar(32) NOT NULL DEFAULT '',"
+ "`oper` enum('Y','N') NOT NULL DEFAULT 'N',"
+ "`away` enum('Y','N') NOT NULL DEFAULT 'N',"
+ "`awaymsg` varchar(255) NOT NULL DEFAULT '',"
+ "`version` varchar(255) NOT NULL DEFAULT '',"
+ "`geocode` varchar(16) NOT NULL DEFAULT '',"
+ "`geocountry` varchar(64) NOT NULL DEFAULT '',"
+ "`georegion` varchar(100) NOT NULL DEFAULT '',"
+ "`geocity` varchar(128) NOT NULL DEFAULT '',"
+ "`locId` INT UNSIGNED,"
+ "PRIMARY KEY (`nickid`),"
+ "UNIQUE KEY `nick` (`nick`),"
+ "KEY `servid` (`servid`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (!this->HasTable(prefix + "ison"))
+ {
+ query = "CREATE TABLE `" + prefix + "ison` ("
+ "`nickid` int unsigned NOT NULL default '0',"
+ "`chanid` int unsigned NOT NULL default '0',"
+ "`modes` varchar(255) NOT NULL default '',"
+ "PRIMARY KEY (`nickid`,`chanid`),"
+ "KEY `modes` (`modes`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (!this->HasTable(prefix + "maxusers"))
+ {
+ query = "CREATE TABLE `" + prefix + "maxusers` ("
+ "`name` VARCHAR(255) NOT NULL,"
+ "`maxusers` int NOT NULL,"
+ "`maxtime` DATETIME NOT NULL,"
+ "`lastused` DATETIME NOT NULL,"
+ "UNIQUE KEY `name` (`name`)"
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
+ this->RunQuery(query);
+ }
+ if (this->HasProcedure(prefix + "UserConnect"))
+ this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "UserConnect"));
+
+ if (GeoIPDB.equals_ci("country"))
+ geoquery = "UPDATE `" + prefix + "user` AS u "
+ "JOIN ( SELECT `countrycode`, `countryname` "
+ "FROM `" + prefix + "geoip_country` "
+ "WHERE INET_ATON(ip_) <= `end` "
+ "AND `start` <= INET_ATON(ip_) "
+ "ORDER BY `end` ASC LIMIT 1 ) as c "
+ "SET u.geocode = c.countrycode, u.geocountry = c.countryname "
+ "WHERE u.nick = nick_; ";
+ else if (GeoIPDB.equals_ci("city"))
+ geoquery = "UPDATE `" + prefix + "user` as u "
+ "JOIN ( SELECT * FROM `" + prefix + "geoip_city_location` "
+ "WHERE `locID` = ( SELECT `locID` "
+ "FROM `" + prefix + "geoip_city_blocks` "
+ "WHERE INET_ATON(ip_) <= `end` "
+ "AND `start` <= INET_ATON(ip_) "
+ "ORDER BY `end` ASC LIMIT 1 ) "
+ ") as l "
+ "SET u.geocode = l.country, "
+ "u.geocity = l.city, "
+ "u.locID = l.locID, "
+ "u.georegion = ( SELECT `regionname` "
+ "FROM `" + prefix + "geoip_city_region` "
+ "WHERE `country` = l.country "
+ "AND `region` = l.region )"
+ "WHERE u.nick = nick_;";
+
+ query = "CREATE PROCEDURE `" + prefix + "UserConnect`"
+ "(nick_ varchar(255), host_ varchar(255), vhost_ varchar(255), "
+ "chost_ varchar(255), realname_ varchar(255), ip_ varchar(255), "
+ "ident_ varchar(255), vident_ varchar(255), account_ varchar(255), "
+ "secure_ enum('Y','N'), fingerprint_ varchar(255), signon_ int, "
+ "server_ varchar(255), uuid_ varchar(32), modes_ varchar(255), "
+ "oper_ enum('Y','N')) "
+ "BEGIN "
+ "DECLARE cur int;"
+ "DECLARE max int;"
+ "INSERT INTO `" + prefix + "user` "
+ "(nick, host, vhost, chost, realname, ip, ident, vident, account, "
+ "secure, fingerprint, signon, server, uuid, modes, oper) "
+ "VALUES (nick_, host_, vhost_, chost_, realname_, ip_, ident_, vident_, "
+ "account_, secure_, fingerprint_, FROM_UNIXTIME(signon_), server_, "
+ "uuid_, modes_, oper_) "
+ "ON DUPLICATE KEY UPDATE host=VALUES(host), vhost=VALUES(vhost), "
+ "chost=VALUES(chost), realname=VALUES(realname), ip=VALUES(ip), "
+ "ident=VALUES(ident), vident=VALUES(vident), account=VALUES(account), "
+ "secure=VALUES(secure), fingerprint=VALUES(fingerprint), signon=VALUES(signon), "
+ "server=VALUES(server), uuid=VALUES(uuid), modes=VALUES(modes), "
+ "oper=VALUES(oper);"
+ "UPDATE `" + prefix + "user` as `u`, `" + prefix + "server` as `s`"
+ "SET u.servid = s.id, "
+ "s.currentusers = s.currentusers + 1 "
+ "WHERE s.name = server_ AND u.nick = nick_;"
+ "SELECT `currentusers` INTO cur FROM `" + prefix + "server` WHERE name=server_;"
+ "SELECT `maxusers` INTO max FROM `" + prefix + "maxusers` WHERE name=server_;"
+ "IF found_rows() AND cur <= max THEN "
+ "UPDATE `" + prefix + "maxusers` SET lastused=now() WHERE name=server_;"
+ "ELSE "
+ "INSERT INTO `" + prefix + "maxusers` (name, maxusers, maxtime, lastused) "
+ "VALUES ( server_, cur, now(), now() ) "
+ "ON DUPLICATE KEY UPDATE "
+ "name=VALUES(name), maxusers=VALUES(maxusers),"
+ "maxtime=VALUES(maxtime), lastused=VALUES(lastused);"
+ "END IF;"
+ + geoquery +
+ "END";
+ this->RunQuery(query);
+
+ if (this->HasProcedure(prefix + "ServerQuit"))
+ this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "ServerQuit"));
+ query = "CREATE PROCEDURE " + prefix + "ServerQuit(sname_ varchar(255)) "
+ "BEGIN "
+ /* 1.
+ * remove all users on the splitting server from the ison table
+ */
+ "DELETE i FROM `" + prefix + "ison` AS i "
+ "INNER JOIN `" + prefix + "server` AS s "
+ "INNER JOIN `" + prefix + "user` AS u "
+ "WHERE i.nickid = u.nickid "
+ "AND u.servid = s.id "
+ "AND s.name = sname_;"
+
+ /* 2.
+ * remove all users on the splitting server from the user table
+ */
+ "DELETE u FROM `" + prefix + "user` AS u "
+ "INNER JOIN `" + prefix + "server` AS s "
+ "WHERE s.id = u.servid "
+ "AND s.name = sname_;"
+
+ /* 3.
+ * on the splitting server, set usercount = 0, split_time = now(), online = 'N'
+ */
+ "UPDATE `" + prefix + "server` SET currentusers = 0, split_time = now(), online = 'N' "
+ "WHERE name = sname_;"
+ "END;"; // end of the procedure
+ this->RunQuery(query);
+
+
+ if (this->HasProcedure(prefix + "UserQuit"))
+ this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "UserQuit"));
+ query = "CREATE PROCEDURE `" + prefix + "UserQuit`"
+ "(nick_ varchar(255)) "
+ "BEGIN "
+ /* decrease usercount on the server where the user was on */
+ "UPDATE `" + prefix + "user` AS `u`, `" + prefix + "server` AS `s` "
+ "SET s.currentusers = s.currentusers - 1 "
+ "WHERE u.nick=nick_ AND u.servid = s.id; "
+ /* remove from all channels where the user was on */
+ "DELETE i FROM `" + prefix + "ison` AS i "
+ "INNER JOIN `" + prefix + "user` as u "
+ "WHERE u.nick = nick_ "
+ "AND i.nickid = u.nickid;"
+ /* remove the user from the user table */
+ "DELETE FROM `" + prefix + "user` WHERE nick = nick_; "
+ "END";
+ this->RunQuery(query);
+
+ if (this->HasProcedure(prefix + "ShutDown"))
+ this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "ShutDown"));
+ query = "CREATE PROCEDURE `" + prefix + "ShutDown`()"
+ "BEGIN "
+ "UPDATE `" + prefix + "server` "
+ "SET currentusers=0, online='N', split_time=now();"
+ "TRUNCATE TABLE `" + prefix + "user`;"
+ "TRUNCATE TABLE `" + prefix + "chan`;"
+ "TRUNCATE TABLE `" + prefix + "ison`;"
+ "END";
+ this->RunQuery(query);
+
+ if (this->HasProcedure(prefix + "JoinUser"))
+ this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "JoinUser"));
+ query = "CREATE PROCEDURE `"+ prefix + "JoinUser`"
+ "(nick_ varchar(255), channel_ varchar(255), modes_ varchar(255)) "
+ "BEGIN "
+ "DECLARE cur int;"
+ "DECLARE max int;"
+ "INSERT INTO `" + prefix + "ison` (nickid, chanid, modes) "
+ "SELECT u.nickid, c.chanid, modes_ "
+ "FROM " + prefix + "user AS u, " + prefix + "chan AS c "
+ "WHERE u.nick=nick_ AND c.channel=channel_;"
+ "SELECT count(i.chanid) INTO cur "
+ "FROM `" + prefix + "chan` AS c, " + prefix + "ison AS i "
+ "WHERE i.chanid = c.chanid AND c.channel=channel_;"
+ "SELECT `maxusers` INTO max FROM `" + prefix + "maxusers` WHERE name=channel_;"
+ "IF found_rows() AND cur <= max THEN "
+ "UPDATE `" + prefix + "maxusers` SET lastused=now() WHERE name=channel_;"
+ "ELSE "
+ "INSERT INTO `" + prefix + "maxusers` (name, maxusers, maxtime, lastused) "
+ "VALUES ( channel_, cur, now(), now() ) "
+ "ON DUPLICATE KEY UPDATE "
+ "name=VALUES(name), maxusers=VALUES(maxusers),"
+ "maxtime=VALUES(maxtime), lastused=VALUES(lastused);"
+ "END IF;"
+ "END";
+ this->RunQuery(query);
+
+ if (this->HasProcedure(prefix + "PartUser"))
+ this->RunQuery(SQL::Query("DROP PROCEDURE " + prefix + "PartUser"));
+ query = "CREATE PROCEDURE `" + prefix + "PartUser`"
+ "(nick_ varchar(255), channel_ varchar(255)) "
+ "BEGIN "
+ "DELETE i FROM `" + prefix + "ison` AS i "
+ "INNER JOIN `" + prefix + "user` AS u "
+ "INNER JOIN `" + prefix + "chan` AS c "
+ "WHERE i.nickid = u.nickid "
+ "AND u.nick = nick_ "
+ "AND i.chanid = c.chanid "
+ "AND c.channel = channel_;"
+ "END";
+ this->RunQuery(query);
+}
diff --git a/modules/irc2sql/utils.cpp b/modules/irc2sql/utils.cpp
new file mode 100644
index 000000000..9c260fcaa
--- /dev/null
+++ b/modules/irc2sql/utils.cpp
@@ -0,0 +1,68 @@
+/*
+ *
+ * (C) 2013-2024 Anope Team
+ * Contact us at team@anope.org
+ *
+ * Please read COPYING and README for further details.
+ */
+
+#include "irc2sql.h"
+
+void IRC2SQL::RunQuery(const SQL::Query &q)
+{
+ if (sql)
+ sql->Run(&sqlinterface, q);
+}
+
+void IRC2SQL::GetTables()
+{
+ TableList.clear();
+ ProcedureList.clear();
+ EventList.clear();
+ if (!sql)
+ return;
+
+ SQL::Result r = this->sql->RunQuery(this->sql->GetTables(prefix));
+ for (int i = 0; i < r.Rows(); ++i)
+ {
+ const std::map<Anope::string, Anope::string> &map = r.Row(i);
+ for (std::map<Anope::string, Anope::string>::const_iterator it = map.begin(); it != map.end(); ++it)
+ TableList.push_back(it->second);
+ }
+ query = "SHOW PROCEDURE STATUS WHERE `Db` = Database();";
+ r = this->sql->RunQuery(query);
+ for (int i = 0; i < r.Rows(); ++i)
+ {
+ ProcedureList.push_back(r.Get(i, "Name"));
+ }
+ query = "SHOW EVENTS WHERE `Db` = Database();";
+ r = this->sql->RunQuery(query);
+ for (int i = 0; i < r.Rows(); ++i)
+ {
+ EventList.push_back(r.Get(i, "Name"));
+ }
+}
+
+bool IRC2SQL::HasTable(const Anope::string &table)
+{
+ for (std::vector<Anope::string>::const_iterator it = TableList.begin(); it != TableList.end(); ++it)
+ if (*it == table)
+ return true;
+ return false;
+}
+
+bool IRC2SQL::HasProcedure(const Anope::string &table)
+{
+ for (std::vector<Anope::string>::const_iterator it = ProcedureList.begin(); it != ProcedureList.end(); ++it)
+ if (*it == table)
+ return true;
+ return false;
+}
+
+bool IRC2SQL::HasEvent(const Anope::string &table)
+{
+ for (std::vector<Anope::string>::const_iterator it = EventList.begin(); it != EventList.end(); ++it)
+ if (*it == table)
+ return true;
+ return false;
+}
generated by cgit (git 2.34.1) at 2025-06-25 09:51:13 +0000