On Tue, Feb 22, 2005 at 09:38:53PM -0700, John LeSueur wrote:
> >In my model, a 'song' record consists of a unique artist/album/title
> >A 'tag' is a name/value pair associated with a 'song':
> > Song: song_id artist album title
> > Tag: song_id name value
> >Searching for the tags associated with a given song would be pretty
> >easy, but complicating matters I need for tags to 'cascade'.
> >
> >This is so that a tag can be set for an entire album, and then
> >overridden if necessary on a song by song basis. For example, if I am
> >checking the value of the tag 'rating' for Artist/Album/Title, but no
> >such tag is found, I want to fall back on the tag for
> >Artist/Album/NULL; failing that I search for Artist/NULL/NULL.
...
> select
> coalesce(songtag.name, albumtag.name, artisttag.name) as name,
> -- you might need to use a case statement instead if the value of a
> tag can be null
> -- in that case this would return a value that did not match the tag
> coalesce(songtag.value, albumtag.value, artisttag.value) as value
> from
> song
> left outer join
> tag songtag
> on song.id = tag.object_id
> left outer join
> tag artisttag
> on song.artist_id = tag.object_id
> left outer join
> tag albumtag
> on song.album_id = tag.object_id
> where
> song.id = ?
> and tag.name = ?
Thanks for the response (and to you too, Dan)! I hadn't known about
coalesce as a builtin, so that should be useful.
I was also thinking that there should be some way to speed up the join
a bit by short-circuiting if the first join finds a tag. Something like:
SELECT coalesce(song_tag.name, album_tag.name, artist_tag.name) as name,
coalesce(song_tag.value, album_tag.value, artist_tag.value) as value,
FROM song
LEFT OUTER JOIN tag AS song_tag
ON song.song_id = song_tag.id
LEFT OUTER JOIN tag as album_tag
ON song_tag.id ISNULL AND <---
song.artist_id = album_tag.id
LEFT OUTER JOIN tag as artist_tag
ON album_tag.id ISNULL AND <---
song.artist_id = artist_tag.id
WHERE
song.id = ? AND
tag.name = ?
(guessing at query, haven't actually tried it yet)
If this is internally optimized the way it could be (if I understand
correctly), it could just fill in NULLs for the 'album_tag' and
'artist_tag' columns without having to do a look up. Do you know if
this is how it works, or if there is some way to make it do that?
> This could be surprisingly fast if you do it like itunes does and
> load the entire song database into memory(which sqlite supports),
> and using a file(itunes uses xml) only to load initially, and store
> permanent changes. There's probably something even simpler you can
> do, but I can't think of it at this moment.
I'm going to be accessing this database from a couple different
places: as a plugin to a music player (and hence long-lived) and from
a small client program accessible via a hot key (short-lived). So I
think that having the XML file as a primary store might not work well
for me (at least from the short-lived client).
But maybe I'm not really understanding the advantages of the in-memory
database. Is it in some way inherently faster on lookups than just
setting SQLite to use a really large cache?
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
