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!