Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
[FEATURE] Send filter expressions to postgres provider
This commit allows to filter features already on server side. Only supported
expressions will be sent to the database. Expressions using unsupported
operators or functions will gracefully fallback to local evaluation.

To make use of this feature

 * Enable it in options: data sources
 * QgsFeatureRequest().setFilterExpression( expression )
 * or QgsVectorLayer::getFeatures( expression )
  • Loading branch information
m-kuhn committed May 22, 2015
1 parent 9b6e23d commit a99deb8
Show file tree
Hide file tree
Showing 5 changed files with 244 additions and 3 deletions.
2 changes: 2 additions & 0 deletions src/providers/postgres/CMakeLists.txt
Expand Up @@ -13,6 +13,7 @@ SET(PG_SRCS
qgspgnewconnection.cpp
qgspgtablemodel.cpp
qgscolumntypethread.cpp
qgspostgresexpressioncompiler.cpp
)
SET(PG_MOC_HDRS
qgspostgresprovider.h
Expand All @@ -27,6 +28,7 @@ SET(PG_MOC_HDRS

SET(PG_HDRS
qgspostgrestransaction.h
qgspostgresexpressioncompiler.h
)

########################################################
Expand Down
152 changes: 152 additions & 0 deletions src/providers/postgres/qgspostgresexpressioncompiler.cpp
@@ -0,0 +1,152 @@
/***************************************************************************
----------------------------------------------------
date : 22.4.2015
copyright : (C) 2015 by Matthias Kuhn
email : matthias (at) opengis.ch
***************************************************************************
* *
* 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 "qgspostgresexpressioncompiler.h"

QgsPostgresExpressionCompiler::QgsPostgresExpressionCompiler( QgsPostgresFeatureSource* source )
: mResult( None )
, mSource( source )
{
}

QgsPostgresExpressionCompiler::~QgsPostgresExpressionCompiler()
{

}

QgsPostgresExpressionCompiler::Result QgsPostgresExpressionCompiler::compile( const QgsExpression* exp )
{
if ( exp->rootNode() )
return compile ( exp->rootNode(), mResult );
else
return Fail;
}

QgsPostgresExpressionCompiler::Result QgsPostgresExpressionCompiler::compile( const QgsExpression::Node* node, QString& result )
{
switch( node->nodeType() )
{
case QgsExpression::ntUnaryOperator:
{
const QgsExpression::NodeUnaryOperator* n = static_cast<const QgsExpression::NodeUnaryOperator*>( node );
switch( n->op() )
{
case QgsExpression::uoNot:
break;

case QgsExpression::uoMinus:
break;
}

break;
}

case QgsExpression::ntBinaryOperator:
{
QString op;
QString left;
QString right;
Result lr;
Result rr;

const QgsExpression::NodeBinaryOperator* n = static_cast<const QgsExpression::NodeBinaryOperator*>( node );

switch ( n->op() )
{
case QgsExpression::boEQ:
op = "=";
break;

case QgsExpression::boGE:
op = ">=";
break;

case QgsExpression::boGT:
op = ">";
break;

case QgsExpression::boLE:
op = "<=";
break;

case QgsExpression::boLT:
op = "<";
break;

case QgsExpression::boLike:
op = "LIKE";
break;

case QgsExpression::boILike:
op = "ILIKE";
break;

case QgsExpression::boOr:
op = "OR";
break;

case QgsExpression::boAnd:
op = "AND";
break;

case QgsExpression::boNE:
op = "<>";
break;
}

if ( !op.isNull() )
{
lr = compile( n->opLeft(), left );
rr = compile( n->opRight(), right );
result = left + " " + op + " " + right;
return ( lr == Complete && rr == Complete ) ? Complete : Fail;
}
else
{
return Fail;
}

break;
}

case QgsExpression::ntFunction:
return Fail;
break;

case QgsExpression::ntLiteral:
{
const QgsExpression::NodeLiteral* n = static_cast<const QgsExpression::NodeLiteral*>( node );
result = QgsPostgresConn::quotedValue( n->value() );
return Complete;
}

case QgsExpression::ntColumnRef:
{
const QgsExpression::NodeColumnRef* n = static_cast<const QgsExpression::NodeColumnRef*>( node );

if ( mSource->mFields.indexFromName( n->name() ) != -1 )
{
result = QgsPostgresConn::quotedIdentifier( n->name() );
return Complete;
}
else
{
// Not a provider field
return Fail;
}
}
}
return Fail;
}
50 changes: 50 additions & 0 deletions src/providers/postgres/qgspostgresexpressioncompiler.h
@@ -0,0 +1,50 @@
/***************************************************************************
----------------------------------------------------
date : 22.4.2015
copyright : (C) 2015 by Matthias Kuhn
email : matthias (at) opengis.ch
***************************************************************************
* *
* 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. *
* *
***************************************************************************/

#ifndef QGSPOSTGRESEXPRESSIONCOMPILER_H
#define QGSPOSTGRESEXPRESSIONCOMPILER_H

#include "qgsexpression.h"
#include "qgspostgresfeatureiterator.h"

class QgsPostgresExpressionCompiler
{
public:
enum Result {
None,
Complete,
Partial,
Fail
};

QgsPostgresExpressionCompiler( QgsPostgresFeatureSource* source );
~QgsPostgresExpressionCompiler();

Result compile( const QgsExpression* exp );

inline Result success() { return mSuccess; }

const QString& result() { return mResult; }

private:
Result compile( const QgsExpression::Node* node, QString& str );

private:
Result mSuccess;
QString mResult;
QgsPostgresFeatureSource* mSource;
};

#endif // QGSPOSTGRESEXPRESSIONCOMPILER_H
32 changes: 30 additions & 2 deletions src/providers/postgres/qgspostgresfeatureiterator.cpp
Expand Up @@ -12,16 +12,18 @@
* (at your option) any later version. *
* *
***************************************************************************/
#include "qgsgeometry.h"
#include "qgspostgresconnpool.h"
#include "qgspostgresexpressioncompiler.h"
#include "qgspostgresfeatureiterator.h"
#include "qgspostgresprovider.h"
#include "qgspostgresconnpool.h"
#include "qgspostgrestransaction.h"
#include "qgsgeometry.h"

#include "qgslogger.h"
#include "qgsmessagelog.h"

#include <QObject>
#include <QSettings>


const int QgsPostgresFeatureIterator::sFeatureQueueSize = 2000;
Expand All @@ -32,6 +34,7 @@ QgsPostgresFeatureIterator::QgsPostgresFeatureIterator( QgsPostgresFeatureSource
, mFeatureQueueSize( sFeatureQueueSize )
, mFetched( 0 )
, mFetchGeometry( false )
, mExpressionCompiled( false)
{
if ( !source->mTransactionConnection )
{
Expand Down Expand Up @@ -67,6 +70,17 @@ QgsPostgresFeatureIterator::QgsPostgresFeatureIterator( QgsPostgresFeatureSource
{
whereClause = QgsPostgresUtils::whereClause( mRequest.filterFids(), mSource->mFields, mConn, mSource->mPrimaryKeyType, mSource->mPrimaryKeyAttrs, mSource->mShared );
}
else if ( request.filterType() == QgsFeatureRequest::FilterExpression
&& QSettings().value( "/qgis/postgres/compileExpressions", false ).toBool() )
{
QgsPostgresExpressionCompiler compiler = QgsPostgresExpressionCompiler( source );

if ( compiler.compile( request.filterExpression() ) == QgsPostgresExpressionCompiler::Complete )
{
whereClause = compiler.result();
mExpressionCompiled = true;
}
}

if ( !mSource->mSqlWhereClause.isEmpty() )
{
Expand Down Expand Up @@ -166,6 +180,14 @@ bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature& feature )
return true;
}

bool QgsPostgresFeatureIterator::nextFeatureFilterExpression( QgsFeature& f )
{
if ( !mExpressionCompiled )
return QgsAbstractFeatureIterator::nextFeatureFilterExpression( f );
else
return fetchFeature( f );
}

bool QgsPostgresFeatureIterator::prepareSimplification( const QgsSimplifyMethod& simplifyMethod )
{
// setup simplification of geometries to fetch
Expand Down Expand Up @@ -233,6 +255,11 @@ bool QgsPostgresFeatureIterator::close()
return true;
}

int QgsPostgresFeatureIterator::count()
{

}

///////////////

QString QgsPostgresFeatureIterator::whereClauseRect()
Expand Down Expand Up @@ -407,6 +434,7 @@ bool QgsPostgresFeatureIterator::declareCursor( const QString& whereClause )

if ( !mConn->openCursor( mCursorName, query ) )
{

// reloading the fields might help next time around
// TODO how to cleanly force reload of fields? P->loadFields();
close();
Expand Down
11 changes: 10 additions & 1 deletion src/providers/postgres/qgspostgresfeatureiterator.h
Expand Up @@ -55,13 +55,14 @@ class QgsPostgresFeatureSource : public QgsAbstractFeatureSource
QSharedPointer<QgsPostgresSharedData> mShared;

/* The transaction connection (if any) gets refed/unrefed when creating/
* destroying the QgsPostfresFeatureSource, to ensure that the transaction
* destroying the QgsPostgresFeatureSource, to ensure that the transaction
* connection remains valid during the life time of the feature source
* even if the QgsPostgresTransaction object which initially created the
* connection has since been destroyed. */
QgsPostgresConn* mTransactionConnection;

friend class QgsPostgresFeatureIterator;
friend class QgsPostgresExpressionCompiler;
};


Expand All @@ -80,10 +81,16 @@ class QgsPostgresFeatureIterator : public QgsAbstractFeatureIteratorFromSource<Q
//! end of iterating: free the resources / lock
virtual bool close() override;

//! the number of features
virtual int count() override;

protected:
//! fetch next feature, return true on success
virtual bool fetchFeature( QgsFeature& feature ) override;

//! fetch next feature filter expression
bool nextFeatureFilterExpression(QgsFeature& f);

//! Setup the simplification of geometries to fetch using the specified simplify method
virtual bool prepareSimplification( const QgsSimplifyMethod& simplifyMethod ) override;

Expand Down Expand Up @@ -119,6 +126,8 @@ class QgsPostgresFeatureIterator : public QgsAbstractFeatureIteratorFromSource<Q
private:
//! returns whether the iterator supports simplify geometries on provider side
virtual bool providerCanSimplify( QgsSimplifyMethod::MethodType methodType ) const override;

bool mExpressionCompiled;
};

#endif // QGSPOSTGRESFEATUREITERATOR_H

0 comments on commit a99deb8

Please sign in to comment.