Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PrepareIndex.sql @ 432:8b7c1c423367 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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$