43 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
50 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
52 static PGconn *pgsqlConn = NULL;
54 #define has_schema_support (version > 70300 ? 1 : 0)
55 #define USE_BACKSLASH_AS_STRING (version >= 90100 ? 1 : 0)
57 #define MAX_DB_OPTION_SIZE 64
63 unsigned int notnull:1;
64 unsigned int hasdefault:1;
77 static
char dbhost[MAX_DB_OPTION_SIZE] = "";
78 static
char dbuser[MAX_DB_OPTION_SIZE] = "";
79 static
char dbpass[MAX_DB_OPTION_SIZE] = "";
80 static
char dbname[MAX_DB_OPTION_SIZE] = "";
81 static
char dbappname[MAX_DB_OPTION_SIZE] = "";
82 static
char dbsock[MAX_DB_OPTION_SIZE] = "";
83 static
int dbport = 5432;
84 static time_t connect_time = 0;
85 static
int order_multi_row_results_by_initial_column = 1;
87 static
int parse_config(
int reload);
88 static
int pgsql_reconnect(const
char *database);
92 static enum { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR } requirements;
95 AST_CLI_DEFINE(handle_cli_realtime_pgsql_status,
"Shows connection information for the PostgreSQL RealTime driver"),
96 AST_CLI_DEFINE(handle_cli_realtime_pgsql_cache,
"Shows cached tables within the PostgreSQL realtime driver"),
99 #define ESCAPE_STRING(buffer, stringname) \
101 int len = strlen(stringname); \
102 struct ast_str *semi = ast_str_thread_get(&semibuf_buf, len * 3 + 1); \
103 const char *chunk = stringname; \
104 ast_str_reset(semi); \
105 for (; *chunk; chunk++) { \
106 if (strchr(";^", *chunk)) { \
107 ast_str_append(&semi, 0, "^%02hhX", *chunk); \
109 ast_str_append(&semi, 0, "%c", *chunk); \
112 if (ast_str_strlen(semi) > (ast_str_size(buffer) - 1) / 2) { \
113 ast_str_make_space(&buffer, ast_str_strlen(semi) * 2 + 1); \
115 PQescapeStringConn(pgsqlConn, ast_str_buffer(buffer), ast_str_buffer(semi), ast_str_size(buffer), &pgresult); \
118 static void destroy_table(
struct tables *table)
121 ast_rwlock_wrlock(&table->lock);
125 ast_rwlock_unlock(&table->lock);
126 ast_rwlock_destroy(&table->lock);
145 static int _pgsql_exec(
const char *database,
const char *tablename,
const char *sql, PGresult **result)
147 ExecStatusType result_status;
150 ast_debug(1,
"PostgreSQL connection not defined, connecting\n");
152 if (pgsql_reconnect(database) != 1) {
153 ast_log(LOG_NOTICE,
"reconnect failed\n");
158 ast_debug(1,
"PostgreSQL connection successful\n");
161 *result = PQexec(pgsqlConn, sql);
162 result_status = PQresultStatus(*result);
163 if (result_status != PGRES_COMMAND_OK
164 && result_status != PGRES_TUPLES_OK
165 && result_status != PGRES_NONFATAL_ERROR) {
167 ast_log(LOG_ERROR,
"PostgreSQL RealTime: Failed to query '%s@%s'.\n", tablename, database);
168 ast_log(LOG_ERROR,
"PostgreSQL RealTime: Query Failed: %s\n", sql);
169 ast_log(LOG_ERROR,
"PostgreSQL RealTime: Query Failed because: %s (%s)\n",
170 PQresultErrorMessage(*result),
171 PQresStatus(result_status));
175 if (PQstatus(pgsqlConn) != CONNECTION_OK) {
185 ast_debug(1,
"PostgreSQL query successful: %s\n", sql);
219 static int pgsql_exec(
const char *database,
const char *tablename,
const char *sql, PGresult **result)
228 while (attempts++ < 2) {
229 ast_debug(1,
"PostgreSQL query attempt %d\n", attempts);
230 res =
_pgsql_exec(database, tablename, sql, result);
234 ast_log(LOG_NOTICE,
"PostgreSQL RealTime: Query finally succeeded: %s\n", sql);
245 ast_debug(1,
"PostgreSQL query attempt %d failed, trying again\n", attempts);
251 static struct tables *find_table(
const char *database,
const char *orig_tablename)
254 struct tables *table;
256 RAII_VAR(PGresult *, result, NULL, PQclear);
258 char *fname, *ftype, *flen, *fnotnull, *fdef;
263 if (!strcasecmp(table->name, orig_tablename)) {
264 ast_debug(1,
"Found table in cache; now locking\n");
265 ast_rwlock_rdlock(&table->lock);
266 ast_debug(1,
"Lock cached table; now returning\n");
272 if (database == NULL) {
277 ast_debug(1,
"Table '%s' not found in cache, querying now\n", orig_tablename);
280 if (has_schema_support) {
281 char *schemaname, *tablename, *tmp_schemaname, *tmp_tablename;
282 if (strchr(orig_tablename,
'.')) {
284 tmp_tablename = strchr(tmp_schemaname,
'.');
285 *tmp_tablename++ =
'\0';
291 tablename =
ast_alloca(strlen(tmp_tablename) * 2 + 1);
292 PQescapeStringConn(pgsqlConn, tablename, tmp_tablename, strlen(tmp_tablename), NULL);
293 schemaname =
ast_alloca(strlen(tmp_schemaname) * 2 + 1);
294 PQescapeStringConn(pgsqlConn, schemaname, tmp_schemaname, strlen(tmp_schemaname), NULL);
296 ast_str_set(&sql, 0,
"SELECT a.attname, t.typname, a.attlen, a.attnotnull, pg_catalog.pg_get_expr(d.adbin, d.adrelid) adsrc, a.atttypmod FROM (((pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND c.relname = '%s' AND n.nspname = %s%s%s) INNER JOIN pg_catalog.pg_attribute a ON (NOT a.attisdropped) AND a.attnum > 0 AND a.attrelid = c.oid) INNER JOIN pg_catalog.pg_type t ON t.oid = a.atttypid) LEFT OUTER JOIN pg_attrdef d ON a.atthasdef AND d.adrelid = a.attrelid AND d.adnum = a.attnum ORDER BY n.nspname, c.relname, attnum",
298 ast_strlen_zero(schemaname) ?
"" :
"'", ast_strlen_zero(schemaname) ?
"current_schema()" : schemaname, ast_strlen_zero(schemaname) ?
"" :
"'");
301 tablename =
ast_alloca(strlen(orig_tablename) * 2 + 1);
302 PQescapeStringConn(pgsqlConn, tablename, orig_tablename, strlen(orig_tablename), NULL);
304 ast_str_set(&sql, 0,
"SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc, a.atttypmod FROM pg_class c, pg_type t, pg_attribute a LEFT OUTER JOIN pg_attrdef d ON a.atthasdef AND d.adrelid = a.attrelid AND d.adnum = a.attnum WHERE c.oid = a.attrelid AND a.atttypid = t.oid AND (a.attnum > 0) AND c.relname = '%s' ORDER BY c.relname, attnum", tablename);
307 ast_mutex_lock(&pgsql_lock);
309 ast_mutex_unlock(&pgsql_lock);
310 ast_debug(1,
"Query of table structure complete. Now retrieving results.\n");
311 if (exec_result != 0) {
312 ast_log(LOG_ERROR,
"Failed to query database columns for table %s\n", orig_tablename);
317 if (!(table =
ast_calloc(1,
sizeof(*table) + strlen(orig_tablename) + 1))) {
318 ast_log(LOG_ERROR,
"Unable to allocate memory for new table structure\n");
322 strcpy(table->name, orig_tablename);
326 rows = PQntuples(result);
327 for (i = 0; i < rows; i++) {
328 fname = PQgetvalue(result, i, 0);
329 ftype = PQgetvalue(result, i, 1);
330 flen = PQgetvalue(result, i, 2);
331 fnotnull = PQgetvalue(result, i, 3);
332 fdef = PQgetvalue(result, i, 4);
333 ast_verb(4,
"Found column '%s' of type '%s'\n", fname, ftype);
335 if (!(column =
ast_calloc(1,
sizeof(*column) + strlen(fname) + strlen(ftype) + 2))) {
336 ast_log(LOG_ERROR,
"Unable to allocate column element for %s, %s\n", orig_tablename, fname);
337 destroy_table(table);
342 if (strcmp(flen,
"-1") == 0) {
344 flen = PQgetvalue(result, i, 5);
345 sscanf(flen,
"%30d", &column->len);
348 sscanf(flen,
"%30d", &column->len);
350 column->name = (
char *)column +
sizeof(*column);
351 column->type = (
char *)column +
sizeof(*column) + strlen(fname) + 1;
352 strcpy(column->name, fname);
353 strcpy(column->type, ftype);
354 if (*fnotnull ==
't') {
359 if (!ast_strlen_zero(fdef)) {
360 column->hasdefault = 1;
362 column->hasdefault = 0;
368 ast_rwlock_rdlock(&table->lock);
373 #define release_table(table) ast_rwlock_unlock(&(table)->lock);
375 static struct columns *find_column(
struct tables *t,
const char *colname)
381 if (strcmp(column->name, colname) == 0) {
388 #define IS_SQL_LIKE_CLAUSE(x) ((x) && ast_ends_with(x, " LIKE"))
389 #define ESCAPE_CLAUSE (USE_BACKSLASH_AS_STRING ? " ESCAPE '\\'" : " ESCAPE '\\\\'")
391 static struct ast_variable *realtime_pgsql(
const char *database,
const char *tablename,
const struct ast_variable *fields)
393 RAII_VAR(PGresult *, result, NULL, PQclear);
411 ast_log(LOG_WARNING,
"PostgreSQL RealTime: No table specified.\n");
419 ast_mutex_lock(&pgsql_lock);
420 if (!pgsql_reconnect(database)) {
421 ast_mutex_unlock(&pgsql_lock);
428 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
433 ast_mutex_unlock(&pgsql_lock);
439 if (!strchr(field->
name,
' ')) {
443 if (IS_SQL_LIKE_CLAUSE(field->
name)) {
444 escape = ESCAPE_CLAUSE;
448 ESCAPE_STRING(escapebuf, field->
value);
450 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
451 ast_mutex_unlock(&pgsql_lock);
456 while ((field = field->
next)) {
458 if (!strchr(field->
name,
' ')) {
462 if (IS_SQL_LIKE_CLAUSE(field->
name)) {
463 escape = ESCAPE_CLAUSE;
467 ESCAPE_STRING(escapebuf, field->
value);
469 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
470 ast_mutex_unlock(&pgsql_lock);
480 ast_mutex_unlock(&pgsql_lock);
486 if (PQntuples(result) > 0) {
488 int numFields = PQnfields(result);
489 char **fieldnames = NULL;
491 ast_debug(1,
"PostgreSQL RealTime: Found a row.\n");
493 if (!(fieldnames =
ast_calloc(1, numFields *
sizeof(
char *)))) {
494 ast_mutex_unlock(&pgsql_lock);
497 for (i = 0; i < numFields; i++)
498 fieldnames[i] = PQfname(result, i);
499 for (i = 0; i < numFields; i++) {
500 stringp = PQgetvalue(result, 0, i);
502 chunk = strsep(&stringp,
";");
505 prev->next = ast_variable_new(fieldnames[i], chunk,
"");
510 prev = var = ast_variable_new(fieldnames[i], chunk,
"");
515 ast_free(fieldnames);
517 ast_debug(1,
"Postgresql RealTime: Could not find any rows in table %s@%s.\n", tablename, database);
520 ast_mutex_unlock(&pgsql_lock);
525 static struct ast_config *realtime_multi_pgsql(
const char *database,
const char *table,
const struct ast_variable *fields)
527 RAII_VAR(PGresult *, result, NULL, PQclear);
528 int num_rows = 0, pgresult;
532 const char *initfield = NULL;
548 ast_log(LOG_WARNING,
"PostgreSQL RealTime: No table specified.\n");
559 ast_mutex_lock(&pgsql_lock);
560 if (!pgsql_reconnect(database)) {
561 ast_mutex_unlock(&pgsql_lock);
568 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
573 ast_mutex_unlock(&pgsql_lock);
579 if ((op = strchr(initfield,
' '))) {
586 if (!strchr(field->
name,
' ')) {
591 if (IS_SQL_LIKE_CLAUSE(field->
name)) {
592 escape = ESCAPE_CLAUSE;
596 ESCAPE_STRING(escapebuf, field->
value);
598 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
599 ast_mutex_unlock(&pgsql_lock);
605 while ((field = field->
next)) {
607 if (!strchr(field->
name,
' ')) {
612 if (IS_SQL_LIKE_CLAUSE(field->
name)) {
613 escape = ESCAPE_CLAUSE;
617 ESCAPE_STRING(escapebuf, field->
value);
619 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
620 ast_mutex_unlock(&pgsql_lock);
628 if (initfield && order_multi_row_results_by_initial_column) {
634 ast_mutex_unlock(&pgsql_lock);
638 ExecStatusType result_status = PQresultStatus(result);
639 if (result_status != PGRES_COMMAND_OK
640 && result_status != PGRES_TUPLES_OK
641 && result_status != PGRES_NONFATAL_ERROR) {
643 "PostgreSQL RealTime: Failed to query %s@%s. Check debug for more info.\n", table, database);
645 ast_debug(1,
"PostgreSQL RealTime: Query Failed because: %s (%s)\n",
646 PQresultErrorMessage(result), PQresStatus(result_status));
647 ast_mutex_unlock(&pgsql_lock);
655 if ((num_rows = PQntuples(result)) > 0) {
656 int numFields = PQnfields(result);
659 char **fieldnames = NULL;
661 ast_debug(1,
"PostgreSQL RealTime: Found %d rows.\n", num_rows);
663 if (!(fieldnames =
ast_calloc(1, numFields *
sizeof(
char *)))) {
664 ast_mutex_unlock(&pgsql_lock);
668 for (i = 0; i < numFields; i++)
669 fieldnames[i] = PQfname(result, i);
671 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
677 for (i = 0; i < numFields; i++) {
678 stringp = PQgetvalue(result, rowIndex, i);
680 chunk = strsep(&stringp,
";");
682 if (initfield && !strcmp(initfield, fieldnames[i])) {
683 ast_category_rename(cat, chunk);
685 var = ast_variable_new(fieldnames[i], chunk,
"");
686 ast_variable_append(cat, var);
692 ast_free(fieldnames);
694 ast_debug(1,
"PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
697 ast_mutex_unlock(&pgsql_lock);
702 static int update_pgsql(
const char *database,
const char *tablename,
const char *keyfield,
705 RAII_VAR(PGresult *, result, NULL, PQclear);
706 int numrows = 0, pgresult;
710 struct tables *table;
720 ast_log(LOG_WARNING,
"PostgreSQL RealTime: No table specified.\n");
724 if (!(table = find_table(database, tablename))) {
725 ast_log(LOG_ERROR,
"Table '%s' does not exist!!\n", tablename);
733 ast_mutex_lock(&pgsql_lock);
734 if (!pgsql_reconnect(database)) {
735 ast_mutex_unlock(&pgsql_lock);
736 release_table(table);
743 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
748 ast_mutex_unlock(&pgsql_lock);
749 release_table(table);
755 if (strcmp(column->name, field->
name) == 0) {
761 ast_log(LOG_ERROR,
"PostgreSQL RealTime: Updating on column '%s', but that column does not exist within the table '%s'!\n", field->
name, tablename);
762 ast_mutex_unlock(&pgsql_lock);
763 release_table(table);
770 ESCAPE_STRING(escapebuf, field->
value);
772 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
773 ast_mutex_unlock(&pgsql_lock);
774 release_table(table);
779 while ((field = field->
next)) {
780 if (!find_column(table, field->
name)) {
781 ast_log(LOG_NOTICE,
"Attempted to update column '%s' in table '%s', but column does not exist!\n", field->
name, tablename);
785 ESCAPE_STRING(escapebuf, field->
value);
787 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
788 ast_mutex_unlock(&pgsql_lock);
789 release_table(table);
795 release_table(table);
797 ESCAPE_STRING(escapebuf, lookup);
799 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", lookup);
800 ast_mutex_unlock(&pgsql_lock);
810 ast_mutex_unlock(&pgsql_lock);
813 ExecStatusType result_status = PQresultStatus(result);
814 if (result_status != PGRES_COMMAND_OK
815 && result_status != PGRES_TUPLES_OK
816 && result_status != PGRES_NONFATAL_ERROR) {
818 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
820 ast_debug(1,
"PostgreSQL RealTime: Query Failed because: %s (%s)\n",
821 PQresultErrorMessage(result), PQresStatus(result_status));
822 ast_mutex_unlock(&pgsql_lock);
827 numrows = atoi(PQcmdTuples(result));
828 ast_mutex_unlock(&pgsql_lock);
830 ast_debug(1,
"PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
839 return (
int) numrows;
844 static int update2_pgsql(
const char *database,
const char *tablename,
const struct ast_variable *lookup_fields,
const struct ast_variable *update_fields)
846 RAII_VAR(PGresult *, result, NULL, PQclear);
847 int numrows = 0, pgresult, first = 1;
852 struct tables *table;
861 ast_log(LOG_WARNING,
"PostgreSQL RealTime: No table specified.\n");
865 if (!escapebuf || !sql || !where) {
870 if (!(table = find_table(database, tablename))) {
871 ast_log(LOG_ERROR,
"Table '%s' does not exist!!\n", tablename);
879 ast_mutex_lock(&pgsql_lock);
880 if (!pgsql_reconnect(database)) {
881 ast_mutex_unlock(&pgsql_lock);
882 release_table(table);
889 for (field = lookup_fields; field; field = field->
next) {
890 if (!find_column(table, field->
name)) {
891 ast_log(LOG_ERROR,
"Attempted to update based on criteria column '%s' (%s@%s), but that column does not exist!\n", field->
name, tablename, database);
892 ast_mutex_unlock(&pgsql_lock);
893 release_table(table);
897 ESCAPE_STRING(escapebuf, field->
value);
899 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
900 ast_mutex_unlock(&pgsql_lock);
901 release_table(table);
910 "PostgreSQL RealTime: Realtime update requires at least 1 parameter and 1 value to search on.\n");
915 ast_mutex_unlock(&pgsql_lock);
916 release_table(table);
922 for (field = update_fields; field; field = field->
next) {
924 if (!find_column(table, field->
name)) {
925 ast_log(LOG_NOTICE,
"Attempted to update column '%s' in table '%s@%s', but column does not exist!\n", field->
name, tablename, database);
929 ESCAPE_STRING(escapebuf, field->
value);
931 ast_log(LOG_ERROR,
"PostgreSQL RealTime: detected invalid input: '%s'\n", field->
value);
932 ast_mutex_unlock(&pgsql_lock);
933 release_table(table);
940 release_table(table);
948 ast_mutex_unlock(&pgsql_lock);
952 numrows = atoi(PQcmdTuples(result));
953 ast_mutex_unlock(&pgsql_lock);
955 ast_debug(1,
"PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
964 return (
int) numrows;
970 static int store_pgsql(
const char *database,
const char *table,
const struct ast_variable *fields)
972 RAII_VAR(PGresult *, result, NULL, PQclear);
987 ast_log(LOG_WARNING,
"PostgreSQL RealTime: No table specified.\n");
995 ast_mutex_lock(&pgsql_lock);
996 if (!pgsql_reconnect(database)) {
997 ast_mutex_unlock(&pgsql_lock);
1003 ast_log(LOG_WARNING,
1004 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
1006 PQfinish(pgsqlConn);
1009 ast_mutex_unlock(&pgsql_lock);
1015 ESCAPE_STRING(buf, field->
name);
1017 ESCAPE_STRING(buf, field->
value);
1019 while ((field = field->
next)) {
1020 ESCAPE_STRING(buf, field->
name);
1022 ESCAPE_STRING(buf, field->
value);
1031 ast_mutex_unlock(&pgsql_lock);
1035 numrows = atoi(PQcmdTuples(result));
1036 ast_mutex_unlock(&pgsql_lock);
1038 ast_debug(1,
"PostgreSQL RealTime: row inserted on table: %s.\n", table);
1053 static int destroy_pgsql(
const char *database,
const char *table,
const char *keyfield,
const char *lookup,
const struct ast_variable *fields)
1055 RAII_VAR(PGresult *, result, NULL, PQclear);
1069 ast_log(LOG_WARNING,
"PostgreSQL RealTime: No table specified.\n");
1077 ast_mutex_lock(&pgsql_lock);
1078 if (!pgsql_reconnect(database)) {
1079 ast_mutex_unlock(&pgsql_lock);
1084 if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup)) {
1085 ast_log(LOG_WARNING,
1086 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
1088 PQfinish(pgsqlConn);
1091 ast_mutex_unlock(&pgsql_lock);
1098 ESCAPE_STRING(buf1, keyfield);
1099 ESCAPE_STRING(buf2, lookup);
1101 for (field = fields; field; field = field->
next) {
1102 ESCAPE_STRING(buf1, field->
name);
1103 ESCAPE_STRING(buf2, field->
value);
1111 ast_mutex_unlock(&pgsql_lock);
1115 numrows = atoi(PQcmdTuples(result));
1116 ast_mutex_unlock(&pgsql_lock);
1118 ast_debug(1,
"PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
1127 return (
int) numrows;
1133 static struct ast_config *config_pgsql(
const char *database,
const char *table,
1135 struct ast_flags flags,
const char *suggested_incl,
const char *who_asked)
1137 RAII_VAR(PGresult *, result, NULL, PQclear);
1143 int last_cat_metric = 0;
1153 if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
1154 ast_log(LOG_WARNING,
"PostgreSQL RealTime: Cannot configure myself.\n");
1158 ast_str_set(&sql, 0,
"SELECT category, var_name, var_val, cat_metric FROM %s "
1159 "WHERE filename='%s' and commented=0 "
1160 "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ", table, file);
1164 ast_mutex_lock(&pgsql_lock);
1168 ast_mutex_unlock(&pgsql_lock);
1172 if ((num_rows = PQntuples(result)) > 0) {
1175 ast_debug(1,
"PostgreSQL RealTime: Found %ld rows.\n", num_rows);
1177 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
1178 char *field_category = PQgetvalue(result, rowIndex, 0);
1179 char *field_var_name = PQgetvalue(result, rowIndex, 1);
1180 char *field_var_val = PQgetvalue(result, rowIndex, 2);
1181 char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
1182 if (!strcmp(field_var_name,
"#include")) {
1183 if (!ast_config_internal_load(field_var_val, cfg, flags,
"", who_asked)) {
1184 ast_mutex_unlock(&pgsql_lock);
1190 if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
1196 last_cat_metric = atoi(field_cat_metric);
1199 new_v = ast_variable_new(field_var_name, field_var_val,
"");
1200 ast_variable_append(cur_cat, new_v);
1203 ast_log(LOG_WARNING,
1204 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
1207 ast_mutex_unlock(&pgsql_lock);
1212 static int require_pgsql(
const char *database,
const char *tablename, va_list ap)
1215 struct tables *table;
1226 table = find_table(database, tablename);
1228 ast_log(LOG_WARNING,
"Table %s not found in database. This table should exist if you're using realtime.\n", tablename);
1232 while ((elm = va_arg(ap,
char *))) {
1234 size = va_arg(ap,
unsigned int);
1236 if (strcmp(column->name, elm) == 0) {
1238 if ((strncmp(column->type,
"char", 4) == 0 || strncmp(column->type,
"varchar", 7) == 0 || strcmp(column->type,
"bpchar") == 0 || strncmp(column->type,
"text", 4) == 0)) {
1239 if (column->len != -1 && (size > column->len)) {
1240 ast_log(LOG_WARNING,
"Column '%s' should be at least %d long, but is only %d long.\n", column->name, size, column->len);
1243 }
else if (strncmp(column->type,
"int", 3) == 0) {
1244 int typesize = atoi(column->type + 3);
1246 if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
1247 type == RQ_INTEGER4 || type == RQ_UINTEGER4 ||
1248 type == RQ_INTEGER3 || type == RQ_UINTEGER3 ||
1249 type == RQ_UINTEGER2) && typesize == 2) {
1250 ast_log(LOG_WARNING,
"Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
1252 }
else if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
1253 type == RQ_UINTEGER4) && typesize == 4) {
1254 ast_log(LOG_WARNING,
"Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
1256 }
else if (type == RQ_CHAR || type == RQ_DATETIME || type == RQ_FLOAT || type == RQ_DATE) {
1257 ast_log(LOG_WARNING,
"Column '%s' is of the incorrect type: (need %s(%d) but saw %s)\n",
1259 type == RQ_CHAR ?
"char" :
1260 type == RQ_DATETIME ?
"datetime" :
1261 type == RQ_DATE ?
"date" :
1262 type == RQ_FLOAT ?
"float" :
1263 "a rather stiff drink ",
1264 size, column->type);
1267 }
else if (strncmp(column->type,
"float", 5) == 0) {
1269 ast_log(LOG_WARNING,
"Column %s cannot be a %s\n", column->name, column->type);
1272 }
else if (strncmp(column->type,
"timestamp", 9) == 0) {
1273 if (type != RQ_DATETIME && type != RQ_DATE) {
1274 ast_log(LOG_WARNING,
"Column %s cannot be a %s\n", column->name, column->type);
1278 ast_log(LOG_WARNING,
"Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
1286 if (requirements == RQ_WARN) {
1287 ast_log(LOG_WARNING,
"Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
1294 if (requirements == RQ_CREATECHAR || type == RQ_CHAR) {
1298 snprintf(fieldtype,
sizeof(fieldtype),
"CHAR(%u)",
1299 size < 15 ? size * 2 :
1300 (size * 3 / 2 > 255) ? 255 : size * 3 / 2);
1301 }
else if (type == RQ_INTEGER1 || type == RQ_UINTEGER1 || type == RQ_INTEGER2) {
1302 snprintf(fieldtype,
sizeof(fieldtype),
"INT2");
1303 }
else if (type == RQ_UINTEGER2 || type == RQ_INTEGER3 || type == RQ_UINTEGER3 || type == RQ_INTEGER4) {
1304 snprintf(fieldtype,
sizeof(fieldtype),
"INT4");
1305 }
else if (type == RQ_UINTEGER4 || type == RQ_INTEGER8) {
1306 snprintf(fieldtype,
sizeof(fieldtype),
"INT8");
1307 }
else if (type == RQ_UINTEGER8) {
1309 snprintf(fieldtype,
sizeof(fieldtype),
"CHAR(20)");
1310 }
else if (type == RQ_FLOAT) {
1311 snprintf(fieldtype,
sizeof(fieldtype),
"FLOAT8");
1312 }
else if (type == RQ_DATE) {
1313 snprintf(fieldtype,
sizeof(fieldtype),
"DATE");
1314 }
else if (type == RQ_DATETIME) {
1315 snprintf(fieldtype,
sizeof(fieldtype),
"TIMESTAMP");
1317 ast_log(LOG_ERROR,
"Unrecognized request type %d\n", type);
1321 ast_str_set(&sql, 0,
"ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, fieldtype);
1322 ast_debug(1,
"About to lock pgsql_lock (running alter on table '%s' to add column '%s')\n", tablename, elm);
1324 ast_mutex_lock(&pgsql_lock);
1325 ast_debug(1,
"About to run ALTER query on table '%s' to add column '%s'\n", tablename, elm);
1328 ast_mutex_unlock(&pgsql_lock);
1329 release_table(table);
1333 ast_debug(1,
"Finished running ALTER query on table '%s'\n", tablename);
1334 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
1335 ast_log(LOG_ERROR,
"Unable to add column: %s\n",
ast_str_buffer(sql));
1338 ast_mutex_unlock(&pgsql_lock);
1344 release_table(table);
1348 static int unload_pgsql(
const char *database,
const char *tablename)
1358 ast_debug(2,
"About to lock table cache list\n");
1360 ast_debug(2,
"About to traverse table cache list\n");
1362 if (strcmp(cur->name, tablename) == 0) {
1363 ast_debug(2,
"About to remove matching cache entry\n");
1365 ast_debug(2,
"About to destroy matching cache entry\n");
1367 ast_debug(1,
"Cache entry '%s@%s' destroyed\n", tablename, database);
1374 return cur ? 0 : -1;
1379 .load_func = config_pgsql,
1380 .realtime_func = realtime_pgsql,
1381 .realtime_multi_func = realtime_multi_pgsql,
1382 .store_func = store_pgsql,
1383 .destroy_func = destroy_pgsql,
1384 .update_func = update_pgsql,
1385 .update2_func = update2_pgsql,
1386 .require_func = require_pgsql,
1387 .unload_func = unload_pgsql,
1390 static int load_module(
void)
1392 if(!parse_config(0))
1402 static int unload_module(
void)
1404 struct tables *table;
1406 ast_mutex_lock(&pgsql_lock);
1409 PQfinish(pgsqlConn);
1416 ast_mutex_unlock(&pgsql_lock);
1421 destroy_table(table);
1428 static int reload(
void)
1435 static int parse_config(
int is_reload)
1442 if (config == CONFIG_STATUS_FILEUNCHANGED) {
1443 if (is_reload && pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
1444 ast_log(LOG_WARNING,
"PostgreSQL RealTime: Not connected\n");
1449 if (config == CONFIG_STATUS_FILEMISSING || config == CONFIG_STATUS_FILEINVALID) {
1450 ast_log(LOG_WARNING,
"Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
1454 ast_mutex_lock(&pgsql_lock);
1458 PQfinish(pgsqlConn);
1462 if (!(s = ast_variable_retrieve(config,
"general",
"dbuser"))) {
1463 ast_log(LOG_WARNING,
1464 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
1465 strcpy(dbuser,
"asterisk");
1470 if (!(s = ast_variable_retrieve(config,
"general",
"dbpass"))) {
1471 ast_log(LOG_WARNING,
1472 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
1473 strcpy(dbpass,
"asterisk");
1478 if (!(s = ast_variable_retrieve(config,
"general",
"dbhost"))) {
1479 ast_log(LOG_WARNING,
1480 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
1486 if (!(s = ast_variable_retrieve(config,
"general",
"dbname"))) {
1487 ast_log(LOG_WARNING,
1488 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
1489 strcpy(dbname,
"asterisk");
1494 if (!(s = ast_variable_retrieve(config,
"general",
"dbport"))) {
1495 ast_log(LOG_WARNING,
1496 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
1502 if (!(s = ast_variable_retrieve(config,
"general",
"dbappname"))) {
1503 dbappname[0] =
'\0';
1508 if (!ast_strlen_zero(dbhost)) {
1510 }
else if (!(s = ast_variable_retrieve(config,
"general",
"dbsock"))) {
1511 ast_log(LOG_WARNING,
1512 "PostgreSQL RealTime: No database socket found, using '/tmp/.s.PGSQL.%d' as default.\n", dbport);
1513 strcpy(dbsock,
"/tmp");
1518 if (!(s = ast_variable_retrieve(config,
"general",
"requirements"))) {
1519 ast_log(LOG_WARNING,
1520 "PostgreSQL RealTime: no requirements setting found, using 'warn' as default.\n");
1521 requirements = RQ_WARN;
1522 }
else if (!strcasecmp(s,
"createclose")) {
1523 requirements = RQ_CREATECLOSE;
1524 }
else if (!strcasecmp(s,
"createchar")) {
1525 requirements = RQ_CREATECHAR;
1529 s = ast_variable_retrieve(config,
"general",
"order_multi_row_results_by_initial_column");
1530 order_multi_row_results_by_initial_column = !s ||
ast_true(s);
1534 if (DEBUG_ATLEAST(1)) {
1535 if (!ast_strlen_zero(dbhost)) {
1536 ast_log(LOG_DEBUG,
"PostgreSQL RealTime Host: %s\n", dbhost);
1537 ast_log(LOG_DEBUG,
"PostgreSQL RealTime Port: %i\n", dbport);
1539 ast_log(LOG_DEBUG,
"PostgreSQL RealTime Socket: %s\n", dbsock);
1541 ast_log(LOG_DEBUG,
"PostgreSQL RealTime User: %s\n", dbuser);
1542 ast_log(LOG_DEBUG,
"PostgreSQL RealTime Password: %s\n", dbpass);
1543 ast_log(LOG_DEBUG,
"PostgreSQL RealTime DBName: %s\n", dbname);
1546 if (!pgsql_reconnect(NULL)) {
1547 ast_log(LOG_WARNING,
1548 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
1549 ast_debug(1,
"PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
1552 ast_verb(2,
"PostgreSQL RealTime reloaded.\n");
1555 ast_mutex_unlock(&pgsql_lock);
1560 static int pgsql_reconnect(
const char *database)
1562 char my_database[50];
1569 if (PQstatus(pgsqlConn) == CONNECTION_OK) {
1574 PQfinish(pgsqlConn);
1579 if ((!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
1583 ast_log(LOG_ERROR,
"PostgreSQL RealTime: Failed to allocate memory for connection string.\n");
1587 ast_str_set(&conn_info, 0,
"host=%s port=%d dbname=%s user=%s",
1588 S_OR(dbhost, dbsock), dbport, my_database, dbuser);
1590 if (!ast_strlen_zero(dbappname)) {
1591 ast_str_append(&conn_info, 0,
" application_name=%s", dbappname);
1594 if (!ast_strlen_zero(dbpass)) {
1599 ast_free(conn_info);
1602 ast_debug(1,
"pgsqlConn=%p\n", pgsqlConn);
1603 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1604 ast_debug(1,
"PostgreSQL RealTime: Successfully connected to database.\n");
1605 connect_time = time(NULL);
1606 version = PQserverVersion(pgsqlConn);
1610 "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
1611 my_database, dbhost, PQresultErrorMessage(NULL));
1615 ast_debug(1,
"PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
1628 e->
command =
"realtime show pgsql cache";
1630 "Usage: realtime show pgsql cache [<table>]\n"
1631 " Shows table cache for the PostgreSQL RealTime driver\n";
1637 l = strlen(a->word);
1641 if (!strncasecmp(a->word, cur->name, l) && ++which > a->n) {
1654 ast_cli(a->fd,
"%s\n", cur->name);
1657 }
else if (a->argc == 5) {
1659 if ((cur = find_table(NULL, a->argv[4]))) {
1661 ast_cli(a->fd,
"Columns for Table Cache '%s':\n", a->argv[4]);
1662 ast_cli(a->fd,
"%-20.20s %-20.20s %-3.3s %-8.8s\n",
"Name",
"Type",
"Len",
"Nullable");
1664 ast_cli(a->fd,
"%-20.20s %-20.20s %3d %-8.8s\n", col->name, col->type, col->len, col->notnull ?
"NOT NULL" :
"");
1668 ast_cli(a->fd,
"No such table '%s'\n", a->argv[4]);
1676 char connection_info[256];
1677 char credentials[100] =
"";
1679 int is_connected = 0, ctimesec = time(NULL) - connect_time;
1683 e->
command =
"realtime show pgsql status";
1685 "Usage: realtime show pgsql status\n"
1686 " Shows connection information for the PostgreSQL RealTime driver\n";
1693 return CLI_SHOWUSAGE;
1695 if (!ast_strlen_zero(dbhost))
1696 snprintf(connection_info,
sizeof(connection_info),
"%s@%s, port %d", dbname, dbhost, dbport);
1697 else if (!ast_strlen_zero(dbsock))
1698 snprintf(connection_info,
sizeof(connection_info),
"%s on socket file %s", dbname, dbsock);
1700 snprintf(connection_info,
sizeof(connection_info),
"%s@%s", dbname, dbhost);
1702 if (!ast_strlen_zero(dbuser))
1703 snprintf(credentials,
sizeof(credentials),
" with username %s", dbuser);
1705 ast_mutex_lock(&pgsql_lock);
1706 is_connected = (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK);
1707 ast_mutex_unlock(&pgsql_lock);
1710 snprintf(buf,
sizeof(buf),
"Connected to %s%s for ", connection_info, credentials);
1714 ast_cli(a->fd,
"Unable to connect %s%s\n", connection_info, credentials);
1720 AST_MODULE_INFO(
ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER,
"PostgreSQL RealTime Configuration Driver",
1721 .support_level = AST_MODULE_SUPPORT_EXTENDED,
1722 .load = load_module,
1723 .unload = unload_module,
1726 .requires =
"extconfig",
struct ast_variable * next
#define AST_THREADSTORAGE(name)
Define a thread storage variable.
require_type
Types used in ast_realtime_require_field.
char * ast_realtime_decode_chunk(char *chunk)
Remove standard encoding from realtime values, which ensures that a semicolon embedded within a singl...
#define AST_LIST_LOCK(head)
Locks a list.
Asterisk locking-related definitions:
Asterisk main include file. File version handling, generic pbx functions.
int ast_cli_unregister_multiple(struct ast_cli_entry *e, int len)
Unregister multiple commands.
descriptor for a cli entry.
#define AST_LIST_UNLOCK(head)
Attempts to unlock a list.
#define ast_rwlock_init(rwlock)
wrapper for rwlock with tracking enabled
char * ast_str_buffer(const struct ast_str *buf)
Returns the string buffer within the ast_str buf.
Structure for variables, used for configurations and for channel variables.
static int _pgsql_exec(const char *database, const char *tablename, const char *sql, PGresult **result)
Helper function for pgsql_exec. For running queries, use pgsql_exec()
int ast_config_engine_deregister(struct ast_config_engine *del)
Deregister config engine.
int ast_config_engine_register(struct ast_config_engine *newconfig)
Register config engine.
int ast_str_append(struct ast_str **buf, ssize_t max_len, const char *fmt,...)
Append to a thread local dynamic string.
#define ast_cli_register_multiple(e, len)
Register multiple commands.
#define ast_strdup(str)
A wrapper for strdup()
Generic File Format Support. Should be included by clients of the file handling routines. File service providers should instead include mod_format.h.
#define AST_LIST_TRAVERSE_SAFE_END
Closes a safe loop traversal block.
Configuration engine structure, used to define realtime drivers.
void ast_category_append(struct ast_config *config, struct ast_category *category)
Appends a category to a config.
#define ast_category_new_anonymous()
Create a nameless category that is not backed by a file.
int ast_str_set(struct ast_str **buf, ssize_t max_len, const char *fmt,...)
Set a dynamic string using variable arguments.
Configuration File Parser.
#define ast_config_load(filename, flags)
Load a config file.
General Asterisk PBX channel definitions.
#define ast_strdupa(s)
duplicate a string in memory from the stack
char * ast_strip(char *s)
Strip leading/trailing whitespace from a string.
#define AST_LIST_REMOVE_CURRENT(field)
Removes the current entry from a list during a traversal.
#define ast_debug(level,...)
Log a DEBUG message.
#define AST_LIST_REMOVE_HEAD(head, field)
Removes and returns the head entry from a list.
struct ast_config * ast_config_new(void)
Create a new base configuration structure.
Core PBX routines and definitions.
#define AST_LIST_HEAD_STATIC(name, type)
Defines a structure to be used to hold a list of specified type, statically initialized.
#define ast_alloca(size)
call __builtin_alloca to ensure we get gcc builtin semantics
#define AST_LIST_HEAD_NOLOCK(name, type)
Defines a structure to be used to hold a list of specified type (with no lock).
#define AST_LIST_INSERT_TAIL(head, elm, field)
Appends a list entry to the tail of a list.
int attribute_pure ast_true(const char *val)
Make sure something is true. Determine if a string containing a boolean value is "true". This function checks to see whether a string passed to it is an indication of an "true" value. It checks to see if the string is "yes", "true", "y", "t", "on" or "1".
Support for dynamic strings.
#define AST_LIST_TRAVERSE(head, var, field)
Loops over (traverses) the entries in a list.
#define AST_LIST_ENTRY(type)
Declare a forward link structure inside a list entry.
#define ast_calloc(num, len)
A wrapper for calloc()
#define ast_category_new_dynamic(name)
Create a category that is not backed by a file.
Module has failed to load, may be in an inconsistent state.
static int pgsql_exec(const char *database, const char *tablename, const char *sql, PGresult **result)
Do a postgres query, with reconnection support.
Structure used to handle boolean flags.
void ast_cli_print_timestr_fromseconds(int fd, int seconds, const char *prefix)
Print on cli a duration in seconds in format s year(s), s week(s), s day(s), s hour(s), s second(s)
#define AST_LIST_HEAD_INIT_NOLOCK(head)
Initializes a list head structure.
Structure for rwlock and tracking information.
Standard Command Line Interface.
void ast_copy_string(char *dst, const char *src, size_t size)
Size-limited null-terminating string copy.
#define S_OR(a, b)
returns the equivalent of logic or for strings: first one if not empty, otherwise second one...
#define AST_LIST_TRAVERSE_SAFE_BEGIN(head, var, field)
Loops safely over (traverses) the entries in a list.
struct ast_str * ast_str_thread_get(struct ast_threadstorage *ts, size_t init_len)
Retrieve a thread locally stored dynamic string.
void ast_config_destroy(struct ast_config *cfg)
Destroys a config.
int ast_rq_is_int(require_type type)
Check if require type is an integer type.
#define ASTERISK_GPL_KEY
The text the key() function should return.
Asterisk module definitions.
#define RAII_VAR(vartype, varname, initval, dtor)
Declare a variable that will call a destructor function when it goes out of scope.
#define ast_str_create(init_len)
Create a malloc'ed dynamic length string.