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