Mercurial > hg > orthanc-databases
annotate SQLite/Plugins/PrepareIndex.sql @ 452:6780dd8b0a66 pg-transactions
fix latency measure for ODBC
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Thu, 18 Jan 2024 10:04:43 +0100 |
| parents | b0c65094b299 |
| children |
| 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, | |
|
262
b0c65094b299
adding support for revisions in attachments
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
256
diff
changeset
|
46 revision INTEGER, |
| 0 | 47 PRIMARY KEY(id, fileType) |
| 48 ); | |
| 49 | |
| 50 CREATE TABLE Changes( | |
| 51 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 52 changeType INTEGER, | |
| 53 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 54 resourceType INTEGER, | |
| 55 date TEXT | |
| 56 ); | |
| 57 | |
| 58 CREATE TABLE ExportedResources( | |
| 59 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 60 resourceType INTEGER, | |
| 61 publicId TEXT, | |
| 62 remoteModality TEXT, | |
| 63 patientId TEXT, | |
| 64 studyInstanceUid TEXT, | |
| 65 seriesInstanceUid TEXT, | |
| 66 sopInstanceUid TEXT, | |
| 67 date TEXT | |
| 68 ); | |
| 69 | |
| 70 CREATE TABLE PatientRecyclingOrder( | |
| 71 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 72 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | |
| 73 ); | |
| 74 | |
| 75 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
| 76 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 77 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
| 78 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
| 79 | |
| 80 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); | |
| 81 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up | |
| 82 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | |
| 83 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | |
| 84 | |
| 85 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5) | |
| 86 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
| 87 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
| 88 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); | |
| 89 | |
| 90 CREATE INDEX ChangesIndex ON Changes(internalId); | |
| 91 | |
| 92 | |
| 93 | |
| 94 -- New tables wrt. Orthanc core | |
| 95 CREATE TABLE DeletedFiles( | |
| 96 uuid TEXT NOT NULL, -- 0 | |
| 97 fileType INTEGER, -- 1 | |
| 98 compressedSize INTEGER, -- 2 | |
| 99 uncompressedSize INTEGER, -- 3 | |
| 100 compressionType INTEGER, -- 4 | |
| 101 uncompressedHash TEXT, -- 5 | |
| 102 compressedHash TEXT -- 6 | |
| 103 ); | |
| 104 | |
| 105 CREATE TABLE RemainingAncestor( | |
| 106 resourceType INTEGER NOT NULL, | |
| 107 publicId TEXT NOT NULL | |
| 108 ); | |
| 109 | |
| 110 CREATE TABLE DeletedResources( | |
| 111 resourceType INTEGER NOT NULL, | |
| 112 publicId TEXT NOT NULL | |
| 113 ); | |
| 114 -- End of differences | |
| 115 | |
| 116 | |
| 117 | |
| 118 CREATE TRIGGER AttachedFileDeleted | |
| 119 AFTER DELETE ON AttachedFiles | |
| 120 BEGIN | |
| 121 INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, | |
| 122 old.uncompressedSize, old.compressionType, | |
| 123 old.uncompressedHash, old.compressedHash); | |
| 124 END; | |
| 125 | |
| 126 | |
| 127 CREATE TRIGGER ResourceDeleted | |
| 128 AFTER DELETE ON Resources | |
| 129 BEGIN | |
| 130 INSERT INTO DeletedResources VALUES(old.resourceType, old.publicId); | |
| 131 END; | |
| 132 | |
| 133 | |
| 134 -- Delete a parent resource when its unique child gets deleted | |
| 135 CREATE TRIGGER ResourceDeletedParentCleaning | |
| 136 AFTER DELETE ON Resources | |
| 137 FOR EACH ROW WHEN NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) | |
| 138 BEGIN | |
| 139 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 140 END; | |
| 141 | |
| 142 -- Signal that the deleted resource has a remaining parent, if the | |
| 143 -- deleted resource has a sibling resource | |
| 144 CREATE TRIGGER ResourceRemainingAncestorFound | |
| 145 AFTER DELETE ON Resources | |
| 146 FOR EACH ROW WHEN EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) | |
| 147 BEGIN | |
| 148 INSERT INTO RemainingAncestor(resourceType, publicId) | |
| 149 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; | |
| 150 END; | |
| 151 | |
| 152 | |
| 153 CREATE TRIGGER PatientAdded | |
| 154 AFTER INSERT ON Resources | |
| 155 FOR EACH ROW WHEN new.resourceType = 0 -- The "0" corresponds to "OrthancPluginResourceType_Patient" | |
| 156 BEGIN | |
| 157 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); | |
| 158 END; |