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