Mercurial > hg > orthanc-databases

comparison SQLite/Plugins/PrepareIndex.sql @ 0:7cea966b6829

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