Add schema for storing previously recorded, things to record

Store results of recording schedule
Centralise some more DB stuff

9 files changed, 227 insertions, 38 deletions

diff --git a/p2pvr/datasources/schema.sql b/p2pvr/datasources/schema.sql
index f65cf5f..a3f5769 100644
--- a/p2pvr/datasources/schema.sql
+++ b/p2pvr/datasources/schema.sql
@@ -163,6 +163,58 @@ CREATE TABLE networks (
 ALTER TABLE public.networks OWNER TO gentoo;
 
 --
+-- Name: record; Type: TABLE; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+CREATE TABLE record (
+    serviceid integer NOT NULL,
+    eventid integer NOT NULL,
+    recordstatus integer NOT NULL,
+    recordingstatus integer DEFAULT 0 NOT NULL
+);
+
+
+ALTER TABLE public.record OWNER TO gentoo;
+
+--
+-- Name: recorded; Type: TABLE; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+CREATE TABLE recorded (
+    recordedid integer NOT NULL,
+    scheduleid integer,
+    title text,
+    subtitle text,
+    description text,
+    starttime timestamp without time zone NOT NULL,
+    duration interval NOT NULL
+);
+
+
+ALTER TABLE public.recorded OWNER TO gentoo;
+
+--
+-- Name: recorded_recordedid_seq; Type: SEQUENCE; Schema: public; Owner: gentoo
+--
+
+CREATE SEQUENCE recorded_recordedid_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.recorded_recordedid_seq OWNER TO gentoo;
+
+--
+-- Name: recorded_recordedid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: gentoo
+--
+
+ALTER SEQUENCE recorded_recordedid_seq OWNED BY recorded.recordedid;
+
+
+--
 -- Name: schedules; Type: TABLE; Schema: public; Owner: gentoo; Tablespace: 
 --
 
@@ -250,6 +302,13 @@ CREATE TABLE transportstreams (
 ALTER TABLE public.transportstreams OWNER TO gentoo;
 
 --
+-- Name: recordedid; Type: DEFAULT; Schema: public; Owner: gentoo
+--
+
+ALTER TABLE ONLY recorded ALTER COLUMN recordedid SET DEFAULT nextval('recorded_recordedid_seq'::regclass);
+
+
+--
 -- Name: scheduleid; Type: DEFAULT; Schema: public; Owner: gentoo
 --
 
@@ -305,6 +364,22 @@ ALTER TABLE ONLY networks
 
 
 --
+-- Name: pk_recorded; Type: CONSTRAINT; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+ALTER TABLE ONLY recorded
+    ADD CONSTRAINT pk_recorded PRIMARY KEY (recordedid);
+
+
+--
+-- Name: pk_records; Type: CONSTRAINT; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+ALTER TABLE ONLY record
+    ADD CONSTRAINT pk_records PRIMARY KEY (serviceid, eventid);
+
+
+--
 -- Name: pk_schedules; Type: CONSTRAINT; Schema: public; Owner: gentoo; Tablespace: 
 --
 
@@ -351,6 +426,27 @@ CREATE INDEX idx_event_title ON events USING btree (lower(title));
 
 
 --
+-- Name: idx_recorded_schedule; Type: INDEX; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+CREATE INDEX idx_recorded_schedule ON recorded USING btree (scheduleid);
+
+
+--
+-- Name: idx_recorded_starttime; Type: INDEX; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+CREATE INDEX idx_recorded_starttime ON recorded USING btree (starttime);
+
+
+--
+-- Name: idx_recorded_title; Type: INDEX; Schema: public; Owner: gentoo; Tablespace: 
+--
+
+CREATE INDEX idx_recorded_title ON recorded USING btree (title);
+
+
+--
 -- Name: fk_delivery_dvbc_transportstream; Type: FK CONSTRAINT; Schema: public; Owner: gentoo
 --
 
@@ -391,6 +487,22 @@ ALTER TABLE ONLY event_schedule
 
 
 --
+-- Name: fk_recorded_schedule; Type: FK CONSTRAINT; Schema: public; Owner: gentoo
+--
+
+ALTER TABLE ONLY recorded
+    ADD CONSTRAINT fk_recorded_schedule FOREIGN KEY (scheduleid) REFERENCES schedules(scheduleid) ON UPDATE CASCADE ON DELETE SET NULL;
+
+
+--
+-- Name: fk_records_event; Type: FK CONSTRAINT; Schema: public; Owner: gentoo
+--
+
+ALTER TABLE ONLY record
+    ADD CONSTRAINT fk_records_event FOREIGN KEY (serviceid, eventid) REFERENCES events(serviceid, eventid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
 -- Name: fk_schedule_event; Type: FK CONSTRAINT; Schema: public; Owner: gentoo
 --
 
diff --git a/p2pvr/lib/dbClient.cpp b/p2pvr/lib/dbClient.cpp
index a9b74e5..cdefc9d 100644
--- a/p2pvr/lib/dbClient.cpp
+++ b/p2pvr/lib/dbClient.cpp
@@ -1,5 +1,15 @@
 #include <pch.hpp>
 #include "dbClient.h"
+#include <sqlMergeTask.h>
+
+void
+DatabaseClient::SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key)
+{
+	merge->cols.insert(new SqlMergeTask::TargetColumn(name, key));
+	if (key) {
+		merge->keys.insert(name);
+	}
+}
 
 void
 DatabaseClient::onAllDatasources(const DataSourceCall & call) const
diff --git a/p2pvr/lib/dbClient.h b/p2pvr/lib/dbClient.h
index 97d19bd..6c59301 100644
--- a/p2pvr/lib/dbClient.h
+++ b/p2pvr/lib/dbClient.h
@@ -12,10 +12,15 @@
 #include <sqlHandleAsVariableType.h>
 #include "p2Helpers.h"
 
+class SqlMergeTask;
+
 class DatabaseClient : public virtual CommonObjects {
 	public:
 		typedef boost::shared_ptr<DB::SelectCommand> SelectPtr;
 		typedef boost::shared_ptr<DB::ModifyCommand> ModifyPtr;
+
+		static void SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key);
+
 	protected:
 		class TxHelper {
 			public:
diff --git a/p2pvr/lib/maintenance.cpp b/p2pvr/lib/maintenance.cpp
index 275951e..eb1b570 100644
--- a/p2pvr/lib/maintenance.cpp
+++ b/p2pvr/lib/maintenance.cpp
@@ -2,16 +2,6 @@
 #include "maintenance.h"
 #include <Ice/Ice.h>
 #include <linux/dvb/frontend.h>
-#include <sqlMergeTask.h>
-
-void
-Maintenance::SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key)
-{
-	merge->cols.insert(new SqlMergeTask::TargetColumn(name, key));
-	if (key) {
-		merge->keys.insert(name);
-	}
-}
 
 void
 Maintenance::UpdateAll(const Ice::Current & ice)
diff --git a/p2pvr/lib/maintenance.h b/p2pvr/lib/maintenance.h
index edb5070..9083ded 100644
--- a/p2pvr/lib/maintenance.h
+++ b/p2pvr/lib/maintenance.h
@@ -4,8 +4,6 @@
 #include <p2pvr.h>
 #include "dbClient.h"
 
-class SqlMergeTask;
-
 class Maintenance : public P2PVR::Maintenance, public DatabaseClient {
 	public:
 		void UpdateAll(const Ice::Current &);
@@ -15,8 +13,6 @@ class Maintenance : public P2PVR::Maintenance, public DatabaseClient {
 		void UpdateProgramAssociations(short type, const Ice::Current &);
 		void UpdateProgramMaps(short type, const Ice::Current &);
 		void UpdateEvents(short type, const Ice::Current &);
-
-		static void SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key);
 };
 
 #endif
diff --git a/p2pvr/lib/maintenance/network.cpp b/p2pvr/lib/maintenance/network.cpp
index c2e4494..7806824 100644
--- a/p2pvr/lib/maintenance/network.cpp
+++ b/p2pvr/lib/maintenance/network.cpp
@@ -27,20 +27,20 @@ class SiNetworkInformationMerger : public SiNetworkInformationParser {
 			}
 
 			SqlMergeTask mergeNetwork("postgres", "networks");
-			CreateColumns<DVBSI::NetworkPtr>(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeNetwork, _1, _2));
+			CreateColumns<DVBSI::NetworkPtr>(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeNetwork, _1, _2));
 			std::vector<DVBSI::NetworkPtr> networks = { n };
 			mergeNetwork.sources.insert(new ContainerIterator<std::vector<DVBSI::NetworkPtr>>(&networks));
 			mergeNetwork.loadComplete(commonObjects);
 			mergeNetwork.execute(NULL);
 
 			SqlMergeTask mergeTransports("postgres", "transportstreams");
