Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/SQL/PrepareIndexV2.sql @ 437:d979f25e60cf pg-transactions
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 |
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; |