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