Boolean search for keywords possible?

[hindewu]

Hi folks,
This is my first question in this forum :
Today I installed XnViewMP and tagged 10 jpgs in IPTC under "keywords" with "green" ...
just for a test.
Then I tagged one of this 10 additional with "blue".
Then I searched with "green blue" and also with "green, blue" and XnviewMP found
all the 10 files, instead of only this one, which contains green AND blue.
Maybe IPTC was the wrong place or I made a mistake....
but can anyone tell me, if a search with AND is possible in XnViewMP or in XnView?
In a database with thousends of pictures this is essential to me.

[xnview]

do you use 'match all of the following conditions'?

[hindewu]

Hi Pierre,
Thanx for your reply.

do you use 'match all of the following conditions'?

Yes, I do!

I just tagged 5 jpgs under Metadata/IPTC/Keywords!
jpg 1,2 and 3 with keyword "red"...
jpg 3,4 and 5 with leyword blue....
so only jpg 3 has red AND blue.

Here is the screen, when I searched for red...everything is fine:

contains red.jpg

Here is the screen, when I searched for blue...everything is fine too:

blue.jpg

But when I search for "red blue" (which is only jpg 3) II get this:

red blue.jpg

the search field is empty.
We have two choices, when we add IPTC- all fields-
1. if any of
or
2. contains
In both cases, the searchfield remains empty!

[xnview]

try by adding 2 item search

[hindewu]

try by adding 2 item search

What are items in this case?...
Are "keywords" , "categories" an "date/time" different items inside the "Edit IPTC window"!?
Or are different items e.g. "EXIF", "IPTC", "file size", "rating" etc.?
Would be great, if you could give an example.


P.S.: Another point, Pierre...I forgot to ask...is it possible to hold all the data
of the database inside the jpgs...like Exif and IPTC data?
What I still haven't understood is, what kind of tags are hold in the jpgs and
and what kind are hold in a special file in the XnView-Folder.

[xnview]

Add 2 'IPTC:all fields', one with blue, and one with red

P.S.: Another point, Pierre...I forgot to ask...is it possible to hold all the data
of the database inside the jpgs...like Exif and IPTC data?
What I still haven't understood is, what kind of tags are hold in the jpgs and
and what kind are hold in a special file in the XnView-Folder.

If you use 'export rating/label/keywords', all is written in the file & database..

[hindewu]

@pierre
Merci beaucoup et bon nuit.

[danjcla]

I'm having the sort of opposite problem, but maybe related - that is, categories having sloppy matching.

So for example we have a bride and a groom. We have photos of them together and each by themselves. It should be possible to have two categories "bride" and "groom", and then derive the 3rd option, bride+groom, via a search.

