--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/OrthancServer/PrepareDatabase-v1.sql	Tue Nov 27 17:48:37 2012 +0100
@@ -0,0 +1,136 @@
+CREATE TABLE GlobalProperties(
+       name TEXT PRIMARY KEY,
+       value TEXT
+       );
+
+CREATE TABLE Resources(
+       uuid TEXT PRIMARY KEY,
+       resourceType INTEGER
+       );
+
+CREATE TABLE Patients(
+       uuid TEXT PRIMARY KEY,
+       dicomPatientId TEXT
+       );
+
+CREATE TABLE Studies(
+       uuid TEXT PRIMARY KEY,
+       parentPatient TEXT REFERENCES Patients(uuid) ON DELETE CASCADE,
+       dicomStudy TEXT
+       );
+
+CREATE TABLE Series(
+       uuid TEXT PRIMARY KEY,
+       parentStudy TEXT REFERENCES Studies(uuid) ON DELETE CASCADE,
+       dicomSeries TEXT,
+       expectedNumberOfInstances INTEGER
+       );
+
+CREATE TABLE Instances(
+       uuid TEXT PRIMARY KEY,
+       parentSeries TEXT REFERENCES Series(uuid) ON DELETE CASCADE,
+       dicomInstance TEXT,
+       fileUuid TEXT,
+       fileSize INTEGER,
+       jsonUuid TEXT,
+       distantAet TEXT,
+       indexInSeries INTEGER
+       );
+
+CREATE TABLE MainDicomTags(
+       uuid TEXT,
+       tagGroup INTEGER,
+       tagElement INTEGER,
+       value TEXT,
+       PRIMARY KEY(uuid, tagGroup, tagElement)
+       );
+
+CREATE TABLE Changes(
+       seq INTEGER PRIMARY KEY AUTOINCREMENT,
+       basePath TEXT,
+       uuid TEXT
+       );
+
+
+CREATE INDEX PatientToStudies ON Studies(parentPatient);
+CREATE INDEX StudyToSeries ON Series(parentStudy);
+CREATE INDEX SeriesToInstances ON Instances(parentSeries);
+
+CREATE INDEX DicomPatientIndex ON Patients(dicomPatientId);
+CREATE INDEX DicomStudyIndex ON Studies(dicomStudy);
+CREATE INDEX DicomSeriesIndex ON Series(dicomSeries);
+CREATE INDEX DicomInstanceIndex ON Instances(dicomInstance);
+
+CREATE INDEX MainDicomTagsIndex ON MainDicomTags(uuid);
+CREATE INDEX MainDicomTagsGroupElement ON MainDicomTags(tagGroup, tagElement);
+CREATE INDEX MainDicomTagsValues ON MainDicomTags(value COLLATE BINARY);
+
+CREATE INDEX ChangesIndex ON Changes(uuid);
+
+CREATE TRIGGER InstanceRemoved
+AFTER DELETE ON Instances
+FOR EACH ROW BEGIN
+  DELETE FROM Resources WHERE uuid = old.uuid;
+  DELETE FROM MainDicomTags WHERE uuid = old.uuid;
+  DELETE FROM Changes WHERE uuid = old.uuid;
+  SELECT DeleteFromFileStorage(old.fileUuid);
+  SELECT DeleteFromFileStorage(old.jsonUuid);
+  SELECT SignalDeletedLevel(3, old.parentSeries);
+END;
+
+CREATE TRIGGER SeriesRemoved
+AFTER DELETE ON Series
+FOR EACH ROW BEGIN
+  DELETE FROM Resources WHERE uuid = old.uuid;
+  DELETE FROM MainDicomTags WHERE uuid = old.uuid;
+  DELETE FROM Changes WHERE uuid = old.uuid;
+  SELECT SignalDeletedLevel(2, old.parentStudy);
+END;
+
+CREATE TRIGGER StudyRemoved
+AFTER DELETE ON Studies
+FOR EACH ROW BEGIN
+  DELETE FROM Resources WHERE uuid = old.uuid;
+  DELETE FROM MainDicomTags WHERE uuid = old.uuid;
+  DELETE FROM Changes WHERE uuid = old.uuid;
+  SELECT SignalDeletedLevel(1, old.parentPatient);
+END;
+
+CREATE TRIGGER PatientRemoved
+AFTER DELETE ON Patients
+FOR EACH ROW BEGIN
+  DELETE FROM Resources WHERE uuid = old.uuid;
+  DELETE FROM MainDicomTags WHERE uuid = old.uuid;
+  DELETE FROM Changes WHERE uuid = old.uuid;
+  SELECT SignalDeletedLevel(0, "");
+END;
+
+
+
+
+CREATE TRIGGER InstanceRemovedUpwardCleaning
+AFTER DELETE ON Instances
+FOR EACH ROW 
+  WHEN (SELECT COUNT(*) FROM Instances WHERE parentSeries = old.parentSeries) = 0
+  BEGIN
+    SELECT DeleteFromFileStorage("deleting parent series");  -- TODO REMOVE THIS
+    DELETE FROM Series WHERE uuid = old.parentSeries;
+  END;
+
+CREATE TRIGGER SeriesRemovedUpwardCleaning
+AFTER DELETE ON Series
+FOR EACH ROW 
+  WHEN (SELECT COUNT(*) FROM Series WHERE parentStudy = old.parentStudy) = 0
+  BEGIN
+    SELECT DeleteFromFileStorage("deleting parent study");  -- TODO REMOVE THIS
+    DELETE FROM Studies WHERE uuid = old.parentStudy;
+  END;
+
+CREATE TRIGGER StudyRemovedUpwardCleaning
+AFTER DELETE ON Studies
+FOR EACH ROW 
+  WHEN (SELECT COUNT(*) FROM Studies WHERE parentPatient = old.parentPatient) = 0
+  BEGIN
+    SELECT DeleteFromFileStorage("deleting parent patient");  -- TODO REMOVE THIS
+    DELETE FROM Patients WHERE uuid = old.parentPatient;
+  END;