1 /***************************************************************************
2 qgsmssqlprovider.cpp - Data provider for mssql server
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 "qgsmssqlprovider.h"
19 #include "qgsmssqlconnection.h"
20 #include "qgsmssqldatabase.h"
21 #include "qgsmssqlproviderconnection.h"
22 #include "qgsfeedback.h"
23
24 #include <QtGlobal>
25 #include <QFileInfo>
26 #include <QDataStream>
27 #include <QStringList>
28 #include <QMessageBox>
29 #include <QSettings>
30 #include <QRegularExpression>
31 #include <QUrl>
32 #include <QtSql/QSqlDatabase>
33 #include <QtSql/QSqlQuery>
34 #include <QtSql/QSqlError>
35 #include <QtSql/QSqlRecord>
36 #include <QtSql/QSqlField>
37 #include <QStringBuilder>
38 #include <QWaitCondition>
39
40
41 #include "qgsapplication.h"
42 #include "qgsdataprovider.h"
43 #include "qgsfeature.h"
44 #include "qgsfields.h"
45 #include "qgsgeometry.h"
46 #include "qgslogger.h"
47 #include "qgsmessageoutput.h"
48 #include "qgsmessagelog.h"
49 #include "qgsrectangle.h"
50 #include "qgis.h"
51
52 #include "qgsmssqldataitems.h"
53 #include "qgsmssqlfeatureiterator.h"
54 #include "qgsmssqltransaction.h"
55
56
57 const QString QgsMssqlProvider::MSSQL_PROVIDER_KEY = QStringLiteral( "mssql" );
58 const QString QgsMssqlProvider::MSSQL_PROVIDER_DESCRIPTION = QStringLiteral( "MSSQL spatial data provider" );
59 int QgsMssqlProvider::sConnectionId = 0;
60
QgsMssqlProvider(const QString & uri,const ProviderOptions & options,QgsDataProvider::ReadFlags flags)61 QgsMssqlProvider::QgsMssqlProvider( const QString &uri, const ProviderOptions &options,
62 QgsDataProvider::ReadFlags flags )
63 : QgsVectorDataProvider( uri, options, flags )
64 , mShared( new QgsMssqlSharedData )
65 {
66 const QgsDataSourceUri anUri = QgsDataSourceUri( uri );
67
68 if ( !anUri.srid().isEmpty() )
69 mSRId = anUri.srid().toInt();
70 else
71 mSRId = -1;
72
73 mWkbType = anUri.wkbType();
74
75 mValid = true;
76
77 mUserName = anUri.username();
78 mPassword = anUri.password();
79 mService = anUri.service();
80 mDatabaseName = anUri.database();
81 mHost = anUri.host();
82
83 mUseEstimatedMetadata = anUri.useEstimatedMetadata();
84 if ( mReadFlags & QgsDataProvider::FlagTrustDataSource )
85 {
86 mUseEstimatedMetadata = true;
87 }
88
89 mDisableInvalidGeometryHandling = anUri.hasParam( QStringLiteral( "disableInvalidGeometryHandling" ) )
90 ? anUri.param( QStringLiteral( "disableInvalidGeometryHandling" ) ).toInt()
91 : false;
92
93 mUseGeometryColumnsTableForExtent = anUri.hasParam( QStringLiteral( "extentInGeometryColumns" ) )
94 ? anUri.param( QStringLiteral( "extentInGeometryColumns" ) ).toInt()
95 : false;
96
97 mSqlWhereClause = anUri.sql();
98
99 mConn = QgsMssqlDatabase::connectDb( mService, mHost, mDatabaseName, mUserName, mPassword, false );
100 if ( !mConn )
101 {
102 mValid = false;
103 return;
104 }
105 QSqlDatabase db = mConn->db();
106
107 if ( !db.isOpen() )
108 {
109 setLastError( db.lastError().text() );
110 QgsDebugMsg( mLastError );
111 mValid = false;
112 return;
113 }
114
115 // Database successfully opened; we can now issue SQL commands.
116 if ( !anUri.schema().isEmpty() )
117 mSchemaName = anUri.schema();
118 else
119 mSchemaName = QStringLiteral( "dbo" );
120
121 if ( !anUri.table().isEmpty() )
122 {
123 // the layer name has been specified
124 mTableName = anUri.table();
125 QStringList sl = mTableName.split( '.' );
126 if ( sl.length() == 2 )
127 {
128 mSchemaName = sl[0];
129 mTableName = sl[1];
130 }
131 mTables = QStringList( mTableName );
132 }
133 else
134 {
135 // Get a list of table
136 mTables = db.tables( QSql::Tables );
137 if ( !mTables.isEmpty() )
138 mTableName = mTables[0];
139 else
140 mValid = false;
141 }
142
143 if ( mValid )
144 {
145 if ( !anUri.geometryColumn().isEmpty() )
146 mGeometryColName = anUri.geometryColumn();
147
148 if ( mSRId < 0 || mWkbType == QgsWkbTypes::Unknown || mGeometryColName.isEmpty() )
149 {
150 loadMetadata();
151 }
152 loadFields();
153
154 UpdateStatistics( mUseEstimatedMetadata );
155
156 //only for views, defined in layer data when loading layer for first time
157 bool primaryKeyFromGeometryColumnsTable = anUri.hasParam( QStringLiteral( "primaryKeyInGeometryColumns" ) )
158 ? anUri.param( QStringLiteral( "primaryKeyInGeometryColumns" ) ).toInt()
159 : false;
160
161 QStringList cols;
162 if ( primaryKeyFromGeometryColumnsTable )
163 {
164 mPrimaryKeyType = PktUnknown;
165 mPrimaryKeyAttrs.clear();
166 primaryKeyFromGeometryColumnsTable = getPrimaryKeyFromGeometryColumns( cols );
167 if ( !primaryKeyFromGeometryColumnsTable )
168 QgsMessageLog::logMessage( tr( "Invalid primary key from geometry_columns table for layer '%1', get primary key from the layer." )
169 .arg( anUri.table() ), tr( "MSSQL" ) );
170 }
171
172 if ( !primaryKeyFromGeometryColumnsTable )
173 {
174 const QString primaryKey = anUri.keyColumn();
175 if ( !primaryKey.isEmpty() )
176 {
177
178 mPrimaryKeyAttrs.clear();
179 cols = parseUriKey( primaryKey );
180 }
181 }
182
183 if ( mValid )
184 {
185 for ( const QString &col : cols )
186 {
187 const int idx = mAttributeFields.indexFromName( col );
188 if ( idx < 0 )
189 {
190 QgsMessageLog::logMessage( tr( "Key field '%1' for view/query not found." ).arg( col ), tr( "MSSQL" ) );
191 mPrimaryKeyType = PktUnknown;
192 mPrimaryKeyAttrs.clear();
193 break;
194 }
195
196 const QgsField &fld = mAttributeFields.at( idx );
197
198 if ( mPrimaryKeyAttrs.size() == 0 &&
199 ( fld.type() == QVariant::Int ||
200 fld.type() == QVariant::LongLong ||
201 ( fld.type() == QVariant::Double && fld.precision() == 0 ) ) )
202 {
203 mPrimaryKeyType = PktInt;
204 }
205 else
206 {
207 mPrimaryKeyType = PktFidMap;
208 }
209
210 mPrimaryKeyAttrs << idx;
211 }
212
213 if ( mGeometryColName.isEmpty() )
214 {
215 // table contains no geometries
216 mWkbType = QgsWkbTypes::NoGeometry;
217 mSRId = 0;
218 }
219 }
220 }
221
222 //fill type names into sets
223 setNativeTypes( QgsMssqlConnection::nativeTypes() );
224 }
225
~QgsMssqlProvider()226 QgsMssqlProvider::~QgsMssqlProvider()
227 {
228 }
229
featureSource() const230 QgsAbstractFeatureSource *QgsMssqlProvider::featureSource() const
231 {
232 return new QgsMssqlFeatureSource( this );
233 }
234
getFeatures(const QgsFeatureRequest & request) const235 QgsFeatureIterator QgsMssqlProvider::getFeatures( const QgsFeatureRequest &request ) const
236 {
237 if ( !mValid )
238 {
239 QgsDebugMsg( QStringLiteral( "Read attempt on an invalid mssql data source" ) );
240 return QgsFeatureIterator();
241 }
242
243 return QgsFeatureIterator( new QgsMssqlFeatureIterator( new QgsMssqlFeatureSource( this ), true, request ) );
244 }
245
DecodeSqlType(const QString & sqlTypeName)246 QVariant::Type QgsMssqlProvider::DecodeSqlType( const QString &sqlTypeName )
247 {
248 QVariant::Type type = QVariant::Invalid;
249 if ( sqlTypeName.startsWith( QLatin1String( "decimal" ), Qt::CaseInsensitive ) ||
250 sqlTypeName.startsWith( QLatin1String( "numeric" ), Qt::CaseInsensitive ) ||
251 sqlTypeName.startsWith( QLatin1String( "real" ), Qt::CaseInsensitive ) ||
252 sqlTypeName.startsWith( QLatin1String( "float" ), Qt::CaseInsensitive ) )
253 {
254 type = QVariant::Double;
255 }
256 else if ( sqlTypeName.startsWith( QLatin1String( "char" ), Qt::CaseInsensitive ) ||
257 sqlTypeName.startsWith( QLatin1String( "nchar" ), Qt::CaseInsensitive ) ||
258 sqlTypeName.startsWith( QLatin1String( "varchar" ), Qt::CaseInsensitive ) ||
259 sqlTypeName.startsWith( QLatin1String( "nvarchar" ), Qt::CaseInsensitive ) ||
260 sqlTypeName.startsWith( QLatin1String( "text" ), Qt::CaseInsensitive ) ||
261 sqlTypeName.startsWith( QLatin1String( "ntext" ), Qt::CaseInsensitive ) ||
262 sqlTypeName.startsWith( QLatin1String( "uniqueidentifier" ), Qt::CaseInsensitive ) )
263 {
264 type = QVariant::String;
265 }
266 else if ( sqlTypeName.startsWith( QLatin1String( "smallint" ), Qt::CaseInsensitive ) ||
267 sqlTypeName.startsWith( QLatin1String( "int" ), Qt::CaseInsensitive ) ||
268 sqlTypeName.startsWith( QLatin1String( "bit" ), Qt::CaseInsensitive ) ||
269 sqlTypeName.startsWith( QLatin1String( "tinyint" ), Qt::CaseInsensitive ) )
270 {
271 type = QVariant::Int;
272 }
273 else if ( sqlTypeName.startsWith( QLatin1String( "bigint" ), Qt::CaseInsensitive ) )
274 {
275 type = QVariant::LongLong;
276 }
277 else if ( sqlTypeName.startsWith( QLatin1String( "binary" ), Qt::CaseInsensitive ) ||
278 sqlTypeName.startsWith( QLatin1String( "varbinary" ), Qt::CaseInsensitive ) ||
279 sqlTypeName.startsWith( QLatin1String( "image" ), Qt::CaseInsensitive ) )
280 {
281 type = QVariant::ByteArray;
282 }
283 else if ( sqlTypeName.startsWith( QLatin1String( "datetime" ), Qt::CaseInsensitive ) ||
284 sqlTypeName.startsWith( QLatin1String( "smalldatetime" ), Qt::CaseInsensitive ) ||
285 sqlTypeName.startsWith( QLatin1String( "datetime2" ), Qt::CaseInsensitive ) )
286 {
287 type = QVariant::DateTime;
288 }
289 else if ( sqlTypeName.startsWith( QLatin1String( "date" ), Qt::CaseInsensitive ) )
290 {
291 type = QVariant::Date;
292 }
293 else if ( sqlTypeName.startsWith( QLatin1String( "timestamp" ), Qt::CaseInsensitive ) )
294 {
295 type = QVariant::String;
296 }
297 else if ( sqlTypeName.startsWith( QLatin1String( "time" ), Qt::CaseInsensitive ) )
298 {
299 type = QVariant::Time;
300 }
301 else
302 {
303 QgsDebugMsg( QStringLiteral( "Unknown field type: %1" ).arg( sqlTypeName ) );
304 // Everything else just dumped as a string.
305 type = QVariant::String;
306 }
307
308 return type;
309 }
310
loadMetadata()311 void QgsMssqlProvider::loadMetadata()
312 {
313 mSRId = 0;
314 mWkbType = QgsWkbTypes::Unknown;
315
316 QSqlQuery query = createQuery();
317 query.setForwardOnly( true );
318 if ( !query.exec( QStringLiteral( "SELECT f_geometry_column, srid, geometry_type, coord_dimension FROM geometry_columns WHERE f_table_schema=%1 AND f_table_name=%2" ).arg( quotedValue( mSchemaName ), quotedValue( mTableName ) ) ) )
319 {
320 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
321 }
322
323 if ( query.isActive() && query.next() )
324 {
325 mGeometryColName = query.value( 0 ).toString();
326 mSRId = query.value( 1 ).toInt();
327 QString detectedType = query.value( 2 ).toString();
328 const QString dim = query.value( 3 ).toString();
329 if ( dim == QLatin1String( "3" ) && !detectedType.endsWith( 'M' ) )
330 detectedType += QLatin1Char( 'Z' );
331 else if ( dim == QLatin1String( "4" ) )
332 detectedType += QLatin1String( "ZM" );
333 mWkbType = getWkbType( detectedType );
334 }
335 }
336
setLastError(const QString & error)337 void QgsMssqlProvider::setLastError( const QString &error )
338 {
339 appendError( error );
340 mLastError = error;
341 }
342
createQuery() const343 QSqlQuery QgsMssqlProvider::createQuery() const
344 {
345 std::shared_ptr<QgsMssqlDatabase> conn = connection();
346 QSqlDatabase d = conn->db();
347 if ( !d.isOpen() )
348 {
349 QgsDebugMsg( "Creating query, but the database is not open!" );
350 }
351 return QSqlQuery( d );
352 }
353
loadFields()354 void QgsMssqlProvider::loadFields()
355 {
356 bool isIdentity = false;
357 mAttributeFields.clear();
358 mDefaultValues.clear();
359 mComputedColumns.clear();
360
361 // get field spec
362 QSqlQuery query = createQuery();
363 query.setForwardOnly( true );
364
365 // Get computed columns which need to be ignored on insert or update.
366 if ( !query.exec( QStringLiteral( "SELECT name FROM sys.columns WHERE is_computed = 1 AND object_id = OBJECT_ID('[%1].[%2]')" ).arg( mSchemaName, mTableName ) ) )
367 {
368 pushError( query.lastError().text() );
369 return;
370 }
371
372 while ( query.next() )
373 {
374 mComputedColumns.append( query.value( 0 ).toString() );
375 }
376
377 // Field has unique constraint
378 QSet<QString> setColumnUnique;
379 {
380 if ( !query.exec( QStringLiteral( "SELECT * FROM information_schema.table_constraints TC"
381 " INNER JOIN information_schema.constraint_column_usage CC ON TC.Constraint_Name = CC.Constraint_Name"
382 " WHERE TC.CONSTRAINT_SCHEMA = '%1' AND TC.TABLE_NAME = '%2' AND TC.constraint_type = 'unique'" )
383 .arg( mSchemaName, mTableName ) ) )
384 {
385 pushError( query.lastError().text() );
386 return;
387 }
388
389 while ( query.next() )
390 setColumnUnique.insert( query.value( QStringLiteral( "COLUMN_NAME" ) ).toString() );
391 }
392
393 if ( !query.exec( QStringLiteral( "exec sp_columns @table_name = N%1, @table_owner = %2" ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
394 {
395 pushError( query.lastError().text() );
396 return;
397 }
398
399 int i = 0;
400 QStringList pkCandidates;
401 while ( query.next() )
402 {
403 const QString colName = query.value( QStringLiteral( "COLUMN_NAME" ) ).toString();
404 const QString sqlTypeName = query.value( QStringLiteral( "TYPE_NAME" ) ).toString();
405
406 // if we don't have an explicitly set geometry column name, and this is a geometry column, then use it
407 // but if we DO have an explicitly set geometry column name, then load the other information if this is that column
408 if ( ( mGeometryColName.isEmpty() && ( sqlTypeName == QLatin1String( "geometry" ) || sqlTypeName == QLatin1String( "geography" ) ) )
409 || colName == mGeometryColName )
410 {
411 mGeometryColName = colName;
412 mGeometryColType = sqlTypeName;
413 mParser.mIsGeography = sqlTypeName == QLatin1String( "geography" );
414 }
415 else
416 {
417 const QVariant::Type sqlType = DecodeSqlType( sqlTypeName );
418 if ( sqlTypeName == QLatin1String( "int identity" ) || sqlTypeName == QLatin1String( "bigint identity" ) )
419 {
420 mPrimaryKeyType = PktInt;
421 mPrimaryKeyAttrs << mAttributeFields.size();
422 isIdentity = true;
423 }
424 else if ( sqlTypeName == QLatin1String( "int" ) || sqlTypeName == QLatin1String( "bigint" ) )
425 {
426 pkCandidates << colName;
427 }
428
429 QgsField field;
430 if ( sqlType == QVariant::String )
431 {
432 // Field length in chars is column 7 ("Length") of the sp_columns output,
433 // except for uniqueidentifiers which must use column 6 ("Precision").
434 int length = query.value( sqlTypeName.startsWith( QStringLiteral( "uniqueidentifier" ), Qt::CaseInsensitive ) ? 6 : 7 ).toInt();
435 if ( sqlTypeName.startsWith( QLatin1Char( 'n' ) ) )
436 {
437 length = length / 2;
438 }
439 field = QgsField( colName,
440 sqlType,
441 sqlTypeName,
442 length );
443 }
444 else if ( sqlType == QVariant::Double )
445 {
446 field = QgsField( colName,
447 sqlType,
448 sqlTypeName,
449 query.value( QStringLiteral( "PRECISION" ) ).toInt(),
450 sqlTypeName == QLatin1String( "decimal" ) ? query.value( QStringLiteral( "SCALE" ) ).toInt() : -1 );
451 }
452 else if ( sqlType == QVariant::Date || sqlType == QVariant::DateTime || sqlType == QVariant::Time )
453 {
454 field = QgsField( colName,
455 sqlType,
456 sqlTypeName,
457 -1,
458 -1 );
459 }
460 else
461 {
462 field = QgsField( colName,
463 sqlType,
464 sqlTypeName );
465 }
466
467 // Field nullable
468 const bool nullable = query.value( QStringLiteral( "NULLABLE" ) ).toBool();
469
470 // Set constraints
471 QgsFieldConstraints constraints;
472 if ( !nullable )
473 constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
474 if ( setColumnUnique.contains( colName ) )
475 constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
476 field.setConstraints( constraints );
477
478 mAttributeFields.append( field );
479
480 // Default value
481 if ( !query.value( QStringLiteral( "COLUMN_DEF" ) ).isNull() )
482 {
483 mDefaultValues.insert( i, query.value( QStringLiteral( "COLUMN_DEF" ) ).toString() );
484 }
485
486 ++i;
487 }
488 }
489
490 // get primary key
491 if ( mPrimaryKeyAttrs.isEmpty() )
492 {
493 query.clear();
494 if ( !query.exec( QStringLiteral( "exec sp_pkeys @table_name = N%1, @table_owner = %2 " ).arg( quotedValue( mTableName ), quotedValue( mSchemaName ) ) ) )
495 {
496 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
497 }
498
499 if ( query.isActive() )
500 {
501 mPrimaryKeyType = PktInt;
502
503 while ( query.next() )
504 {
505 const QString fidColName = query.value( 3 ).toString();
506 const int idx = mAttributeFields.indexFromName( fidColName );
507 const QgsField &fld = mAttributeFields.at( idx );
508
509 if ( !mPrimaryKeyAttrs.isEmpty() ||
510 ( fld.type() != QVariant::Int &&
511 fld.type() != QVariant::LongLong &&
512 ( fld.type() != QVariant::Double || fld.precision() != 0 ) ) )
513 mPrimaryKeyType = PktFidMap;
514
515 mPrimaryKeyAttrs << idx;
516 }
517
518 if ( mPrimaryKeyAttrs.isEmpty() )
519 {
520 mPrimaryKeyType = PktUnknown;
521 }
522 }
523 }
524
525 if ( mPrimaryKeyAttrs.isEmpty() )
526 {
527 const auto constPkCandidates = pkCandidates;
528 for ( const QString &pk : constPkCandidates )
529 {
530 query.clear();
531 if ( !query.exec( QStringLiteral( "select count(distinct [%1]), count([%1]) from [%2].[%3]" )
532 .arg( pk, mSchemaName, mTableName ) ) )
533 {
534 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
535 }
536
537 if ( query.isActive() && query.next() && query.value( 0 ).toInt() == query.value( 1 ).toInt() )
538 {
539 mPrimaryKeyType = PktInt;
540 mPrimaryKeyAttrs << mAttributeFields.indexFromName( pk );
541 return;
542 }
543 }
544 }
545
546 if ( mPrimaryKeyAttrs.isEmpty() )
547 {
548 const QString error = QStringLiteral( "No primary key could be found on table %1" ).arg( mTableName );
549 QgsDebugMsg( error );
550 mValid = false;
551 setLastError( error );
552 }
553
554 if ( mPrimaryKeyAttrs.size() == 1 && !isIdentity )
555 {
556 // primary key has unique constraints
557 QgsFieldConstraints constraints = mAttributeFields.at( mPrimaryKeyAttrs[0] ).constraints();
558 constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
559 mAttributeFields[ mPrimaryKeyAttrs[0] ].setConstraints( constraints );
560 }
561 }
562
quotedValue(const QVariant & value)563 QString QgsMssqlProvider::quotedValue( const QVariant &value )
564 {
565 if ( value.isNull() )
566 return QStringLiteral( "NULL" );
567
568 switch ( value.type() )
569 {
570 case QVariant::Int:
571 case QVariant::LongLong:
572 case QVariant::Double:
573 return value.toString();
574
575 case QVariant::Bool:
576 return QString( value.toBool() ? '1' : '0' );
577
578 default:
579 case QVariant::String:
580 QString v = value.toString();
581 v.replace( '\'', QLatin1String( "''" ) );
582 if ( v.contains( '\\' ) )
583 return v.replace( '\\', QLatin1String( "\\\\" ) ).prepend( "N'" ).append( '\'' );
584 else
585 return v.prepend( '\'' ).append( '\'' );
586 }
587 }
588
quotedIdentifier(const QString & value)589 QString QgsMssqlProvider::quotedIdentifier( const QString &value )
590 {
591 return QStringLiteral( "[%1]" ).arg( value );
592 }
593
defaultValueClause(int fieldId) const594 QString QgsMssqlProvider::defaultValueClause( int fieldId ) const
595 {
596 const QString defVal = mDefaultValues.value( fieldId, QString() );
597
598 if ( defVal.isEmpty() )
599 return QString();
600
601 // NOTE: If EvaluateDefaultValues is activated it is impossible to get the defaultValueClause.
602 // This also apply to QgsPostgresProvider::defaultValueClause.
603 if ( !providerProperty( EvaluateDefaultValues, false ).toBool() )
604 return defVal;
605
606 return QString();
607 }
608
defaultValue(int fieldId) const609 QVariant QgsMssqlProvider::defaultValue( int fieldId ) const
610 {
611 const QString defVal = mDefaultValues.value( fieldId, QString() );
612
613 if ( defVal.isEmpty() )
614 return QVariant();
615
616 if ( !providerProperty( EvaluateDefaultValues, false ).toBool() )
617 return QVariant();
618
619 const QString sql = QStringLiteral( "select %1" )
620 .arg( defVal );
621
622 QSqlQuery query = createQuery();
623 query.setForwardOnly( true );
624
625 if ( !query.exec( sql ) )
626 {
627 const QString errorMessage( tr( "Could not execute query: %1" ).arg( query.lastError().text() ) );
628 QgsDebugMsg( errorMessage );
629 pushError( errorMessage );
630 return QVariant();
631 }
632
633 if ( !query.next() )
634 {
635 const QString errorMessage( tr( "Could not fetch next query value: %1" ).arg( query.lastError().text() ) );
636 QgsDebugMsg( errorMessage );
637 pushError( errorMessage );
638 return QVariant();
639 }
640
641 return query.value( 0 );
642 }
643
storageType() const644 QString QgsMssqlProvider::storageType() const
645 {
646 return QStringLiteral( "MSSQL spatial database" );
647 }
648
convertTimeValue(const QVariant & value)649 QVariant QgsMssqlProvider::convertTimeValue( const QVariant &value )
650 {
651 if ( value.isValid() && value.type() == QVariant::ByteArray )
652 {
653 // time fields can be returned as byte arrays... woot
654 const QByteArray ba = value.toByteArray();
655 if ( ba.length() >= 5 )
656 {
657 const int hours = ba.at( 0 );
658 const int mins = ba.at( 2 );
659 const int seconds = ba.at( 4 );
660 QVariant t = QTime( hours, mins, seconds );
661 if ( !t.isValid() ) // can't handle it
662 t = QVariant( QVariant::Time );
663 return t;
664 }
665 return QVariant( QVariant::Time );
666 }
667 return value;
668 }
669
670 // Returns the minimum value of an attribute
minimumValue(int index) const671 QVariant QgsMssqlProvider::minimumValue( int index ) const
672 {
673 if ( index < 0 || index >= mAttributeFields.count() )
674 {
675 return QVariant();
676 }
677
678 // get the field name
679 const QgsField &fld = mAttributeFields.at( index );
680 QString sql = QStringLiteral( "select min([%1]) from " )
681 .arg( fld.name() );
682
683 sql += QStringLiteral( "[%1].[%2]" ).arg( mSchemaName, mTableName );
684
685 if ( !mSqlWhereClause.isEmpty() )
686 {
687 sql += QStringLiteral( " where (%1)" ).arg( mSqlWhereClause );
688 }
689
690 QSqlQuery query = createQuery();
691 query.setForwardOnly( true );
692
693 if ( !query.exec( sql ) )
694 {
695 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
696 }
697
698 if ( query.isActive() && query.next() )
699 {
700 QVariant v = query.value( 0 );
701 if ( fld.type() == QVariant::Time )
702 v = convertTimeValue( v );
703 if ( v.type() != fld.type() )
704 v = convertValue( fld.type(), v.toString() );
705 return v;
706 }
707
708 return QVariant( QString() );
709 }
710
711 // Returns the maximum value of an attribute
maximumValue(int index) const712 QVariant QgsMssqlProvider::maximumValue( int index ) const
713 {
714 if ( index < 0 || index >= mAttributeFields.count() )
715 {
716 return QVariant();
717 }
718
719 // get the field name
720 const QgsField &fld = mAttributeFields.at( index );
721 QString sql = QStringLiteral( "select max([%1]) from " )
722 .arg( fld.name() );
723
724 sql += QStringLiteral( "[%1].[%2]" ).arg( mSchemaName, mTableName );
725
726 if ( !mSqlWhereClause.isEmpty() )
727 {
728 sql += QStringLiteral( " where (%1)" ).arg( mSqlWhereClause );
729 }
730
731 QSqlQuery query = createQuery();
732 query.setForwardOnly( true );
733
734 if ( !query.exec( sql ) )
735 {
736 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
737 }
738
739 if ( query.isActive() && query.next() )
740 {
741 QVariant v = query.value( 0 );
742 if ( fld.type() == QVariant::Time )
743 v = convertTimeValue( v );
744 if ( v.type() != fld.type() )
745 v = convertValue( fld.type(), v.toString() );
746 return v;
747 }
748
749 return QVariant( QString() );
750 }
751
752 // Returns the list of unique values of an attribute
uniqueValues(int index,int limit) const753 QSet<QVariant> QgsMssqlProvider::uniqueValues( int index, int limit ) const
754 {
755 QSet<QVariant> uniqueValues;
756 if ( index < 0 || index >= mAttributeFields.count() )
757 {
758 return uniqueValues;
759 }
760
761 // get the field name
762 const QgsField &fld = mAttributeFields.at( index );
763 QString sql = QStringLiteral( "select distinct " );
764
765 if ( limit > 0 )
766 {
767 sql += QStringLiteral( " top %1 " ).arg( limit );
768 }
769
770 sql += QStringLiteral( "[%1] from " )
771 .arg( fld.name() );
772
773 sql += QStringLiteral( "[%1].[%2]" ).arg( mSchemaName, mTableName );
774
775 if ( !mSqlWhereClause.isEmpty() )
776 {
777 sql += QStringLiteral( " where (%1)" ).arg( mSqlWhereClause );
778 }
779
780 QSqlQuery query = createQuery();
781 query.setForwardOnly( true );
782
783 if ( !query.exec( sql ) )
784 {
785 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
786 }
787
788 if ( query.isActive() )
789 {
790 // read all features
791 while ( query.next() )
792 {
793 QVariant v = query.value( 0 );
794 if ( fld.type() == QVariant::Time )
795 v = convertTimeValue( v );
796 if ( v.type() != fld.type() )
797 v = convertValue( fld.type(), v.toString() );
798 uniqueValues.insert( v );
799 }
800 }
801 return uniqueValues;
802 }
803
uniqueStringsMatching(int index,const QString & substring,int limit,QgsFeedback * feedback) const804 QStringList QgsMssqlProvider::uniqueStringsMatching( int index, const QString &substring, int limit, QgsFeedback *feedback ) const
805 {
806 QStringList results;
807
808 if ( index < 0 || index >= mAttributeFields.count() )
809 {
810 return results;
811 }
812
813 // get the field name
814 const QgsField &fld = mAttributeFields.at( index );
815 QString sql = QStringLiteral( "select distinct " );
816
817 if ( limit > 0 )
818 {
819 sql += QStringLiteral( " top %1 " ).arg( limit );
820 }
821
822 sql += QStringLiteral( "[%1] from " )
823 .arg( fld.name() );
824
825 sql += QStringLiteral( "[%1].[%2] WHERE" ).arg( mSchemaName, mTableName );
826
827 if ( !mSqlWhereClause.isEmpty() )
828 {
829 sql += QStringLiteral( " (%1) AND" ).arg( mSqlWhereClause );
830 }
831
832 sql += QStringLiteral( " [%1] LIKE '%%2%'" ).arg( fld.name(), substring );
833
834 QSqlQuery query = createQuery();
835 query.setForwardOnly( true );
836
837 if ( !query.exec( sql ) )
838 {
839 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
840 }
841
842 if ( query.isActive() )
843 {
844 // read all features
845 while ( query.next() )
846 {
847 results << query.value( 0 ).toString();
848 if ( feedback && feedback->isCanceled() )
849 break;
850 }
851 }
852 return results;
853 }
854
855 // update the extent, wkb type and srid for this layer, returns false if fails
UpdateStatistics(bool estimate) const856 void QgsMssqlProvider::UpdateStatistics( bool estimate ) const
857 {
858 if ( mGeometryColName.isEmpty() )
859 {
860 return;
861 }
862
863 // get features to calculate the statistics
864 QString statement;
865
866 QSqlQuery query = createQuery();
867 query.setForwardOnly( true );
868
869
870 if ( mUseGeometryColumnsTableForExtent )
871 {
872 if ( !getExtentFromGeometryColumns( mExtent ) )
873 QgsMessageLog::logMessage( tr( "Invalid extent from geometry_columns table for layer '%1', get extent from the layer." ).arg( mTableName ), tr( "MSSQL" ) );
874 else
875 return;
876 }
877
878 // Get the extents from the spatial index table to speed up load times.
879 // We have to use max() and min() because you can have more then one index but the biggest area is what we want to use.
880 const QString sql = "SELECT min(bounding_box_xmin), min(bounding_box_ymin), max(bounding_box_xmax), max(bounding_box_ymax)"
881 " FROM sys.spatial_index_tessellations WHERE object_id = OBJECT_ID('[%1].[%2]')";
882
883 statement = QString( sql ).arg( mSchemaName, mTableName );
884
885 if ( query.exec( statement ) )
886 {
887 if ( query.next() && ( !query.value( 0 ).isNull() ||
888 !query.value( 1 ).isNull() ||
889 !query.value( 2 ).isNull() ||
890 !query.value( 3 ).isNull() ) )
891 {
892 QgsDebugMsgLevel( QStringLiteral( "Found extents in spatial index" ), 2 );
893 mExtent.setXMinimum( query.value( 0 ).toDouble() );
894 mExtent.setYMinimum( query.value( 1 ).toDouble() );
895 mExtent.setXMaximum( query.value( 2 ).toDouble() );
896 mExtent.setYMaximum( query.value( 3 ).toDouble() );
897 return;
898 }
899 }
900 else
901 {
902 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
903 }
904
905 // If we can't find the extents in the spatial index table just do what we normally do.
906 bool readAllGeography = false;
907 if ( estimate )
908 {
909 if ( mGeometryColType == QLatin1String( "geometry" ) )
910 {
911 if ( mDisableInvalidGeometryHandling )
912 statement = QStringLiteral( "select min([%1].STPointN(1).STX), min([%1].STPointN(1).STY), max([%1].STPointN(1).STX), max([%1].STPointN(1).STY)" ).arg( mGeometryColName );
913 else
914 statement = QStringLiteral( "select min(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).STX else NULL end), min(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).STY else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).STX else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).STY else NULL end)" ).arg( mGeometryColName );
915 }
916 else
917 {
918 if ( mDisableInvalidGeometryHandling )
919 statement = QStringLiteral( "select min([%1].STPointN(1).Long), min([%1].STPointN(1).Lat), max([%1].STPointN(1).Long), max([%1].STPointN(1).Lat)" ).arg( mGeometryColName );
920 else
921 statement = QStringLiteral( "select min(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Long else NULL end), min(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Lat else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Long else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STPointN(1).Lat else NULL end)" ).arg( mGeometryColName );
922 }
923
924 // we will first try to sample a small portion of the table/view, so the count of rows involved
925 // will be useful to evaluate if we have enough data to use the sample
926 statement += ", count(*)";
927 }
928 else
929 {
930 if ( mGeometryColType == QLatin1String( "geometry" ) )
931 {
932 if ( mDisableInvalidGeometryHandling )
933 statement = QStringLiteral( "select min([%1].STEnvelope().STPointN(1).STX), min([%1].STEnvelope().STPointN(1).STY), max([%1].STEnvelope().STPointN(3).STX), max([%1].STEnvelope().STPointN(3).STY)" ).arg( mGeometryColName );
934 else
935 statement = QStringLiteral( "select min(case when ([%1].STIsValid() = 1) THEN [%1].STEnvelope().STPointN(1).STX else NULL end), min(case when ([%1].STIsValid() = 1) THEN [%1].STEnvelope().STPointN(1).STY else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STEnvelope().STPointN(3).STX else NULL end), max(case when ([%1].STIsValid() = 1) THEN [%1].STEnvelope().STPointN(3).STY else NULL end)" ).arg( mGeometryColName );
936 }
937 else
938 {
939 statement = QStringLiteral( "select [%1]" ).arg( mGeometryColName );
940 readAllGeography = true;
941 }
942 }
943
944 statement += QStringLiteral( " from [%1].[%2]" ).arg( mSchemaName, mTableName );
945
946 if ( !mSqlWhereClause.isEmpty() )
947 {
948 statement += " where (" + mSqlWhereClause + ')';
949 }
950
951 if ( estimate )
952 {
953 // Try to use just 1% sample of the whole table/view to limit the amount of rows accessed.
954 // This heuristic may fail (e.g. when the table is small or when primary key values do not
955 // get sampled enough) so in case we do not have at least 10 features, we fall back to full
956 // traversal of the table/view
957
958 const int minSampleCount = 10;
959
960 QString cols, delim;
961 for ( const auto idx : mPrimaryKeyAttrs )
962 {
963 const QgsField &fld = mAttributeFields.at( idx );
964 cols += delim + QStringLiteral( "[%1]" ).arg( fld.name() );
965 delim = QStringLiteral( "," );
966 }
967
968 // See https://docs.microsoft.com/en-us/previous-versions/software-testing/cc441928(v=msdn.10)
969 const QString sampleFilter = QString( "(ABS(CAST((BINARY_CHECKSUM([%1])) as int)) % 100) = 42" ).arg( cols );
970
971 const QString statementSample = statement + ( mSqlWhereClause.isEmpty() ? " WHERE " : " AND " ) + sampleFilter;
972
973 if ( query.exec( statementSample ) && query.next() &&
974 !query.value( 0 ).isNull() && query.value( 4 ).toInt() >= minSampleCount )
975 {
976 mExtent.setXMinimum( query.value( 0 ).toDouble() );
977 mExtent.setYMinimum( query.value( 1 ).toDouble() );
978 mExtent.setXMaximum( query.value( 2 ).toDouble() );
979 mExtent.setYMaximum( query.value( 3 ).toDouble() );
980 return;
981 }
982 }
983
984 if ( !query.exec( statement ) )
985 {
986 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
987 }
988
989 if ( !query.isActive() )
990 {
991 return;
992 }
993
994 if ( !readAllGeography && query.next() )
995 {
996 mExtent.setXMinimum( query.value( 0 ).toDouble() );
997 mExtent.setYMinimum( query.value( 1 ).toDouble() );
998 mExtent.setXMaximum( query.value( 2 ).toDouble() );
999 mExtent.setYMaximum( query.value( 3 ).toDouble() );
1000 return;
1001 }
1002
1003 // We have to read all the geometry if readAllGeography is true.
1004 while ( query.next() )
1005 {
1006 QByteArray ar = query.value( 0 ).toByteArray();
1007 std::unique_ptr<QgsAbstractGeometry> geom = mParser.parseSqlGeometry( reinterpret_cast< unsigned char * >( ar.data() ), ar.size() );
1008 if ( geom )
1009 {
1010 const QgsRectangle rect = geom->boundingBox();
1011
1012 if ( rect.xMinimum() < mExtent.xMinimum() )
1013 mExtent.setXMinimum( rect.xMinimum() );
1014 if ( rect.yMinimum() < mExtent.yMinimum() )
1015 mExtent.setYMinimum( rect.yMinimum() );
1016 if ( rect.xMaximum() > mExtent.xMaximum() )
1017 mExtent.setXMaximum( rect.xMaximum() );
1018 if ( rect.yMaximum() > mExtent.yMaximum() )
1019 mExtent.setYMaximum( rect.yMaximum() );
1020
1021 mWkbType = geom->wkbType();
1022 mSRId = mParser.GetSRSId();
1023 }
1024 }
1025 }
1026
1027 // Return the extent of the layer
extent() const1028 QgsRectangle QgsMssqlProvider::extent() const
1029 {
1030 if ( mExtent.isEmpty() )
1031 UpdateStatistics( mUseEstimatedMetadata );
1032 return mExtent;
1033 }
1034
1035 /**
1036 * Returns the feature type
1037 */
wkbType() const1038 QgsWkbTypes::Type QgsMssqlProvider::wkbType() const
1039 {
1040 return mWkbType;
1041 }
1042
1043 /**
1044 * Returns the feature type
1045 */
featureCount() const1046 long long QgsMssqlProvider::featureCount() const
1047 {
1048 // Return the count that we get from the subset.
1049 if ( !mSqlWhereClause.isEmpty() )
1050 return mNumberFeatures;
1051
1052 // If there is no subset set we can get the count from the system tables.
1053 // Which is faster then doing select count(*)
1054 QSqlQuery query = createQuery();
1055 query.setForwardOnly( true );
1056
1057 const QString statement = QStringLiteral(
1058 "SELECT rows"
1059 " FROM sys.tables t"
1060 " JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)"
1061 " WHERE SCHEMA_NAME(t.schema_id) = %1 AND OBJECT_NAME(t.OBJECT_ID) = %2" ).arg( quotedValue( mSchemaName ), quotedValue( mTableName ) );
1062
1063 if ( query.exec( statement ) && query.next() )
1064 {
1065 return query.value( 0 ).toLongLong();
1066 }
1067 else
1068 {
1069 // We couldn't get the rows from the sys tables. Can that ever happen?
1070 // Should just do a select count(*) here.
1071 return -1;
1072 }
1073 }
1074
fields() const1075 QgsFields QgsMssqlProvider::fields() const
1076 {
1077 return mAttributeFields;
1078 }
1079
isValid() const1080 bool QgsMssqlProvider::isValid() const
1081 {
1082 return mValid;
1083 }
1084
addFeatures(QgsFeatureList & flist,Flags flags)1085 bool QgsMssqlProvider::addFeatures( QgsFeatureList &flist, Flags flags )
1086 {
1087 for ( QgsFeatureList::iterator it = flist.begin(); it != flist.end(); ++it )
1088 {
1089 if ( it->hasGeometry() && mWkbType == QgsWkbTypes::NoGeometry )
1090 {
1091 it->clearGeometry();
1092 }
1093 else if ( it->hasGeometry() && QgsWkbTypes::geometryType( it->geometry().wkbType() ) !=
1094 QgsWkbTypes::geometryType( mWkbType ) )
1095 {
1096 pushError( tr( "Could not add feature with geometry type %1 to layer of type %2" ).arg( QgsWkbTypes::displayString( it->geometry().wkbType() ),
1097 QgsWkbTypes::displayString( mWkbType ) ) );
1098 if ( !mSkipFailures )
1099 return false;
1100
1101 continue;
1102 }
1103
1104 QString statement;
1105 QString values;
1106 if ( !( flags & QgsFeatureSink::FastInsert ) )
1107 {
1108 statement += QLatin1String( "DECLARE @px TABLE (" );
1109
1110 QString delim;
1111 for ( const auto idx : mPrimaryKeyAttrs )
1112 {
1113 const QgsField &fld = mAttributeFields.at( idx );
1114
1115 QString type = fld.typeName();
1116 if ( type.endsWith( QLatin1String( " identity" ) ) )
1117 type = type.left( type.length() - 9 );
1118 if ( type == QLatin1String( "char" ) || type == QLatin1String( "varchar" ) )
1119 {
1120 if ( fld.length() > 0 )
1121 type = QStringLiteral( "%1(%2)" ).arg( type ).arg( fld.length() );
1122 }
1123 else if ( type == QLatin1String( "numeric" ) || type == QLatin1String( "decimal" ) )
1124 {
1125 if ( fld.length() > 0 && fld.precision() > 0 )
1126 type = QStringLiteral( "%1(%2,%3)" ).arg( type ).arg( fld.length() ).arg( fld.precision() );
1127 }
1128
1129 statement += delim + QStringLiteral( "%1 %2" ).arg( fld.name(), type );
1130 delim = ",";
1131 }
1132
1133 statement += "); ";
1134 }
1135
1136 statement += QStringLiteral( "INSERT INTO [%1].[%2] (" ).arg( mSchemaName, mTableName );
1137
1138 bool first = true;
1139 QSqlQuery query = createQuery();
1140 query.setForwardOnly( true );
1141
1142 const QgsAttributes attrs = it->attributes();
1143
1144 for ( int i = 0; i < attrs.count(); ++i )
1145 {
1146 if ( i >= mAttributeFields.count() )
1147 break;
1148
1149 const QgsField &fld = mAttributeFields.at( i );
1150
1151 if ( fld.typeName().compare( QLatin1String( "timestamp" ), Qt::CaseInsensitive ) == 0 )
1152 continue; // You can't update timestamp columns they are server only.
1153
1154 if ( fld.typeName().endsWith( QLatin1String( " identity" ), Qt::CaseInsensitive ) )
1155 continue; // skip identity field
1156
1157 if ( fld.name().isEmpty() )
1158 continue; // invalid
1159
1160 if ( mDefaultValues.contains( i ) && mDefaultValues.value( i ) == attrs.at( i ).toString() )
1161 continue; // skip fields having default values
1162
1163 if ( mComputedColumns.contains( fld.name() ) )
1164 continue; // skip computed columns because they are done server side.
1165
1166 if ( !first )
1167 {
1168 statement += ',';
1169 values += ',';
1170 }
1171 else
1172 first = false;
1173
1174 statement += QStringLiteral( "[%1]" ).arg( fld.name() );
1175 values += QLatin1Char( '?' );
1176 }
1177
1178 // append geometry column name
1179 if ( !mGeometryColName.isEmpty() )
1180 {
1181 if ( !first )
1182 {
1183 statement += ',';
1184 values += ',';
1185 }
1186
1187 statement += QStringLiteral( "[%1]" ).arg( mGeometryColName );
1188 if ( mGeometryColType == QLatin1String( "geometry" ) )
1189 {
1190 if ( mUseWkb )
1191 values += QStringLiteral( "geometry::STGeomFromWKB(?,%1).MakeValid()" ).arg( mSRId );
1192 else
1193 values += QStringLiteral( "geometry::STGeomFromText(?,%1).MakeValid()" ).arg( mSRId );
1194 }
1195 else
1196 {
1197 if ( mUseWkb )
1198 values += QStringLiteral( "geography::STGeomFromWKB(?,%1)" ).arg( mSRId );
1199 else
1200 values += QStringLiteral( "geography::STGeomFromText(?,%1)" ).arg( mSRId );
1201 }
1202 }
1203
1204 statement += QLatin1String( ") " );
1205 if ( !( flags & QgsFeatureSink::FastInsert ) && !mPrimaryKeyAttrs.isEmpty() )
1206 {
1207 statement += QLatin1String( " OUTPUT " );
1208
1209 QString delim;
1210
1211 for ( const auto idx : mPrimaryKeyAttrs )
1212 {
1213 const QgsField &fld = mAttributeFields.at( idx );
1214 statement += delim + "inserted." + fld.name();
1215 delim = QStringLiteral( "," );
1216 }
1217
1218 statement += QLatin1String( " INTO @px " );
1219 }
1220
1221 statement += QStringLiteral( " VALUES (" ) + values + ')';
1222
1223 if ( !( flags & QgsFeatureSink::FastInsert && !mPrimaryKeyAttrs.isEmpty() ) )
1224 {
1225 statement += QLatin1String( "; SELECT * FROM @px;" );
1226 }
1227
1228 // use prepared statement to prevent from sql injection
1229 if ( !query.prepare( statement ) )
1230 {
1231 const QString msg = query.lastError().text();
1232 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1233 if ( !mSkipFailures )
1234 {
1235 pushError( msg );
1236 return false;
1237 }
1238 else
1239 continue;
1240 }
1241
1242 for ( int i = 0; i < attrs.count(); ++i )
1243 {
1244 if ( i >= mAttributeFields.count() )
1245 break;
1246
1247 const QgsField &fld = mAttributeFields.at( i );
1248
1249 if ( fld.typeName().compare( QLatin1String( "timestamp" ), Qt::CaseInsensitive ) == 0 )
1250 continue; // You can't update timestamp columns they are server only.
1251
1252 if ( fld.typeName().endsWith( QLatin1String( " identity" ), Qt::CaseInsensitive ) )
1253 continue; // skip identity field
1254
1255 if ( fld.name().isEmpty() )
1256 continue; // invalid
1257
1258 if ( mDefaultValues.contains( i ) && mDefaultValues.value( i ) == attrs.at( i ).toString() )
1259 continue; // skip fields having default values
1260
1261 if ( mComputedColumns.contains( fld.name() ) )
1262 continue; // skip computed columns because they are done server side.
1263
1264 const QVariant::Type type = fld.type();
1265 if ( attrs.at( i ).isNull() || !attrs.at( i ).isValid() )
1266 {
1267 // binding null values
1268 if ( type == QVariant::Date || type == QVariant::DateTime )
1269 query.addBindValue( QVariant( QVariant::String ) );
1270 else
1271 query.addBindValue( QVariant( type ) );
1272 }
1273 else if ( type == QVariant::Int )
1274 {
1275 // binding an INTEGER value
1276 query.addBindValue( attrs.at( i ).toInt() );
1277 }
1278 else if ( type == QVariant::Double )
1279 {
1280 // binding a DOUBLE value
1281 query.addBindValue( attrs.at( i ).toDouble() );
1282 }
1283 else if ( type == QVariant::String )
1284 {
1285 // binding a TEXT value
1286 query.addBindValue( attrs.at( i ).toString() );
1287 }
1288 else if ( type == QVariant::Time )
1289 {
1290 // binding a TIME value
1291 query.addBindValue( attrs.at( i ).toTime().toString( Qt::ISODate ) );
1292 }
1293 else if ( type == QVariant::Date )
1294 {
1295 // binding a DATE value
1296 query.addBindValue( attrs.at( i ).toDate().toString( Qt::ISODate ) );
1297 }
1298 else if ( type == QVariant::DateTime )
1299 {
1300 // binding a DATETIME value
1301 query.addBindValue( attrs.at( i ).toDateTime().toString( Qt::ISODate ) );
1302 }
1303 else
1304 {
1305 query.addBindValue( attrs.at( i ) );
1306 }
1307 }
1308
1309 if ( !mGeometryColName.isEmpty() )
1310 {
1311 QgsGeometry geom = it->geometry();
1312 if ( QgsWkbTypes::isMultiType( mWkbType ) && !geom.isMultipart() )
1313 {
1314 geom.convertToMultiType();
1315 }
1316 if ( mUseWkb )
1317 {
1318 const QByteArray bytea = geom.asWkb();
1319 query.addBindValue( bytea, QSql::In | QSql::Binary );
1320 }
1321 else
1322 {
1323 QString wkt;
1324 if ( !geom.isNull() )
1325 {
1326 // Z and M on the end of a WKT string isn't valid for
1327 // SQL Server so we have to remove it first.
1328 wkt = geom.asWkt();
1329 wkt.replace( QRegularExpression( QStringLiteral( "[mzMZ]+\\s*\\(" ) ), QStringLiteral( "(" ) );
1330 // if we have M value only, we need to insert null-s for the Z value
1331 if ( QgsWkbTypes::hasM( geom.wkbType() ) && !QgsWkbTypes::hasZ( geom.wkbType() ) )
1332 {
1333 wkt.replace( QRegularExpression( QStringLiteral( "(?=\\s[0-9+-.]+[,)])" ) ), QStringLiteral( " NULL" ) );
1334 }
1335 }
1336 query.addBindValue( wkt );
1337 }
1338 }
1339
1340 if ( !query.exec() )
1341 {
1342 const QString msg = query.lastError().text();
1343 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1344 if ( !mSkipFailures )
1345 {
1346 pushError( msg );
1347 return false;
1348 }
1349 }
1350
1351 if ( !( flags & QgsFeatureSink::FastInsert ) && !mPrimaryKeyAttrs.isEmpty() )
1352 {
1353 if ( !query.next() )
1354 {
1355 const QString msg = query.lastError().text();
1356 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1357 if ( !mSkipFailures )
1358 {
1359 pushError( msg );
1360 return false;
1361 }
1362 }
1363
1364 if ( mPrimaryKeyType == PktInt )
1365 {
1366 it->setId( query.value( 0 ).toLongLong() );
1367 }
1368 else
1369 {
1370 QVariantList keyvals;
1371 for ( int i = 0; i < mPrimaryKeyAttrs.size(); ++i )
1372 {
1373 keyvals << query.value( i );
1374 }
1375
1376 it->setId( mShared->lookupFid( keyvals ) );
1377 }
1378 }
1379 }
1380
1381 if ( mTransaction )
1382 mTransaction->dirtyLastSavePoint();
1383
1384 return true;
1385 }
1386
addAttributes(const QList<QgsField> & attributes)1387 bool QgsMssqlProvider::addAttributes( const QList<QgsField> &attributes )
1388 {
1389 QString statement;
1390
1391 if ( attributes.isEmpty() )
1392 return true;
1393
1394 for ( QList<QgsField>::const_iterator it = attributes.begin(); it != attributes.end(); ++it )
1395 {
1396 QString type = it->typeName();
1397 if ( type == QLatin1String( "char" ) || type == QLatin1String( "varchar" ) )
1398 {
1399 if ( it->length() > 0 )
1400 type = QStringLiteral( "%1(%2)" ).arg( type ).arg( it->length() );
1401 }
1402 else if ( type == QLatin1String( "numeric" ) || type == QLatin1String( "decimal" ) )
1403 {
1404 if ( it->length() > 0 && it->precision() > 0 )
1405 type = QStringLiteral( "%1(%2,%3)" ).arg( type ).arg( it->length() ).arg( it->precision() );
1406 }
1407
1408 if ( statement.isEmpty() )
1409 {
1410 statement = QStringLiteral( "ALTER TABLE [%1].[%2] ADD " ).arg(
1411 mSchemaName, mTableName );
1412 }
1413 else
1414 statement += ',';
1415
1416 statement += QStringLiteral( "[%1] %2" ).arg( it->name(), type );
1417 }
1418
1419 QSqlQuery query = createQuery();
1420 query.setForwardOnly( true );
1421 if ( !query.exec( statement ) )
1422 {
1423 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1424 return false;
1425 }
1426
1427 loadFields();
1428
1429 if ( mTransaction )
1430 mTransaction->dirtyLastSavePoint();
1431
1432 return true;
1433 }
1434
deleteAttributes(const QgsAttributeIds & attributes)1435 bool QgsMssqlProvider::deleteAttributes( const QgsAttributeIds &attributes )
1436 {
1437 QString statement;
1438
1439 for ( QgsAttributeIds::const_iterator it = attributes.begin(); it != attributes.end(); ++it )
1440 {
1441 if ( statement.isEmpty() )
1442 {
1443 statement = QStringLiteral( "ALTER TABLE [%1].[%2] DROP COLUMN " ).arg( mSchemaName, mTableName );
1444 }
1445 else
1446 statement += ',';
1447
1448 statement += QStringLiteral( "[%1]" ).arg( mAttributeFields.at( *it ).name() );
1449 }
1450
1451 QSqlQuery query = createQuery();
1452 query.setForwardOnly( true );
1453
1454 if ( !query.exec( statement ) )
1455 {
1456 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1457 return false;
1458 }
1459
1460 query.finish();
1461 loadFields();
1462
1463 if ( mTransaction )
1464 mTransaction->dirtyLastSavePoint();
1465
1466 return true;
1467 }
1468
1469
changeAttributeValues(const QgsChangedAttributesMap & attr_map)1470 bool QgsMssqlProvider::changeAttributeValues( const QgsChangedAttributesMap &attr_map )
1471 {
1472 if ( attr_map.isEmpty() )
1473 return true;
1474
1475 if ( mPrimaryKeyAttrs.isEmpty() )
1476 return false;
1477
1478 for ( QgsChangedAttributesMap::const_iterator it = attr_map.begin(); it != attr_map.end(); ++it )
1479 {
1480 const QgsFeatureId fid = it.key();
1481
1482 // skip added features
1483 if ( FID_IS_NEW( fid ) )
1484 continue;
1485
1486 const QgsAttributeMap &attrs = it.value();
1487 if ( attrs.isEmpty() )
1488 continue;
1489
1490 QString statement = QStringLiteral( "UPDATE [%1].[%2] SET " ).arg( mSchemaName, mTableName );
1491
1492 bool first = true;
1493 bool pkChanged = false;
1494 QSqlQuery query = createQuery();
1495 query.setForwardOnly( true );
1496
1497 for ( QgsAttributeMap::const_iterator it2 = attrs.begin(); it2 != attrs.end(); ++it2 )
1498 {
1499 const QgsField fld = mAttributeFields.at( it2.key() );
1500
1501 if ( fld.typeName().compare( QLatin1String( "timestamp" ), Qt::CaseInsensitive ) == 0 )
1502 continue; // You can't update timestamp columns they are server only.
1503
1504 if ( fld.typeName().endsWith( QLatin1String( " identity" ), Qt::CaseInsensitive ) )
1505 continue; // skip identity field
1506
1507 if ( fld.name().isEmpty() )
1508 continue; // invalid
1509
1510 if ( mComputedColumns.contains( fld.name() ) )
1511 continue; // skip computed columns because they are done server side.
1512
1513 if ( !first )
1514 statement += ',';
1515 else
1516 first = false;
1517
1518 pkChanged = pkChanged || mPrimaryKeyAttrs.contains( it2.key() );
1519
1520 statement += QStringLiteral( "[%1]=?" ).arg( fld.name() );
1521 }
1522
1523 if ( first )
1524 return true; // no fields have been changed
1525
1526 // set attribute filter
1527 statement += QStringLiteral( " WHERE " ) + whereClauseFid( fid );
1528
1529 // use prepared statement to prevent from sql injection
1530 if ( !query.prepare( statement ) )
1531 {
1532 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1533 return false;
1534 }
1535
1536 for ( QgsAttributeMap::const_iterator it2 = attrs.begin(); it2 != attrs.end(); ++it2 )
1537 {
1538 const QgsField fld = mAttributeFields.at( it2.key() );
1539
1540 if ( fld.typeName().compare( QLatin1String( "timestamp" ), Qt::CaseInsensitive ) == 0 )
1541 continue; // You can't update timestamp columns they are server only.
1542
1543 if ( fld.typeName().endsWith( QLatin1String( " identity" ), Qt::CaseInsensitive ) )
1544 continue; // skip identity field
1545
1546 if ( fld.name().isEmpty() )
1547 continue; // invalid
1548
1549 if ( mComputedColumns.contains( fld.name() ) )
1550 continue; // skip computed columns because they are done server side.
1551
1552 const QVariant::Type type = fld.type();
1553 if ( it2->isNull() || !it2->isValid() )
1554 {
1555 // binding null values
1556 if ( type == QVariant::Date || type == QVariant::DateTime )
1557 query.addBindValue( QVariant( QVariant::String ) );
1558 else
1559 query.addBindValue( QVariant( type ) );
1560 }
1561 else if ( type == QVariant::Int )
1562 {
1563 // binding an INTEGER value
1564 query.addBindValue( it2->toInt() );
1565 }
1566 else if ( type == QVariant::Double )
1567 {
1568 // binding a DOUBLE value
1569 query.addBindValue( it2->toDouble() );
1570 }
1571 else if ( type == QVariant::String )
1572 {
1573 // binding a TEXT value
1574 query.addBindValue( it2->toString() );
1575 }
1576 else if ( type == QVariant::DateTime )
1577 {
1578 // binding a DATETIME value
1579 query.addBindValue( it2->toDateTime().toString( Qt::ISODate ) );
1580 }
1581 else if ( type == QVariant::Date )
1582 {
1583 // binding a DATE value
1584 query.addBindValue( it2->toDate().toString( Qt::ISODate ) );
1585 }
1586 else if ( type == QVariant::Time )
1587 {
1588 // binding a TIME value
1589 query.addBindValue( it2->toTime().toString( Qt::ISODate ) );
1590 }
1591 else
1592 {
1593 query.addBindValue( *it2 );
1594 }
1595 }
1596
1597 if ( !query.exec() )
1598 {
1599 QgsDebugMsg( QStringLiteral( "SQL:%1\n Error:%2" ).arg( query.lastQuery(), query.lastError().text() ) );
1600 return false;
1601 }
1602
1603 if ( pkChanged && mPrimaryKeyType == PktFidMap )
1604 {
1605 const QVariant v = mShared->removeFid( fid );
1606 QVariantList k = v.toList();
1607
1608 for ( int i = 0; i < mPrimaryKeyAttrs.size(); ++i )
1609 {
1610 const int idx = mPrimaryKeyAttrs[i];
1611 if ( !attrs.contains( idx ) )
1612 continue;
1613
1614 k[i] = attrs[ idx ];
1615 }
1616
1617 mShared->insertFid( fid, k );
1618 }
1619 }
1620
1621 if ( mTransaction )
1622 mTransaction->dirtyLastSavePoint();
1623
1624 return true;
1625 }
1626
changeGeometryValues(const QgsGeometryMap & geometry_map)1627 bool QgsMssqlProvider::changeGeometryValues( const QgsGeometryMap &geometry_map )
1628 {
1629 if ( geometry_map.isEmpty() )
1630 return true;
1631
1632 if ( mPrimaryKeyAttrs.isEmpty() )
1633 return false;
1634
1635 for ( QgsGeometryMap::const_iterator it = geometry_map.constBegin(); it != geometry_map.constEnd(); ++it )
1636 {
1637 const QgsFeatureId fid = it.key();
1638 // skip added features
1639 if ( FID_IS_NEW( fid ) )
1640 continue;
1641
1642 QString statement;
1643 statement = QStringLiteral( "UPDATE [%1].[%2] SET " ).arg( mSchemaName, mTableName );
1644
1645 QSqlQuery query = createQuery();
1646 query.setForwardOnly( true );
1647
1648 if ( mGeometryColType == QLatin1String( "geometry" ) )
1649 {
1650 if ( mUseWkb )
1651 statement += QStringLiteral( "[%1]=geometry::STGeomFromWKB(?,%2).MakeValid()" ).arg( mGeometryColName ).arg( mSRId );
1652 else
1653 statement += QStringLiteral( "[%1]=geometry::STGeomFromText(?,%2).MakeValid()" ).arg( mGeometryColName ).arg( mSRId );
1654 }
1655 else
1656 {
1657 if ( mUseWkb )
1658 statement += QStringLiteral( "[%1]=geography::STGeomFromWKB(?,%2)" ).arg( mGeometryColName ).arg( mSRId );
1659 else
1660 statement += QStringLiteral( "[%1]=geography::STGeomFromText(?,%2)" ).arg( mGeometryColName ).arg( mSRId );
1661 }
1662
1663 // set attribute filter
1664 statement += QStringLiteral( " WHERE " ) + whereClauseFid( fid );
1665
1666 if ( !query.prepare( statement ) )
1667 {
1668 pushError( query.lastError().text() );
1669 return false;
1670 }
1671
1672 // add geometry param
1673 if ( mUseWkb )
1674 {
1675 const QByteArray bytea = it->asWkb();
1676 query.addBindValue( bytea, QSql::In | QSql::Binary );
1677 }
1678 else
1679 {
1680 QString wkt = it->asWkt();
1681 // Z and M on the end of a WKT string isn't valid for
1682 // SQL Server so we have to remove it first.
1683 wkt.replace( QRegularExpression( QStringLiteral( "[mzMZ]+\\s*\\(" ) ), QStringLiteral( "(" ) );
1684 query.addBindValue( wkt );
1685 }
1686
1687 if ( !query.exec() )
1688 {
1689 pushError( query.lastError().text() );
1690 return false;
1691 }
1692 }
1693
1694 if ( mTransaction )
1695 mTransaction->dirtyLastSavePoint();
1696
1697 return true;
1698 }
1699
deleteFeatures(const QgsFeatureIds & ids)1700 bool QgsMssqlProvider::deleteFeatures( const QgsFeatureIds &ids )
1701 {
1702 if ( mPrimaryKeyAttrs.isEmpty() )
1703 return false;
1704
1705 if ( ids.empty() )
1706 return true; // for consistency providers return true to an empty list
1707
1708 if ( mPrimaryKeyType == PktInt )
1709 {
1710 QString featureIds, delim;
1711 for ( QgsFeatureIds::const_iterator it = ids.begin(); it != ids.end(); ++it )
1712 {
1713 featureIds += delim + FID_TO_STRING( *it );
1714 delim = QStringLiteral( "," );
1715 }
1716
1717 QSqlQuery query = createQuery();
1718 query.setForwardOnly( true );
1719
1720 const QString statement = QStringLiteral( "DELETE FROM [%1].[%2] WHERE [%3] IN (%4)" ).arg( mSchemaName, mTableName, mAttributeFields.at( mPrimaryKeyAttrs[0] ).name(), featureIds );
1721 if ( query.exec( statement ) )
1722 {
1723 if ( query.numRowsAffected() == ids.size() )
1724 {
1725 if ( mTransaction )
1726 mTransaction->dirtyLastSavePoint();
1727 return true;
1728 }
1729
1730 pushError( tr( "Only %1 of %2 features deleted" ).arg( query.numRowsAffected() ).arg( ids.size() ) );
1731 }
1732 else
1733 pushError( query.lastError().text() );
1734 }
1735 else if ( mPrimaryKeyType == PktFidMap )
1736 {
1737 int i = 0;
1738
1739 QSqlQuery query = createQuery();
1740 for ( QgsFeatureIds::const_iterator it = ids.begin(); it != ids.end(); ++it )
1741 {
1742 const QString statement = QStringLiteral( "DELETE FROM [%1].[%2] WHERE %3" ).arg( mSchemaName, mTableName, whereClauseFid( *it ) );
1743 if ( query.exec( statement ) )
1744 {
1745 if ( query.numRowsAffected() == 1 )
1746 {
1747 mShared->removeFid( *it );
1748 i++;
1749 }
1750 }
1751 else
1752 {
1753 pushError( query.lastError().text() );
1754 break;
1755 }
1756 }
1757
1758 if ( i == ids.size() )
1759 {
1760 if ( mTransaction )
1761 mTransaction->dirtyLastSavePoint();
1762 return true;
1763 }
1764
1765 if ( i > 0 )
1766 pushError( tr( "Only %1 of %2 features deleted" ).arg( i ).arg( ids.size() ) );
1767 }
1768
1769 return false;
1770 }
1771
updateExtents()1772 void QgsMssqlProvider::updateExtents()
1773 {
1774 mExtent.setMinimal();
1775 }
1776
capabilities() const1777 QgsVectorDataProvider::Capabilities QgsMssqlProvider::capabilities() const
1778 {
1779 QgsVectorDataProvider::Capabilities cap = CreateAttributeIndex | AddFeatures | AddAttributes | TransactionSupport;
1780 bool hasGeom = false;
1781 if ( !mGeometryColName.isEmpty() )
1782 {
1783 hasGeom = true;
1784 cap |= CreateSpatialIndex;
1785 }
1786
1787 if ( mPrimaryKeyAttrs.isEmpty() )
1788 return cap;
1789
1790 if ( hasGeom )
1791 cap |= ChangeGeometries;
1792
1793 return cap | DeleteFeatures | ChangeAttributeValues | DeleteAttributes |
1794 QgsVectorDataProvider::SelectAtId;
1795 }
1796
createSpatialIndex()1797 bool QgsMssqlProvider::createSpatialIndex()
1798 {
1799 if ( mUseEstimatedMetadata )
1800 UpdateStatistics( false );
1801
1802 QSqlQuery query = createQuery();
1803 query.setForwardOnly( true );
1804 QString statement;
1805 statement = QStringLiteral( "CREATE SPATIAL INDEX [qgs_%1_sidx] ON [%2].[%3] ( [%4] )" ).arg(
1806 mGeometryColName, mSchemaName, mTableName, mGeometryColName );
1807
1808 if ( mGeometryColType == QLatin1String( "geometry" ) )
1809 {
1810 statement += QStringLiteral( " USING GEOMETRY_GRID WITH (BOUNDING_BOX =(%1, %2, %3, %4))" ).arg(
1811 QString::number( mExtent.xMinimum() ), QString::number( mExtent.yMinimum() ),
1812 QString::number( mExtent.xMaximum() ), QString::number( mExtent.yMaximum() ) );
1813 }
1814 else
1815 {
1816 statement += QLatin1String( " USING GEOGRAPHY_GRID" );
1817 }
1818
1819 if ( !query.exec( statement ) )
1820 {
1821 pushError( query.lastError().text() );
1822 return false;
1823 }
1824
1825 return true;
1826 }
1827
createAttributeIndex(int field)1828 bool QgsMssqlProvider::createAttributeIndex( int field )
1829 {
1830 QSqlQuery query = createQuery();
1831 query.setForwardOnly( true );
1832 QString statement;
1833
1834 if ( field < 0 || field >= mAttributeFields.size() )
1835 {
1836 pushError( QStringLiteral( "createAttributeIndex invalid index" ) );
1837 return false;
1838 }
1839
1840 statement = QStringLiteral( "CREATE NONCLUSTERED INDEX [qgs_%1_idx] ON [%2].[%3] ( [%4] )" ).arg(
1841 mGeometryColName, mSchemaName, mTableName, mAttributeFields.at( field ).name() );
1842
1843 if ( !query.exec( statement ) )
1844 {
1845 pushError( query.lastError().text() );
1846 return false;
1847 }
1848
1849 return true;
1850 }
1851
crs() const1852 QgsCoordinateReferenceSystem QgsMssqlProvider::crs() const
1853 {
1854 if ( !mCrs.isValid() && mSRId > 0 )
1855 {
1856 // try to load crs from the database tables as a fallback
1857 QSqlQuery query = createQuery();
1858 query.setForwardOnly( true );
1859 bool execOk = query.exec( QStringLiteral( "SELECT srtext FROM spatial_ref_sys WHERE srid=%1" ).arg( mSRId ) );
1860 if ( execOk && query.isActive() )
1861 {
1862 if ( query.next() )
1863 {
1864 mCrs = QgsCoordinateReferenceSystem::fromWkt( query.value( 0 ).toString() );
1865 if ( mCrs.isValid() )
1866 return mCrs;
1867 }
1868
1869 query.finish();
1870 }
1871 query.clear();
1872
1873 // Look in the system reference table for the data if we can't find it yet
1874 execOk = query.exec( QStringLiteral( "SELECT well_known_text FROM sys.spatial_reference_systems WHERE spatial_reference_id=%1" ).arg( mSRId ) );
1875 if ( execOk && query.isActive() && query.next() )
1876 {
1877 mCrs = QgsCoordinateReferenceSystem::fromWkt( query.value( 0 ).toString() );
1878 if ( mCrs.isValid() )
1879 return mCrs;
1880 }
1881 }
1882 return mCrs;
1883 }
1884
1885
setTransaction(QgsTransaction * transaction)1886 void QgsMssqlProvider::setTransaction( QgsTransaction *transaction )
1887 {
1888 // static_cast since layers cannot be added to a transaction of a non-matching provider
1889 mTransaction = static_cast<QgsMssqlTransaction *>( transaction );
1890 }
1891
transaction() const1892 QgsTransaction *QgsMssqlProvider::transaction() const
1893 {
1894 return mTransaction;
1895 }
1896
connection() const1897 std::shared_ptr<QgsMssqlDatabase> QgsMssqlProvider::connection() const
1898 {
1899 return mTransaction ? mTransaction->conn() : QgsMssqlDatabase::connectDb( uri().connectionInfo(), false );
1900 }
1901
subsetString() const1902 QString QgsMssqlProvider::subsetString() const
1903 {
1904 return mSqlWhereClause;
1905 }
1906
name() const1907 QString QgsMssqlProvider::name() const
1908 {
1909 return MSSQL_PROVIDER_KEY;
1910 }
1911
setSubsetString(const QString & theSQL,bool)1912 bool QgsMssqlProvider::setSubsetString( const QString &theSQL, bool )
1913 {
1914 if ( theSQL.trimmed() == mSqlWhereClause )
1915 return true;
1916
1917 const QString prevWhere = mSqlWhereClause;
1918
1919 mSqlWhereClause = theSQL.trimmed();
1920
1921 QString sql = QStringLiteral( "SELECT count(*) FROM " );
1922
1923 sql += QStringLiteral( "[%1].[%2]" ).arg( mSchemaName, mTableName );
1924
1925 if ( !mSqlWhereClause.isEmpty() )
1926 {
1927 sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
1928 }
1929
1930 QSqlQuery query = createQuery();
1931 query.setForwardOnly( true );
1932 if ( !query.exec( sql ) )
1933 {
1934 pushError( query.lastError().text() );
1935 mSqlWhereClause = prevWhere;
1936 return false;
1937 }
1938
1939 if ( query.isActive() && query.next() )
1940 mNumberFeatures = query.value( 0 ).toLongLong();
1941
1942 QgsDataSourceUri anUri = QgsDataSourceUri( dataSourceUri() );
1943 anUri.setSql( mSqlWhereClause );
1944
1945 setDataSourceUri( anUri.uri() );
1946
1947 mExtent.setMinimal();
1948
1949 emit dataChanged();
1950
1951 return true;
1952 }
1953
description() const1954 QString QgsMssqlProvider::description() const
1955 {
1956 return MSSQL_PROVIDER_DESCRIPTION;
1957 }
1958
pkAttributeIndexes() const1959 QgsAttributeList QgsMssqlProvider::pkAttributeIndexes() const
1960 {
1961 return mPrimaryKeyAttrs;
1962 }
1963
subLayers() const1964 QStringList QgsMssqlProvider::subLayers() const
1965 {
1966 return mTables;
1967 }
1968
convertField(QgsField & field)1969 bool QgsMssqlProvider::convertField( QgsField &field )
1970 {
1971 QString fieldType = QStringLiteral( "nvarchar(max)" ); //default to string
1972 int fieldSize = field.length();
1973 int fieldPrec = field.precision();
1974 switch ( field.type() )
1975 {
1976 case QVariant::LongLong:
1977 fieldType = QStringLiteral( "bigint" );
1978 fieldSize = -1;
1979 fieldPrec = 0;
1980 break;
1981
1982 case QVariant::DateTime:
1983 fieldType = QStringLiteral( "datetime" );
1984 fieldPrec = 0;
1985 break;
1986
1987 case QVariant::Date:
1988 fieldType = QStringLiteral( "date" );
1989 fieldPrec = 0;
1990 break;
1991
1992 case QVariant::Time:
1993 fieldType = QStringLiteral( "time" );
1994 fieldPrec = 0;
1995 break;
1996
1997 case QVariant::String:
1998 fieldType = QStringLiteral( "nvarchar(max)" );
1999 fieldPrec = 0;
2000 break;
2001
2002 case QVariant::Int:
2003 fieldType = QStringLiteral( "int" );
2004 fieldSize = -1;
2005 fieldPrec = 0;
2006 break;
2007
2008 case QVariant::Double:
2009 if ( fieldSize <= 0 || fieldPrec <= 0 )
2010 {
2011 fieldType = QStringLiteral( "float" );
2012 fieldSize = -1;
2013 fieldPrec = 0;
2014 }
2015 else
2016 {
2017 fieldType = QStringLiteral( "decimal" );
2018 }
2019 break;
2020
2021 default:
2022 return false;
2023 }
2024
2025 field.setTypeName( fieldType );
2026 field.setLength( fieldSize );
2027 field.setPrecision( fieldPrec );
2028 return true;
2029 }
2030
mssqlWkbTypeAndDimension(QgsWkbTypes::Type wkbType,QString & geometryType,int & dim)2031 void QgsMssqlProvider::mssqlWkbTypeAndDimension( QgsWkbTypes::Type wkbType, QString &geometryType, int &dim )
2032 {
2033 const QgsWkbTypes::Type flatType = QgsWkbTypes::flatType( wkbType );
2034
2035 if ( flatType == QgsWkbTypes::Point )
2036 geometryType = QStringLiteral( "POINT" );
2037 else if ( flatType == QgsWkbTypes::LineString )
2038 geometryType = QStringLiteral( "LINESTRING" );
2039 else if ( flatType == QgsWkbTypes::Polygon )
2040 geometryType = QStringLiteral( "POLYGON" );
2041 else if ( flatType == QgsWkbTypes::MultiPoint )
2042 geometryType = QStringLiteral( "MULTIPOINT" );
2043 else if ( flatType == QgsWkbTypes::MultiLineString )
2044 geometryType = QStringLiteral( "MULTILINESTRING" );
2045 else if ( flatType == QgsWkbTypes::MultiPolygon )
2046 geometryType = QStringLiteral( "MULTIPOLYGON" );
2047 else if ( flatType == QgsWkbTypes::GeometryCollection )
2048 geometryType = QStringLiteral( "GEOMETRYCOLLECTION" );
2049 else if ( flatType == QgsWkbTypes::CircularString )
2050 geometryType = QStringLiteral( "CIRCULARSTRING" );
2051 else if ( flatType == QgsWkbTypes::CompoundCurve )
2052 geometryType = QStringLiteral( "COMPOUNDCURVE" );
2053 else if ( flatType == QgsWkbTypes::CurvePolygon )
2054 geometryType = QStringLiteral( "CURVEPOLYGON" );
2055 else if ( flatType == QgsWkbTypes::Unknown )
2056 geometryType = QStringLiteral( "GEOMETRY" );
2057 else
2058 {
2059 dim = 0;
2060 return;
2061 }
2062
2063 if ( QgsWkbTypes::hasZ( wkbType ) && QgsWkbTypes::hasM( wkbType ) )
2064 {
2065 dim = 4;
2066 }
2067 else if ( QgsWkbTypes::hasZ( wkbType ) )
2068 {
2069 dim = 3;
2070 }
2071 else if ( QgsWkbTypes::hasM( wkbType ) )
2072 {
2073 geometryType += QLatin1Char( 'M' );
2074 dim = 3;
2075 }
2076 else if ( wkbType >= QgsWkbTypes::Point25D && wkbType <= QgsWkbTypes::MultiPolygon25D )
2077 {
2078 dim = 3;
2079 }
2080 }
2081
getWkbType(const QString & geometryType)2082 QgsWkbTypes::Type QgsMssqlProvider::getWkbType( const QString &geometryType )
2083 {
2084 return QgsWkbTypes::parseType( geometryType );
2085 }
2086
2087
createEmptyLayer(const QString & uri,const QgsFields & fields,QgsWkbTypes::Type wkbType,const QgsCoordinateReferenceSystem & srs,bool overwrite,QMap<int,int> * oldToNewAttrIdxMap,QString * errorMessage,const QMap<QString,QVariant> * options)2088 Qgis::VectorExportResult QgsMssqlProvider::createEmptyLayer( const QString &uri,
2089 const QgsFields &fields,
2090 QgsWkbTypes::Type wkbType,
2091 const QgsCoordinateReferenceSystem &srs,
2092 bool overwrite,
2093 QMap<int, int> *oldToNewAttrIdxMap,
2094 QString *errorMessage,
2095 const QMap<QString, QVariant> *options )
2096 {
2097 Q_UNUSED( options )
2098
2099 // populate members from the uri structure
2100 QgsDataSourceUri dsUri( uri );
2101
2102 // connect to database
2103 std::shared_ptr<QgsMssqlDatabase> db = QgsMssqlDatabase::connectDb( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
2104
2105 if ( !db->isValid() )
2106 {
2107 if ( errorMessage )
2108 *errorMessage = db->errorText();
2109 return Qgis::VectorExportResult::ErrorConnectionFailed;
2110 }
2111
2112 const QString dbName = dsUri.database();
2113
2114 QString schemaName = dsUri.schema();
2115 const QString tableName = dsUri.table();
2116
2117 QString geometryColumn = dsUri.geometryColumn();
2118
2119 QString primaryKey = dsUri.keyColumn();
2120 QString primaryKeyType;
2121
2122 if ( schemaName.isEmpty() )
2123 schemaName = QStringLiteral( "dbo" );
2124
2125 if ( wkbType != QgsWkbTypes::NoGeometry && geometryColumn.isEmpty() )
2126 geometryColumn = QStringLiteral( "geom" );
2127
2128 // get the pk's name and type
2129 bool createdNewPk = false;
2130
2131 // if no pk name was passed, define the new pk field name
2132 if ( primaryKey.isEmpty() )
2133 {
2134 int index = 0;
2135 const QString pk = primaryKey = QStringLiteral( "qgs_fid" );
2136 for ( int i = 0, n = fields.size(); i < n; ++i )
2137 {
2138 if ( fields.at( i ).name() == primaryKey )
2139 {
2140 // it already exists, try again with a new name
2141 primaryKey = QStringLiteral( "%1_%2" ).arg( pk ).arg( index++ );
2142 i = 0;
2143 }
2144 }
2145 createdNewPk = true;
2146 }
2147 else
2148 {
2149 // search for the passed field
2150 for ( int i = 0, n = fields.size(); i < n; ++i )
2151 {
2152 if ( fields.at( i ).name() == primaryKey )
2153 {
2154 // found, get the field type
2155 QgsField fld = fields.at( i );
2156 if ( ( options && options->value( QStringLiteral( "skipConvertFields" ), false ).toBool() ) || convertField( fld ) )
2157 {
2158 primaryKeyType = fld.typeName();
2159 }
2160 }
2161 }
2162 }
2163
2164 // if the field doesn't not exist yet, create it as a serial field
2165 if ( primaryKeyType.isEmpty() )
2166 primaryKeyType = QStringLiteral( "serial" );
2167
2168 QString sql;
2169 QSqlQuery q = QSqlQuery( db->db() );
2170 q.setForwardOnly( true );
2171
2172 // initialize metadata tables (same as OGR SQL)
2173 sql = QString( "IF NOT EXISTS (SELECT * FROM sys.objects WHERE "
2174 "object_id = OBJECT_ID(N'[dbo].[geometry_columns]') AND type in (N'U')) "
2175 "CREATE TABLE geometry_columns (f_table_catalog varchar(128) not null, "
2176 "f_table_schema varchar(128) not null, f_table_name varchar(256) not null, "
2177 "f_geometry_column varchar(256) not null, coord_dimension integer not null, "
2178 "srid integer not null, geometry_type varchar(30) not null, "
2179 "CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, "
2180 "f_table_schema, f_table_name, f_geometry_column));\n"
2181 "IF NOT EXISTS (SELECT * FROM sys.objects "
2182 "WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_sys]') AND type in (N'U')) "
2183 "CREATE TABLE spatial_ref_sys (srid integer not null "
2184 "PRIMARY KEY, auth_name varchar(256), auth_srid integer, srtext varchar(2048), proj4text varchar(2048))" );
2185 if ( !q.exec( sql ) )
2186 {
2187 if ( errorMessage )
2188 *errorMessage = q.lastError().text();
2189 return Qgis::VectorExportResult::ErrorCreatingLayer;
2190 }
2191
2192 // set up spatial reference id
2193 long srid = 0;
2194 if ( srs.isValid() )
2195 {
2196 srid = srs.postgisSrid();
2197 QString auth_srid = QStringLiteral( "null" );
2198 QString auth_name = QStringLiteral( "null" );
2199 QStringList sl = srs.authid().split( ':' );
2200 if ( sl.length() == 2 )
2201 {
2202 auth_name = sl[0];
2203 auth_srid = sl[1];
2204 }
2205 sql = QStringLiteral( "IF NOT EXISTS (SELECT * FROM spatial_ref_sys WHERE srid=%1) INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) VALUES (%1, %2, %3, %4, %5)" )
2206 .arg( srid )
2207 .arg( quotedValue( auth_name ),
2208 auth_srid,
2209 quotedValue( srs.toWkt() ),
2210 quotedValue( srs.toProj() ) );
2211 if ( !q.exec( sql ) )
2212 {
2213 if ( errorMessage )
2214 *errorMessage = q.lastError().text();
2215 return Qgis::VectorExportResult::ErrorCreatingLayer;
2216 }
2217 }
2218
2219 // get wkb type and dimension
2220 QString geometryType;
2221 int dim = 2;
2222 mssqlWkbTypeAndDimension( wkbType, geometryType, dim );
2223
2224 if ( overwrite )
2225 {
2226 // remove the old table with the same name
2227 sql = QStringLiteral( "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[%1].[%2]') AND type in (N'U')) BEGIN DROP TABLE [%1].[%2] DELETE FROM geometry_columns where f_table_schema='%1' and f_table_name='%2' END;" )
2228 .arg( schemaName, tableName );
2229 if ( !q.exec( sql ) )
2230 {
2231 if ( errorMessage )
2232 *errorMessage = q.lastError().text();
2233 return Qgis::VectorExportResult::ErrorCreatingLayer;
2234 }
2235 }
2236 else
2237 {
2238 // test for existing
2239 sql = QStringLiteral( "SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[%1].[%2]') AND type in (N'U')" )
2240 .arg( schemaName, tableName );
2241 if ( !q.exec( sql ) )
2242 {
2243 if ( errorMessage )
2244 *errorMessage = q.lastError().text();
2245 return Qgis::VectorExportResult::ErrorCreatingLayer;
2246 }
2247
2248 // if we got a hit, abort!!
2249 if ( q.next() )
2250 {
2251 if ( errorMessage )
2252 *errorMessage = tr( "Table [%1].[%2] already exists" ).arg( schemaName, tableName );
2253 return Qgis::VectorExportResult::ErrorCreatingLayer;
2254 }
2255 }
2256
2257 if ( !geometryColumn.isEmpty() )
2258 {
2259 sql = QString( "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[%1].[%2]') AND type in (N'U')) DROP TABLE [%1].[%2]\n"
2260 "CREATE TABLE [%1].[%2]([%3] [int] IDENTITY(1,1) NOT NULL, [%4] [geometry] NULL CONSTRAINT [PK_%2] PRIMARY KEY CLUSTERED ( [%3] ASC ))\n"
2261 "DELETE FROM geometry_columns WHERE f_table_schema = '%1' AND f_table_name = '%2'\n"
2262 "INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema],[f_table_name], "
2263 "[f_geometry_column],[coord_dimension],[srid],[geometry_type]) VALUES ('%5', '%1', '%2', '%4', %6, %7, '%8')" )
2264 .arg( schemaName,
2265 tableName,
2266 primaryKey,
2267 geometryColumn,
2268 dbName,
2269 QString::number( dim ),
2270 QString::number( srid ),
2271 geometryType );
2272 }
2273 else
2274 {
2275 //geometryless table
2276 sql = QString( "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[%1].[%2]') AND type in (N'U')) DROP TABLE [%1].[%2]\n"
2277 "CREATE TABLE [%1].[%2]([%3] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_%2] PRIMARY KEY CLUSTERED ( [%3] ASC ))\n"
2278 "DELETE FROM geometry_columns WHERE f_table_schema = '%1' AND f_table_name = '%2'\n"
2279 )
2280 .arg( schemaName,
2281 tableName,
2282 primaryKey );
2283 }
2284
2285 if ( !q.exec( sql ) )
2286 {
2287 if ( errorMessage )
2288 *errorMessage = q.lastError().text();
2289 return Qgis::VectorExportResult::ErrorCreatingLayer;
2290 }
2291
2292 // clear any resources hold by the query
2293 q.clear();
2294 q.setForwardOnly( true );
2295
2296 // use the provider to edit the table
2297 dsUri.setDataSource( schemaName, tableName, geometryColumn, QString(), primaryKey );
2298
2299 const QgsDataProvider::ProviderOptions providerOptions;
2300 const QgsDataProvider::ReadFlags flags = QgsDataProvider::ReadFlags();
2301 QgsMssqlProvider *provider = new QgsMssqlProvider( dsUri.uri(), providerOptions, flags );
2302 if ( !provider->isValid() )
2303 {
2304 if ( errorMessage )
2305 *errorMessage = QObject::tr( "Loading of the MSSQL provider failed" );
2306
2307 delete provider;
2308 return Qgis::VectorExportResult::ErrorInvalidLayer;
2309 }
2310
2311 // add fields to the layer
2312 if ( oldToNewAttrIdxMap )
2313 oldToNewAttrIdxMap->clear();
2314
2315 if ( fields.size() > 0 )
2316 {
2317 // if we had to create a primary key column, we start the old columns from 1
2318 int offset = createdNewPk ? 1 : 0;
2319
2320 // get the list of fields
2321 QList<QgsField> flist;
2322 for ( int i = 0, n = fields.size(); i < n; ++i )
2323 {
2324 QgsField fld = fields.at( i );
2325 if ( oldToNewAttrIdxMap && fld.name() == primaryKey )
2326 {
2327 oldToNewAttrIdxMap->insert( fields.lookupField( fld.name() ), 0 );
2328 continue;
2329 }
2330
2331 if ( fld.name() == geometryColumn )
2332 {
2333 // Found a field with the same name of the geometry column. Skip it!
2334 continue;
2335 }
2336
2337 if ( !( options && options->value( QStringLiteral( "skipConvertFields" ), false ).toBool() ) && !convertField( fld ) )
2338 {
2339 if ( errorMessage )
2340 *errorMessage = QObject::tr( "Unsupported type for field %1" ).arg( fld.name() );
2341
2342 delete provider;
2343 return Qgis::VectorExportResult::ErrorAttributeTypeUnsupported;
2344 }
2345
2346 flist.append( fld );
2347 if ( oldToNewAttrIdxMap )
2348 oldToNewAttrIdxMap->insert( fields.lookupField( fld.name() ), offset++ );
2349 }
2350
2351 if ( !provider->addAttributes( flist ) )
2352 {
2353 if ( errorMessage )
2354 *errorMessage = QObject::tr( "Creation of fields failed" );
2355
2356 delete provider;
2357 return Qgis::VectorExportResult::ErrorAttributeCreationFailed;
2358 }
2359 }
2360 return Qgis::VectorExportResult::Success;
2361 }
2362
2363
2364
2365 /**
2366 * Class factory to return a pointer to a newly created
2367 * QgsMssqlProvider object
2368 */
createProvider(const QString & uri,const QgsDataProvider::ProviderOptions & options,QgsDataProvider::ReadFlags flags)2369 QgsMssqlProvider *QgsMssqlProviderMetadata::createProvider(
2370 const QString &uri,
2371 const QgsDataProvider::ProviderOptions &options,
2372 QgsDataProvider::ReadFlags flags )
2373 {
2374 return new QgsMssqlProvider( uri, options, flags );
2375 }
2376
dataItemProviders() const2377 QList<QgsDataItemProvider *> QgsMssqlProviderMetadata::dataItemProviders() const
2378 {
2379 QList<QgsDataItemProvider *> providers;
2380 providers << new QgsMssqlDataItemProvider;
2381 return providers;
2382 }
2383
createTransaction(const QString & connString)2384 QgsTransaction *QgsMssqlProviderMetadata::createTransaction( const QString &connString )
2385 {
2386 return new QgsMssqlTransaction( connString );
2387 }
2388
connections(bool cached)2389 QMap<QString, QgsAbstractProviderConnection *> QgsMssqlProviderMetadata::connections( bool cached )
2390 {
2391 return connectionsProtected<QgsMssqlProviderConnection, QgsMssqlConnection>( cached );
2392 }
2393
createConnection(const QString & name)2394 QgsAbstractProviderConnection *QgsMssqlProviderMetadata::createConnection( const QString &name )
2395 {
2396 return new QgsMssqlProviderConnection( name );
2397 }
2398
createConnection(const QString & uri,const QVariantMap & configuration)2399 QgsAbstractProviderConnection *QgsMssqlProviderMetadata::createConnection( const QString &uri, const QVariantMap &configuration )
2400 {
2401 return new QgsMssqlProviderConnection( uri, configuration );
2402 }
2403
deleteConnection(const QString & name)2404 void QgsMssqlProviderMetadata::deleteConnection( const QString &name )
2405 {
2406 deleteConnectionProtected<QgsMssqlProviderConnection>( name );
2407 }
2408
saveConnection(const QgsAbstractProviderConnection * conn,const QString & name)2409 void QgsMssqlProviderMetadata::saveConnection( const QgsAbstractProviderConnection *conn, const QString &name )
2410 {
2411 saveConnectionProtected( conn, name );
2412 }
2413
createEmptyLayer(const QString & uri,const QgsFields & fields,QgsWkbTypes::Type wkbType,const QgsCoordinateReferenceSystem & srs,bool overwrite,QMap<int,int> & oldToNewAttrIdxMap,QString & errorMessage,const QMap<QString,QVariant> * options)2414 Qgis::VectorExportResult QgsMssqlProviderMetadata::createEmptyLayer(
2415 const QString &uri,
2416 const QgsFields &fields,
2417 QgsWkbTypes::Type wkbType,
2418 const QgsCoordinateReferenceSystem &srs,
2419 bool overwrite,
2420 QMap<int, int> &oldToNewAttrIdxMap,
2421 QString &errorMessage,
2422 const QMap<QString, QVariant> *options )
2423 {
2424 return QgsMssqlProvider::createEmptyLayer(
2425 uri, fields, wkbType, srs, overwrite,
2426 &oldToNewAttrIdxMap, &errorMessage, options
2427 );
2428 }
2429
saveStyle(const QString & uri,const QString & qmlStyle,const QString & sldStyle,const QString & styleName,const QString & styleDescription,const QString & uiFileContent,bool useAsDefault,QString & errCause)2430 bool QgsMssqlProviderMetadata::saveStyle( const QString &uri,
2431 const QString &qmlStyle,
2432 const QString &sldStyle,
2433 const QString &styleName,
2434 const QString &styleDescription,
2435 const QString &uiFileContent,
2436 bool useAsDefault,
2437 QString &errCause )
2438 {
2439 const QgsDataSourceUri dsUri( uri );
2440 // connect to database
2441 std::shared_ptr<QgsMssqlDatabase> db = QgsMssqlDatabase::connectDb( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
2442
2443 if ( !db->isValid() )
2444 {
2445 QgsDebugMsg( QStringLiteral( "Error connecting to database" ) );
2446 QgsDebugMsg( db->errorText() );
2447 return false;
2448 }
2449
2450 QSqlQuery query = QSqlQuery( db->db() );
2451 query.setForwardOnly( true );
2452 if ( !query.exec( QStringLiteral( "SELECT COUNT(*) FROM information_schema.tables WHERE table_name= N'layer_styles'" ) ) )
2453 {
2454 QgsDebugMsg( query.lastError().text() );
2455 return false;
2456 }
2457 if ( query.isActive() && query.next() && query.value( 0 ).toInt() == 0 )
2458 {
2459 QgsDebugMsgLevel( QStringLiteral( "Need to create styles table" ), 2 );
2460 const bool execOk = query.exec( QString( "CREATE TABLE [dbo].[layer_styles]("
2461 "[id] int IDENTITY(1,1) PRIMARY KEY,"
2462 "[f_table_catalog] [varchar](1024) NULL,"
2463 "[f_table_schema] [varchar](1024) NULL,"
2464 "[f_table_name] [varchar](1024) NULL,"
2465 "[f_geometry_column] [varchar](1024) NULL,"
2466 "[styleName] [varchar](1024) NULL,"
2467 "[styleQML] [text] NULL,"
2468 "[styleSLD] [text] NULL,"
2469 "[useAsDefault] [int] NULL,"
2470 "[description] [text] NULL,"
2471 "[owner] [varchar](1024) NULL,"
2472 "[ui] [text] NULL,"
2473 "[update_time] [datetime] NULL"
2474 ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" ) );
2475 if ( !execOk )
2476 {
2477 errCause = QObject::tr( "Unable to save layer style. It's not possible to create the destination table on the database. Maybe this is due to table permissions. Please contact your database admin" );
2478 return false;
2479 }
2480 query.finish();
2481 query.clear();
2482 }
2483
2484 QString uiFileColumn;
2485 QString uiFileValue;
2486 if ( !uiFileContent.isEmpty() )
2487 {
2488 uiFileColumn = QStringLiteral( ",ui" );
2489 uiFileValue = QStringLiteral( ",XMLPARSE(DOCUMENT %1)" ).arg( uiFileContent );
2490 }
2491 QgsDebugMsgLevel( QStringLiteral( "Ready to insert new style" ), 2 );
2492 // Note: in the construction of the INSERT and UPDATE strings the qmlStyle and sldStyle values
2493 // can contain user entered strings, which may themselves include %## values that would be
2494 // replaced by the QString.arg function. To ensure that the final SQL string is not corrupt these
2495 // two values are both replaced in the final .arg call of the string construction.
2496
2497 QString sql = QString( "INSERT INTO layer_styles"
2498 "(f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner%11"
2499 ") VALUES ("
2500 "%1,%2,%3,%4,%5,%6,%7,%8,%9,%10%12"
2501 ")" )
2502 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2503 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2504 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2505 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) )
2506 .arg( QgsMssqlProvider::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) )
2507 .arg( QgsMssqlProvider::quotedValue( qmlStyle ) )
2508 .arg( QgsMssqlProvider::quotedValue( sldStyle ) )
2509 .arg( useAsDefault ? QStringLiteral( "1" ) : QStringLiteral( "0" ) )
2510 .arg( QgsMssqlProvider::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
2511 .arg( QgsMssqlProvider::quotedValue( dsUri.username() ) )
2512 .arg( uiFileColumn )
2513 .arg( uiFileValue );
2514
2515 const QString checkQuery = QString( "SELECT styleName"
2516 " FROM layer_styles"
2517 " WHERE f_table_catalog=%1"
2518 " AND f_table_schema=%2"
2519 " AND f_table_name=%3"
2520 " AND f_geometry_column=%4"
2521 " AND styleName=%5" )
2522 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2523 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2524 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2525 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) )
2526 .arg( QgsMssqlProvider::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );
2527
2528 if ( !query.exec( checkQuery ) )
2529 {
2530 QgsDebugMsg( query.lastError().text() );
2531 QgsDebugMsg( QStringLiteral( "Check Query failed" ) );
2532 return false;
2533 }
2534 if ( query.isActive() && query.next() && query.value( 0 ).toString() == styleName )
2535 {
2536 if ( QMessageBox::question( nullptr, QObject::tr( "Save style in database" ),
2537 QObject::tr( "A style named \"%1\" already exists in the database for this layer. Do you want to overwrite it?" )
2538 .arg( styleName.isEmpty() ? dsUri.table() : styleName ),
2539 QMessageBox::Yes | QMessageBox::No ) == QMessageBox::No )
2540 {
2541 errCause = QObject::tr( "Operation aborted. No changes were made in the database" );
2542 QgsDebugMsg( QStringLiteral( "User selected not to overwrite styles" ) );
2543 return false;
2544 }
2545
2546 QgsDebugMsgLevel( QStringLiteral( "Updating styles" ), 2 );
2547 sql = QString( "UPDATE layer_styles "
2548 " SET useAsDefault=%1"
2549 ",styleQML=%2"
2550 ",styleSLD=%3"
2551 ",description=%4"
2552 ",owner=%5"
2553 " WHERE f_table_catalog=%6"
2554 " AND f_table_schema=%7"
2555 " AND f_table_name=%8"
2556 " AND f_geometry_column=%9"
2557 " AND styleName=%10" )
2558 .arg( useAsDefault ? QStringLiteral( "1" ) : QStringLiteral( "0" ) )
2559 .arg( QgsMssqlProvider::quotedValue( qmlStyle ) )
2560 .arg( QgsMssqlProvider::quotedValue( sldStyle ) )
2561 .arg( QgsMssqlProvider::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
2562 .arg( QgsMssqlProvider::quotedValue( dsUri.username() ) )
2563 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2564 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2565 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2566 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) )
2567 .arg( QgsMssqlProvider::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );
2568 }
2569 if ( useAsDefault )
2570 {
2571 const QString removeDefaultSql = QString( "UPDATE layer_styles "
2572 " SET useAsDefault=0"
2573 " WHERE f_table_catalog=%1"
2574 " AND f_table_schema=%2"
2575 " AND f_table_name=%3"
2576 " AND f_geometry_column=%4" )
2577 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2578 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2579 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2580 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) );
2581 sql = QStringLiteral( "%1; %2;" ).arg( removeDefaultSql, sql );
2582 }
2583
2584 QgsDebugMsgLevel( QStringLiteral( "Inserting styles" ), 2 );
2585 QgsDebugMsgLevel( sql, 2 );
2586 const bool execOk = query.exec( sql );
2587
2588 if ( !execOk )
2589 {
2590 errCause = QObject::tr( "Unable to save layer style. It's not possible to insert a new record into the style table. Maybe this is due to table permissions. Please contact your database administrator." );
2591 }
2592 return execOk;
2593 }
2594
loadStyle(const QString & uri,QString & errCause)2595 QString QgsMssqlProviderMetadata::loadStyle( const QString &uri, QString &errCause )
2596 {
2597 const QgsDataSourceUri dsUri( uri );
2598 // connect to database
2599 std::shared_ptr<QgsMssqlDatabase> db = QgsMssqlDatabase::connectDb( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
2600
2601 if ( !db->isValid() )
2602 {
2603 QgsDebugMsg( QStringLiteral( "Error connecting to database" ) );
2604 QgsDebugMsg( db->errorText() );
2605 return QString();
2606 }
2607
2608 QSqlQuery query = QSqlQuery( db->db() );
2609 query.setForwardOnly( true );
2610
2611 const QString selectQmlQuery = QString( "SELECT top 1 styleQML"
2612 " FROM layer_styles"
2613 " WHERE f_table_catalog=%1"
2614 " AND f_table_schema=%2"
2615 " AND f_table_name=%3"
2616 " AND f_geometry_column=%4"
2617 " ORDER BY useAsDefault desc" )
2618 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2619 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2620 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2621 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) );
2622
2623 if ( !query.exec( selectQmlQuery ) )
2624 {
2625 QgsDebugMsgLevel( QStringLiteral( "Load of style failed" ), 2 );
2626 const QString msg = query.lastError().text();
2627 errCause = msg;
2628 QgsDebugMsg( msg );
2629 return QString();
2630 }
2631 if ( query.isActive() && query.next() )
2632 {
2633 QString style = query.value( 0 ).toString();
2634 return style;
2635 }
2636 return QString();
2637 }
2638
listStyles(const QString & uri,QStringList & ids,QStringList & names,QStringList & descriptions,QString & errCause)2639 int QgsMssqlProviderMetadata::listStyles( const QString &uri,
2640 QStringList &ids,
2641 QStringList &names,
2642 QStringList &descriptions,
2643 QString &errCause )
2644 {
2645 const QgsDataSourceUri dsUri( uri );
2646 // connect to database
2647 std::shared_ptr<QgsMssqlDatabase> db = QgsMssqlDatabase::connectDb( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
2648
2649 if ( !db->isValid() )
2650 {
2651 QgsDebugMsg( QStringLiteral( "Error connecting to database" ) );
2652 QgsDebugMsg( db->errorText() );
2653 return -1;
2654 }
2655
2656 QSqlQuery query = QSqlQuery( db->db() );
2657 query.setForwardOnly( true );
2658
2659 // check if layer_styles table already exist
2660 if ( !query.exec( QStringLiteral( "SELECT COUNT(*) FROM information_schema.tables WHERE table_name= N'layer_styles'" ) ) )
2661 {
2662 const QString msg = query.lastError().text();
2663 errCause = msg;
2664 QgsDebugMsg( msg );
2665 return -1;
2666 }
2667 if ( query.isActive() && query.next() && query.value( 0 ).toInt() == 0 )
2668 {
2669 QgsDebugMsg( QObject::tr( "No styles available on DB, or there is an error connecting to the database." ) );
2670 return -1;
2671 }
2672
2673 const QString selectRelatedQuery = QString( "SELECT id,styleName,description"
2674 " FROM layer_styles "
2675 " WHERE f_table_catalog=%1"
2676 " AND f_table_schema=%2"
2677 " AND f_table_name=%3"
2678 " AND f_geometry_column=%4"
2679 " ORDER BY useasdefault DESC, update_time DESC" )
2680 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2681 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2682 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2683 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) );
2684 bool queryOk = query.exec( selectRelatedQuery );
2685 if ( !queryOk )
2686 {
2687 QgsDebugMsg( query.lastError().text() );
2688 return -1;
2689 }
2690 int numberOfRelatedStyles = 0;
2691 while ( query.isActive() && query.next() )
2692 {
2693 QgsDebugMsgLevel( query.value( 1 ).toString(), 2 );
2694 ids.append( query.value( 0 ).toString() );
2695 names.append( query.value( 1 ).toString() );
2696 descriptions.append( query.value( 2 ).toString() );
2697 numberOfRelatedStyles = numberOfRelatedStyles + 1;
2698 }
2699 const QString selectOthersQuery = QString( "SELECT id,styleName,description"
2700 " FROM layer_styles "
2701 " WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4)"
2702 " ORDER BY update_time DESC" )
2703 .arg( QgsMssqlProvider::quotedValue( dsUri.database() ) )
2704 .arg( QgsMssqlProvider::quotedValue( dsUri.schema() ) )
2705 .arg( QgsMssqlProvider::quotedValue( dsUri.table() ) )
2706 .arg( QgsMssqlProvider::quotedValue( dsUri.geometryColumn() ) );
2707 QgsDebugMsgLevel( selectOthersQuery, 2 );
2708 queryOk = query.exec( selectOthersQuery );
2709 if ( !queryOk )
2710 {
2711 QgsDebugMsg( query.lastError().text() );
2712 return -1;
2713 }
2714 while ( query.next() )
2715 {
2716 ids.append( query.value( 0 ).toString() );
2717 names.append( query.value( 1 ).toString() );
2718 descriptions.append( query.value( 2 ).toString() );
2719 }
2720 return numberOfRelatedStyles;
2721 }
2722
QgsMssqlProviderMetadata()2723 QgsMssqlProviderMetadata::QgsMssqlProviderMetadata():
2724 QgsProviderMetadata( QgsMssqlProvider::MSSQL_PROVIDER_KEY, QgsMssqlProvider::MSSQL_PROVIDER_DESCRIPTION )
2725 {
2726 }
2727
getStyleById(const QString & uri,QString styleId,QString & errCause)2728 QString QgsMssqlProviderMetadata::getStyleById( const QString &uri, QString styleId, QString &errCause )
2729 {
2730 const QgsDataSourceUri dsUri( uri );
2731 // connect to database
2732 std::shared_ptr<QgsMssqlDatabase> db = QgsMssqlDatabase::connectDb( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
2733
2734 if ( !db->isValid() )
2735 {
2736 QgsDebugMsg( QStringLiteral( "Error connecting to database" ) );
2737 QgsDebugMsg( db->errorText() );
2738 return QString();
2739 }
2740
2741 QSqlQuery query = QSqlQuery( db->db() );
2742 query.setForwardOnly( true );
2743
2744 QString style;
2745 const QString selectQmlQuery = QStringLiteral( "SELECT styleQml FROM layer_styles WHERE id=%1" ).arg( QgsMssqlProvider::quotedValue( styleId ) );
2746 const bool queryOk = query.exec( selectQmlQuery );
2747 if ( !queryOk )
2748 {
2749 QgsDebugMsg( query.lastError().text() );
2750 errCause = query.lastError().text();
2751 return QString();
2752 }
2753 while ( query.next() )
2754 {
2755 style = query.value( 0 ).toString();
2756 }
2757 return style;
2758 }
2759
decodeUri(const QString & uri) const2760 QVariantMap QgsMssqlProviderMetadata::decodeUri( const QString &uri ) const
2761 {
2762 const QgsDataSourceUri dsUri { uri };
2763 QVariantMap uriParts;
2764
2765 if ( ! dsUri.database().isEmpty() )
2766 uriParts[ QStringLiteral( "dbname" ) ] = dsUri.database();
2767 if ( ! dsUri.host().isEmpty() )
2768 uriParts[ QStringLiteral( "host" ) ] = dsUri.host();
2769 if ( ! dsUri.port().isEmpty() )
2770 uriParts[ QStringLiteral( "port" ) ] = dsUri.port();
2771 if ( ! dsUri.service().isEmpty() )
2772 uriParts[ QStringLiteral( "service" ) ] = dsUri.service();
2773 if ( ! dsUri.username().isEmpty() )
2774 uriParts[ QStringLiteral( "username" ) ] = dsUri.username();
2775 if ( ! dsUri.password().isEmpty() )
2776 uriParts[ QStringLiteral( "password" ) ] = dsUri.password();
2777
2778 // Supported?
2779 //if ( ! dsUri.authConfigId().isEmpty() )
2780 // uriParts[ QStringLiteral( "authcfg" ) ] = dsUri.authConfigId();
2781
2782 if ( dsUri.wkbType() != QgsWkbTypes::Type::Unknown )
2783 uriParts[ QStringLiteral( "type" ) ] = dsUri.wkbType();
2784
2785 // Supported?
2786 // uriParts[ QStringLiteral( "selectatid" ) ] = dsUri.selectAtIdDisabled();
2787
2788 if ( ! dsUri.table().isEmpty() )
2789 uriParts[ QStringLiteral( "table" ) ] = dsUri.table();
2790 if ( ! dsUri.schema().isEmpty() )
2791 uriParts[ QStringLiteral( "schema" ) ] = dsUri.schema();
2792 if ( ! dsUri.keyColumn().isEmpty() )
2793 uriParts[ QStringLiteral( "key" ) ] = dsUri.keyColumn();
2794 if ( ! dsUri.srid().isEmpty() )
2795 uriParts[ QStringLiteral( "srid" ) ] = dsUri.srid();
2796
2797 uriParts[ QStringLiteral( "estimatedmetadata" ) ] = dsUri.useEstimatedMetadata();
2798
2799 // is this supported?
2800 // uriParts[ QStringLiteral( "sslmode" ) ] = dsUri.sslMode();
2801
2802 if ( ! dsUri.sql().isEmpty() )
2803 uriParts[ QStringLiteral( "sql" ) ] = dsUri.sql();
2804 if ( ! dsUri.geometryColumn().isEmpty() )
2805 uriParts[ QStringLiteral( "geometrycolumn" ) ] = dsUri.geometryColumn();
2806
2807 // From configuration
2808 static const QStringList configurationParameters
2809 {
2810 QStringLiteral( "geometryColumnsOnly" ),
2811 QStringLiteral( "allowGeometrylessTables" ),
2812 QStringLiteral( "saveUsername" ),
2813 QStringLiteral( "savePassword" ),
2814 QStringLiteral( "estimatedMetadata" ),
2815 QStringLiteral( "disableInvalidGeometryHandling" ),
2816 QStringLiteral( "extentInGeometryColumns" ),
2817 QStringLiteral( "primaryKeyInGeometryColumns" )
2818 };
2819
2820 for ( const auto &configParam : configurationParameters )
2821 {
2822 if ( dsUri.hasParam( configParam ) )
2823 {
2824 uriParts[ configParam ] = dsUri.param( configParam );
2825 }
2826 }
2827
2828 return uriParts;
2829 }
2830
encodeUri(const QVariantMap & parts) const2831 QString QgsMssqlProviderMetadata::encodeUri( const QVariantMap &parts ) const
2832 {
2833 QgsDataSourceUri dsUri;
2834 if ( parts.contains( QStringLiteral( "dbname" ) ) )
2835 dsUri.setDatabase( parts.value( QStringLiteral( "dbname" ) ).toString() );
2836 // Also accepts "database"
2837 if ( parts.contains( QStringLiteral( "database" ) ) )
2838 dsUri.setDatabase( parts.value( QStringLiteral( "database" ) ).toString() );
2839 // Supported?
2840 //if ( parts.contains( QStringLiteral( "port" ) ) )
2841 // dsUri.setParam( QStringLiteral( "port" ), parts.value( QStringLiteral( "port" ) ).toString() );
2842 if ( parts.contains( QStringLiteral( "host" ) ) )
2843 dsUri.setParam( QStringLiteral( "host" ), parts.value( QStringLiteral( "host" ) ).toString() );
2844 if ( parts.contains( QStringLiteral( "service" ) ) )
2845 dsUri.setParam( QStringLiteral( "service" ), parts.value( QStringLiteral( "service" ) ).toString() );
2846 if ( parts.contains( QStringLiteral( "username" ) ) )
2847 dsUri.setUsername( parts.value( QStringLiteral( "username" ) ).toString() );
2848 if ( parts.contains( QStringLiteral( "password" ) ) )
2849 dsUri.setPassword( parts.value( QStringLiteral( "password" ) ).toString() );
2850 // Supported?
2851 //if ( parts.contains( QStringLiteral( "authcfg" ) ) )
2852 // dsUri.setAuthConfigId( parts.value( QStringLiteral( "authcfg" ) ).toString() );
2853 if ( parts.contains( QStringLiteral( "type" ) ) )
2854 dsUri.setParam( QStringLiteral( "type" ), QgsWkbTypes::displayString( static_cast<QgsWkbTypes::Type>( parts.value( QStringLiteral( "type" ) ).toInt() ) ) );
2855 // Supported?
2856 //if ( parts.contains( QStringLiteral( "selectatid" ) ) )
2857 // dsUri.setParam( QStringLiteral( "selectatid" ), parts.value( QStringLiteral( "selectatid" ) ).toString() );
2858 if ( parts.contains( QStringLiteral( "table" ) ) )
2859 dsUri.setTable( parts.value( QStringLiteral( "table" ) ).toString() );
2860 if ( parts.contains( QStringLiteral( "schema" ) ) )
2861 dsUri.setSchema( parts.value( QStringLiteral( "schema" ) ).toString() );
2862 if ( parts.contains( QStringLiteral( "key" ) ) )
2863 dsUri.setParam( QStringLiteral( "key" ), parts.value( QStringLiteral( "key" ) ).toString() );
2864 if ( parts.contains( QStringLiteral( "srid" ) ) )
2865 dsUri.setSrid( parts.value( QStringLiteral( "srid" ) ).toString() );
2866 if ( parts.contains( QStringLiteral( "estimatedmetadata" ) ) )
2867 dsUri.setParam( QStringLiteral( "estimatedmetadata" ), parts.value( QStringLiteral( "estimatedmetadata" ) ).toString() );
2868 // Supported?
2869 //if ( parts.contains( QStringLiteral( "sslmode" ) ) )
2870 // dsUri.setParam( QStringLiteral( "sslmode" ), QgsDataSourceUri::encodeSslMode( static_cast<QgsDataSourceUri::SslMode>( parts.value( QStringLiteral( "sslmode" ) ).toInt( ) ) ) );
2871 if ( parts.contains( QStringLiteral( "sql" ) ) )
2872 dsUri.setSql( parts.value( QStringLiteral( "sql" ) ).toString() );
2873 // Supported?
2874 //if ( parts.contains( QStringLiteral( "checkPrimaryKeyUnicity" ) ) )
2875 // dsUri.setParam( QStringLiteral( "checkPrimaryKeyUnicity" ), parts.value( QStringLiteral( "checkPrimaryKeyUnicity" ) ).toString() );
2876 if ( parts.contains( QStringLiteral( "geometrycolumn" ) ) )
2877 dsUri.setGeometryColumn( parts.value( QStringLiteral( "geometrycolumn" ) ).toString() );
2878 if ( parts.contains( QStringLiteral( "disableInvalidGeometryHandling" ) ) )
2879 dsUri.setParam( QStringLiteral( "disableInvalidGeometryHandling" ), parts.value( QStringLiteral( "disableInvalidGeometryHandling" ) ).toString() );
2880 if ( parts.contains( QStringLiteral( "allowGeometrylessTables" ) ) )
2881 dsUri.setParam( QStringLiteral( "allowGeometrylessTables" ), parts.value( QStringLiteral( "allowGeometrylessTables" ) ).toString() );
2882 if ( parts.contains( QStringLiteral( "geometryColumnsOnly" ) ) )
2883 dsUri.setParam( QStringLiteral( "geometryColumnsOnly" ), parts.value( QStringLiteral( "geometryColumnsOnly" ) ).toString() );
2884 if ( parts.contains( QStringLiteral( "extentInGeometryColumns" ) ) )
2885 dsUri.setParam( QStringLiteral( "extentInGeometryColumns" ), parts.value( QStringLiteral( "extentInGeometryColumns" ) ).toString() );
2886 if ( parts.contains( QStringLiteral( "primaryKeyInGeometryColumns" ) ) )
2887 dsUri.setParam( QStringLiteral( "primaryKeyInGeometryColumns" ), parts.value( QStringLiteral( "primaryKeyInGeometryColumns" ) ).toString() );
2888 return dsUri.uri();
2889 }
2890
providerMetadataFactory()2891 QGISEXTERN QgsProviderMetadata *providerMetadataFactory()
2892 {
2893 return new QgsMssqlProviderMetadata();
2894 }
2895
2896 // ----------
2897
lookupFid(const QVariantList & v)2898 QgsFeatureId QgsMssqlSharedData::lookupFid( const QVariantList &v )
2899 {
2900 const QMutexLocker locker( &mMutex );
2901
2902 const QMap<QVariantList, QgsFeatureId>::const_iterator it = mKeyToFid.constFind( v );
2903
2904 if ( it != mKeyToFid.constEnd() )
2905 {
2906 return it.value();
2907 }
2908
2909 mFidToKey.insert( ++mFidCounter, v );
2910 mKeyToFid.insert( v, mFidCounter );
2911
2912 return mFidCounter;
2913 }
2914
removeFid(QgsFeatureId fid)2915 QVariant QgsMssqlSharedData::removeFid( QgsFeatureId fid )
2916 {
2917 const QMutexLocker locker( &mMutex );
2918
2919 const QVariantList v = mFidToKey[ fid ];
2920 mFidToKey.remove( fid );
2921 mKeyToFid.remove( v );
2922 return v;
2923 }
2924
insertFid(QgsFeatureId fid,const QVariantList & k)2925 void QgsMssqlSharedData::insertFid( QgsFeatureId fid, const QVariantList &k )
2926 {
2927 const QMutexLocker locker( &mMutex );
2928
2929 mFidToKey.insert( fid, k );
2930 mKeyToFid.insert( k, fid );
2931 }
2932
lookupKey(QgsFeatureId featureId)2933 QVariantList QgsMssqlSharedData::lookupKey( QgsFeatureId featureId )
2934 {
2935 const QMutexLocker locker( &mMutex );
2936
2937 const QMap<QgsFeatureId, QVariantList>::const_iterator it = mFidToKey.find( featureId );
2938 if ( it != mFidToKey.constEnd() )
2939 return it.value();
2940 return QVariantList();
2941 }
2942
whereClauseFid(QgsFeatureId featureId)2943 QString QgsMssqlProvider::whereClauseFid( QgsFeatureId featureId )
2944 {
2945 QString whereClause;
2946
2947 switch ( mPrimaryKeyType )
2948 {
2949 case PktInt:
2950 Q_ASSERT( mPrimaryKeyAttrs.size() == 1 );
2951 whereClause = QStringLiteral( "[%1]=%2" ).arg( mAttributeFields.at( mPrimaryKeyAttrs[0] ).name(), FID_TO_STRING( featureId ) );
2952 break;
2953
2954 case PktFidMap:
2955 {
2956 const QVariantList &pkVals = mShared->lookupKey( featureId );
2957 if ( !pkVals.isEmpty() )
2958 {
2959 Q_ASSERT( pkVals.size() == mPrimaryKeyAttrs.size() );
2960
2961 whereClause = QStringLiteral( "(" );
2962
2963 QString delim;
2964 for ( int i = 0; i < mPrimaryKeyAttrs.size(); ++i )
2965 {
2966 const QgsField &fld = mAttributeFields.at( mPrimaryKeyAttrs[i] );
2967 whereClause += QStringLiteral( "%1[%2]=%3" ).arg( delim, fld.name(), quotedValue( pkVals[i] ) );
2968 delim = QStringLiteral( " AND " );
2969 }
2970
2971 whereClause += QLatin1Char( ')' );
2972 }
2973 else
2974 {
2975 QgsDebugMsg( QStringLiteral( "FAILURE: Key values for feature %1 not found." ).arg( featureId ) );
2976 whereClause = QStringLiteral( "NULL IS NOT NULL" );
2977 }
2978 }
2979 break;
2980
2981 default:
2982 Q_ASSERT( !"FAILURE: Primary key unknown" );
2983 whereClause = QStringLiteral( "NULL IS NOT NULL" );
2984 break;
2985 }
2986
2987 return whereClause;
2988 }
2989
2990 /* static */
parseUriKey(const QString & key)2991 QStringList QgsMssqlProvider::parseUriKey( const QString &key )
2992 {
2993 if ( key.isEmpty() ) return QStringList();
2994
2995 QStringList cols;
2996
2997 // remove quotes from key list
2998 if ( key.startsWith( '"' ) && key.endsWith( '"' ) )
2999 {
3000 int i = 1;
3001 QString col;
3002 while ( i < key.size() )
3003 {
3004 if ( key[i] == '"' )
3005 {
3006 if ( i + 1 < key.size() && key[i + 1] == '"' )
3007 {
3008 i++;
3009 }
3010 else
3011 {
3012 cols << col;
3013 col.clear();
3014
3015 if ( ++i == key.size() )
3016 break;
3017
3018 Q_ASSERT( key[i] == ',' );
3019 i++;
3020 Q_ASSERT( key[i] == '"' );
3021 i++;
3022 col.clear();
3023 continue;
3024 }
3025 }
3026
3027 col += key[i++];
3028 }
3029 }
3030 else if ( key.contains( ',' ) )
3031 {
3032 cols = key.split( ',' );
3033 }
3034 else
3035 {
3036 cols << key;
3037 }
3038
3039 return cols;
3040 }
3041
getExtentFromGeometryColumns(QgsRectangle & extent) const3042 bool QgsMssqlProvider::getExtentFromGeometryColumns( QgsRectangle &extent ) const
3043 {
3044 QSqlQuery query = createQuery();
3045 query.setForwardOnly( true );
3046
3047 const QString sql = QStringLiteral( "SELECT qgis_xmin,qgis_xmax,qgis_ymin,qgis_ymax "
3048 "FROM geometry_columns WHERE f_table_name = %1 AND f_table_schema = %2 "
3049 "AND NOT (qgis_xmin IS NULL OR qgis_xmax IS NULL OR qgis_ymin IS NULL OR qgis_ymax IS NULL)" );
3050
3051 const QString statement = sql.arg( quotedValue( mTableName ), quotedValue( mSchemaName ) );
3052 if ( query.exec( statement ) && query.isActive() )
3053 {
3054 query.next();
3055 if ( query.isValid() )
3056 {
3057 extent.setXMinimum( query.value( 0 ).toDouble() );
3058 extent.setXMaximum( query.value( 1 ).toDouble() );
3059 extent.setYMinimum( query.value( 2 ).toDouble() );
3060 extent.setYMaximum( query.value( 3 ).toDouble() );
3061
3062 return true;
3063 }
3064 }
3065
3066 return false;
3067
3068 }
3069
getPrimaryKeyFromGeometryColumns(QStringList & primaryKeys)3070 bool QgsMssqlProvider::getPrimaryKeyFromGeometryColumns( QStringList &primaryKeys )
3071 {
3072 QSqlQuery query = createQuery();
3073 query.setForwardOnly( true );
3074 primaryKeys.clear();
3075
3076 const QString sql = QStringLiteral( "SELECT qgis_pkey FROM geometry_columns WHERE f_table_name = '%1' AND NOT qgis_pkey IS NULL" );
3077 const QString statement = sql.arg( mTableName );
3078 if ( query.exec( statement ) && query.isActive() )
3079 {
3080 query.next();
3081 if ( query.isValid() )
3082 {
3083 primaryKeys = query.value( 0 ).toString().split( ',' );
3084 if ( !primaryKeys.isEmpty() )
3085 return true;
3086 }
3087 }
3088
3089 return false;
3090 }
3091