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