orthanc-databases: PostgreSQL/Plugins/SQL/PrepareIndex.sql annotate

annotate PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 452:6780dd8b0a66 pg-transactions

fix latency measure for ODBC

author	Alain Mazy <am@osimis.io>
date	Thu, 18 Jan 2024 10:04:43 +0100
parents	da0586c5cbd8
children	11c6bcc9d1f2

rev	line source
448 f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	1 -- This SQL file creates a DB in version.revision 6.2 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,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	55 PRIMARY KEY(id, fileType)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	56 );
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 CREATE TABLE IF NOT EXISTS Changes(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	59 seq BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	60 changeType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	61 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	62 resourceType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	63 date VARCHAR(64)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	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 CREATE TABLE IF NOT EXISTS ExportedResources(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	67 seq BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	68 resourceType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	69 publicId VARCHAR(64),
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	70 remoteModality TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	71 patientId VARCHAR(64),
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	72 studyInstanceUid TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	73 seriesInstanceUid TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	74 sopInstanceUid TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	75 date VARCHAR(64)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	76 );
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 CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	79 seq BIGSERIAL NOT NULL PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	80 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	81 CONSTRAINT UniquePatientId UNIQUE (patientId)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	82 );
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 CREATE TABLE IF NOT EXISTS Labels(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	85 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	86 label TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	87 PRIMARY KEY(id, label)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	88 );
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 CREATE TABLE IF NOT EXISTS GlobalIntegers(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	91 key INTEGER PRIMARY KEY,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	92 value BIGINT);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	93 -- GlobalIntegers keys:
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	94 -- 0: CompressedSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	95 -- 1: UncompressedSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	96 -- 2: PatientsCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	97 -- 3: StudiesCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	98 -- 4: SeriesCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	99 -- 5: InstancesCount
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	100 -- 6: ChangeSeq
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	101 -- 7: PatientRecyclingOrderSeq
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	102
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	103 CREATE TABLE IF NOT EXISTS ServerProperties(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	104 server VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	105 property INTEGER, value TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	106 PRIMARY KEY(server, property)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	107 );
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 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	110 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	111 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	112 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	113
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	114 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	115 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	116 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	117 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	118
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	119 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	120 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	121 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	122
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	123 ------------------- Trigram index creation -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	124
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 -- 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	127 -- 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	128 -- 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	129 -- 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	130 -- 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	131
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	132 DO $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	133 begin
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	134 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	135 CREATE EXTENSION IF NOT EXISTS pg_trgm;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	136 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	137 ELSE
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	138 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	139 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	140 END $body$;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	141
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 ------------------- PatientAdded trigger & PatientRecyclingOrder -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	144 DROP TRIGGER IF EXISTS PatientAdded ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	145
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	146 CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	147 IN patient_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	148 IN is_update BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	149 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	150 RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	151 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	152 DECLARE
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	153 newSeq BIGINT;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	154 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	155 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	156 IF is_update > 0 THEN
443 2a48f8fcec6e cleanup Alain Mazy <am@osimis.io> parents: 438 diff changeset	157 -- 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	158 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	159 ELSE
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	160 INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	161 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	162 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	163 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	164 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	165
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	166 CREATE OR REPLACE FUNCTION PatientAddedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	167 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	168 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	169 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	170 IF new.resourceType = 0 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	171 PERFORM PatientAddedOrUpdated(new.internalId, 0);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	172 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	173 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	174 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	175 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	176
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	177 CREATE TRIGGER PatientAdded
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	178 AFTER INSERT ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	179 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	180 EXECUTE PROCEDURE PatientAddedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	181
443 2a48f8fcec6e cleanup Alain Mazy <am@osimis.io> parents: 438 diff changeset	182 -- initial population of PatientRecyclingOrderSeq
437 d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	183 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	184 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	185 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	186
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 ------------------- ResourceDeleted trigger -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	189 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	190
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	191 -- 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	192 -- ResourceDeleted + ResourceDeletedParentCleaning
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	193 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	194 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	195 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	196 -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	197 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	198
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	199 -- 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	200 DELETE FROM Resources WHERE internalId = old.parentId
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	201 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	202 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	203 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	204 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	205
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	206 CREATE TRIGGER ResourceDeleted
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	207 AFTER DELETE ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	208 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	209 EXECUTE PROCEDURE ResourceDeletedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	210
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 ------------------- DeleteResource function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	213
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	214 CREATE OR REPLACE FUNCTION DeleteResource(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	215 IN id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	216 OUT remaining_ancestor_resource_type INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	217 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	218
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	219 DECLARE
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	220 deleted_row RECORD;
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	221 locked_row RECORD;
437 d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	222
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	223 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	224
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	225 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	226
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	227 -- 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	228 -- these tables are used by the triggers
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	229 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	230 resourceType INTEGER NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	231 publicId VARCHAR(64) NOT NULL
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	232 );
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 RESET client_min_messages;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	235
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	236 -- 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	237 DELETE FROM DeletedResources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	238
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	239 -- create/clear the DeletedFiles temporary table
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	240 PERFORM CreateDeletedFilesTemporaryTable();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	241
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	242 -- 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	243 -- 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	244 -- 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	245 -- Locking only the immediate parent is sufficient to prevent from this.
2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	246 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	247
437 d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	248 -- delete the resource itself
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	249 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	250 -- 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	251
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	252 -- 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	253 -- (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	254 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	255 FROM Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	256 WHERE internalId = deleted_row.parentId
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	257 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	258
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	259 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	260
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	261 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	262
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	263 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	264 ) RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	265
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	266 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	267
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	268 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	269
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	270 -- 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	271 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	272 uuid VARCHAR(64) NOT NULL,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	273 fileType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	274 compressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	275 uncompressedSize BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	276 compressionType INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	277 uncompressedHash VARCHAR(40),
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	278 compressedHash VARCHAR(40)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	279 );
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	280
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	281 RESET client_min_messages;
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 -- 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	284 DELETE FROM DeletedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	285 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	286
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	287 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	288
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	289
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	290 CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	291 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	292 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	293 INSERT INTO DeletedFiles VALUES
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	294 (old.uuid, old.filetype, old.compressedSize,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	295 old.uncompressedSize, old.compressionType,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	296 old.uncompressedHash, old.compressedHash);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	297 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	298 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	299 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	300
449 da0586c5cbd8 fix Alain Mazy <am@osimis.io> parents: 448 diff changeset	301 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	302 CREATE TRIGGER AttachedFileDeleted
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	303 AFTER DELETE ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	304 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	305 EXECUTE PROCEDURE AttachedFileDeletedFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	306
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	307
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	308 ------------------- Fast Statistics -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	309
443 2a48f8fcec6e cleanup Alain Mazy <am@osimis.io> parents: 438 diff changeset	310 -- 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	311 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	312 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	313 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	314
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	315 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	316 SELECT 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	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 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	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 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	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 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	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 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	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
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	336 -- 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	337 -- 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	338 -- 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	339 -- 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	340 -- requests the statistics
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	341 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	342 key INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	343 value BIGINT);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	344
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	345 CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	346 IN statistics_key INTEGER,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	347 OUT new_value BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	348 ) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	349 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	350
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	351 -- 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	352 -- 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	353 WITH deleted_rows AS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	354 DELETE FROM GlobalIntegersChanges
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	355 WHERE GlobalIntegersChanges.key = statistics_key
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	356 RETURNING value
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	357 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	358 UPDATE GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	359 SET value = value + (
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	360 SELECT COALESCE(SUM(value), 0)
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	361 FROM deleted_rows
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	362 )
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	363 WHERE GlobalIntegers.key = statistics_key
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	364 RETURNING value INTO new_value;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	365
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	366 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	367 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	368
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	369
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	370 CREATE OR REPLACE FUNCTION UpdateStatistics(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	371 OUT patients_cunt BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	372 OUT studies_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	373 OUT series_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	374 OUT instances_count BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	375 OUT total_compressed_size BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	376 OUT total_uncompressed_size BIGINT
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	377 ) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	378 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	379
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	380 SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	381 SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	382 SELECT UpdateSingleStatistic(2) INTO patients_cunt;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	383 SELECT UpdateSingleStatistic(3) INTO studies_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	384 SELECT UpdateSingleStatistic(4) INTO series_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	385 SELECT UpdateSingleStatistic(5) INTO instances_count;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	386
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	387 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	388 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	389
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	390
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	391 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	392 RETURNS TRIGGER AS $$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	393 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	394 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	395 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	396 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	397 $$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	398
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	399 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	400 RETURNS TRIGGER AS $$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	401 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	402 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	403 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	404 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	405 $$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	406
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	407
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	408 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	409 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	410 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	411 INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	412 INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	413 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	414 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	415 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	416
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	417 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	418 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	419 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	420 INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	421 INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	422 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	423 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	424 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	425
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	426 DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	427 CREATE TRIGGER AttachedFileIncrementSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	428 AFTER INSERT ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	429 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	430 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	431
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	432 DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	433 CREATE TRIGGER AttachedFileDecrementSize
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	434 AFTER DELETE ON AttachedFiles
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	435 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	436 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	437
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	438 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	439 CREATE TRIGGER IncrementResourcesTracker
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	440 AFTER INSERT ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	441 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	442 EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	443
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	444 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	445 CREATE TRIGGER DecrementResourcesTracker
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	446 AFTER DELETE ON Resources
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	447 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	448 EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	449
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	450
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	451 ------------------- InsertOrUpdateMetadata function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	452 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	453 metadata_types INTEGER[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	454 metadata_values TEXT[],
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	455 revisions INTEGER[])
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	456 RETURNS VOID AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	457 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	458 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	459 -- 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	460 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	461 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	462 END LOOP;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	463
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	464 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	465 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	466
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	467
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	468 ------------------- GetLastChange function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	469 DROP TRIGGER IF EXISTS InsertedChange ON Changes;
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 -- insert the value if not already there
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	472 INSERT INTO GlobalIntegers
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	473 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	474 ON CONFLICT DO NOTHING;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	475
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	476 CREATE OR REPLACE FUNCTION InsertedChangeFunc()
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	477 RETURNS TRIGGER AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	478 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	479 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	480 RETURN NULL;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	481 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	482 $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	483
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	484 CREATE TRIGGER InsertedChange
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	485 AFTER INSERT ON Changes
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	486 FOR EACH ROW
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	487 EXECUTE PROCEDURE InsertedChangeFunc();
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	488
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	489
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	490 ------------------- CreateInstance function -------------------
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	491 CREATE OR REPLACE FUNCTION CreateInstance(
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	492 IN patient_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	493 IN study_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	494 IN series_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	495 IN instance_public_id TEXT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	496 OUT is_new_patient BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	497 OUT is_new_study BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	498 OUT is_new_series BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	499 OUT is_new_instance BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	500 OUT patient_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	501 OUT study_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	502 OUT series_internal_id BIGINT,
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	503 OUT instance_internal_id BIGINT) AS $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	504
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	505 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	506 is_new_patient := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	507 is_new_study := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	508 is_new_series := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	509 is_new_instance := 1;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	510
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	511 BEGIN
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	512 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	513 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	514 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	515 is_new_patient := 0;
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	516 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	517 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	518
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	519 BEGIN
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	520 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	521 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	522 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	523 is_new_study := 0;
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	524 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	525 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	526
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	527 BEGIN
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	528 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	529 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	530 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	531 is_new_series := 0;
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	532 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	533 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	534
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	535 BEGIN
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	536 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	537 EXCEPTION
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	538 WHEN unique_violation THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	539 is_new_instance := 0;
444 2ca939d02d39 cleanup Alain Mazy <am@osimis.io> parents: 443 diff changeset	540 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	541 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	542
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	543 IF is_new_instance > 0 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	544 -- 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	545 PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	546 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	547 END;
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	548
d979f25e60cf Re-organized DB creation/upgrade into standalone files Alain Mazy <am@osimis.io> parents: diff changeset	549 $body$ LANGUAGE plpgsql;
448 f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	550
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	551
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	552
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	553 -- 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	554 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	555 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	556 INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	557 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	558 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	559 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	560 INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources
f2427f94d879 added downgrade script + renames version Alain Mazy <am@osimis.io> parents: 444 diff changeset	561 INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex

Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 452:6780dd8b0a66 pg-transactions