Re: [sqlite] Tags / keywords support?

[Krzysztof]

Thanks Constantine! Great idea with table contains tags per record!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tags / keywords support?

[Constantine Yannakopoulos]

On Sat, Dec 6, 2014 at 2:10 AM, Keith Medcalf  wrote:

>
> You probably want your "name" fields in each table to be declared name
> TEXT COLLATE NOCASE UNIQUE
>

​Probably, but my statements were pseudo-, to illustrate the model I'm
proposing. I didn't even bother to try to ​execute them.


> Your ImageTags should also have an index UNIQUE (TagID, ImageID)
>

​Why? That's the primary key reversed. Primary keys are unique by
definition, aren't they? As for "access path" by tagid first, doesn't the
skip-scan optimization (http://www.sqlite.org/optoverview.html#skipscan)
take care of this?
​


> The AUTOINCREMENT keyword in each of the Images and Tags table is
> unnecessary.
>

Probably, although it should be noted that it has a subtle difference from
INTEGER NOT NULL PRIMARY KEY in that it is guaranteed to never reuse values
from deleted records (https://www.sqlite.org/autoinc.html).
​


> You also want fields containing the same thing to have the same name
> (unless you love typing).
>

​You are probably referring to the ON clause. Actually I love explicitness​
​ and I tend to name fields according to their function, not the data they
contain. IDs are IDs, names are names etc. in all tables. I guess it's a
matter of personal taste but it can come handy when you are programming in
an object-oriented language and can have a hierarchy of "entities" and
their fields.​ You can create abstract ancestors that have these fields. Of
course you can always use aliases.


> 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');
>

​I assume the query planner will generate identical (optimal) plans for
in-based queries and their semantically equivalent join-based ones where
UNION becomes OR, INTERSECT becomes AND and MINUS becomes AND NOT.​
 ​If this is true​
​ it is again a matter of personal taste. ​
​If this is not I would like
​you ​
to
​elaborate please.

​Regards.​
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tags / keywords support?

[Keith Medcalf]

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) REFERENC

Re: [sqlite] Tags / keywords support?

[Constantine Yannakopoulos]

On Fri, Dec 5, 2014 at 10:49 PM, Krzysztof  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] Tags / keywords support?

[Krzysztof]

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.

Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users