Mercurial > hg > orthanc

comparison OrthancServer/Sources/Database/Upgrade4To5.sql @ 4044:d25f4c0fa160 framework

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
splitting code into OrthancFramework and OrthancServer
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 10 Jun 2020 20:30:34 +0200
parents OrthancServer/Database/Upgrade4To5.sql@61da3c9b4121
children 28944db5318b
comparison
equal deleted inserted replaced
4043:6c6239aec462 4044:d25f4c0fa160
1 -- This SQLite script updates the version of the Orthanc database from 4 to 5.
2
3
4 -- Remove 2 indexes to speed up
5
6 DROP INDEX MainDicomTagsIndex2;
7 DROP INDEX MainDicomTagsIndexValues;
8
9
10 -- Add a new table to index the DICOM identifiers
11
12 CREATE TABLE DicomIdentifiers(
13 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
14 tagGroup INTEGER,
15 tagElement INTEGER,
16 value TEXT,
17 PRIMARY KEY(id, tagGroup, tagElement)
18 );
19
20 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
21 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
22 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY);
23
24
25 -- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags
26
27 INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags
28 WHERE ((tagGroup = 16 AND tagElement = 32) OR -- PatientID (0x0010, 0x0020)
29 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d)
30 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050)
31 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e)
32 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018)
33
34 DELETE FROM MainDicomTags
35 WHERE ((tagGroup = 16 AND tagElement = 32) OR -- PatientID (0x0010, 0x0020)
36 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d)
37 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050)
38 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e)
39 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018)
40
41
42 -- Upgrade the "ResourceDeleted" trigger
43
44 DROP TRIGGER ResourceDeleted;
45 DROP TRIGGER ResourceDeletedParentCleaning;
46
47 CREATE TRIGGER ResourceDeleted
48 AFTER DELETE ON Resources
49 BEGIN
50 SELECT SignalResourceDeleted(old.publicId, old.resourceType);
51 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType)
52 FROM Resources AS parent WHERE internalId = old.parentId;
53 END;
54
55 CREATE TRIGGER ResourceDeletedParentCleaning
56 AFTER DELETE ON Resources
57 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0
58 BEGIN
59 DELETE FROM Resources WHERE internalId = old.parentId;
60 END;
61
62
63 -- Change the database version
64 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration
65
66 UPDATE GlobalProperties SET value="5" WHERE property=1;