Skip to content

Commit

Permalink
postgres provider: use set application_name (fixes r15280) and verify…
Browse files Browse the repository at this point in the history 
… that pg_is_in_recovery() is false

git-svn-id: http://svn.osgeo.org/qgis/trunk@15284 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
jef committed Feb 28, 2011
1 parent de2ffe1 commit ebc2629
Show file tree
Hide file tree
Showing 5 changed files with 141 additions and 82 deletions.
4 changes: 2 additions & 2 deletions src/app/postgres/qgspgnewconnection.cpp
View file
Expand Up @@ -178,7 +178,7 @@ void QgsPgNewConnection::testConnection()
QString conninfo = uri.connectionInfo();
QgsDebugMsg( "PQconnectdb(\"" + conninfo + "\");" );

PGconn *pd = PQconnectdb(( conninfo + " application_name='Quantum GIS'" ).toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
PGconn *pd = PQconnectdb( conninfo.toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
// check the connection status
if ( PQstatus( pd ) != CONNECTION_OK )
{
Expand All @@ -205,7 +205,7 @@ void QgsPgNewConnection::testConnection()
uri.setPassword( password );

QgsDebugMsg( "PQconnectdb(\"" + uri.connectionInfo() + "\");" );
pd = PQconnectdb(( uri.connectionInfo() + " application_name='Quantum GIS'" ).toLocal8Bit() );
pd = PQconnectdb( uri.connectionInfo().toLocal8Bit() );
}

if ( PQstatus( pd ) == CONNECTION_OK )
Expand Down
37 changes: 31 additions & 6 deletions src/app/postgres/qgspgsourceselect.cpp
View file
Expand Up @@ -51,11 +51,18 @@ QgsPgSourceSelect::QgsPgSourceSelect( QWidget *parent, Qt::WFlags fl )
{
setupUi( this );

mBuildQueryButton = new QPushButton( tr( "&Build query" ) );
mBuildQueryButton->setToolTip( tr( "Build query" ) );
buttonBox->addButton( mBuildQueryButton, QDialogButtonBox::ActionRole );
connect( mBuildQueryButton, SIGNAL( clicked() ), this, SLOT( buildQuery() ) );
mBuildQueryButton->setDisabled( true );

mAddButton = new QPushButton( tr( "&Add" ) );
buttonBox->addButton( mAddButton, QDialogButtonBox::ActionRole );
connect( mAddButton, SIGNAL( clicked() ), this, SLOT( addTables() ) );

QPushButton *pb = new QPushButton( tr( "&Save" ) );
QPushButton *pb;
pb = new QPushButton( tr( "&Save" ) );
pb->setToolTip( tr( "Save connections" ) );
buttonBox->addButton( pb, QDialogButtonBox::ActionRole );
connect( pb, SIGNAL( clicked() ), this, SLOT( saveClicked() ) );
Expand Down Expand Up @@ -208,14 +215,14 @@ void QgsPgSourceSelect::on_cbxAllowGeometrylessTables_stateChanged( int )
on_btnConnect_clicked();
}

void QgsPgSourceSelect::on_btnBuildQuery_clicked()
void QgsPgSourceSelect::buildQuery()
{
setSql( mTablesTreeView->currentIndex() );
}

void QgsPgSourceSelect::on_mTablesTreeView_clicked( const QModelIndex &index )
{
btnBuildQuery->setEnabled( index.parent().isValid() );
mBuildQueryButton->setEnabled( index.parent().isValid() );
}

void QgsPgSourceSelect::on_mTablesTreeView_doubleClicked( const QModelIndex &index )
Expand Down Expand Up @@ -472,7 +479,7 @@ void QgsPgSourceSelect::on_btnConnect_clicked()

m_privConnInfo = m_connInfo;

pd = PQconnectdb(( m_privConnInfo + " application_name='Quantum GIS'" ).toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
pd = PQconnectdb( m_privConnInfo.toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
// check the connection status
if ( PQstatus( pd ) != CONNECTION_OK )
{
Expand All @@ -493,7 +500,7 @@ void QgsPgSourceSelect::on_btnConnect_clicked()

m_privConnInfo = uri.connectionInfo();
QgsDebugMsg( "connecting " + m_privConnInfo );
pd = PQconnectdb(( m_privConnInfo + " application_name='Quantum GIS'" ).toLocal8Bit() );
pd = PQconnectdb( m_privConnInfo.toLocal8Bit() );
}

if ( PQstatus( pd ) == CONNECTION_OK )
Expand All @@ -508,6 +515,16 @@ void QgsPgSourceSelect::on_btnConnect_clicked()
// tell the DB that we want text encoded in UTF8
PQsetClientEncoding( pd, QString( "UNICODE" ).toLocal8Bit() );

PGresult *res = PQexec( pd, "SET application_name='Quantum GIS'" );

if ( !res || PQresultStatus( res ) != PGRES_COMMAND_OK )
{
PQclear( res );
res = PQexec( pd, "ROLLBACK" );
}

PQclear( res );

// get the list of suitable tables and columns and populate the UI
geomCol details;

Expand Down Expand Up @@ -963,11 +980,19 @@ void QgsGeomColumnTypeThread::getLayerTypes()
{
mStopped = false;

PGconn *pd = PQconnectdb(( mConnInfo + " application_name='Quantum GIS'" ).toLocal8Bit() );
PGconn *pd = PQconnectdb( mConnInfo.toLocal8Bit() );
if ( PQstatus( pd ) == CONNECTION_OK )
{
PQsetClientEncoding( pd, QString( "UNICODE" ).toLocal8Bit() );

PGresult *res = PQexec( pd, "SET application_name='Quantum GIS'" );
if ( !res || PQresultStatus( res ) != PGRES_COMMAND_OK )
{
PQclear( res );
res = PQexec( pd, "ROLLBACK" );
}
PQclear( res );

for ( uint i = 0; i < schemas.size() && !mStopped; i++ )
{
QString query = QString( "select distinct "
Expand Down
3 changes: 2 additions & 1 deletion src/app/postgres/qgspgsourceselect.h
View file
Expand Up @@ -119,6 +119,7 @@ class QgsPgSourceSelect : public QDialog, private Ui::QgsDbSourceSelectBase
public slots:
//! Determines the tables the user selected and closes the dialog
void addTables();
void buildQuery();

/*! Connects to the database using the stored connection parameters.
* Once connected, available layers are displayed.
Expand All @@ -129,7 +130,6 @@ class QgsPgSourceSelect : public QDialog, private Ui::QgsDbSourceSelectBase
void on_btnNew_clicked();
//! Opens a dialog to edit an existing connection
void on_btnEdit_clicked();
void on_btnBuildQuery_clicked();
//! Deletes the selected connection
void on_btnDelete_clicked();
//! Saves the selected connections to file
Expand Down Expand Up @@ -187,6 +187,7 @@ class QgsPgSourceSelect : public QDialog, private Ui::QgsDbSourceSelectBase
QgsDbFilterProxyModel mProxyModel;

QString layerURI( const QModelIndex &index );
QPushButton *mBuildQueryButton;
QPushButton *mAddButton;
};

Expand Down
13 changes: 12 additions & 1 deletion src/plugins/spit/qgsspit.cpp
View file
Expand Up @@ -410,7 +410,7 @@ void QgsSpit::dbConnect()
password,
( QgsDataSourceURI::SSLmode ) settings.value( key + "/sslmode", QgsDataSourceURI::SSLprefer ).toInt() );

conn = PQconnectdb(( uri.connectionInfo() + " application_name='Quantum GIS'" ).toUtf8() );
conn = PQconnectdb( uri.connectionInfo().toUtf8() );
}

if ( conn == NULL || PQstatus( conn ) != CONNECTION_OK )
Expand All @@ -423,6 +423,17 @@ void QgsSpit::dbConnect()
}
}

if ( conn )
{
PGresult *res = PQexec( conn, "SET application_name='Quantum GIS'" );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
PQclear( res );
res = PQexec( conn, "ROLLBACK" );
}
PQclear( res );
}

schema_list.clear();
schema_list << "public";

Expand Down
166 changes: 94 additions & 72 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -213,7 +213,7 @@ QgsPostgresProvider::Conn *QgsPostgresProvider::Conn::connectDb( const QString &

QgsDebugMsg( QString( "New postgres connection for " ) + conninfo );

PGconn *pd = PQconnectdb(( conninfo + " application_name='Quantum GIS'" ).toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
PGconn *pd = PQconnectdb( conninfo.toLocal8Bit() ); // use what is set based on locale; after connecting, use Utf8
// check the connection status
if ( PQstatus( pd ) != CONNECTION_OK )
{
Expand All @@ -236,7 +236,7 @@ QgsPostgresProvider::Conn *QgsPostgresProvider::Conn::connectDb( const QString &
uri.setPassword( password );

QgsDebugMsg( "Connecting to " + uri.connectionInfo() );
pd = PQconnectdb(( uri.connectionInfo() + " application_name='Quantum GIS'" ).toLocal8Bit() );
pd = PQconnectdb( uri.connectionInfo().toLocal8Bit() );
}

if ( PQstatus( pd ) == CONNECTION_OK )
Expand Down Expand Up @@ -284,6 +284,11 @@ QgsPostgresProvider::Conn *QgsPostgresProvider::Conn::connectDb( const QString &

connections.insert( conninfo, conn );

if ( !conn->PQexecNR( "SET application_name='Quantum GIS'" ) )
{
conn->PQexecNR( "ROLLBACK" );
}

/* Check to see if we have GEOS support and if not, warn the user about
the problems they will see :) */
QgsDebugMsg( "Checking for GEOS support" );
Expand Down Expand Up @@ -989,91 +994,108 @@ bool QgsPostgresProvider::hasSufficientPermsAndCapabilities()
return false;
}

if ( connectionRO->pgVersion() >= 80400 )
{
sql = QString( "SELECT "
"has_table_privilege(%1,'DELETE'),"
"has_any_column_privilege(%1,'UPDATE'),"
"%2"
"has_table_privilege(%1,'INSERT'),"
"current_schema()" )
.arg( quotedValue( mQuery ) )
.arg( geometryColumn.isNull()
? QString( "'f'," )
: QString( "has_column_privilege(%1,%2,'UPDATE')," )
.arg( quotedValue( mQuery ) )
.arg( quotedValue( geometryColumn ) )
);
}
else
{
sql = QString( "SELECT "
"has_table_privilege(%1,'DELETE'),"
"has_table_privilege(%1,'UPDATE'),"
"has_table_privilege(%1,'UPDATE'),"
"has_table_privilege(%1,'INSERT'),"
"current_schema()" )
.arg( quotedValue( mQuery ) );
}
bool inRecovery = false;

testAccess = connectionRO->PQexec( sql );
if ( PQresultStatus( testAccess ) != PGRES_TUPLES_OK )
if ( connectionRO->pgVersion() >= 90000 )
{
showMessageBox( tr( "Unable to access relation" ),
tr( "Unable to determine table access privileges for the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
.arg( mQuery )
.arg( QString::fromUtf8( PQresultErrorMessage( testAccess ) ) )
.arg( sql ) );
return false;
testAccess = connectionRO->PQexec( "SELECT pg_is_in_recovery()" );
if ( PQresultStatus( testAccess ) != PGRES_TUPLES_OK || QString::fromUtf8( PQgetvalue( testAccess, 0, 0 ) ) == "t" )
{
showMessageBox( tr( "PostgreSQL in recovery" ),
tr( "PostgreSQL is still in recovery after a database crash\n(or you are connected to a (read-only) slave).\nWrite accesses will be denied." ) );
inRecovery = true;
}
}

// postgres has fast access to features at id (thanks to primary key / unique index)
// the latter flag is here just for compatibility
enabledCapabilities = QgsVectorDataProvider::SelectAtId | QgsVectorDataProvider::SelectGeometryAtId;

if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 0 ) ) == "t" )
if ( !inRecovery )
{
// DELETE
enabledCapabilities |= QgsVectorDataProvider::DeleteFeatures;
}
if ( connectionRO->pgVersion() >= 80400 )
{
sql = QString( "SELECT "
"has_table_privilege(%1,'DELETE'),"
"has_any_column_privilege(%1,'UPDATE'),"
"%2"
"has_table_privilege(%1,'INSERT'),"
"current_schema()" )
.arg( quotedValue( mQuery ) )
.arg( geometryColumn.isNull()
? QString( "'f'," )
: QString( "has_column_privilege(%1,%2,'UPDATE')," )
.arg( quotedValue( mQuery ) )
.arg( quotedValue( geometryColumn ) )
);
}
else
{
sql = QString( "SELECT "
"has_table_privilege(%1,'DELETE'),"
"has_table_privilege(%1,'UPDATE'),"
"has_table_privilege(%1,'UPDATE'),"
"has_table_privilege(%1,'INSERT'),"
"current_schema()" )
.arg( quotedValue( mQuery ) );
}

if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 1 ) ) == "t" )
{
// UPDATE
enabledCapabilities |= QgsVectorDataProvider::ChangeAttributeValues;
}
testAccess = connectionRO->PQexec( sql );
if ( PQresultStatus( testAccess ) != PGRES_TUPLES_OK )
{
showMessageBox( tr( "Unable to access relation" ),
tr( "Unable to determine table access privileges for the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
.arg( mQuery )
.arg( QString::fromUtf8( PQresultErrorMessage( testAccess ) ) )
.arg( sql ) );
return false;
}

if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 2 ) ) == "t" )
{
// UPDATE
enabledCapabilities |= QgsVectorDataProvider::ChangeGeometries;
}

