Mercurial > hg > orthanc-databases

changeset 745:54bdb6c3bbba

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added 'Schema' configuration
author Alain Mazy <am@orthanc.team>
date Wed, 08 Oct 2025 12:10:38 +0200
parents 55a827c8913a
children fc41dc504073
files Framework/Common/ITransaction.h Framework/PostgreSQL/PostgreSQLDatabase.cpp Framework/PostgreSQL/PostgreSQLDatabase.h Framework/PostgreSQL/PostgreSQLParameters.cpp Framework/PostgreSQL/PostgreSQLParameters.h Framework/PostgreSQL/PostgreSQLResult.cpp Framework/PostgreSQL/PostgreSQLStatement.cpp Framework/PostgreSQL/PostgreSQLStatement.h Framework/PostgreSQL/PostgreSQLTransaction.h PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/PrepareIndex.sql
diffstat 12 files changed, 100 insertions(+), 22 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Common/ITransaction.h	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/Common/ITransaction.h	Wed Oct 08 12:10:38 2025 +0200
@@ -50,6 +50,8 @@
 
     virtual bool DoesTableExist(const std::string& name) = 0;
 
+    virtual bool DoesSchemaExist(const std::string& name) = 0;
+    
     virtual bool DoesIndexExist(const std::string& name) = 0;
 
     virtual bool DoesTriggerExist(const std::string& name) = 0;  // Only for MySQL
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp	Wed Oct 08 12:10:38 2025 +0200
@@ -111,6 +111,10 @@
       LOG(ERROR) << "PostgreSQL error: " << message;
       throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseUnavailable);
     }
+
+    // select the schema each time we open a connection
+    PostgreSQLStatement setSchema(*this, "SET search_path TO " + parameters_.GetSchema());
+    setSchema.Run();
   }
 
 
@@ -189,22 +193,38 @@
     }
   }
 
-
-  bool PostgreSQLDatabase::DoesTableExist(const std::string& name)
+  bool PostgreSQLDatabase::DoesSchemaExist(const std::string& schema)
   {
-    std::string lower;
-    Orthanc::Toolbox::ToLowerCase(lower, name);
+    PostgreSQLStatement statement(*this, 
+                                  "SELECT schema_name "
+                                  "FROM information_schema.schemata "
+                                  "WHERE schema_name = $1");
+
+    statement.DeclareInputName(0);
+    statement.BindString(0, schema);
+
+    PostgreSQLResult result(statement);
+    return !result.IsDone();
+  }
+
+  bool PostgreSQLDatabase::DoesTableExist(const std::string& tableName)
+  {
+    std::string lowerTableName;
+    Orthanc::Toolbox::ToLowerCase(lowerTableName, tableName);
 
     // 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='r' "
-                                  "AND c.relname=$1");
+                                  "WHERE n.nspname = $1 AND c.relkind='r' "
+                                  "AND c.relname=$2");
 
-    statement.DeclareInputString(0);
-    statement.BindString(0, lower);
+    statement.DeclareInputName(0);
+    statement.BindString(0, parameters_.GetSchema());
+
+    statement.DeclareInputString(1);
+    statement.BindString(1, lowerTableName);
 
     PostgreSQLResult result(statement);
     return !result.IsDone();
@@ -220,11 +240,14 @@
     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");
+                                  "WHERE n.nspname = $1 AND c.relkind='i' "
+                                  "AND c.relname=$2");
 
-    statement.DeclareInputString(0);
-    statement.BindString(0, lower);
+    statement.DeclareInputName(0);
+    statement.BindString(0, parameters_.GetSchema());
+
+    statement.DeclareInputString(1);
+    statement.BindString(1, lower);
 
     PostgreSQLResult result(statement);
     return !result.IsDone();
@@ -246,7 +269,7 @@
     statement.DeclareInputString(1);
     statement.DeclareInputString(2);
     
-    statement.BindString(0, "public" /* schema */);
+    statement.BindString(0, parameters_.GetSchema());
     statement.BindString(1, lowerTable);
     statement.BindString(2, lowerColumn);
     
