a4stats/a4stats_db.c

   1 #include <stdio.h>
   2 #include <stdarg.h>
   3 #include "../lib/version.h"
   4 #include "../dbapi2/dbapi2.h"
   5 #include "../core/error.h"
   6 #include "../core/hooks.h"
   7 #include "../core/schedule.h"
   8 #include "../control/control.h"
   9 #include "../irc/irc.h"
  10 #include "../lua/lua.h"
  11
  12 MODULE_VERSION("");
  13
  14 DBAPIConn *a4statsdb;
  15
  16 static int a4stats_connectdb(void) {
  17   if(!a4statsdb) {
  18     a4statsdb = dbapi2open("pqsql", "a4stats");
  19     if(!a4statsdb) {
  20       Error("a4stats", ERR_WARNING, "Unable to connect to db -- not loaded.");
  21       return 0;
  22     }
  23   }
  24
  25   a4statsdb->createtable(a4statsdb, NULL, NULL,
  26     "CREATE TABLE ? (id SERIAL, name VARCHAR(256) UNIQUE, timestamp INT DEFAULT 0, active INT DEFAULT 1, deleted INT DEFAULT 0, privacy INT DEFAULT 1, "
  27     "h0 INT DEFAULT 0, h1 INT DEFAULT 0, h2 INT DEFAULT 0, h3 INT DEFAULT 0, h4 INT DEFAULT 0, h5 INT DEFAULT 0, "
  28     "h6 INT DEFAULT 0, h7 INT DEFAULT 0, h8 INT DEFAULT 0, h9 INT DEFAULT 0, h10 INT DEFAULT 0, h11 INT DEFAULT 0, "
  29     "h12 INT DEFAULT 0, h13 INT DEFAULT 0, h14 INT DEFAULT 0, h15 INT DEFAULT 0, h16 INT DEFAULT 0, h17 INT DEFAULT 0, "
  30     "h18 INT DEFAULT 0, h19 INT DEFAULT 0, h20 INT DEFAULT 0, h21 INT DEFAULT 0, h22 INT DEFAULT 0, h23 INT DEFAULT 0)", "T", "channels");
  31
  32   a4statsdb->createtable(a4statsdb, NULL, NULL,
  33     "CREATE TABLE ? (channelid INT, kicker VARCHAR(128), kickerid INT, victim VARCHAR(128), victimid INT, timestamp INT, reason VARCHAR(256))", "T", "kicks");
  34
  35   a4statsdb->squery(a4statsdb, "CREATE INDEX kicks_channelid_index ON ? (channelid)", "T", "kicks");
  36   a4statsdb->squery(a4statsdb, "CREATE INDEX kicks_timestamp_index ON ? (timestamp)", "T", "kicks");
  37
  38   a4statsdb->createtable(a4statsdb, NULL, NULL,
  39     "CREATE TABLE ? (channelid INT, setby VARCHAR(128), setbyid INT, timestamp INT, topic VARCHAR(512))", "T", "topics");
  40
  41   a4statsdb->squery(a4statsdb, "CREATE INDEX topics_channelid_index ON ? (channelid)", "T", "topics");
  42
  43   a4statsdb->createtable(a4statsdb, NULL, NULL,
  44     "CREATE TABLE ? (channelid INT, account VARCHAR(128), accountid INT, seen INT DEFAULT 0, rating INT DEFAULT 0, lines INT DEFAULT 0, chars INT DEFAULT 0, words INT DEFAULT 0, "
  45     "h0 INT DEFAULT 0, h1 INT DEFAULT 0, h2 INT DEFAULT 0, h3 INT DEFAULT 0, h4 INT DEFAULT 0, h5 INT DEFAULT 0, "
  46     "h6 INT DEFAULT 0, h7 INT DEFAULT 0, h8 INT DEFAULT 0, h9 INT DEFAULT 0, h10 INT DEFAULT 0, h11 INT DEFAULT 0, "
  47     "h12 INT DEFAULT 0, h13 INT DEFAULT 0, h14 INT DEFAULT 0, h15 INT DEFAULT 0, h16 INT DEFAULT 0, h17 INT DEFAULT 0, "
  48     "h18 INT DEFAULT 0, h19 INT DEFAULT 0, h20 INT DEFAULT 0, h21 INT DEFAULT 0, h22 INT DEFAULT 0, h23 INT DEFAULT 0, "
  49     "last VARCHAR(512), quote VARCHAR(512), quotereset INT DEFAULT 0, mood_happy INT DEFAULT 0, mood_sad INT DEFAULT 0, questions INT DEFAULT 0, yelling INT DEFAULT 0, caps INT DEFAULT 0, "
  50     "slaps INT DEFAULT 0, slapped INT DEFAULT 0, highlights INT DEFAULT 0, kicks INT DEFAULT 0, kicked INT DEFAULT 0, ops INT DEFAULT 0, deops INT DEFAULT 0, actions INT DEFAULT 0, skitzo INT DEFAULT 0, foul INT DEFAULT 0, "
  51     "firstseen INT DEFAULT 0, curnick VARCHAR(16))", "T", "users");
  52
  53   a4statsdb->squery(a4statsdb, "CREATE INDEX users_account_index ON ? (account)", "T", "users");
  54   a4statsdb->squery(a4statsdb, "CREATE INDEX users_accountid_index ON ? (accountid)", "T", "users");
  55   a4statsdb->squery(a4statsdb, "CREATE INDEX users_channelid_index ON ? (channelid)", "T", "users");
  56   a4statsdb->squery(a4statsdb, "CREATE UNIQUE INDEX users_channelid_account_accountid_index ON ? (channelid, account, accountid)", "T", "users");
  57   a4statsdb->squery(a4statsdb, "CREATE INDEX users_channelid_lines_index ON ? (channelid, lines)", "T", "users");
  58
  59   return 1;
  60 }
  61
  62 static void a4stats_closedb(void) {
  63   if(!a4statsdb)
  64     return;
  65
  66   a4statsdb->close(a4statsdb);
  67   a4statsdb = NULL;
  68 }
  69
  70 static int a4stats_lua_escape_string(lua_State *ps) {
  71   const char *input;
  72   char *buf, *buf2;
  73   size_t len, i, o;
  74
  75   if (!lua_isstring(ps, 1))
  76     LUA_RETURN(ps, LUA_FAIL);
  77
  78   input = lua_tostring(ps, 1);
  79   len = strlen(input);
  80
  81   buf = malloc(len * 2 + 1);
  82
  83   if (!buf)
  84     LUA_RETURN(ps, LUA_FAIL);
  85
  86   a4statsdb->escapestring(a4statsdb, buf, input, len);
  87
  88   buf2 = malloc(len * 4 + 1);
  89
  90   if (!buf2) {
  91     free(buf);
  92     LUA_RETURN(ps, LUA_FAIL);
  93   }
  94
  95   /* escape "?" */
  96   o = 0;
  97   for (i = 0; buf[i]; i++) {
  98     if (buf[i] == '?') {
  99       buf2[i + o] = '\\';
 100       o++;
 101     }
 102
 103     buf2[i + o] = buf[i];
 104   }
 105   buf2[i + o] = '\0';
 106
 107   free(buf);
 108
 109   lua_pushstring(ps, buf2);
 110
 111   free(buf2);
 112
 113   return 1;
 114 }
 115
 116 typedef struct db_callback_info {
 117   struct db_callback_info *next;
 118
 119   lua_State *interp;
 120   char callback[64];
 121   int uarg_index;
 122 } db_callback_info;
 123
 124 static db_callback_info *dci_head;
 125
 126 static void a4stats_delete_dci(db_callback_info *dci) {
 127 db_callback_info **pnext;
 128
 129   for (pnext = &dci_head; *pnext; pnext = &((*pnext)->next)) {
 130     if (*pnext == dci) {
 131       *pnext = dci->next;
 132       break;
 133     }
 134   }
 135
 136   free(dci);
 137 }
 138
 139 static void a4stats_fetch_user_cb(const struct DBAPIResult *result, void *uarg) {
 140   db_callback_info *dci = uarg;
 141   time_t seen = 0, quotereset = 0;
 142
 143   if (result) {
 144     if (result->success) {
 145       while (result->next(result)) {
 146         seen = (result->get(result, 0)) ? (time_t)strtoul(result->get(result, 0), NULL, 10) : 0;
 147         quotereset = (result->get(result, 1)) ? (time_t)strtoul(result->get(result, 1), NULL, 10) : 0;
 148       }
 149     }
 150
 151     result->clear(result);
 152   }
 153
 154   if (dci->interp) {
 155     lua_vpcall(dci->interp, dci->callback, "llR", (long)seen, (long)quotereset, dci->uarg_index);
 156     luaL_unref(dci->interp, LUA_REGISTRYINDEX, dci->uarg_index);
 157   }
 158
 159   a4stats_delete_dci(dci);
 160 }
 161
 162 static int a4stats_lua_fetch_user(lua_State *ps) {
 163   const char *account, *callback;
 164   unsigned long channelid, accountid;
 165   db_callback_info *dci;
 166
 167   if (!lua_islong(ps, 1) || !lua_isstring(ps, 2) || !lua_isnumber(ps, 3) || !lua_isstring(ps, 4))
 168     LUA_RETURN(ps, LUA_FAIL);
 169
 170   channelid = lua_tonumber(ps, 1);
 171   account = lua_tostring(ps, 2);
 172   accountid = lua_tonumber(ps, 3);
 173   callback = lua_tostring(ps, 4);
 174
 175   dci = malloc(sizeof(*dci));
 176   dci->interp = ps;
 177
 178   strncpy(dci->callback, callback, sizeof(dci->callback));
 179   dci->callback[sizeof(dci->callback) - 1] = '\0';
 180
 181   lua_pushvalue(ps, 5);
 182   dci->uarg_index = luaL_ref(ps, LUA_REGISTRYINDEX);
 183
 184   a4statsdb->query(a4statsdb, a4stats_fetch_user_cb, dci, "SELECT seen, quotereset FROM ? WHERE channelid = ? AND (accountid != 0 AND accountid = ? OR accountid = 0 AND account = ?)", "TUUs", "users", channelid, accountid, account);
 185
 186   LUA_RETURN(ps, LUA_OK);
 187 }
 188
 189 typedef struct user_update_info {
 190   int stage;
 191   char *update;
 192   unsigned long channelid;
 193   char *account;
 194   unsigned long accountid;
 195 } user_update_info;
 196
 197 static void a4stats_update_user_cb(const struct DBAPIResult *result, void *uarg) {
 198   user_update_info *uui = uarg;
 199
 200   uui->stage++;
 201
 202   if (uui->stage == 1 || (result != NULL && uui->stage == 3))
 203     a4statsdb->query(a4statsdb, a4stats_update_user_cb, uui, uui->update, "TUUs", "users", uui->channelid, uui->accountid, uui->account);
 204   else {
 205     if (result == NULL || result->affected > 0 || uui->stage == 4) {
 206       if (result == NULL || (result->affected == 0 && uui->stage == 4))
 207         Error("a4stats", ERR_WARNING, "Unable to update user.");
 208
 209       free(uui->update);
 210       free(uui->account);
 211       free(uui);
 212       return;
 213     }
 214
 215     a4statsdb->query(a4statsdb, a4stats_update_user_cb, uui, "INSERT INTO ? (channelid, account, accountid, firstseen) VALUES (?, ?, ?, ?)", "TUsUt", "users", uui->channelid, uui->account, uui->accountid, time(NULL));
 216   }
 217 }
 218
 219 static int a4stats_lua_update_user(lua_State *ps) {
 220   const char *account;
 221   unsigned long channelid, accountid;
 222   char query[4096];
 223   int first = 1;
 224   user_update_info *uui;
 225
 226   if (!lua_isnumber(ps, 1) || !lua_isstring(ps, 2) || !lua_isnumber(ps, 3))
 227     LUA_RETURN(ps, LUA_FAIL);
 228
 229   channelid = lua_tonumber(ps, 1);
 230   account = lua_tostring(ps, 2);
 231   accountid = lua_tonumber(ps, 3);
 232
 233   strcpy(query, "UPDATE ? SET ");
 234
 235   lua_pushvalue(ps, 4);
 236   lua_pushnil(ps);
 237
 238   while (lua_next(ps, -2)) {
 239     const char *value = lua_tostring(ps, -1);
 240
 241     if (first)
 242       first = 0;
 243     else
 244       strcat(query, ", ");
 245
 246     strcat(query, value);
 247
 248     lua_pop(ps, 1);
 249   }
 250
 251   lua_pop(ps, 1);
 252
 253   strcat(query, " WHERE channelid = ? AND (accountid != 0 AND accountid = ? OR accountid = 0 AND account = ?)");
 254
 255   uui = malloc(sizeof(*uui));
 256   uui->stage = 0;
 257   uui->update = strdup(query);
 258   uui->channelid = channelid;
 259   uui->account = strdup(account);
 260   uui->accountid = accountid;
 261
 262   a4stats_update_user_cb(NULL, uui);
 263
 264   LUA_RETURN(ps, LUA_OK);
 265 }
 266
 267 static int a4stats_lua_add_line(lua_State *ps) {
 268   char query[256];
 269   const char *channel;
 270   int hour;
 271
 272   if (!lua_isstring(ps, 1) || !lua_isnumber(ps, 2))
 273     LUA_RETURN(ps, LUA_FAIL);
 274
 275   channel = lua_tostring(ps, 1);
 276   hour = lua_tonumber(ps, 2);
 277
 278   snprintf(query, sizeof(query), "UPDATE ? SET h%d = h%d + 1 WHERE name = ?", hour, hour);
 279
 280   a4statsdb->squery(a4statsdb, query, "Ts", "channels", channel);
 281
 282   LUA_RETURN(ps, LUA_OK);
 283 }
 284
 285 static void a4stats_fetch_channels_cb(const struct DBAPIResult *result, void *uarg) {
 286   db_callback_info *dci = uarg;
 287   unsigned long channelid;
 288   int active;
 289   char *channel;
 290
 291   if (result) {
 292     if (result->success) {
 293       while (result->next(result)) {
 294         channelid = strtoul(result->get(result, 0), NULL, 10);
 295         channel = result->get(result, 1);
 296         active = atoi(result->get(result, 2));
 297
 298         if (dci->interp)
 299           lua_vpcall(dci->interp, dci->callback, "lsiR", channelid, channel, active, dci->uarg_index);
 300       }
 301     }
 302
 303     result->clear(result);
 304   }
 305
 306   if (dci->interp)
 307     luaL_unref(dci->interp, LUA_REGISTRYINDEX, dci->uarg_index);
 308
 309   a4stats_delete_dci(dci);
 310 }
 311
 312 static int a4stats_lua_fetch_channels(lua_State *ps) {
 313   const char *callback;
 314   db_callback_info *dci;
 315
 316   if (!lua_isstring(ps, 1))
 317     LUA_RETURN(ps, LUA_FAIL);
 318
 319   callback = lua_tostring(ps, 1);
 320
 321   dci = malloc(sizeof(*dci));
 322   dci->interp = ps;
 323
 324   strncpy(dci->callback, callback, sizeof(dci->callback));
 325   dci->callback[sizeof(dci->callback) - 1] = '\0';
 326
 327   lua_pushvalue(ps, 2);
 328   dci->uarg_index = luaL_ref(ps, LUA_REGISTRYINDEX);
 329
 330   a4statsdb->query(a4statsdb, a4stats_fetch_channels_cb, dci, "SELECT id, name, active FROM ?", "T", "channels");
 331
 332   LUA_RETURN(ps, LUA_OK);
 333 }
 334
 335 static int a4stats_lua_enable_channel(lua_State *ps) {
 336   if (!lua_isstring(ps, 1))
 337     LUA_RETURN(ps, LUA_FAIL);
 338
 339   a4statsdb->squery(a4statsdb, "INSERT INTO ? (name, timestamp) VALUES (?, ?)", "Tst", "channels", lua_tostring(ps, 1), time(NULL));
 340   a4statsdb->squery(a4statsdb, "UPDATE ? SET active = 1, deleted = 0 WHERE name = ?", "Ts", "channels", lua_tostring(ps, 1));
 341
 342   LUA_RETURN(ps, LUA_OK);
 343 }
 344
 345 static int a4stats_lua_disable_channel(lua_State *ps) {
 346   if (!lua_isstring(ps, 1))
 347     LUA_RETURN(ps, LUA_FAIL);
 348
 349   a4statsdb->squery(a4statsdb, "UPDATE ? SET active = 0, deleted = ? WHERE name = ?", "Tts", "channels", time(NULL), lua_tostring(ps, 1));
 350
 351   LUA_RETURN(ps, LUA_OK);
 352 }
 353
 354 static int a4stats_lua_add_kick(lua_State *ps) {
 355   unsigned long channelid, kickerid, victimid;
 356   const char *kicker, *victim, *reason;
 357
 358   if (!lua_isnumber(ps, 1) || !lua_isstring(ps, 2) || !lua_isnumber(ps, 3) || !lua_isstring(ps, 4) || !lua_isnumber(ps, 5) || !lua_isstring(ps, 6))
 359     LUA_RETURN(ps, LUA_FAIL);
 360
 361   channelid = lua_tonumber(ps, 1);
 362   kicker = lua_tostring(ps, 2);
 363   kickerid = lua_tonumber(ps, 3);
 364   victim = lua_tostring(ps, 4);
 365   victimid = lua_tonumber(ps, 5);
 366   reason = lua_tostring(ps, 6);
 367
 368   a4statsdb->squery(a4statsdb, "INSERT INTO ? (channelid, kicker, kickerid, victim, victimid, timestamp, reason) VALUES (?, ?, ?, ?, ?, ?, ?)", "TUsUsUts",
 369     "kicks", channelid, kicker, kickerid, victim, victimid, time(NULL), reason);
 370
 371   LUA_RETURN(ps, LUA_OK);
 372 }
 373
 374 static int a4stats_lua_add_topic(lua_State *ps) {
 375   unsigned long channelid, setbyid;
 376   const char *topic, *setby;
 377
 378   if (!lua_isnumber(ps, 1) || !lua_isstring(ps, 2) || !lua_isstring(ps, 3) || !lua_isnumber(ps, 4))
 379     LUA_RETURN(ps, LUA_FAIL);
 380
 381   channelid = lua_tonumber(ps, 1);
 382   topic = lua_tostring(ps, 2);
 383   setby = lua_tostring(ps, 3);
 384   setbyid = lua_tonumber(ps, 4);
 385
 386   a4statsdb->squery(a4statsdb, "INSERT INTO ? (channelid, topic, timestamp, setby, setbyid) VALUES (?, ?, ?, ?, ?)", "TUstsU",
 387     "topics", channelid, topic, time(NULL), setby, setbyid);
 388
 389   LUA_RETURN(ps, LUA_OK);
 390 }
 391
 392 static void a4stats_hook_loadscript(int hooknum, void *arg) {
 393   void **args = arg;
 394   lua_State *l = args[1];
 395
 396   lua_register(l, "a4_enable_channel", a4stats_lua_enable_channel);
 397   lua_register(l, "a4_disable_channel", a4stats_lua_disable_channel);
 398   lua_register(l, "a4_fetch_channels", a4stats_lua_fetch_channels);
 399   lua_register(l, "a4_add_kick", a4stats_lua_add_kick);
 400   lua_register(l, "a4_add_topic", a4stats_lua_add_topic);
 401   lua_register(l, "a4_add_line", a4stats_lua_add_line);
 402   lua_register(l, "a4_fetch_user", a4stats_lua_fetch_user);
 403   lua_register(l, "a4_update_user", a4stats_lua_update_user);
 404   lua_register(l, "a4_escape_string", a4stats_lua_escape_string);
 405 }
 406
 407 #define lua_unregister(L, n) (lua_pushnil(L), lua_setglobal(L, n))
 408
 409 static void a4stats_hook_unloadscript(int hooknum, void *arg) {
 410   db_callback_info **pnext, *dci;
 411   lua_State *l = arg;
 412
 413   for (pnext = &dci_head; *pnext; pnext = &((*pnext)->next)) {
 414     dci = *pnext;
 415     if (dci->interp == l) {
 416       *pnext = dci->next;
 417       free(dci);
 418     }
 419   }
 420 }
 421
 422 void _init(void) {
 423   lua_list *l;
 424   void *args[2];
 425
 426   a4stats_connectdb();
 427
 428   registerhook(HOOK_LUA_LOADSCRIPT, a4stats_hook_loadscript);
 429   registerhook(HOOK_LUA_UNLOADSCRIPT, a4stats_hook_unloadscript);
 430
 431   args[0] = NULL;
 432   for (l = lua_head; l;l = l->next) {
 433     args[1] = l->l;
 434     a4stats_hook_loadscript(HOOK_LUA_LOADSCRIPT, args);
 435   }
 436 }
 437
 438 void _fini(void) {
 439   lua_list *l;
 440
 441   a4stats_closedb();
 442
 443   for (l = lua_head; l;l = l->next) {
 444     a4stats_hook_loadscript(HOOK_LUA_UNLOADSCRIPT, l->l);
 445
 446     lua_unregister(l->l, "a4_enable_channel");
 447     lua_unregister(l->l, "a4_disable_channel");
 448     lua_unregister(l->l, "a4_fetch_channels");
 449     lua_unregister(l->l, "a4_add_kick");
 450     lua_unregister(l->l, "a4_add_topic");
 451     lua_unregister(l->l, "a4_add_line");
 452     lua_unregister(l->l, "a4_fetch_user");
 453     lua_unregister(l->l, "a4_update_user");
 454     lua_unregister(l->l, "a4_escape_string");
 455   }
 456
 457   deregisterhook(HOOK_LUA_LOADSCRIPT, a4stats_hook_loadscript);
 458   deregisterhook(HOOK_LUA_UNLOADSCRIPT, a4stats_hook_unloadscript);
 459 }
 460