Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/PrepareIndexV2.sql @ 437:d979f25e60cf pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Re-organized DB creation/upgrade into standalone files
author Alain Mazy <am@osimis.io>
date Mon, 18 Dec 2023 18:50:01 +0100
parents
children f790232b77ef
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
155 -- Note: Protected patient are not listed in this table ! So, they won't be updated
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 -- ON CONFLICT (patientId) DO UPDATE SET seq = newSeq;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
160 END IF;
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 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
163 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
164
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
165 CREATE OR REPLACE FUNCTION PatientAddedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
166 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
167 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
168 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
169 IF new.resourceType = 0 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
170 PERFORM PatientAddedOrUpdated(new.internalId, 0);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
171 -- UPDATE GlobalIntegers WHERE key = 7 SET value = value + 1 RETURNING value
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
172 -- INSERT INTO PatientRecyclingOrder VALUES ((SELECT value FROM GlobalIntegers WHERE key = 7), new.internalId)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
173 -- ON CONFLICT ;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
174 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
175 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
176 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
177 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
178
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
179 CREATE TRIGGER PatientAdded
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
180 AFTER INSERT ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
181 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
182 EXECUTE PROCEDURE PatientAddedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
183
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
184 -- initial value for PatientRecyclingOrderSeq
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
185 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
186 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
187 ON CONFLICT DO NOTHING;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
190 ------------------- ResourceDeleted trigger -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
191 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
192
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
193 -- 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
194 -- ResourceDeleted + ResourceDeletedParentCleaning
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
195 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
196 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
197 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
198 -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
199 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
200
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
201 -- 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
202 DELETE FROM Resources WHERE internalId = old.parentId
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
203 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
204 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
205 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
206 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
207
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
208 CREATE TRIGGER ResourceDeleted
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
209 AFTER DELETE ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
210 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
211 EXECUTE PROCEDURE ResourceDeletedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
212
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
213
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
214 ------------------- DeleteResource function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
215
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
216 CREATE OR REPLACE FUNCTION DeleteResource(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
217 IN id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
218 OUT remaining_ancestor_resource_type INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
219 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
220
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
221 DECLARE
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
222 deleted_row RECORD;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
223
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
224 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
225
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
226 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
227
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
228 -- 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
229 -- these tables are used by the triggers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
230 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
231 resourceType INTEGER NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
232 publicId VARCHAR(64) NOT NULL
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
235 RESET client_min_messages;
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 -- 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
238 DELETE FROM DeletedResources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
239
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
240 -- create/clear the DeletedFiles temporary table
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
241 PERFORM CreateDeletedFilesTemporaryTable();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
242
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
243 -- delete the resource itself
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
244 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
245 -- 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
246
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
247 -- 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
248 -- (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
249 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
250 FROM Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
251 WHERE internalId = deleted_row.parentId
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
252 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
253
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
254 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
255
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
256 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
257
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
258 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
259 ) RETURNS VOID AS $body$
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 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
262
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
263 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
264
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
265 -- 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
266 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
267 uuid VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
268 fileType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
269 compressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
270 uncompressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
271 compressionType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
272 uncompressedHash VARCHAR(40),
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
273 compressedHash VARCHAR(40)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
274 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
275
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
276 RESET client_min_messages;
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 -- 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
279 DELETE FROM DeletedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
280 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
281
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
282 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
283
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 DROP TRIGGER IF EXISTS AttachedFileDeleted ON AttachedFiles;
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 CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
288 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
289 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
290 INSERT INTO DeletedFiles VALUES
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
291 (old.uuid, old.filetype, old.compressedSize,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
292 old.uncompressedSize, old.compressionType,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
293 old.uncompressedHash, old.compressedHash);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
294 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
295 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
296 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
297
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
298 CREATE TRIGGER AttachedFileDeleted
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
299 AFTER DELETE ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
300 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
301 EXECUTE PROCEDURE AttachedFileDeletedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
302
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
303
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
304 ------------------- Fast Statistics -------------------
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 -- initialize values if not already theere
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
307 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
308 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
309 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
310
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
311 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
312 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
313 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
314
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
315 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
316 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
317 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
318
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
319 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
320 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
321 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
322
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
323 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
324 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
325 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
326
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
327 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
328 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
329 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
330
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
331
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
332 -- 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
333 -- 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
334 -- 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
335 -- 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
336 -- requests the statistics
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
337 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
338 key INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
339 value BIGINT);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
340
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
341 CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
342 IN statistics_key INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
343 OUT new_value BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
344 ) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
345 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
346
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
347 -- 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
348 -- 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
349 WITH deleted_rows AS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
350 DELETE FROM GlobalIntegersChanges
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
351 WHERE GlobalIntegersChanges.key = statistics_key
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
352 RETURNING value
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
353 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
354 UPDATE GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
355 SET value = value + (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
356 SELECT COALESCE(SUM(value), 0)
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
357 FROM deleted_rows
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
358 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
359 WHERE GlobalIntegers.key = statistics_key
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
360 RETURNING value INTO new_value;
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 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
363 $body$ LANGUAGE plpgsql;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
366 CREATE OR REPLACE FUNCTION UpdateStatistics(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
367 OUT patients_cunt BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
368 OUT studies_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
369 OUT series_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
370 OUT instances_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
371 OUT total_compressed_size BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
372 OUT total_uncompressed_size BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
373 ) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
374 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
375
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
376 SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
377 SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
378 SELECT UpdateSingleStatistic(2) INTO patients_cunt;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
379 SELECT UpdateSingleStatistic(3) INTO studies_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
380 SELECT UpdateSingleStatistic(4) INTO series_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
381 SELECT UpdateSingleStatistic(5) INTO instances_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
382
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
383 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
384 $body$ LANGUAGE plpgsql;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
387 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
388 RETURNS TRIGGER AS $$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
389 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
390 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
391 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
392 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
393 $$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
394
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
395 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
396 RETURNS TRIGGER AS $$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
397 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
398 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
399 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
400 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
401 $$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
402
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
403
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
404 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
405 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
406 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
407 INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
408 INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
409 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
410 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
411 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
412
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
413 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
414 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
415 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
416 INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
417 INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
418 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
419 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
420 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
421
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
422 DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
423 CREATE TRIGGER AttachedFileIncrementSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
424 AFTER INSERT ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
425 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
426 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
427
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
428 DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
429 CREATE TRIGGER AttachedFileDecrementSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
430 AFTER DELETE ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
431 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
432 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
433
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
434 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
435 CREATE TRIGGER IncrementResourcesTracker
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
436 AFTER INSERT ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
437 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
438 EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
439
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
440 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
441 CREATE TRIGGER DecrementResourcesTracker
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
442 AFTER DELETE ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
443 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
444 EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
445
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
446
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
447 ------------------- InsertOrUpdateMetadata function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
448 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
449 metadata_types INTEGER[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
450 metadata_values TEXT[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
451 revisions INTEGER[])
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
452 RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
453 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
454 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
455 -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i];
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
456 INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
457 END LOOP;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
458
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
459 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
460 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
461
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 ------------------- GetLastChange function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
464 DROP TRIGGER IF EXISTS InsertedChange ON Changes;
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 -- insert the value if not already there
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
467 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
468 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
469 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
470
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
471 CREATE OR REPLACE FUNCTION InsertedChangeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
472 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
473 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
474 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
475 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
476 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
477 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
478
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
479 CREATE TRIGGER InsertedChange
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
480 AFTER INSERT ON Changes
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
481 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
482 EXECUTE PROCEDURE InsertedChangeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
483
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
484
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
485 ------------------- CreateInstance function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
486 CREATE OR REPLACE FUNCTION CreateInstance(
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
487 IN patient_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
488 IN study_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
489 IN series_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
490 IN instance_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
491 OUT is_new_patient BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
492 OUT is_new_study BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
493 OUT is_new_series BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
494 OUT is_new_instance BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
495 OUT patient_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
496 OUT study_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
497 OUT series_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
498 OUT instance_internal_id BIGINT) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
499
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
500 DECLARE
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
501 patientSeq BIGINT;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
502 countRecycling BIGINT;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
511 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL);
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;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
515 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
516 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
519 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id);
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;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
523 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
524 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
527 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id);
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;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
531 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
532 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2;
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
535 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id);
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;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
539 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
540 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3;
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;