Skip to content

Commit

Permalink
Rework uniqueness detection for composite keys in HANA
Browse files Browse the repository at this point in the history
  • Loading branch information
@mrylov @nyalldawson
mrylov authored and nyalldawson committed May 4, 2021
1 parent 5dcfc8f commit e93a92e
Show file tree
Hide file tree
Showing 3 changed files with 122 additions and 56 deletions.
130 changes: 84 additions & 46 deletions src/providers/hana/qgshanaconnection.cpp
View file
Expand Up @@ -49,16 +49,51 @@ namespace
DatabaseMetaDataUnicodeRef dmd = conn.getDatabaseMetaDataUnicode();
ResultSetRef rsStats = dmd->getStatistics( nullptr, schemaName.toStdU16String().c_str(),
tableName.toStdU16String().c_str(), odbc::IndexType::UNIQUE, odbc::StatisticsAccuracy::ENSURE );
QMap<QString, QStringList> compositeKeys;
while ( rsStats->next() )
{
QString clmName = QgsHanaUtils::toQString( rsStats->getString( 9 /*COLUMN_NAME*/ ) );
if ( clmName.isEmpty() )
continue;
bool unique = rsStats->getShort( 4 /*NON_UNIQUE*/ ) == 0;
QString name = QgsHanaUtils::toQString( rsStats->getString( 9 /*COLUMN_NAME*/ ) );
if ( !name.isEmpty() )
ret.insert( name, unique );
QString indexName = QgsHanaUtils::toQString( rsStats->getString( 6 /*INDEX_NAME*/ ) );
ret.insert( clmName, unique );
compositeKeys[indexName].append( clmName );
}
rsStats->close();

for ( const QString &key : compositeKeys.keys() )
{
const QStringList indexColumns = compositeKeys.value( key );
if ( indexColumns.size() <= 1 )
continue;
for ( const QString &clmName : indexColumns )
ret[clmName] = false;
}

return ret;
}

int getSrid( PreparedStatementRef &stmt )
{
int srid = -1;
ResultSetRef rsSrid = stmt->executeQuery( );
while ( rsSrid->next() )
{
Int value = rsSrid->getInt( 1 );
if ( value.isNull() )
continue;
if ( srid == -1 )
srid = *value;
else if ( srid != *value )
{
srid = -1;
break;
}
}
rsSrid->close();
return srid;
}
}

