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