Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Unify list of supported spatial types
  • Loading branch information
strk committed Nov 15, 2019
1 parent d1ac2fb commit c52d767
Show file tree
Hide file tree
Showing 2 changed files with 38 additions and 18 deletions.
54 changes: 36 additions & 18 deletions src/providers/postgres/qgspostgresconn.cpp
Expand Up @@ -31,6 +31,7 @@
#include "qgspostgresstringutils.h"

#include <QApplication>
#include <QStringList>
#include <QThread>

#include <climits>
Expand Down Expand Up @@ -402,14 +403,29 @@ void QgsPostgresConn::unref()
delete this;
}

/* private */
QStringList QgsPostgresConn::supportedSpatialTypes()
{
QStringList supportedSpatialTypes;

supportedSpatialTypes << quotedValue( "geometry" )
<< quotedValue( "geography" )
<< quotedValue( "pcpatch" );
if ( hasRaster() ) supportedSpatialTypes << quotedValue( "raster" );
if ( hasTopology() ) supportedSpatialTypes << quotedValue( "topogeometry" );

return supportedSpatialTypes;
}

/* private */
// TODO: deprecate this function
void QgsPostgresConn::addColumnInfo( QgsPostgresLayerProperty &layerProperty, const QString &schemaName, const QString &viewName, bool fetchPkCandidates )
{
// TODO: optimize this query when pk candidates aren't needed
// could use array_agg() and count()
// array output would look like this: "{One,tWo}"
QString sql = QStringLiteral( "SELECT attname, CASE WHEN typname in ('geometry','geography','topogeometry','raster') THEN 1 ELSE null END AS isSpatial FROM pg_attribute JOIN pg_type ON atttypid=pg_type.oid WHERE attrelid=regclass('%1.%2') AND NOT attisdropped AND attnum>0 ORDER BY attnum" )
QString sql = QStringLiteral( "SELECT attname, CASE WHEN typname in (%1) THEN 1 ELSE null END AS isSpatial FROM pg_attribute JOIN pg_type ON atttypid=pg_type.oid WHERE attrelid=regclass('%2.%3') AND NOT attisdropped AND attnum>0 ORDER BY attnum" )
.arg( supportedSpatialTypes().join( ',' ) )
.arg( quotedIdentifier( schemaName ),
quotedIdentifier( viewName ) );
QgsDebugMsg( "getting column info: " + sql );
Expand Down Expand Up @@ -532,7 +548,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
// Can't use regclass here because table must exist, else error occurs.
sql = QString( "SELECT %1,%2,%3,%4,%5,%6,c.relkind,obj_description(c.oid),"
"array_agg(a.attname), "
"count(CASE WHEN t.typname IN ('geometry','geography','topogeometry','raster') THEN 1 ELSE NULL END) "
"count(CASE WHEN t.typname IN (%9) THEN 1 ELSE NULL END) "
", %8 "
" FROM %7 l,pg_class c,pg_namespace n,pg_attribute a,pg_type t"
" WHERE c.relname=%1"
Expand All @@ -547,6 +563,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
)
.arg( tableName, schemaName, columnName, typeName, sridName, dimName, gtableName )
.arg( 1 )
.arg( supportedSpatialTypes().join( ',' ) )
;

if ( searchPublicOnly )
Expand Down Expand Up @@ -663,22 +680,23 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
if ( !searchGeometryColumnsOnly )
{
// Now have a look for spatial columns that aren't in the geometry_columns table.
QString sql = "SELECT"
" c.relname"
",n.nspname"
",a.attname"
",c.relkind"
",CASE WHEN t.typname IN ('geometry','geography','topogeometry') THEN t.typname ELSE b.typname END AS coltype"
",obj_description(c.oid)"
" FROM pg_attribute a"
" JOIN pg_class c ON c.oid=a.attrelid"
" JOIN pg_namespace n ON n.oid=c.relnamespace"
" JOIN pg_type t ON t.oid=a.atttypid"
" LEFT JOIN pg_type b ON b.oid=t.typbasetype"
" WHERE c.relkind IN ('v','r','m','p')"
" AND has_schema_privilege( n.nspname, 'usage' )"
" AND has_table_privilege( c.oid, 'select' )"
" AND (t.typname IN ('geometry','geography','topogeometry') OR b.typname IN ('geometry','geography','topogeometry','pcpatch','raster'))";
QString sql = QStringLiteral( "SELECT"
" c.relname"
",n.nspname"
",a.attname"
",c.relkind"
",CASE WHEN t.typname IN (%1) THEN t.typname ELSE b.typname END AS coltype"
",obj_description(c.oid)"
" FROM pg_attribute a"
" JOIN pg_class c ON c.oid=a.attrelid"
" JOIN pg_namespace n ON n.oid=c.relnamespace"
" JOIN pg_type t ON t.oid=a.atttypid"
" LEFT JOIN pg_type b ON b.oid=t.typbasetype"
" WHERE c.relkind IN ('v','r','m','p')"
" AND has_schema_privilege( n.nspname, 'usage' )"
" AND has_table_privilege( c.oid, 'select' )"
" AND (t.typname IN (%1) OR b.typname IN (%1))" )
.arg( supportedSpatialTypes().join( ',' ) );

// user has select privilege
if ( searchPublicOnly )
Expand Down
2 changes: 2 additions & 0 deletions src/providers/postgres/qgspostgresconn.h
Expand Up @@ -432,6 +432,8 @@ class QgsPostgresConn : public QObject

bool mReadOnly;

QStringList supportedSpatialTypes();

static QMap<QString, QgsPostgresConn *> sConnectionsRW;
static QMap<QString, QgsPostgresConn *> sConnectionsRO;

Expand Down

0 comments on commit c52d767

Please sign in to comment.