--- /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;