QString QgsSpatiaLiteFeatureIterator::whereClauseRect()
{
QgsRectangle rect = mRequest.filterRect();
QString whereClause;
if ( mRequest.flags() & QgsFeatureRequest::ExactIntersect )
{
// we are requested to evaluate a true INTERSECT relationship
whereClause += QString( "Intersects(%1, BuildMbr(%2)) AND " ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ), mbr( rect ) );
}
if ( mSource->mVShapeBased )
{
// handling a VirtualShape layer
whereClause += QString( "MbrIntersects(%1, BuildMbr(%2))" ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ), mbr( rect ) );
}
else if ( rect.isFinite() )
{
if ( mSource->spatialIndexRTree )
{
// using the RTree spatial index
QString mbrFilter = QString( "xmin <= %1 AND " ).arg( qgsDoubleToString( rect.xMaximum() ) );
mbrFilter += QString( "xmax >= %1 AND " ).arg( qgsDoubleToString( rect.xMinimum() ) );
mbrFilter += QString( "ymin <= %1 AND " ).arg( qgsDoubleToString( rect.yMaximum() ) );
mbrFilter += QString( "ymax >= %1" ).arg( qgsDoubleToString( rect.yMinimum() ) );
QString idxName = QString( "idx_%1_%2" ).arg( mSource->mIndexTable, mSource->mIndexGeometry );
whereClause += QString( "%1 IN (SELECT pkid FROM %2 WHERE %3)" )
.arg( quotedPrimaryKey(),
QgsSpatiaLiteProvider::quotedIdentifier( idxName ),
mbrFilter );
}
else if ( mSource->spatialIndexMbrCache )
{
// using the MbrCache spatial index
QString idxName = QString( "cache_%1_%2" ).arg( mSource->mIndexTable, mSource->mIndexGeometry );
whereClause += QString( "%1 IN (SELECT rowid FROM %2 WHERE mbr = FilterMbrIntersects(%3))" )
.arg( quotedPrimaryKey(),
QgsSpatiaLiteProvider::quotedIdentifier( idxName ),
mbr( rect ) );
}
else
{
// using simple MBR filtering
whereClause += QString( "MbrIntersects(%1, BuildMbr(%2))" ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ), mbr( rect ) );
}
}
else
{
whereClause = "1";
}
return whereClause;
}
bool QgsSpatiaLiteFeatureIterator::prepareStatement( const QString& whereClause, long limit )
{
if ( !mHandle )
return false;
try
{
QString sql = QString( "SELECT %1" ).arg( mHasPrimaryKey ? quotedPrimaryKey() : "0" );
int colIdx = 1; // column 0 is primary key
if ( mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes )
{
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it )
{
sql += ',' + fieldName( mSource->mFields.field( *it ) );
colIdx++;
}
}
else
{
// fetch all attributes
for ( int idx = 0; idx < mSource->mFields.count(); ++idx )
{
sql += ',' + fieldName( mSource->mFields.at( idx ) );
colIdx++;
}
}
if ( mFetchGeometry )
{
sql += QString( ", AsBinary(%1)" ).arg( QgsSpatiaLiteProvider::quotedIdentifier( mSource->mGeometryColumn ) );
mGeomColIdx = colIdx;
}
sql += QString( " FROM %1" ).arg( mSource->mQuery );
if ( !whereClause.isEmpty() )
sql += QString( " WHERE %1" ).arg( whereClause );
if ( limit >= 0 )
sql += QString( " LIMIT %1" ).arg( limit );
if ( sqlite3_prepare_v2( mHandle->handle(), sql.toUtf8().constData(), -1, &sqliteStatement, NULL ) != SQLITE_OK )
{
// some error occurred
QgsMessageLog::logMessage( QObject::tr( "SQLite error: %2\nSQL: %1" ).arg( sql, sqlite3_errmsg( mHandle->handle() ) ), QObject::tr( "SpatiaLite" ) );
return false;
}
}
catch ( QgsSpatiaLiteProvider::SLFieldNotFound )
{
rewind();
return false;
}
return true;
}
QString QgsSpatiaLiteFeatureIterator::whereClauseFids()
{
QStringList whereClauses;
foreach ( const QgsFeatureId featureId, mRequest.filterFids() )
{
whereClauses << QString( "%1=%2" ).arg( quotedPrimaryKey() ).arg( featureId );
}
return whereClauses.isEmpty() ? "" : whereClauses.join( " OR " ).prepend( "(" ).append( ")" );
}
QString QgsSpatiaLiteFeatureIterator::whereClauseFids()
{
if ( mRequest.filterFids().isEmpty() )
return "";
QString expr = QString( "%1 IN (" ).arg( quotedPrimaryKey() ), delim;
Q_FOREACH ( const QgsFeatureId featureId, mRequest.filterFids() )
{
expr += delim + QString::number( featureId );
delim = ",";
}
expr += ")";
return expr;
}
QString QgsSpatiaLiteFeatureIterator::whereClauseFid()
{
return QString( "%1=%2" ).arg( quotedPrimaryKey() ).arg( mRequest.filterFid() );
}
QString QgsSqlAnywhereFeatureIterator::whereClauseFid() const
{
return QString( "%1=%2" ).arg( quotedPrimaryKey() ).arg( mRequest.filterFid() );
}
bool
QgsSqlAnywhereFeatureIterator::prepareStatement( QString whereClause )
// adapted from QgsSpatialLiteFeatureIterator::prepareStatement
{
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) && P->mGeometryColumn.isNull() )
return false;
if ( !P->ensureConnRO() )
{
SaDebugMsg( "No read-only database connection." );
return false;
}
QString sql = QString( "SELECT %1" ).arg( quotedPrimaryKey() ); // Column 0 is primary key
// Column 1 is geometry, if applicable
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) )
{
sql += QString( ", %1 .ST_AsBinary('WKB(Version=1.1;endian=%2)') " )
.arg( P->mGeometryColumn )
.arg( QgsApplication::endian() == QgsApplication::XDR ? "xdr" : "ndr" );
}
// Add the rest of the columns
if ( mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes )
{
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it )
{
QString name = P->field( *it ).name();
if ( !name.isEmpty() /*&& name != P->mKeyColumn*/ )
{
sql += "," + P->quotedIdentifier( name );
}
}
}
else
{
// fetch all attributes
for ( int idx = 0; idx < P->mAttributeFields.count(); ++idx )
{
QString name = P->mAttributeFields[idx].name();
if ( !name.isEmpty() /*&& name != P->mKeyColumn*/ )
{
sql += "," + P->quotedIdentifier( name );
}
}
}
sql += QString( " FROM %1 " ).arg( P->mQuotedTableName );
if ( !whereClause.isEmpty() )
sql += QString( " WHERE %1 OPTIONS(FORCE OPTIMIZATION)" ).arg( whereClause );
if ( mStmt )
delete mStmt;
mStmt = P->mConnRO->prepare( sql );
if ( !mStmt->isValid() )
{
// (Error msg will be printed using SaDebugMsg in prepare())
rewind();
return false;
}
// bind parameters if necessary
if ( !mStmtRect.isEmpty() )
{
double xmin = mStmtRect.xMinimum();
double ymin = mStmtRect.yMinimum();
double xmax = mStmtRect.xMaximum();
double ymax = mStmtRect.yMaximum();
a_sqlany_bind_param xminParam;
a_sqlany_bind_param yminParam;
a_sqlany_bind_param xmaxParam;
a_sqlany_bind_param ymaxParam;
size_t xminLen = sizeof( double );
size_t yminLen = sizeof( double );
size_t xmaxLen = sizeof( double );
size_t ymaxLen = sizeof( double );
if ( !mStmt->describe_bind_param( 0, xminParam )
|| !mStmt->describe_bind_param( 1, yminParam )
|| !mStmt->describe_bind_param( 2, xmaxParam )
|| !mStmt->describe_bind_param( 3, ymaxParam ) )
{
P->reportError( QObject::tr( "Error describing bind parameters" ), mStmt );
return false;
}
xminParam.value.buffer = ( char * ) & xmin;
yminParam.value.buffer = ( char * ) & ymin;
xmaxParam.value.buffer = ( char * ) & xmax;
ymaxParam.value.buffer = ( char * ) & ymax;
xminParam.value.length = &xminLen;
yminParam.value.length = &yminLen;
xmaxParam.value.length = &xmaxLen;
ymaxParam.value.length = &ymaxLen;
xminParam.value.type = A_DOUBLE;
yminParam.value.type = A_DOUBLE;
xmaxParam.value.type = A_DOUBLE;
ymaxParam.value.type = A_DOUBLE;
if ( !mStmt->bind_param( 0, xminParam )
|| !mStmt->bind_param( 1, yminParam )
|| !mStmt->bind_param( 2, xmaxParam )
|| !mStmt->bind_param( 3, ymaxParam ) )
{
P->reportError( QObject::tr( "Error binding parameters" ), mStmt );
return false;
}
}
// Execute the statement
if ( !mStmt->execute() )
{
// (Error msg will be printed using SaDebugMsg in execute())
rewind();
return false;
}
return true;
}
