Mercurial > hg > orthanc
annotate OrthancServer/Sources/Database/PrepareDatabase.sql @ 4785:61da49321754 openssl-3.x
integration mainline->openssl-3.x
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Mon, 30 Aug 2021 22:21:24 +0200 |
parents | f0038043fb97 |
children | 2e71a08eea15 |
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 |
4437
d9473bd5ed43
upgrade to year 2021
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4124
diff
changeset
|
4 -- Copyright (C) 2017-2021 Osimis S.A., Belgium |
4124
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
5 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
6 -- 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
|
7 -- 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
|
8 -- 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
|
9 -- License, or (at your option) any later version. |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
10 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
11 -- 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
|
12 -- WITHOUT ANY WARRANTY; without even the implied warranty of |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
13 -- 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
|
14 -- General Public License for more details. |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
15 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
16 -- 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
|
17 -- 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
|
18 |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
19 |
181 | 20 CREATE TABLE GlobalProperties( |
206
4453a010d0db
flush to disk thread
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
203
diff
changeset
|
21 property INTEGER PRIMARY KEY, |
181 | 22 value TEXT |
23 ); | |
24 | |
25 CREATE TABLE Resources( | |
26 internalId INTEGER PRIMARY KEY AUTOINCREMENT, | |
27 resourceType INTEGER, | |
28 publicId TEXT, | |
29 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | |
30 ); | |
31 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
32 CREATE TABLE MainDicomTags( |
1158 | 33 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
34 tagGroup INTEGER, | |
35 tagElement INTEGER, | |
36 value TEXT, | |
37 PRIMARY KEY(id, tagGroup, tagElement) | |
38 ); | |
39 | |
1176 | 40 -- 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
|
41 CREATE TABLE DicomIdentifiers( |
181 | 42 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
43 tagGroup INTEGER, | |
44 tagElement INTEGER, | |
45 value TEXT, | |
46 PRIMARY KEY(id, tagGroup, tagElement) | |
47 ); | |
48 | |
49 CREATE TABLE Metadata( | |
50 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
51 type INTEGER, | |
52 value TEXT, | |
53 PRIMARY KEY(id, type) | |
54 ); | |
55 | |
56 CREATE TABLE AttachedFiles( | |
57 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
|
58 fileType INTEGER, |
181 | 59 uuid TEXT, |
183 | 60 compressedSize INTEGER, |
181 | 61 uncompressedSize INTEGER, |
62 compressionType INTEGER, | |
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
63 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
|
64 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
|
65 PRIMARY KEY(id, fileType) |
181 | 66 ); |
67 | |
183 | 68 CREATE TABLE Changes( |
69 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
70 changeType INTEGER, | |
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
71 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
183 | 72 resourceType INTEGER, |
73 date TEXT | |
74 ); | |
75 | |
231 | 76 CREATE TABLE ExportedResources( |
183 | 77 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
231 | 78 resourceType INTEGER, |
79 publicId TEXT, | |
183 | 80 remoteModality TEXT, |
81 patientId TEXT, | |
82 studyInstanceUid TEXT, | |
83 seriesInstanceUid TEXT, | |
84 sopInstanceUid TEXT, | |
85 date TEXT | |
86 ); | |
87 | |
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
88 CREATE TABLE PatientRecyclingOrder( |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
89 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
90 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
91 ); |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
92 |
181 | 93 CREATE INDEX ChildrenIndex ON Resources(parentId); |
94 CREATE INDEX PublicIndex ON Resources(publicId); | |
190 | 95 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); |
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
96 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); |
181 | 97 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
98 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); |
1176 | 99 -- 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
|
100 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
101 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
102 |
1176 | 103 -- 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
|
104 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
105 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
106 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
183 | 107 |
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
108 CREATE INDEX ChangesIndex ON Changes(internalId); |
181 | 109 |
110 CREATE TRIGGER AttachedFileDeleted | |
111 AFTER DELETE ON AttachedFiles | |
112 BEGIN | |
273
d384af918264
more detailed signal about deleted file
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
262
diff
changeset
|
113 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, |
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
114 old.compressionType, old.compressedSize, |
694
72dc919a028c
upgrade database from v3 to v4
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
693
diff
changeset
|
115 -- 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
|
116 old.uncompressedMD5, old.compressedMD5); |
181 | 117 END; |
118 | |
119 CREATE TRIGGER ResourceDeleted | |
120 AFTER DELETE ON Resources | |
121 BEGIN | |
1176 | 122 SELECT SignalResourceDeleted(old.publicId, old.resourceType); -- New in Orthanc 0.8.5 (db v5) |
183 | 123 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
124 FROM Resources AS parent WHERE internalId = old.parentId; | |
181 | 125 END; |
126 | |
183 | 127 -- Delete a parent resource when its unique child is deleted |
128 CREATE TRIGGER ResourceDeletedParentCleaning | |
129 AFTER DELETE ON Resources | |
130 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
131 BEGIN | |
132 DELETE FROM Resources WHERE internalId = old.parentId; | |
133 END; | |
252 | 134 |
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
135 CREATE TRIGGER PatientAdded |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
136 AFTER INSERT ON Resources |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
137 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
|
138 BEGIN |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
139 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
140 END; |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
141 |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
142 |
252 | 143 -- Set the version of the database schema |
144 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
1668
de1413733c97
reconstructing main dicom tags
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1176
diff
changeset
|
145 INSERT INTO GlobalProperties VALUES (1, "6"); |