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