2010-09-28, 09:59 PM
I needed some data to be searchable for guidePlus and vidImport that isn't possible if a recording is no longer in the EPG table, Sub plans to make some the fields available in the future but in the mean time I created an un-official work around - a combination of a new table that is a copy of the fields in the EPG_Event table and a trigger that inserts the data when a recording is scheduled. Once again very unofficial and may eventually break due to official database changes but it will allow you to do queries on the full set of available fields after the epg_event is gone.
Basically when a scheduled_recording is inserted a trigger is used to make exact copy of the epg_event in a new table called recorded_event. It may make sense to have another trigger that deletes it if the scheduled_recording is deleted (or a foreign key)
It is good for reading data but if you want to write changes you will need to write the XML to the "cached" event in the scheduled_recordings. Join with that table via title=name, channel_oid=channel_oid and start_time = start_time.
I will use it to search the unique ID to tell if an event is a TV series and if so augment any incoming EPG event on that series without require a user to tell me what series are important to them.
Once again - unofficial and not meant to step on Sub's toes, for experts only, may cause cancer and do not use if you are pregnant or have a heart condition or if your head does not reach the height of this mark :-)
Basically when a scheduled_recording is inserted a trigger is used to make exact copy of the epg_event in a new table called recorded_event. It may make sense to have another trigger that deletes it if the scheduled_recording is deleted (or a foreign key)
It is good for reading data but if you want to write changes you will need to write the XML to the "cached" event in the scheduled_recordings. Join with that table via title=name, channel_oid=channel_oid and start_time = start_time.
I will use it to search the unique ID to tell if an event is a TV series and if so augment any incoming EPG event on that series without require a user to tell me what series are important to them.
Once again - unofficial and not meant to step on Sub's toes, for experts only, may cause cancer and do not use if you are pregnant or have a heart condition or if your head does not reach the height of this mark :-)
Code:
CREATE TABLE [RECORDED_EVENT] (
[oid] integer NOT NULL,
[title] varchar(50) NOT NULL,
[subtitle] varchar(50) NOT NULL,
[description] varchar(50) NOT NULL,
[start_time] datetime NOT NULL,
[end_time] datetime NOT NULL,
[channel_oid] int NOT NULL,
[unique_id] varchar(50) NOT NULL,
[rating] varchar(50) NOT NULL,
[original_air_date] datetime,
[season] int NOT NULL,
[episode] int NOT NULL
);Code:
CREATE TRIGGER [recordingInserted]
AFTER INSERT ON [SCHEDULED_RECORDING]
FOR EACH ROW
BEGIN
insert into RECORDED_EVENT
(oid, title, subtitle, description, start_time, end_time,
channel_oid, unique_id, rating, original_air_date, season, episode)
select *
from EPG_EVENT where new.name = EPG_EVENT.title and
EPG_EVENT.start_time = new.start_time and
EPG_EVENT.channel_oid = new.channel_oid;
END