Bug 250610

What should it check? And would would be the benefit of such a tool? If it reports that the database file is corrupted it couldn't do much about that.
The database is a normal sqlite file (or mysql if you want to). So you can use any tool on these databases but I don't see how a checking tool can help. Backups of the database file are the right way to preserve it.

I understand your concerns.

Questions: 
- why does technology use redundant systems in critical cases as in chemical industry, aircraft, e.g.? 
- why does firefox use a daily copy- automatically. If everybody could work with copies?

Again: these file (1,7 GB) contains days of work. Just want more security to preserve this "time-investment".

Maybe repairing is not the best way; in past weeks, I noticed some odd reactions to make me wondering if there could be a database problem involved, however. Not refreshing thumbnails/previews, even when changing the selected folders within Digikam.

And yes, I keep copies in spare. But how can I know, that the database is ok?
What criteria I should check for?

Well there are mainly two aspects about a corrupted database:
 1. the database files are simply unreadable. This is basically a sqlite issue and we should not have to worry about it. Anyway it is easy to detect ;)
 2. the datanase is semantically wrong, e.g. diverges from the images. This is really hard to detect. If the user does not want to store metadata in the images, there are only heuristics like counting the number of lines in a table since last shutdown or something like this. But if the db is the only ground truth, how to check if it is consistent with the image that are otherwise unknown to the system?

Hi, I resurrect this entry as I had the same feeling.
Having used Digikam for several years, my database holds about 40k pictures and more than 500 tags.

My Digikam (currently v1.9) works great but I still haven't been able to migrate to using MySQL as the generated migrated MySQL tables differ from my current SQLite tables (in terms of entries).

This lead my to thinking that there might be some semantic inconsistencies in my SQLite DB.

So I finally wrote a program that checks all rows in the database for poteltial problems (Albums linked to innexistant AlbumRoots, Images linked to innexistant Albums, Images without ImageInformation, Tag hierachies, and what not).