@@ -263,11 +286,11 @@
     ExecuteMultiLines("SELECT lo_unlink(loid) FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) as loids;");
 
     // http://stackoverflow.com/a/21247009/881731
-    ExecuteMultiLines("DROP SCHEMA public CASCADE;");
-    ExecuteMultiLines("CREATE SCHEMA public;");
-    ExecuteMultiLines("GRANT ALL ON SCHEMA public TO postgres;");
-    ExecuteMultiLines("GRANT ALL ON SCHEMA public TO public;");
-    ExecuteMultiLines("COMMENT ON SCHEMA public IS 'standard public schema';");
+    ExecuteMultiLines("DROP SCHEMA " + parameters_.GetSchema() + " CASCADE;");
+    ExecuteMultiLines("CREATE SCHEMA " + parameters_.GetSchema() + ";");
+    ExecuteMultiLines("GRANT ALL ON SCHEMA " + parameters_.GetSchema() + " TO postgres;");
+    ExecuteMultiLines("GRANT ALL ON SCHEMA " + parameters_.GetSchema() + " TO public;");
+    ExecuteMultiLines("COMMENT ON SCHEMA " + parameters_.GetSchema() + " IS 'standard orthanc schema';");
 
     transaction.Commit();
   }
@@ -310,6 +333,11 @@
         return db_.DoesTableExist(name.c_str());
       }
 
+      virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE
+      {
+        return db_.DoesSchemaExist(name.c_str());
+      }
+
       virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
       {
         return db_.DoesIndexExist(name.c_str());
--- a/Framework/PostgreSQL/PostgreSQLDatabase.h	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLDatabase.h	Wed Oct 08 12:10:38 2025 +0200
@@ -81,6 +81,8 @@
     bool DoesColumnExist(const std::string& tableName,
                          const std::string& columnName);
 
+    bool DoesSchemaExist(const std::string& name);
+
     void ClearAll();   // Only for unit tests!
 
     virtual Dialect GetDialect() const ORTHANC_OVERRIDE
--- a/Framework/PostgreSQL/PostgreSQLParameters.cpp	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLParameters.cpp	Wed Oct 08 12:10:38 2025 +0200
@@ -25,6 +25,7 @@
 
 #include <Logging.h>
 #include <OrthancException.h>
+#include <Toolbox.h>
 
 #include <boost/lexical_cast.hpp>
 
@@ -98,6 +99,8 @@
 
     lock_ = configuration.GetBooleanValue("Lock", true);  // Use locking by default
 
+    SetSchema(configuration.GetStringValue("Schema", "public"));
+
     isVerboseEnabled_ = configuration.GetBooleanValue("EnableVerboseLogs", false);
     allowInconsistentChildCounts_ = configuration.GetBooleanValue("AllowInconsistentChildCounts", false);
 
@@ -202,6 +205,11 @@
     database_ = database;
   }
 
+  void PostgreSQLParameters::SetSchema(const std::string& schema)
+  {
+    Orthanc::Toolbox::ToLowerCase(schema_, schema);
+  }
+
   const std::string PostgreSQLParameters::GetReadWriteTransactionStatement() const
   {
     switch (isolationMode_)
--- a/Framework/PostgreSQL/PostgreSQLParameters.h	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLParameters.h	Wed Oct 08 12:10:38 2025 +0200
@@ -45,6 +45,7 @@
     std::string  username_;
     std::string  password_;
     std::string  database_;
+    std::string  schema_;
     std::string  uri_;
     bool         ssl_;
     bool         lock_;
@@ -104,6 +105,13 @@
       return database_;
     }
 
+    void SetSchema(const std::string& schema);
+
+    const std::string& GetSchema() const
+    {
+      return schema_;
+    }
+
     void SetSsl(bool ssl)
     {
       ssl_ = ssl;
--- a/Framework/PostgreSQL/PostgreSQLResult.cpp	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLResult.cpp	Wed Oct 08 12:10:38 2025 +0200
@@ -175,7 +175,7 @@
     CheckColumn(column, 0);
 
     Oid oid = PQftype(reinterpret_cast<PGresult*>(result_), column);
-    if (oid != TEXTOID && oid != VARCHAROID)
+    if (oid != TEXTOID && oid != VARCHAROID && oid != NAMEOID)
     {
       throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType);
     }
