Skip to content

Commit

Permalink
Merge pull request #32138 from jgrocha/geometry-type-based-styles
Browse files Browse the repository at this point in the history 
Support for styles for different geometry types for the same Postgresql table
  • Loading branch information
@elpaso
elpaso committed May 5, 2020
2 parents ef0bfff + 2b9b4ed commit 7d9fbc2
Show file tree
Hide file tree
Showing 2 changed files with 112 additions and 31 deletions.
104 changes: 73 additions & 31 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -70,6 +70,12 @@ static bool tableExists( QgsPostgresConn &conn, const QString &name )
return res.PQgetvalue( 0, 0 ).startsWith( 't' );
}

static bool columnExists( QgsPostgresConn &conn, const QString &table, const QString &column )
{
QgsPostgresResult res( conn.PQexec( "SELECT COUNT(*) FROM information_schema.columns WHERE table_name=" + QgsPostgresConn::quotedValue( table ) + " and column_name=" + QgsPostgresConn::quotedValue( column ) ) );
return res.PQgetvalue( 0, 0 ).toInt() > 0;
}

QgsPostgresPrimaryKeyType
QgsPostgresProvider::pkType( const QgsField &f ) const
{
Expand Down Expand Up @@ -4877,26 +4883,43 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
return false;
}

QgsPostgresResult res( conn->PQexec( "CREATE TABLE IF NOT EXISTS layer_styles("
"id SERIAL PRIMARY KEY"
",f_table_catalog varchar"
",f_table_schema varchar"
",f_table_name varchar"
",f_geometry_column varchar"
",styleName text"
",styleQML xml"
",styleSLD xml"
",useAsDefault boolean"
",description text"
",owner varchar(63) DEFAULT CURRENT_USER"
",ui xml"
",update_time timestamp DEFAULT CURRENT_TIMESTAMP"
")" ) );
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
{
errCause = QObject::tr( "Unable to save layer style. It's not possible to create the destination table on the database. Maybe this is due to table permissions (user=%1). Please contact your database admin" ).arg( dsUri.username() );
conn->unref();
return false;
if ( !tableExists( *conn, QStringLiteral( "layer_styles" ) ) )
{
QgsPostgresResult res( conn->PQexec( "CREATE TABLE layer_styles("
"id SERIAL PRIMARY KEY"
",f_table_catalog varchar"
",f_table_schema varchar"
",f_table_name varchar"
",f_geometry_column varchar"
",styleName text"
",styleQML xml"
",styleSLD xml"
",useAsDefault boolean"
",description text"
",owner varchar(63) DEFAULT CURRENT_USER"
",ui xml"
",update_time timestamp DEFAULT CURRENT_TIMESTAMP"
",type varchar"
")" ) );
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
{
errCause = QObject::tr( "Unable to save layer style. It's not possible to create the destination table on the database. Maybe this is due to table permissions (user=%1). Please contact your database admin" ).arg( dsUri.username() );
conn->unref();
return false;
}
}
else
{
if ( !columnExists( *conn, QStringLiteral( "layer_styles" ), QStringLiteral( "type" ) ) )
{
QgsPostgresResult res( conn->PQexec( "ALTER TABLE layer_styles ADD COLUMN type varchar NULL" ) );
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
{
errCause = QObject::tr( "Unable to add column type to layer_styles table. Maybe this is due to table permissions (user=%1). Please contact your database admin" ).arg( dsUri.username() );
conn->unref();
return false;
}
}
}

if ( dsUri.database().isEmpty() ) // typically when a service file is used
Expand All @@ -4912,15 +4935,17 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
uiFileValue = QStringLiteral( ",XMLPARSE(DOCUMENT %1)" ).arg( QgsPostgresConn::quotedValue( uiFileContent ) );
}

const QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );

// Note: in the construction of the INSERT and UPDATE strings the qmlStyle and sldStyle values
// can contain user entered strings, which may themselves include %## values that would be
// replaced by the QString.arg function. To ensure that the final SQL string is not corrupt these
// two values are both replaced in the final .arg call of the string construction.

QString sql = QString( "INSERT INTO layer_styles("
"f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner%11"
"f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner,type%12"
") VALUES ("
"%1,%2,%3,%4,%5,XMLPARSE(DOCUMENT %16),XMLPARSE(DOCUMENT %17),%8,%9,%10%12"
"%1,%2,%3,%4,%5,XMLPARSE(DOCUMENT %16),XMLPARSE(DOCUMENT %17),%8,%9,%10,%11%13"
")" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
Expand All @@ -4932,6 +4957,7 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
.arg( "CURRENT_USER" )
.arg( uiFileColumn )
.arg( uiFileValue )
.arg( wkbTypeString )
// Must be the final .arg replacement - see above
.arg( QgsPostgresConn::quotedValue( qmlStyle ),
QgsPostgresConn::quotedValue( sldStyle ) );
Expand All @@ -4942,14 +4968,16 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
" AND f_table_schema=%2"
" AND f_table_name=%3"
" AND f_geometry_column=%4"
" AND styleName=%5" )
" AND type=%5"
" AND styleName=%6" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
.arg( wkbTypeString )
.arg( QgsPostgresConn::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );

