Duplication of paths and images in the DB
Posted: Thu Nov 02, 2017 11:44 am
When selecting photos using the Category filter, I noticed some photos appear a number of times.
On exploring the XnView.db file with sqlite3, I noticed that there seems to be mass duplication of paths; specifically entries that start '@' which appear to be my top-level photos directory, and also full paths. e.g. if I say, "SELECT * from FOLDERS" I may see both:
/my/top/dir/photos/2017/January
@photos/2017/January
They have unique folderIDs. If I search the images table I find the images are also duplicated, i.e. they appear (with different ImageID) with both of the above folders' FolderID values set.
Obviously, this makes selecting images by category difficult because a lot of images appear multiple times. It's also going to increase the size and overheads of the database.
Why are there both entries, and which one is the 'preferred' one? I wrote a Perl script to go through the database and remove duplicates (while, I think/hope, not losing any Tags assigned to the images), but obviously I don't know whether the entry I should be keeping is the '@' entry, or the full entry.
Can you advise, and/or fix the code such that it does not store duplicates. I had hoped that clicking the button to optimise the DB would succesfully remove duplicates while also ensuring that the entries in the 'TagsTree' table would make sure that any tags on the removed duplicate were first assigned to the image that was kept (if not already set), but this appeared not to be the case.
Thanks.
On exploring the XnView.db file with sqlite3, I noticed that there seems to be mass duplication of paths; specifically entries that start '@' which appear to be my top-level photos directory, and also full paths. e.g. if I say, "SELECT * from FOLDERS" I may see both:
/my/top/dir/photos/2017/January
@photos/2017/January
They have unique folderIDs. If I search the images table I find the images are also duplicated, i.e. they appear (with different ImageID) with both of the above folders' FolderID values set.
Obviously, this makes selecting images by category difficult because a lot of images appear multiple times. It's also going to increase the size and overheads of the database.
Why are there both entries, and which one is the 'preferred' one? I wrote a Perl script to go through the database and remove duplicates (while, I think/hope, not losing any Tags assigned to the images), but obviously I don't know whether the entry I should be keeping is the '@' entry, or the full entry.
Can you advise, and/or fix the code such that it does not store duplicates. I had hoped that clicking the button to optimise the DB would succesfully remove duplicates while also ensuring that the entries in the 'TagsTree' table would make sure that any tags on the removed duplicate were first assigned to the image that was kept (if not already set), but this appeared not to be the case.
Thanks.