Code: Select all
sqlite3 ~/.xnviewmp/XnView.db < non-sloppy-cats.sql
(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.
(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;