It is a bug which wipes your entire catalog.
Steps to reproduce:
1. Go to DB Manager (Tools > Settings > Cache DB...)
2. Click "Clean Thumbnails"
3. Wait (no warning appears - just a gauge) and leave the program to do its "work"
4. When it is finished, restart the program.
Enjoy! Your catalog is gone!
In fact this bug was reported several times here, but because it is a critical data-loss bug (a showstopper), I raise it again with a fix.
How to fix it
For the users:
Do not press that button! (yet)
For Pierre:
There are several approaces, but I think that the one with the most benefits (fast & other enhancements) is the following:
NOTE: I marked the secondary enhancements which can be easily implemented by fixing this bug in a color like this.
Add in table Images a field called 'TagsCount' of type Integer. Fill it by default with 0 (zero).
Add to the table TagsTree the following triggers (not tested):
Code: Select all
Create Trigger MAIN.TagsTree_Images_AI AFTER INSERT On TagsTree
--when
begin
update images set TagsCount=TagsCount+1 where images.IMAGEID=NEW.IMAGEID;
end;
Create Trigger MAIN.TagsTree_Images_BD BEFORE DELETE On TagsTree
--when
begin
update images set TagsCount=TagsCount-1 where images.IMAGEID=OLD.IMAGEID;
end;
With the above triggers, you will have always the number of Categories (keywords) assigned to each image in real-time.
The speed penalty is towards to 0 since Images.ImageID is indexed and usually one assigns a keyword to relatively few images simultaneously.
Also, there is another feature here:
Based on this, you can also display in the thumbnail's corner the number of the keywords assigned, putting number 0 (ie. not yet categorized) with another color (the IDImager's approach) or, simply, just put a small icon in a corner of the thumb when image has keywords( ie TagsCount<>0) like in Lightroom.
But let's go back to our bug.
Since we have now all the data which we need in the Images table then we can simply do:
Code: Select all
DELETE FROM IMAGES WHERE RATING=0 AND COLOR=0 AND TagsCount=0;The only thing which is left are the empty folder entries in the Folders table.
In order to remove them write a separate method/function and call it. Do like this because the very same problem (empty directories left in the database) appear also after Move and Delete operations. For your knowledge (only of) the fastest queries to retreive the empty folders is:
Code: Select all
select FOLDERID from folders where not exists (select IMAGEID from images where images.FOLDERID = folders.folderID limit 1);Code: Select all
DELETE FROM folders
WHERE
folderid IN
(
SELECT
FOLDERID
FROM
folders
WHERE
NOT EXISTS
(
SELECT
IMAGEID
FROM
images
WHERE
images.FOLDERID = folders.folderID
LIMIT
1
)
) ;
)Also, be sure that the above DELETE to put in the DB Manager under a button called "Clean empty directories" or similar.
----
For the fist time run with the new schema, the users will have the new TagsCount field but will be filled with 0 (zero) even if they have files with keywords (categories) assigned. IOW, our persistent aggregate field is out of sync. That's why we need to fill it. We can update the field with the following (not tested)
Code: Select all
update Images set TagsCount=(Select Count(*) From TagTree where TagsTree.ImageID=Images.ImageID);Just my 2c & HTH
PS: Under the 'Clean Thumbs' button, even if the new algorithm above is much much faster, perhaps is better to put a message before, something like "We will do blablabla... and it will take a little. Confirm?" and keep the moving gauge there.
