Mercurial > hg > orthanc
annotate OrthancServer/Sources/Database/PrepareDatabase.sql @ 5668:bd1352bd9d82 find-refactoring
configuring requests for ModalitiesInStudy and SOPClassesInStudy
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Thu, 04 Jul 2024 21:57:39 +0200 |
| parents | f7adfb22e20e |
| children | 8279eaab0d1d |
| rev | line source |
|---|---|
|
4124
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
1 -- Orthanc - A Lightweight, RESTful DICOM Store |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
2 -- Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
3 -- Department, University Hospital of Liege, Belgium |
|
5640
f7adfb22e20e
updated copyright, as Orthanc Team now replaces Osimis
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5485
diff
changeset
|
4 -- Copyright (C) 2017-2023 Osimis S.A., Belgium |
|
f7adfb22e20e
updated copyright, as Orthanc Team now replaces Osimis
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5485
diff
changeset
|
5 -- Copyright (C) 2024-2024 Orthanc Team SRL, Belgium |
|
5485
48b8dae6dc77
upgrade to year 2024
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5256
diff
changeset
|
6 -- Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium |
|
4124
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
7 -- |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
8 -- This program is free software: you can redistribute it and/or |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
9 -- modify it under the terms of the GNU General Public License as |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
10 -- published by the Free Software Foundation, either version 3 of the |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
11 -- License, or (at your option) any later version. |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
12 -- |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
13 -- This program is distributed in the hope that it will be useful, but |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
14 -- WITHOUT ANY WARRANTY; without even the implied warranty of |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
15 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
16 -- General Public License for more details. |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
17 -- |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
18 -- You should have received a copy of the GNU General Public License |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
19 -- along with this program. If not, see <http://www.gnu.org/licenses/>. |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
20 |
|
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
21 |
| 181 | 22 CREATE TABLE GlobalProperties( |
|
206
4453a010d0db
flush to disk thread
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
203
diff
changeset
|
23 property INTEGER PRIMARY KEY, |
| 181 | 24 value TEXT |
| 25 ); | |
| 26 | |
| 27 CREATE TABLE Resources( | |
| 28 internalId INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 29 resourceType INTEGER, | |
| 30 publicId TEXT, | |
| 31 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 32 ); | |
| 33 | |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
34 CREATE TABLE MainDicomTags( |
| 1158 | 35 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
| 36 tagGroup INTEGER, | |
| 37 tagElement INTEGER, | |
| 38 value TEXT, | |
| 39 PRIMARY KEY(id, tagGroup, tagElement) | |
| 40 ); | |
| 41 | |
| 1176 | 42 -- The following table was added in Orthanc 0.8.5 (database v5) |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
43 CREATE TABLE DicomIdentifiers( |
| 181 | 44 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
| 45 tagGroup INTEGER, | |
| 46 tagElement INTEGER, | |
| 47 value TEXT, | |
| 48 PRIMARY KEY(id, tagGroup, tagElement) | |
| 49 ); | |
| 50 | |
| 51 CREATE TABLE Metadata( | |
| 52 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 53 type INTEGER, | |
| 54 value TEXT, | |
| 55 PRIMARY KEY(id, type) | |
| 56 ); | |
| 57 | |
| 58 CREATE TABLE AttachedFiles( | |
| 59 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
|
197
530a25320461
removal of text as ids in sqlite db
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
192
diff
changeset
|
60 fileType INTEGER, |
| 181 | 61 uuid TEXT, |
| 183 | 62 compressedSize INTEGER, |
| 181 | 63 uncompressedSize INTEGER, |
| 64 compressionType INTEGER, | |
|
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
65 uncompressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4) |
|
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
66 compressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4) |
|
197
530a25320461
removal of text as ids in sqlite db
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
192
diff
changeset
|
67 PRIMARY KEY(id, fileType) |
| 181 | 68 ); |
| 69 | |
| 183 | 70 CREATE TABLE Changes( |
| 71 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 72 changeType INTEGER, | |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
73 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
| 183 | 74 resourceType INTEGER, |
| 75 date TEXT | |
| 76 ); | |
| 77 | |
| 231 | 78 CREATE TABLE ExportedResources( |
| 183 | 79 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
| 231 | 80 resourceType INTEGER, |
| 81 publicId TEXT, | |
| 183 | 82 remoteModality TEXT, |
| 83 patientId TEXT, | |
| 84 studyInstanceUid TEXT, | |
| 85 seriesInstanceUid TEXT, | |
| 86 sopInstanceUid TEXT, | |
| 87 date TEXT | |
| 88 ); | |
| 89 | |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
90 CREATE TABLE PatientRecyclingOrder( |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
91 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
92 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
93 ); |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
94 |
|
5221
d0f7c742d397
started implementation of labels
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5185
diff
changeset
|
95 -- New in Orthanc 1.12.0 |
|
d0f7c742d397
started implementation of labels
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5185
diff
changeset
|
96 CREATE TABLE Labels( |
| 5256 | 97 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
|
5227
988dab8deb1c
"/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5221
diff
changeset
|
98 label TEXT NOT NULL, |
| 5256 | 99 PRIMARY KEY(id, label) -- Prevents duplicates |
|
5221
d0f7c742d397
started implementation of labels
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5185
diff
changeset
|
100 ); |
|
d0f7c742d397
started implementation of labels
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5185
diff
changeset
|
101 |
| 181 | 102 CREATE INDEX ChildrenIndex ON Resources(parentId); |
| 103 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 190 | 104 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
105 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); |
| 181 | 106 |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
107 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); |
| 1176 | 108 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
109 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
110 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
111 |
| 1176 | 112 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5) |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
113 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
114 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
115 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
| 183 | 116 |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
117 CREATE INDEX ChangesIndex ON Changes(internalId); |
| 181 | 118 |
|
5227
988dab8deb1c
"/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5221
diff
changeset
|
119 -- New in Orthanc 1.12.0 |
| 5256 | 120 CREATE INDEX LabelsIndex1 ON Labels(id); |
|
5227
988dab8deb1c
"/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5221
diff
changeset
|
121 CREATE INDEX LabelsIndex2 ON Labels(label); -- This index allows efficient lookups |
|
988dab8deb1c
"/tools/find" accepts the "WithLabels" and "WithoutLabels" arguments
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5221
diff
changeset
|
122 |
| 181 | 123 CREATE TRIGGER AttachedFileDeleted |
| 124 AFTER DELETE ON AttachedFiles | |
| 125 BEGIN | |
|
273
d384af918264
more detailed signal about deleted file
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
262
diff
changeset
|
126 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, |
|
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
127 old.compressionType, old.compressedSize, |
|
694
72dc919a028c
upgrade database from v3 to v4
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
693
diff
changeset
|
128 -- These 2 arguments are new in Orthanc 0.7.3 (database v4) |
|
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
129 old.uncompressedMD5, old.compressedMD5); |
| 181 | 130 END; |
| 131 | |
| 132 CREATE TRIGGER ResourceDeleted | |
| 133 AFTER DELETE ON Resources | |
| 134 BEGIN | |
| 1176 | 135 SELECT SignalResourceDeleted(old.publicId, old.resourceType); -- New in Orthanc 0.8.5 (db v5) |
| 183 | 136 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
| 137 FROM Resources AS parent WHERE internalId = old.parentId; | |
| 181 | 138 END; |
| 139 | |
| 183 | 140 -- Delete a parent resource when its unique child is deleted |
| 141 CREATE TRIGGER ResourceDeletedParentCleaning | |
| 142 AFTER DELETE ON Resources | |
| 143 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
| 144 BEGIN | |
| 145 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 146 END; | |
| 252 | 147 |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
148 CREATE TRIGGER PatientAdded |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
149 AFTER INSERT ON Resources |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
150 FOR EACH ROW WHEN new.resourceType = 1 -- "1" corresponds to "ResourceType_Patient" in C++ |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
151 BEGIN |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
152 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
153 END; |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
154 |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
155 |
| 252 | 156 -- Set the version of the database schema |
| 157 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
|
1668
de1413733c97
reconstructing main dicom tags
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1176
diff
changeset
|
158 INSERT INTO GlobalProperties VALUES (1, "6"); |