00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
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
00077
00078
00079
00080
00081
00082
00083
00084
00085
00086
00087
00088
00089
00090
00091
00092
00093
00094
00095
00096
00097
00098
00099
00100
00101
00102
00103
00104
00105
00106
00107
00108
00109
00110
00111
00112
00113
00114
00115
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;
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," ");
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," ");
00283 s3=strsep(&stringp," ");
00284 while (1) {
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," ");
00346 fetchid_var=strsep(&stringp," ");
00347 while (1) {
00348 var=fetchid_var;
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);
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);
00372 if ((lalares=find_identifier(id,AST_PGSQL_ID_FETCHID))==NULL) {
00373 i=0;
00374 } else {
00375 i=*lalares;
00376 free(lalares);
00377 del_identifier(id,AST_PGSQL_ID_FETCHID);
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;
00399 id1 = add_identifier(AST_PGSQL_ID_FETCHID,lalares);
00400 } else {
00401 ast_log(LOG_WARNING,"ast_PGSQL_fetch : EOF\n");
00402 id1 = 0;
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," ");
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," ");
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," ");
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 }