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