qgspostgresprovider.cpp

一个非常好的GIS开源新版本

/***************************************************************************
  qgspostgresprovider.cpp  -  QGIS data provider for PostgreSQL/PostGIS layers
                             -------------------
    begin                : 2004/01/07
    copyright            : (C) 2004 by Gary E.Sherman
    email                : sherman at mrcc.com
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/

/* $Id: qgspostgresprovider.cpp 8313 2008-04-02 22:35:33Z jef $ */

// for htonl
#ifdef WIN32
#include <winsock.h>
#else
#include <netinet/in.h>
#endif

#include <cassert>

#include <QApplication>
#include <QEvent>
#include <QCustomEvent>

#include <qgis.h>
#include <qgsapplication.h>
#include <qgsfeature.h>
#include <qgsfield.h>
#include <qgsgeometry.h>
#include <qgsmessageoutput.h>
#include <qgsrect.h>
#include <qgsspatialrefsys.h>

#include "qgsprovidercountcalcevent.h"
#include "qgsproviderextentcalcevent.h"

#include "qgspostgresprovider.h"

#include "qgspostgrescountthread.h"
#include "qgspostgresextentthread.h"

#include "qgspostgisbox3d.h"
#include "qgslogger.h"

const QString POSTGRES_KEY = "postgres";
const QString POSTGRES_DESCRIPTION = "PostgreSQL/PostGIS data provider";

QMap<QString, QgsPostgresProvider::Conn *> QgsPostgresProvider::connections;
int QgsPostgresProvider::providerIds=0;

