Merge the adaptive realtime branch, which will make adding new required fields
[asterisk/asterisk.git] / res / res_config_pgsql.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * Copyright (C) 1999-2005, 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  * \arg http://www.postgresql.org
23  */
24
25 /*** MODULEINFO
26         <depend>pgsql</depend>
27  ***/
28
29 #include "asterisk.h"
30
31 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
32
33 #include <libpq-fe.h>                   /* PostgreSQL */
34
35 #include "asterisk/file.h"
36 #include "asterisk/channel.h"
37 #include "asterisk/pbx.h"
38 #include "asterisk/config.h"
39 #include "asterisk/module.h"
40 #include "asterisk/lock.h"
41 #include "asterisk/utils.h"
42 #include "asterisk/cli.h"
43
44 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
45
46 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
47
48 PGconn *pgsqlConn = NULL;
49
50 #define MAX_DB_OPTION_SIZE 64
51
52 struct columns {
53         char *name;
54         char *type;
55         int len;
56         unsigned int notnull:1;
57         unsigned int hasdefault:1;
58         AST_LIST_ENTRY(columns) list;
59 };
60
61 struct tables {
62         ast_mutex_t lock;
63         AST_LIST_HEAD_NOLOCK(psql_columns, columns) columns;
64         AST_LIST_ENTRY(tables) list;
65         char name[0];
66 };
67
68 static AST_LIST_HEAD_STATIC(psql_tables, tables);
69
70 static char dbhost[MAX_DB_OPTION_SIZE] = "";
71 static char dbuser[MAX_DB_OPTION_SIZE] = "";
72 static char dbpass[MAX_DB_OPTION_SIZE] = "";
73 static char dbname[MAX_DB_OPTION_SIZE] = "";
74 static char dbsock[MAX_DB_OPTION_SIZE] = "";
75 static int dbport = 5432;
76 static time_t connect_time = 0;
77
78 static int parse_config(int reload);
79 static int pgsql_reconnect(const char *database);
80 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
81 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
82
83 enum { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR } requirements;
84
85 static struct ast_cli_entry cli_realtime[] = {
86         AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
87         AST_CLI_DEFINE(handle_cli_realtime_pgsql_cache, "Shows cached tables within the PostgreSQL realtime driver"),
88 };
89
90 static void destroy_table(struct tables *table)
91 {
92         struct columns *column;
93         ast_mutex_lock(&table->lock);
94         while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
95                 ast_free(column);
96         }
97         ast_mutex_unlock(&table->lock);
98         ast_mutex_destroy(&table->lock);
99         ast_free(table);
100 }
101
102 static struct tables *find_table(const char *tablename)
103 {
104         struct columns *column;
105         struct tables *table;
106         struct ast_str *sql = ast_str_create(330);
107         char *pgerror;
108         PGresult *result;
109         char *fname, *ftype, *flen, *fnotnull, *fdef;
110         int i, rows;
111
112         AST_LIST_LOCK(&psql_tables);
113         AST_LIST_TRAVERSE(&psql_tables, table, list) {
114                 if (!strcasecmp(table->name, tablename)) {
115                         ast_debug(1, "Found table in cache; now locking\n");
116                         ast_mutex_lock(&table->lock);
117                         ast_debug(1, "Lock cached table; now returning\n");
118                         AST_LIST_UNLOCK(&psql_tables);
119                         return table;
120                 }
121         }
122
123         ast_debug(1, "Table '%s' not found in cache, querying now\n", tablename);
124
125         /* Not found, scan the table */
126         ast_str_set(&sql, 0, "SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc 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);
127         result = PQexec(pgsqlConn, sql->str);
128         ast_debug(1, "Query of table structure complete.  Now retrieving results.\n");
129         if (PQresultStatus(result) != PGRES_TUPLES_OK) {
130                 pgerror = PQresultErrorMessage(result);
131                 ast_log(LOG_ERROR, "Failed to query database columns: %s\n", pgerror);
132                 PQclear(result);
133                 AST_LIST_UNLOCK(&psql_tables);
134                 return NULL;
135         }
136
137         if (!(table = ast_calloc(1, sizeof(*table) + strlen(tablename) + 1))) {
138                 ast_log(LOG_ERROR, "Unable to allocate memory for new table structure\n");
139                 AST_LIST_UNLOCK(&psql_tables);
140                 return NULL;
141         }
142         strcpy(table->name, tablename); /* SAFE */
143         ast_mutex_init(&table->lock);
144         AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
145         
146         rows = PQntuples(result);
147         for (i = 0; i < rows; i++) {
148                 fname = PQgetvalue(result, i, 0);
149                 ftype = PQgetvalue(result, i, 1);
150                 flen = PQgetvalue(result, i, 2);
151                 fnotnull = PQgetvalue(result, i, 3);
152                 fdef = PQgetvalue(result, i, 4);
153                 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
154
155                 if (!(column = ast_calloc(1, sizeof(*column) + strlen(fname) + strlen(ftype) + 2))) {
156                         ast_log(LOG_ERROR, "Unable to allocate column element for %s, %s\n", tablename, fname);
157                         destroy_table(table);
158                         AST_LIST_UNLOCK(&psql_tables);
159                         return NULL;
160                 }
161
162                 sscanf(flen, "%d", &column->len);
163                 column->name = (char *)column + sizeof(*column);
164                 column->type = (char *)column + sizeof(*column) + strlen(fname) + 1;
165                 strcpy(column->name, fname);
166                 strcpy(column->type, ftype);
167                 if (*fnotnull == 't') {
168                         column->notnull = 1;
169                 } else {
170                         column->notnull = 0;
171                 }
172                 if (!ast_strlen_zero(fdef)) {
173                         column->hasdefault = 1;
174                 } else {
175                         column->hasdefault = 0;
176                 }
177                 AST_LIST_INSERT_TAIL(&table->columns, column, list);
178         }
179         PQclear(result);
180
181         AST_LIST_INSERT_TAIL(&psql_tables, table, list);
182         ast_mutex_lock(&table->lock);
183         AST_LIST_UNLOCK(&psql_tables);
184         return table;
185 }
186
187 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
188 {
189         PGresult *result = NULL;
190         int num_rows = 0, pgerror;
191         char sql[256], escapebuf[513];
192         char *stringp;
193         char *chunk;
194         char *op;
195         const char *newparam, *newval;
196         struct ast_variable *var = NULL, *prev = NULL;
197
198         if (!table) {
199                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
200                 return NULL;
201         }
202
203         /* Get the first parameter and first value in our list of passed paramater/value pairs */
204         newparam = va_arg(ap, const char *);
205         newval = va_arg(ap, const char *);
206         if (!newparam || !newval) {
207                 ast_log(LOG_WARNING,
208                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
209                 if (pgsqlConn) {
210                         PQfinish(pgsqlConn);
211                         pgsqlConn = NULL;
212                 };
213                 return NULL;
214         }
215
216         /* Create the first part of the query using the first parameter/value pairs we just extracted
217            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
218         op = strchr(newparam, ' ') ? "" : " =";
219
220         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
221         if (pgerror) {
222                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
223                 va_end(ap);
224                 return NULL;
225         }
226
227         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
228                          escapebuf);
229         while ((newparam = va_arg(ap, const char *))) {
230                 newval = va_arg(ap, const char *);
231                 if (!strchr(newparam, ' '))
232                         op = " =";
233                 else
234                         op = "";
235
236                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
237                 if (pgerror) {
238                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
239                         va_end(ap);
240                         return NULL;
241                 }
242
243                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
244                                  op, escapebuf);
245         }
246         va_end(ap);
247
248         /* We now have our complete statement; Lets connect to the server and execute it. */
249         ast_mutex_lock(&pgsql_lock);
250         if (!pgsql_reconnect(database)) {
251                 ast_mutex_unlock(&pgsql_lock);
252                 return NULL;
253         }
254
255         if (!(result = PQexec(pgsqlConn, sql))) {
256                 ast_log(LOG_WARNING,
257                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
258                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
259                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
260                 ast_mutex_unlock(&pgsql_lock);
261                 return NULL;
262         } else {
263                 ExecStatusType result_status = PQresultStatus(result);
264                 if (result_status != PGRES_COMMAND_OK
265                         && result_status != PGRES_TUPLES_OK
266                         && result_status != PGRES_NONFATAL_ERROR) {
267                         ast_log(LOG_WARNING,
268                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
269                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
270                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
271                                                 PQresultErrorMessage(result), PQresStatus(result_status));
272                         ast_mutex_unlock(&pgsql_lock);
273                         return NULL;
274                 }
275         }
276
277         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
278
279         if ((num_rows = PQntuples(result)) > 0) {
280                 int i = 0;
281                 int rowIndex = 0;
282                 int numFields = PQnfields(result);
283                 char **fieldnames = NULL;
284
285                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
286
287                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
288                         ast_mutex_unlock(&pgsql_lock);
289                         PQclear(result);
290                         return NULL;
291                 }
292                 for (i = 0; i < numFields; i++)
293                         fieldnames[i] = PQfname(result, i);
294                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
295                         for (i = 0; i < numFields; i++) {
296                                 stringp = PQgetvalue(result, rowIndex, i);
297                                 while (stringp) {
298                                         chunk = strsep(&stringp, ";");
299                                         if (!ast_strlen_zero(ast_strip(chunk))) {
300                                                 if (prev) {
301                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
302                                                         if (prev->next) {
303                                                                 prev = prev->next;
304                                                         }
305                                                 } else {
306                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
307                                                 }
308                                         }
309                                 }
310                         }
311                 }
312                 ast_free(fieldnames);
313         } else {
314                 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s.\n", table);
315         }
316
317         ast_mutex_unlock(&pgsql_lock);
318         PQclear(result);
319
320         return var;
321 }
322
323 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
324 {
325         PGresult *result = NULL;
326         int num_rows = 0, pgerror;
327         char sql[256], escapebuf[513];
328         const char *initfield = NULL;
329         char *stringp;
330         char *chunk;
331         char *op;
332         const char *newparam, *newval;
333         struct ast_variable *var = NULL;
334         struct ast_config *cfg = NULL;
335         struct ast_category *cat = NULL;
336
337         if (!table) {
338                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
339                 return NULL;
340         }
341
342         if (!(cfg = ast_config_new()))
343                 return NULL;
344
345         /* Get the first parameter and first value in our list of passed paramater/value pairs */
346         newparam = va_arg(ap, const char *);
347         newval = va_arg(ap, const char *);
348         if (!newparam || !newval) {
349                 ast_log(LOG_WARNING,
350                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
351                 if (pgsqlConn) {
352                         PQfinish(pgsqlConn);
353                         pgsqlConn = NULL;
354                 };
355                 return NULL;
356         }
357
358         initfield = ast_strdupa(newparam);
359         if ((op = strchr(initfield, ' '))) {
360                 *op = '\0';
361         }
362
363         /* Create the first part of the query using the first parameter/value pairs we just extracted
364            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
365
366         if (!strchr(newparam, ' '))
367                 op = " =";
368         else
369                 op = "";
370
371         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
372         if (pgerror) {
373                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
374                 va_end(ap);
375                 return NULL;
376         }
377
378         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
379                          escapebuf);
380         while ((newparam = va_arg(ap, const char *))) {
381                 newval = va_arg(ap, const char *);
382                 if (!strchr(newparam, ' '))
383                         op = " =";
384                 else
385                         op = "";
386
387                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
388                 if (pgerror) {
389                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
390                         va_end(ap);
391                         return NULL;
392                 }
393
394                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
395                                  op, escapebuf);
396         }
397
398         if (initfield) {
399                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
400         }
401
402         va_end(ap);
403
404         /* We now have our complete statement; Lets connect to the server and execute it. */
405         ast_mutex_lock(&pgsql_lock);
406         if (!pgsql_reconnect(database)) {
407                 ast_mutex_unlock(&pgsql_lock);
408                 return NULL;
409         }
410
411         if (!(result = PQexec(pgsqlConn, sql))) {
412                 ast_log(LOG_WARNING,
413                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
414                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
415                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
416                 ast_mutex_unlock(&pgsql_lock);
417                 return NULL;
418         } else {
419                 ExecStatusType result_status = PQresultStatus(result);
420                 if (result_status != PGRES_COMMAND_OK
421                         && result_status != PGRES_TUPLES_OK
422                         && result_status != PGRES_NONFATAL_ERROR) {
423                         ast_log(LOG_WARNING,
424                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
425                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
426                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
427                                                 PQresultErrorMessage(result), PQresStatus(result_status));
428                         ast_mutex_unlock(&pgsql_lock);
429                         return NULL;
430                 }
431         }
432
433         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
434
435         if ((num_rows = PQntuples(result)) > 0) {
436                 int numFields = PQnfields(result);
437                 int i = 0;
438                 int rowIndex = 0;
439                 char **fieldnames = NULL;
440
441                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
442
443                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
444                         ast_mutex_unlock(&pgsql_lock);
445                         PQclear(result);
446                         return NULL;
447                 }
448                 for (i = 0; i < numFields; i++)
449                         fieldnames[i] = PQfname(result, i);
450
451                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
452                         var = NULL;
453                         if (!(cat = ast_category_new("","",99999)))
454                                 continue;
455                         for (i = 0; i < numFields; i++) {
456                                 stringp = PQgetvalue(result, rowIndex, i);
457                                 while (stringp) {
458                                         chunk = strsep(&stringp, ";");
459                                         if (!ast_strlen_zero(ast_strip(chunk))) {
460                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
461                                                         ast_category_rename(cat, chunk);
462                                                 }
463                                                 var = ast_variable_new(fieldnames[i], chunk, "");
464                                                 ast_variable_append(cat, var);
465                                         }
466                                 }
467                         }
468                         ast_category_append(cfg, cat);
469                 }
470                 ast_free(fieldnames);
471         } else {
472                 ast_log(LOG_WARNING,
473                                 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
474         }
475
476         ast_mutex_unlock(&pgsql_lock);
477         PQclear(result);
478
479         return cfg;
480 }
481
482 static int update_pgsql(const char *database, const char *tablename, const char *keyfield,
483                                                 const char *lookup, va_list ap)
484 {
485         PGresult *result = NULL;
486         int numrows = 0, pgerror;
487         char escapebuf[513];
488         const char *newparam, *newval;
489         struct ast_str *sql = ast_str_create(100);
490         struct tables *table;
491         struct columns *column = NULL;
492
493         if (!tablename) {
494                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
495                 ast_free(sql);
496                 return -1;
497         }
498
499         if (!(table = find_table(tablename))) {
500                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
501                 ast_free(sql);
502                 return -1;
503         }
504
505         /* Get the first parameter and first value in our list of passed paramater/value pairs */
506         newparam = va_arg(ap, const char *);
507         newval = va_arg(ap, const char *);
508         if (!newparam || !newval) {
509                 ast_log(LOG_WARNING,
510                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
511                 if (pgsqlConn) {
512                         PQfinish(pgsqlConn);
513                         pgsqlConn = NULL;
514                 };
515                 ast_mutex_unlock(&table->lock);
516                 ast_free(sql);
517                 return -1;
518         }
519
520         /* Check that the column exists in the table */
521         AST_LIST_TRAVERSE(&table->columns, column, list) {
522                 if (strcmp(column->name, newparam) == 0) {
523                         break;
524                 }
525         }
526
527         if (!column) {
528                 ast_log(LOG_ERROR, "PostgreSQL RealTime: Updating on column '%s', but that column does not exist within the table '%s'!\n", newparam, tablename);
529                 ast_mutex_unlock(&table->lock);
530                 ast_free(sql);
531                 return -1;
532         }
533
534         /* Create the first part of the query using the first parameter/value pairs we just extracted
535            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
536
537         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
538         if (pgerror) {
539                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
540                 va_end(ap);
541                 ast_mutex_unlock(&table->lock);
542                 ast_free(sql);
543                 return -1;
544         }
545         ast_str_set(&sql, 0, "UPDATE %s SET %s = '%s'", tablename, newparam, escapebuf);
546
547         while ((newparam = va_arg(ap, const char *))) {
548                 newval = va_arg(ap, const char *);
549
550                 /* If the column is not within the table, then skip it */
551                 AST_LIST_TRAVERSE(&table->columns, column, list) {
552                         if (strcmp(column->name, newparam) == 0) {
553                                 break;
554                         }
555                 }
556
557                 if (!column) {
558                         ast_log(LOG_WARNING, "Attempted to update column '%s' in table '%s', but column does not exist!\n", newparam, tablename);
559                         continue;
560                 }
561
562                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
563                 if (pgerror) {
564                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
565                         va_end(ap);
566                         ast_mutex_unlock(&table->lock);
567                         ast_free(sql);
568                         return -1;
569                 }
570
571                 ast_str_append(&sql, 0, ", %s = '%s'", newparam, escapebuf);
572         }
573         va_end(ap);
574         ast_mutex_unlock(&table->lock);
575
576         PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
577         if (pgerror) {
578                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
579                 va_end(ap);
580                 ast_free(sql);
581                 return -1;
582         }
583
584         ast_str_append(&sql, 0, " WHERE %s = '%s'", keyfield, escapebuf);
585
586         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql->str);
587
588         /* We now have our complete statement; Lets connect to the server and execute it. */
589         ast_mutex_lock(&pgsql_lock);
590         if (!pgsql_reconnect(database)) {
591                 ast_mutex_unlock(&pgsql_lock);
592                 ast_free(sql);
593                 return -1;
594         }
595
596         if (!(result = PQexec(pgsqlConn, sql->str))) {
597                 ast_log(LOG_WARNING,
598                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
599                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
600                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
601                 ast_mutex_unlock(&pgsql_lock);
602                 ast_free(sql);
603                 return -1;
604         } else {
605                 ExecStatusType result_status = PQresultStatus(result);
606                 if (result_status != PGRES_COMMAND_OK
607                         && result_status != PGRES_TUPLES_OK
608                         && result_status != PGRES_NONFATAL_ERROR) {
609                         ast_log(LOG_WARNING,
610                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
611                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
612                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
613                                                 PQresultErrorMessage(result), PQresStatus(result_status));
614                         ast_mutex_unlock(&pgsql_lock);
615                         ast_free(sql);
616                         return -1;
617                 }
618         }
619
620         numrows = atoi(PQcmdTuples(result));
621         ast_mutex_unlock(&pgsql_lock);
622         ast_free(sql);
623
624         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
625
626         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
627          * An integer greater than zero indicates the number of rows affected
628          * Zero indicates that no records were updated
629          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
630          */
631
632         if (numrows >= 0)
633                 return (int) numrows;
634
635         return -1;
636 }
637
638 static int store_pgsql(const char *database, const char *table, va_list ap)
639 {
640         PGresult *result = NULL;
641         Oid insertid;
642         char sql[256];
643         char params[256];
644         char vals[256];
645         char buf[256];
646         int pgresult;
647         const char *newparam, *newval;
648
649         if (!table) {
650                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
651                 return -1;
652         }
653
654         /* Get the first parameter and first value in our list of passed paramater/value pairs */
655         newparam = va_arg(ap, const char *);
656         newval = va_arg(ap, const char *);
657         if (!newparam || !newval) {
658                 ast_log(LOG_WARNING,
659                                 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
660                 if (pgsqlConn) {
661                         PQfinish(pgsqlConn);
662                         pgsqlConn = NULL;
663                 };
664                 return -1;
665         }
666
667         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
668         ast_mutex_lock(&pgsql_lock);
669         if (!pgsql_reconnect(database)) {
670                 ast_mutex_unlock(&pgsql_lock);
671                 return -1;
672         }
673
674         /* Create the first part of the query using the first parameter/value pairs we just extracted
675            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
676         PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
677         snprintf(params, sizeof(params), "%s", buf);
678         PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
679         snprintf(vals, sizeof(vals), "'%s'", buf);
680         while ((newparam = va_arg(ap, const char *))) {
681                 newval = va_arg(ap, const char *);
682                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
683                 snprintf(params + strlen(params), sizeof(params) - strlen(params), ", %s", buf);
684                 PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
685                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", '%s'", buf);
686         }
687         va_end(ap);
688         snprintf(sql, sizeof(sql), "INSERT INTO (%s) VALUES (%s)", params, vals);
689
690         ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql);
691
692         if (!(result = PQexec(pgsqlConn, sql))) {
693                 ast_log(LOG_WARNING,
694                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
695                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
696                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
697                 ast_mutex_unlock(&pgsql_lock);
698                 return -1;
699         } else {
700                 ExecStatusType result_status = PQresultStatus(result);
701                 if (result_status != PGRES_COMMAND_OK
702                         && result_status != PGRES_TUPLES_OK
703                         && result_status != PGRES_NONFATAL_ERROR) {
704                         ast_log(LOG_WARNING,
705                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
706                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
707                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
708                                                 PQresultErrorMessage(result), PQresStatus(result_status));
709                         ast_mutex_unlock(&pgsql_lock);
710                         return -1;
711                 }
712         }
713
714         insertid = PQoidValue(result);
715         ast_mutex_unlock(&pgsql_lock);
716
717         ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
718
719         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
720          * An integer greater than zero indicates the number of rows affected
721          * Zero indicates that no records were updated
722          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
723          */
724
725         if (insertid >= 0)
726                 return (int) insertid;
727
728         return -1;
729 }
730
731 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
732 {
733         PGresult *result = NULL;
734         int numrows = 0;
735         int pgresult;
736         char sql[256];
737         char buf[256], buf2[256];
738         const char *newparam, *newval;
739
740         if (!table) {
741                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
742                 return -1;
743         }
744
745         /* Get the first parameter and first value in our list of passed paramater/value pairs */
746         /*newparam = va_arg(ap, const char *);
747         newval = va_arg(ap, const char *);
748         if (!newparam || !newval) {*/
749         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
750                 ast_log(LOG_WARNING,
751                                 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
752                 if (pgsqlConn) {
753                         PQfinish(pgsqlConn);
754                         pgsqlConn = NULL;
755                 };
756                 return -1;
757         }
758
759         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
760         ast_mutex_lock(&pgsql_lock);
761         if (!pgsql_reconnect(database)) {
762                 ast_mutex_unlock(&pgsql_lock);
763                 return -1;
764         }
765
766
767         /* Create the first part of the query using the first parameter/value pairs we just extracted
768            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
769
770         PQescapeStringConn(pgsqlConn, buf, keyfield, sizeof(keyfield), &pgresult);
771         PQescapeStringConn(pgsqlConn, buf2, lookup, sizeof(lookup), &pgresult);
772         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = '%s'", table, buf, buf2);
773         while ((newparam = va_arg(ap, const char *))) {
774                 newval = va_arg(ap, const char *);
775                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
776                 PQescapeStringConn(pgsqlConn, buf2, newval, sizeof(newval), &pgresult);
777                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s = '%s'", buf, buf2);
778         }
779         va_end(ap);
780
781         ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql);
782
783         if (!(result = PQexec(pgsqlConn, sql))) {
784                 ast_log(LOG_WARNING,
785                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
786                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
787                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
788                 ast_mutex_unlock(&pgsql_lock);
789                 return -1;
790         } else {
791                 ExecStatusType result_status = PQresultStatus(result);
792                 if (result_status != PGRES_COMMAND_OK
793                         && result_status != PGRES_TUPLES_OK
794                         && result_status != PGRES_NONFATAL_ERROR) {
795                         ast_log(LOG_WARNING,
796                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
797                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
798                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
799                                                 PQresultErrorMessage(result), PQresStatus(result_status));
800                         ast_mutex_unlock(&pgsql_lock);
801                         return -1;
802                 }
803         }
804
805         numrows = atoi(PQcmdTuples(result));
806         ast_mutex_unlock(&pgsql_lock);
807
808         ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
809
810         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
811          * An integer greater than zero indicates the number of rows affected
812          * Zero indicates that no records were updated
813          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
814          */
815
816         if (numrows >= 0)
817                 return (int) numrows;
818
819         return -1;
820 }
821
822
823 static struct ast_config *config_pgsql(const char *database, const char *table,
824                                                                            const char *file, struct ast_config *cfg,
825                                                                            struct ast_flags flags, const char *suggested_incl, const char *who_asked)
826 {
827         PGresult *result = NULL;
828         long num_rows;
829         struct ast_variable *new_v;
830         struct ast_category *cur_cat = NULL;
831         char sqlbuf[1024] = "";
832         char *sql = sqlbuf;
833         size_t sqlleft = sizeof(sqlbuf);
834         char last[80] = "";
835         int last_cat_metric = 0;
836
837         last[0] = '\0';
838
839         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
840                 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
841                 return NULL;
842         }
843
844         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
845         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
846         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
847
848         ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
849
850         /* We now have our complete statement; Lets connect to the server and execute it. */
851         ast_mutex_lock(&pgsql_lock);
852         if (!pgsql_reconnect(database)) {
853                 ast_mutex_unlock(&pgsql_lock);
854                 return NULL;
855         }
856
857         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
858                 ast_log(LOG_WARNING,
859                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
860                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
861                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
862                 ast_mutex_unlock(&pgsql_lock);
863                 return NULL;
864         } else {
865                 ExecStatusType result_status = PQresultStatus(result);
866                 if (result_status != PGRES_COMMAND_OK
867                         && result_status != PGRES_TUPLES_OK
868                         && result_status != PGRES_NONFATAL_ERROR) {
869                         ast_log(LOG_WARNING,
870                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
871                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
872                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
873                                                 PQresultErrorMessage(result), PQresStatus(result_status));
874                         ast_mutex_unlock(&pgsql_lock);
875                         return NULL;
876                 }
877         }
878
879         if ((num_rows = PQntuples(result)) > 0) {
880                 int rowIndex = 0;
881
882                 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
883
884                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
885                         char *field_category = PQgetvalue(result, rowIndex, 0);
886                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
887                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
888                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
889                         if (!strcmp(field_var_name, "#include")) {
890                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
891                                         PQclear(result);
892                                         ast_mutex_unlock(&pgsql_lock);
893                                         return NULL;
894                                 }
895                                 continue;
896                         }
897
898                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
899                                 cur_cat = ast_category_new(field_category, "", 99999);
900                                 if (!cur_cat)
901                                         break;
902                                 strcpy(last, field_category);
903                                 last_cat_metric = atoi(field_cat_metric);
904                                 ast_category_append(cfg, cur_cat);
905                         }
906                         new_v = ast_variable_new(field_var_name, field_var_val, "");
907                         ast_variable_append(cur_cat, new_v);
908                 }
909         } else {
910                 ast_log(LOG_WARNING,
911                                 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
912         }
913
914         PQclear(result);
915         ast_mutex_unlock(&pgsql_lock);
916
917         return cfg;
918 }
919
920 static int require_pgsql(const char *database, const char *tablename, va_list ap)
921 {
922         struct columns *column;
923         struct tables *table = find_table(tablename);
924         char *elm;
925         int type, size, res = 0;
926
927         if (!table) {
928                 ast_log(LOG_WARNING, "Table %s not found in database.  This table should exist if you're using realtime.\n", tablename);
929                 return -1;
930         }
931
932         while ((elm = va_arg(ap, char *))) {
933                 type = va_arg(ap, require_type);
934                 size = va_arg(ap, int);
935                 AST_LIST_TRAVERSE(&table->columns, column, list) {
936                         if (strcmp(column->name, elm) == 0) {
937                                 /* Char can hold anything, as long as it is large enough */
938                                 if ((strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0 || strcmp(column->type, "bpchar") == 0)) {
939                                         if ((size > column->len) && column->len != -1) {
940                                                 ast_log(LOG_WARNING, "Column '%s' should be at least %d long, but is only %d long.\n", column->name, size, column->len);
941                                                 res = -1;
942                                         }
943                                 } else if (strncmp(column->type, "int", 3) == 0) {
944                                         int typesize = atoi(column->type + 3);
945                                         /* Integers can hold only other integers */
946                                         if (type == RQ_INTEGER && ((typesize == 2 && size > 4) || (typesize == 4 && size > 10))) {
947                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
948                                                 res = -1;
949                                         } else if (type != RQ_INTEGER) {
950                                                 ast_log(LOG_WARNING, "Column '%s' is of the incorrect type: (need %s(%d) but saw %s)\n", column->name, type == RQ_CHAR ? "char" : "something else ", size, column->type);
951                                                 res = -1;
952                                         }
953                                 } else if (strncmp(column->type, "float", 5) == 0 && type != RQ_INTEGER && type != RQ_FLOAT) {
954                                         ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
955                                         res = -1;
956                                 } else { /* There are other types that no module implements yet */
957                                         ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
958                                         res = -1;
959                                 }
960                                 break;
961                         }
962                 }
963
964                 if (!column) {
965                         if (requirements == RQ_WARN) {
966                                 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
967                         } else {
968                                 struct ast_str *sql = ast_str_create(100), *fieldtype = ast_str_create(16);
969                                 PGresult *res;
970
971                                 if (requirements == RQ_CREATECHAR || type == RQ_CHAR) {
972                                         ast_str_set(&fieldtype, 0, "CHAR(%d)", size);
973                                 } else if (type == RQ_INTEGER) {
974                                         ast_str_set(&fieldtype, 0, "INT%d", size < 5 ? 2 : (size < 11 ? 4 : 8));
975                                 } else if (type == RQ_FLOAT) {
976                                         ast_str_set(&fieldtype, 0, "FLOAT8");
977                                 } else if (type == RQ_DATE) {
978                                         ast_str_set(&fieldtype, 0, "DATE");
979                                 } else if (type == RQ_DATETIME) {
980                                         ast_str_set(&fieldtype, 0, "TIMESTAMP");
981                                 } else {
982                                         ast_free(sql);
983                                         ast_free(fieldtype);
984                                         continue;
985                                 }
986                                 ast_str_set(&sql, 0, "ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, fieldtype->str);
987                                 ast_debug(1, "About to lock pgsql_lock (running alter on table '%s' to add column '%s')\n", tablename, elm);
988
989                                 ast_mutex_lock(&pgsql_lock);
990                                 if (!pgsql_reconnect(database)) {
991                                         ast_mutex_unlock(&pgsql_lock);
992                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", sql->str);
993                                         ast_free(sql);
994                                         ast_free(fieldtype);
995                                         continue;
996                                 }
997
998                                 ast_debug(1, "About to run ALTER query on table '%s' to add column '%s'\n", tablename, elm);
999                                 res = PQexec(pgsqlConn, sql->str);
1000                                 ast_debug(1, "Finished running ALTER query on table '%s'\n", tablename);
1001                                 if (PQresultStatus(res) != PGRES_COMMAND_OK) {
1002                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", sql->str);
1003                                 }
1004                                 PQclear(res);
1005                                 ast_mutex_unlock(&pgsql_lock);
1006
1007                                 ast_free(sql);
1008                                 ast_free(fieldtype);
1009                         }
1010                 }
1011         }
1012         ast_mutex_unlock(&table->lock);
1013         return res;
1014 }
1015
1016 static int unload_pgsql(const char *database, const char *tablename)
1017 {
1018         struct tables *cur;
1019         ast_debug(1, "About to lock table cache list\n");
1020         AST_LIST_LOCK(&psql_tables);
1021         ast_debug(1, "About to traverse table cache list\n");
1022         AST_LIST_TRAVERSE_SAFE_BEGIN(&psql_tables, cur, list) {
1023                 if (strcmp(cur->name, tablename) == 0) {
1024                         ast_debug(1, "About to remove matching cache entry\n");
1025                         AST_LIST_REMOVE_CURRENT(list);
1026                         ast_debug(1, "About to destroy matching cache entry\n");
1027                         destroy_table(cur);
1028                         ast_debug(1, "Cache entry destroyed\n");
1029                         break;
1030                 }
1031         }
1032         AST_LIST_TRAVERSE_SAFE_END
1033         AST_LIST_UNLOCK(&psql_tables);
1034         ast_debug(1, "About to return\n");
1035         return cur ? 0 : -1;
1036 }
1037
1038 static struct ast_config_engine pgsql_engine = {
1039         .name = "pgsql",
1040         .load_func = config_pgsql,
1041         .realtime_func = realtime_pgsql,
1042         .realtime_multi_func = realtime_multi_pgsql,
1043         .store_func = store_pgsql,
1044         .destroy_func = destroy_pgsql,
1045         .update_func = update_pgsql,
1046         .require_func = require_pgsql,
1047         .unload_func = unload_pgsql,
1048 };
1049
1050 static int load_module(void)
1051 {
1052         if(!parse_config(0))
1053                 return AST_MODULE_LOAD_DECLINE;
1054
1055         ast_config_engine_register(&pgsql_engine);
1056         ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
1057         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
1058
1059         return 0;
1060 }
1061
1062 static int unload_module(void)
1063 {
1064         struct tables *table;
1065         /* Acquire control before doing anything to the module itself. */
1066         ast_mutex_lock(&pgsql_lock);
1067
1068         if (pgsqlConn) {
1069                 PQfinish(pgsqlConn);
1070                 pgsqlConn = NULL;
1071         }
1072         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
1073         ast_config_engine_deregister(&pgsql_engine);
1074         ast_verb(1, "PostgreSQL RealTime unloaded.\n");
1075
1076         /* Destroy cached table info */
1077         AST_LIST_LOCK(&psql_tables);
1078         while ((table = AST_LIST_REMOVE_HEAD(&psql_tables, list))) {
1079                 destroy_table(table);
1080         }
1081         AST_LIST_UNLOCK(&psql_tables);
1082
1083         /* Unlock so something else can destroy the lock. */
1084         ast_mutex_unlock(&pgsql_lock);
1085
1086         return 0;
1087 }
1088
1089 static int reload(void)
1090 {
1091         parse_config(1);
1092
1093         return 0;
1094 }
1095
1096 static int parse_config(int reload)
1097 {
1098         struct ast_config *config;
1099         const char *s;
1100         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
1101
1102         if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
1103                 return 0;
1104
1105         if (!config) {
1106                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
1107                 return 0;
1108         }
1109
1110         ast_mutex_lock(&pgsql_lock);
1111
1112         if (pgsqlConn) {
1113                 PQfinish(pgsqlConn);
1114                 pgsqlConn = NULL;
1115         }
1116
1117         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
1118                 ast_log(LOG_WARNING,
1119                                 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
1120                 strcpy(dbuser, "asterisk");
1121         } else {
1122                 ast_copy_string(dbuser, s, sizeof(dbuser));
1123         }
1124
1125         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
1126                 ast_log(LOG_WARNING,
1127                                 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
1128                 strcpy(dbpass, "asterisk");
1129         } else {
1130                 ast_copy_string(dbpass, s, sizeof(dbpass));
1131         }
1132
1133         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
1134                 ast_log(LOG_WARNING,
1135                                 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
1136                 dbhost[0] = '\0';
1137         } else {
1138                 ast_copy_string(dbhost, s, sizeof(dbhost));
1139         }
1140
1141         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
1142                 ast_log(LOG_WARNING,
1143                                 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
1144                 strcpy(dbname, "asterisk");
1145         } else {
1146                 ast_copy_string(dbname, s, sizeof(dbname));
1147         }
1148
1149         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
1150                 ast_log(LOG_WARNING,
1151                                 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
1152                 dbport = 5432;
1153         } else {
1154                 dbport = atoi(s);
1155         }
1156
1157         if (!ast_strlen_zero(dbhost)) {
1158                 /* No socket needed */
1159         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
1160                 ast_log(LOG_WARNING,
1161                                 "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
1162                 strcpy(dbsock, "/tmp/pgsql.sock");
1163         } else {
1164                 ast_copy_string(dbsock, s, sizeof(dbsock));
1165         }
1166
1167         if (!(s = ast_variable_retrieve(config, "general", "requirements"))) {
1168                 ast_log(LOG_WARNING,
1169                                 "PostgreSQL RealTime: no requirements setting found, using 'warn' as default.\n");
1170                 requirements = RQ_WARN;
1171         } else if (!strcasecmp(s, "createclose")) {
1172                 requirements = RQ_CREATECLOSE;
1173         } else if (!strcasecmp(s, "createchar")) {
1174                 requirements = RQ_CREATECHAR;
1175         }
1176
1177         ast_config_destroy(config);
1178
1179         if (option_debug) {
1180                 if (!ast_strlen_zero(dbhost)) {
1181                         ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
1182                         ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
1183                 } else {
1184                         ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
1185                 }
1186                 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
1187                 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
1188                 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
1189         }
1190
1191         if (!pgsql_reconnect(NULL)) {
1192                 ast_log(LOG_WARNING,
1193                                 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
1194                 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
1195         }
1196
1197         ast_verb(2, "PostgreSQL RealTime reloaded.\n");
1198
1199         /* Done reloading. Release lock so others can now use driver. */
1200         ast_mutex_unlock(&pgsql_lock);
1201
1202         return 1;
1203 }
1204
1205 static int pgsql_reconnect(const char *database)
1206 {
1207         char my_database[50];
1208
1209         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
1210
1211         /* mutex lock should have been locked before calling this function. */
1212
1213         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
1214                 PQfinish(pgsqlConn);
1215                 pgsqlConn = NULL;
1216         }
1217
1218         /* DB password can legitimately be 0-length */
1219         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
1220                 struct ast_str *connInfo = ast_str_create(32);
1221
1222                 ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
1223                         dbhost, dbport, my_database, dbuser);
1224                 if (!ast_strlen_zero(dbpass))
1225                         ast_str_append(&connInfo, 0, " password=%s", dbpass);
1226
1227                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
1228                 pgsqlConn = PQconnectdb(connInfo->str);
1229                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
1230                 ast_free(connInfo);
1231                 connInfo = NULL;
1232
1233                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
1234                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1235                         ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
1236                         connect_time = time(NULL);
1237                         return 1;
1238                 } else {
1239                         ast_log(LOG_ERROR,
1240                                         "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
1241                                         dbname, dbhost, PQresultErrorMessage(NULL));
1242                         return 0;
1243                 }
1244         } else {
1245                 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
1246                 return 1;
1247         }
1248 }
1249
1250 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1251 {
1252         struct tables *cur;
1253         int l, which;
1254         char *ret = NULL;
1255
1256         switch (cmd) {
1257         case CLI_INIT:
1258                 e->command = "realtime pgsql cache";
1259                 e->usage =
1260                         "Usage: realtime pgsql cache [<table>]\n"
1261                         "       Shows table cache for the PostgreSQL RealTime driver\n";
1262                 return NULL;
1263         case CLI_GENERATE:
1264                 if (a->argc != 3) {
1265                         return NULL;
1266                 }
1267                 l = strlen(a->word);
1268                 which = 0;
1269                 AST_LIST_LOCK(&psql_tables);
1270                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1271                         if (!strncasecmp(a->word, cur->name, l) && ++which > a->n) {
1272                                 ret = ast_strdup(cur->name);
1273                                 break;
1274                         }
1275                 }
1276                 AST_LIST_UNLOCK(&psql_tables);
1277                 return ret;
1278         }
1279
1280         if (a->argc == 3) {
1281                 /* List of tables */
1282                 AST_LIST_LOCK(&psql_tables);
1283                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1284                         ast_cli(a->fd, "%s\n", cur->name);
1285                 }
1286                 AST_LIST_UNLOCK(&psql_tables);
1287         } else if (a->argc == 4) {
1288                 /* List of columns */
1289                 if ((cur = find_table(a->argv[3]))) {
1290                         struct columns *col;
1291                         ast_cli(a->fd, "Columns for Table Cache '%s':\n", a->argv[3]);
1292                         ast_cli(a->fd, "%-20.20s %-20.20s %-3.3s %-8.8s\n", "Name", "Type", "Len", "Nullable");
1293                         AST_LIST_TRAVERSE(&cur->columns, col, list) {
1294                                 ast_cli(a->fd, "%-20.20s %-20.20s %3d %-8.8s\n", col->name, col->type, col->len, col->notnull ? "NOT NULL" : "");
1295                         }
1296                         ast_mutex_unlock(&cur->lock);
1297                 } else {
1298                         ast_cli(a->fd, "No such table '%s'\n", a->argv[3]);
1299                 }
1300         }
1301         return 0;
1302 }
1303
1304 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1305 {
1306         char status[256], status2[100] = "";
1307         int ctime = time(NULL) - connect_time;
1308
1309         switch (cmd) {
1310         case CLI_INIT:
1311                 e->command = "realtime pgsql status";
1312                 e->usage =
1313                         "Usage: realtime pgsql status\n"
1314                         "       Shows connection information for the PostgreSQL RealTime driver\n";
1315                 return NULL;
1316         case CLI_GENERATE:
1317                 return NULL;
1318         }
1319
1320         if (a->argc != 3)
1321                 return CLI_SHOWUSAGE;
1322
1323         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1324                 if (!ast_strlen_zero(dbhost))
1325                         snprintf(status, 255, "Connected to %s@%s, port %d", dbname, dbhost, dbport);
1326                 else if (!ast_strlen_zero(dbsock))
1327                         snprintf(status, 255, "Connected to %s on socket file %s", dbname, dbsock);
1328                 else
1329                         snprintf(status, 255, "Connected to %s@%s", dbname, dbhost);
1330
1331                 if (!ast_strlen_zero(dbuser))
1332                         snprintf(status2, 99, " with username %s", dbuser);
1333
1334                 if (ctime > 31536000)
1335                         ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
1336                                         status, status2, ctime / 31536000, (ctime % 31536000) / 86400,
1337                                         (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
1338                 else if (ctime > 86400)
1339                         ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
1340                                         status2, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
1341                                         ctime % 60);
1342                 else if (ctime > 3600)
1343                         ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, status2,
1344                                         ctime / 3600, (ctime % 3600) / 60, ctime % 60);
1345                 else if (ctime > 60)
1346                         ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, status2, ctime / 60,
1347                                         ctime % 60);
1348                 else
1349                         ast_cli(a->fd, "%s%s for %d seconds.\n", status, status2, ctime);
1350
1351                 return CLI_SUCCESS;
1352         } else {
1353                 return CLI_FAILURE;
1354         }
1355 }
1356
1357 /* needs usecount semantics defined */
1358 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
1359                 .load = load_module,
1360                 .unload = unload_module,
1361                 .reload = reload
1362                );