src/modules/extra/m_sqlite3.cpp

   1 /*               +------------------------------------+
   2  *               | Inspire Internet Relay Chat Daemon |
   3  *               +------------------------------------+
   4  *
   5  *      InspIRCd: (C) 2002-2009 InspIRCd Development Team
   6  * See: http://wiki.inspircd.org/Credits
   7  *
   8  * This program is free but copyrighted software; see
   9  *                        the file COPYING for details.
  10  *
  11  * ---------------------------------------------------
  12  */
  13
  14 #include "inspircd.h"
  15 #include <sqlite3.h>
  16 #include "m_sqlv2.h"
  17
  18 /* $ModDesc: sqlite3 provider */
  19 /* $CompileFlags: pkgconfversion("sqlite3","3.3") pkgconfincludes("sqlite3","/sqlite3.h","") */
  20 /* $LinkerFlags: pkgconflibs("sqlite3","/libsqlite3.so","-lsqlite3") */
  21 /* $ModDep: m_sqlv2.h */
  22 /* $NoPedantic */
  23
  24 class SQLConn;
  25 class SQLite3Result;
  26 class ResultNotifier;
  27 class SQLiteListener;
  28 class ModuleSQLite3;
  29
  30 typedef std::map<std::string, SQLConn*> ConnMap;
  31 typedef std::deque<classbase*> paramlist;
  32 typedef std::deque<SQLite3Result*> ResultQueue;
  33
  34 unsigned long count(const char * const str, char a)
  35 {
  36         unsigned long n = 0;
  37         for (const char *p = str; *p; ++p)
  38         {
  39                 if (*p == '?')
  40                         ++n;
  41         }
  42         return n;
  43 }
  44
  45 ResultNotifier* notifier = NULL;
  46 SQLiteListener* listener = NULL;
  47 int QueueFD = -1;
  48
  49 class ResultNotifier : public BufferedSocket
  50 {
  51         ModuleSQLite3* mod;
  52
  53  public:
  54         ResultNotifier(ModuleSQLite3* m, int newfd) : BufferedSocket(newfd), mod(m)
  55         {
  56         }
  57
  58         void OnDataReady()
  59         {
  60                 recvq.clear();
  61                 Dispatch();
  62         }
  63
  64         void OnError(BufferedSocketError) {}
  65
  66         void Dispatch();
  67 };
  68
  69 class SQLiteListener : public ListenSocketBase
  70 {
  71         ModuleSQLite3* Parent;
  72         irc::sockets::sockaddrs sock_us;
  73         socklen_t uslen;
  74         FileReader* index;
  75
  76  public:
  77         SQLiteListener(ModuleSQLite3* P, int port, const std::string &addr) : ListenSocketBase(port, addr, "ITC", "none"), Parent(P)
  78         {
  79                 uslen = sizeof(sock_us);
  80                 if (getsockname(this->fd,(sockaddr*)&sock_us,&uslen))
  81                 {
  82                         throw ModuleException("Could not getsockname() to find out port number for ITC port");
  83                 }
  84         }
  85
  86         void OnAcceptReady(int nfd)
  87         {
  88                 new ResultNotifier(Parent, nfd);
  89         }
  90
  91         int GetPort()
  92         {
  93                 int port = 0;
  94                 std::string addr;
  95                 irc::sockets::satoap(&sock_us, addr, port);
  96                 return port;
  97         }
  98 };
  99
 100 class SQLite3Result : public SQLresult
 101 {
 102  private:
 103         int currentrow;
 104         int rows;
 105         int cols;
 106
 107         std::vector<std::string> colnames;
 108         std::vector<SQLfieldList> fieldlists;
 109         SQLfieldList emptyfieldlist;
 110
 111         SQLfieldList* fieldlist;
 112         SQLfieldMap* fieldmap;
 113
 114  public:
 115         SQLite3Result(Module* self, Module* to, unsigned int rid)
 116         : SQLresult(self, to, rid), currentrow(0), rows(0), cols(0), fieldlist(NULL), fieldmap(NULL)
 117         {
 118         }
 119
 120         ~SQLite3Result()
 121         {
 122         }
 123
 124         void AddRow(int colsnum, char **dat, char **colname)
 125         {
 126                 colnames.clear();
 127                 cols = colsnum;
 128                 for (int i = 0; i < colsnum; i++)
 129                 {
 130                         fieldlists.resize(fieldlists.size()+1);
 131                         colnames.push_back(colname[i]);
 132                         SQLfield sf(dat[i] ? dat[i] : "", dat[i] ? false : true);
 133                         fieldlists[rows].push_back(sf);
 134                 }
 135                 rows++;
 136         }
 137
 138         void UpdateAffectedCount()
 139         {
 140                 rows++;
 141         }
 142
 143         virtual int Rows()
 144         {
 145                 return rows;
 146         }
 147
 148         virtual int Cols()
 149         {
 150                 return cols;
 151         }
 152
 153         virtual std::string ColName(int column)
 154         {
 155                 if (column < (int)colnames.size())
 156                 {
 157                         return colnames[column];
 158                 }
 159                 else
 160                 {
 161                         throw SQLbadColName();
 162                 }
 163                 return "";
 164         }
 165
 166         virtual int ColNum(const std::string &column)
 167         {
 168                 for (unsigned int i = 0; i < colnames.size(); i++)
 169                 {
 170                         if (column == colnames[i])
 171                                 return i;
 172                 }
 173                 throw SQLbadColName();
 174                 return 0;
 175         }
 176
 177         virtual SQLfield GetValue(int row, int column)
 178         {
 179                 if ((row >= 0) && (row < rows) && (column >= 0) && (column < Cols()))
 180                 {
 181                         return fieldlists[row][column];
 182                 }
 183
 184                 throw SQLbadColName();
 185
 186                 /* XXX: We never actually get here because of the throw */
 187                 return SQLfield("",true);
 188         }
 189
 190         virtual SQLfieldList& GetRow()
 191         {
 192                 if (currentrow < rows)
 193                         return fieldlists[currentrow];
 194                 else
 195                         return emptyfieldlist;
 196         }
 197
 198         virtual SQLfieldMap& GetRowMap()
 199         {
 200                 /* In an effort to reduce overhead we don't actually allocate the map
 201                  * until the first time it's needed...so...
 202                  */
 203                 if(fieldmap)
 204                 {
 205                         fieldmap->clear();
 206                 }
 207                 else
 208                 {
 209                         fieldmap = new SQLfieldMap;
 210                 }
 211
 212                 if (currentrow < rows)
 213                 {
 214                         for (int i = 0; i < Cols(); i++)
 215                         {
 216                                 fieldmap->insert(std::make_pair(ColName(i), GetValue(currentrow, i)));
 217                         }
 218                         currentrow++;
 219                 }
 220
 221                 return *fieldmap;
 222         }
 223
 224         virtual SQLfieldList* GetRowPtr()
 225         {
 226                 fieldlist = new SQLfieldList();
 227
 228                 if (currentrow < rows)
 229                 {
 230                         for (int i = 0; i < Rows(); i++)
 231                         {
 232                                 fieldlist->push_back(fieldlists[currentrow][i]);
 233                         }
 234                         currentrow++;
 235                 }
 236                 return fieldlist;
 237         }
 238
 239         virtual SQLfieldMap* GetRowMapPtr()
 240         {
 241                 fieldmap = new SQLfieldMap();
 242
 243                 if (currentrow < rows)
 244                 {
 245                         for (int i = 0; i < Cols(); i++)
 246                         {
 247                                 fieldmap->insert(std::make_pair(colnames[i],GetValue(currentrow, i)));
 248                         }
 249                         currentrow++;
 250                 }
 251
 252                 return fieldmap;
 253         }
 254
 255         virtual void Free(SQLfieldMap* fm)
 256         {
 257                 delete fm;
 258         }
 259
 260         virtual void Free(SQLfieldList* fl)
 261         {
 262                 delete fl;
 263         }
 264
 265
 266 };
 267
 268 class SQLConn : public classbase
 269 {
 270  private:
 271         ResultQueue results;
 272         Module* mod;
 273         SQLhost host;
 274         sqlite3* conn;
 275
 276  public:
 277         SQLConn(Module* m, const SQLhost& hi)
 278         : mod(m), host(hi)
 279         {
 280                 if (OpenDB() != SQLITE_OK)
 281                 {
 282                         ServerInstance->Logs->Log("m_sqlite3",DEFAULT, "WARNING: Could not open DB with id: " + host.id);
 283                         CloseDB();
 284                 }
 285         }
 286
 287         ~SQLConn()
 288         {
 289                 CloseDB();
 290         }
 291
 292         SQLerror Query(SQLrequest &req)
 293         {
 294                 /* Pointer to the buffer we screw around with substitution in */
 295                 char* query;
 296
 297                 /* Pointer to the current end of query, where we append new stuff */
 298                 char* queryend;
 299
 300                 /* Total length of the unescaped parameters */
 301                 unsigned long maxparamlen, paramcount;
 302
 303                 /* The length of the longest parameter */
 304                 maxparamlen = 0;
 305
 306                 for(ParamL::iterator i = req.query.p.begin(); i != req.query.p.end(); i++)
 307                 {
 308                         if (i->size() > maxparamlen)
 309                                 maxparamlen = i->size();
 310                 }
 311
 312                 /* How many params are there in the query? */
 313                 paramcount = count(req.query.q.c_str(), '?');
 314
 315                 /* This stores copy of params to be inserted with using numbered params 1;3B*/
 316                 ParamL paramscopy(req.query.p);
 317
 318                 /* To avoid a lot of allocations, allocate enough memory for the biggest the escaped query could possibly be.
 319                  * sizeofquery + (maxtotalparamlength*2) + 1
 320                  *
 321                  * The +1 is for null-terminating the string
 322                  */
 323
 324                 query = new char[req.query.q.length() + (maxparamlen*paramcount*2) + 1];
 325                 queryend = query;
 326
 327                 for(unsigned long i = 0; i < req.query.q.length(); i++)
 328                 {
 329                         if(req.query.q[i] == '?')
 330                         {
 331                                 /* We found a place to substitute..what fun.
 332                                  * use sqlite calls to escape and write the
 333                                  * escaped string onto the end of our query buffer,
 334                                  * then we "just" need to make sure queryend is
 335                                  * pointing at the right place.
 336                                  */
 337
 338                                 /* Is it numbered parameter?
 339                                  */
 340
 341                                 bool numbered;
 342                                 numbered = false;
 343
 344                                 /* Numbered parameter number :|
 345                                  */
 346                                 unsigned int paramnum;
 347                                 paramnum = 0;
 348
 349                                 /* Let's check if it's a numbered param. And also calculate it's number.
 350                                  */
 351
 352                                 while ((i < req.query.q.length() - 1) && (req.query.q[i+1] >= '0') && (req.query.q[i+1] <= '9'))
 353                                 {
 354                                         numbered = true;
 355                                         ++i;
 356                                         paramnum = paramnum * 10 + req.query.q[i] - '0';
 357                                 }
 358
 359                                 if (paramnum > paramscopy.size() - 1)
 360                                 {
 361                                         /* index is out of range!
 362                                          */
 363                                         numbered = false;
 364                                 }
 365
 366
 367                                 if (numbered)
 368                                 {
 369                                         char* escaped;
 370                                         escaped = sqlite3_mprintf("%q", paramscopy[paramnum].c_str());
 371                                         for (char* n = escaped; *n; n++)
 372                                         {
 373                                                 *queryend = *n;
 374                                                 queryend++;
 375                                         }
 376                                         sqlite3_free(escaped);
 377                                 }
 378                                 else if (req.query.p.size())
 379                                 {
 380                                         char* escaped;
 381                                         escaped = sqlite3_mprintf("%q", req.query.p.front().c_str());
 382                                         for (char* n = escaped; *n; n++)
 383                                         {
 384                                                 *queryend = *n;
 385                                                 queryend++;
 386                                         }
 387                                         sqlite3_free(escaped);
 388                                         req.query.p.pop_front();
 389                                 }
 390                                 else
 391                                         break;
 392                         }
 393                         else
 394                         {
 395                                 *queryend = req.query.q[i];
 396                                 queryend++;
 397                         }
 398                 }
 399                 *queryend = 0;
 400                 req.query.q = query;
 401
 402                 SQLite3Result* res = new SQLite3Result(mod, req.source, req.id);
 403                 res->dbid = host.id;
 404                 res->query = req.query.q;
 405                 paramlist params;
 406                 params.push_back(this);
 407                 params.push_back(res);
 408
 409                 char *errmsg = 0;
 410                 sqlite3_update_hook(conn, QueryUpdateHook, &params);
 411                 if (sqlite3_exec(conn, req.query.q.data(), QueryResult, &params, &errmsg) != SQLITE_OK)
 412                 {
 413                         std::string error(errmsg);
 414                         sqlite3_free(errmsg);
 415                         delete[] query;
 416                         delete res;
 417                         return SQLerror(SQL_QSEND_FAIL, error);
 418                 }
 419                 delete[] query;
 420
 421                 results.push_back(res);
 422                 SendNotify();
 423                 return SQLerror();
 424         }
 425
 426         static int QueryResult(void *params, int argc, char **argv, char **azColName)
 427         {
 428                 paramlist* p = (paramlist*)params;
 429                 ((SQLConn*)(*p)[0])->ResultReady(((SQLite3Result*)(*p)[1]), argc, argv, azColName);
 430                 return 0;
 431         }
 432
 433         static void QueryUpdateHook(void *params, int eventid, char const * azSQLite, char const * azColName, sqlite_int64 rowid)
 434         {
 435                 paramlist* p = (paramlist*)params;
 436                 ((SQLConn*)(*p)[0])->AffectedReady(((SQLite3Result*)(*p)[1]));
 437         }
 438
 439         void ResultReady(SQLite3Result *res, int cols, char **data, char **colnames)
 440         {
 441                 res->AddRow(cols, data, colnames);
 442         }
 443
 444         void AffectedReady(SQLite3Result *res)
 445         {
 446                 res->UpdateAffectedCount();
 447         }
 448
 449         int OpenDB()
 450         {
 451                 return sqlite3_open_v2(host.host.c_str(), &conn, SQLITE_OPEN_READWRITE, 0);
 452         }
 453
 454         void CloseDB()
 455         {
 456                 sqlite3_interrupt(conn);
 457                 sqlite3_close(conn);
 458         }
 459
 460         SQLhost GetConfHost()
 461         {
 462                 return host;
 463         }
 464
 465         void SendResults()
 466         {
 467                 while (results.size())
 468                 {
 469                         SQLite3Result* res = results[0];
 470                         if (res->dest)
 471                         {
 472                                 res->Send();
 473                         }
 474                         else
 475                         {
 476                                 /* If the client module is unloaded partway through a query then the provider will set
 477                                  * the pointer to NULL. We cannot just cancel the query as the result will still come
 478                                  * through at some point...and it could get messy if we play with invalid pointers...
 479                                  */
 480                                 delete res;
 481                         }
 482                         results.pop_front();
 483                 }
 484         }
 485
 486         void ClearResults()
 487         {
 488                 while (results.size())
 489                 {
 490                         SQLite3Result* res = results[0];
 491                         delete res;
 492                         results.pop_front();
 493                 }
 494         }
 495
 496         void SendNotify()
 497         {
 498                 if (QueueFD < 0)
 499                 {
 500                         if ((QueueFD = socket(AF_INET, SOCK_STREAM, 0)) == -1)
 501                         {
 502                                 /* crap, we're out of sockets... */
 503                                 return;
 504                         }
 505
 506                         irc::sockets::sockaddrs addr;
 507                         irc::sockets::aptosa("127.0.0.1", listener->GetPort(), &addr);
 508
 509                         if (connect(QueueFD, &addr.sa, sa_size(addr)) == -1)
 510                         {
 511                                 /* wtf, we cant connect to it, but we just created it! */
 512                                 return;
 513                         }
 514                 }
 515                 char id = 0;
 516                 send(QueueFD, &id, 1, 0);
 517         }
 518
 519 };
 520
 521
 522 class ModuleSQLite3 : public Module
 523 {
 524  private:
 525         ConnMap connections;
 526         unsigned long currid;
 527
 528  public:
 529         ModuleSQLite3()
 530         : currid(0)
 531         {
 532                 ServerInstance->Modules->UseInterface("SQLutils");
 533
 534                 if (!ServerInstance->Modules->PublishFeature("SQL", this))
 535                 {
 536                         throw ModuleException("m_sqlite3: Unable to publish feature 'SQL'");
 537                 }
 538
 539                 /* Create a socket on a random port. Let the tcp stack allocate us an available port */
 540                 listener = new SQLiteListener(this, 0, "127.0.0.1");
 541
 542                 if (listener->GetFd() == -1)
 543                 {
 544                         ServerInstance->Modules->DoneWithInterface("SQLutils");
 545                         throw ModuleException("m_sqlite3: unable to create ITC pipe");
 546                 }
 547                 else
 548                 {
 549                         ServerInstance->Logs->Log("m_sqlite3", DEBUG, "SQLite: Interthread comms port is %d", listener->GetPort());
 550                 }
 551
 552                 ReadConf();
 553
 554                 ServerInstance->Modules->PublishInterface("SQL", this);
 555                 Implementation eventlist[] = { I_OnRehash };
 556                 ServerInstance->Modules->Attach(eventlist, this, 1);
 557         }
 558
 559         virtual ~ModuleSQLite3()
 560         {
 561                 ClearQueue();
 562                 ClearAllConnections();
 563
 564                 ServerInstance->SE->DelFd(listener);
 565
 566                 if (QueueFD >= 0)
 567                 {
 568                         shutdown(QueueFD, 2);
 569                         close(QueueFD);
 570                 }
 571
 572                 if (notifier)
 573                 {
 574                         ServerInstance->SE->DelFd(notifier);
 575                         notifier->Close();
 576                 }
 577
 578                 ServerInstance->GlobalCulls.Apply();
 579
 580                 ServerInstance->Modules->UnpublishInterface("SQL", this);
 581                 ServerInstance->Modules->UnpublishFeature("SQL");
 582                 ServerInstance->Modules->DoneWithInterface("SQLutils");
 583         }
 584
 585
 586         void SendQueue()
 587         {
 588                 for (ConnMap::iterator iter = connections.begin(); iter != connections.end(); iter++)
 589                 {
 590                         iter->second->SendResults();
 591                 }
 592         }
 593
 594         void ClearQueue()
 595         {
 596                 for (ConnMap::iterator iter = connections.begin(); iter != connections.end(); iter++)
 597                 {
 598                         iter->second->ClearResults();
 599                 }
 600         }
 601
 602         bool HasHost(const SQLhost &host)
 603         {
 604                 for (ConnMap::iterator iter = connections.begin(); iter != connections.end(); iter++)
 605                 {
 606                         if (host == iter->second->GetConfHost())
 607                                 return true;
 608                 }
 609                 return false;
 610         }
 611
 612         bool HostInConf(const SQLhost &h)
 613         {
 614                 ConfigReader conf;
 615                 for(int i = 0; i < conf.Enumerate("database"); i++)
 616                 {
 617                         SQLhost host;
 618                         host.id         = conf.ReadValue("database", "id", i);
 619                         host.host       = conf.ReadValue("database", "hostname", i);
 620                         host.port       = conf.ReadInteger("database", "port", i, true);
 621                         host.name       = conf.ReadValue("database", "name", i);
 622                         host.user       = conf.ReadValue("database", "username", i);
 623                         host.pass       = conf.ReadValue("database", "password", i);
 624                         if (h == host)
 625                                 return true;
 626                 }
 627                 return false;
 628         }
 629
 630         void ReadConf()
 631         {
 632                 ClearOldConnections();
 633
 634                 ConfigReader conf;
 635                 for(int i = 0; i < conf.Enumerate("database"); i++)
 636                 {
 637                         SQLhost host;
 638
 639                         host.id         = conf.ReadValue("database", "id", i);
 640                         host.host       = conf.ReadValue("database", "hostname", i);
 641                         host.port       = conf.ReadInteger("database", "port", i, true);
 642                         host.name       = conf.ReadValue("database", "name", i);
 643                         host.user       = conf.ReadValue("database", "username", i);
 644                         host.pass       = conf.ReadValue("database", "password", i);
 645
 646                         if (HasHost(host))
 647                                 continue;
 648
 649                         this->AddConn(host);
 650                 }
 651         }
 652
 653         void AddConn(const SQLhost& hi)
 654         {
 655                 if (HasHost(hi))
 656                 {
 657                         ServerInstance->Logs->Log("m_sqlite3",DEFAULT, "WARNING: A sqlite connection with id: %s already exists. Aborting database open attempt.", hi.id.c_str());
 658                         return;
 659                 }
 660
 661                 SQLConn* newconn;
 662
 663                 newconn = new SQLConn(this, hi);
 664
 665                 connections.insert(std::make_pair(hi.id, newconn));
 666         }
 667
 668         void ClearOldConnections()
 669         {
 670                 ConnMap::iterator iter,safei;
 671                 for (iter = connections.begin(); iter != connections.end(); iter++)
 672                 {
 673                         if (!HostInConf(iter->second->GetConfHost()))
 674                         {
 675                                 delete iter->second;
 676                                 safei = iter;
 677                                 --iter;
 678                                 connections.erase(safei);
 679                         }
 680                 }
 681         }
 682
 683         void ClearAllConnections()
 684         {
 685                 ConnMap::iterator i;
 686                 while ((i = connections.begin()) != connections.end())
 687                 {
 688                         connections.erase(i);
 689                         delete i->second;
 690                 }
 691         }
 692
 693         virtual void OnRehash(User* user)
 694         {
 695                 ReadConf();
 696         }
 697
 698         void OnRequest(Request& request)
 699         {
 700                 if(strcmp(SQLREQID, request.id) == 0)
 701                 {
 702                         SQLrequest* req = (SQLrequest*)&request;
 703                         ConnMap::iterator iter;
 704                         if((iter = connections.find(req->dbid)) != connections.end())
 705                         {
 706                                 req->id = NewID();
 707                                 req->error = iter->second->Query(*req);
 708                         }
 709                         else
 710                         {
 711                                 req->error.Id(SQL_BAD_DBID);
 712                         }
 713                 }
 714         }
 715
 716         unsigned long NewID()
 717         {
 718                 if (currid+1 == 0)
 719                         currid++;
 720
 721                 return ++currid;
 722         }
 723
 724         virtual Version GetVersion()
 725         {
 726                 return Version("sqlite3 provider", VF_VENDOR | VF_SERVICEPROVIDER);
 727         }
 728
 729 };
 730
 731 void ResultNotifier::Dispatch()
 732 {
 733         mod->SendQueue();
 734 }
 735
 736 MODULE_INIT(ModuleSQLite3)