I'm a relative novice to sqlite (or sql in general), but I do understand
the value of variable substitution when building queries:
e.g.
set someValue 23
db eval {SELECT something FROM myTable WHERE value=$someValue}
It feels like there should be a comparable solution for IN, passing
instead a Tcl list, but I've failed to find it documented or had any
trial-and-errorr success.
e.g.
set someTags {1 23 45}
db eval {SELECT something FROM myTable WHERE value IN ($someTags)}
If that's not a supported feature, what would be the recommended and
safe way of building the query?
As that's just a detail of a novice's attempt to implement a part of
something that most likely could more correctly be done in a different
way, here's more detail of the overall plan (a light-weight iTunes-like
library):
Given a trio of tables created as:
db eval {
CREATE TABLE IF NOT EXISTS media(
id INTEGER PRIMARY KEY AUTOINCREMENT,
filename TEXT
-- further fields removed
);
CREATE TABLE IF NOT EXISTS tags(
id INTEGER PRIMARY KEY AUTOINCREMENT,
tag TEXT,
unique(tag)
);
CREATE TABLE IF NOT EXISTS tagUsage(
mediaId INTEGER,
tagId INTEGER,
unique(mediaId,tagId)
);
CREATE TRIGGER IF NOT EXISTS tagCleanup AFTER DELETE ON tags
BEGIN
DELETE FROM tagUsage WHERE tagUsage.tagId=OLD.id;
END;
}
'media' describes an audio or video file.
'tags' is just a list of words
'tagUsage' associates tags to media (a media can have zero or more tags,
a tag can belong to zero or more media).
At some point, I'm going to want to write queries that answer the
questions:
1. What media files have at least *one* of this set of tags?
2. What media files have *all* of this set of tags?
That feels like the sort of thing that it should be possible to write in
a single query, but I keep coming back to an initial query, followed by
some processing in code, with a follow up query. I'm sure there's a more
sql-ish way.
Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
