main/cli.c: Refactor function to print seconds formatted
[asterisk/asterisk.git] / res / res_config_pgsql.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2016, Digium, Inc.
5  *
6  * Manuel Guesdon <mguesdon@oxymium.net> - PostgreSQL RealTime Driver Author/Adaptor
7  * Mark Spencer <markster@digium.com>  - Asterisk Author
8  * Matthew Boehm <mboehm@cytelcom.com> - MySQL RealTime Driver Author
9  *
10  * res_config_pgsql.c <PostgreSQL plugin for RealTime configuration engine>
11  *
12  * v1.0   - (07-11-05) - Initial version based on res_config_mysql v2.0
13  */
14
15 /*! \file
16  *
17  * \brief PostgreSQL plugin for Asterisk RealTime Architecture
18  *
19  * \author Mark Spencer <markster@digium.com>
20  * \author Manuel Guesdon <mguesdon@oxymium.net> - PostgreSQL RealTime Driver Author/Adaptor
21  *
22  * PostgreSQL http://www.postgresql.org
23  */
24
25 /*** MODULEINFO
26         <depend>pgsql</depend>
27         <support_level>extended</support_level>
28  ***/
29
30 #include "asterisk.h"
31
32 ASTERISK_REGISTER_FILE()
33
34 #include <libpq-fe.h>                   /* PostgreSQL */
35
36 #include "asterisk/file.h"
37 #include "asterisk/channel.h"
38 #include "asterisk/pbx.h"
39 #include "asterisk/config.h"
40 #include "asterisk/module.h"
41 #include "asterisk/lock.h"
42 #include "asterisk/utils.h"
43 #include "asterisk/cli.h"
44
45 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
46 AST_THREADSTORAGE(sql_buf);
47 AST_THREADSTORAGE(findtable_buf);
48 AST_THREADSTORAGE(where_buf);
49 AST_THREADSTORAGE(escapebuf_buf);
50 AST_THREADSTORAGE(semibuf_buf);
51
52 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
53
54 static PGconn *pgsqlConn = NULL;
55 static int version;
56 #define has_schema_support      (version > 70300 ? 1 : 0)
57
58 #define MAX_DB_OPTION_SIZE 64
59
60 struct columns {
61         char *name;
62         char *type;
63         int len;
64         unsigned int notnull:1;
65         unsigned int hasdefault:1;
66         AST_LIST_ENTRY(columns) list;
67 };
68
69 struct tables {
70         ast_rwlock_t lock;
71         AST_LIST_HEAD_NOLOCK(psql_columns, columns) columns;
72         AST_LIST_ENTRY(tables) list;
73         char name[0];
74 };
75
76 static AST_LIST_HEAD_STATIC(psql_tables, tables);
77
78 static char dbhost[MAX_DB_OPTION_SIZE] = "";
79 static char dbuser[MAX_DB_OPTION_SIZE] = "";
80 static char dbpass[MAX_DB_OPTION_SIZE] = "";
81 static char dbname[MAX_DB_OPTION_SIZE] = "";
82 static char dbappname[MAX_DB_OPTION_SIZE] = "";
83 static char dbsock[MAX_DB_OPTION_SIZE] = "";
84 static int dbport = 5432;
85 static time_t connect_time = 0;
86
87 static int parse_config(int reload);
88 static int pgsql_reconnect(const char *database);
89 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
90 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
91
92 static enum { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR } requirements;
93
94 static struct ast_cli_entry cli_realtime[] = {
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"),
97 };
98
99 #define ESCAPE_STRING(buffer, stringname) \
100         do { \
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); \
108                         } else { \
109                                 ast_str_append(&semi, 0, "%c", *chunk); \
110                         } \
111                 } \
112                 if (ast_str_strlen(semi) > (ast_str_size(buffer) - 1) / 2) { \
113                         ast_str_make_space(&buffer, ast_str_strlen(semi) * 2 + 1); \
114                 } \
115                 PQescapeStringConn(pgsqlConn, ast_str_buffer(buffer), ast_str_buffer(semi), ast_str_size(buffer), &pgresult); \
116         } while (0)
117
118 static void destroy_table(struct tables *table)
119 {
120         struct columns *column;
121         ast_rwlock_wrlock(&table->lock);
122         while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
123                 ast_free(column);
124         }
125         ast_rwlock_unlock(&table->lock);
126         ast_rwlock_destroy(&table->lock);
127         ast_free(table);
128 }
129
130 /*! \brief Helper function for pgsql_exec.  For running querys, use pgsql_exec()
131  *
132  *  Connect if not currently connected.  Run the given query.
133  *
134  *  \param database   database name we are connected to (used for error logging)
135  *  \param tablename  table  name we are connected to (used for error logging)
136  *  \param sql        sql query string to execute
137  *  \param result     pointer for where to store the result handle
138  *
139  *  \return -1 on fatal query error
140  *  \return -2 on query failure that resulted in disconnection
141  *  \return 0 on success
142  *
143  *  \note see pgsql_exec for full example
144  */
145 static int _pgsql_exec(const char *database, const char *tablename, const char *sql, PGresult **result)
146 {
147         ExecStatusType result_status;
148
149         if (!pgsqlConn) {
150                 ast_debug(1, "PostgreSQL connection not defined, connecting\n");
151
152                 if (pgsql_reconnect(database) != 1) {
153                         ast_log(LOG_NOTICE, "reconnect failed\n");
154                         *result = NULL;
155                         return -1;
156                 }
157
158                 ast_debug(1, "PostgreSQL connection successful\n");
159         }
160
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) {
166
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));
172
173                 /* we may have tried to run a command on a disconnected/disconnecting handle */
174                 /* are we no longer connected to the database... if not try again */
175                 if (PQstatus(pgsqlConn) != CONNECTION_OK) {
176                         PQfinish(pgsqlConn);
177                         pgsqlConn = NULL;
178                         return -2;
179                 }
180
181                 /* connection still okay, which means the query is just plain bad */
182                 return -1;
183         }
184
185         ast_debug(1, "PostgreSQL query successful: %s\n", sql);
186         return 0;
187 }
188
189 /*! \brief Do a postgres query, with reconnection support
190  *
191  *  Connect if not currently connected.  Run the given query
192  *  and if we're disconnected afterwards, reconnect and query again.
193  *
194  *  \param database   database name we are connected to (used for error logging)
195  *  \param tablename  table  name we are connected to (used for error logging)
196  *  \param sql        sql query string to execute
197  *  \param result     pointer for where to store the result handle
198  *
199  *  \return -1 on query failure
200  *  \return 0 on success
201  *
202  *  \code
203  *      int i, rows;
204  *      PGresult *result;
205  *      char *field_name, *field_type, *field_len, *field_notnull, *field_default;
206  *
207  *      pgsql_exec("db", "table", "SELECT 1", &result)
208  *
209  *      rows = PQntuples(result);
210  *      for (i = 0; i < rows; i++) {
211  *              field_name    = PQgetvalue(result, i, 0);
212  *              field_type    = PQgetvalue(result, i, 1);
213  *              field_len     = PQgetvalue(result, i, 2);
214  *              field_notnull = PQgetvalue(result, i, 3);
215  *              field_default = PQgetvalue(result, i, 4);
216  *      }
217  *  \endcode
218  */
219 static int pgsql_exec(const char *database, const char *tablename, const char *sql, PGresult **result)
220 {
221         int attempts = 0;
222         int res;
223
224         /* Try the query, note failure if any */
225         /* On first failure, reconnect and try again (_pgsql_exec handles reconnect) */
226         /* On second failure, treat as fatal query error */
227
228         while (attempts++ < 2) {
229                 ast_debug(1, "PostgreSQL query attempt %d\n", attempts);
230                 res = _pgsql_exec(database, tablename, sql, result);
231
232                 if (res == 0) {
233                         if (attempts > 1) {
234                                 ast_log(LOG_NOTICE, "PostgreSQL RealTime: Query finally succeeded: %s\n", sql);
235                         }
236
237                         return 0;
238                 }
239
240                 if (res == -1) {
241                         return -1; /* Still connected to db, but could not process query (fatal error) */
242                 }
243
244                 /* res == -2 (query on a disconnected handle) */
245                 ast_debug(1, "PostgreSQL query attempt %d failed, trying again\n", attempts);
246         }
247
248         return -1;
249 }
250
251 static struct tables *find_table(const char *database, const char *orig_tablename)
252 {
253         struct columns *column;
254         struct tables *table;
255         struct ast_str *sql = ast_str_thread_get(&findtable_buf, 330);
256         RAII_VAR(PGresult *, result, NULL, PQclear);
257         int exec_result;
258         char *fname, *ftype, *flen, *fnotnull, *fdef;
259         int i, rows;
260
261         AST_LIST_LOCK(&psql_tables);
262         AST_LIST_TRAVERSE(&psql_tables, table, list) {
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");
267                         AST_LIST_UNLOCK(&psql_tables);
268                         return table;
269                 }
270         }
271
272         if (database == NULL) {
273                 return NULL;
274         }
275
276         ast_debug(1, "Table '%s' not found in cache, querying now\n", orig_tablename);
277
278         /* Not found, scan the table */
279         if (has_schema_support) {
280                 char *schemaname, *tablename, *tmp_schemaname, *tmp_tablename;
281                 if (strchr(orig_tablename, '.')) {
282                         tmp_schemaname = ast_strdupa(orig_tablename);
283                         tmp_tablename = strchr(tmp_schemaname, '.');
284                         *tmp_tablename++ = '\0';
285                 } else {
286                         tmp_schemaname = "";
287                         tmp_tablename = ast_strdupa(orig_tablename);
288                 }
289
290                 tablename = ast_alloca(strlen(tmp_tablename) * 2 + 1);
291                 PQescapeStringConn(pgsqlConn, tablename, tmp_tablename, strlen(tmp_tablename), NULL);
292                 schemaname = ast_alloca(strlen(tmp_schemaname) * 2 + 1);
293                 PQescapeStringConn(pgsqlConn, schemaname, tmp_schemaname, strlen(tmp_schemaname), NULL);
294
295                 ast_str_set(&sql, 0, "SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.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",
296                         tablename,
297                         ast_strlen_zero(schemaname) ? "" : "'", ast_strlen_zero(schemaname) ? "current_schema()" : schemaname, ast_strlen_zero(schemaname) ? "" : "'");
298         } else {
299                 char *tablename;
300                 tablename = ast_alloca(strlen(orig_tablename) * 2 + 1);
301                 PQescapeStringConn(pgsqlConn, tablename, orig_tablename, strlen(orig_tablename), NULL);
302
303                 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);
304         }
305
306         ast_mutex_lock(&pgsql_lock);
307         exec_result = pgsql_exec(database, orig_tablename, ast_str_buffer(sql), &result);
308         ast_mutex_unlock(&pgsql_lock);
309         ast_debug(1, "Query of table structure complete.  Now retrieving results.\n");
310         if (exec_result != 0) {
311                 ast_log(LOG_ERROR, "Failed to query database columns for table %s\n", orig_tablename);
312                 AST_LIST_UNLOCK(&psql_tables);
313                 return NULL;
314         }
315
316         if (!(table = ast_calloc(1, sizeof(*table) + strlen(orig_tablename) + 1))) {
317                 ast_log(LOG_ERROR, "Unable to allocate memory for new table structure\n");
318                 AST_LIST_UNLOCK(&psql_tables);
319                 return NULL;
320         }
321         strcpy(table->name, orig_tablename); /* SAFE */
322         ast_rwlock_init(&table->lock);
323         AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
324
325         rows = PQntuples(result);
326         for (i = 0; i < rows; i++) {
327                 fname = PQgetvalue(result, i, 0);
328                 ftype = PQgetvalue(result, i, 1);
329                 flen = PQgetvalue(result, i, 2);
330                 fnotnull = PQgetvalue(result, i, 3);
331                 fdef = PQgetvalue(result, i, 4);
332                 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
333
334                 if (!(column = ast_calloc(1, sizeof(*column) + strlen(fname) + strlen(ftype) + 2))) {
335                         ast_log(LOG_ERROR, "Unable to allocate column element for %s, %s\n", orig_tablename, fname);
336                         destroy_table(table);
337                         AST_LIST_UNLOCK(&psql_tables);
338                         return NULL;
339                 }
340
341                 if (strcmp(flen, "-1") == 0) {
342                         /* Some types, like chars, have the length stored in a different field */
343                         flen = PQgetvalue(result, i, 5);
344                         sscanf(flen, "%30d", &column->len);
345                         column->len -= 4;
346                 } else {
347                         sscanf(flen, "%30d", &column->len);
348                 }
349                 column->name = (char *)column + sizeof(*column);
350                 column->type = (char *)column + sizeof(*column) + strlen(fname) + 1;
351                 strcpy(column->name, fname);
352                 strcpy(column->type, ftype);
353                 if (*fnotnull == 't') {
354                         column->notnull = 1;
355                 } else {
356                         column->notnull = 0;
357                 }
358                 if (!ast_strlen_zero(fdef)) {
359                         column->hasdefault = 1;
360                 } else {
361                         column->hasdefault = 0;
362                 }
363                 AST_LIST_INSERT_TAIL(&table->columns, column, list);
364         }
365
366         AST_LIST_INSERT_TAIL(&psql_tables, table, list);
367         ast_rwlock_rdlock(&table->lock);
368         AST_LIST_UNLOCK(&psql_tables);
369         return table;
370 }
371
372 #define release_table(table) ast_rwlock_unlock(&(table)->lock);
373
374 static struct columns *find_column(struct tables *t, const char *colname)
375 {
376         struct columns *column;
377
378         /* Check that the column exists in the table */
379         AST_LIST_TRAVERSE(&t->columns, column, list) {
380                 if (strcmp(column->name, colname) == 0) {
381                         return column;
382                 }
383         }
384         return NULL;
385 }
386
387 static struct ast_variable *realtime_pgsql(const char *database, const char *tablename, const struct ast_variable *fields)
388 {
389         RAII_VAR(PGresult *, result, NULL, PQclear);
390         int num_rows = 0, pgresult;
391         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
392         struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 100);
393         char *stringp;
394         char *chunk;
395         char *op;
396         const struct ast_variable *field = fields;
397         struct ast_variable *var = NULL, *prev = NULL;
398
399         /*
400          * Ignore database from the extconfig.conf since it was
401          * configured by res_pgsql.conf.
402          */
403         database = dbname;
404
405         if (!tablename) {
406                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
407                 return NULL;
408         }
409
410         /* Get the first parameter and first value in our list of passed paramater/value pairs */
411         if (!field) {
412                 ast_log(LOG_WARNING,
413                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
414                 if (pgsqlConn) {
415                         PQfinish(pgsqlConn);
416                         pgsqlConn = NULL;
417                 }
418                 return NULL;
419         }
420
421         /* Create the first part of the query using the first parameter/value pairs we just extracted
422            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
423         op = strchr(field->name, ' ') ? "" : " =";
424
425         ESCAPE_STRING(escapebuf, field->value);
426         if (pgresult) {
427                 ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
428                 return NULL;
429         }
430
431         ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", tablename, field->name, op, ast_str_buffer(escapebuf));
432         while ((field = field->next)) {
433                 if (!strchr(field->name, ' '))
434                         op = " =";
435                 else
436                         op = "";
437
438                 ESCAPE_STRING(escapebuf, field->value);
439                 if (pgresult) {
440                         ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
441                         return NULL;
442                 }
443
444                 ast_str_append(&sql, 0, " AND %s%s '%s'", field->name, op, ast_str_buffer(escapebuf));
445         }
446
447         /* We now have our complete statement; Lets connect to the server and execute it. */
448         ast_mutex_lock(&pgsql_lock);
449
450         if (pgsql_exec(database, tablename, ast_str_buffer(sql), &result) != 0) {
451                 ast_mutex_unlock(&pgsql_lock);
452                 return NULL;
453         }
454
455         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, ast_str_buffer(sql));
456
457         if ((num_rows = PQntuples(result)) > 0) {
458                 int i = 0;
459                 int rowIndex = 0;
460                 int numFields = PQnfields(result);
461                 char **fieldnames = NULL;
462
463                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
464
465                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
466                         ast_mutex_unlock(&pgsql_lock);
467                         return NULL;
468                 }
469                 for (i = 0; i < numFields; i++)
470                         fieldnames[i] = PQfname(result, i);
471                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
472                         for (i = 0; i < numFields; i++) {
473                                 stringp = PQgetvalue(result, rowIndex, i);
474                                 while (stringp) {
475                                         chunk = strsep(&stringp, ";");
476                                         if (chunk && !ast_strlen_zero(ast_realtime_decode_chunk(ast_strip(chunk)))) {
477                                                 if (prev) {
478                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
479                                                         if (prev->next) {
480                                                                 prev = prev->next;
481                                                         }
482                                                 } else {
483                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
484                                                 }
485                                         }
486                                 }
487                         }
488                 }
489                 ast_free(fieldnames);
490         } else {
491                 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s@%s.\n", tablename, database);
492         }
493
494         ast_mutex_unlock(&pgsql_lock);
495
496         return var;
497 }
498
499 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, const struct ast_variable *fields)
500 {
501         RAII_VAR(PGresult *, result, NULL, PQclear);
502         int num_rows = 0, pgresult;
503         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
504         struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 100);
505         const struct ast_variable *field = fields;
506         const char *initfield = NULL;
507         char *stringp;
508         char *chunk;
509         char *op;
510         struct ast_variable *var = NULL;
511         struct ast_config *cfg = NULL;
512         struct ast_category *cat = NULL;
513
514         /*
515          * Ignore database from the extconfig.conf since it was
516          * configured by res_pgsql.conf.
517          */
518         database = dbname;
519
520         if (!table) {
521                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
522                 return NULL;
523         }
524
525         if (!(cfg = ast_config_new()))
526                 return NULL;
527
528         /* Get the first parameter and first value in our list of passed paramater/value pairs */
529         if (!field) {
530                 ast_log(LOG_WARNING,
531                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
532                 if (pgsqlConn) {
533                         PQfinish(pgsqlConn);
534                         pgsqlConn = NULL;
535                 }
536                 ast_config_destroy(cfg);
537                 return NULL;
538         }
539
540         initfield = ast_strdupa(field->name);
541         if ((op = strchr(initfield, ' '))) {
542                 *op = '\0';
543         }
544
545         /* Create the first part of the query using the first parameter/value pairs we just extracted
546            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
547
548         if (!strchr(field->name, ' '))
549                 op = " =";
550         else
551                 op = "";
552
553         ESCAPE_STRING(escapebuf, field->value);
554         if (pgresult) {
555                 ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
556                 ast_config_destroy(cfg);
557                 return NULL;
558         }
559
560         ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", table, field->name, op, ast_str_buffer(escapebuf));
561         while ((field = field->next)) {
562                 if (!strchr(field->name, ' '))
563                         op = " =";
564                 else
565                         op = "";
566
567                 ESCAPE_STRING(escapebuf, field->value);
568                 if (pgresult) {
569                         ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
570                         ast_config_destroy(cfg);
571                         return NULL;
572                 }
573
574                 ast_str_append(&sql, 0, " AND %s%s '%s'", field->name, op, ast_str_buffer(escapebuf));
575         }
576
577         if (initfield) {
578                 ast_str_append(&sql, 0, " ORDER BY %s", initfield);
579         }
580
581
582         /* We now have our complete statement; Lets connect to the server and execute it. */
583         ast_mutex_lock(&pgsql_lock);
584
585         if (pgsql_exec(database, table, ast_str_buffer(sql), &result) != 0) {
586                 ast_mutex_unlock(&pgsql_lock);
587                 ast_config_destroy(cfg);
588                 return NULL;
589         } else {
590                 ExecStatusType result_status = PQresultStatus(result);
591                 if (result_status != PGRES_COMMAND_OK
592                         && result_status != PGRES_TUPLES_OK
593                         && result_status != PGRES_NONFATAL_ERROR) {
594                         ast_log(LOG_WARNING,
595                                         "PostgreSQL RealTime: Failed to query %s@%s. Check debug for more info.\n", table, database);
596                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
597                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
598                                                 PQresultErrorMessage(result), PQresStatus(result_status));
599                         ast_mutex_unlock(&pgsql_lock);
600                         ast_config_destroy(cfg);
601                         return NULL;
602                 }
603         }
604
605         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, ast_str_buffer(sql));
606
607         if ((num_rows = PQntuples(result)) > 0) {
608                 int numFields = PQnfields(result);
609                 int i = 0;
610                 int rowIndex = 0;
611                 char **fieldnames = NULL;
612
613                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
614
615                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
616                         ast_mutex_unlock(&pgsql_lock);
617                         ast_config_destroy(cfg);
618                         return NULL;
619                 }
620                 for (i = 0; i < numFields; i++)
621                         fieldnames[i] = PQfname(result, i);
622
623                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
624                         var = NULL;
625                         if (!(cat = ast_category_new("","",99999)))
626                                 continue;
627                         for (i = 0; i < numFields; i++) {
628                                 stringp = PQgetvalue(result, rowIndex, i);
629                                 while (stringp) {
630                                         chunk = strsep(&stringp, ";");
631                                         if (chunk && !ast_strlen_zero(ast_realtime_decode_chunk(ast_strip(chunk)))) {
632                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
633                                                         ast_category_rename(cat, chunk);
634                                                 }
635                                                 var = ast_variable_new(fieldnames[i], chunk, "");
636                                                 ast_variable_append(cat, var);
637                                         }
638                                 }
639                         }
640                         ast_category_append(cfg, cat);
641                 }
642                 ast_free(fieldnames);
643         } else {
644                 ast_debug(1, "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
645         }
646
647         ast_mutex_unlock(&pgsql_lock);
648
649         return cfg;
650 }
651
652 static int update_pgsql(const char *database, const char *tablename, const char *keyfield,
653                                                 const char *lookup, const struct ast_variable *fields)
654 {
655         RAII_VAR(PGresult *, result, NULL, PQclear);
656         int numrows = 0, pgresult;
657         const struct ast_variable *field = fields;
658         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
659         struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 100);
660         struct tables *table;
661         struct columns *column = NULL;
662
663         /*
664          * Ignore database from the extconfig.conf since it was
665          * configured by res_pgsql.conf.
666          */
667         database = dbname;
668
669         if (!tablename) {
670                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
671                 return -1;
672         }
673
674         if (!(table = find_table(database, tablename))) {
675                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
676                 return -1;
677         }
678
679         /* Get the first parameter and first value in our list of passed paramater/value pairs */
680         if (!field) {
681                 ast_log(LOG_WARNING,
682                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
683                 if (pgsqlConn) {
684                         PQfinish(pgsqlConn);
685                         pgsqlConn = NULL;
686                 }
687                 release_table(table);
688                 return -1;
689         }
690
691         /* Check that the column exists in the table */
692         AST_LIST_TRAVERSE(&table->columns, column, list) {
693                 if (strcmp(column->name, field->name) == 0) {
694                         break;
695                 }
696         }
697
698         if (!column) {
699                 ast_log(LOG_ERROR, "PostgreSQL RealTime: Updating on column '%s', but that column does not exist within the table '%s'!\n", field->name, tablename);
700                 release_table(table);
701                 return -1;
702         }
703
704         /* Create the first part of the query using the first parameter/value pairs we just extracted
705            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
706
707         ESCAPE_STRING(escapebuf, field->value);
708         if (pgresult) {
709                 ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
710                 release_table(table);
711                 return -1;
712         }
713         ast_str_set(&sql, 0, "UPDATE %s SET %s = '%s'", tablename, field->name, ast_str_buffer(escapebuf));
714
715         while ((field = field->next)) {
716                 if (!find_column(table, field->name)) {
717                         ast_log(LOG_NOTICE, "Attempted to update column '%s' in table '%s', but column does not exist!\n", field->name, tablename);
718                         continue;
719                 }
720
721                 ESCAPE_STRING(escapebuf, field->value);
722                 if (pgresult) {
723                         ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
724                         release_table(table);
725                         return -1;
726                 }
727
728                 ast_str_append(&sql, 0, ", %s = '%s'", field->name, ast_str_buffer(escapebuf));
729         }
730         release_table(table);
731
732         ESCAPE_STRING(escapebuf, lookup);
733         if (pgresult) {
734                 ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", lookup);
735                 return -1;
736         }
737
738         ast_str_append(&sql, 0, " WHERE %s = '%s'", keyfield, ast_str_buffer(escapebuf));
739
740         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
741
742         /* We now have our complete statement; Lets connect to the server and execute it. */
743         ast_mutex_lock(&pgsql_lock);
744
745         if (pgsql_exec(database, tablename, ast_str_buffer(sql), &result) != 0) {
746                 ast_mutex_unlock(&pgsql_lock);
747                 return -1;
748         } else {
749                 ExecStatusType result_status = PQresultStatus(result);
750                 if (result_status != PGRES_COMMAND_OK
751                         && result_status != PGRES_TUPLES_OK
752                         && result_status != PGRES_NONFATAL_ERROR) {
753                         ast_log(LOG_WARNING,
754                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
755                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
756                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
757                                                 PQresultErrorMessage(result), PQresStatus(result_status));
758                         ast_mutex_unlock(&pgsql_lock);
759                         return -1;
760                 }
761         }
762
763         numrows = atoi(PQcmdTuples(result));
764         ast_mutex_unlock(&pgsql_lock);
765
766         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
767
768         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
769          * An integer greater than zero indicates the number of rows affected
770          * Zero indicates that no records were updated
771          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
772          */
773
774         if (numrows >= 0)
775                 return (int) numrows;
776
777         return -1;
778 }
779
780 static int update2_pgsql(const char *database, const char *tablename, const struct ast_variable *lookup_fields, const struct ast_variable *update_fields)
781 {
782         RAII_VAR(PGresult *, result, NULL, PQclear);
783         int numrows = 0, pgresult, first = 1;
784         struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 16);
785         const struct ast_variable *field;
786         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
787         struct ast_str *where = ast_str_thread_get(&where_buf, 100);
788         struct tables *table;
789
790         /*
791          * Ignore database from the extconfig.conf since it was
792          * configured by res_pgsql.conf.
793          */
794         database = dbname;
795
796         if (!tablename) {
797                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
798                 return -1;
799         }
800
801         if (!escapebuf || !sql || !where) {
802                 /* Memory error, already handled */
803                 return -1;
804         }
805
806         if (!(table = find_table(database, tablename))) {
807                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
808                 return -1;
809         }
810
811         ast_str_set(&sql, 0, "UPDATE %s SET", tablename);
812         ast_str_set(&where, 0, " WHERE");
813
814         for (field = lookup_fields; field; field = field->next) {
815                 if (!find_column(table, field->name)) {
816                         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);
817                         release_table(table);
818                         return -1;
819                 }
820
821                 ESCAPE_STRING(escapebuf, field->value);
822                 if (pgresult) {
823                         ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
824                         release_table(table);
825                         return -1;
826                 }
827                 ast_str_append(&where, 0, "%s %s='%s'", first ? "" : " AND", field->name, ast_str_buffer(escapebuf));
828                 first = 0;
829         }
830
831         if (first) {
832                 ast_log(LOG_WARNING,
833                                 "PostgreSQL RealTime: Realtime update requires at least 1 parameter and 1 value to search on.\n");
834                 if (pgsqlConn) {
835                         PQfinish(pgsqlConn);
836                         pgsqlConn = NULL;
837                 }
838                 release_table(table);
839                 return -1;
840         }
841
842         /* Now retrieve the columns to update */
843         first = 1;
844         for (field = update_fields; field; field = field->next) {
845                 /* If the column is not within the table, then skip it */
846                 if (!find_column(table, field->name)) {
847                         ast_log(LOG_NOTICE, "Attempted to update column '%s' in table '%s@%s', but column does not exist!\n", field->name, tablename, database);
848                         continue;
849                 }
850
851                 ESCAPE_STRING(escapebuf, field->value);
852                 if (pgresult) {
853                         ast_log(LOG_ERROR, "PostgreSQL RealTime: detected invalid input: '%s'\n", field->value);
854                         release_table(table);
855                         return -1;
856                 }
857
858                 ast_str_append(&sql, 0, "%s %s='%s'", first ? "" : ",", field->name, ast_str_buffer(escapebuf));
859                 first = 0;
860         }
861         release_table(table);
862
863         ast_str_append(&sql, 0, "%s", ast_str_buffer(where));
864
865         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
866
867         /* We now have our complete statement; connect to the server and execute it. */
868         if (pgsql_exec(database, tablename, ast_str_buffer(sql), &result) != 0) {
869                 ast_mutex_unlock(&pgsql_lock);
870                 return -1;
871         }
872
873         numrows = atoi(PQcmdTuples(result));
874         ast_mutex_unlock(&pgsql_lock);
875
876         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
877
878         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
879          * An integer greater than zero indicates the number of rows affected
880          * Zero indicates that no records were updated
881          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
882          */
883
884         if (numrows >= 0) {
885                 return (int) numrows;
886         }
887
888         return -1;
889 }
890
891 static int store_pgsql(const char *database, const char *table, const struct ast_variable *fields)
892 {
893         RAII_VAR(PGresult *, result, NULL, PQclear);
894         int numrows;
895         struct ast_str *buf = ast_str_thread_get(&escapebuf_buf, 256);
896         struct ast_str *sql1 = ast_str_thread_get(&sql_buf, 256);
897         struct ast_str *sql2 = ast_str_thread_get(&where_buf, 256);
898         int pgresult;
899         const struct ast_variable *field = fields;
900
901         /*
902          * Ignore database from the extconfig.conf since it was
903          * configured by res_pgsql.conf.
904          */
905         database = dbname;
906
907         if (!table) {
908                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
909                 return -1;
910         }
911
912         /* Get the first parameter and first value in our list of passed paramater/value pairs */
913         if (!field) {
914                 ast_log(LOG_WARNING,
915                                 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
916                 if (pgsqlConn) {
917                         PQfinish(pgsqlConn);
918                         pgsqlConn = NULL;
919                 }
920                 return -1;
921         }
922
923         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
924         ast_mutex_lock(&pgsql_lock);
925         if (!pgsql_reconnect(database)) {
926                 ast_mutex_unlock(&pgsql_lock);
927                 return -1;
928         }
929
930         /* Create the first part of the query using the first parameter/value pairs we just extracted
931            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
932         ESCAPE_STRING(buf, field->name);
933         ast_str_set(&sql1, 0, "INSERT INTO %s (%s", table, ast_str_buffer(buf));
934         ESCAPE_STRING(buf, field->value);
935         ast_str_set(&sql2, 0, ") VALUES ('%s'", ast_str_buffer(buf));
936         while ((field = field->next)) {
937                 ESCAPE_STRING(buf, field->name);
938                 ast_str_append(&sql1, 0, ", %s", ast_str_buffer(buf));
939                 ESCAPE_STRING(buf, field->value);
940                 ast_str_append(&sql2, 0, ", '%s'", ast_str_buffer(buf));
941         }
942         ast_str_append(&sql1, 0, "%s)", ast_str_buffer(sql2));
943
944         ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", ast_str_buffer(sql1));
945
946         if (pgsql_exec(database, table, ast_str_buffer(sql1), &result) != 0) {
947                 ast_mutex_unlock(&pgsql_lock);
948                 return -1;
949         }
950
951         numrows = atoi(PQcmdTuples(result));
952         ast_mutex_unlock(&pgsql_lock);
953
954         ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s.", table);
955
956         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
957          * An integer greater than zero indicates the number of rows affected
958          * Zero indicates that no records were updated
959          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
960          */
961
962         if (numrows >= 0) {
963                 return numrows;
964         }
965
966         return -1;
967 }
968
969 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, const struct ast_variable *fields)
970 {
971         RAII_VAR(PGresult *, result, NULL, PQclear);
972         int numrows = 0;
973         int pgresult;
974         struct ast_str *sql = ast_str_thread_get(&sql_buf, 256);
975         struct ast_str *buf1 = ast_str_thread_get(&where_buf, 60), *buf2 = ast_str_thread_get(&escapebuf_buf, 60);
976         const struct ast_variable *field;
977
978         /*
979          * Ignore database from the extconfig.conf since it was
980          * configured by res_pgsql.conf.
981          */
982         database = dbname;
983
984         if (!table) {
985                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
986                 return -1;
987         }
988
989         /* Get the first parameter and first value in our list of passed paramater/value pairs */
990         /*newparam = va_arg(ap, const char *);
991         newval = va_arg(ap, const char *);
992         if (!newparam || !newval) {*/
993         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
994                 ast_log(LOG_WARNING,
995                                 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
996                 if (pgsqlConn) {
997                         PQfinish(pgsqlConn);
998                         pgsqlConn = NULL;
999                 };
1000                 return -1;
1001         }
1002
1003         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
1004         ast_mutex_lock(&pgsql_lock);
1005         if (!pgsql_reconnect(database)) {
1006                 ast_mutex_unlock(&pgsql_lock);
1007                 return -1;
1008         }
1009
1010
1011         /* Create the first part of the query using the first parameter/value pairs we just extracted
1012            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
1013
1014         ESCAPE_STRING(buf1, keyfield);
1015         ESCAPE_STRING(buf2, lookup);
1016         ast_str_set(&sql, 0, "DELETE FROM %s WHERE %s = '%s'", table, ast_str_buffer(buf1), ast_str_buffer(buf2));
1017         for (field = fields; field; field = field->next) {
1018                 ESCAPE_STRING(buf1, field->name);
1019                 ESCAPE_STRING(buf2, field->value);
1020                 ast_str_append(&sql, 0, " AND %s = '%s'", ast_str_buffer(buf1), ast_str_buffer(buf2));
1021         }
1022
1023         ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", ast_str_buffer(sql));
1024
1025         if (pgsql_exec(database, table, ast_str_buffer(sql), &result) != 0) {
1026                 ast_mutex_unlock(&pgsql_lock);
1027                 return -1;
1028         }
1029
1030         numrows = atoi(PQcmdTuples(result));
1031         ast_mutex_unlock(&pgsql_lock);
1032
1033         ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
1034
1035         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
1036          * An integer greater than zero indicates the number of rows affected
1037          * Zero indicates that no records were updated
1038          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
1039          */
1040
1041         if (numrows >= 0)
1042                 return (int) numrows;
1043
1044         return -1;
1045 }
1046
1047
1048 static struct ast_config *config_pgsql(const char *database, const char *table,
1049                                                                            const char *file, struct ast_config *cfg,
1050                                                                            struct ast_flags flags, const char *suggested_incl, const char *who_asked)
1051 {
1052         RAII_VAR(PGresult *, result, NULL, PQclear);
1053         long num_rows;
1054         struct ast_variable *new_v;
1055         struct ast_category *cur_cat = NULL;
1056         struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
1057         char last[80];
1058         int last_cat_metric = 0;
1059
1060         last[0] = '\0';
1061
1062         /*
1063          * Ignore database from the extconfig.conf since it is
1064          * configured by res_pgsql.conf.
1065          */
1066         database = dbname;
1067
1068         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
1069                 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
1070                 return NULL;
1071         }
1072
1073         ast_str_set(&sql, 0, "SELECT category, var_name, var_val, cat_metric FROM %s "
1074                         "WHERE filename='%s' and commented=0 "
1075                         "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ", table, file);
1076
1077         ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", ast_str_buffer(sql));
1078
1079         ast_mutex_lock(&pgsql_lock);
1080
1081         /* We now have our complete statement; Lets connect to the server and execute it. */
1082         if (pgsql_exec(database, table, ast_str_buffer(sql), &result) != 0) {
1083                 ast_mutex_unlock(&pgsql_lock);
1084                 return NULL;
1085         }
1086
1087         if ((num_rows = PQntuples(result)) > 0) {
1088                 int rowIndex = 0;
1089
1090                 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
1091
1092                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
1093                         char *field_category = PQgetvalue(result, rowIndex, 0);
1094                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
1095                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
1096                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
1097                         if (!strcmp(field_var_name, "#include")) {
1098                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
1099                                         ast_mutex_unlock(&pgsql_lock);
1100                                         return NULL;
1101                                 }
1102                                 continue;
1103                         }
1104
1105                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
1106                                 cur_cat = ast_category_new(field_category, "", 99999);
1107                                 if (!cur_cat)
1108                                         break;
1109                                 ast_copy_string(last, field_category, sizeof(last));
1110                                 last_cat_metric = atoi(field_cat_metric);
1111                                 ast_category_append(cfg, cur_cat);
1112                         }
1113                         new_v = ast_variable_new(field_var_name, field_var_val, "");
1114                         ast_variable_append(cur_cat, new_v);
1115                 }
1116         } else {
1117                 ast_log(LOG_WARNING,
1118                                 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
1119         }
1120
1121         ast_mutex_unlock(&pgsql_lock);
1122
1123         return cfg;
1124 }
1125
1126 static int require_pgsql(const char *database, const char *tablename, va_list ap)
1127 {
1128         struct columns *column;
1129         struct tables *table;
1130         char *elm;
1131         int type, size, res = 0;
1132
1133         /*
1134          * Ignore database from the extconfig.conf since it was
1135          * configured by res_pgsql.conf.
1136          */
1137         database = dbname;
1138
1139         table = find_table(database, tablename);
1140         if (!table) {
1141                 ast_log(LOG_WARNING, "Table %s not found in database.  This table should exist if you're using realtime.\n", tablename);
1142                 return -1;
1143         }
1144
1145         while ((elm = va_arg(ap, char *))) {
1146                 type = va_arg(ap, require_type);
1147                 size = va_arg(ap, int);
1148                 AST_LIST_TRAVERSE(&table->columns, column, list) {
1149                         if (strcmp(column->name, elm) == 0) {
1150                                 /* Char can hold anything, as long as it is large enough */
1151                                 if ((strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0 || strcmp(column->type, "bpchar") == 0)) {
1152                                         if ((size > column->len) && column->len != -1) {
1153                                                 ast_log(LOG_WARNING, "Column '%s' should be at least %d long, but is only %d long.\n", column->name, size, column->len);
1154                                                 res = -1;
1155                                         }
1156                                 } else if (strncmp(column->type, "int", 3) == 0) {
1157                                         int typesize = atoi(column->type + 3);
1158                                         /* Integers can hold only other integers */
1159                                         if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
1160                                                 type == RQ_INTEGER4 || type == RQ_UINTEGER4 ||
1161                                                 type == RQ_INTEGER3 || type == RQ_UINTEGER3 ||
1162                                                 type == RQ_UINTEGER2) && typesize == 2) {
1163                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
1164                                                 res = -1;
1165                                         } else if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
1166                                                 type == RQ_UINTEGER4) && typesize == 4) {
1167                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
1168                                                 res = -1;
1169                                         } else if (type == RQ_CHAR || type == RQ_DATETIME || type == RQ_FLOAT || type == RQ_DATE) {
1170                                                 ast_log(LOG_WARNING, "Column '%s' is of the incorrect type: (need %s(%d) but saw %s)\n",
1171                                                         column->name,
1172                                                                 type == RQ_CHAR ? "char" :
1173                                                                 type == RQ_DATETIME ? "datetime" :
1174                                                                 type == RQ_DATE ? "date" :
1175                                                                 type == RQ_FLOAT ? "float" :
1176                                                                 "a rather stiff drink ",
1177                                                         size, column->type);
1178                                                 res = -1;
1179                                         }
1180                                 } else if (strncmp(column->type, "float", 5) == 0) {
1181                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
1182                                                 ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
1183                                                 res = -1;
1184                                         }
1185                                 } else if (strncmp(column->type, "timestamp", 9) == 0) {
1186                                         if (type != RQ_DATETIME && type != RQ_DATE) {
1187                                                 ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
1188                                                 res = -1;
1189                                         }
1190                                 } else { /* There are other types that no module implements yet */
1191                                         ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
1192                                         res = -1;
1193                                 }
1194                                 break;
1195                         }
1196                 }
1197
1198                 if (!column) {
1199                         if (requirements == RQ_WARN) {
1200                                 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
1201                         } else {
1202                                 struct ast_str *sql = ast_str_create(100);
1203                                 char fieldtype[15];
1204                                 PGresult *result;
1205
1206                                 if (requirements == RQ_CREATECHAR || type == RQ_CHAR) {
1207                                         /* Size is minimum length; make it at least 50% greater,
1208                                          * just to be sure, because PostgreSQL doesn't support
1209                                          * resizing columns. */
1210                                         snprintf(fieldtype, sizeof(fieldtype), "CHAR(%d)",
1211                                                 size < 15 ? size * 2 :
1212                                                 (size * 3 / 2 > 255) ? 255 : size * 3 / 2);
1213                                 } else if (type == RQ_INTEGER1 || type == RQ_UINTEGER1 || type == RQ_INTEGER2) {
1214                                         snprintf(fieldtype, sizeof(fieldtype), "INT2");
1215                                 } else if (type == RQ_UINTEGER2 || type == RQ_INTEGER3 || type == RQ_UINTEGER3 || type == RQ_INTEGER4) {
1216                                         snprintf(fieldtype, sizeof(fieldtype), "INT4");
1217                                 } else if (type == RQ_UINTEGER4 || type == RQ_INTEGER8) {
1218                                         snprintf(fieldtype, sizeof(fieldtype), "INT8");
1219                                 } else if (type == RQ_UINTEGER8) {
1220                                         /* No such type on PostgreSQL */
1221                                         snprintf(fieldtype, sizeof(fieldtype), "CHAR(20)");
1222                                 } else if (type == RQ_FLOAT) {
1223                                         snprintf(fieldtype, sizeof(fieldtype), "FLOAT8");
1224                                 } else if (type == RQ_DATE) {
1225                                         snprintf(fieldtype, sizeof(fieldtype), "DATE");
1226                                 } else if (type == RQ_DATETIME) {
1227                                         snprintf(fieldtype, sizeof(fieldtype), "TIMESTAMP");
1228                                 } else {
1229                                         ast_log(LOG_ERROR, "Unrecognized request type %d\n", type);
1230                                         ast_free(sql);
1231                                         continue;
1232                                 }
1233                                 ast_str_set(&sql, 0, "ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, fieldtype);
1234                                 ast_debug(1, "About to lock pgsql_lock (running alter on table '%s' to add column '%s')\n", tablename, elm);
1235
1236                                 ast_mutex_lock(&pgsql_lock);
1237                                 ast_debug(1, "About to run ALTER query on table '%s' to add column '%s'\n", tablename, elm);
1238
1239                                 if (pgsql_exec(database, tablename, ast_str_buffer(sql), &result) != 0) {
1240                                                 ast_mutex_unlock(&pgsql_lock);
1241                                         return -1;
1242                                 }
1243
1244                                 ast_debug(1, "Finished running ALTER query on table '%s'\n", tablename);
1245                                 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
1246                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", ast_str_buffer(sql));
1247                                 }
1248                                 PQclear(result);
1249                                 ast_mutex_unlock(&pgsql_lock);
1250
1251                                 ast_free(sql);
1252                         }
1253                 }
1254         }
1255         release_table(table);
1256         return res;
1257 }
1258
1259 static int unload_pgsql(const char *database, const char *tablename)
1260 {
1261         struct tables *cur;
1262
1263         /*
1264          * Ignore database from the extconfig.conf since it was
1265          * configured by res_pgsql.conf.
1266          */
1267         database = dbname;
1268
1269         ast_debug(2, "About to lock table cache list\n");
1270         AST_LIST_LOCK(&psql_tables);
1271         ast_debug(2, "About to traverse table cache list\n");
1272         AST_LIST_TRAVERSE_SAFE_BEGIN(&psql_tables, cur, list) {
1273                 if (strcmp(cur->name, tablename) == 0) {
1274                         ast_debug(2, "About to remove matching cache entry\n");
1275                         AST_LIST_REMOVE_CURRENT(list);
1276                         ast_debug(2, "About to destroy matching cache entry\n");
1277                         destroy_table(cur);
1278                         ast_debug(1, "Cache entry '%s@%s' destroyed\n", tablename, database);
1279                         break;
1280                 }
1281         }
1282         AST_LIST_TRAVERSE_SAFE_END
1283         AST_LIST_UNLOCK(&psql_tables);
1284         ast_debug(2, "About to return\n");
1285         return cur ? 0 : -1;
1286 }
1287
1288 static struct ast_config_engine pgsql_engine = {
1289         .name = "pgsql",
1290         .load_func = config_pgsql,
1291         .realtime_func = realtime_pgsql,
1292         .realtime_multi_func = realtime_multi_pgsql,
1293         .store_func = store_pgsql,
1294         .destroy_func = destroy_pgsql,
1295         .update_func = update_pgsql,
1296         .update2_func = update2_pgsql,
1297         .require_func = require_pgsql,
1298         .unload_func = unload_pgsql,
1299 };
1300
1301 static int load_module(void)
1302 {
1303         if(!parse_config(0))
1304                 return AST_MODULE_LOAD_DECLINE;
1305
1306         ast_config_engine_register(&pgsql_engine);
1307
1308         ast_cli_register_multiple(cli_realtime, ARRAY_LEN(cli_realtime));
1309
1310         return 0;
1311 }
1312
1313 static int unload_module(void)
1314 {
1315         struct tables *table;
1316         /* Acquire control before doing anything to the module itself. */
1317         ast_mutex_lock(&pgsql_lock);
1318
1319         if (pgsqlConn) {
1320                 PQfinish(pgsqlConn);
1321                 pgsqlConn = NULL;
1322         }
1323         ast_cli_unregister_multiple(cli_realtime, ARRAY_LEN(cli_realtime));
1324         ast_config_engine_deregister(&pgsql_engine);
1325
1326         /* Unlock so something else can destroy the lock. */
1327         ast_mutex_unlock(&pgsql_lock);
1328
1329         /* Destroy cached table info */
1330         AST_LIST_LOCK(&psql_tables);
1331         while ((table = AST_LIST_REMOVE_HEAD(&psql_tables, list))) {
1332                 destroy_table(table);
1333         }
1334         AST_LIST_UNLOCK(&psql_tables);
1335
1336         return 0;
1337 }
1338
1339 static int reload(void)
1340 {
1341         parse_config(1);
1342
1343         return 0;
1344 }
1345
1346 static int parse_config(int is_reload)
1347 {
1348         struct ast_config *config;
1349         const char *s;
1350         struct ast_flags config_flags = { is_reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
1351
1352         config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags);
1353         if (config == CONFIG_STATUS_FILEUNCHANGED) {
1354                 if (is_reload && pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
1355                         ast_log(LOG_WARNING,  "PostgreSQL RealTime: Not connected\n");
1356                 }
1357                 return 0;
1358         }
1359
1360         if (config == CONFIG_STATUS_FILEMISSING || config == CONFIG_STATUS_FILEINVALID) {
1361                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
1362                 return 0;
1363         }
1364
1365         ast_mutex_lock(&pgsql_lock);
1366
1367         if (pgsqlConn) {
1368                 PQfinish(pgsqlConn);
1369                 pgsqlConn = NULL;
1370         }
1371
1372         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
1373                 ast_log(LOG_WARNING,
1374                                 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
1375                 strcpy(dbuser, "asterisk");
1376         } else {
1377                 ast_copy_string(dbuser, s, sizeof(dbuser));
1378         }
1379
1380         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
1381                 ast_log(LOG_WARNING,
1382                                 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
1383                 strcpy(dbpass, "asterisk");
1384         } else {
1385                 ast_copy_string(dbpass, s, sizeof(dbpass));
1386         }
1387
1388         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
1389                 ast_log(LOG_WARNING,
1390                                 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
1391                 dbhost[0] = '\0';
1392         } else {
1393                 ast_copy_string(dbhost, s, sizeof(dbhost));
1394         }
1395
1396         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
1397                 ast_log(LOG_WARNING,
1398                                 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
1399                 strcpy(dbname, "asterisk");
1400         } else {
1401                 ast_copy_string(dbname, s, sizeof(dbname));
1402         }
1403
1404         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
1405                 ast_log(LOG_WARNING,
1406                                 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
1407                 dbport = 5432;
1408         } else {
1409                 dbport = atoi(s);
1410         }
1411
1412         if (!(s = ast_variable_retrieve(config, "general", "dbappname"))) {
1413                 dbappname[0] = '\0';
1414         } else {
1415                 ast_copy_string(dbappname, s, sizeof(dbappname));
1416         }
1417
1418         if (!ast_strlen_zero(dbhost)) {
1419                 /* No socket needed */
1420         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
1421                 ast_log(LOG_WARNING,
1422                                 "PostgreSQL RealTime: No database socket found, using '/tmp/.s.PGSQL.%d' as default.\n", dbport);
1423                 strcpy(dbsock, "/tmp");
1424         } else {
1425                 ast_copy_string(dbsock, s, sizeof(dbsock));
1426         }
1427
1428         if (!(s = ast_variable_retrieve(config, "general", "requirements"))) {
1429                 ast_log(LOG_WARNING,
1430                                 "PostgreSQL RealTime: no requirements setting found, using 'warn' as default.\n");
1431                 requirements = RQ_WARN;
1432         } else if (!strcasecmp(s, "createclose")) {
1433                 requirements = RQ_CREATECLOSE;
1434         } else if (!strcasecmp(s, "createchar")) {
1435                 requirements = RQ_CREATECHAR;
1436         }
1437
1438         ast_config_destroy(config);
1439
1440         if (option_debug) {
1441                 if (!ast_strlen_zero(dbhost)) {
1442                         ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
1443                         ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
1444                 } else {
1445                         ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
1446                 }
1447                 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
1448                 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
1449                 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
1450         }
1451
1452         if (!pgsql_reconnect(NULL)) {
1453                 ast_log(LOG_WARNING,
1454                                 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
1455                 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
1456         }
1457
1458         ast_verb(2, "PostgreSQL RealTime reloaded.\n");
1459
1460         /* Done reloading. Release lock so others can now use driver. */
1461         ast_mutex_unlock(&pgsql_lock);
1462
1463         return 1;
1464 }
1465
1466 static int pgsql_reconnect(const char *database)
1467 {
1468         char my_database[50];
1469
1470         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
1471
1472         /* mutex lock should have been locked before calling this function. */
1473
1474         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
1475                 PQfinish(pgsqlConn);
1476                 pgsqlConn = NULL;
1477         }
1478
1479         /* DB password can legitimately be 0-length */
1480         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
1481                 struct ast_str *conn_info = ast_str_create(128);
1482
1483                 if (!conn_info) {
1484                         ast_log(LOG_ERROR, "PostgreSQL RealTime: Failed to allocate memory for connection string.\n");
1485                         return 0;
1486                 }
1487
1488                 ast_str_set(&conn_info, 0, "host=%s port=%d dbname=%s user=%s",
1489                         S_OR(dbhost, dbsock), dbport, my_database, dbuser);
1490
1491                 if (!ast_strlen_zero(dbappname)) {
1492                         ast_str_append(&conn_info, 0, " application_name=%s", dbappname);
1493                 }
1494
1495                 if (!ast_strlen_zero(dbpass)) {
1496                         ast_str_append(&conn_info, 0, " password=%s", dbpass);
1497                 }
1498
1499                 pgsqlConn = PQconnectdb(ast_str_buffer(conn_info));
1500                 ast_free(conn_info);
1501                 conn_info = NULL;
1502
1503                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
1504                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1505                         ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
1506                         connect_time = time(NULL);
1507                         version = PQserverVersion(pgsqlConn);
1508                         return 1;
1509                 } else {
1510                         ast_log(LOG_ERROR,
1511                                         "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
1512                                         my_database, dbhost, PQresultErrorMessage(NULL));
1513                         return 0;
1514                 }
1515         } else {
1516                 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
1517                 return 1;
1518         }
1519 }
1520
1521 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1522 {
1523         struct tables *cur;
1524         int l, which;
1525         char *ret = NULL;
1526
1527         switch (cmd) {
1528         case CLI_INIT:
1529                 e->command = "realtime show pgsql cache";
1530                 e->usage =
1531                         "Usage: realtime show pgsql cache [<table>]\n"
1532                         "       Shows table cache for the PostgreSQL RealTime driver\n";
1533                 return NULL;
1534         case CLI_GENERATE:
1535                 if (a->argc != 4) {
1536                         return NULL;
1537                 }
1538                 l = strlen(a->word);
1539                 which = 0;
1540                 AST_LIST_LOCK(&psql_tables);
1541                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1542                         if (!strncasecmp(a->word, cur->name, l) && ++which > a->n) {
1543                                 ret = ast_strdup(cur->name);
1544                                 break;
1545                         }
1546                 }
1547                 AST_LIST_UNLOCK(&psql_tables);
1548                 return ret;
1549         }
1550
1551         if (a->argc == 4) {
1552                 /* List of tables */
1553                 AST_LIST_LOCK(&psql_tables);
1554                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1555                         ast_cli(a->fd, "%s\n", cur->name);
1556                 }
1557                 AST_LIST_UNLOCK(&psql_tables);
1558         } else if (a->argc == 5) {
1559                 /* List of columns */
1560                 if ((cur = find_table(NULL, a->argv[4]))) {
1561                         struct columns *col;
1562                         ast_cli(a->fd, "Columns for Table Cache '%s':\n", a->argv[4]);
1563                         ast_cli(a->fd, "%-20.20s %-20.20s %-3.3s %-8.8s\n", "Name", "Type", "Len", "Nullable");
1564                         AST_LIST_TRAVERSE(&cur->columns, col, list) {
1565                                 ast_cli(a->fd, "%-20.20s %-20.20s %3d %-8.8s\n", col->name, col->type, col->len, col->notnull ? "NOT NULL" : "");
1566                         }
1567                         release_table(cur);
1568                 } else {
1569                         ast_cli(a->fd, "No such table '%s'\n", a->argv[4]);
1570                 }
1571         }
1572         return 0;
1573 }
1574
1575 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1576 {
1577         char connection_info[256];
1578         char credentials[100] = "";
1579         char buf[376]; /* 256+100+"Connected to "+" for "+NULL */
1580         int ctimesec = time(NULL) - connect_time;
1581
1582         switch (cmd) {
1583         case CLI_INIT:
1584                 e->command = "realtime show pgsql status";
1585                 e->usage =
1586                         "Usage: realtime show pgsql status\n"
1587                         "       Shows connection information for the PostgreSQL RealTime driver\n";
1588                 return NULL;
1589         case CLI_GENERATE:
1590                 return NULL;
1591         }
1592
1593         if (a->argc != 4)
1594                 return CLI_SHOWUSAGE;
1595
1596         if (!ast_strlen_zero(dbhost))
1597                 snprintf(connection_info, sizeof(connection_info), "%s@%s, port %d", dbname, dbhost, dbport);
1598         else if (!ast_strlen_zero(dbsock))
1599                 snprintf(connection_info, sizeof(connection_info), "%s on socket file %s", dbname, dbsock);
1600         else
1601                 snprintf(connection_info, sizeof(connection_info), "%s@%s", dbname, dbhost);
1602
1603         if (!ast_strlen_zero(dbuser))
1604                 snprintf(credentials, sizeof(credentials), " with username %s", dbuser);
1605
1606
1607         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1608                 snprintf(buf, sizeof(buf), "Connected to %s%s for ", connection_info, credentials);
1609                 ast_cli_print_timestr_fromseconds(a->fd, ctimesec, buf);
1610                 return CLI_SUCCESS;
1611         } else {
1612                 ast_cli(a->fd, "Unable to connect %s%s\n", connection_info, credentials);
1613                 return CLI_FAILURE;
1614         }
1615 }
1616
1617 /* needs usecount semantics defined */
1618 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "PostgreSQL RealTime Configuration Driver",
1619         .support_level = AST_MODULE_SUPPORT_EXTENDED,
1620         .load = load_module,
1621         .unload = unload_module,
1622         .reload = reload,
1623         .load_pri = AST_MODPRI_REALTIME_DRIVER,
1624 );