[mythtv-users] Consolidate two databases into one

[Nick Morrott]

On 19/09/2008, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> On 09/18/2008 08:14 PM, Nick Morrott wrote:
>
> > On 18/09/2008, Nick F wrote:
>  >> Is there a way to copy all the recorded shows from one machine to the other
>  >> then insert all the database records relating to recorded shows into that
>  >> database?  I realise this is something that needs to be done manually, but
>  >> are there any mysql commands that could make it easier?  What tables would I
>  >> need to copy?
>  >>
>  > One possible method is in the MythTV documentation:
>  >
>  > http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.7
>  >
>  > The 'record' table holds your recording schedules, the others details
>  > of your actual recordings.
>
>
> Though you may have index/key uniqueness violations if both systems use
>  the same chanid(s) (and you happened to have a recording on at the same
>  time on both).  Note, also that there's a better approach than 23.7 at
>  http://www.mythtv.org/wiki/index.php/Database_Backup_and_Restore , but I
>  didn't mention it because I can't guarantee it will work because of the
>  index/key issue and I don't have time to do much more than provide a
>  link, now.

The thread http://www.gossamer-threads.com/lists/mythtv/users/349161
from some days ago on the exact same topic provides some discussion
and warning about these issues. If you don't have many entries, the
manual procedure described is fairly straightforward, but it does
require editing the raw data and updating the database directly. As
Mike states, you have to be very careful to ensure there are no
clashes between unique fields in the two datasets, and to update any
related table data you are also importing if you do have to change one
of the unique values. You will also have to ensure that you update any
host names in the data to point to the new machine.

I'll post information below about my understanding of the key
relations of the tables that the 'Moving your data to new hardware'
section mentions so that it hits the list archive, in the hope that it
is of use to someone in the future (this is from a 0.21-fixes system)
planning to merge data from multiple machines manually:

TABLE: record
UNIQUE FIELD: record.recordid (autoincremented)
INHERITS/LINKS: record.chanid from channel.chanid
RELATED TABLES: record.recordid used in recorded.recordid, oldrecorded.recordid;
NOTABLE FIELDS: storagegroup

TABLE: recorded
UNIQUE FIELD: recorded.chanid + recorded.starttime
INHERITS/LINKS: recorded.chanid from channel.chanid
RELATED TABLES:
NOTABLE FIELDS: hostname and storagegroup

TABLE: recordedprogram
UNIQUE FIELD: recordedprogram.chanid + recordedprogram.starttime +
recordedprogram.manualid
INHERITS/LINKS: recordedprogram.chanid from channel.chanid

TABLE: recordedrating
UNIQUE FIELD: recordedrating.chanid + recordedrating.starttime +
recordedrating.system + recordedrating.rating
INHERITS/LINKS: recordedrating.chanid from channel.chanid

TABLE: recordedmarkup
UNIQUE FIELD: recordedmarkup.chanid + recordedmarkup.starttime +
recordedmarkup.type + recordedmarkup.mark
INHERITS/LINKS: recordedmarkup.chanid from channel.chanid

TABLE: recordedseek
UNIQUE FIELD: recordedseek.chanid + recordedseek.starttime +
recordedseek.type + recordedseek.mark
INHERITS/LINKS: recordedseek.chanid from channel.chanid

TABLE: oldrecorded
UNIQUE FIELD: oldrecorded.station + oldrecorded.starttime + oldrecorded.title
INHERITS/LINKS: oldrecorded.chanid from channel.chanid,
oldrecorded.recordid from record.recordid, oldrecorded.station from
channel.callsign


The record.recordid field is an autoincrement field, therefore care
must be taken to update conflicting values to values that either slot
into gaps in the range of recordid values, or that the autoincrement
counter for the record table is updated to reflect an increased
recordid maximum value (see
http://www.gossamer-threads.com/lists/mythtv/users/349238#349238).

The related recorded* tables essentially include one or more values
(chanid, starttime...) from the recorded table, so these values should
agree across all of these tables. If one of the values in
mythconverg.recorded has to be updated, also update the value across
the other related recorded* tables.

The oldrecorded table keeps details of your recording history, so for
some users this may be the only table they need to import if they do
not want to also import their existing recordings and schedules. To
ensure that duplicate matching continues to work properly the station
field should agree with the new installation.

Cheers,
Nick

-- 
Nick Morrott

MythTV Official wiki:
http://mythtv.org/wiki/
MythTV users list archive:
http://www.gossamer-threads.com/lists/mythtv/users

"An investment in knowledge always pays the best interest." - Benjamin Franklin