Skip to content

Commit 0f5ea53

Browse files
wonder-skwonder-sk
committedApr 7, 2018
Implement project storage for PostgreSQL + tests of the storage
·
ltr-3_40final-3_2_0
1 parent 5963028 commit 0f5ea53

10 files changed

+382
-0
lines changed
 

‎python/core/qgsprojectstorageregistry.sip.in

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,11 @@ This is a singleton that should be accessed through :py:func:`QgsApplication.pro
2626
QgsProjectStorageRegistry();
2727
~QgsProjectStorageRegistry();
2828

29+
QgsProjectStorage *projectStorageFromType( const QString &type );
30+
%Docstring
31+
Returns storage implementation if the storage type matches one. Returns null pointer otherwise (it is a normal file)
32+
%End
33+
2934
QgsProjectStorage *projectStorageFromUri( const QString &uri );
3035
%Docstring
3136
Returns storage implementation if the URI matches one. Returns null pointer otherwise (it is a normal file)

‎src/core/qgsprojectstorageregistry.cpp

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,11 @@ QgsProjectStorageRegistry::~QgsProjectStorageRegistry()
2727
qDeleteAll( mBackends.values() );
2828
}
2929

30+
QgsProjectStorage *QgsProjectStorageRegistry::projectStorageFromType( const QString &type )
31+
{
32+
return mBackends.value( type, nullptr );
33+
}
34+
3035
QgsProjectStorage *QgsProjectStorageRegistry::projectStorageFromUri( const QString &uri )
3136
{
3237
for ( auto it = mBackends.constBegin(); it != mBackends.constEnd(); ++it )

‎src/core/qgsprojectstorageregistry.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,9 @@ class CORE_EXPORT QgsProjectStorageRegistry
3535
QgsProjectStorageRegistry();
3636
~QgsProjectStorageRegistry();
3737

38+
//! Returns storage implementation if the storage type matches one. Returns null pointer otherwise (it is a normal file)
39+
QgsProjectStorage *projectStorageFromType( const QString &type );
40+
3841
//! Returns storage implementation if the URI matches one. Returns null pointer otherwise (it is a normal file)
3942
QgsProjectStorage *projectStorageFromUri( const QString &uri );
4043

‎src/core/qgsproviderregistry.cpp

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ typedef void buildsupportedrasterfilefilter_t( QString &fileFiltersString );
4242
typedef QString databaseDrivers_t();
4343
typedef QString directoryDrivers_t();
4444
typedef QString protocolDrivers_t();
45+
typedef void initProviderFunction_t();
4546
//typedef int dataCapabilities_t();
4647
//typedef QgsDataItem * dataItem_t(QString);
4748

@@ -228,6 +229,11 @@ void QgsProviderRegistry::init()
228229

229230
QgsDebugMsg( QString( "Checking %1: ...loaded OK (%2 file filters)" ).arg( myLib.fileName() ).arg( fileRasterFilters.split( ";;" ).count() ) );
230231
}
232+
233+
// call initProvider() if such function is available - allows provider to register its services to QGIS
234+
initProviderFunction_t *initFunc = reinterpret_cast< initProviderFunction_t * >( cast_to_fptr( myLib.resolve( "initProvider" ) ) );
235+
if ( initFunc )
236+
initFunc();
231237
}
232238
} // QgsProviderRegistry ctor
233239

‎src/providers/postgres/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,7 @@ SET(PG_SRCS
77
qgspostgresconnpool.cpp
88
qgspostgresdataitems.cpp
99
qgspostgresfeatureiterator.cpp
10+
qgspostgresprojectstorage.cpp
1011
qgspostgrestransaction.cpp
1112
qgspgtablemodel.cpp
1213
qgscolumntypethread.cpp

‎src/providers/postgres/qgspostgresprojectstorage.cpp

Lines changed: 188 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,188 @@
1+
#include "qgspostgresprojectstorage.h"
2+
3+
#include "qgspostgresconn.h"
4+
#include "qgspostgresconnpool.h"
5+
6+
#include "qgsreadwritecontext.h"
7+
8+
#include <QIODevice>
9+
#include <QUrl>
10+
11+
static bool _projectsTableExists( QgsPostgresConn &conn, const QString &schemaName )
12+
{
13+
QString tableName( "qgis_projects" );
14+
QString sql( QStringLiteral( "SELECT COUNT(*) FROM information_schema.tables WHERE table_name=%1 and table_schema=%2" )
15+
.arg( QgsPostgresConn::quotedValue( tableName ), QgsPostgresConn::quotedValue( schemaName ) )
16+
);
17+
QgsPostgresResult res( conn.PQexec( sql ) );
18+
return res.PQgetvalue( 0, 0 ).toInt() > 0;
19+
}
20+
21+
22+
QgsPostgresProjectStorage::QgsPostgresProjectStorage()
23+
{
24+
}
25+
26+
27+
QStringList QgsPostgresProjectStorage::listProjects( const QString &uri )
28+
{
29+
QStringList lst;
30+
31+
QgsPostgresProjectUri projectUri = parseUri( uri );
32+
if ( !projectUri.valid )
33+
return lst;
34+
35+
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( projectUri.connInfo.connectionInfo( false ) );
36+
37+
if ( _projectsTableExists( *conn, projectUri.schemaName ) )
38+
{
39+
QString sql( QStringLiteral( "SELECT name FROM %1.qgis_projects" ).arg( QgsPostgresConn::quotedIdentifier( projectUri.schemaName ) ) );
40+
QgsPostgresResult result( conn->PQexec( sql ) );
41+
if ( result.PQresultStatus() == PGRES_TUPLES_OK )
42+
{
43+
int count = result.PQntuples();
44+
for ( int i = 0; i < count; ++i )
45+
{
46+
QString name = result.PQgetvalue( i, 0 );
47+
lst << name;
48+
}
49+
}
50+
}
51+
52+
QgsPostgresConnPool::instance()->releaseConnection( conn );
53+
54+
return lst;
55+
}
56+
57+
58+
bool QgsPostgresProjectStorage::readProject( const QString &uri, QIODevice *device, QgsReadWriteContext &context )
59+
{
60+
QgsPostgresProjectUri projectUri = parseUri( uri );
61+
if ( !projectUri.valid )
62+
{
63+
context.pushMessage( "Invalid URI for PostgreSQL provider: " + uri, Qgis::Critical );
64+
return false;
65+
}
66+
67+
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( projectUri.connInfo.connectionInfo( false ) );
68+
69+
if ( !_projectsTableExists( *conn, projectUri.schemaName ) )
70+
{
71+
// TODO: write to context
72+
QgsPostgresConnPool::instance()->releaseConnection( conn );
73+
return false;
74+
}
75+
76+
bool ok = false;
77+
QString sql( QStringLiteral( "SELECT content FROM %1.qgis_projects WHERE name = %2" ).arg( QgsPostgresConn::quotedIdentifier( projectUri.schemaName ), QgsPostgresConn::quotedValue( projectUri.projectName ) ) );
78+
QgsPostgresResult result( conn->PQexec( sql ) );
79+
if ( result.PQresultStatus() == PGRES_TUPLES_OK )
80+
{
81+
if ( result.PQntuples() == 1 )
82+
{
83+
// TODO: would be useful to have QByteArray version of PQgetvalue to avoid bytearray -> string -> bytearray conversion
84+
QString hexEncodedContent( result.PQgetvalue( 0, 0 ) );
85+
QByteArray binaryContent( QByteArray::fromHex( hexEncodedContent.toUtf8() ) );
86+
device->write( binaryContent );
87+
device->seek( 0 );
88+
ok = true;
89+
}
90+
}
91+
92+
QgsPostgresConnPool::instance()->releaseConnection( conn );
93+
94+
return ok;
95+
}
96+
97+
98+
bool QgsPostgresProjectStorage::writeProject( const QString &uri, QIODevice *device, QgsReadWriteContext &context )
99+
{
100+
QgsPostgresProjectUri projectUri = parseUri( uri );
101+
if ( !projectUri.valid )
102+
{
103+
context.pushMessage( "Invalid URI for PostgreSQL provider: " + uri, Qgis::Critical );
104+
return false;
105+
}
106+
107+
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( projectUri.connInfo.connectionInfo( false ) );
108+
109+
if ( !_projectsTableExists( *conn, projectUri.schemaName ) )
110+
{
111+
// try to create projects table
112+
QString sql = QStringLiteral( "CREATE TABLE %1.qgis_projects(name TEXT PRIMARY KEY, metadata JSONB, content BYTEA)" ).arg( projectUri.schemaName );
113+
QgsPostgresResult res( conn->PQexec( sql ) );
114+
if ( res.PQresultStatus() != PGRES_COMMAND_OK )
115+
{
116+
QString errCause = QObject::tr( "Unable to save project. 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( projectUri.connInfo.username() );
117+
context.pushMessage( errCause, Qgis::Critical );
118+
QgsPostgresConnPool::instance()->releaseConnection( conn );
119+
return false;
120+
}
121+
}
122+
123+
// read from device and write to the table
124+
QByteArray content = device->readAll();
125+
126+
QString metadata = "{ \"last_modified\": 123 }"; // TODO: real metadata
127+
128+
// TODO: would be useful to have QByteArray version of PQexec() to avoid bytearray -> string -> bytearray conversion
129+
QString sql( "INSERT INTO %1.qgis_projects VALUES (%2, %3, E'\\\\x" );
130+
sql = sql.arg( QgsPostgresConn::quotedIdentifier( projectUri.schemaName ),
131+
QgsPostgresConn::quotedValue( projectUri.projectName ),
132+
QgsPostgresConn::quotedValue( metadata ) );
133+
sql += QString::fromAscii( content.toHex() );
134+
sql += "') ON CONFLICT (name) DO UPDATE SET content = EXCLUDED.content, metadata = EXCLUDED.metadata;";
135+
136+
QgsPostgresResult res( conn->PQexec( sql ) );
137+
bool ok = res.PQresultStatus() == PGRES_COMMAND_OK;
138+
139+
QgsPostgresConnPool::instance()->releaseConnection( conn );
140+
141+
return ok;
142+
}
143+
144+
145+
bool QgsPostgresProjectStorage::removeProject( const QString &uri )
146+
{
147+
QgsPostgresProjectUri projectUri = parseUri( uri );
148+
if ( !projectUri.valid )
149+
return false;
150+
151+
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( projectUri.connInfo.connectionInfo( false ) );
152+
153+
bool removed = false;
154+
if ( _projectsTableExists( *conn, projectUri.schemaName ) )
155+
{
156+
QString sql( QStringLiteral( "DELETE FROM %1.qgis_projects WHERE name = %2" ).arg( QgsPostgresConn::quotedIdentifier( projectUri.schemaName ), QgsPostgresConn::quotedValue( projectUri.projectName ) ) );
157+
QgsPostgresResult res( conn->PQexec( sql ) );
158+
removed = res.PQresultStatus() == PGRES_COMMAND_OK;
159+
}
160+
161+
QgsPostgresConnPool::instance()->releaseConnection( conn );
162+
163+
return removed;
164+
}
165+
166+
167+
QgsPostgresProjectUri QgsPostgresProjectStorage::parseUri( const QString &uri )
168+
{
169+
QUrl u = QUrl::fromEncoded( uri.toUtf8() );
170+
QUrlQuery urlQuery( u.query() );
171+
172+
QgsPostgresProjectUri postUri;
173+
postUri.valid = u.isValid();
174+
175+
QString host = u.host();
176+
QString port = u.port() != -1 ? QString::number( u.port() ) : QString();
177+
QString username = u.userName();
178+
QString password = u.password();
179+
QgsDataSourceUri::SslMode sslMode = QgsDataSourceUri::SslPrefer;
180+
QString authConfigId; // TODO: ?
181+
QString dbName = urlQuery.queryItemValue( "dbname" );
182+
postUri.connInfo.setConnection( host, port, dbName, username, password, sslMode, authConfigId );
183+
// TODO: "service"
184+
185+
postUri.schemaName = urlQuery.queryItemValue( "schema" );
186+
postUri.projectName = urlQuery.queryItemValue( "project" );
187+
return postUri;
188+
}

‎src/providers/postgres/qgspostgresprojectstorage.h

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
#ifndef QGSPOSTGRESPROJECTSTORAGE_H
2+
#define QGSPOSTGRESPROJECTSTORAGE_H
3+
4+
#include "qgsprojectstorage.h"
5+
6+
#include "qgsdatasourceuri.h"
7+
8+
//! Stores information parsed from postgres project URI
9+
typedef struct
10+
{
11+
bool valid;
12+
13+
QgsDataSourceUri connInfo; // using only the bits about connection info (server, port, username, password, service, ssl mode)
14+
15+
QString dbName;
16+
QString schemaName;
17+
QString projectName;
18+
19+
} QgsPostgresProjectUri;
20+
21+
22+
//! Implements storage of QGIS projects inside a PostgreSQL table
23+
class QgsPostgresProjectStorage : public QgsProjectStorage
24+
{
25+
public:
26+
QgsPostgresProjectStorage();
27+
28+
virtual QString type() override { return QStringLiteral( "postgresql" ); }
29+
30+
virtual QStringList listProjects( const QString &uri ) override;
31+
32+
virtual bool readProject( const QString &uri, QIODevice *device, QgsReadWriteContext &context ) override;
33+
34+
virtual bool writeProject( const QString &uri, QIODevice *device, QgsReadWriteContext &context ) override;
35+
36+
virtual bool removeProject( const QString &uri ) override;
37+
38+
private:
39+
static QgsPostgresProjectUri parseUri( const QString &uri );
40+
};
41+
42+
#endif // QGSPOSTGRESPROJECTSTORAGE_H

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@
2121
#include "qgsgeometry.h"
2222
#include "qgsmessageoutput.h"
2323
#include "qgsmessagelog.h"
24+
#include "qgsprojectstorageregistry.h"
2425
#include "qgsrectangle.h"
2526
#include "qgscoordinatereferencesystem.h"
2627
#include "qgsxmlutils.h"
@@ -36,6 +37,7 @@
3637
#include "qgspostgresfeatureiterator.h"
3738
#include "qgspostgrestransaction.h"
3839
#include "qgspostgreslistener.h"
40+
#include "qgspostgresprojectstorage.h"
3941
#include "qgslogger.h"
4042
#include "qgsfeedback.h"
4143
#include "qgssettings.h"
@@ -4911,8 +4913,21 @@ QGISEXTERN QgsTransaction *createTransaction( const QString &connString )
49114913
return new QgsPostgresTransaction( connString );
49124914
}
49134915

4916+
4917+
QgsPostgresProjectStorage *gProjectStorage = nullptr; // when not null it is owned by QgsApplication::projectStorageRegistry()
4918+
4919+
QGISEXTERN void initProvider()
4920+
{
4921+
Q_ASSERT( !gProjectStorage );
4922+
gProjectStorage = new QgsPostgresProjectStorage;
4923+
QgsApplication::projectStorageRegistry()->registerProjectStorage( gProjectStorage ); // takes ownership
4924+
}
4925+
49144926
QGISEXTERN void cleanupProvider()
49154927
{
4928+
QgsApplication::projectStorageRegistry()->unregisterProjectStorage( gProjectStorage ); // destroys the object
4929+
gProjectStorage = nullptr;
4930+
49164931
QgsPostgresConnPool::cleanupInstance();
49174932
}
49184933

‎tests/src/python/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -226,6 +226,7 @@ IF (ENABLE_PGTEST)
226226
ADD_PYTHON_TEST(PyQgsPostgresProvider test_provider_postgres.py)
227227
ADD_PYTHON_TEST(PyQgsRelationEditWidget test_qgsrelationeditwidget.py)
228228
ADD_PYTHON_TEST(PyQgsVectorLayerTools test_qgsvectorlayertools.py)
229+
ADD_PYTHON_TEST(PyQgsProjectStoragePostgres test_project_storage_postgres.py)
229230
ADD_PYTHON_TEST(PyQgsAuthManagerPKIPostgresTest test_authmanager_pki_postgres.py)
230231
ADD_PYTHON_TEST(PyQgsAuthManagerPasswordPostgresTest test_authmanager_password_postgres.py)
231232
ADD_PYTHON_TEST(PyQgsAuthManagerOgrPostgresTest test_authmanager_ogr_postgres.py)

‎tests/src/python/test_project_storage_postgres.py

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,116 @@
1+
# -*- coding: utf-8 -*-
2+
"""QGIS Unit tests for the postgres project storage.
3+
4+
Note: to prepare the DB, you need to run the sql files specified in
5+
tests/testdata/provider/testdata_pg.sh
6+
7+
.. note:: This program is free software; you can redistribute it and/or modify
8+
it under the terms of the GNU General Public License as published by
9+
the Free Software Foundation; either version 2 of the License, or
10+
(at your option) any later version.
11+
12+
"""
13+
from builtins import next
14+
__author__ = 'Martin Dobias'
15+
__date__ = '2018-03-29'
16+
__copyright__ = 'Copyright 2018, The QGIS Project'
17+
# This will get replaced with a git SHA1 when you do a git archive
18+
__revision__ = '$Format:%H$'
19+
20+
import qgis # NOQA
21+
import psycopg2
22+
23+
import os
24+
import time
25+
26+
from qgis.core import (
27+
QgsApplication,
28+
QgsVectorLayer,
29+
QgsProject,
30+
)
31+
from qgis.testing import start_app, unittest
32+
from utilities import unitTestDataPath
33+
34+
QGISAPP = start_app()
35+
TEST_DATA_DIR = unitTestDataPath()
36+
37+
38+
class TestPyQgsProjectStoragePostgres(unittest.TestCase):
39+
40+
@classmethod
41+
def setUpClass(cls):
42+
"""Run before all tests"""
43+
cls.dbconn = 'dbname=\'qgis_test\''
44+
if 'QGIS_PGTEST_DB' in os.environ:
45+
cls.dbconn = os.environ['QGIS_PGTEST_DB']
46+
# Create test layers
47+
cls.vl = QgsVectorLayer(cls.dbconn + ' sslmode=disable key=\'pk\' srid=4326 type=POINT table="qgis_test"."someData" (geom) sql=', 'test', 'postgres')
48+
assert cls.vl.isValid()
49+
cls.con = psycopg2.connect(cls.dbconn)
50+
51+
@classmethod
52+
def tearDownClass(cls):
53+
"""Run after all tests"""
54+
55+
def execSQLCommand(self, sql):
56+
self.assertTrue(self.con)
57+
cur = self.con.cursor()
58+
self.assertTrue(cur)
59+
cur.execute(sql)
60+
cur.close()
61+
self.con.commit()
62+
63+
def dropProjectsTable(self):
64+
self.execSQLCommand("DROP TABLE IF EXISTS qgis_test.qgis_projects;")
65+
66+
def testSaveLoadProject(self):
67+
68+
# TODO: respect QGIS_PGTEST_DB
69+
schema_uri = "postgresql:///?dbname=qgis_test&schema=qgis_test"
70+
project_uri = "postgresql:///?dbname=qgis_test&schema=qgis_test&project=abc"
71+
72+
self.dropProjectsTable() # make sure we have a clean start
73+
74+
prj = QgsProject()
75+
uri = self.vl.source()
76+
vl1 = QgsVectorLayer(uri, 'test', 'postgres')
77+
self.assertEqual(vl1.isValid(), True)
78+
prj.addMapLayer(vl1)
79+
80+
prj_storage = QgsApplication.projectStorageRegistry().projectStorageFromType("postgresql")
81+
self.assertTrue(prj_storage)
82+
83+
lst0 = prj_storage.listProjects(schema_uri)
84+
self.assertEqual(lst0, [])
85+
86+
# try to save project in the database
87+
88+
prj.setFileName(project_uri)
89+
res = prj.write()
90+
self.assertTrue(res)
91+
92+
lst1 = prj_storage.listProjects(schema_uri)
93+
self.assertEqual(lst1, ["abc"])
94+
95+
# now try to load the project back
96+
97+
prj2 = QgsProject()
98+
prj2.setFileName(project_uri)
99+
res = prj2.read()
100+
self.assertTrue(res)
101+
102+
self.assertEqual(len(prj2.mapLayers()), 1)
103+
104+
# try to remove the project
105+
106+
res = prj_storage.removeProject(project_uri)
107+
self.assertTrue(res)
108+
109+
lst2 = prj_storage.listProjects(schema_uri)
110+
self.assertEqual(lst2, [])
111+
112+
self.dropProjectsTable() # make sure we have a clean finish... "leave no trace"
113+
114+
115+
if __name__ == '__main__':
116+
unittest.main()

0 commit comments

Comments
 (0)
Please sign in to comment.