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