digiKam with another database backend than SQLite

Last summer I saw bug #127321 an thought, this can be done by me (partly at least).
After some (successful) prototypic implementation, most of the time where spent into smooth integration in the existing
project:

  • adding a solution for migrating from SQLite to MySQL and vice versa
  • add possibiliy to start an own database server
  • and bug fixing...

Because only a few persons trying out this work (which resides atm. in a dedicated branch), the most bugs are possible undiscovered.

So if you are brave enough to crash your whole existing database structure (images are not touched), have a little amount of free time or you would like
to have the future right now and you are able to compile your version by yourself: You are the right person :)

(For the last point: If you are not yet familiar on how to test current development code, this page gives you a short introduction how to get and compile digiKam from source.
For reporting bugs please use the normal KDE bug tracker.

The dedicated branch is located at this url from KDE svn repository

Assume you compiled your version and would like to test digikam:
After the first start, your existing database settings will be converted to an extended format.
After that, your are using digiKam in SQLite mode, it should work like before (from users point of view).

To switch to MySQL mode:

  • go to the database settings (Settings -> Configure Digikam -> Database)

    Database Settings

  • at the database type combobox, choose QMYSQL as backend.
  • now your have 2 options:
  • -> use an existing database management server where you have created a dedicated account
    - fill in all needed data for that
    -> use an internal database management server
    - this mode works like the internal akonadi database server mode (thanks goes to the akonadi people - great work!), but has a little bit improved
    - only check the "Internal Server" checkbox

  • press "Check connections" - A dialog should show you, that the connection could be established with the current settings.
  • press "OK"
  • The new settings should applied. You are ready to test =).

To copy your SQLite data to your MySQL database do the following:

  • go to the migration dialog (Tools -> Database Migration)

    Database Migration Dialog

  • As source (left side) select your SQLite database
  • As target (right side) type in your MySQL database connection database
  • Press the "Migrate ->" button and enjoy the progress on the bottom of the dialog

Notes:
1) [General] If the connection is down, digiKam stops its database actions, and tries to reconnect.

Information about connection errors

If the database link appears again, digiKam continues it's work.
You can cancel this waiting behaviour but then you have only limitied access (almost nothing) to the digikm application.
(But you can change the database settings, in case that the host has changed or something else.)
2) [Internal Database Server] The digiKam database server is started only once per system. It registers on D-Bus and watches all digiKam
instances of the same user. It stops, if these instances are closed.

Background information:
It's easy to add a new database backend like PostgreSQL to digiKam, because most of the sql statements are moved out from code to a configuration file
(which can be extended on demand). It exists only specific code for the database server and configuration dialog.

Finally: Thanks goes to the great digiKam team and Marcel in particular, which gaves many ideas, hints and coding examples.

Advantages?

What uses does having a MySQL backend? It is faster or more scalable?

Re: Advantages?

Well, if you have one running, why wasting ressource. On top of that, SQLite have huge regressions with recent kernel according to phoronix (I think it is one of the only test that can be trusted out of the whole PTS test suite).

The reason I always use Amarok MySQL capabilities is because it allow me to have multiple account connected simultaneously to the same DB and work. Of course, it is not really the case with Digikam, as the DB was stored in the picture folder. But some people would be able to delete it because it does not look like a picture (and it is not one) so they might wonder what that huge file is doing there and delete it.

But MySQL is faster and can unload CPU+I/O from my desktop and use my external DB server, so I will test, use and debug this feature when I will have some time.

CPU+I/O?

mysql is running?

In my case it's the

In my case it's the flexibility you get when using an "real" dbms. You can place pictures and database on a central media pc (NAS). All computers in your home network can connect to the database and share its metadata.
Also you can access your data with third-party software.
Maybe you want extract some metadata from it, or just want to perform custom queries.

href?

location of the branch "database-ext"?

is there...

http://websvn.kde.org/branches/extragear/graphics/digikam/1.0-databaseext/

svn

Hi
I tried this and it doesnt work :
svn co http://websvn.kde.org/branches/extragear/graphics/digikam/1.0-databaseext/digikam/

