Mercurial > hg > orthanc
view Resources/Archives/PrepareDatabase-v1.sql @ 397:941ea46e9e26 lua-scripting
lua filter of new instances
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 02 May 2013 16:34:00 +0200 |
parents | 1e0595885a81 |
children |
line wrap: on
line source
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;