Mercurial > hg > orthanc
comparison OrthancServer/Database/Upgrade4To5.sql @ 3094:61da3c9b4121 db-changes
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; |