[mssql] Compile some simple expression functions

Should speed up lots of querying operations on sql server layers
qgis · Oct 5, 2018 · 10095ce · 10095ce
1 parent 1d9086c
commit 10095ce
Show file tree

Hide file tree

Showing 3 changed files with 40 additions and 30 deletions.
diff --git a/src/providers/mssql/qgsmssqlexpressioncompiler.cpp b/src/providers/mssql/qgsmssqlexpressioncompiler.cpp
@@ -88,3 +88,38 @@ QString QgsMssqlExpressionCompiler::castToInt( const QString &value ) const
 {
   return QStringLiteral( "CAST((%1) AS integer)" ).arg( value );
 }
+
+static const QMap<QString, QString> FUNCTION_NAMES_SQL_FUNCTIONS_MAP
+{
+  { "sqrt", "sqrt" },
+  { "abs", "abs" },
+  { "cos", "cos" },
+  { "sin", "sin" },
+  { "tan", "tan" },
+  { "radians", "radians" },
+  { "degrees", "degrees" },
+  { "acos", "acos" },
+  { "asin", "asin" },
+  { "atan", "atan" },
+  { "atan2", "atn2" },
+  { "exp", "exp" },
+  { "ln", "ln" },
+  { "log", "log" },
+  { "log10", "log10" },
+  { "pi", "pi" },
+  { "round", "round" },
+  { "floor", "floor" },
+  { "ceil", "ceiling" },
+  { "char", "char" },
+#if 0 // should be possible if/when mssql compiler handles case sensitive string matches
+  { "coalesce", "coalesce" },
+#endif
+  { "trim", "trim" },
+  { "lower", "lower" },
+  { "upper", "upper" },
+};
+
+QString QgsMssqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
+{
+  return FUNCTION_NAMES_SQL_FUNCTIONS_MAP.value( fnName, QString() );
+}
diff --git a/src/providers/mssql/qgsmssqlexpressioncompiler.h b/src/providers/mssql/qgsmssqlexpressioncompiler.h
@@ -31,6 +31,7 @@ class QgsMssqlExpressionCompiler : public QgsSqlExpressionCompiler
     QString quotedValue( const QVariant &value, bool &ok ) override;
     QString castToReal( const QString &value ) const override;
     QString castToInt( const QString &value ) const override;
+    QString sqlFunctionFromFunctionName( const QString &fnName ) const override;
 
 };
 

diff --git a/tests/src/python/test_provider_mssql.py b/tests/src/python/test_provider_mssql.py
@@ -68,7 +68,10 @@ def partiallyCompiledFilters(self):
             'name ILIKE \'%pp%\'',
             '"name" || \' \' || "name" = \'Orange Orange\'',
             '"name" || \' \' || "cnt" = \'Orange 100\'',
-            '"name"="name2"'
+            '"name"="name2"',
+            'lower(name) = \'apple\'',
+            'upper(name) = \'APPLE\'',
+            'name = trim(\'   Apple   \')'
         ])
         return filters
 
@@ -102,36 +105,7 @@ def uncompiledFilters(self):
             'not name = \'Apple\' or not name = \'Apple\'',
             'not name = \'Apple\' and pk = 4',
             'not name = \'Apple\' and not pk = 4',
-            'sqrt(pk) >= 2',
-            'radians(cnt) < 2',
-            'degrees(pk) <= 200',
-            'abs(cnt) <= 200',
-            'cos(pk) < 0',
-            'sin(pk) < 0',
-            'tan(pk) < 0',
-            'acos(-1) < pk',
-            'asin(1) < pk',
-            'atan(3.14) < pk',
-            'atan2(3.14, pk) < 1',
-            'exp(pk) < 10',
-            'ln(pk) <= 1',
-            'log(3, pk) <= 1',
-            'log10(pk) < 0.5',
-            'pk < pi()',
-            'round(3.14) <= pk',
-            'round(0.314,1) * 10 = pk',
-            'floor(3.14) <= pk',
-            'ceil(3.14) <= pk',
-            'pk < pi()',
-            'round(cnt / 66.67) <= 2',
-            'floor(cnt / 66.67) <= 2',
-            'ceil(cnt / 66.67) <= 2',
-            'pk < pi() / 2',
-            'pk = char(51)',
             'pk = coalesce(NULL,3,4)',
-            'lower(name) = \'apple\'',
-            'upper(name) = \'APPLE\'',
-            'name = trim(\'   Apple   \')',
             'x($geometry) < -70',
             'y($geometry) > 70',
             'xmin($geometry) < -70',