[Bug 237878] New: Add foreign keys to the database schema

https://bugs.kde.org/show_bug.cgi?id=237878

           Summary: Add foreign keys to the database schema
           Product: digikam
           Version: unspecified
          Platform: Ubuntu Packages
        OS/Version: unspecified
            Status: UNCONFIRMED
          Severity: wishlist
          Priority: NOR
         Component: general
        AssignedTo: [hidden email]
        ReportedBy: [hidden email]


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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=237878





--- Comment #1 from Alessandro Rossini <me alessandrorossini org>  2010-05-16 22:19:32 ---
Created an attachment (id=43648)
 --> (http://bugs.kde.org/attachment.cgi?id=43648)
Database schema with foreign keys

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878


Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|general                     |Database
            Version|unspecified                 |1.3.0




--- Comment #2 from Gilles Caulier <caulier gilles gmail com>  2010-05-17 09:23:54 ---
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

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Marcel Wiesweg]

https://bugs.kde.org/show_bug.cgi?id=237878





--- Comment #3 from Marcel Wiesweg <marcel wiesweg gmx de>  2010-05-17 18:31:05 ---
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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=237878





--- Comment #4 from Alessandro Rossini <me alessandrorossini org>  2010-05-21 12:52:01 ---
(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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=237878





--- Comment #5 from Alessandro Rossini <me alessandrorossini org>  2010-05-21 13:32:51 ---
(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&section=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.

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Francesco Riosa-2]

https://bugs.kde.org/show_bug.cgi?id=237878


Francesco Riosa <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]




--- Comment #6 from Francesco Riosa <francesco+kde pnpitalia it>  2011-06-28 21:35:17 ---
Note:
Mysql does not support foreign keys with myisam tables only with innodb ones

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

https://bugs.kde.org/show_bug.cgi?id=237878





--- Comment #7 from Alessandro Rossini <me alessandrorossini org>  2011-07-04 14:54:17 ---
(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

--
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]
            Summary|Add foreign keys to the     |MySQL : Add foreign keys to
                   |database schema             |the database schema

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MySQL : Add foreign keys to |MySQL : Add foreign keys to
                   |the database schema         |the database schema [patch]

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

--- Comment #8 from Gilles Caulier <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|MySQL : Add foreign keys to |Add foreign keys to the
                   |the database schema [patch] |database schema

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

--- Comment #9 from Gilles Caulier <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Add foreign keys to the     |Add foreign keys support to
                   |database schema             |database schema

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
            Summary|Add foreign keys support to |SCHEMA : add foreign keys
                   |database schema             |support

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[Gilles Caulier-4]

https://bugs.kde.org/show_bug.cgi?id=237878

Gilles Caulier <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
          Component|Database                    |Database-Schema

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=237878

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #10 from [hidden email] ---
Foreign keys support now exists in digiKam5.0.0-beta6 version.

(It supports use of MySQL Internal & Remote both.)

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=237878

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #11 from [hidden email] ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=237878

--- Comment #12 from Richard Mortimer <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel

[bugzilla_noreply]

https://bugs.kde.org/show_bug.cgi?id=237878

--- Comment #13 from [hidden email] ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
_______________________________________________
Digikam-devel mailing list
[hidden email]
https://mail.kde.org/mailman/listinfo/digikam-devel