[Digikam-users] clear deprecated file entries from database

[Sebastian Straub]

> 
> Not quite exactly what you ask for, but it might do the trick:
> (NOTE: do teh folowing while digikam is NOT running)
> 
> 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 remove the deleted entries, and it should be followed by 
> another integrity check.
> 
> 
> And of course the DBs should be backed up beforehand.
> 
> Hope this helps,
> 
> Remco
>

Thank you very much, that did do the trick!

Actually, it wasn't that easy, as there were some issues with the
database, so just for the record, here's what I did:

Running the integrity check on the main database gave me some errors:

  sqlite> pragma integrity_check;
  integrity_check = row 27 missing from index sqlite_autoindex_Albums_1
  integrity_check = row 28 missing from index sqlite_autoindex_Albums_1
  (...)

Which I was able to fix with

  sqlite> reindex sqlite_autoindex_Albums_1;

But there was more...

  On tree page 4495 cell 1: Rowid 15804 out of order (max larger than
    parent max of 153)
  On tree page 82 cell 10: Rowid 154 out of order (min less than parent
    min of 15804)
  (...)

which I couldn't find a fix for, so, following random code snippets
found on the web [1], I rebuilt the entire database

  sqlite> .mode insert
  sqlite> .output digkam4-dump.db
  sqlite> .dump

now open a new file (e.g. `sqlite digikam4-fixed.db`)

  sqlite> .read digkam4-dump.db

there were some errors while reading stuff

  Error: near line 379: UNIQUE constraint failed: Albums.albumRoot,
    Albums.relativePath
  Error: near line 380: UNIQUE constraint failed: Albums.albumRoot,
    Albums.relativePath
  (...)

but anyway, it seems to have worked

  sqlite> pragma integrity_check;
  ok
  sqlite> vacuum;

Fortunately, the thumbnail-database had no issues, so this worked just
as expected.
Now when I started digiKam, no images were displayed and a full rescan
of all files was running, but after a minute or so all entries were
there, except for those nasty remainders of deleted files :)

Well, it would be nice to have this function accessible through the
maintenance dialogue. while probably not everyone will screw up his
database like I did (how does this always happen to me?), it's not a bad
idea to clean up the sqlite database from time to time, right?
Anyway, thanks again!

best regards
Sebastian


[1]:
http://techblog.dorogin.com/2011/05/sqliteexception-database-disk-image-is.html