Is it safe to add new columns to a table? (created and modified timestamps in program)

[wmorrison]

I would like to alter my program table, adding two new columns for create and modified timestamp, auto-populated. As discussed in these two links:

https://www.oreilly.com/library/view/my ... 05s34.html
https://www.tutorialspoint.com/can-mysq ... p-in-a-row

Is there any reason that would "break" mythtv, either in day-to-day operation, or when updating MythTV?

As for the why...

After mythfilldatabase runs, not all changes are for starttimes greater than the previous max starttime.

Sometimes listings change, such as:

- Shows listed only as "To Be Announced" or "Movie"
- Series episodes with season 0 episode 0 and only the generic series description

These may be updated with specific info as the air date approaches.

Without a way to know when a listing was added or changed, I don't know if there are any other "quirks" but basically SchedulesDirect is only aggregating data from other sources, so it depends on how the channel provides listings.

I want to write some queries to filter down the 20,000+ future listings by "what changed since" a certain date, before further filtering (by category, etc.)

Am I missing some other way of doing this?

[bill6502]

Altering the DB isn't supported. Although you could choose column names that
were extremely unlikely to be selected by a developer, there are places in the
code that rely on column order. Just one example:

Code: Select all

https://code.mythtv.org/cgit/mythtv/tree/mythtv/libs/libmythtv/programdata.cpp#n401

If new columns are added by a developer, then they may appear
after local changes.

mythweb addresses some columns by location as well.

Plus, you'll void your warranty.

[bill6502]

FWIW, the SQLIte DB has half of what you want:

Code: Select all

Table: stations_schedules_hash
{
    "MD5":"9chnj8tRvM+GLAAeFMjB6g"
    "code":0
    "date":"2021-01-01"
    "lastModified":"2021-01-01T07:57:19Z" <<<<<<<<<<<<<
    "message":"OK"
    "response":"OK"
    "stationID":"20455"
}

[wmorrison]

bill6502,

OK, so altering mythconverg tables is not a good idea.

Also, the lastModified data you showed only shows when the entire schedule for that stationID was updated, not individual programs.

But I'm onto something. I made a copy of the sqlite database file, then let mythfilldatabase run. Then, I can start sqlite with the main db, attach the backup as 'lastrun', and do this:

select station,day,starttime,program from main.schedules except select station,day,starttime,program from lastrun.schedules

Returns what's new or updated in the current database. Actually, I'm doing the above in a 'with' clause and then joining other tables to see what changed (titles, etc.)

(BTW, don't use any JSON columns or hashes in the 'except' query. JSON is unsorted, so even if every key and value is the same between databases, the order may be different, so it gets returned as a change. Same with hashes, if they hash the JSON, the hash is different even if every key and value matches. If you really want to compare something in a JSON field extract it, but the non-JSON columns I listed seem to work for my application.)

Still a lot more to figure out, but looks promising. I already see "generic episodes" updating to specific season/episode and "To Be Announced" updating to specific titles, since yesterday, that are airing within a few days. That's the kind of "needle in the haystack" I frequently miss. (I'll see a commercial in something I did record, that mentions the show I would have recorded, but it's too late.)