#215 some tables are not correctly cleaned on dataset/tree deletion

[Till Schäfer]

the tables structure_data, mol_data, and svg_data are not correctly handled on dataset and tree deletion. The tree deletion causes the scaffold_data to be cleand and the dataset deletion causes molecule_data to be cleaned. However, molecules and scaffolds have a common superclass which cannot be cascaded by a foreign key constraint (it is ambigious). Therefore, we will need a solution to clean the super class as well.

[Till Schäfer]

this may be solvable using interceptors
https://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#events

[Philipp Mewes]

Unfortunately, if a tree is deleted, the Interceptor is only triggered for the tree but not for the contained scaffolds. I think the interceptor only works, when an object is deleted directly by the hibernate-framework, but not if it is deleted implicitely by the database.
However the interceptor can be used to do the necessary cleanup when a tree is deleted.

[Philipp Mewes]

Implemented this behaviour ([6512af]). Cleanup for datasets is still missing.

[Philipp Mewes]

The query did not work for datasets. Some foreign key constraints caused trouble. Updated the queries so the structure_data table is cleaned before the respective svg_data and mol_data-entries are removed ([1ee13d]).

[Till Schäfer]

looks good on mysql, but fails on hsql with the exception org.hibernate.exception.SQLGrammarException: could not prepare statement.

This is most likely a problem of different SQL-dialects. HQL would be the better alternative to SQL here, since it abstracts database specific SQL dialects. Please evaluate my first guess and rewrite the statements in HQL. This will also allow us to easily port the backend to new database types in the feature.

MYSQL tested by counting the elements int the tables:

Action          SVG DATA    MOL_DATA    STRUCTURE_DATA  SCAFFOLD_DATA   MOLECULE_DATA
Add Dataset 1   602         602         602             0               602
Add Tree 1.1    1644        1644        1644            1042            602
Add Tree 1.2    2686        2686        2686            2084            602
Delete Tree 1.2 1644        1644        1644            1042            602
Add Tree 1.2    2686        2686        2686            2084            602
Add Dataset 2   3288        3288        3288            2084            1204
Add Tree 2.1    4330        4330        4330            3126            1204
Del Dataset 1   1644        1644        1644            1042            602

[Philipp Mewes]

Using HQL solved the problem ([2416e7]). Nice side-effect: The code becomes much shorter. Also it is not necessary to explicitely access the structure table. HQL automatically also deletes the entries of the super-class in the respective table. However mol_data and svg_data must still be cleaned manually.
Also added a worker and a progress dialog for tree deletion since this process takes more time now ([c59042]).

[Till Schäfer]

Does not delete mol_data and svg_data, when deleting a tree (tested with mysql). Furthermore,it seems that strucQuery.executeUpdate(); in line 62 of DeleteInterceptor can be deleted, since the other strucQuery is updated outsite the if. Plus one minor thing, please use else if in line 64.

[Philipp Mewes]

This is a bit strange. Added a test-query to get SVG entries that are not referenced by a structure any longer:

    FROM Svg svg
    WHERE NOT EXISTS (
            FROM Structure struc
            WHERE struc.svg = svg)

It returns the correct number of orphaned svgs. But running the delete query what looks nearly the same

    DELETE FROM Svg svg
    WHERE NOT EXISTS (
            FROM Structure struc
            WHERE struc.svg = svg)

returns always 0 items deleted, until the last dataset is deleted, then it clears the table completely.

[Philipp Mewes]

By chance i found the solution. In HQL DELETE queries no joins are allowed. In the previous implementation the structure table was joined with the svg table or the mol table respectively in the sub query. By replacing WHERE NOT EXISTS with WHERE NOT IN, the sub query does not need a join any longer ([450f0f]).

[Till Schäfer]

merged in [381d4d]