[mythtv] MythMusic database schema suggestion
Brian Lalor
blalor at bravo5.org
Tue Jun 24 10:39:39 EDT 2003
thor wrote:
>>I took a couple of minutes and whacked out what I think is a
>>reasonable facsimile of a better database layout as it pertains to
>>MythMusic. The things that bother me about the current structure (as of
>>0.9.1) is the repetition of data for artist and album information for each
>>track
>
>
> There is currently a clean mapping between a "track" (file on disk), and
> metadata information. Any attempt to squeak efficiencies out of this is
> probably doomed to failure. A new file means what? Deleting a file means
> what? Re-tagging a file with another application (while myth is *not*
> running) means what?
I don't think I understand. Information about the *artist* should not be
stored on a per-track basis. Adding and deleting tracks would be no more
difficult (simpler, in fact, given the current comma-separated list for
playlists) with the more relational schema I proposed. Re-tagging a *file* is
kind of a moot point unless MythMusic is reading the ID3/OGG tags frequently
rather than using what's in the database. If you want to change the data
about a track/artist/disc, then it'll be up to you (you being the user) to
kick or restart MythMusic or the backend.
> playlists are clearly sub-optimal. Someone should be able to improve on
> comma-delimited strings. I await a precise description of how to do this
> better. This would be very welcome, as I am far from a DB/SQL expert ;-)
I'd recommend looking at my schema or DDJ's. They're both easily manipulated
via SQL.
>>I'm much stronger at SQL than I am at C++;
>
> This is, unfortunately, often the difference between theory and practice.
Not really. I'm quite adept at writing data-driven applications in Python,
PHP and even Java. Just not C++/QT.
> If the target application (i.e. MythMusic/C++) needs a read for every track,
> then extra tables for artists and albums actually increase the startup
> burden.
You don't need to perform additonal reads with my schema. The queries that
are being done now can still be performed with a single query that joins the
data from the multiple tables. No additional startup burden, just easier and
cleaner maintenance of the database.
What's being stored in memory? If I've got 10,000 songs (which I probably do
and my collection isn't that big in relative terms), does that mean you're
reading *10,000* records from the database into memory at startup time? I see
that reaching practical limits of memory pretty quickly...
Allow me to look at it from the point of view of the current (0.9.1) "select
music" screen. Startup of the application should be near-instantaneous. Just
draw the screen. Then we go to the "select music" screen. No data's been
loaded from the database because we don't know what the user wants to do yet,
and for the sake of argument no playlist data is yet loaded. The select music
screen is configured to show a tree view with a structure like this:
.
+ All My Music
| + Artist1
| | + Album1
| | + Album2
| | + ...
| | \ AlbumX
| + ...
| \ ArtistX
+ All My Playlists
+ Playlist1
+ ...
\ PlaylistX
... but when we enter the select music screen, we're presented with a fairly
collapsed view, thusly:
.
+ All My Music
| + Artist1
| + ...
| \ ArtistX
+ All My Playlists
+ Playlist1
+ ...
\ PlaylistX
That's two very simple queries:
SELECT ID, Name FROM artists ORDER BY Name
and
SELECT ID, Title FROM playlists ORDER BY Title
Those are probably the fastest queries you could hope for from any database.
Now, if the user cursors down to Artist1 and expands the entry, we just grab
the discs for that artist:
SELECT albums.ID, albums.Title FROM albums, artist_album_link
WHERE artist_album_link.ArtistID = <artist id>
AND albums.ID = artist_album_link.AlbumID
I could go on, but I won't. :-) I guess I just don't see where this layout
adds any complexity. I think the normalized data makes for easier
manipulation outside of MythMusic and that makes it worthwhile, especially
given the number of rows.
Thanks,
B
--
__ ____
/ / / __/ Brian Lalor
/ _ \/__ \ blalor at bravo5.org
/_.__/____/ http://bravo5.org/
More information about the mythtv-dev
mailing list