Mercurial > hg > orthanc

comparison OrthancServer/Upgrade4To5.sql @ 1158:badc14fee61f db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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;