Re: [sqlite] Tags / keywords support?

2014-12-05 Thread 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  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

Re: [sqlite] Tags / keywords support?

2014-12-05 Thread 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?

2014-12-05 Thread 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

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Max Vlasov 
>> I once implemented a virtual table "allvalues" that outputs all
>> database values with (hope self-explaining) fields
>>
>> TableName, TableRowId, FieldName, Value
>
> Could you expand on how you coped with the underlying database
> changing, and how you mapped virtual table rowids to the actual
> database records?
>

This particular implementation was intended to be used as a
Select-only wrapper so it just iterates through every sqlite_master
table and every row of each table. I didn't support update and insert.
Rowid of this virtual table is compound bit mask starting with table
bits (able to fit the number of tables of this db), field bits
(maximum possible number of fields in a table) and the rest is rowid
of the particular table. So in theory this method won't work for
tables containing large 64-bit ids where there are not enough bits to
be used for table number and field number.

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

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Roger Binns 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 12/05/2014 01:24 AM, Max Vlasov wrote:
> I once implemented a virtual table "allvalues" that outputs all 
> database values with (hope self-explaining) fields
> 
> TableName, TableRowId, FieldName, Value

Could you expand on how you coped with the underlying database
changing, and how you mapped virtual table rowids to the actual
database records?

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlSB+fcACgkQmOOfHg372QRZ0QCdHbaDwwE0mrE8SaITJhn5lB7K
KugAoJaBjpLVj4zemq9kqS1UsCAyvjuc
=1Jet
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Where sqlite vtables are used? (was: Search for text in all tables)

2014-12-05 Thread Alek Paunov 

On 05.12.2014 12:32, Dominique Devienne wrote:

On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov  wrote:


Not particularity the answer to your question, but rather a method you
or others might use.
I once implemented a virtual table "allvalues" that outputs all
database values with (hope self-explaining) fields

   TableName, TableRowId, FieldName, Value

that allows also exploring unknown complex databases.



Thanks for sharing Max! That's a great idea. I'd never would have thought
of that.

I agree with you, Sqlite's virtual tables are great. --DD


Where have you seen/developed interesting vtable implementations?

Please respond with a short description and/or link to sqlite vtable 
source locations you know :-)


Kind regards,
Alek

P.S. Few weeks ago, I tried to materialize few internet searches under 
sqlite_addon tag in openhub.net (formerly ohloh.net) DB, but the list is 
too short yet:


https://www.openhub.net/tags/sqlite_addon
https://www.openhub.net/tags/sqlite_vtable

Better ideas about where to collect/categorize available extensions?

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

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Dominique Devienne 
On Fri, Dec 5, 2014 at 10:24 AM, Max Vlasov  wrote:

> Not particularity the answer to your question, but rather a method you
> or others might use.
> I once implemented a virtual table "allvalues" that outputs all
> database values with (hope self-explaining) fields
>
>   TableName, TableRowId, FieldName, Value
>
> that allows also exploring unknown complex databases.


Thanks for sharing Max! That's a great idea. I'd never would have thought
of that.

I agree with you, Sqlite's virtual tables are great. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

2014-12-05 Thread Max Vlasov 
On Thu, Dec 4, 2014 at 10:44 AM, Baruch Burstein  wrote:
>
> Is it possible to somehow search for/replace a string in all columns of all
> tables?
>

Not particularity the answer to your question, but rather a method you
or others might use.
I once implemented a virtual table "allvalues" that outputs all
database values with (hope self-explaining) fields

  TableName, TableRowId, FieldName, Value

that allows also exploring unknown complex databases. Take for example
places.sqlite of Mozilla Firefox. If you want to explore where it
saves your visited site, you can use the query

  SELECT Distinct TableName, FieldName FROM allvalues where Value like
"%http://%;

Sqlite's virtual tables are a great tool since with a little effort on
the developer side the newly created entity starts working as a first
class citizen of the sqlite engine. Compare this to a quick hack that
outputs all raw data from a specific database to a specific media.

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