--- a/Framework/PostgreSQL/PostgreSQLStatement.cpp	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp	Wed Oct 08 12:10:38 2025 +0200
@@ -223,7 +223,7 @@
     }
 
     oids_[param] = type;
-    binary_[param] = (type == TEXTOID || type == OIDOID) ? 0 : 1;
+    binary_[param] = (type == TEXTOID || type == OIDOID ||type == NAMEOID) ? 0 : 1;
   }
 
 
@@ -245,6 +245,12 @@
   }
 
 
+  void PostgreSQLStatement::DeclareInputName(unsigned int param)
+  {
+    DeclareInputInternal(param, NAMEOID);
+  }
+
+
   void PostgreSQLStatement::DeclareInputBinary(unsigned int param)
   {
     DeclareInputInternal(param, BYTEAOID);
@@ -464,6 +470,7 @@
     switch (oids_[param])
     {
       case TEXTOID:
+      case NAMEOID:
       {
         std::string s = value + '\0';  // Make sure that there is an end-of-string character
         inputs_->SetItem(param, s.c_str(), s.size());
--- a/Framework/PostgreSQL/PostgreSQLStatement.h	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLStatement.h	Wed Oct 08 12:10:38 2025 +0200
@@ -80,6 +80,8 @@
 
     void DeclareInputString(unsigned int param);
 
+    void DeclareInputName(unsigned int param);
+
     void DeclareInputBinary(unsigned int param);
 
     void DeclareInputLargeObject(unsigned int param);
--- a/Framework/PostgreSQL/PostgreSQLTransaction.h	Fri Oct 03 18:57:04 2025 +0200
+++ b/Framework/PostgreSQL/PostgreSQLTransaction.h	Wed Oct 08 12:10:38 2025 +0200
@@ -67,6 +67,11 @@
       return database_.DoesTableExist(name.c_str());
     }
 
+    virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return database_.DoesSchemaExist(name.c_str());
+    }
+
     virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
     {
       return database_.DoesIndexExist(name.c_str());
--- a/PostgreSQL/NEWS	Fri Oct 03 18:57:04 2025 +0200
+++ b/PostgreSQL/NEWS	Wed Oct 08 12:10:38 2025 +0200
@@ -10,6 +10,16 @@
 Minimal Postgresql Server version: 9
 Optimal Postgresql Server version: 11+
 
+Changes:
+* New configuration "Schema" (default value: 'public') to allow Orthanc
+  to use another schema.  Note that, if you are not using the default 'public'
+  value, you must create the schema manually before starting Orthanc and you
+  must make sure that the user has access to it.
+  Note that Orthanc might try to install the pg_trgm extension in the 'public'
+  schema so you should make sure that the user also has access to the 'public'
+  schema or that you install the pg_trgm extension manually in the public schema.
+  The plugin now calls 'SET search_path TO $schema' when opening a new connection
+  to the DB.
 
 Maintenance:
 * Now verifying the DatabasePatchLevel (revision) in another transaction than
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Oct 03 18:57:04 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Oct 08 12:10:38 2025 +0200
@@ -125,6 +125,12 @@
         DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
         bool hasAppliedAnUpgrade = false;
 
+        if (!t.GetDatabaseTransaction().DoesSchemaExist(parameters_.GetSchema()))
+        {
+          LOG(ERROR) << "The schema '" << parameters_.GetSchema() << "' does not exist.  If you are not using the 'public' schema, you must create the schema manually before starting Orthanc.";
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);        
+        }
+
         if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
         {
           LOG(WARNING) << "PostgreSQL is creating the database schema";
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Fri Oct 03 18:57:04 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Oct 08 12:10:38 2025 +0200
@@ -145,8 +145,8 @@
 DO $body$
 begin
 	IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN
-		CREATE EXTENSION IF NOT EXISTS pg_trgm;
-        CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
+		CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
+        CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value public.gin_trgm_ops);
 	ELSE
 		RAISE NOTICE 'pg_trgm extension is not available on you system';
 	END IF;