Fake Features: Uncategorized Search & Strict Search

[danjcla]

If you save the long code section below as "non-sloppy-cats.sql" and then run, while XnView MP isn't running,

Code: Select all

sqlite3 ~/.xnviewmp/XnView.db < non-sloppy-cats.sql

You'll get:

(1) A category named "∅" with all the images a human hasn't categorized yet. You can search on this category in a certain folder to find uncategorized images in that folder.

(2) A set of categories prefixed by "⇔" that implement non-sloppy "AND" based searches of files via the Categories browser. So for three categories bride, groom, church you can easily find images with the bride and only the bride, the bride and groom together but no church, and all three of them together, among other combinations.

You'll need to re-run the script to update the computed categories.


Caveats:

(A) You can't search on the "⇔" 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.

(B) It is possible a computed category that represents two or more categories could get images that don't seem to belong in it; this is because currently the ID used is just the IDs of the two images put together. So for example if bride=2, groom=222, church=2222 then "⇔bride,groom" and "⇔church" would have the same ID, so only "⇔bride,groom" would show up (alpha sort) and it would have both "bridge,groom" and "church" images. In practice, I don't think this is a big issue, as XnView MP seems to start with 3-digit IDs and work up from there.

(C) This has a small but nonzero chance of causing issues, so as you hopefully do anyway, keep backups of your XnView MP database file.

Code: Select all

DROP VIEW IF EXISTS computed_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
WHERE TagsTree.ImageID IS NULL OR TagsTree.TagID = 0;


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


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

DELETE FROM Tags WHERE Label LIKE '%⇔%';
DELETE FROM Tags WHERE Label LIKE '∅';
DELETE FROM TagsTree WHERE TagID < -1;
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]

I just noticed that there is a built-in "computed category" that shows all uncategorized items under "Categories Filter -> Special Items -> Uncategorized".

IMHO the method here is better as you can include it in a (Tools->Search... aka ctrl-f) search, but just thought I should mention this in case people happen upon this post who only need to see all uncategorized photos everywhere.