Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
extent b47b439
  • Loading branch information
jef-n committed Oct 31, 2012
1 parent bbecffd commit 62ebf88
Show file tree
Hide file tree
Showing 6 changed files with 142 additions and 87 deletions.
9 changes: 7 additions & 2 deletions src/providers/postgres/qgspgtablemodel.cpp
Expand Up @@ -27,7 +27,8 @@ QgsPgTableModel::QgsPgTableModel()
headerLabels << tr( "Schema" );
headerLabels << tr( "Table" );
headerLabels << tr( "Type" );
headerLabels << tr( "Geometry column" );
headerLabels << tr( "Geometry Type" );
headerLabels << tr( "Geometry Column" );
headerLabels << tr( "SRID" );
headerLabels << tr( "Primary key column" );
headerLabels << tr( "Select at id" );
Expand All @@ -41,11 +42,12 @@ QgsPgTableModel::~QgsPgTableModel()

void QgsPgTableModel::addTableEntry( QgsPostgresLayerProperty layerProperty )
{
QgsDebugMsg( QString( "%1.%2.%3 type=%4 srid=%5 pk=%6 sql=%7" )
QgsDebugMsg( QString( "%1.%2.%3 type=%4 geomType=%5 srid=%6 pk=%7 sql=%8" )
.arg( layerProperty.schemaName )
.arg( layerProperty.tableName )
.arg( layerProperty.geometryColName )
.arg( layerProperty.type )
.arg( layerProperty.geometryColType )
.arg( layerProperty.srid )
.arg( layerProperty.pkCols.join( "," ) )
.arg( layerProperty.sql ) );
Expand Down Expand Up @@ -85,6 +87,8 @@ void QgsPgTableModel::addTableEntry( QgsPostgresLayerProperty layerProperty )
typeItem->setData( wkbType == QGis::WKBUnknown, Qt::UserRole + 1 );
typeItem->setData( wkbType, Qt::UserRole + 2 );

QStandardItem *geomTypeItem = new QStandardItem( QgsPostgresConn::displayStringForGeomType( layerProperty.geometryColType ) );

QStandardItem *tableItem = new QStandardItem( layerProperty.tableName );
QStandardItem *geomItem = new QStandardItem( layerProperty.geometryColName );
QStandardItem *sridItem = new QStandardItem( layerProperty.srid );
Expand Down Expand Up @@ -115,6 +119,7 @@ void QgsPgTableModel::addTableEntry( QgsPostgresLayerProperty layerProperty )
childItemList << schemaNameItem;
childItemList << tableItem;
childItemList << typeItem;
childItemList << geomTypeItem;
childItemList << geomItem;
childItemList << sridItem;
childItemList << pkItem;
Expand Down
1 change: 1 addition & 0 deletions src/providers/postgres/qgspgtablemodel.h
Expand Up @@ -50,6 +50,7 @@ class QgsPgTableModel : public QStandardItemModel
dbtmSchema = 0,
dbtmTable,
dbtmType,
dbtmGeomType,
dbtmGeomCol,
dbtmSrid,
dbtmPkCol,
Expand Down
107 changes: 73 additions & 34 deletions src/providers/postgres/qgspostgresconn.cpp
Expand Up @@ -309,6 +309,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
for ( int i = 0; i < 3; i++ )
{
QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName;
QgsPostgresGeometryColumnType columnType;

if ( i == 0 )
{
Expand All @@ -318,6 +319,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
typeName = "upper(l.type)";
sridName = "l.srid";
gtableName = "geometry_columns";
columnType = sctGeometry;
}
else if ( i == 1 )
{
Expand All @@ -327,6 +329,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
typeName = "upper(l.type)";
sridName = "l.srid";
gtableName = "geography_columns";
columnType = sctGeography;
}
else if ( i == 2 )
{
Expand All @@ -341,6 +344,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
"END AS type";
sridName = "(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id)";
gtableName = "topology.layer";
columnType = sctTopoGeometry;
}

// The following query returns only tables that exist and the user has SELECT privilege on.
Expand Down Expand Up @@ -388,6 +392,7 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
layerProperty.geometryColName = column;
layerProperty.geometryColType = columnType;

if ( relkind == "v" )
{
Expand All @@ -400,7 +405,6 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
}
layerProperty.srid = srid;
layerProperty.sql = "";
layerProperty.isGeography = i == 1;

mLayersSupported << layerProperty;
nColumns++;
Expand All @@ -418,47 +422,45 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
if ( !searchGeometryColumnsOnly )
{
// Now have a look for geometry columns that aren't in the geometry_columns table.
QString sql = "SELECT "
"pg_class.relname"
",pg_namespace.nspname"
",pg_attribute.attname"
",pg_class.relkind"
" FROM "
"pg_attribute,pg_class,pg_namespace"
" WHERE pg_namespace.oid=pg_class.relnamespace"
" AND pg_attribute.attrelid = pg_class.oid"
" AND ("
" EXISTS (SELECT * FROM pg_type WHERE pg_type.oid=pg_attribute.atttypid AND pg_type.typname IN ('geometry','geography','topogeometry'))"
" OR pg_attribute.atttypid IN (SELECT oid FROM pg_type a WHERE EXISTS (SELECT * FROM pg_type b WHERE a.typbasetype=b.oid AND b.typname IN ('geometry','geography','topogeometry')))"
")"
" AND has_schema_privilege( pg_namespace.nspname, 'usage' )"
" AND has_table_privilege( '\"' || pg_namespace.nspname || '\".\"' || pg_class.relname || '\"', 'select' )";
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"
" 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')"
" AND has_schema_privilege( n.nspname, 'usage' )"
" AND has_table_privilege( '\"' || n.nspname || '\".\"' || c.relname || '\"', 'select' )"
" AND (t.typname IN ('geometry','geography','topogeometry') OR b.typname IN ('geometry','geography','topogeometry'))";

// user has select privilege
if ( searchPublicOnly )
sql += " AND pg_namespace.nspname='public'";
sql += " AND n.nspname='public'";

// skip columns of which we already derived information from the metadata tables
if ( nColumns > 0 )
{
if ( foundInTables & 1 )
{
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geometry_column FROM geometry_columns)";
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geometry_column FROM geometry_columns)";
}

if ( foundInTables & 2 )
{
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geography_column FROM geography_columns)";
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geography_column FROM geography_columns)";
}

if ( foundInTables & 4 )
{
sql += " AND (pg_namespace.nspname,pg_class.relname,pg_attribute.attname) NOT IN (SELECT schema_name,table_name,feature_column FROM topology.layer)";
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT schema_name,table_name,feature_column FROM topology.layer)";
}
}

