qgssqlexpressioncompiler.cpp

Go to the documentation of this file.

/***************************************************************************
                             qgssqlexpressioncompiler.cpp
                             ----------------------------
    begin                : November 2015
    copyright            : (C) 2015 Nyall Dawson
    email                : nyall dot dawson at gmail dot com
 ***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
 
#include "qgssqlexpressioncompiler.h"
 
#include "qgsexpression.h"
#include "qgsexpressionfunction.h"
#include "qgsexpressionnodeimpl.h"
#include "qgsvariantutils.h"
 
#include <QString>
 
using namespace Qt::StringLiterals;
 
QgsSqlExpressionCompiler::QgsSqlExpressionCompiler( const QgsFields &fields, Flags flags, bool ignoreStaticNodes )
  : mFields( fields )
  , mFlags( flags )
  , mIgnoreStaticNodes( ignoreStaticNodes )
{
}
 
QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::compile( const QgsExpression *exp )
{
  if ( exp->rootNode() )
    return compileNode( exp->rootNode(), mResult );
  else
    return Fail;
}
 
QString QgsSqlExpressionCompiler::result()
{
  return mResult;
}
 
bool QgsSqlExpressionCompiler::opIsStringComparison( QgsExpressionNodeBinaryOperator::BinaryOperator op )
{
  if ( op == QgsExpressionNodeBinaryOperator::BinaryOperator::boILike ||
       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boLike ||
       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotILike ||
       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotLike ||
       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boRegexp )
    return true;
  else
    return false;
}
 
QString QgsSqlExpressionCompiler::quotedIdentifier( const QString &identifier )
{
  QString quoted = identifier;
  quoted.replace( '"', "\"\""_L1 );
  quoted = quoted.prepend( '\"' ).append( '\"' );
  return quoted;
}
 
QString QgsSqlExpressionCompiler::quotedValue( const QVariant &value, bool &ok )
{
  ok = true;
 
  if ( QgsVariantUtils::isNull( value ) )
    return u"NULL"_s;
 
  switch ( value.userType() )
  {
    case QMetaType::Type::Int:
    case QMetaType::Type::LongLong:
    case QMetaType::Type::Double:
      return value.toString();
 
    case QMetaType::Type::Bool:
      return value.toBool() ? u"TRUE"_s : u"FALSE"_s;
 
    default:
    case QMetaType::Type::QString:
      QString v = value.toString();
      v.replace( '\'', "''"_L1 );
      if ( v.contains( '\\' ) )
        return v.replace( '\\', "\\\\"_L1 ).prepend( "E'" ).append( '\'' );
      else
        return v.prepend( '\'' ).append( '\'' );
  }
}
 
QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
{
  const QgsSqlExpressionCompiler::Result staticRes = replaceNodeByStaticCachedValueIfPossible( node, result );
  if ( staticRes != Fail )
    return staticRes;
 
  // This is just to identify the most simple cases where nodes are numeric
  std::function<bool( const QgsExpressionNode * )> nodeIsNumeric;
  nodeIsNumeric = [this, &nodeIsNumeric]( const QgsExpressionNode * node )
  {
    const QgsExpressionNode::NodeType nodeType { node->nodeType() };
 
    switch ( nodeType )
    {
      case QgsExpressionNode::ntColumnRef:
      {
        const QgsExpressionNodeColumnRef *col = static_cast<const QgsExpressionNodeColumnRef *>( node );
        const int idx = mFields.indexFromName( col->name() );
        return idx >= 0 && QgsVariantUtils::isNumericType( mFields[idx].type() );
      }
      case QgsExpressionNode::ntLiteral:
      {
        const QgsExpressionNodeLiteral *lit = static_cast<const QgsExpressionNodeLiteral *>( node );
        return QgsVariantUtils::isNumericType( static_cast< QMetaType::Type >( lit->value().userType() ) );
      }
      case QgsExpressionNode::ntBinaryOperator:
      {
        const QgsExpressionNodeBinaryOperator *op = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
        return nodeIsNumeric( op->opLeft() ) && nodeIsNumeric( op->opRight() );
      }
      case QgsExpressionNode::ntUnaryOperator:
      {
        const QgsExpressionNodeUnaryOperator *op = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
        return nodeIsNumeric( op->operand() );
      }
 
      default:
        return false;
    }
 
  };
 
  switch ( node->nodeType() )
  {
    case QgsExpressionNode::ntUnaryOperator:
    {
      const QgsExpressionNodeUnaryOperator *n = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
      switch ( n->op() )
      {
        case QgsExpressionNodeUnaryOperator::uoNot:
        {
          QString right;
          if ( compileNode( n->operand(), right ) == Complete )
          {
            result = "( NOT " + right + ')';
            return Complete;
          }
 
          return Fail;
        }
 
        case QgsExpressionNodeUnaryOperator::uoMinus:
        {
          if ( mFlags.testFlag( NoUnaryMinus ) )
            return Fail;
 
          QString right;
          if ( compileNode( n->operand(), right ) == Complete )
          {
            result = "( - (" + right + "))";
            return Complete;
          }
 
          return Fail;
        }
      }
 
      break;
    }
 
    case QgsExpressionNodeBinaryOperator::ntBinaryOperator:
    {
      const QgsExpressionNodeBinaryOperator *n = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
 
      QString op;
      bool partialCompilation = false;
      bool failOnPartialNode = false;
      switch ( n->op() )
      {
        case QgsExpressionNodeBinaryOperator::boEQ:
          if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->opLeft()->nodeType() == QgsExpressionNode::ntColumnRef && n->opRight()->nodeType() == QgsExpressionNode::ntColumnRef )
          {
            // equality between column refs results in a partial compilation, since provider is performing
            // case-insensitive matches between strings
            partialCompilation = true;
          }
 
          op = u"="_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boGE:
          op = u">="_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boGT:
          op = u">"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boLE:
          op = u"<="_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boLT:
          op = u"<"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boIs:
          op = u"IS"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boIsNot:
          op = u"IS NOT"_s;
          failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
          break;
 
        case QgsExpressionNodeBinaryOperator::boLike:
          op = u"LIKE"_s;
          partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
          break;
 
        case QgsExpressionNodeBinaryOperator::boILike:
          if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
            op = u"LIKE"_s;
          else
            op = u"ILIKE"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boNotLike:
          op = u"NOT LIKE"_s;
          partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
          failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
          break;
 
        case QgsExpressionNodeBinaryOperator::boNotILike:
          failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
          if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
            op = u"NOT LIKE"_s;
          else
            op = u"NOT ILIKE"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boOr:
          if ( mFlags.testFlag( NoNullInBooleanLogic ) )
          {
            if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
              return Fail;
          }
 
          op = u"OR"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boAnd:
          if ( mFlags.testFlag( NoNullInBooleanLogic ) )
          {
            if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
              return Fail;
          }
 
          op = u"AND"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boNE:
          failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
          op = u"<>"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boMul:
          op = u"*"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boPlus:
        {
          const QgsExpressionNodeBinaryOperator *nodeOp = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
          if ( nodeIsNumeric( nodeOp->opLeft() ) && nodeIsNumeric( nodeOp->opRight() ) )
          {
            op = u"+"_s;
          }
          break;
        }
 
        case QgsExpressionNodeBinaryOperator::boMinus:
          op = u"-"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boDiv:
          op = u"/"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boMod:
          op = u"%"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boConcat:
          op = u"||"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boIntDiv:
          op = u"/"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boPow:
          op = u"^"_s;
          break;
 
        case QgsExpressionNodeBinaryOperator::boRegexp:
          op = u"~"_s;
          break;
      }
 
      if ( op.isNull() )
        return Fail;
 
      QString left;
      const Result lr( compileNode( n->opLeft(), left ) );
 
      if ( opIsStringComparison( n ->op() ) )
        left = castToText( left );
 
      QString right;
      const Result rr( compileNode( n->opRight(), right ) );
 
      if ( failOnPartialNode && ( lr == Partial || rr == Partial ) )
        return Fail;
 
      if ( n->op() == QgsExpressionNodeBinaryOperator::boDiv && mFlags.testFlag( IntegerDivisionResultsInInteger ) )
      {
        right = castToReal( right );
        if ( right.isEmpty() )
        {
          // not supported
          return Fail;
        }
      }
 
      result = '(' + left + ' ' + op + ' ' + right + ')';
      if ( n->op() == QgsExpressionNodeBinaryOperator::boIntDiv )
      {
        result = castToInt( result );
        if ( result.isEmpty() )
        {
          // not supported
          return Fail;
        }
      }
 
      if ( lr == Complete && rr == Complete )
        return ( partialCompilation ? Partial : Complete );
      else if ( ( lr == Partial && rr == Complete ) || ( lr == Complete && rr == Partial ) || ( lr == Partial && rr == Partial ) )
        return Partial;
      else
        return Fail;
    }
 
    case QgsExpressionNode::ntBetweenOperator:
    {
      const QgsExpressionNodeBetweenOperator *n = static_cast<const QgsExpressionNodeBetweenOperator *>( node );
      QString res;
      Result betweenResult = Complete;
 
      const Result rn = compileNode( n->node(), res );
      if ( rn == Complete || rn == Partial )
      {
        if ( rn == Partial )
        {
          betweenResult = Partial;
        }
      }
      else
      {
        return rn;
      }
 
      QString s;
      const Result rl = compileNode( n->lowerBound(), s );
      if ( rl == Complete || rl == Partial )
      {
        if ( rl == Partial )
        {
          betweenResult = Partial;
        }
      }
      else
      {
        return rl;
      }
 
      res.append( n->negate() ? u" NOT BETWEEN %1"_s.arg( s ) : u" BETWEEN %1"_s.arg( s ) );
 
      const Result rh = compileNode( n->higherBound(), s );
      if ( rh == Complete || rh == Partial )
      {
        if ( rh == Partial )
        {
          betweenResult = Partial;
        }
      }
      else
      {
        return rh;
      }
 
      res.append( u" AND %1"_s.arg( s ) );
      result = res;
      return betweenResult;
    }
 
    case QgsExpressionNode::ntLiteral:
    {
      const QgsExpressionNodeLiteral *n = static_cast<const QgsExpressionNodeLiteral *>( node );
      bool ok = false;
      if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->value().userType() == QMetaType::Type::QString )
      {
        // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
        // double check results using QGIS' expression engine
        result = quotedValue( n->value(), ok );
        return ok ? Partial : Fail;
      }
      else
      {
        result = quotedValue( n->value(), ok );
        return ok ? Complete : Fail;
      }
    }
 
    case QgsExpressionNode::ntColumnRef:
    {
      const QgsExpressionNodeColumnRef *n = static_cast<const QgsExpressionNodeColumnRef *>( node );
 
      // QGIS expressions don't care about case sensitive field naming, so we match case insensitively here to the
      // layer's fields and then retrieve the actual case of the field name for use in the compilation
      const int fieldIndex = mFields.lookupField( n->name() );
      if ( fieldIndex == -1 )
        // Not a provider field
        return Fail;
 
      result = quotedIdentifier( mFields.at( fieldIndex ).name() );
 
      return Complete;
    }
 
    case QgsExpressionNode::ntInOperator:
    {
      const QgsExpressionNodeInOperator *n = static_cast<const QgsExpressionNodeInOperator *>( node );
      QStringList list;
 
      Result inResult = Complete;
      const auto constList = n->list()->list();
      for ( const QgsExpressionNode *ln : constList )
      {
        QString s;
        const Result r = compileNode( ln, s );
        if ( r == Complete || r == Partial )
        {
          list << s;
          if ( r == Partial )
            inResult = Partial;
        }
        else
          return r;
      }
 
      QString nd;
      const Result rn = compileNode( n->node(), nd );
      if ( rn != Complete && rn != Partial )
        return rn;
 
      result = u"%1 %2IN (%3)"_s.arg( nd, n->isNotIn() ? u"NOT "_s : QString(), list.join( ',' ) );
      return ( inResult == Partial || rn == Partial ) ? Partial : Complete;
    }
 
    case QgsExpressionNode::ntFunction:
    {
      const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
      QgsExpressionFunction *fd = QgsExpression::Functions()[n->fnIndex()];
 
      // get sql function to compile node expression
      const QString nd = sqlFunctionFromFunctionName( fd->name() );
      // if no sql function the node can't be compiled
      if ( nd.isNull() )
        return Fail;
 
      // compile arguments
      QStringList args;
      Result inResult = Complete;
      const auto constList = n->args()->list();
      for ( const QgsExpressionNode *ln : constList )
      {
        QString s;
        const Result r = compileNode( ln, s );
        if ( r == Complete || r == Partial )
        {
          args << s;
          if ( r == Partial )
            inResult = Partial;
        }
        else
          return r;
      }
 
      // update arguments to be adapted to SQL function
      args = sqlArgumentsFromFunctionName( fd->name(), args );
 
      // build result
      result = !nd.isEmpty() ? u"%1(%2)"_s.arg( nd, args.join( ',' ) ) : args.join( ',' );
      return inResult == Partial ? Partial : Complete;
    }
 
    case QgsExpressionNode::ntCondition:
      break;
 
    case QgsExpressionNode::ntIndexOperator:
      break;
  }
 
  return Fail;
}
 
QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
{
  Q_UNUSED( fnName )
  return QString();
}
 
QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
{
  Q_UNUSED( fnName )
  return QStringList( fnArgs );
}
 
QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
{
  Q_UNUSED( value )
  return QString();
}
 
QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
{
  return value;
}
 
QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
{
  Q_UNUSED( value )
  return QString();
}
 
QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::replaceNodeByStaticCachedValueIfPossible( const QgsExpressionNode *node, QString &result )
{
  if ( mIgnoreStaticNodes )
    return Fail;
 
  if ( node->hasCachedStaticValue() )
  {
    bool ok = false;
    if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && node->cachedStaticValue().userType() == QMetaType::Type::QString )
    {
      // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
      // double check results using QGIS' expression engine
      result = quotedValue( node->cachedStaticValue(), ok );
      return ok ? Partial : Fail;
    }
    else
    {
      result = quotedValue( node->cachedStaticValue(), ok );
      return ok ? Complete : Fail;
    }
  }
  return Fail;
}
 
bool QgsSqlExpressionCompiler::nodeIsNullLiteral( const QgsExpressionNode *node ) const
{
  if ( node->nodeType() != QgsExpressionNode::ntLiteral )
    return false;
 
  const QgsExpressionNodeLiteral *nLit = static_cast<const QgsExpressionNodeLiteral *>( node );
  return QgsVariantUtils::isNull( nLit->value() );
}