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