Mercurial > hg > orthanc-databases

changeset 795:14ca14e0fd1e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fixes for sqlite
author Alain Mazy <am@orthanc.team>
date Thu, 22 Jan 2026 07:01:09 +0100
parents b3ee20f1bb83
children 0f0c9103fea8
files Framework/Plugins/ISqlLookupFormatter.cpp Framework/Plugins/ISqlLookupFormatter.h Framework/Plugins/IndexBackend.cpp Framework/SQLite/SQLiteDatabase.cpp Framework/SQLite/SQLiteDatabase.h Resources/CMake/DatabasesFrameworkConfiguration.cmake SQLite/Plugins/IndexPlugin.cpp
diffstat 7 files changed, 143 insertions(+), 95 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Plugins/ISqlLookupFormatter.cpp	Tue Jan 20 16:41:54 2026 +0100
+++ b/Framework/Plugins/ISqlLookupFormatter.cpp	Thu Jan 22 07:01:09 2026 +0100
@@ -135,7 +135,7 @@
         }
         else
         {
-          comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
+          comparison = formatter.FormatLower(tag + ".value") + op + formatter.FormatLower(parameter);
         }
 
         break;
@@ -158,7 +158,7 @@
           }
           else
           {
-            comparison += "lower(" + parameter + ")";
+            comparison += formatter.FormatLower(parameter);
           }
         }
 
@@ -168,7 +168,7 @@
         }
         else
         {
-          comparison = "lower(" +  tag + ".value) IN (" + comparison + ")";
+          comparison = formatter.FormatLower(tag + ".value") + " IN (" + comparison + ")";
         }
 
         break;
@@ -188,45 +188,7 @@
         }
         else
         {
-          std::string escaped;
-          escaped.reserve(value.size());
-
-          for (size_t i = 0; i < value.size(); i++)
-          {
-            if (value[i] == '*')
-            {
-              escaped += "%";
-            }
-            else if (value[i] == '?')
-            {
-              escaped += "_";
-            }
-            else if (value[i] == '%')
-            {
-              escaped += "\\%";
-            }
-            else if (value[i] == '_')
-            {
-              escaped += "\\_";
-            }
-            else if (value[i] == '\\')
-            {
-              escaped += "\\\\";
-            }
-            else if (escapeBrackets && value[i] == '[')
-            {
-              escaped += "\\[";
-            }
-            else if (escapeBrackets && value[i] == ']')
-            {
-              escaped += "\\]";
-            }
-            else
-            {
-              escaped += value[i];
-            }
-          }
-
+          std::string escaped = formatter.FormatWildcardsForLike(value);
           std::string parameter = formatter.GenerateParameter(escaped);
           comparison = formatter.FormatLike(isCaseSensitive,
                                             tag + ".value",
@@ -545,7 +507,7 @@
         }
         else
         {
-          comparison = " AND lower(value) " + op + " lower(" + parameter + ")";
+          comparison = " AND " + formatter.FormatLower("value") + op + formatter.FormatLower(parameter);
         }
 
         break;
@@ -564,7 +526,7 @@
           }
           else
           {
-            comparisonValues.push_back("lower(" + parameter + ")");
+            comparisonValues.push_back(formatter.FormatLower(parameter));
           }
         }
 
@@ -576,7 +538,7 @@
         }
         else
         {
-          comparison = " AND lower(value) IN (" + values + ")";
+          comparison = " AND " + formatter.FormatLower("value") + " IN (" + values + ")";
         }
 
         break;
