Mercurial > hg > orthanc-databases
changeset 434:23c7af6f671a pg-transactions
DeleteResource does not need the RemainingAncestor table anymore
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Wed, 13 Dec 2023 16:52:06 +0100 |
| parents | 5964ce6385a5 |
| children | 326f8304daa1 |
| files | Framework/Common/DatabaseManager.cpp Framework/Common/DatabaseManager.h NOTES PostgreSQL/Plugins/CreateInstance.sql PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/PostgreSQLIndex.h PostgreSQL/Plugins/ResourceDeletedFunc.sql |
| diffstat | 7 files changed, 102 insertions(+), 27 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Common/DatabaseManager.cpp Wed Dec 13 15:48:56 2023 +0100 +++ b/Framework/Common/DatabaseManager.cpp Wed Dec 13 16:52:06 2023 +0100 @@ -492,7 +492,7 @@ return dynamic_cast<const Integer64Value&>(value).GetValue(); default: - //LOG(ERROR) << value.Format(); + // LOG(ERROR) << value.GetType(); throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } } @@ -521,7 +521,18 @@ } } - + bool DatabaseManager::StatementBase::IsNull(size_t field) const + { + if (IsDone()) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } + else + { + return GetResultField(field).GetType() == ValueType_Null; + } + } + std::string DatabaseManager::StatementBase::ReadString(size_t field) const { const IValue& value = GetResultField(field);
--- a/Framework/Common/DatabaseManager.h Wed Dec 13 15:48:56 2023 +0100 +++ b/Framework/Common/DatabaseManager.h Wed Dec 13 16:52:06 2023 +0100 @@ -187,6 +187,8 @@ std::string ReadString(size_t field) const; + bool IsNull(size_t field) const; + void PrintResult(std::ostream& stream) { IResult::Print(stream, GetResult());
--- a/NOTES Wed Dec 13 15:48:56 2023 +0100 +++ b/NOTES Wed Dec 13 16:52:06 2023 +0100 @@ -172,7 +172,8 @@ Orthanc mainline + PG mainline (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: XX s Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 19.861 s Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 22.500 s (with temporary tables) - + test_concurrent_anonymize_same_study deletion took: 32.265 s + Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s Orthanc mainline + PG mainline (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 23.010 s Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 1.71 s (with RemainingAncestor.id)
--- a/PostgreSQL/Plugins/CreateInstance.sql Wed Dec 13 15:48:56 2023 +0100 +++ b/PostgreSQL/Plugins/CreateInstance.sql Wed Dec 13 16:52:06 2023 +0100 @@ -24,35 +24,35 @@ BEGIN INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL); - exception - when unique_violation then + EXCEPTION + WHEN unique_violation THEN isNewPatient := 0; - end; - select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0; + END; + SELECT internalid INTO patientKey FROM "resources" WHERE publicId=patient AND resourcetype = 0; BEGIN INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey); - exception - when unique_violation then + EXCEPTION + WHEN unique_violation THEN isNewStudy := 0; - end; - select internalid into studyKey from "resources" where publicId=study and resourcetype = 1; + END; + SELECT internalid INTO studyKey FROM "resources" WHERE publicId=study AND resourcetype = 1; BEGIN INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey); - exception - when unique_violation then + EXCEPTION + WHEN unique_violation THEN isNewSeries := 0; - end; - select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2; + END; + SELECT internalid INTO seriesKey FROM "resources" WHERE publicId=series AND resourcetype = 2; BEGIN INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey); - exception - when unique_violation then + EXCEPTION + WHEN unique_violation THEN isNewInstance := 0; - end; - select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3; + END; + SELECT internalid INTO instanceKey FROM "resources" WHERE publicId=instance AND resourcetype = 3; IF isNewInstance > 0 THEN -- Move the patient to the end of the recycling order
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Dec 13 15:48:56 2023 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Dec 13 16:52:06 2023 +0100 @@ -539,6 +539,45 @@ } + void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output, + DatabaseManager& manager, + int64_t id) + { + ClearDeletedFiles(manager); + ClearDeletedResources(manager); + + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT * FROM DeleteResource(${id})"); + + statement.SetParameterType("id", ValueType_Integer64); + + Dictionary args; + args.SetIntegerValue("id", id); + + statement.Execute(args); + + if (statement.IsDone() || + statement.GetResultFieldsCount() != 2) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } + + statement.SetResultFieldType(0, ValueType_Integer64); + statement.SetResultFieldType(1, ValueType_Utf8String); + + if (!statement.IsNull(0)) + { + output.SignalRemainingAncestor( + statement.ReadString(1), + static_cast<OrthancPluginResourceType>(statement.ReadInteger32(0))); + } + + SignalDeletedFiles(output, manager); + SignalDeletedResources(output, manager); + + } + #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Wed Dec 13 15:48:56 2023 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Wed Dec 13 16:52:06 2023 +0100 @@ -63,8 +63,11 @@ virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, - OrthancPluginResourceType type) - ORTHANC_OVERRIDE; + OrthancPluginResourceType type) ORTHANC_OVERRIDE; + + virtual void DeleteResource(IDatabaseBackendOutput& output, + DatabaseManager& manager, + int64_t id) ORTHANC_OVERRIDE; virtual uint64_t GetTotalCompressedSize(DatabaseManager& manager) ORTHANC_OVERRIDE;
--- a/PostgreSQL/Plugins/ResourceDeletedFunc.sql Wed Dec 13 15:48:56 2023 +0100 +++ b/PostgreSQL/Plugins/ResourceDeletedFunc.sql Wed Dec 13 16:52:06 2023 +0100 @@ -9,12 +9,6 @@ --RAISE NOTICE 'Delete resource %', old.parentId; INSERT INTO DeletedResources VALUES (old.resourceType, old.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) - INSERT INTO RemainingAncestor SELECT resourceType, publicId - FROM Resources - WHERE internalId = old.parentId - AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); -- 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); @@ -31,3 +25,28 @@ DROP TABLE IF EXISTS DeletedFiles; DROP TABLE IF EXISTS RemainingAncestor; DROP TABLE IF EXISTS DeletedResources; + + +CREATE OR REPLACE FUNCTION DeleteResource( + IN id BIGINT, + OUT remainingAncestorResourceType INTEGER, + OUT remainingAncestorPublicId TEXT) AS $body$ + +DECLARE + deleted_row RECORD; + +BEGIN + + 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 remainingAncestorResourceType, remainingAncestorPublicId + FROM Resources + WHERE internalId = deleted_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); + +END; + +$body$ LANGUAGE plpgsql;