C++ PQdb函数代码示例

/*
 * Reconnect to the server.  If dbname is not NULL, use that database,
 * else the one associated with the archive handle.  If username is
 * not NULL, use that user name, else the one from the handle.  If
 * both the database and the user match the existing connection already,
 * nothing will be done.
 *
 * Returns 1 in any case.
 */
int
ReconnectToServer(ArchiveHandle *AH, const char *dbname, const char *username)
{
	PGconn	   *newConn;
	const char *newdbname;
	const char *newusername;

	if (!dbname)
		newdbname = PQdb(AH->connection);
	else
		newdbname = dbname;

	if (!username)
		newusername = PQuser(AH->connection);
	else
		newusername = username;

	/* Let's see if the request is already satisfied */
	if (strcmp(newdbname, PQdb(AH->connection)) == 0 &&
		strcmp(newusername, PQuser(AH->connection)) == 0)
		return 1;

	newConn = _connectDB(AH, newdbname, newusername);

	PQfinish(AH->connection);
	AH->connection = newConn;

	return 1;
}

/*
 * Send a vacuum/analyze command to the server.  In async mode, return after
 * sending the command; else, wait for it to finish.
 *
 * Any errors during command execution are reported to stderr.  If async is
 * false, this function exits the program after reporting the error.
 */
static void
run_vacuum_command(PGconn *conn, const char *sql, bool echo,
				   const char *table, const char *progname, bool async)
{
	bool		status;

	if (async)
	{
		if (echo)
			printf("%s\n", sql);

		status = PQsendQuery(conn, sql) == 1;
	}
	else
		status = executeMaintenanceCommand(conn, sql, echo);

	if (!status)
	{
		if (table)
			fprintf(stderr,
					_("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
					progname, table, PQdb(conn), PQerrorMessage(conn));
		else
			fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
					progname, PQdb(conn), PQerrorMessage(conn));

		if (!async)
		{
			PQfinish(conn);
			exit(1);
		}
	}
}

/* internal function to check if a table exists in the database
 * takes an unescaped table name and returns an escaped one or null */
int db_unsafe_check_table(PGconn *pgsql, const char *table) {
    int status;
    char *query;
    char *db;
    PGresult *result;

    db = PQdb(pgsql);

    asprintf(&query,
        "SELECT \"table_name\" FROM information_schema.tables WHERE table_catalog = '%s' AND table_schema = current_schema() AND table_name = '%s'",
        db, table);

    db_debug(HIGH, "Query = %s\n", query);

    result = PQexec(pgsql, query);
    free(query);

    if (PQresultStatus(result) == PGRES_TUPLES_OK) {
        if (PQntuples(result) == 1) {
            debug(DEBUG, "%s found!\n", table);
            status = TRUE;
        } else {
            debug(DEBUG, "%s missing!\n", table);
            status = FALSE;
        }
    } else {
        db_debug(LOW, "%s", PQerrorMessage(pgsql));
        status = FALSE;
    }

    (void)PQclear(result);

    return status;
}

void test_database_init()
{
  scheduler_t* scheduler;
  PGresult* db_result;
  GString* sql;

  scheduler = scheduler_init(testdb, NULL);
  database_init(scheduler);

  FO_ASSERT_PTR_NOT_NULL(scheduler->db_conn);

  sprintf(sqltmp, check_scheduler_tables, PQdb(scheduler->db_conn));
  sql = g_string_new(sqltmp);
  g_string_append(sql, "'users';");

  /* get the url for the fossology instance */
  db_result = database_exec(scheduler, sql->str);
  //printf("sql: %s\n", sql->str);
  // TODO skip this test since the order reported here is random, also it will crash if PQntuples < 5
  #if 0
  if(PQresultStatus(db_result) == PGRES_TUPLES_OK && PQntuples(db_result) != 0)
  {
    //printf("result: %s\n",  g_strdup(PQgetvalue(db_result, 0, 0)));
    FO_ASSERT_STRING_EQUAL(g_strdup(PQgetvalue(db_result, 0, 0)), "user_pk");
    FO_ASSERT_STRING_EQUAL(g_strdup(PQgetvalue(db_result, 1, 0)), "user_name");
    FO_ASSERT_STRING_EQUAL(g_strdup(PQgetvalue(db_result, 2, 0)), "root_folder_fk");
    FO_ASSERT_STRING_EQUAL(g_strdup(PQgetvalue(db_result, 3, 0)), "user_desc");
    FO_ASSERT_STRING_EQUAL(g_strdup(PQgetvalue(db_result, 4, 0)), "user_seed");
  }
  #endif
  PQclear(db_result);
  g_string_free(sql, TRUE);
  scheduler_destroy(scheduler);
}