-			CreateColumns<DVBSI::NetworkTransportStreamPtr>(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeTransports, _1, _2));
+			CreateColumns<DVBSI::NetworkTransportStreamPtr>(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeTransports, _1, _2));
 			mergeTransports.sources.insert(new ContainerIterator<DVBSI::NetworkTransportStreams>(&n->TransportStreams));
 			mergeTransports.loadComplete(commonObjects);
 			mergeTransports.execute(NULL);
 
 			SqlMergeTask mergeDvbt("postgres", "delivery_dvbt");
-			CreateColumns<DVBSI::TerrestrialDeliveryPtr>(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeDvbt, _1, _2));
+			CreateColumns<DVBSI::TerrestrialDeliveryPtr>(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeDvbt, _1, _2));
 			BOOST_FOREACH(const auto & s, n->TransportStreams) {
 				if (s->Terrestrial) {
 					mergeDvbt.sources.insert(new SingleIterator<DVBSI::TerrestrialDeliveryPtr>(&s->Terrestrial));
@@ -50,7 +50,7 @@ class SiNetworkInformationMerger : public SiNetworkInformationParser {
 			mergeDvbt.execute(NULL);
 
 			SqlMergeTask mergeServices("postgres", "services");
-			CreateColumns<DVBSI::NetworkService>(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeServices, _1, _2));
+			CreateColumns<DVBSI::NetworkService>(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeServices, _1, _2));
 			BOOST_FOREACH(const auto & s, n->TransportStreams) {
 				mergeServices.sources.insert(new ContainerIterator<DVBSI::NetworkServiceList>(&s->Services));
 			}
diff --git a/p2pvr/lib/maintenance/services.cpp b/p2pvr/lib/maintenance/services.cpp
index efa5974..ec4c6dc 100644
--- a/p2pvr/lib/maintenance/services.cpp
+++ b/p2pvr/lib/maintenance/services.cpp
@@ -24,7 +24,7 @@ class SiServicesMerger : public SiServicesParser {
 			}
 
 			SqlMergeTask mergeServices("postgres", "services");
-			CreateColumns<DVBSI::ServicePtr>(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeServices, _1, _2));
+			CreateColumns<DVBSI::ServicePtr>(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeServices, _1, _2));
 			// Don't change the list of services available from the network
 			mergeServices.doDelete = VariableType(false);
 			mergeServices.doInsert = VariableType(false);
