Prebuild (once) SQL for cursor operations.

Don't open another transaction.
Don't make another fetch to the DB if the previous read was short
anyway.
Add bulk load and bulk read tests.

1 files changed, 57 insertions, 28 deletions

diff --git a/libpqpp/selectcommand.cpp b/libpqpp/selectcommand.cpp
index 359cdfd..afb8c1b 100644
--- a/libpqpp/selectcommand.cpp
+++ b/libpqpp/selectcommand.cpp
@@ -11,7 +11,11 @@ PQ::SelectCommand::SelectCommand(const Connection * conn, const std::string & sq
 	txOpened(false),
 	nTuples(0),
 	tuple(0),
-	execRes(NULL)
+	fTuples(35),
+	execRes(NULL),
+	s_declare(mkdeclare()),
+	s_fetch(mkfetch()),
+	s_close(mkclose())
 {
 }
 
@@ -21,42 +25,66 @@ PQ::SelectCommand::~SelectCommand()
 		c->commitTx();
 	}
 	if (executed) {
-		PQclear(PQexec(c->conn, ("CLOSE " + stmntName).c_str()));
+		PQclear(PQexec(c->conn, s_close.c_str()));
 		if (execRes) {
 			PQclear(execRes);
 		}
 	}
 }
 
+std::string
+PQ::SelectCommand::mkdeclare() const
+{
+	std::string psql;
+	psql.reserve(sql.length() + 40);
+	char buf[4];
+	int p = 1;
+	bool inquote = false;
+	psql += "DECLARE ";
+	psql += stmntName;
+	psql += " CURSOR FOR ";
+	for(std::string::const_iterator i = sql.begin(); i != sql.end(); ++i) {
+		if (*i == '?' && !inquote) {
+			snprintf(buf, 4, "$%d", p++);
+			psql += buf;
+		}
+		else if (*i == '\'') {
+			inquote = !inquote;
+			psql += *i;
+		}
+		else {
+			psql += *i;
+		}
+	}
+	return psql;
+}
+
+std::string
+PQ::SelectCommand::mkfetch() const
+{
+	char buf[BUFSIZ];
+	snprintf(buf, sizeof(buf), "FETCH %d IN %s", fTuples, stmntName.c_str());
+	return buf;
+}
+
+std::string
+PQ::SelectCommand::mkclose() const
+{
+	char buf[BUFSIZ];
+	snprintf(buf, sizeof(buf), "CLOSE %s", stmntName.c_str());
+	return buf;
+}
+
 void
 PQ::SelectCommand::execute()
 {
 	if (!executed) {
-		std::string psql;
-		psql.reserve(sql.length() + 40);
-		char buf[4];
-		int p = 1;
-		bool inquote = false;
-		psql += "DECLARE ";
-		psql += stmntName;
-		psql += " CURSOR FOR ";
-		for(std::string::const_iterator i = sql.begin(); i != sql.end(); ++i) {
-			if (*i == '?' && !inquote) {
-				snprintf(buf, 4, "$%d", p++);
-				psql += buf;
-			}
-			else if (*i == '\'') {
-				inquote = !inquote;
-				psql += *i;
-			}
-			else {
-				psql += *i;
-			}
+		if (!txOpened) {
+			c->beginTx();
+			txOpened = true;
 		}
-		c->beginTx();
-		txOpened = true;
 		execRes = c->checkResult(
-				PQexecParams(c->conn, psql.c_str(), values.size(), NULL, &values.front(), &lengths.front(), &formats.front(), 0),
+				PQexecParams(c->conn, s_declare.c_str(), values.size(), NULL, &values.front(), &lengths.front(), &formats.front(), 0),
 				PGRES_COMMAND_OK);
 		fetchTuples();
 		unsigned int nFields = PQnfields(execRes);
@@ -74,7 +102,7 @@ PQ::SelectCommand::fetchTuples()
 		PQclear(execRes);
 	}
 	execRes = NULL;
-	execRes = c->checkResult(PQexec(c->conn, ("FETCH 35 IN " + stmntName).c_str()), PGRES_TUPLES_OK);
+	execRes = c->checkResult(PQexec(c->conn, s_fetch.c_str()), PGRES_TUPLES_OK);
 	nTuples = PQntuples(execRes);
 	tuple = -1;
 }
@@ -83,15 +111,16 @@ bool
 PQ::SelectCommand::fetch()
 {
 	execute();
-	if (tuple >= (nTuples - 1)) {
+	if ((tuple >= (nTuples - 1)) && (nTuples == fTuples)) {
 		fetchTuples();
 	}
 	if (tuple++ < (nTuples - 1)) {
 		return true;
 	}
 	else {
-		PQclear(PQexec(c->conn, ("CLOSE " + stmntName).c_str()));
+		PQclear(PQexec(c->conn, s_close.c_str()));
 		PQclear(execRes);
+		execRes = NULL;
 		executed = false;
 		return false;
 	}