Mercurial > hg > orthanc

comparison OrthancServer/Database/Upgrade4To5.sql @ 3140:977158ab0623

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
making db-changes the new mainline
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 17 Jan 2019 18:32:42 +0100
parents 61da3c9b4121
children
comparison
equal deleted inserted replaced
3138:ab46e537f92e 3140:977158ab0623
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;