On Wed, Mar 5, 2014, at 09:59 AM, Chris wrote:
> 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
Having realised the above sounds very much like "please do my homework
assignment for me", I should point out that that's not the case. I've
been a C, C++ and Tcl developer for many years, but have only ever
scratched the surface of sqlite. This is me trying to delve a little
deeper while avoiding going down too many wrong turns at the beginning.
Chris
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
