Use find_user for existsmailbox
[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
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         char *s1,*s2,*s3,*s4,*s5;
254         char s[100];
255         char *querystring;
256         char *var;
257         int l;
258         int res,nres;
259         PGconn *karoto;
260         PGresult *PGSQLres;
261         int id,id1;
262         char *stringp=NULL;
263          
264         
265         res=0;
266         l=strlen(data)+2;
267         s1=malloc(l);
268         s2=malloc(l);
269         strcpy(s1,data);
270         stringp=s1;
271         strsep(&stringp," "); // eat the first token, we already know it :P 
272         s3=strsep(&stringp," ");
273         while (1) {     // ugly trick to make branches with break;
274                 var=s3;
275                 s4=strsep(&stringp," ");
276                 id=atoi(s4);
277                 querystring=strsep(&stringp,"\n");
278                 if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
279                         ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_query\n",id);
280                         res=-1;
281                         break;
282                 }
283                 PGSQLres=PQexec(karoto,querystring);
284                 if (PGSQLres==NULL) {
285                         ast_log(LOG_WARNING,"aPGSQL_query: Connection Error (connection identifier = %d, error message : %s)\n",id,PQerrorMessage(karoto));
286                         res=-1;
287                         break;
288                 }
289                 if (PQresultStatus(PGSQLres) == PGRES_BAD_RESPONSE ||
290                     PQresultStatus(PGSQLres) == PGRES_NONFATAL_ERROR ||
291                     PQresultStatus(PGSQLres) == PGRES_FATAL_ERROR) {
292                         ast_log(LOG_WARNING,"aPGSQL_query: Query Error (connection identifier : %d, error message : %s)\n",id,PQcmdStatus(PGSQLres));
293                         res=-1;
294                         break;
295                 }
296                 nres=PQnfields(PGSQLres); 
297                 id1=add_identifier(AST_PGSQL_ID_RESID,PGSQLres);
298                 s5=&s[0];
299                 sprintf(s5,"%d",id1);
300                 pbx_builtin_setvar_helper(chan,var,s);
301                 break;
302         }
303         
304         free(s1);
305         free(s2);
306         return(res);
307 }
308
309
310 static int aPGSQL_fetch(struct ast_channel *chan, void *data) {
311         
312         char *s1,*s2,*s3,*s4,*s5,*s6,*s7;
313         char s[100];
314         char *var;
315         int l;
316         int res;
317         PGresult *PGSQLres;
318         int id,id1,i,j,fnd;
319         int *lalares=NULL;
320         int nres;
321         struct ast_var_t *variables;
322         struct varshead *headp;
323         char *stringp=NULL;
324         
325         headp=&chan->varshead;
326         
327         res=0;
328         l=strlen(data)+2;
329         s7=NULL;
330         s1=malloc(l);
331         s2=malloc(l);
332         strcpy(s1,data);
333         stringp=s1;
334         strsep(&stringp," "); // eat the first token, we already know it :P 
335         s3=strsep(&stringp," ");
336         while (1) {     // ugly trick to make branches with break;
337                 var=s3; // fetchid
338                 fnd=0;
339                 
340                 AST_LIST_TRAVERSE(headp,variables,entries) {
341                         if (strncasecmp(ast_var_name(variables),s3,strlen(s3))==0) {
342                                 s7=ast_var_value(variables);
343                                 fnd=1;
344                                 break;
345                         }
346                 }
347                 
348                 if (fnd==0) { 
349                         s7="0";
350                         pbx_builtin_setvar_helper(chan,s3,s7);
351                 }
352
353                 s4=strsep(&stringp," ");
354                 id=atoi(s4); // resultid
355                 if ((PGSQLres=find_identifier(id,AST_PGSQL_ID_RESID))==NULL) {
356                         ast_log(LOG_WARNING,"Invalid result identifier %d passed in aPGSQL_fetch\n",id);
357                         res=-1;
358                         break;
359                 }
360                 id=atoi(s7); //fetchid
361                 if ((lalares=find_identifier(id,AST_PGSQL_ID_FETCHID))==NULL) {
362                         i=0;
363                 } else {
364                         i=*lalares;
365                         free(lalares);
366                         del_identifier(id,AST_PGSQL_ID_FETCHID);
367                 }
368                 nres=PQnfields(PGSQLres); 
369                 ast_log(LOG_WARNING,"ast_PGSQL_fetch : nres = %d i = %d ;\n",nres,i);
370                 for (j=0;j<nres;j++) {
371                         s5=strsep(&stringp," ");
372                         if (s5==NULL) {
373                                 ast_log(LOG_WARNING,"ast_PGSQL_fetch : More tuples (%d) than variables (%d)\n",nres,j);
374                                 break;
375                         }
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                 i++;
386                 if (i<PQntuples(PGSQLres)) {
387                         lalares=malloc(sizeof(int));
388                         *lalares=i;
389                         id1=add_identifier(AST_PGSQL_ID_FETCHID,lalares);
390                 } else {
391                         id1=0;
392                 }
393                 s5=&s[0];
394                 sprintf(s5,"%d",id1);
395                 ast_log(LOG_WARNING,"Setting var '%s' to value '%s'\n",s3,s);
396                 pbx_builtin_setvar_helper(chan,s3,s);
397                 break;
398         }
399         
400         free(s1);
401         free(s2);
402         return(res);
403 }
404
405 static int aPGSQL_reset(struct ast_channel *chan, void *data) {
406         
407         char *s1,*s3;
408         int l;
409         PGconn *karoto;
410         int id;
411         char *stringp=NULL;
412          
413         
414         l=strlen(data)+2;
415         s1=malloc(l);
416         strcpy(s1,data);
417         stringp=s1;
418         strsep(&stringp," "); // eat the first token, we already know it :P 
419         s3=strsep(&stringp," ");
420         id=atoi(s3);
421         if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
422                 ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_reset\n",id);
423         } else {
424                 PQreset(karoto);
425         } 
426         free(s1);
427         return(0);
428         
429 }
430
431 static int aPGSQL_clear(struct ast_channel *chan, void *data) {
432         
433         char *s1,*s3;
434         int l;
435         PGresult *karoto;
436         int id;
437         char *stringp=NULL;
438          
439         
440         l=strlen(data)+2;
441         s1=malloc(l);
442         strcpy(s1,data);
443         stringp=s1;
444         strsep(&stringp," "); // eat the first token, we already know it :P 
445         s3=strsep(&stringp," ");
446         id=atoi(s3);
447         if ((karoto=find_identifier(id,AST_PGSQL_ID_RESID))==NULL) {
448                 ast_log(LOG_WARNING,"Invalid result identifier %d passed in aPGSQL_clear\n",id);
449         } else {
450                 PQclear(karoto);
451                 del_identifier(id,AST_PGSQL_ID_RESID);
452         }
453         free(s1);
454         return(0);
455         
456 }
457
458            
459            
460         
461 static int aPGSQL_disconnect(struct ast_channel *chan, void *data) {
462         
463         char *s1,*s3;
464         int l;
465         PGconn *karoto;
466         int id;
467         char *stringp=NULL;
468          
469         
470         l=strlen(data)+2;
471         s1=malloc(l);
472         strcpy(s1,data);
473         stringp=s1;
474         strsep(&stringp," "); // eat the first token, we already know it :P 
475         s3=strsep(&stringp," ");
476         id=atoi(s3);
477         if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
478                 ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_disconnect\n",id);
479         } else {
480                 PQfinish(karoto);
481                 del_identifier(id,AST_PGSQL_ID_CONNID);
482         } 
483         free(s1);
484         return(0);
485         
486 }
487
488 static int aPGSQL_debug(struct ast_channel *chan, void *data) {
489         ast_log(LOG_WARNING,"Debug : %s\n",(char *)data);
490         return(0);
491 }
492                 
493         
494
495 static int PGSQL_exec(struct ast_channel *chan, void *data)
496 {
497         struct localuser *u;
498         int result;
499
500         if (!data) {
501                 ast_log(LOG_WARNING, "APP_PGSQL requires an argument (see manual)\n");
502                 return -1;
503         }
504         LOCAL_USER_ADD(u);
505         result=0;
506         
507         if (strncasecmp("connect",data,strlen("connect"))==0) {
508                 result=(aPGSQL_connect(chan,data));
509         } else  if (strncasecmp("query",data,strlen("query"))==0) {
510                 result=(aPGSQL_query(chan,data));
511         } else  if (strncasecmp("fetch",data,strlen("fetch"))==0) {
512                 result=(aPGSQL_fetch(chan,data));
513         } else  if (strncasecmp("reset",data,strlen("reset"))==0) {
514                 result=(aPGSQL_reset(chan,data));
515         } else  if (strncasecmp("clear",data,strlen("clear"))==0) {
516                 result=(aPGSQL_clear(chan,data));
517         } else  if (strncasecmp("debug",data,strlen("debug"))==0) {
518                 result=(aPGSQL_debug(chan,data));
519         } else  if (strncasecmp("disconnect",data,strlen("disconnect"))==0) {
520                 result=(aPGSQL_disconnect(chan,data));
521         } else {
522                 ast_log(LOG_WARNING, "Unknown APP_PGSQL argument : %s\n",(char *)data);
523                 result=-1;      
524         }
525                 
526         LOCAL_USER_REMOVE(u);                                                                                
527         return result;
528
529 }
530
531 int unload_module(void)
532 {
533         STANDARD_HANGUP_LOCALUSERS;
534         return ast_unregister_application(app);
535 }
536
537 int load_module(void)
538 {
539         struct PGSQLidshead *headp;
540         
541         headp=&PGSQLidshead;
542         
543         AST_LIST_HEAD_INIT(headp);
544         return ast_register_application(app, PGSQL_exec, synopsis, descrip);
545 }
546
547 char *description(void)
548 {
549         return tdesc;
550 }
551
552 int usecount(void)
553 {
554         int res;
555         STANDARD_USECOUNT(res);
556         return res;
557 }
558
559 char *key()
560 {
561         return ASTERISK_GPL_KEY;
562 }