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