[Digikam-users] Database inconsistencies

[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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20121212/20580059/attachment.html>