Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Rework field uniqueness detection in HANA
  • Loading branch information
mrylov authored and nyalldawson committed May 4, 2021
1 parent 767a2eb commit 3606fcf
Show file tree
Hide file tree
Showing 6 changed files with 140 additions and 27 deletions.
140 changes: 120 additions & 20 deletions src/providers/hana/qgshanaconnection.cpp
Expand Up @@ -41,6 +41,26 @@

using namespace odbc;

namespace
{
QMap<QString, bool> getColumnsUniqueness( Connection &conn, const QString &schemaName, const QString &tableName )
{
QMap<QString, bool> ret;
DatabaseMetaDataUnicodeRef dmd = conn.getDatabaseMetaDataUnicode();
ResultSetRef rsStats = dmd->getStatistics( nullptr, schemaName.toStdU16String().c_str(),
tableName.toStdU16String().c_str(), odbc::IndexType::UNIQUE, odbc::StatisticsAccuracy::ENSURE );
while ( rsStats->next() )
{
bool unique = rsStats->getShort( 4 /*NON_UNIQUE*/ ) == 0;
QString name = QgsHanaUtils::toQString( rsStats->getString( 9 /*COLUMN_NAME*/ ) );
if ( !name.isEmpty() )
ret.insert( name, unique );
}
rsStats->close();
return ret;
}
}

QgsField AttributeField::toQgsField() const
{
QVariant::Type fieldType;
Expand Down Expand Up @@ -104,12 +124,12 @@ QgsField AttributeField::toQgsField() const
}

QgsField field = QgsField( name, fieldType, typeName, size, precision, comment, QVariant::Invalid );
if ( !isNullable || isAutoIncrement )
if ( !isNullable || isUnique )
{
QgsFieldConstraints constraints;
if ( !isNullable )
constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
if ( isAutoIncrement )
if ( isUnique )
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
field.setConstraints( constraints );
}
Expand Down Expand Up @@ -635,36 +655,47 @@ void QgsHanaConnection::readLayerInfo( QgsHanaLayerProperty &layerProperty )
layerProperty.pkCols = getPrimaryKeyCandidates( layerProperty );
}

