Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/PrepareIndex.sql @ 434:23c7af6f671a pg-transactions
DeleteResource does not need the RemainingAncestor table anymore
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Wed, 13 Dec 2023 16:52:06 +0100 |
| parents | 8b7c1c423367 |
| children |
| rev | line source |
|---|---|
| 0 | 1 CREATE TABLE GlobalProperties( |
| 2 property INTEGER PRIMARY KEY, | |
| 3 value TEXT | |
| 4 ); | |
| 5 | |
| 6 CREATE TABLE Resources( | |
| 7 internalId BIGSERIAL NOT NULL PRIMARY KEY, | |
| 8 resourceType INTEGER NOT NULL, | |
| 9 publicId VARCHAR(64) NOT NULL, | |
| 10 parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE | |
|
418
a7f0f27fe33c
wip: advisory lock around CreateInstance: not ok see WO-139
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
11 -- UNIQUE (publicId) -- this is made unique in C++ code (new in plugin v X.Y.Z) |
| 0 | 12 ); |
| 13 | |
| 14 CREATE TABLE MainDicomTags( | |
| 15 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 16 tagGroup INTEGER, | |
| 17 tagElement INTEGER, | |
| 18 value TEXT, | |
| 19 PRIMARY KEY(id, tagGroup, tagElement) | |
| 20 ); | |
| 21 | |
| 22 CREATE TABLE DicomIdentifiers( | |
| 23 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 24 tagGroup INTEGER, | |
| 25 tagElement INTEGER, | |
| 26 value TEXT, | |
| 27 PRIMARY KEY(id, tagGroup, tagElement) | |
| 28 ); | |
| 29 | |
| 30 CREATE TABLE Metadata( | |
| 31 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 32 type INTEGER NOT NULL, | |
| 33 value TEXT, | |
| 34 PRIMARY KEY(id, type) | |
| 35 ); | |
| 36 | |
| 37 CREATE TABLE AttachedFiles( | |
| 38 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 39 fileType INTEGER, | |
| 40 uuid VARCHAR(64) NOT NULL, | |
| 41 compressedSize BIGINT, | |
| 42 uncompressedSize BIGINT, | |
| 43 compressionType INTEGER, | |
| 44 uncompressedHash VARCHAR(40), | |
| 45 compressedHash VARCHAR(40), | |
| 46 PRIMARY KEY(id, fileType) | |
| 47 ); | |
| 48 | |
| 49 CREATE TABLE Changes( | |
| 50 seq BIGSERIAL NOT NULL PRIMARY KEY, | |
| 51 changeType INTEGER, | |
| 52 internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 53 resourceType INTEGER, | |
| 54 date VARCHAR(64) | |
| 55 ); | |
| 56 | |
| 57 CREATE TABLE ExportedResources( | |
| 58 seq BIGSERIAL NOT NULL PRIMARY KEY, | |
| 59 resourceType INTEGER, | |
| 60 publicId VARCHAR(64), | |
| 61 remoteModality TEXT, | |
| 62 patientId VARCHAR(64), | |
| 63 studyInstanceUid TEXT, | |
| 64 seriesInstanceUid TEXT, | |
| 65 sopInstanceUid TEXT, | |
| 66 date VARCHAR(64) | |
| 67 ); | |
| 68 | |
| 69 CREATE TABLE PatientRecyclingOrder( | |
| 70 seq BIGSERIAL NOT NULL PRIMARY KEY, | |
| 71 patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 72 ); | |
| 73 | |
| 74 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
| 75 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 76 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
| 77 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
| 78 | |
| 79 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id); | |
| 80 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
| 81 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
| 82 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value); | |
| 83 | |
| 84 CREATE INDEX ChangesIndex ON Changes(internalId); | |
| 85 | |
| 86 | |
| 87 -- New tables wrt. Orthanc core | |
| 88 CREATE TABLE DeletedFiles( | |
| 89 uuid VARCHAR(64) NOT NULL, -- 0 | |
| 90 fileType INTEGER, -- 1 | |
| 91 compressedSize BIGINT, -- 2 | |
| 92 uncompressedSize BIGINT, -- 3 | |
| 93 compressionType INTEGER, -- 4 | |
| 94 uncompressedHash VARCHAR(40), -- 5 | |
| 95 compressedHash VARCHAR(40) -- 6 | |
| 96 ); | |
| 97 | |
| 98 CREATE TABLE RemainingAncestor( | |
| 99 resourceType INTEGER NOT NULL, | |
| 100 publicId VARCHAR(64) NOT NULL | |
| 101 ); | |
| 102 | |
| 103 CREATE TABLE DeletedResources( | |
| 104 resourceType INTEGER NOT NULL, | |
| 105 publicId VARCHAR(64) NOT NULL | |
| 106 ); | |
| 107 -- End of differences | |
| 108 | |
| 109 | |
| 110 CREATE FUNCTION AttachedFileDeletedFunc() | |
| 111 RETURNS TRIGGER AS $body$ | |
| 112 BEGIN | |
| 113 INSERT INTO DeletedFiles VALUES | |
| 114 (old.uuid, old.filetype, old.compressedSize, | |
| 115 old.uncompressedSize, old.compressionType, | |
| 116 old.uncompressedHash, old.compressedHash); | |
| 117 RETURN NULL; | |
| 118 END; | |
| 119 $body$ LANGUAGE plpgsql; | |
| 120 | |
| 121 CREATE TRIGGER AttachedFileDeleted | |
| 122 AFTER DELETE ON AttachedFiles | |
| 123 FOR EACH ROW | |
| 124 EXECUTE PROCEDURE AttachedFileDeletedFunc(); | |
| 125 | |
| 126 | |
|
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
127 -- previous version of the trigger, moved into ResourceDeletedFunc.sql |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
128 -- |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
129 -- -- The following trigger combines 2 triggers from SQLite: |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
130 -- -- ResourceDeleted + ResourceDeletedParentCleaning |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
131 -- CREATE FUNCTION ResourceDeletedFunc() |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
132 -- RETURNS TRIGGER AS $body$ |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
133 -- BEGIN |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
134 -- --RAISE NOTICE 'Delete resource %', old.parentId; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
135 -- INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); |
| 0 | 136 |
|
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
137 -- -- http://stackoverflow.com/a/11299968/881731 |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
138 -- IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
139 -- -- Signal that the deleted resource has a remaining parent |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
140 -- -- (a parent that must not be deleted but whose LastUpdate must be updated) |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
141 -- INSERT INTO RemainingAncestor |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
142 -- SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
143 -- ELSE |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
144 -- -- Delete a parent resource when its unique child is deleted |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
145 -- DELETE FROM Resources WHERE internalId = old.parentId; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
146 -- END IF; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
147 -- RETURN NULL; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
148 -- END; |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
149 -- $body$ LANGUAGE plpgsql; |
| 0 | 150 |
|
432
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
151 -- CREATE TRIGGER ResourceDeleted |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
152 -- AFTER DELETE ON Resources |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
153 -- FOR EACH ROW |
|
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
431
diff
changeset
|
154 -- EXECUTE PROCEDURE ResourceDeletedFunc(); |
| 0 | 155 |
| 156 | |
| 157 | |
| 158 CREATE FUNCTION PatientAddedFunc() | |
| 159 RETURNS TRIGGER AS $body$ | |
| 160 BEGIN | |
| 161 -- The "0" corresponds to "OrthancPluginResourceType_Patient" | |
| 162 IF new.resourceType = 0 THEN | |
| 163 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId); | |
| 164 END IF; | |
| 165 RETURN NULL; | |
| 166 END; | |
| 167 $body$ LANGUAGE plpgsql; | |
| 168 | |
| 169 CREATE TRIGGER PatientAdded | |
| 170 AFTER INSERT ON Resources | |
| 171 FOR EACH ROW | |
| 172 EXECUTE PROCEDURE PatientAddedFunc(); |