@@ -596,45 +558,7 @@
         }
         else
         {
-          std::string escaped;
-          escaped.reserve(value.size());
-
-          for (size_t i = 0; i < value.size(); i++)
-          {
-            if (value[i] == '*')
-            {
-              escaped += "%";
-            }
-            else if (value[i] == '?')
-            {
-              escaped += "_";
-            }
-            else if (value[i] == '%')
-            {
-              escaped += "\\%";
-            }
-            else if (value[i] == '_')
-            {
-              escaped += "\\_";
-            }
-            else if (value[i] == '\\')
-            {
-              escaped += "\\\\";
-            }
-            else if (escapeBrackets && value[i] == '[')
-            {
-              escaped += "\\[";
-            }
-            else if (escapeBrackets && value[i] == ']')
-            {
-              escaped += "\\]";
-            }
-            else
-            {
-              escaped += value[i];
-            }
-          }
-
+          std::string escaped = formatter.FormatWildcardsForLike(value);
           std::string parameter = formatter.GenerateParameter(escaped);
           comparison = " AND " + formatter.FormatLike(constraint.IsCaseSensitive(),
                                                       "value",
--- a/Framework/Plugins/ISqlLookupFormatter.h	Tue Jan 20 16:41:54 2026 +0100
+++ b/Framework/Plugins/ISqlLookupFormatter.h	Thu Jan 22 07:01:09 2026 +0100
@@ -69,6 +69,10 @@
 
     virtual std::string FormatLike(bool isCaseSensitive, const std::string& a, const std::string& b) = 0;
 
+    virtual std::string FormatWildcardsForLike(const std::string& value) = 0;
+
+    virtual std::string FormatLower(const std::string& value) = 0;
+
     /**
      * Whether to escape '[' and ']', which is only needed for
      * MSSQL. New in Orthanc 1.10.0, from the following changeset:
--- a/Framework/Plugins/IndexBackend.cpp	Tue Jan 20 16:41:54 2026 +0100
+++ b/Framework/Plugins/IndexBackend.cpp	Thu Jan 22 07:01:09 2026 +0100
@@ -48,10 +48,15 @@
   }
 
 
-  static std::string ConvertWildcardToLike(const std::string& query)
+  static std::string ConvertWildcardToLike(const std::string& query, Dialect dialect)
   {
     std::string s = query;
 
+    if (dialect == Dialect_SQLite)
+    {
+      return s; // we are actually using GLOB that keeps the 'Unix' like wildcards
+    }
+
     for (size_t i = 0; i < s.size(); i++)
     {
       if (s[i] == '*')
@@ -1432,7 +1437,7 @@
 
     if (constraint == OrthancPluginIdentifierConstraint_Wildcard)
     {
-      args.SetUtf8Value("value", ConvertWildcardToLike(value));
+      args.SetUtf8Value("value", ConvertWildcardToLike(value, manager.GetDialect()));
     }
     else
     {
@@ -2226,6 +2231,72 @@
       }
     }
 
+    virtual std::string FormatLower(const std::string& value)
+    {
+      switch (dialect_)
+      {
+        case Dialect_SQLite:
+        {
+          return " lower_with_accents(" + value + ") ";
+        };
+        default:
+          return " lower(" + value + ") ";
+      }
+    }
+
+    virtual std::string FormatWildcardsForLike(const std::string& value)
+    {
+      bool escapeBrackets = IsEscapeBrackets();
+      std::string escaped;
+      escaped.reserve(value.size());
+
+      switch (dialect_)
+      {
+        case Dialect_SQLite:
+        {
+          escaped = value;  // SQLite uses GLOB instead of LIKE -> no need for escaping and we keep the 'Unix' like wildcards
+        }; break;
+        default:
+          for (size_t i = 0; i < value.size(); i++)
+          {
+            if (value[i] == '*')
+            {
+              escaped += "%";
+            }
+            else if (value[i] == '?')
+            {
+              escaped += "_";
+            }
+            else if (value[i] == '%')
+            {
+              escaped += "\\%";
+            }
+            else if (value[i] == '_')
+            {
+              escaped += "\\_";
+            }
+            else if (value[i] == '\\')
+            {
+              escaped += "\\\\";
+            }
+            else if (escapeBrackets && value[i] == '[')
+            {
+              escaped += "\\[";
+            }
+            else if (escapeBrackets && value[i] == ']')
+            {
+              escaped += "\\]";
+            }
+            else
+            {
+              escaped += value[i];
+            }
+          }
+      }
+      return escaped;
+    }
+
+
     virtual std::string FormatLike(bool isCaseSensitive, const std::string& a, const std::string& b)
     {
       switch (dialect_)
@@ -2241,6 +2312,7 @@
             return a + " LIKE " + b + " " + FormatWildcardEscape();
           }
         }; break;
+        case Dialect_MSSQL:
         case Dialect_PostgreSQL: // LIKE is case sensitive by default !
         {
           if (isCaseSensitive)
@@ -2252,8 +2324,17 @@
             return "lower(" + a + ") LIKE lower(" + b + ") " + FormatWildcardEscape();
           }
         }; break;
-        case Dialect_MSSQL:
         case Dialect_SQLite:
+        {
+          if (isCaseSensitive)
+          {
+            return a + " GLOB " + b + " "; // + FormatWildcardEscape();
+          }
+          else
+          {
+            return "lower_with_accents(" + a + ") GLOB lower_with_accents(" + b + ") "; // + FormatWildcardEscape();
+          }
+        }; break;
         default:
           throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
       }
--- a/Framework/SQLite/SQLiteDatabase.cpp	Tue Jan 20 16:41:54 2026 +0100
+++ b/Framework/SQLite/SQLiteDatabase.cpp	Thu Jan 22 07:01:09 2026 +0100
@@ -28,6 +28,7 @@
 #include "../Common/ImplicitTransaction.h"
 
 #include <OrthancException.h>
+#include <Toolbox.h>
 
 namespace OrthancDatabases
 {
@@ -114,4 +115,44 @@
         throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
     }
   }
+
+  class LowerWithAccents : public Orthanc::SQLite::IScalarFunction
+  {
+  public:
+    LowerWithAccents()
+    {
+    }
+
+    virtual const char* GetName() const ORTHANC_OVERRIDE
+    {
+      return "lower_with_accents";
+    }
+
+    virtual unsigned int GetCardinality() const ORTHANC_OVERRIDE
+    {
+      return 1;
+    }
+
+    virtual void Compute(Orthanc::SQLite::FunctionContext& context) ORTHANC_OVERRIDE
+    {
+      std::string source = context.GetStringValue(0);
+      std::string modified = Orthanc::Toolbox::ToLowerCaseWithAccents(source);
+
+      context.SetStringResult(modified);
+    }
+
+  };
+
+  void SQLiteDatabase::OpenInMemory()
+  {
+    connection_.OpenInMemory();
+    connection_.Register(new LowerWithAccents());
+  }
+
+  void SQLiteDatabase::Open(const std::string& path)
+  {
+    connection_.Open(path);
+    connection_.Register(new LowerWithAccents());
+  }
+
 }
--- a/Framework/SQLite/SQLiteDatabase.h	Tue Jan 20 16:41:54 2026 +0100
+++ b/Framework/SQLite/SQLiteDatabase.h	Thu Jan 22 07:01:09 2026 +0100
@@ -39,15 +39,9 @@
     Orthanc::SQLite::Connection  connection_;
     
   public:
-    void OpenInMemory()
-    {
-      connection_.OpenInMemory();
-    }
+    void OpenInMemory();
 
-    void Open(const std::string& path)
-    {
-      connection_.Open(path);
-    }
+    void Open(const std::string& path);
     
     Orthanc::SQLite::Connection& GetObject()
     {
--- a/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Tue Jan 20 16:41:54 2026 +0100
+++ b/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Thu Jan 22 07:01:09 2026 +0100
@@ -26,6 +26,7 @@
 
 if (ENABLE_SQLITE_BACKEND)
   set(ENABLE_SQLITE ON)
+  set(ENABLE_LOCALE ON)      # iconv is needed for lower_with_accents
 endif()
 
 if (ENABLE_POSTGRESQL_BACKEND)
--- a/SQLite/Plugins/IndexPlugin.cpp	Tue Jan 20 16:41:54 2026 +0100
+++ b/SQLite/Plugins/IndexPlugin.cpp	Thu Jan 22 07:01:09 2026 +0100
@@ -25,6 +25,7 @@
 #include "../../Framework/Plugins/PluginInitialization.h"
 
 #include <Logging.h>
+#include <Toolbox.h>
 
 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
 #  include <google/protobuf/any.h>
@@ -42,6 +43,8 @@
     GOOGLE_PROTOBUF_VERIFY_VERSION;
 #endif
 
+    Orthanc::Toolbox::InitializeGlobalLocale(NULL);
+
     if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "SQLite", true))
     {
       return -1;