Skip to content

Commit

Permalink
mssql provider:
Browse files Browse the repository at this point in the history 
* add missing support for non-integer and compound keys
* update unit tests
* unify time handling: iterator / minimumValue / maximumValue / uniqueValues
  • Loading branch information
@jef-n
jef-n committed Sep 17, 2020
1 parent 484ba6f commit b3e7230
Show file tree
Hide file tree
Showing 12 changed files with 947 additions and 223 deletions.
2 changes: 1 addition & 1 deletion src/core/qgsapplication.cpp
View file
Expand Up @@ -764,7 +764,7 @@ QString QgsApplication::resolvePkgPath()
QgsDebugMsgLevel( QStringLiteral( "- source directory: %1" ).arg( sBuildSourcePath()->toUtf8().constData() ), 4 );
QgsDebugMsgLevel( QStringLiteral( "- output directory of the build: %1" ).arg( sBuildOutputPath()->toUtf8().constData() ), 4 );
#if defined(_MSC_VER) && !defined(USING_NMAKE) && !defined(USING_NINJA)
*sCfgIntDir() = appPath.split( '/', QString::SkipEmptyParts ).last();
*sCfgIntDir() = prefix.split( '/', QString::SkipEmptyParts ).last();
qDebug( "- cfg: %s", sCfgIntDir()->toUtf8().constData() );
#endif
}
Expand Down
1 change: 0 additions & 1 deletion src/providers/mssql/qgsmssqlconnection.cpp
View file
Expand Up @@ -363,7 +363,6 @@ bool QgsMssqlConnection::isSystemSchema( const QString &schema )

QgsDataSourceUri QgsMssqlConnection::connUri( const QString &connName )
{

QgsSettings settings;

const QString key = "/MSSQL/connections/" + connName;
Expand Down
7 changes: 4 additions & 3 deletions src/providers/mssql/qgsmssqlexpressioncompiler.cpp
View file
Expand Up @@ -18,9 +18,10 @@

QgsMssqlExpressionCompiler::QgsMssqlExpressionCompiler( QgsMssqlFeatureSource *source )
: QgsSqlExpressionCompiler( source->mFields,
QgsSqlExpressionCompiler::LikeIsCaseInsensitive | QgsSqlExpressionCompiler::CaseInsensitiveStringMatch | QgsSqlExpressionCompiler::IntegerDivisionResultsInInteger )
QgsSqlExpressionCompiler::LikeIsCaseInsensitive |
QgsSqlExpressionCompiler::CaseInsensitiveStringMatch |
QgsSqlExpressionCompiler::IntegerDivisionResultsInInteger )
{

}

QgsSqlExpressionCompiler::Result QgsMssqlExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
Expand All @@ -40,7 +41,7 @@ QgsSqlExpressionCompiler::Result QgsMssqlExpressionCompiler::compileNode( const

default:
// fallback to default handling
return QgsSqlExpressionCompiler::compileNode( node, result );;
return QgsSqlExpressionCompiler::compileNode( node, result );
}

QString op1, op2;
Expand Down
194 changes: 166 additions & 28 deletions src/providers/mssql/qgsmssqlfeatureiterator.cpp
View file
Expand Up @@ -84,19 +84,71 @@ double QgsMssqlFeatureIterator::validLon( const double longitude ) const
return longitude;
}

QString QgsMssqlFeatureIterator::whereClauseFid( QgsFeatureId featureId )
{
QString whereClause;

switch ( mSource->mPrimaryKeyType )
{
case PktInt:
Q_ASSERT( mSource->mPrimaryKeyAttrs.size() == 1 );
whereClause = QStringLiteral( "[%1]=%2" ).arg( mSource->mFields.at( mSource->mPrimaryKeyAttrs[0] ).name(), FID_TO_STRING( featureId ) );
break;

case PktFidMap:
{
const QVariantList &pkVals = mSource->mShared->lookupKey( featureId );
if ( !pkVals.isEmpty() )
{
Q_ASSERT( pkVals.size() == mSource->mPrimaryKeyAttrs.size() );

whereClause = QStringLiteral( "(" );

QString delim;
for ( int i = 0; i < mSource->mPrimaryKeyAttrs.size(); ++i )
{
const QgsField &fld = mSource->mFields.at( mSource->mPrimaryKeyAttrs[i] );
whereClause += QStringLiteral( "%1[%2]=%3" ).arg( delim, fld.name(), QgsMssqlProvider::quotedValue( pkVals[i] ) );
delim = QStringLiteral( " AND " );
}

whereClause += QStringLiteral( ")" );
}
else
{
QgsDebugMsg( QStringLiteral( "FAILURE: Key values for feature %1 not found." ).arg( featureId ) );
whereClause = QStringLiteral( "NULL IS NOT NULL" );
}
}
break;

default:
Q_ASSERT( !"FAILURE: Primary key unknown" );
whereClause = QStringLiteral( "NULL IS NOT NULL" );
break;
}

return whereClause;
}

