This code was in team/murf/bug8684-trunk; it should fix bug 8684 in trunk. I didn...
[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 <stdlib.h>
34 #include <stdio.h>
35 #include <string.h>
36 #include <libpq-fe.h>                   /* PostgreSQL */
37
38 #include "asterisk/file.h"
39 #include "asterisk/logger.h"
40 #include "asterisk/channel.h"
41 #include "asterisk/pbx.h"
42 #include "asterisk/config.h"
43 #include "asterisk/module.h"
44 #include "asterisk/lock.h"
45 #include "asterisk/options.h"
46 #include "asterisk/utils.h"
47 #include "asterisk/cli.h"
48
49 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
50
51 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
52
53 PGconn *pgsqlConn = NULL;
54
55 #define MAX_DB_OPTION_SIZE 64
56
57 static char dbhost[MAX_DB_OPTION_SIZE] = "";
58 static char dbuser[MAX_DB_OPTION_SIZE] = "";
59 static char dbpass[MAX_DB_OPTION_SIZE] = "";
60 static char dbname[MAX_DB_OPTION_SIZE] = "";
61 static char dbsock[MAX_DB_OPTION_SIZE] = "";
62 static int dbport = 5432;
63 static time_t connect_time = 0;
64
65 static int parse_config(int reload);
66 static int pgsql_reconnect(const char *database);
67 static int realtime_pgsql_status(int fd, int argc, char **argv);
68
69 static const char cli_realtime_pgsql_status_usage[] =
70         "Usage: realtime pgsql status\n"
71         "       Shows connection information for the Postgresql RealTime driver\n";
72
73 static struct ast_cli_entry cli_realtime[] = {
74         { { "realtime", "pgsql", "status", NULL },
75         realtime_pgsql_status, "Shows connection information for the Postgresql RealTime driver",
76         cli_realtime_pgsql_status_usage },
77 };
78
79 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
80 {
81         PGresult *result = NULL;
82         int num_rows = 0;
83         char sql[256];
84         char *stringp;
85         char *chunk;
86         char *op;
87         const char *newparam, *newval;
88         struct ast_variable *var = NULL, *prev = NULL;
89
90         if (!table) {
91                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
92                 return NULL;
93         }
94
95         /* Get the first parameter and first value in our list of passed paramater/value pairs */
96         newparam = va_arg(ap, const char *);
97         newval = va_arg(ap, const char *);
98         if (!newparam || !newval) {
99                 ast_log(LOG_WARNING,
100                                 "Postgresql RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
101                 if (pgsqlConn) {
102                         PQfinish(pgsqlConn);
103                         pgsqlConn = NULL;
104                 };
105                 return NULL;
106         }
107
108         /* Create the first part of the query using the first parameter/value pairs we just extracted
109            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
110         op = strchr(newparam, ' ') ? "" : " =";
111
112         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
113                          newval);
114         while ((newparam = va_arg(ap, const char *))) {
115                 newval = va_arg(ap, const char *);
116                 if (!strchr(newparam, ' '))
117                         op = " =";
118                 else
119                         op = "";
120                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
121                                  op, newval);
122         }
123         va_end(ap);
124
125         /* We now have our complete statement; Lets connect to the server and execute it. */
126         ast_mutex_lock(&pgsql_lock);
127         if (!pgsql_reconnect(database)) {
128                 ast_mutex_unlock(&pgsql_lock);
129                 return NULL;
130         }
131
132         if (!(result = PQexec(pgsqlConn, sql))) {
133                 ast_log(LOG_WARNING,
134                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
135                 ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
136                 ast_debug(1, "Postgresql RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
137                 ast_mutex_unlock(&pgsql_lock);
138                 return NULL;
139         } else {
140                 ExecStatusType result_status = PQresultStatus(result);
141                 if (result_status != PGRES_COMMAND_OK
142                         && result_status != PGRES_TUPLES_OK
143                         && result_status != PGRES_NONFATAL_ERROR) {
144                         ast_log(LOG_WARNING,
145                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
146                         ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
147                         ast_debug(1, "Postgresql RealTime: Query Failed because: %s (%s)\n",
148                                                 PQresultErrorMessage(result), PQresStatus(result_status));
149                         ast_mutex_unlock(&pgsql_lock);
150                         return NULL;
151                 }
152         }
153
154         ast_debug(1, "1Postgresql RealTime: Result=%p Query: %s\n", result, sql);
155
156         if ((num_rows = PQntuples(result)) > 0) {
157                 int i = 0;
158                 int rowIndex = 0;
159                 int numFields = PQnfields(result);
160                 char **fieldnames = NULL;
161
162                 ast_debug(1, "Postgresql RealTime: Found %d rows.\n", num_rows);
163
164                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
165                         ast_mutex_unlock(&pgsql_lock);
166                         PQclear(result);
167                         return NULL;
168                 }
169                 for (i = 0; i < numFields; i++)
170                         fieldnames[i] = PQfname(result, i);
171                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
172                         for (i = 0; i < numFields; i++) {
173                                 stringp = PQgetvalue(result, rowIndex, i);
174                                 while (stringp) {
175                                         chunk = strsep(&stringp, ";");
176                                         if (!ast_strlen_zero(ast_strip(chunk))) {
177                                                 if (prev) {
178                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
179                                                         if (prev->next) {
180                                                                 prev = prev->next;
181                                                         }
182                                                 } else {
183                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
184                                                 }
185                                         }
186                                 }
187                         }
188                 }
189                 ast_free(fieldnames);
190         } else {
191                 ast_log(LOG_WARNING,
192                                 "Postgresql RealTime: Could not find any rows in table %s.\n", table);
193         }
194
195         ast_mutex_unlock(&pgsql_lock);
196         PQclear(result);
197
198         return var;
199 }
200
201 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
202 {
203         PGresult *result = NULL;
204         int num_rows = 0;
205         char sql[256];
206         const char *initfield = NULL;
207         char *stringp;
208         char *chunk;
209         char *op;
210         const char *newparam, *newval;
211         struct ast_variable *var = NULL;
212         struct ast_config *cfg = NULL;
213         struct ast_category *cat = NULL;
214
215         if (!table) {
216                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
217                 return NULL;
218         }
219
220         if (!(cfg = ast_config_new()))
221                 return NULL;
222
223         /* Get the first parameter and first value in our list of passed paramater/value pairs */
224         newparam = va_arg(ap, const char *);
225         newval = va_arg(ap, const char *);
226         if (!newparam || !newval) {
227                 ast_log(LOG_WARNING,
228                                 "Postgresql RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
229                 if (pgsqlConn) {
230                         PQfinish(pgsqlConn);
231                         pgsqlConn = NULL;
232                 };
233                 return NULL;
234         }
235
236         initfield = ast_strdupa(newparam);
237         if ((op = strchr(initfield, ' '))) {
238                 *op = '\0';
239         }
240
241         /* Create the first part of the query using the first parameter/value pairs we just extracted
242            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
243
244         if (!strchr(newparam, ' '))
245                 op = " =";
246         else
247                 op = "";
248
249         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
250                          newval);
251         while ((newparam = va_arg(ap, const char *))) {
252                 newval = va_arg(ap, const char *);
253                 if (!strchr(newparam, ' '))
254                         op = " =";
255                 else
256                         op = "";
257                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
258                                  op, newval);
259         }
260
261         if (initfield) {
262                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
263         }
264
265         va_end(ap);
266
267         /* We now have our complete statement; Lets connect to the server and execute it. */
268         ast_mutex_lock(&pgsql_lock);
269         if (!pgsql_reconnect(database)) {
270                 ast_mutex_unlock(&pgsql_lock);
271                 return NULL;
272         }
273
274         if (!(result = PQexec(pgsqlConn, sql))) {
275                 ast_log(LOG_WARNING,
276                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
277                 ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
278                 ast_debug(1, "Postgresql RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
279                 ast_mutex_unlock(&pgsql_lock);
280                 return NULL;
281         } else {
282                 ExecStatusType result_status = PQresultStatus(result);
283                 if (result_status != PGRES_COMMAND_OK
284                         && result_status != PGRES_TUPLES_OK
285                         && result_status != PGRES_NONFATAL_ERROR) {
286                         ast_log(LOG_WARNING,
287                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
288                         ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
289                         ast_debug(1, "Postgresql RealTime: Query Failed because: %s (%s)\n",
290                                                 PQresultErrorMessage(result), PQresStatus(result_status));
291                         ast_mutex_unlock(&pgsql_lock);
292                         return NULL;
293                 }
294         }
295
296         ast_debug(1, "2Postgresql RealTime: Result=%p Query: %s\n", result, sql);
297
298         if ((num_rows = PQntuples(result)) > 0) {
299                 int numFields = PQnfields(result);
300                 int i = 0;
301                 int rowIndex = 0;
302                 char **fieldnames = NULL;
303
304                 ast_debug(1, "Postgresql RealTime: Found %d rows.\n", num_rows);
305
306                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
307                         ast_mutex_unlock(&pgsql_lock);
308                         PQclear(result);
309                         return NULL;
310                 }
311                 for (i = 0; i < numFields; i++)
312                         fieldnames[i] = PQfname(result, i);
313
314                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
315                         var = NULL;
316                         if (!(cat = ast_category_new("","",99999)))
317                                 continue;
318                         for (i = 0; i < numFields; i++) {
319                                 stringp = PQgetvalue(result, rowIndex, i);
320                                 while (stringp) {
321                                         chunk = strsep(&stringp, ";");
322                                         if (!ast_strlen_zero(ast_strip(chunk))) {
323                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
324                                                         ast_category_rename(cat, chunk);
325                                                 }
326                                                 var = ast_variable_new(fieldnames[i], chunk, "");
327                                                 ast_variable_append(cat, var);
328                                         }
329                                 }
330                         }
331                         ast_category_append(cfg, cat);
332                 }
333                 ast_free(fieldnames);
334         } else {
335                 ast_log(LOG_WARNING,
336                                 "Postgresql RealTime: Could not find any rows in table %s.\n", table);
337         }
338
339         ast_mutex_unlock(&pgsql_lock);
340         PQclear(result);
341
342         return cfg;
343 }
344
345 static int update_pgsql(const char *database, const char *table, const char *keyfield,
346                                                 const char *lookup, va_list ap)
347 {
348         PGresult *result = NULL;
349         int numrows = 0;
350         char sql[256];
351         const char *newparam, *newval;
352
353         if (!table) {
354                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
355                 return -1;
356         }
357
358         /* Get the first parameter and first value in our list of passed paramater/value pairs */
359         newparam = va_arg(ap, const char *);
360         newval = va_arg(ap, const char *);
361         if (!newparam || !newval) {
362                 ast_log(LOG_WARNING,
363                                 "Postgresql RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
364                 if (pgsqlConn) {
365                         PQfinish(pgsqlConn);
366                         pgsqlConn = NULL;
367                 };
368                 return -1;
369         }
370
371         /* Create the first part of the query using the first parameter/value pairs we just extracted
372            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
373
374         snprintf(sql, sizeof(sql), "UPDATE %s SET %s = '%s'", table, newparam, newval);
375         while ((newparam = va_arg(ap, const char *))) {
376                 newval = va_arg(ap, const char *);
377                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s = '%s'", newparam,
378                                  newval);
379         }
380         va_end(ap);
381         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s = '%s'", keyfield,
382                          lookup);
383
384         ast_debug(1, "Postgresql RealTime: Update SQL: %s\n", sql);
385
386         /* We now have our complete statement; Lets connect to the server and execute it. */
387         ast_mutex_lock(&pgsql_lock);
388         if (!pgsql_reconnect(database)) {
389                 ast_mutex_unlock(&pgsql_lock);
390                 return -1;
391         }
392
393         if (!(result = PQexec(pgsqlConn, sql))) {
394                 ast_log(LOG_WARNING,
395                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
396                 ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
397                 ast_debug(1, "Postgresql RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
398                 ast_mutex_unlock(&pgsql_lock);
399                 return -1;
400         } else {
401                 ExecStatusType result_status = PQresultStatus(result);
402                 if (result_status != PGRES_COMMAND_OK
403                         && result_status != PGRES_TUPLES_OK
404                         && result_status != PGRES_NONFATAL_ERROR) {
405                         ast_log(LOG_WARNING,
406                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
407                         ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
408                         ast_debug(1, "Postgresql RealTime: Query Failed because: %s (%s)\n",
409                                                 PQresultErrorMessage(result), PQresStatus(result_status));
410                         ast_mutex_unlock(&pgsql_lock);
411                         return -1;
412                 }
413         }
414
415         numrows = atoi(PQcmdTuples(result));
416         ast_mutex_unlock(&pgsql_lock);
417
418         ast_debug(1, "Postgresql RealTime: Updated %d rows on table: %s\n", numrows, table);
419
420         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
421          * An integer greater than zero indicates the number of rows affected
422          * Zero indicates that no records were updated
423          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
424          */
425
426         if (numrows >= 0)
427                 return (int) numrows;
428
429         return -1;
430 }
431
432 static int store_pgsql(const char *database, const char *table, va_list ap)
433 {
434         PGresult *result = NULL;
435         Oid insertid;
436         char sql[256];
437         char params[256];
438         char vals[256];
439         char buf[256];
440         int pgresult;
441         const char *newparam, *newval;
442
443         if (!table) {
444                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
445                 return -1;
446         }
447
448         /* Get the first parameter and first value in our list of passed paramater/value pairs */
449         newparam = va_arg(ap, const char *);
450         newval = va_arg(ap, const char *);
451         if (!newparam || !newval) {
452                 ast_log(LOG_WARNING,
453                                 "Postgresql RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
454                 if (pgsqlConn) {
455                         PQfinish(pgsqlConn);
456                         pgsqlConn = NULL;
457                 };
458                 return -1;
459         }
460
461         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
462         ast_mutex_lock(&pgsql_lock);
463         if (!pgsql_reconnect(database)) {
464                 ast_mutex_unlock(&pgsql_lock);
465                 return -1;
466         }
467
468         /* Create the first part of the query using the first parameter/value pairs we just extracted
469            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
470         PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
471         snprintf(params, sizeof(params), "%s", buf);
472         PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
473         snprintf(vals, sizeof(vals), "'%s'", buf);
474         while ((newparam = va_arg(ap, const char *))) {
475                 newval = va_arg(ap, const char *);
476                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
477                 snprintf(params + strlen(params), sizeof(params) - strlen(params), ", %s", buf);
478                 PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
479                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", '%s'", buf);
480         }
481         va_end(ap);
482         snprintf(sql, sizeof(sql), "INSERT INTO (%s) VALUES (%s)", params, vals);
483
484         ast_debug(1, "Postgresql RealTime: Insert SQL: %s\n", sql);
485
486         if (!(result = PQexec(pgsqlConn, sql))) {
487                 ast_log(LOG_WARNING,
488                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
489                 ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
490                 ast_debug(1, "Postgresql RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
491                 ast_mutex_unlock(&pgsql_lock);
492                 return -1;
493         } else {
494                 ExecStatusType result_status = PQresultStatus(result);
495                 if (result_status != PGRES_COMMAND_OK
496                         && result_status != PGRES_TUPLES_OK
497                         && result_status != PGRES_NONFATAL_ERROR) {
498                         ast_log(LOG_WARNING,
499                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
500                         ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
501                         ast_debug(1, "Postgresql RealTime: Query Failed because: %s (%s)\n",
502                                                 PQresultErrorMessage(result), PQresStatus(result_status));
503                         ast_mutex_unlock(&pgsql_lock);
504                         return -1;
505                 }
506         }
507
508         insertid = PQoidValue(result);
509         ast_mutex_unlock(&pgsql_lock);
510
511         ast_debug(1, "Postgresql RealTime: row inserted on table: %s, id: %u\n", table, insertid);
512
513         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
514          * An integer greater than zero indicates the number of rows affected
515          * Zero indicates that no records were updated
516          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
517          */
518
519         if (insertid >= 0)
520                 return (int) insertid;
521
522         return -1;
523 }
524
525 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
526 {
527         PGresult *result = NULL;
528         int numrows = 0;
529         int pgresult;
530         char sql[256];
531         char buf[256], buf2[256];
532         const char *newparam, *newval;
533
534         if (!table) {
535                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
536                 return -1;
537         }
538
539         /* Get the first parameter and first value in our list of passed paramater/value pairs */
540         /*newparam = va_arg(ap, const char *);
541         newval = va_arg(ap, const char *);
542         if (!newparam || !newval) {*/
543         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
544                 ast_log(LOG_WARNING,
545                                 "Postgresql RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
546                 if (pgsqlConn) {
547                         PQfinish(pgsqlConn);
548                         pgsqlConn = NULL;
549                 };
550                 return -1;
551         }
552
553         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
554         ast_mutex_lock(&pgsql_lock);
555         if (!pgsql_reconnect(database)) {
556                 ast_mutex_unlock(&pgsql_lock);
557                 return -1;
558         }
559
560
561         /* Create the first part of the query using the first parameter/value pairs we just extracted
562            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
563
564         PQescapeStringConn(pgsqlConn, buf, keyfield, sizeof(keyfield), &pgresult);
565         PQescapeStringConn(pgsqlConn, buf2, lookup, sizeof(lookup), &pgresult);
566         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = '%s'", table, buf, buf2);
567         while ((newparam = va_arg(ap, const char *))) {
568                 newval = va_arg(ap, const char *);
569                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
570                 PQescapeStringConn(pgsqlConn, buf2, newval, sizeof(newval), &pgresult);
571                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s = '%s'", buf, buf2);
572         }
573         va_end(ap);
574
575         ast_debug(1, "Postgresql RealTime: Delete SQL: %s\n", sql);
576
577         if (!(result = PQexec(pgsqlConn, sql))) {
578                 ast_log(LOG_WARNING,
579                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
580                 ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
581                 ast_debug(1, "Postgresql RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
582                 ast_mutex_unlock(&pgsql_lock);
583                 return -1;
584         } else {
585                 ExecStatusType result_status = PQresultStatus(result);
586                 if (result_status != PGRES_COMMAND_OK
587                         && result_status != PGRES_TUPLES_OK
588                         && result_status != PGRES_NONFATAL_ERROR) {
589                         ast_log(LOG_WARNING,
590                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
591                         ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
592                         ast_debug(1, "Postgresql RealTime: Query Failed because: %s (%s)\n",
593                                                 PQresultErrorMessage(result), PQresStatus(result_status));
594                         ast_mutex_unlock(&pgsql_lock);
595                         return -1;
596                 }
597         }
598
599         numrows = atoi(PQcmdTuples(result));
600         ast_mutex_unlock(&pgsql_lock);
601
602         ast_debug(1, "Postgresql RealTime: Deleted %d rows on table: %s\n", numrows, table);
603
604         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
605          * An integer greater than zero indicates the number of rows affected
606          * Zero indicates that no records were updated
607          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
608          */
609
610         if (numrows >= 0)
611                 return (int) numrows;
612
613         return -1;
614 }
615
616
617 static struct ast_config *config_pgsql(const char *database, const char *table,
618                                                                            const char *file, struct ast_config *cfg,
619                                                                            struct ast_flags flags, const char *suggested_incl)
620 {
621         PGresult *result = NULL;
622         long num_rows;
623         struct ast_variable *new_v;
624         struct ast_category *cur_cat = NULL;
625         char sqlbuf[1024] = "";
626         char *sql;
627         size_t sqlleft = sizeof(sqlbuf);
628         char last[80] = "";
629         int last_cat_metric = 0;
630
631         last[0] = '\0';
632
633         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
634                 ast_log(LOG_WARNING, "Postgresql RealTime: Cannot configure myself.\n");
635                 return NULL;
636         }
637
638         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
639         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
640         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
641
642         ast_debug(1, "Postgresql RealTime: Static SQL: %s\n", sqlbuf);
643
644         /* We now have our complete statement; Lets connect to the server and execute it. */
645         ast_mutex_lock(&pgsql_lock);
646         if (!pgsql_reconnect(database)) {
647                 ast_mutex_unlock(&pgsql_lock);
648                 return NULL;
649         }
650
651         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
652                 ast_log(LOG_WARNING,
653                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
654                 ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
655                 ast_debug(1, "Postgresql RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
656                 ast_mutex_unlock(&pgsql_lock);
657                 return NULL;
658         } else {
659                 ExecStatusType result_status = PQresultStatus(result);
660                 if (result_status != PGRES_COMMAND_OK
661                         && result_status != PGRES_TUPLES_OK
662                         && result_status != PGRES_NONFATAL_ERROR) {
663                         ast_log(LOG_WARNING,
664                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
665                         ast_debug(1, "Postgresql RealTime: Query: %s\n", sql);
666                         ast_debug(1, "Postgresql RealTime: Query Failed because: %s (%s)\n",
667                                                 PQresultErrorMessage(result), PQresStatus(result_status));
668                         ast_mutex_unlock(&pgsql_lock);
669                         return NULL;
670                 }
671         }
672
673         if ((num_rows = PQntuples(result)) > 0) {
674                 int rowIndex = 0;
675
676                 ast_debug(1, "Postgresql RealTime: Found %ld rows.\n", num_rows);
677
678                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
679                         char *field_category = PQgetvalue(result, rowIndex, 0);
680                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
681                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
682                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
683                         if (!strcmp(field_var_name, "#include")) {
684                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "")) {
685                                         PQclear(result);
686                                         ast_mutex_unlock(&pgsql_lock);
687                                         return NULL;
688                                 }
689                                 continue;
690                         }
691
692                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
693                                 cur_cat = ast_category_new(field_category, "", 99999);
694                                 if (!cur_cat)
695                                         break;
696                                 strcpy(last, field_category);
697                                 last_cat_metric = atoi(field_cat_metric);
698                                 ast_category_append(cfg, cur_cat);
699                         }
700                         new_v = ast_variable_new(field_var_name, field_var_val, "");
701                         ast_variable_append(cur_cat, new_v);
702                 }
703         } else {
704                 ast_log(LOG_WARNING,
705                                 "Postgresql RealTime: Could not find config '%s' in database.\n", file);
706         }
707
708         PQclear(result);
709         ast_mutex_unlock(&pgsql_lock);
710
711         return cfg;
712 }
713
714 static struct ast_config_engine pgsql_engine = {
715         .name = "pgsql",
716         .load_func = config_pgsql,
717         .realtime_func = realtime_pgsql,
718         .realtime_multi_func = realtime_multi_pgsql,
719         .store_func = store_pgsql,
720         .destroy_func = destroy_pgsql,
721         .update_func = update_pgsql
722 };
723
724 static int load_module(void)
725 {
726         if(!parse_config(0))
727                 return AST_MODULE_LOAD_DECLINE;
728
729         ast_config_engine_register(&pgsql_engine);
730         ast_verb(1, "Postgresql RealTime driver loaded.\n");
731         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
732
733         return 0;
734 }
735
736 static int unload_module(void)
737 {
738         /* Acquire control before doing anything to the module itself. */
739         ast_mutex_lock(&pgsql_lock);
740
741         if (pgsqlConn) {
742                 PQfinish(pgsqlConn);
743                 pgsqlConn = NULL;
744         }
745         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
746         ast_config_engine_deregister(&pgsql_engine);
747         ast_verb(1, "Postgresql RealTime unloaded.\n");
748
749         /* Unlock so something else can destroy the lock. */
750         ast_mutex_unlock(&pgsql_lock);
751
752         return 0;
753 }
754
755 static int reload(void)
756 {
757         parse_config(1);
758
759         return 0;
760 }
761
762 static int parse_config(int reload)
763 {
764         struct ast_config *config;
765         const char *s;
766         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
767
768         if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
769                 return 0;
770
771         if (!config) {
772                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
773                 return 0;
774         }
775
776         ast_mutex_lock(&pgsql_lock);
777
778         if (pgsqlConn) {
779                 PQfinish(pgsqlConn);
780                 pgsqlConn = NULL;
781         }
782
783         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
784                 ast_log(LOG_WARNING,
785                                 "Postgresql RealTime: No database user found, using 'asterisk' as default.\n");
786                 strcpy(dbuser, "asterisk");
787         } else {
788                 ast_copy_string(dbuser, s, sizeof(dbuser));
789         }
790
791         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
792                 ast_log(LOG_WARNING,
793                                 "Postgresql RealTime: No database password found, using 'asterisk' as default.\n");
794                 strcpy(dbpass, "asterisk");
795         } else {
796                 ast_copy_string(dbpass, s, sizeof(dbpass));
797         }
798
799         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
800                 ast_log(LOG_WARNING,
801                                 "Postgresql RealTime: No database host found, using localhost via socket.\n");
802                 dbhost[0] = '\0';
803         } else {
804                 ast_copy_string(dbhost, s, sizeof(dbhost));
805         }
806
807         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
808                 ast_log(LOG_WARNING,
809                                 "Postgresql RealTime: No database name found, using 'asterisk' as default.\n");
810                 strcpy(dbname, "asterisk");
811         } else {
812                 ast_copy_string(dbname, s, sizeof(dbname));
813         }
814
815         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
816                 ast_log(LOG_WARNING,
817                                 "Postgresql RealTime: No database port found, using 5432 as default.\n");
818                 dbport = 5432;
819         } else {
820                 dbport = atoi(s);
821         }
822
823         if (dbhost && !(s = ast_variable_retrieve(config, "general", "dbsock"))) {
824                 ast_log(LOG_WARNING,
825                                 "Postgresql RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
826                 strcpy(dbsock, "/tmp/pgsql.sock");
827         } else {
828                 ast_copy_string(dbsock, s, sizeof(dbsock));
829         }
830         ast_config_destroy(config);
831
832         if (option_debug) {
833                 if (dbhost) {
834                         ast_debug(1, "Postgresql RealTime Host: %s\n", dbhost);
835                         ast_debug(1, "Postgresql RealTime Port: %i\n", dbport);
836                 } else {
837                         ast_debug(1, "Postgresql RealTime Socket: %s\n", dbsock);
838                 }
839                 ast_debug(1, "Postgresql RealTime User: %s\n", dbuser);
840                 ast_debug(1, "Postgresql RealTime Password: %s\n", dbpass);
841                 ast_debug(1, "Postgresql RealTime DBName: %s\n", dbname);
842         }
843
844         if (!pgsql_reconnect(NULL)) {
845                 ast_log(LOG_WARNING,
846                                 "Postgresql RealTime: Couldn't establish connection. Check debug.\n");
847                 ast_debug(1, "Postgresql RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
848         }
849
850         ast_verb(2, "Postgresql RealTime reloaded.\n");
851
852         /* Done reloading. Release lock so others can now use driver. */
853         ast_mutex_unlock(&pgsql_lock);
854
855         return 1;
856 }
857
858 static int pgsql_reconnect(const char *database)
859 {
860         char my_database[50];
861
862         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
863
864         /* mutex lock should have been locked before calling this function. */
865
866         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
867                 PQfinish(pgsqlConn);
868                 pgsqlConn = NULL;
869         }
870
871         if ((!pgsqlConn) && (dbhost || dbsock) && dbuser && dbpass && my_database) {
872                 char *connInfo = NULL;
873                 unsigned int size = 100 + strlen(dbhost)
874                         + strlen(dbuser)
875                         + strlen(dbpass)
876                         + strlen(my_database);
877                 
878                 if (!(connInfo = ast_malloc(size)))
879                         return 0;
880                 
881                 sprintf(connInfo, "host=%s port=%d dbname=%s user=%s password=%s",
882                                         dbhost, dbport, my_database, dbuser, dbpass);
883                 ast_debug(1, "%u connInfo=%s\n", size, connInfo);
884                 pgsqlConn = PQconnectdb(connInfo);
885                 ast_debug(1, "%u connInfo=%s\n", size, connInfo);
886                 ast_free(connInfo);
887                 connInfo = NULL;
888                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
889                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
890                         ast_debug(1, "Postgresql RealTime: Successfully connected to database.\n");
891                         connect_time = time(NULL);
892                         return 1;
893                 } else {
894                         ast_log(LOG_ERROR,
895                                         "Postgresql RealTime: Failed to connect database server %s on %s. Check debug for more info.\n",
896                                         dbname, dbhost);
897                         ast_debug(1, "Postgresql RealTime: Cannot Connect: %s\n", PQresultErrorMessage(NULL));
898                         return 0;
899                 }
900         } else {
901                 ast_debug(1, "Postgresql RealTime: Everything is fine.\n");
902                 return 1;
903         }
904 }
905
906 static int realtime_pgsql_status(int fd, int argc, char **argv)
907 {
908         char status[256], status2[100] = "";
909         int ctime = time(NULL) - connect_time;
910
911         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
912                 if (dbhost) {
913                         snprintf(status, 255, "Connected to %s@%s, port %d", dbname, dbhost, dbport);
914                 } else if (dbsock) {
915                         snprintf(status, 255, "Connected to %s on socket file %s", dbname, dbsock);
916                 } else {
917                         snprintf(status, 255, "Connected to %s@%s", dbname, dbhost);
918                 }
919
920                 if (dbuser && *dbuser) {
921                         snprintf(status2, 99, " with username %s", dbuser);
922                 }
923
924                 if (ctime > 31536000) {
925                         ast_cli(fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
926                                         status, status2, ctime / 31536000, (ctime % 31536000) / 86400,
927                                         (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
928                 } else if (ctime > 86400) {
929                         ast_cli(fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
930                                         status2, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
931                                         ctime % 60);
932                 } else if (ctime > 3600) {
933                         ast_cli(fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, status2,
934                                         ctime / 3600, (ctime % 3600) / 60, ctime % 60);
935                 } else if (ctime > 60) {
936                         ast_cli(fd, "%s%s for %d minutes, %d seconds.\n", status, status2, ctime / 60,
937                                         ctime % 60);
938                 } else {
939                         ast_cli(fd, "%s%s for %d seconds.\n", status, status2, ctime);
940                 }
941
942                 return RESULT_SUCCESS;
943         } else {
944                 return RESULT_FAILURE;
945         }
946 }
947
948 /* needs usecount semantics defined */
949 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
950                 .load = load_module,
951                 .unload = unload_module,
952                 .reload = reload
953                );