Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: MythTV: Dev

db schema, metadata and season/episode numbering

  

 
MythTV dev RSS feed   Index | Next | Previous | View Threaded


torbjorn.jansson at mbox200

Apr 11, 2012, 4:02 AM

Post #1 of 15 (598 views)
Permalink
db schema, metadata and season/episode numbering
Hi.

I'm trying to figure out how the season and episode numbers is stored in the
database.
The background to this is that I'm trying to do a bit of creating scripting
to populate the season and episode numbers based on data that is already
present in the database.
Having proper episode and season numbers I think should make the metadata
lookup able to do a better job and if possible I don’t want to do this
myself manually if it can be avoided.

I have an old installation of myth that I've been using for many years and
most of my recordings do have the episode numbers but it is usually stored
in the subtitle or other location.

I've looked at the database and I got some questions.
I think I understand how it works, but I'm not 100% sure.

Table recordedprogram contains one field called syndicatedepisodenumber.
This field appears to be the correct episode and season for most of my
recordings, format is something like E8S2.
I'm thinking I could take this info and update the season & episode fields
in recorded (converting & splitting the string as needed of course).

When syndicatedepisodenumber is missing I would have to read the subtitle
and see if it matches a predefined pattern for episode and season numbers.


recorded & recordedprogram can be joined on chanid + starttime right?
And it is the season and episode fields in recorded that is used by the
metadata lookup?

Comments? Is there a better way maybe that I have not thought of yet?


_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


raymond at wagnerrp

Apr 11, 2012, 6:40 AM

Post #2 of 15 (579 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 4/11/2012 07:02, Torbjörn Jansson wrote:
> Comments? Is there a better way maybe that I have not thought of yet?

The XMLTV format supports an <episode-num> field, and within that the
"xmltv_ns" system format, using three fields, separated by periods,
defining the season, episode, and part numbers. Any work towards
populating the new season and episode fields directly from the guide
data should be done through processing of that field. Any attempt to
parse season and episode number from other formats such as "onscreen" or
"dd_progid", or other fields like subtitle, will be prone to error.
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


torbjorn.jansson at mbox200

Apr 11, 2012, 7:18 AM

Post #3 of 15 (584 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
> On 4/11/2012 07:02, Torbjörn Jansson wrote:
> > Comments? Is there a better way maybe that I have not thought of yet?
>
> The XMLTV format supports an <episode-num> field, and within that the
> "xmltv_ns" system format, using three fields, separated by periods,
> defining the season, episode, and part numbers. Any work towards
> populating the new season and episode fields directly from the guide
> data should be done through processing of that field. Any attempt to
> parse season and episode number from other formats such as "onscreen"
> or
> "dd_progid", or other fields like subtitle, will be prone to error.

Yes i agree, getting good guide data is step one, but it won't fix my
existing things recorded over the last 5 years or something like that.

What I was going to do was a onetime update of the old stuff.


_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


torbjorn.jansson at mbox200

Apr 30, 2012, 6:21 AM

Post #4 of 15 (531 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
> -----Original Message-----
> From: mythtv-dev-bounces [at] mythtv [mailto:mythtv-dev-
> bounces [at] mythtv] On Behalf Of Raymond Wagner
> Sent: Wednesday, April 11, 2012 3:40 PM
> To: Development of MythTV
> Subject: Re: [mythtv] db schema, metadata and season/episode numbering
>
> On 4/11/2012 07:02, Torbjörn Jansson wrote:
> > Comments? Is there a better way maybe that I have not thought of yet?
>
> The XMLTV format supports an <episode-num> field, and within that the
> "xmltv_ns" system format, using three fields, separated by periods,
> defining the season, episode, and part numbers. Any work towards
> populating the new season and episode fields directly from the guide
> data should be done through processing of that field. Any attempt to
> parse season and episode number from other formats such as "onscreen"
> or
> "dd_progid", or other fields like subtitle, will be prone to error.

