|
0
|
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;
|
