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