Mercurial > hg > orthanc
annotate OrthancServer/Sources/Database/Upgrade4To5.sql @ 4709:a9a75281cae9 openssl-3.x
fix build on MSVC 2008 32bit
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Tue, 22 Jun 2021 10:26:27 +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 |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
20 -- |
1158 | 21 -- 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
|
22 -- |
1158 | 23 |
24 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
25 -- Remove 2 indexes to speed up |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
26 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
27 DROP INDEX MainDicomTagsIndex2; |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
28 DROP INDEX MainDicomTagsIndexValues; |
1158 | 29 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
30 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
31 -- Add a new table to index the DICOM identifiers |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
32 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
33 CREATE TABLE DicomIdentifiers( |
1158 | 34 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
35 tagGroup INTEGER, | |
36 tagElement INTEGER, | |
37 value TEXT, | |
38 PRIMARY KEY(id, tagGroup, tagElement) | |
39 ); | |
40 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
41 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
42 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
43 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
1158 | 44 |
45 | |
46 -- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags | |
47 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
48 INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
49 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
|
50 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
51 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
52 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
53 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 54 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
55 DELETE FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
56 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
|
57 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
58 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
59 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
60 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 61 |
62 | |
63 -- Upgrade the "ResourceDeleted" trigger | |
64 | |
65 DROP TRIGGER ResourceDeleted; | |
1176 | 66 DROP TRIGGER ResourceDeletedParentCleaning; |
1158 | 67 |
68 CREATE TRIGGER ResourceDeleted | |
69 AFTER DELETE ON Resources | |
70 BEGIN | |
71 SELECT SignalResourceDeleted(old.publicId, old.resourceType); | |
72 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) | |
73 FROM Resources AS parent WHERE internalId = old.parentId; | |
74 END; | |
75 | |
1176 | 76 CREATE TRIGGER ResourceDeletedParentCleaning |
77 AFTER DELETE ON Resources | |
78 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
79 BEGIN | |
80 DELETE FROM Resources WHERE internalId = old.parentId; | |
81 END; | |
82 | |
1158 | 83 |
84 -- Change the database version | |
85 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
86 | |
87 UPDATE GlobalProperties SET value="5" WHERE property=1; |