Mercurial > hg > orthanc
annotate OrthancServer/Sources/Database/Upgrade4To5.sql @ 5635:0e16e677fe64
fixed broken /instances/../tags after reconstructing studies when IngestTranscoding has changed
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Tue, 21 May 2024 17:09:57 +0200 |
parents | 48b8dae6dc77 |
children | f7adfb22e20e |
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 |
5485
48b8dae6dc77
upgrade to year 2024
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5185
diff
changeset
|
4 -- Copyright (C) 2017-2024 Osimis S.A., Belgium |
48b8dae6dc77
upgrade to year 2024
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
5185
diff
changeset
|
5 -- 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
|
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 -- 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
|
13 -- WITHOUT ANY WARRANTY; without even the implied warranty of |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
14 -- 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
|
15 -- General Public License for more details. |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
16 -- |
28944db5318b
adding missing license headers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
4044
diff
changeset
|
17 -- 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
|
18 -- 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
|
19 |
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 -- |
1158 | 22 -- 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
|
23 -- |
1158 | 24 |
25 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
26 -- Remove 2 indexes to speed up |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
27 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
28 DROP INDEX MainDicomTagsIndex2; |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
29 DROP INDEX MainDicomTagsIndexValues; |
1158 | 30 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
31 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
32 -- Add a new table to index the DICOM identifiers |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
33 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
34 CREATE TABLE DicomIdentifiers( |
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 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
42 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
43 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
44 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
1158 | 45 |
46 | |
47 -- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags | |
48 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
49 INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
50 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
|
51 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
52 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
53 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
54 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 55 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
56 DELETE FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
57 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
|
58 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
59 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
60 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
61 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 62 |
63 | |
64 -- Upgrade the "ResourceDeleted" trigger | |
65 | |
66 DROP TRIGGER ResourceDeleted; | |
1176 | 67 DROP TRIGGER ResourceDeletedParentCleaning; |
1158 | 68 |
69 CREATE TRIGGER ResourceDeleted | |
70 AFTER DELETE ON Resources | |
71 BEGIN | |
72 SELECT SignalResourceDeleted(old.publicId, old.resourceType); | |
73 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) | |
74 FROM Resources AS parent WHERE internalId = old.parentId; | |
75 END; | |
76 | |
1176 | 77 CREATE TRIGGER ResourceDeletedParentCleaning |
78 AFTER DELETE ON Resources | |
79 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
80 BEGIN | |
81 DELETE FROM Resources WHERE internalId = old.parentId; | |
82 END; | |
83 | |
1158 | 84 |
85 -- Change the database version | |
86 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
87 | |
88 UPDATE GlobalProperties SET value="5" WHERE property=1; |