Tidy up and fix some DB core code

Implement the rest of schdules interface properly

13 files changed, 159 insertions, 42 deletions

diff --git a/p2pvr/datasources/schema.sql b/p2pvr/datasources/schema.sql
index acd78ae..f65cf5f 100644
--- a/p2pvr/datasources/schema.sql
+++ b/p2pvr/datasources/schema.sql
@@ -171,7 +171,11 @@ CREATE TABLE schedules (
     serviceid integer,
     eventid integer,
     title text,
-    search text
+    search text,
+    priority integer DEFAULT 0 NOT NULL,
+    early interval DEFAULT '00:00:00'::interval NOT NULL,
+    late interval DEFAULT '00:00:00'::interval NOT NULL,
+    repeats boolean NOT NULL
 );
 
 
diff --git a/p2pvr/ice/p2pvr.ice b/p2pvr/ice/p2pvr.ice
index 0b0b596..b4aeb8a 100644
--- a/p2pvr/ice/p2pvr.ice
+++ b/p2pvr/ice/p2pvr.ice
@@ -188,11 +188,13 @@ module P2PVR {
 	// Something that defines what we would like to record.
 	class Schedule {
 		int ScheduleId;
-		optional(1) string Title;
-		optional(2) string Subtitle;
-		optional(3) int ServiceId;
-		optional(4) Common::TimeOfDay Time;
-		optional(5) string Search;
+		optional(1) int ServiceId;
+		optional(2) int EventId;
+		optional(3) string Title;
+		optional(4) string Search;
+		int Priority;
+		string Early;
+		string Late;
 		bool Repeats;
 	};
 	sequence<Schedule> ScheduleList;
diff --git a/p2pvr/lib/dbClient.cpp b/p2pvr/lib/dbClient.cpp
index 5252e5b..a9b74e5 100644
--- a/p2pvr/lib/dbClient.cpp
+++ b/p2pvr/lib/dbClient.cpp
@@ -1,24 +1,5 @@
 #include <pch.hpp>
 #include "dbClient.h"
-#include <rdbmsDataSource.h>
-#include <sqlVariableBinder.h>
-
-DatabaseClient::SelectPtr
-DatabaseClient::Select(const std::string & sql) const
-{
-	auto db = dataSource<RdbmsDataSource>("postgres");
-	return SelectPtr(db->getReadonly().newSelectCommand(sql));
-}
-DatabaseClient::SelectPtr
-DatabaseClient::Select(const std::string & sql, const std::list<VariableType> & vs) const
-{
-	SelectPtr sel(Select(sql));
-	unsigned int offset = 0;
-	BOOST_FOREACH(const auto & v, vs) {
-		boost::apply_visitor<const SqlVariableBinder, const VariableType>(SqlVariableBinder(sel.get(), offset++), v);
-	}
-	return sel;
-}
 
 void
 DatabaseClient::onAllDatasources(const DataSourceCall & call) const
@@ -35,3 +16,8 @@ DatabaseClient::TxHelper::TxHelper(const DatabaseClient * dbc) :
 {
 }
 
+DatabaseClient::NoRowsFoundException::NoRowsFoundException() :
+	std::runtime_error("No rows found")
+{
+}
+
diff --git a/p2pvr/lib/dbClient.h b/p2pvr/lib/dbClient.h
index aef2bf2..97d19bd 100644
--- a/p2pvr/lib/dbClient.h
+++ b/p2pvr/lib/dbClient.h
@@ -5,11 +5,17 @@
 #include <variableType.h>
 #include <list>
 #include <selectcommand.h>
+#include <modifycommand.h>
 #include <scopeObject.h>
+#include <rdbmsDataSource.h>
+#include <sqlVariableBinder.h>
+#include <sqlHandleAsVariableType.h>
+#include "p2Helpers.h"
 
 class DatabaseClient : public virtual CommonObjects {
 	public:
 		typedef boost::shared_ptr<DB::SelectCommand> SelectPtr;
+		typedef boost::shared_ptr<DB::ModifyCommand> ModifyPtr;
 	protected:
 		class TxHelper {
 			public:
@@ -18,10 +24,63 @@ class DatabaseClient : public virtual CommonObjects {
 				ScopeObject so;
 		};
 
-		SelectPtr Select(const std::string &) const;
-		SelectPtr Select(const std::string &, const std::list<VariableType> &) const;
+		template <typename... Args>
+		ModifyPtr Modify(const std::string & sql, const Args & ... args) const
+		{
+			auto db = dataSource<RdbmsDataSource>("postgres");
+			auto cmd = ModifyPtr(db->getWritable().newModifyCommand(sql));
+			Bind(cmd.get(), 0, args...);
+			return cmd;
+		}
+
+		template <typename... Args>
+		SelectPtr Select(const std::string & sql, const Args & ... args) const
+		{
+			auto db = dataSource<RdbmsDataSource>("postgres");
+			auto cmd = SelectPtr(db->getReadonly().newSelectCommand(sql));
+			Bind(cmd.get(), 0, args...);
+			return cmd;
+		}
+
+		class NoRowsFoundException : public std::runtime_error {
+			public:
+				NoRowsFoundException();
+		};
+
+		template <typename Rtn, typename... Args>
+		Rtn SelectScalar(const std::string & sql, const Args & ... args) const
+		{
+			auto db = dataSource<RdbmsDataSource>("postgres");
+			auto cmd = SelectPtr(db->getReadonly().newSelectCommand(sql));
+			Bind(cmd.get(), 0, args...);
+			while (cmd->fetch()) {
+				HandleAsVariableType h;
+				(*cmd)[0].apply(h);
+				Rtn r;
+				h.variable >> r;
+				return r;
+			}
+			throw NoRowsFoundException();
+		}
 
 	private:
+		static void Bind(DB::Command *, unsigned int) { }
+
+		template <typename Arg>
+		static void Bind(DB::Command * cmd, unsigned int offset, const Arg & arg)
+		{
+			VariableType v;
+			v << arg;
+			boost::apply_visitor<const SqlVariableBinder, const VariableType>(SqlVariableBinder(cmd, offset), v);
+		}
+		
+		template <typename Arg, typename... Args>
+		static void Bind(DB::Command * cmd, unsigned int offset, const Arg & arg, const Args & ... args)
+		{
+			Bind(cmd, offset, arg);
+			Bind(cmd, offset + 1, args...);
+		}
+
 		friend class TxHelper;
 		typedef boost::function<void(DataSourcePtr)> DataSourceCall;
 		void onAllDatasources(const DataSourceCall &) const;
diff --git a/p2pvr/lib/p2Helpers.h b/p2pvr/lib/p2Helpers.h
index ac88de1..e7732a0 100644
--- a/p2pvr/lib/p2Helpers.h
+++ b/p2pvr/lib/p2Helpers.h
@@ -16,7 +16,7 @@ template <typename T>
 VariableType &
 operator>>(VariableType & vt, IceUtil::Optional<T> & v)
 {
-	if (!vt.isNull()) {
+	if (vt.isNull()) {
 		v = NULL;
 	}
 	else {
diff --git a/p2pvr/lib/schedules.cpp b/p2pvr/lib/schedules.cpp
index 79ad624..b3b6800 100644
--- a/p2pvr/lib/schedules.cpp
+++ b/p2pvr/lib/schedules.cpp
@@ -9,7 +9,12 @@
 #include "resources.h"
 #include <boost/date_time/posix_time/posix_time.hpp> 
 
-ResourceString(GetScheduleConditates, lib_sql_GetScheduleConditates_sql);
+ResourceString(Schedules_GetCandidates, lib_sql_Schedules_GetCandidates_sql);
+ResourceString(Schedules_insert, lib_sql_Schedules_insert_sql);
+ResourceString(Schedules_insertNewId, lib_sql_Schedules_insertNewId_sql);
+ResourceString(Schedules_update, lib_sql_Schedules_update_sql);
+ResourceString(Schedules_delete, lib_sql_Schedules_delete_sql);
+ResourceString(Schedules_selectAll, lib_sql_Schedules_selectAll_sql);
 
 std::string Schedules::SchedulerAlgorithm;
 
@@ -48,6 +53,36 @@ UnbindColumns(RowState & rs, ScheduleCandidatePtr const & s)
 	rs.fields[6] >> boost::get<6>(*s);
 }
 
+template<>
+void
+CreateColumns<P2PVR::SchedulePtr>(const ColumnCreator & cc)
+{
+	cc("scheduleid", true);
+	cc("serviceid", false);
+	cc("eventid", false);
+	cc("title", false);
+	cc("search", false);
+	cc("priority", false);
+	cc("early", false);
+	cc("late", false);
+	cc("repeats", false);
+}
+
+template<>
+void
+UnbindColumns(RowState & rs, P2PVR::SchedulePtr const & s)
+{
+	rs.fields[0] >> s->ScheduleId;
+	rs.fields[1] >> s->ServiceId;
+	rs.fields[2] >> s->EventId;
+	rs.fields[3] >> s->Title;
+	rs.fields[4] >> s->Search;
+	rs.fields[5] >> s->Priority;
+	rs.fields[6] >> s->Early;
+	rs.fields[7] >> s->Late;
+	rs.fields[8] >> s->Repeats;
+}
+
 Showing::Showing(unsigned int s, unsigned int e, unsigned int t, datetime start, datetime stop, int p, const Episode * ep) :
 	episode(ep),
 	serviceId(s),
@@ -211,7 +246,7 @@ Schedules::DoReschedule(const Ice::Current & ice)
 	// Load list from database
 	ScheduleCandidates episodes;
 	SqlContainerCreator<ScheduleCandidates, ScheduleCandidate, ScheduleCandidatePtr> cct(episodes);
-	cct.populate(Select(GetScheduleConditates));
+	cct.populate(Select(Schedules_GetCandidates));
 
 	Episodes scheduleList;
 	Showings allShowings;
@@ -286,20 +321,34 @@ Schedules::DoReschedule(const Ice::Current & ice)
 }
 
 void
-Schedules::DeleteSchedule(int , const Ice::Current &)
+Schedules::DeleteSchedule(int id, const Ice::Current & ice)
 {
+	TxHelper tx(this);
+	Modify(Schedules_delete, id)->execute();
+	DoReschedule(ice);
 }
 
 P2PVR::ScheduleList
 Schedules::GetSchedules(const Ice::Current &)
 {
-	P2PVR::ScheduleList rtn;
-	return rtn;
+	P2PVR::ScheduleList schedules;
+	SqlContainerCreator<P2PVR::ScheduleList, P2PVR::Schedule> cct(schedules);
+	cct.populate(Select(Schedules_selectAll));
+	return schedules;
 }
 
 int
-Schedules::UpdateSchedule(const P2PVR::SchedulePtr &, const Ice::Current &)
+Schedules::UpdateSchedule(const P2PVR::SchedulePtr & s, const Ice::Current & ice)
 {
-	return 0;
+	TxHelper tx(this);
+	if (s->ScheduleId == 0) {
+		Modify(Schedules_insert, s->ServiceId, s->EventId, s->Title, s->Search, s->Priority, s->Early, s->Late, s->Repeats)->execute();
+		s->ScheduleId = SelectScalar<int>(Schedules_insertNewId);
+	}
+	else {
+		Modify(Schedules_update, s->ServiceId, s->EventId, s->Title, s->Search, s->Priority, s->Early, s->Late, s->Repeats, s->ScheduleId)->execute();
+	}
+	DoReschedule(ice);
+	return s->ScheduleId;
 }
 
diff --git a/p2pvr/lib/si.cpp b/p2pvr/lib/si.cpp
index 49d1df6..abcb9fd 100644
--- a/p2pvr/lib/si.cpp
+++ b/p2pvr/lib/si.cpp
@@ -40,11 +40,11 @@ SI::GetDeliveryForTransport(int id, const Ice::Current&)
 {
 	P2PVR::Deliveries rtn;
 	SqlContainerCreator<P2PVR::Deliveries, DVBSI::TerrestrialDelivery> cct(rtn);
-	cct.populate(Select("SELECT * FROM delivery_dvbt WHERE transportStreamId = ?", {id}));
+	cct.populate(Select("SELECT * FROM delivery_dvbt WHERE transportStreamId = ?", id));
 	SqlContainerCreator<P2PVR::Deliveries, DVBSI::CableDelivery> ccc(rtn);
-	ccc.populate(Select("SELECT * FROM delivery_dvbc WHERE transportStreamId = ?", {id}));
+	ccc.populate(Select("SELECT * FROM delivery_dvbc WHERE transportStreamId = ?", id));
 	SqlContainerCreator<P2PVR::Deliveries, DVBSI::SatelliteDelivery> ccs(rtn);
-	ccs.populate(Select("SELECT * FROM delivery_dvbs WHERE transportStreamId = ?", {id}));
+	ccs.populate(Select("SELECT * FROM delivery_dvbs WHERE transportStreamId = ?", id));
 	return rtn.front();
 }
 
@@ -53,11 +53,11 @@ SI::GetDeliveryForService(int id, const Ice::Current&)
 {
 	P2PVR::Deliveries rtn;
 	SqlContainerCreator<P2PVR::Deliveries, DVBSI::TerrestrialDelivery> cct(rtn);
-	cct.populate(Select("SELECT d.* FROM services s, delivery_dvbt d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", {id}));
+	cct.populate(Select("SELECT d.* FROM services s, delivery_dvbt d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", id));
 	SqlContainerCreator<P2PVR::Deliveries, DVBSI::CableDelivery> ccc(rtn);
-	ccc.populate(Select("SELECT d.* FROM services s, delivery_dvbc d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", {id}));
+	ccc.populate(Select("SELECT d.* FROM services s, delivery_dvbc d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", id));
 	SqlContainerCreator<P2PVR::Deliveries, DVBSI::SatelliteDelivery> ccs(rtn);
-	ccs.populate(Select("SELECT d.* FROM services s, delivery_dvbs d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", {id}));
+	ccs.populate(Select("SELECT d.* FROM services s, delivery_dvbs d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", id));
 	return rtn.front();
 }
 
@@ -75,7 +75,7 @@ SI::GetService(int id, const Ice::Current&)
 {
 	DVBSI::ServiceList rtn;
 	SqlContainerCreator<DVBSI::ServiceList, DVBSI::Service> cc(rtn);
-	cc.populate(Select("SELECT * FROM services WHERE serviceId = ?", {id}));
+	cc.populate(Select("SELECT * FROM services WHERE serviceId = ?", id));
 	return rtn.front();
 }
 
diff --git a/p2pvr/lib/sql/GetScheduleConditates.sql b/p2pvr/lib/sql/Schedules_GetCandidates.sql
index 1b9441b..1b9441b 100644
--- a/p2pvr/lib/sql/GetScheduleConditates.sql
+++ b/p2pvr/lib/sql/Schedules_GetCandidates.sql
diff --git a/p2pvr/lib/sql/Schedules_delete.sql b/p2pvr/lib/sql/Schedules_delete.sql
new file mode 100644
index 0000000..e14edc3
--- /dev/null
+++ b/p2pvr/lib/sql/Schedules_delete.sql
@@ -0,0 +1 @@
+DELETE FROM schedules WHERE scheduleId = ?
diff --git a/p2pvr/lib/sql/Schedules_insert.sql b/p2pvr/lib/sql/Schedules_insert.sql
new file mode 100644
index 0000000..100e78b
--- /dev/null
+++ b/p2pvr/lib/sql/Schedules_insert.sql
@@ -0,0 +1,2 @@
+INSERT INTO schedules(serviceId, eventId, title, search, priority, early, late, repeats)
+VALUES(?, ?, ?, ?, ?, ?, ?, ?)
diff --git a/p2pvr/lib/sql/Schedules_insertNewId.sql b/p2pvr/lib/sql/Schedules_insertNewId.sql
new file mode 100644
index 0000000..f66acd5
--- /dev/null
+++ b/p2pvr/lib/sql/Schedules_insertNewId.sql
@@ -0,0 +1 @@
+SELECT currval('schedules_scheduleid_seq');
diff --git a/p2pvr/lib/sql/Schedules_selectAll.sql b/p2pvr/lib/sql/Schedules_selectAll.sql
new file mode 100644
index 0000000..5970dc1
--- /dev/null
+++ b/p2pvr/lib/sql/Schedules_selectAll.sql
@@ -0,0 +1,3 @@
+SELECT scheduleid, serviceid, eventid, title, search, priority, early::text early, late::text late, repeats
+FROM schedules
+ORDER BY scheduleId
diff --git a/p2pvr/lib/sql/Schedules_update.sql b/p2pvr/lib/sql/Schedules_update.sql
new file mode 100644
index 0000000..56c9531
--- /dev/null
+++ b/p2pvr/lib/sql/Schedules_update.sql
@@ -0,0 +1,10 @@
+UPDATE schedules SET
+	serviceId = ?,
+	eventId = ?,
+	title = ?,
+	search = ?,
+	priority = ?,
+	early = ?,
+	late = ?,
+	repeats = ?
+WHERE scheduleId = ?