Mercurial > hg > orthanc-databases

comparison MySQL/Plugins/PrepareIndex.sql @ 0:7cea966b6829

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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;