clear deprecated file entries from database

[Sebastian Straub]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi there,

I'm quite new to digiKam, but this one is bugging me and I couldn't
find an answer in the archives...

Sometimes I have to make changes to files that are indexed by digiKam,
using a regular file browser. On next start, digiKam recognizes these
changes, scans new folders and removes nonexisting images and folders
from the album view.

But it seems like the actual database entries are not touched in this
process. When there were face tags attached to the deleted images,
they are still visible in the people-browser, including all their
metadata, but with an empty thumbnail, linking to a blank picture.
Even worse, the deleted images break the tag browser (at least in the
Tumbnail-View), as some of the deleted images do disappear, but they
still leave some blank space. There are more images after that (I can
see the title of the next album), but I can't scroll down (there is no
scrollbar).

So, is there a way to remove deprecated entries from the database,
sync the database entries with the file system? I expected to find a
function like that under tools -> maintenance, but "scan for new
items" or "sync metadata and database" didn't change the situation.

I really don't want to rebuild the entire database each time I change
something from outside digiKam; some help would be greatly appreciated!

best regards
Sebastian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (MingW32)

iQEcBAEBAgAGBQJT6Oy/AAoJELlmX2j+BC3cCgsIALcT/tCHyZEiii6LmW4wLU7r
O/x4+gAdpp49/gqCOOyYK8gW9QHCLYUUoNfqPZpMSy00ZQf68vsThaoG6aq+usji
GQfh6bOh7tEyQVB+R6lBont9ReeAOuLNY/cEvPdtOR5BTwIrkImFNFy24rAcf2V3
oWm1YnJ/fIIAjdrIQ/JiJIsj0tQQDUYhg4CF5cbgMr8h96VYWn3eMg+JViR/0Reo
P1RmSCzrQQKEKrs/+7jD7pSBMS0nupBwXDCAFAaLB+D7yQK+U2D0/sABEzvBLOAU
aaXKxhwqDTCB9h5MPQs/pL7aeyz8cLJNbE25X5HTNBvJi/Z08CwTybWS5VfL/14=
=HdXM
-----END PGP SIGNATURE-----
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[Remco Viëtor]

On Monday 11 August 2014 18:18:07 Sebastian Straub wrote:

> Hi there,
>
> I'm quite new to digiKam, but this one is bugging me and I couldn't
> find an answer in the archives...
>
> Sometimes I have to make changes to files that are indexed by digiKam,
> using a regular file browser. On next start, digiKam recognizes these
> changes, scans new folders and removes nonexisting images and folders
> from the album view.
>
> But it seems like the actual database entries are not touched in this
> process. When there were face tags attached to the deleted images,
> they are still visible in the people-browser, including all their
> metadata, but with an empty thumbnail, linking to a blank picture.
> Even worse, the deleted images break the tag browser (at least in the
> Tumbnail-View), as some of the deleted images do disappear, but they
> still leave some blank space. There are more images after that (I can
> see the title of the next album), but I can't scroll down (there is no
> scrollbar).
>
> So, is there a way to remove deprecated entries from the database,
> sync the database entries with the file system? I expected to find a
> function like that under tools -> maintenance, but "scan for new
> items" or "sync metadata and database" didn't change the situation.
>
> I really don't want to rebuild the entire database each time I change
> something from outside digiKam; some help would be greatly appreciated!
>
> best regards
> Sebastian
> _______________________________________________
> Digikam-users mailing list
> [hidden email]
> https://mail.kde.org/mailman/listinfo/digikam-users
>

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
_______________________________________________
Digikam-users mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-users

[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


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