/*
 * Pass remote error/notice/warning through.
 */
void
plproxy_remote_error(ProxyFunction *func, ProxyConnection *conn, const PGresult *res, bool iserr)
{
	const char *ss = PQresultErrorField(res, PG_DIAG_SQLSTATE);
	const char *sev = PQresultErrorField(res, PG_DIAG_SEVERITY);
	const char *msg = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
	const char *det = PQresultErrorField(res, PG_DIAG_MESSAGE_DETAIL);
	const char *hint = PQresultErrorField(res, PG_DIAG_MESSAGE_HINT);
	const char *spos = PQresultErrorField(res, PG_DIAG_STATEMENT_POSITION);
	const char *ipos = PQresultErrorField(res, PG_DIAG_INTERNAL_POSITION);
	const char *iquery = PQresultErrorField(res, PG_DIAG_INTERNAL_QUERY);
	const char *ctx = PQresultErrorField(res, PG_DIAG_CONTEXT);
	int elevel;

	/* libpq errors may not have sqlstate */
	if (!ss)
		ss = "XX000";

	if (iserr)
		/* must ignore remote level, as it may be FATAL/PANIC */
		elevel = ERROR;
	else
		/* cannot look at sev here, as it may be localized */
		elevel = !strncmp(ss, "00", 2) ? NOTICE : WARNING;

	ereport(elevel, (
		errcode(MAKE_SQLSTATE(ss[0], ss[1], ss[2], ss[3], ss[4])),
		errmsg("%s(%d): [%s] REMOTE %s: %s", func->name, func->arg_count, PQdb(conn->cur->db), sev, msg),
		det ? errdetail("Remote detail: %s", det) : 0,
		hint ? errhint("Remote hint: %s", hint) : 0,
		spos ? errposition(atoi(spos)) : 0,
		ipos ? internalerrposition(atoi(ipos)) : 0,
		iquery ? internalerrquery(iquery) : 0,
		ctx ? errcontext("Remote context: %s", ctx) : 0));
}

static int
switch_database(struct dbpath *dbpath)
{
	PGconn *newdbconn;

	if (dbpath_is_root(*dbpath))
		return 1;

	if (strcmp(dbpath->database, PQdb(dbconn)) == 0)
		return 1;

	newdbconn = PQsetdbLogin(PQhost(dbconn),
							 PQport(dbconn),
							 PQoptions(dbconn),
							 PQtty(dbconn),
							 dbpath->database,
							 PQuser(dbconn),
							 PQpass(dbconn));

	if (PQstatus(newdbconn) != CONNECTION_OK)
	{
		debug("new connection failed");
		PQfinish(newdbconn);
		return 0;
	}

	PQfinish(dbconn);
	dbconn = newdbconn;
	return 1;
}

/*
 * GetQueryResult
 *
 * Process the query result.  Returns true if there's no error, false
 * otherwise -- but errors about trying to vacuum a missing relation are
 * reported and subsequently ignored.
 */
static bool
GetQueryResult(PGconn *conn, const char *progname)
{
	PGresult   *result;

	SetCancelConn(conn);
	while ((result = PQgetResult(conn)) != NULL)
	{
		/*
		 * If errors are found, report them.  Errors about a missing table are
		 * harmless so we continue processing; but die for other errors.
		 */
		if (PQresultStatus(result) != PGRES_COMMAND_OK)
		{
			char	   *sqlState = PQresultErrorField(result, PG_DIAG_SQLSTATE);

			fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
					progname, PQdb(conn), PQerrorMessage(conn));

			if (sqlState && strcmp(sqlState, ERRCODE_UNDEFINED_TABLE) != 0)
			{
				PQclear(result);
				return false;
			}
		}

		PQclear(result);
	}
	ResetCancelConn();

	return true;
}

