Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
5329 lines (4559 sloc) 172 KB
/***************************************************************************
qgspostgresprovider.cpp - QGIS data provider for PostgreSQL/PostGIS layers
-------------------
begin : 2004/01/07
copyright : (C) 2004 by Gary E.Sherman
email : sherman at mrcc.com
***************************************************************************/
/***************************************************************************
* *
* This program is free software; you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation; either version 2 of the License, or *
* (at your option) any later version. *
* *
***************************************************************************/
#include "qgsapplication.h"
#include "qgsfeature.h"
#include "qgsfield.h"
#include "qgsgeometry.h"
#include "qgsmessageoutput.h"
#include "qgsmessagelog.h"
#include "qgsprojectstorageregistry.h"
#include "qgsrectangle.h"
#include "qgscoordinatereferencesystem.h"
#include "qgsxmlutils.h"
#include "qgsvectorlayer.h"
#include <QMessageBox>
#include "qgsvectorlayerexporter.h"
#include "qgspostgresprovider.h"
#include "qgspostgresconn.h"
#include "qgspostgresconnpool.h"
#include "qgspostgresdataitems.h"
#include "qgspostgresfeatureiterator.h"
#include "qgspostgrestransaction.h"
#include "qgspostgreslistener.h"
#include "qgspostgresprojectstorage.h"
#include "qgspostgresproviderconnection.h"
#include "qgslogger.h"
#include "qgsfeedback.h"
#include "qgssettings.h"
#include "qgsjsonutils.h"
#include "qgspostgresprovider.h"
#include "qgsprovidermetadata.h"
#include "qgspostgresproviderconnection.h"
const QString QgsPostgresProvider::POSTGRES_KEY = QStringLiteral( "postgres" );
const QString QgsPostgresProvider::POSTGRES_DESCRIPTION = QStringLiteral( "PostgreSQL/PostGIS data provider" );
static const QString EDITOR_WIDGET_STYLES_TABLE = QStringLiteral( "qgis_editor_widget_styles" );
inline qint64 PKINT2FID( qint32 x )
{
return QgsPostgresUtils::int32pk_to_fid( x );
}
inline qint32 FID2PKINT( qint64 x )
{
return QgsPostgresUtils::fid_to_int32pk( x );
}
static bool tableExists( QgsPostgresConn &conn, const QString &name )
{
QgsPostgresResult res( conn.PQexec( "SELECT EXISTS ( SELECT oid FROM pg_catalog.pg_class WHERE relname=" + QgsPostgresConn::quotedValue( name ) + ")" ) );
return res.PQgetvalue( 0, 0 ).startsWith( 't' );
}
QgsPostgresPrimaryKeyType
QgsPostgresProvider::pkType( const QgsField &f ) const
{
switch ( f.type() )
{
case QVariant::LongLong:
// unless we can guarantee all values are unsigned
// (in which case we could use pktUint64)
// we'll have to use a Map type.
// See https://github.com/qgis/QGIS/issues/22258
return PktFidMap; // pktUint64
case QVariant::Int:
return PktInt;
default:
return PktFidMap;
}
}
QgsPostgresProvider::QgsPostgresProvider( QString const &uri, const ProviderOptions &options )
: QgsVectorDataProvider( uri, options )
, mShared( new QgsPostgresSharedData )
{
QgsDebugMsg( QStringLiteral( "URI: %1 " ).arg( uri ) );
mUri = QgsDataSourceUri( uri );
// populate members from the uri structure
mSchemaName = mUri.schema();
mTableName = mUri.table();
mGeometryColumn = mUri.geometryColumn();
mBoundingBoxColumn = mUri.param( "bbox" );
if ( mBoundingBoxColumn.isEmpty() )
{
mBoundingBoxColumn = mGeometryColumn;
}
mSqlWhereClause = mUri.sql();
mRequestedSrid = mUri.srid();
mRequestedGeomType = mUri.wkbType();
if ( mUri.hasParam( QStringLiteral( "checkPrimaryKeyUnicity" ) ) )
{
if ( mUri.param( QStringLiteral( "checkPrimaryKeyUnicity" ) ).compare( QLatin1String( "0" ) ) == 0 )
{
mCheckPrimaryKeyUnicity = false;
}
else
{
mCheckPrimaryKeyUnicity = true;
}
}
if ( mSchemaName.isEmpty() && mTableName.startsWith( '(' ) && mTableName.endsWith( ')' ) )
{
mIsQuery = true;
mQuery = mTableName;
mTableName.clear();
}
else
{
mIsQuery = false;
if ( !mSchemaName.isEmpty() )
{
mQuery += quotedIdentifier( mSchemaName ) + '.';
}
if ( !mTableName.isEmpty() )
{
mQuery += quotedIdentifier( mTableName );
}
}
mUseEstimatedMetadata = mUri.useEstimatedMetadata();
mSelectAtIdDisabled = mUri.selectAtIdDisabled();
QgsDebugMsg( QStringLiteral( "Connection info is %1" ).arg( mUri.connectionInfo( false ) ) );
QgsDebugMsg( QStringLiteral( "Geometry column is: %1" ).arg( mGeometryColumn ) );
QgsDebugMsg( QStringLiteral( "Schema is: %1" ).arg( mSchemaName ) );
QgsDebugMsg( QStringLiteral( "Table name is: %1" ).arg( mTableName ) );
QgsDebugMsg( QStringLiteral( "Query is: %1" ).arg( mQuery ) );
QgsDebugMsg( QStringLiteral( "Where clause is: %1" ).arg( mSqlWhereClause ) );
// no table/query passed, the provider could be used to get tables
if ( mQuery.isEmpty() )
{
return;
}
mConnectionRO = QgsPostgresConn::connectDb( mUri.connectionInfo( false ), true );
if ( !mConnectionRO )
{
return;
}
if ( !hasSufficientPermsAndCapabilities() ) // check permissions and set capabilities
{
disconnectDb();
return;
}
if ( !getGeometryDetails() ) // gets srid, geometry and data type
{
// the table is not a geometry table
QgsMessageLog::logMessage( tr( "Invalid PostgreSQL layer" ), tr( "PostGIS" ) );
disconnectDb();
return;
}
// NOTE: mValid would be true after true return from
// getGeometryDetails, see https://github.com/qgis/QGIS/issues/21807
if ( mSpatialColType == SctTopoGeometry )
{
if ( !getTopoLayerInfo() ) // gets topology name and layer id
{
QgsMessageLog::logMessage( tr( "Invalid PostgreSQL topology layer" ), tr( "PostGIS" ) );
mValid = false;
disconnectDb();
return;
}
}
mLayerExtent.setMinimal();
// set the primary key
if ( !determinePrimaryKey() )
{
QgsMessageLog::logMessage( tr( "PostgreSQL layer has no primary key." ), tr( "PostGIS" ) );
mValid = false;
disconnectDb();
return;
}
// Set the PostgreSQL message level so that we don't get the
// 'there is no transaction in progress' warning.
#ifndef QGISDEBUG
mConnectionRO->PQexecNR( QStringLiteral( "set client_min_messages to error" ) );
#endif
//fill type names into sets
QList<NativeType> nativeTypes;
nativeTypes // integer types
<< QgsVectorDataProvider::NativeType( tr( "Whole number (smallint - 16bit)" ), QStringLiteral( "int2" ), QVariant::Int, -1, -1, 0, 0 )
<< QgsVectorDataProvider::NativeType( tr( "Whole number (integer - 32bit)" ), QStringLiteral( "int4" ), QVariant::Int, -1, -1, 0, 0 )
<< QgsVectorDataProvider::NativeType( tr( "Whole number (integer - 64bit)" ), QStringLiteral( "int8" ), QVariant::LongLong, -1, -1, 0, 0 )
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (numeric)" ), QStringLiteral( "numeric" ), QVariant::Double, 1, 20, 0, 20 )
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (decimal)" ), QStringLiteral( "decimal" ), QVariant::Double, 1, 20, 0, 20 )
// floating point
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (real)" ), QStringLiteral( "real" ), QVariant::Double, -1, -1, -1, -1 )
<< QgsVectorDataProvider::NativeType( tr( "Decimal number (double)" ), QStringLiteral( "double precision" ), QVariant::Double, -1, -1, -1, -1 )
// string types
<< QgsVectorDataProvider::NativeType( tr( "Text, fixed length (char)" ), QStringLiteral( "char" ), QVariant::String, 1, 255, -1, -1 )
<< QgsVectorDataProvider::NativeType( tr( "Text, limited variable length (varchar)" ), QStringLiteral( "varchar" ), QVariant::String, 1, 255, -1, -1 )
<< QgsVectorDataProvider::NativeType( tr( "Text, unlimited length (text)" ), QStringLiteral( "text" ), QVariant::String, -1, -1, -1, -1 )
<< QgsVectorDataProvider::NativeType( tr( "Text, case-insensitive unlimited length (citext)" ), QStringLiteral( "citext" ), QVariant::String, -1, -1, -1, -1 )
// date type
<< QgsVectorDataProvider::NativeType( tr( "Date" ), QStringLiteral( "date" ), QVariant::Date, -1, -1, -1, -1 )
<< QgsVectorDataProvider::NativeType( tr( "Time" ), QStringLiteral( "time" ), QVariant::Time, -1, -1, -1, -1 )
<< QgsVectorDataProvider::NativeType( tr( "Date & Time" ), QStringLiteral( "timestamp without time zone" ), QVariant::DateTime, -1, -1, -1, -1 )
// complex types
<< QgsVectorDataProvider::NativeType( tr( "Map (hstore)" ), QStringLiteral( "hstore" ), QVariant::Map, -1, -1, -1, -1, QVariant::String )
<< QgsVectorDataProvider::NativeType( tr( "Array of number (integer - 32bit)" ), QStringLiteral( "int4[]" ), QVariant::List, -1, -1, -1, -1, QVariant::Int )
<< QgsVectorDataProvider::NativeType( tr( "Array of number (integer - 64bit)" ), QStringLiteral( "int8[]" ), QVariant::List, -1, -1, -1, -1, QVariant::LongLong )
<< QgsVectorDataProvider::NativeType( tr( "Array of number (double)" ), QStringLiteral( "double precision[]" ), QVariant::List, -1, -1, -1, -1, QVariant::Double )
<< QgsVectorDataProvider::NativeType( tr( "Array of text" ), QStringLiteral( "text[]" ), QVariant::StringList, -1, -1, -1, -1, QVariant::String )
// boolean
<< QgsVectorDataProvider::NativeType( tr( "Boolean" ), QStringLiteral( "bool" ), QVariant::Bool, -1, -1, -1, -1 )
// binary (bytea)
<< QgsVectorDataProvider::NativeType( tr( "Binary object (bytea)" ), QStringLiteral( "bytea" ), QVariant::ByteArray, -1, -1, -1, -1 )
;
if ( connectionRO()->pgVersion() >= 90200 )
{
nativeTypes << QgsVectorDataProvider::NativeType( tr( "JSON (json)" ), QStringLiteral( "json" ), QVariant::Map, -1, -1, -1, -1, QVariant::String );
if ( connectionRO()->pgVersion() >= 90400 )
{
nativeTypes << QgsVectorDataProvider::NativeType( tr( "JSON (jsonb)" ), QStringLiteral( "jsonb" ), QVariant::Map, -1, -1, -1, -1, QVariant::String );
}
}
setNativeTypes( nativeTypes );
QString key;
switch ( mPrimaryKeyType )
{
case PktOid:
key = QStringLiteral( "oid" );
break;
case PktTid:
key = QStringLiteral( "tid" );
break;
case PktInt:
case PktUint64:
Q_ASSERT( mPrimaryKeyAttrs.size() == 1 );
Q_ASSERT( mPrimaryKeyAttrs[0] >= 0 && mPrimaryKeyAttrs[0] < mAttributeFields.count() );
key = mAttributeFields.at( mPrimaryKeyAttrs.at( 0 ) ).name();
break;
case PktFidMap:
{
QString delim;
const auto constMPrimaryKeyAttrs = mPrimaryKeyAttrs;
for ( int idx : constMPrimaryKeyAttrs )
{
key += delim + mAttributeFields.at( idx ).name();
delim = ',';
}
}
break;
case PktUnknown:
QgsMessageLog::logMessage( tr( "PostgreSQL layer has unknown primary key type." ), tr( "PostGIS" ) );
mValid = false;
break;
}
if ( mValid )
{
mUri.setKeyColumn( key );
setDataSourceUri( mUri.uri( false ) );
}
else
{
disconnectDb();
}
mLayerMetadata.setType( QStringLiteral( "dataset" ) );
mLayerMetadata.setCrs( crs() );
}
QgsPostgresProvider::~QgsPostgresProvider()
{
disconnectDb();
QgsDebugMsg( QStringLiteral( "deconstructing." ) );
}
QgsAbstractFeatureSource *QgsPostgresProvider::featureSource() const
{
return new QgsPostgresFeatureSource( this );
}
QgsPostgresConn *QgsPostgresProvider::connectionRO() const
{
return mTransaction ? mTransaction->connection() : mConnectionRO;
}
void QgsPostgresProvider::setListening( bool isListening )
{
if ( isListening && !mListener )
{
mListener.reset( QgsPostgresListener::create( mUri.connectionInfo( false ) ).release() );
connect( mListener.get(), &QgsPostgresListener::notify, this, &QgsPostgresProvider::notify );
}
else if ( !isListening && mListener )
{
disconnect( mListener.get(), &QgsPostgresListener::notify, this, &QgsPostgresProvider::notify );
mListener.reset();
}
}
QgsPostgresConn *QgsPostgresProvider::connectionRW()
{
if ( mTransaction )
{
return mTransaction->connection();
}
else if ( !mConnectionRW )
{
mConnectionRW = QgsPostgresConn::connectDb( mUri.connectionInfo( false ), false );
}
return mConnectionRW;
}
QgsTransaction *QgsPostgresProvider::transaction() const
{
return mTransaction;
}
QString QgsPostgresProvider::providerKey()
{
return POSTGRES_KEY;
}
void QgsPostgresProvider::setTransaction( QgsTransaction *transaction )
{
// static_cast since layers cannot be added to a transaction of a non-matching provider
mTransaction = static_cast<QgsPostgresTransaction *>( transaction );
}
void QgsPostgresProvider::disconnectDb()
{
if ( mConnectionRO )
{
mConnectionRO->unref();
mConnectionRO = nullptr;
}
if ( mConnectionRW )
{
mConnectionRW->unref();
mConnectionRW = nullptr;
}
}
QString QgsPostgresProvider::quotedByteaValue( const QVariant &value )
{
if ( value.isNull() )
return QStringLiteral( "NULL" );
const QByteArray ba = value.toByteArray();
const unsigned char *buf = reinterpret_cast< const unsigned char * >( ba.constData() );
QString param;
param.reserve( ba.length() * 4 );
for ( int i = 0; i < ba.length(); ++i )
{
param += QStringLiteral( "\\%1" ).arg( static_cast< int >( buf[i] ), 3, 8, QChar( '0' ) );
}
return QStringLiteral( "decode('%1','escape')" ).arg( param );
}
QString QgsPostgresProvider::storageType() const
{
return QStringLiteral( "PostgreSQL database with PostGIS extension" );
}
QgsFeatureIterator QgsPostgresProvider::getFeatures( const QgsFeatureRequest &request ) const
{
if ( !mValid )
{
QgsMessageLog::logMessage( tr( "Read attempt on an invalid PostgreSQL data source" ), tr( "PostGIS" ) );
return QgsFeatureIterator();
}
QgsPostgresFeatureSource *featureSrc = static_cast<QgsPostgresFeatureSource *>( featureSource() );
return QgsFeatureIterator( new QgsPostgresFeatureIterator( featureSrc, true, request ) );
}
QString QgsPostgresProvider::pkParamWhereClause( int offset, const char *alias ) const
{
QString whereClause;
QString aliased;
if ( alias ) aliased = QStringLiteral( "%1." ).arg( alias );
switch ( mPrimaryKeyType )
{
case PktTid:
whereClause = QStringLiteral( "%2ctid=$%1" ).arg( offset ).arg( aliased );
break;
case PktOid:
whereClause = QStringLiteral( "%2oid=$%1" ).arg( offset ).arg( aliased );
break;
case PktInt:
case PktUint64:
Q_ASSERT( mPrimaryKeyAttrs.size() == 1 );
whereClause = QStringLiteral( "%3%1=$%2" ).arg( quotedIdentifier( field( mPrimaryKeyAttrs[0] ).name() ) ).arg( offset ).arg( aliased );
break;
case PktFidMap:
{
QString delim;
for ( int i = 0; i < mPrimaryKeyAttrs.size(); i++ )
{
int idx = mPrimaryKeyAttrs[i];
QgsField fld = field( idx );
whereClause += delim + QStringLiteral( "%3%1=$%2" ).arg( connectionRO()->fieldExpression( fld ) ).arg( offset++ ).arg( aliased );
delim = QStringLiteral( " AND " );
}
}
break;
case PktUnknown:
Q_ASSERT( !"FAILURE: Primary key unknown" );
whereClause = QStringLiteral( "NULL" );
break;
}
if ( !mSqlWhereClause.isEmpty() )
{
if ( !whereClause.isEmpty() )
whereClause += QLatin1String( " AND " );
whereClause += '(' + mSqlWhereClause + ')';
}
return whereClause;
}
void QgsPostgresProvider::appendPkParams( QgsFeatureId featureId, QStringList &params ) const
{
switch ( mPrimaryKeyType )
{
case PktOid:
case PktUint64:
params << QString::number( featureId );
break;
case PktInt:
params << QString::number( FID2PKINT( featureId ) );
break;
case PktTid:
params << QStringLiteral( "'(%1,%2)'" ).arg( FID_TO_NUMBER( featureId ) >> 16 ).arg( FID_TO_NUMBER( featureId ) & 0xffff );
break;
case PktFidMap:
{
QVariantList pkVals = mShared->lookupKey( featureId );
if ( !pkVals.isEmpty() )
{
Q_ASSERT( pkVals.size() == mPrimaryKeyAttrs.size() );
}
for ( int i = 0; i < mPrimaryKeyAttrs.size(); i++ )
{
if ( i < pkVals.size() )
{
params << pkVals[i].toString();
}
else
{
QgsDebugMsg( QStringLiteral( "FAILURE: Key value %1 for feature %2 not found." ).arg( mPrimaryKeyAttrs[i] ).arg( featureId ) );
params << QStringLiteral( "NULL" );
}
}
QgsDebugMsg( QStringLiteral( "keys params: %1" ).arg( params.join( "; " ) ) );
}
break;
case PktUnknown:
Q_ASSERT( !"FAILURE: Primary key unknown" );
break;
}
}
QString QgsPostgresProvider::whereClause( QgsFeatureId featureId ) const
{
return QgsPostgresUtils::whereClause( featureId, mAttributeFields, connectionRO(), mPrimaryKeyType, mPrimaryKeyAttrs, mShared );
}
QString QgsPostgresUtils::whereClause( QgsFeatureId featureId, const QgsFields &fields, QgsPostgresConn *conn, QgsPostgresPrimaryKeyType pkType, const QList<int> &pkAttrs, const std::shared_ptr<QgsPostgresSharedData> &sharedData )
{
QString whereClause;
switch ( pkType )
{
case PktTid:
whereClause = QStringLiteral( "ctid='(%1,%2)'" )
.arg( FID_TO_NUMBER( featureId ) >> 16 )
.arg( FID_TO_NUMBER( featureId ) & 0xffff );
break;
case PktOid:
whereClause = QStringLiteral( "oid=%1" ).arg( featureId );
break;
case PktInt:
Q_ASSERT( pkAttrs.size() == 1 );
whereClause = QStringLiteral( "%1=%2" ).arg( QgsPostgresConn::quotedIdentifier( fields.at( pkAttrs[0] ).name() ) ).arg( FID2PKINT( featureId ) );
break;
case PktUint64:
Q_ASSERT( pkAttrs.size() == 1 );
whereClause = QStringLiteral( "%1=%2" ).arg( QgsPostgresConn::quotedIdentifier( fields.at( pkAttrs[0] ).name() ) ).arg( featureId );
break;
case PktFidMap:
{
QVariantList pkVals = sharedData->lookupKey( featureId );
if ( !pkVals.isEmpty() )
{
Q_ASSERT( pkVals.size() == pkAttrs.size() );
QString delim;
for ( int i = 0; i < pkAttrs.size(); i++ )
{
int idx = pkAttrs[i];
QgsField fld = fields.at( idx );
whereClause += delim + conn->fieldExpression( fld );
if ( pkVals[i].isNull() )
whereClause += QLatin1String( " IS NULL" );
else
whereClause += '=' + QgsPostgresConn::quotedValue( pkVals[i].toString() );
delim = QStringLiteral( " AND " );
}
}
else
{
QgsDebugMsg( QStringLiteral( "FAILURE: Key values for feature %1 not found." ).arg( featureId ) );
whereClause = QStringLiteral( "NULL" );
}
}
break;
case PktUnknown:
Q_ASSERT( !"FAILURE: Primary key unknown" );
whereClause = QStringLiteral( "NULL" );
break;
}
return whereClause;
}
QString QgsPostgresUtils::whereClause( const QgsFeatureIds &featureIds, const QgsFields &fields, QgsPostgresConn *conn, QgsPostgresPrimaryKeyType pkType, const QList<int> &pkAttrs, const std::shared_ptr<QgsPostgresSharedData> &sharedData )
{
switch ( pkType )
{
case PktOid:
case PktInt:
case PktUint64:
{
QString expr;
//simple primary key, so prefer to use an "IN (...)" query. These are much faster then multiple chained ...OR... clauses
if ( !featureIds.isEmpty() )
{
QString delim;
expr = QStringLiteral( "%1 IN (" ).arg( ( pkType == PktOid ? QStringLiteral( "oid" ) : QgsPostgresConn::quotedIdentifier( fields.at( pkAttrs[0] ).name() ) ) );
const auto constFeatureIds = featureIds;
for ( const QgsFeatureId featureId : constFeatureIds )
{
expr += delim + FID_TO_STRING( ( pkType == PktOid ? featureId : pkType == PktUint64 ? featureId : FID2PKINT( featureId ) ) );
delim = ',';
}
expr += ')';
}
return expr;
}
case PktFidMap:
case PktTid:
case PktUnknown:
{
//complex primary key, need to build up where string
QStringList whereClauses;
const auto constFeatureIds = featureIds;
for ( const QgsFeatureId featureId : constFeatureIds )
{
whereClauses << whereClause( featureId, fields, conn, pkType, pkAttrs, sharedData );
}
return whereClauses.isEmpty() ? QString() : whereClauses.join( QStringLiteral( " OR " ) ).prepend( '(' ).append( ')' );
}
}
return QString(); //avoid warning
}
QString QgsPostgresUtils::andWhereClauses( const QString &c1, const QString &c2 )
{
if ( c1.isEmpty() )
return c2;
if ( c2.isEmpty() )
return c1;
return QStringLiteral( "(%1) AND (%2)" ).arg( c1, c2 );
}
QString QgsPostgresProvider::filterWhereClause() const
{
QString where;
QString delim = QStringLiteral( " WHERE " );
if ( !mSqlWhereClause.isEmpty() )
{
where += delim + '(' + mSqlWhereClause + ')';
delim = QStringLiteral( " AND " );
}
if ( !mRequestedSrid.isEmpty() && ( mRequestedSrid != mDetectedSrid || mRequestedSrid.toInt() == 0 ) )
{
where += delim + QStringLiteral( "%1(%2%3)=%4" )
.arg( connectionRO()->majorVersion() < 2 ? "srid" : "st_srid",
quotedIdentifier( mGeometryColumn ),
mSpatialColType == SctGeography ? "::geography" : "",
mRequestedSrid );
delim = QStringLiteral( " AND " );
}
if ( mRequestedGeomType != QgsWkbTypes::Unknown && mRequestedGeomType != mDetectedGeomType )
{
where += delim + QgsPostgresConn::postgisTypeFilter( mGeometryColumn, ( QgsWkbTypes::Type )mRequestedGeomType, mSpatialColType == SctGeography );
delim = QStringLiteral( " AND " );
}
return where;
}
void QgsPostgresProvider::setExtent( QgsRectangle &newExtent )
{
mLayerExtent.setXMaximum( newExtent.xMaximum() );
mLayerExtent.setXMinimum( newExtent.xMinimum() );
mLayerExtent.setYMaximum( newExtent.yMaximum() );
mLayerExtent.setYMinimum( newExtent.yMinimum() );
}
/**
* Returns the feature type
*/
QgsWkbTypes::Type QgsPostgresProvider::wkbType() const
{
return mRequestedGeomType != QgsWkbTypes::Unknown ? mRequestedGeomType : mDetectedGeomType;
}
QgsLayerMetadata QgsPostgresProvider::layerMetadata() const
{
return mLayerMetadata;
}
QgsField QgsPostgresProvider::field( int index ) const
{
if ( index < 0 || index >= mAttributeFields.count() )
{
QgsMessageLog::logMessage( tr( "FAILURE: Field %1 not found." ).arg( index ), tr( "PostGIS" ) );
throw PGFieldNotFound();
}
return mAttributeFields.at( index );
}
QgsFields QgsPostgresProvider::fields() const
{
return mAttributeFields;
}
QString QgsPostgresProvider::dataComment() const
{
return mDataComment;
}
//! \todo XXX Perhaps this should be promoted to QgsDataProvider?
QString QgsPostgresProvider::endianString()
{
switch ( QgsApplication::endian() )
{
case QgsApplication::NDR:
return QStringLiteral( "NDR" );
case QgsApplication::XDR:
return QStringLiteral( "XDR" );
default :
return QStringLiteral( "Unknown" );
}
}
struct PGTypeInfo
{
QString typeName;
QString typeType;
QString typeElem;
int typeLen;
};
bool QgsPostgresProvider::loadFields()
{
// Clear cached information about enum values support
mShared->clearSupportsEnumValuesCache();
if ( !mIsQuery )
{
QgsDebugMsg( QStringLiteral( "Loading fields for table %1" ).arg( mTableName ) );
// Get the relation oid for use in later queries
QString sql = QStringLiteral( "SELECT regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
QgsPostgresResult tresult( connectionRO()->PQexec( sql ) );
QString tableoid = tresult.PQgetvalue( 0, 0 );
// Get the table description
sql = QStringLiteral( "SELECT description FROM pg_description WHERE objoid=%1 AND objsubid=0" ).arg( tableoid );
tresult = connectionRO()->PQexec( sql );
if ( tresult.PQntuples() > 0 )
{
mDataComment = tresult.PQgetvalue( 0, 0 );
mLayerMetadata.setAbstract( mDataComment );
}
}
// Populate the field vector for this layer. The field vector contains
// field name, type, length, and precision (if numeric)
QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 0" ).arg( mQuery );
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
// Collect type info
sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type" );
QgsPostgresResult typeResult( connectionRO()->PQexec( sql ) );
QMap<Oid, PGTypeInfo> typeMap;
for ( int i = 0; i < typeResult.PQntuples(); ++i )
{
PGTypeInfo typeInfo =
{
/* typeName = */ typeResult.PQgetvalue( i, 1 ),
/* typeType = */ typeResult.PQgetvalue( i, 2 ),
/* typeElem = */ typeResult.PQgetvalue( i, 3 ),
/* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
};
typeMap.insert( typeResult.PQgetvalue( i, 0 ).toUInt(), typeInfo );
}
QMap<Oid, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap, identityMap;
QMap<Oid, QMap<int, Oid> > attTypeIdMap;
QMap<Oid, QMap<int, bool> > notNullMap, uniqueMap;
if ( result.PQnfields() > 0 )
{
// Collect table oids
QSet<Oid> tableoids;
for ( int i = 0; i < result.PQnfields(); i++ )
{
Oid tableoid = result.PQftable( i );
if ( tableoid > 0 )
{
tableoids.insert( tableoid );
}
}
if ( !tableoids.isEmpty() )
{
QStringList tableoidsList;
const auto constTableoids = tableoids;
for ( Oid tableoid : constTableoids )
{
tableoidsList.append( QString::number( tableoid ) );
}
QString tableoidsFilter = '(' + tableoidsList.join( QStringLiteral( "," ) ) + ')';
// Collect formatted field types
sql = QStringLiteral(
"SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid, attnotnull::int, indisunique::int%1"
" FROM pg_attribute"
" LEFT OUTER JOIN pg_attrdef ON attrelid=adrelid AND attnum=adnum"
// find unique constraints if present. Text cast required to handle int2vector comparison. Distinct required as multiple unique constraints may exist
" LEFT OUTER JOIN ( SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique ) uniq ON attrelid=indrelid AND attnum::text=indkey::text "
" WHERE attrelid IN %2"
).arg( connectionRO()->pgVersion() >= 100000 ? QStringLiteral( ", attidentity" ) : QString() ).arg( tableoidsFilter );
QgsPostgresResult fmtFieldTypeResult( connectionRO()->PQexec( sql ) );
for ( int i = 0; i < fmtFieldTypeResult.PQntuples(); ++i )
{
Oid attrelid = fmtFieldTypeResult.PQgetvalue( i, 0 ).toUInt();
int attnum = fmtFieldTypeResult.PQgetvalue( i, 1 ).toInt(); // Int2
QString formatType = fmtFieldTypeResult.PQgetvalue( i, 2 );
QString descr = fmtFieldTypeResult.PQgetvalue( i, 3 );
QString defVal = fmtFieldTypeResult.PQgetvalue( i, 4 );
Oid attType = fmtFieldTypeResult.PQgetvalue( i, 5 ).toUInt();
bool attNotNull = fmtFieldTypeResult.PQgetvalue( i, 6 ).toInt();
bool uniqueConstraint = fmtFieldTypeResult.PQgetvalue( i, 7 ).toInt();
QString attIdentity = connectionRO()->pgVersion() >= 100000 ? fmtFieldTypeResult.PQgetvalue( i, 8 ) : " ";
fmtFieldTypeMap[attrelid][attnum] = formatType;
descrMap[attrelid][attnum] = descr;
defValMap[attrelid][attnum] = defVal;
attTypeIdMap[attrelid][attnum] = attType;
notNullMap[attrelid][attnum] = attNotNull;
uniqueMap[attrelid][attnum] = uniqueConstraint;
identityMap[attrelid][attnum] = attIdentity.isEmpty() ? " " : attIdentity;
}
}
}
QSet<QString> fields;
mAttributeFields.clear();
mIdentityFields.clear();
for ( int i = 0; i < result.PQnfields(); i++ )
{
QString fieldName = result.PQfname( i );
if ( fieldName == mGeometryColumn )
continue;
Oid fldtyp = result.PQftype( i );
int fldMod = result.PQfmod( i );
int fieldPrec = -1;
Oid tableoid = result.PQftable( i );
int attnum = result.PQftablecol( i );
Oid atttypid = attTypeIdMap[tableoid][attnum];
const PGTypeInfo &typeInfo = typeMap.value( fldtyp );
QString fieldTypeName = typeInfo.typeName;
QString fieldTType = typeInfo.typeType;
int fieldSize = typeInfo.typeLen;
bool isDomain = ( typeMap.value( atttypid ).typeType == QLatin1String( "d" ) );
QString formattedFieldType = fmtFieldTypeMap[tableoid][attnum];
QString originalFormattedFieldType = formattedFieldType;
if ( isDomain )
{
// get correct formatted field type for domain
sql = QStringLiteral( "SELECT format_type(%1, %2)" ).arg( fldtyp ).arg( fldMod );
QgsPostgresResult fmtFieldModResult( connectionRO()->PQexec( sql ) );
if ( fmtFieldModResult.PQntuples() > 0 )
{
formattedFieldType = fmtFieldModResult.PQgetvalue( 0, 0 );
}
}
QString fieldComment = descrMap[tableoid][attnum];
QVariant::Type fieldType;
QVariant::Type fieldSubType = QVariant::Invalid;
if ( fieldTType == QLatin1String( "b" ) )
{
bool isArray = fieldTypeName.startsWith( '_' );
if ( isArray )
fieldTypeName = fieldTypeName.mid( 1 );
if ( fieldTypeName == QLatin1String( "int8" ) || fieldTypeName == QLatin1String( "serial8" ) )
{
fieldType = QVariant::LongLong;
fieldSize = -1;
fieldPrec = 0;
}
else if ( fieldTypeName == QLatin1String( "int2" ) || fieldTypeName == QLatin1String( "int4" ) ||
fieldTypeName == QLatin1String( "oid" ) || fieldTypeName == QLatin1String( "serial" ) )
{
fieldType = QVariant::Int;
fieldSize = -1;
fieldPrec = 0;
}
else if ( fieldTypeName == QLatin1String( "real" ) || fieldTypeName == QLatin1String( "double precision" ) ||
fieldTypeName == QLatin1String( "float4" ) || fieldTypeName == QLatin1String( "float8" ) )
{
fieldType = QVariant::Double;
fieldSize = -1;
fieldPrec = -1;
}
else if ( fieldTypeName == QLatin1String( "numeric" ) )
{
fieldType = QVariant::Double;
if ( formattedFieldType == QLatin1String( "numeric" ) || formattedFieldType.isEmpty() )
{
fieldSize = -1;
fieldPrec = -1;
}
else
{
QRegExp re( "numeric\\((\\d+),(\\d+)\\)" );
if ( re.exactMatch( formattedFieldType ) )
{
fieldSize = re.cap( 1 ).toInt();
fieldPrec = re.cap( 2 ).toInt();
}
else if ( formattedFieldType != QLatin1String( "numeric" ) )
{
QgsMessageLog::logMessage( tr( "Unexpected formatted field type '%1' for field %2" )
.arg( formattedFieldType,
fieldName ),
tr( "PostGIS" ) );
fieldSize = -1;
fieldPrec = -1;
}
}
}
else if ( fieldTypeName == QLatin1String( "varchar" ) )
{
fieldType = QVariant::String;
QRegExp re( "character varying\\((\\d+)\\)" );
if ( re.exactMatch( formattedFieldType ) )
{
fieldSize = re.cap( 1 ).toInt();
}
else
{
fieldSize = -1;
}
}
else if ( fieldTypeName == QLatin1String( "date" ) )
{
fieldType = QVariant::Date;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "time" ) )
{
fieldType = QVariant::Time;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "timestamp" ) )
{
fieldType = QVariant::DateTime;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "bytea" ) )
{
fieldType = QVariant::ByteArray;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "text" ) ||
fieldTypeName == QLatin1String( "citext" ) ||
fieldTypeName == QLatin1String( "geometry" ) ||
fieldTypeName == QLatin1String( "inet" ) ||
fieldTypeName == QLatin1String( "money" ) ||
fieldTypeName == QLatin1String( "ltree" ) ||
fieldTypeName == QLatin1String( "uuid" ) ||
fieldTypeName == QLatin1String( "xml" ) ||
fieldTypeName.startsWith( QLatin1String( "time" ) ) ||
fieldTypeName.startsWith( QLatin1String( "date" ) ) )
{
fieldType = QVariant::String;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "bpchar" ) )
{
// although postgres internally uses "bpchar", this is exposed to users as character in postgres
fieldTypeName = QStringLiteral( "character" );
fieldType = QVariant::String;
QRegExp re( "character\\((\\d+)\\)" );
if ( re.exactMatch( formattedFieldType ) )
{
fieldSize = re.cap( 1 ).toInt();
}
else
{
QgsDebugMsg( QStringLiteral( "Unexpected formatted field type '%1' for field %2" )
.arg( formattedFieldType,
fieldName ) );
fieldSize = -1;
fieldPrec = -1;
}
}
else if ( fieldTypeName == QLatin1String( "char" ) )
{
fieldType = QVariant::String;
QRegExp re( "char\\((\\d+)\\)" );
if ( re.exactMatch( formattedFieldType ) )
{
fieldSize = re.cap( 1 ).toInt();
}
else
{
QgsMessageLog::logMessage( tr( "Unexpected formatted field type '%1' for field %2" )
.arg( formattedFieldType,
fieldName ) );
fieldSize = -1;
fieldPrec = -1;
}
}
else if ( fieldTypeName == QLatin1String( "hstore" ) || fieldTypeName == QLatin1String( "json" ) || fieldTypeName == QLatin1String( "jsonb" ) )
{
fieldType = QVariant::Map;
fieldSubType = QVariant::String;
fieldSize = -1;
}
else if ( fieldTypeName == QLatin1String( "bool" ) )
{
// enum
fieldType = QVariant::Bool;
fieldSize = -1;
}
else
{
// be tolerant in case of views: this might be a field used as a key
const QgsPostgresProvider::Relkind type = relkind();
if ( ( type == Relkind::View || type == Relkind::MaterializedView ) && parseUriKey( mUri.keyColumn( ) ).contains( fieldName ) )
{
// Assume it is convertible to text
fieldType = QVariant::String;
fieldSize = -1;
}
else
{
QgsMessageLog::logMessage( tr( "Field %1 ignored, because of unsupported type %2" ).arg( fieldName, fieldTType ), tr( "PostGIS" ) );
continue;
}
}
if ( isArray )
{
fieldTypeName = '_' + fieldTypeName;
fieldSubType = fieldType;
fieldType = ( fieldType == QVariant::String ? QVariant::StringList : QVariant::List );
fieldSize = -1;
}
}
else if ( fieldTType == QLatin1String( "e" ) )
{
// enum
fieldType = QVariant::String;
fieldSize = -1;
}
else
{
QgsMessageLog::logMessage( tr( "Field %1 ignored, because of unsupported type %2" ).arg( fieldName, fieldTType ), tr( "PostGIS" ) );
continue;
}
if ( fields.contains( fieldName ) )
{
QgsMessageLog::logMessage( tr( "Duplicate field %1 found\n" ).arg( fieldName ), tr( "PostGIS" ) );
return false;
}
fields << fieldName;
if ( isDomain )
{
//field was defined using domain, so use domain type name for fieldTypeName
fieldTypeName = originalFormattedFieldType;
}
mAttrPalIndexName.insert( i, fieldName );
mDefaultValues.insert( mAttributeFields.size(), defValMap[tableoid][attnum] );
QgsField newField = QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldPrec, fieldComment, fieldSubType );
QgsFieldConstraints constraints;
if ( notNullMap[tableoid][attnum] || ( mPrimaryKeyAttrs.size() == 1 && mPrimaryKeyAttrs[0] == i ) || identityMap[tableoid][attnum] != ' ' )
constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
if ( uniqueMap[tableoid][attnum] || ( mPrimaryKeyAttrs.size() == 1 && mPrimaryKeyAttrs[0] == i ) || identityMap[tableoid][attnum] != ' ' )
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
newField.setConstraints( constraints );
mIdentityFields.insert( mAttributeFields.size(), identityMap[tableoid][attnum][0].toLatin1() );
mAttributeFields.append( newField );
}
setEditorWidgets();
return true;
}
void QgsPostgresProvider::setEditorWidgets()
{
if ( ! tableExists( *connectionRO(), EDITOR_WIDGET_STYLES_TABLE ) )
{
return;
}
QStringList quotedFnames;
const QStringList fieldNames = mAttributeFields.names();
for ( const QString &name : fieldNames )
{
quotedFnames << quotedValue( name );
}
// We expect the table to be created like this:
//
// CREATE TABLE qgis_editor_widget_styles (schema_name TEXT NOT NULL, table_name TEXT NOT NULL, field_name TEXT NOT NULL,
// type TEXT NOT NULL, config TEXT,
// PRIMARY KEY(schema_name, table_name, field_name));
const QString sql = QStringLiteral( "SELECT field_name, type, config "
"FROM %1 WHERE schema_name = %2 "
"AND table_name = %3 "
"AND field_name IN ( %4 )" ) .
arg( EDITOR_WIDGET_STYLES_TABLE, quotedValue( mSchemaName ),
quotedValue( mTableName ), quotedFnames.join( "," ) );
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
for ( int i = 0; i < result.PQntuples(); ++i )
{
if ( result.PQgetisnull( i, 2 ) ) continue; // config can be null and it's OK
const QString &configTxt = result.PQgetvalue( i, 2 );
const QString &type = result.PQgetvalue( i, 1 );
const QString &fname = result.PQgetvalue( i, 0 );
QVariantMap config;
QDomDocument doc;
if ( doc.setContent( configTxt ) )
{
config = QgsXmlUtils::readVariant( doc.documentElement() ).toMap();
}
else
{
QgsMessageLog::logMessage(
tr( "Cannot parse widget configuration for field %1.%2.%3\n" )
.arg( mSchemaName, mTableName, fname ), tr( "PostGIS" )
);
continue;
}
// Set corresponding editor widget
for ( auto &field : mAttributeFields )
{
if ( field.name() == fname )
{
field.setEditorWidgetSetup( QgsEditorWidgetSetup( type, config ) );
break;
}
}
}
}
bool QgsPostgresProvider::hasSufficientPermsAndCapabilities()
{
QgsDebugMsg( QStringLiteral( "Checking for permissions on the relation" ) );
QgsPostgresResult testAccess;
if ( !mIsQuery )
{
// Check that we can read from the table (i.e., we have select permission).
QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 1" ).arg( mQuery );
QgsPostgresResult testAccess( connectionRO()->PQexec( sql ) );
if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Unable to access the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
.arg( mQuery,
testAccess.PQresultErrorMessage(),
sql ), tr( "PostGIS" ) );
return false;
}
bool inRecovery = false;
if ( connectionRO()->pgVersion() >= 90000 )
{
testAccess = connectionRO()->PQexec( QStringLiteral( "SELECT pg_is_in_recovery()" ) );
if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK || testAccess.PQgetvalue( 0, 0 ) == QLatin1String( "t" ) )
{
QgsMessageLog::logMessage( tr( "PostgreSQL is still in recovery after a database crash\n(or you are connected to a (read-only) slave).\nWrite accesses will be denied." ), tr( "PostGIS" ) );
inRecovery = true;
}
}
// postgres has fast access to features at id (thanks to primary key / unique index)
// the latter flag is here just for compatibility
if ( !mSelectAtIdDisabled )
{
mEnabledCapabilities = QgsVectorDataProvider::SelectAtId;
}
if ( !inRecovery )
{
if ( connectionRO()->pgVersion() >= 80400 )
{
sql = QString( "SELECT "
"has_table_privilege(%1,'DELETE'),"
"has_any_column_privilege(%1,'UPDATE'),"
"%2"
"has_table_privilege(%1,'INSERT'),"
"current_schema()" )
.arg( quotedValue( mQuery ),
mGeometryColumn.isNull()
? QStringLiteral( "'f'," )
: QStringLiteral( "has_column_privilege(%1,%2,'UPDATE')," )
.arg( quotedValue( mQuery ),
quotedValue( mGeometryColumn ) )
);
}
else
{
sql = QString( "SELECT "
"has_table_privilege(%1,'DELETE'),"
"has_table_privilege(%1,'UPDATE'),"
"has_table_privilege(%1,'UPDATE'),"
"has_table_privilege(%1,'INSERT'),"
"current_schema()" )
.arg( quotedValue( mQuery ) );
}
testAccess = connectionRO()->PQexec( sql );
if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Unable to determine table access privileges for the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
.arg( mQuery,
testAccess.PQresultErrorMessage(),
sql ),
tr( "PostGIS" ) );
return false;
}
if ( testAccess.PQgetvalue( 0, 0 ) == QLatin1String( "t" ) )
{
// DELETE
mEnabledCapabilities |= QgsVectorDataProvider::DeleteFeatures | QgsVectorDataProvider::FastTruncate;
}
if ( testAccess.PQgetvalue( 0, 1 ) == QLatin1String( "t" ) )
{
// UPDATE
mEnabledCapabilities |= QgsVectorDataProvider::ChangeAttributeValues;
}
if ( testAccess.PQgetvalue( 0, 2 ) == QLatin1String( "t" ) )
{
// UPDATE
mEnabledCapabilities |= QgsVectorDataProvider::ChangeGeometries;
}
if ( testAccess.PQgetvalue( 0, 3 ) == QLatin1String( "t" ) )
{
// INSERT
mEnabledCapabilities |= QgsVectorDataProvider::AddFeatures;
}
if ( mSchemaName.isEmpty() )
mSchemaName = testAccess.PQgetvalue( 0, 4 );
sql = QString( "SELECT 1 FROM pg_class,pg_namespace WHERE "
"pg_class.relnamespace=pg_namespace.oid AND "
"%3 AND "
"relname=%1 AND nspname=%2" )
.arg( quotedValue( mTableName ),
quotedValue( mSchemaName ),
connectionRO()->pgVersion() < 80100 ? "pg_get_userbyid(relowner)=current_user" : "pg_has_role(relowner,'MEMBER')" );
testAccess = connectionRO()->PQexec( sql );
if ( testAccess.PQresultStatus() == PGRES_TUPLES_OK && testAccess.PQntuples() == 1 )
{
mEnabledCapabilities |= QgsVectorDataProvider::AddAttributes | QgsVectorDataProvider::DeleteAttributes | QgsVectorDataProvider::RenameAttributes;
}
}
}
else
{
// Check if the sql is a select query
if ( !mQuery.startsWith( '(' ) && !mQuery.endsWith( ')' ) )
{
QgsMessageLog::logMessage( tr( "The custom query is not a select query." ), tr( "PostGIS" ) );
return false;
}
// get a new alias for the subquery
int index = 0;
QString alias;
QRegExp regex;
do
{
alias = QStringLiteral( "subQuery_%1" ).arg( QString::number( index++ ) );
QString pattern = QStringLiteral( "(\\\"?)%1\\1" ).arg( QRegExp::escape( alias ) );
regex.setPattern( pattern );
regex.setCaseSensitivity( Qt::CaseInsensitive );
}
while ( mQuery.contains( regex ) );
// convert the custom query into a subquery
mQuery = QStringLiteral( "%1 AS %2" )
.arg( mQuery,
quotedIdentifier( alias ) );
QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 1" ).arg( mQuery );
testAccess = connectionRO()->PQexec( sql );
if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Unable to execute the query.\nThe error message from the database was:\n%1.\nSQL: %2" )
.arg( testAccess.PQresultErrorMessage(),
sql ), tr( "PostGIS" ) );
return false;
}
if ( !mSelectAtIdDisabled )
{
mEnabledCapabilities = QgsVectorDataProvider::SelectAtId;
}
}
// supports geometry simplification on provider side
mEnabledCapabilities |= ( QgsVectorDataProvider::SimplifyGeometries | QgsVectorDataProvider::SimplifyGeometriesWithTopologicalValidation );
//supports transactions
mEnabledCapabilities |= QgsVectorDataProvider::TransactionSupport;
// supports circular geometries
mEnabledCapabilities |= QgsVectorDataProvider::CircularGeometries;
// supports layer metadata
mEnabledCapabilities |= QgsVectorDataProvider::ReadLayerMetadata;
if ( ( mEnabledCapabilities & QgsVectorDataProvider::ChangeGeometries ) &&
( mEnabledCapabilities & QgsVectorDataProvider::ChangeAttributeValues ) &&
mSpatialColType != SctTopoGeometry )
{
mEnabledCapabilities |= QgsVectorDataProvider::ChangeFeatures;
}
return true;
}
bool QgsPostgresProvider::determinePrimaryKey()
{
if ( !loadFields() )
{
return false;
}
// check to see if there is an unique index on the relation, which
// can be used as a key into the table. Primary keys are always
// unique indices, so we catch them as well.
QString sql;
if ( !mIsQuery )
{
sql = QStringLiteral( "SELECT count(*) FROM pg_inherits WHERE inhparent=%1::regclass" ).arg( quotedValue( mQuery ) );
QgsDebugMsg( QStringLiteral( "Checking whether %1 is a parent table" ).arg( sql ) );
QgsPostgresResult res( connectionRO()->PQexec( sql ) );
bool isParentTable( res.PQntuples() == 0 || res.PQgetvalue( 0, 0 ).toInt() > 0 );
sql = QStringLiteral( "SELECT indexrelid FROM pg_index WHERE indrelid=%1::regclass AND (indisprimary OR indisunique) ORDER BY CASE WHEN indisprimary THEN 1 ELSE 2 END LIMIT 1" ).arg( quotedValue( mQuery ) );
QgsDebugMsg( QStringLiteral( "Retrieving first primary or unique index: %1" ).arg( sql ) );
res = connectionRO()->PQexec( sql );
QgsDebugMsg( QStringLiteral( "Got %1 rows." ).arg( res.PQntuples() ) );
QStringList log;
// no primary or unique indices found
if ( res.PQntuples() == 0 )
{
QgsDebugMsg( QStringLiteral( "Relation has no primary key -- investigating alternatives" ) );
// Two options here. If the relation is a table, see if there is
// an oid column that can be used instead.
// If the relation is a view try to find a suitable column to use as
// the primary key.
const QgsPostgresProvider::Relkind type = relkind();
if ( type == Relkind::OrdinaryTable || type == Relkind::PartitionedTable )
{
QgsDebugMsg( QStringLiteral( "Relation is a table. Checking to see if it has an oid column." ) );
mPrimaryKeyAttrs.clear();
mPrimaryKeyType = PktUnknown;
if ( connectionRO()->pgVersion() >= 100000 )
{
// If there is an generated id on the table, use that instead,
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attidentity IN ('a','d') AND attrelid=regclass(%1) LIMIT 1" ).arg( quotedValue( mQuery ) );
res = connectionRO()->PQexec( sql );
if ( res.PQntuples() == 1 )
{
// Could warn the user here that performance will suffer if
// attribute isn't indexed (and that they may want to add a
// primary key to the table)
int idx = fieldNameIndex( res.PQgetvalue( 0, 0 ) );
mPrimaryKeyType = pkType( mAttributeFields.at( idx ) );
mPrimaryKeyAttrs << idx;
}
}
if ( mPrimaryKeyType == PktUnknown )
{
// If there is an oid on the table, use that instead,
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );
res = connectionRO()->PQexec( sql );
if ( res.PQntuples() == 1 )
{
// Could warn the user here that performance will suffer if
// oid isn't indexed (and that they may want to add a
// primary key to the table)
mPrimaryKeyType = PktOid;
}
}
if ( mPrimaryKeyType == PktUnknown )
{
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='ctid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );
res = connectionRO()->PQexec( sql );
if ( res.PQntuples() == 1 )
{
mPrimaryKeyType = PktTid;
QgsMessageLog::logMessage( tr( "Primary key is ctid - changing of existing features disabled (%1; %2)" ).arg( mGeometryColumn, mQuery ) );
mEnabledCapabilities &= ~( QgsVectorDataProvider::DeleteFeatures | QgsVectorDataProvider::ChangeAttributeValues | QgsVectorDataProvider::ChangeGeometries | QgsVectorDataProvider::ChangeFeatures );
}
}
if ( mPrimaryKeyType == PktUnknown )
{
QgsMessageLog::logMessage( tr( "The table has no column suitable for use as a key. QGIS requires a primary key, a PostgreSQL oid column or a ctid for tables." ), tr( "PostGIS" ) );
}
}
else if ( type == Relkind::View || type == Relkind::MaterializedView || type == Relkind::ForeignTable )
{
determinePrimaryKeyFromUriKeyColumn();
}
else
{
const QMetaEnum metaEnum( QMetaEnum::fromType<Relkind>() );
QString typeName = metaEnum.valueToKey( type );
QgsMessageLog::logMessage( tr( "Unexpected relation type '%1'." ).arg( typeName ), tr( "PostGIS" ) );
}
}
else
{
// have a primary key or unique index
QString indrelid = res.PQgetvalue( 0, 0 );
sql = QStringLiteral( "SELECT attname,attnotnull FROM pg_index,pg_attribute WHERE indexrelid=%1 AND indrelid=attrelid AND pg_attribute.attnum=any(pg_index.indkey)" ).arg( indrelid );
QgsDebugMsg( "Retrieving key columns: " + sql );
res = connectionRO()->PQexec( sql );
QgsDebugMsg( QStringLiteral( "Got %1 rows." ).arg( res.PQntuples() ) );
bool mightBeNull = false;
QString primaryKey;
QString delim;
mPrimaryKeyType = PktFidMap; // map by default, will downgrade if needed
for ( int i = 0; i < res.PQntuples(); i++ )
{
QString name = res.PQgetvalue( i, 0 );
if ( res.PQgetvalue( i, 1 ).startsWith( 'f' ) )
{
QgsMessageLog::logMessage( tr( "Unique column '%1' doesn't have a NOT NULL constraint." ).arg( name ), tr( "PostGIS" ) );
mightBeNull = true;
}
primaryKey += delim + quotedIdentifier( name );
delim = ',';
int idx = fieldNameIndex( name );
if ( idx == -1 )
{
QgsDebugMsg( "Skipping " + name );
continue;
}
QgsField fld = mAttributeFields.at( idx );
// Always use PktFidMap for multi-field keys
mPrimaryKeyType = i ? PktFidMap : pkType( fld );
mPrimaryKeyAttrs << idx;
}
if ( ( mightBeNull || isParentTable ) && !mUseEstimatedMetadata && !uniqueData( primaryKey ) )
{
QgsMessageLog::logMessage( tr( "Ignoring key candidate because of NULL values or inheritance" ), tr( "PostGIS" ) );
mPrimaryKeyType = PktUnknown;
mPrimaryKeyAttrs.clear();
}
}
}
else
{
determinePrimaryKeyFromUriKeyColumn();
}
if ( mPrimaryKeyAttrs.size() == 1 )
{
//primary keys are unique, not null
QgsFieldConstraints constraints = mAttributeFields.at( mPrimaryKeyAttrs[0] ).constraints();
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
mAttributeFields[ mPrimaryKeyAttrs[0] ].setConstraints( constraints );
}
mValid = mPrimaryKeyType != PktUnknown;
return mValid;
}
/* static */
QStringList QgsPostgresProvider::parseUriKey( const QString &key )
{
if ( key.isEmpty() ) return QStringList();
QStringList cols;
// remove quotes from key list
if ( key.startsWith( '"' ) && key.endsWith( '"' ) )
{
int i = 1;
QString col;
while ( i < key.size() )
{
if ( key[i] == '"' )
{
if ( i + 1 < key.size() && key[i + 1] == '"' )
{
i++;
}
else
{
cols << col;
col.clear();
if ( ++i == key.size() )
break;
Q_ASSERT( key[i] == ',' );
i++;
Q_ASSERT( key[i] == '"' );
i++;
col.clear();
continue;
}
}
col += key[i++];
}
}
else if ( key.contains( ',' ) )
{
cols = key.split( ',' );
}
else
{
cols << key;
}
return cols;
}
void QgsPostgresProvider::determinePrimaryKeyFromUriKeyColumn()
{
QString primaryKey = mUri.keyColumn();
mPrimaryKeyType = PktUnknown;
if ( !primaryKey.isEmpty() )
{
const QStringList cols = parseUriKey( primaryKey );
primaryKey.clear();
QString del;
for ( const QString &col : cols )
{
primaryKey += del + quotedIdentifier( col );
del = QStringLiteral( "," );
}
for ( const QString &col : cols )
{
int idx = fieldNameIndex( col );
if ( idx < 0 )
{
QgsMessageLog::logMessage( tr( "Key field '%1' for view/query not found." ).arg( col ), tr( "PostGIS" ) );
mPrimaryKeyAttrs.clear();
break;
}
mPrimaryKeyAttrs << idx;
}
if ( !mPrimaryKeyAttrs.isEmpty() )
{
bool unique = true;
if ( mCheckPrimaryKeyUnicity )
{
unique = uniqueData( primaryKey );
}
if ( mUseEstimatedMetadata || unique )
{
mPrimaryKeyType = PktFidMap; // Map by default
if ( mPrimaryKeyAttrs.size() == 1 )
{
QgsField fld = mAttributeFields.at( 0 );
mPrimaryKeyType = pkType( fld );
}
}
else
{
QgsMessageLog::logMessage( tr( "Primary key field '%1' for view/query not unique." ).arg( primaryKey ), tr( "PostGIS" ) );
}
}
else
{
QgsMessageLog::logMessage( tr( "Keys for view/query undefined." ), tr( "PostGIS" ) );
}
}
else
{
QgsMessageLog::logMessage( tr( "No key field for view/query given." ), tr( "PostGIS" ) );
}
}
bool QgsPostgresProvider::uniqueData( const QString &quotedColNames )
{
// Check to see if the given columns contain unique data
QString sql = QStringLiteral( "SELECT count(distinct (%1))=count((%1)) FROM %2%3" )
.arg( quotedColNames,
mQuery,
filterWhereClause() );
QgsPostgresResult unique( connectionRO()->PQexec( sql ) );
if ( unique.PQresultStatus() != PGRES_TUPLES_OK )
{
pushError( unique.PQresultErrorMessage() );
return false;
}
return unique.PQntuples() == 1 && unique.PQgetvalue( 0, 0 ).startsWith( 't' );
}
// Returns the minimum value of an attribute
QVariant QgsPostgresProvider::minimumValue( int index ) const
{
try
{
// get the field name
QgsField fld = field( index );
QString sql = QStringLiteral( "SELECT min(%1) AS %1 FROM %2" )
.arg( quotedIdentifier( fld.name() ),
mQuery );
if ( !mSqlWhereClause.isEmpty() )
{
sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
}
sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );
QgsPostgresResult rmin( connectionRO()->PQexec( sql ) );
return convertValue( fld.type(), fld.subType(), rmin.PQgetvalue( 0, 0 ), fld.typeName() );
}
catch ( PGFieldNotFound )
{
return QVariant( QString() );
}
}
// Returns the list of unique values of an attribute
QSet<QVariant> QgsPostgresProvider::uniqueValues( int index, int limit ) const
{
QSet<QVariant> uniqueValues;
try
{
// get the field name
QgsField fld = field( index );
QString sql = QStringLiteral( "SELECT DISTINCT %1 FROM %2" )
.arg( quotedIdentifier( fld.name() ),
mQuery );
if ( !mSqlWhereClause.isEmpty() )
{
sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
}
sql += QStringLiteral( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );
if ( limit >= 0 )
{
sql += QStringLiteral( " LIMIT %1" ).arg( limit );
}
sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );
QgsPostgresResult res( connectionRO()->PQexec( sql ) );
if ( res.PQresultStatus() == PGRES_TUPLES_OK )
{
for ( int i = 0; i < res.PQntuples(); i++ )
uniqueValues.insert( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ), fld.typeName() ) );
}
}
catch ( PGFieldNotFound )
{
}
return uniqueValues;
}
QStringList QgsPostgresProvider::uniqueStringsMatching( int index, const QString &substring, int limit, QgsFeedback *feedback ) const
{
QStringList results;
try
{
// get the field name
QgsField fld = field( index );
QString sql = QStringLiteral( "SELECT DISTINCT %1 FROM %2 WHERE" )
.arg( quotedIdentifier( fld.name() ),
mQuery );
if ( !mSqlWhereClause.isEmpty() )
{
sql += QStringLiteral( " ( %1 ) AND " ).arg( mSqlWhereClause );
}
sql += QStringLiteral( " %1::text ILIKE '%%2%'" ).arg( quotedIdentifier( fld.name() ), substring );
sql += QStringLiteral( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );
if ( limit >= 0 )
{
sql += QStringLiteral( " LIMIT %1" ).arg( limit );
}
sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );
QgsPostgresResult res( connectionRO()->PQexec( sql ) );
if ( res.PQresultStatus() == PGRES_TUPLES_OK )
{
for ( int i = 0; i < res.PQntuples(); i++ )
{
results << ( convertValue( fld.type(), fld.subType(), res.PQgetvalue( i, 0 ), fld.typeName() ) ).toString();
if ( feedback && feedback->isCanceled() )
break;
}
}
}
catch ( PGFieldNotFound )
{
}
return results;
}
void QgsPostgresProvider::enumValues( int index, QStringList &enumList ) const
{
if ( index < 0 || index >= mAttributeFields.count() )
return;
if ( ! mShared->fieldSupportsEnumValuesIsSet( index ) )
{
mShared->setFieldSupportsEnumValues( index, true );
}
else if ( ! mShared->fieldSupportsEnumValues( index ) )
{
return;
}
//find out type of index
const QString fieldName = mAttributeFields.at( index ).name();
QString typeName = mAttributeFields.at( index ).typeName();
// Remove schema extension from typeName
typeName.remove( QRegularExpression( "^([^.]+\\.)+" ) );
//is type an enum?
const QString typeSql = QStringLiteral( "SELECT typtype FROM pg_type WHERE typname=%1" ).arg( quotedValue( typeName ) );
QgsPostgresResult typeRes( connectionRO()->PQexec( typeSql ) );
if ( typeRes.PQresultStatus() != PGRES_TUPLES_OK || typeRes.PQntuples() < 1 )
{
mShared->setFieldSupportsEnumValues( index, false );
return;
}
const QString typtype = typeRes.PQgetvalue( 0, 0 );
if ( typtype.compare( QLatin1String( "e" ), Qt::CaseInsensitive ) == 0 )
{
//try to read enum_range of attribute
if ( !parseEnumRange( enumList, fieldName ) )
{
mShared->setFieldSupportsEnumValues( index, false );
}
}
else
{
//is there a domain check constraint for the attribute?
if ( !parseDomainCheckConstraint( enumList, fieldName ) )
{
mShared->setFieldSupportsEnumValues( index, false );
}
}
}
bool QgsPostgresProvider::parseEnumRange( QStringList &enumValues, const QString &attributeName ) const
{
enumValues.clear();
QString enumRangeSql = QStringLiteral( "SELECT enumlabel FROM pg_catalog.pg_enum WHERE enumtypid=(SELECT atttypid::regclass FROM pg_attribute WHERE attrelid=%1::regclass AND attname=%2)" )
.arg( quotedValue( mQuery ),
quotedValue( attributeName ) );
QgsPostgresResult enumRangeRes( connectionRO()->PQexec( enumRangeSql ) );
if ( enumRangeRes.PQresultStatus() != PGRES_TUPLES_OK )
return false;
for ( int i = 0; i < enumRangeRes.PQntuples(); i++ )
{
enumValues << enumRangeRes.PQgetvalue( i, 0 );
}
return true;
}
bool QgsPostgresProvider::parseDomainCheckConstraint( QStringList &enumValues, const QString &attributeName ) const
{
enumValues.clear();
//is it a domain type with a check constraint?
QString domainSql = QStringLiteral( "SELECT domain_name, domain_schema FROM information_schema.columns WHERE table_name=%1 AND column_name=%2" ).arg( quotedValue( mTableName ), quotedValue( attributeName ) );
QgsPostgresResult domainResult( connectionRO()->PQexec( domainSql ) );
if ( domainResult.PQresultStatus() == PGRES_TUPLES_OK && domainResult.PQntuples() > 0 && !domainResult.PQgetvalue( 0, 0 ).isNull() )
{
QString domainCheckDefinitionSql;
if ( connectionRO()->pgVersion() < 120000 )
{
domainCheckDefinitionSql = QStringLiteral( ""
"SELECT consrc FROM pg_constraint "
" WHERE contypid =("
" SELECT oid FROM pg_type "
" WHERE typname = %1 "
" AND typnamespace =("
" SELECT oid FROM pg_namespace WHERE nspname = %2"
" )"
" )" )
.arg( quotedValue( domainResult.PQgetvalue( 0, 0 ) ) )
.arg( quotedValue( domainResult.PQgetvalue( 0, 1 ) ) );
}
else
{
domainCheckDefinitionSql = QStringLiteral( ""
"SELECT pg_catalog.pg_get_constraintdef( ( "
" SELECT oid FROM pg_constraint WHERE contypid = ( "
" SELECT oid FROM pg_type "
" WHERE typname = %1 "
" AND typnamespace =("
" SELECT oid FROM pg_namespace WHERE nspname = %2"
" )"
" )"
" ), true );" )
.arg( quotedValue( domainResult.PQgetvalue( 0, 0 ) ) )
.arg( quotedValue( domainResult.PQgetvalue( 0, 1 ) ) );
}
QgsPostgresResult domainCheckRes( connectionRO()->PQexec( domainCheckDefinitionSql ) );
if ( domainCheckRes.PQresultStatus() == PGRES_TUPLES_OK && domainCheckRes.PQntuples() > 0 )
{
QString checkDefinition = domainCheckRes.PQgetvalue( 0, 0 );
//we assume that the constraint is of the following form:
//(VALUE = ANY (ARRAY['a'::text, 'b'::text, 'c'::text, 'd'::text]))
//normally, PostgreSQL creates that if the constraint has been specified as 'VALUE in ('a', 'b', 'c', 'd')
int anyPos = checkDefinition.indexOf( QRegExp( "VALUE\\s*=\\s*ANY\\s*\\(\\s*ARRAY\\s*\\[" ) );
int arrayPosition = checkDefinition.lastIndexOf( QLatin1String( "ARRAY[" ) );
int closingBracketPos = checkDefinition.indexOf( ']', arrayPosition + 6 );
if ( anyPos == -1 || anyPos >= arrayPosition )
{
return false; //constraint has not the required format
}
if ( arrayPosition != -1 )
{
QString valueList = checkDefinition.mid( arrayPosition + 6, closingBracketPos );
QStringList commaSeparation = valueList.split( ',', QString::SkipEmptyParts );
QStringList::const_iterator cIt = commaSeparation.constBegin();
for ( ; cIt != commaSeparation.constEnd(); ++cIt )
{
//get string between ''
int beginQuotePos = cIt->indexOf( '\'' );
int endQuotePos = cIt->lastIndexOf( '\'' );
if ( beginQuotePos != -1 && ( endQuotePos - beginQuotePos ) > 1 )
{
enumValues << cIt->mid( beginQuotePos + 1, endQuotePos - beginQuotePos - 1 );
}
}
}
return true;
}
}
return false;
}
// Returns the maximum value of an attribute
QVariant QgsPostgresProvider::maximumValue( int index ) const
{
try
{
// get the field name
QgsField fld = field( index );
QString sql = QStringLiteral( "SELECT max(%1) AS %1 FROM %2" )
.arg( quotedIdentifier( fld.name() ),
mQuery );
if ( !mSqlWhereClause.isEmpty() )
{
sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
}
sql = QStringLiteral( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ), sql );
QgsPostgresResult rmax( connectionRO()->PQexec( sql ) );
return convertValue( fld.type(), fld.subType(), rmax.PQgetvalue( 0, 0 ), fld.typeName() );
}
catch ( PGFieldNotFound )
{
return QVariant( QString() );
}
}
bool QgsPostgresProvider::isValid() const
{
return mValid;
}
QString QgsPostgresProvider::defaultValueClause( int fieldId ) const
{
QString defVal = mDefaultValues.value( fieldId, QString() );
if ( !providerProperty( EvaluateDefaultValues, false ).toBool() && !defVal.isEmpty() )
{
return defVal;
}
return QString();
}
QVariant QgsPostgresProvider::defaultValue( int fieldId ) const
{
QString defVal = mDefaultValues.value( fieldId, QString() );
if ( providerProperty( EvaluateDefaultValues, false ).toBool() && !defVal.isEmpty() )
{
QgsField fld = field( fieldId );
QgsPostgresResult res( connectionRO()->PQexec( QStringLiteral( "SELECT %1" ).arg( defVal ) ) );
if ( res.result() )
return convertValue( fld.type(), fld.subType(), res.PQgetvalue( 0, 0 ), fld.typeName() );
else
{
pushError( tr( "Could not execute query" ) );
return QVariant();
}
}
return QVariant();
}
bool QgsPostgresProvider::skipConstraintCheck( int fieldIndex, QgsFieldConstraints::Constraint, const QVariant &value ) const
{
if ( providerProperty( EvaluateDefaultValues, false ).toBool() )
{
return !mDefaultValues.value( fieldIndex ).isEmpty();
}
else
{
// stricter check - if we are evaluating default values only on commit then we can only bypass the check
// if the attribute values matches the original default clause
return mDefaultValues.contains( fieldIndex ) && mDefaultValues.value( fieldIndex ) == value.toString() && !value.isNull();
}
}
QString QgsPostgresProvider::paramValue( const QString &fieldValue, const QString &defaultValue ) const
{
if ( fieldValue.isNull() )
return QString();
if ( fieldValue == defaultValue && !defaultValue.isNull() )
{
QgsPostgresResult result( connectionRO()->PQexec( QStringLiteral( "SELECT %1" ).arg( defaultValue ) ) );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
return result.PQgetvalue( 0, 0 );
}
return fieldValue;
}
/* private */
bool QgsPostgresProvider::getTopoLayerInfo()
{
QString sql = QString( "SELECT t.name, l.layer_id "
"FROM topology.layer l, topology.topology t "
"WHERE l.topology_id = t.id AND l.schema_name=%1 "
"AND l.table_name=%2 AND l.feature_column=%3" )
.arg( quotedValue( mSchemaName ),
quotedValue( mTableName ),
quotedValue( mGeometryColumn ) );
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
throw PGException( result ); // we should probably not do this
}
if ( result.PQntuples() < 1 )
{
QgsMessageLog::logMessage( tr( "Could not find topology of layer %1.%2.%3" )
.arg( quotedValue( mSchemaName ),
quotedValue( mTableName ),
quotedValue( mGeometryColumn ) ),
tr( "PostGIS" ) );
return false;
}
mTopoLayerInfo.topologyName = result.PQgetvalue( 0, 0 );
mTopoLayerInfo.layerId = result.PQgetvalue( 0, 1 ).toLong();
return true;
}
/* private */
void QgsPostgresProvider::dropOrphanedTopoGeoms()
{
QString sql = QString( "DELETE FROM %1.relation WHERE layer_id = %2 AND "
"topogeo_id NOT IN ( SELECT id(%3) FROM %4.%5 )" )
.arg( quotedIdentifier( mTopoLayerInfo.topologyName ) )
.arg( mTopoLayerInfo.layerId )
.arg( quotedIdentifier( mGeometryColumn ),
quotedIdentifier( mSchemaName ),
quotedIdentifier( mTableName ) )
;
QgsDebugMsg( "TopoGeom orphans cleanup query: " + sql );
connectionRW()->PQexecNR( sql );
}
QString QgsPostgresProvider::geomParam( int offset ) const
{
QString geometry;
bool forceMulti = false;
if ( mSpatialColType != SctTopoGeometry )
{
forceMulti = QgsWkbTypes::isMultiType( wkbType() );
}
if ( mSpatialColType == SctTopoGeometry )
{
geometry += QStringLiteral( "toTopoGeom(" );
}
if ( forceMulti )
{
geometry += connectionRO()->majorVersion() < 2 ? "multi(" : "st_multi(";
}
geometry += QStringLiteral( "%1($%2%3,%4)" )
.arg( connectionRO()->majorVersion() < 2 ? "geomfromwkb" : "st_geomfromwkb" )
.arg( offset )
.arg( connectionRO()->useWkbHex() ? "" : "::bytea",
mRequestedSrid.isEmpty() ? mDetectedSrid : mRequestedSrid );
if ( forceMulti )
{
geometry += ')';
}
if ( mSpatialColType == SctTopoGeometry )
{
geometry += QStringLiteral( ",%1,%2)" )
.arg( quotedValue( mTopoLayerInfo.topologyName ) )
.arg( mTopoLayerInfo.layerId );
}
return geometry;
}
bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
{
if ( flist.isEmpty() )
return true;
if ( mIsQuery )
return false;
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
bool returnvalue = true;
try
{
conn->begin();
// Prepare the INSERT statement
QString insert = QStringLiteral( "INSERT INTO %1(" ).arg( mQuery );
QString values;
QString delim;
int offset = 1;
QStringList defaultValues;
QList<int> fieldId;
if ( !mGeometryColumn.isNull() )
{
insert += quotedIdentifier( mGeometryColumn );
values += geomParam( offset++ );
delim = ',';
}
// Optimization: if we have a single primary key column whose default value
// is a sequence, and that none of the features have a value set for that
// column, then we can completely omit inserting it.
bool skipSinglePKField = false;
bool overrideIdentity = false;
if ( ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 ) )
{
if ( mPrimaryKeyAttrs.size() == 1 &&
defaultValueClause( mPrimaryKeyAttrs[0] ).startsWith( "nextval(" ) )
{
bool foundNonEmptyPK = false;
int idx = mPrimaryKeyAttrs[0];
QString defaultValue = defaultValueClause( idx );
for ( int i = 0; i < flist.size(); i++ )
{
QgsAttributes attrs2 = flist[i].attributes();
QVariant v2 = attrs2.value( idx, QVariant( QVariant::Int ) );
// a PK field with a sequence val is auto populate by QGIS with this default
// we are only interested in non default values
if ( !v2.isNull() && v2.toString() != defaultValue )
{
foundNonEmptyPK = true;
break;
}
}
skipSinglePKField = !foundNonEmptyPK;
}
if ( !skipSinglePKField )
{
for ( int idx : mPrimaryKeyAttrs )
{
if ( mIdentityFields[idx] == 'a' )
overrideIdentity = true;
insert += delim + quotedIdentifier( field( idx ).name() );
values += delim + QStringLiteral( "$%1" ).arg( defaultValues.size() + offset );
delim = ',';
fieldId << idx;
defaultValues << defaultValueClause( idx );
}
}
}
QgsAttributes attributevec = flist[0].attributes();
// look for unique attribute values to place in statement instead of passing as parameter
// e.g. for defaults
for ( int idx = 0; idx < attributevec.count(); ++idx )
{
QVariant v = attributevec.value( idx, QVariant( QVariant::Int ) ); // default to NULL for missing attributes
if ( skipSinglePKField && idx == mPrimaryKeyAttrs[0] )
continue;
if ( fieldId.contains( idx ) )
continue;
if ( idx >= mAttributeFields.count() )
continue;
QString fieldname = mAttributeFields.at( idx ).name();
QString fieldTypeName = mAttributeFields.at( idx ).typeName();
QgsDebugMsg( "Checking field against: " + fieldname );
if ( fieldname.isEmpty() || fieldname == mGeometryColumn )
continue;
int i;
for ( i = 1; i < flist.size(); i++ )
{
QgsAttributes attrs2 = flist[i].attributes();
QVariant v2 = attrs2.value( idx, QVariant( QVariant::Int ) ); // default to NULL for missing attributes
if ( v2 != v )
break;
}
insert += delim + quotedIdentifier( fieldname );
if ( mIdentityFields[idx] == 'a' )
overrideIdentity = true;
QString defVal = defaultValueClause( idx );
if ( i == flist.size() )
{
if ( qgsVariantEqual( v, defVal ) )
{
if ( defVal.isNull() )
{
values += delim + "NULL";
}
else
{
values += delim + defVal;
}
}
else if ( fieldTypeName == QLatin1String( "geometry" ) )
{
values += QStringLiteral( "%1%2(%3)" )
.arg( delim,
connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
quotedValue( v.toString() ) );
}
else if ( fieldTypeName == QLatin1String( "geography" ) )
{
values += QStringLiteral( "%1st_geographyfromewkt(%2)" )
.arg( delim,
quotedValue( v.toString() ) );
}
else if ( fieldTypeName == QLatin1String( "jsonb" ) )
{
values += delim + quotedJsonValue( v ) + QStringLiteral( "::jsonb" );
}
else if ( fieldTypeName == QLatin1String( "json" ) )
{
values += delim + quotedJsonValue( v ) + QStringLiteral( "::json" );
}
else if ( fieldTypeName == QLatin1String( "bytea" ) )
{
values += delim + quotedByteaValue( v );
}
//TODO: convert arrays and hstore to native types
else
{
values += delim + quotedValue( v );
}
}
else
{
// value is not unique => add parameter
if ( fieldTypeName == QLatin1String( "geometry" ) )
{
values += QStringLiteral( "%1%2($%3)" )
.arg( delim,
connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt" )
.arg( defaultValues.size() + offset );
}
else if ( fieldTypeName == QLatin1String( "geography" ) )
{
values += QStringLiteral( "%1st_geographyfromewkt($%2)" )
.arg( delim )
.arg( defaultValues.size() + offset );
}
else
{
values += QStringLiteral( "%1$%2" )
.arg( delim )
.arg( defaultValues.size() + offset );
}
defaultValues.append( defVal );
fieldId.append( idx );
}
delim = ',';
}
insert += QStringLiteral( ") %1VALUES (%2)" ).arg( overrideIdentity ? "OVERRIDING SYSTEM VALUE " : "" ).arg( values );
if ( !( flags & QgsFeatureSink::FastInsert ) )
{
if ( mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktInt || mPrimaryKeyType == PktUint64 )
{
insert += QLatin1String( " RETURNING " );
QString delim;
const auto constMPrimaryKeyAttrs = mPrimaryKeyAttrs;
for ( int idx : constMPrimaryKeyAttrs )
{
insert += delim + quotedIdentifier( mAttributeFields.at( idx ).name() );
delim = ',';
}
}
}
QgsDebugMsg( QStringLiteral( "prepare addfeatures: %1" ).arg( insert ) );
QgsPostgresResult stmt( conn->PQprepare( QStringLiteral( "addfeatures" ), insert, fieldId.size() + offset - 1, nullptr ) );
if ( stmt.PQresultStatus() != PGRES_COMMAND_OK )
throw PGException( stmt );
for ( QgsFeatureList::iterator features = flist.begin(); features != flist.end(); ++features )
{
QgsAttributes attrs = features->attributes();
QStringList params;
if ( !mGeometryColumn.isNull() )
{
appendGeomParam( features->geometry(), params );
}
params.reserve( fieldId.size() );
for ( int i = 0; i < fieldId.size(); i++ )
{
int attrIdx = fieldId[i];
QVariant value = attrIdx < attrs.length() ? attrs.at( attrIdx ) : QVariant( QVariant::Int );
QString v;
if ( value.isNull() )
{
QgsField fld = field( attrIdx );
v = paramValue( defaultValues[ i ], defaultValues[ i ] );
features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v, fld.typeName() ) );
}
else
{
v = paramValue( value.toString(), defaultValues[ i ] );
if ( v != value.toString() )
{
QgsField fld = field( attrIdx );
features->setAttribute( attrIdx, convertValue( fld.type(), fld.subType(), v, fld.typeName() ) );
}
}
params << v;
}
QgsPostgresResult result( conn->PQexecPrepared( QStringLiteral( "addfeatures" ), params ) );
if ( !( flags & QgsFeatureSink::FastInsert ) && result.PQresultStatus() == PGRES_TUPLES_OK )
{
for ( int i = 0; i < mPrimaryKeyAttrs.size(); ++i )
{
const int idx = mPrimaryKeyAttrs.at( i );
const QgsField fld = mAttributeFields.at( idx );
features->setAttribute( idx, convertValue( fld.type(), fld.subType(), result.PQgetvalue( 0, i ), fld.typeName() ) );
}
}
else if ( result.PQresultStatus() != PGRES_COMMAND_OK )
throw PGException( result );
if ( !( flags & QgsFeatureSink::FastInsert ) && mPrimaryKeyType == PktOid )
{
features->setId( result.PQoidValue() );
QgsDebugMsgLevel( QStringLiteral( "new fid=%1" ).arg( features->id() ), 4 );
}
}
if ( !( flags & QgsFeatureSink::FastInsert ) )
{
// update feature ids
if ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 )
{
for ( QgsFeatureList::iterator features = flist.begin(); features != flist.end(); ++features )
{
QgsAttributes attrs = features->attributes();
if ( mPrimaryKeyType == PktUint64 )
{
features->setId( STRING_TO_FID( attrs.at( mPrimaryKeyAttrs.at( 0 ) ) ) );
}
else if ( mPrimaryKeyType == PktInt )
{
features->setId( PKINT2FID( STRING_TO_FID( attrs.at( mPrimaryKeyAttrs.at( 0 ) ) ) ) );
}
else
{
QVariantList primaryKeyVals;
const auto constMPrimaryKeyAttrs = mPrimaryKeyAttrs;
for ( int idx : constMPrimaryKeyAttrs )
{
primaryKeyVals << attrs.at( idx );
}
features->setId( mShared->lookupFid( primaryKeyVals ) );
}
QgsDebugMsgLevel( QStringLiteral( "new fid=%1" ).arg( features->id() ), 4 );
}
}
}
conn->PQexecNR( QStringLiteral( "DEALLOCATE addfeatures" ) );
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
mShared->addFeaturesCounted( flist.size() );
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while adding features: %1" ).arg( e.errorMessage() ) );
conn->rollback();
conn->PQexecNR( QStringLiteral( "DEALLOCATE addfeatures" ) );
returnvalue = false;
}
conn->unlock();
return returnvalue;
}
bool QgsPostgresProvider::deleteFeatures( const QgsFeatureIds &id )
{
bool returnvalue = true;
if ( mIsQuery )
{
QgsDebugMsg( QStringLiteral( "Cannot delete features (is a query)" ) );
return false;
}
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
try
{
conn->begin();
for ( QgsFeatureIds::const_iterator it = id.begin(); it != id.end(); ++it )
{
QString sql = QStringLiteral( "DELETE FROM %1 WHERE %2" )
.arg( mQuery, whereClause( *it ) );
QgsDebugMsg( "delete sql: " + sql );
//send DELETE statement and do error handling
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
mShared->removeFid( *it );
}
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
if ( mSpatialColType == SctTopoGeometry )
{
// NOTE: in presence of multiple TopoGeometry objects
// for the same table or when deleting a Geometry
// layer _also_ having a TopoGeometry component,
// orphans would still be left.
// TODO: decouple layer from table and signal table when
// records are added or removed
dropOrphanedTopoGeoms();
}
mShared->addFeaturesCounted( -id.size() );
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while deleting features: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
conn->unlock();
return returnvalue;
}
bool QgsPostgresProvider::truncate()
{
bool returnvalue = true;
if ( mIsQuery )
{
QgsDebugMsg( QStringLiteral( "Cannot truncate (is a query)" ) );
return false;
}
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
try
{
conn->begin();
QString sql = QStringLiteral( "TRUNCATE %1" ).arg( mQuery );
QgsDebugMsg( "truncate sql: " + sql );
//send truncate statement and do error handling
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
if ( returnvalue )
{
if ( mSpatialColType == SctTopoGeometry )
{
// NOTE: in presence of multiple TopoGeometry objects
// for the same table or when deleting a Geometry
// layer _also_ having a TopoGeometry component,
// orphans would still be left.
// TODO: decouple layer from table and signal table when
// records are added or removed
dropOrphanedTopoGeoms();
}
mShared->clear();
}
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while truncating: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
conn->unlock();
return returnvalue;
}
bool QgsPostgresProvider::addAttributes( const QList<QgsField> &attributes )
{
bool returnvalue = true;
if ( mIsQuery )
return false;
if ( attributes.isEmpty() )
return true;
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
try
{
conn->begin();
QString delim;
QString sql = QStringLiteral( "ALTER TABLE %1 " ).arg( mQuery );
for ( QList<QgsField>::const_iterator iter = attributes.begin(); iter != attributes.end(); ++iter )
{
QString type = iter->typeName();
if ( type == QLatin1String( "char" ) || type == QLatin1String( "varchar" ) )
{
if ( iter->length() > 0 )
type = QStringLiteral( "%1(%2)" ).arg( type ).arg( iter->length() );
}
else if ( type == QLatin1String( "numeric" ) || type == QLatin1String( "decimal" ) )
{
if ( iter->length() > 0 && iter->precision() >= 0 )
type = QStringLiteral( "%1(%2,%3)" ).arg( type ).arg( iter->length() ).arg( iter->precision() );
}
sql.append( QStringLiteral( "%1ADD COLUMN %2 %3" ).arg( delim, quotedIdentifier( iter->name() ), type ) );
delim = ',';
}
//send sql statement and do error handling
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
throw PGException( result );
for ( QList<QgsField>::const_iterator iter = attributes.begin(); iter != attributes.end(); ++iter )
{
if ( !iter->comment().isEmpty() )
{
sql = QStringLiteral( "COMMENT ON COLUMN %1.%2 IS %3" )
.arg( mQuery,
quotedIdentifier( iter->name() ),
quotedValue( iter->comment() ) );
result = conn->PQexec( sql );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
throw PGException( result );
}
}
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while adding attributes: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
loadFields();
conn->unlock();
return returnvalue;
}
bool QgsPostgresProvider::deleteAttributes( const QgsAttributeIds &ids )
{
bool returnvalue = true;
if ( mIsQuery )
return false;
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
try
{
conn->begin();
QList<int> idsList = ids.values();
std::sort( idsList.begin(), idsList.end(), std::greater<int>() );
for ( auto iter = idsList.constBegin(); iter != idsList.constEnd(); ++iter )
{
int index = *iter;
if ( index < 0 || index >= mAttributeFields.count() )
continue;
QString column = mAttributeFields.at( index ).name();
QString sql = QStringLiteral( "ALTER TABLE %1 DROP COLUMN %2" )
.arg( mQuery,
quotedIdentifier( column ) );
//send sql statement and do error handling
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
throw PGException( result );
//delete the attribute from mAttributeFields
mAttributeFields.remove( index );
}
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while deleting attributes: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
loadFields();
conn->unlock();
return returnvalue;
}
bool QgsPostgresProvider::renameAttributes( const QgsFieldNameMap &renamedAttributes )
{
if ( mIsQuery )
return false;
QString sql = QStringLiteral( "BEGIN;" );
QgsFieldNameMap::const_iterator renameIt = renamedAttributes.constBegin();
bool returnvalue = true;
for ( ; renameIt != renamedAttributes.constEnd(); ++renameIt )
{
int fieldIndex = renameIt.key();
if ( fieldIndex < 0 || fieldIndex >= mAttributeFields.count() )
{
pushError( tr( "Invalid attribute index: %1" ).arg( fieldIndex ) );
return false;
}
if ( mAttributeFields.indexFromName( renameIt.value() ) >= 0 )
{
//field name already in use
pushError( tr( "Error renaming field %1: name '%2' already exists" ).arg( fieldIndex ).arg( renameIt.value() ) );
return false;
}
sql += QStringLiteral( "ALTER TABLE %1 RENAME COLUMN %2 TO %3;" )
.arg( mQuery,
quotedIdentifier( mAttributeFields.at( fieldIndex ).name() ),
quotedIdentifier( renameIt.value() ) );
}
sql += QLatin1String( "COMMIT;" );
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
try
{
conn->begin();
//send sql statement and do error handling
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
throw PGException( result );
returnvalue = conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while renaming attributes: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
loadFields();
conn->unlock();
return returnvalue;
}
bool QgsPostgresProvider::changeAttributeValues( const QgsChangedAttributesMap &attr_map )
{
bool returnvalue = true;
if ( mIsQuery )
return false;
if ( attr_map.isEmpty() )
return true;
QgsPostgresConn *conn = connectionRW();
if ( !conn )
return false;
conn->lock();
try
{
conn->begin();
// cycle through the features
for ( QgsChangedAttributesMap::const_iterator iter = attr_map.constBegin(); iter != attr_map.constEnd(); ++iter )
{
QgsFeatureId fid = iter.key();
// skip added features
if ( FID_IS_NEW( fid ) )
continue;
const QgsAttributeMap &attrs = iter.value();
if ( attrs.isEmpty() )
continue;
QString sql = QStringLiteral( "UPDATE %1 SET " ).arg( mQuery );
bool pkChanged = false;
// cycle through the changed attributes of the feature
QString delim;
for ( QgsAttributeMap::const_iterator siter = attrs.constBegin(); siter != attrs.constEnd(); ++siter )
{
try
{
QgsField fld = field( siter.key() );
pkChanged = pkChanged || mPrimaryKeyAttrs.contains( siter.key() );
sql += delim + QStringLiteral( "%1=" ).arg( quotedIdentifier( fld.name() ) );
delim = ',';
if ( fld.typeName() == QLatin1String( "geometry" ) )
{
sql += QStringLiteral( "%1(%2)" )
.arg( connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
quotedValue( siter->toString() ) );
}
else if ( fld.typeName() == QLatin1String( "geography" ) )
{
sql += QStringLiteral( "st_geographyfromewkt(%1)" )
.arg( quotedValue( siter->toString() ) );
}
else if ( fld.typeName() == QLatin1String( "jsonb" ) )
{
sql += QStringLiteral( "%1::jsonb" )
.arg( quotedJsonValue( siter.value() ) );
}
else if ( fld.typeName() == QLatin1String( "json" ) )
{
sql += QStringLiteral( "%1::json" )
.arg( quotedJsonValue( siter.value() ) );
}
else if ( fld.typeName() == QLatin1String( "bytea" ) )
{
sql += quotedByteaValue( siter.value() );
}
else
{
sql += quotedValue( *siter );
}
}
catch ( PGFieldNotFound )
{
// Field was missing - shouldn't happen
}
}
sql += QStringLiteral( " WHERE %1" ).arg( whereClause( fid ) );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
// update feature id map if key was changed
if ( pkChanged && mPrimaryKeyType == PktFidMap )
{
QVariantList k = mShared->removeFid( fid );
int keyCount = std::min( mPrimaryKeyAttrs.size(), k.size() );
for ( int i = 0; i < keyCount; i++ )
{
int idx = mPrimaryKeyAttrs.at( i );
if ( !attrs.contains( idx ) )
continue;
k[i] = attrs[ idx ];
}
mShared->insertFid( fid, k );
}
}
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while changing attributes: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
conn->unlock();
return returnvalue;
}
void QgsPostgresProvider::appendGeomParam( const QgsGeometry &geom, QStringList &params ) const
{
if ( geom.isNull() )
{
params << QString();
return;
}
QString param;
QgsGeometry convertedGeom( convertToProviderType( geom ) );
QByteArray wkb( !convertedGeom.isNull() ? convertedGeom.asWkb() : geom.asWkb() );
const unsigned char *buf = reinterpret_cast< const unsigned char * >( wkb.constData() );
int wkbSize = wkb.length();
for ( int i = 0; i < wkbSize; ++i )
{
if ( connectionRO()->useWkbHex() )
param += QStringLiteral( "%1" ).arg( ( int ) buf[i], 2, 16, QChar( '0' ) );
else
param += QStringLiteral( "\\%1" ).arg( ( int ) buf[i], 3, 8, QChar( '0' ) );
}
params << param;
}
bool QgsPostgresProvider::changeGeometryValues( const QgsGeometryMap &geometry_map )
{
if ( mIsQuery || mGeometryColumn.isNull() )
return false;
QgsPostgresConn *conn = connectionRW();
if ( !conn )
{
return false;
}
conn->lock();
bool returnvalue = true;
try
{
// Start the PostGIS transaction
conn->begin();
QString update;
QgsPostgresResult result;
if ( mSpatialColType == SctTopoGeometry )
{
// We will create a new TopoGeometry object with the new shape.
// Later, we'll replace the old TopoGeometry with the new one,
// to avoid orphans and retain higher level in an eventual
// hierarchical definition
update = QStringLiteral( "SELECT id(%1) FROM %2 o WHERE %3" )
.arg( geomParam( 1 ),
mQuery,
pkParamWhereClause( 2 ) );
QString getid = QStringLiteral( "SELECT id(%1) FROM %2 WHERE %3" )
.arg( quotedIdentifier( mGeometryColumn ),
mQuery,
pkParamWhereClause( 1 ) );
QgsDebugMsg( "getting old topogeometry id: " + getid );
result = connectionRO()->PQprepare( QStringLiteral( "getid" ), getid, 1, nullptr );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
.arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( getid ) );
throw PGException( result );
}
QString replace = QString( "UPDATE %1 SET %2="
"( topology_id(%2),layer_id(%2),$1,type(%2) )"
"WHERE %3" )
.arg( mQuery,
quotedIdentifier( mGeometryColumn ),
pkParamWhereClause( 2 ) );
QgsDebugMsg( "TopoGeom swap: " + replace );
result = conn->PQprepare( QStringLiteral( "replacetopogeom" ), replace, 2, nullptr );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
.arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( replace ) );
throw PGException( result );
}
}
else
{
update = QStringLiteral( "UPDATE %1 SET %2=%3 WHERE %4" )
.arg( mQuery,
quotedIdentifier( mGeometryColumn ),
geomParam( 1 ),
pkParamWhereClause( 2 ) );
}
QgsDebugMsg( "updating: " + update );
result = conn->PQprepare( QStringLiteral( "updatefeatures" ), update, 2, nullptr );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
.arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( update ) );
throw PGException( result );
}
QgsDebugMsg( QStringLiteral( "iterating over the map of changed geometries..." ) );
for ( QgsGeometryMap::const_iterator iter = geometry_map.constBegin();
iter != geometry_map.constEnd();
++iter )
{
QgsDebugMsg( "iterating over feature id " + FID_TO_STRING( iter.key() ) );
// Save the id of the current topogeometry
long old_tg_id = -1;
if ( mSpatialColType == SctTopoGeometry )
{
QStringList params;
appendPkParams( iter.key(), params );
result = connectionRO()->PQexecPrepared( QStringLiteral( "getid" ), params );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQexecPrepared of 'getid' returning != PGRES_TUPLES_OK (%1 != expected %2)" )
.arg( result.PQresultStatus() ).arg( PGRES_TUPLES_OK ) );
throw PGException( result );
}
// TODO: watch out for NULL, handle somehow
old_tg_id = result.PQgetvalue( 0, 0 ).toLong();
QgsDebugMsg( QStringLiteral( "Old TG id is %1" ).arg( old_tg_id ) );
}
QStringList params;
appendGeomParam( *iter, params );
appendPkParams( iter.key(), params );
result = conn->PQexecPrepared( QStringLiteral( "updatefeatures" ), params );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
if ( mSpatialColType == SctTopoGeometry )
{
long new_tg_id = result.PQgetvalue( 0, 0 ).toLong(); // new topogeo_id
// Replace old TopoGeom with new TopoGeom, so that
// any hierarchically defined TopoGeom will still have its
// definition and we'll leave no orphans
QString replace = QString( "DELETE FROM %1.relation WHERE "
"layer_id = %2 AND topogeo_id = %3" )
.arg( quotedIdentifier( mTopoLayerInfo.topologyName ) )
.arg( mTopoLayerInfo.layerId )
.arg( old_tg_id );
result = conn->PQexec( replace );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQexec of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
.arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( replace ) );
throw PGException( result );
}
// TODO: use prepared query here
replace = QString( "UPDATE %1.relation SET topogeo_id = %2 "
"WHERE layer_id = %3 AND topogeo_id = %4" )
.arg( quotedIdentifier( mTopoLayerInfo.topologyName ) )
.arg( old_tg_id )
.arg( mTopoLayerInfo.layerId )
.arg( new_tg_id );
QgsDebugMsg( "relation swap: " + replace );
result = conn->PQexec( replace );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQexec of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
.arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( replace ) );
throw PGException( result );
}
} // if TopoGeometry
} // for each feature
conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeatures" ) );
if ( mSpatialColType == SctTopoGeometry )
{
connectionRO()->PQexecNR( QStringLiteral( "DEALLOCATE getid" ) );
conn->PQexecNR( QStringLiteral( "DEALLOCATE replacetopogeom" ) );
}
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while changing geometry values: %1" ).arg( e.errorMessage() ) );
conn->rollback();
conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeatures" ) );
if ( mSpatialColType == SctTopoGeometry )
{
connectionRO()->PQexecNR( QStringLiteral( "DEALLOCATE getid" ) );
conn->PQexecNR( QStringLiteral( "DEALLOCATE replacetopogeom" ) );
}
returnvalue = false;
}
conn->unlock();
QgsDebugMsg( QStringLiteral( "leaving." ) );
return returnvalue;
}
bool QgsPostgresProvider::changeFeatures( const QgsChangedAttributesMap &attr_map,
const QgsGeometryMap &geometry_map )
{
Q_ASSERT( mSpatialColType != SctTopoGeometry );
bool returnvalue = true;
if ( mIsQuery )
return false;
if ( attr_map.isEmpty() )
return true;
QgsPostgresConn *conn = connectionRW();
if ( !conn )
return false;
conn->lock();
try
{
conn->begin();
QgsFeatureIds ids( attr_map.keys().toSet() );
ids |= geometry_map.keys().toSet();
// cycle through the features
const auto constIds = ids;
for ( QgsFeatureId fid : constIds )
{
// skip added features
if ( FID_IS_NEW( fid ) )
continue;
const QgsAttributeMap &attrs = attr_map.value( fid );
if ( attrs.isEmpty() && !geometry_map.contains( fid ) )
continue;
QString sql = QStringLiteral( "UPDATE %1 SET " ).arg( mQuery );
bool pkChanged = false;
// cycle through the changed attributes of the feature
QString delim;
for ( QgsAttributeMap::const_iterator siter = attrs.constBegin(); siter != attrs.constEnd(); ++siter )
{
try
{
QgsField fld = field( siter.key() );
pkChanged = pkChanged || mPrimaryKeyAttrs.contains( siter.key() );
sql += delim + QStringLiteral( "%1=" ).arg( quotedIdentifier( fld.name() ) );
delim = ',';
if ( fld.typeName() == QLatin1String( "geometry" ) )
{
sql += QStringLiteral( "%1(%2)" )
.arg( connectionRO()->majorVersion() < 2 ? "geomfromewkt" : "st_geomfromewkt",
quotedValue( siter->toString() ) );
}
else if ( fld.typeName() == QLatin1String( "geography" ) )
{
sql += QStringLiteral( "st_geographyfromewkt(%1)" )
.arg( quotedValue( siter->toString() ) );
}
else if ( fld.typeName() == QLatin1String( "bytea" ) )
{
sql += quotedByteaValue( siter.value() );
}
else
{
sql += quotedValue( *siter );
}
}
catch ( PGFieldNotFound )
{
// Field was missing - shouldn't happen
}
}
if ( !geometry_map.contains( fid ) )
{
sql += QStringLiteral( " WHERE %1" ).arg( whereClause( fid ) );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
}
else
{
sql += QStringLiteral( "%1%2=%3" ).arg( delim, quotedIdentifier( mGeometryColumn ), geomParam( 1 ) );
sql += QStringLiteral( " WHERE %1" ).arg( whereClause( fid ) );
QgsPostgresResult result( conn->PQprepare( QStringLiteral( "updatefeature" ), sql, 1, nullptr ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsDebugMsg( QStringLiteral( "Exception thrown due to PQprepare of this query returning != PGRES_COMMAND_OK (%1 != expected %2): %3" )
.arg( result.PQresultStatus() ).arg( PGRES_COMMAND_OK ).arg( sql ) );
throw PGException( result );
}
QStringList params;
const QgsGeometry &geom = geometry_map[ fid ];
appendGeomParam( geom, params );
result = conn->PQexecPrepared( QStringLiteral( "updatefeature" ), params );
if ( result.PQresultStatus() != PGRES_COMMAND_OK && result.PQresultStatus() != PGRES_TUPLES_OK )
throw PGException( result );
conn->PQexecNR( QStringLiteral( "DEALLOCATE updatefeature" ) );
}
// update feature id map if key was changed
if ( pkChanged && mPrimaryKeyType == PktFidMap )
{
QVariantList k = mShared->removeFid( fid );
for ( int i = 0; i < mPrimaryKeyAttrs.size(); i++ )
{
int idx = mPrimaryKeyAttrs.at( i );
if ( !attrs.contains( idx ) )
continue;
k[i] = attrs[ idx ];
}
mShared->insertFid( fid, k );
}
}
returnvalue &= conn->commit();
if ( mTransaction )
mTransaction->dirtyLastSavePoint();
}
catch ( PGException &e )
{
pushError( tr( "PostGIS error while changing attributes: %1" ).arg( e.errorMessage() ) );
conn->rollback();
returnvalue = false;
}
conn->unlock();
QgsDebugMsg( QStringLiteral( "leaving." ) );
return returnvalue;
}
QgsAttributeList QgsPostgresProvider::attributeIndexes() const
{
QgsAttributeList lst;
lst.reserve( mAttributeFields.count() );
for ( int i = 0; i < mAttributeFields.count(); ++i )
lst.append( i );
return lst;
}
QgsVectorDataProvider::Capabilities QgsPostgresProvider::capabilities() const
{
return mEnabledCapabilities;
}
bool QgsPostgresProvider::setSubsetString( const QString &theSQL, bool updateFeatureCount )
{
if ( theSQL.trimmed() == mSqlWhereClause )
return true;
QString prevWhere = mSqlWhereClause;
mSqlWhereClause = theSQL.trimmed();
QString sql = QStringLiteral( "SELECT * FROM %1" ).arg( mQuery );
if ( !mSqlWhereClause.isEmpty() )
{
sql += QStringLiteral( " WHERE %1" ).arg( mSqlWhereClause );
}
sql += QLatin1String( " LIMIT 0" );
QgsPostgresResult res( connectionRO()->PQexec( sql ) );
if ( res.PQresultStatus() != PGRES_TUPLES_OK )
{
pushError( res.PQresultErrorMessage() );
mSqlWhereClause = prevWhere;
return false;
}
#if 0
// FIXME
if ( mPrimaryKeyType == PktInt && !uniqueData( primaryKeyAttr ) )
{
sqlWhereClause = prevWhere;
return false;
}
#endif
// Update datasource uri too
mUri.setSql( theSQL );
// Update yet another copy of the uri. Why are there 3 copies of the
// uri? Perhaps this needs some rationalisation.....
setDataSourceUri( mUri.uri( false ) );
if ( updateFeatureCount )
{
mShared->setFeaturesCounted( -1 );
}
mLayerExtent.setMinimal();
emit dataChanged();
return true;
}
/**
* Returns the feature count
*/
long QgsPostgresProvider::featureCount() const
{
long featuresCounted = mShared->featuresCounted();
if ( featuresCounted >= 0 )
return featuresCounted;
// See: https://github.com/qgis/QGIS/issues/25285 - QGIS crashes on featureCount())
if ( ! connectionRO() )
{
return 0;
}
// get total number of features
QString sql;
// use estimated metadata even when there is a where clause,
// although we get an incorrect feature count for the subset
// - but make huge dataset usable.
long num = -1;
if ( !mIsQuery && mUseEstimatedMetadata )
{
if ( connectionRO()->pgVersion() >= 90000 )
{
// parse explain output to estimate feature count
// we don't use pg_class reltuples because it returns 0 for view
sql = QStringLiteral( "EXPLAIN (FORMAT JSON) SELECT count(*) FROM %1%2" ).arg( mQuery, filterWhereClause() );
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
const QString json = result.PQgetvalue( 0, 0 );
const QVariantList explain = QgsJsonUtils::parseJson( json ).toList();
const QVariantMap countPlan = explain.count() ? explain[0].toMap().value( "Plan" ).toMap() : QVariantMap();
const QVariantList queryPlan = countPlan.value( "Plans" ).toList();
const QVariant nbRows = queryPlan.count() ? queryPlan[0].toMap().value( "Plan Rows" ) : QVariant();
if ( nbRows.isValid() )
num = nbRows.toInt();
else
QgsLogger::warning( QStringLiteral( "Cannot parse JSON explain result to estimate feature count (%1) : %2" ).arg( sql, json ) );
}
}
else
{
sql = QStringLiteral( "SELECT count(*) FROM %1%2" ).arg( mQuery, filterWhereClause() );
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
QgsDebugMsg( "number of features as text: " + result.PQgetvalue( 0, 0 ) );
num = result.PQgetvalue( 0, 0 ).toLong();
}
mShared->setFeaturesCounted( num );
QgsDebugMsg( "number of features: " + QString::number( num ) );
return num;
}
bool QgsPostgresProvider::empty() const
{
QString sql = QStringLiteral( "SELECT EXISTS (SELECT * FROM %1%2 LIMIT 1)" ).arg( mQuery, filterWhereClause() );
QgsPostgresResult res( connectionRO()->PQexec( sql ) );
if ( res.PQresultStatus() != PGRES_TUPLES_OK )
{
pushError( res.PQresultErrorMessage() );
return false;
}
return res.PQgetvalue( 0, 0 ) != QLatin1String( "t" );
}
QgsRectangle QgsPostgresProvider::extent() const
{
if ( !isValid() || mGeometryColumn.isNull() )
return QgsRectangle();
if ( mSpatialColType == SctGeography )
return QgsRectangle( -180.0, -90.0, 180.0, 90.0 );
if ( mLayerExtent.isEmpty() )
{
QString sql;
QgsPostgresResult result;
QString ext;
// get the extents
if ( !mIsQuery && mUseEstimatedMetadata )
{
// do stats exists?
sql = QStringLiteral( "SELECT count(*) FROM pg_stats WHERE schemaname=%1 AND tablename=%2 AND attname=%3" )
.arg( quotedValue( mSchemaName ),
quotedValue( mTableName ),
quotedValue( mGeometryColumn ) );
result = connectionRO()->PQexec( sql );
if ( result.PQresultStatus() == PGRES_TUPLES_OK && result.PQntuples() == 1 )
{
if ( result.PQgetvalue( 0, 0 ).toInt() > 0 )
{
sql = QStringLiteral( "SELECT reltuples::bigint FROM pg_catalog.pg_class WHERE oid=regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
result = connectionRO()->PQexec( sql );
if ( result.PQresultStatus() == PGRES_TUPLES_OK
&& result.PQntuples() == 1
&& result.PQgetvalue( 0, 0 ).toLong() > 0 )
{
sql = QStringLiteral( "SELECT %1(%2,%3,%4)" )
.arg( connectionRO()->majorVersion() < 2 ? "estimated_extent" :
( connectionRO()->majorVersion() == 2 && connectionRO()->minorVersion() < 1 ? "st_estimated_extent" : "st_estimatedextent" ),
quotedValue( mSchemaName ),
quotedValue( mTableName ),
quotedValue( mGeometryColumn ) );
result = mConnectionRO->PQexec( sql );
if ( result.PQresultStatus() == PGRES_TUPLES_OK && result.PQntuples() == 1 && !result.PQgetisnull( 0, 0 ) )
{
ext = result.PQgetvalue( 0, 0 );
// fix for what might be a PostGIS bug: when the extent crosses the
// dateline extent() returns -180 to 180 (which appears right), but
// estimated_extent() returns eastern bound of data (>-180) and
// 180 degrees.
if ( !ext.startsWith( QLatin1String( "-180 " ) ) && ext.contains( QLatin1String( ",180 " ) ) )
{
ext.clear();
}
}
}
else
{
// no features => ignore estimated extent
ext.clear();
}
}
}
else
{
QgsDebugMsg( QStringLiteral( "no column statistics for %1.%2.%3" ).arg( mSchemaName, mTableName, mGeometryColumn ) );
}
}
if ( ext.isEmpty() )
{
sql = QStringLiteral( "SELECT %1(%2%3) FROM %4%5" )
.arg( connectionRO()->majorVersion() < 2 ? "extent" : "st_extent",
quotedIdentifier( mGeometryColumn ),
mSpatialColType == SctPcPatch ? "::geometry" : "",
mQuery,
filterWhereClause() );
result = connectionRO()->PQexec( sql );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
connectionRO()->PQexecNR( QStringLiteral( "ROLLBACK" ) );
else if ( result.PQntuples() == 1 && !result.PQgetisnull( 0, 0 ) )
ext = result.PQgetvalue( 0, 0 );
}
if ( !ext.isEmpty() )
{
QgsDebugMsg( "Got extents using: " + sql );
QRegExp rx( "\\((.+) (.+),(.+) (.+)\\)" );
if ( ext.contains( rx ) )
{
QStringList ex = rx.capturedTexts();
mLayerExtent.setXMinimum( ex[1].toDouble() );
mLayerExtent.setYMinimum( ex[2].toDouble() );
mLayerExtent.setXMaximum( ex[3].toDouble() );
mLayerExtent.setYMaximum( ex[4].toDouble() );
}
else
{
QgsMessageLog::logMessage( tr( "result of extents query invalid: %1" ).arg( ext ), tr( "PostGIS" ) );
}
}
QgsDebugMsg( "Set extents to: " + mLayerExtent.toString() );
}
return mLayerExtent;
}
void QgsPostgresProvider::updateExtents()
{
mLayerExtent.setMinimal();
}
bool QgsPostgresProvider::getGeometryDetails()
{
if ( mGeometryColumn.isNull() )
{
mDetectedGeomType = QgsWkbTypes::NoGeometry;
mValid = true;
return true;
}
QgsPostgresResult result;
QString sql;
QString schemaName = mSchemaName;
QString tableName = mTableName;
QString geomCol = mGeometryColumn;
QString geomColType;
if ( mIsQuery )
{
sql = QStringLiteral( "SELECT %1 FROM %2 LIMIT 0" ).arg( quotedIdentifier( mGeometryColumn ), mQuery );
QgsDebugMsg( QStringLiteral( "Getting geometry column: %1" ).arg( sql ) );
QgsPostgresResult result( connectionRO()->PQexec( sql ) );
if ( PGRES_TUPLES_OK == result.PQresultStatus() )
{
Oid tableoid = result.PQftable( 0 );
int column = result.PQftablecol( 0 );
result = connectionRO()->PQexec( sql );
if ( tableoid > 0 && PGRES_TUPLES_OK == result.PQresultStatus() )
{
sql = QStringLiteral( "SELECT pg_namespace.nspname,pg_class.relname FROM pg_class,pg_namespace WHERE pg_class.relnamespace=pg_namespace.oid AND pg_class.oid=%1" ).arg( tableoid );
result = connectionRO()->PQexec( sql );
if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
{
schemaName = result.PQgetvalue( 0, 0 );
tableName = result.PQgetvalue( 0, 1 );
sql = QStringLiteral( "SELECT a.attname, t.typname FROM pg_attribute a, pg_type t WHERE a.attrelid=%1 AND a.attnum=%2 AND a.atttypid = t.oid" ).arg( tableoid ).arg( column );
result = connectionRO()->PQexec( sql );
if ( PGRES_TUPLES_OK == result.PQresultStatus() && 1 == result.PQntuples() )
{
geomCol = result.PQgetvalue( 0, 0 );
geomColType = result.PQgetvalue( 0, 1 );
if ( geomColType == QLatin1String( "geometry" ) )
mSpatialColType = SctGeometry;
else if ( geomColType == QLatin1String( "geography" ) )
mSpatialColType = SctGeography;
else if ( geomColType == QLatin1String( "topogeometry" ) )
mSpatialColType = SctTopoGeometry;
else if ( geomColType == QLatin1String( "pcpatch" ) )
mSpatialColType = SctPcPatch;
else
mSpatialColType = SctNone;
}
else
{
schemaName = mSchemaName;
tableName = mTableName;
}
}
}
else
{
schemaName.clear();
tableName = mQuery;
}
}
else
{
mValid = false;
return false;
}
}
QString detectedType = mRequestedGeomType == QgsWkbTypes::Unknown ? QString() : QgsPostgresConn::postgisWkbTypeName( mRequestedGeomType );
QString detectedSrid = mRequestedSrid;
if ( !schemaName.isEmpty() )
{
// check geometry columns
sql = QStringLiteral( "SELECT upper(type),srid,coord_dimension FROM geometry_columns WHERE f_table_name=%1 AND f_geometry_column=%2 AND f_table_schema=%3" )
.arg( quotedValue( tableName ),
quotedValue( geomCol ),
quotedValue( schemaName ) );
QgsDebugMsg( QStringLiteral( "Getting geometry column: %1" ).arg( sql ) );
result = connectionRO()->PQexec( sql );
QgsDebugMsg( QStringLiteral( "Geometry column query returned %1 rows" ).arg( result.PQntuples() ) );
if ( result.PQntuples() == 1 )
{
QString dt = result.PQgetvalue( 0, 0 );
if ( dt != "GEOMETRY" ) detectedType = dt;
QString dim = result.PQgetvalue( 0, 2 );
if ( dim == QLatin1String( "3" ) && !detectedType.endsWith( 'M' ) )
detectedType += QLatin1String( "Z" );
else if ( dim == QLatin1String( "4" ) )
detectedType += QLatin1String( "ZM" );
QString ds = result.PQgetvalue( 0, 1 );
if ( ds != "0" ) detectedSrid = ds;
mSpatialColType = SctGeometry;
}
else
{
connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
}
if ( detectedType.isEmpty() )
{
// check geography columns
sql = QStringLiteral( "SELECT upper(type),srid FROM geography_columns WHERE f_table_name=%1 AND f_geography_column=%2 AND f_table_schema=%3" )
.arg( quotedValue( tableName ),
quotedValue( geomCol ),
quotedValue( schemaName ) );
QgsDebugMsg( QStringLiteral( "Getting geography column: %1" ).arg( sql ) );
result = connectionRO()->PQexec( sql, false );
QgsDebugMsg( QStringLiteral( "Geography column query returned %1" ).arg( result.PQntuples() ) );
if ( result.PQntuples() == 1 )
{
QString dt = result.PQgetvalue( 0, 0 );
if ( dt != "GEOMETRY" ) detectedType = dt;
QString ds = result.PQgetvalue( 0, 1 );
if ( ds != "0" ) detectedSrid = ds;
mSpatialColType = SctGeography;
}
else
{
connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
}
}
if ( detectedType.isEmpty() && connectionRO()->hasTopology() )
{
// check topology.layer
sql = QString( "SELECT CASE "
"WHEN l.feature_type = 1 THEN 'MULTIPOINT' "
"WHEN l.feature_type = 2 THEN 'MULTILINESTRING' "
"WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' "
"WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
"END AS type, t.srid FROM topology.layer l, topology.topology t "
"WHERE l.topology_id = t.id AND l.schema_name=%3 "
"AND l.table_name=%1 AND l.feature_column=%2" )
.arg( quotedValue( tableName ),
quotedValue( geomCol ),
quotedValue( schemaName ) );
QgsDebugMsg( QStringLiteral( "Getting TopoGeometry column: %1" ).arg( sql ) );
result = connectionRO()->PQexec( sql, false );
QgsDebugMsg( QStringLiteral( "TopoGeometry column query returned %1" ).arg( result.PQntuples() ) );
if ( result.PQntuples() == 1 )
{
detectedType = result.PQgetvalue( 0, 0 );
detectedSrid = result.PQgetvalue( 0, 1 );
mSpatialColType = SctTopoGeometry;
}
else
{
connectionRO()->PQexecNR( QStringLiteral( "COMMIT" ) );
}
}
if ( detectedType.isEmpty() && connectionRO()->hasPointcloud() )
{
// check pointcloud columns
sql = QStringLiteral( "SELECT 'POLYGON',srid FROM pointcloud_columns WHERE \"table\"=%1 AND \"column\"=%2 AND \"schema\"=%3" )
.arg( quotedValue( tableName ),
quotedValue( geomCol ),
quotedValue( schemaName ) );
QgsDebugMsg( QStringLiteral( "Getting pointcloud column: %1" ).arg( sql ) );
result = connectionRO()->PQexec( sql, false );
QgsDebugMsg( QStringLiteral( "Pointcloud column query returned %1" ).arg( result.PQntuples() ) );
if ( result.PQntuples() == 1 )
{
detectedType = result.PQgetvalue( 0, 0 );
detectedSrid = result.PQgetvalue( 0, 1 );
mSpatialColType = SctPcPatch;
}
else