bool isOK() const { return (!closed_) && (PQstatus(conn_) != CONNECTION_BAD); }
main() { char *pghost, *pgport, *pgoptions, *pgtty; char *dbName; int nFields; int row, field; PGconn *conn; PGresult *res; int junk; char *field_name; int field_type; int WKB_OID; char conn_string[255]; bool *bool_val; int *int_val; float *float_val; double *double_val; char *char_val; char *wkb_val; char *table_name = "wkbreader_test"; char query_str[1000]; /* make a connection to the database */ conn = PQconnectdb(""); /* * check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); } //what is the geometry type's OID #? WKB_OID = find_WKB_typeid(conn); /* start a transaction block */ res = PQexec(conn, "BEGIN"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "%s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } /* * should PQclear PGresult whenever it is no longer needed to avoid * memory leaks */ PQclear(res); /* * fetch rows from the pg_database, the system catalog of * databases */ sprintf(query_str, "DECLARE mycursor BINARY CURSOR FOR select text(num), asbinary(the_geom,'ndr') as wkb from %s", table_name); printf(query_str); printf("\n"); res = PQexec(conn, query_str); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "DECLARE CURSOR command failed\n"); fprintf(stderr, "%s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn, "FETCH ALL in mycursor"); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "FETCH ALL command didn't return tuples properly\n"); fprintf(stderr, "%s", PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } for (row=0; row< PQntuples(res); row++) { printf("------------------------------row %i --------------------------\n",row); //not so efficient, but... for (field =0 ; field< PQnfields(res); field++) { field_type =PQftype(res,field); field_name = PQfname(res, field); //we just handle a few of the popular type since this is just an example if (field_type ==16)// bool { bool_val = (bool *) PQgetvalue(res, row, field); if (*bool_val) printf("%s: TRUE\n",field_name); else printf("%s: FALSE\n",field_name); } else if (field_type ==23 )//int4 (int) { int_val = (int *) PQgetvalue(res, row, field); printf("%s: %i\n",field_name,*int_val); } else if (field_type ==700 )//float4 (float) { float_val = (float *) PQgetvalue(res, row, field); printf("%s: %g\n",field_name,*float_val); } else if (field_type ==701 )//float8 (double) { double_val = (double *) PQgetvalue(res, row, field); printf("%s: %g\n",field_name,*double_val); } else if ( (field_type ==1043 ) || (field_type==25) )//varchar { char_val = (char *) PQgetvalue(res, row, field); printf("%s: %s\n",field_name,char_val); } else if (field_type == WKB_OID ) //wkb { char_val = (char *) PQgetvalue(res, row, field); printf("%s: ", field_name); // skip 4 bytes varlena size decode_wkb(char_val, &junk); printf("\n"); } } } PQclear(res); /* close the cursor */ res = PQexec(conn, "CLOSE mycursor"); PQclear(res); /* commit the transaction */ res = PQexec(conn, "COMMIT"); PQclear(res); /* close the connection to the database and cleanup */ PQfinish(conn); return 0; }
static int pdo_pgsql_get_attribute(pdo_dbh_t *dbh, zend_long attr, zval *return_value) { pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data; switch (attr) { case PDO_ATTR_EMULATE_PREPARES: ZVAL_BOOL(return_value, H->emulate_prepares); break; case PDO_PGSQL_ATTR_DISABLE_PREPARES: ZVAL_BOOL(return_value, H->disable_prepares); break; case PDO_ATTR_CLIENT_VERSION: ZVAL_STRING(return_value, PG_VERSION); break; case PDO_ATTR_SERVER_VERSION: if (PQprotocolVersion(H->server) >= 3) { /* PostgreSQL 7.4 or later */ ZVAL_STRING(return_value, (char*)PQparameterStatus(H->server, "server_version")); } else /* emulate above via a query */ { PGresult *res = PQexec(H->server, "SELECT VERSION()"); if (res && PQresultStatus(res) == PGRES_TUPLES_OK) { ZVAL_STRING(return_value, (char *)PQgetvalue(res, 0, 0)); } if (res) { PQclear(res); } } break; case PDO_ATTR_CONNECTION_STATUS: switch (PQstatus(H->server)) { case CONNECTION_STARTED: ZVAL_STRINGL(return_value, "Waiting for connection to be made.", sizeof("Waiting for connection to be made.")-1); break; case CONNECTION_MADE: case CONNECTION_OK: ZVAL_STRINGL(return_value, "Connection OK; waiting to send.", sizeof("Connection OK; waiting to send.")-1); break; case CONNECTION_AWAITING_RESPONSE: ZVAL_STRINGL(return_value, "Waiting for a response from the server.", sizeof("Waiting for a response from the server.")-1); break; case CONNECTION_AUTH_OK: ZVAL_STRINGL(return_value, "Received authentication; waiting for backend start-up to finish.", sizeof("Received authentication; waiting for backend start-up to finish.")-1); break; #ifdef CONNECTION_SSL_STARTUP case CONNECTION_SSL_STARTUP: ZVAL_STRINGL(return_value, "Negotiating SSL encryption.", sizeof("Negotiating SSL encryption.")-1); break; #endif case CONNECTION_SETENV: ZVAL_STRINGL(return_value, "Negotiating environment-driven parameter settings.", sizeof("Negotiating environment-driven parameter settings.")-1); break; case CONNECTION_BAD: default: ZVAL_STRINGL(return_value, "Bad connection.", sizeof("Bad connection.")-1); break; } break; case PDO_ATTR_SERVER_INFO: { int spid = PQbackendPID(H->server); zend_string *str_info = strpprintf(0, "PID: %d; Client Encoding: %s; Is Superuser: %s; Session Authorization: %s; Date Style: %s", spid, (char*)PQparameterStatus(H->server, "client_encoding"), (char*)PQparameterStatus(H->server, "is_superuser"), (char*)PQparameterStatus(H->server, "session_authorization"), (char*)PQparameterStatus(H->server, "DateStyle")); ZVAL_STR(return_value, str_info); } break; default: return 0; } return 1; }
static int config_connect(struct config *conf) { static const struct { const char *name; int cols; } qspec[SQL_MAX] = { { "query_alias", 1 }, { "query_domain", 1 }, { "query_credentials", 2 }, { "query_netaddr", 1 }, { "query_userinfo", 3 }, { "query_source", 1 }, { "query_mailaddr", 1 }, { "query_addrname", 1 }, }; size_t i; char *conninfo, *q; log_debug("debug: table-postgres: (re)connecting"); /* Disconnect first, if needed */ config_reset(conf); conninfo = dict_get(&conf->conf, "conninfo"); if (conninfo == NULL) { log_warnx("warn: table-postgres: missing \"conninfo\" configuration directive"); goto end; } conf->db = PQconnectdb(conninfo); if (conf->db == NULL) { log_warnx("warn: table-postgres: PQconnectdb return NULL"); goto end; } if (PQstatus(conf->db) != CONNECTION_OK) { log_warnx("warn: table-postgres: PQconnectdb: %s", PQerrorMessage(conf->db)); goto end; } for (i = 0; i < SQL_MAX; i++) { q = dict_get(&conf->conf, qspec[i].name); if (q && (conf->statements[i] = table_postgres_prepare_stmt( conf->db, q, 1, qspec[i].cols)) == NULL) goto end; } q = dict_get(&conf->conf, "fetch_source"); if (q && (conf->stmt_fetch_source = table_postgres_prepare_stmt(conf->db, q, 0, 1)) == NULL) goto end; log_debug("debug: table-postgres: connected"); return 1; end: config_reset(conf); return 0; }
/* db->db_lock must be locked when calling this function */ static int c_psql_exec_query (c_psql_database_t *db, udb_query_t *q, udb_query_preparation_area_t *prep_area) { PGresult *res; c_psql_user_data_t *data; const char *host; char **column_names; char **column_values; int column_num; int rows_num; int status; int row, col; /* The user data may hold parameter information, but may be NULL. */ data = udb_query_get_user_data (q); /* Versions up to `3' don't know how to handle parameters. */ if (3 <= db->proto_version) res = c_psql_exec_query_params (db, q, data); else if ((NULL == data) || (0 == data->params_num)) res = c_psql_exec_query_noparams (db, q); else { log_err ("Connection to database \"%s\" (%s) does not support " "parameters (protocol version %d) - " "cannot execute query \"%s\".", db->database, db->instance, db->proto_version, udb_query_get_name (q)); return -1; } /* give c_psql_write() a chance to acquire the lock if called recursively * through dispatch_values(); this will happen if, both, queries and * writers are configured for a single connection */ pthread_mutex_unlock (&db->db_lock); column_names = NULL; column_values = NULL; if (PGRES_TUPLES_OK != PQresultStatus (res)) { pthread_mutex_lock (&db->db_lock); if ((CONNECTION_OK != PQstatus (db->conn)) && (0 == c_psql_check_connection (db))) { PQclear (res); return c_psql_exec_query (db, q, prep_area); } log_err ("Failed to execute SQL query: %s", PQerrorMessage (db->conn)); log_info ("SQL query was: %s", udb_query_get_statement (q)); PQclear (res); return -1; } #define BAIL_OUT(status) \ sfree (column_names); \ sfree (column_values); \ PQclear (res); \ pthread_mutex_lock (&db->db_lock); \ return status rows_num = PQntuples (res); if (1 > rows_num) { BAIL_OUT (0); } column_num = PQnfields (res); column_names = (char **) calloc (column_num, sizeof (char *)); if (NULL == column_names) { log_err ("calloc failed."); BAIL_OUT (-1); } column_values = (char **) calloc (column_num, sizeof (char *)); if (NULL == column_values) { log_err ("calloc failed."); BAIL_OUT (-1); } for (col = 0; col < column_num; ++col) { /* Pointers returned by `PQfname' are freed by `PQclear' via * `BAIL_OUT'. */ column_names[col] = PQfname (res, col); if (NULL == column_names[col]) { log_err ("Failed to resolve name of column %i.", col); BAIL_OUT (-1); } } if (C_PSQL_IS_UNIX_DOMAIN_SOCKET (db->host) || (0 == strcmp (db->host, "127.0.0.1")) || (0 == strcmp (db->host, "localhost"))) host = hostname_g; else host = db->host; status = udb_query_prepare_result (q, prep_area, host, "postgresql", db->instance, column_names, (size_t) column_num, db->interval); if (0 != status) { log_err ("udb_query_prepare_result failed with status %i.", status); BAIL_OUT (-1); } for (row = 0; row < rows_num; ++row) { for (col = 0; col < column_num; ++col) { /* Pointers returned by `PQgetvalue' are freed by `PQclear' via * `BAIL_OUT'. */ column_values[col] = PQgetvalue (res, row, col); if (NULL == column_values[col]) { log_err ("Failed to get value at (row = %i, col = %i).", row, col); break; } } /* check for an error */ if (col < column_num) continue; status = udb_query_handle_result (q, prep_area, column_values); if (status != 0) { log_err ("udb_query_handle_result failed with status %i.", status); } } /* for (row = 0; row < rows_num; ++row) */ udb_query_finish_result (q, prep_area); BAIL_OUT (0); #undef BAIL_OUT } /* c_psql_exec_query */
static int db_postgres_submit_async_query(const db_con_t* _con, const str* _s) { int i,ret=0; struct timeval start; if(! _con || !_s || !_s->s) { LM_ERR("invalid parameter value\n"); return(-1); } submit_func_called = 1; /* this bit of nonsense in case our connection get screwed up */ switch(PQstatus(CON_CONNECTION(_con))) { case CONNECTION_OK: break; case CONNECTION_BAD: LM_DBG("connection reset\n"); PQreset(CON_CONNECTION(_con)); break; case CONNECTION_STARTED: case CONNECTION_MADE: case CONNECTION_AWAITING_RESPONSE: case CONNECTION_AUTH_OK: case CONNECTION_SETENV: case CONNECTION_SSL_STARTUP: case CONNECTION_NEEDED: default: LM_ERR("%p PQstatus(%s) invalid: %.*s\n", _con, PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s); return -1; } for (i=0;i<max_db_queries;i++) { /* free any previous query that is laying about */ if(CON_RESULT(_con)) { free_query(_con); } start_expire_timer(start,db_postgres_exec_query_threshold); ret = PQsendQuery(CON_CONNECTION(_con), _s->s); _stop_expire_timer(start, db_postgres_exec_query_threshold, "pgsql query", _s->s, _s->len, 0, sql_slow_queries, sql_total_queries); /* exec the query */ if (ret) { LM_DBG("%p PQsendQuery(%.*s)\n", _con, _s->len, _s->s); return 0; } else { LM_DBG("%p PQsendQuery failed: %s Query: %.*s\n", _con, PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s); if(PQstatus(CON_CONNECTION(_con))!=CONNECTION_OK) { LM_DBG("connection reset\n"); PQreset(CON_CONNECTION(_con)); } else { /* failure not due to connection loss - no point in retrying */ if(CON_RESULT(_con)) { free_query(_con); } break; } } } LM_ERR("%p PQsendQuery Error: %s Query: %.*s\n", _con, PQerrorMessage(CON_CONNECTION(_con)), _s->len, _s->s); return -1; }
PGresult * do_postgres_cCommand_execute_async(VALUE self, VALUE connection, PGconn *db, VALUE query) { PGresult *response; char* str = StringValuePtr(query); while ((response = PQgetResult(db))) { PQclear(response); } struct timeval start; int retval; gettimeofday(&start, NULL); retval = PQsendQuery(db, str); if (!retval) { if (PQstatus(db) != CONNECTION_OK) { PQreset(db); if (PQstatus(db) == CONNECTION_OK) { retval = PQsendQuery(db, str); } else { do_postgres_full_connect(connection, db); retval = PQsendQuery(db, str); } } if (!retval) { rb_raise(eDO_ConnectionError, "%s", PQerrorMessage(db)); } } int socket_fd = PQsocket(db); rb_fdset_t rset; rb_fd_init(&rset); rb_fd_set(socket_fd, &rset); while (1) { retval = rb_thread_fd_select(socket_fd + 1, &rset, NULL, NULL, NULL); if (retval < 0) { rb_fd_term(&rset); rb_sys_fail(0); } if (retval == 0) { continue; } if (PQconsumeInput(db) == 0) { rb_fd_term(&rset); rb_raise(eDO_ConnectionError, "%s", PQerrorMessage(db)); } if (PQisBusy(db) == 0) { break; } } rb_fd_term(&rset); data_objects_debug(connection, query, &start); return PQgetResult(db); }
void doSQL(PGconn *conn, char *command) { PGresult *result; printf("%s\n", command); result = PQexec(conn, command); printf("status is : %s\n", PQresStatus(PQresultStatus(result))); printf("#rows affected: %s\n", PQcmdTuples(result)); printf("result message: %s\n", PQresultErrorMessage(result)); int dalej; while(dalej=0) { switch(PQresultStatus(result)) { case PGRES_TUPLES_OK: { int n = 0, r = 0; int nrows = PQntuples(result); int nfields = PQnfields(result); printf("number of rows returned = %d\n", nrows); printf("number of fields returned = %d\n", nfields); for(r = 0; r < nrows; r++) { for(n = 0; n < nfields; n++) printf(" %s = %s", PQfname(result, n),PQgetvalue(result,r,n)); printf("\n"); } } } PQclear(result); } int main() { PGresult *result; PGconn *conn; char odp, nazwa[20], miasto[20],przydomek[20],data[20],zapytanie[50],wartosc[20]; int id,budzet; const char *connection_str = "host=localhost port=5432 dbname=pzynis user=pzynis password=alamakota"; conn = PQconnectdb(connection_str); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to %s failed, %s", connection_str, PQerrorMessage(conn)); } else { printf("Connected OK\n"); doSQL(conn, "SELECT * FROM number"); // doSQL(conn, "UPDATE number SET name = 'Zaphod' WHERE value = 1"); // doSQL(conn, "DELETE FROM number WHERE value = 2"); doSQL(conn, "SELECT * FROM number"); } int wybor; printf("Podaj opcje : \n 1- stworz tabele \n 2- usun tabele \n 3- dodaj rekordy \n 4-Zawartosc tabeli \n 5-podyfikuj dane \n 6-usun rekord\n 7-stworz wyzwalacz\n 8-usun wyzwalacz\n "); scanf("%i",&wybor); switch(wybor) { case 1: doSQL(conn, "DROP TABLE druzyny_pilkarskie"); doSQL(conn, "CREATE TABLE druzyny_pilkarskie (id INTEGER PRIMARY KEY, nazwa VARCHAR,miasto VARCHAR,przydomek VARCHAR,rok_zal DATE,budzet MONEY);"); break; case 2: doSQL(conn, "DROP TABLE druzyny_pilkarskie"); printf("Usunieto Tabele \n"); break; case 3: printf("\n\n ---- czy wpisujemy co do tabeli? (t/n) "); scanf("%s",&odp); while( (odp == 't') || (odp == 'T') ) { getchar(); printf(" id = "); scanf("%i",&id); printf(" nazwa = "); scanf("%s",nazwa); printf(" miasto = "); scanf("%s",miasto); printf(" przydomek = "); scanf("%s",przydomek); printf(" data (dd-mm-rrrr)= "); scanf("%s",data); printf(" budzet = "); scanf("%i",&budzet); sprintf(zapytanie, "INSERT INTO druzyny_pilkarskie VALUES(%d, \'%s\',\'%s\',\'%s\',\'%s\',%d)", id, nazwa,miasto,przydomek,data,budzet); printf ("zapytanie= %s\n ",zapytanie); doSQL(conn, zapytanie); printf("\n\n ---- czy wpisujemy co do tabeli? (t/n) "); scanf("%s",&odp); } break; case 4: printf("Zawartosc tabeli :"); doSQL(conn, "SELECT* FROM druzyny_pilkarskie"); break; case 5: printf(" Modyfikowanie rekordów: \n\n"); printf(" Podaj id rekodu= "); scanf("%i",&id); sprintf(zapytanie, "SELECT * FROM druzyny_pilkarskie WHERE id=%i", id); doSQL(conn,zapytanie); printf("\n\nCo chcesz zmienic(id,nazwa,miasto,przydomek,data,budzet)? \n"); scanf("%s",nazwa); printf("Podaj wartosc dla %s \n",nazwa); scanf("%s",wartosc); if((strncmp( nazwa, "id", 2 ) == 0 )||(strncmp( nazwa, "budzet", 6 ) == 0 )) sprintf(zapytanie, "UPDATE druzyny_pilkarskie SET %s=%s where id=%i", nazwa,wartosc,id); else sprintf(zapytanie, "UPDATE druzyny_pilkarskie SET %s='%s' where id=%i", nazwa,wartosc,id); doSQL(conn, zapytanie); break; case 6: printf(" Podaj id rekordu do usuniecia: \n\n"); printf(" Podaj id rekodu= "); scanf("%i",&id); sprintf(zapytanie, " DELETE FROM druzyny_pilkarskie WHERE id=%i", id); doSQL(conn, zapytanie); break; case 7: doSQL(conn, "DROP TRIGGER wyzwalacz ON druzyny_pilkarskie "); printf("Wyszukaj dane: \n\n"); printf(" Podaj dane do znalezienia= "); scanf("%s",nazwa); sprintf(zapytanie, " CREATE OR REPLACE FUNCTION funkcja() RETURNS TRIGGER AS $$ BEGIN IF NEW.budzet>=0 THEN NEW.budzet := now(); END IF ;RETURN NEW; END; $$ LANGUAGE 'plpgsql';"); doSQL(conn, zapytanie); sprintf(zapytanie, "CREATE TRIGGER wyzwalacz AFTER INSERT OR UPDATE ON druzyny_pilkarskie FOR EACH ROW EXECUTE PROCEDURE funkcja();"); doSQL(conn, zapytanie); break; case 8: doSQL(conn, "DROP TRIGGER wyzwalacz ON druzyny_pilkarskie "); break; case 9: dalej=1; break; } }
int pw_pgsql_connect(PGconn ** const id_sql_server) { char *conninfo = NULL; size_t sizeof_conninfo; char *escaped_server = NULL; char *escaped_db = NULL; char *escaped_user = NULL; char *escaped_pw = NULL; int ret = -1; *id_sql_server = NULL; server = malloc(16); db = malloc(16); user = malloc(16); pw = malloc(16); snprintf( server, 10,"localhost"); snprintf( db, 9,"fastprod"); snprintf( user, 9,"fastprod"); snprintf( pw, 9,"fastprod"); if ((escaped_server = pw_pgsql_escape_string(server)) == NULL || (escaped_db = pw_pgsql_escape_string(db)) == NULL || (escaped_user = pw_pgsql_escape_string(user)) == NULL || (escaped_pw = pw_pgsql_escape_string(pw)) == NULL) { rprintf(FLOG,"ERR escaping\n"); goto bye; } //rprintf(FLOG,"ERR conninfo:%s-%s\n",escaped_server,server); #define PGSQL_CONNECT_FMTSTRING \ "host='%s' port='%d' dbname='%s' user='******' password='******'" sizeof_conninfo = sizeof PGSQL_CONNECT_FMTSTRING + strlen(escaped_server) + (size_t) 5U + strlen(escaped_db) + strlen(escaped_user) + strlen(escaped_pw); if ((conninfo = malloc(sizeof_conninfo)) == NULL) { rprintf(FLOG,"ERR malloc(sizeof_conninfo)\n"); goto bye; } if (SNCHECK(snprintf(conninfo, sizeof_conninfo, PGSQL_CONNECT_FMTSTRING, server, port, db, user, pw), sizeof_conninfo)) { rprintf(FLOG,"ERR SNCHECK()\n"); goto bye; } //rprintf(FLOG,"ERR conninfo:%s\n",conninfo); if ((*id_sql_server = PQconnectdb(conninfo)) == NULL || PQstatus(*id_sql_server) == CONNECTION_BAD) { free(conninfo); if (server_down == 0) { server_down++; //logfile(LOG_ERR, MSG_SQL_DOWN); } rprintf(FLOG,"ERR PQconnectdb\n"); goto bye; } server_down = 0; ret = 0; bye: free(conninfo); free(escaped_server); free(escaped_db); free(escaped_user); free(escaped_pw); return ret; }
/****************************************************************************** * * * Function: zbx_db_connect * * * * Purpose: connect to the database * * * * Return value: ZBX_DB_OK - succefully connected * * ZBX_DB_DOWN - database is down * * ZBX_DB_FAIL - failed to connect * * * ******************************************************************************/ int zbx_db_connect(char *host, char *user, char *password, char *dbname, char *dbschema, char *dbsocket, int port) { int ret = ZBX_DB_OK; #if defined(HAVE_IBM_DB2) char *connect = NULL; #elif defined(HAVE_ORACLE) char *connect = NULL; sword err = OCI_SUCCESS; #elif defined(HAVE_POSTGRESQL) char *cport = NULL; DB_RESULT result; DB_ROW row; #endif txn_init = 1; assert(NULL != host); #if defined(HAVE_IBM_DB2) connect = zbx_strdup(connect, "PROTOCOL=TCPIP;"); if ('\0' != *host) connect = zbx_strdcatf(connect, "HOSTNAME=%s;", host); if (NULL != dbname && '\0' != *dbname) connect = zbx_strdcatf(connect, "DATABASE=%s;", dbname); if (0 != port) connect = zbx_strdcatf(connect, "PORT=%d;", port); if (NULL != user && '\0' != *user) connect = zbx_strdcatf(connect, "UID=%s;", user); if (NULL != password && '\0' != *password) connect = zbx_strdcatf(connect, "PWD=%s;", password); memset(&ibm_db2, 0, sizeof(ibm_db2)); /* allocate an environment handle */ if (SUCCEED != zbx_ibm_db2_success(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ibm_db2.henv))) ret = ZBX_DB_FAIL; /* set attribute to enable application to run as ODBC 3.0 application; */ /* recommended for pure IBM DB2 CLI, but not required */ if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLSetEnvAttr(ibm_db2.henv, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0))) ret = ZBX_DB_FAIL; /* allocate a database connection handle */ if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLAllocHandle(SQL_HANDLE_DBC, ibm_db2.henv, &ibm_db2.hdbc))) ret = ZBX_DB_FAIL; /* connect to the database */ if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLDriverConnect(ibm_db2.hdbc, NULL, (SQLCHAR *)connect, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT))) ret = ZBX_DB_FAIL; /* set autocommit on */ if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLSetConnectAttr(ibm_db2.hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_NTS))) ret = ZBX_DB_DOWN; /* we do not generate vendor escape clause sequences */ if (ZBX_DB_OK == ret && SUCCEED != zbx_ibm_db2_success(SQLSetConnectAttr(ibm_db2.hdbc, SQL_ATTR_NOSCAN, (SQLPOINTER)SQL_NOSCAN_ON, SQL_NTS))) ret = ZBX_DB_DOWN; /* set current schema */ if (NULL != dbschema && '\0' != *dbschema && ZBX_DB_OK == ret) { char *dbschema_esc; dbschema_esc = DBdyn_escape_string(dbschema); DBexecute("set current schema='%s'", dbschema_esc); zbx_free(dbschema_esc); } /* output error information */ if (ZBX_DB_OK != ret) { zbx_ibm_db2_log_errors(SQL_HANDLE_ENV, ibm_db2.henv); zbx_ibm_db2_log_errors(SQL_HANDLE_DBC, ibm_db2.hdbc); zbx_db_close(); } zbx_free(connect); #elif defined(HAVE_MYSQL) conn = mysql_init(NULL); if (!mysql_real_connect(conn, host, user, password, dbname, port, dbsocket, CLIENT_MULTI_STATEMENTS)) { zabbix_errlog(ERR_Z3001, dbname, mysql_errno(conn), mysql_error(conn)); ret = ZBX_DB_FAIL; } if (ZBX_DB_OK == ret) { if (0 != mysql_select_db(conn, dbname)) { zabbix_errlog(ERR_Z3001, dbname, mysql_errno(conn), mysql_error(conn)); ret = ZBX_DB_FAIL; } } if (ZBX_DB_OK == ret) { DBexecute("set names utf8"); } if (ZBX_DB_FAIL == ret) { switch (mysql_errno(conn)) { case CR_CONN_HOST_ERROR: case CR_SERVER_GONE_ERROR: case CR_CONNECTION_ERROR: case CR_SERVER_LOST: case CR_UNKNOWN_HOST: 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: break; } } #elif defined(HAVE_ORACLE) #if defined(HAVE_GETENV) && defined(HAVE_PUTENV) if (NULL == getenv("NLS_LANG")) putenv("NLS_LANG=.UTF8"); #endif memset(&oracle, 0, sizeof(oracle)); /* connection string format: [//]host[:port][/service name] */ if ('\0' != *host) { connect = zbx_strdcatf(connect, "//%s", host); if (0 != port) connect = zbx_strdcatf(connect, ":%d", port); if (NULL != dbname && '\0' != *dbname) connect = zbx_strdcatf(connect, "/%s", dbname); } else ret = ZBX_DB_FAIL; if (ZBX_DB_OK == ret) { /* initialize environment */ err = OCIEnvCreate((OCIEnv **)&oracle.envhp, (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *,size_t))0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0); if (OCI_SUCCESS != err) { zabbix_errlog(ERR_Z3001, connect, err, zbx_oci_error(err)); ret = ZBX_DB_FAIL; } } if (ZBX_DB_OK == ret) { /* allocate an error handle */ (void)OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&oracle.errhp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0); /* get the session */ err = OCILogon2(oracle.envhp, oracle.errhp, &oracle.svchp, (text *)user, (ub4)(NULL != user ? strlen(user) : 0), (text *)password, (ub4)(NULL != password ? strlen(password) : 0), (text *)connect, (ub4)strlen(connect), OCI_DEFAULT); if (OCI_SUCCESS == err) { err = OCIAttrGet((void *)oracle.svchp, OCI_HTYPE_SVCCTX, (void *)&oracle.srvhp, (ub4 *)0, OCI_ATTR_SERVER, oracle.errhp); } if (OCI_SUCCESS != err) { zabbix_errlog(ERR_Z3001, connect, err, zbx_oci_error(err)); ret = ZBX_DB_DOWN; } } if (ZBX_DB_OK == ret) { /* initialize statement handle */ err = OCIHandleAlloc((dvoid *)oracle.envhp, (dvoid **)&oracle.stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0); if (OCI_SUCCESS != err) { zabbix_errlog(ERR_Z3001, connect, err, zbx_oci_error(err)); ret = ZBX_DB_DOWN; } } if (ZBX_DB_OK == ret) DBexecute("alter session set nls_numeric_characters='. '"); zbx_free(connect); if (ZBX_DB_OK != ret) zbx_db_close(); #elif defined(HAVE_POSTGRESQL) if (0 != port) cport = zbx_dsprintf(cport, "%d", port); conn = PQsetdbLogin(host, cport, NULL, NULL, dbname, user, password); zbx_free(cport); /* check to see that the backend connection was successfully made */ if (CONNECTION_OK != PQstatus(conn)) { zabbix_errlog(ERR_Z3001, dbname, 0, PQerrorMessage(conn)); ret = ZBX_DB_DOWN; } else { result = DBselect("select oid from pg_type where typname='bytea'"); if (NULL != (row = DBfetch(result))) ZBX_PG_BYTEAOID = atoi(row[0]); DBfree_result(result); } #ifdef HAVE_FUNCTION_PQSERVERVERSION ZBX_PG_SVERSION = PQserverVersion(conn); zabbix_log(LOG_LEVEL_DEBUG, "PostgreSQL Server version: %d", ZBX_PG_SVERSION); #endif if (80100 <= ZBX_PG_SVERSION) { /* disable "nonstandard use of \' in a string literal" warning */ DBexecute("set escape_string_warning to off"); result = DBselect("show standard_conforming_strings"); if (NULL != (row = DBfetch(result))) ZBX_PG_ESCAPE_BACKSLASH = (0 == strcmp(row[0], "off")); DBfree_result(result); } if (90000 <= ZBX_PG_SVERSION) { /* change the output format for values of type bytea from hex (the default) to escape */ DBexecute("set bytea_output=escape"); } #elif defined(HAVE_SQLITE3) #ifdef HAVE_FUNCTION_SQLITE3_OPEN_V2 if (SQLITE_OK != sqlite3_open_v2(dbname, &conn, SQLITE_OPEN_READWRITE, NULL)) #else if (SQLITE_OK != sqlite3_open(dbname, &conn)) #endif { zabbix_errlog(ERR_Z3001, dbname, 0, sqlite3_errmsg(conn)); sqlite3_close(conn); ret = ZBX_DB_DOWN; } else { char *p, *path; /* do not return SQLITE_BUSY immediately, wait for N ms */ sqlite3_busy_timeout(conn, SEC_PER_MIN * 1000); path = strdup(dbname); if (NULL != (p = strrchr(path, '/'))) *++p = '\0'; else *path = '\0'; DBexecute("PRAGMA synchronous = 0"); /* OFF */ DBexecute("PRAGMA temp_store = 2"); /* MEMORY */ DBexecute("PRAGMA temp_store_directory = '%s'", path); zbx_free(path); } #endif /* HAVE_SQLITE3 */ txn_init = 0; return ret; }
struct lmf_str * pgsql_openlib(char *sname, int ldnaseq, int *sascii) { FILE *sql_file; PGconn *conn; PGresult *res; char *tmp_str, *ttmp_str; int tmp_str_len; char *bp, *bps, *bdp, *tp, tchar; int i, qs_len, qqs_len; char *sql_db, *sql_host, *sql_dbname, *sql_user, *sql_pass; char *sql_port; char *sql_do; int sql_do_cnt; struct lmf_str *m_fptr; /* if (sql_reopen) return NULL; - should not be called for re-open */ tmp_str_len = PGSQL_BUF; if ((tmp_str=(char *)calloc(tmp_str_len,sizeof(char)))==NULL) { fprintf(stderr,"cannot allocate %d for pgSQL buffer\n",tmp_str_len); return NULL; } if (sname[0] == '%') { strncpy(tmp_str,sname+1,tmp_str_len); tmp_str[sizeof(tmp_str)-1]='\0'; } else { if ((sql_file=fopen(sname,"r"))==NULL) { fprintf(stderr," cannot open pgSQL file: %s\n",sname); return NULL; } if ((qs_len=fread(tmp_str,sizeof(char),tmp_str_len-1,sql_file))<=0) { fprintf(stderr," cannot read pgSQL file: %s\n",sname); return NULL; } else { tmp_str[qs_len]='\0'; qqs_len = qs_len; while (qqs_len >= tmp_str_len-1) { tmp_str_len += PGSQL_BUF; if ((tmp_str=(char *)realloc(tmp_str,tmp_str_len))==NULL) { fprintf(stderr, " cannot reallocate %d for pgSQL buffer\n",tmp_str_len); return NULL; } ttmp_str = &tmp_str[qqs_len]; if ((qs_len=fread(ttmp_str,sizeof(char),PGSQL_BUF,sql_file))<0) { fprintf(stderr," cannot read pgSQL file: %s\n",sname); return NULL; } ttmp_str[qs_len]='\0'; qqs_len += qs_len; } } fclose(sql_file); } bps = tmp_str; if ((bp=strchr(bps,';'))!=NULL) { *bp='\0'; if ((sql_db=calloc(strlen(bps)+1,sizeof(char)))==NULL) { fprintf(stderr, " cannot allocate space for database name [%d], %s\n", strlen(bps),bps); return NULL; } /* have database name, parse the fields */ else { strcpy(sql_db,bps); /* strcpy OK because allocated strlen(bps) */ bps = bp+1; /* points to next char after ';' */ while (isspace(*bps)) bps++; *bp=';'; /* replace ; */ bp = sql_db; while (*bp=='-') {*bp++ = ' ';} sql_host = strtok(bp," \t\n"); if (sql_host[0]=='@') sql_host=""; sql_dbname = strtok(NULL," \t\n"); sql_user = strtok(NULL," \t\n"); if (sql_user[0]=='@') sql_user=""; sql_pass = strtok(NULL," \t\n"); if (sql_pass[0]=='@') sql_pass=""; if ((tp=strchr(sql_host,':'))!=NULL) { sql_port = tp+1; *tp='\0'; } else sql_port = ""; } } else { fprintf(stderr," cannot find database fields:\n%s\n",tmp_str); return NULL; } /* we have all the info we need to open a database, allocate lmf_str */ if ((m_fptr = (struct lmf_str *)calloc(1,sizeof(struct lmf_str)))==NULL) { fprintf(stderr," cannot allocate lmf_str (%ld) for %s\n", sizeof(struct lmf_str),sname); return NULL; } /* have our struct, initialize it */ strncpy(m_fptr->lb_name,sname,MAX_FN); m_fptr->lb_name[MAX_FN-1]='\0'; m_fptr->sascii = sascii; m_fptr->sql_db = sql_db; m_fptr->getlib = pgsql_getlib; m_fptr->ranlib = pgsql_ranlib; m_fptr->mm_flg = 0; m_fptr->sql_reopen = 0; m_fptr->lb_type = PGSQL_LIB; /* now open the database, if necessary */ conn = PQsetdbLogin(sql_host, sql_port, NULL, NULL, sql_dbname, sql_user, sql_pass); if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database '%s' failed.\n", PQdb(conn)); fprintf(stderr, "%s", PQerrorMessage(conn)); PQfinish(conn); goto error_r; } else { m_fptr->pgsql_conn = conn; fprintf(stderr," Database %s opened on %s\n",sql_dbname,sql_host); } /* check for 'DO' command - copy to 'DO' string */ while (*bps == '-') { *bps++=' ';} if (isspace(bps[-1]) && toupper(bps[0])=='D' && toupper(bps[1])=='O' && isspace(bps[2])) { /* have some 'DO' commands */ /* check where the end of the last DO statement is */ sql_do_cnt = 1; /* count up the number of 'DO' statements for later */ bdp=bps+3; while ((bp=strchr(bdp,';'))!=NULL) { tp = bp+2; /* skip ;\n */ while (isspace(*tp) || *tp == '-') {*tp++ = ' ';} if (toupper(*tp)=='D' && toupper(tp[1])=='O' && isspace(tp[2])) { sql_do_cnt++; /* count the DO statements */ bdp = tp+3; /* move to the next DO statement */ } else break; } if (bp != NULL) { /* end of the last DO, begin of select */ tchar = *(bp+1); *(bp+1)='\0'; /* terminate DO strings */ if ((sql_do = calloc(strlen(bps)+1, sizeof(char)))==NULL) { fprintf(stderr," cannot allocate %d for sql_do\n",strlen(bps)); goto error_r; } else { strcpy(sql_do,bps); *(bp+1)=tchar; /* replace missing ';' */ } bps = bp+1; while (isspace(*bps)) bps++; } else { fprintf(stderr," terminal ';' not found: %s\n",bps); goto error_r; } /* all the DO commands are in m_fptr->sql_do in the form: DO command1; DO command2; DO command3; */ bdp = sql_do; while (sql_do_cnt-- && (bp=strchr(bdp,';'))!=NULL) { /* do the pgsql statement on bdp+3 */ /* check for error */ *bp='\0'; res = PQexec(m_fptr->pgsql_conn,bdp+3); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"*** Error %s - query failed:\n%s\n", PQerrorMessage(m_fptr->pgsql_conn), bdp+3); PQclear(res); goto error_r; } PQclear(res); *bp=';'; bdp = bp+1; while (isspace(*bdp)) bdp++; } } /* copy 1st query field */ if ((bp=strchr(bps,';'))!=NULL) { *bp='\0'; if ((m_fptr->sql_query=calloc(strlen(bps)+41,sizeof(char)))==NULL) { fprintf(stderr, " cannot allocate space for query string [%d], %s\n", strlen(bps),bps); goto error_r; } /* have query, copy it */ else { strncpy(m_fptr->sql_query,"DECLARE next_seq CURSOR FOR ",40); strcat(m_fptr->sql_query,bps); *bp=';'; /* replace ; */ bps = bp+1; while(isspace(*bps)) bps++; } } else { fprintf(stderr," cannot find database query field:\n%s\n",tmp_str); goto error_r; } /* copy get_desc field */ if ((bp=strchr(bps,';'))!=NULL) { *bp='\0'; if ((m_fptr->sql_getdesc=calloc(strlen(bps)+1,sizeof(char)))==NULL) { fprintf(stderr, " cannot allocate space for database name [%d], %s\n", strlen(bps),bps); goto error_r; } /* have get_desc, copy it */ else { strcpy(m_fptr->sql_getdesc,bps); *bp=';'; /* replace ; */ bps = bp+1; while(isspace(*bps)) bps++; } } else { fprintf(stderr," cannot find getdesc field:\n%s\n",tmp_str); goto error_r; } if ((bp=strchr(bps,';'))!=NULL) { *bp='\0';} if ((m_fptr->sql_getseq=calloc(strlen(bps)+1,sizeof(char)))==NULL) { fprintf(stderr, " cannot allocate space for database name [%d], %s\n", strlen(bps),bps); goto error_r; } if (strlen(bps) > 0) { strcpy(m_fptr->sql_getseq,bps); } else { fprintf(stderr," cannot find getseq field:\n%s\n",tmp_str); return NULL; } if (bp!=NULL) *bp=';'; /* now do the fetch */ res = PQexec(m_fptr->pgsql_conn,"BEGIN;"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"*** Error %s - BEGIN failed:\n", PQerrorMessage(conn)); PQclear(res); goto error_r; } PQclear(res); res = PQexec(m_fptr->pgsql_conn, m_fptr->sql_query); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr,"*** Error %d:%s - query failed:\n%s\n", PQresultStatus(res),PQerrorMessage(conn), m_fptr->sql_query); PQclear(res); goto error_r; } PQclear(res); m_fptr->pgsql_res=NULL; return m_fptr; error_r: free(m_fptr->sql_getseq); free(m_fptr->sql_getdesc); free(m_fptr->sql_query); free(m_fptr); free(sql_db); return NULL; }
/* * 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) 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) if (OCI_SUCCESS == (err = zbx_oracle_statement_prepare(sql))) { ub4 nrows = 0; if (OCI_SUCCESS == (err = zbx_oracle_statement_execute(&nrows))) ret = (int)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); } #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; }
/****************************************************************************** * * * 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 prefetch_rows = 200, 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); /* Prefetching when working with Oracle is needed because otherwise it fetches only 1 row at a time when doing */ /* selects (default behavior). There are 2 ways to do prefetching: memory based and rows based. Based on the */ /* study optimal (speed-wise) memory based prefetch is 2 MB. But in case of many subsequent selects CPU usage */ /* jumps up to 100 %. Using rows prefetch with up to 200 rows does not affect CPU usage, it is the same as */ /* without prefetching at all. See ZBX-5920, ZBX-6493 for details. */ /* */ /* Tested on Oracle 11gR2. */ /* */ /* Oracle info: docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci04sql.htm */ if (OCI_SUCCESS == err) { err = OCIAttrSet(result->stmthp, OCI_HTYPE_STMT, &prefetch_rows, sizeof(prefetch_rows), OCI_ATTR_PREFETCH_ROWS, oracle.errhp); } 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, 0 == txn_level ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT); } 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 *)); result->clobs = zbx_malloc(NULL, result->ncolumn * sizeof(OCILobLocator *)); result->values_alloc = zbx_malloc(NULL, result->ncolumn * sizeof(ub4)); memset(result->values, 0, result->ncolumn * sizeof(char *)); memset(result->clobs, 0, result->ncolumn * sizeof(OCILobLocator *)); memset(result->values_alloc, 0, result->ncolumn * sizeof(ub4)); for (counter = 1; OCI_SUCCESS == err && counter <= result->ncolumn; counter++) { OCIParam *parmdp = NULL; OCIDefine *defnp = NULL; ub4 char_semantics; ub2 col_width = 0, data_type; /* 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 data type for the column */ err = OCIAttrGet((void *)parmdp, OCI_DTYPE_PARAM, (dvoid *)&data_type, (ub4 *)NULL, (ub4)OCI_ATTR_DATA_TYPE, (OCIError *)oracle.errhp); } if (SQLT_CLOB == data_type) { if (ZBX_DB_OK == err) { /* allocate the lob locator variable */ err = OCIDescriptorAlloc((dvoid *)oracle.envhp, (dvoid **)&result->clobs[counter - 1], OCI_DTYPE_LOB, (size_t)0, (dvoid **)0); } if (OCI_SUCCESS == err) { /* associate clob var with its define handle */ err = OCIDefineByPos((void *)result->stmthp, &defnp, (OCIError *)oracle.errhp, (ub4)counter, (dvoid *)&result->clobs[counter - 1], (sb4)-1, data_type, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT); } } else { 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 *)NULL, (ub4)OCI_ATTR_CHAR_USED, (OCIError *)oracle.errhp); } if (OCI_SUCCESS == err) { if (0 != char_semantics) { /* retrieve the column width in characters */ err = OCIAttrGet((void *)parmdp, (ub4)OCI_DTYPE_PARAM, (void *)&col_width, (ub4 *)NULL, (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 *)NULL, (ub4)OCI_ATTR_DATA_SIZE, (OCIError *)oracle.errhp); } } col_width++; /* add 1 byte for terminating '\0' */ result->values_alloc[counter - 1] = col_width; result->values[counter - 1] = zbx_malloc(NULL, col_width); *result->values[counter - 1] = '\0'; 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; }
/* ---------- * SlonMain * ---------- */ static void SlonMain(void) { PGresult *res; SlonDString query; int i, n; PGconn *startup_conn; slon_pid = getpid(); #ifndef WIN32 slon_worker_pid = slon_pid; #endif if (pthread_mutex_init(&slon_wait_listen_lock, NULL) < 0) { slon_log(SLON_FATAL, "main: pthread_mutex_init() failed - %s\n", strerror(errno)); slon_abort(); } if (pthread_cond_init(&slon_wait_listen_cond, NULL) < 0) { slon_log(SLON_FATAL, "main: pthread_cond_init() failed - %s\n", strerror(errno)); slon_abort(); } /* * Dump out current configuration - all elements of the various arrays... */ dump_configuration(); /* * Connect to the local database to read the initial configuration */ startup_conn = PQconnectdb(rtcfg_conninfo); if (startup_conn == NULL) { slon_log(SLON_FATAL, "main: PQconnectdb() failed - sleep 10s\n"); sleep(10); slon_retry(); exit(-1); } if (PQstatus(startup_conn) != CONNECTION_OK) { slon_log(SLON_FATAL, "main: Cannot connect to local database - %s - sleep 10s\n", PQerrorMessage(startup_conn)); PQfinish(startup_conn); sleep(10); slon_retry(); exit(-1); } /* * Get our local node ID */ rtcfg_nodeid = db_getLocalNodeId(startup_conn); if (rtcfg_nodeid < 0) { slon_log(SLON_FATAL, "main: Node is not initialized properly - sleep 10s\n"); sleep(10); slon_retry(); exit(-1); } if (db_checkSchemaVersion(startup_conn) < 0) { slon_log(SLON_FATAL, "main: Node has wrong Slony-I schema or module version loaded\n"); slon_abort(); } slon_log(SLON_CONFIG, "main: local node id = %d\n", rtcfg_nodeid); dstring_init(&query); slon_mkquery(&query, "select %s.slon_node_health_check();", rtcfg_namespace); res = PQexec(startup_conn, dstring_data(&query)); if (PQresultStatus(res) != PGRES_TUPLES_OK) { slon_log(SLON_FATAL, "could not call slon_node_health_check() - %", PQresultErrorMessage(res)); slon_abort(); } else { if (PQntuples(res) != 1) { slon_log(SLON_FATAL, "query '%s' returned %d rows (expected 1)\n", query, PQntuples(res)); slon_abort(); } else { if (*(PQgetvalue(res, 0, 0)) == 'f') { slon_log(SLON_FATAL, "slon_node_health_check() returned false - fatal health problem!\n%s\nREPAIR CONFIG may be helpful to rectify this problem\n", PQresultErrorMessage(res)); slon_abort(); } } } PQclear(res); dstring_free(&query); #ifndef WIN32 if (signal(SIGHUP, SIG_IGN) == SIG_ERR) { slon_log(SLON_FATAL, "main: SIGHUP signal handler setup failed -(%d) %s\n", errno, strerror(errno)); slon_abort(); } if (signal(SIGINT, SIG_IGN) == SIG_ERR) { slon_log(SLON_FATAL, "main: SIGINT signal handler setup failed -(%d) %s\n", errno, strerror(errno)); slon_abort(); } if (signal(SIGTERM, SIG_IGN) == SIG_ERR) { slon_log(SLON_FATAL, "main: SIGTERM signal handler setup failed -(%d) %s\n", errno, strerror(errno)); slon_abort(); } if (signal(SIGCHLD, SIG_IGN) == SIG_ERR) { slon_log(SLON_FATAL, "main: SIGCHLD signal handler setup failed -(%d) %s\n", errno, strerror(errno)); slon_abort(); } if (signal(SIGQUIT, SIG_IGN) == SIG_ERR) { slon_log(SLON_FATAL, "main: SIGQUIT signal handler setup failed -(%d) %s\n", errno, strerror(errno)); slon_abort(); } #endif slon_log(SLON_INFO, "main: main process started\n"); /* * Start the event scheduling system */ slon_log(SLON_CONFIG, "main: launching sched_start_mainloop\n"); if (sched_start_mainloop() < 0) slon_retry(); slon_log(SLON_CONFIG, "main: loading current cluster configuration\n"); /* * Begin a transaction */ res = PQexec(startup_conn, "start transaction; " "set transaction isolation level serializable;"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { slon_log(SLON_FATAL, "Cannot start transaction - %s - sleep 10s\n", PQresultErrorMessage(res)); sleep(10); PQclear(res); slon_retry(); } PQclear(res); /* * Read configuration table sl_node */ dstring_init(&query); slon_mkquery(&query, "select no_id, no_active, no_comment, " " (select coalesce(max(con_seqno),0) from %s.sl_confirm " " where con_origin = no_id and con_received = %d) " " as last_event, " " (select ev_snapshot from %s.sl_event " " where ev_origin = no_id " " and ev_seqno = (select max(ev_seqno) " " from %s.sl_event " " where ev_origin = no_id " " and ev_type = 'SYNC')) as last_snapshot " "from %s.sl_node " "order by no_id; ", rtcfg_namespace, rtcfg_nodeid, rtcfg_namespace, rtcfg_namespace, rtcfg_namespace); res = PQexec(startup_conn, dstring_data(&query)); if (PQresultStatus(res) != PGRES_TUPLES_OK) { slon_log(SLON_FATAL, "main: Cannot get node list - %s\n", PQresultErrorMessage(res)); PQclear(res); dstring_free(&query); slon_retry(); } for (i = 0, n = PQntuples(res); i < n; i++) { int no_id = (int) strtol(PQgetvalue(res, i, 0), NULL, 10); int no_active = (*PQgetvalue(res, i, 1) == 't') ? 1 : 0; char *no_comment = PQgetvalue(res, i, 2); int64 last_event; if (no_id == rtcfg_nodeid) { /* * Complete our own local node entry */ rtcfg_nodeactive = no_active; rtcfg_nodecomment = strdup(no_comment); } else { /* * Add a remote node */ slon_scanint64(PQgetvalue(res, i, 3), &last_event); rtcfg_storeNode(no_id, no_comment); rtcfg_setNodeLastEvent(no_id, last_event); rtcfg_setNodeLastSnapshot(no_id, PQgetvalue(res, i, 4)); /* * If it is active, remember for activation just before we start * processing events. */ if (no_active) rtcfg_needActivate(no_id); } } PQclear(res); /* * Read configuration table sl_path - the interesting pieces */ slon_mkquery(&query, "select pa_server, pa_conninfo, pa_connretry " "from %s.sl_path where pa_client = %d" " and pa_conninfo<>'<event pending>'", rtcfg_namespace, rtcfg_nodeid); res = PQexec(startup_conn, dstring_data(&query)); if (PQresultStatus(res) != PGRES_TUPLES_OK) { slon_log(SLON_FATAL, "main: Cannot get path config - %s\n", PQresultErrorMessage(res)); PQclear(res); dstring_free(&query); slon_retry(); } for (i = 0, n = PQntuples(res); i < n; i++) { int pa_server = (int) strtol(PQgetvalue(res, i, 0), NULL, 10); char *pa_conninfo = PQgetvalue(res, i, 1); int pa_connretry = (int) strtol(PQgetvalue(res, i, 2), NULL, 10); rtcfg_storePath(pa_server, pa_conninfo, pa_connretry); } PQclear(res); /* * Load the initial listen configuration */ rtcfg_reloadListen(startup_conn); /* * Read configuration table sl_set */ slon_mkquery(&query, "select set_id, set_origin, set_comment " "from %s.sl_set", rtcfg_namespace); res = PQexec(startup_conn, dstring_data(&query)); if (PQresultStatus(res) != PGRES_TUPLES_OK) { slon_log(SLON_FATAL, "main: Cannot get set config - %s\n", PQresultErrorMessage(res)); PQclear(res); dstring_free(&query); slon_retry(); } for (i = 0, n = PQntuples(res); i < n; i++) { int set_id = (int) strtol(PQgetvalue(res, i, 0), NULL, 10); int set_origin = (int) strtol(PQgetvalue(res, i, 1), NULL, 10); char *set_comment = PQgetvalue(res, i, 2); rtcfg_storeSet(set_id, set_origin, set_comment); } PQclear(res); /* * Read configuration table sl_subscribe - only subscriptions for local * node */ slon_mkquery(&query, "select sub_set, sub_provider, sub_forward, sub_active " "from %s.sl_subscribe " "where sub_receiver = %d", rtcfg_namespace, rtcfg_nodeid); res = PQexec(startup_conn, dstring_data(&query)); if (PQresultStatus(res) != PGRES_TUPLES_OK) { slon_log(SLON_FATAL, "main: Cannot get subscription config - %s\n", PQresultErrorMessage(res)); PQclear(res); dstring_free(&query); slon_retry(); } for (i = 0, n = PQntuples(res); i < n; i++) { int sub_set = (int) strtol(PQgetvalue(res, i, 0), NULL, 10); int sub_provider = (int) strtol(PQgetvalue(res, i, 1), NULL, 10); char *sub_forward = PQgetvalue(res, i, 2); char *sub_active = PQgetvalue(res, i, 3); rtcfg_storeSubscribe(sub_set, sub_provider, sub_forward); if (*sub_active == 't') rtcfg_enableSubscription(sub_set, sub_provider, sub_forward); } PQclear(res); /* * Remember the last known local event sequence */ slon_mkquery(&query, "select coalesce(max(ev_seqno), -1) from %s.sl_event " "where ev_origin = '%d'", rtcfg_namespace, rtcfg_nodeid); res = PQexec(startup_conn, dstring_data(&query)); if (PQresultStatus(res) != PGRES_TUPLES_OK) { slon_log(SLON_FATAL, "main: Cannot get last local eventid - %s\n", PQresultErrorMessage(res)); PQclear(res); dstring_free(&query); slon_retry(); } if (PQntuples(res) == 0) strcpy(rtcfg_lastevent, "-1"); else if (PQgetisnull(res, 0, 0)) strcpy(rtcfg_lastevent, "-1"); else strcpy(rtcfg_lastevent, PQgetvalue(res, 0, 0)); PQclear(res); dstring_free(&query); slon_log(SLON_CONFIG, "main: last local event sequence = %s\n", rtcfg_lastevent); /* * Rollback the transaction we used to get the config snapshot */ res = PQexec(startup_conn, "rollback transaction;"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { slon_log(SLON_FATAL, "main: Cannot rollback transaction - %s\n", PQresultErrorMessage(res)); PQclear(res); slon_retry(); } PQclear(res); /* * Done with the startup, don't need the local connection any more. */ PQfinish(startup_conn); slon_log(SLON_CONFIG, "main: configuration complete - starting threads\n"); /* * Create the local event thread that monitors the local node for * administrative events to adjust the configuration at runtime. We wait * here until the local listen thread has checked that there is no other * slon daemon running. */ pthread_mutex_lock(&slon_wait_listen_lock); if (pthread_create(&local_event_thread, NULL, localListenThread_main, NULL) < 0) { slon_log(SLON_FATAL, "main: cannot create localListenThread - %s\n", strerror(errno)); slon_retry(); } pthread_cond_wait(&slon_wait_listen_cond, &slon_wait_listen_lock); if (!slon_listen_started) { /** * The local listen thread did not start up properly. */ slon_log(SLON_FATAL, "main: localListenThread did not start\n"); slon_abort(); } pthread_mutex_unlock(&slon_wait_listen_lock); /* * Enable all nodes that are active */ rtcfg_doActivate(); /* * Create the local cleanup thread that will remove old events and log * data. */ if (pthread_create(&local_cleanup_thread, NULL, cleanupThread_main, NULL) < 0) { slon_log(SLON_FATAL, "main: cannot create cleanupThread - %s\n", strerror(errno)); slon_retry(); } /* * Create the local sync thread that will generate SYNC events if we had * local database updates. */ if (pthread_create(&local_sync_thread, NULL, syncThread_main, NULL) < 0) { slon_log(SLON_FATAL, "main: cannot create syncThread - %s\n", strerror(errno)); slon_retry(); } /* * Create the local monitor thread that will process monitoring requests */ if (monitor_threads) { if (pthread_create(&local_monitor_thread, NULL, monitorThread_main, NULL) < 0) { slon_log(SLON_FATAL, "main: cannot create monitorThread - %s\n", strerror(errno)); slon_retry(); } } /* * Wait until the scheduler has shut down all remote connections */ slon_log(SLON_INFO, "main: running scheduler mainloop\n"); if (sched_wait_mainloop() < 0) { slon_log(SLON_FATAL, "main: scheduler returned with error\n"); slon_retry(); } slon_log(SLON_INFO, "main: scheduler mainloop returned\n"); /* * Wait for all remote threads to finish */ main_thread = pthread_self(); slon_log(SLON_CONFIG, "main: wait for remote threads\n"); rtcfg_joinAllRemoteThreads(); /* * Wait for the local threads to finish */ if (pthread_join(local_event_thread, NULL) < 0) slon_log(SLON_ERROR, "main: cannot join localListenThread - %s\n", strerror(errno)); if (pthread_join(local_cleanup_thread, NULL) < 0) slon_log(SLON_ERROR, "main: cannot join cleanupThread - %s\n", strerror(errno)); if (pthread_join(local_sync_thread, NULL) < 0) slon_log(SLON_ERROR, "main: cannot join syncThread - %s\n", strerror(errno)); if (pthread_join(local_monitor_thread, NULL) < 0) slon_log(SLON_ERROR, "main: cannot join monitorThread - %s\n", strerror(errno)); slon_log(SLON_CONFIG, "main: done\n"); exit(0); }
int main(int argc, char **argv) { char *in_filename, *out_filename, *out_filename2; char *database; Oid lobjOid; PGconn *conn; PGresult *res; if (argc != 5) { fprintf(stderr, "Usage: %s database_name in_filename out_filename out_filename2\n", argv[0]); exit(1); } database = argv[1]; in_filename = argv[2]; out_filename = argv[3]; out_filename2 = argv[4]; /* * set up the connection */ conn = PQsetdb(NULL, NULL, NULL, NULL, database); /* check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn)); exit_nicely(conn); } res = PQexec(conn, "begin"); PQclear(res); printf("importing file \"%s\" ...\n", in_filename); /* lobjOid = importFile(conn, in_filename); */ lobjOid = lo_import(conn, in_filename); if (lobjOid == 0) fprintf(stderr, "%s\n", PQerrorMessage(conn)); else { printf("\tas large object %u.\n", lobjOid); printf("picking out bytes 4294967000-4294968000 of the large object\n"); pickout(conn, lobjOid, 4294967000U, 1000); printf("overwriting bytes 4294967000-4294968000 of the large object with X's\n"); overwrite(conn, lobjOid, 4294967000U, 1000); printf("exporting large object to file \"%s\" ...\n", out_filename); /* exportFile(conn, lobjOid, out_filename); */ if (lo_export(conn, lobjOid, out_filename) < 0) fprintf(stderr, "%s\n", PQerrorMessage(conn)); printf("truncating to 3294968000 bytes\n"); my_truncate(conn, lobjOid, 3294968000U); printf("exporting truncated large object to file \"%s\" ...\n", out_filename2); if (lo_export(conn, lobjOid, out_filename2) < 0) fprintf(stderr, "%s\n", PQerrorMessage(conn)); } res = PQexec(conn, "end"); PQclear(res); PQfinish(conn); return 0; }
int main(int argc, char **argv) { const char *conninfo; TestSpec *testspec; int i, j; int n; PGresult *res; PQExpBufferData wait_query; int opt; int nallsteps; Step **allsteps; while ((opt = getopt(argc, argv, "nV")) != -1) { switch (opt) { case 'n': dry_run = true; break; case 'V': puts("isolationtester (PostgreSQL) " PG_VERSION); exit(0); default: fprintf(stderr, "Usage: isolationtester [-n] [CONNINFO]\n"); return EXIT_FAILURE; } } /* * Make stdout unbuffered to match stderr; and ensure stderr is unbuffered * too, which it should already be everywhere except sometimes in Windows. */ setbuf(stdout, NULL); setbuf(stderr, NULL); /* * If the user supplies a non-option parameter on the command line, use it * as the conninfo string; otherwise default to setting dbname=postgres * and using environment variables or defaults for all other connection * parameters. */ if (argc > optind) conninfo = argv[optind]; else conninfo = "dbname = postgres"; /* Read the test spec from stdin */ spec_yyparse(); testspec = &parseresult; /* Create a lookup table of all steps. */ nallsteps = 0; for (i = 0; i < testspec->nsessions; i++) nallsteps += testspec->sessions[i]->nsteps; allsteps = malloc(nallsteps * sizeof(Step *)); n = 0; for (i = 0; i < testspec->nsessions; i++) { for (j = 0; j < testspec->sessions[i]->nsteps; j++) allsteps[n++] = testspec->sessions[i]->steps[j]; } qsort(allsteps, nallsteps, sizeof(Step *), &step_qsort_cmp); testspec->nallsteps = nallsteps; testspec->allsteps = allsteps; /* Verify that all step names are unique */ for (i = 1; i < testspec->nallsteps; i++) { if (strcmp(testspec->allsteps[i - 1]->name, testspec->allsteps[i]->name) == 0) { fprintf(stderr, "duplicate step name: %s\n", testspec->allsteps[i]->name); exit_nicely(); } } /* * In dry-run mode, just print the permutations that would be run, and * exit. */ if (dry_run) { run_testspec(testspec); return 0; } printf("Parsed test spec with %d sessions\n", testspec->nsessions); /* * Establish connections to the database, one for each session and an * extra for lock wait detection and global work. */ nconns = 1 + testspec->nsessions; conns = calloc(nconns, sizeof(PGconn *)); backend_pids = calloc(nconns, sizeof(*backend_pids)); for (i = 0; i < nconns; i++) { conns[i] = PQconnectdb(conninfo); if (PQstatus(conns[i]) != CONNECTION_OK) { fprintf(stderr, "Connection %d to database failed: %s", i, PQerrorMessage(conns[i])); exit_nicely(); } /* * Suppress NOTIFY messages, which otherwise pop into results at odd * places. */ res = PQexec(conns[i], "SET client_min_messages = warning;"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "message level setup failed: %s", PQerrorMessage(conns[i])); exit_nicely(); } PQclear(res); /* Get the backend pid for lock wait checking. */ res = PQexec(conns[i], "SELECT pg_backend_pid()"); if (PQresultStatus(res) == PGRES_TUPLES_OK) { if (PQntuples(res) == 1 && PQnfields(res) == 1) backend_pids[i] = strdup(PQgetvalue(res, 0, 0)); else { fprintf(stderr, "backend pid query returned %d rows and %d columns, expected 1 row and 1 column", PQntuples(res), PQnfields(res)); exit_nicely(); } } else { fprintf(stderr, "backend pid query failed: %s", PQerrorMessage(conns[i])); exit_nicely(); } PQclear(res); } /* Set the session index fields in steps. */ for (i = 0; i < testspec->nsessions; i++) { Session *session = testspec->sessions[i]; int stepindex; for (stepindex = 0; stepindex < session->nsteps; stepindex++) session->steps[stepindex]->session = i; } /* * Build the query we'll use to detect lock contention among sessions in * the test specification. Most of the time, we could get away with * simply checking whether a session is waiting for *any* lock: we don't * exactly expect concurrent use of test tables. However, autovacuum will * occasionally take AccessExclusiveLock to truncate a table, and we must * ignore that transient wait. */ initPQExpBuffer(&wait_query); appendPQExpBufferStr(&wait_query, "SELECT pg_catalog.pg_blocking_pids($1) && '{"); /* The spec syntax requires at least one session; assume that here. */ appendPQExpBufferStr(&wait_query, backend_pids[1]); for (i = 2; i < nconns; i++) appendPQExpBuffer(&wait_query, ",%s", backend_pids[i]); appendPQExpBufferStr(&wait_query, "}'::integer[]"); res = PQprepare(conns[0], PREP_WAITING, wait_query.data, 0, NULL); if (PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "prepare of lock wait query failed: %s", PQerrorMessage(conns[0])); exit_nicely(); } PQclear(res); termPQExpBuffer(&wait_query); /* * Run the permutations specified in the spec, or all if none were * explicitly specified. */ run_testspec(testspec); /* Clean up and exit */ for (i = 0; i < nconns; i++) PQfinish(conns[i]); return 0; }
/* * get a connection to master by reading repl_nodes, creating a connection * to each node (one at a time) and finding if it is a master or a standby * * NB: If master_conninfo_out may be NULL. If it is non-null, it is assumed to * point to allocated memory of MAXCONNINFO in length, and the master server * connection string is placed there. */ PGconn * getMasterConnection(PGconn *standby_conn, char *schema, int id, char *cluster, int *master_id, char *master_conninfo_out) { PGconn *master_conn = NULL; PGresult *res1; PGresult *res2; char sqlquery[QUERY_STR_LEN]; char master_conninfo_stack[MAXCONNINFO]; char *master_conninfo = &*master_conninfo_stack; char schema_quoted[MAXLEN]; int i; /* * If the caller wanted to get a copy of the connection info string, sub * out the local stack pointer for the pointer passed by the caller. */ if (master_conninfo_out != NULL) master_conninfo = master_conninfo_out; /* * XXX: This is copied in at least two other procedures * * Assemble the unquoted schema name */ { char *identifier = PQescapeIdentifier(standby_conn, schema, strlen(schema)); maxlen_snprintf(schema_quoted, "%s", identifier); PQfreemem(identifier); } /* find all nodes belonging to this cluster */ log_info(_("finding node list for cluster '%s'\n"), cluster); sqlquery_snprintf(sqlquery, "SELECT id, conninfo FROM %s.repl_nodes " " WHERE cluster = '%s' and id <> %d and not witness", schema_quoted, cluster, id); res1 = PQexec(standby_conn, sqlquery); if (PQresultStatus(res1) != PGRES_TUPLES_OK) { log_err(_("Can't get nodes info: %s\n"), PQerrorMessage(standby_conn)); PQclear(res1); PQfinish(standby_conn); exit(ERR_DB_QUERY); } for (i = 0; i < PQntuples(res1); i++) { /* initialize with the values of the current node being processed */ *master_id = atoi(PQgetvalue(res1, i, 0)); strncpy(master_conninfo, PQgetvalue(res1, i, 1), MAXCONNINFO); log_info(_("checking role of cluster node '%s'\n"), master_conninfo); master_conn = establishDBConnection(master_conninfo, false); if (PQstatus(master_conn) != CONNECTION_OK) continue; /* * Can't use the is_standby() function here because on error that * function closes the connection passed and exits. This still * needs to close master_conn first. */ res2 = PQexec(master_conn, "SELECT pg_is_in_recovery()"); if (PQresultStatus(res2) != PGRES_TUPLES_OK) { log_err(_("Can't get recovery state from this node: %s\n"), PQerrorMessage(master_conn)); PQclear(res2); PQfinish(master_conn); continue; } /* if false, this is the master */ if (strcmp(PQgetvalue(res2, 0, 0), "f") == 0) { PQclear(res2); PQclear(res1); return master_conn; } else { /* if it is a standby clear info */ PQclear(res2); PQfinish(master_conn); *master_id = -1; } } /* If we finish this loop without finding a master then * we doesn't have the info or the master has failed (or we * reached max_connections or superuser_reserved_connections, * anything else I'm missing?). * * Probably we will need to check the error to know if we need * to start failover procedure or just fix some situation on the * standby. */ PQclear(res1); return NULL; }
/* Do a query and return the (0,0) value. The query is assumed to be * a select. */ char *do_pg_query(request_rec * r, char *query, pg_auth_config_rec * sec) { PGresult *pg_result; PGconn *pg_conn; char *val; char *result = NULL; pg_errstr[0] = '\0'; #ifdef DEBUG_AUTH_PGSQL ap_log_rerror(APLOG_MARK, APLOG_WARNING, 0, r, "[mod_auth_pgsql.c] - do_pg_query - going to connect database \"%s\" ", sec->auth_pg_database); #endif /* DEBUG_AUTH_PGSQL */ pg_conn = PQsetdbLogin(sec->auth_pg_host, sec->auth_pg_port, sec->auth_pg_options, NULL, sec->auth_pg_database, sec->auth_pg_user, sec->auth_pg_pwd); if (PQstatus(pg_conn) != CONNECTION_OK) { PQreset(pg_conn); apr_snprintf(pg_errstr, MAX_STRING_LEN, "mod_auth_pgsql database connection error resetting %s", PQerrorMessage(pg_conn)); if (PQstatus(pg_conn) != CONNECTION_OK) { apr_snprintf(pg_errstr, MAX_STRING_LEN, "mod_auth_pgsql database connection error reset failed %s", PQerrorMessage(pg_conn)); PQfinish(pg_conn); return NULL; } } #ifdef DEBUG_AUTH_PGSQL ap_log_rerror(APLOG_MARK, APLOG_WARNING, 0, r, "[mod_auth_pgsql.c] - do_pg_query - going to execute query \"%s\" ", query); #endif /* DEBUG_AUTH_PGSQL */ pg_result = PQexec(pg_conn, query); if (pg_result == NULL) { apr_snprintf(pg_errstr, MAX_STRING_LEN, "PGSQL 2: %s -- Query: %s ", PQerrorMessage(pg_conn), query); PQfinish(pg_conn); return NULL; } if (PQresultStatus(pg_result) == PGRES_EMPTY_QUERY) { PQclear(pg_result); PQfinish(pg_conn); return NULL; } if (PQresultStatus(pg_result) != PGRES_TUPLES_OK) { apr_snprintf(pg_errstr, MAX_STRING_LEN, "PGSQL 3: %s -- Query: %s", PQerrorMessage(pg_conn), query); PQclear(pg_result); PQfinish(pg_conn); return NULL; } if (PQntuples(pg_result) == 1) { val = PQgetvalue(pg_result, 0, 0); if (val == NULL) { apr_snprintf(pg_errstr, MAX_STRING_LEN, "PGSQL 4: %s", PQerrorMessage(pg_conn)); PQclear(pg_result); PQfinish(pg_conn); return NULL; } if (!(result = (char *) apr_pcalloc(r->pool, strlen(val) + 1))) { apr_snprintf(pg_errstr, MAX_STRING_LEN, "Could not get memory for Postgres query."); PQclear(pg_result); PQfinish(pg_conn); return NULL; } strcpy(result, val); } /* ignore errors here ! */ PQclear(pg_result); PQfinish(pg_conn); return result; }
/* Create table table_name of type table_type. */ static logsql_table_ret log_sql_pgsql_create(request_rec *r, logsql_dbconnection *db, logsql_tabletype table_type, const char *table_name) { PGresult *result; const char *tabletype = apr_table_get(db->parms,"tabletype"); void (*handler) (int); char *type_suffix = NULL; char *create_prefix = "create table if not exists `"; char *create_suffix = NULL; char *create_sql; PGconn *conn = db->handle; /* if (!global_config.createtables) { return APR_SUCCESS; }*/ switch (table_type) { case LOGSQL_TABLE_ACCESS: create_suffix = "` (id char(19),\ agent varchar(255),\ bytes_sent int unsigned,\ child_pid smallint unsigned,\ cookie varchar(255),\ machine_id varchar(25),\ request_file varchar(255),\ referer varchar(255),\ remote_host varchar(50),\ remote_logname varchar(50),\ remote_user varchar(50),\ request_duration smallint unsigned,\ request_line varchar(255),\ request_method varchar(10),\ request_protocol varchar(10),\ request_time char(28),\ request_uri varchar(255),\ request_args varchar(255),\ server_port smallint unsigned,\ ssl_cipher varchar(25),\ ssl_keysize smallint unsigned,\ ssl_maxkeysize smallint unsigned,\ status smallint unsigned,\ time_stamp int unsigned,\ virtual_host varchar(255))"; break; case LOGSQL_TABLE_COOKIES: case LOGSQL_TABLE_HEADERSIN: case LOGSQL_TABLE_HEADERSOUT: case LOGSQL_TABLE_NOTES: create_suffix = "` (id char(19),\ item varchar(80),\ val varchar(80))"; break; } if (tabletype) { type_suffix = apr_pstrcat(r->pool, " TYPE=", tabletype, NULL); } /* Find memory long enough to hold the whole CREATE string + \0 */ create_sql = apr_pstrcat(r->pool, create_prefix, table_name, create_suffix, type_suffix, NULL); log_error(APLOG_MARK,APLOG_DEBUG,0, r->server,"create string: %s", create_sql); if (PQstatus(conn) != CONNECTION_OK) { return LOGSQL_QUERY_NOLINK; } /* A failed mysql_query() may send a SIGPIPE, so we ignore that signal momentarily. */ handler = signal(SIGPIPE, SIG_IGN); /* Run the create query */ result = PQexec(conn, create_sql); if (PQresultStatus(result) != PGRES_COMMAND_OK) { log_error(APLOG_MARK,APLOG_ERR,0, r->server,"failed to create table: %s", table_name); signal(SIGPIPE, handler); PQclear(result); return LOGSQL_TABLE_FAIL; } signal(SIGPIPE, handler); PQclear(result); return LOGSQL_TABLE_SUCCESS; }
/* * GetMasterConnection: This function makes a database connection with the given parameters. * The connection handle is returned. * An interactive password prompt is automatically issued if required. * This is a copy of the one in pg_dump. */ PGconn * GetMasterConnection(const char *progName, const char *dbname, const char *pghost, const char *pgport, const char *username, int reqPwd, int ignoreVersion, bool bDispatch) { char *pszPassword = NULL; bool need_pass = false; PGconn *pConn = NULL; SegmentDatabase masterDB; if (reqPwd) { pszPassword = simple_prompt("Password: "******"ERROR", progName, "out of memory when allocating password"); return NULL; } } masterDB.dbid = 0; masterDB.role = 0; masterDB.port = pgport ? atoi(pgport) : 5432; masterDB.pszHost = (char *) pghost; masterDB.pszDBName = (char *) dbname; masterDB.pszDBUser = (char *) username; masterDB.pszDBPswd = pszPassword; /* * Start the connection. Loop until we have a password if requested by * backend. */ do { need_pass = false; pConn = MakeDBConnection(&masterDB, bDispatch); if (pConn == NULL) { mpp_err_msg_cache("ERROR", progName, "failed to connect to database"); return (NULL); } if (PQstatus(pConn) == CONNECTION_BAD && strcmp(PQerrorMessage(pConn), "fe_sendauth: no password supplied\n") == 0 && !feof(stdin)) { PQfinish(pConn); need_pass = true; free(pszPassword); pszPassword = NULL; pszPassword = simple_prompt("Password: "******"ERROR", progName, "connection to database \"%s\" failed : %s", PQdb(pConn), PQerrorMessage(pConn)); return (NULL); } return pConn; }
void do_postgres_full_connect(VALUE self, PGconn *db) { VALUE r_host; char *host = NULL; if ((r_host = rb_iv_get(self, "@host")) != Qnil) { host = StringValuePtr(r_host); } VALUE r_user; char *user = NULL; if ((r_user = rb_iv_get(self, "@user")) != Qnil) { user = StringValuePtr(r_user); } VALUE r_password; char *password = NULL; if ((r_password = rb_iv_get(self, "@password")) != Qnil) { password = StringValuePtr(r_password); } VALUE r_port; const char *port = "5432"; if ((r_port = rb_iv_get(self, "@port")) != Qnil) { port = StringValuePtr(r_port); } VALUE r_path; char *path = NULL; char *database = NULL; if ((r_path = rb_iv_get(self, "@path")) != Qnil) { path = StringValuePtr(r_path); database = strtok(path, "/"); } if (!database || !*database) { database = NULL; } VALUE r_query = rb_iv_get(self, "@query"); const char *search_path = data_objects_get_uri_option(r_query, "search_path"); db = PQsetdbLogin( host, port, NULL, NULL, database, user, password ); if (PQstatus(db) == CONNECTION_BAD) { rb_raise(eDO_ConnectionError, "%s", PQerrorMessage(db)); } PGresult *result; if (search_path) { char *search_path_query; if (!(search_path_query = calloc(256, sizeof(char)))) { rb_memerror(); } snprintf(search_path_query, 256, "set search_path to %s;", search_path); r_query = rb_str_new2(search_path_query); result = do_postgres_cCommand_execute(Qnil, self, db, r_query); if (PQresultStatus(result) != PGRES_COMMAND_OK) { free(search_path_query); do_postgres_raise_error(self, result, r_query); } free(search_path_query); } const char *backslash_off = "SET backslash_quote = off"; const char *standard_strings_on = "SET standard_conforming_strings = on"; const char *warning_messages = "SET client_min_messages = warning"; const char *date_format = "SET datestyle = ISO"; VALUE r_options; r_options = rb_str_new2(backslash_off); result = do_postgres_cCommand_execute(Qnil, self, db, r_options); if (PQresultStatus(result) != PGRES_COMMAND_OK) { rb_warn("%s", PQresultErrorMessage(result)); } r_options = rb_str_new2(standard_strings_on); result = do_postgres_cCommand_execute(Qnil, self, db, r_options); if (PQresultStatus(result) != PGRES_COMMAND_OK) { rb_warn("%s", PQresultErrorMessage(result)); } r_options = rb_str_new2(warning_messages); result = do_postgres_cCommand_execute(Qnil, self, db, r_options); if (PQresultStatus(result) != PGRES_COMMAND_OK) { rb_warn("%s", PQresultErrorMessage(result)); } r_options = rb_str_new2(date_format); result = do_postgres_cCommand_execute(Qnil, self, db, r_options); if (PQresultStatus(result) != PGRES_COMMAND_OK) { rb_warn("%s", PQresultErrorMessage(result)); } VALUE encoding = rb_iv_get(self, "@encoding"); #ifdef HAVE_PQSETCLIENTENCODING VALUE pg_encoding = rb_hash_aref(data_objects_const_get(mDO_PostgresEncoding, "MAP"), encoding); if (pg_encoding != Qnil) { if (PQsetClientEncoding(db, rb_str_ptr_readonly(pg_encoding))) { rb_raise(eDO_ConnectionError, "Couldn't set encoding: %s", rb_str_ptr_readonly(encoding)); } else { #ifdef HAVE_RUBY_ENCODING_H rb_iv_set(self, "@encoding_id", INT2FIX(rb_enc_find_index(rb_str_ptr_readonly(encoding)))); #endif rb_iv_set(self, "@pg_encoding", pg_encoding); } } else { rb_warn("Encoding %s is not a known Ruby encoding for PostgreSQL\n", rb_str_ptr_readonly(encoding)); rb_iv_set(self, "@encoding", rb_str_new2("UTF-8")); #ifdef HAVE_RUBY_ENCODING_H rb_iv_set(self, "@encoding_id", INT2FIX(rb_enc_find_index("UTF-8"))); #endif rb_iv_set(self, "@pg_encoding", rb_str_new2("UTF8")); } #endif rb_iv_set(self, "@connection", Data_Wrap_Struct(rb_cObject, 0, 0, db)); }
bool pgConn::Initialize() { // Set client encoding to Unicode/Ascii, Datestyle to ISO, and ask for notices. if (PQstatus(conn) == CONNECTION_OK) { connStatus = PGCONN_OK; PQsetNoticeProcessor(conn, pgNoticeProcessor, this); wxString sql = wxT("SET DateStyle=ISO;\nSET client_min_messages=notice;\n"); if (BackendMinimumVersion(9, 0)) sql += wxT("SET bytea_output=escape;\n"); sql += wxT("SELECT oid, pg_encoding_to_char(encoding) AS encoding, datlastsysoid\n") wxT(" FROM pg_database WHERE "); if (save_oid) sql += wxT("oid = ") + NumToStr(save_oid); else { // Note, can't use qtDbString here as we don't know the server version yet. wxString db = save_database; db.Replace(wxT("\\"), wxT("\\\\")); db.Replace(wxT("'"), wxT("''")); sql += wxT("datname=") + qtString(db); } pgSet *set = ExecuteSet(sql); if (set) { if (set->ColNumber(wxT("\"datlastsysoid\"")) >= 0) needColQuoting = true; lastSystemOID = set->GetOid(wxT("datlastsysoid")); dbOid = set->GetOid(wxT("oid")); wxString encoding = set->GetVal(wxT("encoding")); if (encoding != wxT("SQL_ASCII") && encoding != wxT("MULE_INTERNAL")) { encoding = wxT("UNICODE"); conv = &wxConvUTF8; } else conv = &wxConvLibc; wxLogInfo(wxT("Setting client_encoding to '%s'"), encoding.c_str()); if (PQsetClientEncoding(conn, encoding.ToAscii())) { wxLogError(wxT("%s"), GetLastError().c_str()); } delete set; // Switch to the requested default role if supported by backend if (dbRole != wxEmptyString && BackendMinimumVersion(8, 1)) { sql = wxT("SET ROLE TO "); sql += qtIdent(dbRole); pgSet *set = ExecuteSet(sql); if (set) delete set; else return false; } return true; } } return false; }
static int pgasp_handler (request_rec * r) { char cursor_string[256]; pgasp_config* config = (pgasp_config*) ap_get_module_config(r->server->module_config, &pgasp_module ) ; pgasp_dir_config* dir_config = (pgasp_dir_config*) ap_get_module_config(r->per_dir_config, &pgasp_module ) ; apr_table_t * GET = NULL, *GETargs = NULL; apr_array_header_t * POST; PGconn * pgc; PGresult * pgr; int i, j, allowed_to_serve, filename_length = 0; int field_count, tuple_count; char * requested_file; char *basename; params_t params; /* PQexecParams doesn't seem to like zero-length strings, so we feed it a dummy */ const char * dummy_get = "nothing"; const char * dummy_user = "******"; const char * cursor_values[2] = { r -> args ? apr_pstrdup(r->pool, r -> args) : dummy_get, r->user ? r->user : dummy_user }; int cursor_value_lengths[2] = { strlen(cursor_values[0]), strlen(cursor_values[1]) }; int cursor_value_formats[2] = { 0, 0 }; if (!r -> handler || strcmp (r -> handler, "pgasp-handler") ) return DECLINED; if (!r -> method || (strcmp (r -> method, "GET") && strcmp (r -> method, "POST")) ) return DECLINED; if (config->is_enabled != true) return OK; /* pretending we have responded, may return DECLINED in the future */ requested_file = apr_pstrdup (r -> pool, r -> path_info /*filename*/); i = strlen(requested_file) - 1; while (i > 0) { if (requested_file[i] == '.') filename_length = i; if (requested_file[i] == '/') break; i--; } if (i >= 0) { requested_file += i+1; /* now pointing to foo.pgasp instead of /var/www/.../foo.pgasp */ if (filename_length > i) filename_length -= i+1; } allowed_to_serve = false; for (i = 0; i < config->allowed_count; i++) { if (!strcmp(config->allowed[i], requested_file)) { allowed_to_serve = true; break; } } if (config->allowed_count == 0) allowed_to_serve = true; if (!allowed_to_serve) { ap_set_content_type(r, "text/plain"); ap_rprintf(r, "Hello there\nThis is PGASP\nEnabled: %s\n", config->is_enabled ? "On" : "Off"); ap_rprintf(r, "Requested: %s\n", requested_file); ap_rprintf(r, "Allowed: %s\n", allowed_to_serve ? "Yes" : "No"); return OK; /* pretending we have served the file, may return HTTP_FORDIDDEN in the future */ } if (filename_length == 0) { basename = requested_file; } else { basename = apr_pstrndup(r->pool, requested_file, filename_length); } ap_args_to_table(r, &GETargs); if (OK != ap_parse_form_data(r, NULL, &POST, -1, (~((apr_size_t)0)))) { __(r->server, " ** ap_parse_form_data is NOT OK"); } GET = (NULL == GET) ? GETargs : apr_table_overlay(r->pool, GETargs, GET); // move all POST parameters into GET table { ap_form_pair_t *pair; char *buffer; apr_off_t len; apr_size_t size; while (NULL != (pair = apr_array_pop(POST))) { apr_brigade_length(pair->value, 1, &len); size = (apr_size_t) len; buffer = apr_palloc(r->pool, size + 1); apr_brigade_flatten(pair->value, buffer, &size); buffer[len] = 0; apr_table_setn(GET, apr_pstrdup(r->pool, pair->name), buffer); //should name and value be ap_unescape_url() -ed? // __(r->server, "POST[%s]: %s", pair->name, buffer); } } params.r = r; params.args = NULL; apr_table_do(tab_args, ¶ms, GET, NULL); params.args = apr_pstrcat(r->pool, "&", params.args, "&", NULL); cursor_values[0] = params.args; cursor_value_lengths[0] = strlen(cursor_values[0]); /* set response content type according to configuration or to default value */ ap_set_content_type(r, dir_config->content_type_set ? dir_config->content_type : "text/html"); /* now connecting to Postgres, getting function output, and printing it */ pgc = pgasp_pool_open (r->server); if (PQstatus(pgc) != CONNECTION_OK) { spit_pg_error ("connect"); pgasp_pool_close(r->server, pgc); return OK; } /* removing extention (.pgasp or other) from file name, and adding "f_" for function name, i.e. foo.pgasp becomes psp_foo() */ snprintf(cursor_string, sizeof(cursor_string), "select * from f_%s($1::varchar)", basename); /* passing GET as first (and only) parameter */ if (0 == PQsendQueryParams (pgc, cursor_string, 1, NULL, cursor_values, cursor_value_lengths, cursor_value_formats, 0)) { spit_pg_error ("sending async query with params"); return clean_up_connection(r->server); } if (0 == PQsetSingleRowMode(pgc)) { ap_log_error(APLOG_MARK, APLOG_WARNING, 0, r->server, "can not fall into single raw mode to fetch data"); } while (NULL != (pgr = PQgetResult(pgc))) { if (PQresultStatus(pgr) != PGRES_TUPLES_OK && PQresultStatus(pgr) != PGRES_SINGLE_TUPLE) { spit_pg_error ("fetch data"); return clean_up_connection(r->server); } /* the following counts and for-loop may seem excessive as it's just 1 row/1 field, but might need it in the future */ field_count = PQnfields(pgr); tuple_count = PQntuples(pgr); for (i = 0; i < tuple_count; i++) { for (j = 0; j < field_count; j++) ap_rprintf(r, "%s", PQgetvalue(pgr, i, j)); ap_rprintf(r, "\n"); } PQclear (pgr); } pgasp_pool_close(r->server, pgc); return OK; }
bool pgConn::IsSSLconnected() { return (conn && PQstatus(conn) == CONNECTION_OK && PQgetssl(conn) != NULL); }
static int c_psql_write (const data_set_t *ds, const value_list_t *vl, user_data_t *ud) { c_psql_database_t *db; char time_str[32]; char values_name_str[1024]; char values_type_str[1024]; char values_str[1024]; const char *params[9]; int success = 0; size_t i; if ((ud == NULL) || (ud->data == NULL)) { log_err ("c_psql_write: Invalid user data."); return -1; } db = ud->data; assert (db->database != NULL); assert (db->writers != NULL); if (cdtime_to_iso8601 (time_str, sizeof (time_str), vl->time) == 0) { log_err ("c_psql_write: Failed to convert time to ISO 8601 format"); return -1; } if (values_name_to_sqlarray (ds, values_name_str, sizeof (values_name_str)) == NULL) return -1; #define VALUE_OR_NULL(v) ((((v) == NULL) || (*(v) == '\0')) ? NULL : (v)) params[0] = time_str; params[1] = vl->host; params[2] = vl->plugin; params[3] = VALUE_OR_NULL(vl->plugin_instance); params[4] = vl->type; params[5] = VALUE_OR_NULL(vl->type_instance); params[6] = values_name_str; #undef VALUE_OR_NULL if( db->expire_delay > 0 && vl->time < (cdtime() - vl->interval - db->expire_delay) ) { log_info ("c_psql_write: Skipped expired value @ %s - %s/%s-%s/%s-%s/%s", params[0], params[1], params[2], params[3], params[4], params[5], params[6] ); return 0; } pthread_mutex_lock (&db->db_lock); if (0 != c_psql_check_connection (db)) { pthread_mutex_unlock (&db->db_lock); return -1; } if ((db->commit_interval > 0) && (db->next_commit == 0)) c_psql_begin (db); for (i = 0; i < db->writers_num; ++i) { c_psql_writer_t *writer; PGresult *res; writer = db->writers[i]; if (values_type_to_sqlarray (ds, values_type_str, sizeof (values_type_str), writer->store_rates) == NULL) { pthread_mutex_unlock (&db->db_lock); return -1; } if (values_to_sqlarray (ds, vl, values_str, sizeof (values_str), writer->store_rates) == NULL) { pthread_mutex_unlock (&db->db_lock); return -1; } params[7] = values_type_str; params[8] = values_str; res = PQexecParams (db->conn, writer->statement, STATIC_ARRAY_SIZE (params), NULL, (const char *const *)params, NULL, NULL, /* return text data */ 0); if ((PGRES_COMMAND_OK != PQresultStatus (res)) && (PGRES_TUPLES_OK != PQresultStatus (res))) { PQclear (res); if ((CONNECTION_OK != PQstatus (db->conn)) && (0 == c_psql_check_connection (db))) { /* try again */ res = PQexecParams (db->conn, writer->statement, STATIC_ARRAY_SIZE (params), NULL, (const char *const *)params, NULL, NULL, /* return text data */ 0); if ((PGRES_COMMAND_OK == PQresultStatus (res)) || (PGRES_TUPLES_OK == PQresultStatus (res))) { PQclear (res); success = 1; continue; } } log_err ("Failed to execute SQL query: %s", PQerrorMessage (db->conn)); log_info ("SQL query was: '%s', " "params: %s, %s, %s, %s, %s, %s, %s, %s", writer->statement, params[0], params[1], params[2], params[3], params[4], params[5], params[6], params[7]); /* this will abort any current transaction -> restart */ if (db->next_commit > 0) c_psql_commit (db); pthread_mutex_unlock (&db->db_lock); return -1; } PQclear (res); success = 1; } if ((db->next_commit > 0) && (cdtime () > db->next_commit)) c_psql_commit (db); pthread_mutex_unlock (&db->db_lock); if (! success) return -1; return 0; } /* c_psql_write */
/* * * main * */ int main(int argc, char *argv[]) { struct adhoc_opts options; int successResult; char *password = NULL; char *password_prompt = NULL; bool new_pass; set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql")); if (argc > 1) { if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0) { usage(); exit(EXIT_SUCCESS); } if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0) { showVersion(); exit(EXIT_SUCCESS); } } #ifdef WIN32 setvbuf(stderr, NULL, _IONBF, 0); #endif setup_cancel_handler(); pset.progname = get_progname(argv[0]); pset.db = NULL; setDecimalLocale(); pset.encoding = PQenv2encoding(); pset.queryFout = stdout; pset.queryFoutPipe = false; pset.cur_cmd_source = stdin; pset.cur_cmd_interactive = false; /* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */ pset.popt.topt.format = PRINT_ALIGNED; pset.popt.topt.border = 1; pset.popt.topt.pager = 1; pset.popt.topt.start_table = true; pset.popt.topt.stop_table = true; pset.popt.default_footer = true; /* We must get COLUMNS here before readline() sets it */ pset.popt.topt.env_columns = getenv("COLUMNS") ? atoi(getenv("COLUMNS")) : 0; pset.notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout))); pset.getPassword = TRI_DEFAULT; EstablishVariableSpace(); SetVariable(pset.vars, "VERSION", PG_VERSION_STR); /* Default values for variables */ SetVariableBool(pset.vars, "AUTOCOMMIT"); SetVariable(pset.vars, "VERBOSITY", "default"); SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1); SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2); SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3); parse_psql_options(argc, argv, &options); if (!pset.popt.topt.fieldSep) pset.popt.topt.fieldSep = pg_strdup(DEFAULT_FIELD_SEP); if (!pset.popt.topt.recordSep) pset.popt.topt.recordSep = pg_strdup(DEFAULT_RECORD_SEP); if (options.username == NULL) password_prompt = pg_strdup(_("Password: "******"Password for user %s: ")) - 2 + strlen(options.username) + 1); sprintf(password_prompt, _("Password for user %s: "), options.username); } if (pset.getPassword == TRI_YES) password = simple_prompt(password_prompt, 100, false); /* loop until we have a password if requested by backend */ do { #define PARAMS_ARRAY_SIZE 7 const char **keywords = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords)); const char **values = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*values)); keywords[0] = "host"; values[0] = options.host; keywords[1] = "port"; values[1] = options.port; keywords[2] = "user"; values[2] = options.username; keywords[3] = "password"; values[3] = password; keywords[4] = "dbname"; values[4] = (options.action == ACT_LIST_DB && options.dbname == NULL) ? "postgres" : options.dbname; keywords[5] = "fallback_application_name"; values[5] = pset.progname; keywords[6] = NULL; values[6] = NULL; new_pass = false; pset.db = PQconnectdbParams(keywords, values, true); free(keywords); free(values); if (PQstatus(pset.db) == CONNECTION_BAD && PQconnectionNeedsPassword(pset.db) && password == NULL && pset.getPassword != TRI_NO) { PQfinish(pset.db); password = simple_prompt(password_prompt, 100, false); new_pass = true; } } while (new_pass); free(password); free(password_prompt); if (PQstatus(pset.db) == CONNECTION_BAD) { fprintf(stderr, "%s: %s", pset.progname, PQerrorMessage(pset.db)); PQfinish(pset.db); exit(EXIT_BADCONN); } PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL); SyncVariables(); if (options.action == ACT_LIST_DB) { int success = listAllDbs(false); PQfinish(pset.db); exit(success ? EXIT_SUCCESS : EXIT_FAILURE); } if (options.logfilename) { pset.logfile = fopen(options.logfilename, "a"); if (!pset.logfile) fprintf(stderr, _("%s: could not open log file \"%s\": %s\n"), pset.progname, options.logfilename, strerror(errno)); } /* * Now find something to do */ /* * process file given by -f */ if (options.action == ACT_FILE) { if (!options.no_psqlrc) process_psqlrc(argv[0]); successResult = process_file(options.action_string, options.single_txn); } /* * process slash command if one was given to -c */ else if (options.action == ACT_SINGLE_SLASH) { PsqlScanState scan_state; if (pset.echo == PSQL_ECHO_ALL) puts(options.action_string); scan_state = psql_scan_create(); psql_scan_setup(scan_state, options.action_string, strlen(options.action_string)); successResult = HandleSlashCmds(scan_state, NULL) != PSQL_CMD_ERROR ? EXIT_SUCCESS : EXIT_FAILURE; psql_scan_destroy(scan_state); } /* * If the query given to -c was a normal one, send it */ else if (options.action == ACT_SINGLE_QUERY) { if (pset.echo == PSQL_ECHO_ALL) puts(options.action_string); successResult = SendQuery(options.action_string) ? EXIT_SUCCESS : EXIT_FAILURE; } /* * or otherwise enter interactive main loop */ else { if (!options.no_psqlrc) process_psqlrc(argv[0]); connection_warnings(true); if (!pset.quiet && !pset.notty) printf(_("Type \"help\" for help.\n\n")); if (!pset.notty) initializeInput(options.no_readline ? 0 : 1); if (options.action_string) /* -f - was used */ pset.inputfile = "<stdin>"; successResult = MainLoop(stdin); } /* clean up */ if (pset.logfile) fclose(pset.logfile); PQfinish(pset.db); setQFout(NULL); return successResult; }
int main(int argc, char **argv) { PGconn *conn; PQExpBufferData sql; PGresult *res; PGresult *pkrel_res; PGresult *fkrel_res; char *fk_relname; char *fk_nspname; char *fk_attname; char *pk_relname; char *pk_nspname; int fk, pk; /* loop counters */ if (argc != 2) { fprintf(stderr, "Usage: %s database\n", argv[0]); exit(EXIT_FAILURE); } initPQExpBuffer(&sql); appendPQExpBuffer(&sql, "dbname=%s", argv[1]); conn = PQconnectdb(sql.data); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "connection error: %s\n", PQerrorMessage(conn)); exit(EXIT_FAILURE); } /* Get a list of relations that have OIDs */ printfPQExpBuffer(&sql, "%s", "SET search_path = public;" "SELECT c.relname, (SELECT nspname FROM " "pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname " "FROM pg_catalog.pg_class c " "WHERE c.relkind = " CppAsString2(RELKIND_RELATION) " AND c.relhasoids " "ORDER BY nspname, c.relname" ); res = PQexec(conn, sql.data); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn)); exit(EXIT_FAILURE); } pkrel_res = res; /* Get a list of columns of OID type (or any OID-alias type) */ printfPQExpBuffer(&sql, "%s", "SELECT c.relname, " "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, " "a.attname " "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a " "WHERE a.attnum > 0" " AND c.relkind = " CppAsString2(RELKIND_RELATION) " AND a.attrelid = c.oid" " AND a.atttypid IN ('pg_catalog.oid'::regtype, " " 'pg_catalog.regclass'::regtype, " " 'pg_catalog.regoper'::regtype, " " 'pg_catalog.regoperator'::regtype, " " 'pg_catalog.regproc'::regtype, " " 'pg_catalog.regprocedure'::regtype, " " 'pg_catalog.regtype'::regtype, " " 'pg_catalog.regconfig'::regtype, " " 'pg_catalog.regdictionary'::regtype) " "ORDER BY nspname, c.relname, a.attnum" ); res = PQexec(conn, sql.data); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn)); exit(EXIT_FAILURE); } fkrel_res = res; /* * For each column and each relation-having-OIDs, look to see if the * column contains any values matching entries in the relation. */ for (fk = 0; fk < PQntuples(fkrel_res); fk++) { fk_relname = PQgetvalue(fkrel_res, fk, 0); fk_nspname = PQgetvalue(fkrel_res, fk, 1); fk_attname = PQgetvalue(fkrel_res, fk, 2); for (pk = 0; pk < PQntuples(pkrel_res); pk++) { pk_relname = PQgetvalue(pkrel_res, pk, 0); pk_nspname = PQgetvalue(pkrel_res, pk, 1); printfPQExpBuffer(&sql, "SELECT 1 " "FROM \"%s\".\"%s\" t1, " "\"%s\".\"%s\" t2 " "WHERE t1.\"%s\"::pg_catalog.oid = t2.oid " "LIMIT 1", fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname); res = PQexec(conn, sql.data); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn)); exit(EXIT_FAILURE); } if (PQntuples(res) != 0) printf("Join %s.%s.%s => %s.%s.oid\n", fk_nspname, fk_relname, fk_attname, pk_nspname, pk_relname); PQclear(res); } } PQclear(fkrel_res); /* Now, do the same for referencing columns that are arrays */ /* Get a list of columns of OID-array type (or any OID-alias type) */ printfPQExpBuffer(&sql, "%s", "SELECT c.relname, " "(SELECT nspname FROM pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace) AS nspname, " "a.attname " "FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a " "WHERE a.attnum > 0" " AND c.relkind = " CppAsString2(RELKIND_RELATION) " AND a.attrelid = c.oid" " AND a.atttypid IN ('pg_catalog.oid[]'::regtype, " " 'pg_catalog.regclass[]'::regtype, " " 'pg_catalog.regoper[]'::regtype, " " 'pg_catalog.regoperator[]'::regtype, " " 'pg_catalog.regproc[]'::regtype, " " 'pg_catalog.regprocedure[]'::regtype, " " 'pg_catalog.regtype[]'::regtype, " " 'pg_catalog.regconfig[]'::regtype, " " 'pg_catalog.regdictionary[]'::regtype) " "ORDER BY nspname, c.relname, a.attnum" ); res = PQexec(conn, sql.data); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn)); exit(EXIT_FAILURE); } fkrel_res = res; /* * For each column and each relation-having-OIDs, look to see if the * column contains any values matching entries in the relation. */ for (fk = 0; fk < PQntuples(fkrel_res); fk++) { fk_relname = PQgetvalue(fkrel_res, fk, 0); fk_nspname = PQgetvalue(fkrel_res, fk, 1); fk_attname = PQgetvalue(fkrel_res, fk, 2); for (pk = 0; pk < PQntuples(pkrel_res); pk++) { pk_relname = PQgetvalue(pkrel_res, pk, 0); pk_nspname = PQgetvalue(pkrel_res, pk, 1); printfPQExpBuffer(&sql, "SELECT 1 " "FROM \"%s\".\"%s\" t1, " "\"%s\".\"%s\" t2 " "WHERE t2.oid = ANY(t1.\"%s\")" "LIMIT 1", fk_nspname, fk_relname, pk_nspname, pk_relname, fk_attname); res = PQexec(conn, sql.data); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "sql error: %s\n", PQerrorMessage(conn)); exit(EXIT_FAILURE); } if (PQntuples(res) != 0) printf("Join %s.%s.%s []=> %s.%s.oid\n", fk_nspname, fk_relname, fk_attname, pk_nspname, pk_relname); PQclear(res); } } PQclear(fkrel_res); PQclear(pkrel_res); PQfinish(conn); termPQExpBuffer(&sql); exit(EXIT_SUCCESS); }
int main(int argc, char* argv[]) { char sPGhost[26], sPGport[26], sPGdb[26], sPGuser[26], sPGpass[26]; printf("Postgres connection settings\n Host>"); scanf("%s", sPGhost); printf(" Port>"); scanf("%s", sPGport); printf(" Base>"); scanf("%s", sPGdb); printf(" User>"); scanf("%s", sPGuser); printf(" Pass>"); scanf("%s", sPGpass); /////////////////////////////// ///////PGSQL Connect/////////// /////////////////////////////// PGconn* mPGconn = NULL; mPGconn = PQsetdbLogin(sPGhost, sPGport, NULL, NULL, sPGdb, sPGuser, sPGpass); if (PQstatus(mPGconn) != CONNECTION_OK) { printf("Could not connect to Postgre database at [%s]: \n %s\n", sPGhost, PQerrorMessage(mPGconn)); PQfinish(mPGconn); return 1; } else { printf("Connected to Postgre database at [%s]\n", sPGhost); printf(" PostgreSQL server ver: [%d]\n\n", PQserverVersion(mPGconn)); } /// Set dummy notice processor PQsetNoticeProcessor(mPGconn, pg_notice, mPGconn); /////////////////////////////// ///////MySQL Connect/////////// /////////////////////////////// MYSQL* mysqlInit; mysqlInit = mysql_init(NULL); if (!mysqlInit) { printf("Could not initialize Mysql connection\n"); return 1; } char sMYhost[26], sMYdb[26], sMYuser[26], sMYpass[26]; int iMYport; printf("Mysql connection settings \n Host>"); scanf("%s", sMYhost); printf(" Port>"); scanf("%d", &iMYport); printf(" Base>"); scanf("%s", sMYdb); printf(" User>"); scanf("%s", sMYuser); printf(" Pass>"); scanf("%s", sMYpass); mysql_options(mysqlInit, MYSQL_SET_CHARSET_NAME, "utf8"); MYSQL* mMysql; mMysql = mysql_real_connect(mysqlInit, sMYhost, sMYuser, sMYpass, sMYdb, iMYport, NULL, 0); if (mMysql) { printf("Connected to MySQL database at [%s] \n", sMYhost); printf(" MySQL client library: [%s] \n", mysql_get_client_info()); printf(" MySQL server ver: [%s] \n\n", mysql_get_server_info(mMysql)); } else { printf("Could not connect to MySQL database at [%s]:\n %s\n", sMYhost , mysql_error(mysqlInit)); mysql_close(mysqlInit); return 1; } ////////////////////////////////////////////////////////////////////////// ////////////////////////////////////////////////////////////////////////// MYSQL_RES* result = NULL; MYSQL_ROW row; MYSQL_FIELD* fields = NULL; uint64 rowCount = 0; uint32 fieldCount = 0; result = mysql_list_tables(mMysql , NULL); rowCount = mysql_num_rows(result); /***********************/ /* get list of tables */ /***********************/ T_TableList mTableList; mTableList.reserve((size_t)rowCount); while ((row = mysql_fetch_row(result)) != NULL) { for (uint32 i = 0; i < mysql_num_fields(result); i++) { mTableList.push_back(row[i]); } } mysql_free_result(result); /****************************************/ /* convert filed type and default type */ /****************************************/ T_Table m_Table; TDataBase m_DataBase_Map; m_DataBase_Map.clear(); for (uint32 j = 0; j < mTableList.size(); ++j) { result = mysql_list_fields(mMysql, mTableList[j].c_str(), NULL); fieldCount = mysql_num_fields(result); fields = mysql_fetch_fields(result); for (uint32 i = 0; i < fieldCount; ++i) { sField mfield; mfield.name = fields[i].name; if (!fields[i].def) { mfield.def = "NULL"; } else if (!strcmp(fields[i].def, "0000-00-00 00:00:00")) { /// Convert MySQL Default timestamp to PGSQL Default timestamp mfield.def.append("'1970-01-01 00:00:00'"); } else { /// Append ' mfield.def.append("'"); mfield.def.append(fields[i].def);; mfield.def.append("'"); } mfield.type = ConvertNativeType(fields[i].type, fields[i].length); mfield.flags = fields[i].flags; m_Table.push_back(mfield); } m_DataBase_Map[mTableList[j]] = m_Table; m_Table.clear(); mysql_free_result(result); } /******************************************/ /* Conversion of the layout of the tables */ /******************************************/ uint32 count = 0; TDataBase::const_iterator citr; for (citr = m_DataBase_Map.begin(); citr != m_DataBase_Map.end(); ++citr) { ostringstream sql_str; sql_str << "DROP TABLE IF EXISTS " << (*citr).first.c_str() << ";\n"; sql_str << "CREATE TABLE " << (*citr).first.c_str() << "(\n"; T_Table::const_iterator v_iter; ostringstream prim_key_str; ostringstream index_str; for (v_iter = (*citr).second.begin(); v_iter != (*citr).second.end(); ++v_iter) { sql_str << " " << (*v_iter).name; if (((*v_iter).flags & AUTO_INCREMENT_FLAG) != 0) { /// AUTO_INCREMENT fields not have "default" data sql_str << " bigserial"; } else { sql_str << " " << (*v_iter).type; sql_str << " default " << (*v_iter).def; } /// IF column have PRIMARY KEY flag then use column in PRIMARY KEY if (IS_PRI_KEY((*v_iter).flags) != 0) { if (prim_key_str.str().size()) prim_key_str << ", "; else { prim_key_str << "ALTER TABLE "; prim_key_str << (*citr).first.c_str(); prim_key_str << " ADD CONSTRAINT pk_"; prim_key_str << (*citr).first.c_str(); prim_key_str << "_"; prim_key_str << (*v_iter).name; prim_key_str << " PRIMARY KEY ("; } prim_key_str << (*v_iter).name; } else if (((*v_iter).flags & MULTIPLE_KEY_FLAG) != 0) { /// IF column have INDEX flag then create INDEX index_str << "CREATE INDEX idx_"; index_str << (*citr).first.c_str(); index_str << "_"; index_str << (*v_iter).name; index_str << " ON "; index_str << (*citr).first.c_str(); index_str << " USING btree ("; index_str << (*v_iter).name; index_str << ");\n"; } else if (((*v_iter).flags & UNIQUE_KEY_FLAG) != 0) { /// IF column have UNIQUE INDEX flag then create INDEX index_str << "CREATE UNIQUE INDEX uidx_"; index_str << (*citr).first.c_str(); index_str << "_"; index_str << (*v_iter).name; index_str << " ON "; index_str << (*citr).first.c_str(); index_str << " USING btree ("; index_str << (*v_iter).name; index_str << ");\n"; } /// don't output "," for last column if (v_iter + 1 != (*citr).second.end()) sql_str << ",\n"; else sql_str << "\n"; } sql_str << ")\n"; /// Out Table structure PG_Exec_str(sql_str.str(), mPGconn); /// out PRIMARY KEY if (prim_key_str.str().size()) { prim_key_str << ")"; PG_Exec_str(prim_key_str.str(), mPGconn); } /// out INDEX's if (index_str.str().size()) PG_Exec_str(index_str.str(), mPGconn); ++count; printf("Convert [%d] tables...\r", count); } printf("Completed the conversion of [%d] tables!\n", count); /****************/ /* Copying data */ /****************/ count = 0; for (uint32 j = 0; j < mTableList.size(); ++j) { ostringstream sql_str; sql_str << "SELECT * FROM "; sql_str << mTableList[j].c_str(); if (mysql_query(mysqlInit, sql_str.str().c_str())) continue; if (!(result = mysql_store_result(mysqlInit))) continue; while ((row = mysql_fetch_row(result)) != NULL) { ostringstream insert_str; insert_str << "INSERT INTO "; insert_str << mTableList[j].c_str(); insert_str << " VALUES ("; fieldCount = mysql_num_fields(result); fields = mysql_fetch_fields(result); for (uint32 i = 0 ; i < fieldCount ; ++i) { if (!row[i]) insert_str << "NULL"; else { if (IsNeeedEscapeString(fields[i].type)) { string field_str = row[i]; PG_Escape_Str(field_str); insert_str << "E'"; insert_str << field_str.c_str(); insert_str << "'"; } else if (!strcmp(row[i], "0000-00-00 00:00:00")) { /// Convert MySQL timestamp to PGSQL timestamp insert_str << "'1970-01-01 00:00:00'"; } else { insert_str << "'"; insert_str << row[i]; insert_str << "'"; } } /// don't output "," for last column if (i + 1 != fieldCount) insert_str << ","; else insert_str << ")\n"; } PG_Exec_str(insert_str.str(), mPGconn); } mysql_free_result(result); ++count; printf("Copied data from [%d] tables...\r", count); } printf("Finished copying the data from [%d] tables!\n", count); mTableList.clear(); m_DataBase_Map.clear(); /// Close connections mysql_close(mMysql); PQfinish(mPGconn); printf("end\n"); return 0; }
/* {{{ proto string PDO::pgsqlCopyFromArray(string $table_name , array $rows [, string $delimiter [, string $null_as ] [, string $fields]) Returns true if the copy worked fine or false if error */ static PHP_METHOD(PDO, pgsqlCopyFromArray) { pdo_dbh_t *dbh; pdo_pgsql_db_handle *H; zval *pg_rows; char *table_name, *pg_delim = NULL, *pg_null_as = NULL, *pg_fields = NULL; size_t table_name_len, pg_delim_len = 0, pg_null_as_len = 0, pg_fields_len; char *query; PGresult *pgsql_result; ExecStatusType status; if (zend_parse_parameters(ZEND_NUM_ARGS(), "s/a|sss", &table_name, &table_name_len, &pg_rows, &pg_delim, &pg_delim_len, &pg_null_as, &pg_null_as_len, &pg_fields, &pg_fields_len) == FAILURE) { return; } if (!zend_hash_num_elements(Z_ARRVAL_P(pg_rows))) { php_error_docref(NULL, E_WARNING, "Cannot copy from an empty array"); RETURN_FALSE; } dbh = Z_PDO_DBH_P(getThis()); PDO_CONSTRUCT_CHECK; PDO_DBH_CLEAR_ERR(); /* using pre-9.0 syntax as PDO_pgsql is 7.4+ compatible */ if (pg_fields) { spprintf(&query, 0, "COPY %s (%s) FROM STDIN WITH DELIMITER E'%c' NULL AS E'%s'", table_name, pg_fields, (pg_delim_len ? *pg_delim : '\t'), (pg_null_as_len ? pg_null_as : "\\\\N")); } else { spprintf(&query, 0, "COPY %s FROM STDIN WITH DELIMITER E'%c' NULL AS E'%s'", table_name, (pg_delim_len ? *pg_delim : '\t'), (pg_null_as_len ? pg_null_as : "\\\\N")); } /* Obtain db Handle */ H = (pdo_pgsql_db_handle *)dbh->driver_data; while ((pgsql_result = PQgetResult(H->server))) { PQclear(pgsql_result); } pgsql_result = PQexec(H->server, query); efree(query); query = NULL; if (pgsql_result) { status = PQresultStatus(pgsql_result); } else { status = (ExecStatusType) PQstatus(H->server); } if (status == PGRES_COPY_IN && pgsql_result) { int command_failed = 0; size_t buffer_len = 0; zval *tmp; PQclear(pgsql_result); ZEND_HASH_FOREACH_VAL(Z_ARRVAL_P(pg_rows), tmp) { size_t query_len; convert_to_string_ex(tmp); if (buffer_len < Z_STRLEN_P(tmp)) { buffer_len = Z_STRLEN_P(tmp); query = erealloc(query, buffer_len + 2); /* room for \n\0 */ } memcpy(query, Z_STRVAL_P(tmp), Z_STRLEN_P(tmp)); query_len = Z_STRLEN_P(tmp); if (query[query_len - 1] != '\n') { query[query_len++] = '\n'; } query[query_len] = '\0'; if (PQputCopyData(H->server, query, query_len) != 1) { efree(query); pdo_pgsql_error(dbh, PGRES_FATAL_ERROR, NULL); PDO_HANDLE_DBH_ERR(); RETURN_FALSE; } } ZEND_HASH_FOREACH_END();
int main(int argc, const char *const argv[]) { PGconn *conn; PGresult *result; int ret; GNUNET_log_setup ("test-pq", "WARNING", NULL); conn = PQconnectdb ("postgres:///gnunetcheck"); if (CONNECTION_OK != PQstatus (conn)) { fprintf (stderr, "Cannot run test, database connection failed: %s\n", PQerrorMessage (conn)); GNUNET_break (0); PQfinish (conn); return 0; /* We ignore this type of error... */ } result = PQexec (conn, "CREATE TEMPORARY TABLE IF NOT EXISTS test_pq (" " pub BYTEA NOT NULL" ",sig BYTEA NOT NULL" ",abs_time INT8 NOT NULL" ",forever INT8 NOT NULL" ",hash BYTEA NOT NULL CHECK(LENGTH(hash)=64)" ",vsize VARCHAR NOT NULL" ",u16 INT2 NOT NULL" ",u32 INT4 NOT NULL" ",u64 INT8 NOT NULL" ")"); if (PGRES_COMMAND_OK != PQresultStatus (result)) { fprintf (stderr, "Failed to create table: %s\n", PQerrorMessage (conn)); PQclear (result); PQfinish (conn); return 1; } PQclear (result); if (GNUNET_OK != postgres_prepare (conn)) { GNUNET_break (0); PQfinish (conn); return 1; } ret = run_queries (conn); result = PQexec (conn, "DROP TABLE test_pq"); if (PGRES_COMMAND_OK != PQresultStatus (result)) { fprintf (stderr, "Failed to create table: %s\n", PQerrorMessage (conn)); PQclear (result); PQfinish (conn); return 1; } PQclear (result); PQfinish (conn); return ret; }