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