/*
* Adapter function to PQunescapeBytea()
* This function should properly unescape the charactor representation
* of the binary data returned from PostgreSQL and return raw binary data.
*/
SEXP
RS_PostgreSQL_unescape_bytea(SEXP escapedstring)
{
SEXP output;
size_t raw_length;
const char* strbuffer;
unsigned char* rawbuffer;
strbuffer = CHAR(STRING_ELT(escapedstring, 0));
if(!strbuffer) RS_DBI_errorMessage("strbuffer is NULL!", RS_DBI_ERROR);
if (strbuffer[0] == '\\' && strbuffer[1] == 'x'){
/* the new hex fomat */
int i;
size_t enc_length = LENGTH(STRING_ELT(escapedstring, 0));
raw_length = enc_length / 2 - 1;
output = allocVector(RAWSXP, raw_length);
rawbuffer = RAW(output);
for(i = 0; i < raw_length; i++){
rawbuffer[i] = (hex2n(strbuffer[2+ i*2]) << 4) + hex2n(strbuffer[2+i*2+1]);
}
return output;
}else{ /* the old escape format */
rawbuffer = PQunescapeBytea((const unsigned char*) strbuffer, &raw_length);
/* explicit cast to suppress warning on signedness by clang */
if(!rawbuffer) RS_DBI_errorMessage("PQunescapeByea Failed", RS_DBI_ERROR);
output = allocVector(RAWSXP, raw_length);
memcpy(RAW(output), rawbuffer, raw_length);
free(rawbuffer);
return output;
}
}
s_object *
RS_PostgreSQL_getResult(Con_Handle * conHandle)
{
S_EVALUATOR RS_DBI_connection * con;
S_EVALUATOR RS_DBI_resultSet * result;
PGconn *my_connection;
Res_Handle *rsHandle;
Sint res_id;
PGresult *my_result;
con = RS_DBI_getConnection(conHandle);
my_connection = (PGconn *) con->drvConnection;
if (con->num_res > 0) {
res_id = (Sint) con->resultSetIds[0];
rsHandle = RS_DBI_asResHandle(MGR_ID(conHandle), CON_ID(conHandle), res_id);
result = RS_DBI_getResultSet(rsHandle);
if (result->completed == 0) {
RS_DBI_errorMessage("connection with pending rows, close resultSet before continuing", RS_DBI_ERROR);
}
else {
RS_PostgreSQL_closeResultSet(rsHandle);
}
}
my_result = PQgetResult(my_connection);
if(my_result == NULL)
return S_NULL_ENTRY;
if (strcmp(PQresultErrorMessage(my_result), "") != 0) {
char *errResultMsg;
const char *omsg;
size_t len;
omsg = PQerrorMessage(my_connection);
len = strlen(omsg);
errResultMsg = malloc(len + 80); /* 80 should be larger than the length of "could not ..."*/
snprintf(errResultMsg, len + 80, "could not Retrieve the result : %s", omsg);
RS_DBI_errorMessage(errResultMsg, RS_DBI_ERROR);
free(errResultMsg);
/* Frees the storage associated with a PGresult.
* void PQclear(PGresult *res); */
PQclear(my_result);
}
/* we now create the wrapper and copy values */
PROTECT(rsHandle = RS_DBI_allocResultSet(conHandle));
result = RS_DBI_getResultSet(rsHandle);
result->drvResultSet = (void *) my_result;
result->rowCount = (Sint) 0;
result->isSelect = 0;
result->rowsAffected = 0;
result->completed = 1;
UNPROTECT(1);
return rsHandle;
}
s_object *
RS_PostgreSQL_closeConnection(Con_Handle * conHandle)
{
S_EVALUATOR RS_DBI_connection * con;
PGconn *my_connection;
s_object *status;
con = RS_DBI_getConnection(conHandle);
if (con->num_res > 0) {
RS_DBI_errorMessage("close the pending result sets before closing this connection", RS_DBI_ERROR);
}
/* make sure we first free the conParams and postgresql connection from
* the RS-RBI connection object.
*/
if (con->conParams) {
RS_PostgreSQL_freeConParams(con->conParams);
con->conParams = (RS_PostgreSQL_conParams *) NULL;
}
my_connection = (PGconn *) con->drvConnection;
PQfinish(my_connection);
con->drvConnection = (void *) NULL;
RS_DBI_freeConnection(conHandle);
MEM_PROTECT(status = NEW_LOGICAL((Sint) 1));
LGL_EL(status, 0) = TRUE;
MEM_UNPROTECT(1);
return status;
}
s_object *
RS_PostgreSQL_CopyIn(Con_Handle * conHandle, s_object * filename)
{
S_EVALUATOR RS_DBI_connection * con;
PGconn *my_connection;
char *dyn_filename;
char copybuf[COPY_IN_BUFSIZE];
FILE* filehandle;
size_t len;
int pqretcode;
con = RS_DBI_getConnection(conHandle);
my_connection = (PGconn *) con->drvConnection;
dyn_filename = RS_DBI_copyString(CHR_EL(filename, 0));
filehandle=fopen(dyn_filename, "r");
if(filehandle == NULL){
char errmsg[1024];
snprintf(errmsg, 1024, "could not open file: %s", dyn_filename);
RS_DBI_errorMessage(dyn_filename, RS_DBI_ERROR);
return S_NULL_ENTRY;
}
while((len = fread(copybuf,1,COPY_IN_BUFSIZE, filehandle))){
pqretcode = PQputCopyData(my_connection, copybuf, len);
chkpqcopydataerr(my_connection, pqretcode);
}
PQputCopyEnd(my_connection, NULL);
fclose(filehandle);
free(dyn_filename);
return S_NULL_ENTRY;
}
static inline void
chkpqcopydataerr(PGconn *my_connection, int pqretcode)
{
if(pqretcode == -1){
char * pqerrmsg = PQerrorMessage(my_connection);
char * rserrmsg;
char * format = "PQputCopyData failed: %s";
size_t len = strlen(pqerrmsg) + strlen(format) + 1;
rserrmsg = malloc(len);
if(rserrmsg){
snprintf(rserrmsg, len, format, pqerrmsg);
RS_DBI_errorMessage(rserrmsg, RS_DBI_ERROR);
}else{
RS_DBI_errorMessage("malloc failed while reporting error in PQputCopyData", RS_DBI_ERROR);
}
}
}
RS_PostgreSQL_conParams *
RS_postgresql_allocConParams(void)
{
RS_PostgreSQL_conParams *conParams;
conParams = (RS_PostgreSQL_conParams *)
malloc(sizeof(RS_PostgreSQL_conParams));
if (!conParams) {
RS_DBI_errorMessage("could not malloc space for connection params", RS_DBI_ERROR);
}
return conParams;
}
s_object *
RS_PostgreSQL_closeManager(Mgr_Handle * mgrHandle)
{
S_EVALUATOR RS_DBI_manager * mgr;
s_object *status;
mgr = RS_DBI_getManager(mgrHandle);
if (mgr->num_con) {
RS_DBI_errorMessage("There are opened connections -- close them first", RS_DBI_ERROR);
}
RS_DBI_freeManager(mgrHandle);
MEM_PROTECT(status = NEW_LOGICAL((Sint) 1));
LGL_EL(status, 0) = TRUE;
MEM_UNPROTECT(1);
return status;
}
RS_DBI_fields *
RS_PostgreSQL_createDataMappings(Res_Handle * rsHandle)
{
PGresult *my_result;
RS_DBI_connection *con;
RS_DBI_resultSet *result;
RS_DBI_fields *flds;
int j, num_fields, internal_type;
char errMsg[128];
result = RS_DBI_getResultSet(rsHandle);
my_result = (PGresult *) result->drvResultSet;
con = RS_DBI_getConnection(rsHandle);
num_fields = PQnfields(my_result);
flds = RS_DBI_allocFields(num_fields); /* this returns malloced data (not from R) */
char buff[1000]; /* Buffer to hold the sql query to check whether the given column is nullable */
PGconn *conn;
PGresult *res;
conn = (PGconn *) con->drvConnection;
for (j = 0; j < num_fields; j++) {
flds->name[j] = RS_DBI_copyString(PQfname(my_result, j));
flds->type[j] = (int) PQftype(my_result, j);
flds->length[j] = (Sint) PQfsize(my_result, j);
/* NOTE: PQfmod is -1 incase of no information */
flds->precision[j] = (Sint) PQfmod(my_result, j);
flds->scale[j] = (Sint) - 1;
/* PQftablecol returns the column number (within its table) of
* the column making up the specified query result column.Zero
* is returned if the column number is out of range, or if the
* specified column is not a simple reference to a table
* column, or when using pre-3.0 protocol. So
* "if(PQftablecol(my_result,j) !=0)" checks whether the
* particular colomn in the result set is column of table or
* not. Or else there is no meaning in checking whether a
* column is nullable or not if it does not belong to the
* table.
*/
flds->nullOk[j] = (Sint) INT_MIN; /* This should translate to NA in R */
if (PQftablecol(my_result, j) != 0) {
/* Code to find whether a row can be nullable or not */
/* we might better just store the table id and column number
for lazy evaluation at dbColumnInfo call*/
/* although the database structure can change, we are not in transaction anyway
and there is no guarantee in current code */
snprintf(buff, 1000, "select attnotnull from pg_attribute where attrelid=%d and attnum='%d'",
PQftable(my_result, j), PQftablecol(my_result, j));
res = PQexec(conn, buff);
if (res && (PQntuples(res) > 0)) {
const char * attnotnull = PQgetvalue(res, 0, 0);
if(strcmp(attnotnull, "f") == 0) {
flds->nullOk[j] = (Sint) 1; /* nollOK is TRUE when attnotnull is f*/
}
if(strcmp(attnotnull, "t") == 0) {
flds->nullOk[j] = (Sint) 0; /* nollOK is FALSE when attnotnull is t*/
}
}
PQclear(res);
}
internal_type = (int) PQftype(my_result, j);
switch (internal_type) {
case BOOLOID:
flds->Sclass[j] = LOGICAL_TYPE;
break;
case BPCHAROID:
flds->Sclass[j] = CHARACTER_TYPE;
flds->isVarLength[j] = (Sint) 0;
break;
case VARCHAROID:
case TEXTOID:
case BYTEAOID:
case NAMEOID:
case MACADDROID:
case INETOID:
flds->Sclass[j] = CHARACTER_TYPE;
flds->isVarLength[j] = (Sint) 1;
break;
case INT2OID:
case INT4OID:
case OIDOID:
flds->Sclass[j] = INTEGER_TYPE;
break;
case INT8OID:
if (sizeof(Sint) >= 8) {
flds->Sclass[j] = INTEGER_TYPE;
}
else {
flds->Sclass[j] = NUMERIC_TYPE;
}
break;
case NUMERICOID:
case FLOAT8OID:
case FLOAT4OID:
flds->Sclass[j] = NUMERIC_TYPE;
break;
case DATEOID:
case TIMEOID:
case TIMETZOID:
case TIMESTAMPOID:
case TIMESTAMPTZOID:
case INTERVALOID:
flds->Sclass[j] = CHARACTER_TYPE;
/*flds->isVarLength[j] = (Sint) 1; */
break;
default:
flds->Sclass[j] = CHARACTER_TYPE;
flds->isVarLength[j] = (Sint) 1;
snprintf(buff, 1000, "select typname, typcategory from pg_type where oid = %d", internal_type);
res = PQexec(conn, buff);
if (res) {
char * typename;
char * typecat;
int ntuples;
ntuples = PQntuples(res);
if(ntuples == 1) {
typename = PQgetvalue(res, 0, 0);
typecat = PQgetvalue(res, 0, 1);
if(*typecat == 'E') { /* This is enum, ok */
} else if(*typecat == 'A') { /*This is array, ok */
} else {
snprintf(errMsg, 128, "unrecognized PostgreSQL field type %s (id:%d) in column %d", typename, internal_type, j);
RS_DBI_errorMessage(errMsg, RS_DBI_WARNING);
}
} else {
snprintf(errMsg, 128, "oid: %d, ntuples: %d", internal_type, ntuples);
RS_DBI_errorMessage(errMsg, RS_DBI_WARNING);
}
PQclear(res);
} else {
Res_Handle *
RS_PostgreSQL_exec(Con_Handle * conHandle, s_object * statement)
{
S_EVALUATOR RS_DBI_connection * con;
Res_Handle *rsHandle;
RS_DBI_resultSet *result;
PGconn *my_connection;
PGresult *my_result;
Sint res_id, is_select=0;
char *dyn_statement;
con = RS_DBI_getConnection(conHandle);
my_connection = (PGconn *) con->drvConnection;
dyn_statement = RS_DBI_copyString(CHR_EL(statement, 0));
/* Do we have a pending resultSet in the current connection?
* PostgreSQL only allows one resultSet per connection.
*/
if (con->num_res > 0) {
res_id = (Sint) con->resultSetIds[0]; /* recall, PostgreSQL has only 1 res */
rsHandle = RS_DBI_asResHandle(MGR_ID(conHandle), CON_ID(conHandle), res_id);
result = RS_DBI_getResultSet(rsHandle);
if (result->completed == 0) {
free(dyn_statement);
RS_DBI_errorMessage("connection with pending rows, close resultSet before continuing", RS_DBI_ERROR);
}
else {
RS_PostgreSQL_closeResultSet(rsHandle);
}
}
/* Here is where we actually run the query */
/* Example: PGresult *PQexec(PGconn *conn, const char *command); */
my_result = PQexec(my_connection, dyn_statement);
if (my_result == NULL) {
char *errMsg;
const char *omsg;
size_t len;
omsg = PQerrorMessage(my_connection);
len = strlen(omsg);
free(dyn_statement);
errMsg = R_alloc(len + 80, 1); /* 80 should be larger than the length of "could not ..."*/
snprintf(errMsg, len + 80, "could not run statement: %s", omsg);
RS_DBI_errorMessage(errMsg, RS_DBI_ERROR);
}
/* ExecStatusType PQresultStatus(const PGresult *res); */
if (PQresultStatus(my_result) == PGRES_TUPLES_OK) {
is_select = (Sint) TRUE;
}
if (PQresultStatus(my_result) == PGRES_COMMAND_OK) {
is_select = (Sint) FALSE;
}
/* char *PQresultErrorMessage(const PGresult *res); */
if (strcmp(PQresultErrorMessage(my_result), "") != 0) {
char *errResultMsg;
const char *omsg;
size_t len;
omsg = PQerrorMessage(my_connection);
len = strlen(omsg);
errResultMsg = R_alloc(len + 80, 1); /* 80 should be larger than the length of "could not ..."*/
snprintf(errResultMsg, len + 80, "could not Retrieve the result : %s", omsg);
/* Frees the storage associated with a PGresult.
* void PQclear(PGresult *res); */
PQclear(my_result);
free(dyn_statement);
RS_DBI_errorMessage(errResultMsg, RS_DBI_ERROR);
}
/* we now create the wrapper and copy values */
PROTECT(rsHandle = RS_DBI_allocResultSet(conHandle));
result = RS_DBI_getResultSet(rsHandle);
result->statement = RS_DBI_copyString(dyn_statement);
result->drvResultSet = (void *) my_result;
result->rowCount = (Sint) 0;
result->isSelect = is_select;
/* Returns the number of rows affected by the SQL command.
* char *PQcmdTuples(PGresult *res);
*/
if (!is_select) {
result->rowsAffected = (Sint) atoi(PQcmdTuples(my_result));
result->completed = 1;
}
else {
result->rowsAffected = (Sint) - 1;
result->completed = 0;
}
if (is_select) {
result->fields = RS_PostgreSQL_createDataMappings(rsHandle);
}
free(dyn_statement);
UNPROTECT(1);
return rsHandle;
}
Con_Handle *
RS_PostgreSQL_newConnection(Mgr_Handle * mgrHandle, s_object * con_params)
{
S_EVALUATOR RS_DBI_connection * con;
RS_PostgreSQL_conParams *conParams;
Con_Handle *conHandle;
PGconn *my_connection;
const char *user = NULL, *password = NULL, *host = NULL, *dbname = NULL, *port = NULL, *tty = NULL, *options = NULL;
if (!is_validHandle(mgrHandle, MGR_HANDLE_TYPE)) {
RS_DBI_errorMessage("invalid PostgreSQLManager", RS_DBI_ERROR);
}
user = CHR_EL(con_params, 0);
password = CHR_EL(con_params, 1);
host = CHR_EL(con_params, 2);
dbname = CHR_EL(con_params, 3);
port = CHR_EL(con_params, 4);
tty = CHR_EL(con_params, 5);
options = CHR_EL(con_params, 6);
my_connection = PQsetdbLogin(host, port, options, tty, dbname, user, password);
conParams = RS_postgresql_allocConParams();
/* save actual connection parameters */
conParams->user = RS_DBI_copyString(PQuser(my_connection));
conParams->password = RS_DBI_copyString(PQpass(my_connection));
{
const char *tmphost = PQhost(my_connection);
if (tmphost) {
conParams->host = RS_DBI_copyString(tmphost);
} else {
conParams->host = RS_DBI_copyString("");
}
}
conParams->dbname = RS_DBI_copyString(PQdb(my_connection));
conParams->port = RS_DBI_copyString(PQport(my_connection));
conParams->tty = RS_DBI_copyString(PQtty(my_connection));
conParams->options = RS_DBI_copyString(PQoptions(my_connection));
if (PQstatus(my_connection) != CONNECTION_OK) {
char buf[1000];
sprintf(buf, "could not connect %s@%s on dbname \"%s\"\n", PQuser(my_connection), host?host:"local", PQdb(my_connection));
PQfinish(my_connection);
my_connection = NULL;
RS_PostgreSQL_freeConParams(conParams); /*free BEFORE emitting err message that do not come back */
RS_DBI_errorMessage(buf, RS_DBI_ERROR);
return R_NilValue; /* don't reach here as it goes back to R proc */
}
PROTECT(conHandle = RS_DBI_allocConnection(mgrHandle, (Sint) 1)); /* The second argument (1) specifies the number of result sets allocated */
con = RS_DBI_getConnection(conHandle);
if (my_connection && !con) {
PQfinish(my_connection);
my_connection = NULL;
RS_PostgreSQL_freeConParams(conParams);
conParams = (RS_PostgreSQL_conParams *) NULL;
RS_DBI_errorMessage("could not alloc space for connection object", RS_DBI_ERROR);
}
if(con) {
con->drvConnection = (void *) my_connection;
con->conParams = (void *) conParams;
}
UNPROTECT(1);
return conHandle;
}
SEXP
RS_PostgreSQL_pqexec(Con_Handle * conHandle, s_object * statement)
{
S_EVALUATOR RS_DBI_connection * con;
SEXP retval;
RS_DBI_resultSet *result;
PGconn *my_connection;
PGresult *my_result;
Sint res_id, is_select=0;
char *dyn_statement;
con = RS_DBI_getConnection(conHandle);
my_connection = (PGconn *) con->drvConnection;
dyn_statement = RS_DBI_copyString(CHR_EL(statement, 0));
/* Here is where we actually run the query */
/* Example: PGresult *PQexec(PGconn *conn, const char *command); */
my_result = PQexec(my_connection, dyn_statement);
if (my_result == NULL) {
char *errMsg;
const char *omsg;
size_t len;
omsg = PQerrorMessage(my_connection);
len = strlen(omsg);
free(dyn_statement);
errMsg = malloc(len + 80); /* 80 should be larger than the length of "could not ..."*/
snprintf(errMsg, len + 80, "could not run statement: %s", omsg);
RS_DBI_errorMessage(errMsg, RS_DBI_ERROR);
free(errMsg);
}
if (PQresultStatus(my_result) == PGRES_TUPLES_OK) {
is_select = (Sint) TRUE;
}
if (PQresultStatus(my_result) == PGRES_COMMAND_OK) {
is_select = (Sint) FALSE;
}
if (strcmp(PQresultErrorMessage(my_result), "") != 0) {
free(dyn_statement);
char *errResultMsg;
const char *omsg;
size_t len;
omsg = PQerrorMessage(my_connection);
len = strlen(omsg);
errResultMsg = malloc(len + 80); /* 80 should be larger than the length of "could not ..."*/
snprintf(errResultMsg, len + 80, "could not Retrieve the result : %s", omsg);
RS_DBI_errorMessage(errResultMsg, RS_DBI_ERROR);
free(errResultMsg);
/* Frees the storage associated with a PGresult.
* void PQclear(PGresult *res); */
PQclear(my_result);
}
free(dyn_statement);
PROTECT(retval = allocVector(LGLSXP, 1));
LOGICAL(retval)[0] = is_select;
UNPROTECT(1);
return retval;
}
