[Bug 250610] New: wish: tool or procedure to check digikam database

[Axel Krebs]

https://bugs.kde.org/show_bug.cgi?id=250610

           Summary: wish: tool or procedure to check digikam database
           Product: digikam
           Version: 1.2.0
          Platform: Ubuntu Packages
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: major
          Priority: NOR
         Component: Database
        AssignedTo: [hidden email]
        ReportedBy: [hidden email]


Version:           1.2.0 (using KDE 4.4.2)
OS:                Linux

Digikams database concentrates extremely high values because of manual work.
The mine contains more than 70.000 pics, thousands of assigned to keywords,
etc.

How to make sure, these work is not endangered?
Therefore I wish a way to check (automatically?) this database.



Reproducible: Always

Steps to Reproduce:
irrelevant, wish!


Expected Results:  
hope for self-checking, self-controlling, self-reapairung database mechanism.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Johannes Wienke-3]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #1 from Johannes Wienke <languitar semipol de>  2010-09-08 23:34:52 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Axel Krebs]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #2 from Axel Krebs <axel krebs t-online de>  2010-09-09 08:34:46 ---
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?

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Johannes Wienke-3]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #3 from Johannes Wienke <languitar semipol de>  2010-09-09 20:29:25 ---
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?

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=250610


Andi Clemens <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
           Severity|major                       |wishlist




--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610


Vincent Tassy <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #4 from Vincent Tassy <timetre free fr>  2011-08-11 13:36:53 ---
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

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=250610


Frederic Grelot <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #5 from Frederic Grelot <fredericg_99 yahoo fr>  2011-08-11 13:45:59 ---
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).

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #6 from Vincent Tassy <timetre free fr>  2011-08-11 14:20:46 ---
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 ...

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #7 from Marcel Wiesweg <marcel wiesweg gmx de>  2011-08-11 16:49:23 ---
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)

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #8 from Vincent Tassy <timetre free fr>  2011-08-12 09:02:12 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #9 from Marcel Wiesweg <marcel wiesweg gmx de>  2011-08-12 17:21:23 ---
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

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #10 from Vincent Tassy <timetre free fr>  2011-08-19 09:14:19 ---
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 !

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=250610


Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #11 from Francesco Riosa <francesco+kde pnpitalia it>  2011-08-19 10:30:12 ---
(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!

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Andi Clemens]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #12 from Andi Clemens <andi clemens googlemail com>  2011-08-19 21:08:43 ---
(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

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Andi Clemens]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #13 from Andi Clemens <andi clemens googlemail com>  2011-08-20 09:41:02 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Andi Clemens]

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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #15 from Vincent Tassy <timetre free fr>  2011-08-21 19:28:26 ---
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 : [hidden email] <mailto:[hidden email]>
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 [hidden email]

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Andi Clemens]

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

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #17 from Vincent Tassy <timetre free fr>  2011-08-22 09:22:37 ---
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
>

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Vincent Tassy-2]

https://bugs.kde.org/show_bug.cgi?id=250610





--- Comment #18 from Vincent Tassy <timetre free fr>  2011-08-22 13:35:11 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel