1-- Orthanc - A Lightweight, RESTful DICOM Store
2-- Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
3-- Department, University Hospital of Liege, Belgium
4-- Copyright (C) 2017-2021 Osimis S.A., Belgium
5--
6-- This program is free software: you can redistribute it and/or
7-- modify it under the terms of the GNU General Public License as
8-- published by the Free Software Foundation, either version 3 of the
9-- License, or (at your option) any later version.
10--
11-- In addition, as a special exception, the copyright holders of this
12-- program give permission to link the code of its release with the
13-- OpenSSL project's "OpenSSL" library (or with modified versions of it
14-- that use the same license as the "OpenSSL" library), and distribute
15-- the linked executables. You must obey the GNU General Public License
16-- in all respects for all of the code used other than "OpenSSL". If you
17-- modify file(s) with this exception, you may extend this exception to
18-- your version of the file(s), but you are not obligated to do so. If
19-- you do not wish to do so, delete this exception statement from your
20-- version. If you delete this exception statement from all source files
21-- in the program, then also delete it here.
22--
23-- This program is distributed in the hope that it will be useful, but
24-- WITHOUT ANY WARRANTY; without even the implied warranty of
25-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
26-- General Public License for more details.
27--
28-- You should have received a copy of the GNU General Public License
29-- along with this program. If not, see <http://www.gnu.org/licenses/>.
30
31
32--
33-- This SQLite script updates the version of the Orthanc database from 4 to 5.
34--
35
36
37-- Remove 2 indexes to speed up
38
39DROP INDEX MainDicomTagsIndex2;
40DROP INDEX MainDicomTagsIndexValues;
41
42
43-- Add a new table to index the DICOM identifiers
44
45CREATE TABLE DicomIdentifiers(
46       id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
47       tagGroup INTEGER,
48       tagElement INTEGER,
49       value TEXT,
50       PRIMARY KEY(id, tagGroup, tagElement)
51       );
52
53CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
54CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
55CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY);
56
57
58-- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags
59
60INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags
61       WHERE ((tagGroup = 16 AND tagElement = 32) OR  -- PatientID (0x0010, 0x0020)
62              (tagGroup = 32 AND tagElement = 13) OR  -- StudyInstanceUID (0x0020, 0x000d)
63              (tagGroup = 8  AND tagElement = 80) OR  -- AccessionNumber (0x0008, 0x0050)
64              (tagGroup = 32 AND tagElement = 14) OR  -- SeriesInstanceUID (0x0020, 0x000e)
65              (tagGroup = 8  AND tagElement = 24));   -- SOPInstanceUID (0x0008, 0x0018)
66
67DELETE FROM MainDicomTags
68       WHERE ((tagGroup = 16 AND tagElement = 32) OR  -- PatientID (0x0010, 0x0020)
69              (tagGroup = 32 AND tagElement = 13) OR  -- StudyInstanceUID (0x0020, 0x000d)
70              (tagGroup = 8  AND tagElement = 80) OR  -- AccessionNumber (0x0008, 0x0050)
71              (tagGroup = 32 AND tagElement = 14) OR  -- SeriesInstanceUID (0x0020, 0x000e)
72              (tagGroup = 8  AND tagElement = 24));   -- SOPInstanceUID (0x0008, 0x0018)
73
74
75-- Upgrade the "ResourceDeleted" trigger
76
77DROP TRIGGER ResourceDeleted;
78DROP TRIGGER ResourceDeletedParentCleaning;
79
80CREATE TRIGGER ResourceDeleted
81AFTER DELETE ON Resources
82BEGIN
83  SELECT SignalResourceDeleted(old.publicId, old.resourceType);
84  SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType)
85    FROM Resources AS parent WHERE internalId = old.parentId;
86END;
87
88CREATE TRIGGER ResourceDeletedParentCleaning
89AFTER DELETE ON Resources
90FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0
91BEGIN
92  DELETE FROM Resources WHERE internalId = old.parentId;
93END;
94
95
96-- Change the database version
97-- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration
98
99UPDATE GlobalProperties SET value="5" WHERE property=1;
100