QgsField AttributeField::toQgsField() const
Expand Down Expand Up @@ -662,9 +697,9 @@ void QgsHanaConnection::readQueryFields( const QString &schemaName, const QStrin
auto getColumnComments = [&clmComments, &conn = mConnection](
const QString & schemaName, const QString & tableName, const QString & columnName )
{
if ( schemaName.isEmpty() || tableName.isEmpty() )
if ( schemaName.isEmpty() || tableName.isEmpty() || columnName.isEmpty() )
return QString();
const QString key = QStringLiteral( "%1.%2" ).arg( schemaName, tableName );
const QString key = QStringLiteral( "%1.%2" ).arg( QgsHanaUtils::quotedIdentifier( schemaName ), QgsHanaUtils::quotedIdentifier( tableName ) );
if ( !clmComments.contains( key ) )
{
const char *sql = "SELECT COLUMN_NAME, COMMENTS FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = ? AND TABLE_NAME = ?";
Expand All @@ -688,9 +723,9 @@ void QgsHanaConnection::readQueryFields( const QString &schemaName, const QStrin
auto isColumnUnique = [&clmUniqueness, &conn = mConnection](
const QString & schemaName, const QString & tableName, const QString & columnName )
{
if ( schemaName.isEmpty() || tableName.isEmpty() )
if ( schemaName.isEmpty() || tableName.isEmpty() || columnName.isEmpty() )
return false;
const QString key = QStringLiteral( "%1.%2" ).arg( schemaName, tableName );
const QString key = QStringLiteral( "%1.%2" ).arg( QgsHanaUtils::quotedIdentifier( schemaName ), QgsHanaUtils::quotedIdentifier( tableName ) );
if ( !clmUniqueness.contains( key ) )
clmUniqueness.insert( key, getColumnsUniqueness( *conn, schemaName, tableName ) );
return clmUniqueness[key].value( columnName, false );
Expand All @@ -702,9 +737,13 @@ void QgsHanaConnection::readQueryFields( const QString &schemaName, const QStrin
ResultSetMetaDataUnicodeRef rsmd = stmt->getMetaDataUnicode();
for ( unsigned short i = 1; i <= rsmd->getColumnCount(); ++i )
{
QString baseTableName = QString::fromStdU16String( rsmd->getBaseTableName( i ) );
QString baseColumnName = QString::fromStdU16String( rsmd->getBaseColumnName( i ) );

QString schema = QString::fromStdU16String( rsmd->getSchemaName( i ) );
if ( schema.isEmpty() )
schema = schemaName;

AttributeField field;
field.schemaName = schema;
field.tableName = QString::fromStdU16String( rsmd->getTableName( i ) );
Expand All @@ -714,13 +753,16 @@ void QgsHanaConnection::readQueryFields( const QString &schemaName, const QStrin
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 = static_cast<int>( rsmd->getScale( i ) );
if ( field.isGeometry() )
field.srid = getColumnSrid( schema, field.tableName, field.name );
// As field comments cannot be retrieved via ODBC, we get it from SYS.TABLE_COLUMNS.
field.comment = getColumnComments( schema, field.tableName, field.name );

if ( !schema.isEmpty() )
{
field.isUnique = isColumnUnique( schema, baseTableName, baseColumnName );
// As field comments cannot be retrieved via ODBC, we get it from SYS.TABLE_COLUMNS.
field.comment = getColumnComments( schema, baseTableName, baseColumnName );
// We skip determing srid, as query layers don't use it.
}

callback( field );
}
Expand All @@ -739,20 +781,16 @@ void QgsHanaConnection::readTableFields( const QString &schemaName, const QStrin
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 sql = QStringLiteral( "SELECT * FROM %1.%2" )
.arg( QgsHanaUtils::quotedIdentifier( schemaName ), QgsHanaUtils::quotedIdentifier( tableName ) );
PreparedStatementRef stmt = prepareStatement( sql );
ResultSetMetaDataUnicodeRef rsmd = stmt->getMetaDataUnicode();
for ( unsigned short i = 1; i <= rsmd->getColumnCount(); ++i )
{
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 );
}
QString name = QString::fromStdU16String( rsmd->getColumnName( i ) );
bool isAutoIncrement = rsmd->isAutoIncrement( i );
clmAutoIncrement[key].insert( name, isAutoIncrement );
}
rsSpecialColumns->close();
}
return clmAutoIncrement[key].value( columnName, false );
};
Expand Down Expand Up @@ -783,7 +821,8 @@ void QgsHanaConnection::readTableFields( const QString &schemaName, const QStrin
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" );
QString isNullable = rsColumns->getString( 18/*IS_NULLABLE*/ );
field.isNullable = ( isNullable == QLatin1String( "YES" ) || isNullable == QLatin1String( "TRUE" ) );
field.isAutoIncrement = isColumnAutoIncrement( field.name );
field.isUnique = isColumnUnique( field.name );
if ( field.isGeometry() )
Expand Down Expand Up @@ -945,27 +984,6 @@ int QgsHanaConnection::getColumnSrid( const QString &schemaName, const QString &
if ( columnName.isEmpty() )
return -1;

auto getSrid = []( PreparedStatementRef & stmt )
{
int srid = -1;
ResultSetRef rsSrid = stmt->executeQuery( );
while ( rsSrid->next() )
{
Int value = rsSrid->getInt( 1 );
if ( value.isNull() )
continue;
if ( srid == -1 )
srid = *value;
else if ( srid != *value )
{
srid = -1;
break;
}
}
rsSrid->close();
return srid;
};

try
{
PreparedStatementRef stmt = mConnection->prepareStatement( "SELECT SRS_ID FROM SYS.ST_GEOMETRY_COLUMNS "
Expand Down Expand Up @@ -994,6 +1012,26 @@ int QgsHanaConnection::getColumnSrid( const QString &schemaName, const QString &
}
}

int QgsHanaConnection::getColumnSrid( const QString &sql, const QString &columnName )
{
if ( columnName.isEmpty() )
return -1;

try
{
QString query = QStringLiteral( "SELECT %1.ST_SRID() FROM (%2) WHERE %1 IS NOT NULL LIMIT %3" )
.arg( QgsHanaUtils::quotedIdentifier( columnName ),
sql,
QString::number( GEOMETRIES_SELECT_LIMIT ) );
PreparedStatementRef stmt = mConnection->prepareStatement( QgsHanaUtils::toUtf16( query ) );
return getSrid( stmt );
}
catch ( const Exception &ex )
{
throw QgsHanaException( ex.what() );
}
}

QgsHanaResultSetRef QgsHanaConnection::getColumns( const QString &schemaName, const QString &tableName, const QString &fieldName )
{
try
Expand Down
17 changes: 9 additions & 8 deletions src/providers/hana/qgshanaconnection.h
View file
Expand Up @@ -31,15 +31,15 @@ struct AttributeField
QString schemaName;
QString tableName;
QString name;
short type;
int srid;
short type = 0;
int srid = -1;
QString typeName;
int size;
int precision;
bool isAutoIncrement;
bool isNullable;
bool isSigned;
bool isUnique;
int size = 0;
int precision = 0;
bool isAutoIncrement = false;
bool isNullable = false;
bool isSigned = false;
bool isUnique = false;
QString comment;

bool isGeometry() const { return type == 29812; /* ST_GEOMETRY, ST_POINT */ }
Expand Down Expand Up @@ -93,6 +93,7 @@ class QgsHanaConnection : public QObject
QgsWkbTypes::Type getColumnGeometryType( const QString &schemaName, const QString &tableName, const QString &columnName );
QString getColumnDataType( const QString &schemaName, const QString &tableName, const QString &columnName );
int getColumnSrid( const QString &schemaName, const QString &tableName, const QString &columnName );
int getColumnSrid( const QString &sql, const QString &columnName );
QgsHanaResultSetRef getColumns( const QString &schemaName, const QString &tableName, const QString &fieldName );
bool isTable( const QString &schemaName, const QString &tableName );

Expand Down
31 changes: 29 additions & 2 deletions tests/src/python/test_provider_hana.py
View file
Expand Up @@ -26,6 +26,7 @@
QgsDataProvider,
QgsFeatureRequest,
QgsFeature,
QgsFieldConstraints,
QgsProviderRegistry,
QgsRectangle,
QgsSettings)
Expand Down Expand Up @@ -199,6 +200,32 @@ def testDefaultValue(self):
self.assertEqual(self.source.defaultValue(4), NULL)
self.source.setProviderProperty(QgsDataProvider.EvaluateDefaultValues, False)

def testCompositeUniqueConstraints(self):
create_sql = f'CREATE TABLE "{self.schemaName}"."unique_composite_constraints" ( ' \
'"ID" INTEGER PRIMARY KEY,' \
'"VAL1" INTEGER,' \
'"VAL2" INTEGER,' \
'"VAL3" INTEGER,' \
'UNIQUE (VAL1, VAL2))'
QgsHanaProviderUtils.executeSQL(self.conn, create_sql)

vl = self.createVectorLayer(f'table="{self.schemaName}"."unique_composite_constraints" sql=',
'testcompositeuniqueconstraints')

fields = vl.dataProvider().fields()
id_field_idx = fields.indexFromName('ID')
val1_field_idx = vl.fields().indexFromName('VAL1')
val2_field_idx = vl.fields().indexFromName('VAL2')
val3_field_idx = vl.fields().indexFromName('VAL3')
self.assertTrue(id_field_idx >= 0)
self.assertTrue(val1_field_idx >= 0)
self.assertTrue(val2_field_idx >= 0)
self.assertTrue(val3_field_idx >= 0)
self.assertTrue(bool(vl.fieldConstraints(id_field_idx) & QgsFieldConstraints.ConstraintUnique))
self.assertFalse(bool(vl.fieldConstraints(val1_field_idx) & QgsFieldConstraints.ConstraintUnique))
self.assertFalse(bool(vl.fieldConstraints(val2_field_idx) & QgsFieldConstraints.ConstraintUnique))
self.assertFalse(bool(vl.fieldConstraints(val3_field_idx) & QgsFieldConstraints.ConstraintUnique))

def testBooleanType(self):
create_sql = f'CREATE TABLE "{self.schemaName}"."boolean_type" ( ' \
'"id" INTEGER NOT NULL PRIMARY KEY,' \
Expand All @@ -222,7 +249,7 @@ def testDecimalAndFloatTypes(self):
'"decimal_field" DECIMAL(15,4),' \
'"float_field" FLOAT(12))'
insert_sql = f'INSERT INTO "{self.schemaName}"."decimal_and_float_type" ("id", "decimal_field", ' \
f'"float_field") VALUES (?, ?, ?) '
f'"float_field") VALUES (?, ?, ?) '
insert_args = [[1, 1.1234, 1.76543]]
self.prepareTestTable('decimal_and_float_type', create_sql, insert_sql, insert_args)

Expand Down Expand Up @@ -334,7 +361,7 @@ def testGeometryAttributes(self):
'GEOM1 ST_GEOMETRY(4326),' \
'GEOM2 ST_GEOMETRY(4326))'
insert_sql = f'INSERT INTO "{self.schemaName}"."geometry_attribute" (ID, GEOM1, GEOM2) ' \
f'VALUES (?, ST_GeomFromText(?, 4326), ST_GeomFromText(?, 4326)) '
f'VALUES (?, ST_GeomFromText(?, 4326), ST_GeomFromText(?, 4326)) '
insert_args = [[1, 'POINT (1 2)', 'LINESTRING (0 0,1 1)']]
self.prepareTestTable('geometry_attribute', create_sql, insert_sql, insert_args)

Expand Down

0 comments on commit e93a92e

Please sign in to comment.