Mercurial > hg > orthanc
annotate OrthancServer/PrepareDatabase.sql @ 2746:571e0058d2b3 Orthanc-0.6.2
close old branch
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Tue, 17 Jul 2018 09:39:26 +0200 |
| parents | d384af918264 |
| children | c2be0a0e049e |
| rev | line source |
|---|---|
| 181 | 1 CREATE TABLE GlobalProperties( |
|
206
4453a010d0db
flush to disk thread
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
203
diff
changeset
|
2 property INTEGER PRIMARY KEY, |
| 181 | 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, | |
|
197
530a25320461
removal of text as ids in sqlite db
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
192
diff
changeset
|
30 fileType INTEGER, |
| 181 | 31 uuid TEXT, |
| 183 | 32 compressedSize INTEGER, |
| 181 | 33 uncompressedSize INTEGER, |
| 34 compressionType INTEGER, | |
|
197
530a25320461
removal of text as ids in sqlite db
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
192
diff
changeset
|
35 PRIMARY KEY(id, fileType) |
| 181 | 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 | |
| 231 | 46 CREATE TABLE ExportedResources( |
| 183 | 47 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
| 231 | 48 resourceType INTEGER, |
| 49 publicId TEXT, | |
| 183 | 50 remoteModality TEXT, |
| 51 patientId TEXT, | |
| 52 studyInstanceUid TEXT, | |
| 53 seriesInstanceUid TEXT, | |
| 54 sopInstanceUid TEXT, | |
| 55 date TEXT | |
| 56 ); | |
| 57 | |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
58 CREATE TABLE PatientRecyclingOrder( |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
59 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
60 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
61 ); |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
62 |
| 181 | 63 CREATE INDEX ChildrenIndex ON Resources(parentId); |
| 64 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 190 | 65 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
66 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); |
| 181 | 67 |
| 183 | 68 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); |
| 69 CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | |
| 70 CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | |
| 71 | |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
72 CREATE INDEX ChangesIndex ON Changes(internalId); |
| 181 | 73 |
| 74 CREATE TRIGGER AttachedFileDeleted | |
| 75 AFTER DELETE ON AttachedFiles | |
| 76 BEGIN | |
|
273
d384af918264
more detailed signal about deleted file
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
262
diff
changeset
|
77 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, |
|
d384af918264
more detailed signal about deleted file
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
262
diff
changeset
|
78 old.compressionType, old.compressedSize); |
| 181 | 79 END; |
| 80 | |
| 81 CREATE TRIGGER ResourceDeleted | |
| 82 AFTER DELETE ON Resources | |
| 83 BEGIN | |
| 183 | 84 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
| 85 FROM Resources AS parent WHERE internalId = old.parentId; | |
| 181 | 86 END; |
| 87 | |
| 183 | 88 -- Delete a parent resource when its unique child is deleted |
| 89 CREATE TRIGGER ResourceDeletedParentCleaning | |
| 90 AFTER DELETE ON Resources | |
| 91 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
| 92 BEGIN | |
| 93 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 94 END; | |
| 252 | 95 |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
96 CREATE TRIGGER PatientAdded |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
97 AFTER INSERT ON Resources |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
98 FOR EACH ROW WHEN new.resourceType = 1 -- "1" corresponds to "ResourceType_Patient" in C++ |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
99 BEGIN |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
100 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
101 END; |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
102 |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
103 |
| 252 | 104 -- Set the version of the database schema |
| 105 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
106 INSERT INTO GlobalProperties VALUES (1, "3"); |