Repository moved permanently to '/branches/extragear/graphics/digikam/1.0-databaseext/digikam/'; please relocate

Thanks for any help
M

Oh, I forgot to link the

Oh, I forgot to link the Howto compile page:

Howto

As svn checkout just use the following command:
svn co svn://anonsvn.kde.org/home/kde/branches/extragear/graphics/digikam/1.0-databaseext/

Hope, this helps.

svn and future release

Thanks, it works, with some bugs, but it works. I think mysql db is useful for huge amount of pictures. I'm interested. Have you an idea about a release date ?
M

end of may 2010

digiKam 1.3.0, including MySQL support is planed for 30 may 2010.

now, code has been migrated to trunk

Mysql support code is now merged to KDE svn trunk. No need to use dedicated branche.

Now someone please stand up

Now someone please stand up and centralise all db stuff to only one backend. Right now we have one for akonadi, one for nepomuk/strigi, one for amarok, and now there is one for digikam. I know i can configure them to use the same mysql server, but why not do that out of the box? It's some coordination issue between the teams?

(p.s. this captcha sucks big time)

Exactly!

Exactly!

My first thought was: why would you create a dedicated MySql <-> SQLite converter program inside digicam? Isn't something usable already out there? More and more applications interface with databases, and it seems that each of them is writing their own abstraction layer or special casing to work with them. The Qt drivers are not enough, as they don't support database structure manipulation in a database agnostic way. I know, because I am writing my own set of Qt database drivers to support this as well...

Perhaps it is time to bundle these efforts in KDE, and create:
1) A set of extended database drivers that allow database creation and database structure manipulation (create and modify tables and views) in a database-independent way.
2) A common database connection/selection widget
3) A program that can move application databases between database backends, perhaps based on some application-defined script, and perhaps
4) A more friendly API to create database-independent queries on top of the current Qt API, which is a little inflexible and not very nice to use.

>My first thought was: why

>My first thought was: why would you create a dedicated MySql <-> SQLite converter program inside digicam? Isn't something usable already out there?

Yes and no. There are good database convertion tools around, think on DBUnit which is at first a tool for testing, but does a good job to transfer data from one database to another (of another vendor).
I first also thought it where easy to switch the database backends. But since there are features in every dbms which are very useful (both in convenience and performance) but not SQL standard, I had to find a solution, which results in two similar but not exactly the schemas and statements for the database storage. With the current solution, like I wrote in my blog entry, It is possible to add another SQL backend where the effort is lower because it is designed for that.

>[...]Perhaps it is time to bundle these efforts in KDE, and create: Point 1-4[...]
I agree, such thing would be useful!! I would like such an set of tools.
But call me a narrow-minded person, I think that we should do that in small steps.
The feature request where opened in 2006. It's 2010 and we have basic independed database access.
Now we can collect expirience and based on that, sometime and someone (person or team) will create a nice tool/library collection.
Maybe you can open a feature request for this interesting issue on B.K.O. then it will be kept in mind.

For me it is incredibly slow,

For me it is incredibly slow, much much slower than SQLite with EXT4 and barriers turned on (the bottleneck on my system when it comes to digiKam).
Somehow some threads are always blocked and therefore digiKam (or someone else) always waits 10 seconds before it continues to work.
After some operations, something is locked again and digiKam waits another 10 seconds (you can see this information in the console window). I mainly tested "Scan for new images", because I added a new directory for testing...
I used the internal MySQL-Server for testing...

This is the best news this

This is the best news this year!!
I've been waiting for this for more then 2 years now since I want to share the metadata with my girlfriend which is so annoying right now (like rereading metadata every now and then...).

P.S.: The CAPTCHA are really difficult =)

IMHO better to use BercleyDB

IMHO better to use BercleyDB or other file DB, rather then SQL server for this purpose.

Images in DB?

What about putting the images and thumbnails in the database, too?

Does this work?

It would be nice to be able tu use these easily for creating one's own web applications or special scriptings.