Database inconsistencies

[Matthieu Pupat]

Hello,

I found what I suspect to be a few inconsistencies in my digikam
database and would like to know if there are any tools to clean them up
and if it is safe to remove those orphan entries listed below:

Images without albums (count: 1481):
SELECT * FROM `Images` WHERE album is NULL
Note: all those have status=3 whatever this means

Comment without attached images (count: 3):
SELECT * FROM `Images` RIGHT JOIN ImageComments ON Images.id=imageId
WHERE `Images`.`id` IS NULL

|HaarMatrix| without attached images (count: 2):
SELECT * FROM `Images` RIGHT JOIN Image|HaarMatrix| ON Images.id=imageId
WHERE `Images`.`id` IS NULL
Note: one of them has imageId set to -1

|Information| without attached images (count: 31):
SELECT * FROM `Images` RIGHT JOIN Image|Information| ON
Images.id=imageId WHERE `Images`.`id` IS NULL
Note: one of them has imageId set to -1

Metadata without attached images (count: 41):
SELECT * FROM `Images` RIGHT JOIN ImageMetadata ON Images.id=imageId
WHERE `Images`.`id` IS NULL
Note: one of them has imageId set to -1

Positions without attached images (count: 10):
SELECT * FROM `Images` RIGHT JOIN ImagePositions ON Images.id=imageId
WHERE `Images`.`id` IS NULL
Note: one of them has imageId set to -1

Thanks in advance,

Matthieu

||
||
||
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Jean-François Rabasse]

On Tue, 11 Dec 2012, Matthieu Pupat wrote:

> Hello,
>
> I found what I suspect to be a few inconsistencies in my digikam database
> and would like to know if there are any tools to clean them up and if it is
> safe to remove those orphan entries listed below:
> ...

Hello,

My general comment is that it's never « safe » to correct by hand databases
inconsistencies, because a database should never have inconsistencies.
If yes, there's a big problem somewhere, either an application inconsistent
schema or a database driver problem. In both cases, you can't guess in a
reliable way what final state you will get after hand tweaking and what
could be the future behaviour.

But you don't say what kind of DB uses your Digikam. MySQL or SQLite3 ?

SQLite3 has integrity constraints problems, depending on the implementation.
As you seem used to SQL, try on a new test DB some simple tests, e.g.
create table images (
     iid integer primary key);

create table comments (
     cid integer references images(iid) on delete cascade);

Now populate your images table with a couple of values, 1, 2, 3
Try to create a comment with cid 4, it should be forbidden.
With my version of SQLite3 it works :-(

Try to create a comment with cid 2, then delete the image with iid 2.
The comment should be cascade deleted. With my SQLite3 it isn't :-(

Same weird behaviours when using triggers.

If you have that kind of behaviour, it's not a Digikam problem at all.
You should forget the idea of having a stable and consistent database.
The best way is, from time to time, to have your metadata in images, then
destroy the Digikam DB, then restart from scratch and let DK browse your
collections and recreate all stuff.



Regards,
Jean-François
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Remco Viëtor]

On Wednesday 12 December 2012 14:00:52 Jean-François Rabasse wrote:

>

> On Tue, 11 Dec 2012, Matthieu Pupat wrote:

>

> > Hello,

> >

> > I found what I suspect to be a few inconsistencies in my digikam database

> > and would like to know if there are any tools to clean them up and if it is

> > safe to remove those orphan entries listed below:

> > ...

>

> Hello,

>

> My general comment is that it's never « safe » to correct by hand databases

> inconsistencies, because a database should never have inconsistencies.

> If yes, there's a big problem somewhere, either an application inconsistent

> schema or a database driver problem. In both cases, you can't guess in a

> reliable way what final state you will get after hand tweaking and what

> could be the future behaviour.

>

> But you don't say what kind of DB uses your Digikam. MySQL or SQLite3 ?

>

> SQLite3 has integrity constraints problems, depending on the implementation.

> As you seem used to SQL, try on a new test DB some simple tests, e.g.

> create table images (

> iid integer primary key);

>

> create table comments (

> cid integer references images(iid) on delete cascade);

>

> Now populate your images table with a couple of values, 1, 2, 3

> Try to create a comment with cid 4, it should be forbidden.

> With my version of SQLite3 it works :-(

>

> Try to create a comment with cid 2, then delete the image with iid 2.

> The comment should be cascade deleted. With my SQLite3 it isn't :-(

>

> Same weird behaviours when using triggers.

>...

 

I agree that it's a bad idea to modify a DB 'by hand'. But, could the non-removal in cascade be a speed concern (limiting the number of small actions on the data base)? Not deleting some data in cascade doesn't seem to cause problems, except poluting the data base with inaccessible data.

 

And a while ago someone posted the following to check and compact the SQLite data base:

First, check the database integrity:

# sqlite3 -line digikam4.db 'pragma integrity\_check;'

# sqlite3 -line thumbnails-digikam.db 'pragma integrity\_check;'

This should pass w/o errors.

 

After that, the databases can be optimised/compacted with

# sqlite3 -line digikam4.db 'vacuum;'

# sqlite3 -line thumbnails-digikam.db 'vacuum;'

 

This should be followed by another integrity check.

 

And of course the DBs should be backed up beforehand.

 

regards,

 

Remco

 

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Jean-François Rabasse]

On Wed, 12 Dec 2012, Remco Viëtor wrote:

> I agree that it's a bad idea to modify a DB 'by hand'. But, could the
> non-removal in cascade be a speed concern (limiting the number of small
> actions on the data base)? Not deleting some data in cascade doesn't seem to
> cause problems, except poluting the data base with inaccessible data.

