Mercurial > hg > orthanc
annotate OrthancServer/PrepareDatabase2.sql @ 189:ccbc2cf64a0d
record main dicom tags and changes
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Mon, 12 Nov 2012 18:03:48 +0100 |
| parents | baada606da3c |
| children | b6cef9d45cc3 |
| rev | line source |
|---|---|
| 181 | 1 CREATE TABLE GlobalProperties( |
| 2 name TEXT 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 Metadata( | |
| 22 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 23 type INTEGER, | |
| 24 value TEXT, | |
| 25 PRIMARY KEY(id, type) | |
| 26 ); | |
| 27 | |
| 28 CREATE TABLE AttachedFiles( | |
| 29 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 30 name TEXT, | |
| 31 uuid TEXT, | |
| 183 | 32 compressedSize INTEGER, |
| 181 | 33 uncompressedSize INTEGER, |
| 34 compressionType INTEGER, | |
| 35 PRIMARY KEY(id, name) | |
| 36 ); | |
| 37 | |
| 183 | 38 CREATE TABLE Changes( |
| 39 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 40 changeType INTEGER, | |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
41 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
| 183 | 42 resourceType INTEGER, |
| 43 date TEXT | |
| 44 ); | |
| 45 | |
| 46 CREATE TABLE ExportedInstances( | |
| 47 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 48 remoteModality TEXT, | |
| 49 publicId TEXT, | |
| 50 patientId TEXT, | |
| 51 studyInstanceUid TEXT, | |
| 52 seriesInstanceUid TEXT, | |
| 53 sopInstanceUid TEXT, | |
| 54 date TEXT | |
| 55 ); | |
| 56 | |
| 181 | 57 CREATE INDEX ChildrenIndex ON Resources(parentId); |
| 58 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 59 | |
| 183 | 60 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); |
| 61 CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | |
| 62 CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | |
| 63 | |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
64 CREATE INDEX ChangesIndex ON Changes(internalId); |
| 181 | 65 |
| 66 CREATE TRIGGER AttachedFileDeleted | |
| 67 AFTER DELETE ON AttachedFiles | |
| 68 BEGIN | |
| 69 SELECT SignalFileDeleted(old.uuid); | |
| 70 END; | |
| 71 | |
| 72 CREATE TRIGGER ResourceDeleted | |
| 73 AFTER DELETE ON Resources | |
| 74 BEGIN | |
| 183 | 75 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
| 76 FROM Resources AS parent WHERE internalId = old.parentId; | |
| 181 | 77 END; |
| 78 | |
| 183 | 79 -- Delete a parent resource when its unique child is deleted |
| 80 CREATE TRIGGER ResourceDeletedParentCleaning | |
| 81 AFTER DELETE ON Resources | |
| 82 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
| 83 BEGIN | |
| 84 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 85 END; |