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