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

[bugzilla_noreply]

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

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

Apply patch #95867 to filter out existing zero (no icon set) icon values during
migration. This is required to enforce referential integrity during migration
to a MySQL database.

M  +4    -4    data/database/dbconfig.xml.cmake.in

http://commits.kde.org/digikam/1f1855693efd9b01a7149853eb59596b1831b5f4

--
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 #95867|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 #53 from [hidden email] ---
Git commit 3eefa0b7493ee7abb72d6532e618a3769e181e2f by Gilles Caulier.
Committed on 02/12/2015 at 21:28.
Pushed by cgilles into branch 'master'.

Apply patch #95866 to force use of InnoDB engine with all Mysql database table.
Related: bug 281838, bug 331628
FIXED-IN: 5.0.0

M  +3    -1    NEWS
M  +40   -21   data/database/dbconfig.xml.cmake.in

http://commits.kde.org/digikam/3eefa0b7493ee7abb72d6532e618a3769e181e2f

--
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 #95866|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 #54 from [hidden email] ---
Richard,

The Mysql Database schema must take a care at least about these bugs :

1/ optimisations with foreign keys . I see that you start to do it with FaceDb.
I don't know if you plan for other Db.

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

2/ Migration dysfunctions : i don't know if they still valid or not.

https://bugs.kde.org/show_bug.cgi?id=286492
https://bugs.kde.org/show_bug.cgi?id=350574
https://bugs.kde.org/show_bug.cgi?id=316690
https://bugs.kde.org/show_bug.cgi?id=325655

3/ Mysql and case-insensitive file path :

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

4/ Items filter which doesn't work for video type-mime :

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

They are other entries to check, but we will take a look later...

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 #55 from Richard Mortimer <[hidden email]> ---
Created attachment 95928
  --> https://bugs.kde.org/attachment.cgi?id=95928&action=edit
Experiment with TagsTree tables.

Build a TagsTree2 file that mimics the existing behaviour
of the MySQL Tags table and use that for all tags tree queries.
The advantage of a separate table is that it can be rebuilt
without destroying or changing main Tags table.

This is experimental and it would be good if people can
exercise the relevant tags behaviour against their database
to ensure that a wide variety of queries have been tested.

If all goes well the intention would be to rename all uses
of TagsTree2 to TagsTree prior to final committal.

--
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 #56 from [hidden email] ---
Richard,

How to test quickly your new patch #95928 exactly ?

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 #57 from Richard Mortimer <[hidden email]> ---
(In reply to caulier.gilles from comment #56)
> How to test quickly your new patch #95928 exactly ?
General testing of the tags functionality with a MySQL database. I'm not 100%
sure how to test it all but a lot if it is in the searching by tag hierarchy
and viewing specific tags. I did test the queries manually but could not
excercise the the C++ code to ensure that the relevant SQL generators were
running correctly.

--
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 #58 from [hidden email] ---
Richard

To process C++ test with database, we can use QTest API.

http://doc.qt.io/qt-5/qtest-overview.html

Currently, we have a core/tests/ code to process this kind of check over a
small SQlite database and also MySQL. It located in albummodel and database
sub-dirs.  

https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/show/tests/

This code will generate a small CLI application which will process usual tests
with database schema content. The code init a DK core instance, create a
database with few image collection from data sub folder and try to run all
basic operations in database. The QTest API permit to check the results step by
step. And end we can have a resume of tests processed with success or not.

This kind of tests application is started automatically by the KDE Jenkins
server, after a complete core compilation, started after each commit.

https://build.kde.org/job/digikam%20master%20kf5-qt5/

To have all test code compiled in digiKam, use the cmake flag
-DBUILD_TESTING=ON while configuration of digiKam repository. This will enable
tests sub-dir compilation. the tests CLI tools will be compiled in build
directory and can be started as well from a console :

[gilles@localhost database]$ ./databasefieldstest
********* Start testing of DatabaseFieldsTest *********
Config: Using QtTest library 5.5.1, Qt 5.5.1 (x86_64-little_endian-lp64 shared
(dynamic) release build; by GCC 5.2.1 20151020)
PASS   : DatabaseFieldsTest::initTestCase()
PASS   : DatabaseFieldsTest::testMinSizeType()
PASS   : DatabaseFieldsTest::testIterators()
PASS   : DatabaseFieldsTest::testMetaInfo()
PASS   : DatabaseFieldsTest::testIteratorsSetOnly()
PASS   : DatabaseFieldsTest::testSet()
PASS   : DatabaseFieldsTest::testSetHashAddSets()
PASS   : DatabaseFieldsTest::testHashRemoveAll()
PASS   : DatabaseFieldsTest::cleanupTestCase()
Totals: 9 passed, 0 failed, 0 skipped, 0 blacklisted
********* Finished testing of DatabaseFieldsTest *********

[gilles@localhost database]$ pwd
/home/gilles/Devel/5.x/build/core/tests/database
[gilles@localhost database]$

There are 3 tools  :

-  testdatabase : it reproduce the basis database core init and shutdown. It
used with valgrind to check if core engine has memory leak. You can forget this
one for the moment.

- databasefieldstest : this one is check if the field is DB are processed with
core engine API properly. This one do not play with tags tree.

- albummodeltest : create another small database (SQlite only for the moment)
to check DB album properties in all conditions.

So, if you need a QTest code to check tags tree in DB, i recommend to create a
new small CLI tool based on 2 last one previously described. It's not very well
complicated. init and populate a small DB (Sqlite/Mysql internal) with a small
collection of image including tags in metadata. This will load automatically
the tags tree in DB with DB scan code. After that you can try to change tags
tree as you want in code and use QTest to check if the changes results is right
or not.

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 #59 from [hidden email] ---
Richard, i tested your patch #95928, and i can see a lots of dupplicates tags
created with the same name when i create a new database (mysql internal).

My collection of image :

digikam version 5.0.0-beta3
Images:
JP2: 10
JPG: 1549
PGF: 7
PNG: 141
PPM: 2
RAW-ARW: 13
RAW-CR2: 45
RAW-CRW: 5
RAW-DCR: 2
RAW-DNG: 18
RAW-MRW: 18
RAW-NEF: 19
RAW-ORF: 4
RAW-PEF: 7
RAW-RAF: 10
RAW-RAW: 1
RAW-RWL: 1
RAW-X3F: 2
TIFF: 28
total: 1882
:
Videos:
MOV: 6
total: 6
:
Total Items: 1888
Albums: 164
Tags: 90
:
Database backend: QMYSQL
Database internal server: Yes
Database internal server Path: /mnt/data

While importing collection in DB, i can see also these warning, about
versionning metadata imported from XMP :

digikam.dimg: "/mnt/data/TEST_IMAGES/splashs/showfoto-splash.png"  : PNG file
identified
digikam.database: Adding new item
"/mnt/data/TEST_IMAGES/splashs/showfoto-splash.png"
digikam.metaengine: DateTime => Exif.Photo.DateTimeOriginal =>
QDateTime(2010-12-29 00:00:00.000 CET Qt::TimeSpec(LocalTime))
digikam.metaengine: DateTime => Exif.Photo.DateTimeOriginal =>
QDateTime(2010-12-29 00:00:00.000 CET Qt::TimeSpec(LocalTime))
digikam.metaengine: "/mnt/data/TEST_IMAGES/splashs/showfoto-splash.png"  ==>
Read Iptc Keywords:  ()
digikam.database: Pick Label found :  0
digikam.database: Cannot find Pick Label Tag for :  0
digikam.database: Color Label found :  0
digikam.database: Cannot find Color Label Tag for :  0
digikam.metaengine: Loading image history  ""
digikam.database: Scanning took 3 ms
digikam.database: Finishing took 1 ms
digikam.database: Folder does not exist or is not readable:
"/mnt/data/lost+found"
digikam.database: Attempt to create tag properties for tag id 0
digikam.database: items to tag (2, 178, 184, 1199, 1383, 1450, 1543, 1563,
1567, 1568, 1806, 1814, 1837, 1838, 1843, 1844, 1846, 1849, 1850, 1854, 1858,
1859, 1860, 1861, 1864, 1865, 1869)
digikam.database: Broken history: Same file referred by different entries.
Refusing to add a loop.
digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 2 UUID: e8ff0e... }"

digikam.database: Graph with 2 vertices:
"{ Ids: () UUID: a7150e... } -> { Id: 178 UUID: a00753... }"

digikam.database: Image 178 type QFlags(0x8)
digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 184 }"

digikam.database: Graph with 2 vertices:
"{ Ids: () UUID: fcac8f... } -> { Id: 1199 UUID: c9fecb... }"

digikam.database: Image 1199 type QFlags(0x8)
digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1383 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1450 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1543 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1563 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1567 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1568 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1806 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1814 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1837 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1838 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1843 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1844 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1846 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1849 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1850 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1854 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1858 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1859 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1860 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1861 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1864 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1865 }"

digikam.database: Graph with 1 vertices:
"Unconnected: { Id: 1869 }"

