postgres provider: add support for compound keys on views

qgis · Sep 4, 2015 · c5cce4b · c5cce4b · slarosa · Sep 4, 2015
1 parent e3b9a98
commit c5cce4b
Show file tree

Hide file tree

Showing 4 changed files with 155 additions and 43 deletions.
diff --git a/src/providers/postgres/qgspgsourceselect.cpp b/src/providers/postgres/qgspgsourceselect.cpp
@@ -76,7 +76,20 @@ QWidget *QgsPgSourceSelectDelegate::createEditor( QWidget *parent, const QStyleO
     if ( values.size() > 0 )
     {
       QComboBox *cb = new QComboBox( parent );
-      cb->addItems( values );
+
+      QStandardItemModel *model = new QStandardItemModel( values.size(), 1, cb );
+
+      int row = 0;
+      foreach ( QString value, values )
+      {
+        QStandardItem *item = new QStandardItem( value );
+        item->setFlags( Qt::ItemIsUserCheckable | Qt::ItemIsEnabled );
+        item->setData( Qt::Unchecked, Qt::CheckStateRole );
+        model->setItem( row++, 0, item );
+      }
+
+      cb->setModel( model );
+
       return cb;
     }
   }
@@ -101,8 +114,25 @@ void QgsPgSourceSelectDelegate::setEditorData( QWidget *editor, const QModelInde
     if ( index.column() == QgsPgTableModel::dbtmType )
       cb->setCurrentIndex( cb->findData( index.data( Qt::UserRole + 2 ).toInt() ) );
 
-    if ( index.column() == QgsPgTableModel::dbtmPkCol && !index.data( Qt::UserRole + 2 ).toString().isEmpty() )
-      cb->setCurrentIndex( cb->findText( index.data( Qt::UserRole + 2 ).toString() ) );
+    if ( index.column() == QgsPgTableModel::dbtmPkCol && !index.data( Qt::UserRole + 2 ).toStringList().isEmpty() )
+    {
+      QStringList cols = index.data( Qt::UserRole + 2 ).toStringList();
+
+      foreach ( QString col, cols )
+      {
+        QStandardItemModel *cbm = qobject_cast<QStandardItemModel*>( cb->model() );
+        for ( int idx = 0; idx < cbm->rowCount(); idx++ )
+        {
+          QStandardItem *item = cbm->item( idx, 0 );
+          if ( item->text() != col )
+            continue;
+
+          item->setData( Qt::Checked, Qt::CheckStateRole );
+          break;
+        }
+      }
+
+    }
   }
 
   QLineEdit *le = qobject_cast<QLineEdit*>( editor );
@@ -132,9 +162,17 @@ void QgsPgSourceSelectDelegate::setModelData( QWidget *editor, QAbstractItemMode
     }
     else if ( index.column() == QgsPgTableModel::dbtmPkCol )
     {
-      QString value( cb->currentText() );
-      model->setData( index, value.isEmpty() ? tr( "Select..." ) : value );
-      model->setData( index, value, Qt::UserRole + 2 );
+      QStandardItemModel *cbm = qobject_cast<QStandardItemModel*>( cb->model() );
+      QStringList cols;
+      for ( int idx = 0; idx < cbm->rowCount(); idx++ )
+      {
+        QStandardItem *item = cbm->item( idx, 0 );
+        if ( item->data( Qt::CheckStateRole ) == Qt::Checked )
+          cols << item->text();
+      }
+
+      model->setData( index, cols.isEmpty() ? tr( "Select..." ) : cols.join( ", " ) );
+      model->setData( index, cols, Qt::UserRole + 2 );
     }
   }
 
