QVector<QgsDataItem*> QgsPGConnectionItem::createChildren()
{
QgsDebugMsg( "Entered" );
QVector<QgsDataItem*> children;
QgsDataSourceURI uri = QgsPostgresConn::connUri( mName );
mConn = QgsPostgresConn::connectDb( uri.connectionInfo(), true );
if ( !mConn )
return children;
QVector<QgsPostgresLayerProperty> layerProperties;
if ( !mConn->supportedLayers( layerProperties, false, true, false ) )
{
children.append( new QgsErrorItem( this, tr( "Failed to retrieve layers" ), mPath + "/error" ) );
return children;
}
QgsGeomColumnTypeThread *columnTypeThread = 0;
foreach( QgsPostgresLayerProperty layerProperty, layerProperties )
{
QgsPGSchemaItem *schemaItem = mSchemaMap.value( layerProperty.schemaName, 0 );
if ( !schemaItem )
{
schemaItem = new QgsPGSchemaItem( this, layerProperty.schemaName, mPath + "/" + layerProperty.schemaName );
children.append( schemaItem );
mSchemaMap[ layerProperty.schemaName ] = schemaItem;
}
if ( layerProperty.type == "GEOMETRY" )
{
if ( !columnTypeThread )
{
QgsPostgresConn *conn = QgsPostgresConn::connectDb( uri.connectionInfo(), true /* readonly */ );
if ( conn )
{
columnTypeThread = new QgsGeomColumnTypeThread( conn, true /* use estimated metadata */ );
connect( columnTypeThread, SIGNAL( setLayerType( QgsPostgresLayerProperty ) ),
this, SLOT( setLayerType( QgsPostgresLayerProperty ) ) );
connect( this, SIGNAL( addGeometryColumn( QgsPostgresLayerProperty ) ),
columnTypeThread, SLOT( addGeometryColumn( QgsPostgresLayerProperty ) ) );
}
}
emit addGeometryColumn( layerProperty );
continue;
}
schemaItem->addLayer( layerProperty );
}
void QgsGeomColumnTypeThread::run()
{
if ( !mConn )
return;
mStopped = false;
int i = 0;
foreach ( QgsPostgresLayerProperty layerProperty, layerProperties )
{
if ( !mStopped )
{
emit progress( i++, layerProperties.size() );
emit progressMessage( tr( "Scanning column %1.%2.%3..." ).arg( layerProperty.schemaName ).arg( layerProperty.tableName ).arg( layerProperty.geometryColName ) );
mConn->retrieveLayerTypes( layerProperty, mUseEstimatedMetadata );
}
if ( mStopped )
{
layerProperty.type = "";
layerProperty.srid = "";
}
// Now tell the layer list dialog box...
emit setLayerType( layerProperty );
}
mConn->disconnect();
mConn = 0;
}
void QgsOracleColumnTypeThread::run()
{
mStopped = false;
QString conninfo = QgsOracleConn::toPoolName( QgsOracleConn::connUri( mName ) );
QgsOracleConn *conn = QgsOracleConnPool::instance()->acquireConnection( conninfo );
if ( !conn )
{
QgsDebugMsg( "Connection failed - " + conninfo );
mStopped = true;
return;
}
emit progressMessage( tr( "Retrieving tables of %1…" ).arg( mName ) );
QVector<QgsOracleLayerProperty> layerProperties;
if ( !conn->supportedLayers( layerProperties,
mSchema,
QgsOracleConn::geometryColumnsOnly( mName ),
QgsOracleConn::userTablesOnly( mName ),
mAllowGeometrylessTables ) ||
layerProperties.isEmpty() )
{
return;
}
int i = 0, n = layerProperties.size();
for ( QVector<QgsOracleLayerProperty>::iterator it = layerProperties.begin(),
end = layerProperties.end();
it != end; ++it )
{
QgsOracleLayerProperty &layerProperty = *it;
if ( !mStopped )
{
emit progress( i++, n );
emit progressMessage( tr( "Scanning column %1.%2.%3…" )
.arg( layerProperty.ownerName,
layerProperty.tableName,
layerProperty.geometryColName ) );
conn->retrieveLayerTypes( layerProperty, mUseEstimatedMetadata, QgsOracleConn::onlyExistingTypes( mName ) );
}
if ( mStopped )
{
layerProperty.types.clear();
layerProperty.srids.clear();
}
// Now tell the layer list dialog box...
emit setLayerType( layerProperty );
}
// store the list for later use (cache)
if ( !mStopped )
mLayerProperties = layerProperties;
emit progress( 0, 0 );
emit progressMessage( tr( "Table retrieval finished." ) );
QgsOracleConnPool::instance()->releaseConnection( conn );
}
void QgsOracleColumnTypeThread::run()
{
QgsDataSourceURI uri = QgsOracleConn::connUri( mName );
QgsOracleConn *conn = QgsOracleConn::connectDb( uri.connectionInfo() );
if ( !conn )
{
QgsDebugMsg( "Connection failed - " + uri.connectionInfo() );
return;
}
mStopped = false;
emit progressMessage( tr( "Retrieving tables of %1..." ).arg( mName ) );
QVector<QgsOracleLayerProperty> layerProperties;
if ( !conn->supportedLayers( layerProperties,
QgsOracleConn::geometryColumnsOnly( mName ),
QgsOracleConn::userTablesOnly( mName ),
mAllowGeometrylessTables ) ||
layerProperties.isEmpty() )
{
return;
}
int i = 0, n = layerProperties.size();
for ( QVector<QgsOracleLayerProperty>::iterator it = layerProperties.begin(),
end = layerProperties.end();
it != end; ++it )
{
QgsOracleLayerProperty &layerProperty = *it;
if ( !mStopped )
{
emit progress( i++, n );
emit progressMessage( tr( "Scanning column %1.%2.%3..." )
.arg( layerProperty.ownerName )
.arg( layerProperty.tableName )
.arg( layerProperty.geometryColName ) );
conn->retrieveLayerTypes( layerProperty, mUseEstimatedMetadata, QgsOracleConn::onlyExistingTypes( mName ) );
}
if ( mStopped )
{
layerProperty.types.clear();
layerProperty.srids.clear();
}
// Now tell the layer list dialog box...
emit setLayerType( layerProperty );
}
emit progress( 0, 0 );
emit progressMessage( tr( "Table retrieval finished." ) );
conn->disconnect();
}
void QgsMssqlGeomColumnTypeThread::run()
{
mStopped = false;
for ( QList<QgsMssqlLayerProperty>::iterator it = layerProperties.begin(),
end = layerProperties.end();
it != end; ++it )
{
QgsMssqlLayerProperty &layerProperty = *it;
if ( !mStopped )
{
QString table;
table = QStringLiteral( "%1[%2]" )
.arg( layerProperty.schemaName.isEmpty() ? QString() : QStringLiteral( "[%1]." ).arg( layerProperty.schemaName ),
layerProperty.tableName );
QString query = QString( "SELECT %3"
" UPPER([%1].STGeometryType()),"
" [%1].STSrid"
" FROM %2"
" WHERE [%1] IS NOT NULL %4"
" GROUP BY [%1].STGeometryType(), [%1].STSrid" )
.arg( layerProperty.geometryColName,
table,
mUseEstimatedMetadata ? "TOP 1" : "",
layerProperty.sql.isEmpty() ? QString() : QStringLiteral( " AND %1" ).arg( layerProperty.sql ) );
// issue the sql query
QSqlDatabase db = QgsMssqlConnection::getDatabase( mService, mHost, mDatabase, mUsername, mPassword );
if ( !QgsMssqlConnection::openDatabase( db ) )
{
QgsDebugMsg( db.lastError().text() );
continue;
}
QSqlQuery q = QSqlQuery( db );
q.setForwardOnly( true );
if ( !q.exec( query ) )
{
QgsDebugMsg( q.lastError().text() );
}
QString type;
QString srid;
if ( q.isActive() )
{
QStringList types;
QStringList srids;
while ( q.next() )
{
QString type = q.value( 0 ).toString().toUpper();
QString srid = q.value( 1 ).toString();
if ( type.isEmpty() )
continue;
types << type;
srids << srid;
}
type = types.join( QStringLiteral( "," ) );
srid = srids.join( QStringLiteral( "," ) );
}
layerProperty.type = type;
layerProperty.srid = srid;
}
else
{
layerProperty.type.clear();
layerProperty.srid.clear();
}
// Now tell the layer list dialog box...
emit setLayerType( layerProperty );
}
}
void QgsGeomColumnTypeThread::run()
{
QgsDataSourceUri uri = QgsPostgresConn::connUri( mName );
mConn = QgsPostgresConnPool::instance()->acquireConnection( uri.connectionInfo( false ) );
if ( !mConn )
{
QgsDebugMsg( "Connection failed - " + uri.connectionInfo( false ) );
return;
}
mStopped = false;
bool dontResolveType = QgsPostgresConn::dontResolveType( mName );
emit progressMessage( tr( "Retrieving tables of %1…" ).arg( mName ) );
QVector<QgsPostgresLayerProperty> layerProperties;
if ( !mConn->supportedLayers( layerProperties,
QgsPostgresConn::geometryColumnsOnly( mName ),
QgsPostgresConn::publicSchemaOnly( mName ),
mAllowGeometrylessTables ) ||
layerProperties.isEmpty() )
{
QgsPostgresConnPool::instance()->releaseConnection( mConn );
mConn = nullptr;
return;
}
int i = 0, n = layerProperties.size();
for ( QVector<QgsPostgresLayerProperty>::iterator it = layerProperties.begin(),
end = layerProperties.end();
it != end; ++it )
{
QgsPostgresLayerProperty &layerProperty = *it;
if ( !mStopped )
{
emit progress( i++, n );
emit progressMessage( tr( "Scanning column %1.%2.%3…" )
.arg( layerProperty.schemaName,
layerProperty.tableName,
layerProperty.geometryColName ) );
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, layerProperty.tableName ) );
continue;
}
mConn->retrieveLayerTypes( layerProperty, mUseEstimatedMetadata );
}
}
if ( mStopped )
{
layerProperty.types.clear();
layerProperty.srids.clear();
break;
}
// Now tell the layer list dialog box...
emit setLayerType( layerProperty );
}
emit progress( 0, 0 );
emit progressMessage( mStopped ? tr( "Table retrieval stopped." ) : tr( "Table retrieval finished." ) );
QgsPostgresConnPool::instance()->releaseConnection( mConn );
mConn = nullptr;
}
void SaGeomColTypeThread::getLayerTypes()
{
mStopped = false;
// establish read-only connection to the database
char errbuf[SACAPI_ERROR_SIZE];
sacapi_i32 code;
SqlAnyConnection *conn = SqlAnyConnection::connect( mConnInfo, true, code, errbuf, sizeof( errbuf ) );
if ( conn )
{
for ( uint i = 0; i < schemas.size() && !mStopped; i++ )
{
QString geomtype = geomtypes[i];
QString sridstr = sridstrs[i];
QString lineinterp = lineinterps[i];
QString sql;
QString quotedTableName;
QString fromStr;
SqlAnyStatement *stmt;
quotedTableName = QString( "%1.%2" )
.arg( quotedIdentifier( schemas[i] ) )
.arg( quotedIdentifier( tables[i] ) );
if ( mEstimateMetadata )
{
fromStr = QString( "(SELECT TOP %1 %2 FROM %3 WHERE %2 IS NOT NULL ) AS sampleGeoms " )
.arg( sGeomTypeSelectLimit )
.arg( quotedIdentifier( columns[i] ) )
.arg( quotedTableName );
}
else
{
fromStr = quotedTableName;
}
// retrieve distinct geometry types
if ( geomtype == "WAITING" )
{
QStringList types;
sql = QString(
"SELECT DISTINCT "
"CASE "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_POINT','ST_MULTIPOINT') THEN 'ST_POINT' "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_LINESTRING','ST_MULTILINESTRING') THEN 'ST_LINESTRING' "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_POLYGON','ST_MULTIPOLYGON') THEN 'ST_POLYGON' "
"ELSE 'ST_GEOMETRY' "
"END "
"FROM %2 " )
.arg( quotedIdentifier( columns[i] ) )
.arg( fromStr );
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
while ( stmt->fetchNext() )
{
QString type;
stmt->getString( 0, type );
types += type;
}
}
delete stmt;
if ( types.isEmpty() )
{
geomtype = "ST_GEOMETRY";
}
else
{
geomtype = types.join( "," );
}
}
// retrieve distinct srids
if ( sridstr == "WAITING" )
{
QStringList srids;
QStringList interps;
sql = QString(
"SELECT srid, "
"IF round_earth = 'Y' THEN 'ROUND EARTH' ELSE 'PLANAR' ENDIF "
"FROM ( "
"SELECT DISTINCT %1.ST_SRID() AS srid FROM %2 "
") AS sridlist, SYS.ST_SPATIAL_REFERENCE_SYSTEMS "
"WHERE srid = srs_id " )
.arg( quotedIdentifier( columns[i] ) )
.arg( fromStr );
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
while ( stmt->fetchNext() )
{
int srid;
QString interp;
stmt->getInt( 0, srid );
stmt->getString( 1, interp );
srids += QString::number( srid );
if ( !interps.contains( interp ) )
{
interps += interp;
}
}
}
delete stmt;
if ( srids.isEmpty() )
{
sridstr = "UNKNOWN";
lineinterp = "UNKNOWN";
}
else
{
sridstr = srids.join( "," );
lineinterp = interps.join( "," );
}
}
// Now tell the layer list dialog box...
emit setLayerType( schemas[i], tables[i], columns[i], geomtype, sridstr, lineinterp );
}
conn->release();
}
}
void SaSourceSelect::on_btnConnect_clicked()
{
if ( mColumnTypeThread )
{
mColumnTypeThread->stop();
mColumnTypeThread = 0;
}
QModelIndex rootItemIndex = mTableModel.indexFromItem( mTableModel.invisibleRootItem() );
mTableModel.removeRows( 0, mTableModel.rowCount( rootItemIndex ), rootItemIndex );
// populate the table list
QSettings settings;
// load the SQL Anywhere interface
if ( !SqlAnyConnection::initApi() )
{
QMessageBox::information( this,
tr( "Failed to load interface" ),
tr( SqlAnyConnection::failedInitMsg() ) );
return;
}
// compute connection information
QString key = "/SQLAnywhere/connections/" + cmbConnections->currentText();
mEstimateMetadata = settings.value( key + "/estimateMetadata", false ).toBool();
mOtherSchemas = settings.value( key + "/otherSchemas", false ).toBool();
mConnInfo = SqlAnyConnection::makeUri( key
, settings.value( key + "/host" ).toString()
, settings.value( key + "/port" ).toString()
, settings.value( key + "/server" ).toString()
, settings.value( key + "/database" ).toString()
, settings.value( key + "/parameters" ).toString()
, settings.value( key + "/username" ).toString()
, settings.value( key + "/password" ).toString()
, settings.value( key + "/simpleEncryption", false ).toBool()
, mEstimateMetadata );
SaDebugMsg( "Connection info: " + mConnInfo );
// establish read-only connection to the database
char errbuf[SACAPI_ERROR_SIZE];
sacapi_i32 code;
SqlAnyConnection *conn = SqlAnyConnection::connect( mConnInfo, true, code, errbuf, sizeof( errbuf ) );
if ( conn )
{
// get the list of suitable tables and columns and populate the UI
geomCol details;
if ( getTableInfo( conn->addRef(), mOtherSchemas ) )
{
// Start the thread that gets the geometry type for relations that
// may take a long time to return
if ( mColumnTypeThread != NULL )
{
connect( mColumnTypeThread, SIGNAL( setLayerType( QString, QString, QString, QString, QString, QString ) ),
this, SLOT( setLayerType( QString, QString, QString, QString, QString, QString ) ) );
// Do it in a thread.
mColumnTypeThread->start();
}
}
else
{
SaDebugMsg( "Unable to get list of spatially enabled tables "
"from the database" );
}
if ( cmbConnections->count() > 0 ) mAddButton->setEnabled( true );
conn->release();
}
else
{
QMessageBox::warning( this, tr( "Connection failed" ),
tr( "Connection to database %1 failed. "
"Check settings and try again.\n\n"
"SQL Anywhere error code: %2\n"
"Description: %3" )
.arg( settings.value( key + "/database" ).toString() )
.arg( code )
.arg( errbuf ) );
}
mTablesTreeView->sortByColumn( SaDbTableModel::dbtmTable, Qt::AscendingOrder );
mTablesTreeView->sortByColumn( SaDbTableModel::dbtmSchema, Qt::AscendingOrder );
//if we have only one schema item, expand it by default
int numTopLevelItems = mTableModel.invisibleRootItem()->rowCount();
if ( numTopLevelItems < 2 || mTableModel.tableCount() < 20 )
{
//expand all the toplevel items
for ( int i = 0; i < numTopLevelItems; ++i )
{
mTablesTreeView->expand( mProxyModel.mapFromSource( mTableModel.indexFromItem( mTableModel.invisibleRootItem()->child( i ) ) ) );
}
}
}