I've done some more checking.
The season and episode part of the <episode-num system="xmltv_ns"> tag is
only stored in the syndicated episode number field.
This is the part of the data that my grabber currently contains.
Example from my xmltv data:
<episode-num system="xmltv_ns">3 . 7 .</episode-num>
(part 8 season 4)

Result is that the partnumber is left empty.

My guess is that the recorded.season and episode is coming from
program.partnumber and parttotal that is not present in my xmltv data.

So, is this a problem that should be fixed in mythtv or the xmltv source
data?
I'm thinking mythtv.
Depending on how this is supposed to work I small adjustment to
mythfilldatabase or how the data is copied from the program table to the
recording may work.



_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


mtdean at thirdcontact

Apr 30, 2012, 7:09 AM

Post #5 of 15 (518 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 04/30/2012 09:21 AM, Torbjörn Jansson wrote:

> From: Raymond Wagner
>> On 4/11/2012 07:02, Torbjörn Jansson wrote:
>>> Comments? Is there a better way maybe that I have not thought of yet?
>> The XMLTV format supports an<episode-num> field, and within that the
>> "xmltv_ns" system format, using three fields, separated by periods,
>> defining the season, episode, and part numbers. Any work towards
>> populating the new season and episode fields directly from the guide
>> data should be done through processing of that field. Any attempt to
>> parse season and episode number from other formats such as "onscreen"
>> or
>> "dd_progid", or other fields like subtitle, will be prone to error.
> I've done some more checking.
> The season and episode part of the<episode-num system="xmltv_ns"> tag is
> only stored in the syndicated episode number field.
> This is the part of the data that my grabber currently contains.
> Example from my xmltv data:
> <episode-num system="xmltv_ns">3 . 7 .</episode-num>
> (part 8 season 4)
>
> Result is that the partnumber is left empty.
>
> My guess is that the recorded.season and episode is coming from
> program.partnumber and parttotal that is not present in my xmltv data.
>
> So, is this a problem that should be fixed in mythtv or the xmltv source
> data?
> I'm thinking mythtv.
> Depending on how this is supposed to work I small adjustment to
> mythfilldatabase or how the data is copied from the program table to the
> recording may work.

partnumber and parttotal are used for "split" episodes--i.e. Fringe,
"Brave New World, Part 1 of 2" is partnumber 1 with parttotal of 2.
They have nothing to do with season/episode.

TTBOMK, the episode-num in your XMLTV data is used to create a
programid, but is not directly stored in the database. Because it's not
in the program table (there is actually no field for it), it cannot be
moved to the recorded table when you record the episode. Instead, I'm
pretty sure the grabber scripts (and/or recording rules themselves, with
data you provide/start) provide all of the season/episode information
that's stored in the DB.

We'll be making some major changes to the schema, soon, that will
probably make it much easier to handle season/episode data (and other
metadata).

Mike
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


torbjorn.jansson at mbox200

Apr 30, 2012, 1:27 PM

Post #6 of 15 (513 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
> TTBOMK, the episode-num in your XMLTV data is used to create a
> programid, but is not directly stored in the database. Because it's
> not
> in the program table (there is actually no field for it), it cannot be
> moved to the recorded table when you record the episode. Instead, I'm
> pretty sure the grabber scripts (and/or recording rules themselves,
> with
> data you provide/start) provide all of the season/episode information
> that's stored in the DB.
>
> We'll be making some major changes to the schema, soon, that will
> probably make it much easier to handle season/episode data (and other
> metadata).
>
> Mike

Ok. I have looked at the code and i think understand.
I'm not that familiar with the mythtv code but I didn’t find anywhere that
inserts the season and episode data in the recorded table unless you do it
manually.

Looking forward to the changes and I hope it eventually makes it to the
fixes branch.

One thing I don’t understand is that this must be working for someone, but
what do they use to get the data in there?
I probably missed something.


_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


raymond at wagnerrp

Apr 30, 2012, 1:34 PM

Post #7 of 15 (512 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 4/30/2012 16:27, Torbjörn Jansson wrote:
> I didn’t find anywhere that inserts the season and episode data in the recorded table unless you do it manually.

