addons/res_config_mysql.c

   1 /*
   2  * Asterisk -- An open source telephony toolkit.
   3  *
   4  * Copyright (C) 1999-2005, Digium, Inc.
   5  *
   6  * Mark Spencer <markster@digium.com>  - Asterisk Author
   7  * Matthew Boehm <mboehm@cytelcom.com> - MySQL RealTime Driver Author
   8  *
   9  * See http://www.asterisk.org for more information about
  10  * the Asterisk project. Please do not directly contact
  11  * any of the maintainers of this project for assistance;
  12  * the project provides a web site, mailing lists and IRC
  13  * channels for your use.
  14  *
  15  * This program is free software, distributed under the terms of
  16  * the GNU General Public License Version 2. See the LICENSE file
  17  * at the top of the source tree.
  18  */
  19
  20 /*!
  21  * \file
  22  * \brief MySQL CDR backend
  23  */
  24
  25 /*** MODULEINFO
  26         <depend>mysqlclient</depend>
  27         <defaultenabled>no</defaultenabled>
  28  ***/
  29
  30 #include "asterisk.h"
  31
  32 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  33
  34 #include <sys/stat.h>
  35
  36 #include <mysql/mysql.h>
  37 #include <mysql/mysql_version.h>
  38 #include <mysql/errmsg.h>
  39
  40 #include "asterisk/channel.h"
  41 #include "asterisk/logger.h"
  42 #include "asterisk/config.h"
  43 #include "asterisk/module.h"
  44 #include "asterisk/lock.h"
  45 #include "asterisk/options.h"
  46 #include "asterisk/cli.h"
  47 #include "asterisk/utils.h"
  48 #include "asterisk/threadstorage.h"
  49
  50 #define RES_CONFIG_MYSQL_CONF "res_config_mysql.conf"
  51 #define RES_CONFIG_MYSQL_CONF_OLD "res_mysql.conf"
  52 #define READHANDLE      0
  53 #define WRITEHANDLE     1
  54
  55 #define ESCAPE_STRING(buf, var) \
  56         do { \
  57                 if ((valsz = strlen(var)) * 2 + 1 > ast_str_size(buf)) { \
  58                         ast_str_make_space(&(buf), valsz * 2 + 1); \
  59                 } \
  60                 mysql_real_escape_string(&dbh->handle, ast_str_buffer(buf), var, valsz); \
  61         } while (0)
  62
  63 AST_THREADSTORAGE(sql_buf);
  64 AST_THREADSTORAGE(sql2_buf);
  65 AST_THREADSTORAGE(find_buf);
  66 AST_THREADSTORAGE(scratch_buf);
  67 AST_THREADSTORAGE(modify_buf);
  68 AST_THREADSTORAGE(modify2_buf);
  69 AST_THREADSTORAGE(modify3_buf);
  70
  71 enum requirements { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR };
  72
  73 struct mysql_conn {
  74         AST_RWLIST_ENTRY(mysql_conn) list;
  75         ast_mutex_t     lock;
  76         MYSQL       handle;
  77         char        host[50];
  78         char        name[50];
  79         char        user[50];
  80         char        pass[50];
  81         char        sock[50];
  82         int         port;
  83         int         connected;
  84         time_t      connect_time;
  85         enum requirements requirements;
  86         char        unique_name[0];
  87 };
  88
  89 struct columns {
  90         char *name;
  91         char *type;
  92         char *dflt;
  93         char null;
  94         int len;
  95         AST_LIST_ENTRY(columns) list;
  96 };
  97
  98 struct tables {
  99         ast_mutex_t lock;
 100         AST_LIST_HEAD_NOLOCK(mysql_columns, columns) columns;
 101         AST_LIST_ENTRY(tables) list;
 102         struct mysql_conn *database;
 103         char name[0];
 104 };
 105
 106 static AST_LIST_HEAD_STATIC(mysql_tables, tables);
 107 static AST_RWLIST_HEAD_STATIC(databases, mysql_conn);
 108
 109 static int parse_config(int reload);
 110 static int mysql_reconnect(struct mysql_conn *conn);
 111 static char *handle_cli_realtime_mysql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
 112 static char *handle_cli_realtime_mysql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
 113 static int load_mysql_config(struct ast_config *config, const char *category, struct mysql_conn *conn);
 114 static int require_mysql(const char *database, const char *tablename, va_list ap);
 115 static int internal_require(const char *database, const char *table, ...) attribute_sentinel;
 116
 117 static struct ast_cli_entry cli_realtime_mysql_status[] = {
 118         AST_CLI_DEFINE(handle_cli_realtime_mysql_status, "Shows connection information for the MySQL RealTime driver"),
 119         AST_CLI_DEFINE(handle_cli_realtime_mysql_cache, "Shows cached tables within the MySQL realtime driver"),
 120 };
 121
 122 static struct mysql_conn *find_database(const char *database, int for_write)
 123 {
 124         char *whichdb;
 125         const char *ptr;
 126         struct mysql_conn *cur;
 127
 128         if ((ptr = strchr(database, '/'))) {
 129                 /* Multiple databases encoded within string */
 130                 if (for_write) {
 131                         whichdb = ast_strdupa(ptr + 1);
 132                 } else {
 133                         whichdb = alloca(ptr - database + 1);
 134                         strncpy(whichdb, database, ptr - database);
 135                         whichdb[ptr - database] = '\0';
 136                 }
 137         } else {
 138                 whichdb = ast_strdupa(database);
 139         }
 140
 141         AST_RWLIST_RDLOCK(&databases);
 142         AST_RWLIST_TRAVERSE(&databases, cur, list) {
 143                 if (!strcmp(cur->unique_name, whichdb)) {
 144                         ast_mutex_lock(&cur->lock);
 145                         break;
 146                 }
 147         }
 148         AST_RWLIST_UNLOCK(&databases);
 149         return cur;
 150 }
 151
 152 #define release_database(a)     ast_mutex_unlock(&(a)->lock)
 153
 154 static int internal_require(const char *database, const char *table, ...)
 155 {
 156         va_list ap;
 157         int res;
 158         va_start(ap, table);
 159         res = require_mysql(database, table, ap);
 160         va_end(ap);
 161         return res;
 162 }
 163
 164 static void destroy_table(struct tables *table)
 165 {
 166         struct columns *column;
 167         ast_mutex_lock(&table->lock);
 168         while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
 169                 ast_free(column);
 170         }
 171         ast_mutex_unlock(&table->lock);
 172         ast_mutex_destroy(&table->lock);
 173         ast_free(table);
 174 }
 175
 176 static struct tables *find_table(const char *database, const char *tablename)
 177 {
 178         struct columns *column;
 179         struct tables *table;
 180         struct ast_str *sql = ast_str_thread_get(&find_buf, 30);
 181         char *fname, *ftype, *flen, *fdflt, *fnull;
 182         struct mysql_conn *dbh;
 183         MYSQL_RES *result;
 184         MYSQL_ROW row;
 185
 186         if (!(dbh = find_database(database, 1))) {
 187                 return NULL;
 188         }
 189
 190         AST_LIST_LOCK(&mysql_tables);
 191         AST_LIST_TRAVERSE(&mysql_tables, table, list) {
 192                 if (!strcasecmp(table->name, tablename)) {
 193                         ast_mutex_lock(&table->lock);
 194                         AST_LIST_UNLOCK(&mysql_tables);
 195                         release_database(dbh);
 196                         return table;
 197                 }
 198         }
 199
 200         /* Not found, scan the table */
 201         ast_str_set(&sql, 0, "DESC %s", tablename);
 202
 203         if (!mysql_reconnect(dbh)) {
 204                 release_database(dbh);
 205                 AST_LIST_UNLOCK(&mysql_tables);
 206                 return NULL;
 207         }
 208
 209         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 210                 ast_log(LOG_ERROR, "Failed to query database '%s', table '%s' columns: %s\n", database, tablename, mysql_error(&dbh->handle));
 211                 release_database(dbh);
 212                 AST_LIST_UNLOCK(&mysql_tables);
 213                 return NULL;
 214         }
 215
 216         if (!(table = ast_calloc(1, sizeof(*table) + strlen(tablename) + 1))) {
 217                 ast_log(LOG_ERROR, "Unable to allocate memory for new table structure\n");
 218                 release_database(dbh);
 219                 AST_LIST_UNLOCK(&mysql_tables);
 220                 return NULL;
 221         }
 222         strcpy(table->name, tablename); /* SAFE */
 223         table->database = dbh;
 224         ast_mutex_init(&table->lock);
 225         AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
 226
 227         if ((result = mysql_store_result(&dbh->handle))) {
 228                 while ((row = mysql_fetch_row(result))) {
 229                         fname = row[0];
 230                         ftype = row[1];
 231                         fnull = row[2];
 232                         fdflt = row[4];
 233                         ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
 234
 235                         if (fdflt == NULL) {
 236                                 fdflt = "";
 237                         }
 238
 239                         if (!(column = ast_calloc(1, sizeof(*column) + strlen(fname) + strlen(ftype) + strlen(fdflt) + 3))) {
 240                                 ast_log(LOG_ERROR, "Unable to allocate column element %s for %s\n", fname, tablename);
 241                                 destroy_table(table);
 242                                 release_database(dbh);
 243                                 AST_LIST_UNLOCK(&mysql_tables);
 244                                 return NULL;
 245                         }
 246
 247                         if ((flen = strchr(ftype, '('))) {
 248                                 sscanf(flen, "(%30d)", &column->len);
 249                         } else {
 250                                 /* Columns like dates, times, and timestamps don't have a length */
 251                                 column->len = -1;
 252                         }
 253
 254                         column->name = (char *)column + sizeof(*column);
 255                         column->type = (char *)column + sizeof(*column) + strlen(fname) + 1;
 256                         column->dflt = (char *)column + sizeof(*column) + strlen(fname) + 1 + strlen(ftype) + 1;
 257                         strcpy(column->name, fname);
 258                         strcpy(column->type, ftype);
 259                         strcpy(column->dflt, fdflt);
 260                         column->null = (strcmp(fnull, "YES") == 0 ? 1 : 0);
 261                         AST_LIST_INSERT_TAIL(&table->columns, column, list);
 262                 }
 263                 mysql_free_result(result);
 264         }
 265
 266         AST_LIST_INSERT_TAIL(&mysql_tables, table, list);
 267         ast_mutex_lock(&table->lock);
 268         AST_LIST_UNLOCK(&mysql_tables);
 269         release_database(dbh);
 270         return table;
 271 }
 272
 273 static void release_table(struct tables *table)
 274 {
 275         if (table) {
 276                 ast_mutex_unlock(&table->lock);
 277         }
 278 }
 279
 280 static struct columns *find_column(struct tables *table, const char *colname)
 281 {
 282         struct columns *column;
 283
 284         AST_LIST_TRAVERSE(&table->columns, column, list) {
 285                 if (strcmp(column->name, colname) == 0) {
 286                         break;
 287                 }
 288         }
 289
 290         return column;
 291 }
 292
 293 static struct ast_variable *realtime_mysql(const char *database, const char *table, va_list ap)
 294 {
 295         struct mysql_conn *dbh;
 296         MYSQL_RES *result;
 297         MYSQL_ROW row;
 298         MYSQL_FIELD *fields;
 299         int numFields, i, valsz;
 300         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
 301         struct ast_str *buf = ast_str_thread_get(&scratch_buf, 16);
 302         char *stringp;
 303         char *chunk;
 304         char *op;
 305         const char *newparam, *newval;
 306         struct ast_variable *var=NULL, *prev=NULL;
 307
 308         if (!(dbh = find_database(database, 0))) {
 309                 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: %s (check res_mysql.conf)\n", database);
 310                 return NULL;
 311         }
 312
 313         if (!table) {
 314                 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
 315                 release_database(dbh);
 316                 return NULL;
 317         }
 318
 319         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 320         newparam = va_arg(ap, const char *);
 321         newval = va_arg(ap, const char *);
 322         if (!newparam || !newval)  {
 323                 ast_log(LOG_WARNING, "MySQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 324                 release_database(dbh);
 325                 return NULL;
 326         }
 327
 328         /* Must connect to the server before anything else, as the escape function requires the mysql handle. */
 329         if (!mysql_reconnect(dbh)) {
 330                 release_database(dbh);
 331                 return NULL;
 332         }
 333
 334         /* Create the first part of the query using the first parameter/value pairs we just extracted
 335            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 336
 337         if (!strchr(newparam, ' '))
 338                 op = " =";
 339         else
 340                 op = "";
 341
 342         ESCAPE_STRING(buf, newval);
 343         ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op, ast_str_buffer(buf));
 344         while ((newparam = va_arg(ap, const char *))) {
 345                 newval = va_arg(ap, const char *);
 346                 if (!strchr(newparam, ' '))
 347                         op = " =";
 348                 else
 349                         op = "";
 350                 ESCAPE_STRING(buf, newval);
 351                 ast_str_append(&sql, 0, " AND %s%s '%s'", newparam, op, ast_str_buffer(buf));
 352         }
 353         va_end(ap);
 354
 355         ast_debug(1, "MySQL RealTime: Retrieve SQL: %s\n", ast_str_buffer(sql));
 356
 357         /* Execution. */
 358         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 359                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to query database: %s\n", mysql_error(&dbh->handle));
 360                 release_database(dbh);
 361                 return NULL;
 362         }
 363
 364         if ((result = mysql_store_result(&dbh->handle))) {
 365                 numFields = mysql_num_fields(result);
 366                 fields = mysql_fetch_fields(result);
 367
 368                 while ((row = mysql_fetch_row(result))) {
 369                         for (i = 0; i < numFields; i++) {
 370                                 if (ast_strlen_zero(row[i]))
 371                                         continue;
 372                                 for (stringp = ast_strdupa(row[i]), chunk = strsep(&stringp, ";"); chunk; chunk = strsep(&stringp, ";")) {
 373                                         if (!chunk || ast_strlen_zero(ast_strip(chunk))) {
 374                                                 continue;
 375                                         }
 376                                         if (prev) {
 377                                                 if ((prev->next = ast_variable_new(fields[i].name, chunk, ""))) {
 378                                                         prev = prev->next;
 379                                                 }
 380                                         } else {
 381                                                 prev = var = ast_variable_new(fields[i].name, chunk, "");
 382                                         }
 383                                 }
 384                         }
 385                 }
 386         } else {
 387                 ast_debug(1, "MySQL RealTime: Could not find any rows in table %s.\n", table);
 388         }
 389
 390         release_database(dbh);
 391         mysql_free_result(result);
 392
 393         return var;
 394 }
 395
 396 static struct ast_config *realtime_multi_mysql(const char *database, const char *table, va_list ap)
 397 {
 398         struct mysql_conn *dbh;
 399         MYSQL_RES *result;
 400         MYSQL_ROW row;
 401         MYSQL_FIELD *fields;
 402         int numFields, i, valsz;
 403         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
 404         struct ast_str *buf = ast_str_thread_get(&scratch_buf, 16);
 405         const char *initfield = NULL;
 406         char *stringp;
 407         char *chunk;
 408         char *op;
 409         const char *newparam, *newval;
 410         struct ast_variable *var = NULL;
 411         struct ast_config *cfg = NULL;
 412         struct ast_category *cat = NULL;
 413
 414         if (!(dbh = find_database(database, 0))) {
 415                 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
 416                 return NULL;
 417         }
 418
 419         if (!table) {
 420                 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
 421                 release_database(dbh);
 422                 return NULL;
 423         }
 424
 425         if (!(cfg = ast_config_new())) {
 426                 /* If I can't alloc memory at this point, why bother doing anything else? */
 427                 ast_log(LOG_WARNING, "Out of memory!\n");
 428                 release_database(dbh);
 429                 return NULL;
 430         }
 431
 432         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 433         newparam = va_arg(ap, const char *);
 434         newval = va_arg(ap, const char *);
 435         if (!newparam || !newval)  {
 436                 ast_log(LOG_WARNING, "MySQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 437                 ast_config_destroy(cfg);
 438                 release_database(dbh);
 439                 return NULL;
 440         }
 441
 442         initfield = ast_strdupa(newparam);
 443         if (initfield && (op = strchr(initfield, ' '))) {
 444                 *op = '\0';
 445         }
 446
 447         /* Must connect to the server before anything else, as the escape function requires the mysql handle. */
 448         if (!mysql_reconnect(dbh)) {
 449                 release_database(dbh);
 450                 ast_config_destroy(cfg);
 451                 return NULL;
 452         }
 453
 454         /* Create the first part of the query using the first parameter/value pairs we just extracted
 455            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 456
 457         if (!strchr(newparam, ' '))
 458                 op = " =";
 459         else
 460                 op = "";
 461
 462         ESCAPE_STRING(buf, newval);
 463         ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op, ast_str_buffer(buf));
 464         while ((newparam = va_arg(ap, const char *))) {
 465                 newval = va_arg(ap, const char *);
 466                 if (!strchr(newparam, ' ')) op = " ="; else op = "";
 467                 ESCAPE_STRING(buf, newval);
 468                 ast_str_append(&sql, 0, " AND %s%s '%s'", newparam, op, ast_str_buffer(buf));
 469         }
 470
 471         if (initfield) {
 472                 ast_str_append(&sql, 0, " ORDER BY %s", initfield);
 473         }
 474
 475         va_end(ap);
 476
 477         ast_debug(1, "MySQL RealTime: Retrieve SQL: %s\n", ast_str_buffer(sql));
 478
 479         /* Execution. */
 480         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 481                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to query database: %s\n", mysql_error(&dbh->handle));
 482                 release_database(dbh);
 483                 ast_config_destroy(cfg);
 484                 return NULL;
 485         }
 486
 487         if ((result = mysql_store_result(&dbh->handle))) {
 488                 numFields = mysql_num_fields(result);
 489                 fields = mysql_fetch_fields(result);
 490
 491                 while ((row = mysql_fetch_row(result))) {
 492                         var = NULL;
 493                         cat = ast_category_new("", "", -1);
 494                         if (!cat) {
 495                                 ast_log(LOG_WARNING, "Out of memory!\n");
 496                                 continue;
 497                         }
 498                         for (i = 0; i < numFields; i++) {
 499                                 if (ast_strlen_zero(row[i]))
 500                                         continue;
 501                                 for (stringp = ast_strdupa(row[i]), chunk = strsep(&stringp, ";"); chunk; chunk = strsep(&stringp, ";")) {
 502                                         if (chunk && !ast_strlen_zero(ast_strip(chunk))) {
 503                                                 if (initfield && !strcmp(initfield, fields[i].name)) {
 504                                                         ast_category_rename(cat, chunk);
 505                                                 }
 506                                                 var = ast_variable_new(fields[i].name, chunk, "");
 507                                                 ast_variable_append(cat, var);
 508                                         }
 509                                 }
 510                         }
 511                         ast_category_append(cfg, cat);
 512                 }
 513         } else {
 514                 ast_debug(1, "MySQL RealTime: Could not find any rows in table %s.\n", table);
 515         }
 516
 517         release_database(dbh);
 518         mysql_free_result(result);
 519
 520         return cfg;
 521 }
 522
 523 static int update_mysql(const char *database, const char *tablename, const char *keyfield, const char *lookup, va_list ap)
 524 {
 525         struct mysql_conn *dbh;
 526         my_ulonglong numrows;
 527         int valsz;
 528         const char *newparam, *newval;
 529         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100), *buf = ast_str_thread_get(&scratch_buf, 100);
 530         struct tables *table;
 531         struct columns *column = NULL;
 532
 533         if (!(dbh = find_database(database, 1))) {
 534                 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
 535                 return -1;
 536         }
 537
 538         if (!tablename) {
 539                 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
 540                 release_database(dbh);
 541                 return -1;
 542         }
 543
 544         if (!(table = find_table(database, tablename))) {
 545                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
 546                 release_database(dbh);
 547                 return -1;
 548         }
 549
 550         if (!(column = find_column(table, keyfield))) {
 551                 ast_log(LOG_ERROR, "MySQL RealTime: Updating on column '%s', but that column does not exist within the table '%s' (db '%s')!\n", keyfield, tablename, database);
 552                 release_table(table);
 553                 release_database(dbh);
 554                 return -1;
 555         }
 556
 557         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 558         newparam = va_arg(ap, const char *);
 559         newval = va_arg(ap, const char *);
 560         if (!newparam || !newval)  {
 561                 ast_log(LOG_WARNING, "MySQL RealTime: Realtime update requires at least 1 parameter and 1 value to update.\n");
 562                 release_table(table);
 563                 release_database(dbh);
 564                 return -1;
 565         }
 566
 567         /* Check that the column exists in the table */
 568         if (!(column = find_column(table, newparam))) {
 569                 ast_log(LOG_ERROR, "MySQL RealTime: Updating column '%s', but that column does not exist within the table '%s' (first pair MUST exist)!\n", newparam, tablename);
 570                 release_table(table);
 571                 release_database(dbh);
 572                 return -1;
 573         }
 574
 575         /* Must connect to the server before anything else, as the escape function requires the mysql handle. */
 576         if (!mysql_reconnect(dbh)) {
 577                 release_table(table);
 578                 release_database(dbh);
 579                 return -1;
 580         }
 581
 582         /* Create the first part of the query using the first parameter/value pairs we just extracted
 583            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 584
 585         ESCAPE_STRING(buf, newval);
 586         ast_str_set(&sql, 0, "UPDATE %s SET %s = '%s'", tablename, newparam, ast_str_buffer(buf));
 587
 588         /* If the column length isn't long enough, give a chance to lengthen it. */
 589         if (strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0) {
 590                 internal_require(database, tablename, newparam, RQ_CHAR, valsz, SENTINEL);
 591         }
 592
 593         while ((newparam = va_arg(ap, const char *))) {
 594                 newval = va_arg(ap, const char *);
 595
 596                 /* If the column is not within the table, then skip it */
 597                 if (!(column = find_column(table, newparam))) {
 598                         ast_log(LOG_WARNING, "Attempted to update column '%s' in table '%s', but column does not exist!\n", newparam, tablename);
 599                         continue;
 600                 }
 601
 602                 ESCAPE_STRING(buf, newval);
 603                 ast_str_append(&sql, 0, ", %s = '%s'", newparam, ast_str_buffer(buf));
 604
 605                 /* If the column length isn't long enough, give a chance to lengthen it. */
 606                 if (strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0) {
 607                         internal_require(database, tablename, newparam, RQ_CHAR, valsz, SENTINEL);
 608                 }
 609         }
 610         va_end(ap);
 611
 612         ESCAPE_STRING(buf, lookup);
 613         ast_str_append(&sql, 0, " WHERE %s = '%s'", keyfield, ast_str_buffer(buf));
 614
 615         ast_debug(1, "MySQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
 616
 617         /* Execution. */
 618         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 619                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to update database: %s\n", mysql_error(&dbh->handle));
 620                 release_table(table);
 621                 release_database(dbh);
 622                 return -1;
 623         }
 624
 625         numrows = mysql_affected_rows(&dbh->handle);
 626         release_table(table);
 627         release_database(dbh);
 628
 629         ast_debug(1, "MySQL RealTime: Updated %llu rows on table: %s\n", numrows, tablename);
 630
 631         /* From http://dev.mysql.com/doc/mysql/en/mysql-affected-rows.html
 632          * An integer greater than zero indicates the number of rows affected
 633          * Zero indicates that no records were updated
 634          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 635         */
 636
 637         return (int)numrows;
 638 }
 639
 640 static int update2_mysql(const char *database, const char *tablename, va_list ap)
 641 {
 642         struct mysql_conn *dbh;
 643         my_ulonglong numrows;
 644         int first = 1;
 645         const char *newparam, *newval;
 646         size_t valsz;
 647         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100), *buf = ast_str_thread_get(&scratch_buf, 100);
 648         struct ast_str *where = ast_str_thread_get(&sql2_buf, 100);
 649         struct tables *table;
 650         struct columns *column = NULL;
 651
 652         if (!tablename) {
 653                 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
 654                 return -1;
 655         }
 656
 657         if (!(dbh = find_database(database, 1))) {
 658                 ast_log(LOG_ERROR, "Invalid database specified: %s\n", database);
 659                 return -1;
 660         }
 661
 662         if (!(table = find_table(database, tablename))) {
 663                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
 664                 release_database(dbh);
 665                 return -1;
 666         }
 667
 668         if (!sql || !buf || !where) {
 669                 release_database(dbh);
 670                 release_table(table);
 671                 return -1;
 672         }
 673
 674         ast_str_set(&sql, 0, "UPDATE %s SET", tablename);
 675         ast_str_set(&where, 0, "WHERE");
 676
 677         /* Must connect to the server before anything else, as the escape function requires the mysql handle. */
 678         if (!mysql_reconnect(dbh)) {
 679                 release_table(table);
 680                 release_database(dbh);
 681                 return -1;
 682         }
 683
 684         while ((newparam = va_arg(ap, const char *))) {
 685                 if (!(column = find_column(table, newparam))) {
 686                         ast_log(LOG_ERROR, "Updating on column '%s', but that column does not exist within the table '%s'!\n", newparam, tablename);
 687                         release_table(table);
 688                         release_database(dbh);
 689                         return -1;
 690                 }
 691                 if (!(newval = va_arg(ap, const char *))) {
 692                         ast_log(LOG_ERROR, "Invalid arguments: no value specified for column '%s' on '%s@%s'\n", newparam, tablename, database);
 693                         release_table(table);
 694                         release_database(dbh);
 695                         return -1;
 696                 }
 697                 ESCAPE_STRING(buf, newval);
 698                 ast_str_append(&where, 0, "%s %s='%s'", first ? "" : " AND", newparam, ast_str_buffer(buf));
 699                 first = 0;
 700
 701                 /* If the column length isn't long enough, give a chance to lengthen it. */
 702                 if (strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0) {
 703                         internal_require(database, tablename, newparam, RQ_CHAR, valsz, SENTINEL);
 704                 }
 705         }
 706
 707         first = 1;
 708         while ((newparam = va_arg(ap, const char *))) {
 709                 if (!(newval = va_arg(ap, const char *))) {
 710                         ast_log(LOG_ERROR, "Invalid arguments: no value specified for column '%s' on '%s@%s'\n", newparam, tablename, database);
 711                         release_table(table);
 712                         release_database(dbh);
 713                         return -1;
 714                 }
 715
 716                 /* If the column is not within the table, then skip it */
 717                 if (!(column = find_column(table, newparam))) {
 718                         ast_log(LOG_WARNING, "Attempted to update column '%s' in table '%s', but column does not exist!\n", newparam, tablename);
 719                         continue;
 720                 }
 721
 722                 ESCAPE_STRING(buf, newval);
 723                 ast_str_append(&sql, 0, "%s %s = '%s'", first ? "" : ",", newparam, ast_str_buffer(buf));
 724
 725                 /* If the column length isn't long enough, give a chance to lengthen it. */
 726                 if (strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0) {
 727                         internal_require(database, tablename, newparam, RQ_CHAR, valsz, SENTINEL);
 728                 }
 729         }
 730         va_end(ap);
 731         release_table(table);
 732
 733         ast_str_append(&sql, 0, " %s", ast_str_buffer(where));
 734
 735         ast_debug(1, "MySQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
 736
 737         /* Execution. */
 738         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 739                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to update database: %s\n", mysql_error(&dbh->handle));
 740                 release_table(table);
 741                 release_database(dbh);
 742                 return -1;
 743         }
 744
 745         numrows = mysql_affected_rows(&dbh->handle);
 746         release_database(dbh);
 747
 748         ast_debug(1, "MySQL RealTime: Updated %llu rows on table: %s\n", numrows, tablename);
 749
 750         /* From http://dev.mysql.com/doc/mysql/en/mysql-affected-rows.html
 751          * An integer greater than zero indicates the number of rows affected
 752          * Zero indicates that no records were updated
 753          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 754         */
 755
 756         return (int)numrows;
 757 }
 758
 759 static int store_mysql(const char *database, const char *table, va_list ap)
 760 {
 761         struct mysql_conn *dbh;
 762         my_ulonglong insertid;
 763         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
 764         struct ast_str *sql2 = ast_str_thread_get(&sql2_buf, 16);
 765         struct ast_str *buf = ast_str_thread_get(&scratch_buf, 16);
 766         int valsz;
 767         const char *newparam, *newval;
 768
 769         if (!(dbh = find_database(database, 1))) {
 770                 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
 771                 return -1;
 772         }
 773
 774         if (!table) {
 775                 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
 776                 release_database(dbh);
 777                 return -1;
 778         }
 779         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 780         newparam = va_arg(ap, const char *);
 781         newval = va_arg(ap, const char *);
 782         if (!newparam || !newval)  {
 783                 ast_log(LOG_WARNING, "MySQL RealTime: Realtime storage requires at least 1 parameter and 1 value to search on.\n");
 784                 release_database(dbh);
 785                 return -1;
 786         }
 787         /* Must connect to the server before anything else, as the escape function requires the mysql handle. */
 788         if (!mysql_reconnect(dbh)) {
 789                 release_database(dbh);
 790                 return -1;
 791         }
 792         /* Create the first part of the query using the first parameter/value pairs we just extracted
 793                 If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 794         ESCAPE_STRING(buf, newval);
 795         ast_str_set(&sql, 0, "INSERT INTO %s (%s", table, newparam);
 796         ast_str_set(&sql2, 0, ") VALUES ('%s'", ast_str_buffer(buf));
 797
 798         internal_require(database, table, newparam, RQ_CHAR, valsz, SENTINEL);
 799
 800         while ((newparam = va_arg(ap, const char *))) {
 801                 if ((newval = va_arg(ap, const char *))) {
 802                         ESCAPE_STRING(buf, newval);
 803                 } else {
 804                         valsz = 0;
 805                         ast_str_reset(buf);
 806                 }
 807                 if (internal_require(database, table, newparam, RQ_CHAR, valsz, SENTINEL) == 0) {
 808                         ast_str_append(&sql, 0, ", %s", newparam);
 809                         ast_str_append(&sql2, 0, ", '%s'", ast_str_buffer(buf));
 810                 }
 811         }
 812         va_end(ap);
 813         ast_str_append(&sql, 0, "%s)", ast_str_buffer(sql2));
 814         ast_debug(1,"MySQL RealTime: Insert SQL: %s\n", ast_str_buffer(sql));
 815
 816         /* Execution. */
 817         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 818                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to insert into database: %s\n", mysql_error(&dbh->handle));
 819                 release_database(dbh);
 820                 return -1;
 821         }
 822
 823         /*!\note The return value is non-portable and may change in future versions. */
 824         insertid = mysql_insert_id(&dbh->handle);
 825         release_database(dbh);
 826
 827         ast_debug(1, "MySQL RealTime: row inserted on table: %s, id: %llu\n", table, insertid);
 828
 829         /* From http://dev.mysql.com/doc/mysql/en/mysql-affected-rows.html
 830          * An integer greater than zero indicates the number of rows affected
 831          * Zero indicates that no records were updated
 832          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 833         */
 834         return (int)insertid;
 835 }
 836
 837 static int destroy_mysql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 838 {
 839         struct mysql_conn *dbh;
 840         my_ulonglong numrows;
 841         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
 842         struct ast_str *buf = ast_str_thread_get(&scratch_buf, 16);
 843         int valsz;
 844         const char *newparam, *newval;
 845
 846         if (!(dbh = find_database(database, 1))) {
 847                 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
 848                 return -1;
 849         }
 850
 851         if (!table) {
 852                 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
 853                 release_database(dbh);
 854                 return -1;
 855         }
 856
 857         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 858         /* newparam = va_arg(ap, const char *);
 859         newval = va_arg(ap, const char *);*/
 860         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
 861                 ast_log(LOG_WARNING, "MySQL RealTime: Realtime destroying requires at least 1 parameter and 1 value to search on.\n");
 862                 release_database(dbh);
 863                 return -1;
 864         }
 865
 866         /* Must connect to the server before anything else, as the escape function requires the mysql handle. */
 867         if (!mysql_reconnect(dbh)) {
 868                 release_database(dbh);
 869                 return -1;
 870         }
 871
 872         /* Create the first part of the query using the first parameter/value pairs we just extracted
 873            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 874         ESCAPE_STRING(buf, lookup);
 875         ast_str_set(&sql, 0, "DELETE FROM %s WHERE %s = '%s'", table, keyfield, ast_str_buffer(buf));
 876         while ((newparam = va_arg(ap, const char *))) {
 877                 newval = va_arg(ap, const char *);
 878                 ESCAPE_STRING(buf, newval);
 879                 ast_str_append(&sql, 0, " AND %s = '%s'", newparam, ast_str_buffer(buf));
 880         }
 881         va_end(ap);
 882
 883         ast_debug(1, "MySQL RealTime: Delete SQL: %s\n", ast_str_buffer(sql));
 884
 885         /* Execution. */
 886         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 887                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to delete from database: %s\n", mysql_error(&dbh->handle));
 888                 release_database(dbh);
 889                 return -1;
 890         }
 891
 892         numrows = mysql_affected_rows(&dbh->handle);
 893         release_database(dbh);
 894
 895         ast_debug(1, "MySQL RealTime: Deleted %llu rows on table: %s\n", numrows, table);
 896
 897         /* From http://dev.mysql.com/doc/mysql/en/mysql-affected-rows.html
 898          * An integer greater than zero indicates the number of rows affected
 899          * Zero indicates that no records were updated
 900          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 901         */
 902
 903         return (int)numrows;
 904 }
 905
 906 static struct ast_config *config_mysql(const char *database, const char *table, const char *file, struct ast_config *cfg, struct ast_flags config_flags, const char *unused, const char *who_asked)
 907 {
 908         struct mysql_conn *dbh;
 909         MYSQL_RES *result;
 910         MYSQL_ROW row;
 911         my_ulonglong num_rows;
 912         struct ast_variable *new_v;
 913         struct ast_category *cur_cat = NULL;
 914         struct ast_str *sql = ast_str_thread_get(&sql_buf, 200);
 915         char last[80] = "";
 916         int last_cat_metric = 0;
 917
 918         ast_clear_flag(&config_flags, CONFIG_FLAG_FILEUNCHANGED);
 919
 920         if (!file || !strcmp(file, RES_CONFIG_MYSQL_CONF)) {
 921                 ast_log(LOG_WARNING, "MySQL RealTime: Cannot configure myself.\n");
 922                 return NULL;
 923         }
 924
 925         if (!(dbh = find_database(database, 0))) {
 926                 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
 927                 return NULL;
 928         }
 929
 930         ast_str_set(&sql, 0, "SELECT category, var_name, var_val, cat_metric FROM %s WHERE filename='%s' and commented=1 ORDER BY filename, cat_metric desc, var_metric asc, category, var_name, var_val, id", table, file);
 931
 932         ast_debug(1, "MySQL RealTime: Static SQL: %s\n", ast_str_buffer(sql));
 933
 934         /* We now have our complete statement; Lets connect to the server and execute it. */
 935         if (!mysql_reconnect(dbh)) {
 936                 return NULL;
 937         }
 938
 939         if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
 940                 ast_log(LOG_WARNING, "MySQL RealTime: Failed to query database. Check debug for more info.\n");
 941                 ast_debug(1, "MySQL RealTime: Query: %s\n", ast_str_buffer(sql));
 942                 ast_debug(1, "MySQL RealTime: Query Failed because: %s\n", mysql_error(&dbh->handle));
 943                 release_database(dbh);
 944                 return NULL;
 945         }
 946
 947         if ((result = mysql_store_result(&dbh->handle))) {
 948                 num_rows = mysql_num_rows(result);
 949                 ast_debug(1, "MySQL RealTime: Found %llu rows.\n", num_rows);
 950
 951                 /* There might exist a better way to access the column names other than counting,
 952                  * but I believe that would require another loop that we don't need. */
 953
 954                 while ((row = mysql_fetch_row(result))) {
 955                         if (!strcmp(row[1], "#include")) {
 956                                 if (!ast_config_internal_load(row[2], cfg, config_flags, "", who_asked)) {
 957                                         mysql_free_result(result);
 958                                         release_database(dbh);
 959                                         ast_config_destroy(cfg);
 960                                         return NULL;
 961                                 }
 962                                 continue;
 963                         }
 964
 965                         if (strcmp(last, row[0]) || last_cat_metric != atoi(row[3])) {
 966                                 if (!(cur_cat = ast_category_new(row[0], "", -1))) {
 967                                         ast_log(LOG_WARNING, "Out of memory!\n");
 968                                         break;
 969                                 }
 970                                 strcpy(last, row[0]);
 971                                 last_cat_metric = atoi(row[3]);
 972                                 ast_category_append(cfg, cur_cat);
 973                         }
 974                         new_v = ast_variable_new(row[1], row[2], "");
 975                         if (cur_cat)
 976                                 ast_variable_append(cur_cat, new_v);
 977                 }
 978         } else {
 979                 ast_log(LOG_WARNING, "MySQL RealTime: Could not find config '%s' in database.\n", file);
 980         }
 981
 982         mysql_free_result(result);
 983         release_database(dbh);
 984
 985         return cfg;
 986 }
 987
 988 static int unload_mysql(const char *database, const char *tablename)
 989 {
 990         struct tables *cur;
 991         AST_LIST_LOCK(&mysql_tables);
 992         AST_LIST_TRAVERSE_SAFE_BEGIN(&mysql_tables, cur, list) {
 993                 if (strcmp(cur->name, tablename) == 0) {
 994                         AST_LIST_REMOVE_CURRENT(list);
 995                         destroy_table(cur);
 996                         break;
 997                 }
 998         }
 999         AST_LIST_TRAVERSE_SAFE_END
1000         AST_LIST_UNLOCK(&mysql_tables);
1001         return cur ? 0 : -1;
1002 }
1003
1004 static int modify_mysql(const char *database, const char *tablename, struct columns *column, require_type type, int len)
1005 {
1006         /*!\note Cannot use ANY of the same scratch space as is used in other functions, as this one is interspersed. */
1007         struct ast_str *sql = ast_str_thread_get(&modify_buf, 100), *escbuf = ast_str_thread_get(&modify2_buf, 100);
1008         struct ast_str *typestr = ast_str_thread_get(&modify3_buf, 30);
1009         int waschar = strncasecmp(column->type, "char", 4) == 0 ? 1 : 0;
1010         int wasvarchar = strncasecmp(column->type, "varchar", 7) == 0 ? 1 : 0;
1011         int res = 0;
1012         struct mysql_conn *dbh;
1013
1014         if (!(dbh = find_database(database, 1))) {
1015                 return -1;
1016         }
1017
1018         do {
1019                 if (type == RQ_CHAR || waschar || wasvarchar) {
1020                         if (wasvarchar) {
1021                                 ast_str_set(&typestr, 0, "VARCHAR(%d)", len);
1022                         } else {
1023                                 ast_str_set(&typestr, 0, "CHAR(%d)", len);
1024                         }
1025                 } else if (type == RQ_UINTEGER1) {
1026                         ast_str_set(&typestr, 0, "tinyint(3) unsigned");
1027                 } else if (type == RQ_INTEGER1) {
1028                         ast_str_set(&typestr, 0, "tinyint(4)");
1029                 } else if (type == RQ_UINTEGER2) {
1030                         ast_str_set(&typestr, 0, "smallint(5) unsigned");
1031                 } else if (type == RQ_INTEGER2) {
1032                         ast_str_set(&typestr, 0, "smallint(6)");
1033                 } else if (type == RQ_UINTEGER3) {
1034                         ast_str_set(&typestr, 0, "mediumint(8) unsigned");
1035                 } else if (type == RQ_INTEGER3) {
1036                         ast_str_set(&typestr, 0, "mediumint(8)");
1037                 } else if (type == RQ_UINTEGER4) {
1038                         ast_str_set(&typestr, 0, "int(10) unsigned");
1039                 } else if (type == RQ_INTEGER4) {
1040                         ast_str_set(&typestr, 0, "int(11)");
1041                 } else if (type == RQ_UINTEGER8) {
1042                         ast_str_set(&typestr, 0, "bigint(19) unsigned");
1043                 } else if (type == RQ_INTEGER8) {
1044                         ast_str_set(&typestr, 0, "bigint(20)");
1045                 } else if (type == RQ_DATETIME) {
1046                         ast_str_set(&typestr, 0, "datetime");
1047                 } else if (type == RQ_DATE) {
1048                         ast_str_set(&typestr, 0, "date");
1049                 } else if (type == RQ_FLOAT) {
1050                         ast_str_set(&typestr, 0, "FLOAT(%d,2)", len);
1051                 } else {
1052                         ast_log(LOG_ERROR, "Unknown type (should NEVER happen)\n");
1053                         res = -1;
1054                         break;
1055                 }
1056                 ast_str_set(&sql, 0, "ALTER TABLE %s MODIFY %s %s", tablename, column->name, ast_str_buffer(typestr));
1057                 if (!column->null) {
1058                         ast_str_append(&sql, 0, " NOT NULL");
1059                 }
1060                 if (!ast_strlen_zero(column->dflt)) {
1061                         size_t valsz;
1062                         ESCAPE_STRING(escbuf, column->dflt);
1063                         ast_str_append(&sql, 0, " DEFAULT '%s'", ast_str_buffer(escbuf));
1064                 }
1065
1066                 if (!mysql_reconnect(dbh)) {
1067                         ast_log(LOG_ERROR, "Unable to add column: %s\n", ast_str_buffer(sql));
1068                         res = -1;
1069                         break;
1070                 }
1071
1072                 /* Execution. */
1073                 if (mysql_real_query(&dbh->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
1074                         ast_log(LOG_WARNING, "MySQL RealTime: Failed to modify database: %s\n", mysql_error(&dbh->handle));
1075                         ast_debug(1, "MySQL RealTime: Query: %s\n", ast_str_buffer(sql));
1076                         res = -1;
1077                 }
1078         } while (0);
1079
1080         release_database(dbh);
1081         return res;
1082 }
1083
1084 #define PICK_WHICH_ALTER_ACTION(stringtype) \
1085         if (table->database->requirements == RQ_WARN) {                                                                       \
1086                 ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' may not be large enough for "            \
1087                         "the required data length: %d (detected stringtype)\n",                                      \
1088                         tablename, database, column->name, size);                                                    \
1089                 res = -1;                                                                                        \
1090         } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {     \
1091                 table_altered = 1;                                                                               \
1092         } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {   \
1093                 table_altered = 1;                                                                               \
1094         } else {                                                                                             \
1095                 res = -1;                                                                                        \
1096         }
1097
1098 static int require_mysql(const char *database, const char *tablename, va_list ap)
1099 {
1100         struct columns *column;
1101         struct tables *table = find_table(database, tablename);
1102         char *elm;
1103         int type, size, res = 0, table_altered = 0;
1104
1105         if (!table) {
1106                 ast_log(LOG_WARNING, "Table %s not found in database.  This table should exist if you're using realtime.\n", tablename);
1107                 return -1;
1108         }
1109
1110         while ((elm = va_arg(ap, char *))) {
1111                 type = va_arg(ap, require_type);
1112                 size = va_arg(ap, int);
1113                 AST_LIST_TRAVERSE(&table->columns, column, list) {
1114                         if (strcmp(column->name, elm) == 0) {
1115                                 /* Char can hold anything, as long as it is large enough */
1116                                 if (strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0) {
1117                                         if ((size > column->len) && column->len != -1) {
1118                                                 if (table->database->requirements == RQ_WARN) {
1119                                                         ast_log(LOG_WARNING, "Realtime table %s@%s: Column '%s' should be at least %d long, but is only %d long.\n", database, tablename, column->name, size, column->len);
1120                                                         res = -1;
1121                                                 } else if (modify_mysql(database, tablename, column, type, size) == 0) {
1122                                                         table_altered = 1;
1123                                                 } else {
1124                                                         res = -1;
1125                                                 }
1126                                         }
1127                                 } else if (strcasestr(column->type, "unsigned")) {
1128                                         if (!ast_rq_is_int(type)) {
1129                                                 if (table->database->requirements == RQ_WARN) {
1130                                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' cannot be type '%s' (need %s)\n",
1131                                                                 database, tablename, column->name, column->type,
1132                                                                 type == RQ_CHAR ? "char" : type == RQ_FLOAT ? "float" :
1133                                                                 type == RQ_DATETIME ? "datetime" : type == RQ_DATE ? "date" : "a rather stiff drink");
1134                                                         res = -1;
1135                                                 } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {
1136                                                         table_altered = 1;
1137                                                 } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {
1138                                                         table_altered = 1;
1139                                                 } else {
1140                                                         res = -1;
1141                                                 }
1142                                         } else if (strncasecmp(column->type, "tinyint", 1) == 0) {
1143                                                 if (type != RQ_UINTEGER1) {
1144                                                         PICK_WHICH_ALTER_ACTION(unsigned tinyint)
1145                                                 }
1146                                         } else if (strncasecmp(column->type, "smallint", 1) == 0) {
1147                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
1148                                                         PICK_WHICH_ALTER_ACTION(unsigned smallint)
1149                                                 }
1150                                         } else if (strncasecmp(column->type, "mediumint", 1) == 0) {
1151                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1152                                                         type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1153                                                         type != RQ_UINTEGER3) {
1154                                                         PICK_WHICH_ALTER_ACTION(unsigned mediumint)
1155                                                 }
1156                                         } else if (strncasecmp(column->type, "int", 1) == 0) {
1157                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1158                                                         type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1159                                                         type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1160                                                         type != RQ_UINTEGER4) {
1161                                                         PICK_WHICH_ALTER_ACTION(unsigned int)
1162                                                 }
1163                                         } else if (strncasecmp(column->type, "bigint", 1) == 0) {
1164                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1165                                                         type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1166                                                         type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1167                                                         type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1168                                                         type != RQ_UINTEGER8) {
1169                                                         PICK_WHICH_ALTER_ACTION(unsigned bigint)
1170                                                 }
1171                                         }
1172                                 } else if (strcasestr(column->type, "int")) {
1173                                         if (!ast_rq_is_int(type)) {
1174                                                 if (table->database->requirements == RQ_WARN) {
1175                                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' cannot be type '%s' (need %s)\n",
1176                                                                 database, tablename, column->name, column->type,
1177                                                                 type == RQ_CHAR ? "char" : type == RQ_FLOAT ? "float" :
1178                                                                 type == RQ_DATETIME ? "datetime" : type == RQ_DATE ? "date" :
1179                                                                 "to get a life, rather than writing silly error messages");
1180                                                         res = -1;
1181                                                 } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {
1182                                                         table_altered = 1;
1183                                                 } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {
1184                                                         table_altered = 1;
1185                                                 } else {
1186                                                         res = -1;
1187                                                 }
1188                                         } else if (strncasecmp(column->type, "tinyint", 1) == 0) {
1189                                                 if (type != RQ_INTEGER1) {
1190                                                         PICK_WHICH_ALTER_ACTION(tinyint)
1191                                                 }
1192                                         } else if (strncasecmp(column->type, "smallint", 1) == 0) {
1193                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
1194                                                         PICK_WHICH_ALTER_ACTION(smallint)
1195                                                 }
1196                                         } else if (strncasecmp(column->type, "mediumint", 1) == 0) {
1197                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1198                                                         type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1199                                                         type != RQ_INTEGER3) {
1200                                                         PICK_WHICH_ALTER_ACTION(mediumint)
1201                                                 }
1202                                         } else if (strncasecmp(column->type, "int", 1) == 0) {
1203                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1204                                                         type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1205                                                         type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1206                                                         type != RQ_INTEGER4) {
1207                                                         PICK_WHICH_ALTER_ACTION(int)
1208                                                 }
1209                                         } else if (strncasecmp(column->type, "bigint", 1) == 0) {
1210                                                 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1211                                                         type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1212                                                         type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1213                                                         type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1214                                                         type != RQ_INTEGER8) {
1215                                                         PICK_WHICH_ALTER_ACTION(bigint)
1216                                                 }
1217                                         }
1218                                 } else if (strncmp(column->type, "float", 5) == 0 && !ast_rq_is_int(type) && type != RQ_FLOAT) {
1219                                         if (table->database->requirements == RQ_WARN) {
1220                                                 ast_log(LOG_WARNING, "Realtime table %s@%s: Column %s cannot be a %s\n", tablename, database, column->name, column->type);
1221                                                 res = -1;
1222                                         } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {
1223                                                 table_altered = 1;
1224                                         } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {
1225                                                 table_altered = 1;
1226                                         } else {
1227                                                 res = -1;
1228                                         }
1229                                 } else if ((strncmp(column->type, "datetime", 8) == 0 || strncmp(column->type, "timestamp", 9)) && type != RQ_DATETIME) {
1230                                         if (table->database->requirements == RQ_WARN) {
1231                                                 ast_log(LOG_WARNING, "Realtime table %s@%s: Column %s cannot be a %s\n", tablename, database, column->name, column->type);
1232                                                 res = -1;
1233                                         } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {
1234                                                 table_altered = 1;
1235                                         } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {
1236                                                 table_altered = 1;
1237                                         } else {
1238                                                 res = -1;
1239                                         }
1240                                 } else if ((strncmp(column->type, "date", 4) == 0) && type != RQ_DATE) {
1241                                         if (table->database->requirements == RQ_WARN) {
1242                                                 ast_log(LOG_WARNING, "Realtime table %s@%s: Column %s cannot be a %s\n", tablename, database, column->name, column->type);
1243                                                 res = -1;
1244                                         } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {
1245                                                 table_altered = 1;
1246                                         } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {
1247                                                 table_altered = 1;
1248                                         } else {
1249                                                 res = -1;
1250                                         }
1251                                 } else { /* Other, possibly unsupported types? */
1252                                         if (table->database->requirements == RQ_WARN) {
1253                                                 ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
1254                                                 res = -1;
1255                                         } else if (table->database->requirements == RQ_CREATECLOSE && modify_mysql(database, tablename, column, type, size) == 0) {
1256                                                 table_altered = 1;
1257                                         } else if (table->database->requirements == RQ_CREATECHAR && modify_mysql(database, tablename, column, RQ_CHAR, size) == 0) {
1258                                                 table_altered = 1;
1259                                         } else {
1260                                         }
1261                                 }
1262                                 break;
1263                         }
1264                 }
1265
1266                 if (!column) {
1267                         if (table->database->requirements == RQ_WARN) {
1268                                 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
1269                         } else {
1270                                 struct ast_str *sql = ast_str_thread_get(&modify_buf, 100), *fieldtype = ast_str_thread_get(&modify3_buf, 16);
1271
1272                                 if (table->database->requirements == RQ_CREATECHAR || type == RQ_CHAR) {
1273                                         ast_str_set(&fieldtype, 0, "CHAR(%d)", size);
1274                                 } else if (type == RQ_UINTEGER1 || type == RQ_UINTEGER2 || type == RQ_UINTEGER3 || type == RQ_UINTEGER4 || type == RQ_UINTEGER8) {
1275                                         if (type == RQ_UINTEGER1) {
1276                                                 ast_str_set(&fieldtype, 0, "TINYINT(3) UNSIGNED");
1277                                         } else if (type == RQ_UINTEGER2) {
1278                                                 ast_str_set(&fieldtype, 0, "SMALLINT(5) UNSIGNED");
1279                                         } else if (type == RQ_UINTEGER3) {
1280                                                 ast_str_set(&fieldtype, 0, "MEDIUMINT(8) UNSIGNED");
1281                                         } else if (type == RQ_UINTEGER4) {
1282                                                 ast_str_set(&fieldtype, 0, "INT(10) UNSIGNED");
1283                                         } else if (type == RQ_UINTEGER8) {
1284                                                 ast_str_set(&fieldtype, 0, "BIGINT(20) UNSIGNED");
1285                                         } else {
1286                                                 ast_log(LOG_WARNING, "Somebody should check this code for a rather large bug... it's about to squash Tokyo.\n");
1287                                                 continue;
1288                                         }
1289                                 } else if (ast_rq_is_int(type)) {
1290                                         if (type == RQ_INTEGER1) {
1291                                                 ast_str_set(&fieldtype, 0, "TINYINT(3)");
1292                                         } else if (type == RQ_INTEGER2) {
1293                                                 ast_str_set(&fieldtype, 0, "SMALLINT(5)");
1294                                         } else if (type == RQ_INTEGER3) {
1295                                                 ast_str_set(&fieldtype, 0, "MEDIUMINT(8)");
1296                                         } else if (type == RQ_INTEGER4) {
1297                                                 ast_str_set(&fieldtype, 0, "INT(10)");
1298                                         } else if (type == RQ_INTEGER8) {
1299                                                 ast_str_set(&fieldtype, 0, "BIGINT(20)");
1300                                         } else {
1301                                                 ast_log(LOG_WARNING, "Somebody should check this code for a rather large bug... it's about to eat Cincinnati.\n");
1302                                                 continue;
1303                                         }
1304                                 } else if (type == RQ_FLOAT) {
1305                                         ast_str_set(&fieldtype, 0, "FLOAT");
1306                                 } else if (type == RQ_DATE) {
1307                                         ast_str_set(&fieldtype, 0, "DATE");
1308                                 } else if (type == RQ_DATETIME) {
1309                                         ast_str_set(&fieldtype, 0, "DATETIME");
1310                                 } else {
1311                                         continue;
1312                                 }
1313                                 ast_str_set(&sql, 0, "ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, ast_str_buffer(fieldtype));
1314
1315                                 ast_mutex_lock(&table->database->lock);
1316                                 if (!mysql_reconnect(table->database)) {
1317                                         ast_mutex_unlock(&table->database->lock);
1318                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", ast_str_buffer(sql));
1319                                         continue;
1320                                 }
1321
1322                                 /* Execution. */
1323                                 if (mysql_real_query(&table->database->handle, ast_str_buffer(sql), ast_str_strlen(sql))) {
1324                                         ast_log(LOG_WARNING, "MySQL RealTime: Failed to query database. Check debug for more info.\n");
1325                                         ast_debug(1, "MySQL RealTime: Query: %s\n", ast_str_buffer(sql));
1326                                         ast_debug(1, "MySQL RealTime: Query Failed because: %s\n", mysql_error(&table->database->handle));
1327                                 } else {
1328                                         table_altered = 1;
1329                                 }
1330                         }
1331                 }
1332         }
1333         release_table(table);
1334
1335         /* If we altered the table, we must refresh the cache */
1336         if (table_altered) {
1337                 unload_mysql(database, tablename);
1338                 release_table(find_table(database, tablename));
1339         }
1340         return res;
1341 }
1342
1343 static struct ast_config_engine mysql_engine = {
1344         .name = "mysql",
1345         .load_func = config_mysql,
1346         .realtime_func = realtime_mysql,
1347         .realtime_multi_func = realtime_multi_mysql,
1348         .store_func = store_mysql,
1349         .destroy_func = destroy_mysql,
1350         .update_func = update_mysql,
1351         .update2_func = update2_mysql,
1352         .require_func = require_mysql,
1353         .unload_func = unload_mysql,
1354 };
1355
1356 static int load_module(void)
1357 {
1358         parse_config(0);
1359
1360         ast_config_engine_register(&mysql_engine);
1361         if (option_verbose > 1)
1362                 ast_verbose(VERBOSE_PREFIX_2 "MySQL RealTime driver loaded.\n");
1363         ast_cli_register_multiple(cli_realtime_mysql_status, sizeof(cli_realtime_mysql_status) / sizeof(struct ast_cli_entry));
1364         return 0;
1365 }
1366
1367 static int unload_module(void)
1368 {
1369         struct mysql_conn *cur;
1370         struct tables *table;
1371
1372         ast_cli_unregister_multiple(cli_realtime_mysql_status, sizeof(cli_realtime_mysql_status) / sizeof(struct ast_cli_entry));
1373         ast_config_engine_deregister(&mysql_engine);
1374         if (option_verbose > 1)
1375                 ast_verbose(VERBOSE_PREFIX_2 "MySQL RealTime unloaded.\n");
1376
1377         ast_module_user_hangup_all();
1378
1379         usleep(1);
1380         AST_RWLIST_WRLOCK(&databases);
1381         while ((cur = AST_RWLIST_REMOVE_HEAD(&databases, list))) {
1382                 mysql_close(&cur->handle);
1383                 ast_mutex_destroy(&cur->lock);
1384                 ast_free(cur);
1385         }
1386         AST_RWLIST_UNLOCK(&databases);
1387
1388         /* Destroy cached table info */
1389         AST_LIST_LOCK(&mysql_tables);
1390         while ((table = AST_LIST_REMOVE_HEAD(&mysql_tables, list))) {
1391                 destroy_table(table);
1392         }
1393         AST_LIST_UNLOCK(&mysql_tables);
1394
1395         return 0;
1396 }
1397
1398 static int reload(void)
1399 {
1400         parse_config(1);
1401
1402         if (option_verbose > 1) {
1403                 ast_verb(2, "MySQL RealTime reloaded.\n");
1404         }
1405
1406         return 0;
1407 }
1408
1409 static int parse_config(int reload)
1410 {
1411         struct ast_config *config = NULL;
1412         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
1413         const char *catg;
1414         struct mysql_conn *cur;
1415
1416         if ((config = ast_config_load(RES_CONFIG_MYSQL_CONF, config_flags)) == CONFIG_STATUS_FILEMISSING) {
1417                 /* Support old config file name */
1418                 config = ast_config_load(RES_CONFIG_MYSQL_CONF_OLD, config_flags);
1419         }
1420
1421         if (config == CONFIG_STATUS_FILEMISSING) {
1422                 return 0;
1423         } else if (config == CONFIG_STATUS_FILEUNCHANGED) {
1424                 return 0;
1425         } else if (config == CONFIG_STATUS_FILEINVALID) {
1426                 ast_log(LOG_ERROR, "Not %sloading " RES_CONFIG_MYSQL_CONF "\n", reload ? "re" : "");
1427         }
1428
1429         AST_RWLIST_WRLOCK(&databases);
1430         for (catg = ast_category_browse(config, NULL); catg; catg = ast_category_browse(config, catg)) {
1431                 /* Does this category already exist? */
1432                 AST_RWLIST_TRAVERSE(&databases, cur, list) {
1433                         if (!strcmp(cur->unique_name, catg)) {
1434                                 break;
1435                         }
1436                 }
1437
1438                 if (!cur) {
1439                         if (!(cur = ast_calloc(1, sizeof(*cur) + strlen(catg) + 1))) {
1440                                 ast_log(LOG_WARNING, "Could not allocate space for MySQL database '%s'\n", catg);
1441                                 continue;
1442                         }
1443
1444                         strcpy(cur->unique_name, catg); /* SAFE */
1445                         ast_mutex_init(&cur->lock);
1446                         AST_RWLIST_INSERT_TAIL(&databases, cur, list);
1447                 }
1448
1449                 load_mysql_config(config, catg, cur);
1450         }
1451         AST_RWLIST_UNLOCK(&databases);
1452
1453         ast_config_destroy(config);
1454
1455         return 0;
1456 }
1457
1458 static int load_mysql_config(struct ast_config *config, const char *category, struct mysql_conn *conn)
1459 {
1460         const char *s;
1461
1462         if (!(s = ast_variable_retrieve(config, category, "dbuser"))) {
1463                 ast_log(LOG_WARNING, "MySQL RealTime: No database user found, using 'asterisk' as default.\n");
1464                 s = "asterisk";
1465         }
1466         ast_copy_string(conn->user, s, sizeof(conn->user));
1467
1468         if (!(s = ast_variable_retrieve(config, category, "dbpass"))) {
1469                 ast_log(LOG_WARNING, "MySQL RealTime: No database password found, using 'asterisk' as default.\n");
1470                 s = "asterisk";
1471         }
1472         ast_copy_string(conn->pass, s, sizeof(conn->pass));
1473
1474         if (!(s = ast_variable_retrieve(config, category, "dbhost"))) {
1475                 ast_log(LOG_WARNING, "MySQL RealTime: No database host found, using localhost via socket.\n");
1476                 s = "";
1477         }
1478         ast_copy_string(conn->host, s, sizeof(conn->host));
1479
1480         if (!(s = ast_variable_retrieve(config, category, "dbname"))) {
1481                 ast_log(LOG_WARNING, "MySQL RealTime: No database name found, using 'asterisk' as default.\n");
1482                 s = "asterisk";
1483         }
1484         ast_copy_string(conn->name, s, sizeof(conn->name));
1485
1486         if (!(s = ast_variable_retrieve(config, category, "dbport"))) {
1487                 ast_log(LOG_WARNING, "MySQL RealTime: No database port found, using 3306 as default.\n");
1488                 conn->port = 3306;
1489         } else
1490                 conn->port = atoi(s);
1491
1492         if (!(s = ast_variable_retrieve(config, category, "dbsock"))) {
1493                 if (ast_strlen_zero(conn->host)) {
1494                         char *paths[3] = { "/tmp/mysql.sock", "/var/lib/mysql/mysql.sock", "/var/run/mysqld/mysqld.sock" };
1495                         struct stat st;
1496                         int i;
1497                         for (i = 0; i < 3; i++) {
1498                                 if (!stat(paths[i], &st)) {
1499                                         ast_log(LOG_WARNING, "MySQL RealTime: No database socket found, using '%s' as default.\n", paths[i]);
1500                                         ast_copy_string(conn->sock, paths[i], sizeof(conn->sock));
1501                                 }
1502                         }
1503                         if (i == 3) {
1504                                 ast_log(LOG_WARNING, "MySQL RealTime: No database socket found (and unable to detect a suitable path).\n");
1505                                 return 0;
1506                         }
1507                 }
1508         } else
1509                 ast_copy_string(conn->sock, s, sizeof(conn->sock));
1510
1511         if (!(s = ast_variable_retrieve(config, category, "requirements"))) {
1512                 ast_log(LOG_WARNING, "MySQL realtime: no requirements setting found, using 'warn' as default.\n");
1513                 conn->requirements = RQ_WARN;
1514         } else if (!strcasecmp(s, "createclose")) {
1515                 conn->requirements = RQ_CREATECLOSE;
1516         } else if (!strcasecmp(s, "createchar")) {
1517                 conn->requirements = RQ_CREATECHAR;
1518         } else if (!strcasecmp(s, "warn")) {
1519                 conn->requirements = RQ_WARN;
1520         } else {
1521                 ast_log(LOG_WARNING, "MySQL realtime: unrecognized requirements setting '%s', using 'warn'\n", s);
1522                 conn->requirements = RQ_WARN;
1523         }
1524
1525         if (!ast_strlen_zero(conn->host)) {
1526                 ast_debug(1, "MySQL RealTime host: %s\n", conn->host);
1527                 ast_debug(1, "MySQL RealTime port: %i\n", conn->port);
1528         } else
1529                 ast_debug(1, "MySQL RealTime socket: %s\n", conn->sock);
1530         ast_debug(1, "MySQL RealTime database name: %s\n", conn->name);
1531         ast_debug(1, "MySQL RealTime user: %s\n", conn->user);
1532         ast_debug(1, "MySQL RealTime password: %s\n", conn->pass);
1533
1534         return 1;
1535 }
1536
1537 static int mysql_reconnect(struct mysql_conn *conn)
1538 {
1539 #ifdef MYSQL_OPT_RECONNECT
1540         my_bool trueval = 1;
1541 #endif
1542
1543         /* mutex lock should have been locked before calling this function. */
1544
1545 reconnect_tryagain:
1546         if ((!conn->connected) && (!ast_strlen_zero(conn->host) || conn->sock) && !ast_strlen_zero(conn->user) && !ast_strlen_zero(conn->name)) {
1547                 if (!mysql_init(&conn->handle)) {
1548                         ast_log(LOG_WARNING, "MySQL RealTime: Insufficient memory to allocate MySQL resource.\n");
1549                         conn->connected = 0;
1550                         return 0;
1551                 }
1552                 if (mysql_real_connect(&conn->handle, conn->host, conn->user, conn->pass, conn->name, conn->port, conn->sock, 0)) {
1553 #ifdef MYSQL_OPT_RECONNECT
1554                         /* The default is no longer to automatically reconnect on failure,
1555                          * (as of 5.0.3) so we have to set that option here. */
1556                         mysql_options(&conn->handle, MYSQL_OPT_RECONNECT, &trueval);
1557 #endif
1558                         ast_debug(1, "MySQL RealTime: Successfully connected to database.\n");
1559                         conn->connected = 1;
1560                         conn->connect_time = time(NULL);
1561                         return 1;
1562                 } else {
1563                         ast_log(LOG_ERROR, "MySQL RealTime: Failed to connect database server %s on %s (err %d). Check debug for more info.\n", conn->name, !ast_strlen_zero(conn->host) ? conn->host : conn->sock, mysql_errno(&conn->handle));
1564                         ast_debug(1, "MySQL RealTime: Cannot Connect (%d): %s\n", mysql_errno(&conn->handle), mysql_error(&conn->handle));
1565                         conn->connected = 0;
1566                         conn->connect_time = 0;
1567                         return 0;
1568                 }
1569         } else {
1570                 /* MySQL likes to return an error, even if it reconnects successfully.
1571                  * So the postman pings twice. */
1572                 if (mysql_ping(&conn->handle) != 0 && (usleep(1) + 2 > 0) && mysql_ping(&conn->handle) != 0) {
1573                         conn->connected = 0;
1574                         conn->connect_time = 0;
1575                         ast_log(LOG_ERROR, "MySQL RealTime: Ping failed (%d).  Trying an explicit reconnect.\n", mysql_errno(&conn->handle));
1576                         ast_debug(1, "MySQL RealTime: Server Error (%d): %s\n", mysql_errno(&conn->handle), mysql_error(&conn->handle));
1577                         goto reconnect_tryagain;
1578                 }
1579
1580                 if (!conn->connected) {
1581                         conn->connected = 1;
1582                         conn->connect_time = time(NULL);
1583                 }
1584
1585                 if (mysql_select_db(&conn->handle, conn->name) != 0) {
1586                         ast_log(LOG_WARNING, "MySQL RealTime: Unable to select database: %s. Still Connected (%u) - %s.\n", conn->name, mysql_errno(&conn->handle), mysql_error(&conn->handle));
1587                         return 0;
1588                 }
1589
1590                 ast_debug(1, "MySQL RealTime: Connection okay.\n");
1591                 return 1;
1592         }
1593 }
1594
1595 static char *handle_cli_realtime_mysql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1596 {
1597         struct tables *cur;
1598         int l, which;
1599         char *ret = NULL;
1600
1601         switch (cmd) {
1602         case CLI_INIT:
1603                 e->command = "realtime mysql cache";
1604                 e->usage =
1605                         "Usage: realtime mysql cache [<database> <table>]\n"
1606                         "       Shows table cache for the MySQL RealTime driver\n";
1607                 return NULL;
1608         case CLI_GENERATE:
1609                 if (a->argc < 4 || a->argc > 5) {
1610                         return NULL;
1611                 }
1612                 l = strlen(a->word);
1613                 which = 0;
1614                 if (a->argc == 5) {
1615                         AST_LIST_LOCK(&mysql_tables);
1616                         AST_LIST_TRAVERSE(&mysql_tables, cur, list) {
1617                                 if (!strcasecmp(a->argv[3], cur->database->unique_name) && !strncasecmp(a->word, cur->name, l) && ++which > a->n) {
1618                                         ret = ast_strdup(cur->name);
1619                                         break;
1620                                 }
1621                         }
1622                         AST_LIST_UNLOCK(&mysql_tables);
1623                 } else {
1624                         struct mysql_conn *cur;
1625                         AST_RWLIST_RDLOCK(&databases);
1626                         AST_RWLIST_TRAVERSE(&databases, cur, list) {
1627                                 if (!strncasecmp(a->word, cur->unique_name, l) && ++which > a->n) {
1628                                         ret = ast_strdup(cur->unique_name);
1629                                         break;
1630                                 }
1631                         }
1632                         AST_RWLIST_UNLOCK(&databases);
1633                 }
1634                 return ret;
1635         }
1636
1637         if (a->argc == 3) {
1638                 /* List of tables */
1639                 AST_LIST_LOCK(&mysql_tables);
1640                 AST_LIST_TRAVERSE(&mysql_tables, cur, list) {
1641                         ast_cli(a->fd, "%20.20s %s\n", cur->database->unique_name, cur->name);
1642                 }
1643                 AST_LIST_UNLOCK(&mysql_tables);
1644         } else if (a->argc == 4) {
1645                 int found = 0;
1646                 /* List of tables */
1647                 AST_LIST_LOCK(&mysql_tables);
1648                 AST_LIST_TRAVERSE(&mysql_tables, cur, list) {
1649                         if (!strcasecmp(cur->database->unique_name, a->argv[3])) {
1650                                 ast_cli(a->fd, "%s\n", cur->name);
1651                                 found = 1;
1652                         }
1653                 }
1654                 AST_LIST_UNLOCK(&mysql_tables);
1655                 if (!found) {
1656                         ast_cli(a->fd, "No tables cached within %s database\n", a->argv[3]);
1657                 }
1658         } else if (a->argc == 5) {
1659                 /* List of columns */
1660                 if ((cur = find_table(a->argv[3], a->argv[4]))) {
1661                         struct columns *col;
1662                         ast_cli(a->fd, "Columns for Table Cache '%s':\n", a->argv[3]);
1663                         ast_cli(a->fd, "%-20.20s %-20.20s %-3.3s\n", "Name", "Type", "Len");
1664                         AST_LIST_TRAVERSE(&cur->columns, col, list) {
1665                                 ast_cli(a->fd, "%-20.20s %-20.20s %3d\n", col->name, col->type, col->len);
1666                         }
1667                         release_table(cur);
1668                 } else {
1669                         ast_cli(a->fd, "No such table '%s'\n", a->argv[3]);
1670                 }
1671         }
1672         return CLI_SUCCESS;
1673 }
1674
1675 static char *handle_cli_realtime_mysql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1676 {
1677         char status[256], status2[100] = "", type[20];
1678         char *ret = NULL;
1679         int ctime = 0, found = 0;
1680         struct mysql_conn *cur;
1681         int l = 0, which = 0;
1682
1683         switch (cmd) {
1684         case CLI_INIT:
1685                 e->command = "realtime mysql status";
1686                 e->usage =
1687                         "Usage: realtime mysql status [<database>]\n"
1688                         "       Shows connection information for the MySQL RealTime driver\n";
1689                 return NULL;
1690         case CLI_GENERATE:
1691                 if (a->argc == 4) {
1692                         AST_RWLIST_RDLOCK(&databases);
1693                         AST_RWLIST_TRAVERSE(&databases, cur, list) {
1694                                 if (!strncasecmp(a->word, cur->unique_name, l) && ++which > a->n) {
1695                                         ret = ast_strdup(cur->unique_name);
1696                                         break;
1697                                 }
1698                         }
1699                         AST_RWLIST_UNLOCK(&databases);
1700                 }
1701                 return ret;
1702         }
1703
1704         if (a->argc != 3)
1705                 return CLI_SHOWUSAGE;
1706
1707         AST_RWLIST_RDLOCK(&databases);
1708         AST_RWLIST_TRAVERSE(&databases, cur, list) {
1709                 if (a->argc == 3 || (a->argc == 4 && !strcasecmp(a->argv[3], cur->unique_name))) {
1710                         found = 1;
1711
1712                         if (mysql_reconnect(cur)) {
1713                                 snprintf(type, sizeof(type), "connected to");
1714                                 ctime = time(NULL) - cur->connect_time;
1715                         } else {
1716                                 snprintf(type, sizeof(type), "configured for");
1717                                 ctime = -1;
1718                         }
1719
1720                         if (!ast_strlen_zero(cur->host)) {
1721                                 snprintf(status, sizeof(status), "%s %s %s@%s, port %d", cur->unique_name, type, cur->name, cur->host, cur->port);
1722                         } else {
1723                                 snprintf(status, sizeof(status), "%s %s %s on socket file %s", cur->unique_name, type, cur->name, cur->sock);
1724                         }
1725
1726                         if (!ast_strlen_zero(cur->user)) {
1727                                 snprintf(status2, sizeof(status2), " with username %s", cur->user);
1728                         } else {
1729                                 status2[0] = '\0';
1730                         }
1731
1732                         if (ctime > 31536000) {
1733                                 ast_cli(a->fd, "%s%s for %.1f years.\n", status, status2, (double)ctime / 31536000.0);
1734                         } else if (ctime > 86400 * 30) {
1735                                 ast_cli(a->fd, "%s%s for %d days.\n", status, status2, ctime / 86400);
1736                         } else if (ctime > 86400) {
1737                                 ast_cli(a->fd, "%s%s for %d days, %d hours.\n", status, status2, ctime / 86400, (ctime % 86400) / 3600);
1738                         } else if (ctime > 3600) {
1739                                 ast_cli(a->fd, "%s%s for %d hours, %d minutes.\n", status, status2, ctime / 3600, (ctime % 3600) / 60);
1740                         } else if (ctime > 60) {
1741                                 ast_cli(a->fd, "%s%s for %d minutes.\n", status, status2, ctime / 60);
1742                         } else if (ctime > -1) {
1743                                 ast_cli(a->fd, "%s%s for %d seconds.\n", status, status2, ctime);
1744                         } else {
1745                                 ast_cli(a->fd, "%s%s.\n", status, status2);
1746                         }
1747                 }
1748         }
1749         AST_RWLIST_UNLOCK(&databases);
1750
1751         if (!found) {
1752                 ast_cli(a->fd, "No connections configured.\n");
1753         }
1754         return CLI_SUCCESS;
1755 }
1756
1757 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "MySQL RealTime Configuration Driver",
1758                 .load = load_module,
1759                 .unload = unload_module,
1760                 .reload = reload,
1761                 );
1762