[FEATURE] allow adding geometryless layers from postgres

git-svn-id: http://svn.osgeo.org/qgis/trunk/qgis@14248 c8812cc2-4d05-0410-92ff-de0c093fc19c
qgis · Sep 18, 2010 · 3f2fe7e · 3f2fe7e
1 parent 9945818
commit 3f2fe7e
Show file tree

Hide file tree

Showing 7 changed files with 138 additions and 36 deletions.
diff --git a/src/app/postgres/qgspgnewconnection.cpp b/src/app/postgres/qgspgnewconnection.cpp
@@ -58,7 +58,8 @@ QgsPgNewConnection::QgsPgNewConnection( QWidget *parent, const QString& connName
     txtPort->setText( port );
     cb_publicSchemaOnly->setChecked( settings.value( key + "/publicOnly", false ).toBool() );
     cb_geometryColumnsOnly->setChecked( settings.value( key + "/geometrycolumnsOnly", false ).toBool() );
-    // Ensure that cb_plublicSchemaOnly is set correctly
+    cb_allowGeometrylessTables->setChecked( settings.value( key + "/allowGeometrylessTables", true ).toBool() );
+    // Ensure that cb_publicSchemaOnly is set correctly
     on_cb_geometryColumnsOnly_clicked();
 
     cb_useEstimatedMetadata->setChecked( settings.value( key + "/estimatedMetadata", false ).toBool() );
@@ -125,6 +126,7 @@ void QgsPgNewConnection::accept()
   settings.setValue( baseKey + "/password", chkStorePassword->isChecked() ? txtPassword->text() : "" );
   settings.setValue( baseKey + "/publicOnly", cb_publicSchemaOnly->isChecked() );
   settings.setValue( baseKey + "/geometryColumnsOnly", cb_geometryColumnsOnly->isChecked() );
+  settings.setValue( baseKey + "/allowGeometrylessTables", cb_allowGeometrylessTables->isChecked() );
   settings.setValue( baseKey + "/sslmode", cbxSSLmode->itemData( cbxSSLmode->currentIndex() ).toInt() );
   settings.setValue( baseKey + "/saveUsername", chkStoreUsername->isChecked() ? "true" : "false" );
   settings.setValue( baseKey + "/savePassword", chkStorePassword->isChecked() ? "true" : "false" );

diff --git a/src/app/postgres/qgspgsourceselect.cpp b/src/app/postgres/qgspgsourceselect.cpp
@@ -362,9 +362,9 @@ QString QgsPgSourceSelect::layerURI( const QModelIndex &index )
     uri += QString( " estimatedmetadata=true" );
   }
 
-  uri += QString( " table=\"%1\".\"%2\" (%3) sql=%4" )
+  uri += QString( " table=\"%1\".\"%2\"%3 sql=%4" )
          .arg( schemaName ).arg( tableName )
-         .arg( geomColumnName )
+         .arg( geomColumnName.isEmpty() ? QString() : QString( " (%1)" ).arg( geomColumnName ) )
          .arg( sql );
 
   return uri;
@@ -429,6 +429,7 @@ void QgsPgSourceSelect::on_btnConnect_clicked()
 
   bool searchPublicOnly = settings.value( key + "/publicOnly" ).toBool();
   bool searchGeometryColumnsOnly = settings.value( key + "/geometryColumnsOnly" ).toBool();
+  bool allowGeometrylessTables = settings.value( key + "/allowGeometrylessTables", true ).toBool();
   mUseEstimatedMetadata = settings.value( key + "/estimatedMetadata" ).toBool();
   // Need to escape the password to allow for single quotes and backslashes
 
@@ -481,7 +482,7 @@ void QgsPgSourceSelect::on_btnConnect_clicked()
     // get the list of suitable tables and columns and populate the UI
     geomCol details;
 
