Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 569:f18e46d7dbf8 attach-custom-data

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged find-refactoring -> attach-custom-data
author Alain Mazy <am@orthanc.team>
date Tue, 24 Sep 2024 15:04:21 +0200
parents 11c6bcc9d1f2
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
470
11c6bcc9d1f2 cleanup
Alain Mazy <am@osimis.io>
parents: 449
diff changeset
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
2a48f8fcec6e cleanup
Alain Mazy <am@osimis.io>
parents: 438
diff changeset
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
2a48f8fcec6e cleanup
Alain Mazy <am@osimis.io>
parents: 438
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
243 -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
244 -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
245 -- that they are deleting the last instance and the parent resources would not be deleted.
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
246 -- Locking only the immediate parent is sufficient to prevent from this.
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
247 SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
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
2a48f8fcec6e cleanup
Alain Mazy <am@osimis.io>
parents: 438
diff changeset
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
f790232b77ef fix InsertOrUpdateMetadata
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
464 INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i])
f790232b77ef fix InsertOrUpdateMetadata
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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
2ca939d02d39 cleanup
Alain Mazy <am@osimis.io>
parents: 443
diff changeset
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;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
552
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
553 $body$ LANGUAGE plpgsql;
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
554
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
555
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
556
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
557 -- 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
558 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
559 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
560 INSERT INTO GlobalProperties VALUES (4, 3); -- GlobalProperty_DatabasePatchLevel
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
561 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
562 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
563 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
564 INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 444
diff changeset
565 INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex