[mssql] Compile make_datetime, make_date, make_time

Allows efficient use of SQL server tables along with temporal mode
qgis · May 22, 2020 · b17da05 · b17da05
1 parent 0618b2e
commit b17da05
Show file tree

Hide file tree

Showing 5 changed files with 95 additions and 43 deletions.
diff --git a/src/core/qgsvectorlayerexporter.cpp b/src/core/qgsvectorlayerexporter.cpp
@@ -82,7 +82,7 @@ QgsVectorLayerExporter::QgsVectorLayerExporter( const QString &uri,
 
   mAttributeCount++;
 
-  QgsDebugMsg( QStringLiteral( "Created empty layer" ) );
+  QgsDebugMsgLevel( QStringLiteral( "Created empty layer" ), 2 );
 
   QString uriUpdated( uri );
   // HACK sorry...

diff --git a/src/providers/mssql/qgsmssqlexpressioncompiler.cpp b/src/providers/mssql/qgsmssqlexpressioncompiler.cpp
@@ -25,45 +25,70 @@ QgsMssqlExpressionCompiler::QgsMssqlExpressionCompiler( QgsMssqlFeatureSource *s
 
 QgsSqlExpressionCompiler::Result QgsMssqlExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
 {
-  if ( node->nodeType() == QgsExpressionNode::ntBinaryOperator )
+  switch ( node->nodeType() )
   {
-    const QgsExpressionNodeBinaryOperator *bin( static_cast<const QgsExpressionNodeBinaryOperator *>( node ) );
-    switch ( bin->op() )
+    case QgsExpressionNode::ntBinaryOperator:
     {
-      // special handling
-      case QgsExpressionNodeBinaryOperator::boPow:
-      case QgsExpressionNodeBinaryOperator::boRegexp:
-      case QgsExpressionNodeBinaryOperator::boConcat:
-        break;
-
-      default:
-        // fallback to default handling
-        return QgsSqlExpressionCompiler::compileNode( node, result );;
+      const QgsExpressionNodeBinaryOperator *bin( static_cast<const QgsExpressionNodeBinaryOperator *>( node ) );
+      switch ( bin->op() )
+      {
+        // special handling
+        case QgsExpressionNodeBinaryOperator::boPow:
+        case QgsExpressionNodeBinaryOperator::boRegexp:
+        case QgsExpressionNodeBinaryOperator::boConcat:
+          break;
+
+        default:
+          // fallback to default handling
+          return QgsSqlExpressionCompiler::compileNode( node, result );;
+      }
+
+      QString op1, op2;
+
+      Result result1 = compileNode( bin->opLeft(), op1 );
+      Result result2 = compileNode( bin->opRight(), op2 );
+      if ( result1 == Fail || result2 == Fail )
+        return Fail;
+
+      switch ( bin->op() )
+      {
+        case QgsExpressionNodeBinaryOperator::boPow:
+          result = QStringLiteral( "power(%1,%2)" ).arg( op1, op2 );
+          return result1 == Partial || result2 == Partial ? Partial : Complete;
+
+        case QgsExpressionNodeBinaryOperator::boRegexp:
+          return Fail; //not supported, regexp syntax is too different to Qt
+
+        case QgsExpressionNodeBinaryOperator::boConcat:
+          result = QStringLiteral( "%1 + %2" ).arg( op1, op2 );
+          return result1 == Partial || result2 == Partial ? Partial : Complete;
+
+        default:
+          break;
+      }
+
+      break;
     }
 
-    QString op1, op2;
-
-    Result result1 = compileNode( bin->opLeft(), op1 );
-    Result result2 = compileNode( bin->opRight(), op2 );
-    if ( result1 == Fail || result2 == Fail )
-      return Fail;
-
-    switch ( bin->op() )
+    case QgsExpressionNode::ntFunction:
     {
-      case QgsExpressionNodeBinaryOperator::boPow:
-        result = QStringLiteral( "power(%1,%2)" ).arg( op1, op2 );
-        return result1 == Partial || result2 == Partial ? Partial : Complete;
-
-      case QgsExpressionNodeBinaryOperator::boRegexp:
-        return Fail; //not supported, regexp syntax is too different to Qt
-
-      case QgsExpressionNodeBinaryOperator::boConcat:
-        result = QStringLiteral( "%1 + %2" ).arg( op1, op2 );
-        return result1 == Partial || result2 == Partial ? Partial : Complete;
-
-      default:
-        break;
+      const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
+      QgsExpressionFunction *fd = QgsExpression::Functions()[n->fnIndex()];
+
+      if ( fd->name() == QLatin1String( "make_datetime" ) || fd->name() == QLatin1String( "make_date" ) || fd->name() == QLatin1String( "make_time" ) )
+      {
+        const auto constList = n->args()->list();
+        for ( const QgsExpressionNode *ln : constList )
+        {
+          if ( ln->nodeType() != QgsExpressionNode::ntLiteral )
+            return Fail;
+        }
+      }
+      return QgsSqlExpressionCompiler::compileNode( node, result );
     }
+
+    default:
+      break;
   }
 
   //fallback to default handling
@@ -138,9 +163,39 @@ static const QMap<QString, QString> FUNCTION_NAMES_SQL_FUNCTIONS_MAP
   { "trim", "trim" },
   { "lower", "lower" },
   { "upper", "upper" },
+  { "make_datetime", "" },
+  { "make_date", "" },
+  { "make_time", "" },
 };
 
 QString QgsMssqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
 {
   return FUNCTION_NAMES_SQL_FUNCTIONS_MAP.value( fnName, QString() );
 }
+
+QStringList QgsMssqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
+{
+  QStringList args( fnArgs );
+  if ( fnName == QLatin1String( "make_datetime" ) )
+  {
+    args = QStringList( QStringLiteral( "'%1-%2-%3T%4:%5:%6Z'" ).arg( args[0].rightJustified( 4, '0' ) )
+                        .arg( args[1].rightJustified( 2, '0' ) )
+                        .arg( args[2].rightJustified( 2, '0' ) )
+                        .arg( args[3].rightJustified( 2, '0' ) )
+                        .arg( args[4].rightJustified( 2, '0' ) )
+                        .arg( args[5].rightJustified( 2, '0' ) ) );
+  }
+  else if ( fnName == QLatin1String( "make_date" ) )
+  {
+    args = QStringList( QStringLiteral( "'%1-%2-%3'" ).arg( args[0].rightJustified( 4, '0' ) )
+                        .arg( args[1].rightJustified( 2, '0' ) )
+                        .arg( args[2].rightJustified( 2, '0' ) ) );
+  }
+  else if ( fnName == QLatin1String( "make_time" ) )
+  {
+    args = QStringList( QStringLiteral( "'%1:%2:%3'" ).arg( args[0].rightJustified( 2, '0' ) )
+                        .arg( args[1].rightJustified( 2, '0' ) )
+                        .arg( args[2].rightJustified( 2, '0' ) ) );
+  }
+  return args;
+}
diff --git a/src/providers/mssql/qgsmssqlexpressioncompiler.h b/src/providers/mssql/qgsmssqlexpressioncompiler.h
@@ -33,7 +33,7 @@ class QgsMssqlExpressionCompiler : public QgsSqlExpressionCompiler
     QString castToReal( const QString &value ) const override;
     QString castToInt( const QString &value ) const override;
     QString sqlFunctionFromFunctionName( const QString &fnName ) const override;
-
+    QStringList sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const override;
 };
 
 #endif // QGSMSSQLEXPRESSIONCOMPILER_H
diff --git a/src/providers/mssql/qgsmssqlprovider.cpp b/src/providers/mssql/qgsmssqlprovider.cpp
@@ -741,8 +741,10 @@ void QgsMssqlProvider::UpdateStatistics( bool estimate ) const
       return;
     }
   }
