Mercurial > hg > orthanc-databases
annotate MySQL/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 | 9e419261f1c9 |
| 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 BIGINT NOT NULL AUTO_INCREMENT, | |
| 8 resourceType INTEGER NOT NULL, | |
| 9 publicId VARCHAR(64) NOT NULL, | |
| 10 parentId BIGINT, | |
| 11 PRIMARY KEY(internalId) | |
| 12 -- MySQL does not allow recursive foreign keys on the same table | |
| 13 -- CONSTRAINT Resources1 FOREIGN KEY (parentId) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 14 ); | |
| 15 | |
| 16 CREATE TABLE MainDicomTags( | |
| 17 id BIGINT NOT NULL, | |
| 18 tagGroup INTEGER NOT NULL, | |
| 19 tagElement INTEGER NOT NULL, | |
| 20 value VARCHAR(255), | |
| 21 PRIMARY KEY(id, tagGroup, tagElement), | |
| 22 CONSTRAINT MainDicomTags1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 23 ); | |
| 24 | |
| 25 CREATE TABLE DicomIdentifiers( | |
| 26 id BIGINT NOT NULL, | |
| 27 tagGroup INTEGER NOT NULL, | |
| 28 tagElement INTEGER NOT NULL, | |
| 29 value VARCHAR(255), | |
| 30 PRIMARY KEY(id, tagGroup, tagElement), | |
| 31 CONSTRAINT DicomIdentifiers1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 32 ); | |
| 33 | |
| 34 CREATE TABLE Metadata( | |
| 35 id BIGINT NOT NULL, | |
| 36 type INTEGER NOT NULL, | |
| 37 value TEXT, | |
| 38 PRIMARY KEY(id, type), | |
| 39 CONSTRAINT Metadata1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 40 ); | |
| 41 | |
| 42 CREATE TABLE AttachedFiles( | |
| 43 id BIGINT NOT NULL, | |
| 44 fileType INTEGER, | |
| 45 uuid VARCHAR(64) NOT NULL, | |
| 46 compressedSize BIGINT, | |
| 47 uncompressedSize BIGINT, | |
| 48 compressionType INTEGER, | |
| 49 uncompressedHash VARCHAR(40), | |
| 50 compressedHash VARCHAR(40), | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
51 -- revision INTEGER, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase |
|
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
52 -- customData LONGTEXT, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase |
| 0 | 53 PRIMARY KEY(id, fileType), |
| 54 CONSTRAINT AttachedFiles1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 55 ); | |
| 56 | |
| 57 CREATE TABLE Changes( | |
| 58 seq BIGINT NOT NULL AUTO_INCREMENT, | |
| 59 changeType INTEGER, | |
| 60 internalId BIGINT NOT NULL, | |
| 61 resourceType INTEGER, | |
| 62 date VARCHAR(64), | |
| 63 PRIMARY KEY(seq), | |
| 64 CONSTRAINT Changes1 FOREIGN KEY (internalId) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 65 ); | |
| 66 | |
| 67 CREATE TABLE ExportedResources( | |
| 68 seq BIGINT NOT NULL AUTO_INCREMENT, | |
| 69 resourceType INTEGER, | |
| 70 publicId VARCHAR(64), | |
| 71 remoteModality TEXT, | |
| 72 patientId VARCHAR(64), | |
| 73 studyInstanceUid TEXT, | |
| 74 seriesInstanceUid TEXT, | |
| 75 sopInstanceUid TEXT, | |
| 76 date VARCHAR(64), | |
| 77 PRIMARY KEY(seq) | |
| 78 ); | |
| 79 | |
| 80 CREATE TABLE PatientRecyclingOrder( | |
| 81 seq BIGINT NOT NULL AUTO_INCREMENT, | |
| 82 patientId BIGINT NOT NULL, | |
| 83 PRIMARY KEY(seq), | |
| 84 CONSTRAINT PatientRecyclingOrder1 FOREIGN KEY (patientId) REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 85 ); | |
| 86 | |
| 87 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
| 88 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 89 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
| 90 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
| 91 | |
| 92 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id); | |
| 93 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
| 94 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
| 95 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value); | |
| 96 | |
| 97 CREATE INDEX ChangesIndex ON Changes(internalId); | |
| 98 | |
| 99 | |
| 100 -- New tables wrt. Orthanc core | |
| 101 CREATE TABLE DeletedFiles( | |
| 102 uuid VARCHAR(64) NOT NULL, -- 0 | |
| 103 fileType INTEGER, -- 1 | |
| 104 compressedSize BIGINT, -- 2 | |
| 105 uncompressedSize BIGINT, -- 3 | |
| 106 compressionType INTEGER, -- 4 | |
| 107 uncompressedHash VARCHAR(40), -- 5 | |
| 108 compressedHash VARCHAR(40) -- 6 | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
109 -- revision INTEGER, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase |
|
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
110 -- customData LONGTEXT, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase |
| 0 | 111 ); |
| 112 -- End of differences | |
| 113 | |
| 114 | |
| 115 | |
| 116 -- NB: Character "@" is used to replace the semicolon characters in triggers | |
| 117 | |
| 118 -- In MySQL, this trigger is only used if replacing some attachment | |
| 119 CREATE TRIGGER AttachedFileDeleted | |
| 120 AFTER DELETE ON AttachedFiles | |
| 121 FOR EACH ROW | |
| 122 BEGIN | |
| 123 INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, | |
| 124 old.uncompressedSize, old.compressionType, | |
| 125 old.uncompressedHash, old.compressedHash)@ | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
126 -- old.revision, old.customData -- new in v 4.X, added in MySQLIndex::ConfigureDatabase |
|
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
127 |
| 0 | 128 END; |
| 129 | |
| 130 | |
| 131 CREATE TRIGGER ResourceDeleted | |
| 132 BEFORE DELETE ON Resources -- WARNING: Must be "BEFORE", otherwise the attached file is already deleted | |
| 133 FOR EACH ROW | |
| 134 BEGIN | |
| 135 INSERT INTO DeletedFiles SELECT uuid, fileType, compressedSize, uncompressedSize, compressionType, uncompressedHash, compressedHash FROM AttachedFiles WHERE id=old.internalId@ | |
|
366
cd9521e04249
DatabaseBackendAdapterV4: added support for customData + revision when not already done
Alain Mazy <am@osimis.io>
parents:
16
diff
changeset
|
136 -- revision, customData -- new in v 4.X, added in MySQLIndex::ConfigureDatabase |
| 0 | 137 END; |
| 138 | |
| 139 | |
| 140 CREATE TRIGGER PatientAdded | |
| 141 AFTER INSERT ON Resources | |
| 142 FOR EACH ROW | |
| 143 BEGIN | |
| 144 IF new.resourceType = 0 THEN -- The "0" corresponds to "OrthancPluginResourceType_Patient" | |
| 145 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId)@ | |
| 146 END IF@ | |
| 147 END; |