diff --git a/p2pvr/lib/schedules.cpp b/p2pvr/lib/schedules.cpp
index b3b6800..f36cade 100644
--- a/p2pvr/lib/schedules.cpp
+++ b/p2pvr/lib/schedules.cpp
@@ -5,7 +5,9 @@
 #include <logger.h>
 #include <Ice/Ice.h>
 #include <sqlVariableBinder.h>
+#include <sqlMergeTask.h>
 #include "p2Helpers.h"
+#include "containerIterator.h"
 #include "resources.h"
 #include <boost/date_time/posix_time/posix_time.hpp> 
 
@@ -23,10 +25,42 @@ DECLARE_OPTIONS(Schedules, "P2PVR Scheduler options")
  "Implementation of episode group scheduler problem solver")
 END_OPTIONS()
 
-typedef boost::tuple<std::string, int, int, int, datetime, datetime, int> ScheduleCandidate;
+class ScheduleCandidate {
+	public:
+		std::string What;
+		int ServiceId;
+		int EventId;
+		int TransportStreamId;
+		datetime StartTime;
+		datetime StopTime;
+		int Priority;
+};
 typedef boost::shared_ptr<ScheduleCandidate> ScheduleCandidatePtr;
 typedef std::vector<ScheduleCandidatePtr> ScheduleCandidates;
 
