Mercurial > hg > orthanc
annotate OrthancServer/Sources/Database/Upgrade4To5.sql @ 4834:bec432ee1094
download of numpy arrays from the REST API
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Fri, 26 Nov 2021 19:03:32 +0100 |
parents | 7053502fbf97 |
children | 2e71a08eea15 43e613a7756b |
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 |
4831
7053502fbf97
added copyright UCLouvain
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4437
diff
changeset
|
5 -- Copyright (C) 2021-2021 Sebastien Jodogne, ICTEAM UCLouvain, Belgium |
4124
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
6 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
7 -- 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
|
8 -- 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
|
9 -- 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
|
10 -- License, or (at your option) any later version. |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
11 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
12 -- In addition, as a special exception, the copyright holders of this |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
13 -- program give permission to link the code of its release with the |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
14 -- OpenSSL project's "OpenSSL" library (or with modified versions of it |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
15 -- that use the same license as the "OpenSSL" library), and distribute |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
16 -- the linked executables. You must obey the GNU General Public License |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
17 -- in all respects for all of the code used other than "OpenSSL". If you |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
18 -- modify file(s) with this exception, you may extend this exception to |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
19 -- your version of the file(s), but you are not obligated to do so. If |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
20 -- you do not wish to do so, delete this exception statement from your |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
21 -- version. If you delete this exception statement from all source files |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
22 -- in the program, then also delete it here. |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
23 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
24 -- 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
|
25 -- WITHOUT ANY WARRANTY; without even the implied warranty of |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
26 -- 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
|
27 -- General Public License for more details. |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
28 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
29 -- 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
|
30 -- 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
|
31 |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
32 |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
33 -- |
1158 | 34 -- This SQLite script updates the version of the Orthanc database from 4 to 5. |
4124
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
35 -- |
1158 | 36 |
37 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
38 -- Remove 2 indexes to speed up |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
39 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
40 DROP INDEX MainDicomTagsIndex2; |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
41 DROP INDEX MainDicomTagsIndexValues; |
1158 | 42 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
43 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
44 -- Add a new table to index the DICOM identifiers |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
45 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
46 CREATE TABLE DicomIdentifiers( |
1158 | 47 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
48 tagGroup INTEGER, | |
49 tagElement INTEGER, | |
50 value TEXT, | |
51 PRIMARY KEY(id, tagGroup, tagElement) | |
52 ); | |
53 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
54 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
55 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
56 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
1158 | 57 |
58 | |
59 -- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags | |
60 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
61 INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
62 WHERE ((tagGroup = 16 AND tagElement = 32) OR -- PatientID (0x0010, 0x0020) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
63 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
64 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
65 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
66 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 67 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
68 DELETE FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
69 WHERE ((tagGroup = 16 AND tagElement = 32) OR -- PatientID (0x0010, 0x0020) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
70 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
71 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
72 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
73 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 74 |
75 | |
76 -- Upgrade the "ResourceDeleted" trigger | |
77 | |
78 DROP TRIGGER ResourceDeleted; | |
1176 | 79 DROP TRIGGER ResourceDeletedParentCleaning; |
1158 | 80 |
81 CREATE TRIGGER ResourceDeleted | |
82 AFTER DELETE ON Resources | |
83 BEGIN | |
84 SELECT SignalResourceDeleted(old.publicId, old.resourceType); | |
85 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) | |
86 FROM Resources AS parent WHERE internalId = old.parentId; | |
87 END; | |
88 | |
1176 | 89 CREATE TRIGGER ResourceDeletedParentCleaning |
90 AFTER DELETE ON Resources | |
91 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
92 BEGIN | |
93 DELETE FROM Resources WHERE internalId = old.parentId; | |
94 END; | |
95 | |
1158 | 96 |
97 -- Change the database version | |
98 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
99 | |
100 UPDATE GlobalProperties SET value="5" WHERE property=1; |