Mailing List Archive: MythTV: Dev

database schema

Index | Next | Previous | View Threaded

mythtv at zencow

Mar 14, 2003, 4:11 AM

Post #1 of 15 (2257 views)
Permalink

database schema

Hey guys...

I was wondering if there was a reason that the
TIMESTAMP field type was used instead of the
DATETIME field type for all the time/date fields.

Some things could probably be simplified if all
of those fields were of type DATETIME instead of
TIMESTAMP.

Basically, updates to records, like in the recorded
table, would not explicitly need the starttime
specified as an update value when updating other
fields in those records.

You would really only want to use a TIMESTAMP data
type in a field if you wanted it to automatically
update to "now" whenever the record was modified.

Since it didn't look like any of the fields where
the TIMESTAMP datatype is being used are for
tracking record changes, I went ahead and made the
change in my own schema and things seem to be ok.

This change will make it easier to make manual
edits to tables whenever things needed fixing
during testing.

I can point you to the relevant mysql documentation
if you need more info on this.

-Chris

mdz at debian

Mar 14, 2003, 6:58 PM

Post #2 of 15 (2223 views)
Permalink

Re: database schema [In reply to]

On Fri, Mar 14, 2003 at 03:11:28AM -0800, Chris Palmer wrote:

> I was wondering if there was a reason that the
> TIMESTAMP field type was used instead of the
> DATETIME field type for all the time/date fields.
>
> Some things could probably be simplified if all
> of those fields were of type DATETIME instead of
> TIMESTAMP.

I believe that all of the TIMESTAMP fields currently in the schema would
make more sense as DATETIME fields. This sounds like a good candidate for
some post-0.8 cleanup if you would like to send a patch.

--
- mdz

mikej at carmelfly

Mar 15, 2003, 2:54 AM

Post #3 of 15 (2222 views)
Permalink

Re: database schema [In reply to]

Hmm.. well it depends on what the intention of the field is...

From the MySQL site:

-- SNIP --
The TIMESTAMP column type provides a type that you can use to
automatically mark INSERT or UPDATE operations with the current date and
time. If you have multiple TIMESTAMP columns, only the first one is
updated automatically.
-- SNIP --

If this is what you are going for, then a timestamp is good, if you DO
NOT want the field to auto-update when the fields is updated or
inserted, then you MUST use a DATETIME...

To which portion of the SQL Schema are you refering?

- Mike

On Fri, 2003-03-14 at 17:58, Matt Zimmerman wrote:
> On Fri, Mar 14, 2003 at 03:11:28AM -0800, Chris Palmer wrote:
>
> > I was wondering if there was a reason that the
> > TIMESTAMP field type was used instead of the
> > DATETIME field type for all the time/date fields.
> >
> > Some things could probably be simplified if all
> > of those fields were of type DATETIME instead of
> > TIMESTAMP.
>
> I believe that all of the TIMESTAMP fields currently in the schema would
> make more sense as DATETIME fields. This sounds like a good candidate for
> some post-0.8 cleanup if you would like to send a patch.

mythtv at zencow

Mar 15, 2003, 4:51 AM

Post #4 of 15 (2223 views)
Permalink

Re: database schema [In reply to]

I was thinking that this should apply to all of the current
TIMESTAMP fields. There are currently no DATETIME fields,
but I can't see a use for any of them to be TIMESTAMP fields,
since none of them have to do with keeping track of when a
row of data is updated. They all have to do with show times,
which are known values.

The TIMESTAMP fields also make it difficult to manually edit
the tables, since the database will automatically change the
value of the starttime for a recording, which just doesn't make
any sense at all, especially since it's part of the unique key
to identify a recording.

My guess is that when the schema was first designed, that it
was assumed that the TIMESTAMP datatype was the proper type
based solely on its name. As a datatype it is interchangeable
with the DATETIME field, but it has other specific built-in
functionality which makes it useful for logging/tracking of
database updates.

-Chris

Mike Javorski wrote
> Hmm.. well it depends on what the intention of the field is...
>
> >From the MySQL site:
>
> -- SNIP --
> The TIMESTAMP column type provides a type that you can use to
> automatically mark INSERT or UPDATE operations with the current date and
> time. If you have multiple TIMESTAMP columns, only the first one is
> updated automatically.
> -- SNIP --
>
> If this is what you are going for, then a timestamp is good, if you DO
> NOT want the field to auto-update when the fields is updated or
> inserted, then you MUST use a DATETIME...
>
> To which portion of the SQL Schema are you refering?
>
> - Mike
>
>
>
> On Fri, 2003-03-14 at 17:58, Matt Zimmerman wrote:
> > On Fri, Mar 14, 2003 at 03:11:28AM -0800, Chris Palmer wrote:
> >
> > > I was wondering if there was a reason that the
> > > TIMESTAMP field type was used instead of the
> > > DATETIME field type for all the time/date fields.
> > >
> > > Some things could probably be simplified if all
> > > of those fields were of type DATETIME instead of
> > > TIMESTAMP.
> >
> > I believe that all of the TIMESTAMP fields currently in the schema would
> > make more sense as DATETIME fields. This sounds like a good candidate for
> > some post-0.8 cleanup if you would like to send a patch.
>
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev [at] snowman
> http://www.snowman.net/mailman/listinfo/mythtv-dev
>