It doesn't, as there is no season/episode number stored for guide data
for it to be inserted into recordings. It must be inserted afterwards,
either manually by the user, or automatically by mythmetadatalookup.
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


stuart at tase

Apr 30, 2012, 2:00 PM

Post #8 of 15 (517 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On Monday 30 Apr 2012 16:34:06 Raymond Wagner wrote:
> On 4/30/2012 16:27, Torbjörn Jansson wrote:
> > I didn’t find anywhere that inserts the season and episode data in the
> > recorded table unless you do it manually.
>
> It doesn't, as there is no season/episode number stored for guide data
> for it to be inserted into recordings. It must be inserted afterwards,
> either manually by the user, or automatically by mythmetadatalookup.

There is season/episode information for some guide sources, which is I believe
what's being discussed here. When I last raised the subject of inserting
season/episode information from guide data into the database it was shot down
by Robert M because he believed that it couldn't be trusted and would
interfere with his metadata lookup stuff.

Since he's no longer here to block it I'd be interested in pushing forward, at
least in the UK it's pretty decent information. There's no reason to simply
ignore it, especially when the current metadata lookup is so hit and miss (tv
espiodes frequently given film metadata and vice-versa), having season/episode
information would either make it more reliable or as reliable, but certainly
not less. Even if the air order differs from the order at tvdb.com (subtitle
etc) can always take precedence over episode number.

If nothing else it could be inserted as airepisode/airseason - let those of us
who get this data use it in the UI and for sorting.
--
Stuart Morgan
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


raymond at wagnerrp

Apr 30, 2012, 2:16 PM

Post #9 of 15 (512 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 4/30/2012 17:00, Stuart Morgan wrote:
> On Monday 30 Apr 2012 16:34:06 Raymond Wagner wrote:
>> On 4/30/2012 16:27, Torbjörn Jansson wrote:
>>> I didn’t find anywhere that inserts the season and episode data in the
>>> recorded table unless you do it manually.
>>
>> It doesn't, as there is no season/episode number stored for guide data
>> for it to be inserted into recordings. It must be inserted afterwards,
>> either manually by the user, or automatically by mythmetadatalookup.
> There is season/episode information for some guide sources, which is I believe
> what's being discussed here.

I mean there is no code to take the season/episode from the `program`
table and insert it into the `recorded` table at the time of recording,
as there are no such fields in the `program` table to begin with.
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


torbjorn.jansson at mbox200

Apr 30, 2012, 3:25 PM

Post #10 of 15 (513 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
> Since he's no longer here to block it I'd be interested in pushing
> forward, at
> least in the UK it's pretty decent information. There's no reason to
> simply
> ignore it, especially when the current metadata lookup is so hit and
> miss (tv
> espiodes frequently given film metadata and vice-versa), having
> season/episode
> information would either make it more reliable or as reliable, but
> certainly
> not less. Even if the air order differs from the order at tvdb.com
> (subtitle
> etc) can always take precedence over episode number.
>
> If nothing else it could be inserted as airepisode/airseason - let
> those of us
> who get this data use it in the UI and for sorting.
> --
> Stuart Morgan

I'm all for it.
That’s why I started to look into this.

What I have now is a bit of a hack and takes advantage of the syndicatedepisodenumber being updated with the right info by mythfilldatabase with the xmltv season and episode info.
The query below is can probably be done better and it would have been nice with some regexp matching.

UPDATE `mythconverg`.`recorded`,(
SELECT
chanid,
starttime,
IF(INSTR(syndicatedepisodenumber,'S')>0,SUBSTRING(syndicatedepisodenumber,2,INSTR(syndicatedepisodenumber,'S')-2),SUBSTRING(syndicatedepisodenumber,2,LENGTH(syndicatedepisodenumber))) AS e,
IF(INSTR(syndicatedepisodenumber,'S')=0,0,SUBSTRING(syndicatedepisodenumber,INSTR(syndicatedepisodenumber,'S')+1,99)) AS s
FROM
`mythconverg`.`recordedprogram`
WHERE
LENGTH(syndicatedepisodenumber)>0
) AS A SET `mythconverg`.`recorded`.season=A.s, `mythconverg`.`recorded`.episode=A.e
WHERE
`mythconverg`.`recorded`.`chanid`=A.`chanid`
AND `mythconverg`.`recorded`.`starttime`=A.`starttime`
AND episode=0
AND season=0

