Skip to content

Commit 7d9fbc2

Browse files
elpasoelpaso
authoredMay 5, 2020
Merge pull request #32138 from jgrocha/geometry-type-based-styles
Support for styles for different geometry types for the same Postgresql table
·
ltr-3_40final-3_14_0
2 parents ef0bfff + 2b9b4ed commit 7d9fbc2

File tree

2 files changed

+112
-31
lines changed

2 files changed

+112
-31
lines changed
 

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 73 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,12 @@ static bool tableExists( QgsPostgresConn &conn, const QString &name )
7070
return res.PQgetvalue( 0, 0 ).startsWith( 't' );
7171
}
7272

73+
static bool columnExists( QgsPostgresConn &conn, const QString &table, const QString &column )
74+
{
75+
QgsPostgresResult res( conn.PQexec( "SELECT COUNT(*) FROM information_schema.columns WHERE table_name=" + QgsPostgresConn::quotedValue( table ) + " and column_name=" + QgsPostgresConn::quotedValue( column ) ) );
76+
return res.PQgetvalue( 0, 0 ).toInt() > 0;
77+
}
78+
7379
QgsPostgresPrimaryKeyType
7480
QgsPostgresProvider::pkType( const QgsField &f ) const
7581
{
@@ -4877,26 +4883,43 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
48774883
return false;
48784884
}
48794885

4880-
QgsPostgresResult res( conn->PQexec( "CREATE TABLE IF NOT EXISTS layer_styles("
4881-
"id SERIAL PRIMARY KEY"
4882-
",f_table_catalog varchar"
4883-
",f_table_schema varchar"
4884-
",f_table_name varchar"
4885-
",f_geometry_column varchar"
4886-
",styleName text"
4887-
",styleQML xml"
4888-
",styleSLD xml"
4889-
",useAsDefault boolean"
4890-
",description text"
4891-
",owner varchar(63) DEFAULT CURRENT_USER"
4892-
",ui xml"
4893-
",update_time timestamp DEFAULT CURRENT_TIMESTAMP"
4894-
")" ) );
4895-
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
4896-
{
4897-
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() );
4898-
conn->unref();
4899-
return false;
4886+
if ( !tableExists( *conn, QStringLiteral( "layer_styles" ) ) )
4887+
{
4888+
QgsPostgresResult res( conn->PQexec( "CREATE TABLE layer_styles("
4889+
"id SERIAL PRIMARY KEY"
4890+
",f_table_catalog varchar"
4891+
",f_table_schema varchar"
4892+
",f_table_name varchar"
4893+
",f_geometry_column varchar"
4894+
",styleName text"
4895+
",styleQML xml"
4896+
",styleSLD xml"
4897+
",useAsDefault boolean"
4898+
",description text"
4899+
",owner varchar(63) DEFAULT CURRENT_USER"
4900+
",ui xml"
4901+
",update_time timestamp DEFAULT CURRENT_TIMESTAMP"
4902+
",type varchar"
4903+
")" ) );
4904+
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
4905+
{
4906+
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() );
4907+
conn->unref();
4908+
return false;
4909+
}
4910+
}
4911+
else
4912+
{
4913+
if ( !columnExists( *conn, QStringLiteral( "layer_styles" ), QStringLiteral( "type" ) ) )
4914+
{
4915+
QgsPostgresResult res( conn->PQexec( "ALTER TABLE layer_styles ADD COLUMN type varchar NULL" ) );
4916+
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
4917+
{
4918+
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() );
4919+
conn->unref();
4920+
return false;
4921+
}
4922+
}
49004923
}
49014924

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

4938+
const QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );
4939+
49154940
// Note: in the construction of the INSERT and UPDATE strings the qmlStyle and sldStyle values
49164941
// can contain user entered strings, which may themselves include %## values that would be
49174942
// replaced by the QString.arg function. To ensure that the final SQL string is not corrupt these
49184943
// two values are both replaced in the final .arg call of the string construction.
49194944

