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