/* [Re]connects to database */
static GSM_Error SMSDPgSQL_Connect(GSM_SMSDConfig * Config)
{
	unsigned char buf[400];
	PGresult *Res;

	unsigned int port = 5432;
	char *pport;

	pport = strstr(Config->host, ":");
	if (pport) {
		*pport++ = '\0';
		port = atoi(pport);
	}

	sprintf(buf, "host = '%s' user = '%s' password = '%s' dbname = '%s' port = %d", Config->host, Config->user, Config->password, Config->database, port);

	SMSDPgSQL_Free(Config);
	Config->conn.pg = PQconnectdb(buf);
	if (PQstatus(Config->conn.pg) != CONNECTION_OK) {
		SMSD_Log(DEBUG_ERROR, Config, "Error connecting to database: %s", PQerrorMessage(Config->conn.pg));
		PQfinish(Config->conn.pg);
		return ERR_DB_CONNECT;
	}

	Res = PQexec(Config->conn.pg, "SET NAMES UTF8");
	PQclear(Res);
	SMSD_Log(DEBUG_INFO, Config, "Connected to database: %s on %s. Server version: %d Protocol: %d",
		 PQdb(Config->conn.pg), PQhost(Config->conn.pg), PQserverVersion(Config->conn.pg), PQprotocolVersion(Config->conn.pg));

	return ERR_NONE;
}

static int c_psql_check_connection (c_psql_database_t *db)
{
	/* "ping" */
	PQclear (PQexec (db->conn, "SELECT 42;"));

	if (CONNECTION_OK != PQstatus (db->conn)) {
		PQreset (db->conn);

		/* trigger c_release() */
		if (0 == db->conn_complaint.interval)
			db->conn_complaint.interval = 1;

		if (CONNECTION_OK != PQstatus (db->conn)) {
			c_complain (LOG_ERR, &db->conn_complaint,
					"Failed to connect to database %s: %s",
					db->database, PQerrorMessage (db->conn));
			return -1;
		}

		db->proto_version = PQprotocolVersion (db->conn);
		if (3 > db->proto_version)
			log_warn ("Protocol version %d does not support parameters.",
					db->proto_version);
	}

	db->server_version = PQserverVersion (db->conn);

	c_release (LOG_INFO, &db->conn_complaint,
			"Successfully reconnected to database %s", PQdb (db->conn));
	return 0;
} /* c_psql_check_connection */

static int c_psql_check_connection (c_psql_database_t *db)
{
	_Bool init = 0;

	if (! db->conn) {
		init = 1;

		/* trigger c_release() */
		if (0 == db->conn_complaint.interval)
			db->conn_complaint.interval = 1;

		c_psql_connect (db);
	}

	/* "ping" */
	PQclear (PQexec (db->conn, "SELECT 42;"));

	if (CONNECTION_OK != PQstatus (db->conn)) {
		PQreset (db->conn);

		/* trigger c_release() */
		if (0 == db->conn_complaint.interval)
			db->conn_complaint.interval = 1;

		if (CONNECTION_OK != PQstatus (db->conn)) {
			c_complain (LOG_ERR, &db->conn_complaint,
					"Failed to connect to database %s (%s): %s",
					db->database, db->instance,
					PQerrorMessage (db->conn));
			return -1;
		}

		db->proto_version = PQprotocolVersion (db->conn);
	}

	db->server_version = PQserverVersion (db->conn);

	if (c_would_release (&db->conn_complaint)) {
		char *server_host;
		int   server_version;

		server_host    = PQhost (db->conn);
		server_version = PQserverVersion (db->conn);

		c_do_release (LOG_INFO, &db->conn_complaint,
				"Successfully %sconnected to database %s (user %s) "
				"at server %s%s%s (server version: %d.%d.%d, "
				"protocol version: %d, pid: %d)", init ? "" : "re",
				PQdb (db->conn), PQuser (db->conn),
				C_PSQL_SOCKET3 (server_host, PQport (db->conn)),
				C_PSQL_SERVER_VERSION3 (server_version),
				db->proto_version, PQbackendPID (db->conn));

		if (3 > db->proto_version)
			log_warn ("Protocol version %d does not support parameters.",
					db->proto_version);
	}
	return 0;
} /* c_psql_check_connection */

const wxString dbgPgConn::getName() const
{
	wxString 	result = wxString( PQhost( m_pgConn ), wxConvUTF8 );

	result += wxT( "/" );
	result.Append( wxString( PQdb( m_pgConn ), wxConvUTF8 ));

	return( result );

}

