Example #1
0
 bool isOK() const
 {
     return (!closed_) && (PQstatus(conn_) != CONNECTION_BAD);
 }
Example #2
0
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;
}
Example #3
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;
}
Example #4
0
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;
}
Example #5
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 */
Example #6
0
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;
}
Example #7
0
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);
}
Example #8
0
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;

}

}
Example #9
0
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='%s' password='%s'"
        
    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;
}
Example #10
0
/******************************************************************************
 *                                                                            *
 * 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;
}
Example #11
0
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;
}
Example #12
0
/*
 * 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;
}
Example #13
0
/******************************************************************************
 *                                                                            *
 * 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;
}
Example #14
0
/* ----------
 * 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);
}
Example #15
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;
}
Example #16
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;
}
Example #17
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;
}
Example #20
0
/*
 * 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: ", 100, false);
		if (pszPassword == NULL)
		{
			mpp_err_msg_cache("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: ", 100, false);
			masterDB.pszDBPswd = pszPassword;
		}
	} while (need_pass);

	if (pszPassword)
		free(pszPassword);

	/* check to see that the backend connection was successfully made */
	if (PQstatus(pConn) == CONNECTION_BAD)
	{
		mpp_err_msg_cache("ERROR", progName, "connection to database \"%s\" failed : %s",
						  PQdb(pConn), PQerrorMessage(pConn));
		return (NULL);
	}

	return pConn;
}
Example #21
0
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));
}
Example #22
0
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;
}
Example #23
0
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 = "nobody";

   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, &params, 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;
}
Example #24
0
bool pgConn::IsSSLconnected()
{
	return (conn && PQstatus(conn) == CONNECTION_OK && PQgetssl(conn) != NULL);
}
Example #25
0
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 */
Example #26
0
/*
 *
 * 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: "));
	else
	{
		password_prompt = malloc(strlen(_("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;
}
Example #27
0
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);
}
Example #28
0
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;

}
Example #29
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();
Example #30
0
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;
}