2 * Asterisk -- An open source telephony toolkit.
4 * Copyright (C) 1999-2005, Digium, Inc.
6 * Mark Spencer <markster@digium.com> - Asterisk Author
7 * Matthew Boehm <mboehm@cytelcom.com> - MySQL RealTime Driver Author
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.
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.
22 * \brief MySQL CDR backend
26 <depend>mysqlclient</depend>
27 <defaultenabled>no</defaultenabled>
28 <support_level>extended</support_level>
35 #include <mysql/mysql.h>
36 #include <mysql/mysql_version.h>
37 #include <mysql/errmsg.h>
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"
50 #define RES_CONFIG_MYSQL_CONF "res_config_mysql.conf"
51 #define RES_CONFIG_MYSQL_CONF_OLD "res_mysql.conf"
55 #define ESCAPE_STRING(buf, var) \
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); \
64 ast_str_append(&semi, 0, "%c", *chunk); \
67 if (ast_str_strlen(semi) * 2 + 1 > ast_str_size(buf)) { \
68 ast_str_make_space(&(buf), ast_str_strlen(semi) * 2 + 1); \
70 mysql_real_escape_string(&dbh->handle, ast_str_buffer(buf), ast_str_buffer(semi), ast_str_strlen(semi)); \
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);
82 enum requirements { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR };
85 AST_RWLIST_ENTRY(mysql_conn) list;
97 enum requirements requirements;
107 AST_LIST_ENTRY(columns) list;
112 AST_LIST_HEAD_NOLOCK(mysql_columns, columns) columns;
113 AST_LIST_ENTRY(tables) list;
114 struct mysql_conn *database;
118 static AST_LIST_HEAD_STATIC(mysql_tables, tables);
119 static AST_RWLIST_HEAD_STATIC(databases, mysql_conn);
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);
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"),
133 static struct mysql_conn *find_database(const char *database, int for_write)
137 struct mysql_conn *cur;
139 if ((ptr = strchr(database, '/'))) {
140 /* Multiple databases encoded within string */
142 whichdb = ast_strdupa(ptr + 1);
144 whichdb = ast_alloca(ptr - database + 1);
145 strncpy(whichdb, database, ptr - database);
146 whichdb[ptr - database] = '\0';
149 whichdb = ast_strdupa(database);
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);
159 AST_RWLIST_UNLOCK(&databases);
163 #define release_database(a) ast_mutex_unlock(&(a)->lock)
165 static void destroy_table(struct tables *table)
167 struct columns *column;
168 ast_mutex_lock(&table->lock);
169 while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
172 ast_mutex_unlock(&table->lock);
173 ast_mutex_destroy(&table->lock);
177 static struct tables *find_table(const char *database, const char *tablename)
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;
187 if (!(dbh = find_database(database, 1))) {
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);
201 /* Not found, scan the table */
202 ast_str_set(&sql, 0, "DESC %s", tablename);
204 if (!mysql_reconnect(dbh)) {
205 release_database(dbh);
206 AST_LIST_UNLOCK(&mysql_tables);
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);
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);
223 strcpy(table->name, tablename); /* SAFE */
224 table->database = dbh;
225 ast_mutex_init(&table->lock);
226 AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
228 if ((result = mysql_store_result(&dbh->handle))) {
229 while ((row = mysql_fetch_row(result))) {
234 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
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);
248 if ((flen = strchr(ftype, '('))) {
249 sscanf(flen, "(%30d)", &column->len);
251 /* Columns like dates, times, and timestamps don't have a length */
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);
264 mysql_free_result(result);
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);
274 static void release_table(struct tables *table)
277 ast_mutex_unlock(&table->lock);
281 static struct columns *find_column(struct tables *table, const char *colname)
283 struct columns *column;
285 AST_LIST_TRAVERSE(&table->columns, column, list) {
286 if (strcmp(column->name, colname) == 0) {
294 static char *decode_chunk(char *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);
306 #define IS_SQL_LIKE_CLAUSE(x) ((x) && ast_ends_with(x, " LIKE"))
308 /* MySQL requires us to escape the escape... yo dawg */
309 static char *ESCAPE_CLAUSE = " ESCAPE '\\\\'";
311 static struct ast_variable *realtime_mysql(const char *database, const char *table, const struct ast_variable *rt_fields)
313 struct mysql_conn *dbh;
318 struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
319 struct ast_str *buf = ast_str_thread_get(&scratch_buf, 16);
324 const struct ast_variable *field = rt_fields;
325 struct ast_variable *var=NULL, *prev=NULL;
327 if (!(dbh = find_database(database, 0))) {
328 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: %s (check res_mysql.conf)\n", database);
333 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
334 release_database(dbh);
338 /* Get the first parameter and first value in our list of passed paramater/value pairs */
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);
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);
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 */
354 if (!strchr(field->name, ' ')) {
358 if (IS_SQL_LIKE_CLAUSE(field->name)) {
359 escape = ESCAPE_CLAUSE;
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)) {
367 if (!strchr(field->name, ' ')) {
371 if (IS_SQL_LIKE_CLAUSE(field->name)) {
372 escape = ESCAPE_CLAUSE;
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);
379 ast_debug(1, "MySQL RealTime: Retrieve SQL: %s\n", ast_str_buffer(sql));
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);
388 if ((result = mysql_store_result(&dbh->handle))) {
389 numFields = mysql_num_fields(result);
390 fields = mysql_fetch_fields(result);
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) {
397 } else if (ast_strlen_zero(row[i])) {
400 for (stringp = row[i], chunk = strsep(&stringp, ";"); chunk; chunk = strsep(&stringp, ";")) {
402 if ((prev->next = ast_variable_new(fields[i].name, decode_chunk(chunk), ""))) {
406 prev = var = ast_variable_new(fields[i].name, decode_chunk(chunk), "");
412 ast_debug(1, "MySQL RealTime: Could not find any rows in table %s.\n", table);
415 release_database(dbh);
416 mysql_free_result(result);
421 static struct ast_config *realtime_multi_mysql(const char *database, const char *table, const struct ast_variable *rt_fields)
423 struct mysql_conn *dbh;
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;
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;
440 if (!(dbh = find_database(database, 0))) {
441 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
446 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
447 release_database(dbh);
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);
458 /* Get the first parameter and first value in our list of passed paramater/value pairs */
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);
466 initfield = ast_strdupa(field->name);
467 if ((op = strchr(initfield, ' '))) {
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);
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 */
481 if (!strchr(field->name, ' ')) {
485 if (IS_SQL_LIKE_CLAUSE(field->name)) {
486 escape = ESCAPE_CLAUSE;
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)) {
494 if (!strchr(field->name, ' ')) {
498 if (IS_SQL_LIKE_CLAUSE(field->name)) {
499 escape = ESCAPE_CLAUSE;
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);
507 ast_str_append(&sql, 0, " ORDER BY %s", initfield);
510 ast_debug(1, "MySQL RealTime: Retrieve SQL: %s\n", ast_str_buffer(sql));
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);
520 if ((result = mysql_store_result(&dbh->handle))) {
521 numFields = mysql_num_fields(result);
522 fields = mysql_fetch_fields(result);
524 while ((row = mysql_fetch_row(result))) {
526 cat = ast_category_new("", "", -1);
528 ast_log(LOG_WARNING, "Out of memory!\n");
531 for (i = 0; i < numFields; i++) {
532 if (ast_strlen_zero(row[i]))
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);
539 var = ast_variable_new(fields[i].name, chunk, "");
540 ast_variable_append(cat, var);
544 ast_category_append(cfg, cat);
547 ast_debug(1, "MySQL RealTime: Could not find any rows in table %s.\n", table);
550 release_database(dbh);
551 mysql_free_result(result);
556 static int update_mysql(const char *database, const char *tablename, const char *keyfield, const char *lookup, const struct ast_variable *rt_fields)
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;
565 if (!(dbh = find_database(database, 1))) {
566 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
571 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
572 release_database(dbh);
576 if (!(table = find_table(database, tablename))) {
577 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
578 release_database(dbh);
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);
589 /* Get the first parameter and first value in our list of passed paramater/value pairs */
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);
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);
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);
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 */
615 ESCAPE_STRING(buf, field->value);
616 ast_str_set(&sql, 0, "UPDATE %s SET `%s` = '%s'", tablename, field->name, ast_str_buffer(buf));
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);
625 ESCAPE_STRING(buf, field->value);
626 ast_str_append(&sql, 0, ", `%s` = '%s'", field->name, ast_str_buffer(buf));
629 ESCAPE_STRING(buf, lookup);
630 ast_str_append(&sql, 0, " WHERE `%s` = '%s'", keyfield, ast_str_buffer(buf));
632 ast_debug(1, "MySQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
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);
642 numrows = mysql_affected_rows(&dbh->handle);
643 release_table(table);
644 release_database(dbh);
646 ast_debug(1, "MySQL RealTime: Updated %llu rows on table: %s\n", numrows, tablename);
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.)
657 static int update2_mysql(const char *database, const char *tablename, const struct ast_variable *lookup_fields, const struct ast_variable *update_fields)
659 struct mysql_conn *dbh;
660 my_ulonglong numrows;
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;
669 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
673 if (!(dbh = find_database(database, 1))) {
674 ast_log(LOG_ERROR, "Invalid database specified: %s\n", database);
678 if (!(table = find_table(database, tablename))) {
679 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
680 release_database(dbh);
684 if (!sql || !buf || !where) {
685 release_database(dbh);
686 release_table(table);
690 ast_str_set(&sql, 0, "UPDATE %s SET", tablename);
691 ast_str_set(&where, 0, "WHERE");
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);
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);
708 ESCAPE_STRING(buf, field->value);
709 ast_str_append(&where, 0, "%s `%s` = '%s'", first ? "" : " AND", field->name, ast_str_buffer(buf));
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);
721 ESCAPE_STRING(buf, field->value);
722 ast_str_append(&sql, 0, "%s `%s` = '%s'", first ? "" : ",", field->name, ast_str_buffer(buf));
726 release_table(table);
728 ast_str_append(&sql, 0, " %s", ast_str_buffer(where));
730 ast_debug(1, "MySQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
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);
740 numrows = mysql_affected_rows(&dbh->handle);
741 release_database(dbh);
743 ast_debug(1, "MySQL RealTime: Updated %llu rows on table: %s\n", numrows, tablename);
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.)
754 static int store_mysql(const char *database, const char *table, const struct ast_variable *rt_fields)
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;
762 if (!(dbh = find_database(database, 1))) {
763 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
768 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
769 release_database(dbh);
772 /* Get the first parameter and first value in our list of passed paramater/value pairs */
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);
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);
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));
789 while ((field = field->next)) {
790 ESCAPE_STRING(buf, field->value);
792 ast_str_append(&sql, 0, ", `%s`", field->name);
793 ast_str_append(&sql2, 0, ", '%s'", ast_str_buffer(buf));
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));
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);
805 release_database(dbh);
807 ast_debug(1, "MySQL RealTime: row inserted on table: %s\n", table);
812 static int destroy_mysql(const char *database, const char *table, const char *keyfield, const char *lookup, const struct ast_variable *rt_fields)
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;
820 if (!(dbh = find_database(database, 1))) {
821 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
826 ast_log(LOG_WARNING, "MySQL RealTime: No table specified.\n");
827 release_database(dbh);
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);
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);
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));
855 ast_debug(1, "MySQL RealTime: Delete SQL: %s\n", ast_str_buffer(sql));
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);
864 numrows = mysql_affected_rows(&dbh->handle);
865 release_database(dbh);
867 ast_debug(1, "MySQL RealTime: Deleted %llu rows on table: %s\n", numrows, table);
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.)
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)
880 struct mysql_conn *dbh;
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);
888 int last_cat_metric = 0;
890 ast_clear_flag(&config_flags, CONFIG_FLAG_FILEUNCHANGED);
892 if (!file || !strcmp(file, RES_CONFIG_MYSQL_CONF)) {
893 ast_log(LOG_WARNING, "MySQL RealTime: Cannot configure myself.\n");
897 if (!(dbh = find_database(database, 0))) {
898 ast_log(LOG_WARNING, "MySQL RealTime: Invalid database specified: '%s' (check res_mysql.conf)\n", database);
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);
904 ast_debug(1, "MySQL RealTime: Static SQL: %s\n", ast_str_buffer(sql));
906 /* We now have our complete statement; Lets connect to the server and execute it. */
907 if (!mysql_reconnect(dbh)) {
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);
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);
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. */
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);
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");
941 strcpy(last, row[0]);
942 last_cat_metric = atoi(row[3]);
943 ast_category_append(cfg, cur_cat);
945 new_v = ast_variable_new(row[1], row[2], "");
947 ast_variable_append(cur_cat, new_v);
950 ast_log(LOG_WARNING, "MySQL RealTime: Could not find config '%s' in database.\n", file);
953 mysql_free_result(result);
954 release_database(dbh);
959 static int unload_mysql(const char *database, const char *tablename)
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);
970 AST_LIST_TRAVERSE_SAFE_END
971 AST_LIST_UNLOCK(&mysql_tables);
975 static int require_mysql(const char *database, const char *tablename, va_list ap)
977 struct columns *column;
978 struct tables *table = find_table(database, tablename);
985 ast_log(LOG_WARNING, "Table %s not found in database. This table should exist if you're using realtime.\n", tablename);
989 while ((elm = va_arg(ap, char *))) {
990 type = va_arg(ap, require_type);
991 size = va_arg(ap, int);
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);
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");
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); \
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); \
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); \
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); \
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); \
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");
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); \
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); \
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); \
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); \
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); \
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);
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);
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);
1121 } else { /* Other, possibly unsupported types? */
1122 ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
1130 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
1133 release_table(table);
1138 static struct ast_config_engine mysql_engine = {
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,
1151 static int load_module(void)
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));
1161 static int unload_module(void)
1163 struct mysql_conn *cur;
1164 struct tables *table;
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");
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);
1176 AST_RWLIST_UNLOCK(&databases);
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);
1183 AST_LIST_UNLOCK(&mysql_tables);
1188 static int reload(void)
1191 ast_verb(2, "MySQL RealTime reloaded.\n");
1195 static int parse_config(int reload)
1197 struct ast_config *config = NULL;
1198 struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
1200 struct mysql_conn *cur;
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);
1207 if (config == CONFIG_STATUS_FILEMISSING) {
1209 } else if (config == CONFIG_STATUS_FILEUNCHANGED) {
1211 } else if (config == CONFIG_STATUS_FILEINVALID) {
1212 ast_log(LOG_ERROR, "Not %sloading " RES_CONFIG_MYSQL_CONF "\n", reload ? "re" : "");
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)) {
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);
1230 strcpy(cur->unique_name, catg); /* SAFE */
1231 ast_mutex_init(&cur->lock);
1232 AST_RWLIST_INSERT_TAIL(&databases, cur, list);
1235 load_mysql_config(config, catg, cur);
1237 AST_RWLIST_UNLOCK(&databases);
1239 ast_config_destroy(config);
1244 static int load_mysql_config(struct ast_config *config, const char *category, struct mysql_conn *conn)
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");
1252 ast_copy_string(conn->user, s, sizeof(conn->user));
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");
1258 ast_copy_string(conn->pass, s, sizeof(conn->pass));
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");
1264 ast_copy_string(conn->host, s, sizeof(conn->host));
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");
1270 ast_copy_string(conn->name, s, sizeof(conn->name));
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");
1276 conn->port = atoi(s);
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" };
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));
1290 ast_log(LOG_WARNING, "MySQL RealTime: No database socket found (and unable to detect a suitable path).\n");
1295 ast_copy_string(conn->sock, s, sizeof(conn->sock));
1297 if ((s = ast_variable_retrieve(config, category, "dbcharset"))) {
1298 ast_copy_string(conn->charset, s, sizeof(conn->charset));
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;
1311 ast_log(LOG_WARNING, "MySQL realtime: unrecognized requirements setting '%s', using 'warn'\n", s);
1312 conn->requirements = RQ_WARN;
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);
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);
1329 static int mysql_reconnect(struct mysql_conn *conn)
1331 #ifdef MYSQL_OPT_RECONNECT
1332 my_bool trueval = 1;
1335 /* mutex lock should have been locked before calling this function. */
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;
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);
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);
1359 ast_debug(1, "MySQL RealTime: Successfully connected to database.\n");
1360 conn->connected = 1;
1361 conn->connect_time = time(NULL);
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;
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;
1381 if (!conn->connected) {
1382 conn->connected = 1;
1383 conn->connect_time = time(NULL);
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));
1391 ast_debug(1, "MySQL RealTime: Connection okay.\n");
1396 static char *handle_cli_realtime_mysql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1404 e->command = "realtime mysql cache";
1406 "Usage: realtime mysql cache [<database> <table>]\n"
1407 " Shows table cache for the MySQL RealTime driver\n";
1410 if (a->argc < 4 || a->argc > 5) {
1413 l = strlen(a->word);
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);
1423 AST_LIST_UNLOCK(&mysql_tables);
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);
1433 AST_RWLIST_UNLOCK(&databases);
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);
1444 AST_LIST_UNLOCK(&mysql_tables);
1445 } else if (a->argc == 4) {
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);
1455 AST_LIST_UNLOCK(&mysql_tables);
1457 ast_cli(a->fd, "No tables cached within %s database\n", a->argv[3]);
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);
1470 ast_cli(a->fd, "No such table '%s'\n", a->argv[3]);
1476 static char *handle_cli_realtime_mysql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1478 char status[256], status2[100] = "", type[20];
1480 int ctime = 0, found = 0;
1481 struct mysql_conn *cur;
1482 int l = 0, which = 0;
1486 e->command = "realtime mysql status";
1488 "Usage: realtime mysql status [<database>]\n"
1489 " Shows connection information for the MySQL RealTime driver\n";
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);
1500 AST_RWLIST_UNLOCK(&databases);
1506 return CLI_SHOWUSAGE;
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))) {
1513 if (mysql_reconnect(cur)) {
1514 snprintf(type, sizeof(type), "connected to");
1515 ctime = time(NULL) - cur->connect_time;
1517 snprintf(type, sizeof(type), "configured for");
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);
1524 snprintf(status, sizeof(status), "%s %s %s on socket file %s", cur->unique_name, type, cur->name, cur->sock);
1527 if (!ast_strlen_zero(cur->user)) {
1528 snprintf(status2, sizeof(status2), " with username %s", cur->user);
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);
1546 ast_cli(a->fd, "%s%s.\n", status, status2);
1550 AST_RWLIST_UNLOCK(&databases);
1553 ast_cli(a->fd, "No connections configured.\n");
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,
1563 .load_pri = AST_MODPRI_REALTIME_DRIVER,