_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


mtdean at thirdcontact

Apr 30, 2012, 4:19 PM

Post #11 of 15 (512 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 04/30/2012 06:25 PM, Torbjörn Jansson wrote:
>> Since he's no longer here to block it I'd be interested in pushing
>> forward, at
>> least in the UK it's pretty decent information. There's no reason to
>> simply
>> ignore it, especially when the current metadata lookup is so hit and
>> miss (tv
>> espiodes frequently given film metadata and vice-versa), having
>> season/episode
>> information would either make it more reliable or as reliable, but
>> certainly
>> not less. Even if the air order differs from the order at tvdb.com
>> (subtitle
>> etc) can always take precedence over episode number.
>>
>> If nothing else it could be inserted as airepisode/airseason - let
>> those of us
>> who get this data use it in the UI and for sorting.
> I'm all for it.
> That’s why I started to look into this.
>
> What I have now is a bit of a hack and takes advantage of the syndicatedepisodenumber being updated with the right info by mythfilldatabase with the xmltv season and episode info.

You can't use syndicatedepisodenumber. That's totally different--just
because it sometimes has something we humans can identify as season and
episode information doesn't mean that it always does. It is the episode
number as defined by the production company--whose format is chosen by
the production company and is generally meaningless for a great number
of shows.

You would need to add fields to hold season and episode to the program
table. Or, just wait for the changes I'm about to start making that
completely change the entire schema we'll be using.

Also, you will have to worry about the fact that your listings provider
will use different numbering schemes than ttvdb.com uses, so you'll
likely need to fix all the metadata grabbers to "do the right thing" in
the face of differences.

Mike
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


torbjorn.jansson at mbox200

May 1, 2012, 12:22 AM

Post #12 of 15 (502 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
> On 04/30/2012 06:25 PM, Torbjörn Jansson wrote:
> >> Since he's no longer here to block it I'd be interested in pushing
> >> forward, at
> >> least in the UK it's pretty decent information. There's no reason to
> >> simply
> >> ignore it, especially when the current metadata lookup is so hit and
> >> miss (tv
> >> espiodes frequently given film metadata and vice-versa), having
> >> season/episode
> >> information would either make it more reliable or as reliable, but
> >> certainly
> >> not less. Even if the air order differs from the order at tvdb.com
> >> (subtitle
> >> etc) can always take precedence over episode number.
> >>
> >> If nothing else it could be inserted as airepisode/airseason - let
> >> those of us
> >> who get this data use it in the UI and for sorting.
> > I'm all for it.
> > That’s why I started to look into this.
> >
> > What I have now is a bit of a hack and takes advantage of the
> syndicatedepisodenumber being updated with the right info by
> mythfilldatabase with the xmltv season and episode info.
>
> You can't use syndicatedepisodenumber. That's totally different--just
> because it sometimes has something we humans can identify as season and
> episode information doesn't mean that it always does. It is the
> episode
> number as defined by the production company--whose format is chosen by
> the production company and is generally meaningless for a great number
> of shows.
>
> You would need to add fields to hold season and episode to the program
> table. Or, just wait for the changes I'm about to start making that
> completely change the entire schema we'll be using.
>
> Also, you will have to worry about the fact that your listings provider
> will use different numbering schemes than ttvdb.com uses, so you'll
> likely need to fix all the metadata grabbers to "do the right thing" in
> the face of differences.
>
> Mike

I know it is not the right way to do it.