void QgsHanaConnection::readQueryFields( const QString &sql, const QString &schemaName,
void QgsHanaConnection::readQueryFields( const QString &schemaName, const QString &sql,
const std::function<void( const AttributeField &field )> &callback )
{
QMap<QString, QMap<QString, QString>> clmComments;
auto getColumnComments = [&clmComments, &conn = mConnection]( const QString & schemaName, const QString & tableName, const QString & columnName )
auto getColumnComments = [&clmComments, &conn = mConnection](
const QString & schemaName, const QString & tableName, const QString & columnName )
{
if ( schemaName.isEmpty() || tableName.isEmpty() )
return QString();

const QString key = QStringLiteral( "%1.%2" ).arg( schemaName, tableName );
if ( clmComments.contains( key ) )
return clmComments[key].value( columnName );

const char *sql = "SELECT COLUMN_NAME, COMMENTS FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME = ?";
PreparedStatementRef stmt = conn->prepareStatement( sql );
stmt->setNString( 1, NString( schemaName.toStdU16String() ) );
stmt->setNString( 2, NString( tableName.toStdU16String() ) );

ResultSetRef rsColumns = stmt->executeQuery();
while ( rsColumns->next() )
if ( !clmComments.contains( key ) )
{
QString name = QgsHanaUtils::toQString( rsColumns->getString( 1 ) );
QString comments = QgsHanaUtils::toQString( rsColumns->getString( 2 ) );
clmComments[key].insert( name, comments );
}
rsColumns->close();
const char *sql = "SELECT COLUMN_NAME, COMMENTS FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME = ?";
PreparedStatementRef stmt = conn->prepareStatement( sql );
stmt->setNString( 1, NString( schemaName.toStdU16String() ) );
stmt->setNString( 2, NString( tableName.toStdU16String() ) );

ResultSetRef rsColumns = stmt->executeQuery();
while ( rsColumns->next() )
{
QString name = QgsHanaUtils::toQString( rsColumns->getString( 1 ) );
QString comments = QgsHanaUtils::toQString( rsColumns->getString( 2 ) );
clmComments[key].insert( name, comments );
}
rsColumns->close();
}
return clmComments[key].value( columnName );
};

QMap<QString, QMap<QString, bool>> clmUniqueness;
auto isColumnUnique = [&clmUniqueness, &conn = mConnection](
const QString & schemaName, const QString & tableName, const QString & columnName )
{
if ( schemaName.isEmpty() || tableName.isEmpty() )
return false;
const QString key = QStringLiteral( "%1.%2" ).arg( schemaName, tableName );
if ( !clmUniqueness.contains( key ) )
clmUniqueness.insert( key, getColumnsUniqueness( *conn, schemaName, tableName ) );
return clmUniqueness[key].value( columnName, false );
};

try
{
PreparedStatementRef stmt = prepareStatement( sql );
Expand All @@ -683,6 +714,7 @@ void QgsHanaConnection::readQueryFields( const QString &sql, const QString &sche
field.isSigned = rsmd->isSigned( i );
field.isNullable = rsmd->isNullable( i );
field.isAutoIncrement = rsmd->isAutoIncrement( i );
field.isUnique = isColumnUnique( schema, field.tableName, field.name );
field.size = static_cast<int>( rsmd->getColumnLength( i ) );
field.precision = -1;
if ( field.isGeometry() )
Expand All @@ -699,6 +731,74 @@ void QgsHanaConnection::readQueryFields( const QString &sql, const QString &sche
}
}

void QgsHanaConnection::readTableFields( const QString &schemaName, const QString &tableName, const std::function<void( const AttributeField &field )> &callback )
{
QMap<QString, QMap<QString, bool>> clmAutoIncrement;
auto isColumnAutoIncrement = [&]( const QString & columnName )
{
const QString key = QStringLiteral( "%1.%2" ).arg( schemaName, tableName );
if ( !clmAutoIncrement.contains( key ) )
{
DatabaseMetaDataUnicodeRef dmd = mConnection->getDatabaseMetaDataUnicode();
ResultSetRef rsSpecialColumns = dmd->getSpecialColumns( RowIdentifierType::ROWVER, nullptr,
schemaName.toStdU16String().c_str(), tableName.toStdU16String().c_str(),
RowIdentifierScope::SESSION, ColumnNullableValue::NULLABLE );
while ( rsSpecialColumns->next() )
{
QString name = QgsHanaUtils::toQString( rsSpecialColumns->getString( 9 /*COLUMN_NAME*/ ) );
if ( !name.isEmpty() )
{
bool unique = rsSpecialColumns->getShort( 4 /*NON_UNIQUE*/ ) == 0;
clmAutoIncrement[key].insert( name, unique );
}
}
rsSpecialColumns->close();
}
return clmAutoIncrement[key].value( columnName, false );
};

QMap<QString, QMap<QString, bool>> clmUniqueness;
auto isColumnUnique = [&]( const QString & columnName )
{
const QString key = QStringLiteral( "%1.%2" ).arg( schemaName, tableName );
if ( !clmUniqueness.contains( key ) )
clmUniqueness.insert( key, getColumnsUniqueness( *mConnection, schemaName, tableName ) );
return clmUniqueness[key].value( columnName, false );
};

try
{
QgsHanaResultSetRef rsColumns = getColumns( schemaName, tableName, QStringLiteral( "%" ) );
while ( rsColumns->next() )
{
AttributeField field;
field.schemaName = rsColumns->getString( 2/*TABLE_SCHEM*/ );
field.tableName = rsColumns->getString( 3/*TABLE_NAME*/ );
field.name = rsColumns->getString( 4/*COLUMN_NAME*/ );
field.type = rsColumns->getShort( 5/*DATA_TYPE*/ );
field.typeName = rsColumns->getString( 6/*TYPE_NAME*/ );
field.size = rsColumns->getInt( 7/*COLUMN_SIZE*/ );
field.isSigned = field.type == SQLDataTypes::SmallInt || field.type == SQLDataTypes::Integer ||
field.type == SQLDataTypes::BigInt || field.type == SQLDataTypes::Decimal ||
field.type == SQLDataTypes::Numeric || field.type == SQLDataTypes::Real ||
field.type == SQLDataTypes::Float || field.type == SQLDataTypes::Double;
field.isNullable = rsColumns->getString( 18/*IS_NULLABLE*/ ) == QLatin1String( "TRUE" );
field.isAutoIncrement = isColumnAutoIncrement( field.name );
field.isUnique = isColumnUnique( field.name );
field.precision = -1;
if ( field.isGeometry() )
field.srid = getColumnSrid( schemaName, tableName, field.name );
field.comment = rsColumns->getString( 12/*REMARKS*/ );

callback( field );
}
}
catch ( const Exception &ex )
{
throw QgsHanaException( ex.what() );
}
}

QVector<QgsHanaSchemaProperty> QgsHanaConnection::getSchemas( const QString &ownerName )
{
QString sql = QStringLiteral( "SELECT SCHEMA_NAME, SCHEMA_OWNER FROM SYS.SCHEMAS WHERE "
Expand Down
4 changes: 3 additions & 1 deletion src/providers/hana/qgshanaconnection.h
Expand Up @@ -39,6 +39,7 @@ struct AttributeField
bool isAutoIncrement;
bool isNullable;
bool isSigned;
bool isUnique;
QString comment;

bool isGeometry() const { return type == 29812; /* ST_GEOMETRY, ST_POINT */ }
Expand Down Expand Up @@ -85,7 +86,8 @@ class QgsHanaConnection : public QObject
bool userTablesOnly = true,
const std::function<bool( const QgsHanaLayerProperty &layer )> &layerFilter = nullptr );
void readLayerInfo( QgsHanaLayerProperty &layerProperty );
void readQueryFields( const QString &sql, const QString &schemaName, const std::function<void( const AttributeField &field )> &callback );
void readQueryFields( const QString &schemaName, const QString &sql, const std::function<void( const AttributeField &field )> &callback );
void readTableFields( const QString &schemaName, const QString &tableName, const std::function<void( const AttributeField &field )> &callback );
QVector<QgsHanaSchemaProperty> getSchemas( const QString &ownerName );
QStringList getLayerPrimaryKey( const QString &schemaName, const QString &tableName );
QgsWkbTypes::Type getColumnGeometryType( const QString &schemaName, const QString &tableName, const QString &columnName );
Expand Down
5 changes: 4 additions & 1 deletion src/providers/hana/qgshanaprovider.cpp
Expand Up @@ -1385,7 +1385,10 @@ void QgsHanaProvider::readAttributeFields( QgsHanaConnection &conn )
mDefaultValues.insert( mAttributeFields.size() - 1, getColumnDefaultValue( schemaName, tableName, field.name ) );
};

conn.readQueryFields( buildQuery( QStringLiteral( "*" ) ), mSchemaName, processField );
if ( mIsQuery )
conn.readQueryFields( mSchemaName, buildQuery( QStringLiteral( "*" ) ), processField );
else
conn.readTableFields( mSchemaName, mTableName, processField );

determinePrimaryKey( conn );
}
Expand Down
6 changes: 1 addition & 5 deletions src/providers/hana/qgshanaproviderconnection.cpp
Expand Up @@ -411,11 +411,7 @@ QStringList QgsHanaProviderConnection::schemas( ) const
QgsFields QgsHanaProviderConnection::fields( const QString &schema, const QString &table ) const
{
QgsHanaConnectionRef conn = createConnection();

const QString geometryColumn = QgsDataSourceUri( uri() ).geometryColumn();
const QString sql = QStringLiteral( "SELECT * FROM %1.%2" )
.arg( QgsHanaUtils::quotedIdentifier( schema ),
QgsHanaUtils::quotedIdentifier( table ) );
try
{
QgsFields fields;
Expand All @@ -424,7 +420,7 @@ QgsFields QgsHanaProviderConnection::fields( const QString &schema, const QStrin
if ( field.name != geometryColumn )
fields.append( field.toQgsField() );
};
conn->readQueryFields( sql, schema, processField );
conn->readTableFields( schema, table, processField );
return fields;
}
catch ( const QgsHanaException &ex )
Expand Down
10 changes: 10 additions & 0 deletions src/providers/hana/qgshanaresultset.cpp
Expand Up @@ -87,6 +87,16 @@ double QgsHanaResultSet::getDouble( unsigned short columnIndex )
return *mResultSet->getDouble( columnIndex );
}

int QgsHanaResultSet::getInt( unsigned short columnIndex )
{
return *mResultSet->getInt( columnIndex );
}

short QgsHanaResultSet::getShort( unsigned short columnIndex )
{
return *mResultSet->getShort( columnIndex );
}

QString QgsHanaResultSet::getString( unsigned short columnIndex )
{
return QgsHanaUtils::toQString( mResultSet->getNString( columnIndex ) );
Expand Down
2 changes: 2 additions & 0 deletions src/providers/hana/qgshanaresultset.h
Expand Up @@ -47,6 +47,8 @@ class QgsHanaResultSet
bool next();

double getDouble( unsigned short columnIndex );
int getInt( unsigned short columnIndex );
short getShort( unsigned short columnIndex );
QString getString( unsigned short columnIndex );
QVariant getValue( unsigned short columnIndex );
QgsGeometry getGeometry( unsigned short columnIndex );
Expand Down

0 comments on commit 3606fcf

Please sign in to comment.