Mercurial > hg > orthanc
annotate OrthancServer/Database/PrepareDatabase.sql @ 3508:70524b4acc72
Name of temporary files now include the process ID to ease design of scripts cleaning /tmp
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Thu, 29 Aug 2019 11:41:26 +0200 |
| parents | 61da3c9b4121 |
| children |
| 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 | |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
13 CREATE TABLE MainDicomTags( |
| 1158 | 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 | |
| 1176 | 21 -- The following table was added in Orthanc 0.8.5 (database v5) |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
22 CREATE TABLE DicomIdentifiers( |
| 181 | 23 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
| 24 tagGroup INTEGER, | |
| 25 tagElement INTEGER, | |
| 26 value TEXT, | |
| 27 PRIMARY KEY(id, tagGroup, tagElement) | |
| 28 ); | |
| 29 | |
| 30 CREATE TABLE Metadata( | |
| 31 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
| 32 type INTEGER, | |
| 33 value TEXT, | |
| 34 PRIMARY KEY(id, type) | |
| 35 ); | |
| 36 | |
| 37 CREATE TABLE AttachedFiles( | |
| 38 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
|
39 fileType INTEGER, |
| 181 | 40 uuid TEXT, |
| 183 | 41 compressedSize INTEGER, |
| 181 | 42 uncompressedSize INTEGER, |
| 43 compressionType INTEGER, | |
|
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
44 uncompressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4) |
|
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
45 compressedMD5 TEXT, -- New in Orthanc 0.7.3 (database v4) |
|
197
530a25320461
removal of text as ids in sqlite db
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
192
diff
changeset
|
46 PRIMARY KEY(id, fileType) |
| 181 | 47 ); |
| 48 | |
| 183 | 49 CREATE TABLE Changes( |
| 50 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 51 changeType INTEGER, | |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
52 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
| 183 | 53 resourceType INTEGER, |
| 54 date TEXT | |
| 55 ); | |
| 56 | |
| 231 | 57 CREATE TABLE ExportedResources( |
| 183 | 58 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
| 231 | 59 resourceType INTEGER, |
| 60 publicId TEXT, | |
| 183 | 61 remoteModality TEXT, |
| 62 patientId TEXT, | |
| 63 studyInstanceUid TEXT, | |
| 64 seriesInstanceUid TEXT, | |
| 65 sopInstanceUid TEXT, | |
| 66 date TEXT | |
| 67 ); | |
| 68 | |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
69 CREATE TABLE PatientRecyclingOrder( |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
70 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
71 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
72 ); |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
73 |
| 181 | 74 CREATE INDEX ChildrenIndex ON Resources(parentId); |
| 75 CREATE INDEX PublicIndex ON Resources(publicId); | |
| 190 | 76 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
77 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); |
| 181 | 78 |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
79 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); |
| 1176 | 80 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
81 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
82 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
83 |
| 1176 | 84 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5) |
|
1159
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
85 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
86 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); |
|
8f9d49192815
speeding up db schema
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1158
diff
changeset
|
87 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); |
| 183 | 88 |
|
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
89 CREATE INDEX ChangesIndex ON Changes(internalId); |
| 181 | 90 |
| 91 CREATE TRIGGER AttachedFileDeleted | |
| 92 AFTER DELETE ON AttachedFiles | |
| 93 BEGIN | |
|
273
d384af918264
more detailed signal about deleted file
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
262
diff
changeset
|
94 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, |
|
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
95 old.compressionType, old.compressedSize, |
|
694
72dc919a028c
upgrade database from v3 to v4
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
693
diff
changeset
|
96 -- These 2 arguments are new in Orthanc 0.7.3 (database v4) |
|
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
97 old.uncompressedMD5, old.compressedMD5); |
| 181 | 98 END; |
| 99 | |
| 100 CREATE TRIGGER ResourceDeleted | |
| 101 AFTER DELETE ON Resources | |
| 102 BEGIN | |
| 1176 | 103 SELECT SignalResourceDeleted(old.publicId, old.resourceType); -- New in Orthanc 0.8.5 (db v5) |
| 183 | 104 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
| 105 FROM Resources AS parent WHERE internalId = old.parentId; | |
| 181 | 106 END; |
| 107 | |
| 183 | 108 -- Delete a parent resource when its unique child is deleted |
| 109 CREATE TRIGGER ResourceDeletedParentCleaning | |
| 110 AFTER DELETE ON Resources | |
| 111 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
| 112 BEGIN | |
| 113 DELETE FROM Resources WHERE internalId = old.parentId; | |
| 114 END; | |
| 252 | 115 |
|
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
116 CREATE TRIGGER PatientAdded |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
117 AFTER INSERT ON Resources |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
118 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
|
119 BEGIN |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
120 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
121 END; |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
122 |
|
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
123 |
| 252 | 124 -- Set the version of the database schema |
| 125 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
|
1668
de1413733c97
reconstructing main dicom tags
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
1176
diff
changeset
|
126 INSERT INTO GlobalProperties VALUES (1, "6"); |