mdz at debian

Mar 15, 2003, 7:40 AM

Post #5 of 15 (2208 views)
Permalink

Re: database schema [In reply to]

On Sat, Mar 15, 2003 at 01:54:05AM -0800, Mike Javorski wrote:

> Hmm.. well it depends on what the intention of the field is...

Yes, I know, and I am saying that I looked at them, and in every case I saw,
the intention of those fields is only to store a date and time together, and
not to have an automatically-updated timestamp.

> To which portion of the SQL Schema are you refering?

mizar:[~/src/deb/mine/upstream/mythtv] grep TIMESTAMP database/mc.sql
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP NOT NULL,
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP NOT NULL,
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP NOT NULL,
preferstarttime TIMESTAMP NOT NULL,
preferendtime TIMESTAMP NOT NULL,
dislikestarttime TIMESTAMP NOT NULL,
dislikeendtime TIMESTAMP NOT NULL,
starttime TIMESTAMP NOT NULL,
endtime TIMESTAMP NOT NULL,
starttime TIMESTAMP NOT NULL,
starttime TIMESTAMP NOT NULL,

Those columns.

--
- mdz

dert at pobox

Mar 15, 2003, 12:56 PM

Post #6 of 15 (2209 views)
Permalink

Re: database schema [In reply to]

I'm late to the party but I just wanted to offer more support for
updating the schema. My last few requests for changes that would
actually impact performance/elegance were rebuffed, so I expected
DATETIME versus TIMESTAMP to be met with even less ethusiasm.

ijr at po

Mar 15, 2003, 10:05 PM

Post #7 of 15 (2190 views)
Permalink

Re: database schema [In reply to]

On Saturday 15 March 2003 06:51 am, Chris Palmer wrote:
> My guess is that when the schema was first designed, that it
> was assumed that the TIMESTAMP datatype was the proper type
> based solely on its name. As a datatype it is interchangeable
> with the DATETIME field, but it has other specific built-in
> functionality which makes it useful for logging/tracking of
> database updates.

I chose the timestamp datatype because I liked its storage format more, and
it's half the size of the datetime datatype. Modification to the records in
question rarely happens, so I figured it wasn't that big of a deal.

Isaac

mikej at carmelfly

Mar 15, 2003, 11:44 PM

Post #8 of 15 (2238 views)
Permalink

Re: database schema [In reply to]

Well not wanting to step on your toes Isaac, but I see two items on this
issue worth commenting on:

