[mythtv] Some questions about the database schema

[f-myth-users@media.mit.edu]

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!