Mercurial > hg > orthanc-databases
view PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 618:20d7c9471e8e
fix pg unit tests (1)
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Wed, 18 Dec 2024 11:23:35 +0100 |
parents | c2497f8bf5b6 |
children | a9a7dceeaad0 |
line wrap: on
line source
-- This SQL file creates a DB in Rev2 directly -- 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 to developers: -- - it is and must stay idempotent. -- - it is executed when the DB is "locked", only one Orthanc instance can execute it at a given time. 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, childCount INTEGER, 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) ); DO $$ DECLARE pg_version text; BEGIN SELECT version() INTO pg_version; IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN -- PostgreSQL 11 or later -- new ChildrenIndex2 introduced in Rev3 (replacing previous ChildrenIndex) EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; ELSE EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; END IF; END $$; 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 DicomIdentifiersIndex3 ON DicomIdentifiers(tagGroup, tagElement, value); 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 patients 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); 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); END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS PatientAdded on Resources; CREATE TRIGGER PatientAdded AFTER INSERT ON Resources FOR EACH ROW EXECUTE PROCEDURE PatientAddedFunc(); -- initial population of 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; DROP TRIGGER IF EXISTS ResourceDeleted on Resources; 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; locked_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(); -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize -- that they are deleting the last instance and the parent resources would not be deleted. -- Locking only the immediate parent is sufficient to prevent from this. SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; -- 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; 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; DROP TRIGGER IF EXISTS AttachedFileDeleted on AttachedFiles; CREATE TRIGGER AttachedFileDeleted AFTER DELETE ON AttachedFiles FOR EACH ROW EXECUTE PROCEDURE AttachedFileDeletedFunc(); ------------------- Fast Statistics ------------------- -- initial population of GlobalIntegers if not already there 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 (id, type) DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; 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; DROP TRIGGER IF EXISTS InsertedChange on Changes; 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$ 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, 0) RETURNING internalid INTO patient_internal_id; EXCEPTION WHEN unique_violation THEN is_new_patient := 0; 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 END; BEGIN INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id; EXCEPTION WHEN unique_violation THEN is_new_study := 0; 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; END; BEGIN INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id; EXCEPTION WHEN unique_violation THEN is_new_series := 0; 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; END; BEGIN INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id; EXCEPTION WHEN unique_violation THEN is_new_instance := 0; 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 END; 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; -- function to compute a statistic in a ReadOnly transaction CREATE OR REPLACE FUNCTION ComputeStatisticsReadOnly( IN statistics_key INTEGER, OUT accumulated_value BIGINT ) RETURNS BIGINT AS $body$ DECLARE current_value BIGINT; BEGIN SELECT VALUE FROM GlobalIntegers INTO current_value WHERE key = statistics_key; SELECT COALESCE(SUM(value), 0) + current_value FROM GlobalIntegersChanges INTO accumulated_value WHERE key = statistics_key; END; $body$ LANGUAGE plpgsql; -- -- new in Rev3 -- Computes the childCount for a number of resources for which it has not been computed yet. -- This is actually used only after an update to Rev3. A thread will call this function -- at regular interval to update all missing values and stop once all values have been processed. CREATE OR REPLACE FUNCTION ComputeMissingChildCount( IN batch_size BIGINT, OUT updated_rows_count BIGINT ) RETURNS BIGINT AS $body$ BEGIN UPDATE Resources AS r SET childCount = (SELECT COUNT(childLevel.internalId) FROM Resources AS childLevel WHERE childLevel.parentId = r.internalId) WHERE internalId IN ( SELECT internalId FROM Resources WHERE resourceType < 3 AND childCount IS NULL LIMIT batch_size); -- Get the number of rows affected GET DIAGNOSTICS updated_rows_count = ROW_COUNT; END; $body$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS IncrementChildCount on Resources; DROP TRIGGER IF EXISTS DecrementChildCount on Resources; CREATE OR REPLACE FUNCTION UpdateChildCount() RETURNS TRIGGER AS $body$ BEGIN IF TG_OP = 'INSERT' THEN IF new.parentId IS NOT NULL THEN -- try to increment the childcount from the parent -- note that we already have the lock on this row because the parent is locked in CreateInstance UPDATE Resources SET childCount = childCount + 1 WHERE internalId = new.parentId AND childCount IS NOT NULL; -- this should only happen for old studies whose childCount has not yet been computed -- note: this child has already been added so it will be counted IF NOT FOUND THEN UPDATE Resources SET childCount = (SELECT COUNT(*) FROM Resources WHERE internalId = new.parentId) WHERE internalId = new.parentId; END IF; END IF; ELSIF TG_OP = 'DELETE' THEN IF old.parentId IS NOT NULL THEN -- Decrement the child count for the parent -- note that we already have the lock on this row because the parent is locked in DeleteResource UPDATE Resources SET childCount = childCount - 1 WHERE internalId = old.parentId AND childCount IS NOT NULL; -- this should only happen for old studies whose childCount has not yet been computed -- note: this child has already been removed so it will not be counted IF NOT FOUND THEN UPDATE Resources SET childCount = (SELECT COUNT(*) FROM Resources WHERE internalId = new.parentId) WHERE internalId = new.parentId; END IF; END IF; END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql; CREATE TRIGGER IncrementChildCount AFTER INSERT ON Resources FOR EACH ROW EXECUTE PROCEDURE UpdateChildCount(); CREATE TRIGGER DecrementChildCount AFTER DELETE ON Resources FOR EACH ROW WHEN (OLD.parentId IS NOT NULL) EXECUTE PROCEDURE UpdateChildCount(); -- set the global properties that actually documents the DB version, revision and some of the capabilities DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13, 14); INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion INSERT INTO GlobalProperties VALUES (4, 3); -- GlobalProperty_DatabasePatchLevel INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance -- this is actually the 3rd version of HasCreateInstance INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex INSERT INTO GlobalProperties VALUES (14, 1); -- GlobalProperty_HasComputeStatisticsReadOnly