Database: Detecting Violations for Referential Integrity

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.

1. A component which wants to provide diagnostics of possible violations related to intented foreign keys MUST implement an ILIAS\Setup\Agent
2. The getStatusObjective method MUST return an instance of ILIAS\Setup\CollectedObjective
3. 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.
   1. 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.
   2. 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.
   3. The violates have to be detected with the mechanisms provided by Database: Management Functions for Referential Integrity in ilDBInterface.
   4. 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
   5. 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
   6. 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.
   7. 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).
4. 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.
5. 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

$mailObjData = new Setup\Metrics\Metric(
    Setup\Metrics\Metric::STABILITY_VOLATILE,
    Setup\Metrics\Metric::TYPE_COUNTER,
    5,
    'Number of violations for the intended FK on table "mail" and field "folder_id"'
);
 
$riViolations = new Setup\Metrics\Metric(
    Setup\Metrics\Metric::STABILITY_VOLATILE,
    Setup\Metrics\Metric::TYPE_COLLECTION,
    [
        "mail->folder_id" => $mailObjData,
    ],
    'Holds information about the number of violations for intended foreign keys'
);
$storage->store("Database FK Violations", $riViolations); // Here we need additional the option to add specific tags

2.2 Output Example

1
2
3
4
5
6
7

$ php setup/cli.php status
 
[...]
mail:
    Database FK Violations:
        mail->folder_id: 5
[...]

2.3 ForeignKey.md Example (debatable if this is really required)

Suggested location:  ./Services/Mail/ForeignKey.md

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# Mail: Detecting Violations for Referential Integrity
 
## mail->folder_id
 
Violations for the intended foreign key could be determined by:
 
```sql
SELECT *
FROM mail
LEFT JOIN mail_obj_data
    ON mail_obj_data.obj_id = mail.folder_id
WHERE mail_obj_data.obj_id IS NULL:
```
The maintainer recommends to delete all affected rows because the corresponding user account seems to be already deleted as well.

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