# HG changeset patch # User Alain Mazy # Date 1727183061 -7200 # Node ID f18e46d7dbf8598c6cf9758973760b0d11c516fe # Parent 82f73188b58d291b066ebb0266f6c3be2de098f6# Parent 77c8544bbd7df2374ea0bf28eff374ade0351d28 merged find-refactoring -> attach-custom-data diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Common/DatabaseManager.cpp --- a/Framework/Common/DatabaseManager.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Common/DatabaseManager.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -550,6 +550,17 @@ } } + std::string DatabaseManager::StatementBase::ReadStringOrNull(size_t field) const + { + if (IsNull(field)) + { + return std::string(); + } + else + { + return ReadString(field); + } + } DatabaseManager::CachedStatement::CachedStatement(const StatementId& statementId, DatabaseManager& manager, diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Common/DatabaseManager.h --- a/Framework/Common/DatabaseManager.h Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Common/DatabaseManager.h Tue Sep 24 15:04:21 2024 +0200 @@ -188,6 +188,8 @@ std::string ReadString(size_t field) const; + std::string ReadStringOrNull(size_t field) const; + bool IsNull(size_t field) const; void PrintResult(std::ostream& stream) diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/DatabaseBackendAdapterV2.cpp --- a/Framework/Plugins/DatabaseBackendAdapterV2.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV2.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -187,7 +187,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) ORTHANC_OVERRIDE + const std::string& compressedHash, + const std::string& /*customData*/) ORTHANC_OVERRIDE { OrthancPluginAttachment attachment; attachment.uuid = uuid.c_str(); @@ -219,7 +220,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) ORTHANC_OVERRIDE + const std::string& compressedHash, + const std::string& /*customData*/) ORTHANC_OVERRIDE { if (allowedAnswers_ != AllowedAnswers_All && allowedAnswers_ != AllowedAnswers_Attachment) diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/DatabaseBackendAdapterV3.cpp --- a/Framework/Plugins/DatabaseBackendAdapterV3.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV3.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -421,7 +421,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) ORTHANC_OVERRIDE + const std::string& compressedHash, + const std::string& /*customData*/) ORTHANC_OVERRIDE { OrthancPluginDatabaseEvent event; event.type = OrthancPluginDatabaseEventType_DeletedAttachment; @@ -467,7 +468,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) ORTHANC_OVERRIDE + const std::string& compressedHash, + const std::string& /*customData*/) ORTHANC_OVERRIDE { SetupAnswerType(_OrthancPluginDatabaseAnswerType_Attachment); diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/DatabaseBackendAdapterV4.cpp --- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -195,7 +195,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) ORTHANC_OVERRIDE + const std::string& compressedHash, + const std::string& customData) ORTHANC_OVERRIDE { Orthanc::DatabasePluginMessages::FileInfo* attachment; @@ -269,7 +270,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) ORTHANC_OVERRIDE + const std::string& compressedHash, + const std::string& customData) ORTHANC_OVERRIDE { if (lookupAttachment_ != NULL) { diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/DatabaseBackendAdapterV4.h diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/IDatabaseBackend.h --- a/Framework/Plugins/IDatabaseBackend.h Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/IDatabaseBackend.h Tue Sep 24 15:04:21 2024 +0200 @@ -59,11 +59,21 @@ virtual bool HasRevisionsSupport() const = 0; + virtual bool HasAttachmentCustomDataSupport() const = 0; + virtual void AddAttachment(DatabaseManager& manager, int64_t id, const OrthancPluginAttachment& attachment, int64_t revision) = 0; +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) + + virtual void AddAttachment2(DatabaseManager& manager, + int64_t id, + const OrthancPluginAttachment2& attachment, + int64_t revision) = 0; +#endif + virtual void AttachChild(DatabaseManager& manager, int64_t parent, int64_t child) = 0; diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/IDatabaseBackendOutput.h --- a/Framework/Plugins/IDatabaseBackendOutput.h Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/IDatabaseBackendOutput.h Tue Sep 24 15:04:21 2024 +0200 @@ -56,7 +56,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) = 0; + const std::string& compressedHash, + const std::string& customData) = 0; virtual void SignalDeletedResource(const std::string& publicId, OrthancPluginResourceType resourceType) = 0; @@ -70,7 +71,8 @@ const std::string& uncompressedHash, int32_t compressionType, uint64_t compressedSize, - const std::string& compressedHash) = 0; + const std::string& compressedHash, + const std::string& customData) = 0; virtual void AnswerChange(int64_t seq, int32_t changeType, diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/IndexBackend.cpp --- a/Framework/Plugins/IndexBackend.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -264,7 +264,7 @@ DatabaseManager::CachedStatement statement( STATEMENT_FROM_HERE, manager, "SELECT uuid, fileType, uncompressedSize, uncompressedHash, compressionType, " - "compressedSize, compressedHash FROM DeletedFiles"); + "compressedSize, compressedHash, revision, customData FROM DeletedFiles"); statement.SetReadOnly(true); statement.Execute(); @@ -277,7 +277,8 @@ statement.ReadString(3), statement.ReadInteger32(4), statement.ReadInteger64(5), - statement.ReadString(6)); + statement.ReadString(6), + statement.ReadStringOrNull(8)); statement.Next(); } @@ -344,12 +345,25 @@ } } - - static void ExecuteAddAttachment(DatabaseManager::CachedStatement& statement, - Dictionary& args, + static void ExecuteAddAttachment(DatabaseManager& manager, int64_t id, - const OrthancPluginAttachment& attachment) + const char* uuid, + int32_t contentType, + uint64_t uncompressedSize, + const char* uncompressedHash, + int32_t compressionType, + uint64_t compressedSize, + const char* compressedHash, + const char* customData, + int64_t revision) { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "INSERT INTO AttachedFiles VALUES(${id}, ${type}, ${uuid}, ${compressed}, " + "${uncompressed}, ${compression}, ${hash}, ${hash-compressed}, ${revision}, ${custom-data})"); + + Dictionary args; + statement.SetParameterType("id", ValueType_Integer64); statement.SetParameterType("type", ValueType_Integer64); statement.SetParameterType("uuid", ValueType_Utf8String); @@ -358,51 +372,45 @@ statement.SetParameterType("compression", ValueType_Integer64); statement.SetParameterType("hash", ValueType_Utf8String); statement.SetParameterType("hash-compressed", ValueType_Utf8String); + statement.SetParameterType("revision", ValueType_Integer64); + statement.SetParameterType("custom-data", ValueType_Utf8String); args.SetIntegerValue("id", id); - args.SetIntegerValue("type", attachment.contentType); - args.SetUtf8Value("uuid", attachment.uuid); - args.SetIntegerValue("compressed", attachment.compressedSize); - args.SetIntegerValue("uncompressed", attachment.uncompressedSize); - args.SetIntegerValue("compression", attachment.compressionType); - args.SetUtf8Value("hash", attachment.uncompressedHash); - args.SetUtf8Value("hash-compressed", attachment.compressedHash); + args.SetIntegerValue("type", contentType); + args.SetUtf8Value("uuid", uuid); + args.SetIntegerValue("compressed", compressedSize); + args.SetIntegerValue("uncompressed", uncompressedSize); + args.SetIntegerValue("compression", compressionType); + args.SetUtf8Value("hash", uncompressedHash); + args.SetUtf8Value("hash-compressed", compressedHash); + args.SetIntegerValue("revision", revision); + args.SetUtf8Value("custom-data", customData); statement.Execute(args); } - + void IndexBackend::AddAttachment(DatabaseManager& manager, int64_t id, const OrthancPluginAttachment& attachment, int64_t revision) { - if (HasRevisionsSupport()) - { - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "INSERT INTO AttachedFiles VALUES(${id}, ${type}, ${uuid}, ${compressed}, " - "${uncompressed}, ${compression}, ${hash}, ${hash-compressed}, ${revision})"); - - Dictionary args; - - statement.SetParameterType("revision", ValueType_Integer64); - args.SetIntegerValue("revision", revision); - - ExecuteAddAttachment(statement, args, id, attachment); - } - else - { - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "INSERT INTO AttachedFiles VALUES(${id}, ${type}, ${uuid}, ${compressed}, " - "${uncompressed}, ${compression}, ${hash}, ${hash-compressed})"); - - Dictionary args; - ExecuteAddAttachment(statement, args, id, attachment); - } + assert(HasRevisionsSupport() && HasAttachmentCustomDataSupport()); // all plugins supports these features now + ExecuteAddAttachment(manager, id, attachment.uuid, attachment.contentType, attachment.uncompressedSize, attachment.uncompressedHash, + attachment.compressionType, attachment.compressedSize, attachment.compressedHash, "", revision); } +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) + void IndexBackend::AddAttachment2(DatabaseManager& manager, + int64_t id, + const OrthancPluginAttachment2& attachment, + int64_t revision) + { + assert(HasRevisionsSupport() && HasAttachmentCustomDataSupport()); // all plugins supports these features now + ExecuteAddAttachment(manager, id, attachment.uuid, attachment.contentType, attachment.uncompressedSize, attachment.uncompressedHash, + attachment.compressionType, attachment.compressedSize, attachment.compressedHash, attachment.customData, revision); + } +#endif void IndexBackend::AttachChild(DatabaseManager& manager, int64_t parent, @@ -1169,12 +1177,21 @@ statement.Execute(args); } - - static bool ExecuteLookupAttachment(DatabaseManager::CachedStatement& statement, - IDatabaseBackendOutput& output, + + /* Use GetOutput().AnswerAttachment() */ + bool IndexBackend::LookupAttachment(IDatabaseBackendOutput& output, + int64_t& revision /*out*/, + DatabaseManager& manager, int64_t id, int32_t contentType) { + assert(HasRevisionsSupport() && HasAttachmentCustomDataSupport()); // we force v4 plugins to support both ! + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT uuid, uncompressedSize, compressionType, compressedSize, uncompressedHash, " + "compressedHash, revision, customData FROM AttachedFiles WHERE id=${id} AND fileType=${type}"); + + statement.SetReadOnly(true); statement.SetParameterType("id", ValueType_Integer64); statement.SetParameterType("type", ValueType_Integer64); @@ -1191,64 +1208,35 @@ } else { + if (statement.GetResultField(6).GetType() == ValueType_Null) + { + // "NULL" can happen with a database created by PostgreSQL + // plugin <= 3.3 (because of "ALTER TABLE AttachedFiles") + revision = 0; + } + else + { + revision = statement.ReadInteger64(6); + } + + std::string customData; + if (statement.GetResultField(7).GetType() == ValueType_Utf8String) // column has been added in 1.12.0 + { + customData = statement.ReadString(7); + } + + output.AnswerAttachment(statement.ReadString(0), contentType, statement.ReadInteger64(1), statement.ReadString(4), statement.ReadInteger32(2), statement.ReadInteger64(3), - statement.ReadString(5)); + statement.ReadString(5), + customData); return true; } - } - - - - /* Use GetOutput().AnswerAttachment() */ - bool IndexBackend::LookupAttachment(IDatabaseBackendOutput& output, - int64_t& revision /*out*/, - DatabaseManager& manager, - int64_t id, - int32_t contentType) - { - if (HasRevisionsSupport()) - { - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "SELECT uuid, uncompressedSize, compressionType, compressedSize, uncompressedHash, " - "compressedHash, revision FROM AttachedFiles WHERE id=${id} AND fileType=${type}"); - - if (ExecuteLookupAttachment(statement, output, id, contentType)) - { - if (statement.GetResultField(6).GetType() == ValueType_Null) - { - // "NULL" can happen with a database created by PostgreSQL - // plugin <= 3.3 (because of "ALTER TABLE AttachedFiles") - revision = 0; - } - else - { - revision = statement.ReadInteger64(6); - } - - return true; - } - else - { - return false; - } - } - else - { - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "SELECT uuid, uncompressedSize, compressionType, compressedSize, uncompressedHash, " - "compressedHash FROM AttachedFiles WHERE id=${id} AND fileType=${type}"); - - revision = 0; - - return ExecuteLookupAttachment(statement, output, id, contentType); - } + } diff -r 77c8544bbd7d -r f18e46d7dbf8 Framework/Plugins/IndexBackend.h --- a/Framework/Plugins/IndexBackend.h Mon Sep 23 16:06:53 2024 +0200 +++ b/Framework/Plugins/IndexBackend.h Tue Sep 24 15:04:21 2024 +0200 @@ -90,7 +90,16 @@ int64_t id, const OrthancPluginAttachment& attachment, int64_t revision) ORTHANC_OVERRIDE; - + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) + + virtual void AddAttachment2(DatabaseManager& manager, + int64_t id, + const OrthancPluginAttachment2& attachment, + int64_t revision) ORTHANC_OVERRIDE; + +#endif + virtual void AttachChild(DatabaseManager& manager, int64_t parent, int64_t child) ORTHANC_OVERRIDE; diff -r 77c8544bbd7d -r f18e46d7dbf8 MySQL/CMakeLists.txt --- a/MySQL/CMakeLists.txt Mon Sep 23 16:06:53 2024 +0200 +++ b/MySQL/CMakeLists.txt Tue Sep 24 15:04:21 2024 +0200 @@ -91,6 +91,7 @@ MYSQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/PrepareIndex.sql MYSQL_GET_LAST_CHANGE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/GetLastChangeIndex.sql MYSQL_CREATE_INSTANCE ${CMAKE_SOURCE_DIR}/Plugins/CreateInstance.sql + MYSQL_INSTALL_REVISION_AND_CUSTOM_DATA ${CMAKE_SOURCE_DIR}/Plugins/InstallRevisionAndCustomData.sql MYSQL_DELETE_RESOURCES ${CMAKE_SOURCE_DIR}/Plugins/DeleteResources.sql ) diff -r 77c8544bbd7d -r f18e46d7dbf8 MySQL/NEWS --- a/MySQL/NEWS Mon Sep 23 16:06:53 2024 +0200 +++ b/MySQL/NEWS Tue Sep 24 15:04:21 2024 +0200 @@ -1,6 +1,8 @@ Pending changes in the mainline =============================== +* Added support for customData in AttachedFiles +* Added support for revision in AttachedFiles & Metadata * Added support for ExtendedChanges: - changes?type=...&to=... * Fixed a memory leak when executing non cached SQL statements (rarely used) diff -r 77c8544bbd7d -r f18e46d7dbf8 MySQL/Plugins/InstallRevisionAndCustomData.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/MySQL/Plugins/InstallRevisionAndCustomData.sql Tue Sep 24 15:04:21 2024 +0200 @@ -0,0 +1,28 @@ +ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER; +ALTER TABLE DeletedFiles ADD COLUMN revision INTEGER; +ALTER TABLE Metadata ADD COLUMN revision INTEGER; + +ALTER TABLE AttachedFiles ADD COLUMN customData LONGTEXT; +ALTER TABLE DeletedFiles ADD COLUMN customData LONGTEXT; + +DROP TRIGGER AttachedFileDeleted; + +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +FOR EACH ROW + BEGIN + INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash, + old.revision, old.customData)@ + END; + + +DROP TRIGGER ResourceDeleted; + +CREATE TRIGGER ResourceDeleted +BEFORE DELETE ON Resources +FOR EACH ROW + BEGIN + INSERT INTO DeletedFiles SELECT uuid, fileType, compressedSize, uncompressedSize, compressionType, uncompressedHash, compressedHash, revision, customData FROM AttachedFiles WHERE id=old.internalId@ + END; \ No newline at end of file diff -r 77c8544bbd7d -r f18e46d7dbf8 MySQL/Plugins/MySQLIndex.cpp --- a/MySQL/Plugins/MySQLIndex.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/MySQL/Plugins/MySQLIndex.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -337,8 +337,26 @@ t.Commit(); } + if (revision == 8) + { + DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); + + // Install revision and customData extension + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::MYSQL_INSTALL_REVISION_AND_CUSTOM_DATA); - if (revision != 8) + // Need to escape arobases: Don't use "t.GetDatabaseTransaction().ExecuteMultiLines()" here + db.ExecuteMultiLines(query, true); + + revision = 9; + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); + + t.Commit(); + } + + if (revision != 9) { LOG(ERROR) << "MySQL plugin is incompatible with database schema revision: " << revision; throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); diff -r 77c8544bbd7d -r f18e46d7dbf8 MySQL/Plugins/MySQLIndex.h --- a/MySQL/Plugins/MySQLIndex.h Mon Sep 23 16:06:53 2024 +0200 +++ b/MySQL/Plugins/MySQLIndex.h Tue Sep 24 15:04:21 2024 +0200 @@ -51,7 +51,12 @@ virtual bool HasRevisionsSupport() const ORTHANC_OVERRIDE { - return false; // TODO - REVISIONS + return true; + } + + virtual bool HasAttachmentCustomDataSupport() const ORTHANC_OVERRIDE + { + return true; } virtual int64_t CreateResource(DatabaseManager& manager, diff -r 77c8544bbd7d -r f18e46d7dbf8 MySQL/Plugins/PrepareIndex.sql --- a/MySQL/Plugins/PrepareIndex.sql Mon Sep 23 16:06:53 2024 +0200 +++ b/MySQL/Plugins/PrepareIndex.sql Tue Sep 24 15:04:21 2024 +0200 @@ -48,6 +48,8 @@ compressionType INTEGER, uncompressedHash VARCHAR(40), compressedHash VARCHAR(40), + -- revision INTEGER, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase + -- customData LONGTEXT, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase PRIMARY KEY(id, fileType), CONSTRAINT AttachedFiles1 FOREIGN KEY (id) REFERENCES Resources(internalId) ON DELETE CASCADE ); @@ -104,6 +106,8 @@ compressionType INTEGER, -- 4 uncompressedHash VARCHAR(40), -- 5 compressedHash VARCHAR(40) -- 6 + -- revision INTEGER, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase + -- customData LONGTEXT, -- new in v 4.X, added in MySQLIndex::ConfigureDatabase ); -- End of differences @@ -119,6 +123,8 @@ INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, old.uncompressedSize, old.compressionType, old.uncompressedHash, old.compressedHash)@ + -- old.revision, old.customData -- new in v 4.X, added in MySQLIndex::ConfigureDatabase + END; @@ -127,6 +133,7 @@ FOR EACH ROW BEGIN INSERT INTO DeletedFiles SELECT uuid, fileType, compressedSize, uncompressedSize, compressionType, uncompressedHash, compressedHash FROM AttachedFiles WHERE id=old.internalId@ + -- revision, customData -- new in v 4.X, added in MySQLIndex::ConfigureDatabase END; diff -r 77c8544bbd7d -r f18e46d7dbf8 Odbc/NEWS --- a/Odbc/NEWS Mon Sep 23 16:06:53 2024 +0200 +++ b/Odbc/NEWS Tue Sep 24 15:04:21 2024 +0200 @@ -9,6 +9,8 @@ * Fix check of Orthanc runtime version * Added support for ExtendedChanges: - changes?type=...&to=... +* Added support for customData in AttachedFiles + * Fix bug 224, error when using LIMIT with MSSQLServer https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=224 * Fixed a memory leak when executing non cached SQL statements (rarely used) @@ -23,7 +25,6 @@ * Now detecting communication link failure with the DB and retrying to connect. * Fixed "MaximumConnectionRetries" configuration that was not taken into account. - Release 1.1 (2021-12-06) ======================== diff -r 77c8544bbd7d -r f18e46d7dbf8 Odbc/Plugins/OdbcIndex.cpp --- a/Odbc/Plugins/OdbcIndex.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/Odbc/Plugins/OdbcIndex.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -162,6 +162,49 @@ return OdbcDatabase::CreateDatabaseFactory(maxConnectionRetries_, connectionRetryInterval_, connectionString_, true); } + static void AdaptTypesToDialect(std::string& sql, Dialect dialect) + { + switch (dialect) + { + case Dialect_SQLite: + boost::replace_all(sql, "${LONGTEXT}", "TEXT"); + boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"); + boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, "); + break; + + case Dialect_PostgreSQL: + boost::replace_all(sql, "${LONGTEXT}", "TEXT"); + boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGSERIAL NOT NULL PRIMARY KEY"); + boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "DEFAULT, "); + break; + + case Dialect_MySQL: + boost::replace_all(sql, "${LONGTEXT}", "LONGTEXT"); + boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY"); + boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, "); + break; + + case Dialect_MSSQL: + /** + * cf. OMSSQL-5: Use VARCHAR(MAX) instead of TEXT: (1) + * Microsoft issued a warning stating that "ntext, text, and + * image data types will be removed in a future version of + * SQL Server" + * (https://msdn.microsoft.com/en-us/library/ms187993.aspx), + * and (2) SQL Server does not support comparison of TEXT + * with '=' operator (e.g. in WHERE statements such as + * IndexBackend::LookupIdentifier())." + **/ + boost::replace_all(sql, "${LONGTEXT}", "VARCHAR(MAX)"); + boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT IDENTITY NOT NULL PRIMARY KEY"); + boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", ""); + break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + } + void OdbcIndex::ConfigureDatabase(DatabaseManager& manager, bool hasIdentifierTags, @@ -189,46 +232,8 @@ { std::string sql; Orthanc::EmbeddedResources::GetFileResource(sql, Orthanc::EmbeddedResources::ODBC_PREPARE_INDEX); - - switch (db.GetDialect()) - { - case Dialect_SQLite: - boost::replace_all(sql, "${LONGTEXT}", "TEXT"); - boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT"); - boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, "); - break; - - case Dialect_PostgreSQL: - boost::replace_all(sql, "${LONGTEXT}", "TEXT"); - boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGSERIAL NOT NULL PRIMARY KEY"); - boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "DEFAULT, "); - break; - - case Dialect_MySQL: - boost::replace_all(sql, "${LONGTEXT}", "LONGTEXT"); - boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY"); - boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", "NULL, "); - break; - - case Dialect_MSSQL: - /** - * cf. OMSSQL-5: Use VARCHAR(MAX) instead of TEXT: (1) - * Microsoft issued a warning stating that "ntext, text, and - * image data types will be removed in a future version of - * SQL Server" - * (https://msdn.microsoft.com/en-us/library/ms187993.aspx), - * and (2) SQL Server does not support comparison of TEXT - * with '=' operator (e.g. in WHERE statements such as - * IndexBackend::LookupIdentifier())." - **/ - boost::replace_all(sql, "${LONGTEXT}", "VARCHAR(MAX)"); - boost::replace_all(sql, "${AUTOINCREMENT_TYPE}", "BIGINT IDENTITY NOT NULL PRIMARY KEY"); - boost::replace_all(sql, "${AUTOINCREMENT_INSERT}", ""); - break; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); - } + + AdaptTypesToDialect(sql, db.GetDialect()); { DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); @@ -242,6 +247,22 @@ db.ExecuteMultiLines("ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); } + { // v 4.X: add customData + int patchLevel; + + if (!LookupGlobalIntegerProperty(patchLevel, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) + { + std::string sqlAddCustomData = "ALTER TABLE AttachedFiles ADD customData ${LONGTEXT};" + "ALTER TABLE DeletedFiles ADD customData ${LONGTEXT}"; + + AdaptTypesToDialect(sqlAddCustomData, db.GetDialect()); + + db.ExecuteMultiLines(sqlAddCustomData); + + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1); + } + } + t.Commit(); } } diff -r 77c8544bbd7d -r f18e46d7dbf8 Odbc/Plugins/OdbcIndex.h --- a/Odbc/Plugins/OdbcIndex.h Mon Sep 23 16:06:53 2024 +0200 +++ b/Odbc/Plugins/OdbcIndex.h Tue Sep 24 15:04:21 2024 +0200 @@ -66,6 +66,11 @@ return true; } + bool HasAttachmentCustomDataSupport() const ORTHANC_OVERRIDE + { + return true; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE; diff -r 77c8544bbd7d -r f18e46d7dbf8 PostgreSQL/CMakeLists.txt --- a/PostgreSQL/CMakeLists.txt Mon Sep 23 16:06:53 2024 +0200 +++ b/PostgreSQL/CMakeLists.txt Tue Sep 24 15:04:21 2024 +0200 @@ -92,6 +92,7 @@ POSTGRESQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql POSTGRESQL_UPGRADE_REV1_TO_REV2 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql + POSTGRESQL_UPGRADE_REV2_TO_REV3 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql ) diff -r 77c8544bbd7d -r f18e46d7dbf8 PostgreSQL/NEWS --- a/PostgreSQL/NEWS Mon Sep 23 16:06:53 2024 +0200 +++ b/PostgreSQL/NEWS Tue Sep 24 15:04:21 2024 +0200 @@ -8,6 +8,7 @@ * Fix updates from plugin version 3.3 to latest version * Added support for ExtendedChanges: - changes?type=...&to=... +* Added support for customData in AttachedFiles * Performance optimizations (to be summarized before release): - using more prepared SQL statements: - InsertOrUpdateMetadata diff -r 77c8544bbd7d -r f18e46d7dbf8 PostgreSQL/Plugins/PostgreSQLIndex.cpp --- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -136,6 +136,7 @@ bool needToRunUpgradeFromUnknownToV1 = false; bool needToRunUpgradeV1toV2 = false; + bool needToRunUpgradeV2toV3 = false; int revision; if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) @@ -150,6 +151,12 @@ LOG(WARNING) << "DatabasePatchLevel is 1"; needToRunUpgradeFromUnknownToV1 = true; needToRunUpgradeV1toV2 = true; + needToRunUpgradeV2toV3 = true; + } + else if (revision == 2) + { + LOG(WARNING) << "DatabasePatchLevel is 2"; + needToRunUpgradeV2toV3 = true; } int hasTrigram = 0; @@ -203,7 +210,21 @@ (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2); t.GetDatabaseTransaction().ExecuteMultiLines(query); - // apply all idempotent changes that are in the PrepareIndexV2 + // apply all idempotent changes that are in the PrepareIndex + ApplyPrepareIndex(t, manager); + } + + if (needToRunUpgradeV2toV3) + { + LOG(WARNING) << "Upgrading DB schema from revision 2 to revision 3"; + + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_TO_REV3); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + + // apply all idempotent changes that are in the PrepareIndex (update triggers + set Patch level to 3) ApplyPrepareIndex(t, manager); } } diff -r 77c8544bbd7d -r f18e46d7dbf8 PostgreSQL/Plugins/PostgreSQLIndex.h --- a/PostgreSQL/Plugins/PostgreSQLIndex.h Mon Sep 23 16:06:53 2024 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Tue Sep 24 15:04:21 2024 +0200 @@ -63,6 +63,11 @@ return true; } + virtual bool HasAttachmentCustomDataSupport() const ORTHANC_OVERRIDE + { + return true; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE; diff -r 77c8544bbd7d -r f18e46d7dbf8 PostgreSQL/Plugins/SQL/PrepareIndex.sql --- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Sep 23 16:06:53 2024 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Tue Sep 24 15:04:21 2024 +0200 @@ -52,6 +52,7 @@ uncompressedHash VARCHAR(40), compressedHash VARCHAR(40), revision INTEGER, + customData TEXT, -- new in schema rev 3 PRIMARY KEY(id, fileType) ); @@ -275,7 +276,9 @@ uncompressedSize BIGINT, compressionType INTEGER, uncompressedHash VARCHAR(40), - compressedHash VARCHAR(40) + compressedHash VARCHAR(40), + revision INTEGER, + customData TEXT ); RESET client_min_messages; @@ -293,7 +296,8 @@ INSERT INTO DeletedFiles VALUES (old.uuid, old.filetype, old.compressedSize, old.uncompressedSize, old.compressionType, - old.uncompressedHash, old.compressedHash); + old.uncompressedHash, old.compressedHash, + old.revision, old.customData); RETURN NULL; END; $body$ LANGUAGE plpgsql; @@ -553,7 +557,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); INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion -INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel +INSERT INTO GlobalProperties VALUES (4, 3); -- GlobalProperty_DatabasePatchLevel INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance -- this is actually the 3rd version of HasCreateInstance diff -r 77c8544bbd7d -r f18e46d7dbf8 PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql --- a/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Mon Sep 23 16:06:53 2024 +0200 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Tue Sep 24 15:04:21 2024 +0200 @@ -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) -- It actually contains only the changes that: -- can not be executed with an idempotent statement in SQL -- or would polute the PrepareIndex.sql diff -r 77c8544bbd7d -r f18e46d7dbf8 README --- a/README Mon Sep 23 16:06:53 2024 +0200 +++ b/README Tue Sep 24 15:04:21 2024 +0200 @@ -57,6 +57,69 @@ https://orthanc.uclouvain.be/book/developers/repositories.html +Development +----------- + +PostgreSQL +========== + +To quickly start a test PG server: + + docker run -p 5432:5432 --rm --env POSTGRES_HOST_AUTH_METHOD=trust postgres:13.4 + +And use this Orthanc configuration: + "PostgreSQL": { + "EnableIndex": true, + "EnableStorage": false, // DICOM files are stored in the Orthanc container in /var/lib/orthanc/db/ + "Host": "localhost", // the name of the PostgreSQL container + "Database": "postgres", // default database name in PostgreSQL container (no need to create it) + "Username": "postgres", // default user name in PostgreSQL container (no need to create it) + "Password": "postgres" + }, + +MySQL +===== + +To quickly start a test MySQL server: + + docker run -p 3306:3306 --rm --env MYSQL_PASSWORD=orthanc --env MYSQL_USER=orthanc --env MYSQL_DATABASE=orthanc --env MYSQL_ROOT_PASSWORD=pwd-root mysql:8.0 mysqld --default-authentication-plugin=mysql_native_password --log-bin-trust-function-creators=1 + +And use this Orthanc configuration: + "MySQL": { + "EnableIndex": true, + "EnableStorage": false, + "Host": "localhost", + "Database": "orthanc", + "Username": "orthanc", + "Password": "orthanc", + "UnixSocket": "" + }, + + +ODBC (SQL Server) +================= + +To quickly start a test MySQL server: + + docker run -e "ACCEPT_EULA=Y" --rm --env "SA_PASSWORD=yourStrong-Password" --entrypoint=bash -it -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest + +Then: + (sleep 15s && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P yourStrong-Password -Q 'CREATE DATABASE orthanctest') & /opt/mssql/bin/sqlservr + +And use this Orthanc configuration: + "Odbc" : { + "IndexConnectionString": "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;Database=orthanctest;Uid=sa;Pwd=yourStrong-Password;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;", + "EnableIndex": true, + "EnableStorage": false + } + + +SQLite +====== + +To quickly test the SQLite plugin, simply run orthanc and load the plugin (no configuration required). + + Licensing --------- diff -r 77c8544bbd7d -r f18e46d7dbf8 Resources/CMake/DatabasesPluginConfiguration.cmake diff -r 77c8544bbd7d -r f18e46d7dbf8 SQLite/CMakeLists.txt --- a/SQLite/CMakeLists.txt Mon Sep 23 16:06:53 2024 +0200 +++ b/SQLite/CMakeLists.txt Tue Sep 24 15:04:21 2024 +0200 @@ -55,6 +55,7 @@ EmbedResources( SQLITE_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/PrepareIndex.sql + SQLITE_INSTALL_CUSTOM_DATA ${CMAKE_SOURCE_DIR}/Plugins/InstallCustomData.sql ) if (EXISTS ${ORTHANC_SDK_ROOT}/orthanc/OrthancDatabasePlugin.proto) diff -r 77c8544bbd7d -r f18e46d7dbf8 SQLite/NEWS --- a/SQLite/NEWS Mon Sep 23 16:06:53 2024 +0200 +++ b/SQLite/NEWS Tue Sep 24 15:04:21 2024 +0200 @@ -1,3 +1,9 @@ +Pending changes in the mainline +=============================== + +* Added support for customData in AttachedFiles + + Pending changes in the mainline =============================== diff -r 77c8544bbd7d -r f18e46d7dbf8 SQLite/Plugins/InstallCustomData.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/SQLite/Plugins/InstallCustomData.sql Tue Sep 24 15:04:21 2024 +0200 @@ -0,0 +1,17 @@ + +-- Add new column for customData +ALTER TABLE AttachedFiles ADD COLUMN customData TEXT; +ALTER TABLE DeletedFiles ADD COLUMN revision INTEGER; +ALTER TABLE DeletedFiles ADD COLUMN customData TEXT; + + +DROP TRIGGER AttachedFileDeleted; + +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +BEGIN + INSERT INTO DeletedFiles VALUES(old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash, + old.revision, old.customData); +END; diff -r 77c8544bbd7d -r f18e46d7dbf8 SQLite/Plugins/SQLiteIndex.cpp --- a/SQLite/Plugins/SQLiteIndex.cpp Mon Sep 23 16:06:53 2024 +0200 +++ b/SQLite/Plugins/SQLiteIndex.cpp Tue Sep 24 15:04:21 2024 +0200 @@ -149,7 +149,22 @@ SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); } - if (revision != 1) + // install customData + if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel) + || revision == 1) + { + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::SQLITE_INSTALL_CUSTOM_DATA); + + t.GetDatabaseTransaction().ExecuteMultiLines(query); + + revision = 2; + SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); + } + + if (revision != 2) { LOG(ERROR) << "SQLite plugin is incompatible with database schema revision: " << revision; throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); diff -r 77c8544bbd7d -r f18e46d7dbf8 SQLite/Plugins/SQLiteIndex.h --- a/SQLite/Plugins/SQLiteIndex.h Mon Sep 23 16:06:53 2024 +0200 +++ b/SQLite/Plugins/SQLiteIndex.h Tue Sep 24 15:04:21 2024 +0200 @@ -55,6 +55,11 @@ return true; } + bool HasAttachmentCustomDataSupport() const ORTHANC_OVERRIDE + { + return true; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE;