Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/PrepareIndex.sql @ 366:cd9521e04249 attach-custom-data
DatabaseBackendAdapterV4: added support for customData + revision when not already done
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Thu, 15 Sep 2022 18:12:34 +0200 |
| parents | 7cea966b6829 |
| 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 | |
| 11 ); | |
| 12 | |
| 13 CREATE TABLE MainDicomTags( | |
| 14 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 15 tagGroup INTEGER, | |
| 16 tagElement INTEGER, | |
| 17 value TEXT, | |
| 18 PRIMARY KEY(id, tagGroup, tagElement) | |
| 19 ); | |
| 20 | |
| 21 CREATE TABLE DicomIdentifiers( | |
| 22 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 23 tagGroup INTEGER, | |
| 24 tagElement INTEGER, | |
| 25 value TEXT, | |
| 26 PRIMARY KEY(id, tagGroup, tagElement) | |
| 27 ); | |
| 28 | |
| 29 CREATE TABLE Metadata( | |
| 30 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 31 type INTEGER NOT NULL, | |
| 32 value TEXT, | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
33 -- revision INTEGER, -- new in v4.0 (this column is added in PostgreSQLIndex::ConfigureDatabase) |
| 0 | 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), | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
46 -- revision BIGINT, -- new in v 4.0 (this column is added in PostgreSQLIndex::ConfigureDatabase) |
|
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
47 -- customData TEXT, -- new in v 4.X (this column is added in PostgreSQLIndex::ConfigureDatabase) |
| 0 | 48 PRIMARY KEY(id, fileType) |
| 49 ); | |
| 50 | |
| 51 CREATE TABLE Changes( | |
| 52 seq BIGSERIAL NOT NULL PRIMARY KEY, | |
| 53 changeType INTEGER, | |
| 54 internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 55 resourceType INTEGER, | |
| 56 date VARCHAR(64) | |
| 57 ); | |
| 58 | |
| 59 CREATE TABLE ExportedResources( | |
| 60 seq BIGSERIAL NOT NULL PRIMARY KEY, | |
| 61 resourceType INTEGER, | |
| 62 publicId VARCHAR(64), | |
| 63 remoteModality TEXT, | |
| 64 patientId VARCHAR(64), | |
| 65 studyInstanceUid TEXT, | |
| 66 seriesInstanceUid TEXT, | |
| 67 sopInstanceUid TEXT, | |
| 68 date VARCHAR(64) | |
| 69 ); | |
| 70 | |
| 71 CREATE TABLE PatientRecyclingOrder( | |
| 72 seq BIGSERIAL NOT NULL PRIMARY KEY, | |
| 73 patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 74 ); | |
| 75 | |
| 76 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
| 77 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 78 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
| 79 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
| 80 | |
| 81 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id); | |
| 82 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
| 83 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
| 84 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value); | |
| 85 | |
| 86 CREATE INDEX ChangesIndex ON Changes(internalId); | |
| 87 | |
| 88 | |
| 89 -- New tables wrt. Orthanc core | |
| 90 CREATE TABLE DeletedFiles( | |
| 91 uuid VARCHAR(64) NOT NULL, -- 0 | |
| 92 fileType INTEGER, -- 1 | |
| 93 compressedSize BIGINT, -- 2 | |
| 94 uncompressedSize BIGINT, -- 3 | |
| 95 compressionType INTEGER, -- 4 | |
| 96 uncompressedHash VARCHAR(40), -- 5 | |
| 97 compressedHash VARCHAR(40) -- 6 | |
| 98 ); | |
| 99 | |
| 100 CREATE TABLE RemainingAncestor( | |
| 101 resourceType INTEGER NOT NULL, | |
| 102 publicId VARCHAR(64) NOT NULL | |
| 103 ); | |
| 104 | |
| 105 CREATE TABLE DeletedResources( | |
| 106 resourceType INTEGER NOT NULL, | |
| 107 publicId VARCHAR(64) NOT NULL | |
| 108 ); | |
| 109 -- End of differences | |
| 110 | |
| 111 | |
| 112 CREATE FUNCTION AttachedFileDeletedFunc() | |
| 113 RETURNS TRIGGER AS $body$ | |
| 114 BEGIN | |
| 115 INSERT INTO DeletedFiles VALUES | |
| 116 (old.uuid, old.filetype, old.compressedSize, | |
| 117 old.uncompressedSize, old.compressionType, | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
118 old.uncompressedHash, old.compressedHash |
|
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
119 -- old.customData -- new in v 4.X (this column is added in PostgreSQLIndex::ConfigureDatabase) |
|
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
0
diff
changeset
|
120 ); |
| 0 | 121 RETURN NULL; |
| 122 END; | |
| 123 $body$ LANGUAGE plpgsql; | |
| 124 | |
| 125 CREATE TRIGGER AttachedFileDeleted | |
| 126 AFTER DELETE ON AttachedFiles | |
| 127 FOR EACH ROW | |
| 128 EXECUTE PROCEDURE AttachedFileDeletedFunc(); | |
| 129 | |
| 130 | |
| 131 -- The following trigger combines 2 triggers from SQLite: | |
| 132 -- ResourceDeleted + ResourceDeletedParentCleaning | |
| 133 CREATE FUNCTION ResourceDeletedFunc() | |
| 134 RETURNS TRIGGER AS $body$ | |
| 135 BEGIN | |
| 136 --RAISE NOTICE 'Delete resource %', old.parentId; | |
| 137 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); | |
| 138 | |
| 139 -- http://stackoverflow.com/a/11299968/881731 | |
| 140 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN | |
| 141 -- Signal that the deleted resource has a remaining parent | |
| 142 INSERT INTO RemainingAncestor | |
| 143 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; | |
| 144 ELSE | |
| 145 -- Delete a parent resource when its unique child is deleted | |
| 146 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 147 END IF; | |
| 148 RETURN NULL; | |
| 149 END; | |
| 150 $body$ LANGUAGE plpgsql; | |
| 151 | |
| 152 CREATE TRIGGER ResourceDeleted | |
| 153 AFTER DELETE ON Resources | |
| 154 FOR EACH ROW | |
| 155 EXECUTE PROCEDURE ResourceDeletedFunc(); | |
| 156 | |
| 157 | |
| 158 | |
| 159 CREATE FUNCTION PatientAddedFunc() | |
| 160 RETURNS TRIGGER AS $body$ | |
| 161 BEGIN | |
| 162 -- The "0" corresponds to "OrthancPluginResourceType_Patient" | |
| 163 IF new.resourceType = 0 THEN | |
| 164 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId); | |
| 165 END IF; | |
| 166 RETURN NULL; | |
| 167 END; | |
| 168 $body$ LANGUAGE plpgsql; | |
| 169 | |
| 170 CREATE TRIGGER PatientAdded | |
| 171 AFTER INSERT ON Resources | |
| 172 FOR EACH ROW | |
| 173 EXECUTE PROCEDURE PatientAddedFunc(); |