49204945
QString sql = QString( "INSERT INTO layer_styles("
4921-
"f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner%11"
4946+
"f_table_catalog,f_table_schema,f_table_name,f_geometry_column,styleName,styleQML,styleSLD,useAsDefault,description,owner,type%12"
49224947
") VALUES ("
4923-
"%1,%2,%3,%4,%5,XMLPARSE(DOCUMENT %16),XMLPARSE(DOCUMENT %17),%8,%9,%10%12"
4948+
"%1,%2,%3,%4,%5,XMLPARSE(DOCUMENT %16),XMLPARSE(DOCUMENT %17),%8,%9,%10,%11%13"
49244949
")" )
49254950
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
49264951
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
@@ -4932,6 +4957,7 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
49324957
.arg( "CURRENT_USER" )
49334958
.arg( uiFileColumn )
49344959
.arg( uiFileValue )
4960+
.arg( wkbTypeString )
49354961
// Must be the final .arg replacement - see above
49364962
.arg( QgsPostgresConn::quotedValue( qmlStyle ),
49374963
QgsPostgresConn::quotedValue( sldStyle ) );
@@ -4942,14 +4968,16 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
49424968
" AND f_table_schema=%2"
49434969
" AND f_table_name=%3"
49444970
" AND f_geometry_column=%4"
4945-
" AND styleName=%5" )
4971+
" AND type=%5"
4972+
" AND styleName=%6" )
49464973
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
49474974
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
49484975
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
49494976
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
4977+
.arg( wkbTypeString )
49504978
.arg( QgsPostgresConn::quotedValue( styleName.isEmpty() ? dsUri.table() : styleName ) );
49514979

4952-
res = conn->PQexec( checkQuery );
4980+
QgsPostgresResult res( conn->PQexec( checkQuery ) );
49534981
if ( res.PQntuples() > 0 )
49544982
{
49554983
if ( QMessageBox::question( nullptr, QObject::tr( "Save style in database" ),
@@ -4972,8 +5000,10 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
49725000
" AND f_table_schema=%7"
49735001
" AND f_table_name=%8"
49745002
" AND f_geometry_column=%9"
4975-
" AND styleName=%10" )
5003+
" AND styleName=%10"
5004+
" AND type=%2" )
49765005
.arg( useAsDefault ? "true" : "false" )
5006+
.arg( wkbTypeString )
49775007
.arg( QgsPostgresConn::quotedValue( styleDescription.isEmpty() ? QDateTime::currentDateTime().toString() : styleDescription ) )
49785008
.arg( "CURRENT_USER" )
49795009
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
@@ -4993,11 +5023,14 @@ bool QgsPostgresProviderMetadata::saveStyle( const QString &uri, const QString &
49935023
" WHERE f_table_catalog=%1"
49945024
" AND f_table_schema=%2"
49955025
" AND f_table_name=%3"
4996-
" AND f_geometry_column=%4" )
5026+
" AND f_geometry_column=%4"
5027+
" AND type=%5" )
49975028
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
49985029
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
49995030
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
5000-
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) );
5031+
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
5032+
.arg( wkbTypeString );
5033+
50015034
sql = QStringLiteral( "BEGIN; %1; %2; COMMIT;" ).arg( removeDefaultSql, sql );
50025035
}
50035036

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

5081+
QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );
5082+
50485083
QString selectQmlQuery = QString( "SELECT styleQML"
50495084
" FROM layer_styles"
50505085
" WHERE f_table_catalog=%1"
50515086
" AND f_table_schema=%2"
50525087
" AND f_table_name=%3"
50535088
" AND f_geometry_column %4"
5089+
" AND type=%5"
50545090
" ORDER BY CASE WHEN useAsDefault THEN 1 ELSE 2 END"
50555091
",update_time DESC LIMIT 1" )
50565092
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
50575093
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
50585094
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
5059-
.arg( geomColumnExpr );
5095+
.arg( geomColumnExpr )
5096+
.arg( wkbTypeString );
50605097

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

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

