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 }