Add Slicer support for deserializing a SQL select

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());
+}
+