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