1 /*************************************************************************** 2 qgspostgresprovider.h - Data provider for PostgreSQL/PostGIS layers 3 ------------------- 4 begin : Jan 2, 2004 5 copyright : (C) 2003 by Gary E.Sherman 6 email : sherman at mrcc.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 #ifndef QGSPOSTGRESPROVIDER_H 19 #define QGSPOSTGRESPROVIDER_H 20 21 #include "qgsvectordataprovider.h" 22 #include "qgsrectangle.h" 23 #include "qgspostgresconn.h" 24 #include "qgsfields.h" 25 #include "qgsprovidermetadata.h" 26 #include <memory> 27 28 class QgsFeature; 29 class QgsField; 30 class QgsGeometry; 31 32 class QgsPostgresFeatureIterator; 33 class QgsPostgresSharedData; 34 class QgsPostgresTransaction; 35 class QgsPostgresListener; 36 37 #include "qgsdatasourceuri.h" 38 39 /** 40 * \class QgsPostgresProvider 41 * \brief Data provider for PostgreSQL/PostGIS layers. 42 * 43 * This provider implements the 44 * interface defined in the QgsDataProvider class to provide access to spatial 45 * data residing in a PostgreSQL/PostGIS enabled database. 46 */ 47 class QgsPostgresProvider final: public QgsVectorDataProvider 48 { 49 Q_OBJECT 50 51 public: 52 53 static const QString POSTGRES_KEY; 54 static const QString POSTGRES_DESCRIPTION; 55 56 enum Relkind 57 { 58 NotSet, 59 Unknown, 60 OrdinaryTable, // r 61 Index, // i 62 Sequence, // s 63 View, // v 64 MaterializedView, // m 65 CompositeType, // c 66 ToastTable, // t 67 ForeignTable, // f 68 PartitionedTable // p - PostgreSQL 10 69 }; 70 Q_ENUM( Relkind ) 71 72 /** 73 * Import a vector layer into the database 74 * \param options options for provider, specified via a map of option name 75 * to value. Valid options are lowercaseFieldNames (set to true to convert 76 * field names to lowercase), dropStringConstraints (set to true to remove 77 * length constraints on character fields). 78 */ 79 static Qgis::VectorExportResult createEmptyLayer( 80 const QString &uri, 81 const QgsFields &fields, 82 QgsWkbTypes::Type wkbType, 83 const QgsCoordinateReferenceSystem &srs, 84 bool overwrite, 85 QMap<int, int> *oldToNewAttrIdxMap, 86 QString *errorMessage = nullptr, 87 const QMap<QString, QVariant> *options = nullptr 88 ); 89 90 /** 91 * Constructor for the provider. The uri must be in the following format: 92 * host=localhost dbname=test [user=gsherman [password=xxx] | authcfg=xxx] table=test.alaska (the_geom) 93 * \param uri String containing the required parameters to connect to the database 94 * and query the table. 95 * \param options generic data provider options 96 * \param flags generic data provider flags 97 */ 98 explicit QgsPostgresProvider( QString const &uri, const QgsDataProvider::ProviderOptions &providerOptions, 99 QgsDataProvider::ReadFlags flags = QgsDataProvider::ReadFlags() ); 100 101 102 ~QgsPostgresProvider() override; 103 104 QgsAbstractFeatureSource *featureSource() const override; 105 QString storageType() const override; 106 QgsCoordinateReferenceSystem crs() const override; 107 QgsFeatureIterator getFeatures( const QgsFeatureRequest &request ) const override; 108 QgsWkbTypes::Type wkbType() const override; 109 QgsLayerMetadata layerMetadata() const override; 110 111 /** 112 * Returns the number of layers for the current data source 113 * \note Should this be subLayerCount() instead? 114 */ 115 size_t layerCount() const; 116 117 long long featureCount() const override; 118 119 /** 120 * Determines if there is at least one feature available on this table. 121 * 122 * \note In contrast to the featureCount() method, this method is not 123 * affected by estimated metadata. 124 * 125 * \since QGIS 3.4 126 */ 127 bool empty() const override; 128 129 /** 130 * Returns a string representation of the endian-ness for the layer 131 */ 132 static QString endianString(); 133 134 /** 135 * Returns a list of unquoted column names from an uri key 136 */ 137 static QStringList parseUriKey( const QString &key ); 138 139 /** 140 * Changes the stored extent for this layer to the supplied extent. 141 * For example, this is called when the extent worker thread has a result. 142 */ 143 void setExtent( QgsRectangle &newExtent ); 144 145 QgsRectangle extent() const override; 146 void updateExtents() override; 147 148 /** 149 * Determine the fields making up the primary key 150 */ 151 bool determinePrimaryKey(); 152 153 /** 154 * Determine the fields making up the primary key from the uri attribute keyColumn 155 * 156 * Fills mPrimaryKeyType and mPrimaryKeyAttrs 157 * from mUri 158 */ 159 void determinePrimaryKeyFromUriKeyColumn(); 160 161 QgsFields fields() const override; 162 QString dataComment() const override; 163 QVariant minimumValue( int index ) const override; 164 QVariant maximumValue( int index ) const override; 165 QSet< QVariant > uniqueValues( int index, int limit = -1 ) const override; 166 QStringList uniqueStringsMatching( int index, const QString &substring, int limit = -1, 167 QgsFeedback *feedback = nullptr ) const override; 168 void enumValues( int index, QStringList &enumList ) const override; 169 bool isValid() const override; isSaveAndLoadStyleToDatabaseSupported()170 bool isSaveAndLoadStyleToDatabaseSupported() const override { return true; } isDeleteStyleFromDatabaseSupported()171 bool isDeleteStyleFromDatabaseSupported() const override { return true; } 172 QgsAttributeList attributeIndexes() const override; pkAttributeIndexes()173 QgsAttributeList pkAttributeIndexes() const override { return mPrimaryKeyAttrs; } 174 QString defaultValueClause( int fieldId ) const override; 175 QVariant defaultValue( int fieldId ) const override; 176 bool skipConstraintCheck( int fieldIndex, QgsFieldConstraints::Constraint constraint, const QVariant &value = QVariant() ) const override; 177 bool addFeatures( QgsFeatureList &flist, QgsFeatureSink::Flags flags = QgsFeatureSink::Flags() ) override; 178 bool deleteFeatures( const QgsFeatureIds &id ) override; 179 bool truncate() override; 180 bool addAttributes( const QList<QgsField> &attributes ) override; 181 bool deleteAttributes( const QgsAttributeIds &name ) override; 182 bool renameAttributes( const QgsFieldNameMap &renamedAttributes ) override; 183 bool changeAttributeValues( const QgsChangedAttributesMap &attr_map ) override; 184 bool changeGeometryValues( const QgsGeometryMap &geometry_map ) override; 185 bool changeFeatures( const QgsChangedAttributesMap &attr_map, const QgsGeometryMap &geometry_map ) override; 186 187 //! Gets the postgres connection 188 PGconn *pgConnection(); 189 190 //! Gets the table name associated with this provider instance 191 QString getTableName(); 192 193 QString subsetString() const override; 194 bool setSubsetString( const QString &theSQL, bool updateFeatureCount = true ) override; supportsSubsetString()195 bool supportsSubsetString() const override { return true; } 196 QgsVectorDataProvider::Capabilities capabilities() const override; 197 SpatialIndexPresence hasSpatialIndex() const override; 198 199 /** 200 * The Postgres provider does its own transforms so we return 201 * true for the following three functions to indicate that transforms 202 * should not be handled by the QgsCoordinateTransform object. See the 203 * documentation on QgsVectorDataProvider for details on these functions. 204 */ 205 // XXX For now we have disabled native transforms in the PG provider since 206 // it appears there are problems with some of the projection definitions supportsNativeTransform()207 bool supportsNativeTransform() {return false;} 208 209 QString name() const override; 210 QString description() const override; 211 QgsTransaction *transaction() const override; 212 static QString providerKey(); 213 214 /** 215 * Convert the postgres string representation into the given QVariant type. 216 * \param type the wanted type 217 * \param subType if type is a collection, the wanted element type 218 * \param value the value to convert 219 * \returns a QVariant of the given type or a null QVariant 220 */ 221 static QVariant convertValue( QVariant::Type type, QVariant::Type subType, const QString &value, const QString &typeName ); 222 223 QList<QgsRelation> discoverRelations( const QgsVectorLayer *target, const QList<QgsVectorLayer *> &layers ) const override; 224 QgsAttrPalIndexNameHash palAttributeIndexNames() const override; 225 226 /** 227 * Returns true if the data source has metadata, false otherwise. For 228 * example, if the kind of relation for the layer is a view or a 229 * materialized view, then no metadata are associated with the data 230 * source. 231 * 232 * \returns true if data source has metadata, false otherwise. 233 * 234 * \since QGIS 3.0 235 */ 236 bool hasMetadata() const override; 237 238 /** 239 * Launch a listening thead to listen to postgres NOTIFY on "qgis" channel 240 * the notification is transformed into a Qt signal. 241 * 242 * \since QGIS 3.0 243 */ 244 void setListening( bool isListening ) override; 245 246 private: 247 248 /** 249 * \returns relation kind 250 */ 251 Relkind relkind() const; 252 253 /** 254 * Change internal query with \a query 255 */ 256 void setQuery( const QString &query ); 257 258 bool declareCursor( const QString &cursorName, 259 const QgsAttributeList &fetchAttributes, 260 bool fetchGeometry, 261 QString whereClause ); 262 263 bool getFeature( QgsPostgresResult &queryResult, 264 int row, 265 bool fetchGeometry, 266 QgsFeature &feature, 267 const QgsAttributeList &fetchAttributes ); 268 269 QString geomParam( int offset ) const; 270 271 272 static QString getNextString( const QString &txt, int &i, const QString &sep ); 273 static QVariant parseHstore( const QString &txt ); 274 static QVariant parseJson( const QString &txt ); 275 static QVariant parseOtherArray( const QString &txt, QVariant::Type subType, const QString &typeName ); 276 static QVariant parseStringArray( const QString &txt ); 277 static QVariant parseMultidimensionalArray( const QString &txt ); 278 static QVariant parseArray( const QString &txt, QVariant::Type type, QVariant::Type subType, const QString &typeName ); 279 280 281 /** 282 * Gets parametrized primary key clause 283 * \param offset specifies offset to use for the pk value parameter 284 * \param alias specifies an optional alias given to the subject table 285 */ 286 QString pkParamWhereClause( int offset, const char *alias = nullptr ) const; 287 QString whereClause( QgsFeatureId featureId ) const; 288 QString whereClause( QgsFeatureIds featureIds ) const; 289 QString filterWhereClause() const; 290 291 bool hasSufficientPermsAndCapabilities(); 292 293 QgsField field( int index ) const; 294 295 /** 296 * Load the field list 297 */ 298 bool loadFields(); 299 300 /** 301 * Set the default widget type for the fields 302 */ 303 void setEditorWidgets(); 304 305 //! Convert a QgsField to work with PG 306 static bool convertField( QgsField &field, const QMap<QString, QVariant> *coordinateTransformContext = nullptr ); 307 308 /** 309 * Parses the enum_range of an attribute and inserts the possible values into a stringlist 310 * \param enumValues the stringlist where the values are appended 311 * \param attributeName the name of the enum attribute 312 * \returns true in case of success and fals in case of error (e.g. if the type is not an enum type) 313 */ 314 bool parseEnumRange( QStringList &enumValues, const QString &attributeName ) const; 315 316 /** 317 * Parses the possible enum values of a domain type (given in the check constraint of the domain type) 318 * \param enumValues Reference to list that receives enum values 319 * \param attributeName Name of the domain type attribute 320 * \returns true in case of success and false in case of error (e.g. if the attribute is not a domain type or does not have a check constraint) 321 */ 322 bool parseDomainCheckConstraint( QStringList &enumValues, const QString &attributeName ) const; 323 324 /** 325 * Returns the type of primary key for a PK field 326 * 327 * \param fld the field to determine PK type of 328 * \returns the PrimaryKeyType 329 * 330 * \note that this only makes sense for single-field primary keys, 331 * whereas multi-field keys always need the PktFidMap 332 * primary key type. 333 */ 334 QgsPostgresPrimaryKeyType pkType( const QgsField &fld ) const; 335 336 /** 337 * Search all the layers using the given table. 338 */ 339 static QList<QgsVectorLayer *> searchLayers( const QList<QgsVectorLayer *> &layers, const QString &connectionInfo, const QString &schema, const QString &tableName ); 340 341 /** 342 * Effect a reload including resetting the feature count 343 * and setting the layer extent to minimal 344 * 345 * \since QGIS 3.12 346 */ 347 void reloadProviderData() override; 348 349 //! Old-style mapping of index to name for QgsPalLabeling fix 350 QgsAttrPalIndexNameHash mAttrPalIndexName; 351 352 QgsFields mAttributeFields; 353 QHash<int, char> mIdentityFields; 354 QString mDataComment; 355 356 //! Data source URI struct for this layer 357 QgsDataSourceUri mUri; 358 359 /** 360 * Flag indicating if the layer data source is a valid PostgreSQL layer 361 */ 362 bool mValid = false; 363 364 /** 365 * provider references query (instead of a table) 366 */ 367 bool mIsQuery; 368 369 /** 370 * Name of the table with no schema 371 */ 372 QString mTableName; 373 374 /** 375 * Name of the table or subquery 376 */ 377 QString mQuery; 378 379 /** 380 * Name of the schema 381 */ 382 QString mSchemaName; 383 384 /** 385 * SQL statement used to limit the features retrieved 386 */ 387 QString mSqlWhereClause; 388 389 /** 390 * Kind of relation 391 */ 392 mutable Relkind mKind = Relkind::NotSet; 393 394 /** 395 * Data type for the primary key 396 */ 397 QgsPostgresPrimaryKeyType mPrimaryKeyType = PktUnknown; 398 399 /** 400 * Data type for the spatial column 401 */ 402 QgsPostgresGeometryColumnType mSpatialColType = SctNone; 403 404 /** 405 * List of primary key attributes for fetching features. 406 */ 407 QList<int> mPrimaryKeyAttrs; 408 QString mPrimaryKeyDefault; 409 410 QString mGeometryColumn; //!< Name of the geometry column 411 QString mBoundingBoxColumn; //!< Name of the bounding box column 412 mutable QgsRectangle mLayerExtent; //!< Rectangle that contains the extent (bounding box) of the layer 413 414 QgsWkbTypes::Type mDetectedGeomType = QgsWkbTypes::Unknown ; //!< Geometry type detected in the database 415 QgsWkbTypes::Type mRequestedGeomType = QgsWkbTypes::Unknown ; //!< Geometry type requested in the uri 416 QString mDetectedSrid; //!< Spatial reference detected in the database 417 QString mRequestedSrid; //!< Spatial reference requested in the uri 418 419 std::shared_ptr<QgsPostgresSharedData> mShared; //!< Mutable data shared between provider and feature sources 420 421 bool getGeometryDetails(); 422 423 //! @{ Only used with TopoGeometry layers 424 425 struct TopoLayerInfo 426 { 427 QString topologyName; 428 long layerId; 429 }; 430 431 TopoLayerInfo mTopoLayerInfo; 432 433 bool getTopoLayerInfo(); 434 435 void dropOrphanedTopoGeoms(); 436 437 //! @} 438 439 /* Use estimated metadata. Uses fast table counts, geometry type and extent determination */ 440 bool mUseEstimatedMetadata = false; 441 442 bool mSelectAtIdDisabled = false; //!< Disable support for SelectAtId 443 444 struct PGFieldNotFound {}; //! Exception to throw 445 446 struct PGException 447 { PGExceptionPGException448 explicit PGException( QgsPostgresResult &r ) 449 : mWhat( r.PQresultErrorMessage() ) 450 {} 451 errorMessagePGException452 QString errorMessage() const 453 { 454 return mWhat; 455 } 456 457 private: 458 QString mWhat; 459 }; 460 461 // A function that determines if the given columns contain unique entries 462 bool uniqueData( const QString "edColNames ); 463 464 QgsVectorDataProvider::Capabilities mEnabledCapabilities = QgsVectorDataProvider::Capabilities(); 465 466 void appendGeomParam( const QgsGeometry &geom, QStringList ¶m ) const; 467 void appendPkParams( QgsFeatureId fid, QStringList ¶m ) const; 468 469 QString paramValue( const QString &fieldvalue, const QString &defaultValue ) const; 470 471 QgsPostgresConn *mConnectionRO = nullptr ; //!< Read-only database connection (initially) 472 QgsPostgresConn *mConnectionRW = nullptr ; //!< Read-write database connection (on update) 473 474 QgsPostgresConn *connectionRO() const; 475 QgsPostgresConn *connectionRW(); 476 477 void disconnectDb(); 478 quotedIdentifier(const QString & ident)479 static QString quotedIdentifier( const QString &ident ) { return QgsPostgresConn::quotedIdentifier( ident ); } quotedValue(const QVariant & value)480 static QString quotedValue( const QVariant &value ) { return QgsPostgresConn::quotedValue( value ); } quotedJsonValue(const QVariant & value)481 static QString quotedJsonValue( const QVariant &value ) { return QgsPostgresConn::quotedJsonValue( value ); } 482 static QString quotedByteaValue( const QVariant &value ); 483 484 friend class QgsPostgresFeatureSource; 485 486 QgsPostgresTransaction *mTransaction = nullptr; 487 488 void setTransaction( QgsTransaction *transaction ) override; 489 490 QHash<int, QString> mDefaultValues; 491 492 // for handling generated columns, available in PostgreSQL 12+ 493 // See https://www.postgresql.org/docs/12/ddl-generated-columns.html 494 QHash<int, QString> mGeneratedValues; 495 496 bool mCheckPrimaryKeyUnicity = true; 497 498 QgsLayerMetadata mLayerMetadata; 499 500 std::unique_ptr< QgsPostgresListener > mListener; 501 }; 502 503 504 //! Assorted Postgres utility functions 505 class QgsPostgresUtils 506 { 507 public: 508 static bool deleteLayer( const QString &uri, QString &errCause ); 509 static bool deleteSchema( const QString &schema, const QgsDataSourceUri &uri, QString &errCause, bool cascade = false ); 510 511 static QString whereClause( QgsFeatureId featureId, 512 const QgsFields &fields, 513 QgsPostgresConn *conn, 514 QgsPostgresPrimaryKeyType pkType, 515 const QList<int> &pkAttrs, 516 const std::shared_ptr<QgsPostgresSharedData> &sharedData ); 517 518 static QString whereClause( const QgsFeatureIds &featureIds, 519 const QgsFields &fields, 520 QgsPostgresConn *conn, 521 QgsPostgresPrimaryKeyType pkType, 522 const QList<int> &pkAttrs, 523 const std::shared_ptr<QgsPostgresSharedData> &sharedData ); 524 525 static QString andWhereClauses( const QString &c1, const QString &c2 ); 526 527 static const qint64 INT32PK_OFFSET = 4294967296; // 2^32 528 529 // We shift negative 32bit integers to above the max 32bit 530 // positive integer to support the whole range of int32 values 531 // See https://github.com/qgis/QGIS/issues/22258 int32pk_to_fid(qint32 x)532 static qint64 int32pk_to_fid( qint32 x ) 533 { 534 return x >= 0 ? x : x + INT32PK_OFFSET; 535 } 536 fid_to_int32pk(qint64 x)537 static qint32 fid_to_int32pk( qint64 x ) 538 { 539 return x <= ( ( INT32PK_OFFSET ) / 2.0 ) ? x : -( INT32PK_OFFSET - x ); 540 } 541 542 //! Replaces invalid XML chars with UTF-8[<char_code>] 543 static void replaceInvalidXmlChars( QString &xml ); 544 545 //! Replaces UTF-8[<char_code>] with the actual unicode char 546 static void restoreInvalidXmlChars( QString &xml ); 547 }; 548 549 /** 550 * Data shared between provider class and its feature sources. Ideally there should 551 * be as few members as possible because there could be simultaneous reads/writes 552 * from different threads and therefore locking has to be involved. 553 */ 554 class QgsPostgresSharedData 555 { 556 public: 557 QgsPostgresSharedData() = default; 558 559 long long featuresCounted(); 560 void setFeaturesCounted( long long count ); 561 void addFeaturesCounted( long long diff ); 562 void ensureFeaturesCountedAtLeast( long long fetched ); 563 564 // FID lookups 565 QgsFeatureId lookupFid( const QVariantList &v ); // lookup existing mapping or add a new one 566 QVariantList removeFid( QgsFeatureId fid ); 567 void insertFid( QgsFeatureId fid, const QVariantList &k ); 568 QVariantList lookupKey( QgsFeatureId featureId ); 569 void clear(); 570 571 void clearSupportsEnumValuesCache( ); 572 bool fieldSupportsEnumValuesIsSet( int index ); 573 bool fieldSupportsEnumValues( int index ); 574 void setFieldSupportsEnumValues( int index, bool isSupported ); 575 576 protected: 577 QMutex mMutex; //!< Access to all data members is guarded by the mutex 578 579 long long mFeaturesCounted = -1 ; //!< Number of features in the layer 580 581 QgsFeatureId mFidCounter = 0; // next feature id if map is used 582 QMap<QVariantList, QgsFeatureId> mKeyToFid; // map key values to feature id 583 QMap<QgsFeatureId, QVariantList> mFidToKey; // map feature id back to key values 584 QMap<int, bool> mFieldSupportsEnumValues; // map field index to bool flag supports enum values 585 }; 586 587 class QgsPostgresProviderMetadata final: public QgsProviderMetadata 588 { 589 public: 590 QgsPostgresProviderMetadata(); 591 QgsDataProvider *createProvider( const QString &uri, const QgsDataProvider::ProviderOptions &options, QgsDataProvider::ReadFlags flags = QgsDataProvider::ReadFlags() ) override; 592 QList< QgsDataItemProvider * > dataItemProviders() const override; 593 Qgis::VectorExportResult createEmptyLayer( const QString &uri, const QgsFields &fields, QgsWkbTypes::Type wkbType, 594 const QgsCoordinateReferenceSystem &srs, 595 bool overwrite, 596 QMap<int, int> &oldToNewAttrIdxMap, QString &errorMessage, 597 const QMap<QString, QVariant> *options ) override; 598 bool saveStyle( const QString &uri, const QString &qmlStyle, const QString &sldStyle, const QString &styleName, 599 const QString &styleDescription, const QString &uiFileContent, bool useAsDefault, QString &errCause ) override; 600 QString loadStyle( const QString &uri, QString &errCause ) override; 601 int listStyles( const QString &uri, QStringList &ids, 602 QStringList &names, QStringList &descriptions, QString &errCause ) override; 603 bool deleteStyleById( const QString &uri, QString styleId, QString &errCause ) override; 604 QString getStyleById( const QString &uri, QString styleId, QString &errCause ) override; 605 QgsTransaction *createTransaction( const QString &connString ) override; 606 QMap<QString, QgsAbstractProviderConnection *> connections( bool cached = true ) override; 607 QgsAbstractProviderConnection *createConnection( const QString &name ) override; 608 QgsAbstractProviderConnection *createConnection( const QString &uri, const QVariantMap &configuration ) override; 609 void deleteConnection( const QString &name ) override; 610 void saveConnection( const QgsAbstractProviderConnection *createConnection, const QString &name ) override; 611 void initProvider() override; 612 void cleanupProvider() override; 613 QVariantMap decodeUri( const QString &uri ) const override; 614 QString encodeUri( const QVariantMap &parts ) const override; 615 }; 616 617 // clazy:excludeall=qstring-allocations 618 619 #endif 620