Mercurial > hg > orthanc-databases
diff PostgreSQL/Plugins/SQL/PrepareIndexV2.sql @ 437:d979f25e60cf pg-transactions
Re-organized DB creation/upgrade into standalone files
author | Alain Mazy <am@osimis.io> |
---|---|
date | Mon, 18 Dec 2023 18:50:01 +0100 |
parents | |
children | f790232b77ef |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/PrepareIndexV2.sql Mon Dec 18 18:50:01 2023 +0100 @@ -0,0 +1,548 @@ +-- This SQL file creates a DB in revision 2 directly (version is being used from v 6.0 of the PostgreSQL plugin) +-- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions. +-- This script is self contained, it contains everything that needs to be run to create an Orthanc DB. +-- Note: it is and must be idempotent. + +CREATE TABLE IF NOT EXISTS GlobalProperties( + property INTEGER PRIMARY KEY, + value TEXT + ); + +CREATE TABLE IF NOT EXISTS Resources( + internalId BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL, + parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePublicId UNIQUE (publicId) + ); + +CREATE TABLE IF NOT EXISTS MainDicomTags( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE IF NOT EXISTS DicomIdentifiers( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE IF NOT EXISTS Metadata( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + type INTEGER NOT NULL, + value TEXT, + revision INTEGER, + PRIMARY KEY(id, type) + ); + +CREATE TABLE IF NOT EXISTS AttachedFiles( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + fileType INTEGER, + uuid VARCHAR(64) NOT NULL, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40), + revision INTEGER, + PRIMARY KEY(id, fileType) + ); + +CREATE TABLE IF NOT EXISTS Changes( + seq BIGSERIAL NOT NULL PRIMARY KEY, + changeType INTEGER, + internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + resourceType INTEGER, + date VARCHAR(64) + ); + +CREATE TABLE IF NOT EXISTS ExportedResources( + seq BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER, + publicId VARCHAR(64), + remoteModality TEXT, + patientId VARCHAR(64), + studyInstanceUid TEXT, + seriesInstanceUid TEXT, + sopInstanceUid TEXT, + date VARCHAR(64) + ); + +CREATE TABLE IF NOT EXISTS PatientRecyclingOrder( + seq BIGSERIAL NOT NULL PRIMARY KEY, + patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePatientId UNIQUE (patientId) + ); + +CREATE TABLE IF NOT EXISTS Labels( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + label TEXT, + PRIMARY KEY(id, label) + ); + +CREATE TABLE IF NOT EXISTS GlobalIntegers( + key INTEGER PRIMARY KEY, + value BIGINT); +-- GlobalIntegers keys: +-- 0: CompressedSize +-- 1: UncompressedSize +-- 2: PatientsCount +-- 3: StudiesCount +-- 4: SeriesCount +-- 5: InstancesCount +-- 6: ChangeSeq +-- 7: PatientRecyclingOrderSeq + +CREATE TABLE IF NOT EXISTS ServerProperties( + server VARCHAR(64) NOT NULL, + property INTEGER, value TEXT, + PRIMARY KEY(server, property) + ); + +CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId); +CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId); +CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType); +CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId); + +CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value); + +CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId); +CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id); +CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label); + +------------------- Trigram index creation ------------------- + + +-- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested +-- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better +-- performance for "strict" searches (i.e. searches involving no wildcard). +-- https://www.postgresql.org/docs/current/static/pgtrgm.html +-- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47 + +DO $body$ +begin + IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN + CREATE EXTENSION IF NOT EXISTS pg_trgm; + CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops); + ELSE + RAISE NOTICE 'pg_trgm extension is not available on you system'; + END IF; +END $body$; + + +------------------- PatientAdded trigger & PatientRecyclingOrder ------------------- +DROP TRIGGER IF EXISTS PatientAdded ON Resources; + +CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( + IN patient_id BIGINT, + IN is_update BIGINT + ) +RETURNS VOID AS $body$ +BEGIN + DECLARE + newSeq BIGINT; + BEGIN + UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq; + IF is_update > 0 THEN + -- Note: Protected patient are not listed in this table ! So, they won't be updated + UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id; + ELSE + INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id); + -- ON CONFLICT (patientId) DO UPDATE SET seq = newSeq; + END IF; + END; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION PatientAddedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- The "0" corresponds to "OrthancPluginResourceType_Patient" + IF new.resourceType = 0 THEN + PERFORM PatientAddedOrUpdated(new.internalId, 0); + -- UPDATE GlobalIntegers WHERE key = 7 SET value = value + 1 RETURNING value + -- INSERT INTO PatientRecyclingOrder VALUES ((SELECT value FROM GlobalIntegers WHERE key = 7), new.internalId) + -- ON CONFLICT ; + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER PatientAdded +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE PatientAddedFunc(); + +-- initial value for PatientRecyclingOrderSeq +INSERT INTO GlobalIntegers + SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder + ON CONFLICT DO NOTHING; + + +------------------- ResourceDeleted trigger ------------------- +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; + +-- The following trigger combines 2 triggers from SQLite: +-- ResourceDeleted + ResourceDeletedParentCleaning +CREATE OR REPLACE FUNCTION ResourceDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId; + INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); + + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = old.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE ResourceDeletedFunc(); + + +------------------- DeleteResource function ------------------- + +CREATE OR REPLACE FUNCTION DeleteResource( + IN id BIGINT, + OUT remaining_ancestor_resource_type INTEGER, + OUT remaining_anncestor_public_id TEXT) AS $body$ + +DECLARE + deleted_row RECORD; + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + -- these tables are used by the triggers + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedResources; + + -- create/clear the DeletedFiles temporary table + PERFORM CreateDeletedFilesTemporaryTable(); + + -- delete the resource itself + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; + -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + + + -- If this resource still has siblings, keep track of the remaining parent + -- (a parent that must not be deleted but whose LastUpdate must be updated) + SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id + FROM Resources + WHERE internalId = deleted_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); + +END; + +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable( +) RETURNS VOID AS $body$ + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles( + uuid VARCHAR(64) NOT NULL, + fileType INTEGER, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40) + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedFiles; +END; + +$body$ LANGUAGE plpgsql; + + +DROP TRIGGER IF EXISTS AttachedFileDeleted ON AttachedFiles; + +CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO DeletedFiles VALUES + (old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDeletedFunc(); + + +------------------- Fast Statistics ------------------- + +-- initialize values if not already theere +INSERT INTO GlobalIntegers + SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0 -- Count patients + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1 -- Count studies + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2 -- Count series + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3 -- Count instances + ON CONFLICT DO NOTHING; + + +-- this table stores all changes that needs to be performed to the GlobalIntegers table +-- This way, each transaction can add row independently in this table without having to lock +-- any row (which was the case with previous FastTotalSize). +-- These changes will be applied at regular interval by an external thread or when someone +-- requests the statistics +CREATE TABLE IF NOT EXISTS GlobalIntegersChanges( + key INTEGER, + value BIGINT); + +CREATE OR REPLACE FUNCTION UpdateSingleStatistic( + IN statistics_key INTEGER, + OUT new_value BIGINT +) AS $body$ +BEGIN + + -- Delete the current changes, sum them and update the GlobalIntegers row. + -- New rows can be added in the meantime, they won't be deleted or summed. + WITH deleted_rows AS ( + DELETE FROM GlobalIntegersChanges + WHERE GlobalIntegersChanges.key = statistics_key + RETURNING value + ) + UPDATE GlobalIntegers + SET value = value + ( + SELECT COALESCE(SUM(value), 0) + FROM deleted_rows + ) + WHERE GlobalIntegers.key = statistics_key + RETURNING value INTO new_value; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION UpdateStatistics( + OUT patients_cunt BIGINT, + OUT studies_count BIGINT, + OUT series_count BIGINT, + OUT instances_count BIGINT, + OUT total_compressed_size BIGINT, + OUT total_uncompressed_size BIGINT +) AS $body$ +BEGIN + + SELECT UpdateSingleStatistic(0) INTO total_compressed_size; + SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size; + SELECT UpdateSingleStatistic(2) INTO patients_cunt; + SELECT UpdateSingleStatistic(3) INTO studies_count; + SELECT UpdateSingleStatistic(4) INTO series_count; + SELECT UpdateSingleStatistic(5) INTO instances_count; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize); + INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize); + INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles; +CREATE TRIGGER AttachedFileIncrementSize +AFTER INSERT ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); + +DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles; +CREATE TRIGGER AttachedFileDecrementSize +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); + +DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources; +CREATE TRIGGER IncrementResourcesTracker +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE IncrementResourcesTrackerFunc(); + +DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources; +CREATE TRIGGER DecrementResourcesTracker +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE DecrementResourcesTrackerFunc(); + + +------------------- InsertOrUpdateMetadata function ------------------- +CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[], + metadata_types INTEGER[], + metadata_values TEXT[], + revisions INTEGER[]) +RETURNS VOID AS $body$ +BEGIN + FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP + -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i]; + INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) ON CONFLICT DO NOTHING; + END LOOP; + +END; +$body$ LANGUAGE plpgsql; + + +------------------- GetLastChange function ------------------- +DROP TRIGGER IF EXISTS InsertedChange ON Changes; + +-- insert the value if not already there +INSERT INTO GlobalIntegers + SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes + ON CONFLICT DO NOTHING; + +CREATE OR REPLACE FUNCTION InsertedChangeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + UPDATE GlobalIntegers SET value = new.seq WHERE key = 6; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER InsertedChange +AFTER INSERT ON Changes +FOR EACH ROW +EXECUTE PROCEDURE InsertedChangeFunc(); + + +------------------- CreateInstance function ------------------- +CREATE OR REPLACE FUNCTION CreateInstance( + IN patient_public_id TEXT, + IN study_public_id TEXT, + IN series_public_id TEXT, + IN instance_public_id TEXT, + OUT is_new_patient BIGINT, + OUT is_new_study BIGINT, + OUT is_new_series BIGINT, + OUT is_new_instance BIGINT, + OUT patient_internal_id BIGINT, + OUT study_internal_id BIGINT, + OUT series_internal_id BIGINT, + OUT instance_internal_id BIGINT) AS $body$ + +DECLARE + patientSeq BIGINT; + countRecycling BIGINT; + +BEGIN + is_new_patient := 1; + is_new_study := 1; + is_new_series := 1; + is_new_instance := 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL); + EXCEPTION + WHEN unique_violation THEN + is_new_patient := 0; + END; + SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_study := 0; + END; + SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_series := 0; + END; + SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_instance := 0; + END; + SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3; + + IF is_new_instance > 0 THEN + -- Move the patient to the end of the recycling order. + PERFORM PatientAddedOrUpdated(patient_internal_id, 1); + END IF; +END; + +$body$ LANGUAGE plpgsql;