Version: (using KDE 4.4.3) Installed from: Ubuntu Packages I think that adding foreign keys would avoid many of the current triggers in the database schema, and I guess that the database would be more efficient. I have added foreign keys to the current database schema (see attachment). I have not added a foreign key from the icon column of the Albums table to the id column of the Images table since this would introduce a circular dependency between the Albums and Images tables. However, this would be easily solved by adding a new AlbumIcon table or similar. Note that SQLite version 3.6.19+ is needed to execute these statements (see http://www.sqlite.org/foreignkeys.html). I have migrated my current database to the updated schema and it seems to work without problems with Digikam 1.2.0. Best regards.
Created attachment 43648 [details] Database schema with foreign keys
digiKam 1.3.0 will support MySQL too. SQlite still supported of course. In settings panel, you can choose the DB backend. Your DB schema with foreign keys will be fine with MySQL ? Note : later, PostgreSQL will be supported too... Gilles Caulier
I think Holger already removed a number of Triggers with his MySQL port. Please note that 3.6.19 is released only last October. Even on my system, which is fairly recent, 3.6.16 is installed. We cannot expect cutting-edge versions of this library.
(In reply to comment #2) > digiKam 1.3.0 will support MySQL too. SQlite still supported of course. In > settings panel, you can choose the DB backend. > > Your DB schema with foreign keys will be fine with MySQL ? MySQL has supported foreign keys since version 3.23.44: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html With some minor changes to the syntax, my database schema should work fine with MySQL. > Note : later, PostgreSQL will be supported too... As far as I remember, PostgreSQL has always supported foreign keys: http://www.postgresql.org/docs/8.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK Also in this case, my database schema should work fine with PostgreSQL. If you can post or send me the current database schema proposed by Holger, I can define and test database schemata on SQLite, MySQL and PostgreSQL. Best regards.
(In reply to comment #3) > Please note that 3.6.19 is released only last October. Even on my system, which > is fairly recent, 3.6.16 is installed. We cannot expect cutting-edge versions > of this library. Ubuntu 10.04 and Fedora 13 already ship SQLite 3.6.22: http://packages.ubuntu.com/search?keywords=sqlite3&searchon=names&suite=lucid§ion=all https://admin.fedoraproject.org/pkgdb/builds/search/repo:F-13*%20sqlite openSUSE 11.3, which is to be released in July, will ship SQLite 3.6.23: http://download.opensuse.org/factory/repo/oss/suse/i586/ PCLinuxOS 2010.1 and Arch Linux 2010.05 also already ship SQLite 3.6.23. Considering that the major distributions all ship SQLite 3.6.19+, there should be no problem in adding a dependency to it. Best regards.
Note: Mysql does not support foreign keys with myisam tables only with innodb ones
(In reply to comment #6) > Note: > Mysql does not support foreign keys with myisam tables only with innodb ones That is correct, but InnoDB is the default storage engine starting in MySQL 5.5: http://dev.mysql.com/tech-resources/articles/introduction-to-mysql-55.html Again, considering that the upcoming releases of the major distributions will all ship MySQL 5.5 as default, there should be no issue in including foreign keys in the database schema. Best regards, Alessandro Rossini
Marcel, I look if we can include foreign keys in database schema - SQlite3 support it since 3.6.19 : http://www.sqlite.org/foreignkeys.html Sqlite current version is 3.8.5 - Mysql support it since 5.5 : http://dev.mysql.com/doc/refman/5.1/en/create-table-foreign-keys.html Mysql current version is 5.6.20 - MariaDb support it but i cannot see since which version exactly ? - Postgresql support it too Gilles
Alessandro, The database schema is common to sqlite and mysql. It defined into an XML file here : https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/data/database/dbconfig.xml.cmake.in We need a patch against this file to include foreign keys. Gilles Caulier
Foreign keys support now exists in digiKam5.0.0-beta6 version. (It supports use of MySQL Internal & Remote both.)
Richard, Can we considering this file is implemented for next 5.0.0 release, following all your patches applied to the code last winter ? Gilles Caulier
Gilles, I think we can say that we have the basic support there but there are still bugs to be worked out. I know Swati is working on those. Richard
Richard, Please look the story on this entry https://bugs.kde.org/show_bug.cgi?id=363697 ... and give us your viewpoint about the solution given in patch about foreign keys and all other pending questions, especially the comment #5. Thanks in advance. Gilles
digiKam latest version 5.0.0 very well uses the foreign keys support. Here, SQLite and MySQL both support foreign keys well. It is not only essential for data consistency, but also to make sure that wrong queries aren't executed. So it's definitely an advantage to include it in DB schema. Richard, Can this file be closed now after improving MySQL support ? Thanks.
Yes. I think this can now be considered resolved.
Thanks Richard. I close this file now. Gilles Caulier