Well, given the volume, most of my DB is consistent but my tool still found 466 "ghost" pictures (pictures present in the DB linked to an Album that doesn't exist ...) and these I'll need to fix by hand ...

So there is definitely a need for such a tool and I'll make mine available ASAP

Hi, 

Using Mysql with a single server/file storage shared between 3 computers, sometimes running simultaneously, I noticed many duplicates in the database : lots of images (if not all of them!) are present in the db multiple times (up to 10 entries for the same file!). Hashes and path match, but the albums are different : I suspect that the whole tree gets rebuilt sometimes, and the previous tree just becomes a "ghost" in the db.
I think about opening a bug about this, but I second Vincent's opinion that adding a "database check" option would be a great idea (as a function of digikam or as an external program : the difficulty would be that it has to handle both SQLite and Mysql databases).

I agree Frederic,

it seems lots of these "ghost" images have a value of 3 in their "status" column.
I suspect also that once in a while digikam must clean up those entries but I don't really know what's the pattern ...

Images with status 3 and no album are officially ghost images, not a bug but a feature, and should get cleaned up regularly, see collectionscanner for the conditions. (without that, you'd loose all metadata if an image is deleted first and then found somewhere else on move)

Thanks for that Marcel.
I looked at the collectionscanner and saw the cleaning code based on the
number of days and number of scans

Is there a document explaining the different possible values for the
"status" column and their meanings ?

Vince.

No, but in databaseconstants.h, the possible values are defined as an enum:

enum Status
{
    UndefinedStatus = 0,
    Visible         = 1,
    Hidden          = 2,
    Removed         = 3
};

where we dont really use the Hidden value

So here's the tool I wrote to check the consistency of the DB.
For the moment it's SQLite only and DB schema V5.

http://linuxdarkroom.tassy.net/digikam-db-validator

Hope this helps ! It's returned interesting results on my DB !

(In reply to comment #10)
> So here's the tool I wrote to check the consistency of the DB.
> For the moment it's SQLite only and DB schema V5.
> 
> http://linuxdarkroom.tassy.net/digikam-db-validator
> 
> Hope this helps ! It's returned interesting results on my DB !

Very interesting thanks!

(In reply to comment #10)
> So here's the tool I wrote to check the consistency of the DB.
> For the moment it's SQLite only and DB schema V5.
> 
> http://linuxdarkroom.tassy.net/digikam-db-validator
> 
> Hope this helps ! It's returned interesting results on my DB !

This tool doesn't work for me due to several issues:

1. it tries to use kde-config for determing the config file, but on my system the application is called kde4-config

2. It doesn't seem to evaluate the volumeids, therefore none of the albums in the database can be found on disk. For example: the tool tries to read the folder "/images/something", although the correct path is "/mnt/data/images/something". 
"/mnt/data" is the volumeid (I guess) that is missing.

Andi

I have added the volumeid (in my case "/mnt/data" manually in the tool, and now it seems to run just fine. Somehow this tool needs to translate the UUID in the volumeID to the actual mount path in the system.

One solution for finding the mountpoint on a unix system would be:
mount | awk -v dev=$(blkid -l -t UUID="5f7e24f4-c39d-4e27-8063-00bbad2c9b3c" -o device) '$1 == dev{print $3}'

The UUID has to be replaced of course.

Hi Andi,

thanks for reporting the issue and looking into it !

Haven't had time to check it out this weekend, will do that tomorrow.

Could you please send me a dump of your AlbumRoots table so that I can compare with what I have and better diagnose ?

just run the command: sqlite3 Pictures/digikam4.db "SELECT * FROM AlbumRoots"
(Replace with the correct pat of your db file.

Just for info, mine looks like this:
1|Local|0|1|volumeid:?uuid=aeefb3d2-a2d9-48fc-a783-164cc6057067|/home/timetre/Pictures
2|NAS|0|3|networkshareid:?mountpath=%2FNAS%2FPictures|/

I have one collection in my home directory and one collection on my NAS.

Thanks,

Vince.

On 08/20/2011 10:07 PM, Andi Clemens wrote:
> https://bugs.kde.org/show_bug.cgi?id=250610
>
>
>
>
>
> --- Comment #14 from Andi Clemens <andi clemens googlemail com>  2011-08-20 20:07:48 ---
> One solution for finding the mountpoint on a unix system would be:
> mount | awk -v dev=$(blkid -l -t UUID="5f7e24f4-c39d-4e27-8063-00bbad2c9b3c" -o
> device) '$1 == dev{print $3}'
>
> The UUID has to be replaced of course.
>

Vincent Tassy

E : vincent@tassy.net <mailto:vincent@tassy.net>
http://photo.tassy.net
http://www.showmetheplanet.com

My profiles: LinkedIn <http://fr.linkedin.com/in/vincenttassy> Picasa <http://picasaweb.google.com/timetre> Flickr <http://www.flickr.com/photos/timetre>
Twitter <http://www.twitter.com/timetre>
Contact me: Skype tassyv MSN vince@nsx.fr.fm

That's why it is working for you, I guess that /home is no individual partition, but located in the root partition, so you don't need the UUID information.

In my case, /mnt/data is an individual partition, that's why /mnt/data is missing in the root albums description:

3|private|0|1|volumeid:?uuid=5f7e24f4-c39d-4e27-8063-00bbad2c9b3c|/images/private
4|public|0|1|volumeid:?uuid=5f7e24f4-c39d-4e27-8063-00bbad2c9b3c|/images/public

Andi,

thanks, it makes perfect sense ...
Since my Local collection is indeed under the / mount point the path was
directly useable and I didn't look at the volumeid info ... Now I
understand what it's for ;-)
I'll fix the tool ASAP.

Thanks for your help !

Vince.

On 08/21/2011 10:23 PM, Andi Clemens wrote:
> https://bugs.kde.org/show_bug.cgi?id=250610
>
>
>
>
>
> --- Comment #16 from Andi Clemens <andi clemens googlemail com>  2011-08-21 20:23:29 ---
> That's why it is working for you, I guess that /home is no individual
> partition, but located in the root partition, so you don't need the UUID
> information.
>
> In my case, /mnt/data is an individual partition, that's why /mnt/data is
> missing in the root albums description:
>
> 3|private|0|1|volumeid:?uuid=5f7e24f4-c39d-4e27-8063-00bbad2c9b3c|/images/private
> 4|public|0|1|volumeid:?uuid=5f7e24f4-c39d-4e27-8063-00bbad2c9b3c|/images/public
>

Okay, I've now fixed all the reported issues with the tool.

- It now checks for the KDE local prefix with kde4-config, kde-config or
defaults to ~/.kde

- It will use the volumeids from the AlbumRoots table to build the
complete collection paths (thanks Andi). Instead of invoking several
command lines as you suggested, I figure it out using the
/dev/disks/by-uuid and /etc/mtab content.
- It will also handle items on a removable media so it will issue a
warning since it cannot check for the existence of files and folders but
it won't report it as an error

Let me know if I missed something !

It's available here: http://linuxdarkroom.tassy.net/digikam-db-validator

Vince.

OK I will check it out later this evening.

Seems to work fine, there is only one tiny little bug: the version is set to the year 2001 :-)

Andi

Darn you're right ! I must have time-warped for a minute ;-)

changed it back to 2011. Thanks again for the feedback !

Vince.

Your welcome...!

Vincent,

What's about this file, as your DB validator tool is complete ?

Gilles Caulier

For me, you can close this one.

My tool did the job for me and I don't expect recurring needs since Digikam is strong and stable now ...
I'll maintain my validator against the current DB scheme version just in case ...

Vincent.

Thinking about publishing this tool on digikam.org?


Am 15.12.2011 11:39, schrieb Gilles Caulier:
> https://bugs.kde.org/show_bug.cgi?id=250610
> 
> 
> Gilles Caulier <caulier.gilles@gmail.com> changed:
> 
>            What    |Removed                     |Added
> ----------------------------------------------------------------------------
>              Status|UNCONFIRMED                 |RESOLVED
>          Resolution|                            |FIXED
> 
> 
> 
>

With digiKam 5.6.0, we introduce a Maintenance tool which check the integrity of database tables and contains. No need an external tool for these operations.