Improve mssql loading with predefined extent/pkey

qgis · Jan 13, 2021 · 64d5878 · 64d5878
1 parent 5ebab69
commit 64d5878
Show file tree

Hide file tree

Showing 12 changed files with 454 additions and 73 deletions.
diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -167,6 +167,30 @@ void QgsMssqlConnection::setGeometryColumnsOnly( const QString &name, bool enabl
   settings.setValue( "/MSSQL/connections/" + name + "/geometryColumnsOnly", enabled );
 }
 
+bool QgsMssqlConnection::extentInGeometryColumns( const QString &name )
+{
+  QgsSettings settings;
+  return settings.value( "/MSSQL/connections/" + name + "/extentInGeometryColumns", false ).toBool();
+}
+
+void QgsMssqlConnection::setExtentInGeometryColumns( const QString &name, bool enabled )
+{
+  QgsSettings settings;
+  settings.setValue( "/MSSQL/connections/" + name + "/extentInGeometryColumns", enabled );
+}
+
+bool QgsMssqlConnection::primaryKeyInGeometryColumns( const QString &name )
+{
+  QgsSettings settings;
+  return settings.value( "/MSSQL/connections/" + name + "/primaryKeyInGeometryColumns", false ).toBool();
+}
+
+void QgsMssqlConnection::setPrimaryKeyInGeometryColumns( const QString &name, bool enabled )
+{
+  QgsSettings settings;
+  settings.setValue( "/MSSQL/connections/" + name + "/primaryKeyInGeometryColumns", enabled );
+}
+
 bool QgsMssqlConnection::allowGeometrylessTables( const QString &name )
 {
   QgsSettings settings;

diff --git a/src/providers/mssql/qgsmssqlconnection.h b/src/providers/mssql/qgsmssqlconnection.h
@@ -63,6 +63,51 @@ class QgsMssqlConnection
      */
     static void setGeometryColumnsOnly( const QString &name, bool enabled );
 
+    /**
+     * Returns whether the connection with matching \a name should,
+     * use the extent manually specified in the geometry_columns table using additional
+     * QGIS-specific columns: qgis_xmin, qgis_xmax, qgis_ymin, qgis_ymax.
+     *
+     * This is an optional optimization that allows QGIS to skip extent calculation when loading
+     * layers and thus lowering the amount of time needed to load them. The disadvantage
+     * is that the extent needs to be manually set and updated by database admins and it requires
+     * adding custom columns to the geometry_columns table.
+     *
+     * \see setExtentInGeometryColumns()
+     */
+    static bool extentInGeometryColumns( const QString &name );
+
+    /**
+     * Sets whether the connection with matching \a name should
+     *
+     * \see extentInGeometryColumns()
+     */
+    static void setExtentInGeometryColumns( const QString &name, bool enabled );
+
+    /**
+     * Returns whether the connection with matching \a name should
+     * determine primary key's column name from a manually specified value in the geometry_columns table using
+     * an additional QGIS-specific column called "qgis_pkey". If more than one column is used for the primary key,
+     * value of "qgis_pkey" can contain multiple column names separated by comma.
+     *
+     * Note: this option only applies to views: for tables the primary key is automatically fetched from table definition.
+     *
+     * This is an optional optimization that allows QGIS to skip primary key calculation for views when loading
+     * layers and thus lowering the amount of time needed to load them. The disadvantage
+     * is that the primary key column name needs to be manually set and updated by database admins
+     * and it requires adding a custom column to the geometry_columns table.
+     *
+     * \see setPrimaryKeyInGeometryColumn()
+     */
+    static bool primaryKeyInGeometryColumns( const QString &name );
+
+    /**
+     * Sets whether the connection with matching \a name should
+     *
+     * \see primaryKeyInGeometryColumns()
+     */
+    static void setPrimaryKeyInGeometryColumns( const QString &name, bool enabled );
+
     /**
      * Returns true if the connection with matching \a name should
      * show geometryless tables when scanning for tables.

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -509,6 +509,12 @@ QString QgsMssqlLayerItem::createUri()
   uri.setUseEstimatedMetadata( QgsMssqlConnection::useEstimatedMetadata( connItem->name() ) );
   mDisableInvalidGeometryHandling = QgsMssqlConnection::isInvalidGeometryHandlingDisabled( connItem->name() );
   uri.setParam( QStringLiteral( "disableInvalidGeometryHandling" ), mDisableInvalidGeometryHandling ? QStringLiteral( "1" ) : QStringLiteral( "0" ) );
+  if ( QgsMssqlConnection::geometryColumnsOnly( connItem->name() ) )
+  {
+    uri.setParam( QStringLiteral( "extentInGeometryColumns" ), QgsMssqlConnection::extentInGeometryColumns( connItem->name() ) ? QStringLiteral( "1" ) : QStringLiteral( "0" ) );
+  }
+  if ( mLayerProperty.isView )
+    uri.setParam( QStringLiteral( "primaryKeyInGeometryColumns" ), QgsMssqlConnection::primaryKeyInGeometryColumns( connItem->name() ) ? QStringLiteral( "1" ) : QStringLiteral( "0" ) );
 
   QgsDebugMsgLevel( QStringLiteral( "layer uri: %1" ).arg( uri.uri() ), 3 );
   return uri.uri();

diff --git a/src/providers/mssql/qgsmssqlnewconnection.cpp b/src/providers/mssql/qgsmssqlnewconnection.cpp
@@ -45,9 +45,12 @@ QgsMssqlNewConnection::QgsMssqlNewConnection( QWidget *parent, const QString &co
   connect( txtHost, &QLineEdit::textChanged, this, &QgsMssqlNewConnection::updateOkButtonState );
   connect( listDatabase, &QListWidget::currentItemChanged, this, &QgsMssqlNewConnection::updateOkButtonState );
   connect( listDatabase, &QListWidget::currentItemChanged, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
-  connect( cb_geometryColumns,  &QCheckBox::clicked, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
+  connect( groupBoxGeometryColumns,  &QGroupBox::toggled, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
   connect( cb_allowGeometrylessTables,  &QCheckBox::clicked, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
 
+  connect( checkBoxExtentFromGeometryColumns, &QCheckBox::toggled, this, &QgsMssqlNewConnection::onExtentFromGeometryToggled );
+  connect( checkBoxPKFromGeometryColumns, &QCheckBox::toggled, this, &QgsMssqlNewConnection::onPrimaryKeyFromGeometryToggled );
+
   lblWarning->hide();
 
   if ( !connName.isEmpty() )
@@ -66,7 +69,9 @@ QgsMssqlNewConnection::QgsMssqlNewConnection( QWidget *parent, const QString &co
       mSchemaSettings = schemasVariant.toMap();
 
     listDatabase->setCurrentRow( 0 );
-    cb_geometryColumns->setChecked( QgsMssqlConnection::geometryColumnsOnly( connName ) );
+    groupBoxGeometryColumns->setChecked( QgsMssqlConnection::geometryColumnsOnly( connName ) );
+    whileBlocking( checkBoxExtentFromGeometryColumns )->setChecked( QgsMssqlConnection::extentInGeometryColumns( connName ) );
+    whileBlocking( checkBoxPKFromGeometryColumns )->setChecked( QgsMssqlConnection::primaryKeyInGeometryColumns( connName ) );
     cb_allowGeometrylessTables->setChecked( QgsMssqlConnection::allowGeometrylessTables( connName ) );
     cb_useEstimatedMetadata->setChecked( QgsMssqlConnection::useEstimatedMetadata( connName ) );
     mCheckNoInvalidGeometryHandling->setChecked( QgsMssqlConnection::isInvalidGeometryHandlingDisabled( connName ) );
@@ -147,7 +152,9 @@ void QgsMssqlNewConnection::accept()
 
   settings.setValue( baseKey + "/schemasFiltering", groupBoxSchemasFilter->isChecked() );
 
-  QgsMssqlConnection::setGeometryColumnsOnly( connName, cb_geometryColumns->isChecked() );
+  QgsMssqlConnection::setGeometryColumnsOnly( connName, groupBoxGeometryColumns->isChecked() );
+  QgsMssqlConnection::setExtentInGeometryColumns( connName, checkBoxExtentFromGeometryColumns->isChecked() && testExtentInGeometryColumns() );
+  QgsMssqlConnection::setPrimaryKeyInGeometryColumns( connName, checkBoxPKFromGeometryColumns->isChecked() && testPrimaryKeyInGeometryColumns() );
   QgsMssqlConnection::setAllowGeometrylessTables( connName, cb_allowGeometrylessTables->isChecked() );
   QgsMssqlConnection::setUseEstimatedMetadata( connName, cb_useEstimatedMetadata->isChecked() );
   QgsMssqlConnection::setInvalidGeometryHandlingDisabled( connName, mCheckNoInvalidGeometryHandling->isChecked() );
@@ -196,22 +203,7 @@ bool QgsMssqlNewConnection::testConnection( const QString &testDatabase )
     return false;
   }
 
-  QString database;
-  QListWidgetItem *item = listDatabase->currentItem();
-  if ( !testDatabase.isEmpty() )
-  {
-    database = testDatabase;
-  }
-  else if ( item && item->text() != QLatin1String( "(from service)" ) )
-  {
-    database = item->text();
-  }
-
-  QSqlDatabase db = QgsMssqlConnection::getDatabase( txtService->text().trimmed(),
-                    txtHost->text().trimmed(),
-                    database,
-                    txtUsername->text().trimmed(),
-                    txtPassword->text().trimmed() );
+  QSqlDatabase db = getDatabase( testDatabase );
 
   if ( db.isOpen() )
     db.close();
@@ -224,10 +216,6 @@ bool QgsMssqlNewConnection::testConnection( const QString &testDatabase )
   }
   else
   {
-    if ( database.isEmpty() )
-    {
-      database = txtService->text();
-    }
     bar->clearWidgets();
   }
 
@@ -243,11 +231,8 @@ void QgsMssqlNewConnection::listDatabases()
   listDatabase->clear();
   QString queryStr = QStringLiteral( "SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" );
 
-  QSqlDatabase db = QgsMssqlConnection::getDatabase( txtService->text().trimmed(),
-                    txtHost->text().trimmed(),
-                    QStringLiteral( "master" ),
-                    txtUsername->text().trimmed(),
-                    txtPassword->text().trimmed() );
+  QSqlDatabase db = getDatabase( QStringLiteral( "master" ) );
+
   if ( db.open() )
   {
     QSqlQuery query = QSqlQuery( db );
@@ -287,6 +272,27 @@ void QgsMssqlNewConnection::showHelp()
   QgsHelp::openHelp( QStringLiteral( "managing_data_source/opening_data.html#connecting-to-mssql-spatial" ) );
 }
 
+QSqlDatabase QgsMssqlNewConnection::getDatabase( const QString &name ) const
+{
+  QString database;
+  QListWidgetItem *item = listDatabase->currentItem();
+  if ( !name.isEmpty() )
+  {
+    database = name;
+  }
+  else if ( item && item->text() != QLatin1String( "(from service)" ) )
+  {
+    database = item->text();
+  }
+
+  return QgsMssqlConnection::getDatabase( txtService->text().trimmed(),
+                                          txtHost->text().trimmed(),
+                                          database,
+                                          txtUsername->text().trimmed(),
+                                          txtPassword->text().trimmed() );
+}
+
+
 void QgsMssqlNewConnection::updateOkButtonState()
 {
   QListWidgetItem *item = listDatabase->currentItem();
@@ -304,11 +310,7 @@ void QgsMssqlNewConnection::onCurrentDataBaseChange()
   if ( listDatabase->currentItem() )
     databaseName = listDatabase->currentItem()->text();
 
-  QSqlDatabase db = QgsMssqlConnection::getDatabase( txtService->text().trimmed(),
-                    txtHost->text().trimmed(),
-                    databaseName,
-                    txtUsername->text().trimmed(),
-                    txtPassword->text().trimmed() );
+  QSqlDatabase db = getDatabase();
 
   QStringList schemasList = QgsMssqlConnection::schemas( db, nullptr );
   int i = 0;
@@ -323,6 +325,66 @@ void QgsMssqlNewConnection::onCurrentDataBaseChange()
   mSchemaModel.setSettings( databaseName, schemasList, QgsMssqlConnection::excludedSchemasList( txtName->text(), databaseName ) );
 }
 
+void QgsMssqlNewConnection::onExtentFromGeometryToggled( bool checked )
+{
+  if ( !checked )
+  {
+    bar->clearWidgets();
+    return;
+  }
+
+  if ( !testExtentInGeometryColumns() )
+    bar->pushWarning( tr( "Use extent from geometry_columns table" ), tr( "Extent columns (qgis_xmin, qgis_ymin, qgis_xmax, qgis_ymax) not found." ) );
+  else
+    bar->pushInfo( tr( "Use extent from geometry_columns table" ), tr( "Extent columns found." ) );
+}
+
+void QgsMssqlNewConnection::onPrimaryKeyFromGeometryToggled( bool checked )
+{
+  if ( !checked )
+  {
+    bar->clearWidgets();
+    return;
+  }
+
+  if ( !testPrimaryKeyInGeometryColumns() )
+    bar->pushWarning( tr( "Use primary key(s) from geometry_columns table" ), tr( "Primary key column (qgs_pkey) not found." ) );
+  else
+    bar->pushInfo( tr( "Use primary key(s) from geometry_columns table" ), tr( "Primary key column found." ) );
+}
+
+bool QgsMssqlNewConnection::testExtentInGeometryColumns() const
+{
+  QSqlDatabase db = getDatabase();
+
+  if ( !QgsMssqlConnection::openDatabase( db ) )
+    return false;
+
+  QString queryStr = QStringLiteral( "SELECT qgis_xmin,qgis_xmax,qgis_ymin,qgis_ymax FROM geometry_columns" );
+  QSqlQuery query = QSqlQuery( db );
+  bool test = query.exec( queryStr );
+
+  db.close();
+
+  return test;
+}
+
+bool QgsMssqlNewConnection::testPrimaryKeyInGeometryColumns() const
+{
+  QSqlDatabase db = getDatabase();
+
+  if ( !QgsMssqlConnection::openDatabase( db ) )
+    return false;
+
+  QString queryStr = QStringLiteral( "SELECT qgis_pkey FROM geometry_columns" );
+  QSqlQuery query = QSqlQuery( db );
+  bool test = query.exec( queryStr );
+
+  db.close();
+
+  return test;
+}
+
 QgsMssqlNewConnection::SchemaModel::SchemaModel( QObject *parent ): QAbstractListModel( parent )
 {}
 

diff --git a/src/providers/mssql/qgsmssqlnewconnection.h b/src/providers/mssql/qgsmssqlnewconnection.h
@@ -21,6 +21,8 @@
 #include "qgshelp.h"
 #include <QAbstractListModel>
 
+#include <QSqlDatabase>
+
 /**
  * \class QgsMssqlNewConnection
  * \brief Dialog to allow the user to configure and save connection
@@ -50,6 +52,10 @@ class QgsMssqlNewConnection : public QDialog, private Ui::QgsMssqlNewConnectionB
     //! Updates state of the OK button depending of the filled fields
     void updateOkButtonState();
     void onCurrentDataBaseChange();
+
+    void onExtentFromGeometryToggled( bool checked );
+    void onPrimaryKeyFromGeometryToggled( bool checked );
+
   private:
     //! Class that reprents a model to display available schemas on a database and choose which will be displayed in QGIS
     class SchemaModel: public QAbstractListModel
@@ -87,6 +93,11 @@ class QgsMssqlNewConnection : public QDialog, private Ui::QgsMssqlNewConnectionB
     QVariantMap mSchemaSettings; //store the schema settings edited during this QDialog life time
     SchemaModel mSchemaModel;
 
+    QSqlDatabase getDatabase( const QString &name = QString() ) const;
+
+    bool testExtentInGeometryColumns() const;
+
+    bool testPrimaryKeyInGeometryColumns() const;
 };
 
 #endif //  QGSMSSQLNEWCONNECTION_H