Tidy SQL statements into sub-dirs

14 files changed, 258 insertions, 0 deletions

diff --git a/p2pvr/daemon/sql/si/allDeliveries.sql b/p2pvr/daemon/sql/si/allDeliveries.sql
new file mode 100644
index 0000000..2efa70a
--- /dev/null
+++ b/p2pvr/daemon/sql/si/allDeliveries.sql
@@ -0,0 +1,3 @@
+SELECT *
+FROM allDeliveries
+ORDER BY transportStreamId
diff --git a/p2pvr/daemon/sql/si/allNetworks.sql b/p2pvr/daemon/sql/si/allNetworks.sql
new file mode 100644
index 0000000..37d7e64
--- /dev/null
+++ b/p2pvr/daemon/sql/si/allNetworks.sql
@@ -0,0 +1,3 @@
+SELECT *
+FROM networks
+ORDER BY networkId
diff --git a/p2pvr/daemon/sql/si/deliveryForService.sql b/p2pvr/daemon/sql/si/deliveryForService.sql
new file mode 100644
index 0000000..cf4115f
--- /dev/null
+++ b/p2pvr/daemon/sql/si/deliveryForService.sql
@@ -0,0 +1,4 @@
+SELECT d.*
+FROM allDeliveries d, services s
+WHERE d.transportStreamId = s.transportStreamId
+AND s.serviceId = ?
diff --git a/p2pvr/daemon/sql/si/deliveryForTransport.sql b/p2pvr/daemon/sql/si/deliveryForTransport.sql
new file mode 100644
index 0000000..1805d7a
--- /dev/null
+++ b/p2pvr/daemon/sql/si/deliveryForTransport.sql
@@ -0,0 +1,3 @@
+SELECT *
+FROM allDeliveries
+WHERE transportStreamId = ?
diff --git a/p2pvr/daemon/sql/si/eventById.sql b/p2pvr/daemon/sql/si/eventById.sql
new file mode 100644
index 0000000..b751ad6
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventById.sql
@@ -0,0 +1,32 @@
+select
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e
+where serviceid = ?
+and eventid = ?
+and current
+
diff --git a/p2pvr/daemon/sql/si/eventByUid.sql b/p2pvr/daemon/sql/si/eventByUid.sql
new file mode 100644
index 0000000..1d559ec
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventByUid.sql
@@ -0,0 +1,30 @@
+select
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e
+where eventuid = ?
+
diff --git a/p2pvr/daemon/sql/si/eventSearch.sql b/p2pvr/daemon/sql/si/eventSearch.sql
new file mode 100644
index 0000000..ffbe93f
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventSearch.sql
@@ -0,0 +1,37 @@
+select 
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e
+where tsrange(?, ?, '[)') && tsrange(e.starttime, e.stoptime)
+and (e.serviceId = ? or ?::int is null)
+and (event_tsvector(e) @@ plainto_tsquery(?) or ?::text is null)
+and current
+order by
+	max(ts_rank(event_tsvector(e), plainto_tsquery(?))) over(partition by title) desc,
+	max(ts_rank(event_tsvector(e), plainto_tsquery(?))) over(partition by title, subtitle, description) desc,
+	e.title, e.subtitle, e.description, e.starttime, e.serviceId, e.eventId
+
diff --git a/p2pvr/daemon/sql/si/eventsInRange.sql b/p2pvr/daemon/sql/si/eventsInRange.sql
new file mode 100644
index 0000000..1d0eb17
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventsInRange.sql
@@ -0,0 +1,32 @@
+select
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e
+where tsrange(?, ?, '[)') && tsrange(e.starttime, e.stoptime)
+and current
+order by e.serviceid, e.starttime
+
diff --git a/p2pvr/daemon/sql/si/eventsInSchedule.sql b/p2pvr/daemon/sql/si/eventsInSchedule.sql
new file mode 100644
index 0000000..72ae26e
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventsInSchedule.sql
@@ -0,0 +1,33 @@
+select
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e, record r
+where e.eventuid = r.eventuid
+and r.scheduleid = ?
+and e.current
+order by e.serviceid, e.starttime
+
diff --git a/p2pvr/daemon/sql/si/eventsInSchedules.sql b/p2pvr/daemon/sql/si/eventsInSchedules.sql
new file mode 100644
index 0000000..880b28a
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventsInSchedules.sql
@@ -0,0 +1,32 @@
+select
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e, record r
+where e.eventuid = r.eventuid
+and current
+order by e.serviceid, e.starttime
+
diff --git a/p2pvr/daemon/sql/si/eventsOnNow.sql b/p2pvr/daemon/sql/si/eventsOnNow.sql
new file mode 100644
index 0000000..391e8de
--- /dev/null
+++ b/p2pvr/daemon/sql/si/eventsOnNow.sql
@@ -0,0 +1,31 @@
+select
+	e.serviceid,
+	e.eventid,
+	e.title,
+	e.titlelang,
+	e.subtitle,
+	e.description,
+	e.descriptionlang,
+	e.videoaspect,
+	e.videoframerate,
+	e.videohd,
+	e.audiochannels,
+	e.audiolanguage,
+	e.subtitlelanguage,
+	e.category,
+	e.subcategory,
+	e.usercategory,
+	e.dvbrating,
+	e.starttime,
+	e.stoptime,
+	e.episode,
+	e.episodes,
+	e.year,
+	e.flags,
+	e.season,
+	e.eventuid,
+	e.current
+from events e
+where now()::timestamp without time zone <@ tsrange(e.starttime, e.stoptime)
+and current
+order by e.serviceid
diff --git a/p2pvr/daemon/sql/si/serviceNextUsed.sql b/p2pvr/daemon/sql/si/serviceNextUsed.sql
new file mode 100644
index 0000000..e5fbbf1
--- /dev/null
+++ b/p2pvr/daemon/sql/si/serviceNextUsed.sql
@@ -0,0 +1,12 @@
+SELECT d.*
+FROM allDeliveries d
+	LEFT OUTER JOIN services s
+		ON d.transportstreamid = s.transportstreamid
+	LEFT OUTER JOIN events e
+		ON s.serviceid = e.serviceid
+		AND e.starttime > NOW()
+	LEFT OUTER JOIN record r
+		ON r.eventuid = e.eventuid
+		AND r.recordstatus = 'WillRecordThisShowing'
+ORDER BY e.starttime, s.serviceid
+LIMIT 1
diff --git a/p2pvr/daemon/sql/si/servicesSelectAll.sql b/p2pvr/daemon/sql/si/servicesSelectAll.sql
new file mode 100644
index 0000000..513a076
--- /dev/null
+++ b/p2pvr/daemon/sql/si/servicesSelectAll.sql
@@ -0,0 +1,3 @@
+SELECT serviceId, transportStreamId, name, providerName, defaultAuthority, runningStatus, eitSchedule, eitPresentFollowing, freeCaMode
+FROM services
+ORDER BY serviceId
diff --git a/p2pvr/daemon/sql/si/servicesSelectById.sql b/p2pvr/daemon/sql/si/servicesSelectById.sql
new file mode 100644
index 0000000..f0bda72
--- /dev/null
+++ b/p2pvr/daemon/sql/si/servicesSelectById.sql
@@ -0,0 +1,3 @@
+SELECT serviceId, transportStreamId, name, providerName, defaultAuthority, runningStatus, eitSchedule, eitPresentFollowing, freeCaMode
+FROM services
+WHERE serviceId = ?