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

via KDE Bugzilla bugzilla_noreply at kde.org
Sat Apr 30 20:46:39 BST 2016


--- Comment #70 from swatilodha27 at gmail.com ---
(In reply to Richard Mortimer from comment #69)
> (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.

To solve this maybe code could be reviewed in order to cross check that "0"
isn't mentioned in ELSE condition, or in place of NULL.
 But I'm pretty confused on how to find locations where code needs to be
changed, so as to make it compatible with both the databases?

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

Referential integrity can be enforced by strictly not violating any of one of
the following:
1)No record should be allowed to add in the foreign key table, unless there
exists a corresponding record in the superior table.
2) CASCADING UPDATE to automatically update corresponding record if the primary
key record is updated.
3) CASCADING DELETE to automatically delete corresponding record if the primary
key record is deleted.


You are receiving this mail because:
You are the assignee for the bug.

More information about the Digikam-devel mailing list