Missed recordings caused by EIT guide update

[Klaas de Waal]

Problem

In a system that uses EIT for the guide data, if you schedule a single recording and the start time does later on change even as little as one minute then the entry will be shown in the "Upcoming Recordings" as "Not listed" and it will not be recorded.

Analysis

The programs scheduled to be recorded, i.e. the records in database table program, are identified by the fields chanid plus starttime. The EIT program guide updates do change sometimes the program starttime. There are a number of tables that also store program related information and that use the same primary key of chanid plus starttime. The EIT program update code does update the starttime in all these tables, but NOT in the database table record. This is the table that is used by the scheduler and, for single record recordings only, the starttime is used to identify the program to be recorded. When the starttime is changed the program cannot be found anymore and it will not be recorded.

Solution

The solution is to update the starttime also in table record when the EIT update code updates the program starttime. This is implemented in the attached patch.

Additional information

There is an extensive email thread in the developers mailing list, title "Missed recordings caused by EIT guide update", started 23 Aug 2019, which describes this bug, the solution presented here and possible alternative solutions in more detail.

[Klaas de Waal]

Update starttime in table record for single recordings when EIT updates change the starttime of a program.

[Klaas de Waal]

The solution implemented here does fix the existing implementation of the single recording and it does this for EIT program updates only.

To capture the knowledge about this and to serve as an inspiration for future development, possibly but not necessarily by me, here is a summary of the issues and the alternatives.

Change the primary key for table program
The table program has chanid plus starttime as the primary key. If the table program would have a primary key that is independent of the data then a reference to an entry in the table program would remain valid even if the starttime changes.

Re-implement the "Single Recording"
For all other types of recording the starttime is not used as a key and hence the problem of changing starttimes does not exist. For instance, with a power rule it is possible to search for a program with a specific title in a time interval. It should be possible to implement the "Single Recording" in this way. This would solve the problem both for EIT and for XMLTV program guide updates.

What about XMLTV/mythfilldatabase
The program guide update code that is used by mythfilldatabase is much simpler and does not try to identify the program that is being updated. This is not done because the XMLTV/Schedules Direct data is always complete so you can always add a program to the guide. In contrast, EIT information can come in bits and pieces so there it is essential to identify which program the update is intended for.
It is possible to merge the guide update code so that the XMLTV updates are also processed by the EIT program matching code.
However, a better solution is probably to re-implement the Single Recording with a search rule, as described above.

Use MySQL schema for field update propagation
As mentioned by Philipp Hahn in the email thread:
Both MySQL and PostgreSQL support "ON UPDATE CASCADE", but the Schema definition all lack the required "REFERENCES $table($column)" statements for that to work. Then the DBMS would do the job for you, faster and less error prone and guaranteed to follow the ACID properties.
This is an intriguing feature which I did not know before. However, adding a database rule like this would always update the fields in table record; the fix in the code is selective so that it only updates the table record when there is a single recording. Also, a fix in code is much more flexible than a database schema change. Another consideration is that if database rules like this are used it does need to be commented in the C++ code that depends on it otherwise the code will be, even more than now, incomprehensible to most mortal beings.

[Klaas de Waal <kdewaal@…>]

In 2dcfeb4952/mythtv:

Update recording starttime for single recordings

When a program guide update from the EIT changes the
starttime of a program and if that program is sceduled
to be recorded as a single reording then update
the starttime of hte scheduled recording.

If the starttime of a scheduled single recording is
not correct then the program is shown as "Not Listed"
in the Scheduled Recording page because the starttime
is used to identify the program to be recorded.

Fixes #13481

[gigem]

Replying to Klaas de Waal:

Change the primary key for table program
The table program has chanid plus starttime as the primary key. If the table program would have a primary key that is independent of the data then a reference to an entry in the table program would remain valid even if the starttime changes.

This is simply not true. Even if all of the common, program information was moved to a new table indexed by by just programid, there will still need to be table indexed by channel, time and program. That's just how a program guide must work. FWIW, I'm in favor of moving that common program information to another table. The current schema is the way it is because it predates any thoughts of having programids.

Re-implement the "Single Recording"
For all other types of recording the starttime is not used as a key and hence the problem of changing starttimes does not exist. For instance, with a power rule it is possible to search for a program with a specific title in a time interval. It should be possible to implement the "Single Recording" in this way. This would solve the problem both for EIT and for XMLTV program guide updates.

This is also not true. Override and Don't Record rule types use program time too.