res = conn->PQexec( checkQuery );
QgsPostgresResult res( conn->PQexec( checkQuery ) );
if ( res.PQntuples() > 0 )
{
if ( QMessageBox::question( nullptr, QObject::tr( "Save style in database" ),
Expand All @@ -4972,8 +5000,10 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
" AND f_table_schema=%7"
" AND f_table_name=%8"
" AND f_geometry_column=%9"
" AND styleName=%10" )
" AND styleName=%10"
" AND type=%2" )
.arg( useAsDefault ? "true" : "false" )
.arg( wkbTypeString )
.arg( QgsPostgresConn::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
.arg( "CURRENT_USER" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
Expand All @@ -4993,11 +5023,14 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
" WHERE f_table_catalog=%1"
" AND f_table_schema=%2"
" AND f_table_name=%3"
" AND f_geometry_column=%4" )
" AND f_geometry_column=%4"
" AND type=%5" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) );
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
.arg( wkbTypeString );

sql = QStringLiteral( "BEGIN; %1; %2; COMMIT;" ).arg( removeDefaultSql, sql );
}

Expand Down Expand Up @@ -5045,18 +5078,22 @@ QString QgsPostgresProviderMetadata::loadStyle( const QString &uri, QString &err
geomColumnExpr = QStringLiteral( "=" ) + QgsPostgresConn::quotedValue( dsUri.geometryColumn() );
}

QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );

QString selectQmlQuery = QString( "SELECT styleQML"
" FROM layer_styles"
" WHERE f_table_catalog=%1"
" AND f_table_schema=%2"
" AND f_table_name=%3"
" AND f_geometry_column %4"
" AND type=%5"
" ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END"
",update_time DESC LIMIT 1" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
.arg( geomColumnExpr );
.arg( geomColumnExpr )
.arg( wkbTypeString );

QgsPostgresResult result( conn->PQexec( selectQmlQuery ) );

Expand All @@ -5083,17 +5120,21 @@ int QgsPostgresProviderMetadata::listStyles( const QString &uri, QStringList &id
dsUri.setDatabase( conn->currentDatabase() );
}

QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );

QString selectRelatedQuery = QString( "SELECT id,styleName,description"
" FROM layer_styles"
" WHERE f_table_catalog=%1"
" AND f_table_schema=%2"
" AND f_table_name=%3"
" AND f_geometry_column=%4"
" AND type=%5"
" ORDER BY useasdefault DESC, update_time DESC" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) );
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
.arg( wkbTypeString );

QgsPostgresResult result( conn->PQexec( selectRelatedQuery ) );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
Expand All @@ -5114,12 +5155,13 @@ int QgsPostgresProviderMetadata::listStyles( const QString &uri, QStringList &id

QString selectOthersQuery = QString( "SELECT id,styleName,description"
" FROM layer_styles"
" WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4)"
" WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4 AND type=%5)"
" ORDER BY update_time DESC" )
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) );
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
.arg( wkbTypeString );

result = conn->PQexec( selectOthersQuery );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
Expand Down
39 changes: 39 additions & 0 deletions tests/src/python/test_provider_postgres.py
View file
Expand Up @@ -1265,6 +1265,45 @@ def testStyle(self):
self.assertEqual(desclist, [])
self.assertEqual(errmsg, "")

def testStyleWithGeometryType(self):
"""Test saving styles with the additional geometry type
Layers are created from geometries_table
"""

myconn = 'service=\'qgis_test\''
if 'QGIS_PGTEST_DB' in os.environ:
myconn = os.environ['QGIS_PGTEST_DB']

# point layer
myPoint = QgsVectorLayer(myconn + ' sslmode=disable srid=4326 type=POINT table="qgis_test"."geometries_table" (geom) sql=', 'Point', 'postgres')
self.assertTrue(myPoint.isValid())
myPoint.saveStyleToDatabase('myPointStyle', '', False, '')

# polygon layer
myPolygon = QgsVectorLayer(myconn + ' sslmode=disable srid=4326 type=POLYGON table="qgis_test"."geometries_table" (geom) sql=', 'Poly', 'postgres')
self.assertTrue(myPoint.isValid())
myPolygon.saveStyleToDatabase('myPolygonStyle', '', False, '')

# how many
related_count, idlist, namelist, desclist, errmsg = myPolygon.listStylesInDatabase()
self.assertEqual(len(idlist), 2)
self.assertEqual(namelist, ['myPolygonStyle', 'myPointStyle'])

# raw psycopg2 query
self.assertTrue(self.con)
cur = self.con.cursor()
self.assertTrue(cur)
cur.execute("select stylename, type from layer_styles order by type")
self.assertEqual(cur.fetchall(), [('myPointStyle', 'Point'), ('myPolygonStyle', 'Polygon')])
cur.close()

# delete them
myPolygon.deleteStyleFromDatabase(idlist[1])
myPolygon.deleteStyleFromDatabase(idlist[0])
styles = myPolygon.listStylesInDatabase()
ids = styles[1]
self.assertEqual(len(ids), 0)

def testHasMetadata(self):
# views don't have metadata
vl = QgsVectorLayer('{} table="qgis_test"."{}" key="pk" sql='.format(self.dbconn, 'bikes_view'), "bikes_view", "postgres")
Expand Down

0 comments on commit 7d9fbc2

Please sign in to comment.