But the reason it does work is because of this section in mythfilldatabase.
At around line 460 in xmltvparser.cpp:
----------
else if (info.attribute("system") == "xmltv_ns")
{

int tmp;
QString episodenum(getFirstText(info));
episode = episodenum.section('.',1,1);
episode = episode.section('/',0,0).trimmed();
season = episodenum.section('.',0,0).trimmed();
QString part(episodenum.section('.',2,2));
QString partnumber(part.section('/',0,0).trimmed());
QString parttotal(part.section('/',1,1).trimmed());

pginfo->categoryType = kCategorySeries;

if (!episode.isEmpty())
{
tmp = episode.toInt() + 1;
episode = QString::number(tmp);
pginfo->syndicatedepisodenumber = QString('E' + episode);
}

if (!season.isEmpty())
{
tmp = season.toInt() + 1;
season = QString::number(tmp);
pginfo->syndicatedepisodenumber.append(QString('S' + season));
}
----------

As you can see the episode and season is put into the syndicatedepisodenumber and in my case the only guide data I have is from xmltv (eit doesn’t count since it is not so good)

A proper fix is of course much better than my hack.


_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


dekarl at spaetfruehstuecken

May 1, 2012, 3:40 AM

Post #13 of 15 (496 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 01.05.2012 09:22, Torbjörn Jansson wrote:
> As you can see the episode and season is put into the syndicatedepisodenumber and in my case the only guide data I have is from xmltv (eit doesn’t count since it is not so good)
>
> A proper fix is of course much better than my hack.

The proper fix is super simple. Just find a metadatabase with episode
data that matches your title/subtitle data and a liberal license. Then
write a grabber for that and you're done.

Regards,
Karl
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


stuart at tase

May 1, 2012, 8:56 AM

Post #14 of 15 (492 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On Tuesday 01 May 2012 12:40:36 Karl Dietz wrote:
> On 01.05.2012 09:22, Torbjörn Jansson wrote:
> > As you can see the episode and season is put into the
> > syndicatedepisodenumber and in my case the only guide data I have is
> > from xmltv (eit doesn’t count since it is not so good)
> >
> > A proper fix is of course much better than my hack.
>
> The proper fix is super simple. Just find a metadatabase with episode
> data that matches your title/subtitle data and a liberal license. Then
> write a grabber for that and you're done.

In this case our respective xmltv grabbers already return that information.
There's no need to disregard what our guide data says and go to another
metadata source.
--
Stuart Morgan
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev


dekarl at spaetfruehstuecken

May 23, 2012, 11:25 PM

Post #15 of 15 (376 views)
Permalink
Re: db schema, metadata and season/episode numbering [In reply to]
On 01.05.2012 17:56, Stuart Morgan wrote:
> On Tuesday 01 May 2012 12:40:36 Karl Dietz wrote:
>> On 01.05.2012 09:22, Torbjörn Jansson wrote:
>>> As you can see the episode and season is put into the
>>> syndicatedepisodenumber and in my case the only guide data I have is
>>> from xmltv (eit doesn’t count since it is not so good)
>>>
>>> A proper fix is of course much better than my hack.
>>
>> The proper fix is super simple. Just find a metadatabase with episode
>> data that matches your title/subtitle data and a liberal license. Then
>> write a grabber for that and you're done.
>
> In this case our respective xmltv grabbers already return that information.
> There's no need to disregard what our guide data says and go to another
> metadata source.

Sorry, the summary would have been way better with more details. I've
been talking that through for my own guide with Robert back when he
added the metadata grabbers.

I'm assuming we are talking about the episode number of recordings? For
the program guide it could make sense to store the episode number in its
own dedicated fields.

The episode number of a recording belongs to the metadata grabbers, so
you must have a guide source that uses the same numbering system and
episode order that your series grabber uses.
As your series grabber is likely the thetvdb grabber you must match
their episodes, which most guide sources don't.

The hint at the shared metadatabase is related to importing the unique
content id from the xmltv data, once there is one agreed upon id space.
At that point in time the metadata grabbers can let go of the episode
number fields, but you will likely use a feed of that database in your
guide creation process, too :-)

Regards,
Karl
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://www.mythtv.org/mailman/listinfo/mythtv-dev

MythTV dev RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.