void QgsMssqlFeatureIterator::BuildStatement( const QgsFeatureRequest &request )
{
mFallbackStatement.clear();
mStatement.clear();

bool limitAtProvider = ( mRequest.limit() >= 0 );
bool limitAtProvider = mRequest.limit() >= 0;

// build sql statement

// note: 'SELECT ' is added later, to account for 'SELECT TOP...' type queries
mStatement += QStringLiteral( "[%1]" ).arg( mSource->mFidColName );
mFidCol = mSource->mFields.indexFromName( mSource->mFidColName );
mAttributesToFetch.append( mFidCol );
QString delim;
for ( auto idx : mSource->mPrimaryKeyAttrs )
{
mStatement += QStringLiteral( "%1[%2]" ).arg( delim, mSource->mFields.at( idx ).name() );
delim = ',';
}

mAttributesToFetch << mSource->mPrimaryKeyAttrs;

bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
QgsAttributeList attrs = subsetOfAttributes ? mRequest.subsetOfAttributes() : mSource->mFields.allAttributesList();
Expand All @@ -123,11 +175,10 @@ void QgsMssqlFeatureIterator::BuildStatement( const QgsFeatureRequest &request )

for ( int i : qgis::as_const( attrs ) )
{
QString fieldname = mSource->mFields.at( i ).name();
if ( mSource->mFidColName == fieldname )
if ( mSource->mPrimaryKeyAttrs.contains( i ) )
continue;

mStatement += QStringLiteral( ",[%1]" ).arg( fieldname );
mStatement += QStringLiteral( ",[%1]" ).arg( mSource->mFields.at( i ).name() );

mAttributesToFetch.append( i );
}
Expand Down Expand Up @@ -184,37 +235,84 @@ void QgsMssqlFeatureIterator::BuildStatement( const QgsFeatureRequest &request )
}

// set fid filter
if ( request.filterType() == QgsFeatureRequest::FilterFid && !mSource->mFidColName.isEmpty() )
if ( request.filterType() == QgsFeatureRequest::FilterFid && !mSource->mPrimaryKeyAttrs.isEmpty() )
{
QString fidfilter = QStringLiteral( " [%1] = %2" ).arg( mSource->mFidColName, FID_TO_STRING( request.filterFid() ) );
// set attribute filter
if ( !filterAdded )
mStatement += QLatin1String( " WHERE " );
else
mStatement += QLatin1String( " AND " );

mStatement += fidfilter;
if ( mSource->mPrimaryKeyType == PktInt )
{
mStatement += QStringLiteral( "[%1]=%2" ).arg( mSource->mFields[mSource->mPrimaryKeyAttrs[0]].name(), FID_TO_STRING( request.filterFid() ) );
}
else if ( mSource->mPrimaryKeyType == PktFidMap )
{
QVariantList key = mSource->mShared->lookupKey( request.filterFid() );
if ( !key.isEmpty() )
{
mStatement += "(";

QString delim;
for ( int i = 0; i < mSource->mPrimaryKeyAttrs.size(); i++ )
{
QString colName = mSource->mFields[mSource->mPrimaryKeyAttrs[i]].name();
QString expr;
if ( key[i].isNull() )
expr = QString( "[%1] IS NULL" ).arg( colName );
else
expr = QString( "[%1]=%2" ).arg( colName, QgsMssqlProvider::quotedValue( key[i] ) );

mStatement += QStringLiteral( "%1%2" ).arg( delim, expr );
delim = " AND ";
}

mStatement += ")";
}
}

filterAdded = true;
}
else if ( request.filterType() == QgsFeatureRequest::FilterFids && !mSource->mFidColName.isEmpty()
else if ( request.filterType() == QgsFeatureRequest::FilterFids && !mSource->mPrimaryKeyAttrs.isEmpty()
&& !mRequest.filterFids().isEmpty() )
{
QString delim;
QString inClause = QStringLiteral( "%1 IN (" ).arg( mSource->mFidColName );
const auto constFilterFids = mRequest.filterFids();
for ( QgsFeatureId featureId : constFilterFids )
{
inClause += delim + FID_TO_STRING( featureId );
delim = ',';
}
inClause.append( ')' );

if ( !filterAdded )
mStatement += QLatin1String( " WHERE " );
else
mStatement += QLatin1String( " AND " );

mStatement += inClause;
if ( mSource->mPrimaryKeyType == PktInt )
{
QString delim;
QString colName = mSource->mFields[mSource->mPrimaryKeyAttrs[0]].name();
QString inClause = QStringLiteral( "[%1] IN (" ).arg( colName );
const auto constFilterFids = mRequest.filterFids();
for ( QgsFeatureId featureId : constFilterFids )
{
inClause += delim + FID_TO_STRING( featureId );
delim = ',';
}
inClause.append( ')' );

mStatement += inClause;
}
else
{
const auto constFilterFids = mRequest.filterFids();

if ( !constFilterFids.isEmpty() )
{
QString delim( "(" );
for ( QgsFeatureId featureId : constFilterFids )
{
mStatement += delim + whereClauseFid( featureId ) + ")";
delim = " OR (";
}

mStatement += ")";
}
}

filterAdded = true;
}

Expand Down Expand Up @@ -376,6 +474,8 @@ bool QgsMssqlFeatureIterator::fetchFeature( QgsFeature &feature )
const QVariant originalValue = mQuery->value( i );
QgsField fld = mSource->mFields.at( mAttributesToFetch.at( i ) );
QVariant v = originalValue;
if ( fld.type() == QVariant::Time )
v = QgsMssqlProvider::convertTimeValue( v );
if ( v.type() != fld.type() )
v = QgsVectorDataProvider::convertValue( fld.type(), originalValue.toString() );

Expand All @@ -398,7 +498,45 @@ bool QgsMssqlFeatureIterator::fetchFeature( QgsFeature &feature )
feature.setAttribute( mAttributesToFetch.at( i ), v );
}