Well, yes and no. It's a pollution, you're right, but it can cause problems
in case of reuse of identifiers.
Imagine you have an image with id xxx, and a related comment referencing
that id. The image gets destroyed, the comment no.
In a future a new image is created with (available) id xxx, and the
garbage comment gets reconnected.

Database integrity constraints should be a sine qua non condition.
But, as for SQLite3, the tool has weaknesses and we must be aware of
that.

> And a while ago someone posted the following to check and compact
> the SQLite data base:
> ...

I remember, the « someone » was me :-)

But it's a matter of cleanup, not a regular guaranty. I don't know
what trust level can be set on a software « integrity\_check » when
the same software doesn't handle integrity properly. :-)

My personal philosophy is that, in the case of DK, the database isn't
that much important IF there's a safe storage for meta information.
Editing metadata is work and time, using a database is only a fast tool
for future searches.
If metadata is archived into the image XMP section, all images become
independant of the DK software, version, and it's always possible to
wipe out the database and rebrowse from scratch.
(Or use metadata with other applications.)

Regards,
Jean-François
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Matthieu Pupat]

On 12/12/2012 02:00 PM, Jean-François Rabasse wrote:
>
> But you don't say what kind of DB uses your Digikam. MySQL or SQLite3 ?
I have MySQL database
>
>
> The best way is, from time to time, to have your metadata in images, then
> destroy the Digikam DB, then restart from scratch and let DK browse your
> collections and recreate all stuff.
I would do that but I have a few CR2 and movies file that do not have
information stored in them. I used to use Geosetter and it would store
the metadata in a separate .THM file next to the CR2 or video. Could
this be a future development for digikam?

BTW does all DB information go in the metadata (tags, faces, ...)?

Thanks,

Matthieu
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[maderios]

On 12/13/2012 05:38 PM, Matthieu Pupat wrote:
> On 12/12/2012 02:00 PM, Jean-François Rabasse wrote:
>>
>> But you don't say what kind of DB uses your Digikam. MySQL or SQLite3 ?
> I have MySQL database
Useful, maybe
To repair mysql db on  an unix like (gnu/linux, bsd, etc..)
As root:
mysqlcheck -p --auto-repair --all-databases

Greetings
--
Maderios

"Art is meant to disturb. Science reassures."
"L'art est fait pour troubler. La science rassure" (Georges Braque)

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Marcel Wiesweg]

The images with status=3 and a NULL album are intentional, these are deleted
entries which are kept in the database, should the image appear again (could
happen with lengthy move operations)
They should be cleared after a time.

The other entries seem to be invalid. There is a trigger which deletes entries
in all dependent tables when the entry in "Images" is deleted. Dont know why
there are these leftovers.

Marcel

> I found what I suspect to be a few inconsistencies in my digikam
> database and would like to know if there are any tools to clean them up
> and if it is safe to remove those orphan entries listed below:
>
> Images without albums (count: 1481):
> SELECT * FROM `Images` WHERE album is NULL
> Note: all those have status=3 whatever this means
>
> Comment without attached images (count: 3):
> SELECT * FROM `Images` RIGHT JOIN ImageComments ON Images.id=imageId
> WHERE `Images`.`id` IS NULL
>
> |HaarMatrix| without attached images (count: 2):
> SELECT * FROM `Images` RIGHT JOIN Image|HaarMatrix| ON Images.id=imageId
> WHERE `Images`.`id` IS NULL
> Note: one of them has imageId set to -1
>
> |Information| without attached images (count: 31):
> SELECT * FROM `Images` RIGHT JOIN Image|Information| ON
> Images.id=imageId WHERE `Images`.`id` IS NULL
> Note: one of them has imageId set to -1
>
> Metadata without attached images (count: 41):
> SELECT * FROM `Images` RIGHT JOIN ImageMetadata ON Images.id=imageId
> WHERE `Images`.`id` IS NULL
> Note: one of them has imageId set to -1
>
> Positions without attached images (count: 10):
> SELECT * FROM `Images` RIGHT JOIN ImagePositions ON Images.id=imageId
> WHERE `Images`.`id` IS NULL
> Note: one of them has imageId set to -1

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Matthieu Pupat-2]

On 12/13/2012 07:45 PM, Marcel Wiesweg wrote:
> The images with status=3 and a NULL album are intentional, these are deleted
> entries which are kept in the database, should the image appear again (could
> happen with lengthy move operations)
> They should be cleared after a time.

I assumed they were indeed deleted images. I did not do mass deletion
lately so they have been there for some time. Any idea how long is "a time"?
>
> The other entries seem to be invalid. There is a trigger which deletes entries
> in all dependent tables when the entry in "Images" is deleted. Dont know why
> there are these leftovers.

My db is very old. Maybe an issue with db upgrade or a bug of an older
digikam.

_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Matthieu Pupat]

On 12/13/2012 07:45 PM, Marcel Wiesweg wrote:
> The images with status=3 and a NULL album are intentional, these are
> deleted
> entries which are kept in the database, should the image appear again
> (could
> happen with lengthy move operations)
> They should be cleared after a time.

I assumed they were indeed deleted images. I did not do mass deletion
lately so they have been there for some time. Any idea how long is "a
time"?
>
> The other entries seem to be invalid. There is a trigger which deletes
> entries
> in all dependent tables when the entry in "Images" is deleted. Dont
> know why
> there are these leftovers.

My db is very old. Maybe an issue with db upgrade or a bug of an older
digikam.
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Marcel Wiesweg]

>
> I assumed they were indeed deleted images. I did not do mass deletion
> lately so they have been there for some time. Any idea how long is "a
> time"?

It's set arbitrarily. You need a minimum time, days, and a minimum number of
program starts. This is a right or wrong as you like. (and there has once been
a bug preventing this completely).
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users