Asterisk.org: Developer Documentation (28 Oct 2009)

Wed Oct 28 15:47:49 2009

## Asterisk developer's documentation

# 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 }