summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--p2pvr/datasources/schema.sql11
-rw-r--r--p2pvr/lib/schedules.cpp18
-rw-r--r--p2pvr/lib/schedules.h3
-rw-r--r--p2pvr/lib/sql/Schedules_GetCandidates.sql43
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-23 11:25:30 +0000