summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorDan Goodliffe <dan@randomdan.homeip.net>2015-10-12 21:14:03 +0100
committerDan Goodliffe <dan@randomdan.homeip.net>2015-10-12 21:14:03 +0100
commit20211f62e4c4d40af15e22cfd921fc86d36de0c0 (patch)
treeb9b4c240f2ee949344f14a28c6504d1f31b2574a
parentAdd database specific type (timespan) and convertors (diff)
downloadslicer-20211f62e4c4d40af15e22cfd921fc86d36de0c0.tar.bz2
slicer-20211f62e4c4d40af15e22cfd921fc86d36de0c0.tar.xz
slicer-20211f62e4c4d40af15e22cfd921fc86d36de0c0.zip
Add Slicer support for deserializing a SQL select
-rw-r--r--slicer/Jamfile.jam2
-rw-r--r--slicer/db/Jamfile.jam49
-rw-r--r--slicer/db/modelParts.cpp0
-rw-r--r--slicer/db/slicer.sql12
-rw-r--r--slicer/db/sqlSelectDeserializer.cpp187
-rw-r--r--slicer/db/sqlSelectDeserializer.h39
-rw-r--r--slicer/db/testSelect.cpp203
7 files changed, 492 insertions, 0 deletions
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 : <install-source-root>. : tool//slicer : slicer//slicer : [ glob slicer/*.h ] ;
package.install install-xml : <install-source-root>. : : xml//slicer-xml : [ glob xml/*.h ] ;
package.install install-json : <install-source-root>. : : json//slicer-json : [ glob json/*.h ] ;
+package.install install-db : <install-source-root>. : : 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 : : : :
+ <cflags>"`pkg-config --cflags glibmm-2.4`"
+ <linkflags>"`pkg-config --libs glibmm-2.4`"
+ ;
+
+lib dbppcore : : : : <include>/usr/include/dbpp ;
+lib dbpp-postgresql : : : : <include>/usr/include/dbpp-postgresql ;
+lib boost_system ;
+lib boost_filesystem ;
+lib boost_utf : : <name>boost_unit_test_framework ;
+lib IceUtil ;
+
+lib slicer-db :
+ [ glob *.cpp : test*.cpp ]
+ :
+ <include>..
+ <library>IceUtil
+ <library>dbppcore
+ <library>glibmm
+ <library>../slicer//slicer
+ <cflags>-fvisibility=hidden
+ <variant>release:<cflags>-flto
+ : :
+ <library>dbppcore
+ ;
+
+path-constant me : . ;
+
+run testSelect.cpp
+ : : :
+ <define>ROOT=\"$(me)\"
+ <define>BOOST_TEST_DYN_LINK
+ <library>slicer-db
+ <library>dbpp-postgresql
+ <library>boost_system
+ <library>boost_filesystem
+ <library>boost_utf
+ <library>../test//slicer-test
+ <library>../test//common
+ <library>../slicer//slicer
+ <include>..
+ <dependency>slicer.sql
+ <dependency>../test//compilation
+ :
+ testSelect
+ ;
+
diff --git a/slicer/db/modelParts.cpp b/slicer/db/modelParts.cpp
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/slicer/db/modelParts.cpp
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 <boost/algorithm/string/predicate.hpp>
+#include <boost/numeric/conversion/cast.hpp>
+
+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<boost::posix_time::time_duration>,
+ public Slicer::TValueSource<boost::posix_time::ptime>
+ {
+ 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<Ice::Byte>(cb);
+ }
+
+ void set(Ice::Short & b) const override
+ {
+ int64_t cb;
+ column >> cb;
+ b = boost::numeric_cast<Ice::Byte>(cb);
+ }
+
+ void set(Ice::Int & b) const override
+ {
+ int64_t cb;
+ column >> cb;
+ b = boost::numeric_cast<Ice::Int>(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<Ice::Float>(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<SqlSource> SqlSourcePtr;
+
+ SqlSelectDeserializer::SqlSelectDeserializer(DB::SelectCommand & c, IceUtil::Optional<std::string> 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 <slicer/serializer.h>
+#include <selectcommand.h>
+#include <visibility.h>
+
+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<std::string> typeIdCol = IceUtil::Optional<std::string>());
+
+ 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<std::string> typeIdColName;
+ IceUtil::Optional<unsigned int> 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 <boost/test/unit_test.hpp>
+#include <boost/date_time/posix_time/posix_time_io.hpp>
+#include <mock.h>
+#include <slicer/slicer.h>
+#include <definedDirs.h>
+#include "sqlSelectDeserializer.h"
+#include <types.h>
+
+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<DB::Connection> DBPtr;
+typedef boost::shared_ptr<DB::SelectCommand> 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<Slicer::SqlSelectDeserializer, Ice::Int>(*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<Slicer::SqlSelectDeserializer, Ice::Double>(*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<Slicer::SqlSelectDeserializer, std::string>(*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<Slicer::SqlSelectDeserializer, bool>(*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<Slicer::SqlSelectDeserializer, bool>(*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<Slicer::SqlSelectDeserializer, TestModule::BuiltInsPtr>(*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<Slicer::SqlSelectDeserializer, TestModule::BasePtr>(*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<Slicer::SqlSelectDeserializer, TestModule::BaseSeq>(*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<Slicer::SqlSelectDeserializer, DB::SpecificTypesPtr>(*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 <typename T, typename ... P>
+T
+BoostThrowWrapperHelper(P & ... p)
+{
+ return Slicer::DeserializeAny<Slicer::SqlSelectDeserializer, T>(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<Ice::Int>(*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<Ice::Int>(*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<TestModule::BuiltInsPtr>(*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<TestModule::BuiltInsPtr>(*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<Slicer::SqlSelectDeserializer, TestModule::BaseSeq>(*sel);
+ BOOST_REQUIRE_EQUAL(0, bi.size());
+}
+