Hello --
I've got a SQL problem that I'm guess is about half general and half
SQLite specific. I'm pretty new to SQLite, familiar with basic SQL
(mostly from MySQL), and I'm accessing SQLite through C and Perl.
The problem I'm working on relates to tagging music for some music
management software I'm thinking about. It currently exists mostly in
my head, but it will be released as open source if it pans out.
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.
As I see it, this means that I need to be able to search the tags
table by three different song_id's (id_exact, id_album, id_artist). I
can come up with several ways of doing this: joins, subselects, or
unions. For example, the join approach would need to do an outer join
between the song and tag tables three times, once on song_id=id_exact,
once on song_id=id_album, and finally on song_id=id_artist.
(note that if a tag exists for id_exact, it overrides the results for
id_album and id_artist: the cascade is conditional on no tag found.)
It strikes me that this is probably going to be pretty inefficient.
My requirements are not for incredible speed, but I probably need to
be able to search through a database of 10,000 songs and 100,000 tags
in something less than a tenth of a second on a typical desktop PC.
Is there a way of approaching this that works better with SQLite than
other ways? (ie, are intersects optimized but subselects not?) Is
there a standard pattern for doing this sort of cascade? My guess is
that there are probably a lot of ways of solving this problem, but
that one of them is going to be strikingly more efficient than the
others, and which one is best might be specific to SQLite.
Thanks for any suggestions,
Nathan Kurz
[EMAIL PROTECTED]
ps. I'd like to be able to do this search in a single SQL statement
(rather than multiple queries) because at some point I need to
start doing intersects of multiple tag searches. Another bridge
I'll cross when I get to it!
