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 }