annotate MySQL/Plugins/PrepareIndex.sql @ 221:73cc85f3d9c1

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