QgsPostgresProvider::QgsPostgresProvider(QString const & uri)
: QgsVectorDataProvider(uri),
  geomType(QGis::WKBUnknown),
  mFeatureQueueSize(200),
  gotPostgisVersion(false),
  mFetching(false)
{
  // assume this is a valid layer until we determine otherwise
  valid = true;

  providerId=providerIds++;

  QgsDebugMsg("Postgresql Layer Creation");
  QgsDebugMsg("URI: " + uri);

  mUri = QgsDataSourceURI(uri);

  /* populate members from the uri structure */
  mSchemaName = mUri.schema();
  mTableName = mUri.table();
  geometryColumn = mUri.geometryColumn();
  sqlWhereClause = mUri.sql();

  // Keep a schema qualified table name for convenience later on.
  mSchemaTableName = mUri.quotedTablename();

  QgsDebugMsg("Table name is " + mTableName);
  QgsDebugMsg("SQL is " + sqlWhereClause);
  QgsDebugMsg("Connection info is " + mUri.connInfo() );

  QgsDebugMsg("Geometry column is: " + geometryColumn);
  QgsDebugMsg("Schema is: " + mSchemaName);
  QgsDebugMsg("Table name is: " + mTableName);

  //QString logFile = "./pg_provider_" + mTableName + ".log";
  //pLog.open((const char *)logFile);
  //QgsDebugMsg("Opened log file for " + mTableName);

  connection = connectDb( mUri.connInfo() );
  if( connection==NULL ) {
    valid = false;
    return;
  }

  QgsDebugMsg("Checking for permissions on the relation");

  // Check that we can read from the table (i.e., we have
  // select permission).
  QString sql = QString("select * from %1 limit 1").arg(mSchemaTableName);
  PGresult* testAccess = PQexec(connection, sql.toUtf8());
  if (PQresultStatus(testAccess) != PGRES_TUPLES_OK)
  {
    showMessageBox(tr("Unable to access relation"),
        tr("Unable to access the ") + mSchemaTableName + 
        tr(" relation.\nThe error message from the database was:\n") +
        QString::fromUtf8(PQresultErrorMessage(testAccess)) + ".\n" + 
        "SQL: " + sql);
    PQclear(testAccess);
    valid = false;
    disconnectDb();
    return;
  }
  PQclear(testAccess);

  sql = QString("SELECT "
                           "has_table_privilege(%1,'DELETE'),"
                           "has_table_privilege(%1,'UPDATE'),"
                           "has_table_privilege(%1,'INSERT'),"
                           "current_schema()")
                           .arg( quotedValue(mSchemaTableName) );

  testAccess = PQexec( connection, sql.toUtf8() );
  if( PQresultStatus(testAccess) != PGRES_TUPLES_OK ) {
    showMessageBox(tr("Unable to access relation"),
        tr("Unable to determine table access privileges for the ") + mSchemaTableName + 
        tr(" relation.\nThe error message from the database was:\n") +
        QString::fromUtf8(PQresultErrorMessage(testAccess)) + ".\n" + 
        "SQL: " + sql);
    PQclear(testAccess);
    valid = false;
    disconnectDb();
    return;
  }

  enabledCapabilities = QgsVectorDataProvider::SelectGeometryAtId;
  
  if( QString::fromUtf8( PQgetvalue(testAccess, 0, 0) )=="t" ) {
    // DELETE
    enabledCapabilities |= QgsVectorDataProvider::DeleteFeatures;
  }
      
  if( QString::fromUtf8( PQgetvalue(testAccess, 0, 1) )=="t" ) {
    // UPDATE
    enabledCapabilities |= QgsVectorDataProvider::ChangeGeometries | QgsVectorDataProvider::ChangeAttributeValues;
  }

  if( QString::fromUtf8( PQgetvalue(testAccess, 0, 2) )=="t" ) {
    // INSERT
    enabledCapabilities |= QgsVectorDataProvider::AddFeatures;
  }

  mCurrentSchema = QString::fromUtf8( PQgetvalue(testAccess, 0, 3) );
  if(mCurrentSchema==mSchemaName) {
    mUri.clearSchema();
    setDataSourceUri( mUri.uri() );
  }
  if(mSchemaName=="")
    mSchemaName=mCurrentSchema;
  PQclear(testAccess);

  sql = QString("SELECT 1 FROM pg_class,pg_namespace WHERE "
                   "pg_class.relnamespace=pg_namespace.oid AND "
                   "pg_get_userbyid(relowner)=current_user AND "
                   "relname=%1 AND nspname=%2")
          .arg( quotedValue(mTableName) )
          .arg( quotedValue(mSchemaName) );
  testAccess = PQexec(connection, sql.toUtf8());
  if (PQresultStatus(testAccess) == PGRES_TUPLES_OK && PQntuples(testAccess)==1)
  {
    enabledCapabilities |= QgsVectorDataProvider::AddAttributes | QgsVectorDataProvider::DeleteAttributes;
  }
  PQclear(testAccess);

  if ( !getGeometryDetails() ) // gets srid and geometry type
  {
    // the table is not a geometry table
    numberFeatures = 0;
    valid = false;

    QgsDebugMsg("Invalid Postgres layer");
    disconnectDb();
    return;
  }

  deduceEndian();
  calculateExtents();
  getFeatureCount();

  // load the field list
  loadFields();

  // set the primary key
  getPrimaryKey();

  // Set the postgresql message level so that we don't get the
  // 'there is no transaction in progress' warning.
#ifndef QGISDEBUG
  PQexecNR(connection, QString("set client_min_messages to error").toUtf8());
#endif

  // Kick off the long running threads

#ifdef POSTGRESQL_THREADS
  QgsDebugMsg("About to touch mExtentThread");
  mExtentThread.setConnInfo( mUri.connInfo );
  mExtentThread.setTableName( mTableName );
  mExtentThread.setSqlWhereClause( sqlWhereClause );
  mExtentThread.setGeometryColumn( geometryColumn );
  mExtentThread.setCallback( this );
  QgsDebugMsg("About to start mExtentThread");
  mExtentThread.start();
  QgsDebugMsg("Main thread just dispatched mExtentThread");

  QgsDebugMsg("About to touch mCountThread");
  mCountThread.setConnInfo( mUri.connInfo );
  mCountThread.setTableName( mTableName );
  mCountThread.setSqlWhereClause( sqlWhereClause );
  mCountThread.setGeometryColumn( geometryColumn );
  mCountThread.setCallback( this );
  QgsDebugMsg("About to start mCountThread");
  mCountThread.start();
  QgsDebugMsg("Main thread just dispatched mCountThread");
#endif

  //fill type names into sets
  mSupportedNativeTypes.insert("double precision");
  mSupportedNativeTypes.insert("int4");
  mSupportedNativeTypes.insert("int8");
  mSupportedNativeTypes.insert("text");
  mSupportedNativeTypes.insert("varchar(30)");

  if (primaryKey.isEmpty())
  {
    valid = false;
  }

  // Close the database connection if the layer isn't going to be loaded.
  if (!valid)
    disconnectDb();
}