if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 3 ) ) == "t" )
{
// INSERT
enabledCapabilities |= QgsVectorDataProvider::AddFeatures;
}
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 0 ) ) == "t" )
{
// DELETE
enabledCapabilities |= QgsVectorDataProvider::DeleteFeatures;
}

mCurrentSchema = QString::fromUtf8( PQgetvalue( testAccess, 0, 4 ) );
if ( mCurrentSchema == mSchemaName )
{
mUri.clearSchema();
}
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 1 ) ) == "t" )
{
// UPDATE
enabledCapabilities |= QgsVectorDataProvider::ChangeAttributeValues;
}

if ( mSchemaName == "" )
mSchemaName = mCurrentSchema;
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 2 ) ) == "t" )
{
// UPDATE
enabledCapabilities |= QgsVectorDataProvider::ChangeGeometries;
}

sql = QString( "SELECT 1 FROM pg_class,pg_namespace WHERE "
"pg_class.relnamespace=pg_namespace.oid AND "
"pg_get_userbyid(relowner)=current_user AND "
"relname=%1 AND nspname=%2" )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( mSchemaName ) );
testAccess = connectionRO->PQexec( sql );
if ( PQresultStatus( testAccess ) == PGRES_TUPLES_OK && PQntuples( testAccess ) == 1 )
{
enabledCapabilities |= QgsVectorDataProvider::AddAttributes | QgsVectorDataProvider::DeleteAttributes;
if ( QString::fromUtf8( PQgetvalue( testAccess, 0, 3 ) ) == "t" )
{
// INSERT
enabledCapabilities |= QgsVectorDataProvider::AddFeatures;
}

mCurrentSchema = QString::fromUtf8( PQgetvalue( testAccess, 0, 4 ) );
if ( mCurrentSchema == mSchemaName )
{
mUri.clearSchema();
}

if ( mSchemaName == "" )
mSchemaName = mCurrentSchema;

sql = QString( "SELECT 1 FROM pg_class,pg_namespace WHERE "
"pg_class.relnamespace=pg_namespace.oid AND "
"pg_get_userbyid(relowner)=current_user AND "
"relname=%1 AND nspname=%2" )
.arg( quotedValue( mTableName ) )
.arg( quotedValue( mSchemaName ) );
testAccess = connectionRO->PQexec( sql );
if ( PQresultStatus( testAccess ) == PGRES_TUPLES_OK && PQntuples( testAccess ) == 1 )
{
enabledCapabilities |= QgsVectorDataProvider::AddAttributes | QgsVectorDataProvider::DeleteAttributes;
}
}
}
else
Expand Down

0 comments on commit ebc2629

Please sign in to comment.