# HG changeset patch # User Alain Mazy # Date 1749031436 -7200 # Node ID 0ce2725836c6cdb5f66f83ad843a405d687ce736 # Parent 96a6640b849839acf29c956235500caa57120ad1 removed DeletedResource trigger that is now inlined in the DeleteResource function to improve observability and ease comprehension diff -r 96a6640b8498 -r 0ce2725836c6 PostgreSQL/Plugins/PostgreSQLIndex.cpp --- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Jun 04 11:01:07 2025 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Jun 04 12:03:56 2025 +0200 @@ -459,7 +459,7 @@ void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager) { - { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS + { DatabaseManager::CachedStatement statement( STATEMENT_FROM_HERE, manager, "SELECT CreateDeletedFilesTemporaryTable()" @@ -471,29 +471,12 @@ void PostgreSQLIndex::ClearDeletedResources(DatabaseManager& manager) { - { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(" - "resourceType INTEGER NOT NULL," - "publicId VARCHAR(64) NOT NULL" - ");" - ); - statement.Execute(); - } - { - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "DELETE FROM DeletedResources;" - ); - - statement.Execute(); - } - + // not used anymore in PostgreSQL } void PostgreSQLIndex::ClearRemainingAncestor(DatabaseManager& manager) { + // not used anymore in PostgreSQL } void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output, diff -r 96a6640b8498 -r 0ce2725836c6 PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql --- a/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql Wed Jun 04 11:01:07 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql Wed Jun 04 12:03:56 2025 +0200 @@ -220,6 +220,31 @@ $body$ LANGUAGE plpgsql; +-- restore the DeletedResource trigger + +------------------- 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(); ---------- diff -r 96a6640b8498 -r 0ce2725836c6 PostgreSQL/Plugins/SQL/PrepareIndex.sql --- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Wed Jun 04 11:01:07 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Wed Jun 04 12:03:56 2025 +0200 @@ -169,30 +169,6 @@ END; $body$ LANGUAGE plpgsql; -------------------- 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 ------------------- @@ -202,7 +178,10 @@ OUT remaining_anncestor_public_id TEXT) AS $body$ DECLARE - deleted_row RECORD; + deleted_resource_row RECORD; + deleted_parent_row RECORD; + deleted_grand_parent_row RECORD; + deleted_grand_grand_parent_row RECORD; locked_row RECORD; BEGIN @@ -224,16 +203,47 @@ 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 + + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_resource_row; + -- keep track of the deleted resources for C++ code + INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.publicId); + -- 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); + WHERE internalId = deleted_resource_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId); + + IF deleted_resource_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_resource_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId) + RETURNING * INTO deleted_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_parent_row.resourceType, deleted_parent_row.publicId); + IF deleted_parent_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_parent_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_parent_row.parentId) + RETURNING * INTO deleted_grand_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); + + IF deleted_grand_parent_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_grand_parent_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_grand_parent_row.parentId) + RETURNING * INTO deleted_grand_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); + END IF; + END IF; + END IF; + END IF; + END IF; + END IF; END; $body$ LANGUAGE plpgsql; @@ -258,7 +268,8 @@ $body$ LANGUAGE plpgsql; - +-- Keep track of deleted files such that the C++ code knows which files have been deleted. +-- Attached files are deleted by cascade when the related resource is deleted. CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() RETURNS TRIGGER AS $body$ BEGIN diff -r 96a6640b8498 -r 0ce2725836c6 PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql --- a/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql Wed Jun 04 11:01:07 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql Wed Jun 04 12:03:56 2025 +0200 @@ -44,4 +44,9 @@ DROP TRIGGER IF EXISTS PatientAdded on Resources; DROP FUNCTION IF EXISTS PatientAddedFunc; -DROP FUNCTION IF EXISTS PatientAddedOrUpdated; \ No newline at end of file +DROP FUNCTION IF EXISTS PatientAddedOrUpdated; + +-- The DeletedResources trigger is not used anymore + +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; +DROP FUNCTION IF EXISTS ResourceDeletedFunc(); \ No newline at end of file