[mythtv] Some questions about the database schema

[Carl Reynolds]

f-myth-users at media.mit.edu wrote:

>I've been poking around the schema in the process of developing some
>archival & searching tools for our research, and I've got some
>questions I can't find the answers to.  Any help appreciated.
>
>[All of these relate to .18.1, but I'd be very interested to know if
>they're going to persist in .19/SVN and therefore if I'll have to code
>around them, or if they're fixed, or if they're bugs that may be
>fixed.  Most of this is in support of archiving stuff to "near-line"
>storage (think "NAS" or "tape robot" and you'll be close) and then
>retrieving it again for later analysis.]
>
>(1) I almost always record programs with a 2-minute pre- and
>post-roll.  However, "recordedprogram" only shows the programs where
>I -didn't- preroll (postrolled ones -are- there), so it has a grand
>total of 3 programs right now.  (It would have zero if I hadn't
>screwed up those 3.)  "recorded" is much more complete, -however-,
>that table doesn't have the "syndicatedepisodenumber" column that
>recordedprogram has, and I need to preserve that column's data---not
>only do we already have an existing (non-Myth) database with thousands
>of entries that contain this info, but many web sources use that
>number when talking about the episode.  [Example: PBS's "American
>Experience" is currently airing ep #1805, but that string of digits
>appears nowhere in its series ID (SH004188) or its episode ID
>(EP0041880221), so I can't derive it from those---though it -does-
>appear in the "episode details" screen in the UI, so it's in the
>database somewhere else.  (I assume the series/program ID's are
>furnished from DataDirect and otherwise uninterpreted by Myth?)]
>
>So:  is the omission of prerolled episodes from "recordedprogram" a bug?
>Is it fixed in SVN?  Is the omission of "syndicatedepisodenumber" from
>"recorded" deliberate, a bug, or don't-care?  (I'd rather that "recorded"
>had that column added to it; that would mean I could pull a complete
>description of the episode out all at once by just selecting a single
>row of "recorded" and I could just ignore "recordedprogram" entirely.)
>
>Also:  "oldrecorded" doesn't have "syndicatedepsidoenumber" either.
>Bug? Deliberate? Can this be added if it's not already in SVN?
>
>(2) I assume that "recorded" and "recordedprogram" have records
>deleted from them when the corresponding episode is deleted.  Can I
>assume that, barring "Remove Episode" in "Previously Recorded" and
>other manual database surgeries, no records will ever be deleted from
>"oldrecorded"?
>
>(3) Does myth have any one preferred "canonical" way of naming an
>episode uniquely?  I can see several possibilities, including episode
>ID, a pair of [channum, starttime], and various other ways.  But if
>I'm going to talk about keeping track of episodes uniquely, it'd make
>sense to use as a primary key whatever myth uses, assuming there is
>such a primary.  Is there, or should I just invent my own mechanism?
>["recordid" looks like it starts at 1 and gets incremented forever;
>can I rely on it never being duplicated?  Assuming it appears in all
>the other tables I mention above, I could use that to select various
>rows from them, so is this the canonical representation?]
>
>(4) As part of the "tape robot", it's convenient to record (presumably
>in "oldrecorded") exactly which volume(s) a given recording ended up
>on.  The logical thing for me to do is to add a column to that table.
>Is it, in general, a safe assumption that I can add columns to myth
>tables and, as long as I've named them something unlikely to be used
>in later development, the columns will otherwise be ignored and not
>cause anything else weird to happen?  [I'm thinking of prefixing any
>added column with something like FNORD_ or something else unlikely to
>be used by anyone who adds a column to the real myth source.]
>
>Thanks much!
>  
>
I have also been trying to learn more about the database for the last 
month or so. Since I'm trying to learn this stuff, my answers are little 
more than educated guesses. I would like to see someone more 
knowledgeable than me give you some answers, because I think you have 
some interesting questions.

I can't begin to answer your first two questions. However, for #3, as 
far as I have been able to discover, there is no single primary key that 
is used by mythconverg to refer to a movie in the various tables of the 
database. From what I have been able to gather from the code, each 
section of the program uses specific tables from the database and does 
not try to coordinate its own information with other parts of the 
program or other tables in the database. So, there is no single primary 
key that could be used to refer to the same episode in different tables.

As far as I can tell, adding new columns to your database with a unique 
name should cause no problems with future enhancements to the database. 
However, as you upgrade you will have to patch the new version of the 
program to use and maintain your unique columns. Other than that there 
should be now conflicts.

Hopefully someone else will give a more complete answer and will correct 
any mistakes I have made.



Carl.