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