Select a remmeber the scheduleId for an upcoming recording

4 files changed, 49 insertions, 26 deletions

diff --git a/p2pvr/datasources/schema.sql b/p2pvr/datasources/schema.sql
index f6d1d45..53c0e08 100644
--- a/p2pvr/datasources/schema.sql
+++ b/p2pvr/datasources/schema.sql
@@ -156,7 +156,8 @@ CREATE TABLE record (
     serviceid integer NOT NULL,
     eventid integer NOT NULL,
     recordstatus integer NOT NULL,
-    recordingstatus integer DEFAULT 0 NOT NULL
+    recordingstatus integer DEFAULT 0 NOT NULL,
+    scheduleid integer NOT NULL
 );
 
 
@@ -559,6 +560,14 @@ ALTER TABLE ONLY record
 
 
 --
+-- Name: fk_records_schedule; Type: FK CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY record
+    ADD CONSTRAINT fk_records_schedule FOREIGN KEY (scheduleid) REFERENCES schedules(scheduleid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
 -- Name: fk_schedule_event; Type: FK CONSTRAINT; Schema: public; Owner: -
 --
 
diff --git a/p2pvr/lib/schedules.cpp b/p2pvr/lib/schedules.cpp
index f6fedc2..8fb96ac 100644
--- a/p2pvr/lib/schedules.cpp
+++ b/p2pvr/lib/schedules.cpp
@@ -35,6 +35,7 @@ class ScheduleCandidate {
 		datetime StartTime;
 		datetime StopTime;
 		int Priority;
+		int ScheduleId;
 };
 typedef boost::shared_ptr<ScheduleCandidate> ScheduleCandidatePtr;
 typedef std::vector<ScheduleCandidatePtr> ScheduleCandidates;
@@ -48,16 +49,18 @@ enum RecordStatuses {
 class Record {
 	public:
 		Record() { };
-		Record(int s, int e, RecordStatuses rs) :
+		Record(int s, int e, RecordStatuses rs, int sc) :
 			ServiceId(s),
 			EventId(e),
-			RecordStatus(rs)
+			RecordStatus(rs),
+			ScheduleId(sc)
 		{
 		}
 
 		int ServiceId;
 		int EventId;
 		RecordStatuses RecordStatus;
+		int ScheduleId;
 };
 typedef boost::shared_ptr<Record> RecordPtr;
 typedef std::vector<RecordPtr> Records;
@@ -73,6 +76,7 @@ CreateColumns<ScheduleCandidatePtr>(const ColumnCreator & cc)
 	cc("starttime", false);
 	cc("stoptime", false);
 	cc("priority", false);
+	cc("scheduleid", false);
 }
 
 template<>
@@ -86,6 +90,7 @@ UnbindColumns(RowState & rs, ScheduleCandidatePtr const & s)
 	rs.fields[4] >> s->StartTime;
 	rs.fields[5] >> s->StopTime;
 	rs.fields[6] >> s->Priority;
+	rs.fields[7] >> s->ScheduleId;
 }
 
 template<>
@@ -125,6 +130,7 @@ CreateColumns<RecordPtr>(const ColumnCreator & cc)
 	cc("serviceid", true);
 	cc("eventid", true);
 	cc("recordstatus", false);
+	cc("scheduleid", false);
 }
 
 template<>
@@ -134,13 +140,15 @@ BindColumns(RowState & rs, RecordPtr const & s)
 	rs.fields[0] << s->ServiceId;
 	rs.fields[1] << s->EventId;
 	rs.fields[2] << (int)s->RecordStatus;
+	rs.fields[3] << s->ScheduleId;
 }
 
-Showing::Showing(unsigned int s, unsigned int e, unsigned int t, datetime start, datetime stop, int p, const Episode * ep) :
+Showing::Showing(unsigned int s, unsigned int e, unsigned int t, unsigned int sc, datetime start, datetime stop, int p, const Episode * ep) :
 	episode(ep),
 	serviceId(s),
 	eventId(e),
 	priority(p),
+	scheduleId(sc),
 	transportStreamId(t),
 	startTime(start),
 	stopTime(stop),
