src/db.c

   1 /*  Thales - IRC to Relational Database Gateway
   2  *  Copyright (C) 2002 Lucas Nussbaum <lucas@lucas-nussbaum.net>
   3  *
   4  *  This program is free software; you can redistribute it and/or modify
   5  *  it under the terms of the GNU General Public License as published by
   6  *  the Free Software Foundation; either version 2 of the License, or
   7  *  (at your option) any later version.
   8  *
   9  *  This program is distributed in the hope that it will be useful,
  10  *  but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  *  GNU General Public License for more details.
  13  *
  14  *  You should have received a copy of the GNU General Public License
  15  *  along with this program; if not, write to the Free Software
  16  *  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  17  */
  18 /* Thales' database interface */
  19
  20 #include "db.h"
  21 #include "misc.h"
  22 #include "actions.h"
  23 #ifdef HASHLISTSUPPORT
  24 #include "hashlist.h"
  25 #endif
  26
  27 extern char *MysqlServer;
  28 extern char *MysqlDatabase;
  29 extern char *MysqlUser;
  30 extern char *MysqlPassword;
  31
  32 extern unsigned int nbusers_max;
  33 extern unsigned int nbusers;
  34 extern unsigned int nbchans_max;
  35 extern unsigned int nbchans;
  36
  37 extern int verbose;
  38
  39 MYSQL *myptr;
  40
  41 #ifdef HASHLISTSUPPORT
  42 hashlist hashnicks;
  43 hashlist hashchans;
  44 hashlist hashservs;
  45 #endif
  46
  47 /* last time we cleaned the server table */
  48 static int ServerLastClean = -1;
  49 extern int ServerCleanFreq;
  50 extern int ServerCacheTime;
  51
  52 /* last time we cleaned the user table */
  53 static int UserLastClean = -1;
  54 extern int UserCleanFreq;
  55 extern int UserCacheTime;
  56
  57
  58 void db_connect()
  59 {
  60         MYSQL_RES *resptr;
  61         if (myptr)
  62                 mysql_close(myptr);
  63         /* connecting to MySQL */
  64         myptr = mysql_init(NULL);
  65         if (!mysql_real_connect
  66                  (myptr, MysqlServer, MysqlUser, MysqlPassword, MysqlDatabase, 0,
  67                   NULL, 0))
  68                 fatal("Failed to connect to database : %s\n", mysql_error(myptr));
  69
  70         /* Cleaning up the database */
  71         db_query("DELETE FROM " TBL_CHAN);
  72         db_query("DELETE FROM " TBL_ISON);
  73         db_query("DELETE FROM " TBL_SERV);
  74         db_query("DELETE FROM " TBL_USER);
  75
  76         db_query("SELECT val FROM " TBL_MAXV " WHERE type=\'users\'");
  77         resptr = mysql_store_result(myptr);
  78         if (mysql_num_rows(resptr))
  79                 nbusers_max = atoi(*mysql_fetch_row(resptr));
  80         else
  81                 db_query("INSERT INTO " TBL_MAXV " VALUES ('users', '0', NOW())");
  82         mysql_free_result(resptr);
  83
  84         db_query("SELECT val FROM " TBL_MAXV " WHERE type=\'channels\'");
  85         resptr = mysql_store_result(myptr);
  86         if (mysql_num_rows(resptr))
  87                 nbchans_max = atoi(*mysql_fetch_row(resptr));
  88         else
  89                 db_query("INSERT INTO " TBL_MAXV " VALUES ('channels', '0', NOW())");
  90         mysql_free_result(resptr);
  91
  92 #ifdef HASHLISTSUPPORT
  93         /* init the hash lists */
  94         hashnicks = newhashlist();
  95         hashchans = newhashlist();
  96         hashservs = newhashlist();
  97 #endif
  98 }
  99
 100 /* escape the string */
 101 char *db_escape(char *ch)
 102 {
 103         char *buf = NULL;
 104         int size;
 105         if (ch)
 106         {
 107                 size = (strlen(ch)) * 2 + 1;
 108                 buf = (char *) malloc(sizeof(char) * size);
 109                 if (*ch)
 110                         mysql_real_escape_string(myptr, buf, ch, strlen(ch));
 111                 else
 112                         strcpy(buf, "");
 113         }
 114         return buf;
 115 }
 116
 117 /* send an SQL query to the database */
 118 int db_query(const char *fmt, ...)
 119 {
 120         va_list args;
 121         char buf[2048];                           /* should be enough for long queries (don't underestimate :o)) */
 122         va_start(args, fmt);
 123         vsprintf(buf, fmt, args);
 124         if (verbose)
 125                 log(">SQL : %s", buf);
 126         if (mysql_real_query(myptr, buf, strlen(buf)))
 127                 fatal("Query failed: %s", mysql_error(myptr));
 128         return 0;
 129 }
 130
 131 /* serv should be db_escape'd before call */
 132 /* -1 if server not found, servid else */
 133 int db_checkserver(char *serv)
 134 {
 135         int servid = -1;
 136         MYSQL_RES *resptr;
 137         db_query("SELECT servid FROM " TBL_SERV " WHERE server=\"%s\"", serv);
 138         resptr = mysql_store_result(myptr);
 139         if (mysql_num_rows(resptr))
 140                 servid = atoi(*mysql_fetch_row(resptr));
 141         mysql_free_result(resptr);
 142         return servid;
 143 }
 144
 145 /* serv should be db_escape'd before call */
 146 int db_getserver(char *serv)
 147 {
 148 #ifdef HASHLISTSUPPORT
 149         strtolwr(serv);
 150         return hash_find(hashservs, serv, KEYOTHER);
 151 #else
 152         MYSQL_RES *resptr;
 153         int res = 0;
 154
 155         db_query("SELECT servid FROM " TBL_SERV " WHERE server=\'%s\'", serv);
 156         resptr = mysql_store_result(myptr);
 157         if (mysql_num_rows(resptr))
 158                 res = atoi(*mysql_fetch_row(resptr));
 159         mysql_free_result(resptr);
 160         return res;
 161 #endif
 162 }
 163
 164 /* changes a nick in the hashlist */
 165 void db_chgnick(char *newnick, char *oldnick)
 166 {
 167 #ifdef HASHLISTSUPPORT
 168         /* people often change from NICK-sthing to NICK-sthing */
 169         strtolwr(newnick);
 170         strtolwr(oldnick);
 171         hash_update(hashnicks, newnick, oldnick, KEYOTHER);
 172 #endif
 173 }
 174
 175 /* remove a server from the hashlist */
 176 void db_delserver(char *server)
 177 {
 178 #ifdef HASHLISTSUPPORT
 179         strtolwr(server);
 180         hash_del(hashservs, server, KEYOTHER);
 181 #endif
 182 }
 183
 184 /* add a server to the hashlist */
 185 void db_addserver(char *server, int servid)
 186 {
 187 #ifdef HASHLISTSUPPORT
 188         strtolwr(server);
 189         hash_add(hashservs, server, servid, KEYOTHER);
 190 #endif
 191 }
 192
 193 /* add a nick to the hashlist */
 194 void db_addnick(char *nick, int nickid)
 195 {
 196 #ifdef HASHLISTSUPPORT
 197         strtolwr(nick);
 198         hash_add(hashnicks, nick, nickid, KEYOTHER);
 199 #endif
 200 }
 201
 202 /* add a nick to the hashlist */
 203 void db_delnick(char *nick)
 204 {
 205 #ifdef HASHLISTSUPPORT
 206         strtolwr(nick);
 207         hash_del(hashnicks, nick, KEYOTHER);
 208 #endif
 209 }
 210
 211 /* nick should be db_escape'd before call */
 212 /* -1 if nick not found, nickid else */
 213 int db_checknick(char *nick)
 214 {
 215 #ifdef HASHLISTSUPPORT
 216         int res = 0;
 217         char *nicklow = strdup(nick);
 218         strtolwr(nicklow);
 219         res = hash_find_unsure(hashnicks, nicklow, KEYOTHER);
 220         free(nicklow);
 221         return res;
 222 #else
 223         int nickid = -1;
 224         MYSQL_RES *resptr;
 225         db_query("SELECT nickid FROM " TBL_USER " WHERE nick=\"%s\"", nick);
 226         resptr = mysql_store_result(myptr);
 227         if (mysql_num_rows(resptr))
 228                 nickid = atoi(*mysql_fetch_row(resptr));
 229         mysql_free_result(resptr);
 230         return nickid;
 231 #endif
 232 }
 233
 234 /* nick should be db_escape'd before call */
 235 int db_getnick(char *nick)
 236 {
 237 #ifdef HASHLISTSUPPORT
 238         int res = 0;
 239         char *nicklow = strdup(nick);
 240         strtolwr(nicklow);
 241         res = hash_find(hashnicks, nicklow, KEYOTHER);
 242         free(nicklow);
 243         return res;
 244 #else
 245         MYSQL_RES *resptr;
 246         int res = 0;
 247
 248         db_query("SELECT nickid FROM " TBL_USER " WHERE nick=\'%s\'", nick);
 249         resptr = mysql_store_result(myptr);
 250         if (mysql_num_rows(resptr))
 251                 res = atoi(*mysql_fetch_row(resptr));
 252         else
 253                 fatal("nickname not found !");
 254         mysql_free_result(resptr);
 255         return res;
 256 #endif
 257 }
 258
 259 int db_getservfromnick(char *nick)
 260 {
 261         MYSQL_RES *resptr;
 262         int res = 0;
 263         db_query("SELECT servid FROM " TBL_USER " WHERE nick=\'%s\'", nick);
 264         resptr = mysql_store_result(myptr);
 265         if (mysql_num_rows(resptr))
 266                 res = atoi(*mysql_fetch_row(resptr));
 267         else
 268                 fatal("nickname not found !");
 269         mysql_free_result(resptr);
 270         return res;
 271 }
 272
 273 int db_insertid()
 274 {
 275         return mysql_insert_id(myptr);
 276 }
 277
 278 void db_removenick(char *nick)
 279 {
 280         int nickid = db_getnick(nick);
 281         //TODO
 282         if (nickid == 0)
 283                 fatal("nickid 0");
 284         db_removefromchans(nickid);
 285         if (UserCacheTime)
 286                 db_query("UPDATE " TBL_USER
 287                                         " SET online=\"N\", lastquit=NOW(), servid=NULL WHERE nickid=\"%d\"",
 288                                         nickid);
 289         else
 290                 db_query("DELETE FROM " TBL_USER " WHERE nickid=\'%d\'", nickid);
 291 }
 292
 293
 294 void db_removefromchans(int nickid)
 295 {
 296         MYSQL_RES *resptr;
 297         char **res;
 298         db_query("SELECT " TBL_ISON ".chanid, channel FROM " TBL_ISON ", "
 299                                 TBL_CHAN " WHERE nickid=\'%d\' AND " TBL_CHAN ".chanid = "
 300                                 TBL_ISON ".chanid", nickid);
 301         resptr = mysql_store_result(myptr);
 302         db_query("DELETE FROM " TBL_ISON " WHERE nickid=\'%d\'", nickid);
 303         while ((res = mysql_fetch_row(resptr)))
 304         {
 305                 char *chan = db_escape(res[1]);
 306                 db_checkemptychan(atoi(res[0]), chan);
 307                 free(chan);
 308         }
 309         mysql_free_result(resptr);
 310 }
 311
 312 void db_checkemptychan(int chanid, char *chan)
 313 {
 314         MYSQL_RES *resptr2;
 315         db_query("SELECT chanid FROM " TBL_ISON " WHERE chanid=\'%d\'", chanid);
 316         resptr2 = mysql_store_result(myptr);
 317         if (!mysql_num_rows(resptr2))
 318         {
 319                 db_query("DELETE FROM " TBL_CHAN " WHERE chanid=\'%d\'", chanid);
 320 #ifdef HASHLISTSUPPORT
 321                 hash_del(hashchans, chan, KEYCHAN);
 322 #endif
 323                 nbchans--;
 324         }
 325         mysql_free_result(resptr2);
 326 }
 327
 328 int db_getlusers(int type)
 329 {
 330         MYSQL_RES *resptr;
 331         int retcode;
 332         switch (type)
 333         {
 334         case LUSERS_USERS:
 335                 db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_li=\'N\'");
 336                 break;
 337         case LUSERS_USERSINV:
 338                 db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_li=\'Y\'");
 339                 break;
 340         case LUSERS_OPERS:
 341                 db_query("SELECT COUNT(*) FROM " TBL_USER " WHERE mode_lo=\'Y\'");
 342                 break;
 343         case LUSERS_CHAN:
 344                 db_query("SELECT COUNT(*) FROM " TBL_CHAN);
 345                 break;
 346         case LUSERS_SERV:
 347                 db_query("SELECT COUNT(*) FROM " TBL_SERV);
 348                 break;
 349         case LUSERS_USERSGLOB:
 350                 db_query("SELECT COUNT(*) FROM " TBL_USER);
 351                 break;
 352         case LUSERS_USERSMAX:
 353                 db_query("SELECT val FROM " TBL_MAXV " WHERE type='users'");
 354                 break;
 355         }
 356         resptr = mysql_store_result(myptr);
 357         if (mysql_num_rows(resptr))
 358                 retcode = atoi(*mysql_fetch_row(resptr));
 359         else
 360                 retcode = -1;
 361         mysql_free_result(resptr);
 362         return retcode;
 363 }
 364
 365 /* chan should be db_escape'd before call */
 366 int db_getchannel(char *chan)
 367 {
 368 #ifdef HASHLISTSUPPORT
 369         strtolwr(chan);
 370         return hash_find(hashchans, chan, KEYCHAN);
 371 #else
 372         int res = 0;
 373
 374         MYSQL_RES *resptr;
 375         strtolwr(chan);
 376         db_query("SELECT chanid FROM " TBL_CHAN " WHERE channel=\'%s\'", chan);
 377         resptr = mysql_store_result(myptr);
 378         if (mysql_num_rows(resptr))
 379                 res = atoi(*mysql_fetch_row(resptr));
 380         else
 381                 fatal("channel not found !");
 382         mysql_free_result(resptr);
 383         return res;
 384 #endif
 385 }
 386
 387 /* chan should be db_escape'd before call */
 388 /* chan is created if not exists */
 389 int db_getchancreate(char *chan)
 390 {
 391         int res = -1;
 392
 393 #ifdef HASHLISTSUPPORT
 394         strtolwr(chan);
 395         res = hash_find_unsure(hashchans, chan, KEYCHAN);
 396 #else
 397         MYSQL_RES *resptr;
 398         strtolwr(chan);
 399         db_query("SELECT chanid FROM " TBL_CHAN " WHERE channel=\'%s\'", chan);
 400         resptr = mysql_store_result(myptr);
 401         if (mysql_num_rows(resptr))
 402                 res = atoi(*mysql_fetch_row(resptr));
 403         mysql_free_result(resptr);
 404 #endif
 405         if (res == -1)
 406
 407         {
 408                 db_query("INSERT INTO " TBL_CHAN " (channel) VALUES (\'%s\')", chan);
 409                 res = db_insertid();
 410 #ifdef HASHLISTSUPPORT
 411                 hash_add(hashchans, chan, res, KEYCHAN);
 412 #endif
 413                 nbchans++;
 414                 do_checknbchansmax();
 415         }
 416         return res;
 417 }
 418
 419 /* close connection to DBMS */
 420 void db_close()
 421 {
 422         if (myptr)
 423                 mysql_close(myptr);
 424         myptr = NULL;
 425 }
 426
 427 /* cleanup the server table, removing old entries */
 428 void db_cleanserver()
 429 {
 430         MYSQL_RES *resptr;
 431         int curtime = time(NULL);
 432         char **res;
 433         if (ServerLastClean == -1)
 434                 ServerLastClean = curtime;
 435         if (curtime > (ServerLastClean + ServerCleanFreq))
 436         {
 437                 ServerLastClean = curtime;
 438                 db_query("SELECT server FROM " TBL_SERV
 439                                         " WHERE online=\"N\" AND lastsplit<FROM_UNIXTIME(\"%d\")",
 440                                         curtime - ServerCacheTime);
 441                 resptr = mysql_store_result(myptr);
 442                 if (mysql_num_rows(resptr))
 443                 {
 444                         while ((res = mysql_fetch_row(resptr)))
 445                         {
 446                                 char *server = db_escape(res[0]);
 447                                 db_delserver(server);
 448                                 free(server);
 449                         }
 450                         mysql_free_result(resptr);
 451                         db_query("DELETE FROM " TBL_SERV
 452                                                 " WHERE online=\"N\" AND lastsplit<FROM_UNIXTIME(\"%d\")",
 453                                                 curtime - ServerCacheTime);
 454                 }
 455         }
 456 }
 457
 458 /* cleanup the user table, removing old entries */
 459 void db_cleanuser()
 460 {
 461         MYSQL_RES *resptr;
 462         int curtime = time(NULL);
 463         char **res;
 464         if (UserLastClean == -1)
 465                 UserLastClean = curtime;
 466         if (curtime > (UserLastClean + UserCleanFreq))
 467         {
 468                 UserLastClean = curtime;
 469                 db_query("SELECT nick FROM " TBL_USER
 470                                         " WHERE online=\"N\" AND lastquit<FROM_UNIXTIME(\"%d\")",
 471                                         curtime - UserCacheTime);
 472
 473                 resptr = mysql_store_result(myptr);
 474                 if (mysql_num_rows(resptr))
 475                 {
 476                         while ((res = mysql_fetch_row(resptr)))
 477                         {
 478                                 char *nick = db_escape(res[0]);
 479                                 db_delnick(nick);
 480                                 free(nick);
 481                         }
 482                         mysql_free_result(resptr);
 483                         db_query("DELETE FROM " TBL_USER
 484                                                 " WHERE online=\"N\" AND lastquit<FROM_UNIXTIME(\"%d\")",
 485                                                 curtime - UserCacheTime);
 486                 }
 487         }
 488 }