Re: [sqlite] Tags / keywords support?
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 >http://sqlite.org:8080/cgi-bin/mailman/l
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tags / keywords support?
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
>> 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
-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)
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
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
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