/*
* SendQuery: send the query string to the backend
* (and print out results)
*
* Note: This is the "front door" way to send a query. That is, use it to
* send queries actually entered by the user. These queries will be subject to
* single step mode.
* To send "back door" queries (generated by slash commands, etc.) in a
* controlled way, use PSQLexec().
*
* Returns true if the query executed successfully, false otherwise.
*/
bool
SendQuery(const char *query)
{
PGresult *results;
TimevalStruct before,
after;
bool OK;
if (!pset.db)
{
psql_error("You are currently not connected to a database.\n");
return false;
}
if (GetVariableBool(pset.vars, "SINGLESTEP"))
{
char buf[3];
printf(gettext("***(Single step mode: verify command)*******************************************\n"
"%s\n"
"***(press return to proceed or enter x and return to cancel)********************\n"),
query);
fflush(stdout);
if (fgets(buf, sizeof(buf), stdin) != NULL)
if (buf[0] == 'x')
return false;
}
else if (VariableEquals(pset.vars, "ECHO", "queries"))
{
puts(query);
fflush(stdout);
}
SetCancelConn();
if (PQtransactionStatus(pset.db) == PQTRANS_IDLE &&
!GetVariableBool(pset.vars, "AUTOCOMMIT") &&
!is_transact_command(query))
{
results = PQexec(pset.db, "BEGIN");
if (PQresultStatus(results) != PGRES_COMMAND_OK)
{
psql_error("%s", PQerrorMessage(pset.db));
PQclear(results);
ResetCancelConn();
return false;
}
PQclear(results);
}
if (pset.timing)
GETTIMEOFDAY(&before);
results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
OK = (AcceptResult(results) && ProcessCopyResult(results));
if (pset.timing)
GETTIMEOFDAY(&after);
/* but printing results isn't: */
if (OK)
OK = PrintQueryResults(results);
PQclear(results);
/* Possible microtiming output */
if (OK && pset.timing)
printf(gettext("Time: %.3f ms\n"), DIFF_MSEC(&after, &before));
/* check for events that may occur during query execution */
if (pset.encoding != PQclientEncoding(pset.db) &&
PQclientEncoding(pset.db) >= 0)
{
/* track effects of SET CLIENT_ENCODING */
pset.encoding = PQclientEncoding(pset.db);
pset.popt.topt.encoding = pset.encoding;
SetVariable(pset.vars, "ENCODING",
pg_encoding_to_char(pset.encoding));
}
PrintNotifications();
return OK;
}
int msPOSTGRESQLJoinNext(joinObj *join)
{
msPOSTGRESQLJoinInfo *joininfo = join->joininfo;
int i, length, row_count;
char *sql, *columns;
/* We need a connection, and a join value. */
if(!joininfo || !joininfo->conn) {
msSetError(MS_JOINERR, "Join has not been connected.\n",
"msPOSTGRESQLJoinNext()");
return MS_FAILURE;
}
if(!joininfo->from_value) {
msSetError(MS_JOINERR, "Join has not been prepared.\n",
"msPOSTGRESQLJoinNext()");
return MS_FAILURE;
}
/* Free the previous results. */
if(join->values) {
msFreeCharArray(join->values, join->numitems);
join->values = NULL;
}
/* We only need to execute the query if no results exist. */
if(!joininfo->query_result) {
/* Write the list of column names. */
length = 0;
for(i = 0; i < join->numitems; i++) {
length += 8 + strlen(join->items[i]) + 2;
}
columns = (char *)malloc(length);
if(!columns) {
msSetError(MS_MEMERR, "Failure to malloc.\n",
"msPOSTGRESQLJoinNext()");
return MS_FAILURE;
}
strcpy(columns, "");
for(i = 0; i < join->numitems; i++) {
strcat(columns, "\"");
strcat(columns, join->items[i]);
strcat(columns, "\"::text");
if(i != join->numitems - 1) {
strcat(columns, ", ");
}
}
/* Create the query string. */
sql = (char *)malloc(26 + strlen(columns) + strlen(join->table) +
strlen(join->to) + strlen(joininfo->from_value));
if(!sql) {
msSetError(MS_MEMERR, "Failure to malloc.\n",
"msPOSTGRESQLJoinNext()");
return MS_FAILURE;
}
sprintf(sql, "SELECT %s FROM %s WHERE %s = '%s'", columns, join->table, join->to, joininfo->from_value);
if(joininfo->layer_debug) {
msDebug("msPOSTGRESQLJoinNext(): executing %s.\n", sql);
}
free(columns);
joininfo->query_result = PQexec(joininfo->conn, sql);
if(!joininfo->query_result ||
PQresultStatus(joininfo->query_result) != PGRES_TUPLES_OK) {
msSetError(MS_QUERYERR, "Error executing queri %s: %s\n",
"msPOSTGRESQLJoinNext()", sql,
PQerrorMessage(joininfo->conn));
if(joininfo->query_result) {
PQclear(joininfo->query_result);
joininfo->query_result = NULL;
}
free(sql);
return MS_FAILURE;
}
free(sql);
}
row_count = PQntuples(joininfo->query_result);
/* see if we're done processing this set */
if(joininfo->row_num >= row_count) {
return(MS_DONE);
}
if(joininfo->layer_debug) {
msDebug("msPOSTGRESQLJoinNext(): fetching row %ld.\n",
joininfo->row_num);
}
/* Copy the resulting values into the joinObj. */
join->values = (char **)malloc(sizeof(char *) * join->numitems);
for(i = 0; i < join->numitems; i++) {
join->values[i] = msStrdup(PQgetvalue(
joininfo->query_result, joininfo->row_num, i));
}
joininfo->row_num++;
return MS_SUCCESS;
}
int main(int argc, char * argv[])
{
PGconn *conn;
PGresult *res;
char * conninfo = calloc (MAX_LEN, sizeof (char));
char * query = calloc (MAX_LEN, sizeof (char));
char * nama = calloc (MAX_LEN, sizeof (char));
char * alamat = calloc (MAX_LEN, sizeof (char));
char * temp = calloc (MAX_LEN, sizeof (char));
int field_count;
int rec_count;
int c,menu;
int i,j;
strcpy (conninfo, "dbname=test user=nop");
conn = PQconnectdb (conninfo);
if (PQstatus (conn) != CONNECTION_OK)
{
fprintf (stderr, "Kesalahan koneksi: %s\n", PQerrorMessage (conn));
exit (1);
}
while ( 1 )
{
fprintf(stdout, "\n\n\nDATA PASIEN\n");
fprintf(stdout, "***********\n\n");
fprintf(stdout, "a Tambah data\n");
fprintf(stdout, "b Tampil data\n");
fprintf(stdout, "x Keluar aplikasi\n");
fprintf(stdout, "Pilihan Anda: ");
c = tolower(fgetc (stdin));
menu = c;
while (c != '\n' && c != EOF) c = fgetc (stdin);
if (menu == 'a')
{
fprintf(stdout, "Tambah data\n");
fprintf(stdout, "===========\n");
fprintf(stdout, "Nama : ");
fgets (nama, MAX_LEN-1, stdin);
fprintf(stdout, "Alamat : ");
fgets (alamat, MAX_LEN-1, stdin);
sprintf (query, "insert into pasien (nama, alamat) values ('%s','%s')", nama, alamat);
res = PQexec (conn, query);
PQclear (res);
}
else
if (menu == 'b')
{
fprintf(stdout, "Tampil data\n");
fprintf(stdout, "===========\n");
sprintf (query, "select nama,alamat from pasien");
res = PQexec (conn, query);
field_count = PQnfields (res);
for (i=0; i< field_count; i++)
{
fprintf (stdout, "%-40s", PQfname (res, i));
}
fprintf (stdout, "\n");
rec_count = PQntuples (res);
for (i=0; i< rec_count; i++)
{
for (j=0; j< field_count; j++)
{
strcpy (temp, PQgetvalue (res, i, j));
temp[strlen(temp)-1] = 0;
fprintf (stdout, "%-40s", temp);
}
fprintf (stdout, "\n");
}
PQclear (res);
}
else
if (menu == 'x')
{
fprintf(stdout, "Bye\n");
break;
}
};
PQfinish (conn);
free (nama);
free (alamat);
free (query);
free (conninfo);
free (temp);
return 0;
}
/*+++++++++++++++++++++++++ Main Program or Function +++++++++++++++*/
void SCIA_WR_SQL_CH4_TILE( PGconn *conn, const char *prodName,
unsigned int num_rec,
const struct imap_rec *rec )
{
register unsigned int nr;
register unsigned int affectedRows = 0u;
char sql_query[SQL_STR_SIZE], cbuff[SQL_STR_SIZE];
char *pntr;
int nrow, numChar, meta_id;
long long tile_id;
PGresult *res;
/*
* check if product is already in database
*/
(void) snprintf( sql_query, SQL_STR_SIZE,
"SELECT pk_meta FROM %s WHERE name=\'%s\'",
META_TBL_NAME, prodName );
res = PQexec( conn, sql_query );
if ( PQresultStatus( res ) != PGRES_TUPLES_OK ) {
NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
}
if ( (nrow = PQntuples( res )) == 0 ) {
NADC_GOTO_ERROR( NADC_ERR_FATAL, prodName );
}
pntr = PQgetvalue( res, 0, 0 );
meta_id = (int) strtol( pntr, (char **) NULL, 10 );
PQclear( res );
/*
* Start a transaction block
*/
res = PQexec( conn, "BEGIN" );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
PQclear( res );
/*
* insert all tiles in products
*/
for ( nr = 0; nr < num_rec; nr++ ) {
/* obtain next value for serial pk_tile */
res = PQexec( conn,
"SELECT nextval(\'tile_imap_ch4_pk_tile_seq\')" );
if ( PQresultStatus( res ) != PGRES_TUPLES_OK )
NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
pntr = PQgetvalue( res, 0, 0 );
tile_id = strtoll( pntr, (char **) NULL, 10 );
PQclear( res );
numChar = snprintf( sql_query, SQL_STR_SIZE, SQL_INSERT_TILE,
TILE_TBL_NAME, tile_id, meta_id, rec[nr].jday,
NINT(16 * rec[nr].meta.intg_time),
rec[nr].meta.elev,
rec[nr].ch4_vcd, rec[nr].ch4_error,
rec[nr].co2_vcd, rec[nr].co2_error,
rec[nr].ch4_vmr,
rec[nr].lon_corner[0],rec[nr].lat_corner[0],
rec[nr].lon_corner[1],rec[nr].lat_corner[1],
rec[nr].lon_corner[2],rec[nr].lat_corner[2],
rec[nr].lon_corner[3],rec[nr].lat_corner[3],
rec[nr].lon_corner[0],rec[nr].lat_corner[0] );
(void) fprintf( stderr, "%s [%-d]\n", sql_query, numChar );
if ( numChar >= SQL_STR_SIZE )
NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
res = PQexec( conn, sql_query );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK ) {
NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
PQclear( res );
res = PQexec( conn, "ROLLBACK" );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
goto done;
}
PQclear( res );
affectedRows += 1;
}
/*
* end the transaction
*/
res = PQexec( conn, "COMMIT" );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
done:
PQclear( res );
(void) snprintf( cbuff, SQL_STR_SIZE, "affectedRows=%-u", affectedRows );
NADC_ERROR( NADC_ERR_NONE, cbuff );
}
void save_TUBii_command(client *c, int argc, sds *argv)
{
/* Update the TUBii state. */
uint32_t key;
PGconn *conn;
char conninfo[1024];
PGresult *res = NULL;
char command[10000];
//char* dbname="test", dbhost="", dbuser="", dbpass="";
sprintf(conninfo, "dbname=%s host=%s user=%s password=%s", dbconfig.name, dbconfig.host, dbconfig.user, dbconfig.password);
/* Now we update the database */
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK) {
addReplyErrorFormat(c, "connection to database failed: %s", PQerrorMessage(conn));
goto pq_error;
}
sprintf(command, "insert into TUBii ("
"control_reg, trigger_mask, speaker_mask, counter_mask,"
"caen_gain_reg, caen_channel_reg, lockout_reg, dgt_reg, dac_reg,"
"combo_enable_mask, combo_mask, counter_mode, clock_status,"
"prescale_value, prescale_channel, burst_rate, burst_channel"
") "
"VALUES ("
"%u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u, %u"
") "
"RETURNING key",
mReadReg((u32) MappedRegsBaseAddress, RegOffset10),
getTriggerMask(), getSpeakerMask(), getCounterMask(),
mReadReg((u32) MappedRegsBaseAddress, RegOffset11),
mReadReg((u32) MappedRegsBaseAddress, RegOffset12),
mReadReg((u32) MappedRegsBaseAddress, RegOffset14),
mReadReg((u32) MappedRegsBaseAddress, RegOffset15),
mReadReg((u32) MappedRegsBaseAddress, RegOffset13),
mReadReg((u32) MappedComboBaseAddress, RegOffset2),
mReadReg((u32) MappedComboBaseAddress, RegOffset3),
counter_mode, clockStatus(),
mReadReg((u32) MappedPrescaleBaseAddress, RegOffset2),
mReadReg((u32) MappedPrescaleBaseAddress, RegOffset3),
mReadReg((u32) MappedBurstBaseAddress, RegOffset2),
mReadReg((u32) MappedBurstBaseAddress, RegOffset3)
);
res = PQexec(conn, command);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
addReplyErrorFormat(c, "insert command failed: %s",
PQerrorMessage(conn));
goto pq_error;
}
if (PQnfields(res) != 1) {
addReplyError(c, "failed to get key from insert");
goto pq_error;
}
if (safe_strtoul(PQgetvalue(res, 0, 0), &key)) {
addReplyErrorFormat(c, "couldn't convert key from '%s' -> int", PQgetvalue(res, 0, 0));
goto pq_error;
}
PQclear(res);
PQfinish(conn);
addReply(c, ":%u", key);
return;
err:
addReplyError(c, tubii_err);
return;
pq_error:
if (res) PQclear(res);
PQfinish(conn);
}
/*************************************************************************
*
* Function: sql_query
*
* Purpose: Issue a query to the database
*
*************************************************************************/
static int sql_query(SQLSOCK * sqlsocket, SQL_CONFIG *config, char *querystr) {
rlm_sql_postgres_sock *pg_sock = sqlsocket->conn;
int numfields = 0;
char *errorcode;
char *errormsg;
if (config->sqltrace)
radlog(L_DBG,"rlm_sql_postgresql: query:\n%s", querystr);
if (pg_sock->conn == NULL) {
radlog(L_ERR, "rlm_sql_postgresql: Socket not connected");
return SQL_DOWN;
}
pg_sock->result = PQexec(pg_sock->conn, querystr);
/*
* Returns a PGresult pointer or possibly a null pointer.
* A non-null pointer will generally be returned except in
* out-of-memory conditions or serious errors such as inability
* to send the command to the server. If a null pointer is
* returned, it should be treated like a PGRES_FATAL_ERROR
* result.
*/
if (!pg_sock->result)
{
radlog(L_ERR, "rlm_sql_postgresql: PostgreSQL Query failed Error: %s",
PQerrorMessage(pg_sock->conn));
/* As this error COULD be a connection error OR an out-of-memory
* condition return value WILL be wrong SOME of the time regardless!
* Pick your poison....
*/
return SQL_DOWN;
} else {
ExecStatusType status = PQresultStatus(pg_sock->result);
radlog(L_DBG, "rlm_sql_postgresql: Status: %s", PQresStatus(status));
switch (status) {
case PGRES_COMMAND_OK:
/*Successful completion of a command returning no data.*/
/*affected_rows function only returns
the number of affected rows of a command
returning no data...
*/
pg_sock->affected_rows = affected_rows(pg_sock->result);
radlog(L_DBG, "rlm_sql_postgresql: query affected rows = %i", pg_sock->affected_rows);
return 0;
break;
case PGRES_TUPLES_OK:
/*Successful completion of a command returning data (such as a SELECT or SHOW).*/
pg_sock->cur_row = 0;
pg_sock->affected_rows = PQntuples(pg_sock->result);
numfields = PQnfields(pg_sock->result); /*Check row storing functions..*/
radlog(L_DBG, "rlm_sql_postgresql: query affected rows = %i , fields = %i", pg_sock->affected_rows, numfields);
return 0;
break;
case PGRES_BAD_RESPONSE:
/*The server's response was not understood.*/
radlog(L_DBG, "rlm_sql_postgresql: Bad Response From Server!!");
return -1;
break;
case PGRES_NONFATAL_ERROR:
/*A nonfatal error (a notice or warning) occurred. Possibly never returns*/
return -1;
break;
case PGRES_FATAL_ERROR:
#if defined(PG_DIAG_SQLSTATE) && defined(PG_DIAG_MESSAGE_PRIMARY)
/*A fatal error occurred.*/
errorcode = PQresultErrorField(pg_sock->result, PG_DIAG_SQLSTATE);
errormsg = PQresultErrorField(pg_sock->result, PG_DIAG_MESSAGE_PRIMARY);
radlog(L_DBG, "rlm_sql_postgresql: Error %s", errormsg);
return check_fatal_error(errorcode);
#endif
break;
default:
/* FIXME: An unhandled error occurred.*/
/* PGRES_EMPTY_QUERY PGRES_COPY_OUT PGRES_COPY_IN */
return -1;
break;
}
/*
Note to self ... sql_store_result returns 0 anyway
after setting the sqlsocket->affected_rows..
sql_num_fields returns 0 at worst case which means the check below
has a really small chance to return false..
lets remove it then .. yuck!!
*/
/*
} else {
if ((sql_store_result(sqlsocket, config) == 0)
&& (sql_num_fields(sqlsocket, config) >= 0))
return 0;
else
return -1;
}
*/
}
return -1;
}
static int begin_transaction(db_con_t * _h, char *_s)
{
PGresult *mr;
int rv;
/*
** Note:
** The upper layers of code may attempt a transaction
** before opening or having a valid connection to the
** database. We try to sense this, and open the database
** if we have the sqlurl in the _h structure. Otherwise,
** all we can do is return an error.
*/
if(_h)
{
if(CON_CONNECTED(_h))
{
mr = PQexec(CON_CONNECTION(_h), "BEGIN");
if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
{
/*
** We get here if the connection to the
** db is corrupt, which can happen a few
** different ways, but all of them are
** related to the parent process forking,
** or being forked.
*/
PLOG("begin_transaction","corrupt connection");
CON_CONNECTED(_h) = 0;
}
else
{
/*
** this is the normal way out.
** the transaction ran fine.
*/
PQclear(mr);
return(0);
}
}
else
{
DLOG("begin_transaction", "called before db_init");
}
/*
** if we get here we have a corrupt db connection,
** but we probably have a valid db_con_t structure.
** attempt to open the db.
*/
if((rv = connect_db(_h, CON_SQLURL(_h))) != 0)
{
/*
** our attempt to fix the connection failed
*/
char buf[256];
sprintf(buf, "no connection, FATAL %d!", rv);
PLOG("begin_transaction",buf);
return(rv);
}
}
else
{
PLOG("begin_transaction","must call db_init first!");
return(-1);
}
/*
** we get here if the database connection was corrupt,
** i didn't want to use recursion ...
*/
mr = PQexec(CON_CONNECTION(_h), "BEGIN");
if(!mr || PQresultStatus(mr) != PGRES_COMMAND_OK)
{
char buf[256];
sprintf("FATAL %s, '%s'!\n",
PQerrorMessage(CON_CONNECTION(_h)), _s);
PLOG("begin_transaction", buf);
return(-1);
}
DLOG("begin_transaction", "db channel reset successful");
PQclear(mr);
return(0);
}
/**
* query = select * from pg_database
*/
char darkkernel_bdd_execute(bdd *self,char *query)
{
PGresult *res;
char portal[1000],result=1;
int nFields;
int i,
j;
if (!self->conn)
self->connect(self);
if (!self->conn)
return 0;
/* Start a transaction block */
res = PQexec(self->conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(self->conn));
PQclear(res);
self->disconnect(self);
result=0;
}
PQclear(res);
/*
* Fetch rows from pg_database, the system catalog of databases
*/
sprintf(portal, "DECLARE myportal CURSOR FOR %s\0", query);
res = PQexec(self->conn, portal);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(self->conn));
PQclear(res);
PQfinish(self->conn);
}
PQclear(res);
res = PQexec(self->conn, "FETCH ALL in myportal");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(self->conn));
PQclear(res);
PQfinish(self->conn);
}
/* first, print out the attribute names */
nFields = PQnfields(res);
for (i = 0; i < nFields; i++)
printf("%-15s", PQfname(res, i));
printf("\n\n");
/* next, print out the rows */
for (i = 0; i < PQntuples(res); i++)
{
for (j = 0; j < nFields; j++)
printf("%-15s", PQgetvalue(res, i, j));
printf("\n");
}
PQclear(res);
/* close the portal ... we don't bother to check for errors ... */
res = PQexec(self->conn, "CLOSE myportal");
PQclear(res);
/* end the transaction */
res = PQexec(self->conn, "END");
PQclear(res);
self->disconnect(self);
return result;
}
/*
* Execute SQL statement. For non-select statements only.
*/
int zbx_db_vexecute(const char *fmt, va_list args)
{
char *sql = NULL;
int ret = ZBX_DB_OK;
double sec = 0;
#if defined(HAVE_IBM_DB2)
SQLHANDLE hstmt = 0;
SQLRETURN ret1;
SQLLEN row1;
SQLLEN rows = 0;
#elif defined(HAVE_MYSQL)
int status;
#elif defined(HAVE_ORACLE)
OCIStmt *stmthp = NULL;
sword err = OCI_SUCCESS;
#elif defined(HAVE_POSTGRESQL)
PGresult *result;
char *error = NULL;
#elif defined(HAVE_SQLITE3)
int err;
char *error = NULL;
#endif
if (0 != CONFIG_LOG_SLOW_QUERIES)
sec = zbx_time();
sql = zbx_dvsprintf(sql, fmt, args);
if (0 == txn_init && 0 == txn_level)
zabbix_log(LOG_LEVEL_DEBUG, "query without transaction detected");
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
ret = ZBX_DB_FAIL;
goto clean;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
#if defined(HAVE_IBM_DB2)
/* allocate a statement handle */
if (SUCCEED != zbx_ibm_db2_success(SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &hstmt)))
ret = ZBX_DB_DOWN;
/* directly execute the statement; returns SQL_NO_DATA_FOUND when no rows were affected */
if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success_ext(SQLExecDirect(hstmt, (SQLCHAR *)sql, SQL_NTS)))
ret = ZBX_DB_DOWN;
/* get number of affected rows */
if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &rows)))
ret = ZBX_DB_DOWN;
/* process other SQL statements in the batch */
while (ZBX_DB_OK == ret && SUCCEED == zbx_ibm_db2_success(ret1 = SQLMoreResults(hstmt)))
{
if (SUCCEED != zbx_ibm_db2_success(SQLRowCount(hstmt, &row1)))
ret = ZBX_DB_DOWN;
else
rows += row1;
}
if (ZBX_DB_OK == ret && SQL_NO_DATA_FOUND != ret1)
ret = ZBX_DB_DOWN;
if (ZBX_DB_OK != ret)
{
zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, hstmt);
ret = (SQL_CD_TRUE == IBM_DB2server_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else if (0 <= rows)
{
ret = (int)rows;
}
if (hstmt)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
#elif defined(HAVE_MYSQL)
if (NULL == conn)
{
zabbix_errlog(ERR_Z3003);
ret = ZBX_DB_FAIL;
}
else
{
if (0 != (status = mysql_query(conn, sql)))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
switch (mysql_errno(conn))
{
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
case ER_UNKNOWN_ERROR:
ret = ZBX_DB_DOWN;
break;
default:
ret = ZBX_DB_FAIL;
break;
}
}
else
{
do
{
if (0 != mysql_field_count(conn))
{
zabbix_log(LOG_LEVEL_DEBUG, "cannot retrieve result set");
break;
}
else
ret += (int)mysql_affected_rows(conn);
/* more results? -1 = no, >0 = error, 0 = yes (keep looping) */
if (0 < (status = mysql_next_result(conn)))
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
}
while (0 == status);
}
}
#elif defined(HAVE_ORACLE)
err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (OCI_SUCCESS == err)
{
err = OCIStmtPrepare(stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
}
if (OCI_SUCCESS == err)
{
err = OCIStmtExecute(oracle.svchp, stmthp, oracle.errhp, (ub4)1, (ub4)0,
(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
if (OCI_SUCCESS == err)
{
ub4 nrows = 0;
err = OCIAttrGet((void *)stmthp, OCI_HTYPE_STMT, (ub4 *)&nrows,
(ub4 *)0, OCI_ATTR_ROW_COUNT, oracle.errhp);
ret = nrows;
}
}
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
ret = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (NULL != stmthp)
{
(void)OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
stmthp = NULL;
}
#elif defined(HAVE_POSTGRESQL)
result = PQexec(conn,sql);
if (NULL == result)
{
zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
else if (PGRES_COMMAND_OK != PQresultStatus(result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result)),
PQresultErrorMessage(result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
ret = (CONNECTION_OK == PQstatus(conn) ? ZBX_DB_FAIL : ZBX_DB_DOWN);
}
if (ZBX_DB_OK == ret)
ret = atoi(PQcmdTuples(result));
PQclear(result);
#elif defined(HAVE_SQLITE3)
if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
{
zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
exit(FAIL);
}
lbl_exec:
if (SQLITE_OK != (err = sqlite3_exec(conn, sql, NULL, 0, &error)))
{
if (SQLITE_BUSY == err)
goto lbl_exec;
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
switch (err)
{
case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
are this far into execution, zbx_db_connect() was successful */
case SQLITE_NOMEM: /* A malloc() failed */
case SQLITE_TOOBIG: /* String or BLOB exceeds size limit */
case SQLITE_CONSTRAINT: /* Abort due to constraint violation */
case SQLITE_MISMATCH: /* Data type mismatch */
ret = ZBX_DB_FAIL;
break;
default:
ret = ZBX_DB_DOWN;
break;
}
}
if (ZBX_DB_OK == ret)
ret = sqlite3_changes(conn);
if (0 == txn_level)
php_sem_release(&sqlite_access);
#endif /* HAVE_SQLITE3 */
if (0 != CONFIG_LOG_SLOW_QUERIES)
{
sec = zbx_time() - sec;
if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
}
if (ZBX_DB_FAIL == ret && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
clean:
zbx_free(sql);
return ret;
}
int
main(int argc, char *argv[])
{
static struct option long_options[] = {
{"host", required_argument, NULL, 'h'},
{"port", required_argument, NULL, 'p'},
{"username", required_argument, NULL, 'U'},
{"no-password", no_argument, NULL, 'w'},
{"password", no_argument, NULL, 'W'},
{"echo", no_argument, NULL, 'e'},
{"interactive", no_argument, NULL, 'i'},
{NULL, 0, NULL, 0}
};
const char *progname;
int optindex;
int c;
char *dbname = NULL;
char *host = NULL;
char *port = NULL;
char *username = NULL;
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
bool interactive = false;
PQExpBufferData sql;
PGconn *conn;
PGresult *result;
progname = get_progname(argv[0]);
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
handle_help_version_opts(argc, argv, "dropdb", help);
while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1)
{
switch (c)
{
case 'h':
host = optarg;
break;
case 'p':
port = optarg;
break;
case 'U':
username = optarg;
break;
case 'w':
prompt_password = TRI_NO;
break;
case 'W':
prompt_password = TRI_YES;
break;
case 'e':
echo = true;
break;
case 'i':
interactive = true;
break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
}
}
switch (argc - optind)
{
case 0:
fprintf(stderr, _("%s: missing required argument database name\n"), progname);
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
case 1:
dbname = argv[optind];
break;
default:
fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
progname, argv[optind + 1]);
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
}
if (interactive)
{
printf(_("Database \"%s\" will be permanently removed.\n"), dbname);
if (!yesno_prompt("Are you sure?"))
exit(0);
}
initPQExpBuffer(&sql);
appendPQExpBuffer(&sql, "DROP DATABASE %s;\n",
fmtId(dbname));
/*
* Connect to the 'postgres' database by default, except have
* the 'postgres' user use 'template1' so he can drop the
* 'postgres' database.
*/
conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
host, port, username, prompt_password, progname);
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
if (PQresultStatus(result) != PGRES_COMMAND_OK)
{
fprintf(stderr, _("%s: database removal failed: %s"),
progname, PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
PQclear(result);
PQfinish(conn);
exit(0);
}
bool QgsShapeFile::insertLayer( QString dbname, QString schema, QString primary_key, QString geom_col,
QString srid, PGconn * conn, QProgressDialog& pro, bool &fin,
QString& errorText )
{
Q_UNUSED( dbname );
connect( &pro, SIGNAL( canceled() ), this, SLOT( cancelImport() ) );
import_canceled = false;
bool result = true;
QString query = QString( "CREATE TABLE %1.%2(%3 SERIAL PRIMARY KEY" )
.arg( QgsPgUtil::quotedIdentifier( schema ) )
.arg( QgsPgUtil::quotedIdentifier( table_name ) )
.arg( QgsPgUtil::quotedIdentifier( primary_key ) );
for ( uint n = 0; n < column_names.size() && result; n++ )
{
query += QString( ",%1 %2" )
.arg( QgsPgUtil::quotedIdentifier( column_names[n] ) )
.arg( column_types[n] );
}
query += " )";
QgsDebugMsg( "Query string is: " + query );
PGresult *res = PQexec( conn, query.toUtf8() );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
// flag error and send query and error message to stdout on debug
errorText += tr( "The database gave an error while executing this SQL:\n%1\nThe error was:\n%2\n" )
.arg( query ).arg( PQresultErrorMessage( res ) );
PQclear( res );
return false;
}
else
{
PQclear( res );
}
query = QString( "SELECT AddGeometryColumn(%1,%2,%3,%4,%5,2)" )
.arg( QgsPgUtil::quotedValue( schema ) )
.arg( QgsPgUtil::quotedValue( table_name ) )
.arg( QgsPgUtil::quotedValue( geom_col ) )
.arg( srid )
.arg( QgsPgUtil::quotedValue( geom_type ) );
res = PQexec( conn, query.toUtf8() );
if ( PQresultStatus( res ) != PGRES_TUPLES_OK )
{
errorText += tr( "The database gave an error while executing this SQL:\n%1\nThe error was:\n%2\n" )
.arg( query ).arg( PQresultErrorMessage( res ) );
PQclear( res );
return false;
}
else
{
PQclear( res );
}
if ( isMulti )
{
query = QString( "select constraint_name from information_schema.table_constraints where table_schema=%1 and table_name=%2 and constraint_name in ('$2','enforce_geotype_the_geom')" )
.arg( QgsPgUtil::quotedValue( schema ) )
.arg( QgsPgUtil::quotedValue( table_name ) );
QStringList constraints;
res = PQexec( conn, query.toUtf8() );
if ( PQresultStatus( res ) == PGRES_TUPLES_OK )
{
for ( int i = 0; i < PQntuples( res ); i++ )
constraints.append( PQgetvalue( res, i, 0 ) );
}
PQclear( res );
if ( constraints.size() > 0 )
{
// drop the check constraint
// TODO This whole concept needs to be changed to either
// convert the geometries to the same type or allow
// multiple types in the check constraint. For now, we
// just drop the constraint...
query = QString( "alter table %1 drop constraint %2" )
.arg( QgsPgUtil::quotedIdentifier( table_name ) )
.arg( QgsPgUtil::quotedIdentifier( constraints[0] ) );
res = PQexec( conn, query.toUtf8() );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
errorText += tr( "The database gave an error while executing this SQL:\n%1\nThe error was:\n%2\n" )
.arg( query ).arg( PQresultErrorMessage( res ) );
PQclear( res );
return false;
}
PQclear( res );
}
}
//adding the data into the table
for ( int m = 0; m < features && result; m++ )
{
if ( import_canceled )
{
fin = true;
break;
}
OGRFeatureH feat = OGR_L_GetNextFeature( ogrLayer );
if ( feat )
{
OGRGeometryH geom = OGR_F_GetGeometryRef( feat );
if ( geom )
{
query = QString( "INSERT INTO %1.%2(" )
.arg( QgsPgUtil::quotedIdentifier( schema ) )
.arg( QgsPgUtil::quotedIdentifier( table_name ) );
QString values = " VALUES (";
char *geo_temp;
// 'GeometryFromText' supports only 2D coordinates
// TODO for proper 2.5D support we would need to use 'GeomFromEWkt'
if ( hasMoreDimensions )
OGR_G_SetCoordinateDimension( geom, 2 );
OGR_G_ExportToWkt( geom, &geo_temp );
QString geometry( geo_temp );
CPLFree( geo_temp );
for ( uint n = 0; n < column_types.size(); n++ )
{
QString val;
// FIXME: OGR_F_GetFieldAsString returns junk when called with a 8.255 float field
if ( column_types[n] == "float" )
val = QString::number( OGR_F_GetFieldAsDouble( feat, n ) );
else
val = codec->toUnicode( OGR_F_GetFieldAsString( feat, n ) );
if ( val.isEmpty() )
val = "NULL";
else
val = QgsPgUtil::quotedValue( val );
if ( n > 0 )
{
query += ",";
values += ",";
}
query += QgsPgUtil::quotedIdentifier( column_names[n] );
values += val;
}
query += "," + QgsPgUtil::quotedIdentifier( geom_col );
values += QString( ",st_geometryfromtext(%1,%2)" )
.arg( QgsPgUtil::quotedValue( geometry ) )
.arg( srid );
query += ")" + values + ")";
if ( result )
res = PQexec( conn, query.toUtf8() );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
// flag error and send query and error message to stdout on debug
result = false;
errorText += tr( "The database gave an error while executing this SQL:" ) + "\n";
// the query string can be quite long. Trim if necessary...
if ( query.count() > 100 )
errorText += query.left( 150 ) +
tr( "... (rest of SQL trimmed)", "is appended to a truncated SQL statement" ) +
"\n";
else
errorText += query + '\n';
errorText += tr( "The error was:\n%1\n" ).arg( PQresultErrorMessage( res ) );
errorText += '\n';
}
else
{
PQclear( res );
}
pro.setValue( pro.value() + 1 );
qApp->processEvents();
}
OGR_F_Destroy( feat );
}
}
// create the GIST index if the the load was successful
if ( result )
{
// prompt user to see if they want to build the index and warn
// them about the potential time-cost
}
OGR_L_ResetReading( ogrLayer );
return result;
}
/*
* Actual code to make call to the database and print the output data.
*/
int
sql_exec(PGconn *conn, const char *todo, bool quiet)
{
PGresult *res;
int nfields;
int nrows;
int i,
j,
l;
int *length;
char *pad;
/* make the call */
res = PQexec(conn, todo);
/* check and deal with errors */
if (!res || PQresultStatus(res) > 2)
{
fprintf(stderr, "oid2name: query failed: %s\n", PQerrorMessage(conn));
fprintf(stderr, "oid2name: query was: %s\n", todo);
PQclear(res);
PQfinish(conn);
exit(-1);
}
/* get the number of fields */
nrows = PQntuples(res);
nfields = PQnfields(res);
/* for each field, get the needed width */
length = (int *) pg_malloc(sizeof(int) * nfields);
for (j = 0; j < nfields; j++)
length[j] = strlen(PQfname(res, j));
for (i = 0; i < nrows; i++)
{
for (j = 0; j < nfields; j++)
{
l = strlen(PQgetvalue(res, i, j));
if (l > length[j])
length[j] = strlen(PQgetvalue(res, i, j));
}
}
/* print a header */
if (!quiet)
{
for (j = 0, l = 0; j < nfields; j++)
{
fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
l += length[j] + 2;
}
fprintf(stdout, "\n");
pad = (char *) pg_malloc(l + 1);
MemSet(pad, '-', l);
pad[l] = '\0';
fprintf(stdout, "%s\n", pad);
free(pad);
}
/* for each row, dump the information */
for (i = 0; i < nrows; i++)
{
for (j = 0; j < nfields; j++)
fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
fprintf(stdout, "\n");
}
/* cleanup */
PQclear(res);
free(length);
return 0;
}
static PGresult *c_psql_exec_query_noparams (c_psql_database_t *db,
udb_query_t *q)
{
return PQexec (db->conn, udb_query_get_statement (q));
} /* c_psql_exec_query_noparams */
void
GenSQL (PGconn *dbh,const char *file)
{
int i,ntuples;
int reverse=0;
int type_check=0;
int preamble=0;
PGresult *result;
Oid col_type;
FILE *out;
char token_data[TOKEN_DATA_LEN+1];
char token_type[TOKEN_TYPE_LEN+1];
unsigned long long token;
memset((void *)token_type, 0, TOKEN_TYPE_LEN+1);
memset((void *)token_data, 0, TOKEN_DATA_LEN+1);
if (strncmp(file,"-",1)==0) {
out=stdout;
} else {
if ( (out = fopen(file,"w+")) == NULL ) {
fprintf(stderr, "Failed to open file %s for writing - %s\n",
file, strerror(errno));
PQfinish(dbh);
exit(EXIT_FAILURE);
}
}
result = PQexec(dbh, "SELECT uid, token, spam_hits, innocent_hits, last_hit "
"FROM dspam_token_data");
if (! result || PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "Failed to run result: %s\n", PQresultErrorMessage(result));
if (result) PQclear(result);
PQfinish(dbh);
fclose (out);
exit(EXIT_FAILURE);
}
ntuples = PQntuples(result);
for (i=0; i<ntuples; i++)
{
if (!type_check)
{
type_check = 1;
col_type = PQftype(result, 1);
if (col_type == BIGINTOID)
{
fprintf(stderr, "Datatype of dspam_token_data.token *not* NUMERIC;\n"
"assuming that you want to revert back to NUMERIC(20) type from BIGINT type\n");
reverse=1;
} else if (col_type != NUMERICOID)
{
fprintf(stderr, "Type of dspam_token_data.token is not BIGINT *or* NUMERIC(20)!\n"
"I have got no clue of how to deal with this and I am going to sulk now.\n");
if (result) PQclear(result);
PQfinish(dbh);
fclose (out);
exit(EXIT_FAILURE);
}
}
if (!preamble)
{
preamble = 1;
if (reverse == 0) {
snprintf(token_type, TOKEN_TYPE_LEN, "bigint");
} else {
snprintf(token_type, TOKEN_TYPE_LEN, "numeric(20)");
}
fprintf(out,"BEGIN;\n"
"DROP TABLE dspam_token_data;\n"
"COMMIT;\n"
"BEGIN;\n"
"CREATE TABLE dspam_token_data (\n"
" uid smallint,\n"
" token %s,\n"
" spam_hits int,\n"
" innocent_hits int,\n"
" last_hit date,\n"
" UNIQUE (token, uid)\n"
") WITHOUT OIDS;\n"
"COMMIT;\n"
"BEGIN;\n"
"COPY dspam_token_data (uid,token,spam_hits,innocent_hits,last_hit) FROM stdin;\n"
, token_type);
}
if (!reverse) {
token = strtoull( PQgetvalue(result,i,1), NULL, 0);
snprintf(token_data, TOKEN_DATA_LEN, "%lld", token);
} else {
token = (unsigned long long) strtoll( PQgetvalue(result,i,1), NULL, 0);
snprintf(token_data, TOKEN_DATA_LEN, "%llu", token);
}
fprintf(out,"%s\t%s\t%s\t%s\t%s\n",
PQgetvalue(result,i,0), token_data,
PQgetvalue(result,i,2),
PQgetvalue(result,i,3),
PQgetvalue(result,i,4) );
}
if (result) PQclear(result);
fprintf(out, "\\.\n\n"
"COMMIT;\n"
"BEGIN;\n"
"CREATE INDEX id_token_data_03 ON dspam_token_data(token);\n"
"CREATE INDEX id_token_data_04 ON dspam_token_data(uid);\n"
"COMMIT;\n"
"ANALYSE;\n");
fclose (out);
}
/*
* SendQuery: send the query string to the backend
* (and print out results)
*
* Note: This is the "front door" way to send a query. That is, use it to
* send queries actually entered by the user. These queries will be subject to
* single step mode.
* To send "back door" queries (generated by slash commands, etc.) in a
* controlled way, use PSQLexec().
*
* Returns true if the query executed successfully, false otherwise.
*/
bool
SendQuery(const char *query)
{
PGresult *results;
PGTransactionStatusType transaction_status;
double elapsed_msec = 0;
bool OK = false;
bool on_error_rollback_savepoint = false;
static bool on_error_rollback_warning = false;
if (!pset.db)
{
psql_error("You are currently not connected to a database.\n");
goto sendquery_cleanup;
}
if (pset.singlestep)
{
char buf[3];
printf(_("***(Single step mode: verify command)*******************************************\n"
"%s\n"
"***(press return to proceed or enter x and return to cancel)********************\n"),
query);
fflush(stdout);
if (fgets(buf, sizeof(buf), stdin) != NULL)
if (buf[0] == 'x')
goto sendquery_cleanup;
}
else if (pset.echo == PSQL_ECHO_QUERIES)
{
puts(query);
fflush(stdout);
}
if (pset.logfile)
{
fprintf(pset.logfile,
_("********* QUERY **********\n"
"%s\n"
"**************************\n\n"), query);
fflush(pset.logfile);
}
SetCancelConn();
transaction_status = PQtransactionStatus(pset.db);
if (transaction_status == PQTRANS_IDLE &&
!pset.autocommit &&
!command_no_begin(query))
{
results = PQexec(pset.db, "BEGIN");
if (PQresultStatus(results) != PGRES_COMMAND_OK)
{
psql_error("%s", PQerrorMessage(pset.db));
PQclear(results);
ResetCancelConn();
goto sendquery_cleanup;
}
PQclear(results);
transaction_status = PQtransactionStatus(pset.db);
}
if (transaction_status == PQTRANS_INTRANS &&
pset.on_error_rollback != PSQL_ERROR_ROLLBACK_OFF &&
(pset.cur_cmd_interactive ||
pset.on_error_rollback == PSQL_ERROR_ROLLBACK_ON))
{
if (on_error_rollback_warning == false && pset.sversion < 80000)
{
psql_error("The server (version %d.%d) does not support savepoints for ON_ERROR_ROLLBACK.\n",
pset.sversion / 10000, (pset.sversion / 100) % 100);
on_error_rollback_warning = true;
}
else
{
results = PQexec(pset.db, "SAVEPOINT pg_psql_temporary_savepoint");
if (PQresultStatus(results) != PGRES_COMMAND_OK)
{
psql_error("%s", PQerrorMessage(pset.db));
PQclear(results);
ResetCancelConn();
goto sendquery_cleanup;
}
PQclear(results);
on_error_rollback_savepoint = true;
}
}
if (pset.fetch_count <= 0 || !is_select_command(query))
{
/* Default fetch-it-all-and-print mode */
instr_time before,
after;
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
results = PQexec(pset.db, query);
/* these operations are included in the timing result: */
ResetCancelConn();
OK = ProcessResult(&results);
if (pset.timing)
{
INSTR_TIME_SET_CURRENT(after);
INSTR_TIME_SUBTRACT(after, before);
elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
}
/* but printing results isn't: */
if (OK && results)
OK = PrintQueryResults(results);
}
else
{
/* Fetch-in-segments mode */
OK = ExecQueryUsingCursor(query, &elapsed_msec);
ResetCancelConn();
results = NULL; /* PQclear(NULL) does nothing */
}
if (!OK && pset.echo == PSQL_ECHO_ERRORS)
psql_error("STATEMENT: %s\n", query);
/* If we made a temporary savepoint, possibly release/rollback */
if (on_error_rollback_savepoint)
{
const char *svptcmd = NULL;
transaction_status = PQtransactionStatus(pset.db);
switch (transaction_status)
{
case PQTRANS_INERROR:
/* We always rollback on an error */
svptcmd = "ROLLBACK TO pg_psql_temporary_savepoint";
break;
case PQTRANS_IDLE:
/* If they are no longer in a transaction, then do nothing */
break;
case PQTRANS_INTRANS:
/*
* Do nothing if they are messing with savepoints themselves:
* If the user did RELEASE or ROLLBACK, our savepoint is gone.
* If they issued a SAVEPOINT, releasing ours would remove
* theirs.
*/
if (results &&
(strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
strcmp(PQcmdStatus(results), "ROLLBACK") == 0))
svptcmd = NULL;
else
svptcmd = "RELEASE pg_psql_temporary_savepoint";
break;
case PQTRANS_ACTIVE:
case PQTRANS_UNKNOWN:
default:
OK = false;
/* PQTRANS_UNKNOWN is expected given a broken connection. */
if (transaction_status != PQTRANS_UNKNOWN || ConnectionUp())
psql_error("unexpected transaction status (%d)\n",
transaction_status);
break;
}
if (svptcmd)
{
PGresult *svptres;
svptres = PQexec(pset.db, svptcmd);
if (PQresultStatus(svptres) != PGRES_COMMAND_OK)
{
psql_error("%s", PQerrorMessage(pset.db));
PQclear(svptres);
OK = false;
PQclear(results);
ResetCancelConn();
goto sendquery_cleanup;
}
PQclear(svptres);
}
}
PQclear(results);
/* Possible microtiming output */
if (pset.timing)
printf(_("Time: %.3f ms\n"), elapsed_msec);
/* check for events that may occur during query execution */
if (pset.encoding != PQclientEncoding(pset.db) &&
PQclientEncoding(pset.db) >= 0)
{
/* track effects of SET CLIENT_ENCODING */
pset.encoding = PQclientEncoding(pset.db);
pset.popt.topt.encoding = pset.encoding;
SetVariable(pset.vars, "ENCODING",
pg_encoding_to_char(pset.encoding));
}
PrintNotifications();
/* perform cleanup that should occur after any attempted query */
sendquery_cleanup:
/* reset \g's output-to-filename trigger */
if (pset.gfname)
{
free(pset.gfname);
pset.gfname = NULL;
}
/* reset \gset trigger */
if (pset.gset_prefix)
{
free(pset.gset_prefix);
pset.gset_prefix = NULL;
}
return OK;
}
/******************************************************************************
* *
* Function: zbx_db_vselect *
* *
* Purpose: execute a select statement *
* *
* Return value: data, NULL (on error) or (DB_RESULT)ZBX_DB_DOWN *
* *
******************************************************************************/
DB_RESULT zbx_db_vselect(const char *fmt, va_list args)
{
char *sql = NULL;
DB_RESULT result = NULL;
double sec = 0;
#if defined(HAVE_IBM_DB2)
int i;
SQLRETURN ret = SQL_SUCCESS;
#elif defined(HAVE_ORACLE)
sword err = OCI_SUCCESS;
ub4 counter;
#elif defined(HAVE_POSTGRESQL)
char *error = NULL;
#elif defined(HAVE_SQLITE3)
int ret = FAIL;
char *error = NULL;
#endif
if (0 != CONFIG_LOG_SLOW_QUERIES)
sec = zbx_time();
sql = zbx_dvsprintf(sql, fmt, args);
if (1 == txn_error)
{
zabbix_log(LOG_LEVEL_DEBUG, "ignoring query [txnlev:%d] [%s] within failed transaction", txn_level, sql);
goto clean;
}
zabbix_log(LOG_LEVEL_DEBUG, "query [txnlev:%d] [%s]", txn_level, sql);
#if defined(HAVE_IBM_DB2)
result = zbx_malloc(result, sizeof(ZBX_IBM_DB2_RESULT));
memset(result, 0, sizeof(ZBX_IBM_DB2_RESULT));
/* allocate a statement handle */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLAllocHandle(SQL_HANDLE_STMT, ibm_db2.hdbc, &result->hstmt)))
goto error;
/* directly execute the statement */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLExecDirect(result->hstmt, (SQLCHAR *)sql, SQL_NTS)))
goto error;
/* identify the number of output columns */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLNumResultCols(result->hstmt, &result->ncolumn)))
goto error;
if (0 == result->ncolumn)
goto error;
result->nalloc = 0;
result->values = zbx_malloc(result->values, sizeof(char *) * result->ncolumn);
result->values_cli = zbx_malloc(result->values_cli, sizeof(char *) * result->ncolumn);
result->values_len = zbx_malloc(result->values_len, sizeof(SQLINTEGER) * result->ncolumn);
for (i = 0; i < result->ncolumn; i++)
{
/* get the display size for a column */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLColAttribute(result->hstmt, (SQLSMALLINT)(i + 1),
SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, &result->values_len[i])))
{
goto error;
}
result->values_len[i] += 1; /* '\0'; */
/* allocate memory to bind a column */
result->values_cli[i] = zbx_malloc(NULL, result->values_len[i]);
result->nalloc++;
/* bind columns to program variables, converting all types to CHAR */
if (SUCCEED != zbx_ibm_db2_success(ret = SQLBindCol(result->hstmt, (SQLSMALLINT)(i + 1),
SQL_C_CHAR, result->values_cli[i], result->values_len[i], &result->values_len[i])))
{
goto error;
}
}
error:
if (SUCCEED != zbx_ibm_db2_success(ret) || 0 == result->ncolumn)
{
zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc);
zbx_ibm_db2_log_errors(SQL_HANDLE_STMT, result->hstmt);
IBM_DB2free_result(result);
result = (SQL_CD_TRUE == IBM_DB2server_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
#elif defined(HAVE_MYSQL)
if (NULL == conn)
{
zabbix_errlog(ERR_Z3003);
result = NULL;
}
else
{
if (0 != mysql_query(conn, sql))
{
zabbix_errlog(ERR_Z3005, mysql_errno(conn), mysql_error(conn), sql);
switch (mysql_errno(conn))
{
case CR_CONN_HOST_ERROR:
case CR_SERVER_GONE_ERROR:
case CR_CONNECTION_ERROR:
case CR_SERVER_LOST:
case ER_SERVER_SHUTDOWN:
case ER_ACCESS_DENIED_ERROR: /* wrong user or password */
case ER_ILLEGAL_GRANT_FOR_TABLE: /* user without any privileges */
case ER_TABLEACCESS_DENIED_ERROR:/* user without some privilege */
case ER_UNKNOWN_ERROR:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
default:
result = NULL;
break;
}
}
else
result = mysql_store_result(conn);
}
#elif defined(HAVE_ORACLE)
result = zbx_malloc(NULL, sizeof(ZBX_OCI_DB_RESULT));
memset(result, 0, sizeof(ZBX_OCI_DB_RESULT));
err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&result->stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if (OCI_SUCCESS == err)
{
err = OCIStmtPrepare(result->stmthp, oracle.errhp, (text *)sql, (ub4)strlen((char *)sql),
(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
}
if (OCI_SUCCESS == err)
{
err = OCIStmtExecute(oracle.svchp, result->stmthp, oracle.errhp, (ub4)0, (ub4)0,
(CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_COMMIT_ON_SUCCESS);
}
if (OCI_SUCCESS == err)
{
/* get the number of columns in the query */
err = OCIAttrGet((void *)result->stmthp, OCI_HTYPE_STMT, (void *)&result->ncolumn,
(ub4 *)0, OCI_ATTR_PARAM_COUNT, oracle.errhp);
}
if (OCI_SUCCESS != err)
goto error;
assert(0 < result->ncolumn);
result->values = zbx_malloc(NULL, result->ncolumn * sizeof(char *));
memset(result->values, 0, result->ncolumn * sizeof(char *));
for (counter = 1; OCI_SUCCESS == err && counter <= result->ncolumn; counter++)
{
OCIParam *parmdp = NULL;
OCIDefine *defnp = NULL;
ub4 char_semantics;
ub2 col_width;
/* request a parameter descriptor in the select-list */
err = OCIParamGet((void *)result->stmthp, OCI_HTYPE_STMT, oracle.errhp, (void **)&parmdp, (ub4)counter);
if (OCI_SUCCESS == err)
{
/* retrieve the length semantics for the column */
char_semantics = 0;
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&char_semantics, (ub4 *)0,
(ub4)OCI_ATTR_CHAR_USED, (OCIError *)oracle.errhp);
}
if (OCI_SUCCESS == err)
{
col_width = 0;
if (char_semantics)
{
/* retrieve the column width in characters */
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
(ub4)OCI_ATTR_CHAR_SIZE, (OCIError *)oracle.errhp);
}
else
{
/* retrieve the column width in bytes */
err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)0,
(ub4)OCI_ATTR_DATA_SIZE, (OCIError *)oracle.errhp);
}
}
col_width++;
result->values[counter - 1] = zbx_malloc(NULL, col_width);
memset(result->values[counter - 1], 0, col_width);
if (OCI_SUCCESS == err)
{
/* represent any data as characters */
err = OCIDefineByPos(result->stmthp, &defnp, oracle.errhp, counter,
(dvoid *)result->values[counter - 1], col_width, SQLT_STR,
(dvoid *)0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
}
/* free cell descriptor */
OCIDescriptorFree(parmdp, OCI_DTYPE_PARAM);
parmdp = NULL;
}
error:
if (OCI_SUCCESS != err)
{
zabbix_errlog(ERR_Z3005, err, zbx_oci_error(err), sql);
OCI_DBfree_result(result);
result = (OCI_SERVER_NORMAL == OCI_DBserver_status() ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
#elif defined(HAVE_POSTGRESQL)
result = zbx_malloc(NULL, sizeof(ZBX_PG_DB_RESULT));
result->pg_result = PQexec(conn, sql);
result->values = NULL;
result->cursor = 0;
result->row_num = 0;
if (NULL == result->pg_result)
zabbix_errlog(ERR_Z3005, 0, "result is NULL", sql);
if (PGRES_TUPLES_OK != PQresultStatus(result->pg_result))
{
error = zbx_dsprintf(error, "%s:%s",
PQresStatus(PQresultStatus(result->pg_result)),
PQresultErrorMessage(result->pg_result));
zabbix_errlog(ERR_Z3005, 0, error, sql);
zbx_free(error);
PG_DBfree_result(result);
result = (CONNECTION_OK == PQstatus(conn) ? NULL : (DB_RESULT)ZBX_DB_DOWN);
}
else /* init rownum */
result->row_num = PQntuples(result->pg_result);
#elif defined(HAVE_SQLITE3)
if (0 == txn_level && PHP_MUTEX_OK != php_sem_acquire(&sqlite_access))
{
zabbix_log(LOG_LEVEL_CRIT, "ERROR: cannot create lock on SQLite3 database");
exit(FAIL);
}
result = zbx_malloc(NULL, sizeof(ZBX_SQ_DB_RESULT));
result->curow = 0;
lbl_get_table:
if (SQLITE_OK != (ret = sqlite3_get_table(conn,sql, &result->data, &result->nrow, &result->ncolumn, &error)))
{
if (SQLITE_BUSY == ret)
goto lbl_get_table;
zabbix_errlog(ERR_Z3005, 0, error, sql);
sqlite3_free(error);
SQ_DBfree_result(result);
switch (ret)
{
case SQLITE_ERROR: /* SQL error or missing database; assuming SQL error, because if we
are this far into execution, zbx_db_connect() was successful */
case SQLITE_NOMEM: /* a malloc() failed */
case SQLITE_MISMATCH: /* data type mismatch */
result = NULL;
break;
default:
result = (DB_RESULT)ZBX_DB_DOWN;
break;
}
}
if (0 == txn_level)
php_sem_release(&sqlite_access);
#endif /* HAVE_SQLITE3 */
if (0 != CONFIG_LOG_SLOW_QUERIES)
{
sec = zbx_time() - sec;
if (sec > (double)CONFIG_LOG_SLOW_QUERIES / 1000.0)
zabbix_log(LOG_LEVEL_WARNING, "slow query: " ZBX_FS_DBL " sec, \"%s\"", sec, sql);
}
if (NULL == result && 0 < txn_level)
{
zabbix_log(LOG_LEVEL_DEBUG, "query [%s] failed, setting transaction as failed", sql);
txn_error = 1;
}
clean:
zbx_free(sql);
return result;
}
int center_manage_updatedata(){
int i;
PGconn *db_conn;
PGresult *db_res;
int db_count;
int cacheid;
center_jmod_info *jmod_info;
int proid;
int lang_flag;
std::map<std::string,center_jmod_info*>::iterator jmod_it;
center_pro_info *pro_info;
std::vector<std::pair<int,int> > pro_list;
if((db_conn = center_manage_conndb()) == NULL){
return -1;
}
db_res = PQexec(db_conn,"SELECT DISTINCT \"jmodname\" FROM \"mod\";");
if(PQresultStatus(db_res) != PGRES_TUPLES_OK){
center_manage_closedb(db_conn);
return -1;
}
db_count = PQntuples(db_res);
for(i = 0;i < db_count;i++){
jmod_info = new center_jmod_info(PQgetvalue(db_res,i,0),2);
center_manage_jmodmap.insert(std::pair<std::string,center_jmod_info*>(jmod_info->name,jmod_info));
}
PQclear(db_res);
db_res = PQexec(db_conn,"SELECT \"proid\",\"cacheid\",\"lang\",\"jmodname\" FROM \"problem\" INNER JOIN \"mod\" ON (\"problem\".\"modid\"=\"mod\".\"modid\");");
if(PQresultStatus(db_res) != PGRES_TUPLES_OK){
center_manage_closedb(db_conn);
return -1;
}
db_count = PQntuples(db_res);
for(i = 0;i < db_count;i++){
sscanf(PQgetvalue(db_res,i,0),"%d",&proid);
sscanf(PQgetvalue(db_res,i,1),"%d",&cacheid);
sscanf(PQgetvalue(db_res,i,2),"%d",&lang_flag);
if((jmod_it = center_manage_jmodmap.find(PQgetvalue(db_res,i,3))) == center_manage_jmodmap.end()){
continue;
}
if(manage_updatepro(proid,cacheid,jmod_it->second,lang_flag) == 1){
pro_list.push_back(std::make_pair(proid,cacheid));
printf("pro update %d %d\n",proid,cacheid);
}
}
PQclear(db_res);
if(!pro_list.empty()){
center_judge_updatepro(pro_list);
}
center_manage_closedb(db_conn);
return 0;
}
static void
BaseBackup(void)
{
PGresult *res;
char *sysidentifier;
uint32 timeline;
char current_path[MAXPGPATH];
char escaped_label[MAXPGPATH];
int i;
char xlogstart[64];
char xlogend[64];
/*
* Connect in replication mode to the server
*/
conn = GetConnection();
/*
* Run IDENTIFY_SYSTEM so we can get the timeline
*/
res = PQexec(conn, "IDENTIFY_SYSTEM");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not identify system: %s\n"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) != 1)
{
fprintf(stderr, _("%s: could not identify system, got %i rows\n"),
progname, PQntuples(res));
disconnect_and_exit(1);
}
sysidentifier = strdup(PQgetvalue(res, 0, 0));
timeline = atoi(PQgetvalue(res, 0, 1));
PQclear(res);
/*
* Start the actual backup
*/
PQescapeStringConn(conn, escaped_label, label, sizeof(escaped_label), &i);
snprintf(current_path, sizeof(current_path), "BASE_BACKUP LABEL '%s' %s %s %s %s",
escaped_label,
showprogress ? "PROGRESS" : "",
includewal && !streamwal ? "WAL" : "",
fastcheckpoint ? "FAST" : "",
includewal ? "NOWAIT" : "");
if (PQsendQuery(conn, current_path) == 0)
{
fprintf(stderr, _("%s: could not send base backup command: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
/*
* Get the starting xlog position
*/
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not initiate base backup: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) != 1)
{
fprintf(stderr, _("%s: no start point returned from server\n"),
progname);
disconnect_and_exit(1);
}
strcpy(xlogstart, PQgetvalue(res, 0, 0));
if (verbose && includewal)
fprintf(stderr, "xlog start point: %s\n", xlogstart);
PQclear(res);
MemSet(xlogend, 0, sizeof(xlogend));
/*
* Get the header
*/
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not get backup header: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) < 1)
{
fprintf(stderr, _("%s: no data returned from server\n"), progname);
disconnect_and_exit(1);
}
/*
* Sum up the total size, for progress reporting
*/
totalsize = totaldone = 0;
tablespacecount = PQntuples(res);
for (i = 0; i < PQntuples(res); i++)
{
if (showprogress)
totalsize += atol(PQgetvalue(res, i, 2));
/*
* Verify tablespace directories are empty. Don't bother with the
* first once since it can be relocated, and it will be checked before
* we do anything anyway.
*/
if (format == 'p' && !PQgetisnull(res, i, 1))
verify_dir_is_empty_or_create(PQgetvalue(res, i, 1));
}
/*
* When writing to stdout, require a single tablespace
*/
if (format == 't' && strcmp(basedir, "-") == 0 && PQntuples(res) > 1)
{
fprintf(stderr, _("%s: can only write single tablespace to stdout, database has %d\n"),
progname, PQntuples(res));
disconnect_and_exit(1);
}
/*
* If we're streaming WAL, start the streaming session before we start
* receiving the actual data chunks.
*/
if (streamwal)
{
if (verbose)
fprintf(stderr, _("%s: starting background WAL receiver\n"),
progname);
StartLogStreamer(xlogstart, timeline, sysidentifier);
}
/*
* Start receiving chunks
*/
for (i = 0; i < PQntuples(res); i++)
{
if (format == 't')
ReceiveTarFile(conn, res, i);
else
ReceiveAndUnpackTarFile(conn, res, i);
} /* Loop over all tablespaces */
if (showprogress)
{
progress_report(PQntuples(res), NULL);
fprintf(stderr, "\n"); /* Need to move to next line */
}
PQclear(res);
/*
* Get the stop position
*/
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not get WAL end position from server\n"),
progname);
disconnect_and_exit(1);
}
if (PQntuples(res) != 1)
{
fprintf(stderr, _("%s: no WAL end position returned from server\n"),
progname);
disconnect_and_exit(1);
}
strcpy(xlogend, PQgetvalue(res, 0, 0));
if (verbose && includewal)
fprintf(stderr, "xlog end point: %s\n", xlogend);
PQclear(res);
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, _("%s: final receive failed: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (bgchild > 0)
{
#ifndef WIN32
int status;
int r;
#else
DWORD status;
#endif
if (verbose)
fprintf(stderr, _("%s: waiting for background process to finish streaming...\n"), progname);
#ifndef WIN32
if (pipewrite(bgpipe[1], xlogend, strlen(xlogend)) != strlen(xlogend))
{
fprintf(stderr, _("%s: could not send command to background pipe: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
/* Just wait for the background process to exit */
r = waitpid(bgchild, &status, 0);
if (r == -1)
{
fprintf(stderr, _("%s: could not wait for child process: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
if (r != bgchild)
{
fprintf(stderr, _("%s: child %i died, expected %i\n"),
progname, r, bgchild);
disconnect_and_exit(1);
}
if (!WIFEXITED(status))
{
fprintf(stderr, _("%s: child process did not exit normally\n"),
progname);
disconnect_and_exit(1);
}
if (WEXITSTATUS(status) != 0)
{
fprintf(stderr, _("%s: child process exited with error %i\n"),
progname, WEXITSTATUS(status));
disconnect_and_exit(1);
}
/* Exited normally, we're happy! */
#else /* WIN32 */
/*
* On Windows, since we are in the same process, we can just store the
* value directly in the variable, and then set the flag that says
* it's there.
*/
if (sscanf(xlogend, "%X/%X", &xlogendptr.xlogid, &xlogendptr.xrecoff) != 2)
{
fprintf(stderr, _("%s: could not parse xlog end position \"%s\"\n"),
progname, xlogend);
exit(1);
}
InterlockedIncrement(&has_xlogendptr);
/* First wait for the thread to exit */
if (WaitForSingleObjectEx((HANDLE) bgchild, INFINITE, FALSE) != WAIT_OBJECT_0)
{
_dosmaperr(GetLastError());
fprintf(stderr, _("%s: could not wait for child thread: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
if (GetExitCodeThread((HANDLE) bgchild, &status) == 0)
{
_dosmaperr(GetLastError());
fprintf(stderr, _("%s: could not get child thread exit status: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
if (status != 0)
{
fprintf(stderr, _("%s: child thread exited with error %u\n"),
progname, (unsigned int) status);
disconnect_and_exit(1);
}
/* Exited normally, we're happy */
#endif
}
/*
* End of copy data. Final result is already checked inside the loop.
*/
PQfinish(conn);
if (verbose)
fprintf(stderr, "%s: base backup completed\n", progname);
}
static int submit_query(db_con_t* _h, const char* _s)
{
int rv;
/*
** this bit of nonsense in case our connection get screwed up
*/
switch(rv = PQstatus(CON_CONNECTION(_h)))
{
case CONNECTION_OK: break;
case CONNECTION_BAD:
PLOG("submit_query", "connection reset");
PQreset(CON_CONNECTION(_h));
break;
}
/*
** free any previous query that is laying about
*/
if(CON_RESULT(_h))
{
free_query(_h);
}
/*
** exec the query
*/
CON_RESULT(_h) = PQexec(CON_CONNECTION(_h), _s);
rv = 0;
if(PQresultStatus(CON_RESULT(_h)) == 0)
{
PLOG("submit_query", "initial failure, FATAL");
/*
** terrible error??
*/
rv = -3;
}
else
{
/*
** the query ran, get the status
*/
switch(PQresultStatus(CON_RESULT(_h)))
{
case PGRES_EMPTY_QUERY: rv = -9; break;
case PGRES_COMMAND_OK: rv = 0; break;
case PGRES_TUPLES_OK: rv = 0; break;
case PGRES_COPY_OUT: rv = -4; break;
case PGRES_COPY_IN: rv = -5; break;
case PGRES_BAD_RESPONSE: rv = -6; break;
case PGRES_NONFATAL_ERROR: rv = -7; break;
case PGRES_FATAL_ERROR: rv = -8; break;
default: rv = -2; break;
}
}
if(rv < 0)
{
/*
** log the error
*/
char buf[256];
sprintf(buf, "query '%s', result '%s'\n",
_s, PQerrorMessage(CON_CONNECTION(_h)));
PLOG("submit_query", buf);
}
return(rv);
}
int
main(int argc, char *argv[])
{
static struct option long_options[] = {
{"list", no_argument, NULL, 'l'},
{"host", required_argument, NULL, 'h'},
{"port", required_argument, NULL, 'p'},
{"username", required_argument, NULL, 'U'},
{"no-password", no_argument, NULL, 'w'},
{"password", no_argument, NULL, 'W'},
{"dbname", required_argument, NULL, 'd'},
{"echo", no_argument, NULL, 'e'},
{NULL, 0, NULL, 0}
};
const char *progname;
int optindex;
int c;
bool listlangs = false;
const char *dbname = NULL;
char *host = NULL;
char *port = NULL;
char *username = NULL;
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
char *langname = NULL;
char *p;
PQExpBufferData sql;
PGconn *conn;
PGresult *result;
progname = get_progname(argv[0]);
set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
handle_help_version_opts(argc, argv, "createlang", help);
while ((c = getopt_long(argc, argv, "lh:p:U:wWd:e", long_options, &optindex)) != -1)
{
switch (c)
{
case 'l':
listlangs = true;
break;
case 'h':
host = optarg;
break;
case 'p':
port = optarg;
break;
case 'U':
username = optarg;
break;
case 'w':
prompt_password = TRI_NO;
break;
case 'W':
prompt_password = TRI_YES;
break;
case 'd':
dbname = optarg;
break;
case 'e':
echo = true;
break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
}
}
if (argc - optind > 0)
{
if (listlangs)
dbname = argv[optind++];
else
{
langname = argv[optind++];
if (argc - optind > 0)
dbname = argv[optind++];
}
}
if (argc - optind > 0)
{
fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
progname, argv[optind]);
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
}
if (dbname == NULL)
{
if (getenv("PGDATABASE"))
dbname = getenv("PGDATABASE");
else if (getenv("PGUSER"))
dbname = getenv("PGUSER");
else
dbname = get_user_name(progname);
}
initPQExpBuffer(&sql);
/*
* List option
*/
if (listlangs)
{
printQueryOpt popt;
static const bool translate_columns[] = {false, true};
conn = connectDatabase(dbname, host, port, username, prompt_password,
progname);
printfPQExpBuffer(&sql, "SELECT lanname as \"%s\", "
"(CASE WHEN lanpltrusted THEN '%s' ELSE '%s' END) as \"%s\" "
"FROM pg_catalog.pg_language WHERE lanispl;",
gettext_noop("Name"),
gettext_noop("yes"), gettext_noop("no"),
gettext_noop("Trusted?"));
result = executeQuery(conn, sql.data, progname, echo);
memset(&popt, 0, sizeof(popt));
popt.topt.format = PRINT_ALIGNED;
popt.topt.border = 1;
popt.topt.start_table = true;
popt.topt.stop_table = true;
popt.topt.encoding = PQclientEncoding(conn);
popt.title = _("Procedural Languages");
popt.translate_header = true;
popt.translate_columns = translate_columns;
printQuery(result, &popt, stdout, NULL);
PQfinish(conn);
exit(0);
}
if (langname == NULL)
{
fprintf(stderr, _("%s: missing required argument language name\n"), progname);
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
}
for (p = langname; *p; p++)
if (*p >= 'A' && *p <= 'Z')
*p += ('a' - 'A');
conn = connectDatabase(dbname, host, port, username, prompt_password, progname);
/*
* Make sure the language isn't already installed
*/
printfPQExpBuffer(&sql,
"SELECT oid FROM pg_catalog.pg_language WHERE lanname = '%s';",
langname);
result = executeQuery(conn, sql.data, progname, echo);
if (PQntuples(result) > 0)
{
PQfinish(conn);
fprintf(stderr,
_("%s: language \"%s\" is already installed in database \"%s\"\n"),
progname, langname, dbname);
/* separate exit status for "already installed" */
exit(2);
}
PQclear(result);
printfPQExpBuffer(&sql, "CREATE LANGUAGE \"%s\";\n", langname);
if (echo)
printf("%s", sql.data);
result = PQexec(conn, sql.data);
if (PQresultStatus(result) != PGRES_COMMAND_OK)
{
fprintf(stderr, _("%s: language installation failed: %s"),
progname, PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
PQclear(result);
PQfinish(conn);
exit(0);
}
/*****************************************************
* \brief Read a natural block of raster band data
*****************************************************/
CPLErr PostGISRasterTileRasterBand::IReadBlock(CPL_UNUSED int nBlockXOff,
CPL_UNUSED int nBlockYOff,
void * pImage)
{
CPLString osCommand;
PGresult * poResult = NULL;
int nWKBLength = 0;
int nPixelSize = GDALGetDataTypeSize(eDataType)/8;
PostGISRasterTileDataset * poRTDS =
(PostGISRasterTileDataset *)poDS;
// Get by PKID
if (poRTDS->poRDS->pszPrimaryKeyName) {
osCommand.Printf("select st_band(%s, %d) from %s.%s where "
"%s = '%s'", poRTDS->poRDS->pszColumn, nBand, poRTDS->poRDS->pszSchema, poRTDS->poRDS->pszTable,
poRTDS->poRDS->pszPrimaryKeyName, poRTDS->pszPKID);
}
// Get by upperleft
else {
CPLLocaleC oCLocale; // Force C locale to avoid commas instead of decimal points (for QGIS e.g.)
osCommand.Printf("select st_band(%s, %d) from %s.%s where "
"abs(ST_UpperLeftX(%s) - %.8f) < 1e-8 and abs(ST_UpperLeftY(%s) - %.8f) < 1e-8",
poRTDS->poRDS->pszColumn, nBand, poRTDS->poRDS->pszSchema, poRTDS->poRDS->pszTable, poRTDS->poRDS->pszColumn,
poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_X], poRTDS->poRDS->pszColumn,
poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_Y]);
}
poResult = PQexec(poRTDS->poRDS->poConn, osCommand.c_str());
#ifdef DEBUG_QUERY
CPLDebug("PostGIS_Raster", "PostGISRasterTileRasterBand::IReadBlock(): "
"Query = \"%s\" --> number of rows = %d",
osCommand.c_str(), poResult ? PQntuples(poResult) : 0 );
#endif
if (poResult == NULL ||
PQresultStatus(poResult) != PGRES_TUPLES_OK ||
PQntuples(poResult) <= 0) {
if (poResult)
PQclear(poResult);
ReportError(CE_Failure, CPLE_AppDefined,
"Error getting block of data (upperpixel = %f, %f)",
poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_X],
poRTDS->adfGeoTransform[GEOTRSFRM_TOPLEFT_Y]);
return CE_Failure;
}
// TODO: Check this
if (bIsOffline) {
CPLError(CE_Failure, CPLE_AppDefined, "This raster has outdb "
"storage. This feature isn't still available");
PQclear(poResult);
return CE_Failure;
}
/* Copy only data size, without payload */
int nExpectedDataSize =
nBlockXSize * nBlockYSize * nPixelSize;
GByte * pbyData = CPLHexToBinary(PQgetvalue(poResult, 0, 0),
&nWKBLength);
int nExpectedWKBLength = RASTER_HEADER_SIZE + BAND_SIZE(nPixelSize, nExpectedDataSize);
CPLErr eRet = CE_None;
if( nWKBLength != nExpectedWKBLength )
{
CPLDebug("PostGIS_Raster", "nWKBLength=%d, nExpectedWKBLength=%d", nWKBLength, nExpectedWKBLength );
eRet = CE_Failure;
}
else
{
GByte * pbyDataToRead =
(GByte*)GET_BAND_DATA(pbyData,1, nPixelSize,
nExpectedDataSize);
// Do byte-swapping if necessary */
int bIsLittleEndian = (pbyData[0] == 1);
#ifdef CPL_LSB
int bSwap = !bIsLittleEndian;
#else
int bSwap = bIsLittleEndian;
#endif
if( bSwap && nPixelSize > 1 )
{
GDALSwapWords( pbyDataToRead, nPixelSize,
nBlockXSize * nBlockYSize,
nPixelSize );
}
memcpy(pImage, pbyDataToRead, nExpectedDataSize);
}
CPLFree(pbyData);
PQclear(poResult);
return eRet;
}
int main(int argc, char *argv[]) {
/* First, we need to take in input from the items file. */
if (argc != 6) {
printf("Usage: workload total alpha beta gamma delta\n");
printf("The values for alpha, beta, gamma and delta need to be integers that sum to 100.\n");
exit(0);
}
int i, j, k, numrecs;
// These are the parameters that come from the user.
int total, nalpha, nbeta, ngamma, ndelta;
// These represent thresholds.
int talpha, tbeta, tgamma, tdelta;
// These are derived parameters from the database.
int alphacells, betacells, gammacells, deltacells;
char **recs;
PGconn *psql;
AttributeInfo *head_alpha, *head_beta, *head_gamma, *head_delta;
AttributeInfo *tail_alpha, *tail_beta, *tail_gamma, *tail_delta;
AttributeInfo **alpha, **beta, **gamma, **delta;
head_alpha = NULL; head_beta = NULL; head_gamma = NULL; head_delta = NULL;
tail_alpha = NULL; tail_beta = NULL; tail_gamma = NULL; tail_delta = NULL;
// Storing our parameters.
total = atoi(argv[1]);
nalpha = atoi(argv[2]);
nbeta = atoi(argv[3]);
ngamma = atoi(argv[4]);
ndelta = atoi(argv[5]);
// Establish thresholds for our RNG.
tdelta = 100 - ndelta;
tgamma = tdelta - ngamma;
tbeta = tgamma - nbeta;
talpha = 0;
if (nalpha+nbeta+ngamma+ndelta != 100) {
printf("The values for alpha, beta, gamma and delta need to be integers that sum to 100.\n");
exit(0);
}
// Seeding our RNG.
srand(time(NULL));
// We start off by getting a recommender list.
recs = recommenderList(&numrecs);
printf("Numrecs: %d\n",numrecs);
/* Connect to the database. */
psql = PQconnectdb("host = 'localhost' port = '5432' dbname = 'recathon'");
if (PQstatus(psql) != CONNECTION_OK) printf("bad conn\n");
printf("%s, %s, %s, %s, %s\n",PQdb(psql), PQuser(psql), PQpass(psql), PQhost(psql), PQport(psql));
if (psql == NULL) printf("connection failed\n");
// Next, we need to query the index of each recommender, to get the attribute information and
// cell types.
for (i = 0; i < numrecs; i++) {
char *querystring, *celltype;
PGresult *query;
int rows, cols;
AttributeInfo *newatt;
querystring = (char*) malloc(1024*sizeof(char));
// Since we don't know all of the attributes, we need to request everything.
sprintf(querystring,"select * from %sindex;",recs[i]);
query = PQexec(psql,querystring);
rows = PQntuples(query);
cols = PQnfields(query);
// A new AttributeInfo for each row.
for (j = 0; j < rows; j++) {
// Get query information. Cell type is attribute #8. Recommender-specific
// attributes begin at #13.
newatt = (AttributeInfo*) malloc(sizeof(AttributeInfo));
newatt->next = NULL;
newatt->recname = (char*) malloc(128*sizeof(char));
sprintf(newatt->recname,"%s",recs[i]);
newatt->numatts = cols - 12;
newatt->attnames = (char**) malloc(newatt->numatts*sizeof(char*));
for (k = 0; k < newatt->numatts; k++)
newatt->attnames[k] = (char*) malloc(64*sizeof(char));
newatt->attvalues = (char**) malloc(newatt->numatts*sizeof(char*));
for (k = 0; k < newatt->numatts; k++)
newatt->attvalues[k] = (char*) malloc(64*sizeof(char));
celltype = PQgetvalue(query,j,7);
if (strcmp(celltype,"Alpha") == 0)
newatt->celltype = CELL_ALPHA;
else if (strcmp(celltype,"Beta") == 0)
newatt->celltype = CELL_BETA;
else if (strcmp(celltype,"Gamma") == 0)
newatt->celltype = CELL_GAMMA;
else
newatt->celltype = CELL_DELTA;
// Get column information.
for (k = 0; k < cols-12; k++) {
sprintf(newatt->attnames[k],"%s",PQfname(query,k+12));
sprintf(newatt->attvalues[k],"%s",PQgetvalue(query,j,k+12));
}
// With the item complete, we put it into the appropriate bucket.
switch (newatt->celltype) {
case CELL_ALPHA:
if (!head_alpha) {
head_alpha = newatt;
tail_alpha = newatt;
} else {
tail_alpha->next = newatt;
tail_alpha = newatt;
}
break;
case CELL_BETA:
if (!head_beta) {
head_beta = newatt;
tail_beta = newatt;
} else {
tail_beta->next = newatt;
tail_beta = newatt;
}
break;
case CELL_GAMMA:
if (!head_gamma) {
head_gamma = newatt;
tail_gamma = newatt;
} else {
tail_gamma->next = newatt;
tail_gamma = newatt;
}
break;
default:
if (!head_delta) {
head_delta = newatt;
tail_delta = newatt;
} else {
tail_delta->next = newatt;
tail_delta = newatt;
}
break;
}
}
PQclear(query);
free(querystring);
}
// For easy randomization, we should flatten our AttributeInfo lists.
alpha = flatten(head_alpha, &alphacells);
beta = flatten(head_beta, &betacells);
gamma = flatten(head_gamma, &gammacells);
delta = flatten(head_delta, &deltacells);
// DEBUG: loop through the lists of alpha/beta/gamma/delta cells and print info.
if (DEBUG) {
printf("--- ALPHA CELLS ---\n");
printCellList(alpha, alphacells);
printf("--- BETA CELLS ---\n");
printCellList(beta, betacells);
printf("--- GAMMA CELLS ---\n");
printCellList(gamma, gammacells);
printf("--- DELTA CELLS ---\n");
printCellList(delta, deltacells);
}
// One more thing we need to do is obtain a list of users that will work for
// each AttributeInfo. We can semi-randomize by sorting based on zip code.
addUsers(alpha, alphacells, psql);
addUsers(beta, betacells, psql);
addUsers(gamma, gammacells, psql);
addUsers(delta, deltacells, psql);
// Now to issue the given number of queries, with the frequencies established
// probabilistically.
for (i = 0; i < total; i++) {
int randnum, randatt, randuser, userid;
recathon_cell celltype;
bool valid = false;
PGresult *workquery;
char *qstring;
AttributeInfo *queryatt;
// It's possible one of our buckets will have nothing in it, so
// we need to continue choosing until we get something valid.
while (!valid) {
// A RNG chooses which kind of cell we work with.
randnum = rand() % 100;
if (randnum < tbeta) {
if (alphacells > 0) {
valid = true;
celltype = CELL_ALPHA;
}
}
else if (randnum < tgamma) {
if (betacells > 0) {
valid = true;
celltype = CELL_BETA;
}
}
else if (randnum < tdelta) {
if (gammacells > 0) {
valid = true;
celltype = CELL_GAMMA;
}
}
else {
if (deltacells > 0) {
valid = true;
celltype = CELL_DELTA;
}
}
}
// Depending on our cell type, we'll have a different set of possible
// queries to issue; we can choose from the alpha, beta, gamma or delta
// buckets. Which item we get is also random.
switch (celltype) {
case CELL_ALPHA:
randatt = rand() % alphacells;
queryatt = alpha[randatt];
break;
case CELL_BETA:
randatt = rand() % betacells;
queryatt = beta[randatt];
break;
case CELL_GAMMA:
randatt = rand() % gammacells;
queryatt = gamma[randatt];
break;
default:
randatt = rand() % deltacells;
queryatt = delta[randatt];
break;
}
randuser = rand() % 10;
userid = queryatt->valid_users[randuser];
qstring = (char*) malloc(1024*sizeof(char));
sprintf(qstring,"select itemid from %s recommend(10) userid=%d",queryatt->recname,userid);
if (queryatt->numatts > 0) {
strncat(qstring," and ",5);
for (j = 0; j < queryatt->numatts; j++) {
char addition[128];
sprintf(addition,"%s = '%s' ",
queryatt->attnames[j],queryatt->attvalues[j]);
strncat(qstring,addition,strlen(addition));
if (j+1 < queryatt->numatts)
strncat(qstring,"and ",5);
}
}
strncat(qstring,";",1);
workquery = PQexec(psql,qstring);
PQclear(workquery);
free(qstring);
}
PQfinish(psql);
}
/**
\brief Add a new license to license_ref table
Adds a license to license_ref table.
@param licenseName Name of license
@return rf_pk for success, 0 for failure
*/
FUNCTION long add2license_ref(char *licenseName)
{
PGresult *result;
char query[myBUFSIZ];
char insert[myBUFSIZ];
char escLicName[myBUFSIZ];
char *specialLicenseText;
long rf_pk;
int len;
int error;
int numRows;
// escape the name
len = strlen(licenseName);
PQescapeStringConn(gl.pgConn, escLicName, licenseName, len, &error);
if (error)
LOG_WARNING("Does license name %s have multibyte encoding?", licenseName)
/* verify the license is not already in the table */
sprintf(query, "SELECT rf_pk FROM " LICENSE_REF_TABLE " where rf_shortname='%s'", escLicName);
result = PQexec(gl.pgConn, query);
if (fo_checkPQresult(gl.pgConn, result, query, __FILE__, __LINE__))
return 0;
numRows = PQntuples(result);
if (numRows)
{
rf_pk = atol(PQgetvalue(result, 0, 0));
PQclear(result);
return rf_pk;
}
PQclear(result);
/* Insert the new license */
specialLicenseText = "License by Nomos.";
sprintf(insert, "insert into license_ref(rf_shortname, rf_text, rf_detector_type) values('%s', '%s', 2)", escLicName,
specialLicenseText);
result = PQexec(gl.pgConn, insert);
// ignore duplicate constraint failure (23505), report others
if ((result == 0)
|| ((PQresultStatus(result) != PGRES_COMMAND_OK)
&& (strncmp(PG_ERRCODE_UNIQUE_VIOLATION, PQresultErrorField(result, PG_DIAG_SQLSTATE), 5))))
{
printf("ERROR: %s(%d): Nomos failed to add a new license. %s/n: %s/n",
__FILE__, __LINE__, PQresultErrorMessage(result), insert);
PQclear(result);
return (0);
}
PQclear(result);
/* retrieve the new rf_pk */
result = PQexec(gl.pgConn, query);
if (fo_checkPQresult(gl.pgConn, result, query, __FILE__, __LINE__))
return 0;
numRows = PQntuples(result);
if (numRows)
rf_pk = atol(PQgetvalue(result, 0, 0));
else
{
printf("ERROR: %s:%s:%d Just inserted value is missing. On: %s", __FILE__, "add2license_ref()", __LINE__, query);
PQclear(result);
return (0);
}
PQclear(result);
return (rf_pk);
}
static void
BaseBackup(void)
{
PGresult *res;
char *sysidentifier;
uint32 latesttli;
uint32 starttli;
char current_path[MAXPGPATH];
char escaped_label[MAXPGPATH];
int i;
char xlogstart[64];
char xlogend[64];
int minServerMajor,
maxServerMajor;
int serverMajor;
/*
* Connect in replication mode to the server
*/
conn = GetConnection();
if (!conn)
/* Error message already written in GetConnection() */
exit(1);
/*
* Check server version. BASE_BACKUP command was introduced in 9.1, so we
* can't work with servers older than 9.1.
*/
minServerMajor = 901;
maxServerMajor = PG_VERSION_NUM / 100;
serverMajor = PQserverVersion(conn) / 100;
if (serverMajor < minServerMajor || serverMajor > maxServerMajor)
{
const char *serverver = PQparameterStatus(conn, "server_version");
fprintf(stderr, _("%s: incompatible server version %s\n"),
progname, serverver ? serverver : "'unknown'");
disconnect_and_exit(1);
}
/*
* If WAL streaming was requested, also check that the server is new
* enough for that.
*/
if (streamwal && !CheckServerVersionForStreaming(conn))
{
/* Error message already written in CheckServerVersionForStreaming() */
disconnect_and_exit(1);
}
/*
* Build contents of recovery.conf if requested
*/
if (writerecoveryconf)
GenerateRecoveryConf(conn);
/*
* Run IDENTIFY_SYSTEM so we can get the timeline
*/
res = PQexec(conn, "IDENTIFY_SYSTEM");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not send replication command \"%s\": %s"),
progname, "IDENTIFY_SYSTEM", PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) != 1 || PQnfields(res) != 3)
{
fprintf(stderr,
_("%s: could not identify system: got %d rows and %d fields, expected %d rows and %d fields\n"),
progname, PQntuples(res), PQnfields(res), 1, 3);
disconnect_and_exit(1);
}
sysidentifier = pg_strdup(PQgetvalue(res, 0, 0));
latesttli = atoi(PQgetvalue(res, 0, 1));
PQclear(res);
/*
* Start the actual backup
*/
PQescapeStringConn(conn, escaped_label, label, sizeof(escaped_label), &i);
snprintf(current_path, sizeof(current_path),
"BASE_BACKUP LABEL '%s' %s %s %s %s",
escaped_label,
showprogress ? "PROGRESS" : "",
includewal && !streamwal ? "WAL" : "",
fastcheckpoint ? "FAST" : "",
includewal ? "NOWAIT" : "");
if (PQsendQuery(conn, current_path) == 0)
{
fprintf(stderr, _("%s: could not send replication command \"%s\": %s"),
progname, "BASE_BACKUP", PQerrorMessage(conn));
disconnect_and_exit(1);
}
/*
* Get the starting xlog position
*/
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not initiate base backup: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) != 1)
{
fprintf(stderr,
_("%s: server returned unexpected response to BASE_BACKUP command; got %d rows and %d fields, expected %d rows and %d fields\n"),
progname, PQntuples(res), PQnfields(res), 1, 2);
disconnect_and_exit(1);
}
strcpy(xlogstart, PQgetvalue(res, 0, 0));
/*
* 9.3 and later sends the TLI of the starting point. With older servers,
* assume it's the same as the latest timeline reported by
* IDENTIFY_SYSTEM.
*/
if (PQnfields(res) >= 2)
starttli = atoi(PQgetvalue(res, 0, 1));
else
starttli = latesttli;
PQclear(res);
MemSet(xlogend, 0, sizeof(xlogend));
if (verbose && includewal)
fprintf(stderr, _("transaction log start point: %s on timeline %u\n"),
xlogstart, starttli);
/*
* Get the header
*/
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, _("%s: could not get backup header: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) < 1)
{
fprintf(stderr, _("%s: no data returned from server\n"), progname);
disconnect_and_exit(1);
}
/*
* Sum up the total size, for progress reporting
*/
totalsize = totaldone = 0;
tablespacecount = PQntuples(res);
for (i = 0; i < PQntuples(res); i++)
{
if (showprogress)
totalsize += atol(PQgetvalue(res, i, 2));
/*
* Verify tablespace directories are empty. Don't bother with the
* first once since it can be relocated, and it will be checked before
* we do anything anyway.
*/
if (format == 'p' && !PQgetisnull(res, i, 1))
verify_dir_is_empty_or_create(PQgetvalue(res, i, 1));
}
/*
* When writing to stdout, require a single tablespace
*/
if (format == 't' && strcmp(basedir, "-") == 0 && PQntuples(res) > 1)
{
fprintf(stderr,
_("%s: can only write single tablespace to stdout, database has %d\n"),
progname, PQntuples(res));
disconnect_and_exit(1);
}
/*
* If we're streaming WAL, start the streaming session before we start
* receiving the actual data chunks.
*/
if (streamwal)
{
if (verbose)
fprintf(stderr, _("%s: starting background WAL receiver\n"),
progname);
StartLogStreamer(xlogstart, starttli, sysidentifier);
}
/*
* Start receiving chunks
*/
for (i = 0; i < PQntuples(res); i++)
{
if (format == 't')
ReceiveTarFile(conn, res, i);
else
ReceiveAndUnpackTarFile(conn, res, i);
} /* Loop over all tablespaces */
if (showprogress)
{
progress_report(PQntuples(res), NULL);
fprintf(stderr, "\n"); /* Need to move to next line */
}
PQclear(res);
/*
* Get the stop position
*/
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr,
_("%s: could not get transaction log end position from server: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (PQntuples(res) != 1)
{
fprintf(stderr,
_("%s: no transaction log end position returned from server\n"),
progname);
disconnect_and_exit(1);
}
strcpy(xlogend, PQgetvalue(res, 0, 0));
if (verbose && includewal)
fprintf(stderr, "transaction log end point: %s\n", xlogend);
PQclear(res);
res = PQgetResult(conn);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, _("%s: final receive failed: %s"),
progname, PQerrorMessage(conn));
disconnect_and_exit(1);
}
if (bgchild > 0)
{
#ifndef WIN32
int status;
int r;
#else
DWORD status;
uint32 hi,
lo;
#endif
if (verbose)
fprintf(stderr,
_("%s: waiting for background process to finish streaming ...\n"), progname);
#ifndef WIN32
if (write(bgpipe[1], xlogend, strlen(xlogend)) != strlen(xlogend))
{
fprintf(stderr,
_("%s: could not send command to background pipe: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
/* Just wait for the background process to exit */
r = waitpid(bgchild, &status, 0);
if (r == -1)
{
fprintf(stderr, _("%s: could not wait for child process: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
if (r != bgchild)
{
fprintf(stderr, _("%s: child %d died, expected %d\n"),
progname, r, (int) bgchild);
disconnect_and_exit(1);
}
if (!WIFEXITED(status))
{
fprintf(stderr, _("%s: child process did not exit normally\n"),
progname);
disconnect_and_exit(1);
}
if (WEXITSTATUS(status) != 0)
{
fprintf(stderr, _("%s: child process exited with error %d\n"),
progname, WEXITSTATUS(status));
disconnect_and_exit(1);
}
/* Exited normally, we're happy! */
#else /* WIN32 */
/*
* On Windows, since we are in the same process, we can just store the
* value directly in the variable, and then set the flag that says
* it's there.
*/
if (sscanf(xlogend, "%X/%X", &hi, &lo) != 2)
{
fprintf(stderr,
_("%s: could not parse transaction log location \"%s\"\n"),
progname, xlogend);
disconnect_and_exit(1);
}
xlogendptr = ((uint64) hi) << 32 | lo;
InterlockedIncrement(&has_xlogendptr);
/* First wait for the thread to exit */
if (WaitForSingleObjectEx((HANDLE) bgchild, INFINITE, FALSE) !=
WAIT_OBJECT_0)
{
_dosmaperr(GetLastError());
fprintf(stderr, _("%s: could not wait for child thread: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
if (GetExitCodeThread((HANDLE) bgchild, &status) == 0)
{
_dosmaperr(GetLastError());
fprintf(stderr, _("%s: could not get child thread exit status: %s\n"),
progname, strerror(errno));
disconnect_and_exit(1);
}
if (status != 0)
{
fprintf(stderr, _("%s: child thread exited with error %u\n"),
progname, (unsigned int) status);
disconnect_and_exit(1);
}
/* Exited normally, we're happy */
#endif
}
/* Free the recovery.conf contents */
destroyPQExpBuffer(recoveryconfcontents);
/*
* End of copy data. Final result is already checked inside the loop.
*/
PQclear(res);
PQfinish(conn);
if (verbose)
fprintf(stderr, "%s: base backup completed\n", progname);
}
void load_TUBii_command(client *c, int argc, sds *argv)
{
/* Load CAEN hardware settings from the database. */
uint32_t key;
PGconn *conn;
PGresult *res = NULL;
char conninfo[1024];
char command[10000];
char *name, *value_str;
uint32_t value;
int i;
int rows;
if (safe_strtoul(argv[1], &key)) {
addReplyErrorFormat(c, "'%s' is not a valid uint32_t", argv[1]);
return;
}
sprintf(command, "select * from TUBii where key = %i", key);
sprintf(conninfo, "dbname=%s host=%s user=%s password=%s", dbconfig.name, dbconfig.host, dbconfig.user, dbconfig.password);
/* Request row from the database. */
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK) {
addReplyErrorFormat(c, "connection to database failed: %s",
PQerrorMessage(conn));
goto pq_error;
}
res = PQexec(conn, command);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
addReplyErrorFormat(c, "select command failed: %s",
PQerrorMessage(conn));
goto pq_error;
}
rows = PQntuples(res);
if (rows != 1) {
if (rows == 0) {
addReplyErrorFormat(c, "no database row with key = %i", key);
} else {
addReplyError(c, "this should never happen. Call Tony");
}
goto pq_error;
}
for (i = 0; i < PQnfields(res); i++) {
name = PQfname(res, i);
if (!strcmp(name, "key") || !strcmp(name, "timestamp")) continue;
value_str = PQgetvalue(res, 0, i);
if (safe_strtoul(value_str, &value)) {
addReplyErrorFormat(c, "unable to convert value '%s' for field %s",
value_str, name);
goto pq_error;
}
if (!strcmp(name, "control_reg")) {
ControlReg(value);
} else if (!strcmp(name, "trigger_mask")) {
triggerMask(value,0);
} else if (!strcmp(name, "speaker_mask")) {
speakerMask(value);
} else if (!strcmp(name, "counter_mask")) {
counterMask(value);
} else if (!strcmp(name, "caen_gain_reg")) {
CAENWords(value, mReadReg((u32) MappedRegsBaseAddress, RegOffset12));
} else if (!strcmp(name, "caen_channel_reg")) {
CAENWords(mReadReg((u32) MappedRegsBaseAddress, RegOffset11), value);
} else if (!strcmp(name, "lockout_reg")) {
GTDelays(value, mReadReg((u32) MappedRegsBaseAddress, RegOffset15));
} else if (!strcmp(name, "dgt_reg")) {
GTDelays(mReadReg((u32) MappedRegsBaseAddress, RegOffset14), value);
} else if (!strcmp(name, "dac_reg")) {
DACThresholds(value);
} else if (!strcmp(name, "counter_mode")) {
counterMode(value);
} else if (!strcmp(name, "clock_status")) {
// Do Nowt
} else if (!strcmp(name, "combo_enable_mask")) {
mWriteReg((u32) MappedComboBaseAddress, RegOffset2, value);
} else if (!strcmp(name, "combo_mask")) {
mWriteReg((u32) MappedComboBaseAddress, RegOffset3, value);
} else if (!strcmp(name, "prescale_value")) {
mWriteReg((u32) MappedPrescaleBaseAddress, RegOffset2, value);
} else if (!strcmp(name, "prescale_channel")) {
mWriteReg((u32) MappedPrescaleBaseAddress, RegOffset3, value);
} else if (!strcmp(name, "burst_rate")) {
mWriteReg((u32) MappedBurstBaseAddress, RegOffset2, value);
} else if (!strcmp(name, "burst_channel")) {
mWriteReg((u32) MappedBurstBaseAddress, RegOffset3, value);
} else {
addReplyErrorFormat(c, "got unknown field '%s'", name);
goto pq_error;
}
}
addReplyStatus(c, "OK");
PQclear(res);
PQfinish(conn);
return;
err:
addReplyError(c, tubii_err);
return;
pq_error:
if (res) PQclear(res);
PQfinish(conn);
}
/*
* ExecQueryUsingCursor: run a SELECT-like query using a cursor
*
* This feature allows result sets larger than RAM to be dealt with.
*
* Returns true if the query executed successfully, false otherwise.
*
* If pset.timing is on, total query time (exclusive of result-printing) is
* stored into *elapsed_msec.
*/
static bool
ExecQueryUsingCursor(const char *query, double *elapsed_msec)
{
bool OK = true;
PGresult *results;
PQExpBufferData buf;
printQueryOpt my_popt = pset.popt;
FILE *fout;
bool is_pipe;
bool is_pager = false;
bool started_txn = false;
int ntuples;
int fetch_count;
char fetch_cmd[64];
instr_time before,
after;
int flush_error;
*elapsed_msec = 0;
/* initialize print options for partial table output */
my_popt.topt.start_table = true;
my_popt.topt.stop_table = false;
my_popt.topt.prior_records = 0;
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
/* if we're not in a transaction, start one */
if (PQtransactionStatus(pset.db) == PQTRANS_IDLE)
{
results = PQexec(pset.db, "BEGIN");
OK = AcceptResult(results) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
PQclear(results);
if (!OK)
return false;
started_txn = true;
}
/* Send DECLARE CURSOR */
initPQExpBuffer(&buf);
appendPQExpBuffer(&buf, "DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s",
query);
results = PQexec(pset.db, buf.data);
OK = AcceptResult(results) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
PQclear(results);
termPQExpBuffer(&buf);
if (!OK)
goto cleanup;
if (pset.timing)
{
INSTR_TIME_SET_CURRENT(after);
INSTR_TIME_SUBTRACT(after, before);
*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
}
/*
* In \gset mode, we force the fetch count to be 2, so that we will throw
* the appropriate error if the query returns more than one row.
*/
if (pset.gset_prefix)
fetch_count = 2;
else
fetch_count = pset.fetch_count;
snprintf(fetch_cmd, sizeof(fetch_cmd),
"FETCH FORWARD %d FROM _psql_cursor",
fetch_count);
/* prepare to write output to \g argument, if any */
if (pset.gfname)
{
if (!openQueryOutputFile(pset.gfname, &fout, &is_pipe))
{
OK = false;
goto cleanup;
}
if (is_pipe)
disable_sigpipe_trap();
}
else
{
fout = pset.queryFout;
is_pipe = false; /* doesn't matter */
}
/* clear any pre-existing error indication on the output stream */
clearerr(fout);
for (;;)
{
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
/* get fetch_count tuples at a time */
results = PQexec(pset.db, fetch_cmd);
if (pset.timing)
{
INSTR_TIME_SET_CURRENT(after);
INSTR_TIME_SUBTRACT(after, before);
*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
}
if (PQresultStatus(results) != PGRES_TUPLES_OK)
{
/* shut down pager before printing error message */
if (is_pager)
{
ClosePager(fout);
is_pager = false;
}
OK = AcceptResult(results);
Assert(!OK);
PQclear(results);
break;
}
if (pset.gset_prefix)
{
/* StoreQueryTuple will complain if not exactly one row */
OK = StoreQueryTuple(results);
PQclear(results);
break;
}
ntuples = PQntuples(results);
if (ntuples < fetch_count)
{
/* this is the last result set, so allow footer decoration */
my_popt.topt.stop_table = true;
}
else if (fout == stdout && !is_pager)
{
/*
* If query requires multiple result sets, hack to ensure that
* only one pager instance is used for the whole mess
*/
fout = PageOutput(INT_MAX, &(my_popt.topt));
is_pager = true;
}
printQuery(results, &my_popt, fout, is_pager, pset.logfile);
PQclear(results);
/* after the first result set, disallow header decoration */
my_popt.topt.start_table = false;
my_popt.topt.prior_records += ntuples;
/*
* Make sure to flush the output stream, so intermediate results are
* visible to the client immediately. We check the results because if
* the pager dies/exits/etc, there's no sense throwing more data at
* it.
*/
flush_error = fflush(fout);
/*
* Check if we are at the end, if a cancel was pressed, or if there
* were any errors either trying to flush out the results, or more
* generally on the output stream at all. If we hit any errors
* writing things to the stream, we presume $PAGER has disappeared and
* stop bothering to pull down more data.
*/
if (ntuples < fetch_count || cancel_pressed || flush_error ||
ferror(fout))
break;
}
if (pset.gfname)
{
/* close \g argument file/pipe */
if (is_pipe)
{
pclose(fout);
restore_sigpipe_trap();
}
else
fclose(fout);
}
else if (is_pager)
{
/* close transient pager */
ClosePager(fout);
}
cleanup:
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
/*
* We try to close the cursor on either success or failure, but on failure
* ignore the result (it's probably just a bleat about being in an aborted
* transaction)
*/
results = PQexec(pset.db, "CLOSE _psql_cursor");
if (OK)
{
OK = AcceptResult(results) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
}
PQclear(results);
if (started_txn)
{
results = PQexec(pset.db, OK ? "COMMIT" : "ROLLBACK");
OK &= AcceptResult(results) &&
(PQresultStatus(results) == PGRES_COMMAND_OK);
PQclear(results);
}
if (pset.timing)
{
INSTR_TIME_SET_CURRENT(after);
INSTR_TIME_SUBTRACT(after, before);
*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
}
return OK;
}
int msPOSTGRESQLJoinConnect(layerObj *layer, joinObj *join)
{
char *maskeddata, *temp, *sql, *column;
char *conn_decrypted;
int i, test;
PGresult *query_result;
msPOSTGRESQLJoinInfo *joininfo;
if(join->joininfo)
return MS_SUCCESS;
joininfo = (msPOSTGRESQLJoinInfo *)malloc(sizeof(msPOSTGRESQLJoinInfo));
if(!joininfo) {
msSetError(MS_MEMERR, "Error allocating join info struct.",
"msPOSTGRESQLJoinConnect()");
return MS_FAILURE;
}
joininfo->conn = NULL;
joininfo->row_num = 0;
joininfo->query_result = NULL;
joininfo->from_index = 0;
joininfo->to_column = join->to;
joininfo->from_value = NULL;
joininfo->layer_debug = layer->debug;
join->joininfo = joininfo;
/*
* We need three things at a minimum, the connection string, a table
* name, and a column to join on.
*/
if(!join->connection) {
msSetError(MS_QUERYERR, "No connection information provided.",
"MSPOSTGRESQLJoinConnect()");
return MS_FAILURE;
}
if(!join->table) {
msSetError(MS_QUERYERR, "No join table name found.",
"msPOSTGRESQLJoinConnect()");
return MS_FAILURE;
}
if(!joininfo->to_column) {
msSetError(MS_QUERYERR, "No join to column name found.",
"msPOSTGRESQLJoinConnect()");
return MS_FAILURE;
}
/* Establish database connection */
conn_decrypted = msDecryptStringTokens(layer->map, join->connection);
if (conn_decrypted != NULL) {
joininfo->conn = PQconnectdb(conn_decrypted);
free(conn_decrypted);
}
if(!joininfo->conn || PQstatus(joininfo->conn) == CONNECTION_BAD) {
maskeddata = (char *)malloc(strlen(layer->connection) + 1);
strcpy(maskeddata, join->connection);
temp = strstr(maskeddata, "password="******"Unable to connect to PostgreSQL using the string %s.\n Error reported: %s\n",
"msPOSTGRESQLJoinConnect()",
maskeddata, PQerrorMessage(joininfo->conn));
free(maskeddata);
if(!joininfo->conn) {
free(joininfo->conn);
}
free(joininfo);
join->joininfo = NULL;
return MS_FAILURE;
}
/* Determine the number and names of columns in the join table. */
sql = (char *)malloc(36 + strlen(join->table) + 1);
sprintf(sql, "SELECT * FROM %s WHERE false LIMIT 0", join->table);
if(joininfo->layer_debug) {
msDebug("msPOSTGRESQLJoinConnect(): executing %s.\n", sql);
}
query_result = PQexec(joininfo->conn, sql);
if(!query_result || PQresultStatus(query_result) != PGRES_TUPLES_OK) {
msSetError(MS_QUERYERR, "Error determining join items: %s.",
"msPOSTGRESQLJoinConnect()", PQerrorMessage(joininfo->conn));
if(query_result) {
PQclear(query_result);
query_result = NULL;
}
free(sql);
return MS_FAILURE;
}
free(sql);
join->numitems = PQnfields(query_result);
join->items = malloc(sizeof(char *) * (join->numitems));
/* We want the join-to column to be first in the list. */
test = 1;
for(i = 0; i < join->numitems; i++) {
column = PQfname(query_result, i);
if(strcmp(column, joininfo->to_column) != 0) {
join->items[i + test] = (char *)malloc(strlen(column) + 1);
strcpy(join->items[i + test], column);
} else {
test = 0;
join->items[0] = (char *)malloc(strlen(column) + 1);
strcpy(join->items[0], column);
}
}
PQclear(query_result);
query_result = NULL;
if(test == 1) {
msSetError(MS_QUERYERR, "Unable to find join to column: %s",
"msPOSTGRESQLJoinConnect()", joininfo->to_column);
return MS_FAILURE;
}
if(joininfo->layer_debug) {
for(i = 0; i < join->numitems; i++) {
msDebug("msPOSTGRESQLJoinConnect(): Column %d named %s\n", i, join->items[i]);
}
}
/* Determine the index of the join from column. */
for(i = 0; i < layer->numitems; i++) {
if(strcasecmp(layer->items[i], join->from) == 0) {
joininfo->from_index = i;
break;
}
}
if(i == layer->numitems) {
msSetError(MS_JOINERR, "Item %s not found in layer %s.",
"msPOSTGRESQLJoinConnect()", join->from, layer->name);
return MS_FAILURE;
}
return MS_SUCCESS;
}
/*
* PSQLexecWatch
*
* This function is used for \watch command to send the query to
* the server and print out the results.
*
* Returns 1 if the query executed successfully, 0 if it cannot be repeated,
* e.g., because of the interrupt, -1 on error.
*/
int
PSQLexecWatch(const char *query, const printQueryOpt *opt)
{
PGresult *res;
double elapsed_msec = 0;
instr_time before;
instr_time after;
if (!pset.db)
{
psql_error("You are currently not connected to a database.\n");
return 0;
}
SetCancelConn();
if (pset.timing)
INSTR_TIME_SET_CURRENT(before);
res = PQexec(pset.db, query);
ResetCancelConn();
if (!AcceptResult(res))
{
PQclear(res);
return 0;
}
if (pset.timing)
{
INSTR_TIME_SET_CURRENT(after);
INSTR_TIME_SUBTRACT(after, before);
elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
}
/*
* If SIGINT is sent while the query is processing, the interrupt will be
* consumed. The user's intention, though, is to cancel the entire watch
* process, so detect a sent cancellation request and exit in this case.
*/
if (cancel_pressed)
{
PQclear(res);
return 0;
}
switch (PQresultStatus(res))
{
case PGRES_TUPLES_OK:
printQuery(res, opt, pset.queryFout, false, pset.logfile);
break;
case PGRES_COMMAND_OK:
fprintf(pset.queryFout, "%s\n%s\n\n", opt->title, PQcmdStatus(res));
break;
case PGRES_EMPTY_QUERY:
psql_error(_("\\watch cannot be used with an empty query\n"));
PQclear(res);
return -1;
case PGRES_COPY_OUT:
case PGRES_COPY_IN:
case PGRES_COPY_BOTH:
psql_error(_("\\watch cannot be used with COPY\n"));
PQclear(res);
return -1;
default:
psql_error(_("unexpected result status for \\watch\n"));
PQclear(res);
return -1;
}
PQclear(res);
fflush(pset.queryFout);
/* Possible microtiming output */
if (pset.timing)
printf(_("Time: %.3f ms\n"), elapsed_msec);
return 1;
}
bool PostgreSQLInterface::ExecuteBlockingCommand(const char *command, PGresult **result, bool rollbackOnFailure)
{
*result = PQexec(pgConn, command);
return IsResultSuccessful(*result, rollbackOnFailure);
}
/*
* handleCopyOut
* receives data as a result of a COPY ... TO STDOUT command
*
* conn should be a database connection that you just issued COPY TO on
* and got back a PGRES_COPY_OUT result.
* copystream is the file stream for the data to go to.
*
* result is true if successful, false if not.
*/
bool
handleCopyOut(PGconn *conn, FILE *copystream)
{
bool OK = true;
char *buf;
int ret;
PGresult *res;
for (;;)
{
ret = PQgetCopyData(conn, &buf, 0);
if (ret < 0)
break; /* done or error */
if (buf)
{
if (fwrite(buf, 1, ret, copystream) != ret)
{
if (OK) /* complain only once, keep reading data */
psql_error("could not write COPY data: %s\n",
strerror(errno));
OK = false;
}
PQfreemem(buf);
}
}
if (OK && fflush(copystream))
{
psql_error("could not write COPY data: %s\n",
strerror(errno));
OK = false;
}
if (ret == -2)
{
psql_error("COPY data transfer failed: %s", PQerrorMessage(conn));
OK = false;
}
/*
* Check command status and return to normal libpq state. After a
* client-side error, the server will remain ready to deliver data. The
* cleanest thing is to fully drain and discard that data. If the
* client-side error happened early in a large file, this takes a long
* time. Instead, take advantage of the fact that PQexec() will silently
* end any ongoing PGRES_COPY_OUT state. This does cause us to lose the
* results of any commands following the COPY in a single command string.
* It also only works for protocol version 3. XXX should we clean up
* using the slow way when the connection is using protocol version 2?
*
* We must not ever return with the status still PGRES_COPY_OUT. Our
* caller is unable to distinguish that situation from reaching the next
* COPY in a command string that happened to contain two consecutive COPY
* TO STDOUT commands. We trust that no condition can make PQexec() fail
* indefinitely while retaining status PGRES_COPY_OUT.
*/
while (res = PQgetResult(conn), PQresultStatus(res) == PGRES_COPY_OUT)
{
OK = false;
PQclear(res);
PQexec(conn, "-- clear PGRES_COPY_OUT state");
}
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
psql_error("%s", PQerrorMessage(conn));
OK = false;
}
PQclear(res);
return OK;
}