[mythtv] MythTV database structure for channels

Stephen Davies mythtv-dev@snowman.net
Sun, 25 Aug 2002 00:35:40 +0100 (BST)

On Fri, 23 Aug 2002, Isaac Richards wrote:

> So yeah, I don't see anything that I don't like here -- send what you'd like 
> to do to change things..  As long as you don't break the most common case 
> (ie, 1 tuner card), I pretty much don't mind what you do to the database / 
> scheduler.

Hi Isaac,

Here's my proposal for how to extend the "channel" table into a set of
tables to keep track of channel sources, capture cards and so forth:

1) channel.channum has a new "logical" meaning

In the "channel" table - channum can just stay int as it us. But it is now
a logical channel number rather than always being the actual tuned TV

This means that the program, xxxrecord and recorded tables aren't
affected.  For the user interface we still show a list of channels, can
scroll up and down through them as before etc etc.

I propose that if the channum doesn't find a match in the new tuning
table (or if the new tables don't exist), then the channum is used
directly as per the existing code.  This provides backward compatibility
for people who have the simple setup.

2) Add new tables to track channel sources, how they are connected to the
available capture cards, and provide into on how each channel is tuned on
each source:

[This SQL code is currently for Postgres, the database I usually use.]

-- A broadcaster is a source of channels.  A "channel aggregator" if you
-- like.  A satellite service (and its receiver), a cable TV service, or a
-- terrestrial antenna would be examples.
-- Sometimes more than one channel from the broadcaster may be received
-- at the same time (subject to having more than one capture card
-- connected to the broadcaster).  An example would be the terrestrial
-- antenna.  In other cases, external receiver equipment limits us to one
-- channel at a time.  Digital satellite receivers are examples.  The
-- "shareable" flag keeps track.

create table broadcaster (
  broadcasternum        serial                  primary key,
  name                  varchar(80)             not null  unique,
  shareable             char(1)                 default 'N'
    -- can more than one channel be tuned at once on this
    -- chansource?  N/Y, and maybe 2..9 for a specific maximum

-- Track each capture card.  Generally a physical TV capture card, but
-- could also be used in a more abstract way for any other "way in"
-- for video or audio
-- The cardtype here can be used to map to an appropriate capture
-- class within the C++ code - creating a hook for supporting non-
-- V4L capture devices.  That class will interpret the cardinfo.

create table capturecard (
  capturecardnum        serial                  primary key,
  cardtype              varchar(12)             default 'V4L',
  cardinfo              varchar(80)
    -- extra data depending on type.  For v4l, for example, the
    -- video device and audio device will be needed.

-- Though each capture card can capture 1 thing at a time, they usually
-- have several inputs amongst which you can select.  Each input will be
-- connected to a chansource.  The "connection" can be a physical input,
-- or may be achieved by tuning a tuner or suchlike.
-- the "preference" allows you to tweak which particular connection will
-- be used for a chansource, or which connection/chansource for a channel.
-- It allows eg cable to be used before satellite etc etc, helping to keep
-- equipment free for external uses.

create table cardinput (
  capturecardnum        integer,
  broadcasternum        integer,
  shareable             char(1)                 default 'N'
    -- possible to capture more than one channel at a time
    -- on this input?  N/Y, and maybe 2..9 for a specific maximum?
    -- "N" for V4L cards.
    -- "Y" for a card like the budget DVB-T card which can filter
    -- more than one channel at a time from the same multiplex.
  externaltunecmd       varchar(80),
    -- generally null. but could be used to switch an external input
    -- selector or something like that.
  cardtuning            varchar(80),
    -- for v4l type devices, which input on the card, or which rf channel.
    -- for other device types format/meaning will depend on the device
  preference            integer,
    -- how preferred to use this combination?

  foreign key (capturecardnum) references capturecard on delete cascade,
  foreign key (broadcasternum) references broadcaster on delete cascade

-- Which channels come from each broadcaster, and how to tune into that
-- channel on that broadcaster

create table tuning (
  channum                       integer         not null,
  broadcasternum                 integer         not null,
  externaltunecmd               varchar(80),
    -- being the command(s) used to tune any external box(es).
    -- null if there is no external box
  cardtuning			varchar(80),
    -- if a specific capture card tuning must be set for
    -- this specific channel on this source.  Use this for
    -- RF modulated sources.  null if the cardtuning from the
    -- cardinput record should be used.
  foreign key (channum) references channel on delete cascade,
  foreign key (broadcasternum) references broadcaster on delete cascade

Here's a practical example - a setup with 2 capture cards.  Analogue cable
connected to both cards' RF input, an external satellite receiver on
video0 Composite1.

broadcaster table would have:

broadcasternum   name              shareable
==============   =============     =========
     1           Cable             Y
     2           Satellite         N

capturecard table:

capturecardnum   cardtype   cardinfo
==============   ========   =============================
     1           v4l        vid=/dev/video0 aud=/dev/dsp2
     2           v4l        vid=/dev/video1 aud=/dev/dsp3

cardinput table:

capturecardnum   broadcasternum   shareable   externaltunecmd   cardtuning   preference
==============   ==============   =========   ===============   ==========   ==========
     1                 1              N       null              null          10
     2                 1              N       null              null          10
     1                 2              N       null              Composite1    20

tuning, assuming channel 13 received in both cable and sat, channel 123 on
sat only, channel 3 on cable only

channum   broadcasternum   externaltunecmd   cardtuning
=======   ==============   ===============   ==========
  13           1           null              13
  13           2           sattune 113       null
  123          2           sattune 123       null
  3            1           null              3