Mercurial > hg > orthanc-databases
changeset 599:cda99cc96843 find-refactoring tip
childCount in the Resources table instead of a separate table
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Thu, 28 Nov 2024 12:42:36 +0100 |
| parents | 8f6e7ae942f3 |
| children | |
| files | Framework/Plugins/IndexBackend.cpp PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql |
| diffstat | 6 files changed, 250 insertions(+), 68 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Plugins/IndexBackend.cpp Wed Nov 27 16:05:49 2024 +0100 +++ b/Framework/Plugins/IndexBackend.cpp Thu Nov 28 12:42:36 2024 +0100 @@ -3587,9 +3587,30 @@ } else if (childrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers { - if (HasChildCountTable()) + if (HasChildCountTable()) // TODO: rename in HasChildCountColumn ? { - // we get the count value either from the childCount table if it has been computed or from the Resources table + // // we get the count value either from the childCount table if it has been computed or from the Resources table + // sql += "UNION ALL SELECT " + // " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " + // " Lookup.internalId AS c1_internalId, " + // " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + // " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + // " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + // " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + // " " + formatter.FormatNull("INT") + " AS c6_int1, " + // " " + formatter.FormatNull("INT") + " AS c7_int2, " + // " " + formatter.FormatNull("INT") + " AS c8_int3, " + // " COALESCE(" + // " (ChildCount.childCount)," + // " (SELECT COUNT(childLevel.internalId)" + // " FROM Resources AS childLevel" + // " WHERE Lookup.internalId = childLevel.parentId" + // " )) AS c9_big_int1, " + // " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + // "FROM Lookup " + // "LEFT JOIN ChildCount ON Lookup.internalId = ChildCount.parentId "; + + // we get the count value either from the childCount column if it has been computed or from the Resources table sql += "UNION ALL SELECT " " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " " Lookup.internalId AS c1_internalId, " @@ -3601,14 +3622,14 @@ " " + formatter.FormatNull("INT") + " AS c7_int2, " " " + formatter.FormatNull("INT") + " AS c8_int3, " " COALESCE(" - " (ChildCount.childCount)," + " (Resources.childCount)," " (SELECT COUNT(childLevel.internalId)" " FROM Resources AS childLevel" " WHERE Lookup.internalId = childLevel.parentId" " )) AS c9_big_int1, " " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " "FROM Lookup " - "LEFT JOIN ChildCount ON Lookup.internalId = ChildCount.parentId "; + "LEFT JOIN Resources ON Lookup.internalId = Resources.internalId "; } else { @@ -3687,7 +3708,31 @@ { if (HasChildCountTable()) { - // we get the count value either from the childCount table if it has been computed or from the Resources table + // // we get the count value either from the childCount table if it has been computed or from the Resources table + // sql += "UNION ALL SELECT " + // " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + // " Lookup.internalId AS c1_internalId, " + // " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + // " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + // " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + // " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + // " " + formatter.FormatNull("INT") + " AS c6_int1, " + // " " + formatter.FormatNull("INT") + " AS c7_int2, " + // " " + formatter.FormatNull("INT") + " AS c8_int3, " + // " COALESCE(" + // " (SELECT SUM(ChildCount.childCount)" + // " FROM ChildCount" + // " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + // " WHERE ChildCount.parentId = childLevel.internalId)," + // " (SELECT COUNT(grandChildLevel.internalId)" + // " FROM Resources AS childLevel" + // " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + // " WHERE Lookup.internalId = childLevel.parentId" + // " )) AS c9_big_int1, " + // " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + // "FROM Lookup "; + + // we get the count value either from the childCount column if it has been computed or from the Resources table sql += "UNION ALL SELECT " " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " " Lookup.internalId AS c1_internalId, " @@ -3699,10 +3744,9 @@ " " + formatter.FormatNull("INT") + " AS c7_int2, " " " + formatter.FormatNull("INT") + " AS c8_int3, " " COALESCE(" - " (SELECT SUM(ChildCount.childCount)" - " FROM ChildCount" - " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" - " WHERE ChildCount.parentId = childLevel.internalId)," + " (SELECT SUM(childLevel.childCount)" + " FROM Resources AS childLevel" + " WHERE childLevel.parentId = Lookup.internalId)," " (SELECT COUNT(grandChildLevel.internalId)" " FROM Resources AS childLevel" " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" @@ -3799,7 +3843,33 @@ { if (HasChildCountTable()) { - // we get the count value either from the childCount table if it has been computed or from the Resources table + // // we get the count value either from the childCount table if it has been computed or from the Resources table + // sql += "UNION ALL SELECT " + // " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + // " Lookup.internalId AS c1_internalId, " + // " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + // " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + // " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + // " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + // " " + formatter.FormatNull("INT") + " AS c6_int1, " + // " " + formatter.FormatNull("INT") + " AS c7_int2, " + // " " + formatter.FormatNull("INT") + " AS c8_int3, " + // " COALESCE(" + // " (SELECT SUM(ChildCount.childCount)" + // " FROM ChildCount" + // " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + // " INNER JOIN Resources AS grandChildLevel ON grandChildLevel.parentId = childLevel.internalId" + // " WHERE ChildCount.parentId = grandChildLevel.internalId)," + // " (SELECT COUNT(grandGrandChildLevel.internalId)" + // " FROM Resources AS childLevel" + // " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + // " INNER JOIN Resources AS grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId" + // " WHERE Lookup.internalId = childLevel.parentId" + // " )) AS c9_big_int1, " + // " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + // "FROM Lookup "; + + // we get the count value either from the childCount column if it has been computed or from the Resources table sql += "UNION ALL SELECT " " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " " Lookup.internalId AS c1_internalId, " @@ -3811,11 +3881,10 @@ " " + formatter.FormatNull("INT") + " AS c7_int2, " " " + formatter.FormatNull("INT") + " AS c8_int3, " " COALESCE(" - " (SELECT SUM(ChildCount.childCount)" - " FROM ChildCount" + " (SELECT SUM(grandChildLevel.childCount)" + " FROM Resources AS grandChildLevel" " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" - " INNER JOIN Resources AS grandChildLevel ON grandChildLevel.parentId = childLevel.internalId" - " WHERE ChildCount.parentId = grandChildLevel.internalId)," + " WHERE grandChildLevel.parentId = childLevel.internalId)," " (SELECT COUNT(grandGrandChildLevel.internalId)" " FROM Resources AS childLevel" " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId"
--- a/PostgreSQL/CMakeLists.txt Wed Nov 27 16:05:49 2024 +0100 +++ b/PostgreSQL/CMakeLists.txt Thu Nov 28 12:42:36 2024 +0100 @@ -92,7 +92,7 @@ POSTGRESQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql POSTGRESQL_UPGRADE_REV1_TO_REV2 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql - POSTGRESQL_UPGRADE_REV2_TO_REV3 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql + POSTGRESQL_UPGRADE_REV2_TO_REV3 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3b.sql )
--- a/PostgreSQL/NEWS Wed Nov 27 16:05:49 2024 +0100 +++ b/PostgreSQL/NEWS Thu Nov 28 12:42:36 2024 +0100 @@ -16,9 +16,9 @@ creation of this index. If needed, you can create it manually before installing the new plugin by executing these SQL commands: - https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql + https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_To_Rev3b.sql -* WARNING: A new table is being created when upgrading to this version. A new thread +* WARNING: A new column has been added in the resources table when upgrading to this version. A new thread is populating this new table and might consume DB bandwitdh and CPU.
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql Thu Nov 28 12:42:36 2024 +0100 @@ -0,0 +1,84 @@ +-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1). + -- It actually deletes the ChildCount table and triggers + -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than + -- ChildrenIndex and is not incompatible with previous revisions. + +-- remove the childCount column in resources +DO $body$ +BEGIN + IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN + ALTER TABLE Resources DROP COLUMN childcount; + ELSE + raise notice 'the resources.childcount column does not exists'; + END IF; + +END $body$; + + +------------------- re-install old 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) 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) 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) 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) 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; + + + + +-- set the global properties that actually documents the DB version, revision and some of the capabilities +-- modify only the ones that have changed +DELETE FROM GlobalProperties WHERE property IN (4, 11); +INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Wed Nov 27 16:05:49 2024 +0100 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Thu Nov 28 12:42:36 2024 +0100 @@ -15,6 +15,7 @@ resourceType INTEGER NOT NULL, publicId VARCHAR(64) NOT NULL, parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + childCount INTEGER, CONSTRAINT UniquePublicId UNIQUE (publicId) ); @@ -529,7 +530,7 @@ is_new_instance := 1; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id; + 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; @@ -537,7 +538,7 @@ END; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id; + 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; @@ -545,7 +546,7 @@ END; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id; + 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; @@ -553,7 +554,7 @@ END; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id; + 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; @@ -590,89 +591,75 @@ $body$ LANGUAGE plpgsql; --- new in Rev3 +-- -- new in Rev3b -CREATE TABLE IF NOT EXISTS ChildCount ( - parentId BIGINT PRIMARY KEY REFERENCES Resources(internalId) ON DELETE CASCADE, - childCount INTEGER NOT NULL DEFAULT 0, - CONSTRAINT UniqueParentID UNIQUE (parentId) -); - - --- Computes the ChildCount for a number of resources for which it has not been computed yet. +-- 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 - - INSERT INTO ChildCount (parentID, childCount) - SELECT r.internalId AS parentId, COUNT(childLevel.internalId) AS childCount - FROM Resources AS r - LEFT JOIN Resources AS childLevel ON r.internalId = childLevel.parentId - WHERE r.internalId IN ( - SELECT internalId FROM Resources AS r - WHERE resourceType < 3 AND NOT EXISTS(SELECT 1 FROM ChildCount WHERE ChildCount.parentId = r.internalId) - LIMIT batch_size) - GROUP BY r.internalId; + 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$ -DECLARE - parent_id BIGINT; BEGIN IF TG_OP = 'INSERT' THEN IF new.parentId IS NOT NULL THEN -- try to increment the childcount from the parent - UPDATE ChildCount + -- note that we already have the lock on this row because the parent is locked in CreateInstance + UPDATE Resources SET childCount = childCount + 1 - WHERE parentId = new.parentId - RETURNING parentId INTO parent_id; + 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 - INSERT INTO childcount (parentId, childCount) - SELECT parentID, COUNT(*) - FROM Resources - WHERE parentId = parent_id - GROUP BY parentId; + UPDATE Resources + SET childCount = (SELECT COUNT(*) + FROM Resources + WHERE internalId = new.parentId) + WHERE internalId = new.parentId; END IF; END IF; - -- this is a future parent, start counting the children - IF new.resourcetype < 3 THEN - insert into ChildCount (parentId, childCount) - values (new.internalId, 0); - END IF; - ELSIF TG_OP = 'DELETE' THEN IF old.parentId IS NOT NULL THEN -- Decrement the child count for the parent - UPDATE ChildCount + -- note that we already have the lock on this row because the parent is locked in DeleteResource + UPDATE Resources SET childCount = childCount - 1 - WHERE parentId = old.parentId - RETURNING parentId INTO parent_id; + 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 - INSERT INTO childcount (parentId, childCount) - SELECT parentID, COUNT(*) - FROM Resources - WHERE parentId = parent_id - GROUP BY parentId; + UPDATE Resources + SET childCount = (SELECT COUNT(*) + FROM Resources + WHERE internalId = new.parentId) + WHERE internalId = new.parentId; END IF; END IF; @@ -681,13 +668,11 @@ END; $body$ LANGUAGE plpgsql; -DROP TRIGGER IF EXISTS IncrementChildCount on Resources; CREATE TRIGGER IncrementChildCount AFTER INSERT ON Resources FOR EACH ROW EXECUTE PROCEDURE UpdateChildCount(); -DROP TRIGGER IF EXISTS DecrementChildCount on Resources; CREATE TRIGGER DecrementChildCount AFTER DELETE ON Resources FOR EACH ROW
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql Thu Nov 28 12:42:36 2024 +0100 @@ -0,0 +1,44 @@ +-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2) +-- It actually contains only the changes that: + -- can not be executed with an idempotent statement in SQL + -- or would polute the PrepareIndex.sql + -- do facilite an up-time upgrade +-- This file is executed only if the current schema is in revision 2 and it is executed +-- before PrepareIndex.sql that is idempotent. + + +-- create a new ChildrenIndex2 that is replacing ChildrenIndex. +-- We create it in this partial update so it can be created while the system is up ! +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 + 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 $$; + +DROP INDEX IF EXISTS ChildrenIndex; -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading + +-- add the childCount columns in Resources if not yet done + +DO $body$ +BEGIN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN + ALTER TABLE Resources ADD COLUMN childcount INTEGER; + ELSE + raise notice 'the resources.childcount column already exists'; + END IF; + +END $body$; + + + +-- other changes performed in PrepareIndex.sql: + -- add ChildCount tables and triggers +