sql += " AND pg_class.relkind IN ('v','r')"; // only from views and relations (tables)

QgsDebugMsg( "sql: " + sql );

result = PQexec( sql );
Expand All @@ -485,13 +487,31 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
QString schemaName = result.PQgetvalue( i, 1 ); // nspname
QString column = result.PQgetvalue( i, 2 ); // attname
QString relkind = result.PQgetvalue( i, 3 ); // relation kind
QString coltype = result.PQgetvalue( i, 4 ); // column type

QgsDebugMsg( QString( "%1.%2.%3: %4" ).arg( schemaName ).arg( tableName ).arg( column ).arg( relkind ) );

layerProperty.type = QString::null;
layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
layerProperty.geometryColName = column;
if ( coltype == "geometry" )
{
layerProperty.geometryColType = sctGeometry;
}
else if ( coltype == "geography" )
{
layerProperty.geometryColType = sctGeography;
}
else if ( coltype == "topogeometry" )
{
layerProperty.geometryColType = sctTopoGeometry;
}
else
{
Q_ASSERT( !"Unknown geometry type" );
}

if ( relkind == "v" )
{
layerProperty.pkCols = pkCandidates( schemaName, tableName );
Expand All @@ -501,8 +521,8 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
continue;
}
}

layerProperty.sql = "";
layerProperty.isGeography = false; // TODO might be geography after all

mLayersSupported << layerProperty;
nColumns++;
Expand Down Expand Up @@ -552,10 +572,10 @@ bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchP
layerProperty.schemaName = schema;
layerProperty.tableName = table;
layerProperty.geometryColName = QString::null;
layerProperty.geometryColType = sctNone;
layerProperty.pkCols = relkind == "v" ? pkCandidates( schema, table ) : QStringList();
layerProperty.srid = "";
layerProperty.sql = "";
layerProperty.isGeography = false;

mLayersSupported << layerProperty;
nColumns++;
Expand Down Expand Up @@ -695,12 +715,10 @@ QString QgsPostgresConn::postgisVersion()
return mPostgisVersionInfo;
}

