Skip to content

Commit

Permalink
oracle provider:
Browse files Browse the repository at this point in the history 
* if a no spatial index is found try sdo_filter (fixes #11358)
* allow specifing connection parameters (eg. OCI_ATTR_PREFETCH_ROWS=10000)
  • Loading branch information
@jef-n
jef-n committed Nov 3, 2014
1 parent d546de1 commit 991f94d
Show file tree
Hide file tree
Showing 13 changed files with 134 additions and 109 deletions.
2 changes: 2 additions & 0 deletions src/gui/qgsmanageconnectionsdialog.cpp
View file
Expand Up @@ -494,6 +494,7 @@ QDomDocument QgsManageConnectionsDialog::saveOracleConnections( const QStringLis
el.setAttribute( "host", settings.value( path + "/host", "" ).toString() );
el.setAttribute( "port", settings.value( path + "/port", "" ).toString() );
el.setAttribute( "database", settings.value( path + "/database", "" ).toString() );
el.setAttribute( "dboptions", settings.value( path + "/dboptions", "" ).toString() );
el.setAttribute( "estimatedMetadata", settings.value( path + "/estimatedMetadata", "0" ).toString() );
el.setAttribute( "userTablesOnly", settings.value( path + "/userTablesOnly", "0" ).toString() );
el.setAttribute( "geometryColumnsOnly", settings.value( path + "/geometryColumnsOnly", "0" ).toString() );
Expand Down Expand Up @@ -937,6 +938,7 @@ void QgsManageConnectionsDialog::loadOracleConnections( const QDomDocument &doc,
settings.setValue( "/host", child.attribute( "host" ) );
settings.setValue( "/port", child.attribute( "port" ) );
settings.setValue( "/database", child.attribute( "database" ) );
settings.setValue( "/dboptions", child.attribute( "dboptions" ) );
settings.setValue( "/estimatedMetadata", child.attribute( "estimatedMetadata" ) );
settings.setValue( "/userTablesOnly", child.attribute( "userTablesOnly" ) );
settings.setValue( "/geometryColumnsOnly", child.attribute( "geometryColumnsOnly" ) );
Expand Down
2 changes: 1 addition & 1 deletion src/providers/oracle/qgsoraclecolumntypethread.cpp
View file
Expand Up @@ -40,7 +40,7 @@ void QgsOracleColumnTypeThread::run()
mStopped = false;

QgsDataSourceURI uri = QgsOracleConn::connUri( mName );
QgsOracleConn *conn = QgsOracleConn::connectDb( uri.connectionInfo() );
QgsOracleConn *conn = QgsOracleConn::connectDb( uri );
if ( !conn )
{
QgsDebugMsg( "Connection failed - " + uri.connectionInfo() );
Expand Down
9 changes: 8 additions & 1 deletion src/providers/oracle/qgsoracleconn.cpp
View file
Expand Up @@ -67,10 +67,13 @@ QgsOracleConn::QgsOracleConn( QgsDataSourceURI uri )

mDatabase = QSqlDatabase::addDatabase( "QOCISPATIAL", QString( "oracle%1" ).arg( snConnections++ ) );
mDatabase.setDatabaseName( database );
mDatabase.setConnectOptions( "OCI_ATTR_PREFETCH_ROWS=1000" );
QString options = uri.hasParam( "dboptions" ) ? uri.param( "dboptions" ) : "OCI_ATTR_PREFETCH_ROWS=1000";
mDatabase.setConnectOptions( options );
mDatabase.setUserName( uri.username() );
mDatabase.setPassword( uri.password() );

QgsDebugMsg( QString( "Connecting with options: " ) + options );

if ( !mDatabase.open() )
{
QString username = uri.username();
Expand Down Expand Up @@ -706,6 +709,10 @@ QgsDataSourceURI QgsOracleConn::connUri( QString theConnName )
QgsDataSourceURI uri;
uri.setConnection( host, port, database, username, password );
uri.setUseEstimatedMetadata( useEstimatedMetadata );
if ( !settings.value( key + "/dboptions" ).toString().isEmpty() )
{
uri.setParam( "dboptions", settings.value( key + "/dboptions" ).toString() );
}

return uri;
}
Expand Down
21 changes: 9 additions & 12 deletions src/providers/oracle/qgsoraclefeatureiterator.cpp
View file
Expand Up @@ -52,7 +52,7 @@ QgsOracleFeatureIterator::QgsOracleFeatureIterator( QgsOracleFeatureSource* sour
break;

case QgsFeatureRequest::FilterRect:
if ( !mSource->mGeometryColumn.isNull() )
if ( !mSource->mGeometryColumn.isNull() && mSource->mHasSpatialIndex )
{
QgsRectangle rect( mRequest.filterRect() );
QString bbox = QString( "mdsys.sdo_geometry(2003,%1,NULL,"
Expand All @@ -65,18 +65,15 @@ QgsOracleFeatureIterator::QgsOracleFeatureIterator( QgsOracleFeatureSource* sour
.arg( qgsDoubleToString( rect.xMaximum() ) )
.arg( qgsDoubleToString( rect.yMaximum() ) );

if ( !mSource->mSpatialIndex.isNull() )
{
whereClause = QString( "sdo_filter(%1,%2)='TRUE'" ).arg( QgsOracleProvider::quotedIdentifier( mSource->mGeometryColumn ) ).arg( bbox );
whereClause = QString( "sdo_filter(%1,%2)='TRUE'" ).arg( QgsOracleProvider::quotedIdentifier( mSource->mGeometryColumn ) ).arg( bbox );
#if 0
if ( mRequest.flags() & QgsFeatureRequest::ExactIntersect )
{
whereClause += QString( " AND sdo_relate(%1,%2,'mask=ANYINTERACT')='TRUE'" )
.arg( quotedIdentifier( P->mGeometryColumn ) )
.arg( bbox );
}
#endif
if ( mRequest.flags() & QgsFeatureRequest::ExactIntersect )
{
whereClause += QString( " AND sdo_relate(%1,%2,'mask=ANYINTERACT')='TRUE'" )
.arg( quotedIdentifier( P->mGeometryColumn ) )
.arg( bbox );
}
#endif
}
break;

Expand Down Expand Up @@ -344,7 +341,7 @@ QgsOracleFeatureSource::QgsOracleFeatureSource( const QgsOracleProvider* p )
, mFields( p->mAttributeFields )
, mGeometryColumn( p->mGeometryColumn )
, mSrid( p->mSrid )
, mSpatialIndex( p->mSpatialIndex )
, mHasSpatialIndex( p->mHasSpatialIndex )
, mDetectedGeomType( p->mDetectedGeomType )
, mRequestedGeomType( p->mRequestedGeomType )
, mSqlWhereClause( p->mSqlWhereClause )
Expand Down
2 changes: 1 addition & 1 deletion src/providers/oracle/qgsoraclefeatureiterator.h
View file
Expand Up @@ -41,7 +41,7 @@ class QgsOracleFeatureSource : public QgsAbstractFeatureSource

QString mGeometryColumn; //! name of the geometry column
int mSrid; //! srid of column
QString mSpatialIndex; //! name of spatial index of geometry column
bool mHasSpatialIndex; //! has spatial index of geometry column
QGis::WkbType mDetectedGeomType; //! geometry type detected in the database
QGis::WkbType mRequestedGeomType; //! geometry type requested in the uri
QString mSqlWhereClause;
Expand Down
4 changes: 4 additions & 0 deletions src/providers/oracle/qgsoraclenewconnection.cpp
View file
Expand Up @@ -44,6 +44,7 @@ QgsOracleNewConnection::QgsOracleNewConnection( QWidget *parent, const QString&
port = "1521";
}
txtPort->setText( port );
txtOptions->setText( settings.value( key + "/dboptions" ).toString() );
cb_userTablesOnly->setChecked( settings.value( key + "/userTablesOnly", false ).toBool() );
cb_geometryColumnsOnly->setChecked( settings.value( key + "/geometryColumnsOnly", true ).toBool() );
cb_allowGeometrylessTables->setChecked( settings.value( key + "/allowGeometrylessTables", false ).toBool() );
Expand Down Expand Up @@ -125,6 +126,7 @@ void QgsOracleNewConnection::accept()
settings.setValue( baseKey + "/onlyExistingTypes", cb_onlyExistingTypes->isChecked() ? "true" : "false" );
settings.setValue( baseKey + "/saveUsername", chkStoreUsername->isChecked() ? "true" : "false" );
settings.setValue( baseKey + "/savePassword", chkStorePassword->isChecked() ? "true" : "false" );
settings.setValue( baseKey + "/dboptions", txtOptions->text() );

// remove old save setting
settings.remove( baseKey + "/save" );
Expand All @@ -136,6 +138,8 @@ void QgsOracleNewConnection::on_btnConnect_clicked()
{
QgsDataSourceURI uri;
uri.setConnection( txtHost->text(), txtPort->text(), txtDatabase->text(), txtUsername->text(), txtPassword->text() );
if ( !txtOptions->text().isEmpty() )
uri.setParam( "dboptions", txtOptions->text() );

QgsOracleConn *conn = QgsOracleConn::connectDb( uri );

Expand Down
50 changes: 29 additions & 21 deletions src/providers/oracle/qgsoracleprovider.cpp
View file
Expand Up @@ -48,7 +48,8 @@ QgsOracleProvider::QgsOracleProvider( QString const & uri )
, mPrimaryKeyType( pktUnknown )
, mDetectedGeomType( QGis::WKBUnknown )
, mRequestedGeomType( QGis::WKBUnknown )
, mSpatialIndex( QString::null )
, mHasSpatialIndex( false )
, mSpatialIndexName( QString::null )
, mShared( new QgsOracleSharedData )
{
static int geomMetaType = -1;
Expand All @@ -68,7 +69,7 @@ QgsOracleProvider::QgsOracleProvider( QString const & uri )
mRequestedGeomType = mUri.wkbType();
mUseEstimatedMetadata = mUri.useEstimatedMetadata();

mConnection = QgsOracleConn::connectDb( mUri.connectionInfo() );
mConnection = QgsOracleConn::connectDb( mUri );
if ( !mConnection )
{
return;
Expand Down Expand Up @@ -664,30 +665,22 @@ bool QgsOracleProvider::loadFields()
{
if ( qry.next() )
{
mSpatialIndex = qry.value( 0 ).toString();
mSpatialIndexName = qry.value( 0 ).toString();
if ( qry.value( 1 ).toString() != "VALID" )
{
QgsMessageLog::logMessage( tr( "Invalid spatial index %1 on column %2.%3.%4 found - expect poor performance." )
.arg( mSpatialIndex )
.arg( mSpatialIndexName )
.arg( mOwnerName )
.arg( mTableName )
.arg( mGeometryColumn ),
tr( "Oracle" ) );
mSpatialIndex = QString::null;
}
else
{
QgsDebugMsg( QString( "Valid spatial index %1 found" ).arg( mSpatialIndex ) );
QgsDebugMsg( QString( "Valid spatial index %1 found" ).arg( mSpatialIndexName ) );
mHasSpatialIndex = true;
}
}
else
{
QgsMessageLog::logMessage( tr( "No spatial index on column %1.%2.%3 found - expect poor performance." )
.arg( mOwnerName )
.arg( mTableName )
.arg( mGeometryColumn ),
tr( "Oracle" ) );
}
}
else
{
Expand All @@ -698,6 +691,21 @@ bool QgsOracleProvider::loadFields()
.arg( qry.lastError().text() ),
tr( "Oracle" ) );
}

if ( !mHasSpatialIndex )
{
mHasSpatialIndex = qry.exec( QString( "SELECT %2 FROM %1 WHERE sdo_filter(%2,mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(1,1,-1,-1)))='TRUE'" )
.arg( mQuery )
.arg( quotedIdentifier( mGeometryColumn ) ) );
if ( !mHasSpatialIndex )
{
QgsMessageLog::logMessage( tr( "No spatial index on column %1.%2.%3 found - expect poor performance." )
.arg( mOwnerName )
.arg( mTableName )
.arg( mGeometryColumn ),
tr( "Oracle" ) );
}
}
}

qry.finish();
Expand Down Expand Up @@ -2006,7 +2014,7 @@ QgsRectangle QgsOracleProvider::extent()

bool ok = false;

if ( !mSpatialIndex.isNull() && ( mUseEstimatedMetadata || mSqlWhereClause.isEmpty() ) )
if ( mHasSpatialIndex && ( mUseEstimatedMetadata || mSqlWhereClause.isEmpty() ) )
{
sql = QString( "SELECT SDO_TUNE.EXTENT_OF(%1,%2) FROM dual" )
.arg( quotedValue( QString( "%1.%2" ).arg( mOwnerName ).arg( mTableName ) ) )
Expand Down Expand Up @@ -2080,7 +2088,7 @@ bool QgsOracleProvider::getGeometryDetails()

int detectedSrid = -1;
QGis::WkbType detectedType = QGis::WKBUnknown;
mSpatialIndex = QString::null;
mHasSpatialIndex = false;

if ( mIsQuery )
{
Expand Down Expand Up @@ -2289,7 +2297,7 @@ bool QgsOracleProvider::createSpatialIndex()
QgsDebugMsg( "geographic CRS" );
}

if ( mSpatialIndex.isNull() )
if ( !mHasSpatialIndex )
{
int n = 0;
if ( exec( qry, QString( "SELECT coalesce(substr(max(index_name),10),'0') FROM all_indexes WHERE index_name LIKE 'QGIS_IDX_%' ESCAPE '#' ORDER BY index_name" ) ) &&
Expand All @@ -2311,11 +2319,11 @@ bool QgsOracleProvider::createSpatialIndex()
return false;
}

mSpatialIndex = QString( "QGIS_IDX_%1" ).arg( n, 10, 10, QChar( '0' ) );
mSpatialIndexName = QString( "QGIS_IDX_%1" ).arg( n, 10, 10, QChar( '0' ) );
}
else
{
if ( !exec( qry, QString( "ALTER INDEX %1 REBUILD" ).arg( mSpatialIndex ) ) )
if ( !exec( qry, QString( "ALTER INDEX %1 REBUILD" ).arg( mSpatialIndexName ) ) )
{
QgsMessageLog::logMessage( tr( "Rebuild of spatial index failed.\nSQL:%1\nError: %2" )
.arg( qry.lastQuery() )
Expand Down Expand Up @@ -2402,7 +2410,7 @@ QgsVectorLayerImport::ImportError QgsOracleProvider::createEmptyLayer(
QgsDebugMsg( QString( "Connection info is: %1" ).arg( dsUri.connectionInfo() ) );

// create the table
QgsOracleConn *conn = QgsOracleConn::connectDb( dsUri.connectionInfo() );
QgsOracleConn *conn = QgsOracleConn::connectDb( dsUri );
if ( !conn )
{
if ( errorMessage )
Expand Down Expand Up @@ -2891,7 +2899,7 @@ QGISEXTERN bool deleteLayer( const QString& uri, QString& errCause )
QString tableName = dsUri.table();
QString geometryCol = dsUri.geometryColumn();

QgsOracleConn* conn = QgsOracleConn::connectDb( dsUri.connectionInfo() );
QgsOracleConn* conn = QgsOracleConn::connectDb( dsUri );
if ( !conn )
{
errCause = QObject::tr( "Connection to database failed" );
Expand Down
3 changes: 2 additions & 1 deletion src/providers/oracle/qgsoracleprovider.h
View file
Expand Up @@ -397,8 +397,9 @@ class QgsOracleProvider : public QgsVectorDataProvider
QgsFeatureId mFidCounter; //! next feature id if map is used
QgsOracleConn *mConnection;

QString mSpatialIndex; //! name of spatial index of geometry column
bool mHasSpatial; //! Oracle Spatial is installed
bool mHasSpatialIndex; //! Geometry column is indexed
QString mSpatialIndexName; //! name of spatial index of geometry column

QSharedPointer<QgsOracleSharedData> mShared;

Expand Down
21 changes: 7 additions & 14 deletions src/providers/oracle/qgsoraclesourceselect.cpp
View file
Expand Up @@ -330,11 +330,9 @@ void QgsOracleSourceSelect::on_cmbConnections_currentIndexChanged( const QString
cbxAllowGeometrylessTables->blockSignals( false );

// populate the table list
QgsDataSourceURI uri = QgsOracleConn::connUri( cmbConnections->currentText() );
mConnInfo = uri.connectionInfo();
mUseEstimatedMetadata = uri.useEstimatedMetadata();
mConnInfo = QgsOracleConn::connUri( cmbConnections->currentText() );

QgsDebugMsg( "Connection info: " + uri.connectionInfo() );
QgsDebugMsg( "Connection info: " + mConnInfo.uri() );

loadTableFromCache();
}
Expand Down Expand Up @@ -482,7 +480,7 @@ void QgsOracleSourceSelect::addTables()
if ( idx.column() != QgsOracleTableModel::dbtmTable )
continue;

QString uri = mTableModel.layerURI( mProxyModel.mapToSource( idx ), mConnInfo, mUseEstimatedMetadata );
QString uri = mTableModel.layerURI( mProxyModel.mapToSource( idx ), mConnInfo );
if ( uri.isNull() )
continue;

Expand Down Expand Up @@ -524,7 +522,7 @@ void QgsOracleSourceSelect::on_btnConnect_clicked()
mTablesTreeDelegate->setConnectionInfo( uri.connectionInfo() );

mColumnTypeThread = new QgsOracleColumnTypeThread( cmbConnections->currentText(),
mUseEstimatedMetadata,
uri.useEstimatedMetadata(),
cbxAllowGeometrylessTables->isChecked() );

connect( mColumnTypeThread, SIGNAL( setLayerType( QgsOracleLayerProperty ) ),
Expand Down Expand Up @@ -593,11 +591,6 @@ QStringList QgsOracleSourceSelect::selectedTables()
return mSelectedTables;
}

QString QgsOracleSourceSelect::connectionInfo()
{
return mConnInfo;
}

void QgsOracleSourceSelect::setSql( const QModelIndex &index )
{
if ( !index.parent().isValid() )
Expand All @@ -609,7 +602,7 @@ void QgsOracleSourceSelect::setSql( const QModelIndex &index )
QModelIndex idx = mProxyModel.mapToSource( index );
QString tableName = mTableModel.itemFromIndex( idx.sibling( idx.row(), QgsOracleTableModel::dbtmTable ) )->text();

QString uri = mTableModel.layerURI( idx, mConnInfo, mUseEstimatedMetadata );
QString uri = mTableModel.layerURI( idx, mConnInfo );
if ( uri.isNull() )
{
QgsDebugMsg( "no uri" );
Expand Down Expand Up @@ -670,16 +663,16 @@ void QgsOracleSourceSelect::loadTableFromCache()
mTableModel.removeRows( 0, mTableModel.rowCount( rootItemIndex ), rootItemIndex );

QString connName = cmbConnections->currentText();
QgsDataSourceURI uri = QgsOracleConn::connUri( connName );
QVector<QgsOracleLayerProperty> layers;
if ( !QgsOracleTableCache::loadFromCache( connName, _currentFlags( connName, mUseEstimatedMetadata, cbxAllowGeometrylessTables->isChecked() ), layers ) )
if ( !QgsOracleTableCache::loadFromCache( connName, _currentFlags( connName, uri.useEstimatedMetadata(), cbxAllowGeometrylessTables->isChecked() ), layers ) )
return;

foreach ( const QgsOracleLayerProperty& layerProperty, layers )
mTableModel.addTableEntry( layerProperty );

QApplication::setOverrideCursor( Qt::BusyCursor );

QgsDataSourceURI uri = QgsOracleConn::connUri( connName );

mIsConnected = true;
mTablesTreeDelegate->setConnectionInfo( uri.connectionInfo() );
Expand Down
9 changes: 3 additions & 6 deletions src/providers/oracle/qgsoraclesourceselect.h
View file
Expand Up @@ -53,7 +53,7 @@ class QgsOracleSourceSelectDelegate : public QItemDelegate
void setModelData( QWidget *editor, QAbstractItemModel *model, const QModelIndex &index ) const;
void setEditorData( QWidget *editor, const QModelIndex &index ) const;

void setConnectionInfo( const QString& connInfo ) { mConnInfo = connInfo; }
void setConnectionInfo( const QgsDataSourceURI& connInfo ) { mConnInfo = connInfo; }

protected:
void setConn( QgsOracleConn *conn ) const { if ( mConn ) mConn->disconnect(); mConn = conn; }
Expand All @@ -66,7 +66,7 @@ class QgsOracleSourceSelectDelegate : public QItemDelegate
}

private:
QString mConnInfo;
QgsDataSourceURI mConnInfo;
//! lazily initialized connection (to detect possible primary keys)
mutable QgsOracleConn *mConn;
};
Expand All @@ -92,8 +92,6 @@ class QgsOracleSourceSelect : public QDialog, private Ui::QgsDbSourceSelectBase
void populateConnectionList();
//! String list containing the selected tables
QStringList selectedTables();
//! Connection info (database, host, user, password)
QString connectionInfo();

signals:
void addDatabaseLayers( QStringList const & layerPathList, QString const & providerKey );
Expand Down Expand Up @@ -164,9 +162,8 @@ class QgsOracleSourceSelect : public QDialog, private Ui::QgsDbSourceSelectBase
QStringList mColumnLabels;
// Our thread for doing long running queries
QgsOracleColumnTypeThread *mColumnTypeThread;
QString mConnInfo;
QgsDataSourceURI mConnInfo;
QStringList mSelectedTables;
bool mUseEstimatedMetadata;
// Storage for the range of layer type icons
QMap<QString, QPair<QString, QIcon> > mLayerIcons;

Expand Down

0 comments on commit 991f94d

Please sign in to comment.