5123+
QString wkbTypeString = QgsPostgresConn::quotedValue( QgsWkbTypes::geometryDisplayString( QgsWkbTypes::geometryType( dsUri.wkbType() ) ) );
5124+
50865125
QString selectRelatedQuery = QString( "SELECT id,styleName,description"
50875126
" FROM layer_styles"
50885127
" WHERE f_table_catalog=%1"
50895128
" AND f_table_schema=%2"
50905129
" AND f_table_name=%3"
50915130
" AND f_geometry_column=%4"
5131+
" AND type=%5"
50925132
" ORDER BY useasdefault DESC, update_time DESC" )
50935133
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
50945134
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
50955135
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
5096-
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) );
5136+
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
5137+
.arg( wkbTypeString );
50975138

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

51155156
QString selectOthersQuery = QString( "SELECT id,styleName,description"
51165157
" FROM layer_styles"
5117-
" WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4)"
5158+
" WHERE NOT (f_table_catalog=%1 AND f_table_schema=%2 AND f_table_name=%3 AND f_geometry_column=%4 AND type=%5)"
51185159
" ORDER BY update_time DESC" )
51195160
.arg( QgsPostgresConn::quotedValue( dsUri.database() ) )
51205161
.arg( QgsPostgresConn::quotedValue( dsUri.schema() ) )
51215162
.arg( QgsPostgresConn::quotedValue( dsUri.table() ) )
5122-
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) );
5163+
.arg( QgsPostgresConn::quotedValue( dsUri.geometryColumn() ) )
5164+
.arg( wkbTypeString );
51235165

51245166
result = conn->PQexec( selectOthersQuery );
51255167
if ( result.PQresultStatus() != PGRES_TUPLES_OK )

‎tests/src/python/test_provider_postgres.py

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1265,6 +1265,45 @@ def testStyle(self):
12651265
self.assertEqual(desclist, [])
12661266
self.assertEqual(errmsg, "")
12671267

1268+
def testStyleWithGeometryType(self):
1269+
"""Test saving styles with the additional geometry type
1270+
Layers are created from geometries_table
1271+
"""
1272+
1273+
myconn = 'service=\'qgis_test\''
1274+
if 'QGIS_PGTEST_DB' in os.environ:
1275+
myconn = os.environ['QGIS_PGTEST_DB']
1276+
1277+
# point layer
1278+
myPoint = QgsVectorLayer(myconn + ' sslmode=disable srid=4326 type=POINT table="qgis_test"."geometries_table" (geom) sql=', 'Point', 'postgres')
1279+
self.assertTrue(myPoint.isValid())
1280+
myPoint.saveStyleToDatabase('myPointStyle', '', False, '')
1281+
1282+
# polygon layer
1283+
myPolygon = QgsVectorLayer(myconn + ' sslmode=disable srid=4326 type=POLYGON table="qgis_test"."geometries_table" (geom) sql=', 'Poly', 'postgres')
1284+
self.assertTrue(myPoint.isValid())
1285+
myPolygon.saveStyleToDatabase('myPolygonStyle', '', False, '')
1286+
1287+
# how many
1288+
related_count, idlist, namelist, desclist, errmsg = myPolygon.listStylesInDatabase()
1289+
self.assertEqual(len(idlist), 2)
1290+
self.assertEqual(namelist, ['myPolygonStyle', 'myPointStyle'])
1291+
1292+
# raw psycopg2 query
1293+
self.assertTrue(self.con)
1294+
cur = self.con.cursor()
1295+
self.assertTrue(cur)
1296+
cur.execute("select stylename, type from layer_styles order by type")
1297+
self.assertEqual(cur.fetchall(), [('myPointStyle', 'Point'), ('myPolygonStyle', 'Polygon')])
1298+
cur.close()
1299+
1300+
# delete them
1301+
myPolygon.deleteStyleFromDatabase(idlist[1])
1302+
myPolygon.deleteStyleFromDatabase(idlist[0])
1303+
styles = myPolygon.listStylesInDatabase()
1304+
ids = styles[1]
1305+
self.assertEqual(len(ids), 0)
1306+
12681307
def testHasMetadata(self):
12691308
# views don't have metadata
12701309
vl = QgsVectorLayer('{} table="qgis_test"."{}" key="pk" sql='.format(self.dbconn, 'bikes_view'), "bikes_view", "postgres")

0 commit comments

Comments
 (0)
Please sign in to comment.