Mercurial > hg > orthanc-databases
diff SQLite/Plugins/PrepareIndex.sql @ 0:7cea966b6829
initial commit
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Wed, 04 Jul 2018 08:16:29 +0200 |
parents | |
children | e184dcadf163 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/SQLite/Plugins/PrepareIndex.sql Wed Jul 04 08:16:29 2018 +0200 @@ -0,0 +1,156 @@ +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 DicomIdentifiers( + 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, + uncompressedHash TEXT, + compressedHash TEXT, + 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); +-- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up +-- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); +-- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); + +-- The 3 following indexes were added in Orthanc 0.8.5 (database v5) +CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); +CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); + +CREATE INDEX ChangesIndex ON Changes(internalId); + + + +-- New tables wrt. Orthanc core +CREATE TABLE DeletedFiles( + uuid TEXT NOT NULL, -- 0 + fileType INTEGER, -- 1 + compressedSize INTEGER, -- 2 + uncompressedSize INTEGER, -- 3 + compressionType INTEGER, -- 4 + uncompressedHash TEXT, -- 5 + compressedHash TEXT -- 6 + ); + +CREATE TABLE RemainingAncestor( + resourceType INTEGER NOT NULL, + publicId TEXT NOT NULL + ); + +CREATE TABLE DeletedResources( + resourceType INTEGER NOT NULL, + publicId TEXT NOT NULL + ); +-- End of differences + + + +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +BEGIN + INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash); +END; + + +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +BEGIN + INSERT INTO DeletedResources VALUES(old.resourceType, old.publicId); +END; + + +-- Delete a parent resource when its unique child gets deleted +CREATE TRIGGER ResourceDeletedParentCleaning +AFTER DELETE ON Resources +FOR EACH ROW WHEN NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) +BEGIN + DELETE FROM Resources WHERE internalId = old.parentId; +END; + +-- Signal that the deleted resource has a remaining parent, if the +-- deleted resource has a sibling resource +CREATE TRIGGER ResourceRemainingAncestorFound +AFTER DELETE ON Resources +FOR EACH ROW WHEN EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) +BEGIN + INSERT INTO RemainingAncestor(resourceType, publicId) + SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; +END; + + +CREATE TRIGGER PatientAdded +AFTER INSERT ON Resources +FOR EACH ROW WHEN new.resourceType = 0 -- The "0" corresponds to "OrthancPluginResourceType_Patient" +BEGIN + INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); +END;