|
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),
|
|
|
51 PRIMARY KEY(id, fileType),
|
|
|
52 CONSTRAINT AttachedFiles1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE
|
|
|
53 );
|
|
|
54
|
|
|
55 CREATE TABLE Changes(
|
|
|
56 seq BIGINT NOT NULL AUTO_INCREMENT,
|
|
|
57 changeType INTEGER,
|
|
|
58 internalId BIGINT NOT NULL,
|
|
|
59 resourceType INTEGER,
|
|
|
60 date VARCHAR(64),
|
|
|
61 PRIMARY KEY(seq),
|
|
|
62 CONSTRAINT Changes1 FOREIGN KEY (internalId) REFERENCES Resources(internalId) ON DELETE CASCADE
|
|
|
63 );
|
|
|
64
|
|
|
65 CREATE TABLE ExportedResources(
|
|
|
66 seq BIGINT NOT NULL AUTO_INCREMENT,
|
|
|
67 resourceType INTEGER,
|
|
|
68 publicId VARCHAR(64),
|
|
|
69 remoteModality TEXT,
|
|
|
70 patientId VARCHAR(64),
|
|
|
71 studyInstanceUid TEXT,
|
|
|
72 seriesInstanceUid TEXT,
|
|
|
73 sopInstanceUid TEXT,
|
|
|
74 date VARCHAR(64),
|
|
|
75 PRIMARY KEY(seq)
|
|
|
76 );
|
|
|
77
|
|
|
78 CREATE TABLE PatientRecyclingOrder(
|
|
|
79 seq BIGINT NOT NULL AUTO_INCREMENT,
|
|
|
80 patientId BIGINT NOT NULL,
|
|
|
81 PRIMARY KEY(seq),
|
|
|
82 CONSTRAINT PatientRecyclingOrder1 FOREIGN KEY (patientId) REFERENCES Resources(internalId) ON DELETE CASCADE
|
|
|
83 );
|
|
|
84
|
|
|
85 CREATE INDEX ChildrenIndex ON Resources(parentId);
|
|
|
86 CREATE INDEX PublicIndex ON Resources(publicId);
|
|
|
87 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
|
|
|
88 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
|
|
|
89
|
|
|
90 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id);
|
|
|
91 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
|
|
|
92 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
|
|
|
93 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value);
|
|
|
94
|
|
|
95 CREATE INDEX ChangesIndex ON Changes(internalId);
|
|
|
96
|
|
|
97
|
|
|
98 -- New tables wrt. Orthanc core
|
|
|
99 CREATE TABLE DeletedFiles(
|
|
|
100 uuid VARCHAR(64) NOT NULL, -- 0
|
|
|
101 fileType INTEGER, -- 1
|
|
|
102 compressedSize BIGINT, -- 2
|
|
|
103 uncompressedSize BIGINT, -- 3
|
|
|
104 compressionType INTEGER, -- 4
|
|
|
105 uncompressedHash VARCHAR(40), -- 5
|
|
|
106 compressedHash VARCHAR(40) -- 6
|
|
|
107 );
|
|
|
108 -- End of differences
|
|
|
109
|
|
|
110
|
|
|
111
|
|
|
112 -- NB: Character "@" is used to replace the semicolon characters in triggers
|
|
|
113
|
|
|
114 -- In MySQL, this trigger is only used if replacing some attachment
|
|
|
115 CREATE TRIGGER AttachedFileDeleted
|
|
|
116 AFTER DELETE ON AttachedFiles
|
|
|
117 FOR EACH ROW
|
|
|
118 BEGIN
|
|
|
119 INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize,
|
|
|
120 old.uncompressedSize, old.compressionType,
|
|
|
121 old.uncompressedHash, old.compressedHash)@
|
|
|
122 END;
|
|
|
123
|
|
|
124
|
|
|
125 CREATE TRIGGER ResourceDeleted
|
|
|
126 BEFORE DELETE ON Resources -- WARNING: Must be "BEFORE", otherwise the attached file is already deleted
|
|
|
127 FOR EACH ROW
|
|
|
128 BEGIN
|
|
|
129 INSERT INTO DeletedFiles SELECT uuid, fileType, compressedSize, uncompressedSize, compressionType, uncompressedHash, compressedHash FROM AttachedFiles WHERE id=old.internalId@
|
|
|
130 END;
|
|
|
131
|
|
|
132
|
|
|
133 CREATE TRIGGER PatientAdded
|
|
|
134 AFTER INSERT ON Resources
|
|
|
135 FOR EACH ROW
|
|
|
136 BEGIN
|
|
|
137 IF new.resourceType = 0 THEN -- The "0" corresponds to "OrthancPluginResourceType_Patient"
|
|
|
138 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId)@
|
|
|
139 END IF@
|
|
|
140 END;
|
