void QgsPgNewConnection::testConnection()
{
QgsDataSourceUri uri;
if ( !txtService->text().isEmpty() )
{
uri.setConnection( txtService->text(), txtDatabase->text(),
txtUsername->text(), txtPassword->text(),
( QgsDataSourceUri::SslMode ) cbxSSLmode->currentData().toInt(),
mAuthConfigSelect->configId() );
}
else
{
uri.setConnection( txtHost->text(), txtPort->text(), txtDatabase->text(),
txtUsername->text(), txtPassword->text(),
( QgsDataSourceUri::SslMode ) cbxSSLmode->currentData().toInt(),
mAuthConfigSelect->configId() );
}
QgsPostgresConn *conn = QgsPostgresConn::connectDb( uri.connectionInfo( false ), true );
if ( conn )
{
// Database successfully opened; we can now issue SQL commands.
bar->pushMessage( tr( "Connection to %1 was successful" ).arg( txtDatabase->text() ),
QgsMessageBar::INFO );
// free pg connection resources
conn->unref();
}
else
{
bar->pushMessage( tr( "Connection failed - consult message log for details." ),
QgsMessageBar::WARNING );
}
}
QVector<QgsDataItem*> QgsPGConnectionItem::createChildren()
{
QVector<QgsDataItem*>items;
QgsDataSourceUri uri = QgsPostgresConn::connUri( mName );
// TODO: wee need to cancel somehow acquireConnection() if deleteLater() was called on this item to avoid later credential dialog if connection failed
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( uri.connectionInfo( false ) );
if ( !conn )
{
items.append( new QgsErrorItem( this, tr( "Connection failed" ), mPath + "/error" ) );
QgsDebugMsg( "Connection failed - " + uri.connectionInfo( false ) );
return items;
}
QList<QgsPostgresSchemaProperty> schemas;
bool ok = conn->getSchemas( schemas );
QgsPostgresConnPool::instance()->releaseConnection( conn );
if ( !ok )
{
items.append( new QgsErrorItem( this, tr( "Failed to get schemas" ), mPath + "/error" ) );
return items;
}
Q_FOREACH ( const QgsPostgresSchemaProperty& schema, schemas )
{
QgsPGSchemaItem * schemaItem = new QgsPGSchemaItem( this, mName, schema.name, mPath + '/' + schema.name );
if ( !schema.description.isEmpty() )
{
schemaItem->setToolTip( schema.description );
}
items.append( schemaItem );
}
void QgsPGConnectionItem::createSchema()
{
QString schemaName = QInputDialog::getText( nullptr, tr( "Create Schema" ), tr( "Schema name:" ) );
if ( schemaName.isEmpty() )
return;
QgsDataSourceUri uri = QgsPostgresConn::connUri( mName );
QgsPostgresConn *conn = QgsPostgresConn::connectDb( uri.connectionInfo( false ), false );
if ( !conn )
{
QMessageBox::warning( nullptr, tr( "Create Schema" ), tr( "Unable to create schema." ) );
return;
}
//create the schema
QString sql = QStringLiteral( "CREATE SCHEMA %1" ).arg( QgsPostgresConn::quotedIdentifier( schemaName ) );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QMessageBox::warning( nullptr, tr( "Create Schema" ), tr( "Unable to create schema %1\n%2" ).arg( schemaName,
result.PQresultErrorMessage() ) );
conn->unref();
return;
}
conn->unref();
refresh();
// the parent should be updated
if ( mParent )
mParent->refreshConnections();
}
void QgsPGSchemaItem::renameSchema()
{
QgsNewNameDialog dlg( tr( "schema '%1'" ).arg( mName ), mName );
dlg.setWindowTitle( tr( "Rename Schema" ) );
if ( dlg.exec() != QDialog::Accepted || dlg.name() == mName )
return;
QString schemaName = QgsPostgresConn::quotedIdentifier( mName );
QgsDataSourceUri uri = QgsPostgresConn::connUri( mConnectionName );
QgsPostgresConn *conn = QgsPostgresConn::connectDb( uri.connectionInfo( false ), false );
if ( !conn )
{
QMessageBox::warning( nullptr, tr( "Rename Schema" ), tr( "Unable to rename schema." ) );
return;
}
//rename the schema
QString sql = QStringLiteral( "ALTER SCHEMA %1 RENAME TO %2" )
.arg( schemaName, QgsPostgresConn::quotedIdentifier( dlg.name() ) );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QMessageBox::warning( nullptr, tr( "Rename Schema" ), tr( "Unable to rename schema %1\n%2" ).arg( schemaName,
result.PQresultErrorMessage() ) );
conn->unref();
return;
}
conn->unref();
QMessageBox::information( nullptr, tr( "Rename Schema" ), tr( "Schema renamed successfully." ) );
if ( mParent )
mParent->refresh();
}
QVector<QgsDataItem*> QgsPGConnectionItem::createChildren()
{
QgsDebugMsg( "Entered" );
QVector<QgsDataItem*>items;
QgsDataSourceURI uri = QgsPostgresConn::connUri( mName );
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( uri.connectionInfo() );
if ( !conn )
{
items.append( new QgsErrorItem( this, tr( "Connection failed" ), mPath + "/error" ) );
QgsDebugMsg( "Connection failed - " + uri.connectionInfo() );
return items;
}
QVector<QgsPostgresLayerProperty> layerProperties;
bool ok = conn->supportedLayers( layerProperties,
QgsPostgresConn::geometryColumnsOnly( mName ),
QgsPostgresConn::publicSchemaOnly( mName ),
QgsPostgresConn::allowGeometrylessTables( mName ) );
QgsPostgresConnPool::instance()->releaseConnection( conn );
if ( !ok )
{
items.append( new QgsErrorItem( this, tr( "Failed to get schemas" ), mPath + "/error" ) );
return items;
}
QSet<QString> schemaNames;
foreach ( QgsPostgresLayerProperty layerProperty, layerProperties )
{
schemaNames.insert( layerProperty.schemaName );
}
void QgsPGLayerItem::renameLayer()
{
QString typeName = mLayerProperty.isView ? tr( "View" ) : tr( "Table" );
QString lowerTypeName = mLayerProperty.isView ? tr( "view" ) : tr( "table" );
QgsNewNameDialog dlg( tr( "%1 %2.%3" ).arg( lowerTypeName, mLayerProperty.schemaName, mLayerProperty.tableName ), mLayerProperty.tableName );
dlg.setWindowTitle( tr( "Rename %1" ).arg( typeName ) );
if ( dlg.exec() != QDialog::Accepted || dlg.name() == mLayerProperty.tableName )
return;
QString schemaName = mLayerProperty.schemaName;
QString tableName = mLayerProperty.tableName;
QString schemaTableName;
if ( !schemaName.isEmpty() )
{
schemaTableName = QgsPostgresConn::quotedIdentifier( schemaName ) + '.';
}
QString oldName = schemaTableName + QgsPostgresConn::quotedIdentifier( tableName );
QString newName = QgsPostgresConn::quotedIdentifier( dlg.name() );
QgsDataSourceUri dsUri( mUri );
QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
if ( !conn )
{
QMessageBox::warning( nullptr, tr( "Rename %1" ).arg( typeName ), tr( "Unable to rename %1." ).arg( lowerTypeName ) );
return;
}
//rename the layer
QString sql;
if ( mLayerProperty.isView )
{
sql = QStringLiteral( "ALTER %1 VIEW %2 RENAME TO %3" ).arg( mLayerProperty.relKind == QLatin1String( "m" ) ? QStringLiteral( "MATERIALIZED" ) : QString(),
oldName, newName );
}
else
{
sql = QStringLiteral( "ALTER TABLE %1 RENAME TO %2" ).arg( oldName, newName );
}
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QMessageBox::warning( nullptr, tr( "Rename %1" ).arg( typeName ), tr( "Unable to rename %1 %2\n%3" ).arg( lowerTypeName, mName,
result.PQresultErrorMessage() ) );
conn->unref();
return;
}
conn->unref();
if ( mParent )
mParent->refresh();
}
void QgsPgNewConnection::testConnection()
{
QgsDataSourceURI uri;
if ( !txtService->text().isEmpty() )
{
uri.setConnection( txtService->text(), txtDatabase->text(),
txtUsername->text(), txtPassword->text(),
( QgsDataSourceURI::SSLmode ) cbxSSLmode->itemData( cbxSSLmode->currentIndex() ).toInt(),
mAuthConfigSelect->configId() );
}
else
{
uri.setConnection( txtHost->text(), txtPort->text(), txtDatabase->text(),
txtUsername->text(), txtPassword->text(),
( QgsDataSourceURI::SSLmode ) cbxSSLmode->itemData( cbxSSLmode->currentIndex() ).toInt(),
mAuthConfigSelect->configId() );
}
QString conninfo = uri.connectionInfo();
QgsPostgresConn *conn = QgsPostgresConn::connectDb( conninfo, true );
if ( conn )
{
// Database successfully opened; we can now issue SQL commands.
QMessageBox::information( this,
tr( "Test connection" ),
tr( "Connection to %1 was successful" ).arg( txtDatabase->text() ) );
// free pg connection resources
conn->unref();
}
else
{
QMessageBox::information( this,
tr( "Test connection" ),
tr( "Connection failed - consult message log for details.\n\n" ) );
}
}
void QgsPGLayerItem::refreshMaterializedView()
{
if ( QMessageBox::question( nullptr, QObject::tr( "Refresh Materialized View" ),
QObject::tr( "Are you sure you want to refresh the materialized view %1.%2?\n\nThis will update all data within the table." ).arg( mLayerProperty.schemaName, mLayerProperty.tableName ),
QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
return;
QgsDataSourceUri dsUri( mUri );
QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
if ( !conn )
{
QMessageBox::warning( nullptr, tr( "Refresh View" ), tr( "Unable to refresh the view." ) );
return;
}
QString schemaName = mLayerProperty.schemaName;
QString tableName = mLayerProperty.tableName;
QString schemaTableName;
if ( !schemaName.isEmpty() )
{
schemaTableName = QgsPostgresConn::quotedIdentifier( schemaName ) + '.';
}
QString tableRef = schemaTableName + QgsPostgresConn::quotedIdentifier( tableName );
QString sql = QStringLiteral( "REFRESH MATERIALIZED VIEW CONCURRENTLY %1" ).arg( tableRef );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QMessageBox::warning( nullptr, tr( "Refresh View" ), tr( "Unable to refresh view %1\n%2" ).arg( mName,
result.PQresultErrorMessage() ) );
conn->unref();
return;
}
conn->unref();
QMessageBox::information( nullptr, tr( "Refresh View" ), tr( "Materialized view refreshed successfully." ) );
}
void QgsPGLayerItem::truncateTable()
{
if ( QMessageBox::question( nullptr, QObject::tr( "Truncate Table" ),
QObject::tr( "Are you sure you want to truncate %1.%2?\n\nThis will delete all data within the table." ).arg( mLayerProperty.schemaName, mLayerProperty.tableName ),
QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
return;
QgsDataSourceUri dsUri( mUri );
QgsPostgresConn *conn = QgsPostgresConn::connectDb( dsUri.connectionInfo( false ), false );
if ( !conn )
{
QMessageBox::warning( nullptr, tr( "Truncate Table" ), tr( "Unable to truncate table." ) );
return;
}
QString schemaName = mLayerProperty.schemaName;
QString tableName = mLayerProperty.tableName;
QString schemaTableName;
if ( !schemaName.isEmpty() )
{
schemaTableName = QgsPostgresConn::quotedIdentifier( schemaName ) + '.';
}
QString tableRef = schemaTableName + QgsPostgresConn::quotedIdentifier( tableName );
QString sql = QStringLiteral( "TRUNCATE TABLE %1" ).arg( tableRef );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_COMMAND_OK )
{
QMessageBox::warning( nullptr, tr( "Truncate Table" ), tr( "Unable to truncate %1\n%2" ).arg( mName,
result.PQresultErrorMessage() ) );
conn->unref();
return;
}
conn->unref();
QMessageBox::information( nullptr, tr( "Truncate Table" ), tr( "Table truncated successfully." ) );
}
void QgsPgNewConnection::testConnection()
{
QgsTemporaryCursorOverride cursorOverride( Qt::WaitCursor );
QgsDataSourceUri uri;
if ( !txtService->text().isEmpty() )
{
uri.setConnection( txtService->text(), txtDatabase->text(),
mAuthSettings->username(), mAuthSettings->password(),
( QgsDataSourceUri::SslMode ) cbxSSLmode->currentData().toInt(),
mAuthSettings->configId() );
}
else
{
uri.setConnection( txtHost->text(), txtPort->text(), txtDatabase->text(),
mAuthSettings->username(), mAuthSettings->password(),
( QgsDataSourceUri::SslMode ) cbxSSLmode->currentData().toInt(),
mAuthSettings->configId() );
}
QgsPostgresConn *conn = QgsPostgresConn::connectDb( uri.connectionInfo( false ), true );
if ( conn )
{
// Database successfully opened; we can now issue SQL commands.
bar->pushMessage( tr( "Connection to %1 was successful." ).arg( txtDatabase->text() ),
Qgis::Info );
// free pg connection resources
conn->unref();
}
else
{
bar->pushMessage( tr( "Connection failed - consult message log for details." ),
Qgis::Warning );
}
}
void QgsPGSchemaItem::deleteSchema()
{
// check if schema contains tables/views
QgsDataSourceUri uri = QgsPostgresConn::connUri( mConnectionName );
QgsPostgresConn *conn = QgsPostgresConn::connectDb( uri.connectionInfo( false ), false );
if ( !conn )
{
QMessageBox::warning( nullptr, tr( "Delete Schema" ), tr( "Unable to delete schema." ) );
return;
}
QString sql = QStringLiteral( "SELECT table_name FROM information_schema.tables WHERE table_schema='%1'" ).arg( mName );
QgsPostgresResult result( conn->PQexec( sql ) );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
QMessageBox::warning( nullptr, tr( "Delete Schema" ), tr( "Unable to delete schema." ) );
conn->unref();
return;
}
QStringList childObjects;
int maxListed = 10;
for ( int idx = 0; idx < result.PQntuples(); idx++ )
{
childObjects << result.PQgetvalue( idx, 0 );
QgsPostgresSchemaProperty schema;
if ( idx == maxListed - 1 )
break;
}
int count = result.PQntuples();
if ( count > 0 )
{
QString objects = childObjects.join( QStringLiteral( "\n" ) );
if ( count > maxListed )
{
objects += QStringLiteral( "\n[%1 additional objects not listed]" ).arg( count - maxListed );
}
if ( QMessageBox::question( nullptr, QObject::tr( "Delete Schema" ),
QObject::tr( "Schema '%1' contains objects:\n\n%2\n\nAre you sure you want to delete the schema and all these objects?" ).arg( mName, objects ),
QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
{
conn->unref();
return;
}
}
else
{
if ( QMessageBox::question( nullptr, QObject::tr( "Delete Schema" ),
QObject::tr( "Are you sure you want to delete the schema '%1'?" ).arg( mName ),
QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
return;
}
QString errCause;
bool res = ::deleteSchema( mName, uri, errCause, count > 0 );
if ( !res )
{
QMessageBox::warning( nullptr, tr( "Delete Schema" ), errCause );
}
else
{
QMessageBox::information( nullptr, tr( "Delete Schema" ), tr( "Schema deleted successfully." ) );
if ( mParent )
mParent->refresh();
}
}
QVector<QgsDataItem *> QgsPGSchemaItem::createChildren()
{
QVector<QgsDataItem *>items;
QgsDataSourceUri uri = QgsPostgresConn::connUri( mConnectionName );
QgsPostgresConn *conn = QgsPostgresConnPool::instance()->acquireConnection( uri.connectionInfo( false ) );
if ( !conn )
{
items.append( new QgsErrorItem( this, tr( "Connection failed" ), mPath + "/error" ) );
QgsDebugMsg( "Connection failed - " + uri.connectionInfo( false ) );
return items;
}
QVector<QgsPostgresLayerProperty> layerProperties;
bool ok = conn->supportedLayers( layerProperties,
QgsPostgresConn::geometryColumnsOnly( mConnectionName ),
QgsPostgresConn::publicSchemaOnly( mConnectionName ),
QgsPostgresConn::allowGeometrylessTables( mConnectionName ), mName );
if ( !ok )
{
items.append( new QgsErrorItem( this, tr( "Failed to get layers" ), mPath + "/error" ) );
QgsPostgresConnPool::instance()->releaseConnection( conn );
return items;
}
bool dontResolveType = QgsPostgresConn::dontResolveType( mConnectionName );
const auto constLayerProperties = layerProperties;
for ( QgsPostgresLayerProperty layerProperty : constLayerProperties )
{
if ( layerProperty.schemaName != mName )
continue;
if ( !layerProperty.geometryColName.isNull() &&
( layerProperty.types.value( 0, QgsWkbTypes::Unknown ) == QgsWkbTypes::Unknown ||
layerProperty.srids.value( 0, std::numeric_limits<int>::min() ) == std::numeric_limits<int>::min() ) )
{
if ( dontResolveType )
{
//QgsDebugMsg( QStringLiteral( "skipping column %1.%2 without type constraint" ).arg( layerProperty.schemaName ).arg( layerProperty.tableName ) );
continue;
}
conn->retrieveLayerTypes( layerProperty, true /* useEstimatedMetadata */ );
}
for ( int i = 0; i < layerProperty.size(); i++ )
{
QgsPGLayerItem *layerItem = createLayer( layerProperty.at( i ) );
if ( layerItem )
items.append( layerItem );
}
}
QgsPostgresConnPool::instance()->releaseConnection( conn );
QgsProjectStorage *storage = QgsApplication::projectStorageRegistry()->projectStorageFromType( "postgresql" );
if ( QgsPostgresConn::allowProjectsInDatabase( mConnectionName ) && storage )
{
QgsPostgresProjectUri postUri;
postUri.connInfo = uri;
postUri.schemaName = mName;
QString schemaUri = QgsPostgresProjectStorage::encodeUri( postUri );
const QStringList projectNames = storage->listProjects( schemaUri );
for ( const QString &projectName : projectNames )
{
QgsPostgresProjectUri projectUri( postUri );
projectUri.projectName = projectName;
items.append( new QgsProjectItem( this, projectName, QgsPostgresProjectStorage::encodeUri( projectUri ) ) );
}
}
return items;
}