Mercurial > hg > orthanc
comparison OrthancServer/PrepareDatabase.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 | 72dc919a028c |
children | 8f9d49192815 |
comparison
equal
deleted
inserted
replaced
1157:22ef457b7985 | 1158:badc14fee61f |
---|---|
8 resourceType INTEGER, | 8 resourceType INTEGER, |
9 publicId TEXT, | 9 publicId TEXT, |
10 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | 10 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE |
11 ); | 11 ); |
12 | 12 |
13 CREATE TABLE MainDicomTags( | 13 CREATE TABLE MainResourcesTags( |
14 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
15 tagGroup INTEGER, | |
16 tagElement INTEGER, | |
17 value TEXT, | |
18 PRIMARY KEY(id, tagGroup, tagElement) | |
19 ); | |
20 | |
21 CREATE TABLE MainInstancesTags( | |
14 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | 22 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
15 tagGroup INTEGER, | 23 tagGroup INTEGER, |
16 tagElement INTEGER, | 24 tagElement INTEGER, |
17 value TEXT, | 25 value TEXT, |
18 PRIMARY KEY(id, tagGroup, tagElement) | 26 PRIMARY KEY(id, tagGroup, tagElement) |
65 CREATE INDEX ChildrenIndex ON Resources(parentId); | 73 CREATE INDEX ChildrenIndex ON Resources(parentId); |
66 CREATE INDEX PublicIndex ON Resources(publicId); | 74 CREATE INDEX PublicIndex ON Resources(publicId); |
67 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | 75 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); |
68 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | 76 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); |
69 | 77 |
70 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); | 78 CREATE INDEX MainResourcesTagsIndex1 ON MainResourcesTags(id); |
71 CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | 79 CREATE INDEX MainResourcesTagsIndex2 ON MainResourcesTags(tagGroup, tagElement); |
72 CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | 80 CREATE INDEX MainResourcesTagsIndexValues ON MainResourcesTags(value COLLATE BINARY); |
81 CREATE INDEX MainInstancesTagsIndex ON MainInstancesTags(id); | |
73 | 82 |
74 CREATE INDEX ChangesIndex ON Changes(internalId); | 83 CREATE INDEX ChangesIndex ON Changes(internalId); |
75 | 84 |
76 CREATE TRIGGER AttachedFileDeleted | 85 CREATE TRIGGER AttachedFileDeleted |
77 AFTER DELETE ON AttachedFiles | 86 AFTER DELETE ON AttachedFiles |
83 END; | 92 END; |
84 | 93 |
85 CREATE TRIGGER ResourceDeleted | 94 CREATE TRIGGER ResourceDeleted |
86 AFTER DELETE ON Resources | 95 AFTER DELETE ON Resources |
87 BEGIN | 96 BEGIN |
97 SELECT SignalResourceDeleted(old.publicId, old.resourceType); -- New in Orthanc 0.8.4 (db v5) | |
88 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) | 98 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
89 FROM Resources AS parent WHERE internalId = old.parentId; | 99 FROM Resources AS parent WHERE internalId = old.parentId; |
90 END; | 100 END; |
91 | 101 |
92 -- Delete a parent resource when its unique child is deleted | 102 -- Delete a parent resource when its unique child is deleted |
105 END; | 115 END; |
106 | 116 |
107 | 117 |
108 -- Set the version of the database schema | 118 -- Set the version of the database schema |
109 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | 119 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration |
110 INSERT INTO GlobalProperties VALUES (1, "4"); | 120 INSERT INTO GlobalProperties VALUES (1, "5"); |