Skip to content

Commit

Permalink
[FEATURE] postgres provider: add support for generated identity colum…
Browse files Browse the repository at this point in the history 
…ns (implements #21745)
  • Loading branch information
@jef-n
jef-n committed Apr 7, 2019
1 parent 8b3f97f commit 59dd851
Show file tree
Hide file tree
Showing 2 changed files with 58 additions and 22 deletions.
79 changes: 57 additions & 22 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -758,7 +758,7 @@ bool QgsPostgresProvider::loadFields()
}


QMap<int, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap;
QMap<int, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap, identityMap;
QMap<int, QMap<int, int> > attTypeIdMap;
QMap<int, QMap<int, bool> > notNullMap, uniqueMap;
if ( result.PQnfields() > 0 )
Expand All @@ -785,14 +785,17 @@ bool QgsPostgresProvider::loadFields()
QString tableoidsFilter = '(' + tableoidsList.join( QStringLiteral( "," ) ) + ')';

// Collect formatted field types
sql = "SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid, attnotnull::int, indisunique::int"
" FROM pg_attribute"
" LEFT OUTER JOIN pg_attrdef ON attrelid=adrelid AND attnum=adnum"
sql = QStringLiteral(
"SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid, attnotnull::int, indisunique::int%1"
" FROM pg_attribute"
" LEFT OUTER JOIN pg_attrdef ON attrelid=adrelid AND attnum=adnum"

// find unique constraints if present. Text cast required to handle int2vector comparison. Distinct required as multiple unique constraints may exist
" LEFT OUTER JOIN ( SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique ) uniq ON attrelid=indrelid AND attnum::text=indkey::text "
// find unique constraints if present. Text cast required to handle int2vector comparison. Distinct required as multiple unique constraints may exist
" LEFT OUTER JOIN ( SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique ) uniq ON attrelid=indrelid AND attnum::text=indkey::text "

" WHERE attrelid IN %2"
).arg( connectionRO()->pgVersion() >= 100000 ? QStringLiteral( ", attidentity" ) : QString() ).arg( tableoidsFilter );

" WHERE attrelid IN " + tableoidsFilter;
QgsPostgresResult fmtFieldTypeResult( connectionRO()->PQexec( sql ) );
for ( int i = 0; i < fmtFieldTypeResult.PQntuples(); ++i )
{
Expand All @@ -804,18 +807,21 @@ bool QgsPostgresProvider::loadFields()
int attType = fmtFieldTypeResult.PQgetvalue( i, 5 ).toInt();
bool attNotNull = fmtFieldTypeResult.PQgetvalue( i, 6 ).toInt();
bool uniqueConstraint = fmtFieldTypeResult.PQgetvalue( i, 7 ).toInt();
QString attIdentity = connectionRO()->pgVersion() >= 100000 ? fmtFieldTypeResult.PQgetvalue( i, 8 ) : " ";
fmtFieldTypeMap[attrelid][attnum] = formatType;
descrMap[attrelid][attnum] = descr;
defValMap[attrelid][attnum] = defVal;
attTypeIdMap[attrelid][attnum] = attType;
notNullMap[attrelid][attnum] = attNotNull;
uniqueMap[attrelid][attnum] = uniqueConstraint;
identityMap[attrelid][attnum] = attIdentity.isEmpty() ? " " : attIdentity;
}
}
}

QSet<QString> fields;
mAttributeFields.clear();
mIdentityFields.clear();
for ( int i = 0; i < result.PQnfields(); i++ )
{
QString fieldName = result.PQfname( i );
Expand Down Expand Up @@ -1054,6 +1060,7 @@ bool QgsPostgresProvider::loadFields()
constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
newField.setConstraints( constraints );

mIdentityFields.insert( mAttributeFields.size(), identityMap[tableoid][attnum][0].toLatin1() );
mAttributeFields.append( newField );
}

Expand Down Expand Up @@ -1329,19 +1336,40 @@ bool QgsPostgresProvider::determinePrimaryKey()
QgsDebugMsg( QStringLiteral( "Relation is a table. Checking to see if it has an oid column." ) );

mPrimaryKeyAttrs.clear();
mPrimaryKeyType = PktUnknown;

