Feature Wiki

Information about planned and released features

Tabs

Database: Announcing Entity Deletions with Established RI

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.

The linked feature wiki pages address the establishment of a referential integrity and mainly discuss the analysis and migration phases. 

Entities in ILIAS are not only defined by a database record but may also refer to resources located in the server's filesystem or to records on other servers (API dependencies, e.g. a Adobe Connect Meeting).
Therefore depending entities (B) with an applied foreign key ('Belongs To' relation) have to be aware of a deletion of an entity (A) they refer to. Furthermore such an entity (B) referenced by other entities ('Belongs To' or 'Has Many' relations, e.g. C and D) itself in turn MUST announce the deletion of it's database tuples as well.

In addition two possible mechanisms have to be considered how database deletions are performed in ILIAS:

  1. Without Cascading Deletions (current Situation in ILIAS, and still to be considered in future):
    • Dependent child entities MUST be deleted before a parent entity gets deleted (achieved by hard wiring or raising events).
    • Dependent child entities MUST be notified about deletions of a parent entity to gather information of other resources to be deleted as well.
  2. With Cascading Deletions:
    • The database records of dependent child entities are automatically deleted if they have defined a 'FOREIGN KEY ... ON DELETE CASCADE'
    • But: Dependent child entities MUST still be notified about deletions of a parent entity to gather information of other resources to be deleted as well.

2 Conceptual Summary

As we see in the description of the initial problem, depending child entities need to be notified about the deletion of the entity they refer to, no matter if 'Cascading Deletions' are used or not. Therefore we suggest to use the ILIAS event system to announce deletions.

The following example shows how ILIAS events could be used to notify other components abouth a deletion. 'pre'- or 'post-deletion events are already used in some ILIAS components.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
class EntityToBeDeleted
{
    private ilAppEventHandler $eventHandler;
    private ilDBInterface $db;
 
    public function delete() : void
    {
        $params = [
            'primaryKeys' =>[
                $this->getId()
            ]
        ];
 
        try {
            $this->eventHandler->raise(
                'Services/EntityToBeDeleted',
                'announceDeletion',
                $params
            );
 
            // May result in deletion cascades
            $this->db->prepareManip(
                'DELETE FROM my_entity WHERE id = %s',
                ['integer'],
                [$this->getId()]
            );
 
            try {
                $this->eventHandler->raise(
                    'Services/EntityToBeDeleted',
                    'confirmDeletion',
                    $params
                );
            } catch (ilDatabaseReferentialIntegrityException $e) {
                $this->eventHandler->raise(
                    'Services/EntityToBeDeleted',
                    'rejectConfirmDeletion',
                    $params
                );
                // Exceptions should be delegated to the upper context which could handle it
            }
        } catch (ilDatabaseReferentialIntegrityException $e) {
            $this->eventHandler->raise(
                'Services/EntityToBeDeleted',
                'rejectAnnounceDeletion',
                $params
            );
            // Exceptions should be delegated to the upper context which has to handle it
        }
    }
}

All relevant primary key values MUST be provided within the event parameters. There MAY be a need for different events (or a slightly modification of the code above) to handle the 'Delete Multiple' case.

There are major issues with the approach above:

  • General
    • There is no way (described) to handle transitive dependencies.
  • With 'Cascading Deletions'
    • If there is a 'Has Many' relation for the 'EntityToBeDeleted' and the first depending entity listening on 'announceDeletion' deletes it's non-database resources, this ressources are lost forever if one of the entities #2nd - #nth raises an exception or the cascading database deletion fails at all.
Therefore dependent entities MUST provide a mechanism to retrieve information about non-database ressources and their deletion, which can be executed after the cascading database deletion has been successfully executed.  A new service MUST be used to gather this information. In addition the events should be moved into the service to reduce the boilerplate code for a deletion.

2.1 Service Interfaces and Classes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
final class PrimaryKeyField
{
    private string $field;
    private int $field_type;
    private mixed $value;
 
    public function __construct(string $field, int $field_type, mixed $value)
    {
        $this->field = $field;
        $this->field_type = $field_type;
        $this->value = $value;
    }
 
