# HG changeset patch # User Alain Mazy # Date 1731079186 -3600 # Node ID 5abad3976d9f67f6045890ae5dbc3f4cdc633859 # Parent 44451bed9a257733d1f96e07b8a4e1316559a8bc PG: new ChildrendIndex2 to replace ChildrendIndex diff -r 44451bed9a25 -r 5abad3976d9f Framework/Common/ITransaction.h --- a/Framework/Common/ITransaction.h Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/Common/ITransaction.h Fri Nov 08 16:19:46 2024 +0100 @@ -50,6 +50,8 @@ virtual bool DoesTableExist(const std::string& name) = 0; + virtual bool DoesIndexExist(const std::string& name) = 0; + virtual bool DoesTriggerExist(const std::string& name) = 0; // Only for MySQL virtual void ExecuteMultiLines(const std::string& query) = 0; diff -r 44451bed9a25 -r 5abad3976d9f Framework/MySQL/MySQLDatabase.cpp --- a/Framework/MySQL/MySQLDatabase.cpp Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/MySQL/MySQLDatabase.cpp Fri Nov 08 16:19:46 2024 +0100 @@ -570,6 +570,11 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed"); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed"); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed"); diff -r 44451bed9a25 -r 5abad3976d9f Framework/MySQL/MySQLTransaction.h --- a/Framework/MySQL/MySQLTransaction.h Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/MySQL/MySQLTransaction.h Fri Nov 08 16:19:46 2024 +0100 @@ -64,6 +64,11 @@ return db_.DoesTableExist(*this, name); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return db_.DoesIndexExist(*this, name); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return db_.DoesTriggerExist(*this, name); diff -r 44451bed9a25 -r 5abad3976d9f Framework/Odbc/OdbcDatabase.cpp --- a/Framework/Odbc/OdbcDatabase.cpp Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/Odbc/OdbcDatabase.cpp Fri Nov 08 16:19:46 2024 +0100 @@ -84,6 +84,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return db_.DoesIndexExist(name.c_str()); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; @@ -188,6 +193,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; // note implemented yet + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; diff -r 44451bed9a25 -r 5abad3976d9f Framework/PostgreSQL/PostgreSQLDatabase.cpp --- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp Fri Nov 08 16:19:46 2024 +0100 @@ -210,6 +210,26 @@ return !result.IsDone(); } + bool PostgreSQLDatabase::DoesIndexExist(const std::string& name) + { + std::string lower; + Orthanc::Toolbox::ToLowerCase(lower, name); + + // http://stackoverflow.com/a/24089729/881731 + + PostgreSQLStatement statement(*this, + "SELECT 1 FROM pg_catalog.pg_class c " + "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " + "WHERE n.nspname = 'public' AND c.relkind='i' " + "AND c.relname=$1"); + + statement.DeclareInputString(0); + statement.BindString(0, lower); + + PostgreSQLResult result(statement); + return !result.IsDone(); + } + bool PostgreSQLDatabase::DoesColumnExist(const std::string& tableName, const std::string& columnName) @@ -290,6 +310,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return db_.DoesIndexExist(name.c_str()); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; diff -r 44451bed9a25 -r 5abad3976d9f Framework/PostgreSQL/PostgreSQLDatabase.h --- a/Framework/PostgreSQL/PostgreSQLDatabase.h Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.h Fri Nov 08 16:19:46 2024 +0100 @@ -74,6 +74,8 @@ void ExecuteMultiLines(const std::string& sql); + bool DoesIndexExist(const std::string& name); + bool DoesTableExist(const std::string& name); bool DoesColumnExist(const std::string& tableName, diff -r 44451bed9a25 -r 5abad3976d9f Framework/PostgreSQL/PostgreSQLTransaction.h --- a/Framework/PostgreSQL/PostgreSQLTransaction.h Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLTransaction.h Fri Nov 08 16:19:46 2024 +0100 @@ -67,6 +67,11 @@ return database_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return database_.DoesIndexExist(name.c_str()); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; diff -r 44451bed9a25 -r 5abad3976d9f Framework/SQLite/SQLiteDatabase.cpp --- a/Framework/SQLite/SQLiteDatabase.cpp Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/SQLite/SQLiteDatabase.cpp Fri Nov 08 16:19:46 2024 +0100 @@ -77,6 +77,11 @@ return db_.GetObject().DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; diff -r 44451bed9a25 -r 5abad3976d9f Framework/SQLite/SQLiteTransaction.h --- a/Framework/SQLite/SQLiteTransaction.h Wed Nov 06 19:27:21 2024 +0100 +++ b/Framework/SQLite/SQLiteTransaction.h Fri Nov 08 16:19:46 2024 +0100 @@ -69,6 +69,11 @@ return database_.GetObject().DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; // Not implemented yet + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; diff -r 44451bed9a25 -r 5abad3976d9f PostgreSQL/NEWS --- a/PostgreSQL/NEWS Wed Nov 06 19:27:21 2024 +0100 +++ b/PostgreSQL/NEWS Fri Nov 08 16:19:46 2024 +0100 @@ -3,7 +3,19 @@ DB schema revision: 2 Minimum plugin SDK (for build): 1.12.3 +Optimal plugin SDK (for build): 1.12.5+ Minimum Orthanc runtime: 1.12.3 +Optimal Orthanc runtime: 1.12.5+ + +* WARNING: An Index is being replaced to improve performance. The creation + of the new index can take some time (we have observed 3 minutes on a + DB with 70M instances). Orthanc will not be available during the + creation of this index. If needed, you can create it manually before installing + the new plugin by executing these SQL commands: + + CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId); -- new in rev 3 + DROP INDEX IF EXISTS ChildrenIndex; -- till rev 2; replaced by ChildrenIndex in rev 3 but no need to uninstall ChildrenIndex2 it when downgrading + * Fix updates from plugin version 3.3 to latest version * Added support for ExtendedChanges: @@ -20,7 +32,6 @@ - "IndexConnectionsCount": 50 - "TransactionMode": "ReadCommitted" - Release 6.2 (2024-03-25) ======================== diff -r 44451bed9a25 -r 5abad3976d9f PostgreSQL/Plugins/PostgreSQLIndex.cpp --- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Nov 06 19:27:21 2024 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Fri Nov 08 16:19:46 2024 +0100 @@ -226,6 +226,17 @@ ApplyPrepareIndex(t, manager); } + if (!t.GetDatabaseTransaction().DoesIndexExist("ChildrenIndex2")) + { + // apply all idempotent changes that are in the PrepareIndex. In this case, we are just interested by + // ChildrenIndex2 that does not need to be uninstalled in case of downgrade. + ApplyPrepareIndex(t, manager); + + // delete old index + DatabaseManager::StandaloneStatement statement(manager, "DROP INDEX IF EXISTS ChildrenIndex"); + statement.Execute(); + } + // If you add new tests here, update the test in the "ReadOnly" code below } @@ -238,12 +249,8 @@ DatabaseManager::Transaction t(manager, TransactionType_ReadOnly); - int property = 0; - - // test if the last "extension" has been installed - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasComputeStatisticsReadOnly) || - property != 1) + // test if the latest "extension" has been installed + if (!t.GetDatabaseTransaction().DoesIndexExist("ChildrenIndex2")) { LOG(ERROR) << "READ-ONLY SYSTEM: the DB does not have the correct schema to run with this version of the plugin"; throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); diff -r 44451bed9a25 -r 5abad3976d9f PostgreSQL/Plugins/SQL/PrepareIndex.sql --- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Wed Nov 06 19:27:21 2024 +0100 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Fri Nov 08 16:19:46 2024 +0100 @@ -106,7 +106,7 @@ PRIMARY KEY(server, property) ); -CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId); +CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId); -- new in rev 3 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); @@ -572,7 +572,7 @@ -- 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); +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, 2); -- GlobalProperty_DatabasePatchLevel INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast diff -r 44451bed9a25 -r 5abad3976d9f PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql --- a/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Wed Nov 06 19:27:21 2024 +0100 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Fri Nov 08 16:19:46 2024 +0100 @@ -1,4 +1,4 @@ --- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1 or 2) +-- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1) -- It actually contains only the changes that: -- can not be executed with an idempotent statement in SQL -- or would polute the PrepareIndex.sql