Mercurial > hg > orthanc-databases
diff Odbc/Plugins/PrepareIndex.sql @ 329:b5fb8b77ce4d
initial commit of ODBC framework
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Tue, 10 Aug 2021 20:08:53 +0200 |
parents | |
children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Odbc/Plugins/PrepareIndex.sql Tue Aug 10 20:08:53 2021 +0200 @@ -0,0 +1,124 @@ +CREATE TABLE GlobalProperties( + property INTEGER PRIMARY KEY, + value ${LONGTEXT} + ); + +CREATE TABLE ServerProperties( + server VARCHAR(64) NOT NULL, + property INTEGER NOT NULL, + value ${LONGTEXT}, + PRIMARY KEY(server, property) + ); + +CREATE TABLE Resources( + internalId ${AUTOINCREMENT_TYPE}, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL, + parentId BIGINT + ); + +CREATE TABLE MainDicomTags( + id BIGINT NOT NULL, + tagGroup INTEGER NOT NULL, + tagElement INTEGER NOT NULL, + value VARCHAR(255), + PRIMARY KEY(id, tagGroup, tagElement), + CONSTRAINT MainDicomTags1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE DicomIdentifiers( + id BIGINT NOT NULL, + tagGroup INTEGER NOT NULL, + tagElement INTEGER NOT NULL, + value VARCHAR(255), + PRIMARY KEY(id, tagGroup, tagElement), + CONSTRAINT DicomIdentifiers1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE Metadata( + id BIGINT NOT NULL, + type INTEGER NOT NULL, + value ${LONGTEXT}, + revision INTEGER, + PRIMARY KEY(id, type), + CONSTRAINT Metadata1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE AttachedFiles( + id BIGINT NOT NULL, + fileType INTEGER, + uuid VARCHAR(64) NOT NULL, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40), + revision INTEGER, + PRIMARY KEY(id, fileType), + CONSTRAINT AttachedFiles1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE Changes( + seq ${AUTOINCREMENT_TYPE}, + changeType INTEGER, + internalId BIGINT NOT NULL, + resourceType INTEGER, + date VARCHAR(64), + CONSTRAINT Changes1 FOREIGN KEY (internalId) REFERENCES Resources(internalId) ON DELETE CASCADE + ); + +CREATE TABLE ExportedResources( + seq ${AUTOINCREMENT_TYPE}, + resourceType INTEGER, + publicId VARCHAR(64), + remoteModality VARCHAR(64), + patientId VARCHAR(64), + studyInstanceUid VARCHAR(128), + seriesInstanceUid VARCHAR(128), + sopInstanceUid VARCHAR(128), + date VARCHAR(64) + ); + +CREATE TABLE PatientRecyclingOrder( + seq ${AUTOINCREMENT_TYPE}, + patientId BIGINT NOT NULL, + CONSTRAINT PatientRecyclingOrder1 FOREIGN KEY (patientId) 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 MainDicomTagsIndex ON MainDicomTags(id); +CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); +CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value); + +CREATE INDEX ChangesIndex ON Changes(internalId); + + + +-- New tables wrt. Orthanc core +CREATE TABLE DeletedFiles( -- Same structure as AttachedFiles + id BIGINT NOT NULL, + fileType INTEGER, + uuid VARCHAR(64) NOT NULL, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40), + revision INTEGER + ); + +CREATE TABLE DeletedResources( + internalId BIGINT NOT NULL PRIMARY KEY, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + + + +-- Set version of database to 6 +INSERT INTO GlobalProperties VALUES(1, '6');