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