// If there is an oid on the table, use that instead,
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );
if ( connectionRO()->pgVersion() >= 100000 )
{
// If there is an generated id on the table, use that instead,
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attidentity IN ('a','d') AND attrelid=regclass(%1) LIMIT 1" ).arg( quotedValue( mQuery ) );
res = connectionRO()->PQexec( sql );
if ( res.PQntuples() == 1 )
{
// Could warn the user here that performance will suffer if
// attribute isn't indexed (and that they may want to add a
// primary key to the table)
int idx = fieldNameIndex( res.PQgetvalue( 0, 0 ) );
mPrimaryKeyType = pkType( mAttributeFields.at( idx ) );
mPrimaryKeyAttrs << idx;
}
}

res = connectionRO()->PQexec( sql );
if ( res.PQntuples() == 1 )
if ( mPrimaryKeyType == PktUnknown )
{
// Could warn the user here that performance will suffer if
// oid isn't indexed (and that they may want to add a
// primary key to the table)
mPrimaryKeyType = PktOid;
// If there is an oid on the table, use that instead,
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );

res = connectionRO()->PQexec( sql );
if ( res.PQntuples() == 1 )
{
// Could warn the user here that performance will suffer if
// oid isn't indexed (and that they may want to add a
// primary key to the table)
mPrimaryKeyType = PktOid;
}
}
else

if ( mPrimaryKeyType == PktUnknown )
{
sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='ctid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );

Expand All @@ -1353,10 +1381,11 @@ bool QgsPostgresProvider::determinePrimaryKey()
QgsMessageLog::logMessage( tr( "Primary key is ctid - changing of existing features disabled (%1; %2)" ).arg( mGeometryColumn, mQuery ) );
mEnabledCapabilities &= ~( QgsVectorDataProvider::DeleteFeatures | QgsVectorDataProvider::ChangeAttributeValues | QgsVectorDataProvider::ChangeGeometries | QgsVectorDataProvider::ChangeFeatures );
}
else
{
QgsMessageLog::logMessage( tr( "The table has no column suitable for use as a key. QGIS requires a primary key, a PostgreSQL oid column or a ctid for tables." ), tr( "PostGIS" ) );
}
}

if ( mPrimaryKeyType == PktUnknown )
{
QgsMessageLog::logMessage( tr( "The table has no column suitable for use as a key. QGIS requires a primary key, a PostgreSQL oid column or a ctid for tables." ), tr( "PostGIS" ) );
}
}
else if ( type == Relkind::View || type == Relkind::MaterializedView )
Expand Down Expand Up @@ -2026,7 +2055,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )

// Prepare the INSERT statement
QString insert = QStringLiteral( "INSERT INTO %1(" ).arg( mQuery );
QString values = QStringLiteral( ") VALUES (" );
QString values;
QString delim;
int offset = 1;

Expand All @@ -2046,6 +2075,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
// is a sequence, and that none of the features have a value set for that
// column, then we can completely omit inserting it.
bool skipSinglePKField = false;
bool overrideIdentity = false;

if ( ( mPrimaryKeyType == PktInt || mPrimaryKeyType == PktFidMap || mPrimaryKeyType == PktUint64 ) )
{
Expand Down Expand Up @@ -2074,6 +2104,8 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
{
for ( int idx : mPrimaryKeyAttrs )
{
if ( mIdentityFields[idx] == 'a' )
overrideIdentity = true;
insert += delim + quotedIdentifier( field( idx ).name() );
values += delim + QStringLiteral( "$%1" ).arg( defaultValues.size() + offset );
delim = ',';
Expand Down Expand Up @@ -2118,6 +2150,9 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )

insert += delim + quotedIdentifier( fieldname );

if ( mIdentityFields[idx] == 'a' )
overrideIdentity = true;

QString defVal = defaultValueClause( idx );

if ( i == flist.size() )
Expand Down Expand Up @@ -2182,7 +2217,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist, Flags flags )
delim = ',';
}

insert += values + ')';
insert += QStringLiteral( ") %1VALUES (%2)" ).arg( overrideIdentity ? "OVERRIDING SYSTEM VALUE " : "" ).arg( values );

if ( !( flags & QgsFeatureSink::FastInsert ) )
{
Expand Down
1 change: 1 addition & 0 deletions src/providers/postgres/qgspostgresprovider.h
View file
Expand Up @@ -323,6 +323,7 @@ class QgsPostgresProvider : public QgsVectorDataProvider
QgsAttrPalIndexNameHash mAttrPalIndexName;

QgsFields mAttributeFields;
QHash<int, char> mIdentityFields;
QString mDataComment;

//! Data source URI struct for this layer
Expand Down

0 comments on commit 59dd851

Please sign in to comment.