From 4a150338a468dccb31b250d68bacedd1954c983c Mon Sep 17 00:00:00 2001 From: randomdan Date: Thu, 12 Dec 2013 06:27:15 +0000 Subject: Tidy up and fix some DB core code Implement the rest of schdules interface properly --- p2pvr/datasources/schema.sql | 6 ++- p2pvr/ice/p2pvr.ice | 12 +++--- p2pvr/lib/dbClient.cpp | 24 +++--------- p2pvr/lib/dbClient.h | 63 ++++++++++++++++++++++++++++++- p2pvr/lib/p2Helpers.h | 2 +- p2pvr/lib/schedules.cpp | 63 +++++++++++++++++++++++++++---- p2pvr/lib/si.cpp | 14 +++---- p2pvr/lib/sql/GetScheduleConditates.sql | 20 ---------- p2pvr/lib/sql/Schedules_GetCandidates.sql | 20 ++++++++++ p2pvr/lib/sql/Schedules_delete.sql | 1 + p2pvr/lib/sql/Schedules_insert.sql | 2 + p2pvr/lib/sql/Schedules_insertNewId.sql | 1 + p2pvr/lib/sql/Schedules_selectAll.sql | 3 ++ p2pvr/lib/sql/Schedules_update.sql | 10 +++++ 14 files changed, 179 insertions(+), 62 deletions(-) delete mode 100644 p2pvr/lib/sql/GetScheduleConditates.sql create mode 100644 p2pvr/lib/sql/Schedules_GetCandidates.sql create mode 100644 p2pvr/lib/sql/Schedules_delete.sql create mode 100644 p2pvr/lib/sql/Schedules_insert.sql create mode 100644 p2pvr/lib/sql/Schedules_insertNewId.sql create mode 100644 p2pvr/lib/sql/Schedules_selectAll.sql create mode 100644 p2pvr/lib/sql/Schedules_update.sql diff --git a/p2pvr/datasources/schema.sql b/p2pvr/datasources/schema.sql index acd78ae..f65cf5f 100644 --- a/p2pvr/datasources/schema.sql +++ b/p2pvr/datasources/schema.sql @@ -171,7 +171,11 @@ CREATE TABLE schedules ( serviceid integer, eventid integer, title text, - search text + search text, + priority integer DEFAULT 0 NOT NULL, + early interval DEFAULT '00:00:00'::interval NOT NULL, + late interval DEFAULT '00:00:00'::interval NOT NULL, + repeats boolean NOT NULL ); diff --git a/p2pvr/ice/p2pvr.ice b/p2pvr/ice/p2pvr.ice index 0b0b596..b4aeb8a 100644 --- a/p2pvr/ice/p2pvr.ice +++ b/p2pvr/ice/p2pvr.ice @@ -188,11 +188,13 @@ module P2PVR { // Something that defines what we would like to record. class Schedule { int ScheduleId; - optional(1) string Title; - optional(2) string Subtitle; - optional(3) int ServiceId; - optional(4) Common::TimeOfDay Time; - optional(5) string Search; + optional(1) int ServiceId; + optional(2) int EventId; + optional(3) string Title; + optional(4) string Search; + int Priority; + string Early; + string Late; bool Repeats; }; sequence ScheduleList; diff --git a/p2pvr/lib/dbClient.cpp b/p2pvr/lib/dbClient.cpp index 5252e5b..a9b74e5 100644 --- a/p2pvr/lib/dbClient.cpp +++ b/p2pvr/lib/dbClient.cpp @@ -1,24 +1,5 @@ #include #include "dbClient.h" -#include -#include - -DatabaseClient::SelectPtr -DatabaseClient::Select(const std::string & sql) const -{ - auto db = dataSource("postgres"); - return SelectPtr(db->getReadonly().newSelectCommand(sql)); -} -DatabaseClient::SelectPtr -DatabaseClient::Select(const std::string & sql, const std::list & vs) const -{ - SelectPtr sel(Select(sql)); - unsigned int offset = 0; - BOOST_FOREACH(const auto & v, vs) { - boost::apply_visitor(SqlVariableBinder(sel.get(), offset++), v); - } - return sel; -} void DatabaseClient::onAllDatasources(const DataSourceCall & call) const @@ -35,3 +16,8 @@ DatabaseClient::TxHelper::TxHelper(const DatabaseClient * dbc) : { } +DatabaseClient::NoRowsFoundException::NoRowsFoundException() : + std::runtime_error("No rows found") +{ +} + diff --git a/p2pvr/lib/dbClient.h b/p2pvr/lib/dbClient.h index aef2bf2..97d19bd 100644 --- a/p2pvr/lib/dbClient.h +++ b/p2pvr/lib/dbClient.h @@ -5,11 +5,17 @@ #include #include #include +#include #include +#include +#include +#include +#include "p2Helpers.h" class DatabaseClient : public virtual CommonObjects { public: typedef boost::shared_ptr SelectPtr; + typedef boost::shared_ptr ModifyPtr; protected: class TxHelper { public: @@ -18,10 +24,63 @@ class DatabaseClient : public virtual CommonObjects { ScopeObject so; }; - SelectPtr Select(const std::string &) const; - SelectPtr Select(const std::string &, const std::list &) const; + template + ModifyPtr Modify(const std::string & sql, const Args & ... args) const + { + auto db = dataSource("postgres"); + auto cmd = ModifyPtr(db->getWritable().newModifyCommand(sql)); + Bind(cmd.get(), 0, args...); + return cmd; + } + + template + SelectPtr Select(const std::string & sql, const Args & ... args) const + { + auto db = dataSource("postgres"); + auto cmd = SelectPtr(db->getReadonly().newSelectCommand(sql)); + Bind(cmd.get(), 0, args...); + return cmd; + } + + class NoRowsFoundException : public std::runtime_error { + public: + NoRowsFoundException(); + }; + + template + Rtn SelectScalar(const std::string & sql, const Args & ... args) const + { + auto db = dataSource("postgres"); + auto cmd = SelectPtr(db->getReadonly().newSelectCommand(sql)); + Bind(cmd.get(), 0, args...); + while (cmd->fetch()) { + HandleAsVariableType h; + (*cmd)[0].apply(h); + Rtn r; + h.variable >> r; + return r; + } + throw NoRowsFoundException(); + } private: + static void Bind(DB::Command *, unsigned int) { } + + template + static void Bind(DB::Command * cmd, unsigned int offset, const Arg & arg) + { + VariableType v; + v << arg; + boost::apply_visitor(SqlVariableBinder(cmd, offset), v); + } + + template + static void Bind(DB::Command * cmd, unsigned int offset, const Arg & arg, const Args & ... args) + { + Bind(cmd, offset, arg); + Bind(cmd, offset + 1, args...); + } + friend class TxHelper; typedef boost::function DataSourceCall; void onAllDatasources(const DataSourceCall &) const; diff --git a/p2pvr/lib/p2Helpers.h b/p2pvr/lib/p2Helpers.h index ac88de1..e7732a0 100644 --- a/p2pvr/lib/p2Helpers.h +++ b/p2pvr/lib/p2Helpers.h @@ -16,7 +16,7 @@ template VariableType & operator>>(VariableType & vt, IceUtil::Optional & v) { - if (!vt.isNull()) { + if (vt.isNull()) { v = NULL; } else { diff --git a/p2pvr/lib/schedules.cpp b/p2pvr/lib/schedules.cpp index 79ad624..b3b6800 100644 --- a/p2pvr/lib/schedules.cpp +++ b/p2pvr/lib/schedules.cpp @@ -9,7 +9,12 @@ #include "resources.h" #include -ResourceString(GetScheduleConditates, lib_sql_GetScheduleConditates_sql); +ResourceString(Schedules_GetCandidates, lib_sql_Schedules_GetCandidates_sql); +ResourceString(Schedules_insert, lib_sql_Schedules_insert_sql); +ResourceString(Schedules_insertNewId, lib_sql_Schedules_insertNewId_sql); +ResourceString(Schedules_update, lib_sql_Schedules_update_sql); +ResourceString(Schedules_delete, lib_sql_Schedules_delete_sql); +ResourceString(Schedules_selectAll, lib_sql_Schedules_selectAll_sql); std::string Schedules::SchedulerAlgorithm; @@ -48,6 +53,36 @@ UnbindColumns(RowState & rs, ScheduleCandidatePtr const & s) rs.fields[6] >> boost::get<6>(*s); } +template<> +void +CreateColumns(const ColumnCreator & cc) +{ + cc("scheduleid", true); + cc("serviceid", false); + cc("eventid", false); + cc("title", false); + cc("search", false); + cc("priority", false); + cc("early", false); + cc("late", false); + cc("repeats", false); +} + +template<> +void +UnbindColumns(RowState & rs, P2PVR::SchedulePtr const & s) +{ + rs.fields[0] >> s->ScheduleId; + rs.fields[1] >> s->ServiceId; + rs.fields[2] >> s->EventId; + rs.fields[3] >> s->Title; + rs.fields[4] >> s->Search; + rs.fields[5] >> s->Priority; + rs.fields[6] >> s->Early; + rs.fields[7] >> s->Late; + rs.fields[8] >> s->Repeats; +} + Showing::Showing(unsigned int s, unsigned int e, unsigned int t, datetime start, datetime stop, int p, const Episode * ep) : episode(ep), serviceId(s), @@ -211,7 +246,7 @@ Schedules::DoReschedule(const Ice::Current & ice) // Load list from database ScheduleCandidates episodes; SqlContainerCreator cct(episodes); - cct.populate(Select(GetScheduleConditates)); + cct.populate(Select(Schedules_GetCandidates)); Episodes scheduleList; Showings allShowings; @@ -286,20 +321,34 @@ Schedules::DoReschedule(const Ice::Current & ice) } void -Schedules::DeleteSchedule(int , const Ice::Current &) +Schedules::DeleteSchedule(int id, const Ice::Current & ice) { + TxHelper tx(this); + Modify(Schedules_delete, id)->execute(); + DoReschedule(ice); } P2PVR::ScheduleList Schedules::GetSchedules(const Ice::Current &) { - P2PVR::ScheduleList rtn; - return rtn; + P2PVR::ScheduleList schedules; + SqlContainerCreator cct(schedules); + cct.populate(Select(Schedules_selectAll)); + return schedules; } int -Schedules::UpdateSchedule(const P2PVR::SchedulePtr &, const Ice::Current &) +Schedules::UpdateSchedule(const P2PVR::SchedulePtr & s, const Ice::Current & ice) { - return 0; + TxHelper tx(this); + if (s->ScheduleId == 0) { + Modify(Schedules_insert, s->ServiceId, s->EventId, s->Title, s->Search, s->Priority, s->Early, s->Late, s->Repeats)->execute(); + s->ScheduleId = SelectScalar(Schedules_insertNewId); + } + else { + Modify(Schedules_update, s->ServiceId, s->EventId, s->Title, s->Search, s->Priority, s->Early, s->Late, s->Repeats, s->ScheduleId)->execute(); + } + DoReschedule(ice); + return s->ScheduleId; } diff --git a/p2pvr/lib/si.cpp b/p2pvr/lib/si.cpp index 49d1df6..abcb9fd 100644 --- a/p2pvr/lib/si.cpp +++ b/p2pvr/lib/si.cpp @@ -40,11 +40,11 @@ SI::GetDeliveryForTransport(int id, const Ice::Current&) { P2PVR::Deliveries rtn; SqlContainerCreator cct(rtn); - cct.populate(Select("SELECT * FROM delivery_dvbt WHERE transportStreamId = ?", {id})); + cct.populate(Select("SELECT * FROM delivery_dvbt WHERE transportStreamId = ?", id)); SqlContainerCreator ccc(rtn); - ccc.populate(Select("SELECT * FROM delivery_dvbc WHERE transportStreamId = ?", {id})); + ccc.populate(Select("SELECT * FROM delivery_dvbc WHERE transportStreamId = ?", id)); SqlContainerCreator ccs(rtn); - ccs.populate(Select("SELECT * FROM delivery_dvbs WHERE transportStreamId = ?", {id})); + ccs.populate(Select("SELECT * FROM delivery_dvbs WHERE transportStreamId = ?", id)); return rtn.front(); } @@ -53,11 +53,11 @@ SI::GetDeliveryForService(int id, const Ice::Current&) { P2PVR::Deliveries rtn; SqlContainerCreator cct(rtn); - cct.populate(Select("SELECT d.* FROM services s, delivery_dvbt d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", {id})); + cct.populate(Select("SELECT d.* FROM services s, delivery_dvbt d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", id)); SqlContainerCreator ccc(rtn); - ccc.populate(Select("SELECT d.* FROM services s, delivery_dvbc d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", {id})); + ccc.populate(Select("SELECT d.* FROM services s, delivery_dvbc d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", id)); SqlContainerCreator ccs(rtn); - ccs.populate(Select("SELECT d.* FROM services s, delivery_dvbs d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", {id})); + ccs.populate(Select("SELECT d.* FROM services s, delivery_dvbs d WHERE serviceid = ? AND s.transportstreamid = d.transportstreamid", id)); return rtn.front(); } @@ -75,7 +75,7 @@ SI::GetService(int id, const Ice::Current&) { DVBSI::ServiceList rtn; SqlContainerCreator cc(rtn); - cc.populate(Select("SELECT * FROM services WHERE serviceId = ?", {id})); + cc.populate(Select("SELECT * FROM services WHERE serviceId = ?", id)); return rtn.front(); } diff --git a/p2pvr/lib/sql/GetScheduleConditates.sql b/p2pvr/lib/sql/GetScheduleConditates.sql deleted file mode 100644 index 1b9441b..0000000 --- a/p2pvr/lib/sql/GetScheduleConditates.sql +++ /dev/null @@ -1,20 +0,0 @@ -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 seen - where lower(e.title) = lower(seen.title) - and coalesce(lower(e.subtitle), '') = coalesce(lower(seen.subtitle), '') - and ts_rank(to_tsvector(e.description), plainto_tsquery(seen.description)) + - ts_rank(to_tsvector(seen.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 - diff --git a/p2pvr/lib/sql/Schedules_GetCandidates.sql b/p2pvr/lib/sql/Schedules_GetCandidates.sql new file mode 100644 index 0000000..1b9441b --- /dev/null +++ b/p2pvr/lib/sql/Schedules_GetCandidates.sql @@ -0,0 +1,20 @@ +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 seen + where lower(e.title) = lower(seen.title) + and coalesce(lower(e.subtitle), '') = coalesce(lower(seen.subtitle), '') + and ts_rank(to_tsvector(e.description), plainto_tsquery(seen.description)) + + ts_rank(to_tsvector(seen.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 + diff --git a/p2pvr/lib/sql/Schedules_delete.sql b/p2pvr/lib/sql/Schedules_delete.sql new file mode 100644 index 0000000..e14edc3 --- /dev/null +++ b/p2pvr/lib/sql/Schedules_delete.sql @@ -0,0 +1 @@ +DELETE FROM schedules WHERE scheduleId = ? diff --git a/p2pvr/lib/sql/Schedules_insert.sql b/p2pvr/lib/sql/Schedules_insert.sql new file mode 100644 index 0000000..100e78b --- /dev/null +++ b/p2pvr/lib/sql/Schedules_insert.sql @@ -0,0 +1,2 @@ +INSERT INTO schedules(serviceId, eventId, title, search, priority, early, late, repeats) +VALUES(?, ?, ?, ?, ?, ?, ?, ?) diff --git a/p2pvr/lib/sql/Schedules_insertNewId.sql b/p2pvr/lib/sql/Schedules_insertNewId.sql new file mode 100644 index 0000000..f66acd5 --- /dev/null +++ b/p2pvr/lib/sql/Schedules_insertNewId.sql @@ -0,0 +1 @@ +SELECT currval('schedules_scheduleid_seq'); diff --git a/p2pvr/lib/sql/Schedules_selectAll.sql b/p2pvr/lib/sql/Schedules_selectAll.sql new file mode 100644 index 0000000..5970dc1 --- /dev/null +++ b/p2pvr/lib/sql/Schedules_selectAll.sql @@ -0,0 +1,3 @@ +SELECT scheduleid, serviceid, eventid, title, search, priority, early::text early, late::text late, repeats +FROM schedules +ORDER BY scheduleId diff --git a/p2pvr/lib/sql/Schedules_update.sql b/p2pvr/lib/sql/Schedules_update.sql new file mode 100644 index 0000000..56c9531 --- /dev/null +++ b/p2pvr/lib/sql/Schedules_update.sql @@ -0,0 +1,10 @@ +UPDATE schedules SET + serviceId = ?, + eventId = ?, + title = ?, + search = ?, + priority = ?, + early = ?, + late = ?, + repeats = ? +WHERE scheduleId = ? -- cgit v1.2.3