However what seems to happen (and I'm a new user and could easily be making a simple error) in searches is that you can't get either of them alone [insert joke in poor taste here]:

"bride" searched for: "bride" and bride+groom pictures returned
"groom" searched for: "groom" and bride+groom pictures returned

Is there any way to search for "bride NOT groom" or "bride AND <picture has 1 category>" or something like that?

I should add that none of the XnView MP to/from In-file metadata stuff seems to be working consistently for me, so syncing the categories to file keywords and then using the possibly not non-greedy search available for those wouldn't work out.

As I'm thinking the answer to this will be no I'm working on an ugly hack, http://newsgroup.xnview.com/viewtopic.p ... 60#p124460 if interested. Oh and might as well stick this here...

SELECT Pathname || Filename AS Fullpath, Label
FROM Tags
JOIN TagsTree on TagsTree.TagID = Tags.TagID
JOIN Images on Images.ImageID = TagsTree.ImageID
JOIN Folders on Folders.FolderID = Images.FolderID
ORDER BY Fullpath

[CameronD]

...
Is there any way to search for "bride NOT groom" or "bride AND <picture has 1 category>" or something like that?
...

Even if the metadata sync between file and DB was working completely reliably there is still no negation option on strings like XMP:Subject or IPTC:Keywords. The only selections are "contains" or "is any of".

Perhaps the most flexible option might be to have a separate "Exclusion Conditions" box

[danjcla]

Thanks; I'm now working on doing this in-database. For the specific case of being able to find photos that are only in one category, the below seems to work; if a photo is only in category "bride", it also gets the computed category "bride⇔1". As soon as it is added to another category that doesn't have the character "⇔" in it, the computed category "bride⇔1" disappears. So selecting category "bride⇔1" will show photos with only the bride.

The below SQL does this (in a non-normalized way, just for ease of looking at stuff for the time being); hopefully I'll be able to move existing tables to new "tablename_real" tables, and have the tables be views via the SQLite updateable views workaround. I think this should be transparent to XnView MP unless XnView MP explicitly checks to make sure the schema is what it thinks it should be. It should be possible to just silently ignore the user trying to manually add / remove / rename / etc a photo to a computed category.

If this does work, I think it should be possible to cover ⇔[2...n] cases via CTEs (common table expressions), but I'm horrible at recursion so I'd probably put that off for a while; the "one and only one person" case covers 90% of my needs.

~~

SELECT Images.ImageID AS ImageID, Tags.TagID + 1000000 AS TagID, Tags.Label || '⇔1' AS Label, Tags.ParentID AS ParentID, Tags.ID AS ID, Tags.Hidden AS Hidden, Tags.Description AS Description, Tags.Shortcut AS Shortcut
FROM Tags
JOIN TagsTree on TagsTree.TagID = Tags.TagID
JOIN Images on Images.ImageID = TagsTree.ImageID
JOIN Folders on Folders.FolderID = Images.FolderID
WHERE Tags.Label NOT LIKE '%⇔1%'
GROUP BY Images.ImageID
HAVING count(Images.ImageID) = 1

UNION

SELECT Images.ImageID AS ImageID, Tags.TagID AS TagID, Tags.Label AS Label, Tags.ParentID AS ParentID, Tags.ID AS ID, Tags.Hidden AS Hidden, Tags.Description AS Description, Tags.Shortcut AS Shortcut
FROM Tags
JOIN TagsTree on TagsTree.TagID = Tags.TagID
JOIN Images on Images.ImageID = TagsTree.ImageID
JOIN Folders on Folders.FolderID = Images.FolderID

ORDER BY Images.ImageID

[danjcla]

Have this working manually now. Re: doing it automatically, the faux writeable views way doesn't look like it's going to work; I think triggers will work, but I haven't figured out how to bullshit a global state sync from the one-row-at-a-time trigger mechanism yet. Anyway, first create some views (only need to do this once):

CREATE VIEW computed_all AS
SELECT Images.ImageID AS ImageID, Tags.TagID -1000000 AS TagID, '⇔' || Tags.Label AS Label, Tags.ParentID AS ParentID, Tags.ID AS ID, Tags.Hidden AS Hidden, Tags.Description AS Description, Tags.Shortcut AS Shortcut
FROM Tags
JOIN TagsTree on TagsTree.TagID = Tags.TagID
JOIN Images on Images.ImageID = TagsTree.ImageID
JOIN Folders on Folders.FolderID = Images.FolderID
WHERE Tags.Label NOT LIKE '%⇔%'
GROUP BY Images.ImageID
HAVING count(Images.ImageID) = 1;

CREATE VIEW computed_tagstree AS
SELECT DISTINCT ImageID, TagID
FROM computed_all;

CREATE VIEW computed_tags AS
SELECT DISTINCT TagID, Label, ParentID, ID, Hidden, Description, Shortcut
FROM computed_all;

And then run this whenever you want to update the computed tags:

DELETE FROM Tags WHERE Label LIKE '%⇔%';
INSERT OR IGNORE INTO Tags SELECT * FROM computed_tags;
DELETE FROM TagsTree WHERE TagID < 0;
INSERT OR IGNORE INTO TagsTree SELECT * FROM computed_tagstree;

If you are using sqlitebrowser, remember you need to "Write Changes" before changes you can see within sqlitebrowser are visible elsewhere (everything you do in sqlitebrowser is in the context of one massive transaction until you write changes, which causes a "COMMIT" aka "END TRANSACTION" to happen.)

You also have to open/close XnView MP for it to see the changes. Although when I was playing with triggers, at one point I got to a place where XnView MP did see changes without open/close, so that is possible. Also when playing with triggers, it looks like it should be possible to do all of this immediately / as-needed while the GUI is open, but getting it 100% right looks annoying and hard to debug. Anyway, here's a little script to do the while-XnView-is-closed updates; you can run it any number of times, it never does anything bad.

#!/bin/sh

sqlite3 ~/.xnviewmp/XnView.db "DELETE FROM Tags WHERE Tags.Label LIKE '%⇔%'"
sqlite3 ~/.xnviewmp/XnView.db "INSERT OR REPLACE INTO Tags SELECT * FROM computed_tags"
sqlite3 ~/.xnviewmp/XnView.db "DELETE FROM TagsTree WHERE TagID < 0"
sqlite3 ~/.xnviewmp/XnView.db "INSERT OR REPLACE INTO TagsTree SELECT * FROM computed_tagstree"

Oh, and this all assumes you don't (a) have 999999 or more tags or (b) use the ⇔ character in file names. If you do, search-replace those things with higher / different values.

[danjcla]

Here's an updated version that also gives you an "empty set" tag - a.k.a. the files a human has not yet tagged. You can use this together with search to find all untagged files in a folder.

BTW you can't search on the other generated categories because search will only let you select a category if it has an ID that is >= 0. I think 0 is just not used, so is safe to use for one thing; so I use it for the empty set tag. The problem with having the rest of the autogenerated categories (aka tags) have IDs >=0 is that XnView itself adds the next manually-created tag to the next largest integer; so the negative numbers can be used as a sort of additional namespace. I'm sure there would be some way of keeping track of generated values etc other than this, but I think it'd be way more complex.

CREATE VIEW computed_all AS
SELECT Images.ImageID AS ImageID, Tags.TagID -1000000 AS TagID, '⇔' || Tags.Label AS Label, Tags.ParentID AS ParentID, Tags.ID AS ID, Tags.Hidden AS Hidden, Tags.Description AS Description, Tags.Shortcut AS Shortcut
FROM Tags
JOIN TagsTree on TagsTree.TagID = Tags.TagID
JOIN Images on Images.ImageID = TagsTree.ImageID
JOIN Folders on Folders.FolderID = Images.FolderID
WHERE Tags.Label NOT LIKE '%⇔%' AND Tags.Label NOT LIKE '∅'
GROUP BY Images.ImageID
HAVING count(Images.ImageID) = 1
UNION
SELECT Images.ImageID AS ImageID, '0' AS TagID, '∅' AS Label, '-1' AS ParentID, '0' AS ID, '0' AS Hidden, '' AS Description, '' AS Shortcut
FROM Images
LEFT JOIN TagsTree ON Images.ImageID = TagsTree.ImageID
JOIN Folders ON Folders.FolderID = Images.FolderID
WHERE TagsTree.ImageID IS NULL

CREATE VIEW computed_tagstree AS
SELECT DISTINCT ImageID, TagID
FROM computed_all

CREATE VIEW computed_tags AS
SELECT DISTINCT TagID, Label, ParentID, ID, Hidden, Description, Shortcut
FROM computed_all

And then run this whenever you want to update the computed tags:

DELETE FROM Tags WHERE Label LIKE '∅';
DELETE FROM Tags WHERE Label LIKE '%⇔%';
DELETE FROM TagsTree WHERE TagID < 0;
INSERT OR IGNORE INTO Tags SELECT * FROM computed_tags;
INSERT OR IGNORE INTO TagsTree SELECT * FROM computed_tagstree;

[danjcla]

Figured out the general case to get the most specific results possible for N tags...

[danjcla]

Here is a new version of the computed_all view that results in stuff being in the correct alphabetical order, which makes stuff a lot easier to find...

Code: Select all

CREATE VIEW computed_all AS
SELECT 
        ImageID2 AS ImageID, 
        '-' || group_concat(TagID2,'') AS TagID, 
        '⇔' || group_concat(Label2) AS Label, 
        ParentID2 AS ParentID, 
        ID2 AS ID, 
        Hidden2 AS Hidden, 
        Description2 AS Description, 
        Shortcut2 AS Shortcut
FROM ( 
        SELECT 
                Images.ImageID AS ImageID2, 
                Tags.TagID AS TagID2, 
                Tags.Label AS Label2, 
                Tags.ParentID AS ParentID2, 
                Tags.ID AS ID2, 
                Tags.Hidden AS Hidden2, 
                Tags.Description AS Description2, 
                Tags.Shortcut AS Shortcut2
        FROM Tags
        JOIN TagsTree on TagsTree.TagID = Tags.TagID
        JOIN Images on Images.ImageID = TagsTree.ImageID
        WHERE TagsTree.TagID > 0
        ORDER BY ImageID2, Label2
     )
GROUP BY ImageID

UNION

SELECT Images.ImageID AS ImageID, 
       '0' AS TagID, 
       '∅' AS Label, 
       '-1' AS ParentID, 
       '0' AS ID, 
       '0' AS Hidden, 
       '' AS Description, 
       '' AS Shortcut
FROM Images
LEFT JOIN TagsTree ON Images.ImageID = TagsTree.ImageID
JOIN Folders ON Folders.FolderID = Images.FolderID
WHERE TagsTree.ImageID IS NULL

Other views staysthe same...

Code: Select all

CREATE VIEW computed_tagstree AS
SELECT DISTINCT ImageID, TagID
FROM computed_all

Code: Select all

CREATE VIEW computed_tags AS
SELECT DISTINCT TagID, Label, ParentID, ID, Hidden, Description, Shortcut
FROM computed_all

And then to refresh:

Code: Select all

DELETE FROM Tags WHERE Label LIKE '∅';
DELETE FROM Tags WHERE Label LIKE '%⇔%';
DELETE FROM TagsTree WHERE TagID < -1;
INSERT OR IGNORE INTO Tags SELECT * FROM computed_tags;
INSERT OR IGNORE INTO TagsTree SELECT * FROM computed_tagstree;

[danjcla]

Moved this over to its own thread.