Mercurial > hg > orthanc-databases
annotate MySQL/Plugins/PrepareIndex.sql @ 577:991b9b285e1a attach-custom-data tip
fix custom-data handling
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Wed, 02 Oct 2024 09:40:20 +0200 |
parents | cd9521e04249 |
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; |