- TIMESTAMP is limited to POST 1970 & PRE 2037 dates. Granted the 2037
limit isn't an issue, but if we were to (eventually) indicate things
like original air date of a show (such as Dick Van Dyke or Scooby-Doo,
both of which were made in the 60's), the system would go foo-bar on the
dates.

- As far as size is concerned, its really at the "microscopic level" in
this instance.. I mean 8 BYTES vs. 4. We aren't talking a HUGE deal
(considering most of us run machines with more than enough space).

Anyway.. if I get some time, I will look around and maybe send you an
"all-encompassing" patch (that updates everything that needn't be a
timestamp to be a datetime, and adjust the SQL queries accordingly) for
your approval. Def. going to be for post-.80 (don't want to slow the
release down if I can avoid it)

- Mike

On Sat, 2003-03-15 at 21:05, Isaac Richards wrote:
> On Saturday 15 March 2003 06:51 am, Chris Palmer wrote:
> > My guess is that when the schema was first designed, that it
> > was assumed that the TIMESTAMP datatype was the proper type
> > based solely on its name. As a datatype it is interchangeable
> > with the DATETIME field, but it has other specific built-in
> > functionality which makes it useful for logging/tracking of
> > database updates.
>
> I chose the timestamp datatype because I liked its storage format more, and
> it's half the size of the datetime datatype. Modification to the records in
> question rarely happens, so I figured it wasn't that big of a deal.
>
> Isaac
>
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev [at] snowman
> http://www.snowman.net/mailman/listinfo/mythtv-dev

dert at pobox

Mar 16, 2003, 12:08 AM

Post #9 of 15 (2231 views)
Permalink

Re: database schema [In reply to]

Original airdates are only available as years and are currently stored as such.

The size issue has consistently been shrugged off, but what I would rather look
at is the use of fixed-length records. If we moved strings out of the program
table, many queries would run much faster. This may not seem like an issue when
you only have 60 channels and only keep a week of program data in your DB, but
when you have multiple inputs, each with several hundred channels and a month of
future program data, interactivity suffers.

Also, we've verified that MySQL 3.23.54a doesn't optimize some queries well
enough for them to run with decent interactivity, while 3.23.55 does fine.
I really think that version 3.23.55 should be made a requirement, or at least
a recommendation. Especially in light of the fact that patches to resolve
these differences have been rejected.

Thanks for offering to fix the TIMESTAMPs...

#if Mike Javorski /* Mar 15, 22:44 */
> - TIMESTAMP is limited to POST 1970 & PRE 2037 dates. Granted the 2037
> limit isn't an issue, but if we were to (eventually) indicate things
> like original air date of a show (such as Dick Van Dyke or Scooby-Doo,
> both of which were made in the 60's), the system would go foo-bar on the
> dates.
>
> - As far as size is concerned, its really at the "microscopic level" in
> this instance.. I mean 8 BYTES vs. 4. We aren't talking a HUGE deal
> (considering most of us run machines with more than enough space).
#endif /* mikej [at] carmelfly */

mikej at carmelfly

Mar 16, 2003, 12:29 AM

Post #10 of 15 (2214 views)
Permalink

Re: database schema [In reply to]

Actually, I CAN get actual original dates, but whatever..

In what sense are you speaking of "strings". Do you mean for
titles,series and things of that nature? if so there is the problem of
standardization.. the ideal thing to do would be to have each series
have an official "number" and do everything numerically (this is how
TiVo manages everything, with numerical record ID's). This is NOT a
small task though, and would take some creative coordination..

- Mike

On Sat, 2003-03-15 at 23:08, Andy Davidoff wrote:
> Original airdates are only available as years and are currently stored as such.
>
> The size issue has consistently been shrugged off, but what I would rather look
> at is the use of fixed-length records. If we moved strings out of the program
> table, many queries would run much faster. This may not seem like an issue when
> you only have 60 channels and only keep a week of program data in your DB, but
> when you have multiple inputs, each with several hundred channels and a month of
> future program data, interactivity suffers.
>
> Also, we've verified that MySQL 3.23.54a doesn't optimize some queries well
> enough for them to run with decent interactivity, while 3.23.55 does fine.
> I really think that version 3.23.55 should be made a requirement, or at least
> a recommendation. Especially in light of the fact that patches to resolve
> these differences have been rejected.
>
> Thanks for offering to fix the TIMESTAMPs...
>
>
> #if Mike Javorski /* Mar 15, 22:44 */
> > - TIMESTAMP is limited to POST 1970 & PRE 2037 dates. Granted the 2037
> > limit isn't an issue, but if we were to (eventually) indicate things
> > like original air date of a show (such as Dick Van Dyke or Scooby-Doo,
> > both of which were made in the 60's), the system would go foo-bar on the
> > dates.
> >
> > - As far as size is concerned, its really at the "microscopic level" in
> > this instance.. I mean 8 BYTES vs. 4. We aren't talking a HUGE deal
> > (considering most of us run machines with more than enough space).
> #endif /* mikej [at] carmelfly */
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev [at] snowman
> http://www.snowman.net/mailman/listinfo/mythtv-dev

dert at pobox

Mar 16, 2003, 12:37 AM

Post #11 of 15 (2230 views)
Permalink

Re: database schema [In reply to]

#if Mike Javorski /* Mar 15, 23:29 */
> Actually, I CAN get actual original dates, but whatever..

Really? Apologies; please patch the schema for that too, then! ;-)

> In what sense are you speaking of "strings". Do you mean for
> titles,series and things of that nature? if so there is the problem of
> standardization.. the ideal thing to do would be to have each series
> have an official "number" and do everything numerically (this is how
> TiVo manages everything, with numerical record ID's). This is NOT a
> small task though, and would take some creative coordination..
#endif /* mikej [at] carmelfly */

It sounds to me like you're talking about episode numbers, which I was
not aware TiVo stored (it doesn't seem to be in the TiVo UI). Here in
tv_grab_na-land, I don't see episode numbers either, so that's why I
didn't parse/insert them into the DB. If you've got 'em, go for it.

If you're talking about "breakeation" or the hashing of strings, or
reference counting, then you're talking about more sophisticated
solutions to the problem.

I'm just talking about the title, subtitle, and description fields of
the program table. Long strings which could be stored elsewhere to
reduce table/index sizes, seek times, and so on. We'd replace them
with integers (in the program table) which would be foreign keys into
the string-store. Reference counting is "free", but I doubt it'd be
worth the added complexity. I would call this a "small task".

mythtv at zencow

Mar 16, 2003, 4:59 AM

Post #12 of 15 (2199 views)
Permalink

Re: database schema [In reply to]

Andy Davidoff wrote
> It sounds to me like you're talking about episode numbers, which I was
> not aware TiVo stored (it doesn't seem to be in the TiVo UI). Here in
> tv_grab_na-land, I don't see episode numbers either, so that's why I
> didn't parse/insert them into the DB. If you've got 'em, go for it.

If you have a TiVo, you can see episode numbers, *if* that info is
available (sometimes it isn't). You can find out an episode number
by looking at the extended program info page, by hitting "Enter" on
your remote when looking at the show description page.

I just learned about that page in the last couple months. It's also
useful when the show description is too long to view the whole thing
(not too often, but it happens).

-Chris

wadcyr8_197 at hotmail

May 13, 2007, 1:32 PM

Post #13 of 15 (1409 views)
Permalink

Re: database schema [In reply to]

Patrick Reynolds a �crit :
> On Sun, 13 May 2007, wad wrote:
>
>
>> I'm looking for a diagram of the mythconverg database. I need it in
>> order to to a presentation of MythTV. Is that available or does anyone
>> know how can I make it ?
>>
>
> mysqldump -u root -d mythconverg
>
> It's 770 fields in 80 tables, at least on my system. I'm not sure what
> kind of diagram you hope to get.
>
> --Patrick
In fact it's more a conceptual diagram I hope, like a Merise
representation :d
therefore, mysqldump is a good idea, but I prefer the use of phpmyadmin
for things like that :D

Wad

steve.p.daniels at googlemail

May 13, 2007, 2:07 PM

Post #14 of 15 (1412 views)
Permalink

Re: database schema [In reply to]

wad wrote:
> Patrick Reynolds a �crit :
>> On Sun, 13 May 2007, wad wrote:
>>
>>
>>> I'm looking for a diagram of the mythconverg database. I need it in
>>> order to to a presentation of MythTV. Is that available or does anyone
>>> know how can I make it ?
>>>
>>
>> mysqldump -u root -d mythconverg
>>
>> It's 770 fields in 80 tables, at least on my system. I'm not sure what
>> kind of diagram you hope to get.
>>
>> --Patrick
> In fact it's more a conceptual diagram I hope, like a Merise
> representation :d
> therefore, mysqldump is a good idea, but I prefer the use of phpmyadmin
> for things like that :D
>
> Wad
>

I suppose if you installed MySQL Workbench, it might be able to crunch
through the database and show you it in a pretty format :-)

It's quite powerful, but has had a it's fair share of bugs in it's time.
So I wish you luck with it!

http://www.mysql.com/products/tools/workbench/

Let me know how you get one,

Steve Daniels
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev

steve.p.daniels at googlemail

May 13, 2007, 2:13 PM

Post #15 of 15 (1400 views)
Permalink

Re: database schema [In reply to]

wad wrote:
> Patrick Reynolds a �crit :
>> On Sun, 13 May 2007, wad wrote:
>>
>>
>>> I'm looking for a diagram of the mythconverg database. I need it in
>>> order to to a presentation of MythTV. Is that available or does anyone
>>> know how can I make it ?
>>>
>>
>> mysqldump -u root -d mythconverg
>>
>> It's 770 fields in 80 tables, at least on my system. I'm not sure what
>> kind of diagram you hope to get.
>>
>> --Patrick
> In fact it's more a conceptual diagram I hope, like a Merise
> representation :d
> therefore, mysqldump is a good idea, but I prefer the use of phpmyadmin
> for things like that :D
>
> Wad
>

Re: my last post on MySQL Workbench, you'll want to load it up, and go
to the Database menu, then go to "Reverse Engineering", fill in your
database details, and keep clicking next selecting all the bits and bobs.

At the end of it, you'll have all 80 odd table shown in the screen, you
can drag and drop these round the screen, clicking the arrow at the top
right of each table will show it's contents. Then it's up to you to
somehow order them, move em round etc. till it looks pretty. If you
manage it, I would be *very* interested in seeing the final, pretty result.

HTH

Steve Daniels
_______________________________________________
mythtv-dev mailing list
mythtv-dev [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-dev

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads