[Digikam-devel] [digikam] [Bug 355831] MySQL Schema Improvements

[Richard Mortimer via KDE Bugzilla]

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

--- Comment #69 from Richard Mortimer <richm+kde at oldelvet.org.uk> ---
(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.