Mercurial > hg > orthanc-databases
changeset 749:97cfbcf1a35a pg-next-699
merged default -> pg-next-699
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Thu, 09 Oct 2025 15:31:21 +0200 |
| parents | b31e78cf18eb (current diff) 176e5dfbad43 (diff) |
| children | e7a353b083fb |
| files | PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/PrepareIndex.sql |
| diffstat | 17 files changed, 170 insertions(+), 24 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Common/ITransaction.h Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/Common/ITransaction.h Thu Oct 09 15:31:21 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/MySQL/MySQLDatabase.cpp Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/MySQL/MySQLDatabase.cpp Thu Oct 09 15:31:21 2025 +0200 @@ -570,6 +570,11 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed"); } + virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE { throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed");
--- a/Framework/MySQL/MySQLTransaction.h Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/MySQL/MySQLTransaction.h Thu Oct 09 15:31:21 2025 +0200 @@ -64,6 +64,11 @@ return db_.DoesTableExist(*this, name); } + virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/Framework/Odbc/OdbcDatabase.cpp Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/Odbc/OdbcDatabase.cpp Thu Oct 09 15:31:21 2025 +0200 @@ -84,6 +84,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE { return false; @@ -193,6 +198,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE { return false; // note implemented yet
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.h Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLParameters.cpp Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLParameters.h Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLResult.cpp Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLStatement.h Thu Oct 09 15:31:21 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 Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLTransaction.h Thu Oct 09 15:31:21 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/Framework/SQLite/SQLiteDatabase.cpp Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/SQLite/SQLiteDatabase.cpp Thu Oct 09 15:31:21 2025 +0200 @@ -77,6 +77,11 @@ return db_.GetObject().DoesTableExist(name.c_str()); } + virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/Framework/SQLite/SQLiteTransaction.h Thu Oct 09 15:29:55 2025 +0200 +++ b/Framework/SQLite/SQLiteTransaction.h Thu Oct 09 15:31:21 2025 +0200 @@ -69,6 +69,11 @@ return database_.GetObject().DoesTableExist(name.c_str()); } + virtual bool DoesSchemaExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE { return false; // Not implemented yet
--- a/PostgreSQL/NEWS Thu Oct 09 15:29:55 2025 +0200 +++ b/PostgreSQL/NEWS Thu Oct 09 15:31:21 2025 +0200 @@ -1,6 +1,29 @@ Pending changes in the mainline =============================== +DB schema revision: 6 +Minimum plugin SDK (for build): 1.12.5 +Optimal plugin SDK (for build): 1.12.9 +Minimum Orthanc runtime: 1.12.5 +Optimal Orthanc runtime: 1.12.9 + +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 + the one that upgrades the schema. * Added a new primary key column in the InvalidChildCounts and GlobalIntegersChanges tables. This new column is required for pg_repack to be able to reclaim space on these tables.
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Thu Oct 09 15:29:55 2025 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Thu Oct 09 15:31:21 2025 +0200 @@ -125,11 +125,18 @@ 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"; ApplyPrepareIndex(t, manager); + hasAppliedAnUpgrade = true; if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) { @@ -279,6 +286,21 @@ LOG(WARNING) << "Upgrading DB schema by applying PrepareIndex.sql"; // apply all idempotent changes that are in the PrepareIndex.sql ApplyPrepareIndex(t, manager); + + // first check that the patch level has been upgraded correctly before we commit the transaction ! + if (!LookupGlobalIntegerProperty(currentRevision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) + { + LOG(ERROR) << "No Database revision found after the upgrade !"; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } + + LOG(WARNING) << "Database revision after the upgrade (1) is " << currentRevision; + + if (currentRevision != CURRENT_DB_REVISION) + { + LOG(ERROR) << "Invalid database revision after the upgrade (1) !"; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } } } @@ -287,9 +309,18 @@ if (hasAppliedAnUpgrade) { int currentRevision = 0; + + // wait 1s before reading the patch level in case you are using read-replicas + boost::this_thread::sleep(boost::posix_time::milliseconds(1000)); + + if (!LookupGlobalIntegerProperty(currentRevision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) + { + LOG(ERROR) << "No Database revision found after the upgrade (2) !"; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } LookupGlobalIntegerProperty(currentRevision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel); - LOG(WARNING) << "Database revision after the upgrade is " << currentRevision; + LOG(WARNING) << "Database revision after the upgrade (2) is " << currentRevision; if (currentRevision != CURRENT_DB_REVISION) {
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Thu Oct 09 15:29:55 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Thu Oct 09 15:31:21 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; @@ -154,7 +154,7 @@ --------------------- PatientRecyclingOrder ------------------- --- from rev 99, we always maintain a PatientRecyclingOrder metadata, no matter if the patient is protected or not +-- from rev 6, we always maintain a PatientRecyclingOrder metadata, no matter if the patient is protected or not CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( IN patient_id BIGINT )