digikam.database: Complete scan took: 31320 msecs.

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 #60 from Richard Mortimer <[hidden email]> ---
Gilles,

Many thanks for the information and the testing feedback. I have not had much
time to look into this yet.

A quick look at the schema does indicate that there is a missing UNIQUE clause
in the Tags table definition for MySQL. I do not think that is the full
solution but it will certainly stop duplicate entries reaching 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 #61 from [hidden email] ---
Richard,

I started to write a test cases implementation for Mysql and tree-view. I need
to fix some point of Mysql database settings, especially with internal database
server that i will use for that.

Code is here but not completed and compiled for the moment :

https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/master/entry/tests/database/databasetagstest.cpp

I will continue this week end and during this Christmas holidays...

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

--- Comment #62 from Felix Leif Keppmann <[hidden email]> ---
Hello,


currently I face the issue of not being able to completely delete directories.
Directories are deleted on disk, but not deleted from the database, i.e.,
obsolete ghost directories remain in Digikam's album tree.

Seems to be related to failing MySQL queries:

 "UPDATE Albums SET albumRoot=0, relativePath=? WHERE id=?;"
Error messages: "QMYSQL3: Unable to execute statement" "Cannot add or update a
child row: a foreign key constraint fails (`digikam_core`.`Albums`, CONSTRAINT
`Albums_AlbumRoots` FOREIGN KEY (`albumRoot`) REFERENCES `AlbumRoots` (`id`) ON
DELETE CASCADE ON UPDATE CASCADE)" 1452 2
Bound values:  (QVariant(QString, "1-/test"), QVariant(int, 2))

Log:
https://bpaste.net/show/120359f6c1f2

Version:
5.0.0-beta3 <-> 5.0.0-beta4
Commit c75e88ddb078cc2aef3ea3427b934fe18fda906f
Sat, 20 Feb 2016 08:35:35 +0000 (09:34 +0059)

Setup / Reproduce:
1) Digikam started with three empty MySQL databases, i.e., core, faces, and
thumbnails
2) Add test collection
3) Add test album / directory to collection
4) Delete test album -> failure, i.e., album stays in Digikam album tree,
deleted on disk, MySQL error in log
5) Delete test (ghost) album again -> no change

Maybe this helps to fix it.
As workaround, recreating a ghost album as directory manually at disk makes it
reusable in Digikam.
I run Digikam with MySQL databases and would be glad to help testing.


Regards
Felix

--
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
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--- Comment #63 from [hidden email] ---
Hello Richard.

I'm Swati, currently working on digiKam MySQL database support project. I read
this open bug for MySQL Schema improvements and thought of a plausible solution
to handle the tags (rename, move or delete)

An image can have many tags and a tag can belong to multiple image. So to
handle this many-many relationship between the two, we could take use of the
following tables:

 Table: Image
Columns: Image_ID, Image_Title

Table: Tag
Columns: Tag_ID, Tag_Title

Table: Image_Tag
Columns: Tag_ID, Image_ID

Setting the Image_ID and Tag_ID as foreign keys would help in linking the
tables.

Maybe this will help.

Regards
Swati

--
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 #64 from Richard Mortimer <[hidden email]> ---
Apologies. Life has caught up with me a little these past months. Things are
still busy but here are a few thoughts...

