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