Mercurial > hg > orthanc-databases

changeset 75:52c70007bb87 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new extension implemented for PostgreSQL: SetResourcesContent
author Sebastien Jodogne <s.jodogne@gmail.com>
date Sat, 05 Jan 2019 11:41:15 +0100
parents a4e440e65c68
children a1c6238b26f8
files Framework/Common/DatabaseManager.h Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h Framework/Plugins/OrthancCppDatabasePlugin.h PostgreSQL/NEWS
diffstat 5 files changed, 202 insertions(+), 6 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Common/DatabaseManager.h	Fri Jan 04 17:24:30 2019 +0100
+++ b/Framework/Common/DatabaseManager.h	Sat Jan 05 11:41:15 2019 +0100
@@ -208,6 +208,12 @@
 
       virtual ~StandaloneStatement();
 
+      void Execute()
+      {
+        Dictionary parameters;
+        Execute(parameters);
+      }
+
       void Execute(const Dictionary& parameters);
     };
   };
--- a/Framework/Plugins/IndexBackend.cpp	Fri Jan 04 17:24:30 2019 +0100
+++ b/Framework/Plugins/IndexBackend.cpp	Sat Jan 05 11:41:15 2019 +0100
@@ -705,14 +705,14 @@
 
       case Dialect_PostgreSQL:
         statement.reset(new DatabaseManager::CachedStatement(
-                        STATEMENT_FROM_HERE, GetManager(),
-                        "SELECT CAST(COUNT(*) AS BIGINT) FROM Resources WHERE resourceType=${type}"));
+                          STATEMENT_FROM_HERE, GetManager(),
+                          "SELECT CAST(COUNT(*) AS BIGINT) FROM Resources WHERE resourceType=${type}"));
         break;
 
       case Dialect_SQLite:
         statement.reset(new DatabaseManager::CachedStatement(
-                        STATEMENT_FROM_HERE, GetManager(),
-                        "SELECT COUNT(*) FROM Resources WHERE resourceType=${type}"));
+                          STATEMENT_FROM_HERE, GetManager(),
+                          "SELECT COUNT(*) FROM Resources WHERE resourceType=${type}"));
         break;
 
       default:
@@ -1719,4 +1719,145 @@
     }    
   }
 #endif
+
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
+  static void ExecuteSetResourcesContentTags(
+    DatabaseManager& manager,
+    const std::string& table,
+    const std::string& variablePrefix,
+    uint32_t count,
+    const OrthancPluginResourcesContentTags* tags)
+  {
+    std::string sql;
+    Dictionary args;
+    
+    for (uint32_t i = 0; i < count; i++)
+    {
+      std::string name = variablePrefix + boost::lexical_cast<std::string>(i);
+
+      args.SetUtf8Value(name, tags[i].value);
+      
+      std::string insert = ("(" + boost::lexical_cast<std::string>(tags[i].resource) + ", " +
+                           boost::lexical_cast<std::string>(tags[i].group) + ", " +
+                           boost::lexical_cast<std::string>(tags[i].element) + ", " +
+                           "${" + name + "})");
+
+      if (sql.empty())
+      {
+        sql = "INSERT INTO " + table + " VALUES " + insert;
+      }
+      else
+      {
+        sql += ", " + insert;
+      }
+    }
+
+    if (!sql.empty())
+    {
+      DatabaseManager::StandaloneStatement statement(manager, sql);
+
+      for (uint32_t i = 0; i < count; i++)
+      {
+        statement.SetParameterType(variablePrefix + boost::lexical_cast<std::string>(i),
+                                   ValueType_Utf8String);
+      }
+
+      statement.Execute(args);
+    }
+  }
+#endif
+  
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
+  static void ExecuteSetResourcesContentMetadata(
+    DatabaseManager& manager,
+    uint32_t count,
+    const OrthancPluginResourcesContentMetadata* metadata)
+  {
+    std::string sqlRemove;  // To overwrite    
+    std::string sqlInsert;
+    Dictionary args;
+    
+    for (uint32_t i = 0; i < count; i++)
+    {
+      std::string name = "m" + boost::lexical_cast<std::string>(i);
+
+      args.SetUtf8Value(name, metadata[i].value);
+      
+      std::string insert = ("(" + boost::lexical_cast<std::string>(metadata[i].resource) + ", " +
+                            boost::lexical_cast<std::string>(metadata[i].metadata) + ", " +
+                           "${" + name + "})");
+
+      std::string remove = ("(id=" + boost::lexical_cast<std::string>(metadata[i].resource) +
+                            " AND type=" + boost::lexical_cast<std::string>(metadata[i].metadata)
+                            + ")");
+
+      if (sqlInsert.empty())
+      {
+        sqlInsert = "INSERT INTO Metadata VALUES " + insert;
+      }
+      else
+      {
+        sqlInsert += ", " + insert;
+      }
+
+      if (sqlRemove.empty())
+      {
+        sqlRemove = "DELETE FROM Metadata WHERE " + remove;
+      }
+      else
+      {
+        sqlRemove += " OR " + remove;
+      }
+    }
+
+    if (!sqlRemove.empty())
+    {
+      DatabaseManager::StandaloneStatement statement(manager, sqlRemove);
+      statement.Execute();
+    }
+    
+    if (!sqlInsert.empty())
+    {
+      DatabaseManager::StandaloneStatement statement(manager, sqlInsert);
+
+      for (uint32_t i = 0; i < count; i++)
+      {
+        statement.SetParameterType("m" + boost::lexical_cast<std::string>(i),
+                                   ValueType_Utf8String);
+      }
+
+      statement.Execute(args);
+    }
+  }
+#endif
+  
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
+  // New primitive since Orthanc 1.5.2
+  void IndexBackend::SetResourcesContent(
+    uint32_t countIdentifierTags,
+    const OrthancPluginResourcesContentTags* identifierTags,
+    uint32_t countMainDicomTags,
+    const OrthancPluginResourcesContentTags* mainDicomTags,
+    uint32_t countMetadata,
+    const OrthancPluginResourcesContentMetadata* metadata)
+  {
+    /**
+     * TODO - PostgreSQL doesn't allow multiple commands in a prepared
+     * statement, so we execute 3 separate commands (for identifiers,
+     * main tags and metadata). Maybe MySQL does not suffer from the
+     * same limitation, to check.
+     **/
+    
+    ExecuteSetResourcesContentTags(GetManager(), "DicomIdentifiers", "i",
+                                   countIdentifierTags, identifierTags);
+
+    ExecuteSetResourcesContentTags(GetManager(), "MainDicomTags", "t",
+                                   countMainDicomTags, mainDicomTags);
+
+    ExecuteSetResourcesContentMetadata(GetManager(), countMetadata, metadata);
+  }
+#endif
 }
