1 /***************************************************************************
2 qgsmssqlnewconnection.cpp - description
3 -------------------
4 begin : 2011-10-08
5 copyright : (C) 2011 by Tamas Szekeres
6 email : szekerest at gmail.com
7 ***************************************************************************/
8
9 /***************************************************************************
10 * *
11 * This program is free software; you can redistribute it and/or modify *
12 * it under the terms of the GNU General Public License as published by *
13 * the Free Software Foundation; either version 2 of the License, or *
14 * (at your option) any later version. *
15 * *
16 ***************************************************************************/
17
18 #include <QInputDialog>
19 #include <QMessageBox>
20 #include <QSqlDatabase>
21 #include <QSqlError>
22 #include <QRegularExpression>
23 #include <QRegularExpressionValidator>
24 #include <QMenu>
25
26 #include "qgsmssqlnewconnection.h"
27 #include "qgsmssqlprovider.h"
28 #include "qgssettings.h"
29 #include "qgsmssqlconnection.h"
30 #include "qgsmssqldatabase.h"
31 #include "qgsgui.h"
32
QgsMssqlNewConnection(QWidget * parent,const QString & connName,Qt::WindowFlags fl)33 QgsMssqlNewConnection::QgsMssqlNewConnection( QWidget *parent, const QString &connName, Qt::WindowFlags fl )
34 : QDialog( parent, fl )
35 , mOriginalConnName( connName )
36 {
37 setupUi( this );
38 QgsGui::enableAutoGeometryRestore( this );
39
40 connect( btnListDatabase, &QPushButton::clicked, this, &QgsMssqlNewConnection::btnListDatabase_clicked );
41 connect( btnConnect, &QPushButton::clicked, this, &QgsMssqlNewConnection::btnConnect_clicked );
42 connect( cb_trustedConnection, &QCheckBox::clicked, this, &QgsMssqlNewConnection::cb_trustedConnection_clicked );
43 connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsMssqlNewConnection::showHelp );
44
45 buttonBox->button( QDialogButtonBox::Ok )->setDisabled( true );
46 connect( txtName, &QLineEdit::textChanged, this, &QgsMssqlNewConnection::updateOkButtonState );
47 connect( txtService, &QLineEdit::textChanged, this, &QgsMssqlNewConnection::updateOkButtonState );
48 connect( txtHost, &QLineEdit::textChanged, this, &QgsMssqlNewConnection::updateOkButtonState );
49 connect( listDatabase, &QListWidget::currentItemChanged, this, &QgsMssqlNewConnection::updateOkButtonState );
50 connect( listDatabase, &QListWidget::currentItemChanged, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
51 connect( groupBoxGeometryColumns, &QGroupBox::toggled, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
52 connect( cb_allowGeometrylessTables, &QCheckBox::clicked, this, &QgsMssqlNewConnection::onCurrentDataBaseChange );
53
54 connect( checkBoxExtentFromGeometryColumns, &QCheckBox::toggled, this, &QgsMssqlNewConnection::onExtentFromGeometryToggled );
55 connect( checkBoxPKFromGeometryColumns, &QCheckBox::toggled, this, &QgsMssqlNewConnection::onPrimaryKeyFromGeometryToggled );
56
57 lblWarning->hide();
58
59 if ( !connName.isEmpty() )
60 {
61 // populate the dialog with the information stored for the connection
62 // populate the fields with the stored setting parameters
63 const QgsSettings settings;
64
65 const QString key = "/MSSQL/connections/" + connName;
66 txtService->setText( settings.value( key + "/service" ).toString() );
67 txtHost->setText( settings.value( key + "/host" ).toString() );
68 listDatabase->addItem( settings.value( key + "/database" ).toString() );
69 groupBoxSchemasFilter->setChecked( settings.value( key + "/schemasFiltering" ).toBool() );
70 const QVariant schemasVariant = settings.value( key + "/excludedSchemas" );
71 if ( schemasVariant.isValid() && schemasVariant.type() == QVariant::Map )
72 mSchemaSettings = schemasVariant.toMap();
73
74 listDatabase->setCurrentRow( 0 );
75 groupBoxGeometryColumns->setChecked( QgsMssqlConnection::geometryColumnsOnly( connName ) );
76 whileBlocking( checkBoxExtentFromGeometryColumns )->setChecked( QgsMssqlConnection::extentInGeometryColumns( connName ) );
77 whileBlocking( checkBoxPKFromGeometryColumns )->setChecked( QgsMssqlConnection::primaryKeyInGeometryColumns( connName ) );
78 cb_allowGeometrylessTables->setChecked( QgsMssqlConnection::allowGeometrylessTables( connName ) );
79 cb_useEstimatedMetadata->setChecked( QgsMssqlConnection::useEstimatedMetadata( connName ) );
80 mCheckNoInvalidGeometryHandling->setChecked( QgsMssqlConnection::isInvalidGeometryHandlingDisabled( connName ) );
81
82 if ( settings.value( key + "/saveUsername" ).toString() == QLatin1String( "true" ) )
83 {
84 txtUsername->setText( settings.value( key + "/username" ).toString() );
85 chkStoreUsername->setChecked( true );
86 cb_trustedConnection->setChecked( false );
87 }
88
89 if ( settings.value( key + "/savePassword" ).toString() == QLatin1String( "true" ) )
90 {
91 txtPassword->setText( settings.value( key + "/password" ).toString() );
92 chkStorePassword->setChecked( true );
93 }
94
95 txtName->setText( connName );
96 }
97 txtName->setValidator( new QRegularExpressionValidator( QRegularExpression( QStringLiteral( "[^\\/]+" ) ), txtName ) );
98 cb_trustedConnection_clicked();
99
100 schemaView->setModel( &mSchemaModel );
101 schemaView->setContextMenuPolicy( Qt::CustomContextMenu );
102
103 connect( schemaView, &QWidget::customContextMenuRequested, this, [this]( const QPoint & p )
104 {
105 QMenu menu;
106 menu.addAction( tr( "Check All" ), this, [this]
107 {
108 mSchemaModel.checkAll();
109 } );
110
111 menu.addAction( tr( "Uncheck All" ), this, [this]
112 {
113 mSchemaModel.unCheckAll();
114 } );
115
116 menu.exec( this->schemaView->viewport()->mapToGlobal( p ) );
117 }
118 );
119 onCurrentDataBaseChange();
120
121 groupBoxSchemasFilter->setCollapsed( !groupBoxSchemasFilter->isChecked() );
122 }
123
124 //! Autoconnected SLOTS
accept()125 void QgsMssqlNewConnection::accept()
126 {
127 QgsSettings settings;
128 QString baseKey = QStringLiteral( "/MSSQL/connections/" );
129 settings.setValue( baseKey + "selected", txtName->text() );
130
131 // warn if entry was renamed to an existing connection
132 if ( ( mOriginalConnName.isNull() || mOriginalConnName.compare( txtName->text(), Qt::CaseInsensitive ) != 0 ) &&
133 ( settings.contains( baseKey + txtName->text() + "/service" ) ||
134 settings.contains( baseKey + txtName->text() + "/host" ) ) &&
135 QMessageBox::question( this,
136 tr( "Save Connection" ),
137 tr( "Should the existing connection %1 be overwritten?" ).arg( txtName->text() ),
138 QMessageBox::Ok | QMessageBox::Cancel ) == QMessageBox::Cancel )
139 {
140 return;
141 }
142
143 // on rename delete the original entry first
144 if ( !mOriginalConnName.isNull() && mOriginalConnName != txtName->text() )
145 {
146 settings.remove( baseKey + mOriginalConnName );
147 settings.sync();
148 }
149
150 const QString connName = txtName->text();
151 baseKey += connName;
152 QString database;
153 QListWidgetItem *item = listDatabase->currentItem();
154 if ( item && item->text() != QLatin1String( "(from service)" ) )
155 {
156 database = item->text();
157 }
158
159 settings.setValue( baseKey + "/service", txtService->text() );
160 settings.setValue( baseKey + "/host", txtHost->text() );
161 settings.setValue( baseKey + "/database", database );
162 settings.setValue( baseKey + "/username", chkStoreUsername->isChecked() ? txtUsername->text() : QString() );
163 settings.setValue( baseKey + "/password", chkStorePassword->isChecked() ? txtPassword->text() : QString() );
164 settings.setValue( baseKey + "/saveUsername", chkStoreUsername->isChecked() ? "true" : "false" );
165 settings.setValue( baseKey + "/savePassword", chkStorePassword->isChecked() ? "true" : "false" );
166
167 if ( groupBoxSchemasFilter->isChecked() )
168 {
169 if ( !mSchemaModel.dataBaseName().isEmpty() )
170 mSchemaSettings.insert( mSchemaModel.dataBaseName(), mSchemaModel.uncheckedSchemas() );
171 settings.setValue( baseKey + "/excludedSchemas", mSchemaSettings );
172 }
173
174 settings.setValue( baseKey + "/schemasFiltering", groupBoxSchemasFilter->isChecked() );
175
176 QgsMssqlConnection::setGeometryColumnsOnly( connName, groupBoxGeometryColumns->isChecked() );
177 QgsMssqlConnection::setExtentInGeometryColumns( connName, checkBoxExtentFromGeometryColumns->isChecked() && testExtentInGeometryColumns() );
178 QgsMssqlConnection::setPrimaryKeyInGeometryColumns( connName, checkBoxPKFromGeometryColumns->isChecked() && testPrimaryKeyInGeometryColumns() );
179 QgsMssqlConnection::setAllowGeometrylessTables( connName, cb_allowGeometrylessTables->isChecked() );
180 QgsMssqlConnection::setUseEstimatedMetadata( connName, cb_useEstimatedMetadata->isChecked() );
181 QgsMssqlConnection::setInvalidGeometryHandlingDisabled( connName, mCheckNoInvalidGeometryHandling->isChecked() );
182
183 QDialog::accept();
184 }
185
btnConnect_clicked()186 void QgsMssqlNewConnection::btnConnect_clicked()
187 {
188 testConnection();
189 }
190
btnListDatabase_clicked()191 void QgsMssqlNewConnection::btnListDatabase_clicked()
192 {
193 listDatabases();
194 }
195
cb_trustedConnection_clicked()196 void QgsMssqlNewConnection::cb_trustedConnection_clicked()
197 {
198 if ( cb_trustedConnection->checkState() == Qt::Checked )
199 {
200 txtUsername->setEnabled( false );
201 txtUsername->clear();
202 txtPassword->setEnabled( false );
203 txtPassword->clear();
204 }
205 else
206 {
207 txtUsername->setEnabled( true );
208 txtPassword->setEnabled( true );
209 }
210 }
211
212 //! End Autoconnected SLOTS
213
testConnection(const QString & testDatabase)214 bool QgsMssqlNewConnection::testConnection( const QString &testDatabase )
215 {
216 bar->pushMessage( tr( "Testing connection" ), tr( "……" ) );
217 // Gross but needed to show the last message.
218 qApp->processEvents();
219
220 if ( txtService->text().isEmpty() && txtHost->text().isEmpty() )
221 {
222 bar->clearWidgets();
223 bar->pushWarning( tr( "Connection Failed" ), tr( "Host name hasn't been specified." ) );
224 return false;
225 }
226
227 std::shared_ptr<QgsMssqlDatabase> db = getDatabase( testDatabase );
228
229 if ( !db->isValid() )
230 {
231 bar->clearWidgets();
232 bar->pushWarning( tr( "Error opening connection" ), db->errorText() );
233 return false;
234 }
235 else
236 {
237 bar->clearWidgets();
238 }
239
240 return true;
241 }
242
listDatabases()243 void QgsMssqlNewConnection::listDatabases()
244 {
245 testConnection( QStringLiteral( "master" ) );
246 QString currentDataBase;
247 if ( listDatabase->currentItem() )
248 currentDataBase = listDatabase->currentItem()->text();
249 listDatabase->clear();
250 const QString queryStr = QStringLiteral( "SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')" );
251
252 std::shared_ptr<QgsMssqlDatabase> db = getDatabase( QStringLiteral( "master" ) );
253
254 if ( db->isValid() )
255 {
256 QSqlQuery query = QSqlQuery( db->db() );
257 query.setForwardOnly( true );
258 ( void )query.exec( queryStr );
259
260 if ( !txtService->text().isEmpty() )
261 {
262 listDatabase->addItem( QStringLiteral( "(from service)" ) );
263 }
264
265 if ( query.isActive() )
266 {
267 while ( query.next() )
268 {
269 const QString name = query.value( 0 ).toString();
270 listDatabase->addItem( name );
271 }
272 listDatabase->setCurrentRow( 0 );
273 }
274 }
275
276 for ( int i = 0; i < listDatabase->count(); ++i )
277 {
278 if ( listDatabase->item( i )->text() == currentDataBase )
279 {
280 listDatabase->setCurrentRow( i );
281 break;
282 }
283 }
284 onCurrentDataBaseChange();
285 }
286
showHelp()287 void QgsMssqlNewConnection::showHelp()
288 {
289 QgsHelp::openHelp( QStringLiteral( "managing_data_source/opening_data.html#connecting-to-mssql-spatial" ) );
290 }
291
getDatabase(const QString & name) const292 std::shared_ptr<QgsMssqlDatabase> QgsMssqlNewConnection::getDatabase( const QString &name ) const
293 {
294 QString database;
295 QListWidgetItem *item = listDatabase->currentItem();
296 if ( !name.isEmpty() )
297 {
298 database = name;
299 }
300 else if ( item && item->text() != QLatin1String( "(from service)" ) )
301 {
302 database = item->text();
303 }
304
305 return QgsMssqlDatabase::connectDb( txtService->text().trimmed(),
306 txtHost->text().trimmed(),
307 database,
308 txtUsername->text().trimmed(),
309 txtPassword->text().trimmed() );
310 }
311
312
updateOkButtonState()313 void QgsMssqlNewConnection::updateOkButtonState()
314 {
315 QListWidgetItem *item = listDatabase->currentItem();
316 const bool disabled = txtName->text().isEmpty() || ( txtService->text().isEmpty() && txtHost->text().isEmpty() ) || !item;
317 buttonBox->button( QDialogButtonBox::Ok )->setDisabled( disabled );
318 }
319
onCurrentDataBaseChange()320 void QgsMssqlNewConnection::onCurrentDataBaseChange()
321 {
322 //First store the schema settings for the previous dataBase
323 if ( !mSchemaModel.dataBaseName().isEmpty() )
324 mSchemaSettings.insert( mSchemaModel.dataBaseName(), mSchemaModel.uncheckedSchemas() );
325
326 QString databaseName;
327 if ( listDatabase->currentItem() )
328 databaseName = listDatabase->currentItem()->text();
329
330 std::shared_ptr<QgsMssqlDatabase> db = getDatabase();
331
332 QStringList schemasList = QgsMssqlConnection::schemas( db, nullptr );
333 int i = 0;
334 while ( i < schemasList.count() )
335 {
336 if ( QgsMssqlConnection::isSystemSchema( schemasList.at( i ) ) )
337 schemasList.removeAt( i );
338 else
339 ++i;
340 }
341
342 mSchemaModel.setSettings( databaseName, schemasList, QgsMssqlConnection::excludedSchemasList( txtName->text(), databaseName ) );
343 }
344
onExtentFromGeometryToggled(bool checked)345 void QgsMssqlNewConnection::onExtentFromGeometryToggled( bool checked )
346 {
347 if ( !checked )
348 {
349 bar->clearWidgets();
350 return;
351 }
352
353 if ( !testExtentInGeometryColumns() )
354 bar->pushWarning( tr( "Use extent from geometry_columns table" ), tr( "Extent columns (qgis_xmin, qgis_ymin, qgis_xmax, qgis_ymax) not found." ) );
355 else
356 bar->pushInfo( tr( "Use extent from geometry_columns table" ), tr( "Extent columns found." ) );
357 }
358
onPrimaryKeyFromGeometryToggled(bool checked)359 void QgsMssqlNewConnection::onPrimaryKeyFromGeometryToggled( bool checked )
360 {
361 if ( !checked )
362 {
363 bar->clearWidgets();
364 return;
365 }
366
367 if ( !testPrimaryKeyInGeometryColumns() )
368 bar->pushWarning( tr( "Use primary key(s) from geometry_columns table" ), tr( "Primary key column (qgs_pkey) not found." ) );
369 else
370 bar->pushInfo( tr( "Use primary key(s) from geometry_columns table" ), tr( "Primary key column found." ) );
371 }
372
373
testExtentInGeometryColumns() const374 bool QgsMssqlNewConnection::testExtentInGeometryColumns() const
375 {
376 std::shared_ptr<QgsMssqlDatabase> db = getDatabase();
377 if ( !db->isValid() )
378 return false;
379
380 const QString queryStr = QStringLiteral( "SELECT qgis_xmin,qgis_xmax,qgis_ymin,qgis_ymax FROM geometry_columns" );
381 QSqlQuery query = QSqlQuery( db->db() );
382 const bool test = query.exec( queryStr );
383
384 return test;
385 }
386
testPrimaryKeyInGeometryColumns() const387 bool QgsMssqlNewConnection::testPrimaryKeyInGeometryColumns() const
388 {
389 std::shared_ptr<QgsMssqlDatabase> db = getDatabase();
390 if ( !db->isValid() )
391 return false;
392
393 const QString queryStr = QStringLiteral( "SELECT qgis_pkey FROM geometry_columns" );
394 QSqlQuery query = QSqlQuery( db->db() );
395 const bool test = query.exec( queryStr );
396
397 return test;
398 }
399
SchemaModel(QObject * parent)400 QgsMssqlNewConnection::SchemaModel::SchemaModel( QObject *parent ): QAbstractListModel( parent )
401 {}
402
rowCount(const QModelIndex & parent) const403 int QgsMssqlNewConnection::SchemaModel::rowCount( const QModelIndex &parent ) const
404 {
405 Q_UNUSED( parent )
406 return mSchemas.count();
407 }
408
data(const QModelIndex & index,int role) const409 QVariant QgsMssqlNewConnection::SchemaModel::data( const QModelIndex &index, int role ) const
410 {
411 if ( !index.isValid() || index.row() >= mSchemas.count() )
412 return QVariant();
413
414
415 switch ( role )
416 {
417 case Qt::CheckStateRole:
418 if ( mExcludedSchemas.contains( mSchemas.at( index.row() ) ) )
419 return Qt::CheckState::Unchecked;
420 else
421 return Qt::CheckState::Checked;
422 break;
423 case Qt::DisplayRole:
424 return mSchemas.at( index.row() );
425 break;
426 default:
427 return QVariant();
428 }
429
430 return QVariant();
431 }
432
setData(const QModelIndex & index,const QVariant & value,int role)433 bool QgsMssqlNewConnection::SchemaModel::setData( const QModelIndex &index, const QVariant &value, int role )
434 {
435 if ( !index.isValid() || index.row() >= mSchemas.count() )
436 return false;
437
438 switch ( role )
439 {
440 case Qt::CheckStateRole:
441 if ( value == Qt::Checked && mExcludedSchemas.contains( mSchemas.at( index.row() ) ) )
442 mExcludedSchemas.removeOne( mSchemas.at( index.row() ) );
443 else if ( value == Qt::Unchecked && !mExcludedSchemas.contains( mSchemas.at( index.row() ) ) )
444 mExcludedSchemas.append( mSchemas.at( index.row() ) );
445 return true;
446 break;
447 default:
448 return false;
449 }
450
451 return false;
452 }
453
flags(const QModelIndex & index) const454 Qt::ItemFlags QgsMssqlNewConnection::SchemaModel::flags( const QModelIndex &index ) const
455 {
456 return QAbstractListModel::flags( index ) | Qt::ItemFlag::ItemIsUserCheckable;
457 }
458
uncheckedSchemas() const459 QStringList QgsMssqlNewConnection::SchemaModel::uncheckedSchemas() const
460 {
461 return mExcludedSchemas;
462 }
463
464
dataBaseName() const465 QString QgsMssqlNewConnection::SchemaModel::dataBaseName() const
466 {
467 return mDataBaseName;
468 }
469
setDataBaseName(const QString & dataBaseName)470 void QgsMssqlNewConnection::SchemaModel::setDataBaseName( const QString &dataBaseName )
471 {
472 mDataBaseName = dataBaseName;
473 }
474
setSettings(const QString & database,const QStringList & schemas,const QStringList & excludedSchemas)475 void QgsMssqlNewConnection::SchemaModel::setSettings( const QString &database, const QStringList &schemas, const QStringList &excludedSchemas )
476 {
477 beginResetModel();
478 mDataBaseName = database;
479 mSchemas = schemas;
480 mExcludedSchemas = excludedSchemas;
481 endResetModel();
482 }
483
checkAll()484 void QgsMssqlNewConnection::SchemaModel::checkAll()
485 {
486 mExcludedSchemas.clear();
487 emit dataChanged( index( 0, 0, QModelIndex() ), index( mSchemas.count() - 1, 0, QModelIndex() ) );
488 }
489
unCheckAll()490 void QgsMssqlNewConnection::SchemaModel::unCheckAll()
491 {
492 mExcludedSchemas = mSchemas;
493 emit dataChanged( index( 0, 0, QModelIndex() ), index( mSchemas.count() - 1, 0, QModelIndex() ) );
494 }
495