@@ -196,7 +234,7 @@ QgsPgSourceSelect::QgsPgSourceSelect( QWidget *parent, Qt::WindowFlags fl, bool
   mSearchColumnComboBox->addItem( tr( "Table" ) );
   mSearchColumnComboBox->addItem( tr( "Type" ) );
   mSearchColumnComboBox->addItem( tr( "Geometry column" ) );
-  mSearchColumnComboBox->addItem( tr( "Primary key column" ) );
+  mSearchColumnComboBox->addItem( tr( "Feature id" ) );
   mSearchColumnComboBox->addItem( tr( "SRID" ) );
   mSearchColumnComboBox->addItem( tr( "Sql" ) );
 
@@ -378,7 +416,7 @@ void QgsPgSourceSelect::on_mSearchColumnComboBox_currentIndexChanged( const QStr
   {
     mProxyModel.setFilterKeyColumn( QgsPgTableModel::dbtmGeomCol );
   }
-  else if ( text == tr( "Primary key column" ) )
+  else if ( text == tr( "Feature id" ) )
   {
     mProxyModel.setFilterKeyColumn( QgsPgTableModel::dbtmPkCol );
   }

diff --git a/src/providers/postgres/qgspgtablemodel.cpp b/src/providers/postgres/qgspgtablemodel.cpp
@@ -32,7 +32,7 @@ QgsPgTableModel::QgsPgTableModel()
   headerLabels << tr( "Data Type" );
   headerLabels << tr( "Spatial Type" );
   headerLabels << tr( "SRID" );
-  headerLabels << tr( "Primary Key" );
+  headerLabels << tr( "Feature id" );
   headerLabels << tr( "Select at id" );
   headerLabels << tr( "Sql" );
   setHorizontalHeaderLabels( headerLabels );
@@ -62,15 +62,15 @@ void QgsPgTableModel::addTableEntry( const QgsPostgresLayerProperty& layerProper
     QString tip;
     if ( wkbType == QGis::WKBUnknown )
     {
-      tip = tr( "Specify a geometry type" );
+      tip = tr( "Specify a geometry type in the '%1' column" ).arg( tr( "Data Type" ) );
     }
     else if ( wkbType != QGis::WKBNoGeometry && srid == INT_MIN )
     {
-      tip = tr( "Enter a SRID" );
+      tip = tr( "Enter a SRID into the '%1' column" ).arg( tr( "SRID" ) );
     }
     else if ( layerProperty.pkCols.size() > 0 )
     {
-      tip = tr( "Select a primary key" );
+      tip = tr( "Select columns in the '%1' column that uniquely identify features of this layer" ).arg( tr( "Feature id" ) );
     }
 
     QStandardItem *schemaNameItem = new QStandardItem( layerProperty.schemaName );
@@ -127,16 +127,18 @@ void QgsPgTableModel::addTableEntry( const QgsPostgresLayerProperty& layerProper
     {
       if ( tip.isEmpty() )
       {
-        item->setFlags( item->flags() | Qt::ItemIsSelectable | Qt::ItemIsEnabled );
+        item->setFlags( item->flags() | Qt::ItemIsSelectable );
         item->setToolTip( "" );
       }
       else
       {
         item->setFlags( item->flags() & ~Qt::ItemIsSelectable );
 
+        if ( item == schemaNameItem )
+          item->setData( QgsApplication::getThemeIcon( "/mIconWarn.png" ), Qt::DecorationRole );
+
         if ( item == schemaNameItem || item == tableItem || item == geomItem )
         {
-          item->setFlags( item->flags() & ~Qt::ItemIsEnabled );
           item->setToolTip( tip );
         }
       }
@@ -267,38 +269,49 @@ bool QgsPgTableModel::setData( const QModelIndex &idx, const QVariant &value, in
     QString tip;
     if ( wkbType == QGis::WKBUnknown )
     {
-      tip = tr( "Specify a geometry type" );
+      tip = tr( "Specify a geometry type in the '%1' column" ).arg( tr( "Data Type" ) );
     }
     else if ( wkbType != QGis::WKBNoGeometry )
     {
       bool ok;
       int srid = idx.sibling( idx.row(), dbtmSrid ).data().toInt( &ok );
 
       if ( !ok || srid == INT_MIN )
-        tip = tr( "Enter a SRID" );
+        tip = tr( "Enter a SRID into the '%1' column" ).arg( tr( "SRID" ) );
     }
 
     QStringList pkCols = idx.sibling( idx.row(), dbtmPkCol ).data( Qt::UserRole + 1 ).toStringList();
     if ( tip.isEmpty() && pkCols.size() > 0 )
     {
-      if ( !pkCols.contains( idx.sibling( idx.row(), dbtmPkCol ).data().toString() ) )
-        tip = tr( "Select a primary key" );
+      QSet<QString> s0( idx.sibling( idx.row(), dbtmPkCol ).data( Qt::UserRole + 2 ).toStringList().toSet() );
+      QSet<QString> s1( pkCols.toSet() );
+      if ( s0.intersect( s1 ).isEmpty() )
+        tip = tr( "Select columns in the '%1' column that uniquely identify features of this layer" ).arg( tr( "Feature id" ) );
     }
 
     for ( int i = 0; i < dbtmColumns; i++ )
     {
       QStandardItem *item = itemFromIndex( idx.sibling( idx.row(), i ) );
       if ( tip.isEmpty() )
       {
-        item->setFlags( item->flags() | Qt::ItemIsSelectable | Qt::ItemIsEnabled );
+        if ( i == dbtmSchema )
+        {
+          item->setData( QVariant(), Qt::DecorationRole );
+        }
+
+        item->setFlags( item->flags() | Qt::ItemIsSelectable );
         item->setToolTip( "" );
       }
       else
       {
         item->setFlags( item->flags() & ~Qt::ItemIsSelectable );
+
+        if ( i == dbtmSchema )
+          item->setData( QgsApplication::getThemeIcon( "/mIconWarn.png" ), Qt::DecorationRole );
+
         if ( i == dbtmSchema || i == dbtmTable || i == dbtmGeomCol )
         {
-          item->setFlags( item->flags() & ~Qt::ItemIsEnabled );
+          item->setFlags( item->flags() );
           item->setToolTip( tip );
         }
       }
@@ -325,10 +338,9 @@ QString QgsPgTableModel::layerURI( const QModelIndex &index, const QString& conn
   }
 
   QStandardItem *pkItem = itemFromIndex( index.sibling( index.row(), dbtmPkCol ) );
-  QString pkColumnName = pkItem->data( Qt::UserRole + 2 ).toString();
-
-  if ( pkItem->data( Qt::UserRole + 1 ).toStringList().size() > 0 &&
-       !pkItem->data( Qt::UserRole + 1 ).toStringList().contains( pkColumnName ) )
+  QSet<QString> s0( pkItem->data( Qt::UserRole + 1 ).toStringList().toSet() );
+  QSet<QString> s1( pkItem->data( Qt::UserRole + 2 ).toStringList().toSet() );
+  if ( !s0.isEmpty() && s0.intersect( s1 ).isEmpty() )
   {
     // no valid primary candidate selected
     QgsDebugMsg( "no pk candidate selected" );
@@ -358,7 +370,14 @@ QString QgsPgTableModel::layerURI( const QModelIndex &index, const QString& conn
   QString sql = index.sibling( index.row(), dbtmSql ).data( Qt::DisplayRole ).toString();
 
   QgsDataSourceURI uri( connInfo );
-  uri.setDataSource( schemaName, tableName, geomColumnName, sql, pkColumnName );
+
+  QStringList cols;
+  foreach ( QString col, s1 )
+  {
+    cols << QgsPostgresConn::quotedIdentifier( col );
+  }
+
+  uri.setDataSource( schemaName, tableName, geomColumnName, sql, cols.join( "," ) );
   uri.setUseEstimatedMetadata( useEstimatedMetadata );
   uri.setWkbType( wkbType );
   uri.setSrid( srid );

diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -1180,14 +1180,69 @@ bool QgsPostgresProvider::determinePrimaryKey()
 
         if ( !primaryKey.isEmpty() )
         {
-          int idx = fieldNameIndex( primaryKey );
+          QStringList cols;
 
-          if ( idx >= 0 )
+          // remove quotes from key list
+          if ( primaryKey.startsWith( '"' ) && primaryKey.endsWith( '"' ) )
+          {
+            int i = 1;
+            QString col;
+            while ( i < primaryKey.size() )
+            {
+              if ( primaryKey[i] == '"' )
+              {
+                if ( i + 1 < primaryKey.size() && primaryKey[i+1] == '"' )
+                {
+                  i++;
+                }
+                else
+                {
+                  cols << col;
+                  col = "";
+
+                  if ( ++i == primaryKey.size() )
+                    break;
+
+                  Q_ASSERT( primaryKey[i] == ',' );
+                  i++;
+                  Q_ASSERT( primaryKey[i] == '"' );
+                  i++;
+                  col = "";
+                  continue;
+                }
+              }
+
+              col += primaryKey[i++];
+            }
+          }
+          else if ( primaryKey.contains( "," ) )
+          {
+            cols = primaryKey.split( "," );
+          }
+          else
+          {
+            cols << primaryKey;
+            primaryKey = quotedIdentifier( primaryKey );
+          }
+
+          foreach ( QString col, cols )
+          {
+            int idx = fieldNameIndex( col );
+            if ( idx < 0 )
+            {
+              QgsMessageLog::logMessage( tr( "Key field '%1' for view not found." ).arg( col ), tr( "PostGIS" ) );
+              mPrimaryKeyAttrs.clear();
+              break;
+            }
+
+            mPrimaryKeyAttrs << idx;
+          }
+
+          if ( mPrimaryKeyAttrs.size() > 0 )
           {
             if ( mUseEstimatedMetadata || uniqueData( mQuery, primaryKey ) )
             {
-              mPrimaryKeyType = ( mAttributeFields[idx].type() == QVariant::Int || mAttributeFields[idx].type() == QVariant::LongLong ) ? pktInt : pktFidMap;
-              mPrimaryKeyAttrs << idx;
+              mPrimaryKeyType = ( mPrimaryKeyAttrs.size() == 1 && ( mAttributeFields[ mPrimaryKeyAttrs[0] ].type() == QVariant::Int || mAttributeFields[ mPrimaryKeyAttrs[0] ].type() == QVariant::LongLong ) ) ? pktInt : pktFidMap;
             }
             else
             {
@@ -1196,7 +1251,7 @@ bool QgsPostgresProvider::determinePrimaryKey()
           }
           else
           {
-            QgsMessageLog::logMessage( tr( "Key field '%1' for view not found." ).arg( primaryKey ), tr( "PostGIS" ) );
+            QgsMessageLog::logMessage( tr( "Keys for view undefined." ).arg( primaryKey ), tr( "PostGIS" ) );
           }
         }
         else
@@ -1269,12 +1324,12 @@ bool QgsPostgresProvider::determinePrimaryKey()
   return mValid;
 }
 
-bool QgsPostgresProvider::uniqueData( QString query, QString colName )
+bool QgsPostgresProvider::uniqueData( QString query, QString quotedColName )
 {
   Q_UNUSED( query );
   // Check to see if the given column contains unique data
   QString sql = QString( "SELECT count(distinct %1)=count(%1) FROM %2%3" )
-                .arg( quotedIdentifier( colName ) )
+                .arg( quotedColName )
                 .arg( mQuery )
                 .arg( filterWhereClause() );
 
@@ -3062,16 +3117,16 @@ QgsVectorLayerImport::ImportError QgsPostgresProvider::createEmptyLayer(
   }
   else
   {
-      // if the pk field's type is one of the postgres integer types,
-      // use the equivalent autoincremental type (serialN)
-      if ( primaryKeyType == "int2" || primaryKeyType == "int4" )
-      {
-        primaryKeyType = "serial";
-      }
-      else if ( primaryKeyType == "int8" )
-      {
-        primaryKeyType = "serial8";
-      }
+    // if the pk field's type is one of the postgres integer types,
+    // use the equivalent autoincremental type (serialN)
+    if ( primaryKeyType == "int2" || primaryKeyType == "int4" )
+    {
+      primaryKeyType = "serial";
+    }
+    else if ( primaryKeyType == "int8" )
+    {
+      primaryKeyType = "serial8";
+    }
   }
 
   try

diff --git a/src/ui/qgsdbsourceselectbase.ui b/src/ui/qgsdbsourceselectbase.ui
@@ -6,7 +6,7 @@
    <rect>
     <x>0</x>
     <y>0</y>
-    <width>592</width>
+    <width>773</width>
     <height>476</height>
    </rect>
   </property>