Mercurial > hg > orthanc-databases

changeset 592:5abad3976d9f find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
PG: new ChildrendIndex2 to replace ChildrendIndex
author Alain Mazy <am@orthanc.team>
date Fri, 08 Nov 2024 16:19:46 +0100
parents 44451bed9a25
children 0ecf0f9558a6
files Framework/Common/ITransaction.h Framework/MySQL/MySQLDatabase.cpp Framework/MySQL/MySQLTransaction.h Framework/Odbc/OdbcDatabase.cpp Framework/PostgreSQL/PostgreSQLDatabase.cpp Framework/PostgreSQL/PostgreSQLDatabase.h Framework/PostgreSQL/PostgreSQLTransaction.h Framework/SQLite/SQLiteDatabase.cpp Framework/SQLite/SQLiteTransaction.h PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql
diffstat 13 files changed, 92 insertions(+), 10 deletions(-) [+]
line wrap: on
line diff
--- 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;
--- 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");
--- 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);
--- 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;
--- 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;
--- 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,
--- 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;
--- 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;
--- 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;
--- 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)
 ========================
 
--- 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);
--- 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
--- 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