Database: Management Functions for Referential Integrity in ilDBInterface

1 Initial Problem

This feature request is part of Project: Establish Referential Integrity (suggested as "Big Project" for ILIAS 9) and refers to Generalized Management Functions for Referential Integrity.

With ILIAS 8 Abandon support for MyISAM engine and MySQL fulltext search has been implemented as a first step to establish an RI in ILIAS.

With ILIAS 9 we would like to make a small step forward (to the final goal: Project: Establish Referential Integrity) by extending the ilDbInterface to handle foreign keys.

2 Conceptual Summary

First of all: In one of our RI related community/feature workshops on 02 Dec 2021 (there were three workshops until now) we discussed and decided that foreign keys MUST NOT be applied beyond own component boundaries. Application wide foreign keys would introduce a new layer of coupling additionally to PHP, which we would like to avoid. Furthermore a maintainer of component A would have to make assumptions on identities (which is alreay a huge issue in ILIAS) of components from maintainer B. Handling this would require tight communication between the developers of database table A and database table B, which could result in locking situations (we already have those in Mantis issues or PRs). Therefore we would like to start with foreign keys beeing applied within isolated components only. This might be changed in the far future if all tools and processes are available and have proofed to be successful.

The README file of the database component will be enriched by the rules of when and how to apply foreign keys, and when/how referential actions (see below) should/can be used.

With this change request we propose to extend the ilDbInterface by defining and implementing the following functions (the signatures are not final, they will be discussed in the implementation phase and code reviews):

• addForeignKey (string $table, string $constraint_name, ReferenceOption $option, array $columns, string $target_table, array $target_columns) : void;
• dropForeignKey (string $table, string $constraint_name) : void;
• foreignKeyExists (string $table, string $constraint_name) : bool;

There is not need for modification since altering the key is limited to the referential actions. This could be achieved by calling dropForeignKey/addForeignKey. ReferenceOption could be a type describing the operation (DELETE or UPDATE) and aggregating the referential actions (could be an [emulated] ENUM):

• CASCADE
• SET NULL
• RESTRICT
• SET DEFAULT
• NO ACTION

The functions will throw respective database exceptions if the change could not be applied.

In addition to the DDL related enhancements there should be a new API (DQL) for developers for checking if a distinct foreign key can be applied. To make it possible to programmatically express such definitions for a foreign key, new database related types should be introduced (we are thinking about something like Field, Association and Definitions). There will be a OK or Error result depending on whether a foreign key can be allied.

  

This API can then be utilized in the diagnostic and migration mechanisms (see: Outlook)

2.1 Why do we want to add these changes to ILIAS 9?

• A benefit from introducing these functions with ILIAS 9 is that foreign keys could already be used by/within plugins. Developers (and primarily component maintainers/contributors) could gain experiences with the API in plugins. The feedback can be processed while designing further tools and mechanisms (see: Outlook).
• Concomitant with the implementation of this feature the change of the maintenance model (from "Maintainer" to "Coordinator") of the Database component could be pushed for ILIAS 9.
• Tesche, Uwe [utesche] as a database expert will leave the Helmut Schmidt Universität, having him available for the implementation, checks/tests and approval for this next step would be highly appreciated.
• Funding for implementation and review (by Schmid, Fabian [fschmid]) for ILIAS 9 is available.

2.2 Outlook

• A mechanism in the ILIAS Setup to detect and report possible violations (with ILIAS 9 the application with existing violations will result in exceptions) will be a next step for ILIAS 10: Database: Detecting Violations for Referential Integrity
  • In our workshop on 02 Dec 2021 we discussed that (detailed information will be put on the feature wiki page):
    • The 'status' command handled should be used for diagnostic purposes.
    • The 'metrics' concept should be enhanced to allow a labeling of metrics with a qualifier.
    • There should be the option to include/exclude metrics depending on a passed qualifier (when executing the PHP cli command).
    • Metrics should be cacheable, therefore we'll need a centralized storage to set/flush values (similar to the decentralized database update steps). 
• Handling possible violations (backup, export, delete) in migrations like outlined in Generalized Management Functions for Referential Integrity could be another ILIAS 10 topic and will be discussed in further workshops.

3 User Interface Modifications

3.1 List of Affected Views

• None
  

3.2 User Interface Details

None

3.3 New User Interface Concepts

None

3.4 Accessibility Implications

None

4 Technical Information

The technical information and implications are described in the conceptual summary.

5 Privacy

None

6 Security

None

7 Contact

• Author of the Request: Jansen, Michael [mjansen]
• Maintainer: Schmid, Fabian [fschmid]
• Implementation of the feature is done by: Databay AG

8 Funding

If you are interest in funding this feature, please add your name and institution to this list.

• …

9 Discussion

Schmid, Fabian [fschmid] 2022-08-05: Thank you very much for the request and the willingness to implement this. I find the suggestion very good, also that experience can already be gained with ILIAS 9. The proposed methods fit from my point of view, also that there is no explicit modifyForeignKey. I fully support the request!

10 Implementation

The class ilDBPdo and the ilDBInterface were extended with four new functions for the creation of foreign key constraints. 

• addForeignKey
• dropForeignKey
• foreignKeyExists
• buildIntegrityAnalyser

Also a new field definition type ( ForeignKeyConstraints ) was created for the usage of constraints with the foreign keys.  For a more detailed overview how to use this new functions you can take a look at the README.md in Services/Database 

Test Cases

The feature implementation contained some new unit tests: https://github.com/ILIAS-eLearning/ILIAS/pull/5338

Approval

Approved at 03.03.2023 by Schmid, Fabian [fschmid].