Mercurial > hg > orthanc

comparison OrthancServer/Database/Upgrade4To5.sql @ 3094:61da3c9b4121 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cont reorganization
author Sebastien Jodogne <s.jodogne@gmail.com>
date Sat, 05 Jan 2019 17:52:24 +0100
parents OrthancServer/Upgrade4To5.sql@f24e04838054
children
comparison
equal deleted inserted replaced
3093:2e1808b6146a 3094:61da3c9b4121
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;