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