[digikam] [Bug 355831] New: MySQL Schema Improvements

[bugzilla_noreply]

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

Richard Mortimer <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95823|0                           |1
        is obsolete|                            |

--- Comment #37 from Richard Mortimer <[hidden email]> ---
Created attachment 95825
  --> https://bugs.kde.org/attachment.cgi?id=95825&action=edit
Standalone MySQL Thumbnail settings to ThumbSettings table

--
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=355831

--- Comment #38 from [hidden email] ---
Thanks Richard,

I will review your patches tomorow or wenesday...

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

[bugzilla_noreply]

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

--- Comment #39 from Richard Mortimer <[hidden email]> ---
Gilles,

1) I did not yet get chance to rebase the other two patches to separate those
from the face and thumbnail patches. I will do so within the next day.

2) I started to look at the Tags and Albums icon code. The entire code seems to
assume that an icon value of zero means no icon. This is not good for
referential integrity. Ideally all usages of qlonglong for icon value should be
replaced with something like QVariant that allows a null value too. I think
this is too big a change at the moment so my suggestion is that the database
storage code is modified to hardcode 0 as being equivalent to null in the
database. I did a quick test on both MySQL and SQLite and both work fine with
that assumption because when reading from the database a null icon is turned
into a zero when reading a longlong from QVariant that is null. Doing a full
QVariant conversion for icon fields might be something that could be included
in a GSoC project for PostgreSQL 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

[bugzilla_noreply]

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

--- Comment #40 from [hidden email] ---
Git commit 04baf90347ae3365378333f6ae4f84c1dc39c73d by Gilles Caulier.
Committed on 01/12/2015 at 21:31.
Pushed by cgilles into branch 'master'.

apply patch #95824 to :
Use FaceSettings table to allow shared database in MySQL.
Use InnoDB engine to ensure that foreign key relationships are enforced.
Use foreign key between Identities and IdentityAttributes.

M  +31   -16   data/database/dbconfig.xml.cmake.in
M  +9    -6    libs/facesengine/facedb/facedb.cpp
M  +1    -1    libs/facesengine/facedb/facedb.h

http://commits.kde.org/digikam/04baf90347ae3365378333f6ae4f84c1dc39c73d

--
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=355831

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95824|0                           |1
        is obsolete|                            |

--
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=355831

--- Comment #41 from [hidden email] ---
Git commit 9b543e6a84a15af6d29f70c39a4964992f42e7cf by Gilles Caulier.
Committed on 01/12/2015 at 21:34.
Pushed by cgilles into branch 'master'.

Apply patch #95825 to :
Allows the thumbnail and main image databases to be contained within the same
database.
Also ensure that thumbnail MySQL tables use the InnoDB storage engine and not
the older MyISAM engine.

M  +66   -10   data/database/dbconfig.xml.cmake.in
M  +27   -4    libs/database/thumbsdb/thumbsdb.cpp
M  +1    -0    libs/database/thumbsdb/thumbsdb.h
M  +23   -1    libs/database/thumbsdb/thumbsdbchemaupdater.cpp
M  +1    -0    libs/database/thumbsdb/thumbsdbchemaupdater.h

http://commits.kde.org/digikam/9b543e6a84a15af6d29f70c39a4964992f42e7cf

--
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=355831

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95825|0                           |1
        is obsolete|                            |

--
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=355831

--- Comment #42 from Richard Mortimer <[hidden email]> ---
Created attachment 95860
  --> https://bugs.kde.org/attachment.cgi?id=95860&action=edit
Store empty icon (image 0) as a NULL value

Tested on both SQLite and MySQL databases. Album and tag icons can be set and
cleared.

--
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=355831

Richard Mortimer <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95731|0                           |1
        is obsolete|                            |
  Attachment #95746|0                           |1
        is obsolete|                            |

--- Comment #43 from Richard Mortimer <[hidden email]> ---
Created attachment 95861
  --> https://bugs.kde.org/attachment.cgi?id=95861&action=edit
Remove coredb trigger dependency for MySQL.

This is still a work in progress.

Known issues:

1 - Tags broken on MySQL.
2 - Existing "zero" value icons break database migration.
3 - No support for upgrade of existing coredb contents on MySQL.
4 - On migration from SQLite to MySQL I noticed breakages in filenames on child
albums thumbnails. Works fine on a fresh MySQL database setup. Needs more
investigation.

--
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=355831

--- Comment #44 from [hidden email] ---
Git commit bca001b16dd6aba437aece1bc09e9400ec300d1d by Gilles Caulier.
Committed on 02/12/2015 at 10:50.
Pushed by cgilles into branch 'master'.

Apply patch #95860 to store empty icon (image 0) as a NULL value with SQlite
and Mysql Databases

