Mercurial > hg > orthanc-databases
annotate SQLite/Plugins/PrepareIndex.sql @ 256:e184dcadf163
handling of revisions in metadata
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Fri, 16 Apr 2021 17:13:33 +0200 |
| parents | 7cea966b6829 |
| children | b0c65094b299 |
| rev | line source |
|---|---|
| 0 | 1 CREATE TABLE GlobalProperties( |
| 2 property INTEGER PRIMARY KEY, | |
| 3 value TEXT | |
| 4 ); | |
| 5 | |
| 6 CREATE TABLE Resources( | |
| 7 internalId INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 8 resourceType INTEGER, | |
| 9 publicId TEXT, | |
| 10 parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 11 ); | |
| 12 | |
| 13 CREATE TABLE MainDicomTags( | |
| 14 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 15 tagGroup INTEGER, | |
| 16 tagElement INTEGER, | |
| 17 value TEXT, | |
| 18 PRIMARY KEY(id, tagGroup, tagElement) | |
| 19 ); | |
| 20 | |
| 21 CREATE TABLE DicomIdentifiers( | |
| 22 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 23 tagGroup INTEGER, | |
| 24 tagElement INTEGER, | |
| 25 value TEXT, | |
| 26 PRIMARY KEY(id, tagGroup, tagElement) | |
| 27 ); | |
| 28 | |
| 29 CREATE TABLE Metadata( | |
| 30 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 31 type INTEGER, | |
| 32 value TEXT, | |
|
256
e184dcadf163
handling of revisions in metadata
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
33 revision INTEGER, |
| 0 | 34 PRIMARY KEY(id, type) |
| 35 ); | |
| 36 | |
| 37 CREATE TABLE AttachedFiles( | |
| 38 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 39 fileType INTEGER, | |
| 40 uuid TEXT, | |
| 41 compressedSize INTEGER, | |
| 42 uncompressedSize INTEGER, | |
| 43 compressionType INTEGER, | |
| 44 uncompressedHash TEXT, | |
| 45 compressedHash TEXT, | |
| 46 PRIMARY KEY(id, fileType) | |
| 47 ); | |
| 48 | |
| 49 CREATE TABLE Changes( | |
| 50 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 51 changeType INTEGER, | |
| 52 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 53 resourceType INTEGER, | |
| 54 date TEXT | |
| 55 ); | |
| 56 | |
| 57 CREATE TABLE ExportedResources( | |
| 58 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 59 resourceType INTEGER, | |
| 60 publicId TEXT, | |
| 61 remoteModality TEXT, | |
| 62 patientId TEXT, | |
| 63 studyInstanceUid TEXT, | |
| 64 seriesInstanceUid TEXT, | |
| 65 sopInstanceUid TEXT, | |
| 66 date TEXT | |
| 67 ); | |
| 68 | |
| 69 CREATE TABLE PatientRecyclingOrder( | |
| 70 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 71 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 72 ); | |
| 73 | |
| 74 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
| 75 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 76 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
| 77 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
| 78 | |
| 79 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); | |
| 80 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up | |
| 81 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | |
| 82 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | |
| 83 | |
| 84 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5) | |
| 85 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
| 86 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
| 87 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); | |
| 88 | |
| 89 CREATE INDEX ChangesIndex ON Changes(internalId); | |
| 90 | |
| 91 | |
| 92 | |
| 93 -- New tables wrt. Orthanc core | |
| 94 CREATE TABLE DeletedFiles( | |
| 95 uuid TEXT NOT NULL, -- 0 | |
| 96 fileType INTEGER, -- 1 | |
| 97 compressedSize INTEGER, -- 2 | |
| 98 uncompressedSize INTEGER, -- 3 | |
| 99 compressionType INTEGER, -- 4 | |
| 100 uncompressedHash TEXT, -- 5 | |
| 101 compressedHash TEXT -- 6 | |
| 102 ); | |
| 103 | |
| 104 CREATE TABLE RemainingAncestor( | |
| 105 resourceType INTEGER NOT NULL, | |
| 106 publicId TEXT NOT NULL | |
| 107 ); | |
| 108 | |
| 109 CREATE TABLE DeletedResources( | |
| 110 resourceType INTEGER NOT NULL, | |
| 111 publicId TEXT NOT NULL | |
| 112 ); | |
| 113 -- End of differences | |
| 114 | |
| 115 | |
| 116 | |
| 117 CREATE TRIGGER AttachedFileDeleted | |
| 118 AFTER DELETE ON AttachedFiles | |
| 119 BEGIN | |
| 120 INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, | |
| 121 old.uncompressedSize, old.compressionType, | |
| 122 old.uncompressedHash, old.compressedHash); | |
| 123 END; | |
| 124 | |
| 125 | |
| 126 CREATE TRIGGER ResourceDeleted | |
| 127 AFTER DELETE ON Resources | |
| 128 BEGIN | |
| 129 INSERT INTO DeletedResources VALUES(old.resourceType, old.publicId); | |
| 130 END; | |
| 131 | |
| 132 | |
| 133 -- Delete a parent resource when its unique child gets deleted | |
| 134 CREATE TRIGGER ResourceDeletedParentCleaning | |
| 135 AFTER DELETE ON Resources | |
| 136 FOR EACH ROW WHEN NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) | |
| 137 BEGIN | |
| 138 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 139 END; | |
| 140 | |
| 141 -- Signal that the deleted resource has a remaining parent, if the | |
| 142 -- deleted resource has a sibling resource | |
| 143 CREATE TRIGGER ResourceRemainingAncestorFound | |
| 144 AFTER DELETE ON Resources | |
| 145 FOR EACH ROW WHEN EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) | |
| 146 BEGIN | |
| 147 INSERT INTO RemainingAncestor(resourceType, publicId) | |
| 148 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; | |
| 149 END; | |
| 150 | |
| 151 | |
| 152 CREATE TRIGGER PatientAdded | |
| 153 AFTER INSERT ON Resources | |
| 154 FOR EACH ROW WHEN new.resourceType = 0 -- The "0" corresponds to "OrthancPluginResourceType_Patient" | |
| 155 BEGIN | |
| 156 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); | |
| 157 END; |