@@ -310,7 +318,7 @@ Schedules::DoReschedule(const Ice::Current & ice)
 			cur = new Episode(c->What);
 			scheduleList.push_back(cur);
 		}
-		ShowingPtr s = new Showing(c->ServiceId, c->EventId, c->TransportStreamId,
+		ShowingPtr s = new Showing(c->ServiceId, c->EventId, c->TransportStreamId, c->ScheduleId,
 				c->StartTime, c->StopTime, c->Priority, cur.get());
 		minPriority = std::min(minPriority, s->priority);
 		cur->showings.push_back(s);
@@ -383,7 +391,7 @@ Schedules::DoReschedule(const Ice::Current & ice)
 					records.push_back(RecordPtr(new Record(i->serviceId, i->eventId,
 									found ?
 									selected.find(i) != selected.end() ? Record_WillRecordThisShowing : Record_WillRecordOtherShowing :
-									Record_CannotRecordAnyShowing)));
+									Record_CannotRecordAnyShowing, i->scheduleId)));
 				}
 			}
 		}
diff --git a/p2pvr/lib/schedules.h b/p2pvr/lib/schedules.h
index caee1f7..ff725e7 100644
--- a/p2pvr/lib/schedules.h
+++ b/p2pvr/lib/schedules.h
@@ -11,12 +11,13 @@ class Episode;
 
 class Showing : public IntrusivePtrBase {
 	public:
-		Showing(unsigned int s, unsigned int e, unsigned int t, datetime start, datetime stop, int p, const Episode * ep);
+		Showing(unsigned int s, unsigned int e, unsigned int t, unsigned int sc, datetime start, datetime stop, int p, const Episode * ep);
 		// Record what?
 		const Episode * episode;
 		const unsigned int serviceId;
 		const unsigned int eventId;
 		int priority;
+		const unsigned int scheduleId;
 		// Requires
 		const unsigned int transportStreamId;
 		const datetime startTime;
diff --git a/p2pvr/lib/sql/Schedules_GetCandidates.sql b/p2pvr/lib/sql/Schedules_GetCandidates.sql
index aa6ec0f..f7ad997 100644
--- a/p2pvr/lib/sql/Schedules_GetCandidates.sql
+++ b/p2pvr/lib/sql/Schedules_GetCandidates.sql
@@ -1,20 +1,25 @@
-select (e.title, e.subtitle, e.description)::text what, e.serviceid, e.eventid, sv.transportstreamid,
-			 e.starttime - max(s.early), e.stoptime - interval '1 second' + max(s.late),
-			 max(s.priority)
-from services sv, events e, schedules s
-where (s.serviceid is null or s.serviceid = e.serviceid)
-and (s.title is null or lower(s.title) = lower(e.title))
-and (s.eventid is null or s.eventid = e.eventid)
-and (s.search is null or event_tsvector(e) @@ plainto_tsquery(s.search))
-and sv.serviceid = e.serviceid
-and e.starttime > now()
-and not exists (
-	select 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
+select what, serviceid, eventid, transportstreamid,
+	starttime - early starttime, stoptime + late stoptime,
+	priority, scheduleid
+from (
+	select (e.title, e.subtitle, e.description)::text what, e.serviceid, e.eventid, sv.transportstreamid,
+		e.starttime, e.stoptime - interval '1 second' stoptime,
+		s.early, s.late, s.scheduleid, s.priority,
+		rank() over(partition by e.serviceid, e.eventid, sv.serviceid order by s.priority desc, s.scheduleid) schedulerank
+	from services sv, events e, schedules s
+	where (s.serviceid is null or s.serviceid = e.serviceid)
+	and (s.title is null or lower(s.title) = lower(e.title))
+	and (s.eventid is null or s.eventid = e.eventid)
+	and (s.search is null or event_tsvector(e) @@ plainto_tsquery(s.search))
+	and sv.serviceid = e.serviceid
+	and e.starttime > now()
+	and not exists (
+		select 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)) e
+where e.schedulerank = 1
+order by e.priority desc, e.what, e.transportstreamid, e.starttime