Mercurial > hg > orthanc
annotate OrthancServer/PrepareDatabase.sql @ 1158:badc14fee61f db-changes
speed up db
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Tue, 16 Sep 2014 18:01:42 +0200 |
parents | 72dc919a028c |
children | 8f9d49192815 |
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 | |
1158 | 13 CREATE TABLE MainResourcesTags( |
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 MainInstancesTags( | |
181 | 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, | |
33 PRIMARY KEY(id, type) | |
34 ); | |
35 | |
36 CREATE TABLE AttachedFiles( | |
37 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
|
38 fileType INTEGER, |
181 | 39 uuid TEXT, |
183 | 40 compressedSize INTEGER, |
181 | 41 uncompressedSize INTEGER, |
42 compressionType INTEGER, | |
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
43 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
|
44 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
|
45 PRIMARY KEY(id, fileType) |
181 | 46 ); |
47 | |
183 | 48 CREATE TABLE Changes( |
49 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
50 changeType INTEGER, | |
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
51 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, |
183 | 52 resourceType INTEGER, |
53 date TEXT | |
54 ); | |
55 | |
231 | 56 CREATE TABLE ExportedResources( |
183 | 57 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
231 | 58 resourceType INTEGER, |
59 publicId TEXT, | |
183 | 60 remoteModality TEXT, |
61 patientId TEXT, | |
62 studyInstanceUid TEXT, | |
63 seriesInstanceUid TEXT, | |
64 sopInstanceUid TEXT, | |
65 date TEXT | |
66 ); | |
67 | |
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
68 CREATE TABLE PatientRecyclingOrder( |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
69 seq INTEGER PRIMARY KEY AUTOINCREMENT, |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
70 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
71 ); |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
72 |
181 | 73 CREATE INDEX ChildrenIndex ON Resources(parentId); |
74 CREATE INDEX PublicIndex ON Resources(publicId); | |
190 | 75 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); |
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
76 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); |
181 | 77 |
1158 | 78 CREATE INDEX MainResourcesTagsIndex1 ON MainResourcesTags(id); |
79 CREATE INDEX MainResourcesTagsIndex2 ON MainResourcesTags(tagGroup, tagElement); | |
80 CREATE INDEX MainResourcesTagsIndexValues ON MainResourcesTags(value COLLATE BINARY); | |
81 CREATE INDEX MainInstancesTagsIndex ON MainInstancesTags(id); | |
183 | 82 |
189
ccbc2cf64a0d
record main dicom tags and changes
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
183
diff
changeset
|
83 CREATE INDEX ChangesIndex ON Changes(internalId); |
181 | 84 |
85 CREATE TRIGGER AttachedFileDeleted | |
86 AFTER DELETE ON AttachedFiles | |
87 BEGIN | |
273
d384af918264
more detailed signal about deleted file
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
262
diff
changeset
|
88 SELECT SignalFileDeleted(old.uuid, old.fileType, old.uncompressedSize, |
693
01d8611c4a60
md5 for attached files
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
616
diff
changeset
|
89 old.compressionType, old.compressedSize, |
694
72dc919a028c
upgrade database from v3 to v4
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
693
diff
changeset
|
90 -- 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
|
91 old.uncompressedMD5, old.compressedMD5); |
181 | 92 END; |
93 | |
94 CREATE TRIGGER ResourceDeleted | |
95 AFTER DELETE ON Resources | |
96 BEGIN | |
1158 | 97 SELECT SignalResourceDeleted(old.publicId, old.resourceType); -- New in Orthanc 0.8.4 (db v5) |
183 | 98 SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) |
99 FROM Resources AS parent WHERE internalId = old.parentId; | |
181 | 100 END; |
101 | |
183 | 102 -- Delete a parent resource when its unique child is deleted |
103 CREATE TRIGGER ResourceDeletedParentCleaning | |
104 AFTER DELETE ON Resources | |
105 FOR EACH ROW WHEN (SELECT COUNT(*) FROM Resources WHERE parentId = old.parentId) = 0 | |
106 BEGIN | |
107 DELETE FROM Resources WHERE internalId = old.parentId; | |
108 END; | |
252 | 109 |
262
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
110 CREATE TRIGGER PatientAdded |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
111 AFTER INSERT ON Resources |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
112 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
|
113 BEGIN |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
114 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
115 END; |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
116 |
2354560daf2f
primitives for recycling patients
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
252
diff
changeset
|
117 |
252 | 118 -- Set the version of the database schema |
119 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration | |
1158 | 120 INSERT INTO GlobalProperties VALUES (1, "5"); |