    public function getField() : string
    {
        return $this->field;
    }
 
    public function getFieldType() : int
    {
        return $this->field_type;
    }
 
    public function value() : mixed
    {
        return $this->value;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
final class PrimaryKey
{
    private string $table;
    /** @var array<string, PrimaryKeyField> */
    private array $fields = [];
 
    public function __construct(string $table, PrimaryKeyField $field)
    {
        $this->table = $table;
        $this->fields[$field->getField()] = $field;
    }
 
    /**
     * @param PrimaryKeyField $field
     * @return PrimaryKey
     * \InvalidArgumentException
     */
    public function withFurtherField(PrimaryKeyField $field) : self
    {
        $clone = clone $this;
        if (isset($clone ->fields[$field->getField()])) {
            throw new \InvalidArgumentException(sprintf(
                'Field %s already exists in primary key definition',
                $field->getField()
            ));
        }
 
        $clone->fields[$field->getField()] = $field;
 
        return $this;
    }
 
    /**
     * @return PrimaryKeyField[]
     */
    public function fields() : array
    {
        return array_values($this->fields);
    }
 
    private function table() : string
    {
        return $this->table;
    }
 
    public function equals(PrimaryKey $field) : bool
    {
        return $this->table === $field->table() && array_diff_key($this->fields, $field->fields) === [] && array_diff_key($field->fields, $this->fields) === [];
    }
}
1
2
3
4
5
6
7
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
class ReferentialIntegrityException extends \ilDatabaseException
{
}
1
2
3
4
5
6
7
8
9
10
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
interface Transaction
{
    public function commit() : void;
 
    public function rollback() : void;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
final class RobustTransactionDecorator implements Transaction
{
    private Transaction $decorated;
    private \ilLogger $logger;
 
    public function __construct(Transaction $decorated, \ilLogger $logger)
    {
        $this->decorated = $decorated;
        $this->logger = $logger;
    }
 
    public function commit() : void
    {
        try {
            $this->decorated->commit();
        } catch (\Throwable $e) {
            $this->logger->error($e->getMessage());
            $this->logger->error($e->getTraceAsString());
        }
    }
 
    public function rollback() : void
    {
        try {
            $this->decorated->rollback();
        } catch (\Throwable $e) {
            $this->logger->error($e->getMessage());
            $this->logger->error($e->getTraceAsString());
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
final class ClosureTransaction implements Transaction
{
    private \Closure $commit;
    private \Closure $rollback;
 
    public function __construct(\Closure $commit, ?\Closure $rollback = null)
    {
        $this->commit = $commit;
 
        if (null === $rollback) {
            $rollback = static function () : void {
            };
        }
        $this->rollback = $rollback;
    }
 
    public function commit() : void
    {
        ($this->commit)();
    }
 
    public function rollback() : void
    {
        ($this->rollback)();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
final class Step
{
    private string $component;
    /** @var PrimaryKey[] */
    private $primaryKeys;
    private Transaction $transaction;
 
    public function __construct(string $component, array $primaryKeys, Transaction $transaction)
    {
        $this->component = $component;
        $this->primaryKeys = $primaryKeys;
        $this->transaction = $transaction;
    }
 
    public function component() : string
    {
        return $this->component;
    }
 
    /**
     * @return PrimaryKey[]
     */
    public function primaryKeys() : array
    {
        return $this->primaryKeys;
    }
 
    public function transaction() : Transaction
    {
        return $this->transaction;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
<?php declare(strict_types=1);
 
namespace ILIAS\Database\RI;
 
class Service
{
    private \ilAppEventHandler $eventHandler;
    private \ilLogger $logger;
 
    private ?Step $base_step = null;
    /** @var \SplQueue|Step[] */
    private \SplQueue $queue;
 
    public function __construct(\ilAppEventHandler $eventHandler, \ilLogger $logger)
    {
        $this->eventHandler = $eventHandler;
        $this->logger = $logger;
        $this->queue = new \SplQueue();
        $this->queue->setIteratorMode(\SplQueue::IT_MODE_FIFO | \SplQueue::IT_MODE_KEEP);
    }
 
    /**
     * @param PrimaryKey[] $primaryKeys
     * @return PrimaryKey[]
     */
    private function filterDuplicatePrimaryKeys(array $primaryKeys) : array
    {
        return array_filter($primaryKeys, function (PrimaryKey $primaryKey) : bool {
            foreach ($this->queue as $element) {
                foreach ($element->primaryKeys() as $existing_primary_key) {
                    if ($primaryKey->equals($existing_primary_key)) {
                        return false;
                    }
                }
            }
 
            return true;
        });
    }
 
    private function emptyQueue() : void
    {
        $this->base_step = null;
        $this->queue = new \SplQueue();
    }
 
    /**
     * @param string $component
     * @param PrimaryKey[] $primaryKeys
     * @param Transaction $transaction
     * @throws ReferentialIntegrityException
     */
    public function startCascadingDelete(
        string $component,
        array $primaryKeys,
        Transaction $transaction
    ) : void {
        if ($this->base_step !== null) {
            throw new ReferentialIntegrityException(
                'A RI deletion is already in progress and is not committed or rolled back!'
            );
        }
 
        $this->base_step = new Step(
            $component,
            $primaryKeys,
            $transaction
        );
 
        $this->eventHandler->raise(
            $component,
            'announceDeletion',
            [
                'primaryKeys' => $primaryKeys
            ]
        );
    }
 
    /**
     * @param string $component
     * @param PrimaryKey[] $primaryKeys
     * @param Transaction $transaction
     * @throws ReferentialIntegrityException
     */
    public function appendToCascadingDelete(string $component, array $primaryKeys, Transaction $transaction) : void
    {
        if ($this->base_step === null) {
            throw new ReferentialIntegrityException(
                'A RI deletion has not been started, yet!'
            );
        }
 
        $primaryKeys = $this->filterDuplicatePrimaryKeys($primaryKeys);
        if ($primaryKeys === []) {
            // Prevent circles
            return;
        }
 
        $this->eventHandler->raise(
            $component,
            'announceDeletion',
            [
                'base_component' => $this->base_step->component(),
                'primaryKey' => $primaryKeys
            ]
        );
 
        $this->queue->enqueue(new Step(
            $component,
            $primaryKeys,
            new RobustTransactionDecorator($transaction, $this->logger)
        ));
    }
 
    /**
     * @throws ReferentialIntegrityException
     */
    public function commit() : void
    {
        if ($this->base_step === null) {
            throw new ReferentialIntegrityException(
                'A RI deletion has not been started, yet!'
            );
        }
 
        try {
            // If there are circles, a database exception may occur, possible cascading delete
            $this->base_step->transaction()->commit();
        } catch (\ilDatabaseException $e) {
            throw new ReferentialIntegrityException(
                'Could not execute RI deletion: ',
                $e->getMessage()
            );
        }
 
        foreach ($this->queue as $element) {
            $element->commit();
        }
 
        $this->emptyQueue();
    }
 
    /**
     * @throws ReferentialIntegrityException
     */
    public function rollback() : void
    {
        if ($this->base_step === null) {
            throw new ReferentialIntegrityException(
                'A RI deletion has not been started, yet!'
            );
        }
 
        foreach ($this->queue as $element) {
            $element->rollback();
        }
 
        $this->emptyQueue();
    }
}

2.2 Consumer Perspective

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<?php declare(strict_types=1);
 
use \ILIAS\Database\RI;
 
 
class EntityToBeDeleted
{
    private Service $service;
    private \ilDBInterface $db;
 
    public function __construct(Service $service, \ilDBInterface $db)
    {
        $this->service = $service;
        $this->db = $db;
    }
 
    public function delete() : void
    {
        $primaryKeyDefinitions = [
            new PrimaryKey(
                'usr_data',
                new PrimaryKeyField('usr_id', \ilDBConstants::T_INTEGER, 4711)
            ),
            new PrimaryKey(
                'usr_data',
                new PrimaryKeyField('usr_id', \ilDBConstants::T_INTEGER, 4712)
            )
        ];
 
        try {
            $this->service->startCascadingDelete(
                'Services/EntityToBeDeleted',
                $primaryKeyDefinitions,
                new ClosureTransaction(function () use ($primaryKeyDefinitions) {
                    $this->db->manipulateF(
                        'DELETE FROM my_entity WHERE id IN (0' . str_repeat(
                            ' , %s',
                            count($primaryKeyDefinitions)
                        ) . ')',
                        array_map(static function (PrimaryKey $primaryKey) : int {
                            return $primaryKey->fields()[0]->getFieldType();
                        }, $primaryKeyDefinitions),
                        array_map(static function (PrimaryKey $primaryKey) : int {
                            return $primaryKey->fields()[0]->value();
                        }, $primaryKeyDefinitions)
                    );
                })
            );
 
            $this->service->commit();
        } catch (ReferentialIntegrityException $e) {
            $this->service->rollback();
            // Exceptions should be delegated to the upper context which has to handle it
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php declare(strict_types=1);
 
use \ILIAS\Database\RI;
use \ILIAS\ResourceStorage\Services;
 
class ListeningEntity
{
    private Service $riService;
    private Services $storageService;
 
    public function __construct(Service $riService, Services $storageService)
    {
        $this->riService = $riService;
        $this->storageService = $storageService;
    }
 
    public function handleEvent(string $component, string $event, array $params) : void
    {
        if ('announceDeletion' === $event) {
            $primaryKeyDefinition = [/* Lookup own primary keys by $params['primaryKey'] array */];
 
            $this->riService->appendToCascadingDelete(
                'Module/ListeningEntity',
                $primaryKeyDefinition,
                new ClosureTransaction(function () use ($primaryKeyDefinition) {
                    $identifications = [/* Determined by $primaryKeyDefinition */];
 
                    foreach ($identifications as $identification) {
                        $this->storageService->manage()->remove(
                            $identification,
                            new \ilListeningEntityIRSSStakeholder()
                        );
                    }
 
                    // If a foreign key has NOT been established yet, this callback could be also used to delete the database records determined by $primaryKeyDefinition
                    // The service can provide a function which returns a boolean true if the migration has been completed, otherwise false
                })
            );
        }
    }
}

  • The events MUST be added by the developer who introduced the foreign key in his/her component referencing a primary key of another component maintained by another developer. This means: The maintainer of component B with a foreign key referencing component A muss register the corresponding 'listen' and 'raise' parts in the 'service'.xml and 'module.xml' files.
  • The events MUST be suggested via pull requests on https://github.com/ILIAS-eLearning/ILIAS.
  • The events SHOULD be only added during the feature implementation phase. There may be exceptions (e.g. for hotfixes).
  • The usage of the service in the 'root' of the deletion cascade MUST be added (if it does not exist, yet) by the maintainer of the depending component via pull request.

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

None

5 Privacy

None

6 Security

None

7 Contact

  • Author of the Request: {Please add your name.}
  • Maintainer: {Please add your name before applying for an initial workshop or a Jour Fixe meeting.}
  • Implementation of the feature is done by: {The maintainer must add the name of the implementing developer.}

8 Funding

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

  • …

9 Discussion

Jansen, Michael [mjansen] To be discussed:

  • Do we need a mechanism to prevent the deletion when consumers are not able to reflect the intention
    • How should this be done and presented to the end user/actor on the user interface?  
      • Throwing an exception (suggest above) and providing a general error message (Something went wrong, please contact the administrator)
      • Providing a meaningful error message (e.g. a language variable, or a custom string, ...)
      • ...
  • Do we need an extended mechanism to process the queue of consumers after the root deletion has been delegated to the SQL server?
    • This could be done via back ground tasks instead of using 'In Memory Callbacks'.
      • If this should be considered, we need something like a 'Number of Retries' AND a 'Escalating Delay Period' setting.
    • Maybe this could be a decision of the consumer, and must not be part of the service itself.

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]