Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/PrepareIndexV2.sql @ 444:2ca939d02d39 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cleanup
author Alain Mazy <am@osimis.io>
date Wed, 10 Jan 2024 15:22:40 +0100
parents 2a48f8fcec6e
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
1 -- This SQL file creates a DB in revision 2 directly (version is being used from v 6.0 of the PostgreSQL plugin)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
2 -- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
3 -- This script is self contained, it contains everything that needs to be run to create an Orthanc DB.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
4 -- Note: it is and must be idempotent.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
5
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
6 CREATE TABLE IF NOT EXISTS GlobalProperties(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
7 property INTEGER PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
8 value TEXT
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
9 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
10
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
11 CREATE TABLE IF NOT EXISTS Resources(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
12 internalId BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
13 resourceType INTEGER NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
14 publicId VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
15 parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
16 CONSTRAINT UniquePublicId UNIQUE (publicId)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
17 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
18
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
19 CREATE TABLE IF NOT EXISTS MainDicomTags(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
20 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
21 tagGroup INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
22 tagElement INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
23 value TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
24 PRIMARY KEY(id, tagGroup, tagElement)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
25 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
26
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
27 CREATE TABLE IF NOT EXISTS DicomIdentifiers(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
28 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
29 tagGroup INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
30 tagElement INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
31 value TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
32 PRIMARY KEY(id, tagGroup, tagElement)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
33 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
34
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
35 CREATE TABLE IF NOT EXISTS Metadata(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
36 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
37 type INTEGER NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
38 value TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
39 revision INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
40 PRIMARY KEY(id, type)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
41 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
42
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
43 CREATE TABLE IF NOT EXISTS AttachedFiles(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
44 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
45 fileType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
46 uuid VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
47 compressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
48 uncompressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
49 compressionType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
50 uncompressedHash VARCHAR(40),
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
51 compressedHash VARCHAR(40),
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
52 revision INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
53 PRIMARY KEY(id, fileType)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
54 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
55
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
56 CREATE TABLE IF NOT EXISTS Changes(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
57 seq BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
58 changeType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
59 internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
60 resourceType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
61 date VARCHAR(64)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
62 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
63
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
64 CREATE TABLE IF NOT EXISTS ExportedResources(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
65 seq BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
66 resourceType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
67 publicId VARCHAR(64),
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
68 remoteModality TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
69 patientId VARCHAR(64),
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
70 studyInstanceUid TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
71 seriesInstanceUid TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
72 sopInstanceUid TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
73 date VARCHAR(64)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
74 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
75
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
76 CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
77 seq BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
78 patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
79 CONSTRAINT UniquePatientId UNIQUE (patientId)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
80 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
81
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
82 CREATE TABLE IF NOT EXISTS Labels(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
83 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
84 label TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
85 PRIMARY KEY(id, label)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
86 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
87
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
88 CREATE TABLE IF NOT EXISTS GlobalIntegers(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
89 key INTEGER PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
90 value BIGINT);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
91 -- GlobalIntegers keys:
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
92 -- 0: CompressedSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
93 -- 1: UncompressedSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
94 -- 2: PatientsCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
95 -- 3: StudiesCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
96 -- 4: SeriesCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
97 -- 5: InstancesCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
98 -- 6: ChangeSeq
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
99 -- 7: PatientRecyclingOrderSeq
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
100
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
101 CREATE TABLE IF NOT EXISTS ServerProperties(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
102 server VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
103 property INTEGER, value TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
104 PRIMARY KEY(server, property)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
105 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
106
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
107 CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
108 CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
109 CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
110 CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
111
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
112 CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
113 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
114 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
115 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
116
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
117 CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
118 CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
119 CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
120
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
121 ------------------- Trigram index creation -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
122
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
123
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
124 -- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
125 -- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
126 -- performance for "strict" searches (i.e. searches involving no wildcard).
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
127 -- https://www.postgresql.org/docs/current/static/pgtrgm.html
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
128 -- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
129
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
130 DO $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
131 begin
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
132 IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
133 CREATE EXTENSION IF NOT EXISTS pg_trgm;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
134 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
135 ELSE
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
136 RAISE NOTICE 'pg_trgm extension is not available on you system';
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
137 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
138 END $body$;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
139
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
140
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
141 ------------------- PatientAdded trigger & PatientRecyclingOrder -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
142 DROP TRIGGER IF EXISTS PatientAdded ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
143
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
144 CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
145 IN patient_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
146 IN is_update BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
147 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
148 RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
149 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
150 DECLARE
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
151 newSeq BIGINT;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
152 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
153 UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
154 IF is_update > 0 THEN
443
2a48f8fcec6e cleanup
Alain Mazy <am@osimis.io>
parents: 438
diff changeset
155 -- Note: Protected patients are not listed in this table ! So, they won't be updated
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
156 UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
157 ELSE
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
158 INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
159 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
160 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
161 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
162 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
163
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
164 CREATE OR REPLACE FUNCTION PatientAddedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
165 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
166 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
167 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
168 IF new.resourceType = 0 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
169 PERFORM PatientAddedOrUpdated(new.internalId, 0);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
170 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
171 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
172 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
173 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
174
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
175 CREATE TRIGGER PatientAdded
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
176 AFTER INSERT ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
177 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
178 EXECUTE PROCEDURE PatientAddedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
179
443
2a48f8fcec6e cleanup
Alain Mazy <am@osimis.io>
parents: 438
diff changeset
180 -- initial population of PatientRecyclingOrderSeq
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
181 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
182 SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
183 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
184
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
185
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
186 ------------------- ResourceDeleted trigger -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
187 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
188
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
189 -- The following trigger combines 2 triggers from SQLite:
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
190 -- ResourceDeleted + ResourceDeletedParentCleaning
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
191 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
192 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
193 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
194 -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
195 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
196
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
197 -- If this resource is the latest child, delete the parent
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
198 DELETE FROM Resources WHERE internalId = old.parentId
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
199 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
200 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
201 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
202 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
203
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
204 CREATE TRIGGER ResourceDeleted
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
205 AFTER DELETE ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
206 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
207 EXECUTE PROCEDURE ResourceDeletedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
208
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
209
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
210 ------------------- DeleteResource function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
211
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
212 CREATE OR REPLACE FUNCTION DeleteResource(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
213 IN id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
214 OUT remaining_ancestor_resource_type INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
215 OUT remaining_anncestor_public_id TEXT) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
216
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
217 DECLARE
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
218 deleted_row RECORD;
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
219 locked_row RECORD;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
220
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
221 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
222
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
223 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
224
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
225 -- note: temporary tables are created at session (connection) level -> they are likely to exist
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
226 -- these tables are used by the triggers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
227 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
228 resourceType INTEGER NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
229 publicId VARCHAR(64) NOT NULL
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
230 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
231
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
232 RESET client_min_messages;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
233
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
234 -- clear the temporary table in case it has been created earlier in the session
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
235 DELETE FROM DeletedResources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
236
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
237 -- create/clear the DeletedFiles temporary table
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
238 PERFORM CreateDeletedFilesTemporaryTable();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
239
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
240 -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
241 -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
242 -- that they are deleting the last instance and the parent resources would not be deleted.
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
243 -- Locking only the immediate parent is sufficient to prevent from this.
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
244 SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
245
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
246 -- delete the resource itself
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
247 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
248 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children +
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
249
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
250 -- If this resource still has siblings, keep track of the remaining parent
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
251 -- (a parent that must not be deleted but whose LastUpdate must be updated)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
252 SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
253 FROM Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
254 WHERE internalId = deleted_row.parentId
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
255 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
256
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
257 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
258
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
259 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
260
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
261 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
262 ) RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
263
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
264 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
265
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
266 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
267
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
268 -- note: temporary tables are created at session (connection) level -> they are likely to exist
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
269 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
270 uuid VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
271 fileType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
272 compressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
273 uncompressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
274 compressionType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
275 uncompressedHash VARCHAR(40),
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
276 compressedHash VARCHAR(40)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
277 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
278
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
279 RESET client_min_messages;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
280
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
281 -- clear the temporary table in case it has been created earlier in the session
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
282 DELETE FROM DeletedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
283 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
284
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
285 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
286
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
287
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
288 DROP TRIGGER IF EXISTS AttachedFileDeleted ON AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
289
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
290 CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
291 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
292 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
293 INSERT INTO DeletedFiles VALUES
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
294 (old.uuid, old.filetype, old.compressedSize,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
295 old.uncompressedSize, old.compressionType,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
296 old.uncompressedHash, old.compressedHash);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
297 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
298 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
299 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
300
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
301 CREATE TRIGGER AttachedFileDeleted
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
302 AFTER DELETE ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
303 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
304 EXECUTE PROCEDURE AttachedFileDeletedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
305
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
306
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
307 ------------------- Fast Statistics -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
308
443
2a48f8fcec6e cleanup
Alain Mazy <am@osimis.io>
parents: 438
diff changeset
309 -- initial population of GlobalIntegers if not already there
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
310 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
311 SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
312 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
313
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
314 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
315 SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
316 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
317
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
318 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
319 SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0 -- Count patients
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
320 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
321
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
322 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
323 SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1 -- Count studies
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
324 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
325
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
326 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
327 SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2 -- Count series
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
328 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
329
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
330 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
331 SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3 -- Count instances
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
332 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
333
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
334
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
335 -- this table stores all changes that needs to be performed to the GlobalIntegers table
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
336 -- This way, each transaction can add row independently in this table without having to lock
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
337 -- any row (which was the case with previous FastTotalSize).
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
338 -- These changes will be applied at regular interval by an external thread or when someone
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
339 -- requests the statistics
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
340 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
341 key INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
342 value BIGINT);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
343
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
344 CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
345 IN statistics_key INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
346 OUT new_value BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
347 ) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
348 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
349
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
350 -- Delete the current changes, sum them and update the GlobalIntegers row.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
351 -- New rows can be added in the meantime, they won't be deleted or summed.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
352 WITH deleted_rows AS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
353 DELETE FROM GlobalIntegersChanges
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
354 WHERE GlobalIntegersChanges.key = statistics_key
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
355 RETURNING value
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
356 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
357 UPDATE GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
358 SET value = value + (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
359 SELECT COALESCE(SUM(value), 0)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
360 FROM deleted_rows
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
361 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
362 WHERE GlobalIntegers.key = statistics_key
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
363 RETURNING value INTO new_value;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
364
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
365 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
366 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
367
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
368
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
369 CREATE OR REPLACE FUNCTION UpdateStatistics(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
370 OUT patients_cunt BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
371 OUT studies_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
372 OUT series_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
373 OUT instances_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
374 OUT total_compressed_size BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
375 OUT total_uncompressed_size BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
376 ) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
377 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
378
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
379 SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
380 SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
381 SELECT UpdateSingleStatistic(2) INTO patients_cunt;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
382 SELECT UpdateSingleStatistic(3) INTO studies_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
383 SELECT UpdateSingleStatistic(4) INTO series_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
384 SELECT UpdateSingleStatistic(5) INTO instances_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
385
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
386 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
387 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
388
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
389
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
390 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
391 RETURNS TRIGGER AS $$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
392 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
393 INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
394 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
395 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
396 $$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
397
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
398 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
399 RETURNS TRIGGER AS $$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
400 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
401 INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
402 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
403 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
404 $$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
405
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
406
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
407 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
408 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
409 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
410 INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
411 INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
412 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
413 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
414 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
415
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
416 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
417 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
418 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
419 INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
420 INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
421 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
422 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
423 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
424
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
425 DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
426 CREATE TRIGGER AttachedFileIncrementSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
427 AFTER INSERT ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
428 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
429 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
430
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
431 DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
432 CREATE TRIGGER AttachedFileDecrementSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
433 AFTER DELETE ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
434 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
435 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
436
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
437 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
438 CREATE TRIGGER IncrementResourcesTracker
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
439 AFTER INSERT ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
440 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
441 EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
442
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
443 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
444 CREATE TRIGGER DecrementResourcesTracker
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
445 AFTER DELETE ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
446 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
447 EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
448
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
449
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
450 ------------------- InsertOrUpdateMetadata function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
451 CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
452 metadata_types INTEGER[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
453 metadata_values TEXT[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
454 revisions INTEGER[])
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
455 RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
456 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
457 FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
458 -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i];
438
f790232b77ef fix InsertOrUpdateMetadata
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
459 INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i])
f790232b77ef fix InsertOrUpdateMetadata
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
460 ON CONFLICT (id, type) DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
461 END LOOP;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
462
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
463 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
464 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
465
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
466
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
467 ------------------- GetLastChange function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
468 DROP TRIGGER IF EXISTS InsertedChange ON Changes;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
469
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
470 -- insert the value if not already there
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
471 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
472 SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
473 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
474
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
475 CREATE OR REPLACE FUNCTION InsertedChangeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
476 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
477 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
478 UPDATE GlobalIntegers SET value = new.seq WHERE key = 6;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
479 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
480 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
481 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
482
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
483 CREATE TRIGGER InsertedChange
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
484 AFTER INSERT ON Changes
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
485 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
486 EXECUTE PROCEDURE InsertedChangeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
487
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
488
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
489 ------------------- CreateInstance function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
490 CREATE OR REPLACE FUNCTION CreateInstance(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
491 IN patient_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
492 IN study_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
493 IN series_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
494 IN instance_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
495 OUT is_new_patient BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
496 OUT is_new_study BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
497 OUT is_new_series BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
498 OUT is_new_instance BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
499 OUT patient_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
500 OUT study_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
501 OUT series_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
502 OUT instance_internal_id BIGINT) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
503
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
504 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
505 is_new_patient := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
506 is_new_study := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
507 is_new_series := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
508 is_new_instance := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
509
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
510 BEGIN
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
511 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
512 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
513 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
514 is_new_patient := 0;
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
515 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
516 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
517
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
518 BEGIN
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
519 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
520 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
521 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
522 is_new_study := 0;
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
523 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
524 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
525
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
526 BEGIN
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
527 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
528 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
529 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
530 is_new_series := 0;
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
531 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
532 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
533
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
534 BEGIN
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
535 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id;
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
536 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
537 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
538 is_new_instance := 0;
444
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
539 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
540 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
541
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
542 IF is_new_instance > 0 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
543 -- Move the patient to the end of the recycling order.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
544 PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
545 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
546 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
547
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
548 $body$ LANGUAGE plpgsql;