Mercurial > hg > orthanc
view OrthancServer/PrepareDatabase.sql @ 615:ec0b7a51d7bd find-move-scp
speed up find
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 24 Oct 2013 11:42:46 +0200 |
parents | c2be0a0e049e |
children | dbecea588ef5 |
line wrap: on
line source
CREATE TABLE GlobalProperties( property INTEGER PRIMARY KEY, value TEXT ); CREATE TABLE Resources( internalId INTEGER PRIMARY KEY AUTOINCREMENT, resourceType INTEGER, publicId TEXT, parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE ); CREATE TABLE MainDicomTags( id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, tagGroup INTEGER, tagElement INTEGER, value TEXT, PRIMARY KEY(id, tagGroup, tagElement) ); CREATE TABLE Metadata( id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, type INTEGER, value TEXT, PRIMARY KEY(id, type) ); CREATE TABLE AttachedFiles( id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, fileType INTEGER, uuid TEXT, compressedSize INTEGER, uncompressedSize INTEGER, compressionType INTEGER, PRIMARY KEY(id, fileType) ); CREATE TABLE Changes( seq INTEGER PRIMARY KEY AUTOINCREMENT, changeType INTEGER, internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, resourceType INTEGER, date TEXT ); CREATE TABLE ExportedResources( seq INTEGER PRIMARY KEY AUTOINCREMENT, resourceType INTEGER, publicId TEXT, remoteModality TEXT, patientId TEXT, studyInstanceUid TEXT, seriesInstanceUid TEXT, sopInstanceUid TEXT, date TEXT ); CREATE TABLE PatientRecyclingOrder( seq INTEGER PRIMARY KEY AUTOINCREMENT, patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE ); CREATE INDEX ChildrenIndex ON Resources(parentId); CREATE INDEX PublicIndex ON Resources(publicId); CREATE INDEX ResourceTypeIndex ON Resources(resourceType); CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); CREATE INDEX ChangesIndex ON Changes(internalId); CREATE TRIGGER AttachedFileDeleted AFTER DELETE ON AttachedFiles BEGIN SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, old.compressionType, old.compressedSize); END; CREATE TRIGGER ResourceDeleted AFTER DELETE ON Resources BEGIN SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) FROM Resources AS parent WHERE internalId = old.parentId; END; -- Delete a parent resource when its unique child is deleted CREATE TRIGGER ResourceDeletedParentCleaning AFTER DELETE ON Resources FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 BEGIN DELETE FROM Resources WHERE internalId = old.parentId; END; CREATE TRIGGER PatientAdded AFTER INSERT ON Resources FOR EACH ROW WHEN new.resourceType = 1 -- "1" corresponds to "ResourceType_Patient" in C++ BEGIN INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); END; -- Set the version of the database schema -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration INSERT INTO GlobalProperties VALUES (1, "3");