-
-  QgsDebugMsg( query.lastError().text() );
+  else
+  {
+    QgsDebugMsg( query.lastError().text() );
+  }
 
   // If we can't find the extents in the spatial index table just do what we normally do.
   bool readAllGeography = false;
@@ -2317,7 +2319,7 @@ QString QgsMssqlProviderMetadata::loadStyle( const QString &uri, QString &errCau
 
   if ( !query.exec( selectQmlQuery ) )
   {
-    QgsDebugMsg( QStringLiteral( "Load of style failed" ) );
+    QgsDebugMsgLevel( QStringLiteral( "Load of style failed" ), 2 );
     QString msg = query.lastError().text();
     errCause = msg;
     QgsDebugMsg( msg );

diff --git a/tests/src/python/test_provider_mssql.py b/tests/src/python/test_provider_mssql.py
@@ -215,13 +215,8 @@ def uncompiledFilters(self):
             'overlaps(buffer($geometry,1),geom_from_wkt( \'Polygon ((-75.1 76.1, -75.1 81.6, -68.8 81.6, -68.8 76.1, -75.1 76.1))\'))',
             'intersects(centroid($geometry),geom_from_wkt( \'Polygon ((-74.4 78.2, -74.4 79.1, -66.8 79.1, -66.8 78.2, -74.4 78.2))\'))',
             'intersects(point_on_surface($geometry),geom_from_wkt( \'Polygon ((-74.4 78.2, -74.4 79.1, -66.8 79.1, -66.8 78.2, -74.4 78.2))\'))',
-            '"dt" <= make_datetime(2020, 5, 4, 12, 13, 14)',
-            '"dt" < make_date(2020, 5, 4)',
             '"dt" = to_datetime(\'000www14ww13ww12www4ww5ww2020\',\'zzzwwwsswwmmwwhhwwwdwwMwwyyyy\')',
-            '"date" <= make_datetime(2020, 5, 4, 12, 13, 14)',
-            '"date" >= make_date(2020, 5, 4)',
             '"date" = to_date(\'www4ww5ww2020\',\'wwwdwwMwwyyyy\')',
-            '"time" >= make_time(12, 14, 14)',
             '"time" = to_time(\'000www14ww13ww12www\',\'zzzwwwsswwmmwwhhwww\')'
         ])
         return filters