digiKam treats image zero as a "not set" icon value. This
breaks referential integrity in the database because image
zero does not exist. Explicitly convert a value of zero
into NULL when stored in the database.

Ideally digiKam should use an explicit null (in QVariant?)
placeholder for the no-icon case but currently no database
stores an image zero by default so the existing practice
works.

Note that when reading from the database the toLongLong
method of QVariant returns a zero when called against a
null value so this means that no changes are required
when reading null values in from the database.

M  +26   -5    libs/database/coredb/coredb.cpp

http://commits.kde.org/digikam/bca001b16dd6aba437aece1bc09e9400ec300d1d

--
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=355831

--- Comment #45 from [hidden email] ---
Git commit 801f835f246b2b2a38a886920056c149dd6894c0 by Gilles Caulier.
Committed on 02/12/2015 at 11:04.
Pushed by cgilles into branch 'master'.

apply patch #95861 to remove trigger dependency for MySQL.

Add full referential integrity to AlbumRoots, Albums, Images and Tags
tables entries.
Provide the equivalent behaviour to the triggers using ON DELETE and
ON UPDATE in FOREIGN KEY references.

Use dbaction to perform database specific preparation for migration.

The MySQL schema has a circular dependency and this must be removed
before any leftover contents of the database are removed prior to the
migration. No actions are required for SQLite

Referential integrity in MySQL does not allow the Albums.icon field
to be set prior to the Images table being populated. Use a fake
AlbumsExtra table to copy over the icons data.
Note we use the same procedure for both SQLite and MySQL to ensure
that migration is possible between any combination of databases.

M  +63   -51   data/database/dbconfig.xml.cmake.in
M  +12   -0    libs/database/coredb/coredbcopymanager.cpp

http://commits.kde.org/digikam/801f835f246b2b2a38a886920056c149dd6894c0

--
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=355831

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95861|0                           |1
        is obsolete|                            |

--
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=355831

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95860|0                           |1
        is obsolete|                            |

--
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=355831

--- Comment #46 from [hidden email] ---
Richard,

Did you plan to specific ImnoDb database type with Mysql for Core database
creation action, as with Thumbs and Face databases ?

If yes, this will permit to close bug #331628 as well...

Also, in Mysql, The Core database settings table is named "Settings", where for
Thumbs database is now named ThumbSettings and for Face database FaceSettings.
Why not to rename Settings table as CoreSettings to be homogeneous everywhere ?

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

[bugzilla_noreply]

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

--- Comment #47 from Richard Mortimer <[hidden email]> ---
(In reply to caulier.gilles from comment #46)
> Richard,
>
> Did you plan to specific ImnoDb database type with Mysql for Core database
> creation action, as with Thumbs and Face databases ?
Yes. That seems to have got lost somewhere in all the rebasing.

>
> If yes, this will permit to close bug #331628 as well...
>
> Also, in Mysql, The Core database settings table is named "Settings", where
> for Thumbs database is now named ThumbSettings and for Face database
> FaceSettings. Why not to rename Settings table as CoreSettings to be
> homogeneous everywhere ?
Will look to implement that when the coredb MySQL schema upgrade scripts are
written.

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=355831

--- Comment #48 from Richard Mortimer <[hidden email]> ---
Created attachment 95865
  --> https://bugs.kde.org/attachment.cgi?id=95865&action=edit
MySQL database permissions/setup guidance streamlining.

Make it clear that the password is specific to the account/hostname combination
and not the database.

--
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=355831

--- Comment #49 from Richard Mortimer <[hidden email]> ---
Created attachment 95866
  --> https://bugs.kde.org/attachment.cgi?id=95866&action=edit
Specify InnoDB engine on setup for coredb tables.

--
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=355831

--- Comment #50 from Richard Mortimer <[hidden email]> ---
Created attachment 95867
  --> https://bugs.kde.org/attachment.cgi?id=95867&action=edit
Do not migrate "zero" icon values for Albums and Tags

MySQL referential integrity does not allow to refer to a non-existant
placeholder image. Use a null value instead.

--
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=355831

--- Comment #51 from [hidden email] ---
Git commit 5c1416f17b230a598b8938ee3a18bb31b65303d0 by Gilles Caulier.
Committed on 02/12/2015 at 21:21.
Pushed by cgilles into branch 'master'.

Apply patch #95865 to rewrite MySQL database creation and setup guidance.
The MySQL password is tied to the account not the database.

M  +10   -8    libs/database/utils/dbsettingswidget.cpp

http://commits.kde.org/digikam/5c1416f17b230a598b8938ee3a18bb31b65303d0

--
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=355831

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #95865|0                           |1
        is obsolete|                            |

--
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