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
