Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 577:991b9b285e1a attach-custom-data tip
fix custom-data handling
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Wed, 02 Oct 2024 09:40:20 +0200 |
parents | b0533b703c2c |
children |
rev | line source |
---|---|
470 | 1 -- This SQL file creates a DB in Rev2 directly |
437
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. |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
4 -- Note to developers: |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
5 -- - it is and must stay idempotent. |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
6 -- - it is executed when the DB is "locked", only one Orthanc instance can execute it at a given time. |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
7 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
8 CREATE TABLE IF NOT EXISTS GlobalProperties( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
9 property INTEGER PRIMARY KEY, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
10 value TEXT |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
11 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
12 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
13 CREATE TABLE IF NOT EXISTS Resources( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
14 internalId BIGSERIAL NOT NULL PRIMARY KEY, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
15 resourceType INTEGER NOT NULL, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
16 publicId VARCHAR(64) NOT NULL, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
17 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
|
18 CONSTRAINT UniquePublicId UNIQUE (publicId) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
19 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
20 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
21 CREATE TABLE IF NOT EXISTS MainDicomTags( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
22 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
|
23 tagGroup INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
24 tagElement INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
25 value TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
26 PRIMARY KEY(id, tagGroup, tagElement) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
27 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
28 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
29 CREATE TABLE IF NOT EXISTS DicomIdentifiers( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
30 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
|
31 tagGroup INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
32 tagElement INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
33 value TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
34 PRIMARY KEY(id, tagGroup, tagElement) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
35 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
36 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
37 CREATE TABLE IF NOT EXISTS Metadata( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
38 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
|
39 type INTEGER NOT NULL, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
40 value TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
41 revision INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
42 PRIMARY KEY(id, type) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
43 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
44 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
45 CREATE TABLE IF NOT EXISTS AttachedFiles( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
46 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
|
47 fileType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
48 uuid VARCHAR(64) NOT NULL, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
49 compressedSize BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
50 uncompressedSize BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
51 compressionType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
52 uncompressedHash VARCHAR(40), |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
53 compressedHash VARCHAR(40), |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
54 revision INTEGER, |
569
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
55 customData TEXT, -- new in schema rev 3 |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
56 PRIMARY KEY(id, fileType) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
57 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
58 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
59 CREATE TABLE IF NOT EXISTS Changes( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
60 seq BIGSERIAL NOT NULL PRIMARY KEY, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
61 changeType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
62 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
|
63 resourceType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
64 date VARCHAR(64) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
65 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
66 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
67 CREATE TABLE IF NOT EXISTS ExportedResources( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
68 seq BIGSERIAL NOT NULL PRIMARY KEY, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
69 resourceType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
70 publicId VARCHAR(64), |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
71 remoteModality TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
72 patientId VARCHAR(64), |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
73 studyInstanceUid TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
74 seriesInstanceUid TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
75 sopInstanceUid TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
76 date VARCHAR(64) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
77 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
78 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
79 CREATE TABLE IF NOT EXISTS PatientRecyclingOrder( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
80 seq BIGSERIAL NOT NULL PRIMARY KEY, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
81 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
|
82 CONSTRAINT UniquePatientId UNIQUE (patientId) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
83 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
84 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
85 CREATE TABLE IF NOT EXISTS Labels( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
86 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
|
87 label TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
88 PRIMARY KEY(id, label) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
89 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
90 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
91 CREATE TABLE IF NOT EXISTS GlobalIntegers( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
92 key INTEGER PRIMARY KEY, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
93 value BIGINT); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
94 -- GlobalIntegers keys: |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
95 -- 0: CompressedSize |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
96 -- 1: UncompressedSize |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
97 -- 2: PatientsCount |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
98 -- 3: StudiesCount |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
99 -- 4: SeriesCount |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
100 -- 5: InstancesCount |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
101 -- 6: ChangeSeq |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
102 -- 7: PatientRecyclingOrderSeq |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
103 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
104 CREATE TABLE IF NOT EXISTS ServerProperties( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
105 server VARCHAR(64) NOT NULL, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
106 property INTEGER, value TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
107 PRIMARY KEY(server, property) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
108 ); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
109 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
110 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
|
111 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
|
112 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
|
113 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
|
114 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
115 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
|
116 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
|
117 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
|
118 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
|
119 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
120 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
|
121 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
|
122 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
|
123 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
124 ------------------- Trigram index creation ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
125 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
126 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
127 -- 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
|
128 -- 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
|
129 -- 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
|
130 -- 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
|
131 -- 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
|
132 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
133 DO $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
134 begin |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
135 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
|
136 CREATE EXTENSION IF NOT EXISTS pg_trgm; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
137 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
|
138 ELSE |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
139 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
|
140 END IF; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
141 END $body$; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
142 |
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 ------------------- PatientAdded trigger & PatientRecyclingOrder ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
145 DROP TRIGGER IF EXISTS PatientAdded ON Resources; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
146 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
147 CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
148 IN patient_id BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
149 IN is_update BIGINT |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
150 ) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
151 RETURNS VOID AS $body$ |
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 DECLARE |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
154 newSeq BIGINT; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
155 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
156 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
|
157 IF is_update > 0 THEN |
443 | 158 -- Note: Protected patients are not listed in this table ! So, they won't be updated |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
159 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
|
160 ELSE |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
161 INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
162 END IF; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
163 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
164 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
165 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
166 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
167 CREATE OR REPLACE FUNCTION PatientAddedFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
168 RETURNS TRIGGER AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
169 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
170 -- The "0" corresponds to "OrthancPluginResourceType_Patient" |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
171 IF new.resourceType = 0 THEN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
172 PERFORM PatientAddedOrUpdated(new.internalId, 0); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
173 END IF; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
174 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
175 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
176 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
177 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
178 CREATE TRIGGER PatientAdded |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
179 AFTER INSERT ON Resources |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
180 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
181 EXECUTE PROCEDURE PatientAddedFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
182 |
443 | 183 -- initial population of PatientRecyclingOrderSeq |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
184 INSERT INTO GlobalIntegers |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
185 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
|
186 ON CONFLICT DO NOTHING; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
187 |
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 ------------------- ResourceDeleted trigger ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
190 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
191 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
192 -- 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
|
193 -- ResourceDeleted + ResourceDeletedParentCleaning |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
194 CREATE OR REPLACE FUNCTION ResourceDeletedFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
195 RETURNS TRIGGER AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
196 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
197 -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
198 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
|
199 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
200 -- 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
|
201 DELETE FROM Resources WHERE internalId = old.parentId |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
202 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
|
203 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
204 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
205 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
206 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
207 CREATE TRIGGER ResourceDeleted |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
208 AFTER DELETE ON Resources |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
209 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
210 EXECUTE PROCEDURE ResourceDeletedFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
211 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
212 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
213 ------------------- DeleteResource function ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
214 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
215 CREATE OR REPLACE FUNCTION DeleteResource( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
216 IN id BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
217 OUT remaining_ancestor_resource_type INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
218 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
|
219 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
220 DECLARE |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
221 deleted_row RECORD; |
444 | 222 locked_row RECORD; |
437
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 |
444 | 243 -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that |
244 -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize | |
245 -- that they are deleting the last instance and the parent resources would not be deleted. | |
246 -- Locking only the immediate parent is sufficient to prevent from this. | |
247 SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; | |
248 | |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
249 -- delete the resource itself |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
250 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
|
251 -- 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
|
252 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
253 -- 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
|
254 -- (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
|
255 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
|
256 FROM Resources |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
257 WHERE internalId = deleted_row.parentId |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
258 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
|
259 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
260 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
261 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
262 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
263 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
264 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
265 ) RETURNS VOID AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
266 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
267 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
268 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
269 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
|
270 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
271 -- 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
|
272 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
273 uuid VARCHAR(64) NOT NULL, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
274 fileType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
275 compressedSize BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
276 uncompressedSize BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
277 compressionType INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
278 uncompressedHash VARCHAR(40), |
569
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
279 compressedHash VARCHAR(40), |
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
280 revision INTEGER, |
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
281 customData TEXT |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
282 ); |
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 RESET client_min_messages; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
285 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
286 -- 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
|
287 DELETE FROM DeletedFiles; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
288 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
289 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
290 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
291 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
292 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
293 CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
294 RETURNS TRIGGER AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
295 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
296 INSERT INTO DeletedFiles VALUES |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
297 (old.uuid, old.filetype, old.compressedSize, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
298 old.uncompressedSize, old.compressionType, |
569
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
299 old.uncompressedHash, old.compressedHash, |
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
300 old.revision, old.customData); |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
301 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
302 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
303 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
304 |
449 | 305 DROP TRIGGER IF EXISTS AttachedFileDeleted on AttachedFiles; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
306 CREATE TRIGGER AttachedFileDeleted |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
307 AFTER DELETE ON AttachedFiles |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
308 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
309 EXECUTE PROCEDURE AttachedFileDeletedFunc(); |
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 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
312 ------------------- Fast Statistics ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
313 |
443 | 314 -- initial population of GlobalIntegers if not already there |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
315 INSERT INTO GlobalIntegers |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
316 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
|
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 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
|
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 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
|
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 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
|
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 INSERT INTO GlobalIntegers |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
332 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
|
333 ON CONFLICT DO NOTHING; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
334 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
335 INSERT INTO GlobalIntegers |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
336 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
|
337 ON CONFLICT DO NOTHING; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
338 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
339 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
340 -- 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
|
341 -- 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
|
342 -- 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
|
343 -- 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
|
344 -- requests the statistics |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
345 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
346 key INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
347 value BIGINT); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
348 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
349 CREATE OR REPLACE FUNCTION UpdateSingleStatistic( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
350 IN statistics_key INTEGER, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
351 OUT new_value BIGINT |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
352 ) AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
353 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
354 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
355 -- 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
|
356 -- 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
|
357 WITH deleted_rows AS ( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
358 DELETE FROM GlobalIntegersChanges |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
359 WHERE GlobalIntegersChanges.key = statistics_key |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
360 RETURNING 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 UPDATE GlobalIntegers |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
363 SET value = value + ( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
364 SELECT COALESCE(SUM(value), 0) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
365 FROM deleted_rows |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
366 ) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
367 WHERE GlobalIntegers.key = statistics_key |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
368 RETURNING value INTO new_value; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
369 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
370 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
371 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
372 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
373 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
374 CREATE OR REPLACE FUNCTION UpdateStatistics( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
375 OUT patients_cunt BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
376 OUT studies_count BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
377 OUT series_count BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
378 OUT instances_count BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
379 OUT total_compressed_size BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
380 OUT total_uncompressed_size BIGINT |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
381 ) AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
382 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
383 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
384 SELECT UpdateSingleStatistic(0) INTO total_compressed_size; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
385 SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
386 SELECT UpdateSingleStatistic(2) INTO patients_cunt; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
387 SELECT UpdateSingleStatistic(3) INTO studies_count; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
388 SELECT UpdateSingleStatistic(4) INTO series_count; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
389 SELECT UpdateSingleStatistic(5) INTO instances_count; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
390 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
391 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
392 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
393 |
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 IncrementResourcesTrackerFunc() |
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(new.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 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
404 RETURNS TRIGGER AS $$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
405 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
406 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
|
407 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
408 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
409 $$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
410 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
411 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
412 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
413 RETURNS TRIGGER AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
414 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
415 INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
416 INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
417 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
418 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
419 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
420 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
421 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
422 RETURNS TRIGGER AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
423 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
424 INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
425 INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
426 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
427 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
428 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
429 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
430 DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
431 CREATE TRIGGER AttachedFileIncrementSize |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
432 AFTER INSERT ON AttachedFiles |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
433 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
434 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
435 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
436 DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
437 CREATE TRIGGER AttachedFileDecrementSize |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
438 AFTER DELETE ON AttachedFiles |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
439 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
440 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
441 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
442 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
443 CREATE TRIGGER IncrementResourcesTracker |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
444 AFTER INSERT ON Resources |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
445 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
446 EXECUTE PROCEDURE IncrementResourcesTrackerFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
447 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
448 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
449 CREATE TRIGGER DecrementResourcesTracker |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
450 AFTER DELETE ON Resources |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
451 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
452 EXECUTE PROCEDURE DecrementResourcesTrackerFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
453 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
454 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
455 ------------------- InsertOrUpdateMetadata function ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
456 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
|
457 metadata_types INTEGER[], |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
458 metadata_values TEXT[], |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
459 revisions INTEGER[]) |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
460 RETURNS VOID AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
461 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
462 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
|
463 -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i]; |
438 | 464 INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) |
465 ON CONFLICT (id, type) DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; | |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
466 END LOOP; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
467 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
468 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
469 $body$ LANGUAGE plpgsql; |
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 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
472 ------------------- GetLastChange function ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
473 DROP TRIGGER IF EXISTS InsertedChange ON Changes; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
474 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
475 -- insert the value if not already there |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
476 INSERT INTO GlobalIntegers |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
477 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
|
478 ON CONFLICT DO NOTHING; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
479 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
480 CREATE OR REPLACE FUNCTION InsertedChangeFunc() |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
481 RETURNS TRIGGER AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
482 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
483 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
|
484 RETURN NULL; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
485 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
486 $body$ LANGUAGE plpgsql; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
487 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
488 CREATE TRIGGER InsertedChange |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
489 AFTER INSERT ON Changes |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
490 FOR EACH ROW |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
491 EXECUTE PROCEDURE InsertedChangeFunc(); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
492 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
493 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
494 ------------------- CreateInstance function ------------------- |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
495 CREATE OR REPLACE FUNCTION CreateInstance( |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
496 IN patient_public_id TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
497 IN study_public_id TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
498 IN series_public_id TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
499 IN instance_public_id TEXT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
500 OUT is_new_patient BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
501 OUT is_new_study BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
502 OUT is_new_series BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
503 OUT is_new_instance BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
504 OUT patient_internal_id BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
505 OUT study_internal_id BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
506 OUT series_internal_id BIGINT, |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
507 OUT instance_internal_id BIGINT) AS $body$ |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
508 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
509 BEGIN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
510 is_new_patient := 1; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
511 is_new_study := 1; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
512 is_new_series := 1; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
513 is_new_instance := 1; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
514 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
515 BEGIN |
444 | 516 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
517 EXCEPTION |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
518 WHEN unique_violation THEN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
519 is_new_patient := 0; |
444 | 520 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
521 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
522 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
523 BEGIN |
444 | 524 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
525 EXCEPTION |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
526 WHEN unique_violation THEN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
527 is_new_study := 0; |
444 | 528 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
529 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
530 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
531 BEGIN |
444 | 532 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
533 EXCEPTION |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
534 WHEN unique_violation THEN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
535 is_new_series := 0; |
444 | 536 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
537 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
538 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
539 BEGIN |
444 | 540 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
541 EXCEPTION |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
542 WHEN unique_violation THEN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
543 is_new_instance := 0; |
444 | 544 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
545 END; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
546 |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
547 IF is_new_instance > 0 THEN |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
548 -- 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
|
549 PERFORM PatientAddedOrUpdated(patient_internal_id, 1); |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
550 END IF; |
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
551 END; |
572 | 552 $body$ LANGUAGE plpgsql; |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
553 |
572 | 554 -- function to compute a statistic in a ReadOnly transaction |
555 CREATE OR REPLACE FUNCTION ComputeStatisticsReadOnly( | |
556 IN statistics_key INTEGER, | |
557 OUT accumulated_value BIGINT | |
558 ) RETURNS BIGINT AS $body$ | |
559 | |
560 DECLARE | |
561 current_value BIGINT; | |
562 | |
563 BEGIN | |
564 | |
575 | 565 SELECT VALUE FROM GlobalIntegers |
572 | 566 INTO current_value |
567 WHERE key = statistics_key; | |
568 | |
575 | 569 SELECT COALESCE(SUM(value), 0) + current_value FROM GlobalIntegersChanges |
572 | 570 INTO accumulated_value |
571 WHERE key = statistics_key; | |
572 | |
573 END; | |
437
d979f25e60cf
Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
574 $body$ LANGUAGE plpgsql; |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
575 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
576 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
577 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
578 -- set the global properties that actually documents the DB version, revision and some of the capabilities |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
579 DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
580 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion |
569
f18e46d7dbf8
merged find-refactoring -> attach-custom-data
Alain Mazy <am@orthanc.team>
parents:
470
diff
changeset
|
581 INSERT INTO GlobalProperties VALUES (4, 3); -- GlobalProperty_DatabasePatchLevel |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
582 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
583 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
584 INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance -- this is actually the 3rd version of HasCreateInstance |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
585 INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
444
diff
changeset
|
586 INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex |
572 | 587 INSERT INTO GlobalProperties VALUES (14, 1); -- GlobalProperty_HasComputeStatisticsReadOnly |