Mercurial > hg > orthanc-databases
annotate SQLite/Plugins/PrepareIndex.sql @ 258:9856290c3cfd
todo
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Fri, 16 Apr 2021 17:45:11 +0200 |
parents | e184dcadf163 |
children | b0c65094b299 |
rev | line source |
---|---|
0 | 1 CREATE TABLE GlobalProperties( |
2 property INTEGER 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 DicomIdentifiers( | |
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, | |
256
e184dcadf163
handling of revisions in metadata
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
0
diff
changeset
|
33 revision INTEGER, |
0 | 34 PRIMARY KEY(id, type) |
35 ); | |
36 | |
37 CREATE TABLE AttachedFiles( | |
38 id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
39 fileType INTEGER, | |
40 uuid TEXT, | |
41 compressedSize INTEGER, | |
42 uncompressedSize INTEGER, | |
43 compressionType INTEGER, | |
44 uncompressedHash TEXT, | |
45 compressedHash TEXT, | |
46 PRIMARY KEY(id, fileType) | |
47 ); | |
48 | |
49 CREATE TABLE Changes( | |
50 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
51 changeType INTEGER, | |
52 internalId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE, | |
53 resourceType INTEGER, | |
54 date TEXT | |
55 ); | |
56 | |
57 CREATE TABLE ExportedResources( | |
58 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
59 resourceType INTEGER, | |
60 publicId TEXT, | |
61 remoteModality TEXT, | |
62 patientId TEXT, | |
63 studyInstanceUid TEXT, | |
64 seriesInstanceUid TEXT, | |
65 sopInstanceUid TEXT, | |
66 date TEXT | |
67 ); | |
68 | |
69 CREATE TABLE PatientRecyclingOrder( | |
70 seq INTEGER PRIMARY KEY AUTOINCREMENT, | |
71 patientId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE | |
72 ); | |
73 | |
74 CREATE INDEX ChildrenIndex ON Resources(parentId); | |
75 CREATE INDEX PublicIndex ON Resources(publicId); | |
76 CREATE INDEX ResourceTypeIndex ON Resources(resourceType); | |
77 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); | |
78 | |
79 CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id); | |
80 -- The 2 following indexes were removed in Orthanc 0.8.5 (database v5), to speed up | |
81 -- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement); | |
82 -- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY); | |
83 | |
84 -- The 3 following indexes were added in Orthanc 0.8.5 (database v5) | |
85 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); | |
86 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); | |
87 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY); | |
88 | |
89 CREATE INDEX ChangesIndex ON Changes(internalId); | |
90 | |
91 | |
92 | |
93 -- New tables wrt. Orthanc core | |
94 CREATE TABLE DeletedFiles( | |
95 uuid TEXT NOT NULL, -- 0 | |
96 fileType INTEGER, -- 1 | |
97 compressedSize INTEGER, -- 2 | |
98 uncompressedSize INTEGER, -- 3 | |
99 compressionType INTEGER, -- 4 | |
100 uncompressedHash TEXT, -- 5 | |
101 compressedHash TEXT -- 6 | |
102 ); | |
103 | |
104 CREATE TABLE RemainingAncestor( | |
105 resourceType INTEGER NOT NULL, | |
106 publicId TEXT NOT NULL | |
107 ); | |
108 | |
109 CREATE TABLE DeletedResources( | |
110 resourceType INTEGER NOT NULL, | |
111 publicId TEXT NOT NULL | |
112 ); | |
113 -- End of differences | |
114 | |
115 | |
116 | |
117 CREATE TRIGGER AttachedFileDeleted | |
118 AFTER DELETE ON AttachedFiles | |
119 BEGIN | |
120 INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, | |
121 old.uncompressedSize, old.compressionType, | |
122 old.uncompressedHash, old.compressedHash); | |
123 END; | |
124 | |
125 | |
126 CREATE TRIGGER ResourceDeleted | |
127 AFTER DELETE ON Resources | |
128 BEGIN | |
129 INSERT INTO DeletedResources VALUES(old.resourceType, old.publicId); | |
130 END; | |
131 | |
132 | |
133 -- Delete a parent resource when its unique child gets deleted | |
134 CREATE TRIGGER ResourceDeletedParentCleaning | |
135 AFTER DELETE ON Resources | |
136 FOR EACH ROW WHEN NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) | |
137 BEGIN | |
138 DELETE FROM Resources WHERE internalId = old.parentId; | |
139 END; | |
140 | |
141 -- Signal that the deleted resource has a remaining parent, if the | |
142 -- deleted resource has a sibling resource | |
143 CREATE TRIGGER ResourceRemainingAncestorFound | |
144 AFTER DELETE ON Resources | |
145 FOR EACH ROW WHEN EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) | |
146 BEGIN | |
147 INSERT INTO RemainingAncestor(resourceType, publicId) | |
148 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; | |
149 END; | |
150 | |
151 | |
152 CREATE TRIGGER PatientAdded | |
153 AFTER INSERT ON Resources | |
154 FOR EACH ROW WHEN new.resourceType = 0 -- The "0" corresponds to "OrthancPluginResourceType_Patient" | |
155 BEGIN | |
156 INSERT INTO PatientRecyclingOrder VALUES (NULL, new.internalId); | |
157 END; |