diff options
Diffstat (limited to 'p2pvr/daemon/sql/Schedules_GetCandidates.sql')
| -rw-r--r-- | p2pvr/daemon/sql/Schedules_GetCandidates.sql | 25 |
1 files changed, 25 insertions, 0 deletions
diff --git a/p2pvr/daemon/sql/Schedules_GetCandidates.sql b/p2pvr/daemon/sql/Schedules_GetCandidates.sql new file mode 100644 index 0000000..8e8b15e --- /dev/null +++ b/p2pvr/daemon/sql/Schedules_GetCandidates.sql @@ -0,0 +1,25 @@ +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.stoptime > 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 + |