feature.setId( mQuery->record().value( mSource->mFidColName ).toLongLong() );
QgsFeatureId fid = 0;

switch ( mSource->mPrimaryKeyType )
{
case PktInt:
// get 64bit integer from result
fid = mQuery->record().value( mSource->mFields.at( mSource->mPrimaryKeyAttrs.value( 0 ) ).name() ).toLongLong();
if ( mAttributesToFetch.contains( mSource->mPrimaryKeyAttrs.value( 0 ) ) )
feature.setAttribute( mSource->mPrimaryKeyAttrs.value( 0 ), fid );
break;

case PktFidMap:
{
QVariantList primaryKeyVals;
foreach ( int idx, mSource->mPrimaryKeyAttrs )
{
QgsField fld = mSource->mFields.at( idx );

QVariant v = mQuery->record().value( fld.name() );
if ( fld.type() == QVariant::Time )
v = QgsMssqlProvider::convertTimeValue( v );
if ( v.type() != fld.type() )
v = QgsVectorDataProvider::convertValue( fld.type(), v.toString() );
primaryKeyVals << v;

if ( mAttributesToFetch.contains( idx ) )
feature.setAttribute( idx, v );
}

fid = mSource->mShared->lookupFid( primaryKeyVals );
}
break;

case PktUnknown:
Q_ASSERT( !"FAILURE: cannot get feature with unknown primary key" );
return false;
}

feature.setId( fid );

feature.clearGeometry();
if ( mSource->isSpatial() )
Expand All @@ -408,9 +546,7 @@ bool QgsMssqlFeatureIterator::fetchFeature( QgsFeature &feature )
{
std::unique_ptr<QgsAbstractGeometry> geom = mParser.parseSqlGeometry( reinterpret_cast< unsigned char * >( ar.data() ), ar.size() );
if ( geom )
{
feature.setGeometry( QgsGeometry( std::move( geom ) ) );
}
}
}

Expand Down Expand Up @@ -487,7 +623,7 @@ bool QgsMssqlFeatureIterator::rewind()

if ( !result )
{
QgsDebugMsg( mQuery->lastError().text() );
QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( mQuery->lastQuery(), mQuery->lastError().text() ) );
close();
return false;
}
Expand Down Expand Up @@ -520,7 +656,9 @@ bool QgsMssqlFeatureIterator::close()

QgsMssqlFeatureSource::QgsMssqlFeatureSource( const QgsMssqlProvider *p )
: mFields( p->mAttributeFields )
, mFidColName( p->mFidColName )
, mPrimaryKeyType( p->mPrimaryKeyType )
, mPrimaryKeyAttrs( p->mPrimaryKeyAttrs )
, mShared( p->mShared )
, mSRId( p->mSRId )
, mIsGeography( p->mParser.mIsGeography )
, mGeometryColName( p->mGeometryColName )
Expand Down
12 changes: 6 additions & 6 deletions src/providers/mssql/qgsmssqlfeatureiterator.h
View file
Expand Up @@ -25,6 +25,8 @@
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>

#include "qgsmssqlprovider.h"

class QgsMssqlProvider;

class QgsMssqlFeatureSource final: public QgsAbstractFeatureSource
Expand All @@ -36,7 +38,9 @@ class QgsMssqlFeatureSource final: public QgsAbstractFeatureSource

private:
QgsFields mFields;
QString mFidColName;
QgsMssqlPrimaryKeyType mPrimaryKeyType;
QList<int> mPrimaryKeyAttrs;
std::shared_ptr<QgsMssqlSharedData> mShared;
long mSRId;

/* sql geo type */
Expand Down Expand Up @@ -83,13 +87,12 @@ class QgsMssqlFeatureIterator final: public QgsAbstractFeatureIteratorFromSource
bool close() override;

protected:

bool fetchFeature( QgsFeature &feature ) override;
bool nextFeatureFilterExpression( QgsFeature &f ) override;

private:
void BuildStatement( const QgsFeatureRequest &request );

QString whereClauseFid( QgsFeatureId featureId );

private:

Expand All @@ -110,9 +113,6 @@ class QgsMssqlFeatureIterator final: public QgsAbstractFeatureIteratorFromSource

QString mFallbackStatement;

// Field index of FID column
int mFidCol = -1;

// List of attribute indices to fetch with nextFeature calls
QgsAttributeList mAttributesToFetch;

Expand Down

0 comments on commit b3e7230

Please sign in to comment.