-    if ( getTableInfo( pd, searchGeometryColumnsOnly, searchPublicOnly ) )
+    if ( getTableInfo( pd, searchGeometryColumnsOnly, searchPublicOnly, allowGeometrylessTables ) )
     {
       // Start the thread that gets the geometry type for relations that
       // may take a long time to return
@@ -604,7 +605,7 @@ QStringList QgsPgSourceSelect::pkCandidates( PGconn *pg, QString schemaName, QSt
   return cols;
 }
 
-bool QgsPgSourceSelect::getTableInfo( PGconn *pg, bool searchGeometryColumnsOnly, bool searchPublicOnly )
+bool QgsPgSourceSelect::getTableInfo( PGconn *pg, bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
 {
   int nColumns = 0;
   int nGTables = 0;
@@ -797,6 +798,58 @@ bool QgsPgSourceSelect::getTableInfo( PGconn *pg, bool searchGeometryColumnsOnly
     result = 0;
   }
 
+  if ( allowGeometrylessTables )
+  {
+    QString sql = "select "
+                  "pg_class.relname"
+                  ",pg_namespace.nspname"
+                  ",pg_class.relkind"
+                  " from "
+                  " pg_class"
+                  ",pg_namespace"
+                  " where "
+                  "pg_namespace.oid=pg_class.relnamespace"
+                  " and has_schema_privilege( pg_namespace.nspname, 'usage' )"
+                  " and has_table_privilege( '\"' || pg_namespace.nspname || '\".\"' || pg_class.relname || '\"', 'select' )"
+                  " and pg_class.relkind in( 'v', 'r' )";
+
+    // user has select privilege
+    if ( searchPublicOnly )
+      sql += " and pg_namespace.nspname = 'public'";
+
+    QgsDebugMsg( "sql: " + sql );
+
+    result = PQexec( pg, sql.toUtf8() );
+
+    if ( PQresultStatus( result ) != PGRES_TUPLES_OK )
+    {
+      QMessageBox::warning( this,
+                            tr( "Accessible tables could not be determined" ),
+                            tr( "Database connection was successful, but the accessible tables could not be determined.\n\n"
+                                "The error message from the database was:\n%1\n" )
+                            .arg( QString::fromUtf8( PQresultErrorMessage( result ) ) ) );
+      if ( nColumns == 0 )
+        nColumns = -1;
+    }
+    else if ( PQntuples( result ) > 0 )
+    {
+      for ( int i = 0; i < PQntuples( result ); i++ )
+      {
+        QString table  = QString::fromUtf8( PQgetvalue( result, i, 0 ) ); // relname
+        QString schema = QString::fromUtf8( PQgetvalue( result, i, 1 ) ); // nspname
+        QString relkind = QString::fromUtf8( PQgetvalue( result, i, 2 ) ); // relation kind
+
+        QgsDebugMsg( QString( "%1.%2: %3" ).arg( schema ).arg( table ).arg( relkind ) );
+
+        mTableModel.addTableEntry( tr( "No geometry" ), schema, table, QString::null, relkind == "v" ? pkCandidates( pg, schema, table ) : QStringList(), "" );
+        nColumns++;
+      }
+    }
+
+    PQclear( result );
+    result = 0;
+  }
+
   if ( nColumns == 0 )
   {
     QMessageBox::warning( this,

diff --git a/src/app/postgres/qgspgsourceselect.h b/src/app/postgres/qgspgsourceselect.h
@@ -155,7 +155,7 @@ class QgsPgSourceSelect : public QDialog, private Ui::QgsPgSourceSelectBase
     typedef QList<geomPair> geomCol;
 
     /**Inserts information about the spatial tables into mTableModel*/
-    bool getTableInfo( PGconn *pg, bool searchGeometryColumnsOnly, bool searchPublicOnly );
+    bool getTableInfo( PGconn *pg, bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables );
 
     /** get primary key candidates (all int4 columns) */
     QStringList pkCandidates( PGconn *pg, QString schemaName, QString viewName );

diff --git a/src/core/qgsdatasourceuri.cpp b/src/core/qgsdatasourceuri.cpp
@@ -103,6 +103,10 @@ QgsDataSourceURI::QgsDataSourceURI( QString uri ) : mSSLmode( SSLprefer ), mKeyC
 
           i++;
         }
+        else
+        {
+          mGeometryColumn = QString::null;
+        }
       }
       else if ( pname == "key" )
       {
@@ -439,9 +443,9 @@ QString QgsDataSourceURI::uri() const
     theUri += QString( " estimatedmetadata=true" );
   }
 
-  theUri += QString( " table=%1 (%2) sql=%3" )
+  theUri += QString( " table=%1%2 sql=%3" )
             .arg( quotedTablename() )
-            .arg( mGeometryColumn )
+            .arg( mGeometryColumn.isNull() ? QString() : QString( " (%1)" ).arg( mGeometryColumn ) )
             .arg( mSql );
 
   return theUri;

diff --git a/src/core/qgsvectorlayer.cpp b/src/core/qgsvectorlayer.cpp
@@ -124,7 +124,7 @@ QgsVectorLayer::QgsVectorLayer( QString vectorLayerPath,
     setCoordinateSystem();
 
     QSettings settings;
-    if ( settings.value( "/qgis/use_symbology_ng", false ).toBool() )
+    if ( settings.value( "/qgis/use_symbology_ng", false ).toBool() && geometryType() != QGis::NoGeometry )
     {
       // using symbology-ng!
       setUsingRendererV2( true );
@@ -139,7 +139,7 @@ QgsVectorLayer::QgsVectorLayer( QString vectorLayerPath,
     }
 
     // if the default style failed to load or was disabled use some very basic defaults
-    if ( !defaultLoadedFlag )
+    if ( !defaultLoadedFlag && geometryType() != QGis::NoGeometry )
     {
       // add single symbol renderer
       if ( mUsingRendererV2 )

diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -72,7 +72,7 @@ QgsPostgresProvider::QgsPostgresProvider( QString const & uri )
 
   mUri = QgsDataSourceURI( uri );
 
-  /* populate members from the uri structure */
+  // populate members from the uri structure
   mSchemaName = mUri.schema();
   mTableName = mUri.table();
   geometryColumn = mUri.geometryColumn();
@@ -390,6 +390,11 @@ bool QgsPostgresProvider::declareCursor(
   bool fetchGeometry,
   QString whereClause )
 {
+  if ( fetchGeometry && geometryColumn.isNull() )
+  {
+    return false;
+  }
+
   try
   {
     QString query = QString( "select %1" ).arg( quotedIdentifier( primaryKey ) );
@@ -548,7 +553,7 @@ void QgsPostgresProvider::select( QgsAttributeList fetchAttributes, QgsRectangle
 
   QString whereClause;
 
-  if ( !rect.isEmpty() )
+  if ( !rect.isEmpty() && !geometryColumn.isNull() )
   {
     if ( isGeography )
     {
@@ -983,10 +988,16 @@ bool QgsPostgresProvider::hasSufficientPermsAndCapabilities()
       sql = QString( "SELECT "
                      "has_table_privilege(%1,'DELETE'),"
                      "has_any_column_privilege(%1,'UPDATE'),"
-                     "has_column_privilege(%1,%2,'UPDATE'),"
+                     "%2"
                      "has_table_privilege(%1,'INSERT'),"
                      "current_schema()" )
-            .arg( quotedValue( mQuery ) ).arg( quotedValue( geometryColumn ) );
+            .arg( quotedValue( mQuery ) )
+            .arg( geometryColumn.isNull()
+                  ? QString( "" )
+                  : QString( "has_column_privilege(%1,%2,'UPDATE')," )
+                  .arg( quotedValue( mQuery ) )
+                  .arg( quotedValue( geometryColumn ) )
+                );
     }
     else
     {
@@ -2254,18 +2265,28 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist )
     connectionRW->PQexecNR( "BEGIN" );
 
     // Prepare the INSERT statement
-    QString insert = QString( "INSERT INTO %1(%2" )
-                     .arg( mQuery )
-                     .arg( quotedIdentifier( geometryColumn ) ),
-                     values = QString( ") VALUES (GeomFromWKB($1%1,%2)" )
-                              .arg( connectionRW->useWkbHex() ? "" : "::bytea" )
-                              .arg( srid );
+    QString insert = QString( "INSERT INTO %1(" ).arg( mQuery );
+    QString values;
+    QString delim = ",";
+
+    if ( !geometryColumn.isNull() )
+    {
+      insert += quotedIdentifier( geometryColumn );
+      values = QString( ") VALUES (GeomFromWKB($1%1,%2)" )
+               .arg( connectionRW->useWkbHex() ? "" : "::bytea" )
+               .arg( srid );
+      delim = ",";
+    }
+    else
+    {
+      delim = "";
+    }
 
     int offset;
     if ( primaryKeyType != "tid" && primaryKeyType != "oid" )
     {
-      insert += "," + quotedIdentifier( primaryKey );
-      values += ",$2";
+      insert += delim + quotedIdentifier( primaryKey );
+      values += delim + "$2";
       offset = 3;
     }
     else
@@ -2306,7 +2327,7 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist )
           break;
       }
 
-      insert += "," + quotedIdentifier( fieldname );
+      insert += delim + quotedIdentifier( fieldname );
 
       QString defVal = defaultValue( it.key() ).toString();
 
@@ -2316,44 +2337,46 @@ bool QgsPostgresProvider::addFeatures( QgsFeatureList &flist )
         {
           if ( defVal.isNull() )
           {
-            values += ",NULL";
+            values += delim + "NULL";
           }
           else
           {
-            values += "," + defVal;
+            values += delim + defVal;
           }
         }
         else if ( fit->typeName() == "geometry" )
         {
-          values += QString( ",geomfromewkt(%1)" ).arg( quotedValue( it->toString() ) );
+          values += QString( "%1geomfromewkt(%2)" ).arg( delim ).arg( quotedValue( it->toString() ) );
         }
         else if ( fit->typeName() == "geography" )
         {
-          values += QString( ",st_geographyfromewkt(%1)" ).arg( quotedValue( it->toString() ) );
+          values += QString( "%1st_geographyfromewkt(%2)" ).arg( delim ).arg( quotedValue( it->toString() ) );
         }
         else
         {
-          values += "," + quotedValue( it->toString() );
+          values += delim + quotedValue( it->toString() );
         }
       }
       else
       {
         // value is not unique => add parameter
         if ( fit->typeName() == "geometry" )
         {
-          values += QString( ",geomfromewkt($%1)" ).arg( defaultValues.size() + offset );
+          values += QString( "%1geomfromewkt($%2)" ).arg( delim ).arg( defaultValues.size() + offset );
         }
         else if ( fit->typeName() == "geography" )
         {
-          values += QString( ",st_geographyfromewkt($%1)" ).arg( defaultValues.size() + offset );
+          values += QString( "%1st_geographyfromewkt($%2)" ).arg( delim ).arg( defaultValues.size() + offset );
         }
         else
         {
-          values += QString( ",$%1" ).arg( defaultValues.size() + offset );
+          values += QString( "%1$%2" ).arg( delim ).arg( defaultValues.size() + offset );
         }
         defaultValues.append( defVal );
         fieldId.append( it.key() );
       }
+
+      delim = ",";
     }
 
     insert += values + ")";
@@ -2656,7 +2679,7 @@ bool QgsPostgresProvider::changeGeometryValues( QgsGeometryMap & geometry_map )
 {
   QgsDebugMsg( "entering." );
 
-  if ( isQuery )
+  if ( isQuery || geometryColumn.isNull() )
     return false;
 
   if ( !connectRW() )
@@ -2811,6 +2834,9 @@ long QgsPostgresProvider::featureCount() const
 
 QgsRectangle QgsPostgresProvider::extent()
 {
+  if ( geometryColumn.isNull() )
+    return QgsRectangle();
+
   if ( isGeography )
     return QgsRectangle( -180.0, -90.0, 180.0, 90.0 );
 
@@ -2846,7 +2872,7 @@ QgsRectangle QgsPostgresProvider::extent()
             // dateline extent() returns -180 to 180 (which appears right), but
             // estimated_extent() returns eastern bound of data (>-180) and
             // 180 degrees.
-            if ( !ext.startsWith( "-180 ") && ext.contains( ",180 " ) )
+            if ( !ext.startsWith( "-180 " ) && ext.contains( ",180 " ) )
             {
               ext.clear();
             }
@@ -2974,6 +3000,13 @@ bool QgsPostgresProvider::deduceEndian()
 
 bool QgsPostgresProvider::getGeometryDetails()
 {
+  if ( geometryColumn.isNull() )
+  {
+    geomType = QGis::WKBNoGeometry;
+    valid = true;
+    return true;
+  }
+
   QString fType( "" );
   srid = "";
   valid = false;

diff --git a/src/ui/qgspgnewconnectionbase.ui b/src/ui/qgspgnewconnectionbase.ui
@@ -6,8 +6,8 @@
    <rect>
     <x>0</x>
     <y>0</y>
-    <width>323</width>
-    <height>384</height>
+    <width>352</width>
+    <height>413</height>
    </rect>
   </property>
   <property name="sizePolicy">
@@ -228,7 +228,7 @@
         </item>
        </layout>
       </item>
-      <item row="4" column="0">
+      <item row="5" column="0">
        <widget class="QCheckBox" name="cb_useEstimatedMetadata">
         <property name="toolTip">
          <string>Use estimated table statistics for the layer metadata.</string>
@@ -249,6 +249,16 @@
         </property>
        </widget>
       </item>
+      <item row="4" column="0">
+       <widget class="QCheckBox" name="cb_allowGeometrylessTables">
+        <property name="text">
+         <string>Allow geometryless tables</string>
+        </property>
+        <property name="checked">
+         <bool>true</bool>
+        </property>
+       </widget>
+      </item>
      </layout>
     </widget>
    </item>