src/modules/extra/m_mysql.cpp

   1 /*
   2  * InspIRCd -- Internet Relay Chat Daemon
   3  *
   4  *   Copyright (C) 2009-2010 Daniel De Graaf <danieldg@inspircd.org>
   5  *   Copyright (C) 2006-2007, 2009 Dennis Friis <peavey@inspircd.org>
   6  *   Copyright (C) 2006-2009 Craig Edwards <craigedwards@brainbox.cc>
   7  *   Copyright (C) 2008 Robin Burchell <robin+git@viroteck.net>
   8  *
   9  * This file is part of InspIRCd.  InspIRCd is free software: you can
  10  * redistribute it and/or modify it under the terms of the GNU General Public
  11  * License as published by the Free Software Foundation, version 2.
  12  *
  13  * This program is distributed in the hope that it will be useful, but WITHOUT
  14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  15  * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
  16  * details.
  17  *
  18  * You should have received a copy of the GNU General Public License
  19  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20  */
  21
  22 /// $CompilerFlags: execute("mysql_config --include" "MYSQL_CXXFLAGS")
  23 /// $LinkerFlags: execute("mysql_config --libs_r" "MYSQL_LDFLAGS" "-lmysqlclient")
  24
  25 /// $PackageInfo: require_system("arch") mariadb-libs
  26 /// $PackageInfo: require_system("centos" "6.0" "6.99") mysql-devel
  27 /// $PackageInfo: require_system("centos" "7.0") mariadb-devel
  28 /// $PackageInfo: require_system("darwin") mysql-connector-c
  29 /// $PackageInfo: require_system("debian") libmysqlclient-dev
  30 /// $PackageInfo: require_system("ubuntu") libmysqlclient-dev
  31
  32 #ifdef __GNUC__
  33 # pragma GCC diagnostic push
  34 #endif
  35
  36 // Fix warnings about the use of `long long` on C++03.
  37 #if defined __clang__
  38 # pragma clang diagnostic ignored "-Wc++11-long-long"
  39 #elif defined __GNUC__
  40 # pragma GCC diagnostic ignored "-Wlong-long"
  41 #endif
  42
  43 #include "inspircd.h"
  44 #include <mysql.h>
  45 #include "modules/sql.h"
  46
  47 #ifdef __GNUC__
  48 # pragma GCC diagnostic pop
  49 #endif
  50
  51 #ifdef _WIN32
  52 # pragma comment(lib, "libmysql.lib")
  53 #endif
  54
  55 /* VERSION 3 API: With nonblocking (threaded) requests */
  56
  57 /* THE NONBLOCKING MYSQL API!
  58  *
  59  * MySQL provides no nonblocking (asyncronous) API of its own, and its developers recommend
  60  * that instead, you should thread your program. This is what i've done here to allow for
  61  * asyncronous SQL requests via mysql. The way this works is as follows:
  62  *
  63  * The module spawns a thread via class Thread, and performs its mysql queries in this thread,
  64  * using a queue with priorities. There is a mutex on either end which prevents two threads
  65  * adjusting the queue at the same time, and crashing the ircd. Every 50 milliseconds, the
  66  * worker thread wakes up, and checks if there is a request at the head of its queue.
  67  * If there is, it processes this request, blocking the worker thread but leaving the ircd
  68  * thread to go about its business as usual. During this period, the ircd thread is able
  69  * to insert futher pending requests into the queue.
  70  *
  71  * Once the processing of a request is complete, it is removed from the incoming queue to
  72  * an outgoing queue, and initialized as a 'response'. The worker thread then signals the
  73  * ircd thread (via a loopback socket) of the fact a result is available, by sending the
  74  * connection ID through the connection.
  75  *
  76  * The ircd thread then mutexes the queue once more, reads the outbound response off the head
  77  * of the queue, and sends it on its way to the original calling module.
  78  *
  79  * XXX: You might be asking "why doesnt it just send the response from within the worker thread?"
  80  * The answer to this is simple. The majority of InspIRCd, and in fact most ircd's are not
  81  * threadsafe. This module is designed to be threadsafe and is careful with its use of threads,
  82  * however, if we were to call a module's OnRequest even from within a thread which was not the
  83  * one the module was originally instantiated upon, there is a chance of all hell breaking loose
  84  * if a module is ever put in a re-enterant state (stack corruption could occur, crashes, data
  85  * corruption, and worse, so DONT think about it until the day comes when InspIRCd is 100%
  86  * gauranteed threadsafe!)
  87  */
  88
  89 class SQLConnection;
  90 class MySQLresult;
  91 class DispatcherThread;
  92
  93 struct QQueueItem
  94 {
  95         SQL::Query* q;
  96         std::string query;
  97         SQLConnection* c;
  98         QQueueItem(SQL::Query* Q, const std::string& S, SQLConnection* C) : q(Q), query(S), c(C) {}
  99 };
 100
 101 struct RQueueItem
 102 {
 103         SQL::Query* q;
 104         MySQLresult* r;
 105         RQueueItem(SQL::Query* Q, MySQLresult* R) : q(Q), r(R) {}
 106 };
 107
 108 typedef insp::flat_map<std::string, SQLConnection*> ConnMap;
 109 typedef std::deque<QQueueItem> QueryQueue;
 110 typedef std::deque<RQueueItem> ResultQueue;
 111
 112 /** MySQL module
 113  *  */
 114 class ModuleSQL : public Module
 115 {
 116  public:
 117         DispatcherThread* Dispatcher;
 118         QueryQueue qq;       // MUST HOLD MUTEX
 119         ResultQueue rq;      // MUST HOLD MUTEX
 120         ConnMap connections; // main thread only
 121
 122         ModuleSQL();
 123         void init() CXX11_OVERRIDE;
 124         ~ModuleSQL();
 125         void ReadConfig(ConfigStatus& status) CXX11_OVERRIDE;
 126         void OnUnloadModule(Module* mod) CXX11_OVERRIDE;
 127         Version GetVersion() CXX11_OVERRIDE;
 128 };
 129
 130 class DispatcherThread : public SocketThread
 131 {
 132  private:
 133         ModuleSQL* const Parent;
 134  public:
 135         DispatcherThread(ModuleSQL* CreatorModule) : Parent(CreatorModule) { }
 136         ~DispatcherThread() { }
 137         void Run() CXX11_OVERRIDE;
 138         void OnNotify() CXX11_OVERRIDE;
 139 };
 140
 141 #if !defined(MYSQL_VERSION_ID) || MYSQL_VERSION_ID<32224
 142 #define mysql_field_count mysql_num_fields
 143 #endif
 144
 145 /** Represents a mysql result set
 146  */
 147 class MySQLresult : public SQL::Result
 148 {
 149  public:
 150         SQL::Error err;
 151         int currentrow;
 152         int rows;
 153         std::vector<std::string> colnames;
 154         std::vector<SQL::Row> fieldlists;
 155
 156         MySQLresult(MYSQL_RES* res, int affected_rows) : err(SQL::SUCCESS), currentrow(0), rows(0)
 157         {
 158                 if (affected_rows >= 1)
 159                 {
 160                         rows = affected_rows;
 161                         fieldlists.resize(rows);
 162                 }
 163                 unsigned int field_count = 0;
 164                 if (res)
 165                 {
 166                         MYSQL_ROW row;
 167                         int n = 0;
 168                         while ((row = mysql_fetch_row(res)))
 169                         {
 170                                 if (fieldlists.size() < (unsigned int)rows+1)
 171                                 {
 172                                         fieldlists.resize(fieldlists.size()+1);
 173                                 }
 174                                 field_count = 0;
 175                                 MYSQL_FIELD *fields = mysql_fetch_fields(res);
 176                                 if(mysql_num_fields(res) == 0)
 177                                         break;
 178                                 if (fields && mysql_num_fields(res))
 179                                 {
 180                                         colnames.clear();
 181                                         while (field_count < mysql_num_fields(res))
 182                                         {
 183                                                 std::string a = (fields[field_count].name ? fields[field_count].name : "");
 184                                                 if (row[field_count])
 185                                                         fieldlists[n].push_back(SQL::Field(row[field_count]));
 186                                                 else
 187                                                         fieldlists[n].push_back(SQL::Field());
 188                                                 colnames.push_back(a);
 189                                                 field_count++;
 190                                         }
 191                                         n++;
 192                                 }
 193                                 rows++;
 194                         }
 195                         mysql_free_result(res);
 196                 }
 197         }
 198
 199         MySQLresult(SQL::Error& e) : err(e)
 200         {
 201
 202         }
 203
 204         int Rows() CXX11_OVERRIDE
 205         {
 206                 return rows;
 207         }
 208
 209         void GetCols(std::vector<std::string>& result) CXX11_OVERRIDE
 210         {
 211                 result.assign(colnames.begin(), colnames.end());
 212         }
 213
 214         bool HasColumn(const std::string& column, size_t& index) CXX11_OVERRIDE
 215         {
 216                 for (size_t i = 0; i < colnames.size(); ++i)
 217                 {
 218                         if (colnames[i] == column)
 219                         {
 220                                 index = i;
 221                                 return true;
 222                         }
 223                 }
 224                 return false;
 225         }
 226
 227         SQL::Field GetValue(int row, int column)
 228         {
 229                 if ((row >= 0) && (row < rows) && (column >= 0) && (column < (int)fieldlists[row].size()))
 230                 {
 231                         return fieldlists[row][column];
 232                 }
 233                 return SQL::Field();
 234         }
 235
 236         bool GetRow(SQL::Row& result) CXX11_OVERRIDE
 237         {
 238                 if (currentrow < rows)
 239                 {
 240                         result.assign(fieldlists[currentrow].begin(), fieldlists[currentrow].end());
 241                         currentrow++;
 242                         return true;
 243                 }
 244                 else
 245                 {
 246                         result.clear();
 247                         return false;
 248                 }
 249         }
 250 };
 251
 252 /** Represents a connection to a mysql database
 253  */
 254 class SQLConnection : public SQL::Provider
 255 {
 256  public:
 257         reference<ConfigTag> config;
 258         MYSQL *connection;
 259         Mutex lock;
 260
 261         // This constructor creates an SQLConnection object with the given credentials, but does not connect yet.
 262         SQLConnection(Module* p, ConfigTag* tag) : SQL::Provider(p, "SQL/" + tag->getString("id")),
 263                 config(tag), connection(NULL)
 264         {
 265         }
 266
 267         ~SQLConnection()
 268         {
 269                 Close();
 270         }
 271
 272         // This method connects to the database using the credentials supplied to the constructor, and returns
 273         // true upon success.
 274         bool Connect()
 275         {
 276                 unsigned int timeout = 1;
 277                 connection = mysql_init(connection);
 278                 mysql_options(connection,MYSQL_OPT_CONNECT_TIMEOUT,(char*)&timeout);
 279                 std::string host = config->getString("host");
 280                 std::string user = config->getString("user");
 281                 std::string pass = config->getString("pass");
 282                 std::string dbname = config->getString("name");
 283                 unsigned int port = config->getUInt("port", 3306);
 284                 bool rv = mysql_real_connect(connection, host.c_str(), user.c_str(), pass.c_str(), dbname.c_str(), port, NULL, 0);
 285                 if (!rv)
 286                         return rv;
 287
 288                 // Enable character set settings
 289                 std::string charset = config->getString("charset");
 290                 if ((!charset.empty()) && (mysql_set_character_set(connection, charset.c_str())))
 291                         ServerInstance->Logs->Log(MODNAME, LOG_DEFAULT, "WARNING: Could not set character set to \"%s\"", charset.c_str());
 292
 293                 std::string initquery;
 294                 if (config->readString("initialquery", initquery))
 295                 {
 296                         mysql_query(connection,initquery.c_str());
 297                 }
 298                 return true;
 299         }
 300
 301         ModuleSQL* Parent()
 302         {
 303                 return (ModuleSQL*)(Module*)creator;
 304         }
 305
 306         MySQLresult* DoBlockingQuery(const std::string& query)
 307         {
 308
 309                 /* Parse the command string and dispatch it to mysql */
 310                 if (CheckConnection() && !mysql_real_query(connection, query.data(), query.length()))
 311                 {
 312                         /* Successfull query */
 313                         MYSQL_RES* res = mysql_use_result(connection);
 314                         unsigned long rows = mysql_affected_rows(connection);
 315                         return new MySQLresult(res, rows);
 316                 }
 317                 else
 318                 {
 319                         /* XXX: See /usr/include/mysql/mysqld_error.h for a list of
 320                          * possible error numbers and error messages */
 321                         SQL::Error e(SQL::QREPLY_FAIL, InspIRCd::Format("%u: %s", mysql_errno(connection), mysql_error(connection)));
 322                         return new MySQLresult(e);
 323                 }
 324         }
 325
 326         bool CheckConnection()
 327         {
 328                 if (!connection || mysql_ping(connection) != 0)
 329                         return Connect();
 330                 return true;
 331         }
 332
 333         std::string GetError()
 334         {
 335                 return mysql_error(connection);
 336         }
 337
 338         void Close()
 339         {
 340                 mysql_close(connection);
 341         }
 342
 343         void Submit(SQL::Query* q, const std::string& qs) CXX11_OVERRIDE
 344         {
 345                 ServerInstance->Logs->Log(MODNAME, LOG_DEBUG, "Executing MySQL query: " + qs);
 346                 Parent()->Dispatcher->LockQueue();
 347                 Parent()->qq.push_back(QQueueItem(q, qs, this));
 348                 Parent()->Dispatcher->UnlockQueueWakeup();
 349         }
 350
 351         void Submit(SQL::Query* call, const std::string& q, const SQL::ParamList& p) CXX11_OVERRIDE
 352         {
 353                 std::string res;
 354                 unsigned int param = 0;
 355                 for(std::string::size_type i = 0; i < q.length(); i++)
 356                 {
 357                         if (q[i] != '?')
 358                                 res.push_back(q[i]);
 359                         else
 360                         {
 361                                 if (param < p.size())
 362                                 {
 363                                         std::string parm = p[param++];
 364                                         // In the worst case, each character may need to be encoded as using two bytes,
 365                                         // and one byte is the terminating null
 366                                         std::vector<char> buffer(parm.length() * 2 + 1);
 367
 368                                         // The return value of mysql_real_escape_string() is the length of the encoded string,
 369                                         // not including the terminating null
 370                                         unsigned long escapedsize = mysql_real_escape_string(connection, &buffer[0], parm.c_str(), parm.length());
 371                                         res.append(&buffer[0], escapedsize);
 372                                 }
 373                         }
 374                 }
 375                 Submit(call, res);
 376         }
 377
 378         void Submit(SQL::Query* call, const std::string& q, const SQL::ParamMap& p) CXX11_OVERRIDE
 379         {
 380                 std::string res;
 381                 for(std::string::size_type i = 0; i < q.length(); i++)
 382                 {
 383                         if (q[i] != '$')
 384                                 res.push_back(q[i]);
 385                         else
 386                         {
 387                                 std::string field;
 388                                 i++;
 389                                 while (i < q.length() && isalnum(q[i]))
 390                                         field.push_back(q[i++]);
 391                                 i--;
 392
 393                                 SQL::ParamMap::const_iterator it = p.find(field);
 394                                 if (it != p.end())
 395                                 {
 396                                         std::string parm = it->second;
 397                                         // NOTE: See above
 398                                         std::vector<char> buffer(parm.length() * 2 + 1);
 399                                         unsigned long escapedsize = mysql_escape_string(&buffer[0], parm.c_str(), parm.length());
 400                                         res.append(&buffer[0], escapedsize);
 401                                 }
 402                         }
 403                 }
 404                 Submit(call, res);
 405         }
 406 };
 407
 408 ModuleSQL::ModuleSQL()
 409 {
 410         Dispatcher = NULL;
 411 }
 412
 413 void ModuleSQL::init()
 414 {
 415         if (mysql_library_init(0, NULL, NULL))
 416                 throw ModuleException("Unable to initialise the MySQL library!");
 417
 418         Dispatcher = new DispatcherThread(this);
 419         ServerInstance->Threads.Start(Dispatcher);
 420 }
 421
 422 ModuleSQL::~ModuleSQL()
 423 {
 424         if (Dispatcher)
 425         {
 426                 Dispatcher->join();
 427                 Dispatcher->OnNotify();
 428                 delete Dispatcher;
 429         }
 430
 431         for(ConnMap::iterator i = connections.begin(); i != connections.end(); i++)
 432         {
 433                 delete i->second;
 434         }
 435
 436         mysql_library_end();
 437 }
 438
 439 void ModuleSQL::ReadConfig(ConfigStatus& status)
 440 {
 441         ConnMap conns;
 442         ConfigTagList tags = ServerInstance->Config->ConfTags("database");
 443         for(ConfigIter i = tags.first; i != tags.second; i++)
 444         {
 445                 if (!stdalgo::string::equalsci(i->second->getString("module"), "mysql"))
 446                         continue;
 447                 std::string id = i->second->getString("id");
 448                 ConnMap::iterator curr = connections.find(id);
 449                 if (curr == connections.end())
 450                 {
 451                         SQLConnection* conn = new SQLConnection(this, i->second);
 452                         conns.insert(std::make_pair(id, conn));
 453                         ServerInstance->Modules->AddService(*conn);
 454                 }
 455                 else
 456                 {
 457                         conns.insert(*curr);
 458                         connections.erase(curr);
 459                 }
 460         }
 461
 462         // now clean up the deleted databases
 463         Dispatcher->LockQueue();
 464         SQL::Error err(SQL::BAD_DBID);
 465         for(ConnMap::iterator i = connections.begin(); i != connections.end(); i++)
 466         {
 467                 ServerInstance->Modules->DelService(*i->second);
 468                 // it might be running a query on this database. Wait for that to complete
 469                 i->second->lock.Lock();
 470                 i->second->lock.Unlock();
 471                 // now remove all active queries to this DB
 472                 for (size_t j = qq.size(); j > 0; j--)
 473                 {
 474                         size_t k = j - 1;
 475                         if (qq[k].c == i->second)
 476                         {
 477                                 qq[k].q->OnError(err);
 478                                 delete qq[k].q;
 479                                 qq.erase(qq.begin() + k);
 480                         }
 481                 }
 482                 // finally, nuke the connection
 483                 delete i->second;
 484         }
 485         Dispatcher->UnlockQueue();
 486         connections.swap(conns);
 487 }
 488
 489 void ModuleSQL::OnUnloadModule(Module* mod)
 490 {
 491         SQL::Error err(SQL::BAD_DBID);
 492         Dispatcher->LockQueue();
 493         unsigned int i = qq.size();
 494         while (i > 0)
 495         {
 496                 i--;
 497                 if (qq[i].q->creator == mod)
 498                 {
 499                         if (i == 0)
 500                         {
 501                                 // need to wait until the query is done
 502                                 // (the result will be discarded)
 503                                 qq[i].c->lock.Lock();
 504                                 qq[i].c->lock.Unlock();
 505                         }
 506                         qq[i].q->OnError(err);
 507                         delete qq[i].q;
 508                         qq.erase(qq.begin() + i);
 509                 }
 510         }
 511         Dispatcher->UnlockQueue();
 512         // clean up any result queue entries
 513         Dispatcher->OnNotify();
 514 }
 515
 516 Version ModuleSQL::GetVersion()
 517 {
 518         return Version("Provides MySQL support", VF_VENDOR);
 519 }
 520
 521 void DispatcherThread::Run()
 522 {
 523         this->LockQueue();
 524         while (!this->GetExitFlag())
 525         {
 526                 if (!Parent->qq.empty())
 527                 {
 528                         QQueueItem i = Parent->qq.front();
 529                         i.c->lock.Lock();
 530                         this->UnlockQueue();
 531                         MySQLresult* res = i.c->DoBlockingQuery(i.query);
 532                         i.c->lock.Unlock();
 533
 534                         /*
 535                          * At this point, the main thread could be working on:
 536                          *  Rehash - delete i.c out from under us. We don't care about that.
 537                          *  UnloadModule - delete i.q and the qq item. Need to avoid reporting results.
 538                          */
 539
 540                         this->LockQueue();
 541                         if (!Parent->qq.empty() && Parent->qq.front().q == i.q)
 542                         {
 543                                 Parent->qq.pop_front();
 544                                 Parent->rq.push_back(RQueueItem(i.q, res));
 545                                 NotifyParent();
 546                         }
 547                         else
 548                         {
 549                                 // UnloadModule ate the query
 550                                 delete res;
 551                         }
 552                 }
 553                 else
 554                 {
 555                         /* We know the queue is empty, we can safely hang this thread until
 556                          * something happens
 557                          */
 558                         this->WaitForQueue();
 559                 }
 560         }
 561         this->UnlockQueue();
 562 }
 563
 564 void DispatcherThread::OnNotify()
 565 {
 566         // this could unlock during the dispatch, but OnResult isn't expected to take that long
 567         this->LockQueue();
 568         for(ResultQueue::iterator i = Parent->rq.begin(); i != Parent->rq.end(); i++)
 569         {
 570                 MySQLresult* res = i->r;
 571                 if (res->err.code == SQL::SUCCESS)
 572                         i->q->OnResult(*res);
 573                 else
 574                         i->q->OnError(res->err);
 575                 delete i->q;
 576                 delete i->r;
 577         }
 578         Parent->rq.clear();
 579         this->UnlockQueue();
 580 }
 581
 582 MODULE_INIT(ModuleSQL)