From ed2885ac4d872a8ff7bd2ede57adfc002d9d78ba Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 30 Jul 2017 15:57:53 +0100 Subject: Tidy SQL statements into sub-dirs --- p2pvr/daemon/maintenance/events.cpp | 4 +- p2pvr/daemon/recordings.cpp | 9 ++-- p2pvr/daemon/schedules.cpp | 20 ++++----- p2pvr/daemon/si.cpp | 56 ++++++++++++------------ p2pvr/daemon/sql/Maint_pruneEvents.sql | 4 -- p2pvr/daemon/sql/Recordings_delete.sql | 2 - p2pvr/daemon/sql/Recordings_getAll.sql | 3 -- p2pvr/daemon/sql/Recordings_getStorage.sql | 4 -- p2pvr/daemon/sql/SI_allDeliveries.sql | 3 -- p2pvr/daemon/sql/SI_allNetworks.sql | 3 -- p2pvr/daemon/sql/SI_deliveryForService.sql | 4 -- p2pvr/daemon/sql/SI_deliveryForTransport.sql | 3 -- p2pvr/daemon/sql/SI_eventById.sql | 32 -------------- p2pvr/daemon/sql/SI_eventByUid.sql | 30 ------------- p2pvr/daemon/sql/SI_eventSearch.sql | 37 ---------------- p2pvr/daemon/sql/SI_eventsInRange.sql | 32 -------------- p2pvr/daemon/sql/SI_eventsInSchedule.sql | 33 -------------- p2pvr/daemon/sql/SI_eventsInSchedules.sql | 32 -------------- p2pvr/daemon/sql/SI_eventsOnNow.sql | 31 ------------- p2pvr/daemon/sql/SI_serviceNextUsed.sql | 12 ----- p2pvr/daemon/sql/SI_servicesSelectAll.sql | 3 -- p2pvr/daemon/sql/SI_servicesSelectById.sql | 3 -- p2pvr/daemon/sql/Schedules_delete.sql | 1 - p2pvr/daemon/sql/Schedules_getCandidates.sql | 26 ----------- p2pvr/daemon/sql/Schedules_pendingRecord.sql | 13 ------ p2pvr/daemon/sql/Schedules_scheduledToRecord.sql | 6 --- p2pvr/daemon/sql/Schedules_selectAll.sql | 3 -- p2pvr/daemon/sql/Schedules_selectById.sql | 4 -- p2pvr/daemon/sql/maintenance/pruneEvents.sql | 4 ++ p2pvr/daemon/sql/recordings/deleteById.sql | 2 + p2pvr/daemon/sql/recordings/getAll.sql | 3 ++ p2pvr/daemon/sql/schedules/deleteById.sql | 1 + p2pvr/daemon/sql/schedules/getCandidates.sql | 26 +++++++++++ p2pvr/daemon/sql/schedules/pendingRecord.sql | 13 ++++++ p2pvr/daemon/sql/schedules/scheduledToRecord.sql | 6 +++ p2pvr/daemon/sql/schedules/selectAll.sql | 3 ++ p2pvr/daemon/sql/schedules/selectById.sql | 4 ++ p2pvr/daemon/sql/si/allDeliveries.sql | 3 ++ p2pvr/daemon/sql/si/allNetworks.sql | 3 ++ p2pvr/daemon/sql/si/deliveryForService.sql | 4 ++ p2pvr/daemon/sql/si/deliveryForTransport.sql | 3 ++ p2pvr/daemon/sql/si/eventById.sql | 32 ++++++++++++++ p2pvr/daemon/sql/si/eventByUid.sql | 30 +++++++++++++ p2pvr/daemon/sql/si/eventSearch.sql | 37 ++++++++++++++++ p2pvr/daemon/sql/si/eventsInRange.sql | 32 ++++++++++++++ p2pvr/daemon/sql/si/eventsInSchedule.sql | 33 ++++++++++++++ p2pvr/daemon/sql/si/eventsInSchedules.sql | 32 ++++++++++++++ p2pvr/daemon/sql/si/eventsOnNow.sql | 31 +++++++++++++ p2pvr/daemon/sql/si/serviceNextUsed.sql | 12 +++++ p2pvr/daemon/sql/si/servicesSelectAll.sql | 3 ++ p2pvr/daemon/sql/si/servicesSelectById.sql | 3 ++ 51 files changed, 364 insertions(+), 369 deletions(-) delete mode 100644 p2pvr/daemon/sql/Maint_pruneEvents.sql delete mode 100644 p2pvr/daemon/sql/Recordings_delete.sql delete mode 100644 p2pvr/daemon/sql/Recordings_getAll.sql delete mode 100644 p2pvr/daemon/sql/Recordings_getStorage.sql delete mode 100644 p2pvr/daemon/sql/SI_allDeliveries.sql delete mode 100644 p2pvr/daemon/sql/SI_allNetworks.sql delete mode 100644 p2pvr/daemon/sql/SI_deliveryForService.sql delete mode 100644 p2pvr/daemon/sql/SI_deliveryForTransport.sql delete mode 100644 p2pvr/daemon/sql/SI_eventById.sql delete mode 100644 p2pvr/daemon/sql/SI_eventByUid.sql delete mode 100644 p2pvr/daemon/sql/SI_eventSearch.sql delete mode 100644 p2pvr/daemon/sql/SI_eventsInRange.sql delete mode 100644 p2pvr/daemon/sql/SI_eventsInSchedule.sql delete mode 100644 p2pvr/daemon/sql/SI_eventsInSchedules.sql delete mode 100644 p2pvr/daemon/sql/SI_eventsOnNow.sql delete mode 100644 p2pvr/daemon/sql/SI_serviceNextUsed.sql delete mode 100644 p2pvr/daemon/sql/SI_servicesSelectAll.sql delete mode 100644 p2pvr/daemon/sql/SI_servicesSelectById.sql delete mode 100644 p2pvr/daemon/sql/Schedules_delete.sql delete mode 100644 p2pvr/daemon/sql/Schedules_getCandidates.sql delete mode 100644 p2pvr/daemon/sql/Schedules_pendingRecord.sql delete mode 100644 p2pvr/daemon/sql/Schedules_scheduledToRecord.sql delete mode 100644 p2pvr/daemon/sql/Schedules_selectAll.sql delete mode 100644 p2pvr/daemon/sql/Schedules_selectById.sql create mode 100644 p2pvr/daemon/sql/maintenance/pruneEvents.sql create mode 100644 p2pvr/daemon/sql/recordings/deleteById.sql create mode 100644 p2pvr/daemon/sql/recordings/getAll.sql create mode 100644 p2pvr/daemon/sql/schedules/deleteById.sql create mode 100644 p2pvr/daemon/sql/schedules/getCandidates.sql create mode 100644 p2pvr/daemon/sql/schedules/pendingRecord.sql create mode 100644 p2pvr/daemon/sql/schedules/scheduledToRecord.sql create mode 100644 p2pvr/daemon/sql/schedules/selectAll.sql create mode 100644 p2pvr/daemon/sql/schedules/selectById.sql create mode 100644 p2pvr/daemon/sql/si/allDeliveries.sql create mode 100644 p2pvr/daemon/sql/si/allNetworks.sql create mode 100644 p2pvr/daemon/sql/si/deliveryForService.sql create mode 100644 p2pvr/daemon/sql/si/deliveryForTransport.sql create mode 100644 p2pvr/daemon/sql/si/eventById.sql create mode 100644 p2pvr/daemon/sql/si/eventByUid.sql create mode 100644 p2pvr/daemon/sql/si/eventSearch.sql create mode 100644 p2pvr/daemon/sql/si/eventsInRange.sql create mode 100644 p2pvr/daemon/sql/si/eventsInSchedule.sql create mode 100644 p2pvr/daemon/sql/si/eventsInSchedules.sql create mode 100644 p2pvr/daemon/sql/si/eventsOnNow.sql create mode 100644 p2pvr/daemon/sql/si/serviceNextUsed.sql create mode 100644 p2pvr/daemon/sql/si/servicesSelectAll.sql create mode 100644 p2pvr/daemon/sql/si/servicesSelectById.sql diff --git a/p2pvr/daemon/maintenance/events.cpp b/p2pvr/daemon/maintenance/events.cpp index b4b3c92..0f4cf4e 100644 --- a/p2pvr/daemon/maintenance/events.cpp +++ b/p2pvr/daemon/maintenance/events.cpp @@ -7,7 +7,7 @@ #include #include #include -#include "sql/Maint_pruneEvents.sql.h" +#include "sql/maintenance/pruneEvents.sql.h" class SiEventsStream : public Slicer::Stream { private: @@ -84,7 +84,7 @@ Maintenance::UpdateEvents(const Ice::Current & ice) Slicer::SerializeAny(stream, dbc.get(), tp); - P2PVR::sql::Maint_pruneEvents.modify(dbc.get())->execute(); + P2PVR::sql::maintenance::pruneEvents.modify(dbc.get())->execute(); logger->messagebf(LOG::INFO, "%s: Updated events", __PRETTY_FUNCTION__); auto sch = P2PVR::SchedulesPrx::checkedCast(ice.adapter->createProxy(ic->stringToIdentity("Schedules"))); diff --git a/p2pvr/daemon/recordings.cpp b/p2pvr/daemon/recordings.cpp index 7c5125b..5570d4c 100644 --- a/p2pvr/daemon/recordings.cpp +++ b/p2pvr/daemon/recordings.cpp @@ -4,9 +4,8 @@ #include #include #include -#include -#include -#include +#include +#include IceTray::Logging::LoggerPtr Recordings::logger(LOGMANAGER()->getLogger()); @@ -31,7 +30,7 @@ Recordings::DeleteRecording(int id, const Ice::Current &) { auto dbc = db->get(); logger->messagebf(LOG::INFO, "%s: Deleting recording Id: %d", __PRETTY_FUNCTION__, id); - auto del = P2PVR::sql::Recordings_delete.modify(dbc.get()); + auto del = P2PVR::sql::recordings::deleteById.modify(dbc.get()); del->bindParamI(0, id); del->execute(); } @@ -40,6 +39,6 @@ P2PVR::RecordingList Recordings::GetRecordings(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::Recordings_getAll); + return fetch(P2PVR::sql::recordings::getAll); } diff --git a/p2pvr/daemon/schedules.cpp b/p2pvr/daemon/schedules.cpp index f343e91..389a979 100644 --- a/p2pvr/daemon/schedules.cpp +++ b/p2pvr/daemon/schedules.cpp @@ -11,11 +11,11 @@ #include #include -#include "sql/Schedules_getCandidates.sql.h" -#include "sql/Schedules_delete.sql.h" -#include "sql/Schedules_selectAll.sql.h" -#include "sql/Schedules_selectById.sql.h" -#include "sql/Schedules_scheduledToRecord.sql.h" +#include "sql/schedules/getCandidates.sql.h" +#include "sql/schedules/deleteById.sql.h" +#include "sql/schedules/selectAll.sql.h" +#include "sql/schedules/selectById.sql.h" +#include "sql/schedules/scheduledToRecord.sql.h" namespace po = boost::program_options; @@ -197,7 +197,7 @@ Schedules::DoReschedule(const Ice::Current & ice) unsigned int tunerCount = devs->TunerCount(); // Load list from database - auto episodes = fetch(P2PVR::sql::Schedules_getCandidates); + auto episodes = fetch(P2PVR::sql::schedules::getCandidates); Episodes scheduleList; Showings allShowings; @@ -301,7 +301,7 @@ void Schedules::DeleteSchedule(int id, const Ice::Current & ice) { auto dbc = db->get(); - auto del = P2PVR::sql::Schedules_delete.modify(dbc.get()); + auto del = P2PVR::sql::schedules::deleteById.modify(dbc.get()); del->bindParamI(0, id); del->execute(); DoReschedule(ice); @@ -311,14 +311,14 @@ P2PVR::ScheduleList Schedules::GetSchedules(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::Schedules_selectAll); + return fetch(P2PVR::sql::schedules::selectAll); } P2PVR::SchedulePtr Schedules::GetSchedule(int id, const Ice::Current &) { logger->messagebf(LOG::DEBUG, "%s(%d)", __PRETTY_FUNCTION__, id); - auto schedules = fetch(P2PVR::sql::Schedules_selectById, id); + auto schedules = fetch(P2PVR::sql::schedules::selectById, id); if (schedules.empty()) throw P2PVR::NotFound(); return schedules.front(); } @@ -327,7 +327,7 @@ P2PVR::ScheduledToRecordList Schedules::GetScheduledToRecord(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::Schedules_scheduledToRecord); + return fetch(P2PVR::sql::schedules::scheduledToRecord); } Ice::Int diff --git a/p2pvr/daemon/si.cpp b/p2pvr/daemon/si.cpp index 1186547..bee7fa7 100644 --- a/p2pvr/daemon/si.cpp +++ b/p2pvr/daemon/si.cpp @@ -6,20 +6,20 @@ #include #include -#include "sql/SI_allNetworks.sql.h" -#include "sql/SI_allDeliveries.sql.h" -#include "sql/SI_deliveryForTransport.sql.h" -#include "sql/SI_deliveryForService.sql.h" -#include "sql/SI_serviceNextUsed.sql.h" -#include "sql/SI_servicesSelectAll.sql.h" -#include "sql/SI_servicesSelectById.sql.h" -#include "sql/SI_eventByUid.sql.h" -#include "sql/SI_eventById.sql.h" -#include "sql/SI_eventsOnNow.sql.h" -#include "sql/SI_eventsInSchedule.sql.h" -#include "sql/SI_eventsInSchedules.sql.h" -#include "sql/SI_eventsInRange.sql.h" -#include "sql/SI_eventSearch.sql.h" +#include "sql/si/allNetworks.sql.h" +#include "sql/si/allDeliveries.sql.h" +#include "sql/si/deliveryForTransport.sql.h" +#include "sql/si/deliveryForService.sql.h" +#include "sql/si/serviceNextUsed.sql.h" +#include "sql/si/servicesSelectAll.sql.h" +#include "sql/si/servicesSelectById.sql.h" +#include "sql/si/eventByUid.sql.h" +#include "sql/si/eventById.sql.h" +#include "sql/si/eventsOnNow.sql.h" +#include "sql/si/eventsInSchedule.sql.h" +#include "sql/si/eventsInSchedules.sql.h" +#include "sql/si/eventsInRange.sql.h" +#include "sql/si/eventSearch.sql.h" IceTray::Logging::LoggerPtr SI::logger(LOGMANAGER()->getLogger()); @@ -32,14 +32,14 @@ DVBSI::Networks SI::GetNetworks(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_allNetworks); + return fetch(P2PVR::sql::si::allNetworks); } P2PVR::Deliveries SI::GetAllDeliveries(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - auto rtn = fetch("delivery_type", P2PVR::sql::SI_allDeliveries); + auto rtn = fetch("delivery_type", P2PVR::sql::si::allDeliveries); logger->messagebf(LOG::DEBUG, "%s: Found %d delivery methods", __PRETTY_FUNCTION__, rtn.size()); return rtn; } @@ -49,7 +49,7 @@ SI::GetDeliveryForTransport(int id, const Ice::Current&) { try { logger->messagef(LOG::DEBUG, "%s(%d)", __PRETTY_FUNCTION__, id); - return fetch("delivery_type", P2PVR::sql::SI_deliveryForTransport, id); + return fetch("delivery_type", P2PVR::sql::si::deliveryForTransport, id); } catch (const Slicer::NoRowsReturned &) { throw P2PVR::NotFound(); @@ -61,7 +61,7 @@ SI::GetDeliveryForSi(const Ice::Current&) { try { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch("delivery_type", P2PVR::sql::SI_serviceNextUsed); + return fetch("delivery_type", P2PVR::sql::si::serviceNextUsed); } catch (const Slicer::NoRowsReturned &) { return NULL; @@ -73,7 +73,7 @@ SI::GetDeliveryForService(int id, const Ice::Current&) { try { logger->messagef(LOG::DEBUG, "%s(%d)", __PRETTY_FUNCTION__, id); - return fetch("delivery_type", P2PVR::sql::SI_deliveryForService, id); + return fetch("delivery_type", P2PVR::sql::si::deliveryForService, id); } catch (const Slicer::NoRowsReturned &) { throw P2PVR::NotFound(); @@ -84,14 +84,14 @@ DVBSI::ServiceList SI::GetServices(const Ice::Current&) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_servicesSelectAll); + return fetch(P2PVR::sql::si::servicesSelectAll); } DVBSI::ServicePtr SI::GetService(int id, const Ice::Current&) { logger->messagef(LOG::DEBUG, "%s(%d)", __PRETTY_FUNCTION__, id); - auto rtn = fetch(P2PVR::sql::SI_servicesSelectById, id); + auto rtn = fetch(P2PVR::sql::si::servicesSelectById, id); if (rtn.empty()) throw P2PVR::NotFound(); return rtn.front(); } @@ -102,7 +102,7 @@ SI::GetEvents(const P2PVR::IntSequence & eventUids, const Ice::Current &) logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); P2PVR::Events rtn; for (const auto & uid : eventUids) { - auto list = fetch(P2PVR::sql::SI_eventByUid, uid); + auto list = fetch(P2PVR::sql::si::eventByUid, uid); std::copy(list.begin(), list.end(), std::back_inserter(rtn)); } if (rtn.size() != eventUids.size()) throw P2PVR::NotFound(); @@ -113,7 +113,7 @@ P2PVR::EventPtr SI::GetEvent(int serviceId, int eventId, const Ice::Current &) { logger->messagef(LOG::DEBUG, "%s(s=%d, e=%d)", __PRETTY_FUNCTION__, serviceId, eventId); - auto rtn = fetch(P2PVR::sql::SI_eventById, serviceId, eventId); + auto rtn = fetch(P2PVR::sql::si::eventById, serviceId, eventId); if (rtn.empty()) throw P2PVR::NotFound(); return rtn.front(); } @@ -122,7 +122,7 @@ P2PVR::Events SI::EventsOnNow(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_eventsOnNow); + return fetch(P2PVR::sql::si::eventsOnNow); } namespace IceTray { @@ -142,7 +142,7 @@ P2PVR::Events SI::EventsInRange(const Common::DateTime & from, const Common::DateTime & to, const Ice::Current &) { logger->messagebf(LOG::DEBUG, "%s([%s]-[%s])", from, to, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_eventsInRange, from, to); + return fetch(P2PVR::sql::si::eventsInRange, from, to); } P2PVR::Events @@ -150,20 +150,20 @@ SI::EventSearch(const IceUtil::Optional & keywords, const IceUtil:: { logger->messagebf(LOG::DEBUG, "%s(keywords=%s,serviceId=%s,from=%s,to=%s)", keywords, serviceId, from, to, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_eventSearch, from, to, serviceId, serviceId, keywords, keywords, keywords, keywords); + return fetch(P2PVR::sql::si::eventSearch, from, to, serviceId, serviceId, keywords, keywords, keywords, keywords); } P2PVR::Events SI::EventsInSchedules(const Ice::Current &) { logger->message(LOG::DEBUG, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_eventsInSchedules); + return fetch(P2PVR::sql::si::eventsInSchedules); } P2PVR::Events SI::EventsInSchedule(int scheduleId, const Ice::Current &) { logger->messagebf(LOG::DEBUG, "%s(%d)", scheduleId, __PRETTY_FUNCTION__); - return fetch(P2PVR::sql::SI_eventsInSchedule, scheduleId); + return fetch(P2PVR::sql::si::eventsInSchedule, scheduleId); } diff --git a/p2pvr/daemon/sql/Maint_pruneEvents.sql b/p2pvr/daemon/sql/Maint_pruneEvents.sql deleted file mode 100644 index b6f1d1b..0000000 --- a/p2pvr/daemon/sql/Maint_pruneEvents.sql +++ /dev/null @@ -1,4 +0,0 @@ -DELETE FROM events e -WHERE e.eventuid NOT IN (SELECT eventuid FROM recorded) -AND e.eventuid NOT IN (SELECT eventuid FROM recordings) -AND NOT current diff --git a/p2pvr/daemon/sql/Recordings_delete.sql b/p2pvr/daemon/sql/Recordings_delete.sql deleted file mode 100644 index 3395376..0000000 --- a/p2pvr/daemon/sql/Recordings_delete.sql +++ /dev/null @@ -1,2 +0,0 @@ -DELETE FROM recordings -WHERE recordingId = ? diff --git a/p2pvr/daemon/sql/Recordings_getAll.sql b/p2pvr/daemon/sql/Recordings_getAll.sql deleted file mode 100644 index 3c3ff51..0000000 --- a/p2pvr/daemon/sql/Recordings_getAll.sql +++ /dev/null @@ -1,3 +0,0 @@ -SELECT recordingId, storageAddress, guid, scheduleId, eventUid -FROM recordings r -ORDER BY recordingId diff --git a/p2pvr/daemon/sql/Recordings_getStorage.sql b/p2pvr/daemon/sql/Recordings_getStorage.sql deleted file mode 100644 index fdaf58d..0000000 --- a/p2pvr/daemon/sql/Recordings_getStorage.sql +++ /dev/null @@ -1,4 +0,0 @@ -SELECT storageAddress, guid -FROM recordings -WHERE recordingId = ? - diff --git a/p2pvr/daemon/sql/SI_allDeliveries.sql b/p2pvr/daemon/sql/SI_allDeliveries.sql deleted file mode 100644 index 2efa70a..0000000 --- a/p2pvr/daemon/sql/SI_allDeliveries.sql +++ /dev/null @@ -1,3 +0,0 @@ -SELECT * -FROM allDeliveries -ORDER BY transportStreamId diff --git a/p2pvr/daemon/sql/SI_allNetworks.sql b/p2pvr/daemon/sql/SI_allNetworks.sql deleted file mode 100644 index 37d7e64..0000000 --- a/p2pvr/daemon/sql/SI_allNetworks.sql +++ /dev/null @@ -1,3 +0,0 @@ -SELECT * -FROM networks -ORDER BY networkId diff --git a/p2pvr/daemon/sql/SI_deliveryForService.sql b/p2pvr/daemon/sql/SI_deliveryForService.sql deleted file mode 100644 index cf4115f..0000000 --- a/p2pvr/daemon/sql/SI_deliveryForService.sql +++ /dev/null @@ -1,4 +0,0 @@ -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 deleted file mode 100644 index 1805d7a..0000000 --- a/p2pvr/daemon/sql/SI_deliveryForTransport.sql +++ /dev/null @@ -1,3 +0,0 @@ -SELECT * -FROM allDeliveries -WHERE transportStreamId = ? diff --git a/p2pvr/daemon/sql/SI_eventById.sql b/p2pvr/daemon/sql/SI_eventById.sql deleted file mode 100644 index b751ad6..0000000 --- a/p2pvr/daemon/sql/SI_eventById.sql +++ /dev/null @@ -1,32 +0,0 @@ -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 deleted file mode 100644 index 1d559ec..0000000 --- a/p2pvr/daemon/sql/SI_eventByUid.sql +++ /dev/null @@ -1,30 +0,0 @@ -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 deleted file mode 100644 index ffbe93f..0000000 --- a/p2pvr/daemon/sql/SI_eventSearch.sql +++ /dev/null @@ -1,37 +0,0 @@ -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 deleted file mode 100644 index 1d0eb17..0000000 --- a/p2pvr/daemon/sql/SI_eventsInRange.sql +++ /dev/null @@ -1,32 +0,0 @@ -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 deleted file mode 100644 index 72ae26e..0000000 --- a/p2pvr/daemon/sql/SI_eventsInSchedule.sql +++ /dev/null @@ -1,33 +0,0 @@ -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 deleted file mode 100644 index 880b28a..0000000 --- a/p2pvr/daemon/sql/SI_eventsInSchedules.sql +++ /dev/null @@ -1,32 +0,0 @@ -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 deleted file mode 100644 index 391e8de..0000000 --- a/p2pvr/daemon/sql/SI_eventsOnNow.sql +++ /dev/null @@ -1,31 +0,0 @@ -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 deleted file mode 100644 index e5fbbf1..0000000 --- a/p2pvr/daemon/sql/SI_serviceNextUsed.sql +++ /dev/null @@ -1,12 +0,0 @@ -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 deleted file mode 100644 index 513a076..0000000 --- a/p2pvr/daemon/sql/SI_servicesSelectAll.sql +++ /dev/null @@ -1,3 +0,0 @@ -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 deleted file mode 100644 index f0bda72..0000000 --- a/p2pvr/daemon/sql/SI_servicesSelectById.sql +++ /dev/null @@ -1,3 +0,0 @@ -SELECT serviceId, transportStreamId, name, providerName, defaultAuthority, runningStatus, eitSchedule, eitPresentFollowing, freeCaMode -FROM services -WHERE serviceId = ? diff --git a/p2pvr/daemon/sql/Schedules_delete.sql b/p2pvr/daemon/sql/Schedules_delete.sql deleted file mode 100644 index e14edc3..0000000 --- a/p2pvr/daemon/sql/Schedules_delete.sql +++ /dev/null @@ -1 +0,0 @@ -DELETE FROM schedules WHERE scheduleId = ? diff --git a/p2pvr/daemon/sql/Schedules_getCandidates.sql b/p2pvr/daemon/sql/Schedules_getCandidates.sql deleted file mode 100644 index 0bb7bd8..0000000 --- a/p2pvr/daemon/sql/Schedules_getCandidates.sql +++ /dev/null @@ -1,26 +0,0 @@ -select what, eventuid, transportstreamid, - starttime - early starttime, stoptime + late stoptime, - priority, scheduleid -from ( - select (e.title, e.subtitle, e.description)::text what, e.eventuid, sv.transportstreamid, - e.starttime, e.stoptime - interval '1 second' stoptime, - s.early, s.late, s.scheduleid, s.priority, - rank() over(partition by e.eventuid, 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.eventuid is null or s.eventuid = e.eventuid) - 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, events re - where r.eventuid = re.eventuid - and lower(e.title) = lower(re.title) - and coalesce(lower(e.subtitle), '') = coalesce(lower(re.subtitle), '') - and ts_rank(to_tsvector(e.description), plainto_tsquery(re.description)) + - ts_rank(to_tsvector(re.description), plainto_tsquery(e.description)) > 1)) e -where e.schedulerank = 1 -order by e.priority desc, e.what, e.transportstreamid, e.starttime - diff --git a/p2pvr/daemon/sql/Schedules_pendingRecord.sql b/p2pvr/daemon/sql/Schedules_pendingRecord.sql deleted file mode 100644 index ba4d7d8..0000000 --- a/p2pvr/daemon/sql/Schedules_pendingRecord.sql +++ /dev/null @@ -1,13 +0,0 @@ -select * -from record r, events e, schedules s -where r.serviceid = e.serviceid -and r.eventid = e.eventid -and /*r.scheduleid*/ 161 = s.scheduleid -and r.recordstatus = 1 -and r.recordingstatus = 0 -and e.starttime - s.early - interval '3minutes' < now() -order by e.starttime - s.early - -; -select * -from schedules \ No newline at end of file diff --git a/p2pvr/daemon/sql/Schedules_scheduledToRecord.sql b/p2pvr/daemon/sql/Schedules_scheduledToRecord.sql deleted file mode 100644 index 3695bb5..0000000 --- a/p2pvr/daemon/sql/Schedules_scheduledToRecord.sql +++ /dev/null @@ -1,6 +0,0 @@ -SELECT e.eventuid, r.recordstatus, s.scheduleid -FROM record r, events e, schedules s -WHERE r.eventuid = e.eventuid -AND r.scheduleid = s.scheduleid -AND e.stoptime + s.late > NOW() -ORDER BY e.starttime, e.serviceid diff --git a/p2pvr/daemon/sql/Schedules_selectAll.sql b/p2pvr/daemon/sql/Schedules_selectAll.sql deleted file mode 100644 index 56778a9..0000000 --- a/p2pvr/daemon/sql/Schedules_selectAll.sql +++ /dev/null @@ -1,3 +0,0 @@ -SELECT scheduleid, serviceid, eventuid, title, search, priority, early, late, repeats -FROM schedules -ORDER BY scheduleId diff --git a/p2pvr/daemon/sql/Schedules_selectById.sql b/p2pvr/daemon/sql/Schedules_selectById.sql deleted file mode 100644 index 291398c..0000000 --- a/p2pvr/daemon/sql/Schedules_selectById.sql +++ /dev/null @@ -1,4 +0,0 @@ -SELECT scheduleid, serviceid, eventuid, title, search, priority, early, late, repeats -FROM schedules -WHERE scheduleid = ? - diff --git a/p2pvr/daemon/sql/maintenance/pruneEvents.sql b/p2pvr/daemon/sql/maintenance/pruneEvents.sql new file mode 100644 index 0000000..b6f1d1b --- /dev/null +++ b/p2pvr/daemon/sql/maintenance/pruneEvents.sql @@ -0,0 +1,4 @@ +DELETE FROM events e +WHERE e.eventuid NOT IN (SELECT eventuid FROM recorded) +AND e.eventuid NOT IN (SELECT eventuid FROM recordings) +AND NOT current diff --git a/p2pvr/daemon/sql/recordings/deleteById.sql b/p2pvr/daemon/sql/recordings/deleteById.sql new file mode 100644 index 0000000..3395376 --- /dev/null +++ b/p2pvr/daemon/sql/recordings/deleteById.sql @@ -0,0 +1,2 @@ +DELETE FROM recordings +WHERE recordingId = ? diff --git a/p2pvr/daemon/sql/recordings/getAll.sql b/p2pvr/daemon/sql/recordings/getAll.sql new file mode 100644 index 0000000..3c3ff51 --- /dev/null +++ b/p2pvr/daemon/sql/recordings/getAll.sql @@ -0,0 +1,3 @@ +SELECT recordingId, storageAddress, guid, scheduleId, eventUid +FROM recordings r +ORDER BY recordingId diff --git a/p2pvr/daemon/sql/schedules/deleteById.sql b/p2pvr/daemon/sql/schedules/deleteById.sql new file mode 100644 index 0000000..e14edc3 --- /dev/null +++ b/p2pvr/daemon/sql/schedules/deleteById.sql @@ -0,0 +1 @@ +DELETE FROM schedules WHERE scheduleId = ? diff --git a/p2pvr/daemon/sql/schedules/getCandidates.sql b/p2pvr/daemon/sql/schedules/getCandidates.sql new file mode 100644 index 0000000..0bb7bd8 --- /dev/null +++ b/p2pvr/daemon/sql/schedules/getCandidates.sql @@ -0,0 +1,26 @@ +select what, eventuid, transportstreamid, + starttime - early starttime, stoptime + late stoptime, + priority, scheduleid +from ( + select (e.title, e.subtitle, e.description)::text what, e.eventuid, sv.transportstreamid, + e.starttime, e.stoptime - interval '1 second' stoptime, + s.early, s.late, s.scheduleid, s.priority, + rank() over(partition by e.eventuid, 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.eventuid is null or s.eventuid = e.eventuid) + 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, events re + where r.eventuid = re.eventuid + and lower(e.title) = lower(re.title) + and coalesce(lower(e.subtitle), '') = coalesce(lower(re.subtitle), '') + and ts_rank(to_tsvector(e.description), plainto_tsquery(re.description)) + + ts_rank(to_tsvector(re.description), plainto_tsquery(e.description)) > 1)) e +where e.schedulerank = 1 +order by e.priority desc, e.what, e.transportstreamid, e.starttime + diff --git a/p2pvr/daemon/sql/schedules/pendingRecord.sql b/p2pvr/daemon/sql/schedules/pendingRecord.sql new file mode 100644 index 0000000..ba4d7d8 --- /dev/null +++ b/p2pvr/daemon/sql/schedules/pendingRecord.sql @@ -0,0 +1,13 @@ +select * +from record r, events e, schedules s +where r.serviceid = e.serviceid +and r.eventid = e.eventid +and /*r.scheduleid*/ 161 = s.scheduleid +and r.recordstatus = 1 +and r.recordingstatus = 0 +and e.starttime - s.early - interval '3minutes' < now() +order by e.starttime - s.early + +; +select * +from schedules \ No newline at end of file diff --git a/p2pvr/daemon/sql/schedules/scheduledToRecord.sql b/p2pvr/daemon/sql/schedules/scheduledToRecord.sql new file mode 100644 index 0000000..3695bb5 --- /dev/null +++ b/p2pvr/daemon/sql/schedules/scheduledToRecord.sql @@ -0,0 +1,6 @@ +SELECT e.eventuid, r.recordstatus, s.scheduleid +FROM record r, events e, schedules s +WHERE r.eventuid = e.eventuid +AND r.scheduleid = s.scheduleid +AND e.stoptime + s.late > NOW() +ORDER BY e.starttime, e.serviceid diff --git a/p2pvr/daemon/sql/schedules/selectAll.sql b/p2pvr/daemon/sql/schedules/selectAll.sql new file mode 100644 index 0000000..56778a9 --- /dev/null +++ b/p2pvr/daemon/sql/schedules/selectAll.sql @@ -0,0 +1,3 @@ +SELECT scheduleid, serviceid, eventuid, title, search, priority, early, late, repeats +FROM schedules +ORDER BY scheduleId diff --git a/p2pvr/daemon/sql/schedules/selectById.sql b/p2pvr/daemon/sql/schedules/selectById.sql new file mode 100644 index 0000000..291398c --- /dev/null +++ b/p2pvr/daemon/sql/schedules/selectById.sql @@ -0,0 +1,4 @@ +SELECT scheduleid, serviceid, eventuid, title, search, priority, early, late, repeats +FROM schedules +WHERE scheduleid = ? + 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 = ? -- cgit v1.2.3