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