Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/PrepareIndex.sql @ 432:8b7c1c423367 pg-transactions
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
author | Alain Mazy <am@osimis.io> |
---|---|
date | Mon, 11 Dec 2023 14:39:27 +0100 |
parents | 7c1fe5d6c12c |
children |
comparison
equal
deleted
inserted
replaced
431:7c1fe5d6c12c | 432:8b7c1c423367 |
---|---|
122 AFTER DELETE ON AttachedFiles | 122 AFTER DELETE ON AttachedFiles |
123 FOR EACH ROW | 123 FOR EACH ROW |
124 EXECUTE PROCEDURE AttachedFileDeletedFunc(); | 124 EXECUTE PROCEDURE AttachedFileDeletedFunc(); |
125 | 125 |
126 | 126 |
127 -- The following trigger combines 2 triggers from SQLite: | 127 -- previous version of the trigger, moved into ResourceDeletedFunc.sql |
128 -- ResourceDeleted + ResourceDeletedParentCleaning | 128 -- |
129 CREATE FUNCTION ResourceDeletedFunc() | 129 -- -- The following trigger combines 2 triggers from SQLite: |
130 RETURNS TRIGGER AS $body$ | 130 -- -- ResourceDeleted + ResourceDeletedParentCleaning |
131 BEGIN | 131 -- CREATE FUNCTION ResourceDeletedFunc() |
132 --RAISE NOTICE 'Delete resource %', old.parentId; | 132 -- RETURNS TRIGGER AS $body$ |
133 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); | 133 -- BEGIN |
134 -- --RAISE NOTICE 'Delete resource %', old.parentId; | |
135 -- INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); | |
134 | 136 |
135 -- http://stackoverflow.com/a/11299968/881731 | 137 -- -- http://stackoverflow.com/a/11299968/881731 |
136 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN | 138 -- IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN |
137 -- Signal that the deleted resource has a remaining parent | 139 -- -- Signal that the deleted resource has a remaining parent |
138 -- (a parent that must not be deleted but whose LastUpdate must be updated) | 140 -- -- (a parent that must not be deleted but whose LastUpdate must be updated) |
139 INSERT INTO RemainingAncestor | 141 -- INSERT INTO RemainingAncestor |
140 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; | 142 -- SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; |
141 ELSE | 143 -- ELSE |
142 -- Delete a parent resource when its unique child is deleted | 144 -- -- Delete a parent resource when its unique child is deleted |
143 DELETE FROM Resources WHERE internalId = old.parentId; | 145 -- DELETE FROM Resources WHERE internalId = old.parentId; |
144 END IF; | 146 -- END IF; |
145 RETURN NULL; | 147 -- RETURN NULL; |
146 END; | 148 -- END; |
147 $body$ LANGUAGE plpgsql; | 149 -- $body$ LANGUAGE plpgsql; |
148 | 150 |
149 CREATE TRIGGER ResourceDeleted | 151 -- CREATE TRIGGER ResourceDeleted |
150 AFTER DELETE ON Resources | 152 -- AFTER DELETE ON Resources |
151 FOR EACH ROW | 153 -- FOR EACH ROW |
152 EXECUTE PROCEDURE ResourceDeletedFunc(); | 154 -- EXECUTE PROCEDURE ResourceDeletedFunc(); |
153 | 155 |
154 | 156 |
155 | 157 |
156 CREATE FUNCTION PatientAddedFunc() | 158 CREATE FUNCTION PatientAddedFunc() |
157 RETURNS TRIGGER AS $body$ | 159 RETURNS TRIGGER AS $body$ |