Mercurial > hg > orthanc
annotate OrthancServer/Upgrade4To5.sql @ 1793:9a3a77d1d2a2
backward compatibility with Orthanc 0.9.4
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 19 Nov 2015 11:57:32 +0100 |
parents | f24e04838054 |
children |
rev | line source |
---|---|
1158 | 1 -- This SQLite script updates the version of the Orthanc database from 4 to 5. |
2 | |
3 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
4 -- Remove 2 indexes to speed up |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
5 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
6 DROP INDEX MainDicomTagsIndex2; |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
7 DROP INDEX MainDicomTagsIndexValues; |
1158 | 8 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
9 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
10 -- Add a new table to index the DICOM identifiers |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
11 |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
12 CREATE TABLE DicomIdentifiers( |
1158 | 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 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
20 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
21 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
22 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
1158 | 23 |
24 | |
25 -- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags | |
26 | |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
27 INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
28 WHERE ((tagGroup = 16 AND tagElement = 32) OR -- PatientID (0x0010, 0x0020) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
29 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
30 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
31 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
32 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 33 |
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
34 DELETE FROM MainDicomTags |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
35 WHERE ((tagGroup = 16 AND tagElement = 32) OR -- PatientID (0x0010, 0x0020) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
36 (tagGroup = 32 AND tagElement = 13) OR -- StudyInstanceUID (0x0020, 0x000d) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
37 (tagGroup = 8 AND tagElement = 80) OR -- AccessionNumber (0x0008, 0x0050) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
38 (tagGroup = 32 AND tagElement = 14) OR -- SeriesInstanceUID (0x0020, 0x000e) |
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
39 (tagGroup = 8 AND tagElement = 24)); -- SOPInstanceUID (0x0008, 0x0018) |
1158 | 40 |
41 | |
42 -- Upgrade the "ResourceDeleted" trigger | |
43 | |
44 DROP TRIGGER ResourceDeleted; | |
1176 | 45 DROP TRIGGER ResourceDeletedParentCleaning; |
1158 | 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 | |
1176 | 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 | |
1158 | 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; |