From 61280570f8654d127b68a3d4a67e16457b7e4a78 Mon Sep 17 00:00:00 2001 From: randomdan Date: Tue, 7 Jan 2014 19:57:43 +0000 Subject: Select a remmeber the scheduleId for an upcoming recording --- p2pvr/datasources/schema.sql | 11 +++++++- p2pvr/lib/schedules.cpp | 18 +++++++++---- p2pvr/lib/schedules.h | 3 ++- p2pvr/lib/sql/Schedules_GetCandidates.sql | 43 +++++++++++++++++-------------- 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 ); @@ -558,6 +559,14 @@ 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_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 ScheduleCandidatePtr; typedef std::vector 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 RecordPtr; typedef std::vector Records; @@ -73,6 +76,7 @@ CreateColumns(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(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 -- cgit v1.2.3