From 20211f62e4c4d40af15e22cfd921fc86d36de0c0 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Mon, 12 Oct 2015 21:14:03 +0100 Subject: Add Slicer support for deserializing a SQL select --- slicer/Jamfile.jam | 2 + slicer/db/Jamfile.jam | 49 +++++++++ slicer/db/modelParts.cpp | 0 slicer/db/slicer.sql | 12 +++ slicer/db/sqlSelectDeserializer.cpp | 187 +++++++++++++++++++++++++++++++++ slicer/db/sqlSelectDeserializer.h | 39 +++++++ slicer/db/testSelect.cpp | 203 ++++++++++++++++++++++++++++++++++++ 7 files changed, 492 insertions(+) create mode 100644 slicer/db/Jamfile.jam create mode 100644 slicer/db/modelParts.cpp create mode 100644 slicer/db/slicer.sql create mode 100644 slicer/db/sqlSelectDeserializer.cpp create mode 100644 slicer/db/sqlSelectDeserializer.h create mode 100644 slicer/db/testSelect.cpp diff --git a/slicer/Jamfile.jam b/slicer/Jamfile.jam index 04fc453..69c914b 100644 --- a/slicer/Jamfile.jam +++ b/slicer/Jamfile.jam @@ -4,8 +4,10 @@ build-project tool ; build-project slicer ; build-project xml ; build-project json ; +build-project db ; build-project test ; package.install install : . : tool//slicer : slicer//slicer : [ glob slicer/*.h ] ; package.install install-xml : . : : xml//slicer-xml : [ glob xml/*.h ] ; package.install install-json : . : : json//slicer-json : [ glob json/*.h ] ; +package.install install-db : . : : db//slicer-db : [ glob db/*.h ] ; diff --git a/slicer/db/Jamfile.jam b/slicer/db/Jamfile.jam new file mode 100644 index 0000000..9d18558 --- /dev/null +++ b/slicer/db/Jamfile.jam @@ -0,0 +1,49 @@ +import testing ; + +alias glibmm : : : : + "`pkg-config --cflags glibmm-2.4`" + "`pkg-config --libs glibmm-2.4`" + ; + +lib dbppcore : : : : /usr/include/dbpp ; +lib dbpp-postgresql : : : : /usr/include/dbpp-postgresql ; +lib boost_system ; +lib boost_filesystem ; +lib boost_utf : : boost_unit_test_framework ; +lib IceUtil ; + +lib slicer-db : + [ glob *.cpp : test*.cpp ] + : + .. + IceUtil + dbppcore + glibmm + ../slicer//slicer + -fvisibility=hidden + release:-flto + : : + dbppcore + ; + +path-constant me : . ; + +run testSelect.cpp + : : : + ROOT=\"$(me)\" + BOOST_TEST_DYN_LINK + slicer-db + dbpp-postgresql + boost_system + boost_filesystem + boost_utf + ../test//slicer-test + ../test//common + ../slicer//slicer + .. + slicer.sql + ../test//compilation + : + testSelect + ; + diff --git a/slicer/db/modelParts.cpp b/slicer/db/modelParts.cpp new file mode 100644 index 0000000..e69de29 diff --git a/slicer/db/slicer.sql b/slicer/db/slicer.sql new file mode 100644 index 0000000..94807f5 --- /dev/null +++ b/slicer/db/slicer.sql @@ -0,0 +1,12 @@ +CREATE TABLE test( + id int, + fl numeric(8,4), + string text, + boolean bool, + dt timestamp without time zone, + ts interval); + +INSERT INTO test VALUES(1, 1.1, 'text one', true, '2015-01-27 23:06:03', '1 day 11:13:12'); +INSERT INTO test VALUES(2, 12.12, 'text two', true, '2015-02-27 23:06:03', '1 day 12:13:12'); +INSERT INTO test VALUES(3, 123.123, 'text three', false, '2015-03-27 23:06:03', '1 day 13:13:12'); +INSERT INTO test VALUES(4, 1234.1234, 'text four', false, '2015-04-27 23:06:03', '1 day 14:13:12'); diff --git a/slicer/db/sqlSelectDeserializer.cpp b/slicer/db/sqlSelectDeserializer.cpp new file mode 100644 index 0000000..2a499b6 --- /dev/null +++ b/slicer/db/sqlSelectDeserializer.cpp @@ -0,0 +1,187 @@ +#include "sqlSelectDeserializer.h" +#include +#include + +namespace Slicer { + NoRowsReturned::NoRowsReturned() : std::runtime_error("No rows returned") { } + + TooManyRowsReturned::TooManyRowsReturned() : std::runtime_error("Too many rows returned") { } + + class SqlSource : public Slicer::ValueSource, + public Slicer::TValueSource, + public Slicer::TValueSource + { + public: + SqlSource(const DB::Column & c) : + column(c) + { + } + + bool isNull() const + { + return column.isNull(); + } + + void set(boost::posix_time::ptime & b) const override + { + column >> b; + } + + void set(boost::posix_time::time_duration & b) const override + { + column >> b; + } + + void set(bool & b) const override + { + column >> b; + } + + void set(Ice::Byte & b) const override + { + int64_t cb; + column >> cb; + b = boost::numeric_cast(cb); + } + + void set(Ice::Short & b) const override + { + int64_t cb; + column >> cb; + b = boost::numeric_cast(cb); + } + + void set(Ice::Int & b) const override + { + int64_t cb; + column >> cb; + b = boost::numeric_cast(cb); + } + + void set(Ice::Long & b) const override + { + column >> b; + } + + void set(Ice::Float & b) const override + { + double cb; + column >> cb; + b = boost::numeric_cast(cb); + } + + void set(Ice::Double & b) const override + { + column >> b; + } + + void set(std::string & b) const override + { + column >> b; + } + + private: + const DB::Column & column; + }; + typedef IceUtil::Handle SqlSourcePtr; + + SqlSelectDeserializer::SqlSelectDeserializer(DB::SelectCommand & c, IceUtil::Optional tc) : + cmd(c), + typeIdColName(tc) + { + } + + void + SqlSelectDeserializer::Deserialize(Slicer::ModelPartPtr mp) + { + cmd.execute(); + columnCount = cmd.columnCount(); + if (typeIdColName) { + typeIdColIdx = cmd.getOrdinal(*typeIdColName); + } + switch (mp->GetType()) { + case Slicer::mpt_Sequence: + DeserializeSequence(mp); + return; + case Slicer::mpt_Complex: + DeserializeObject(mp); + return; + case Slicer::mpt_Simple: + DeserializeSimple(mp); + return; + default: + throw std::invalid_argument("Unspported model type"); + } + } + + void + SqlSelectDeserializer::DeserializeSimple(Slicer::ModelPartPtr mp) + { + auto fmp = mp->GetAnonChild(); + if (!cmd.fetch()) { + throw NoRowsReturned(); + } + SqlSourcePtr h = new SqlSource(cmd[0]); + if (!h->isNull()) { + fmp->Create(); + fmp->SetValue(h); + fmp->Complete(); + } + if (cmd.fetch()) { + throw TooManyRowsReturned(); + } + } + + void + SqlSelectDeserializer::DeserializeSequence(Slicer::ModelPartPtr mp) + { + mp = mp->GetAnonChild(); + while (cmd.fetch()) { + DeserializeRow(mp); + } + } + + void + SqlSelectDeserializer::DeserializeObject(Slicer::ModelPartPtr mp) + { + if (!cmd.fetch()) { + throw NoRowsReturned(); + } + DeserializeRow(mp); + if (cmd.fetch()) { + while (cmd.fetch()) ; + throw TooManyRowsReturned(); + } + } + + void + SqlSelectDeserializer::DeserializeRow(Slicer::ModelPartPtr mp) + { + auto rmp = mp->GetAnonChild(); + if (rmp) { + if (typeIdColIdx) { + std::string subclass; + cmd[*typeIdColIdx] >> subclass; + rmp = rmp->GetSubclassModelPart(subclass); + } + rmp->Create(); + for (auto col = 0u; col < columnCount; col += 1) { + const DB::Column & c = cmd[col]; + SqlSourcePtr h = new SqlSource(c); + auto fmpr = rmp->GetAnonChildRef([&c](Slicer::HookCommonPtr h) { + return boost::iequals(c.name.raw(), h->PartName()); + }); + if (fmpr) { + auto fmp = fmpr->Child(); + if (!h->isNull()) { + fmp->Create(); + fmp->SetValue(h); + fmp->Complete(); + } + } + } + rmp->Complete(); + } + } +} + diff --git a/slicer/db/sqlSelectDeserializer.h b/slicer/db/sqlSelectDeserializer.h new file mode 100644 index 0000000..3d73c94 --- /dev/null +++ b/slicer/db/sqlSelectDeserializer.h @@ -0,0 +1,39 @@ +#ifndef SLICER_DB_SQLSELECTDESERIALIZER_H +#define SLICER_DB_SQLSELECTDESERIALIZER_H + +#include +#include +#include + +namespace Slicer { + class NoRowsReturned : public std::runtime_error { + public: + NoRowsReturned(); + }; + + class TooManyRowsReturned : public std::runtime_error { + public: + TooManyRowsReturned(); + }; + + class DLL_PUBLIC SqlSelectDeserializer : public Slicer::Deserializer { + public: + SqlSelectDeserializer(DB::SelectCommand &, IceUtil::Optional typeIdCol = IceUtil::Optional()); + + virtual void Deserialize(Slicer::ModelPartPtr) override; + + protected: + void DLL_PRIVATE DeserializeSimple(Slicer::ModelPartPtr); + void DLL_PRIVATE DeserializeObject(Slicer::ModelPartPtr); + void DLL_PRIVATE DeserializeSequence(Slicer::ModelPartPtr); + void DLL_PRIVATE DeserializeRow(Slicer::ModelPartPtr); + + DB::SelectCommand & cmd; + unsigned int columnCount; + IceUtil::Optional typeIdColName; + IceUtil::Optional typeIdColIdx; + }; +} + +#endif + diff --git a/slicer/db/testSelect.cpp b/slicer/db/testSelect.cpp new file mode 100644 index 0000000..29ea5bc --- /dev/null +++ b/slicer/db/testSelect.cpp @@ -0,0 +1,203 @@ +#define BOOST_TEST_MODULE db_select +#include +#include +#include +#include +#include +#include "sqlSelectDeserializer.h" +#include + +class StandardMockDatabase : public PQ::Mock { + public: + StandardMockDatabase() : PQ::Mock("user=postgres dbname=postgres", "pqmock", { + rootDir / "slicer.sql" }) + { + } +}; + +BOOST_GLOBAL_FIXTURE( StandardMockDatabase ); + +typedef boost::shared_ptr DBPtr; +typedef boost::shared_ptr SelectPtr; + +boost::posix_time::ptime +mkDateTime(short y, short m, short d, short h, short M, short s) +{ + return boost::posix_time::ptime(boost::gregorian::date(y, m, d), boost::posix_time::time_duration(h, M, s)); +} + +boost::posix_time::time_duration +mkTimespan(short d, short h, short M, short s) +{ + return boost::posix_time::time_duration((d * 24) + h, M, s); +} + +BOOST_AUTO_TEST_CASE( select_simple_int ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT MAX(id) FROM test")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_EQUAL(4, bi); +} + +BOOST_AUTO_TEST_CASE( select_simple_double ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT MAX(fl) FROM test")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_CLOSE(1234.1234, bi, 0.0001); +} + +BOOST_AUTO_TEST_CASE( select_simple_string ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT MAX(string) FROM test")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_EQUAL("text two", bi); +} + +BOOST_AUTO_TEST_CASE( select_simple_true ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT true")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_EQUAL(true, bi); +} + +BOOST_AUTO_TEST_CASE( select_simple_false ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT NOT(true)")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_EQUAL(false, bi); +} + +BOOST_AUTO_TEST_CASE( select_single ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand( + "SELECT boolean mbool, \ + id mbyte, id mshort, id mint, id mlong, \ + fl mdouble, fl mfloat, \ + string mstring \ + FROM test \ + ORDER BY id \ + LIMIT 1")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE(bi); + BOOST_REQUIRE_EQUAL(true, bi->mbool); + BOOST_REQUIRE_EQUAL(1, bi->mbyte); + BOOST_REQUIRE_EQUAL(1, bi->mshort); + BOOST_REQUIRE_EQUAL(1, bi->mint); + BOOST_REQUIRE_EQUAL(1, bi->mlong); + BOOST_REQUIRE_CLOSE(1.1, bi->mfloat, 0.0001); + BOOST_REQUIRE_CLOSE(1.1, bi->mdouble, 0.0001); + BOOST_REQUIRE_EQUAL("text one", bi->mstring); +} + +BOOST_AUTO_TEST_CASE( select_inherit_single ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand( + "SELECT id a, '::TestModule::D' || CAST(id AS TEXT) tc, 200 b, 300 c, 400 d \ + FROM test \ + WHERE id = 2")); + auto bi = Slicer::DeserializeAny(*sel, "tc"); + BOOST_REQUIRE(bi); + auto d2 = TestModule::D2Ptr::dynamicCast(bi); + BOOST_REQUIRE(d2); + BOOST_REQUIRE_EQUAL(2, d2->a); + BOOST_REQUIRE_EQUAL(300, d2->c); +} + +BOOST_AUTO_TEST_CASE( select_inherit_sequence ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand( + "SELECT id a, '::TestModule::D' || CAST(id AS TEXT) tc, 200 b, 300 c, 400 d \ + FROM test \ + WHERE id < 4 \ + ORDER BY id DESC")); + auto bi = Slicer::DeserializeAny(*sel, "tc"); + BOOST_REQUIRE_EQUAL(3, bi.size()); + auto d3 = TestModule::D3Ptr::dynamicCast(bi[0]); + auto d2 = TestModule::D2Ptr::dynamicCast(bi[1]); + auto d1 = TestModule::D1Ptr::dynamicCast(bi[2]); + BOOST_REQUIRE(d3); + BOOST_REQUIRE(d2); + BOOST_REQUIRE(d1); + BOOST_REQUIRE_EQUAL(3, d3->a); + BOOST_REQUIRE_EQUAL(300, d3->c); + BOOST_REQUIRE_EQUAL(400, d3->d); + BOOST_REQUIRE_EQUAL(2, d2->a); + BOOST_REQUIRE_EQUAL(300, d2->c); + BOOST_REQUIRE_EQUAL(1, d1->a); + BOOST_REQUIRE_EQUAL(200, d1->b); +} + +BOOST_AUTO_TEST_CASE( select_inherit_datetime ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand( + "SELECT dt, to_char(dt, 'YYYY-MM-DD') date, ts \ + FROM test \ + WHERE id = 3")); + DB::SpecificTypesPtr bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_EQUAL(2015, bi->dt.year); + BOOST_REQUIRE_EQUAL(3, bi->dt.month); + BOOST_REQUIRE_EQUAL(27, bi->dt.day); + BOOST_REQUIRE_EQUAL(23, bi->dt.hour); + BOOST_REQUIRE_EQUAL(6, bi->dt.minute); + BOOST_REQUIRE_EQUAL(3, bi->dt.second); + BOOST_REQUIRE_EQUAL(2015, bi->date.year); + BOOST_REQUIRE_EQUAL(3, bi->date.month); + BOOST_REQUIRE_EQUAL(27, bi->date.day); + BOOST_REQUIRE_EQUAL(1, bi->ts.days); + BOOST_REQUIRE_EQUAL(13, bi->ts.hours); + BOOST_REQUIRE_EQUAL(13, bi->ts.minutes); + BOOST_REQUIRE_EQUAL(12, bi->ts.seconds); +} + +template +T +BoostThrowWrapperHelper(P & ... p) +{ + return Slicer::DeserializeAny(p...); +} + +BOOST_AUTO_TEST_CASE( select_inherit_tooManyRowsSimple ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT id FROM test")); + BOOST_REQUIRE_THROW(BoostThrowWrapperHelper(*sel), Slicer::TooManyRowsReturned); +} + +BOOST_AUTO_TEST_CASE( select_inherit_noRowsSimple ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT id FROM test WHERE false")); + BOOST_REQUIRE_THROW(BoostThrowWrapperHelper(*sel), Slicer::NoRowsReturned); +} + +BOOST_AUTO_TEST_CASE( select_inherit_tooManyRowsComplex ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT id FROM test")); + BOOST_REQUIRE_THROW(BoostThrowWrapperHelper(*sel), Slicer::TooManyRowsReturned); +} + +BOOST_AUTO_TEST_CASE( select_inherit_noRowsComplex ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT id FROM test WHERE false")); + BOOST_REQUIRE_THROW(BoostThrowWrapperHelper(*sel), Slicer::NoRowsReturned); +} + +BOOST_AUTO_TEST_CASE( select_inherit_emptySequence ) +{ + auto db = DBPtr(DB::MockDatabase::openConnectionTo("pqmock")); + auto sel = SelectPtr(db->newSelectCommand("SELECT id FROM test WHERE false")); + auto bi = Slicer::DeserializeAny(*sel); + BOOST_REQUIRE_EQUAL(0, bi.size()); +} + -- cgit v1.2.3