Some questions about digikam4.db
Locked
12
12
Some questions about digikam4.db
 
|
Hi,
I currently use digiKam/kipi-plugins 2.2.0. (Building 2.3.0 at the moment.) I just recently looked at the digikam4.db and can see that the size of the has suddenly been ~4 times bigger. As I have a couple of backups around it seems that when I went from v.2.0.0 to 2.1.0 the db grew from ~24MB to ~100MB. I have a history of backups which dates back almost a year and the db size has been almost the same, ~24 all the time. I have not added that many new images that could explain the size. I have looked in the tables and I have found that the: Images table contains 962 images that doens't have any Album set. ImagesTags table have 1628 rows connected to images without an Album set. The oldest image is from 2010-07-25. At least some of the images have been moved from one album to another. Haven't checked them all. Could it be stray entries related to the move? Is there a routine "clean" the db from such entries? As suggested on the mailing list I have tried this: sqlite3 -line digikam4.db 'vacuum;' Sure the db size went down but only a MB or two. Another reflection: There seems to be a new table: Thumbnails. There are 4500 thumbnails in that, about 1/3 of my total no of images. I can see that none of the latest and none of the earliest images are in there. This table could explain most of the db size I think. But is it used and for what? I would be happy if someone could enlighten me. /Anders _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
Hi Anders, To resume : something have be done between 2.1 and 2.3 to store thumb in main DB instead dedicated one.
Try this : run digiKam in with a fresh account and look if thumb DB blow up size when you compute all thumbnails through dedicated batch tool (look into Tools menu). The goal is to look if this work around is always valid or if it have been fixed with 2.3.0
Best Gilles Caulier
2011/11/9 Anders Stedtlund <[hidden email]> Hi, _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
Am 09.11.2011 09:26, schrieb Gilles Caulier:
> Hi Anders, > > yes, thumbnails storing is probably a side effect of this huge size. I > remember a thread where Francesco and Marcel speak about a problem with > dedicated thumbs DB. > > To resume : something have be done between 2.1 and 2.3 to store thumb in > main DB instead dedicated one. This is not good news. If ever possible separate thumbs from main data. I don't want to backup all the thumbs but my configuration data. My thumbnail DB has more than 400MB of data and there is no need to clutter a backup with this data. What is your strategy for backup if thumbs are in the main config DB? Regards Martin _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
2011/11/9 Martin (KDE) <[hidden email]>
Am 09.11.2011 09:26, schrieb Gilles Caulier: I agree. But i think that 2.3 will back to the right way. Please check as i explained before. Waiting Francesco explaination about... Gilles Caulier _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
|
|
In reply to this post by Martin (KDE)
Op 09-11-11 09:47, Martin (KDE) schreef:
> Am 09.11.2011 09:26, schrieb Gilles Caulier: >> Hi Anders, >> >> yes, thumbnails storing is probably a side effect of this huge size. I >> remember a thread where Francesco and Marcel speak about a problem with >> dedicated thumbs DB. >> >> To resume : something have be done between 2.1 and 2.3 to store thumb in >> main DB instead dedicated one. > This is not good news. If ever possible separate thumbs from main data. > I don't want to backup all the thumbs but my configuration data. My > thumbnail DB has more than 400MB of data and there is no need to clutter > a backup with this data. > > What is your strategy for backup if thumbs are in the main config DB? > > Regards > > Martin > > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Gilles Caulier-4
On Wed, Nov 09, 2011 09:51:57 AM +0100, Gilles Caulier wrote:
> 2011/11/9 Martin (KDE) <[hidden email]> > > This is not good news. If ever possible separate thumbs from main > data. I don't want to backup all the thumbs but my configuration > data. My thumbnail DB has more than 400MB of data and there is no > need to clutter a backup with this data. what if someone put together (as a temporary workaround, of course) a script that empties (without destroying) whatever table contains the thumbnails right before backups? Would this crash or confuse digiKam the next time it starts up? Marco _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
Hi,
I have now tested 2.3.0 with a new fresh profile. In this version the Thumbnails table doesn't exits anymore. Would it be safe to just drop the Thumbnails table in the old db file? /Anders 2011/11/9 M. Fioretti <[hidden email]>: > On Wed, Nov 09, 2011 09:51:57 AM +0100, Gilles Caulier wrote: > >> 2011/11/9 Martin (KDE) <[hidden email]> >> > >> This is not good news. If ever possible separate thumbs from main >> data. I don't want to backup all the thumbs but my configuration >> data. My thumbnail DB has more than 400MB of data and there is no >> need to clutter a backup with this data. > > what if someone put together (as a temporary workaround, of course) a > script that empties (without destroying) whatever table contains the > thumbnails right before backups? Would this crash or confuse digiKam > the next time it starts up? > > Marco > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
2011/11/9 Anders Stedtlund <[hidden email]>
Hi, I think yes, but the right response must come from Marcel or Francesco, to be sure. Gilles Caulier
_______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
Hi,
I have just tested anyway. (Have backup of course...) Deleted Thumbnails and compacted. Now the size of digikam4.db is ~20MB. digiKam starts and everything seems to be working... I can see that there is actually 4 tables that have been removed: CustomIdentifiers FilePaths Thumbnails UniqueHashes How about removing them all from the old db? What might happen? /Anders 2011/11/9 Gilles Caulier <[hidden email]>: > > > 2011/11/9 Anders Stedtlund <[hidden email]> >> >> Hi, >> >> I have now tested 2.3.0 with a new fresh profile. In this version the >> Thumbnails table doesn't exits anymore. >> >> Would it be safe to just drop the Thumbnails table in the old db file? > > I think yes, but the right response must come from Marcel or Francesco, to > be sure. > Gilles Caulier > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
Make a backup and drop table using sqlite manager.
http://www.sqlitemanager.org/
If it work, perhaps somebody can write a script to automatize this task Gilles
2011/11/9 Anders Stedtlund <[hidden email]> Hi, _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
oups, not sqlitemanager, but sqlitebrowser (:=))):
Gilles
2011/11/9 Gilles Caulier <[hidden email]> Make a backup and drop table using sqlite manager. _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Gilles Caulier-4
Hi,
I installed: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ Then removed the tables. Everything seems to work. The indexes connected to the tables were removed automatically. The db size shrunk even more. Now, what can I do with the stray images without any album? I can see that there's a trigger for removing an image in the db. When is that triggered? I asume not when moving files? Is it safe to remove these and all records in the other tables for these images? /Anders 2011/11/9 Gilles Caulier <[hidden email]>: > Make a backup and drop table using sqlite manager. > http://www.sqlitemanager.org/ > > If it work, perhaps somebody can write a script to automatize this task > Gilles > > 2011/11/9 Anders Stedtlund <[hidden email]> >> >> Hi, >> >> I have just tested anyway. (Have backup of course...) >> >> Deleted Thumbnails and compacted. Now the size of digikam4.db is >> ~20MB. digiKam starts and everything seems to be working... >> >> I can see that there is actually 4 tables that have been removed: >> CustomIdentifiers >> FilePaths >> Thumbnails >> UniqueHashes >> >> How about removing them all from the old db? What might happen? >> >> /Anders >> >> 2011/11/9 Gilles Caulier <[hidden email]>: >> > >> > >> > 2011/11/9 Anders Stedtlund <[hidden email]> >> >> >> >> Hi, >> >> >> >> I have now tested 2.3.0 with a new fresh profile. In this version the >> >> Thumbnails table doesn't exits anymore. >> >> >> >> Would it be safe to just drop the Thumbnails table in the old db file? >> > >> > I think yes, but the right response must come from Marcel or Francesco, >> > to >> > be sure. >> > Gilles Caulier >> > _______________________________________________ >> > Digikam-users mailing list >> > [hidden email] >> > https://mail.kde.org/mailman/listinfo/digikam-users >> > >> > >> _______________________________________________ >> Digikam-users mailing list >> [hidden email] >> https://mail.kde.org/mailman/listinfo/digikam-users > > > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Gilles Caulier-4
Eh??
Strange - I recently migrated from 2.2.0 MySQL to 2.3.0 MySQL The thumbnails are still in a separate DB. Other puzzling thing: upon deleting an image the entry remains in the main DB ("Images" table) with status changed to "3" - it is not physically deleted, and the delete is not cascaded to the thumbnails DB, hence the thumbnails table is growing although I'm pruning my pictures... I am not sure about the usefulness of this design... Cheers Ignatius Gilles Caulier thus spake on 09/11/11 09:26: > Hi Anders, > > yes, thumbnails storing is probably a side effect of this huge size. I > remember a thread where Francesco and Marcel speak about a problem with > dedicated thumbs DB. > > To resume : something have be done between 2.1 and 2.3 to store thumb in > main DB instead dedicated one. > > Try this : run digiKam in with a fresh account and look if thumb DB blow > up size when you compute all thumbnails through dedicated batch tool > (look into Tools menu). The goal is to look if this work around is > always valid or if it have been fixed with 2.3.0 > > Best > > Gilles Caulier > > 2011/11/9 Anders Stedtlund <[hidden email] <mailto:[hidden email]>> > > Hi, > > I currently use digiKam/kipi-plugins 2.2.0. (Building 2.3.0 at the > moment.) > > I just recently looked at the digikam4.db and can see that the size of > the has suddenly been ~4 times bigger. As I have a couple of backups > around it seems that when I went from v.2.0.0 to 2.1.0 the db grew > from ~24MB to ~100MB. I have a history of backups which dates back > almost a year and the db size has been almost the same, ~24 all the > time. I have not added that many new images that could explain the > size. > > I have looked in the tables and I have found that the: > Images table contains 962 images that doens't have any Album set. > ImagesTags table have 1628 rows connected to images without an Album > set. > > The oldest image is from 2010-07-25. At least some of the images have > been moved from one album to another. Haven't checked them all. Could > it be stray entries related to the move? Is there a routine "clean" > the db from such entries? > > As suggested on the mailing list I have tried this: > sqlite3 -line digikam4.db 'vacuum;' > > Sure the db size went down but only a MB or two. > > Another reflection: > There seems to be a new table: Thumbnails. There are 4500 thumbnails > in that, about 1/3 of my total no of images. I can see that none of > the latest and none of the earliest images are in there. This table > could explain most of the db size I think. But is it used and for > what? > > I would be happy if someone could enlighten me. > > /Anders > _______________________________________________ > Digikam-users mailing list > [hidden email] <mailto:[hidden email]> > https://mail.kde.org/mailman/listinfo/digikam-users Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
On Wed, 9 Nov 2011, Gilles Caulier wrote: > Make a backup and drop table using sqlite manager. > > http://www.sqlitemanager.org/ > > If it work, perhaps somebody can write a script to automatize this task > > Gilles >> 2011/11/9 Anders Stedtlund <falolaf at gmail.com> >> >> Hi, >> >> I have just tested anyway. (Have backup of course...) >> >> Deleted Thumbnails and compacted. Now the size of digikam4.db is >> ~20MB. digiKam starts and everything seems to be working... >> >> I can see that there is actually 4 tables that have been removed: >> CustomIdentifiers >> FilePaths >> Thumbnails >> UniqueHashes >> >> How about removing them all from the old db? What might happen? >> >> /Anders Just a hint, probably no need that somebody writes a script to automatize that kind of task. The command line SQLite client can already work with commands files. Write SQL commands (and metacommands, at least the final .exit) you want to do in a simple text file. Let's call it "cleanup" and it contains the two following lines : delete from Thumbnails; .exit then run the command (after backup of your .db file, as usual) : sqlite3 -init cleanup digikam4.db and you're done. And if you wish to do more jobs and empty more tables, as Anders suggested, just change the file into : delete from Thumbnails; delete from CustomIdentifiers; delete from FilePaths; delete from UniqueHashes; .exit > Make a backup and drop table using sqlite manager. Gilles, did you meant "drop" or "empty" the table ? IMHO I think dropping tables should be discouraged. delete from Thumbnails; will remove all data tuples in the table, making the table empty. drop table Thumbnails; will destroy also the table, thus altering the DB schema and most applications don't really love missing tables. I don't know what the sqlitebrowser does exactly, but this issue should probably be considered. Jean-François _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Ignatius Reilly
Sorry for the "second shoot" :) I forgot to signal that, in my opinion, there's also another space consuming table that doesn't really need to be backuped, it is the ImageHaarMatrix table that contains patterns signatures for the find duplicates function. It's the same kind of issue that the images thumbnails, it's nothing but "scratch data", because both can be rebuilt upon need via the DK commands menus, "Rebuild Thumbnails" and "Rebuild Fingerprints". It's not fundamental application data, just scratch data kept for faster access. So it can be (or should be) either into a separate db file or other solution. Some KDE applications, e.g. the Dolphin files browser, use a dedicated directory for that $HOME/.thumbnails. And, from an application program point of view, loading a small amount of data (a thumbnail image file is usually 10 or 12 Kbytes) is faster from a disk file than via the blob management of a RDBMS (that also access data on the disk, plus the SQL and blob I/O overhead). Jean-François _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Jean-François Rabasse
Hi Jean-François,
Did what you suggested. I left the tables. Only table that did contain any tuples was Thumbnails. /Anders 2011/11/9 Jean-François Rabasse <[hidden email]>: > > On Wed, 9 Nov 2011, Gilles Caulier wrote: > >> Make a backup and drop table using sqlite manager. >> >> http://www.sqlitemanager.org/ >> >> If it work, perhaps somebody can write a script to automatize this task >> >> Gilles > >>> 2011/11/9 Anders Stedtlund <falolaf at gmail.com> >>> >>> Hi, >>> >>> I have just tested anyway. (Have backup of course...) >>> >>> Deleted Thumbnails and compacted. Now the size of digikam4.db is >>> ~20MB. digiKam starts and everything seems to be working... >>> >>> I can see that there is actually 4 tables that have been removed: >>> CustomIdentifiers >>> FilePaths >>> Thumbnails >>> UniqueHashes >>> >>> How about removing them all from the old db? What might happen? >>> >>> /Anders > > Hello, > > Just a hint, > probably no need that somebody writes a script to automatize that > kind of task. > The command line SQLite client can already work with commands files. > > Write SQL commands (and metacommands, at least the final .exit) you want > to do in a simple text file. Let's call it "cleanup" and it contains the > two following lines : > > delete from Thumbnails; > .exit > > then run the command (after backup of your .db file, as usual) : > sqlite3 -init cleanup digikam4.db > > and you're done. > > And if you wish to do more jobs and empty more tables, as Anders > suggested, just change the file into : > > delete from Thumbnails; > delete from CustomIdentifiers; > delete from FilePaths; > delete from UniqueHashes; > .exit > > >> Make a backup and drop table using sqlite manager. > > Gilles, did you meant "drop" or "empty" the table ? > IMHO I think dropping tables should be discouraged. > > delete from Thumbnails; > will remove all data tuples in the table, making the table empty. > > drop table Thumbnails; > will destroy also the table, thus altering the DB schema and most > applications don't really love missing tables. > > I don't know what the sqlitebrowser does exactly, but this issue should > probably be considered. > > Jean-François > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Jean-François Rabasse
Hi Jean-François,
digiKam used to have thumbnail images in the file system. Do you suggest that the thumbnails db should be removed in the future? (If I read between the lines...) Would probably load thumbnails faster when opening an album with many subalbums. /Anders 2011/11/9 Jean-François Rabasse <[hidden email]>: > > Sorry for the "second shoot" :) > > I forgot to signal that, in my opinion, there's also another space > consuming table that doesn't really need to be backuped, it is the > ImageHaarMatrix table that contains patterns signatures for the > find duplicates function. > > It's the same kind of issue that the images thumbnails, it's nothing > but "scratch data", because both can be rebuilt upon need via the > DK commands menus, "Rebuild Thumbnails" and "Rebuild Fingerprints". > > It's not fundamental application data, just scratch data kept for > faster access. So it can be (or should be) either into a separate > db file or other solution. > Some KDE applications, e.g. the Dolphin files browser, use a dedicated > directory for that $HOME/.thumbnails. And, from an application program > point of view, loading a small amount of data (a thumbnail image file > is usually 10 or 12 Kbytes) is faster from a disk file than via the > blob management of a RDBMS (that also access data on the disk, plus the > SQL and blob I/O overhead). > > Jean-François > _______________________________________________ > Digikam-users mailing list > [hidden email] > https://mail.kde.org/mailman/listinfo/digikam-users > > Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Jean-François Rabasse
2011/11/9 Jean-François Rabasse <[hidden email]>
yes, i know this, and we already talk with Marcel about. I agree to move this data to another delegate DB file.
Please report this wish in a new bugzilla file.
The thumbnail storing place used by Dolphin is defined in opendesktop paper. digiKam support this rule in the past, and can support it if really necessary as a compilation time option (look cmake configure option), but this way is deprecated :
1/ PNG format is used to store thumbnail which space consuming (digiKam use PGF format based on wavelets compression which is definitively better to optimize space and speed) 2/ Storing place of thumbs is not configurable (digiKam thumbs DB can be). It will fill your home directory
3/ We don't care about thumbs sharing, especially about dumy thumbs generated by another application. digiKam thumbs generator is better in all case (try to support all RAW files with dolphin for ex...)
4/ digiKam thumbs DB can handle removable media. OpenDesktop cannot do. Gilles Caulier _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Anders Stedtlund
2011/11/10 Anders Stedtlund <[hidden email]>
Hi Jean-François, It will never be. Look my previous mail. digiKam thumbnails DB is the better way and it promise new future features... Gilles Caulier
_______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
Re: Some questions about digikam4.db
|
|
In reply to this post by Anders Stedtlund
On Thu, 10 Nov 2011, Anders Stedtlund wrote: > Hi Jean-François, > > digiKam used to have thumbnail images in the file system. Do you > suggest that the thumbnails db should be removed in the future? (If I > read between the lines...) Hi Anders, Oh no, I didn't meant "removed"; having thumbnaisl under hand is an important feature for such an application. My opinion is that that kind of data, thumbnails or fingerprints, should probably not be part of the master DB file. Because it's not part of the DB schema, it's only auxiliary data, setup in advance for faster access upon need, and can be rebuilt at any time, so don't need to be backuped even in case of disk disaster. But saving that data "outside" the master DB, be it into another DB file or in the file system, or whatever, is of little importance, the application knows how to get it and it's only a development choice. The inconvenient of "packing" all in one DB file is size (as was the start point of this thread). When one looks at Digikam versions using two files, digikam4.db and thumbnails-digikam.db, one sees the second file is about 10 times bigger ! Jean-François PS: another argument for the "splitted" philosophy is when an images application runs on different machines. It's probably not a Digikam issue, but it's a common configuration in web based images bank applications; the main application (CGI program) is on one machine running a HTTP server, the database is on another machine running a SQL server, Oracle, PorsgreSQL, MySQL, etc., protected on a local network. The design rule says "data should be where it's needed", so the application will access master DB data via SQL requests, but will load and send/display thumbnails images from it's local disk system. If you put thumbnails in the main DB, you jam your local network bandwidth. So, splitting -> more flexibility and in some cases higher performances. _______________________________________________ Digikam-users mailing list [hidden email] https://mail.kde.org/mailman/listinfo/digikam-users |
«
Return to digikam-users
|
1 view|%1 views
| Free forum by Nabble | Edit this page |