--- a/Framework/Plugins/IndexBackend.h	Fri Jan 04 17:24:30 2019 +0100
+++ b/Framework/Plugins/IndexBackend.h	Sat Jan 05 11:41:15 2019 +0100
@@ -265,5 +265,16 @@
                                  uint32_t limit,
                                  bool requestSomeInstance);
 #endif
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
+    // New primitive since Orthanc 1.5.2
+    virtual void SetResourcesContent(
+      uint32_t countIdentifierTags,
+      const OrthancPluginResourcesContentTags* identifierTags,
+      uint32_t countMainDicomTags,
+      const OrthancPluginResourcesContentTags* mainDicomTags,
+      uint32_t countMetadata,
+      const OrthancPluginResourcesContentMetadata* metadata);
+#endif
   };
 }
--- a/Framework/Plugins/OrthancCppDatabasePlugin.h	Fri Jan 04 17:24:30 2019 +0100
+++ b/Framework/Plugins/OrthancCppDatabasePlugin.h	Sat Jan 05 11:41:15 2019 +0100
@@ -510,6 +510,17 @@
       throw std::runtime_error("Not implemented");
     }
 #endif
+
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
+    virtual void SetResourcesContent(
+      uint32_t countIdentifierTags,
+      const OrthancPluginResourcesContentTags* identifierTags,
+      uint32_t countMainDicomTags,
+      const OrthancPluginResourcesContentTags* mainDicomTags,
+      uint32_t countMetadata,
+      const OrthancPluginResourcesContentMetadata* metadata) = 0;
+#endif
   };
 
 
@@ -1536,6 +1547,30 @@
     }
 #endif
 
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
+    static OrthancPluginErrorCode SetResourcesContent(
+      void* payload,
+      uint32_t countIdentifierTags,
+      const OrthancPluginResourcesContentTags* identifierTags,
+      uint32_t countMainDicomTags,
+      const OrthancPluginResourcesContentTags* mainDicomTags,
+      uint32_t countMetadata,
+      const OrthancPluginResourcesContentMetadata* metadata)
+    {
+      IDatabaseBackend* backend = reinterpret_cast<IDatabaseBackend*>(payload);
+
+      try
+      {
+        backend->SetResourcesContent(countIdentifierTags, identifierTags,
+                                     countMainDicomTags, mainDicomTags,
+                                     countMetadata, metadata);
+        return OrthancPluginErrorCode_Success;
+      }
+      ORTHANC_PLUGINS_DATABASE_CATCH      
+    }
+#endif    
+
     
   public:
     /**
@@ -1617,11 +1652,13 @@
 #endif
 
 #if ORTHANC_PLUGINS_HAS_DATABASE_OPTIMIZATIONS_1 == 1
-      extensions.lookupResources = LookupResources;   // New in Orthanc 1.5.2 (fast lookup)
+      // Optimizations brought by Orthanc 1.5.2
+      extensions.lookupResources = LookupResources;          // Fast lookup
+      extensions.setResourcesContent = SetResourcesContent;  // Fast setting tags/metadata
 
       if (backend.HasCreateInstance())
       {
-        extensions.createInstance = CreateInstance;   // New in Orthanc 1.5.2 (fast create)
+        extensions.createInstance = CreateInstance;          // Fast creation of resources
       }
       
       performanceWarning = false;
--- a/PostgreSQL/NEWS	Fri Jan 04 17:24:30 2019 +0100
+++ b/PostgreSQL/NEWS	Sat Jan 05 11:41:15 2019 +0100
@@ -1,6 +1,7 @@
 Pending changes in the mainline
 ===============================
 
+* Database optimizations thanks to the new primitives in Orthanc SDK 
 * Fix Debian issue #906771 (Uncaught exception prevents db intialization
   (likely related to pg_trgm))
 * Fix: Catching exceptions in destructors