Mercurial > hg > orthanc
comparison OrthancServer/Upgrade4To5.sql @ 1158:badc14fee61f db-changes
speed up db
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Tue, 16 Sep 2014 18:01:42 +0200 |
parents | |
children | 8f9d49192815 |
comparison
equal
deleted
inserted
replaced
1157:22ef457b7985 | 1158:badc14fee61f |
---|---|
1 -- This SQLite script updates the version of the Orthanc database from 4 to 5. | |
2 | |
3 | |
4 CREATE TABLE MainResourcesTags( | |
5 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
6 tagGroup INTEGER, | |
7 tagElement INTEGER, | |
8 value TEXT, | |
9 PRIMARY KEY(id, tagGroup, tagElement) | |
10 ); | |
11 | |
12 CREATE TABLE MainInstancesTags( | |
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 MainResourcesTagsIndex1 ON MainResourcesTags(id); | |
21 CREATE INDEX MainResourcesTagsIndex2 ON MainResourcesTags(tagGroup, tagElement); | |
22 CREATE INDEX MainResourcesTagsIndexValues ON MainResourcesTags(value COLLATE BINARY); | |
23 CREATE INDEX MainInstancesTagsIndex ON MainInstancesTags(id); | |
24 | |
25 | |
26 -- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags | |
27 -- Below, the value "4" corresponds to "ResourceType_Instance". | |
28 -- The "8" and "24" correspond to SOPInstanceUID (0x0008, 0x0018) | |
29 | |
30 INSERT INTO MainResourcesTags SELECT MainDicomTags.* FROM MainDicomTags | |
31 INNER JOIN Resources ON Resources.internalId = MainDicomTags.id | |
32 WHERE (Resources.resourceType != 4 OR | |
33 (MainDicomTags.tagGroup = 8 AND | |
34 MainDicomTags.tagElement = 24)); | |
35 | |
36 INSERT INTO MainInstancesTags SELECT MainDicomTags.* FROM MainDicomTags | |
37 INNER JOIN Resources ON Resources.internalId = MainDicomTags.id | |
38 WHERE (Resources.resourceType = 4 AND | |
39 (MainDicomTags.tagGroup != 8 OR | |
40 MainDicomTags.tagElement != 24)); | |
41 | |
42 -- Remove the MainDicomTags table | |
43 | |
44 DROP INDEX MainDicomTagsIndex1; | |
45 DROP INDEX MainDicomTagsIndex2; | |
46 DROP INDEX MainDicomTagsIndexValues; | |
47 DROP TABLE MainDicomTags; | |
48 | |
49 | |
50 -- Upgrade the "ResourceDeleted" trigger | |
51 | |
52 DROP TRIGGER ResourceDeleted; | |
53 | |
54 CREATE TRIGGER ResourceDeleted | |
55 AFTER DELETE ON Resources | |
56 BEGIN | |
57 SELECT SignalResourceDeleted(old.publicId, old.resourceType); | |
58 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) | |
59 FROM Resources AS parent 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; |