QgsPostgresProvider::~QgsPostgresProvider()
{
#ifdef POSTGRESQL_THREADS
  QgsDebugMsg("About to wait for mExtentThread");

  mExtentThread.wait();

  QgsDebugMsg("Finished waiting for mExtentThread");

  QgsDebugMsg("About to wait for mCountThread");

  mCountThread.wait();

  QgsDebugMsg("Finished waiting for mCountThread");

  // Make sure all events from threads have been processed
  // (otherwise they will get destroyed prematurely)
  QApplication::sendPostedEvents(this, QGis::ProviderExtentCalcEvent);
  QApplication::sendPostedEvents(this, QGis::ProviderCountCalcEvent);
#endif

  disconnectDb();

  QgsDebugMsg("deconstructing.");

  //pLog.flush();
}

PGconn *QgsPostgresProvider::connectDb(const QString & conninfo)
{
  if( connections.contains(conninfo) )
  {
    QgsDebugMsg( QString("Using cached connection for %1").arg(conninfo) );
    connections[conninfo]->ref++;
    return connections[conninfo]->conn;
  }

  QgsDebugMsg(QString("New postgres connection for ") + conninfo);

  PGconn *pd = PQconnectdb(conninfo.toLocal8Bit());	// use what is set based on locale; after connecting, use Utf8
  // check the connection status
  if (PQstatus(pd) != CONNECTION_OK) 
  {
    QgsDebugMsg("Connection to database failed");
    return NULL;
  }

  //set client encoding to unicode because QString uses UTF-8 anyway
  QgsDebugMsg("setting client encoding to UNICODE");

  int errcode=PQsetClientEncoding(pd, QString("UNICODE").toLocal8Bit());

  if(errcode==0) 
  {
    QgsDebugMsg("encoding successfully set");
  } 
  else if(errcode==-1) 
  {
    QgsDebugMsg("error in setting encoding");
  } 
  else 
  {
    QgsDebugMsg("undefined return value from encoding setting");
  }

  /* Check to see if we have GEOS support and if not, warn the user about
     the problems they will see :) */
  QgsDebugMsg("Checking for GEOS support");

  if(!hasGEOS(pd))
  {
    showMessageBox(tr("No GEOS Support!"),
        tr("Your PostGIS installation has no GEOS support.\n"
          "Feature selection and identification will not "
          "work properly.\nPlease install PostGIS with " 
          "GEOS support (http://geos.refractions.net)"));
  }
  
  QgsDebugMsg("Connection to the database was successful");
  
  Conn *conn = new Conn(pd);
  connections.insert( conninfo, conn );

  return pd;
}

void QgsPostgresProvider::disconnectDb()
{
  if(mFetching) 
  {
    PQexecNR(connection, QString("CLOSE qgisf%1").arg(providerId).toUtf8() );
    mFetching=false;
  }

  QMap<QString, Conn *>::iterator i;
  for(i=connections.begin(); i!=connections.end() && i.value()->conn!=connection; i++)
    ;

  assert( i.value()->conn==connection );
  assert( i.value()->ref>0 );

  if( --i.value()->ref==0 ) {
    PQfinish( connection );
    delete i.value();
    connections.remove( i.key() );
  }
    
  connection = 0;
}

QString QgsPostgresProvider::storageType() const
{
  return "PostgreSQL database with PostGIS extension";
}

bool QgsPostgresProvider::declareCursor(const QString &cursorName,
                                        const QgsAttributeList &fetchAttributes,
                                        bool fetchGeometry,
                                        QString whereClause)
{
  try
  {
    QString declare = QString("declare %1 binary cursor with hold for select %2")
      .arg(cursorName).arg(quotedIdentifier(primaryKey));

    if(fetchGeometry)
    {
      declare += QString(",asbinary(%1,'%2')")
        .arg( quotedIdentifier(geometryColumn) )
        .arg( endianString() ); 
    }

    for (QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it)
    {
      const QgsField &fld = field(*it);

      const QString &fieldname = fld.name();
      if( fieldname == primaryKey )
        continue;

      const QString &type = fld.typeName();
      if( type == "money" )
      {
        declare += QString(",cash_out(%1)").arg( quotedIdentifier(fieldname) );
      }
      else if( type.startsWith("_") )
      {
        declare += QString(",array_out(%1)").arg( quotedIdentifier(fieldname) );
      }
      else if( type == "bool" )
      {
        declare += QString(",boolout(%1)").arg( quotedIdentifier(fieldname) );
      }
      else
      {
        declare += "," + quotedIdentifier(fieldname) + "::text";
      }
    }

    declare += " from " + mSchemaTableName;

    if( !whereClause.isEmpty() )
      declare += QString(" where %1").arg(whereClause);

    QgsDebugMsg("Binary cursor: " + declare);