static void
cluster_one_database(const char *dbname, bool verbose, const char *table,
					 const char *host, const char *port,
					 const char *username, enum trivalue prompt_password,
					 const char *progname, bool echo)
{
	PQExpBufferData sql;

	PGconn	   *conn;

	conn = connectDatabase(dbname, host, port, username, prompt_password,
						   progname, echo, false);

	initPQExpBuffer(&sql);

	appendPQExpBufferStr(&sql, "CLUSTER");
	if (verbose)
		appendPQExpBufferStr(&sql, " VERBOSE");
	if (table)
	{
		appendPQExpBufferChar(&sql, ' ');
		appendQualifiedRelation(&sql, table, conn, progname, echo);
	}
	appendPQExpBufferChar(&sql, ';');

	if (!executeMaintenanceCommand(conn, sql.data, echo))
	{
		if (table)
			fprintf(stderr, _("%s: clustering of table \"%s\" in database \"%s\" failed: %s"),
					progname, table, PQdb(conn), PQerrorMessage(conn));
		else
			fprintf(stderr, _("%s: clustering of database \"%s\" failed: %s"),
					progname, PQdb(conn), PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}
	PQfinish(conn);
	termPQExpBuffer(&sql);
}

/*************************************************************************
 *
 *	Function: sql_create_socket
 *
 *	Purpose: Establish connection to the db
 *
 *************************************************************************/
static int sql_init_socket(rlm_sql_handle_t *handle, rlm_sql_config_t *config) {
	char *dbstring;
	rlm_sql_postgres_conn_t *conn;

#ifdef HAVE_OPENSSL_CRYPTO_H
	static bool ssl_init = false;

	if (!ssl_init) {
		PQinitOpenSSL(0, 0);
		ssl_init = true;
	}
#endif

	MEM(conn = handle->conn = talloc_zero(handle, rlm_sql_postgres_conn_t));
	talloc_set_destructor(conn, _sql_socket_destructor);

	dbstring = strchr(config->sql_db, '=') ?
		talloc_strdup(conn, config->sql_db) :
		talloc_asprintf(conn, "dbname='%s'", config->sql_db);

	if (config->sql_server[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " host='%s'", config->sql_server);
	}

	if (config->sql_port[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " port=%s", config->sql_port);
	}

	if (config->sql_login[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " user='%s'", config->sql_login);
	}

	if (config->sql_password[0] != '\0') {
		dbstring = talloc_asprintf_append(dbstring, " password='%s'", config->sql_password);
	}

	conn->dbstring = dbstring;
	conn->db = PQconnectdb(dbstring);
	DEBUG2("rlm_sql_postgresql: Connecting using parameters: %s", dbstring);
	if (!conn->db || (PQstatus(conn->db) != CONNECTION_OK)) {
		ERROR("rlm_sql_postgresql: Connection failed: %s", PQerrorMessage(conn->db));
		return -1;
	}
	DEBUG2("Connected to database '%s' on '%s' server version %i, protocol version %i, backend PID %i ",
	       PQdb(conn->db), PQhost(conn->db), PQserverVersion(conn->db), PQprotocolVersion(conn->db),
	       PQbackendPID(conn->db));

	return 0;
}

void
sql_shutdown()
{
    PGconn *pgsql;

    if (!pgsql_struct)
        return;
    pgsql = pgsql_struct;
    STARTLOG(LOG_ALWAYS, "SQL", "DISC")
    log_printf
    ("Disconnected from SQL server %s, SQL database selected: %s",
     PQhost(pgsql), PQdb(pgsql));
    ENDLOG PQfinish(pgsql);
    pgsql_struct = NULL;
    mudstate.sql_socket = -1;
}

/*
 * SyncVariables
 *
 * Make psql's internal variables agree with connection state upon
 * establishing a new connection.
 */
void
SyncVariables(void)
{
	/* get stuff from connection */
	pset.encoding = PQclientEncoding(pset.db);
	pset.popt.topt.encoding = pset.encoding;
	pset.sversion = PQserverVersion(pset.db);

	SetVariable(pset.vars, "DBNAME", PQdb(pset.db));
	SetVariable(pset.vars, "USER", PQuser(pset.db));
	SetVariable(pset.vars, "HOST", PQhost(pset.db));
	SetVariable(pset.vars, "PORT", PQport(pset.db));
	SetVariable(pset.vars, "ENCODING", pg_encoding_to_char(pset.encoding));

	/* send stuff to it, too */
	PQsetErrorVerbosity(pset.db, pset.verbosity);
}