From 17cc678fa30eb7b56a66f705b790c24c1eb05a25 Mon Sep 17 00:00:00 2001 From: randomdan Date: Fri, 13 Dec 2013 18:20:51 +0000 Subject: Add schema for storing previously recorded, things to record Store results of recording schedule Centralise some more DB stuff --- p2pvr/datasources/schema.sql | 112 ++++++++++++++++++++++++++++++ p2pvr/lib/dbClient.cpp | 10 +++ p2pvr/lib/dbClient.h | 5 ++ p2pvr/lib/maintenance.cpp | 10 --- p2pvr/lib/maintenance.h | 4 -- p2pvr/lib/maintenance/network.cpp | 8 +-- p2pvr/lib/maintenance/services.cpp | 2 +- p2pvr/lib/schedules.cpp | 104 +++++++++++++++++++++++---- p2pvr/lib/sql/Schedules_GetCandidates.sql | 10 +-- 9 files changed, 227 insertions(+), 38 deletions(-) diff --git a/p2pvr/datasources/schema.sql b/p2pvr/datasources/schema.sql index f65cf5f..a3f5769 100644 --- a/p2pvr/datasources/schema.sql +++ b/p2pvr/datasources/schema.sql @@ -162,6 +162,58 @@ CREATE TABLE networks ( ALTER TABLE public.networks OWNER TO gentoo; +-- +-- Name: record; Type: TABLE; Schema: public; Owner: gentoo; Tablespace: +-- + +CREATE TABLE record ( + serviceid integer NOT NULL, + eventid integer NOT NULL, + recordstatus integer NOT NULL, + recordingstatus integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE public.record OWNER TO gentoo; + +-- +-- Name: recorded; Type: TABLE; Schema: public; Owner: gentoo; Tablespace: +-- + +CREATE TABLE recorded ( + recordedid integer NOT NULL, + scheduleid integer, + title text, + subtitle text, + description text, + starttime timestamp without time zone NOT NULL, + duration interval NOT NULL +); + + +ALTER TABLE public.recorded OWNER TO gentoo; + +-- +-- Name: recorded_recordedid_seq; Type: SEQUENCE; Schema: public; Owner: gentoo +-- + +CREATE SEQUENCE recorded_recordedid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.recorded_recordedid_seq OWNER TO gentoo; + +-- +-- Name: recorded_recordedid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: gentoo +-- + +ALTER SEQUENCE recorded_recordedid_seq OWNED BY recorded.recordedid; + + -- -- Name: schedules; Type: TABLE; Schema: public; Owner: gentoo; Tablespace: -- @@ -249,6 +301,13 @@ CREATE TABLE transportstreams ( ALTER TABLE public.transportstreams OWNER TO gentoo; +-- +-- Name: recordedid; Type: DEFAULT; Schema: public; Owner: gentoo +-- + +ALTER TABLE ONLY recorded ALTER COLUMN recordedid SET DEFAULT nextval('recorded_recordedid_seq'::regclass); + + -- -- Name: scheduleid; Type: DEFAULT; Schema: public; Owner: gentoo -- @@ -304,6 +363,22 @@ ALTER TABLE ONLY networks ADD CONSTRAINT pk_networks PRIMARY KEY (networkid); +-- +-- Name: pk_recorded; Type: CONSTRAINT; Schema: public; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY recorded + ADD CONSTRAINT pk_recorded PRIMARY KEY (recordedid); + + +-- +-- Name: pk_records; Type: CONSTRAINT; Schema: public; Owner: gentoo; Tablespace: +-- + +ALTER TABLE ONLY record + ADD CONSTRAINT pk_records PRIMARY KEY (serviceid, eventid); + + -- -- Name: pk_schedules; Type: CONSTRAINT; Schema: public; Owner: gentoo; Tablespace: -- @@ -350,6 +425,27 @@ CREATE INDEX idx_event_search ON events USING gin ((((setweight(to_tsvector('eng CREATE INDEX idx_event_title ON events USING btree (lower(title)); +-- +-- Name: idx_recorded_schedule; Type: INDEX; Schema: public; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_recorded_schedule ON recorded USING btree (scheduleid); + + +-- +-- Name: idx_recorded_starttime; Type: INDEX; Schema: public; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_recorded_starttime ON recorded USING btree (starttime); + + +-- +-- Name: idx_recorded_title; Type: INDEX; Schema: public; Owner: gentoo; Tablespace: +-- + +CREATE INDEX idx_recorded_title ON recorded USING btree (title); + + -- -- Name: fk_delivery_dvbc_transportstream; Type: FK CONSTRAINT; Schema: public; Owner: gentoo -- @@ -390,6 +486,22 @@ ALTER TABLE ONLY event_schedule ADD CONSTRAINT fk_eventschedule_schedule FOREIGN KEY (scheduleid) REFERENCES schedules(scheduleid) ON UPDATE CASCADE ON DELETE CASCADE; +-- +-- Name: fk_recorded_schedule; Type: FK CONSTRAINT; Schema: public; Owner: gentoo +-- + +ALTER TABLE ONLY recorded + ADD CONSTRAINT fk_recorded_schedule FOREIGN KEY (scheduleid) REFERENCES schedules(scheduleid) ON UPDATE CASCADE ON DELETE SET NULL; + + +-- +-- Name: fk_records_event; Type: FK CONSTRAINT; Schema: public; Owner: gentoo +-- + +ALTER TABLE ONLY record + ADD CONSTRAINT fk_records_event FOREIGN KEY (serviceid, eventid) REFERENCES events(serviceid, eventid) ON UPDATE CASCADE ON DELETE CASCADE; + + -- -- Name: fk_schedule_event; Type: FK CONSTRAINT; Schema: public; Owner: gentoo -- diff --git a/p2pvr/lib/dbClient.cpp b/p2pvr/lib/dbClient.cpp index a9b74e5..cdefc9d 100644 --- a/p2pvr/lib/dbClient.cpp +++ b/p2pvr/lib/dbClient.cpp @@ -1,5 +1,15 @@ #include #include "dbClient.h" +#include + +void +DatabaseClient::SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key) +{ + merge->cols.insert(new SqlMergeTask::TargetColumn(name, key)); + if (key) { + merge->keys.insert(name); + } +} void DatabaseClient::onAllDatasources(const DataSourceCall & call) const diff --git a/p2pvr/lib/dbClient.h b/p2pvr/lib/dbClient.h index 97d19bd..6c59301 100644 --- a/p2pvr/lib/dbClient.h +++ b/p2pvr/lib/dbClient.h @@ -12,10 +12,15 @@ #include #include "p2Helpers.h" +class SqlMergeTask; + class DatabaseClient : public virtual CommonObjects { public: typedef boost::shared_ptr SelectPtr; typedef boost::shared_ptr ModifyPtr; + + static void SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key); + protected: class TxHelper { public: diff --git a/p2pvr/lib/maintenance.cpp b/p2pvr/lib/maintenance.cpp index 275951e..eb1b570 100644 --- a/p2pvr/lib/maintenance.cpp +++ b/p2pvr/lib/maintenance.cpp @@ -2,16 +2,6 @@ #include "maintenance.h" #include #include -#include - -void -Maintenance::SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key) -{ - merge->cols.insert(new SqlMergeTask::TargetColumn(name, key)); - if (key) { - merge->keys.insert(name); - } -} void Maintenance::UpdateAll(const Ice::Current & ice) diff --git a/p2pvr/lib/maintenance.h b/p2pvr/lib/maintenance.h index edb5070..9083ded 100644 --- a/p2pvr/lib/maintenance.h +++ b/p2pvr/lib/maintenance.h @@ -4,8 +4,6 @@ #include #include "dbClient.h" -class SqlMergeTask; - class Maintenance : public P2PVR::Maintenance, public DatabaseClient { public: void UpdateAll(const Ice::Current &); @@ -15,8 +13,6 @@ class Maintenance : public P2PVR::Maintenance, public DatabaseClient { void UpdateProgramAssociations(short type, const Ice::Current &); void UpdateProgramMaps(short type, const Ice::Current &); void UpdateEvents(short type, const Ice::Current &); - - static void SqlMergeColumnsInserter(SqlMergeTask * merge, const std::string & name, bool key); }; #endif diff --git a/p2pvr/lib/maintenance/network.cpp b/p2pvr/lib/maintenance/network.cpp index c2e4494..7806824 100644 --- a/p2pvr/lib/maintenance/network.cpp +++ b/p2pvr/lib/maintenance/network.cpp @@ -27,20 +27,20 @@ class SiNetworkInformationMerger : public SiNetworkInformationParser { } SqlMergeTask mergeNetwork("postgres", "networks"); - CreateColumns(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeNetwork, _1, _2)); + CreateColumns(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeNetwork, _1, _2)); std::vector networks = { n }; mergeNetwork.sources.insert(new ContainerIterator>(&networks)); mergeNetwork.loadComplete(commonObjects); mergeNetwork.execute(NULL); SqlMergeTask mergeTransports("postgres", "transportstreams"); - CreateColumns(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeTransports, _1, _2)); + CreateColumns(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeTransports, _1, _2)); mergeTransports.sources.insert(new ContainerIterator(&n->TransportStreams)); mergeTransports.loadComplete(commonObjects); mergeTransports.execute(NULL); SqlMergeTask mergeDvbt("postgres", "delivery_dvbt"); - CreateColumns(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeDvbt, _1, _2)); + CreateColumns(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeDvbt, _1, _2)); BOOST_FOREACH(const auto & s, n->TransportStreams) { if (s->Terrestrial) { mergeDvbt.sources.insert(new SingleIterator(&s->Terrestrial)); @@ -50,7 +50,7 @@ class SiNetworkInformationMerger : public SiNetworkInformationParser { mergeDvbt.execute(NULL); SqlMergeTask mergeServices("postgres", "services"); - CreateColumns(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeServices, _1, _2)); + CreateColumns(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeServices, _1, _2)); BOOST_FOREACH(const auto & s, n->TransportStreams) { mergeServices.sources.insert(new ContainerIterator(&s->Services)); } diff --git a/p2pvr/lib/maintenance/services.cpp b/p2pvr/lib/maintenance/services.cpp index efa5974..ec4c6dc 100644 --- a/p2pvr/lib/maintenance/services.cpp +++ b/p2pvr/lib/maintenance/services.cpp @@ -24,7 +24,7 @@ class SiServicesMerger : public SiServicesParser { } SqlMergeTask mergeServices("postgres", "services"); - CreateColumns(boost::bind(&Maintenance::SqlMergeColumnsInserter, &mergeServices, _1, _2)); + CreateColumns(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeServices, _1, _2)); // Don't change the list of services available from the network mergeServices.doDelete = VariableType(false); mergeServices.doInsert = VariableType(false); diff --git a/p2pvr/lib/schedules.cpp b/p2pvr/lib/schedules.cpp index b3b6800..f36cade 100644 --- a/p2pvr/lib/schedules.cpp +++ b/p2pvr/lib/schedules.cpp @@ -5,7 +5,9 @@ #include #include #include +#include #include "p2Helpers.h" +#include "containerIterator.h" #include "resources.h" #include @@ -23,10 +25,42 @@ DECLARE_OPTIONS(Schedules, "P2PVR Scheduler options") "Implementation of episode group scheduler problem solver") END_OPTIONS() -typedef boost::tuple ScheduleCandidate; +class ScheduleCandidate { + public: + std::string What; + int ServiceId; + int EventId; + int TransportStreamId; + datetime StartTime; + datetime StopTime; + int Priority; +}; typedef boost::shared_ptr ScheduleCandidatePtr; typedef std::vector ScheduleCandidates; +enum RecordStatuses { + Record_WillRecordThisShowing = 0, + Record_WillRecordOtherShowing = 1, + Record_CannotRecordAnyShowing = 2 +}; + +class Record { + public: + Record() { }; + Record(int s, int e, RecordStatuses rs) : + ServiceId(s), + EventId(e), + RecordStatus(rs) + { + } + + int ServiceId; + int EventId; + RecordStatuses RecordStatus; +}; +typedef boost::shared_ptr RecordPtr; +typedef std::vector Records; + template<> void CreateColumns(const ColumnCreator & cc) @@ -44,13 +78,13 @@ template<> void UnbindColumns(RowState & rs, ScheduleCandidatePtr const & s) { - rs.fields[0] >> boost::get<0>(*s); - rs.fields[1] >> boost::get<1>(*s); - rs.fields[2] >> boost::get<2>(*s); - rs.fields[3] >> boost::get<3>(*s); - rs.fields[4] >> boost::get<4>(*s); - rs.fields[5] >> boost::get<5>(*s); - rs.fields[6] >> boost::get<6>(*s); + rs.fields[0] >> s->What; + rs.fields[1] >> s->ServiceId; + rs.fields[2] >> s->EventId; + rs.fields[3] >> s->TransportStreamId; + rs.fields[4] >> s->StartTime; + rs.fields[5] >> s->StopTime; + rs.fields[6] >> s->Priority; } template<> @@ -83,6 +117,24 @@ UnbindColumns(RowState & rs, P2PVR::SchedulePtr const & s) rs.fields[8] >> s->Repeats; } +template<> +void +CreateColumns(const ColumnCreator & cc) +{ + cc("serviceid", true); + cc("eventid", true); + cc("recordstatus", false); +} + +template<> +void +BindColumns(RowState & rs, RecordPtr const & s) +{ + rs.fields[0] << s->ServiceId; + rs.fields[1] << s->EventId; + rs.fields[2] << (int)s->RecordStatus; +} + Showing::Showing(unsigned int s, unsigned int e, unsigned int t, datetime start, datetime stop, int p, const Episode * ep) : episode(ep), serviceId(s), @@ -253,18 +305,17 @@ Schedules::DoReschedule(const Ice::Current & ice) EpisodePtr cur; int minPriority = 0; BOOST_FOREACH(const auto & c, episodes) { - const auto & thisWhat = boost::get<0>(*c); - if (!cur || cur->what != thisWhat) { - cur = new Episode(thisWhat); + if (!cur || cur->what != c->What) { + cur = new Episode(c->What); scheduleList.push_back(cur); } - ShowingPtr s = new Showing(boost::get<1>(*c), boost::get<2>(*c), boost::get<3>(*c), - boost::get<4>(*c), boost::get<5>(*c), boost::get<6>(*c), cur.get()); + ShowingPtr s = new Showing(c->ServiceId, c->EventId, c->TransportStreamId, + c->StartTime, c->StopTime, c->Priority, cur.get()); minPriority = std::min(minPriority, s->priority); cur->showings.push_back(s); allShowings.push_back(s); } - Logger()->messagebf(LOG_DEBUG, "%d episodes created", scheduleList.size()); + Logger()->messagebf(LOG_DEBUG, "%d episodes created, %s showings", scheduleList.size(), allShowings.size()); BOOST_FOREACH(const auto & e, scheduleList) { Logger()->messagebf(LOG_DEBUG, " %s", e->what); BOOST_FOREACH(const auto & s, e->showings) { @@ -274,6 +325,7 @@ Schedules::DoReschedule(const Ice::Current & ice) e->priority /= e->showings.size(); } + Records records; // Solve while (!scheduleList.empty()) { auto work = scheduleList.begin(); @@ -317,7 +369,31 @@ Schedules::DoReschedule(const Ice::Current & ice) } } Logger()->message(LOG_DEBUG, "----------"); + BOOST_FOREACH(const auto & c, group) { + bool found = false; + BOOST_FOREACH(const auto & i, c->showings) { + if (i && selected.find(i) != selected.end()) { + found = true; + break; + } + } + BOOST_FOREACH(const auto & i, c->showings) { + if (i) { + records.push_back(RecordPtr(new Record(i->serviceId, i->eventId, + found ? + selected.find(i) != selected.end() ? Record_WillRecordThisShowing : Record_WillRecordOtherShowing : + Record_CannotRecordAnyShowing))); + } + } + } } + + TxHelper tx(this); + SqlMergeTask mergeRecords("postgres", "record"); + CreateColumns(boost::bind(&DatabaseClient::SqlMergeColumnsInserter, &mergeRecords, _1, _2)); + mergeRecords.sources.insert(new ContainerIterator(&records)); + mergeRecords.loadComplete(this); + mergeRecords.execute(NULL); } void diff --git a/p2pvr/lib/sql/Schedules_GetCandidates.sql b/p2pvr/lib/sql/Schedules_GetCandidates.sql index 1b9441b..aa6ec0f 100644 --- a/p2pvr/lib/sql/Schedules_GetCandidates.sql +++ b/p2pvr/lib/sql/Schedules_GetCandidates.sql @@ -10,11 +10,11 @@ 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) + 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 -- cgit v1.2.3