Skip to content

Commit 2273dfa

Browse files
nyalldawsonnyalldawson
committedApr 22, 2018
[FEATURE][oracle] Allow restricting table list for a connection
to a specific schema This allows a schema to be set in the connection properties for an oracle db connection. If set, only tables within that schema will be scanned and listed for the connection. Previously the option existed to restrict the scan to tables which belong to the user, but this option does not support the use case where a connection must access tables from a different user, and the default "scan everything" setting is too expensive (since it often takes multiple minutes to perform, especially when geometryless tables are shown). Sponsored by Open Spatial (http://www.openspatial.com)
·
ltr-3_40final-3_2_0
1 parent 31faa11 commit 2273dfa

9 files changed

+186
-120
lines changed
 

‎src/gui/qgsmanageconnectionsdialog.cpp

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -548,6 +548,7 @@ QDomDocument QgsManageConnectionsDialog::saveOracleConnections( const QStringLis
548548
el.setAttribute( QStringLiteral( "database" ), settings.value( path + "/database", "" ).toString() );
549549
el.setAttribute( QStringLiteral( "dboptions" ), settings.value( path + "/dboptions", "" ).toString() );
550550
el.setAttribute( QStringLiteral( "dbworkspace" ), settings.value( path + "/dbworkspace", "" ).toString() );
551+
el.setAttribute( QStringLiteral( "schema" ), settings.value( path + "/schema", QString() ).toString() );
551552
el.setAttribute( QStringLiteral( "estimatedMetadata" ), settings.value( path + "/estimatedMetadata", "0" ).toString() );
552553
el.setAttribute( QStringLiteral( "userTablesOnly" ), settings.value( path + "/userTablesOnly", "0" ).toString() );
553554
el.setAttribute( QStringLiteral( "geometryColumnsOnly" ), settings.value( path + "/geometryColumnsOnly", "0" ).toString() );
@@ -1088,6 +1089,7 @@ void QgsManageConnectionsDialog::loadOracleConnections( const QDomDocument &doc,
10881089
settings.setValue( QStringLiteral( "/database" ), child.attribute( QStringLiteral( "database" ) ) );
10891090
settings.setValue( QStringLiteral( "/dboptions" ), child.attribute( QStringLiteral( "dboptions" ) ) );
10901091
settings.setValue( QStringLiteral( "/dbworkspace" ), child.attribute( QStringLiteral( "dbworkspace" ) ) );
1092+
settings.setValue( QStringLiteral( "/schema" ), child.attribute( QStringLiteral( "schema" ) ) );
10911093
settings.setValue( QStringLiteral( "/estimatedMetadata" ), child.attribute( QStringLiteral( "estimatedMetadata" ) ) );
10921094
settings.setValue( QStringLiteral( "/userTablesOnly" ), child.attribute( QStringLiteral( "userTablesOnly" ) ) );
10931095
settings.setValue( QStringLiteral( "/geometryColumnsOnly" ), child.attribute( QStringLiteral( "geometryColumnsOnly" ) ) );

‎src/providers/oracle/qgsoraclecolumntypethread.cpp

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,9 +21,10 @@ email : jef at norbit dot de
2121

2222
#include <QMetaType>
2323

24-
QgsOracleColumnTypeThread::QgsOracleColumnTypeThread( const QString &name, bool useEstimatedMetadata, bool allowGeometrylessTables )
24+
QgsOracleColumnTypeThread::QgsOracleColumnTypeThread( const QString &name, const QString &limitToSchema, bool useEstimatedMetadata, bool allowGeometrylessTables )
2525
: QThread()
2626
, mName( name )
27+
, mSchema( limitToSchema )
2728
, mUseEstimatedMetadata( useEstimatedMetadata )
2829
, mAllowGeometrylessTables( allowGeometrylessTables )
2930
, mStopped( false )
@@ -52,6 +53,7 @@ void QgsOracleColumnTypeThread::run()
5253
emit progressMessage( tr( "Retrieving tables of %1…" ).arg( mName ) );
5354
QVector<QgsOracleLayerProperty> layerProperties;
5455
if ( !conn->supportedLayers( layerProperties,
56+
mSchema,
5557
QgsOracleConn::geometryColumnsOnly( mName ),
5658
QgsOracleConn::userTablesOnly( mName ),
5759
mAllowGeometrylessTables ) ||

‎src/providers/oracle/qgsoraclecolumntypethread.h

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,16 @@ class QgsOracleColumnTypeThread : public QThread
2828
{
2929
Q_OBJECT
3030
public:
31+
32+
/**
33+
*
34+
* \param connName
35+
* \param limitToSchema If specified, only tables from this schema will be scanned
36+
* \param useEstimatedMetaData
37+
* \param allowGeometrylessTables
38+
*/
3139
QgsOracleColumnTypeThread( const QString &connName,
40+
const QString &limitToSchema,
3241
bool useEstimatedMetaData,
3342
bool allowGeometrylessTables );
3443

@@ -54,6 +63,7 @@ class QgsOracleColumnTypeThread : public QThread
5463
QgsOracleColumnTypeThread() = default;
5564

5665
QString mName;
66+
QString mSchema;
5767
bool mUseEstimatedMetadata = false;
5868
bool mAllowGeometrylessTables = false;
5969
bool mStopped = false;

‎src/providers/oracle/qgsoracleconn.cpp

Lines changed: 14 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -240,7 +240,7 @@ QStringList QgsOracleConn::pkCandidates( const QString &ownerName, const QString
240240
return cols;
241241
}
242242

243-
bool QgsOracleConn::tableInfo( bool geometryColumnsOnly, bool userTablesOnly, bool allowGeometrylessTables )
243+
bool QgsOracleConn::tableInfo( const QString &schema, bool geometryColumnsOnly, bool userTablesOnly, bool allowGeometrylessTables )
244244
{
245245
QgsDebugMsgLevel( QStringLiteral( "Entering." ), 4 );
246246

@@ -258,16 +258,18 @@ bool QgsOracleConn::tableInfo( bool geometryColumnsOnly, bool userTablesOnly, bo
258258
.arg( geometryColumnsOnly ? QStringLiteral( "c.srid" ) : QStringLiteral( "NULL AS srid" ) )
259259
.arg( prefix )
260260
.arg( geometryColumnsOnly ? QStringLiteral( "sdo_geom_metadata" ) : QStringLiteral( "tab_columns" ) )
261-
.arg( userTablesOnly ? QStringLiteral( "" ) : QStringLiteral( " AND c.owner='xxxxSCHEMAxxxx'" ) )
261+
.arg( userTablesOnly ? QString() : QStringLiteral( " AND c.owner=%1" ).arg( schema.isEmpty() ? QStringLiteral( "o.owner" ) : quotedValue( schema ) ) )
262262
.arg( geometryColumnsOnly ? QString() : QStringLiteral( " WHERE c.data_type='SDO_GEOMETRY'" ) );
263263

264264
if ( allowGeometrylessTables )
265265
{
266266

267267
// also here!
268268
sql += QStringLiteral( " UNION SELECT %1,object_name,NULL AS column_name,NULL AS srid,object_type AS type"
269-
" FROM %2_objects c WHERE c.object_type IN ('TABLE','VIEW','SYNONYM')" )
270-
.arg( owner, prefix );
269+
" FROM %2_objects c WHERE c.object_type IN ('TABLE','VIEW','SYNONYM') %3" )
270+
.arg( owner,
271+
prefix,
272+
userTablesOnly || schema.isEmpty() ? QString() : QStringLiteral( " AND c.owner=%1" ).arg( quotedValue( schema ) ) );
271273
}
272274

273275
// sql = "SELECT * FROM (" + sql + ")";
@@ -302,10 +304,10 @@ bool QgsOracleConn::tableInfo( bool geometryColumnsOnly, bool userTablesOnly, bo
302304
return true;
303305
}
304306

305-
bool QgsOracleConn::supportedLayers( QVector<QgsOracleLayerProperty> &layers, bool geometryTablesOnly, bool userTablesOnly, bool allowGeometrylessTables )
307+
bool QgsOracleConn::supportedLayers( QVector<QgsOracleLayerProperty> &layers, const QString &limitToSchema, bool geometryTablesOnly, bool userTablesOnly, bool allowGeometrylessTables )
306308
{
307309
// Get the list of supported tables
308-
if ( !tableInfo( geometryTablesOnly, userTablesOnly, allowGeometrylessTables ) )
310+
if ( !tableInfo( limitToSchema, geometryTablesOnly, userTablesOnly, allowGeometrylessTables ) )
309311
{
310312
QgsMessageLog::logMessage( tr( "Unable to get list of spatially enabled tables from the database" ), tr( "Oracle" ) );
311313
return false;
@@ -786,6 +788,12 @@ bool QgsOracleConn::userTablesOnly( const QString &connName )
786788
return settings.value( "/Oracle/connections/" + connName + "/userTablesOnly", false ).toBool();
787789
}
788790

791+
QString QgsOracleConn::restrictToSchema( const QString &connName )
792+
{
793+
QgsSettings settings;
794+
return settings.value( "/Oracle/connections/" + connName + "/schema" ).toString();
795+
}
796+
789797
bool QgsOracleConn::geometryColumnsOnly( const QString &connName )
790798
{
791799
QgsSettings settings;

‎src/providers/oracle/qgsoracleconn.h

Lines changed: 15 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -127,16 +127,27 @@ class QgsOracleConn : public QObject
127127
*/
128128
static QString quotedValue( const QVariant &value, QVariant::Type type = QVariant::Invalid );
129129

130-
//! Get the list of supported layers
130+
/**
131+
* Get the list of supported layers.
132+
*
133+
* If \a limitToSchema is specified, than only layers from the matching schema will be
134+
* returned.
135+
*
136+
*/
131137
bool supportedLayers( QVector<QgsOracleLayerProperty> &layers,
138+
const QString &limitToSchema,
132139
bool geometryTablesOnly,
133140
bool userTablesOnly = true,
134141
bool allowGeometrylessTables = false );
135142

136143
void retrieveLayerTypes( QgsOracleLayerProperty &layerProperty, bool useEstimatedMetadata, bool onlyExistingTypes );
137144

138-
//! Gets information about the spatial tables
139-
bool tableInfo( bool geometryTablesOnly, bool userTablesOnly, bool allowGeometrylessTables );
145+
/**
146+
* Gets information about the spatial tables.
147+
*
148+
* If \a schema is specified, only tables from this schema will be retrieved.
149+
*/
150+
bool tableInfo( const QString &schema, bool geometryTablesOnly, bool userTablesOnly, bool allowGeometrylessTables );
140151

141152
//! Get primary key candidates (all int4 columns)
142153
QStringList pkCandidates( const QString &ownerName, const QString &viewName );
@@ -163,6 +174,7 @@ class QgsOracleConn : public QObject
163174
static void setSelectedConnection( const QString &connName );
164175
static QgsDataSourceUri connUri( const QString &connName );
165176
static bool userTablesOnly( const QString &connName );
177+
static QString restrictToSchema( const QString &connName );
166178
static bool geometryColumnsOnly( const QString &connName );
167179
static bool allowGeometrylessTables( const QString &connName );
168180
static bool estimatedMetadata( const QString &connName );

‎src/providers/oracle/qgsoracledataitems.cpp

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,7 @@ QVector<QgsDataItem *> QgsOracleConnectionItem::createChildren()
9191
if ( !mColumnTypeThread )
9292
{
9393
mColumnTypeThread = new QgsOracleColumnTypeThread( mName,
94+
QgsOracleConn::restrictToSchema( mName ),
9495
/* useEstimatedMetadata */ true,
9596
QgsOracleConn::allowGeometrylessTables( mName ) );
9697

‎src/providers/oracle/qgsoraclenewconnection.cpp

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,9 @@ QgsOracleNewConnection::QgsOracleNewConnection( QWidget *parent, const QString &
3030
, mOriginalConnName( connName )
3131
{
3232
setupUi( this );
33+
34+
txtSchema->setShowClearButton( true );
35+
3336
connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsOracleNewConnection::showHelp );
3437
connect( btnConnect, &QPushButton::clicked, this, &QgsOracleNewConnection::testConnection );
3538

@@ -50,6 +53,7 @@ QgsOracleNewConnection::QgsOracleNewConnection( QWidget *parent, const QString &
5053
txtPort->setText( port );
5154
txtOptions->setText( settings.value( key + QStringLiteral( "/dboptions" ) ).toString() );
5255
txtWorkspace->setText( settings.value( key + QStringLiteral( "/dbworkspace" ) ).toString() );
56+
txtSchema->setText( settings.value( key + QStringLiteral( "/schema" ) ).toString() );
5357
cb_userTablesOnly->setChecked( settings.value( key + QStringLiteral( "/userTablesOnly" ), false ).toBool() );
5458
cb_geometryColumnsOnly->setChecked( settings.value( key + QStringLiteral( "/geometryColumnsOnly" ), true ).toBool() );
5559
cb_allowGeometrylessTables->setChecked( settings.value( key + QStringLiteral( "/allowGeometrylessTables" ), false ).toBool() );
@@ -136,6 +140,7 @@ void QgsOracleNewConnection::accept()
136140
settings.setValue( baseKey + QStringLiteral( "/savePassword" ), chkStorePassword->isChecked() ? QStringLiteral( "true" ) : QStringLiteral( "false" ) );
137141
settings.setValue( baseKey + QStringLiteral( "/dboptions" ), txtOptions->text() );
138142
settings.setValue( baseKey + QStringLiteral( "/dbworkspace" ), txtWorkspace->text() );
143+
settings.setValue( baseKey + QStringLiteral( "/schema" ), txtSchema->text() );
139144

140145
QDialog::accept();
141146
}

‎src/providers/oracle/qgsoraclesourceselect.cpp

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -44,7 +44,7 @@ QWidget *QgsOracleSourceSelectDelegate::createEditor( QWidget *parent, const QSt
4444

4545
QString tableName = index.sibling( index.row(), QgsOracleTableModel::DbtmTable ).data( Qt::DisplayRole ).toString();
4646
if ( tableName.isEmpty() )
47-
return 0;
47+
return nullptr;
4848

4949
if ( index.column() == QgsOracleTableModel::DbtmSql )
5050
{
@@ -515,6 +515,7 @@ void QgsOracleSourceSelect::on_btnConnect_clicked()
515515
mTablesTreeDelegate->setConnectionInfo( uri );
516516

517517
mColumnTypeThread = new QgsOracleColumnTypeThread( cmbConnections->currentText(),
518+
QgsOracleConn::restrictToSchema( cmbConnections->currentText() ),
518519
uri.useEstimatedMetadata(),
519520
cbxAllowGeometrylessTables->isChecked() );
520521

‎src/ui/qgsoraclenewconnectionbase.ui

Lines changed: 134 additions & 109 deletions
Large diffs are not rendered by default.

0 commit comments

Comments
 (0)
Please sign in to comment.