Sun Aug 6 15:02:26 2006

Asterisk developer's documentation


Main Page | Modules | Alphabetical List | Data Structures | Directories | File List | Data Fields | Globals | Related Pages

app_sql_postgres.c

Go to the documentation of this file.
00001 /*
00002  * Asterisk -- An open source telephony toolkit.
00003  *
00004  * Copyright (C) 2002, Christos Ricudis
00005  *
00006  * Christos Ricudis <ricudis@itc.auth.gr>
00007  *
00008  * See http://www.asterisk.org for more information about
00009  * the Asterisk project. Please do not directly contact
00010  * any of the maintainers of this project for assistance;
00011  * the project provides a web site, mailing lists and IRC
00012  * channels for your use.
00013  *
00014  * This program is free software, distributed under the terms of
00015  * the GNU General Public License Version 2. See the LICENSE file
00016  * at the top of the source tree.
00017  */
00018 
00019 /*! \file
00020  *
00021  * \brief Connect to PostgreSQL
00022  * 
00023  * \ingroup applications
00024  */
00025 
00026 #include <stdlib.h>
00027 #include <unistd.h>
00028 #include <string.h>
00029 #include <sys/types.h>
00030 #include <stdio.h>
00031 #include <unistd.h>
00032 
00033 #include "asterisk.h"
00034 
00035 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 29732 $")
00036 
00037 #include "asterisk/file.h"
00038 #include "asterisk/logger.h"
00039 #include "asterisk/channel.h"
00040 #include "asterisk/pbx.h"
00041 #include "asterisk/module.h"
00042 #include "asterisk/linkedlists.h"
00043 #include "asterisk/chanvars.h"
00044 #include "asterisk/lock.h"
00045 
00046 #include "libpq-fe.h"
00047 
00048 static char *tdesc = "Simple PostgreSQL Interface";
00049 
00050 static char *app = "PGSQL";
00051 
00052 static char *synopsis = "Do several SQLy things";
00053 
00054 static char *descrip = 
00055 "PGSQL():  Do several SQLy things\n"
00056 "Syntax:\n"
00057 "  PGSQL(Connect var option-string)\n"
00058 "    Connects to a database.  Option string contains standard PostgreSQL\n"
00059 "    parameters like host=, dbname=, user=.  Connection identifer returned\n"
00060 "    in ${var}\n"
00061 "  PGSQL(Query var ${connection_identifier} query-string)\n"
00062 "    Executes standard SQL query contained in query-string using established\n"
00063 "    connection identified by ${connection_identifier}. Reseult of query is\n"
00064 "    is stored in ${var}.\n"
00065 "  PGSQL(Fetch statusvar ${result_identifier} var1 var2 ... varn)\n"
00066 "    Fetches a single row from a result set contained in ${result_identifier}.\n"
00067 "    Assigns returned fields to ${var1} ... ${varn}.  ${statusvar} is set TRUE\n"
00068 "    if additional rows exist in reseult set.\n"
00069 "  PGSQL(Clear ${result_identifier})\n"
00070 "    Frees memory and datastructures associated with result set.\n" 
00071 "  PGSQL(Disconnect ${connection_identifier})\n"
00072 "    Disconnects from named connection to PostgreSQL.\n" ;
00073 
00074 /*
00075 
00076 Syntax of SQL commands : 
00077 
00078    Connect var option-string
00079    
00080    Connects to a database using the option-string and stores the 
00081    connection identifier in ${var}
00082    
00083    
00084    Query var ${connection_identifier} query-string
00085    
00086    Submits query-string to database backend and stores the result
00087    identifier in ${var}
00088    
00089    
00090    Fetch statusvar ${result_identifier} var1 var2 var3 ... varn
00091    
00092    Fetches a row from the query and stores end-of-table status in 
00093    ${statusvar} and columns in ${var1}..${varn}
00094    
00095    
00096    Clear ${result_identifier}
00097 
00098    Clears data structures associated with ${result_identifier}
00099    
00100    
00101    Disconnect ${connection_identifier}
00102    
00103    Disconnects from named connection
00104    
00105    
00106 EXAMPLES OF USE : 
00107 
00108 exten => s,2,PGSQL(Connect connid host=localhost user=asterisk dbname=credit)
00109 exten => s,3,PGSQL(Query resultid ${connid} SELECT username,credit FROM credit WHERE callerid=${CALLERIDNUM})
00110 exten => s,4,PGSQL(Fetch fetchid ${resultid} datavar1 datavar2)
00111 exten => s,5,GotoIf(${fetchid}?6:8)
00112 exten => s,6,Festival("User ${datavar1} currently has credit balance of ${datavar2} dollars.")  
00113 exten => s,7,Goto(s,4)
00114 exten => s,8,PGSQL(Clear ${resultid})
00115 exten => s,9,PGSQL(Disconnect ${connid})
00116 
00117 */
00118 
00119 STANDARD_LOCAL_USER;
00120 
00121 LOCAL_USER_DECL;
00122 
00123 #define AST_PGSQL_ID_DUMMY 0
00124 #define AST_PGSQL_ID_CONNID 1
00125 #define AST_PGSQL_ID_RESID 2
00126 #define AST_PGSQL_ID_FETCHID 3
00127 
00128 struct ast_PGSQL_id {
00129    int identifier_type; /* 0=dummy, 1=connid, 2=resultid */
00130    int identifier;
00131    void *data;
00132    AST_LIST_ENTRY(ast_PGSQL_id) entries;
00133 } *ast_PGSQL_id;
00134 
00135 static AST_LIST_HEAD_STATIC(PGSQLidshead,ast_PGSQL_id);
00136 
00137 static void *find_identifier(int identifier,int identifier_type) {
00138    struct PGSQLidshead *headp;
00139    struct ast_PGSQL_id *i;
00140    void *res=NULL;
00141    int found=0;
00142    
00143    headp=&PGSQLidshead;
00144    
00145    if (AST_LIST_LOCK(headp)) {
00146       ast_log(LOG_WARNING,"Unable to lock identifiers list\n");
00147    } else {
00148       AST_LIST_TRAVERSE(headp,i,entries) {
00149          if ((i->identifier==identifier) && (i->identifier_type==identifier_type)) {
00150             found=1;
00151             res=i->data;
00152             break;
00153          }
00154       }
00155       if (!found) {
00156          ast_log(LOG_WARNING,"Identifier %d, identifier_type %d not found in identifier list\n",identifier,identifier_type);
00157       }
00158       AST_LIST_UNLOCK(headp);
00159    }
00160    
00161    return(res);
00162 }
00163 
00164 static int add_identifier(int identifier_type,void *data) {
00165    struct ast_PGSQL_id *i,*j;
00166    struct PGSQLidshead *headp;
00167    int maxidentifier=0;
00168    
00169    headp=&PGSQLidshead;
00170    i=NULL;
00171    j=NULL;
00172    
00173    if (AST_LIST_LOCK(headp)) {
00174       ast_log(LOG_WARNING,"Unable to lock identifiers list\n");
00175       return(-1);
00176    } else {
00177       i=malloc(sizeof(struct ast_PGSQL_id));
00178       AST_LIST_TRAVERSE(headp,j,entries) {
00179          if (j->identifier>maxidentifier) {
00180             maxidentifier=j->identifier;
00181          }
00182       }
00183       
00184       i->identifier=maxidentifier+1;
00185       i->identifier_type=identifier_type;
00186       i->data=data;
00187       AST_LIST_INSERT_HEAD(headp,i,entries);
00188       AST_LIST_UNLOCK(headp);
00189    }
00190    return(i->identifier);
00191 }
00192 
00193 static int del_identifier(int identifier,int identifier_type) {
00194    struct ast_PGSQL_id *i;
00195    struct PGSQLidshead *headp;
00196    int found=0;
00197    
00198         headp=&PGSQLidshead;
00199         
00200         if (AST_LIST_LOCK(headp)) {
00201       ast_log(LOG_WARNING,"Unable to lock identifiers list\n");
00202    } else {
00203       AST_LIST_TRAVERSE(headp,i,entries) {
00204          if ((i->identifier==identifier) && 
00205              (i->identifier_type==identifier_type)) {
00206             AST_LIST_REMOVE(headp,i,entries);
00207             free(i);
00208             found=1;
00209             break;
00210          }
00211       }
00212       AST_LIST_UNLOCK(headp);
00213    }
00214                    
00215    if (found==0) {
00216       ast_log(LOG_WARNING,"Could not find identifier %d, identifier_type %d in list to delete\n",identifier,identifier_type);
00217       return(-1);
00218    } else {
00219       return(0);
00220    }
00221 }
00222 
00223 static int aPGSQL_connect(struct ast_channel *chan, void *data) {
00224    
00225    char *s1;
00226    char s[100] = "";
00227    char *optionstring;
00228    char *var;
00229    int l;
00230    int res;
00231    PGconn *karoto;
00232    int id;
00233    char *stringp=NULL;
00234     
00235    
00236    res=0;
00237    l=strlen(data)+2;
00238    s1=malloc(l);
00239    strncpy(s1, data, l -1);
00240    stringp=s1;
00241    strsep(&stringp," "); /* eat the first token, we already know it :P  */
00242    var=strsep(&stringp," ");
00243    optionstring=strsep(&stringp,"\n");
00244       
00245          karoto = PQconnectdb(optionstring);
00246         if (PQstatus(karoto) == CONNECTION_BAD) {
00247          ast_log(LOG_WARNING,"Connection to database using '%s' failed. postgress reports : %s\n", optionstring,
00248                                                  PQerrorMessage(karoto));
00249          res=-1;
00250         } else {
00251          ast_log(LOG_WARNING,"adding identifier\n");
00252       id=add_identifier(AST_PGSQL_ID_CONNID,karoto);
00253       snprintf(s, sizeof(s), "%d", id);
00254       pbx_builtin_setvar_helper(chan,var,s);
00255    }
00256    
00257    free(s1);
00258    return res;
00259 }
00260 
00261 static int aPGSQL_query(struct ast_channel *chan, void *data) {
00262    
00263 
00264    char *s1,*s2,*s3,*s4;
00265    char s[100] = "";
00266    char *querystring;
00267    char *var;
00268    int l;
00269    int res,nres;
00270    PGconn *karoto;
00271    PGresult *PGSQLres;
00272    int id,id1;
00273    char *stringp=NULL;
00274     
00275    
00276    res=0;
00277    l=strlen(data)+2;
00278    s1=malloc(l);
00279    s2=malloc(l);
00280    strncpy(s1, data, l - 1);
00281    stringp=s1;
00282    strsep(&stringp," "); /* eat the first token, we already know it :P  */
00283    s3=strsep(&stringp," ");
00284    while (1) { /* ugly trick to make branches with break; */
00285       var=s3;
00286       s4=strsep(&stringp," ");
00287       id=atoi(s4);
00288       querystring=strsep(&stringp,"\n");
00289       if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
00290          ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_query\n",id);
00291          res=-1;
00292          break;
00293       }
00294       PGSQLres=PQexec(karoto,querystring);
00295       if (PGSQLres==NULL) {
00296          ast_log(LOG_WARNING,"aPGSQL_query: Connection Error (connection identifier = %d, error message : %s)\n",id,PQerrorMessage(karoto));
00297          res=-1;
00298          break;
00299       }
00300       if (PQresultStatus(PGSQLres) == PGRES_BAD_RESPONSE ||
00301           PQresultStatus(PGSQLres) == PGRES_NONFATAL_ERROR ||
00302           PQresultStatus(PGSQLres) == PGRES_FATAL_ERROR) {
00303             ast_log(LOG_WARNING,"aPGSQL_query: Query Error (connection identifier : %d, error message : %s)\n",id,PQcmdStatus(PGSQLres));
00304             res=-1;
00305             break;
00306       }
00307       nres=PQnfields(PGSQLres); 
00308       id1=add_identifier(AST_PGSQL_ID_RESID,PGSQLres);
00309       snprintf(s, sizeof(s), "%d", id1);
00310       pbx_builtin_setvar_helper(chan,var,s);
00311       break;
00312    }
00313    
00314    free(s1);
00315    free(s2);
00316 
00317    return(res);
00318 }
00319 
00320 
00321 static int aPGSQL_fetch(struct ast_channel *chan, void *data) {
00322    
00323    char *s1,*s2,*fetchid_var,*s4,*s5,*s6,*s7;
00324    char s[100];
00325    char *var;
00326    int l;
00327    int res;
00328    PGresult *PGSQLres;
00329    int id,id1,i,j,fnd;
00330    int *lalares=NULL;
00331    int nres;
00332         struct ast_var_t *variables;
00333         struct varshead *headp;
00334    char *stringp=NULL;
00335         
00336         headp=&chan->varshead;
00337    
00338    res=0;
00339    l=strlen(data)+2;
00340    s7=NULL;
00341    s1=malloc(l);
00342    s2=malloc(l);
00343    strncpy(s1, data, l - 1);
00344    stringp=s1;
00345    strsep(&stringp," "); /* eat the first token, we already know it :P  */
00346    fetchid_var=strsep(&stringp," ");
00347    while (1) { /* ugly trick to make branches with break; */
00348      var=fetchid_var; /* fetchid */
00349       fnd=0;
00350       
00351       AST_LIST_TRAVERSE(headp,variables,entries) {
00352        if (strncasecmp(ast_var_name(variables),fetchid_var,strlen(fetchid_var))==0) {
00353                            s7=ast_var_value(variables);
00354                            fnd=1;
00355                                 break;
00356          }
00357       }
00358       
00359       if (fnd==0) { 
00360          s7="0";
00361        pbx_builtin_setvar_helper(chan,fetchid_var,s7);
00362       }
00363 
00364       s4=strsep(&stringp," ");
00365       id=atoi(s4); /* resultid */
00366       if ((PGSQLres=find_identifier(id,AST_PGSQL_ID_RESID))==NULL) {
00367          ast_log(LOG_WARNING,"Invalid result identifier %d passed in aPGSQL_fetch\n",id);
00368          res=-1;
00369          break;
00370       }
00371       id=atoi(s7); /*fetchid */
00372       if ((lalares=find_identifier(id,AST_PGSQL_ID_FETCHID))==NULL) {
00373        i=0;       /* fetching the very first row */
00374       } else {
00375          i=*lalares;
00376          free(lalares);
00377        del_identifier(id,AST_PGSQL_ID_FETCHID); /* will re-add it a bit later */
00378       }
00379 
00380      if (i<PQntuples(PGSQLres)) {
00381       nres=PQnfields(PGSQLres); 
00382       ast_log(LOG_WARNING,"ast_PGSQL_fetch : nres = %d i = %d ;\n",nres,i);
00383       for (j=0;j<nres;j++) {
00384          s5=strsep(&stringp," ");
00385          if (s5==NULL) {
00386             ast_log(LOG_WARNING,"ast_PGSQL_fetch : More tuples (%d) than variables (%d)\n",nres,j);
00387             break;
00388          }
00389          s6=PQgetvalue(PGSQLres,i,j);
00390          if (s6==NULL) { 
00391             ast_log(LOG_WARNING,"PWgetvalue(res,%d,%d) returned NULL in ast_PGSQL_fetch\n",i,j);
00392             break;
00393          }
00394          ast_log(LOG_WARNING,"===setting variable '%s' to '%s'\n",s5,s6);
00395          pbx_builtin_setvar_helper(chan,s5,s6);
00396       }
00397          lalares=malloc(sizeof(int));
00398        *lalares = ++i; /* advance to the next row */
00399        id1 = add_identifier(AST_PGSQL_ID_FETCHID,lalares);
00400       } else {
00401        ast_log(LOG_WARNING,"ast_PGSQL_fetch : EOF\n");
00402        id1 = 0; /* no more rows */
00403       }
00404       snprintf(s, sizeof(s), "%d", id1);
00405      ast_log(LOG_WARNING,"Setting var '%s' to value '%s'\n",fetchid_var,s);
00406      pbx_builtin_setvar_helper(chan,fetchid_var,s);
00407       break;
00408    }
00409    
00410    free(s1);
00411    free(s2);
00412    return(res);
00413 }
00414 
00415 static int aPGSQL_reset(struct ast_channel *chan, void *data) {
00416    
00417    char *s1,*s3;
00418    int l;
00419    PGconn *karoto;
00420    int id;
00421    char *stringp=NULL;
00422     
00423    
00424    l=strlen(data)+2;
00425    s1=malloc(l);
00426    strncpy(s1, data, l - 1);
00427    stringp=s1;
00428    strsep(&stringp," "); /* eat the first token, we already know it :P  */
00429    s3=strsep(&stringp," ");
00430    id=atoi(s3);
00431    if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
00432       ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_reset\n",id);
00433    } else {
00434       PQreset(karoto);
00435    } 
00436    free(s1);
00437    return(0);
00438    
00439 }
00440 
00441 static int aPGSQL_clear(struct ast_channel *chan, void *data) {
00442    
00443    char *s1,*s3;
00444    int l;
00445    PGresult *karoto;
00446    int id;
00447    char *stringp=NULL;
00448     
00449    
00450    l=strlen(data)+2;
00451    s1=malloc(l);
00452    strncpy(s1, data, l - 1);
00453    stringp=s1;
00454    strsep(&stringp," "); /* eat the first token, we already know it :P  */
00455    s3=strsep(&stringp," ");
00456    id=atoi(s3);
00457    if ((karoto=find_identifier(id,AST_PGSQL_ID_RESID))==NULL) {
00458       ast_log(LOG_WARNING,"Invalid result identifier %d passed in aPGSQL_clear\n",id);
00459    } else {
00460       PQclear(karoto);
00461       del_identifier(id,AST_PGSQL_ID_RESID);
00462    }
00463    free(s1);
00464    return(0);
00465    
00466 }
00467 
00468       
00469       
00470    
00471 static int aPGSQL_disconnect(struct ast_channel *chan, void *data) {
00472    
00473    char *s1,*s3;
00474    int l;
00475    PGconn *karoto;
00476    int id;
00477    char *stringp=NULL;
00478     
00479    
00480    l=strlen(data)+2;
00481    s1=malloc(l);
00482    strncpy(s1, data, l - 1);
00483    stringp=s1;
00484    strsep(&stringp," "); /* eat the first token, we already know it :P  */
00485    s3=strsep(&stringp," ");
00486    id=atoi(s3);
00487    if ((karoto=find_identifier(id,AST_PGSQL_ID_CONNID))==NULL) {
00488       ast_log(LOG_WARNING,"Invalid connection identifier %d passed in aPGSQL_disconnect\n",id);
00489    } else {
00490       PQfinish(karoto);
00491       del_identifier(id,AST_PGSQL_ID_CONNID);
00492    } 
00493    free(s1);
00494    return(0);
00495    
00496 }
00497 
00498 static int aPGSQL_debug(struct ast_channel *chan, void *data) {
00499    ast_log(LOG_WARNING,"Debug : %s\n",(char *)data);
00500    return(0);
00501 }
00502 
00503 static int PGSQL_exec(struct ast_channel *chan, void *data)
00504 {
00505    struct localuser *u;
00506    int result;
00507 
00508    if (ast_strlen_zero(data)) {
00509       ast_log(LOG_WARNING, "APP_PGSQL requires an argument (see manual)\n");
00510       return -1;
00511    }
00512    
00513    LOCAL_USER_ADD(u);
00514 
00515    result=0;
00516    
00517    if (strncasecmp("connect",data,strlen("connect"))==0) {
00518       result=(aPGSQL_connect(chan,data));
00519    } else   if (strncasecmp("query",data,strlen("query"))==0) {
00520       result=(aPGSQL_query(chan,data));
00521    } else   if (strncasecmp("fetch",data,strlen("fetch"))==0) {
00522       result=(aPGSQL_fetch(chan,data));
00523    } else   if (strncasecmp("reset",data,strlen("reset"))==0) {
00524       result=(aPGSQL_reset(chan,data));
00525    } else   if (strncasecmp("clear",data,strlen("clear"))==0) {
00526       result=(aPGSQL_clear(chan,data));
00527    } else  if (strncasecmp("debug",data,strlen("debug"))==0) {
00528       result=(aPGSQL_debug(chan,data));
00529    } else   if (strncasecmp("disconnect",data,strlen("disconnect"))==0) {
00530       result=(aPGSQL_disconnect(chan,data));
00531    } else {
00532       ast_log(LOG_WARNING, "Unknown APP_PGSQL argument : %s\n",(char *)data);
00533       result=-1;  
00534    }
00535       
00536    LOCAL_USER_REMOVE(u);                                                                                
00537    
00538    return result;
00539 }
00540 
00541 int unload_module(void)
00542 {
00543    int res;
00544 
00545    res = ast_unregister_application(app);
00546 
00547    STANDARD_HANGUP_LOCALUSERS;
00548    
00549    return res;
00550 }
00551 
00552 int load_module(void)
00553 {
00554    return ast_register_application(app, PGSQL_exec, synopsis, descrip);
00555 }
00556 
00557 char *description(void)
00558 {
00559    return tdesc;
00560 }
00561 
00562 int usecount(void)
00563 {
00564    int res;
00565    STANDARD_USECOUNT(res);
00566    return res;
00567 }
00568 
00569 char *key()
00570 {
00571    return ASTERISK_GPL_KEY;
00572 }

Generated on Sun Aug 6 15:02:26 2006 for Asterisk - the Open Source PBX by  doxygen 1.4.2