+enum RecordStatuses {
+	Record_WillRecordThisShowing = 0,
+	Record_WillRecordOtherShowing = 1,
+	Record_CannotRecordAnyShowing = 2
+};
+
+class Record {
+	public:
+		Record() { };
+		Record(int s, int e, RecordStatuses rs) :
+			ServiceId(s),
+			EventId(e),
+			RecordStatus(rs)
+		{
+		}
+
+		int ServiceId;
+		int EventId;
+		RecordStatuses RecordStatus;
+};
+typedef boost::shared_ptr<Record> RecordPtr;
+typedef std::vector<RecordPtr> Records;
+
 template<>
 void
 CreateColumns<ScheduleCandidatePtr>(const ColumnCreator & cc)
@@ -44,13 +78,13 @@ template<>
 void
 UnbindColumns(RowState & rs, ScheduleCandidatePtr const & s)
 {
-	rs.fields[0] >> boost::get<0>(*s);
-	rs.fields[1] >> boost::get<1>(*s);
-	rs.fields[2] >> boost::get<2>(*s);
-	rs.fields[3] >> boost::get<3>(*s);
-	rs.fields[4] >> boost::get<4>(*s);
-	rs.fields[5] >> boost::get<5>(*s);
-	rs.fields[6] >> boost::get<6>(*s);
+	rs.fields[0] >> s->What;
+	rs.fields[1] >> s->ServiceId;
+	rs.fields[2] >> s->EventId;
+	rs.fields[3] >> s->TransportStreamId;
+	rs.fields[4] >> s->StartTime;
+	rs.fields[5] >> s->StopTime;
+	rs.fields[6] >> s->Priority;
 }
 
 template<>
@@ -83,6 +117,24 @@ UnbindColumns(RowState & rs, P2PVR::SchedulePtr const & s)
 	rs.fields[8] >> s->Repeats;
 }
 
+template<>
+void
+CreateColumns<RecordPtr>(const ColumnCreator & cc)
+{
+	cc("serviceid", true);
+	cc("eventid", true);
+	cc("recordstatus", false);
+}
+
+template<>
+void
+BindColumns(RowState & rs, RecordPtr const & s)
+{
+	rs.fields[0] << s->ServiceId;
+	rs.fields[1] << s->EventId;
+	rs.fields[2] << (int)s->RecordStatus;
+}
+
 Showing::Showing(unsigned int s, unsigned int e, unsigned int t, datetime start, datetime stop, int p, const Episode * ep) :
 	episode(ep),
 	serviceId(s),
