Mercurial > hg > orthanc
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/OrthancServer/Upgrade4To5.sql Tue Sep 16 18:01:42 2014 +0200 @@ -0,0 +1,66 @@ +-- This SQLite script updates the version of the Orthanc database from 4 to 5. + + +CREATE TABLE MainResourcesTags( + id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE MainInstancesTags( + id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE INDEX MainResourcesTagsIndex1 ON MainResourcesTags(id); +CREATE INDEX MainResourcesTagsIndex2 ON MainResourcesTags(tagGroup, tagElement); +CREATE INDEX MainResourcesTagsIndexValues ON MainResourcesTags(value COLLATE BINARY); +CREATE INDEX MainInstancesTagsIndex ON MainInstancesTags(id); + + +-- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags +-- Below, the value "4" corresponds to "ResourceType_Instance". +-- The "8" and "24" correspond to SOPInstanceUID (0x0008, 0x0018) + +INSERT INTO MainResourcesTags SELECT MainDicomTags.* FROM MainDicomTags + INNER JOIN Resources ON Resources.internalId = MainDicomTags.id + WHERE (Resources.resourceType != 4 OR + (MainDicomTags.tagGroup = 8 AND + MainDicomTags.tagElement = 24)); + +INSERT INTO MainInstancesTags SELECT MainDicomTags.* FROM MainDicomTags + INNER JOIN Resources ON Resources.internalId = MainDicomTags.id + WHERE (Resources.resourceType = 4 AND + (MainDicomTags.tagGroup != 8 OR + MainDicomTags.tagElement != 24)); + +-- Remove the MainDicomTags table + +DROP INDEX MainDicomTagsIndex1; +DROP INDEX MainDicomTagsIndex2; +DROP INDEX MainDicomTagsIndexValues; +DROP TABLE MainDicomTags; + + +-- Upgrade the "ResourceDeleted" trigger + +DROP TRIGGER ResourceDeleted; + +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +BEGIN + SELECT SignalResourceDeleted(old.publicId, old.resourceType); + SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) + FROM Resources AS parent WHERE internalId = old.parentId; +END; + + +-- Change the database version +-- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration + +UPDATE GlobalProperties SET value="5" WHERE property=1;