From 57ffc548bcd6fcce7143febc25264a33855ad366 Mon Sep 17 00:00:00 2001 From: Tilghman Lesher Date: Wed, 30 Jul 2008 17:32:28 +0000 Subject: Add adaptive capabilities to the sqlite realtime driver (closes issue #13097) Reported by: gknispel_proformatique Patches: 20080730__bug13097.diff.txt uploaded by Corydon76 (license 14) Tested by: Corydon76 git-svn-id: https://origsvn.digium.com/svn/asterisk/trunk@134442 65c4cc65-6c06-0410-ace0-fbb531ad65f3 --- res/res_config_sqlite.c | 359 ++++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 299 insertions(+), 60 deletions(-) diff --git a/res/res_config_sqlite.c b/res/res_config_sqlite.c index 3faff62e2..1c7489e4c 100644 --- a/res/res_config_sqlite.c +++ b/res/res_config_sqlite.c @@ -80,6 +80,7 @@ ASTERISK_FILE_VERSION(__FILE__, "$Revision$") #include +#include "asterisk/app.h" #include "asterisk/pbx.h" #include "asterisk/cdr.h" #include "asterisk/cli.h" @@ -443,6 +444,10 @@ static int realtime_destroy_handler(const char *database, const char *table, * \return RESULT_SUCCESS */ static char *handle_cli_show_sqlite_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a); +static char *handle_cli_sqlite_show_tables(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a); + +static int realtime_require_handler(const char *database, const char *table, va_list ap); +static int realtime_unload_handler(const char *unused, const char *tablename); /*! The SQLite database object. */ static sqlite *db; @@ -477,7 +482,9 @@ static struct ast_config_engine sqlite_engine = .realtime_multi_func = realtime_multi_handler, .store_func = realtime_store_handler, .destroy_func = realtime_destroy_handler, - .update_func = realtime_update_handler + .update_func = realtime_update_handler, + .require_func = realtime_require_handler, + .unload_func = realtime_unload_handler, }; /*! @@ -491,8 +498,24 @@ AST_MUTEX_DEFINE_STATIC(mutex); */ static struct ast_cli_entry cli_status[] = { AST_CLI_DEFINE(handle_cli_show_sqlite_status, "Show status information about the SQLite 2 driver"), + AST_CLI_DEFINE(handle_cli_sqlite_show_tables, "Cached table information about the SQLite 2 driver"), +}; + +struct sqlite_cache_columns { + char *name; + char *type; + unsigned char isint; /*!< By definition, only INTEGER PRIMARY KEY is an integer; everything else is a string. */ + AST_RWLIST_ENTRY(sqlite_cache_columns) list; }; +struct sqlite_cache_tables { + char *name; + AST_RWLIST_HEAD(_columns, sqlite_cache_columns) columns; + AST_RWLIST_ENTRY(sqlite_cache_tables) list; +}; + +static AST_RWLIST_HEAD_STATIC(sqlite_tables, sqlite_cache_tables); + /* * Taken from Asterisk 1.2 cdr_sqlite.so. */ @@ -522,47 +545,11 @@ static char *sql_create_cdr_table = " PRIMARY KEY (id)\n" ");"; -/*! SQL query format to insert a CDR entry. */ -static char *sql_add_cdr_entry = -"INSERT INTO '%q' (" -" clid," -" src," -" dst," -" dcontext," -" channel," -" dstchannel," -" lastapp," -" lastdata," -" start," -" answer," -" end," -" duration," -" billsec," -" disposition," -" amaflags," -" accountcode," -" uniqueid," -" userfield" -") VALUES (" -" '%q'," -" '%q'," -" '%q'," -" '%q'," -" '%q'," -" '%q'," -" '%q'," -" '%q'," -" datetime(%d,'unixepoch','localtime')," -" datetime(%d,'unixepoch','localtime')," -" datetime(%d,'unixepoch','localtime')," -" '%ld'," -" '%ld'," -" '%ld'," -" '%ld'," -" '%q'," -" '%q'," -" '%q'" -");"; +/*! + * SQL query format to describe the table structure + */ +static char *sql_table_structure = +"SELECT sql FROM sqlite_master WHERE type='table' AND tbl_name='%s'"; /*! * SQL query format to fetch the static configuration of a file. @@ -570,12 +557,143 @@ static char *sql_add_cdr_entry = * * \see add_cfg_entry() */ -static char *sql_get_config_table = +static const char *sql_get_config_table = "SELECT *" " FROM '%q'" " WHERE filename = '%q' AND commented = 0" " ORDER BY cat_metric ASC, var_metric ASC;"; +static void free_table(struct sqlite_cache_tables *tblptr) +{ + struct sqlite_cache_columns *col; + + /* Obtain a write lock to ensure there are no read locks outstanding */ + AST_RWLIST_WRLOCK(&(tblptr->columns)); + while ((col = AST_RWLIST_REMOVE_HEAD(&(tblptr->columns), list))) { + ast_free(col); + } + AST_RWLIST_UNLOCK(&(tblptr->columns)); + AST_RWLIST_HEAD_DESTROY(&(tblptr->columns)); + ast_free(tblptr); +} + +static int find_table_cb(void *vtblptr, int argc, char **argv, char **columnNames) +{ + struct sqlite_cache_tables *tblptr = vtblptr; + char *sql = ast_strdupa(argv[0]), *start, *end, *type, *remainder; + int i; + AST_DECLARE_APP_ARGS(fie, + AST_APP_ARG(ld)[100]; /* This means we support up to 100 columns per table */ + ); + struct sqlite_cache_columns *col; + + /* This is really fun. We get to parse an SQL statement to figure out + * what columns are in the table. + */ + if ((start = strchr(sql, '(')) && (end = strrchr(sql, ')'))) { + start++; + *end = '\0'; + } else { + /* Abort */ + return -1; + } + + AST_STANDARD_APP_ARGS(fie, start); + for (i = 0; i < fie.argc; i++) { + fie.ld[i] = ast_skip_blanks(fie.ld[i]); + ast_debug(5, "Found field: %s\n", fie.ld[i]); + if (strncasecmp(fie.ld[i], "PRIMARY KEY", 11) == 0 && (start = strchr(fie.ld[i], '(')) && (end = strchr(fie.ld[i], ')'))) { + *end = '\0'; + AST_RWLIST_TRAVERSE(&(tblptr->columns), col, list) { + if (strcasecmp(start + 1, col->name) == 0 && strcasestr(col->type, "INTEGER")) { + col->isint = 1; + } + } + continue; + } + /* type delimiter could be any space character */ + for (type = fie.ld[i]; *type > 32; type++); + *type++ = '\0'; + type = ast_skip_blanks(type); + for (remainder = type; *remainder > 32; remainder++); + *remainder = '\0'; + if (!(col = ast_calloc(1, sizeof(*col) + strlen(fie.ld[i]) + strlen(type) + 2))) { + return -1; + } + col->name = (char *)col + sizeof(*col); + col->type = (char *)col + sizeof(*col) + strlen(fie.ld[i]) + 1; + strcpy(col->name, fie.ld[i]); /* SAFE */ + strcpy(col->type, type); /* SAFE */ + if (strcasestr(col->type, "INTEGER") && strcasestr(col->type, "PRIMARY KEY")) { + col->isint = 1; + } + AST_LIST_INSERT_TAIL(&(tblptr->columns), col, list); + } + return 0; +} + +static struct sqlite_cache_tables *find_table(const char *tablename) +{ + struct sqlite_cache_tables *tblptr; + int i, err; + char *sql, *errstr = NULL; + + AST_RWLIST_RDLOCK(&sqlite_tables); + + for (i = 0; i < 2; i++) { + AST_RWLIST_TRAVERSE(&sqlite_tables, tblptr, list) { + if (strcmp(tblptr->name, tablename) == 0) { + break; + } + } + if (tblptr) { + AST_RWLIST_RDLOCK(&(tblptr->columns)); + AST_RWLIST_UNLOCK(&sqlite_tables); + return tblptr; + } + + if (i == 0) { + AST_RWLIST_UNLOCK(&sqlite_tables); + AST_RWLIST_WRLOCK(&sqlite_tables); + } + } + + /* Table structure not cached; build the structure now */ + asprintf(&sql, sql_table_structure, tablename); + if (!(tblptr = ast_calloc(1, sizeof(*tblptr) + strlen(tablename) + 1))) { + AST_RWLIST_UNLOCK(&sqlite_tables); + ast_log(LOG_ERROR, "Memory error. Cannot cache table '%s'\n", tablename); + return NULL; + } + tblptr->name = (char *)tblptr + sizeof(*tblptr); + strcpy(tblptr->name, tablename); /* SAFE */ + AST_RWLIST_HEAD_INIT(&(tblptr->columns)); + + ast_debug(1, "About to query table structure: %s\n", sql); + + ast_mutex_lock(&mutex); + if ((err = sqlite_exec(db, sql, find_table_cb, tblptr, &errstr))) { + ast_mutex_unlock(&mutex); + ast_log(LOG_WARNING, "SQLite error %d: %s\n", err, errstr); + ast_free(errstr); + free_table(tblptr); + return NULL; + } + ast_mutex_unlock(&mutex); + + if (AST_LIST_EMPTY(&(tblptr->columns))) { + free_table(tblptr); + return NULL; + } + + AST_RWLIST_INSERT_TAIL(&sqlite_tables, tblptr, list); + AST_RWLIST_RDLOCK(&(tblptr->columns)); + AST_RWLIST_UNLOCK(&sqlite_tables); + return tblptr; +} + +#define release_table(a) AST_RWLIST_UNLOCK(&((a)->columns)) + static int set_var(char **var, const char *name, const char *value) { if (*var) @@ -622,9 +740,9 @@ static int load_config(void) SET_VAR(config, dbfile, var); else if (!strcasecmp(var->name, "config_table")) SET_VAR(config, config_table, var); - else if (!strcasecmp(var->name, "cdr_table")) + else if (!strcasecmp(var->name, "cdr_table")) { SET_VAR(config, cdr_table, var); - else + } else ast_log(LOG_WARNING, "Unknown parameter : %s\n", var->name); } @@ -641,43 +759,75 @@ static int load_config(void) static void unload_config(void) { + struct sqlite_cache_tables *tbl; ast_free(dbfile); dbfile = NULL; ast_free(config_table); config_table = NULL; ast_free(cdr_table); cdr_table = NULL; + AST_RWLIST_WRLOCK(&sqlite_tables); + while ((tbl = AST_RWLIST_REMOVE_HEAD(&sqlite_tables, list))) { + free_table(tbl); + } + AST_RWLIST_UNLOCK(&sqlite_tables); } static int cdr_handler(struct ast_cdr *cdr) { - char *query, *errormsg; - int error; + char *errormsg, *tmp, workspace[500]; + int error, scannum; + struct sqlite_cache_tables *tbl = find_table(cdr_table); + struct sqlite_cache_columns *col; + struct ast_str *sql1 = ast_str_create(160), *sql2 = ast_str_create(16); + + if (!tbl) { + ast_log(LOG_WARNING, "No such table: %s\n", cdr_table); + return -1; + } - query = sqlite_mprintf(sql_add_cdr_entry, cdr_table, cdr->clid, - cdr->src, cdr->dst, cdr->dcontext, cdr->channel, - cdr->dstchannel, cdr->lastapp, cdr->lastdata, - cdr->start.tv_sec, cdr->answer.tv_sec, - cdr->end.tv_sec, cdr->duration, cdr->billsec, - cdr->disposition, cdr->amaflags, cdr->accountcode, - cdr->uniqueid, cdr->userfield); + ast_str_set(&sql1, 0, "INSERT INTO %s (", cdr_table); + ast_str_set(&sql2, 0, ") VALUES ("); - if (!query) { - ast_log(LOG_WARNING, "Unable to allocate SQL query\n"); - return 1; + AST_RWLIST_TRAVERSE(&(tbl->columns), col, list) { + if (col->isint) { + ast_cdr_getvar(cdr, col->name, &tmp, workspace, sizeof(workspace), 0, 1); + if (!tmp) { + continue; + } + if (sscanf(tmp, "%d", &scannum) == 1) { + ast_str_append(&sql1, 0, "%s,", col->name); + ast_str_append(&sql2, 0, "%d,", scannum); + } + } else { + ast_cdr_getvar(cdr, col->name, &tmp, workspace, sizeof(workspace), 0, 0); + if (!tmp) { + continue; + } + ast_str_append(&sql1, 0, "%s,", col->name); + tmp = sqlite_mprintf("%Q", tmp); + ast_str_append(&sql2, 0, "%s,", tmp); + sqlite_freemem(tmp); + } } + release_table(tbl); - ast_debug(1, "SQL query: %s\n", query); + sql1->str[--sql1->used] = '\0'; + sql2->str[--sql2->used] = '\0'; + ast_str_append(&sql1, 0, "%s)", sql2->str); + ast_free(sql2); + + ast_debug(1, "SQL query: %s\n", sql1->str); ast_mutex_lock(&mutex); RES_CONFIG_SQLITE_BEGIN - error = sqlite_exec(db, query, NULL, NULL, &errormsg); + error = sqlite_exec(db, sql1->str, NULL, NULL, &errormsg); RES_CONFIG_SQLITE_END(error) ast_mutex_unlock(&mutex); - sqlite_freemem(query); + ast_free(sql1); if (error) { ast_log(LOG_ERROR, "%s\n", errormsg); @@ -1371,6 +1521,57 @@ static int realtime_destroy_handler(const char *database, const char *table, return rows_num; } +static int realtime_require_handler(const char *unused, const char *tablename, va_list ap) +{ + struct sqlite_cache_tables *tbl = find_table(tablename); + struct sqlite_cache_columns *col; + char *elm; + int type, size, res = 0; + + if (!tbl) { + return -1; + } + + while ((elm = va_arg(ap, char *))) { + type = va_arg(ap, require_type); + size = va_arg(ap, int); + /* Check if the field matches the criteria */ + AST_RWLIST_TRAVERSE(&tbl->columns, col, list) { + if (strcmp(col->name, elm) == 0) { + /* SQLite only has two types - the 32-bit integer field that + * is the key column, and everything else (everything else + * being a string). + */ + if (col->isint && !ast_rq_is_int(type)) { + ast_log(LOG_WARNING, "Realtime table %s: column '%s' is an integer field, but Asterisk requires that it not be!\n", tablename, col->name); + res = -1; + } + break; + } + } + if (!col) { + ast_log(LOG_WARNING, "Realtime table %s requires column '%s', but that column does not exist!\n", tablename, elm); + } + } + AST_RWLIST_UNLOCK(&(tbl->columns)); + return res; +} + +static int realtime_unload_handler(const char *unused, const char *tablename) +{ + struct sqlite_cache_tables *tbl; + AST_RWLIST_WRLOCK(&sqlite_tables); + AST_RWLIST_TRAVERSE_SAFE_BEGIN(&sqlite_tables, tbl, list) { + if (!strcasecmp(tbl->name, tablename)) { + AST_RWLIST_REMOVE_CURRENT(list); + free_table(tbl); + } + } + AST_RWLIST_TRAVERSE_SAFE_END + AST_RWLIST_UNLOCK(&sqlite_tables); + return 0; +} + static char *handle_cli_show_sqlite_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a) { switch (cmd) { @@ -1405,6 +1606,44 @@ static char *handle_cli_show_sqlite_status(struct ast_cli_entry *e, int cmd, str return CLI_SUCCESS; } +static char *handle_cli_sqlite_show_tables(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a) +{ + struct sqlite_cache_tables *tbl; + struct sqlite_cache_columns *col; + int found = 0; + + switch (cmd) { + case CLI_INIT: + e->command = "sqlite show tables"; + e->usage = + "Usage: sqlite show tables\n" + " Show table information about the SQLite 2 driver\n"; + return NULL; + case CLI_GENERATE: + return NULL; + } + + if (a->argc != 3) + return CLI_SHOWUSAGE; + + AST_RWLIST_RDLOCK(&sqlite_tables); + AST_RWLIST_TRAVERSE(&sqlite_tables, tbl, list) { + found++; + ast_cli(a->fd, "Table %s:\n", tbl->name); + AST_RWLIST_TRAVERSE(&(tbl->columns), col, list) { + fprintf(stderr, "%s\n", col->name); + ast_cli(a->fd, " %20.20s %-30.30s\n", col->name, col->type); + } + } + AST_RWLIST_UNLOCK(&sqlite_tables); + + if (!found) { + ast_cli(a->fd, "No tables currently in cache\n"); + } + + return CLI_SUCCESS; +} + static int unload_module(void) { if (cli_status_registered) -- cgit v1.2.3