Mercurial > hg > orthanc

comparison OrthancServer/PrepareDatabase.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 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");