--
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 #65 from Richard Mortimer <[hidden email]> ---
(In reply to Felix Leif Keppmann from comment #62)

>
> currently I face the issue of not being able to completely delete
> directories. Directories are deleted on disk, but not deleted from the
> database, i.e., obsolete ghost directories remain in Digikam's album tree.
>
> Seems to be related to failing MySQL queries:
>
>  "UPDATE Albums SET albumRoot=0, relativePath=? WHERE id=?;"
> Error messages: "QMYSQL3: Unable to execute statement" "Cannot add or update
> a child row: a foreign key constraint fails (`digikam_core`.`Albums`,
> CONSTRAINT `Albums_AlbumRoots` FOREIGN KEY (`albumRoot`) REFERENCES
> `AlbumRoots` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)" 1452 2
> Bound values:  (QVariant(QString, "1-/test"), QVariant(int, 2))
>

I think the issue here is that the database is trying to use albumRoot=0 to
mark the album as being disconnected/deleted. However with referential
integrity set this now is not allowed.

This seems to be happening in CoreDB::makeStaleAlbum(int albumID)

The lifecycle of stale albums needs considering a little more.

--
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 #66 from Richard Mortimer <[hidden email]> ---
(In reply to swatilodha27 from comment #63)

> Hello Richard.
>
> I'm Swati, currently working on digiKam MySQL database support project. I
> read this open bug for MySQL Schema improvements and thought of a plausible
> solution to handle the tags (rename, move or delete)
>
> An image can have many tags and a tag can belong to multiple image. So to
> handle this many-many relationship between the two, we could take use of the
> following tables:
>
>  Table: Image
> Columns: Image_ID, Image_Title
>  
> Table: Tag
> Columns: Tag_ID, Tag_Title
>  
> Table: Image_Tag
> Columns: Tag_ID, Image_ID
>
> Setting the Image_ID and Tag_ID as foreign keys would help in linking the
> tables.
>

Foreign keys have already been added here.

CONSTRAINT ImageTags_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON
DELETE CASCADE ON UPDATE CASCADE,
                            CONSTRAINT ImageTags_Tags FOREIGN KEY (tagid)
REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE,

Without seeing a specific error I cannot tell what the problem may be with
rename, move or delete.

--
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 #67 from Richard Mortimer <[hidden email]> ---
Apart from testing and fixing issues I am aware of the following tasks that
need completing for MySQL support.

1) Test and refine the "Experiment with TagsTree tables" patch to ensure that
it maintains the tags tree correctly.
2) Add a migration script to upgrade existing MySQL databases with the
referential integrity patches.
3) Look at image, album and tag rename, move and delete. The existing SQL
assumes too many magic "0" values and intermediate steps where referential
integrity would be broken.
4) Write unit tests for the various database operations. This should help with
the above steps.

--
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 #68 from [hidden email] ---
Hello Richard.

I guess then the only possible way is to reproduce every error reported by the
end user and try to generate it.

Queries in relation to the points you mentioned for completing MySQL support:
1)In the  "Experiment with TagsTree tables" patch, only testing needs to be
done? To ensure that it generates expected results?

2) To add a script for migration of databases with referential integrity
patches, would it require to create a new table in the database? I'm not
actually clear on this point.

3) To accurately make image/album/tag function, referential integrity is the
best possible solution in MySQL. To check if it is broken, multiple tables in
DB need to be checked and figure out in which one a record is missing, which is
present in other tables. I guess this could be a solution?

Regards
Swati

--
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 #69 from Richard Mortimer <[hidden email]> ---
(In reply to swatilodha27 from comment #68)
> Hello Richard.
>
> I guess then the only possible way is to reproduce every error reported by
> the end user and try to generate it.
Some errors may be obvious by just looking at the code. But reproducing things
is definitely a good way to start. It also means that you can test the fix!

>
> Queries in relation to the points you mentioned for completing MySQL support:
> 1)In the  "Experiment with TagsTree tables" patch, only testing needs to be
> done? To ensure that it generates expected results?
Yes. I think it replicates existing behaviour.

>
> 2) To add a script for migration of databases with referential integrity
> patches, would it require to create a new table in the database? I'm not
> actually clear on this point.
No. It just requires adding "alter table" commands to convert the previous
schema to the new format. It should be pretty straightforward. But in many ways
it is easier to do that when the referential integrity setup has been tested
for new databases.

During migration there is a chance that there will be some rows that have
broken referential integrity. That said MySQL support has always been
experimental so it is probably reasonable to expect that users may need to
perform some cleanups to get an existing database to migrate to a
non-experimental version.
>
> 3) To accurately make image/album/tag function, referential integrity is the
> best possible solution in MySQL. To check if it is broken, multiple tables
> in DB need to be checked and figure out in which one a record is missing,
> which is present in other tables. I guess this could be a solution?
Yes that is correct.

But note that historically Digikam has not used a strict referential integrity
solution. In a number of cases it uses zero instead of what should be null
along with other "magic" values. In order to eliminate these from the database
the actual C++ code will likely need changing to stop it relying on the magic
zero values. Much of this code is shared with SQLite so care has to be taken to
ensure that the new code works with both databases. Finding these locations
will require some analysis of the code to audit all uses of a particular field
with correction where required.

The other issue (I'm guessing here based on what I've seen so far) is that
digikam seems to perform two stage database operations in some cases. In the
deletion example it seems to update the database to mark that an object is
about to the deleted. Then it tries the deletion and if successful goes ahead
and deletes the item from the database. In the case I looked at it seemed that
referential integrity was broken between the two database operations and that
is why the operation failed in MySQL. If my assessment is correct then some
thought needs giving to how the operation can be performed in a database that
enforces referential integrity.

Regards

Richard
>
> Regards
> Swati

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