From 2e2aafeb4c2c88e9eba977bcaf9b6a173612f511 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Tue, 2 Jun 2015 01:42:13 +0100
Subject: 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.

---
 libpqpp/selectcommand.cpp | 85 +++++++++++++++++++++++++++++++----------------
 libpqpp/selectcommand.h   |  9 ++++-
 2 files changed, 65 insertions(+), 29 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;
 	}
diff --git a/libpqpp/selectcommand.h b/libpqpp/selectcommand.h
index daa2707..6506e5e 100644
--- a/libpqpp/selectcommand.h
+++ b/libpqpp/selectcommand.h
@@ -19,10 +19,17 @@ namespace PQ {
 
 		private:
 			void fetchTuples();
+			std::string mkdeclare() const;
+			std::string mkfetch() const;
+			std::string mkclose() const;
+
 			mutable bool executed;
 			mutable bool txOpened;
-			int nTuples, tuple;
+			int nTuples, tuple, fTuples;
 			PGresult * execRes;
+			std::string s_declare;
+			std::string s_fetch;
+			std::string s_close;
 
 			friend class Column;
 	};
-- 
cgit v1.2.3