Merge rgagnon's pedantic string changes (apps n-z) (bug #2038)
[asterisk/asterisk.git] / apps / app_sql_postgres.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * Connect to PostgreSQL
5  * 
6  * Copyright (C) 2002, Christos Ricudis
7  *
8  * Christos Ricudis <ricudis@itc.auth.gr>
9  *
10  * This program is free software, distributed under the terms of
11  * the GNU General Public License
12  */
13
14 #include <asterisk/file.h>
15 #include <asterisk/logger.h>
16 #include <asterisk/channel.h>
17 #include <asterisk/pbx.h>
18 #include <asterisk/module.h>
19 #include <asterisk/linkedlists.h>
20 #include <asterisk/chanvars.h>
21 #include <asterisk/lock.h>
22 #include <stdlib.h>
23 #include <unistd.h>
24 #include <string.h>
25 #include <stdlib.h>
26 #include <sys/types.h>
27 #include <stdio.h>
28 #include <unistd.h>
29 #include "libpq-fe.h"
30
31 #define EXTRA_LOG 0
32
33
34 static char *tdesc = "Simple PostgreSQL Interface";
35
36 static char *app = "PGSQL";
37
38 static char *synopsis = "Do several SQLy things";
39
40 static char *descrip = 
41 "PGSQL():  Do several SQLy things\n"
42 "Syntax:\n"
43 "  PGSQL(Connect var option-string)\n"
44 "    Connects to a database.  Option string contains standard PostgreSQL\n"
45 "    parameters like host=, dbname=, user=.  Connection identifer returned\n"
46 "    in ${var}\n"
47 "  PGSQL(Query var ${connection_identifier} query-string)\n"
48 "    Executes standard SQL query contained in query-string using established\n"
49 "    connection identified by ${connection_identifier}. Reseult of query is\n"
50 "    is stored in ${var}.\n"
51 "  PGSQL(Fetch statusvar ${result_identifier} var1 var2 ... varn)\n"
52 "    Fetches a single row from a result set contained in ${result_identifier}.\n"
53 "    Assigns returned fields to ${var1} ... ${varn}.  ${statusvar} is set TRUE\n"
54 "    if additional rows exist in reseult set.\n"
55 "  PGSQL(Clear ${result_identifier})\n"
56 "    Frees memory and datastructures associated with result set.\n" 
57 "  PGSQL(Disconnect ${connection_identifier})\n"
58 "    Disconnects from named connection to PostgreSQL.\n" ;
59
60 /*
61
62 Syntax of SQL commands : 
63
64         Connect var option-string
65         
66         Connects to a database using the option-string and stores the 
67         connection identifier in ${var}
68         
69         
70         Query var ${connection_identifier} query-string
71         
72         Submits query-string to database backend and stores the result
73         identifier in ${var}
74         
75         
76         Fetch statusvar ${result_identifier} var1 var2 var3 ... varn
77         
78         Fetches a row from the query and stores end-of-table status in 
79         ${statusvar} and columns in ${var1}..${varn}
80         
81         
82         Clear ${result_identifier}
83
84         Clears data structures associated with ${result_identifier}
85         
86         
87         Disconnect ${connection_identifier}
88         
89         Disconnects from named connection
90         
91         
92 EXAMPLES OF USE : 
93
94 exten => s,2,PGSQL(Connect connid host=localhost user=asterisk dbname=credit)
95 exten => s,3,PGSQL(Query resultid ${connid} SELECT username,credit FROM credit WHERE callerid=${CALLERIDNUM})
96 exten => s,4,PGSQL(Fetch fetchid ${resultid} datavar1 datavar2)
97 exten => s,5,GotoIf(${fetchid}?6:8)
98 exten => s,6,Festival("User ${datavar1} currently has credit balance of ${datavar2} dollars.")  
99 exten => s,7,Goto(s,4)
100 exten => s,8,PGSQL(Clear ${resultid})
101 exten => s,9,PGSQL(Disconnect ${connid})
102
103 */
104
105 STANDARD_LOCAL_USER;
106
107 LOCAL_USER_DECL;
108
109 extern void pbx_builtin_setvar_helper(struct ast_channel *chan, char *name, char *value); 
110
111 #define AST_PGSQL_ID_DUMMY 0
112 #define AST_PGSQL_ID_CONNID 1
113 #define AST_PGSQL_ID_RESID 2
114 #define AST_PGSQL_ID_FETCHID 3
115
116 struct ast_PGSQL_id {
117         int identifier_type; /* 0=dummy, 1=connid, 2=resultid */
118         int identifier;
119         void *data;
120         AST_LIST_ENTRY(ast_PGSQL_id) entries;
121 } *ast_PGSQL_id;
122
123 AST_LIST_HEAD(PGSQLidshead,ast_PGSQL_id) PGSQLidshead;
124
125 static void *find_identifier(int identifier,int identifier_type) {
126         struct PGSQLidshead *headp;
127         struct ast_PGSQL_id *i;
128         void *res=NULL;
129         int found=0;
130         
131         headp=&PGSQLidshead;
132         
133         if (AST_LIST_LOCK(headp)) {
134                 ast_log(LOG_WARNING,"Unable to lock identifiers list\n");
135         } else {
136                 AST_LIST_TRAVERSE(headp,i,entries) {
137                         if ((i->identifier==identifier) && (i->identifier_type==identifier_type)) {
138                                 found=1;
139                                 res=i->data;
140                                 break;
141                         }
142                 }
143                 if (!found) {
144                         ast_log(LOG_WARNING,"Identifier %d, identifier_type %d not found in identifier list\n",identifier,identifier_type);
145                 }
146                 AST_LIST_UNLOCK(headp);
147         }
148         
149         return(res);
150 }
151
152 static int add_identifier(int identifier_type,void *data) {
153         struct ast_PGSQL_id *i,*j;
154         struct PGSQLidshead *headp;
155         int maxidentifier=0;
156         
157         headp=&PGSQLidshead;
158         i=NULL;
159         j=NULL;
160         
161         if (AST_LIST_LOCK(headp)) {
162                 ast_log(LOG_WARNING,"Unable to lock identifiers list\n");
163                 return(-1);
164         } else {
165                 i=malloc(sizeof(struct ast_PGSQL_id));
166                 AST_LIST_TRAVERSE(headp,j,entries) {
167                         if (j->identifier>maxidentifier) {
168                                 maxidentifier=j->identifier;
169                         }
170                 }
171                 
172                 i->identifier=maxidentifier+1;
173                 i->identifier_type=identifier_type;
174                 i->data=data;
175                 AST_LIST_INSERT_HEAD(headp,i,entries);
176                 AST_LIST_UNLOCK(headp);
177         }
178         return(i->identifier);
179 }
180
181 static int del_identifier(int identifier,int identifier_type) {
182         struct ast_PGSQL_id *i;
183         struct PGSQLidshead *headp;
184         int found=0;
185         
186         headp=&PGSQLidshead;
187         
188         if (AST_LIST_LOCK(headp)) {
189                 ast_log(LOG_WARNING,"Unable to lock identifiers list\n");
190         } else {
191                 AST_LIST_TRAVERSE(headp,i,entries) {
192                         if ((i->identifier==identifier) && 
193                             (i->identifier_type==identifier_type)) {
194                                 AST_LIST_REMOVE(headp,i,ast_PGSQL_id,entries);
195                                 free(i);
196                                 found=1;
197                                 break;
198                         }
199                 }
200                 AST_LIST_UNLOCK(headp);
201         }
202                         
203         if (found==0) {
204                 ast_log(LOG_WARNING,"Could not find identifier %d, identifier_type %d in list to delete\n",identifier,identifier_type);
205                 return(-1);
206         } else {
207                 return(0);
208         }
209 }
210
211 static int aPGSQL_connect(struct ast_channel *chan, void *data) {
212         
213         char *s1;
214         char s[100] = "";
215         char *optionstring;
216         char *var;
217         int l;
218         int res;
219         PGconn *karoto;
220         int id;
221         char *stringp=NULL;
222          
223         
224         res=0;
225         l=strlen(data)+2;
226         s1=malloc(l);
227         strncpy(s1, data, l -1);
228         stringp=s1;
229         strsep(&stringp," "); // eat the first token, we already know it :P 
230         var=strsep(&stringp," ");
231         optionstring=strsep(&stringp,"\n");
232                 
233         karoto = PQconnectdb(optionstring);
234         if (PQstatus(karoto) == CONNECTION_BAD) {
235                 ast_log(LOG_WARNING,"Connection to database using '%s' failed. postgress reports : %s\n", optionstring,
236                                                  PQerrorMessage(karoto));
237                 res=-1;
238         } else {
239                 ast_log(LOG_WARNING,"adding identifier\n");
240                 id=add_identifier(AST_PGSQL_ID_CONNID,karoto);
241                 snprintf(s, sizeof(s), "%d", id);
242                 pbx_builtin_setvar_helper(chan,var,s);
243         }
244         
245         free(s1);
246         return res;
247 }
248
249 static int aPGSQL_query(struct ast_channel *chan, void *data) {
250         
251
252         char *s1,*s2,*s3,*s4;
253         char s[100] = "";
254         char *querystring;
255         char *var;
256         int l;
257         int res,nres;
258         PGconn *karoto;
259         PGresult *PGSQLres;
260         int id,id1;
261         char *stringp=NULL;
262          
263         
264         res=0;
265         l=strlen(data)+2;
266         s1=malloc(l);
267         s2=malloc(l);
268         strncpy(s1, data, l - 1);
269         stringp=s1;
270         strsep(&stringp," "); // eat the first token, we already know it :P 
271         s3=strsep(&stringp," ");
272         while (1) {     // ugly trick to make branches with break;
273                 var=s3;
274                 s4=strsep(&stringp," ");
275                 id=atoi(s4);
276                 querystring=strsep(&stringp,"\n");
277                 if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
278                         ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_query\n",id);
279                         res=-1;
280                         break;
281                 }
282                 PGSQLres=PQexec(karoto,querystring);
283                 if (PGSQLres==NULL) {
284                         ast_log(LOG_WARNING,"aPGSQL_query: Connection Error (connection identifier = %d, error message : %s)\n",id,PQerrorMessage(karoto));
285                         res=-1;
286                         break;
287                 }
288                 if (PQresultStatus(PGSQLres) == PGRES_BAD_RESPONSE ||
289                     PQresultStatus(PGSQLres) == PGRES_NONFATAL_ERROR ||
290                     PQresultStatus(PGSQLres) == PGRES_FATAL_ERROR) {
291                         ast_log(LOG_WARNING,"aPGSQL_query: Query Error (connection identifier : %d, error message : %s)\n",id,PQcmdStatus(PGSQLres));
292                         res=-1;
293                         break;
294                 }
295                 nres=PQnfields(PGSQLres); 
296                 id1=add_identifier(AST_PGSQL_ID_RESID,PGSQLres);
297                 snprintf(s, sizeof(s), "%d", id1);
298                 pbx_builtin_setvar_helper(chan,var,s);
299                 break;
300         }
301         
302         free(s1);
303         free(s2);
304
305         return(res);
306 }
307
308
309 static int aPGSQL_fetch(struct ast_channel *chan, void *data) {
310         
311         char *s1,*s2,*fetchid_var,*s4,*s5,*s6,*s7;
312         char s[100];
313         char *var;
314         int l;
315         int res;
316         PGresult *PGSQLres;
317         int id,id1,i,j,fnd;
318         int *lalares=NULL;
319         int nres;
320         struct ast_var_t *variables;
321         struct varshead *headp;
322         char *stringp=NULL;
323         
324         headp=&chan->varshead;
325         
326         res=0;
327         l=strlen(data)+2;
328         s7=NULL;
329         s1=malloc(l);
330         s2=malloc(l);
331         strncpy(s1, data, l - 1);
332         stringp=s1;
333         strsep(&stringp," "); // eat the first token, we already know it :P 
334         fetchid_var=strsep(&stringp," ");
335         while (1) {     // ugly trick to make branches with break;
336           var=fetchid_var; // fetchid
337                 fnd=0;
338                 
339                 AST_LIST_TRAVERSE(headp,variables,entries) {
340             if (strncasecmp(ast_var_name(variables),fetchid_var,strlen(fetchid_var))==0) {
341                                 s7=ast_var_value(variables);
342                                 fnd=1;
343                                 break;
344                         }
345                 }
346                 
347                 if (fnd==0) { 
348                         s7="0";
349             pbx_builtin_setvar_helper(chan,fetchid_var,s7);
350                 }
351
352                 s4=strsep(&stringp," ");
353                 id=atoi(s4); // resultid
354                 if ((PGSQLres=find_identifier(id,AST_PGSQL_ID_RESID))==NULL) {
355                         ast_log(LOG_WARNING,"Invalid result identifier %d passed in aPGSQL_fetch\n",id);
356                         res=-1;
357                         break;
358                 }
359                 id=atoi(s7); //fetchid
360                 if ((lalares=find_identifier(id,AST_PGSQL_ID_FETCHID))==NULL) {
361             i=0;       // fetching the very first row
362                 } else {
363                         i=*lalares;
364                         free(lalares);
365             del_identifier(id,AST_PGSQL_ID_FETCHID); // will re-add it a bit later
366                 }
367
368           if (i<PQntuples(PGSQLres)) {
369                 nres=PQnfields(PGSQLres); 
370                 ast_log(LOG_WARNING,"ast_PGSQL_fetch : nres = %d i = %d ;\n",nres,i);
371                 for (j=0;j<nres;j++) {
372                         s5=strsep(&stringp," ");
373                         if (s5==NULL) {
374                                 ast_log(LOG_WARNING,"ast_PGSQL_fetch : More tuples (%d) than variables (%d)\n",nres,j);
375                                 break;
376                         }
377                         s6=PQgetvalue(PGSQLres,i,j);
378                         if (s6==NULL) { 
379                                 ast_log(LOG_WARNING,"PWgetvalue(res,%d,%d) returned NULL in ast_PGSQL_fetch\n",i,j);
380                                 break;
381                         }
382                         ast_log(LOG_WARNING,"===setting variable '%s' to '%s'\n",s5,s6);
383                         pbx_builtin_setvar_helper(chan,s5,s6);
384                 }
385                         lalares=malloc(sizeof(int));
386             *lalares = ++i; // advance to the next row
387             id1 = add_identifier(AST_PGSQL_ID_FETCHID,lalares);
388                 } else {
389             ast_log(LOG_WARNING,"ast_PGSQL_fetch : EOF\n");
390             id1 = 0; // no more rows
391                 }
392                 snprintf(s, sizeof(s), "%d", id1);
393           ast_log(LOG_WARNING,"Setting var '%s' to value '%s'\n",fetchid_var,s);
394           pbx_builtin_setvar_helper(chan,fetchid_var,s);
395                 break;
396         }
397         
398         free(s1);
399         free(s2);
400         return(res);
401 }
402
403 static int aPGSQL_reset(struct ast_channel *chan, void *data) {
404         
405         char *s1,*s3;
406         int l;
407         PGconn *karoto;
408         int id;
409         char *stringp=NULL;
410          
411         
412         l=strlen(data)+2;
413         s1=malloc(l);
414         strncpy(s1, data, l - 1);
415         stringp=s1;
416         strsep(&stringp," "); // eat the first token, we already know it :P 
417         s3=strsep(&stringp," ");
418         id=atoi(s3);
419         if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
420                 ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_reset\n",id);
421         } else {
422                 PQreset(karoto);
423         } 
424         free(s1);
425         return(0);
426         
427 }
428
429 static int aPGSQL_clear(struct ast_channel *chan, void *data) {
430         
431         char *s1,*s3;
432         int l;
433         PGresult *karoto;
434         int id;
435         char *stringp=NULL;
436          
437         
438         l=strlen(data)+2;
439         s1=malloc(l);
440         strncpy(s1, data, l - 1);
441         stringp=s1;
442         strsep(&stringp," "); // eat the first token, we already know it :P 
443         s3=strsep(&stringp," ");
444         id=atoi(s3);
445         if ((karoto=find_identifier(id,AST_PGSQL_ID_RESID))==NULL) {
446                 ast_log(LOG_WARNING,"Invalid result identifier %d passed in aPGSQL_clear\n",id);
447         } else {
448                 PQclear(karoto);
449                 del_identifier(id,AST_PGSQL_ID_RESID);
450         }
451         free(s1);
452         return(0);
453         
454 }
455
456            
457            
458         
459 static int aPGSQL_disconnect(struct ast_channel *chan, void *data) {
460         
461         char *s1,*s3;
462         int l;
463         PGconn *karoto;
464         int id;
465         char *stringp=NULL;
466          
467         
468         l=strlen(data)+2;
469         s1=malloc(l);
470         strncpy(s1, data, l - 1);
471         stringp=s1;
472         strsep(&stringp," "); // eat the first token, we already know it :P 
473         s3=strsep(&stringp," ");
474         id=atoi(s3);
475         if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
476                 ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_disconnect\n",id);
477         } else {
478                 PQfinish(karoto);
479                 del_identifier(id,AST_PGSQL_ID_CONNID);
480         } 
481         free(s1);
482         return(0);
483         
484 }
485
486 static int aPGSQL_debug(struct ast_channel *chan, void *data) {
487         ast_log(LOG_WARNING,"Debug : %s\n",(char *)data);
488         return(0);
489 }
490                 
491         
492
493 static int PGSQL_exec(struct ast_channel *chan, void *data)
494 {
495         struct localuser *u;
496         int result;
497
498 #if EXTRA_LOG
499         printf("PRSQL_exec: data=%s\n",(char*)data);
500 #endif
501
502         if (!data) {
503                 ast_log(LOG_WARNING, "APP_PGSQL requires an argument (see manual)\n");
504                 return -1;
505         }
506         LOCAL_USER_ADD(u);
507         result=0;
508         
509         if (strncasecmp("connect",data,strlen("connect"))==0) {
510                 result=(aPGSQL_connect(chan,data));
511         } else  if (strncasecmp("query",data,strlen("query"))==0) {
512                 result=(aPGSQL_query(chan,data));
513         } else  if (strncasecmp("fetch",data,strlen("fetch"))==0) {
514                 result=(aPGSQL_fetch(chan,data));
515         } else  if (strncasecmp("reset",data,strlen("reset"))==0) {
516                 result=(aPGSQL_reset(chan,data));
517         } else  if (strncasecmp("clear",data,strlen("clear"))==0) {
518                 result=(aPGSQL_clear(chan,data));
519         } else  if (strncasecmp("debug",data,strlen("debug"))==0) {
520                 result=(aPGSQL_debug(chan,data));
521         } else  if (strncasecmp("disconnect",data,strlen("disconnect"))==0) {
522                 result=(aPGSQL_disconnect(chan,data));
523         } else {
524                 ast_log(LOG_WARNING, "Unknown APP_PGSQL argument : %s\n",(char *)data);
525                 result=-1;      
526         }
527                 
528         LOCAL_USER_REMOVE(u);                                                                                
529         return result;
530
531 }
532
533 int unload_module(void)
534 {
535         STANDARD_HANGUP_LOCALUSERS;
536         return ast_unregister_application(app);
537 }
538
539 int load_module(void)
540 {
541         struct PGSQLidshead *headp;
542         
543         headp=&PGSQLidshead;
544         
545         AST_LIST_HEAD_INIT(headp);
546         return ast_register_application(app, PGSQL_exec, synopsis, descrip);
547 }
548
549 char *description(void)
550 {
551         return tdesc;
552 }
553
554 int usecount(void)
555 {
556         int res;
557         STANDARD_USECOUNT(res);
558         return res;
559 }
560
561 char *key()
562 {
563         return ASTERISK_GPL_KEY;
564 }