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