Mercurial > hg > orthanc-databases

comparison PostgreSQL/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 cd9521e04249 a7f0f27fe33c
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 BIGSERIAL NOT NULL PRIMARY KEY,
8 resourceType INTEGER NOT NULL,
9 publicId VARCHAR(64) NOT NULL,
10 parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE
11 );
12
13 CREATE TABLE MainDicomTags(
14 id BIGINT 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 BIGINT 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 BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
31 type INTEGER NOT NULL,
32 value TEXT,
33 PRIMARY KEY(id, type)
34 );
35
36 CREATE TABLE AttachedFiles(
37 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
38 fileType INTEGER,
39 uuid VARCHAR(64) NOT NULL,
40 compressedSize BIGINT,
41 uncompressedSize BIGINT,
42 compressionType INTEGER,
43 uncompressedHash VARCHAR(40),
44 compressedHash VARCHAR(40),
45 PRIMARY KEY(id, fileType)
46 );
47
48 CREATE TABLE Changes(
49 seq BIGSERIAL NOT NULL PRIMARY KEY,
50 changeType INTEGER,
51 internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
52 resourceType INTEGER,
53 date VARCHAR(64)
54 );
55
56 CREATE TABLE ExportedResources(
57 seq BIGSERIAL NOT NULL PRIMARY KEY,
58 resourceType INTEGER,
59 publicId VARCHAR(64),
60 remoteModality TEXT,
61 patientId VARCHAR(64),
62 studyInstanceUid TEXT,
63 seriesInstanceUid TEXT,
64 sopInstanceUid TEXT,
65 date VARCHAR(64)
66 );
67
68 CREATE TABLE PatientRecyclingOrder(
69 seq BIGSERIAL NOT NULL PRIMARY KEY,
70 patientId BIGINT 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 MainDicomTagsIndex ON MainDicomTags(id);
79 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
80 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
81 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value);
82
83 CREATE INDEX ChangesIndex ON Changes(internalId);
84
85
86 -- New tables wrt. Orthanc core
87 CREATE TABLE DeletedFiles(
88 uuid VARCHAR(64) NOT NULL, -- 0
89 fileType INTEGER, -- 1
90 compressedSize BIGINT, -- 2
91 uncompressedSize BIGINT, -- 3
92 compressionType INTEGER, -- 4
93 uncompressedHash VARCHAR(40), -- 5
94 compressedHash VARCHAR(40) -- 6
95 );
96
97 CREATE TABLE RemainingAncestor(
98 resourceType INTEGER NOT NULL,
99 publicId VARCHAR(64) NOT NULL
100 );
101
102 CREATE TABLE DeletedResources(
103 resourceType INTEGER NOT NULL,
104 publicId VARCHAR(64) NOT NULL
105 );
106 -- End of differences
107
108
109 CREATE FUNCTION AttachedFileDeletedFunc()
110 RETURNS TRIGGER AS $body$
111 BEGIN
112 INSERT INTO DeletedFiles VALUES
113 (old.uuid, old.filetype, old.compressedSize,
114 old.uncompressedSize, old.compressionType,
115 old.uncompressedHash, old.compressedHash);
116 RETURN NULL;
117 END;
118 $body$ LANGUAGE plpgsql;
119
120 CREATE TRIGGER AttachedFileDeleted
121 AFTER DELETE ON AttachedFiles
122 FOR EACH ROW
123 EXECUTE PROCEDURE AttachedFileDeletedFunc();
124
125
126 -- The following trigger combines 2 triggers from SQLite:
127 -- ResourceDeleted + ResourceDeletedParentCleaning
128 CREATE FUNCTION ResourceDeletedFunc()
129 RETURNS TRIGGER AS $body$
130 BEGIN
131 --RAISE NOTICE 'Delete resource %', old.parentId;
132 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
133
134 -- http://stackoverflow.com/a/11299968/881731
135 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN
136 -- Signal that the deleted resource has a remaining parent
137 INSERT INTO RemainingAncestor
138 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
139 ELSE
140 -- Delete a parent resource when its unique child is deleted
141 DELETE FROM Resources WHERE internalId = old.parentId;
142 END IF;
143 RETURN NULL;
144 END;
145 $body$ LANGUAGE plpgsql;
146
147 CREATE TRIGGER ResourceDeleted
148 AFTER DELETE ON Resources
149 FOR EACH ROW
150 EXECUTE PROCEDURE ResourceDeletedFunc();
151
152
153
154 CREATE FUNCTION PatientAddedFunc()
155 RETURNS TRIGGER AS $body$
156 BEGIN
157 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
158 IF new.resourceType = 0 THEN
159 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId);
160 END IF;
161 RETURN NULL;
162 END;
163 $body$ LANGUAGE plpgsql;
164
165 CREATE TRIGGER PatientAdded
166 AFTER INSERT ON Resources
167 FOR EACH ROW
168 EXECUTE PROCEDURE PatientAddedFunc();