You probably want your "name" fields in each table to be declared name TEXT
COLLATE NOCASE UNIQUE
Your ImageTags should also have an index UNIQUE (TagID, ImageID)
The AUTOINCREMENT keyword in each of the Images and Tags table is unnecessary.
You also want fields containing the same thing to have the same name (unless
you love typing).
CREATE TABLE Images
(
ImageId INTEGER PRIMARY KEY,
name TEXT COLLATE NOCASE UNIQUE NOT NULL,
data BLOB NOT NULL
);
CREATE TABLE Tags
(
TagId INTEGER PRIMARY KEY,
name TEXT COLLATE NOCASE UNIQUE NOT NULL
);
CREATE TABLE ImageTags
(
ImageId INTEGER NOT NULL REFERENCES Images ON DELETE CASCADE,
TagId INTEGER NOT NULL REFERENCES Tags ON DELETE CASCADE,
PRIMARY KEY (ImageId, TagId),
UNIQUE (TagId, ImageID)
);
Finding Images with various AND (intersect) and OR (UNION) of Tags is very
efficient.
select *
from Images
where ImageId in (select ImageId from ImageTags join Tags on (TagId) where
name='summer'
intersect
select ImageId from ImageTags join Tags on (TagId) where
name='house');
which will find all summer house images.
select *
from Images
where ImageId in (select ImageID
from (select ImageId from ImageTags join Tags on (TagId)
where name in ('summer', 'winter')
intersect
select ImageId from ImageTags join Tags on (TagId)
where name='cottage')
union
select ImageID
from (select ImageId from ImageTags join Tags on (TagId)
where name in ('spring', 'fall')
intersect
select ImageId from ImageTags join Tags on (TagId)
where name='campground')
);
which will find all ((summer or winter) cottage's) and ((spring or fall)
campground's)
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Constantine Yannakopoulos
>Sent: Friday, 5 December, 2014 14:39
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Tags / keywords support?
>
>On Fri, Dec 5, 2014 at 10:49 PM, Krzysztof <dib...@wp.pl> wrote:
>
>> Hi,
>>
>> I need extra field which contain tags / keywords describing such
>> record. Then I want to find record ID by using tags. I know that it is
>> easy with TEXT field and LIKE condition but I have issue with update
>> speed. Let say that you have collection of photos and you want to add
>> tags like "summer", "beach" (with ignoring duplicates). But then you
>> want to remove from collection tags "beach". It is quite expensive
>> (find, remove using native language then update record with new
>> value). I'm reading about FTS but I think that it is overloaded for my
>> needs and I didn't find functions for remove keywords.
>> For example PostgreSQL has special field HSTORE which is list of
>> key=>value type field. It is not suitable for tags but it is just
>> example for special data types. HSTORE has routines for update (with
>> ignoring duplicates), removing, search, enumerates etc.
>>
>
>Why not normalize your design and store tags per image in a separate
>junction table?
>
>CREATE TABLE Images(
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name STRING,
> data BLOB);
>
>CREATE TABLE Tags(
> id INTEGER PRIMARY KEY AUTOINCREMENT,
> name STRING);
>
>CREATE TABLE ImageTags(
> ImageId INTEGER,
> TagId INTEGER,
> PRIMARY KEY (ImageId, TagId),
> FOREIGN KEY (ImageId) REFERENCES Images (ID) ON DELETE CASCADE,
> FOREIGN KEY (TagId) REFERENCES Tags (ID) ON DELETE CASCADE);
>
>You can easily search for images that have a specific tag name with a
>simple join:
>
>SELECT
> Images.id,
> Images.name
>FROM
> Images
> JOIN ImageTags ON Images.id = ImageTags.ImageId
> JOIN Tags ON Tags.Id = ImageTags.TagId
>WHERE
> Tags.Name IN ('MyTag1', 'MyTag2', ...);
>
>To add a tag to an image, you add it into the Tags table if it doesn't
>exist and then you add the appropriate junction record into ImageTags.
>To remove a tag from an image you just delete the corresponding junction
>record.
>To remove a tag from all possible images you delete the corresponding tag
>record and all junctions will be cascade-deleted.
>
>You can go half-way and merge the tables Tags and ImageTags into one:
>
>CREATE TABLE ImageTags(
> ImageId INTEGER,
> Tag STRING,
> PRIMARY KEY (ImageId, Tag),
> FOREIGN KEY (ImageId) REFERENCES Images (ID) ON DELETE CASCADE);
>
>But then tag strings will be duplicated if a tag is assigned to more than
>one image, which is probably OK if they are relatively short.
>
>Regards.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