@@ -253,18 +305,17 @@ Schedules::DoReschedule(const Ice::Current & ice)
 	EpisodePtr cur;
 	int minPriority = 0;
 	BOOST_FOREACH(const auto & c, episodes) {
-		const auto & thisWhat = boost::get<0>(*c);
-		if (!cur || cur->what != thisWhat) {
-			cur = new Episode(thisWhat);
+		if (!cur || cur->what != c->What) {
+			cur = new Episode(c->What);
 			scheduleList.push_back(cur);
 		}
-		ShowingPtr s = new Showing(boost::get<1>(*c), boost::get<2>(*c), boost::get<3>(*c),
-				boost::get<4>(*c), boost::get<5>(*c), boost::get<6>(*c), cur.get());
+		ShowingPtr s = new Showing(c->ServiceId, c->EventId, c->TransportStreamId,
+				c->StartTime, c->StopTime, c->Priority, cur.get());
 		minPriority = std::min(minPriority, s->priority);
 		cur->showings.push_back(s);
 		allShowings.push_back(s);
 	}
-	Logger()->messagebf(LOG_DEBUG, "%d episodes created", scheduleList.size());
+	Logger()->messagebf(LOG_DEBUG, "%d episodes created, %s showings", scheduleList.size(), allShowings.size());
 	BOOST_FOREACH(const auto & e, scheduleList) {
 		Logger()->messagebf(LOG_DEBUG, "	%s", e->what);
 		BOOST_FOREACH(const auto & s, e->showings) {
@@ -274,6 +325,7 @@ Schedules::DoReschedule(const Ice::Current & ice)
 		e->priority /= e->showings.size();
 	}
 
+	Records records;
 	// Solve
 	while (!scheduleList.empty()) {
 		auto work = scheduleList.begin();
@@ -317,7 +369,31 @@ Schedules::DoReschedule(const Ice::Current & ice)
 			}
 		}
 		Logger()->message(LOG_DEBUG, "----------");
+		BOOST_FOREACH(const auto & c, group) {
+			bool found = false;
+			BOOST_FOREACH(const auto & i, c->showings) {
+				if (i && selected.find(i) != selected.end()) {
+					found = true;
+					break;
+				}
+			}
+			BOOST_FOREACH(const auto & i, c->showings) {
+				if (i) {
+					records.push_back(RecordPtr(new Record(i->serviceId, i->eventId,
+									found ?
+									selected.find(i) != selected.end() ? Record_WillRecordThisShowing : Record_WillRecordOtherShowing :
+									Record_CannotRecordAnyShowing)));
+				}
+			}
+		}
 	}
+
+	TxHelper tx(this);
+	SqlMergeTask mergeRecords("postgres", "record");
+	CreateColumns<RecordPtr>(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeRecords, _1, _2));
+	mergeRecords.sources.insert(new ContainerIterator<Records>(&records));
+	mergeRecords.loadComplete(this);
+	mergeRecords.execute(NULL);
 }
 
 void
diff --git a/p2pvr/lib/sql/Schedules_GetCandidates.sql b/p2pvr/lib/sql/Schedules_GetCandidates.sql
index 1b9441b..aa6ec0f 100644
--- a/p2pvr/lib/sql/Schedules_GetCandidates.sql
+++ b/p2pvr/lib/sql/Schedules_GetCandidates.sql
@@ -10,11 +10,11 @@ and sv.serviceid = e.serviceid
 and e.starttime > now()
 and not exists (
 	select 1
-	from seen
-	where lower(e.title) = lower(seen.title)
-	and coalesce(lower(e.subtitle), '') = coalesce(lower(seen.subtitle), '')
-	and ts_rank(to_tsvector(e.description), plainto_tsquery(seen.description)) +
-		ts_rank(to_tsvector(seen.description), plainto_tsquery(e.description)) > 1)
+	from recorded r
+	where lower(e.title) = lower(r.title)
+	and coalesce(lower(e.subtitle), '') = coalesce(lower(r.subtitle), '')
+	and ts_rank(to_tsvector(e.description), plainto_tsquery(r.description)) +
+		ts_rank(to_tsvector(r.description), plainto_tsquery(e.description)) > 1)
 group by e.serviceid, e.eventid, sv.serviceid
 order by max(s.priority) desc, e.title, e.subtitle, e.description, sv.transportstreamid, e.starttime