QString QgsPostgresConn::quotedIdentifier( QString ident, bool isGeography )
QString QgsPostgresConn::quotedIdentifier( QString ident )
{
ident.replace( '"', "\"\"" );
ident = ident.prepend( "\"" ).append( "\"" );
if ( isGeography )
ident += "::geometry";
return ident;
}

Expand Down Expand Up @@ -1093,13 +1111,14 @@ void QgsPostgresConn::retrieveLayerTypes( QgsPostgresLayerProperty &layerPropert
" WHEN %2 THEN 'LINESTRING'"
" WHEN %3 THEN 'POLYGON'"
" END,"
" %4(%5)"
" FROM %6" )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPoint, layerProperty.isGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBLineString, layerProperty.isGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPolygon, layerProperty.isGeography ) )
" %4(%5%6)"
" FROM %7" )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPoint, layerProperty.geometryColType == sctGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBLineString, layerProperty.geometryColType == sctGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPolygon, layerProperty.geometryColType == sctGeography ) )
.arg( majorVersion() < 2 ? "srid" : "st_srid" )
.arg( quotedIdentifier( layerProperty.geometryColName, layerProperty.isGeography ) )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( layerProperty.geometryColType == sctGeography ? "::geometry" : "" )
.arg( table );

QgsDebugMsg( "Retrieving geometry types: " + query );
Expand Down Expand Up @@ -1196,7 +1215,9 @@ QString QgsPostgresConn::postgisWkbTypeName( QGis::WkbType wkbType )

QString QgsPostgresConn::postgisTypeFilter( QString geomCol, QGis::WkbType geomType, bool isGeography )
{
geomCol = quotedIdentifier( geomCol, isGeography );
geomCol = quotedIdentifier( geomCol );
if ( isGeography )
geomCol += "::geometry";

switch ( geomType )
{
Expand Down Expand Up @@ -1331,6 +1352,24 @@ QString QgsPostgresConn::displayStringForWkbType( QGis::WkbType type )
return QString::null;
}

QString QgsPostgresConn::displayStringForGeomType( QgsPostgresGeometryColumnType type )
{
switch ( type )
{
case sctNone:
return tr( "None" );
case sctGeometry:
return tr( "Geometry" );
case sctGeography:
return tr( "Geography" );
case sctTopoGeometry:
return tr( "Topology" );
}

Q_ASSERT( !"unexpected geometry column type" );
return QString::null;
}

QGis::WkbType QgsPostgresConn::wkbTypeFromGeomType( QGis::GeometryType geomType )
{
switch ( geomType )
Expand Down
27 changes: 18 additions & 9 deletions src/providers/postgres/qgspostgresconn.h
Expand Up @@ -33,19 +33,27 @@ extern "C"

class QgsField;

enum QgsPostgresGeometryColumnType
{
sctNone,
sctGeometry,
sctGeography,
sctTopoGeometry
};

/** Layer Property structure */
// TODO: Fill to Postgres/PostGIS specifications
struct QgsPostgresLayerProperty
{
// Postgres/PostGIS layer properties
QString type;
QString schemaName;
QString tableName;
QString geometryColName;
QStringList pkCols;
QString srid;
bool isGeography;
QString sql;
QString type;
QString schemaName;
QString tableName;
QString geometryColName;
QgsPostgresGeometryColumnType geometryColType;
QStringList pkCols;
QString srid;
QString sql;
};

class QgsPostgresResult
Expand Down Expand Up @@ -138,7 +146,7 @@ class QgsPostgresConn : public QObject

/** Double quote a PostgreSQL identifier for placement in a SQL string.
*/
static QString quotedIdentifier( QString ident, bool isGeography = false );
static QString quotedIdentifier( QString ident );

/** Quote a value for placement in a SQL string.
*/
Expand Down Expand Up @@ -167,6 +175,7 @@ class QgsPostgresConn : public QObject
static const int sGeomTypeSelectLimit;

static QString displayStringForWkbType( QGis::WkbType wkbType );
static QString displayStringForGeomType( QgsPostgresGeometryColumnType geomType );
static QGis::WkbType wkbTypeFromPostgis( QString dbType );

static QString postgisWkbTypeName( QGis::WkbType wkbType );
Expand Down

0 comments on commit 62ebf88

Please sign in to comment.