From 19609c41b15a818df30508576f50fc0eddc11636 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Mon, 5 Jun 2017 18:53:22 +0100 Subject: Support fetching data in binary format (no numeric, datetime, interval support yet) --- libpqpp/pq-binarycolumn.cpp | 43 ++++++++++++++++++++++++++++++++++++++ libpqpp/pq-binarycolumn.h | 17 +++++++++++++++ libpqpp/pq-bulkselectcommand.cpp | 6 +++--- libpqpp/pq-bulkselectcommand.h | 2 +- libpqpp/pq-command.cpp | 9 +++++--- libpqpp/pq-command.h | 4 +++- libpqpp/pq-connection.cpp | 4 ++-- libpqpp/pq-cursorselectcommand.cpp | 6 +++--- libpqpp/pq-selectbase.cpp | 9 +++++--- libpqpp/pq-selectbase.h | 4 +++- libpqpp/unittests/testpq.cpp | 43 ++++++++++++++++++++++++++++++++++++++ 11 files changed, 130 insertions(+), 17 deletions(-) create mode 100644 libpqpp/pq-binarycolumn.cpp create mode 100644 libpqpp/pq-binarycolumn.h diff --git a/libpqpp/pq-binarycolumn.cpp b/libpqpp/pq-binarycolumn.cpp new file mode 100644 index 0000000..ff49b26 --- /dev/null +++ b/libpqpp/pq-binarycolumn.cpp @@ -0,0 +1,43 @@ +#include "pq-binarycolumn.h" +#include "pq-selectbase.h" +#include + +PQ::BinaryColumn::BinaryColumn(const PQ::SelectBase * s, unsigned int f) : + PQ::Column(s, f) +{ +} + +void +PQ::BinaryColumn::apply(DB::HandleField & h) const +{ + if (isNull()) { + h.null(); + return; + } + switch (oid) { + case 18: //CHAROID: + case 1043: //VARCHAROID: + case 25: //TEXTOID: + case 142: //XMLOID: + h.string(value(), length()); + break; + case 16: //BOOLOID: + h.boolean(valueAs()); + break; + case 21: //INT2OID: + h.integer(be16toh(valueAs())); + break; + case 23: //INT4OID: + h.integer(be32toh(valueAs())); + break; + case 20: //INT8OID: + h.integer(be64toh(valueAs())); + break; + case 17: //BYTEAOID + h.blob(DB::Blob(value(), length())); + break; + default: + throw DB::ColumnTypeNotSupported(); + } +} + diff --git a/libpqpp/pq-binarycolumn.h b/libpqpp/pq-binarycolumn.h new file mode 100644 index 0000000..14719a3 --- /dev/null +++ b/libpqpp/pq-binarycolumn.h @@ -0,0 +1,17 @@ +#ifndef PG_BINARY_COLUMN_H +#define PG_BINARY_COLUMN_H + +#include "pq-column.h" + +namespace PQ { + class BinaryColumn : public Column { + public: + BinaryColumn(const SelectBase *, unsigned int field); + + void apply(DB::HandleField &) const override; + }; +} + +#endif + + diff --git a/libpqpp/pq-bulkselectcommand.cpp b/libpqpp/pq-bulkselectcommand.cpp index ab44df5..c9abec5 100644 --- a/libpqpp/pq-bulkselectcommand.cpp +++ b/libpqpp/pq-bulkselectcommand.cpp @@ -3,9 +3,9 @@ #include "pq-column.h" #include "pq-error.h" -PQ::BulkSelectCommand::BulkSelectCommand(Connection * conn, const std::string & sql, const DB::CommandOptions * opts) : +PQ::BulkSelectCommand::BulkSelectCommand(Connection * conn, const std::string & sql, const PQ::CommandOptions * pqco, const DB::CommandOptions * opts) : DB::Command(sql), - PQ::SelectBase(sql), + PQ::SelectBase(sql, pqco), PQ::PreparedStatement(conn, sql, opts), executed(false) { @@ -16,7 +16,7 @@ PQ::BulkSelectCommand::execute() { if (!executed) { execRes = c->checkResult( - PQexecPrepared(c->conn, prepare(), values.size(), &values.front(), &lengths.front(), &formats.front(), 0), + PQexecPrepared(c->conn, prepare(), values.size(), &values.front(), &lengths.front(), &formats.front(), binary), PGRES_TUPLES_OK); nTuples = PQntuples(execRes); tuple = -1; diff --git a/libpqpp/pq-bulkselectcommand.h b/libpqpp/pq-bulkselectcommand.h index 9b76c4d..6fcf883 100644 --- a/libpqpp/pq-bulkselectcommand.h +++ b/libpqpp/pq-bulkselectcommand.h @@ -11,7 +11,7 @@ namespace PQ { class Column; class BulkSelectCommand : public SelectBase, public PreparedStatement { public: - BulkSelectCommand(Connection *, const std::string & sql, const DB::CommandOptions *); + BulkSelectCommand(Connection *, const std::string & sql, const PQ::CommandOptions * pqco, const DB::CommandOptions *); bool fetch() override; void execute() override; diff --git a/libpqpp/pq-command.cpp b/libpqpp/pq-command.cpp index 5476393..9fd4002 100644 --- a/libpqpp/pq-command.cpp +++ b/libpqpp/pq-command.cpp @@ -32,16 +32,19 @@ PQ::Command::~Command() PQ::CommandOptions::CommandOptions(std::size_t hash, const DB::CommandOptionsMap & map) : DB::CommandOptions(hash), fetchTuples(get(map, "page-size", 35)), - useCursor(!isSet(map, "no-cursor")) + useCursor(!isSet(map, "no-cursor")), + fetchBinary(isSet(map, "fetch-binary")) { } PQ::CommandOptions::CommandOptions(std::size_t hash, unsigned int ft, - bool uc) : + bool uc, + bool fb) : DB::CommandOptions(hash), fetchTuples(ft), - useCursor(uc) + useCursor(uc), + fetchBinary(fb) { } diff --git a/libpqpp/pq-command.h b/libpqpp/pq-command.h index 989844c..2431639 100644 --- a/libpqpp/pq-command.h +++ b/libpqpp/pq-command.h @@ -15,10 +15,12 @@ namespace PQ { CommandOptions(std::size_t, const DB::CommandOptionsMap &); CommandOptions(std::size_t hash, unsigned int fetchTuples = 35, - bool useCursor = true); + bool useCursor = true, + bool fetchBinary = false); unsigned int fetchTuples; bool useCursor; + bool fetchBinary; }; class Command : public virtual DB::Command { diff --git a/libpqpp/pq-connection.cpp b/libpqpp/pq-connection.cpp index 643e021..e189acf 100644 --- a/libpqpp/pq-connection.cpp +++ b/libpqpp/pq-connection.cpp @@ -101,7 +101,7 @@ PQ::Connection::newSelectCommand(const std::string & sql, const DB::CommandOptio { auto pqco = dynamic_cast(opts); if (pqco && !pqco->useCursor) { - return new BulkSelectCommand(this, sql, opts); + return new BulkSelectCommand(this, sql, pqco, opts); } return new CursorSelectCommand(this, sql, pqco, opts); } @@ -180,7 +180,7 @@ static const DB::CommandOptions selectLastValOpts(std::hash()(selec int64_t PQ::Connection::insertId() { - BulkSelectCommand getId(this, selectLastVal, &selectLastValOpts); + BulkSelectCommand getId(this, selectLastVal, nullptr, &selectLastValOpts); int64_t id = -1; while (getId.fetch()) { getId[0] >> id; diff --git a/libpqpp/pq-cursorselectcommand.cpp b/libpqpp/pq-cursorselectcommand.cpp index 54b843f..2b639c8 100644 --- a/libpqpp/pq-cursorselectcommand.cpp +++ b/libpqpp/pq-cursorselectcommand.cpp @@ -9,7 +9,7 @@ AdHocFormatter(PQCursorSelectClose, "CLOSE %?"); PQ::CursorSelectCommand::CursorSelectCommand(Connection * conn, const std::string & sql, const PQ::CommandOptions * pqco, const DB::CommandOptions * opts) : DB::Command(sql), - PQ::SelectBase(sql), + PQ::SelectBase(sql, pqco), PQ::Command(conn, sql, opts), executed(false), txOpened(false), @@ -50,7 +50,7 @@ PQ::CursorSelectCommand::execute() s_declare = mkdeclare(); } c->checkResultFree( - PQexecParams(c->conn, s_declare.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(), binary), PGRES_COMMAND_OK); fetchTuples(); createColumns(execRes); @@ -61,7 +61,7 @@ PQ::CursorSelectCommand::execute() void PQ::CursorSelectCommand::fetchTuples() { - execRes = c->checkResult(PQexec(c->conn, s_fetch.c_str()), PGRES_TUPLES_OK); + execRes = c->checkResult(PQexecParams(c->conn, s_fetch.c_str(), 0, NULL, NULL, NULL, NULL, binary), PGRES_TUPLES_OK); nTuples = PQntuples(execRes); tuple = -1; } diff --git a/libpqpp/pq-selectbase.cpp b/libpqpp/pq-selectbase.cpp index 7dbdb4f..1024e5a 100644 --- a/libpqpp/pq-selectbase.cpp +++ b/libpqpp/pq-selectbase.cpp @@ -1,12 +1,15 @@ #include "pq-selectbase.h" #include "pq-column.h" +#include "pq-binarycolumn.h" +#include "pq-command.h" -PQ::SelectBase::SelectBase(const std::string & sql) : +PQ::SelectBase::SelectBase(const std::string & sql, const PQ::CommandOptions * pqco) : DB::Command(sql), DB::SelectCommand(sql), nTuples(0), tuple(0), - execRes(NULL) + execRes(NULL), + binary(pqco ? pqco->fetchBinary : false) { } @@ -22,7 +25,7 @@ PQ::SelectBase::createColumns(PGresult * execRes) { unsigned int nFields = PQnfields(execRes); for (unsigned int f = 0; f < nFields; f += 1) { - insertColumn(DB::ColumnPtr(new Column(this, f))); + insertColumn(DB::ColumnPtr(binary ? new BinaryColumn(this, f) : new Column(this, f))); } } diff --git a/libpqpp/pq-selectbase.h b/libpqpp/pq-selectbase.h index a6b199a..804535d 100644 --- a/libpqpp/pq-selectbase.h +++ b/libpqpp/pq-selectbase.h @@ -5,17 +5,19 @@ #include namespace PQ { + class CommandOptions; class SelectBase : public DB::SelectCommand { friend class Column; protected: - SelectBase(const std::string & sql); + SelectBase(const std::string & sql, const PQ::CommandOptions * pqco); ~SelectBase(); void createColumns(PGresult *); int nTuples, tuple; PGresult * execRes; + bool binary; }; } diff --git a/libpqpp/unittests/testpq.cpp b/libpqpp/unittests/testpq.cpp index 97dc22d..f9ebf64 100644 --- a/libpqpp/unittests/testpq.cpp +++ b/libpqpp/unittests/testpq.cpp @@ -407,6 +407,49 @@ BOOST_AUTO_TEST_CASE( blobs ) } } +BOOST_AUTO_TEST_CASE( fetchAsBinary ) +{ + auto ro = DB::ConnectionPtr(DB::MockDatabase::openConnectionTo("PQmock")); + std::vector buf(29); + memcpy(&buf[0], "This is some binary text data", 29); + PQ::CommandOptions opts(0); + opts.fetchBinary = true; + opts.useCursor = false; + auto sel = ro->select("SELECT data, md5, length(data) FROM blobtest", &opts); + for (const auto & r : sel->as, int64_t>()) { + // Assert the DB understood the insert + BOOST_REQUIRE_EQUAL(r.value<2>(), buf.size()); + BOOST_REQUIRE(r.value<1>()); + BOOST_REQUIRE_EQUAL(*r.value<1>(), "37c7c3737f93e8d17e845deff8fa74d2"); + // Assert the fetch of the data is correct + BOOST_REQUIRE_EQUAL(r.value<0>().len, buf.size()); + BOOST_REQUIRE(memcmp(r.value<0>().data, &buf[0], 29) == 0); + } + *opts.hash += 1; + sel = ro->select("SELECT CAST(length(data) AS BIGINT) big, CAST(length(data) AS SMALLINT) small FROM blobtest", &opts); + for (const auto & r : sel->as()) { + BOOST_REQUIRE_EQUAL(r.value<0>(), buf.size()); + BOOST_REQUIRE_EQUAL(r.value<1>(), buf.size()); + } + *opts.hash += 1; + sel = ro->select("SELECT true a, false b", &opts); + for (const auto & r : sel->as()) { + BOOST_REQUIRE_EQUAL(r.value<0>(), true); + BOOST_REQUIRE_EQUAL(r.value<1>(), false); + } + *opts.hash += 1; + sel = ro->select("SELECT xmlelement(name xml)", &opts); + for (const auto & r : sel->as()) { + BOOST_REQUIRE_EQUAL(r.value<0>(), ""); + } + *opts.hash += 1; + sel = ro->select("SELECT NULL, now()", &opts); + for (const auto & r : sel->as, boost::posix_time::ptime>()) { + BOOST_REQUIRE(!r.value<0>()); + BOOST_REQUIRE_THROW(r.value<1>(), DB::ColumnTypeNotSupported); + } +} + BOOST_AUTO_TEST_SUITE_END(); BOOST_AUTO_TEST_CASE( connfail ) -- cgit v1.2.3