Feature Wiki
Tabs
Database: Detecting Violations for Referential Integrity
Page Overview
[Hide]1 Initial Problem
This feature request is part of Project: Establish Referential Integrity and refers to Generalized Management Functions for Referential Integrity.
In order to establish Referential Integrity (RI) in the Database Management System (DBMS), there MUST be a process to determine if an intented Foreign Key (FK) would be applicable on the respective field(s) of the affected database table. Administrators and operators of (an) ILIAS installation(s) MUST be able to get an overview of violations based on the current database state to plan their ILIAS updates accordingly.
2 Conceptual Summary
To provide such information we suggest to use the Status/Metrics Concept as a tool to collect the number of violations for an intended FK.
- A component which wants to provide diagnostics of possible violations related to intented foreign keys MUST implement an ILIAS\Setup\Agent
- The getStatusObjective method MUST return an instance of ILIAS\Setup\CollectedObjective
- The collectFrom method of the component's concrete implementation of ILIAS\Setup\CollectedObjectiveMUST provide/store metrics for detected violations regarding existing data for each individual intended FK.
- Because the state of the database table and thus the possible violations might be different everytime the metrics are collected/gathered, each violation metric MUST use a ILIAS\Setup\Metrics\Metric::STABILITY_VOLATILE stability , whereas the type MUST be \ILIAS\Setup\Metrics\Metric::TYPE_GAUGE.
- A single component could intend to apply multiple FKs, thus a metric of type ILIAS\Setup\Metrics\Metric::TYPE_COLLECTION MUST be used as a composite for the determined violations.
- The violates have to be detected with the mechanisms provided by Database: Management Functions for Referential Integrity in ilDBInterface.
- If no violations could be determined for an intended FK, a metric MUST NOT be stored
- Debatable: As an alternative, the explicit state "Foreign Key Applied" could be stored instead
- If no violations could be determined for the component, the composite metric (collection) MUST NOT be stored.
- Debatable: As an alternative, the explicit state "Foreign Key Applied" could be stored instead
- Debatable: Each single violation metric describing the number of violations for one intended FK MUST have an appropriate key in the metrics collection. The key MUST be a globally unique identification string describing this intended FK. This identification string SHOULD be used in a corresponding Markdown document (suggestion: ForeignKey.md) to provide:
- A SQL query to determine all problematic tuples for one FK.
- Optional/Further information provided by the maintainer which SHOULD help the ILIAS administator/operator to manually fix/remove these violations.
- The violation metrics MUST be created/ordered based on the depedency hierarchy, which means: FK violations for tables without any other dependend table should be listed first (if possible).
- The number of violations for an intended FK MUST be determined with an appropriate SQL query (should be build by the tools provided by Database: Management Functions for Referential Integrity in ilDBInterface), which is usually similar to:
- SELECT COUNT(*) FROM my_table LEFT JOIN referenced_table ON referenced_table.pk = my_table.fk WHERE referenced_table.pk IS NULL;
- or
- SELECT COUNT(*) FROM my_table LEFT JOIN referenced_table ON referenced_table.pk = my_table.fk WHERE referenced_table.pk IS NULL AND my_table.fk IS NOT NULL;
- Slight adaptions of these queries might be necessary if the intended FK should be composed of multiple fields.
- If any intended FK has been finally applied on the DBMS, the violation metrics for this FK MUST be removed from the status report. Furthermore the corresponding entry in the document (ForeignKey.md) MUST be removed. If no entry is left, the document MUST be deleted.
General Rules:
- In general, the RI status MUST NOT be determined for the status presented on the ILIAS user interface.
- There MUST be an option to mute (or inverted: to announce) the RI status determination when the 'status' command is executed in the PHP CLI environment. There fore the 'status' command of the ILIAS setup has to be enhanced to accept 'tags' being provided as options
- Possible CLI Option: --tags=db:ri
- Due to (possible) performance issues there MUST be a mechanism to cache metrics (and of course, to invalidatre this cache). This requires a similar approach like the decentralized database update steps introduced with ILIAS 8.
2.1 CollectedObjective Example
1 | $mailObjData = new Setup\Metrics\Metric( |
2.2 Output Example
1 | $ php setup/cli.php status |
2.3 ForeignKey.md Example (debatable if this is really required)
Suggested location: ./Services/Mail/ForeignKey.md
1 | # Mail: Detecting Violations for Referential Integrity |
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: Every maintainer who wants to create FKs for his/her tables/fields
- Implementation of the feature is done by: Every maintainer who wants to create FKs for his/her tables/fields
8 Funding
If you are interest in funding this feature, please add your name and institution to this list.
- Helmut-Schmidt-Universität / Universität der Bundeswehr Hamburg
9 Discussion
10 Implementation
{ The maintainer has to give a description of the final implementation and add screenshots if possible. }
Test Cases
Test cases completed at {date} by {user}
- {Test case number linked to Testrail} : {test case title}
Approval
Approved at {date} by {user}.
Last edited: 13. Oct 2023, 15:50, Kunkel, Matthias [mkunkel]