qgsmysqlprovider.cpp

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

/***************************************************************************
  qgsmysqlprovider.cpp -  Data provider for MySQL 5.0+
  -------------------
          begin                : 2006-01-07
          copyright            : (C) 2006 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: */

#include "qgsmysqlprovider.h"

#include <cfloat>
#include <iostream>

#include <qfile.h>
#include <qdatastream.h>
#include <qtextstream.h>
#include <qstringlist.h>
#include <qmessagebox.h>
#include <qsettings.h>
#include <qregexp.h>
#include <QUrl>
#include <qglobal.h>

#include "../../src/qgsdataprovider.h"
#include "../../src/qgsencodingfiledialog.h"
#include "../../src/qgsfeature.h"
#include "../../src/qgsfield.h"
#include "../../src/qgsrect.h"



static const QString TEXT_PROVIDER_KEY = "mysql";
static const QString TEXT_PROVIDER_DESCRIPTION = "MySQL data provider";



QgsMySQLProvider::QgsMySQLProvider(QString const &uri)
    : QgsVectorDataProvider(uri) 
{
  // assume this is a valid layer until we determine otherwise
  valid = true;
  /* OPEN LOG FILE */

  // Make connection to the data source
  // For postgres, the connection information is passed as a space delimited
  // string:
  //  host=192.168.1.5 dbname=test port=3306 user=gsherman password=xxx table=tablename
  std::cout << "Data source uri is " << uri.toLocal8Bit().data() << std::endl;

  // Strip the table and sql statement name off and store them
  int sqlStart = uri.indexOf(" sql");
  int tableStart = uri.indexOf("table=");
#ifdef QGISDEBUG
  qDebug(  "****************************************");
  qDebug(  "****   MySQL Layer Creation   *****" );
  qDebug(  "****************************************");
  qDebug(  (const char*)(QString("URI: ") + uri).toLocal8Bit().data() );
  QString msg;

  qDebug(  "tableStart: " + msg.setNum(tableStart) );
  qDebug(  "sqlStart: " + msg.setNum(sqlStart));
#endif 
  mTableName = uri.mid(tableStart + 6, sqlStart - tableStart -6);
  if(sqlStart > -1)
  { 
    sqlWhereClause = uri.mid(sqlStart + 5);
  }
  else
  {
    sqlWhereClause = QString::null;
  }
  QString connInfo = uri.left(uri.indexOf("table="));
#ifdef QGISDEBUG
  qDebug( (const char*)(QString("Table name is ") + mTableName).toLocal8Bit().data());
  qDebug( (const char*)(QString("SQL is ") + sqlWhereClause).toLocal8Bit().data() );
  qDebug( "Connection info is " + connInfo);
#endif
  // calculate the schema if specified
  mSchemaName = "";
  if (mTableName.indexOf(".") > -1) {
    mSchemaName = mTableName.left(mTableName.indexOf("."));
  }
  geometryColumn = mTableName.mid(mTableName.indexOf(" (") + 2);
  geometryColumn.truncate(geometryColumn.length() - 1);
  mTableName = mTableName.mid(mTableName.indexOf(".") + 1, mTableName.indexOf(" (") - (mTableName.indexOf(".") + 1)); 

  // Keep a schema qualified table name for convenience later on.
  if (mSchemaName.length() > 0)
    mSchemaTableName = "\"" + mSchemaName + "\".\"" + mTableName + "\"";
  else
    mSchemaTableName = "\"" + mTableName + "\"";

  /* populate the uri structure */
  mUri.schema = mSchemaName;
  mUri.table = mTableName;
  mUri.geometryColumn = geometryColumn;
  mUri.sql = sqlWhereClause;
  // parse the connection info
  QStringList conParts = connInfo.split(" ");
  QStringList parm = conParts[0].split("=");
  if(parm.size() == 2)
  {
    mUri.host = parm[1];
  }
  parm = conParts[1].split("=");
  if(parm.size() == 2)
  {
    mUri.database = parm[1];
  }
  parm = conParts[2].split("=");
  if(parm.size() == 2)
  {
    mUri.port = parm[1];
  }

  parm = conParts[3].split("=");
  if(parm.size() == 2)
  {
    mUri.username = parm[1];
  }
  parm = conParts[4].split("=");
  if(parm.size() == 2)
  {
    mUri.password = parm[1];
  }
  /* end uri structure */

#ifdef QGISDEBUG
  std::cerr << "Geometry column is: " << geometryColumn.toLocal8Bit().data() << std::endl;
  std::cerr << "Schema is: " << mSchemaName.toLocal8Bit().data() << std::endl;
  std::cerr << "Table name is: " << mTableName.toLocal8Bit().data() << std::endl;
#endif
  //QString logFile = "./pg_provider_" + mTableName + ".log";
  //pLog.open((const char *)logFile);
#ifdef QGISDEBUG
  std::cerr << "Opened log file for " << mTableName.toLocal8Bit().data() << std::endl;
#endif
  MYSQL *res = mysql_init(&mysql);
  MYSQL *con = mysql_real_connect(&mysql, mUri.host.toLocal8Bit(), mUri.username.toLocal8Bit(), mUri.password.toLocal8Bit(), mUri.table.toLocal8Bit(), 0, NULL, 0);
  // check the connection status
  if(con)
  {

#ifdef QGISDEBUG
    std::cerr << "Checking for select permission on the relation\n";
#endif
    // Check that we can read from the table (i.e., we have
    // select permission).
    QString sql = "select * from " + mSchemaTableName + " limit 1";
    PGresult* testAccess = PQexec(pd, (const char*)(sql.utf8()));
    if (PQresultStatus(testAccess) != PGRES_TUPLES_OK)
    {
      QApplication::restoreOverrideCursor();
      QMessageBox::warning(0, tr("Unable to access relation"),
          tr("Unable to access the ") + mSchemaTableName + 
          tr(" relation.\nThe error message from the database was:\n") +
          QString(PQresultErrorMessage(testAccess)) + ".\n" + 
          "SQL: " + sql);
      QApplication::setOverrideCursor(Qt::waitCursor);
      PQclear(testAccess);
      valid = false;
      return;
    }
    PQclear(testAccess);

    /* Check to see if we have GEOS support and if not, warn the user about
       the problems they will see :) */
#ifdef QGISDEBUG
    std::cerr << "Checking for GEOS support" << std::endl;
#endif
    if(!hasGEOS(pd))
    {
      QApplication::restoreOverrideCursor();
      QMessageBox::warning(0, tr("No GEOS Support!"),
          tr("Your PostGIS installation has no GEOS support.\nFeature selection and "
            "identification will not work properly.\nPlease install PostGIS with " 
            "GEOS support (http://geos.refractions.net)"));
      QApplication::setOverrideCursor(Qt::waitCursor);
    }
    //--std::cout << "Connection to the database was successful\n";

    if (getGeometryDetails()) // gets srid and geometry type
    {
      deduceEndian();
      calculateExtents();
      getFeatureCount();

      // Populate the field vector for this layer. The field vector contains
      // field name, type, length, and precision (if numeric)
      sql = "select * from " + mSchemaTableName + " limit 1";

      PGresult* result = PQexec(pd, (const char *) (sql.utf8()));
      //--std::cout << "Field: Name, Type, Size, Modifier:" << std::endl;
      for (int i = 0; i < PQnfields(result); i++)
      {
        QString fieldName = PQfname(result, i);
        int fldtyp = PQftype(result, i);
        QString typOid = QString().setNum(fldtyp);
        int fieldModifier = PQfmod(result, i);

        sql = "select typelem from pg_type where typelem = " + typOid + " and typlen = -1";
        //  //--std::cout << sql << std::endl;
        PGresult *oidResult = PQexec(pd, (const char *) sql);
        // get the oid of the "real" type
        QString poid = PQgetvalue(oidResult, 0, PQfnumber(oidResult, "typelem"));
        PQclear(oidResult);

        sql = "select typname, typlen from pg_type where oid = " + poid;
        // //--std::cout << sql << std::endl;
        oidResult = PQexec(pd, (const char *) sql);
        QString fieldType = PQgetvalue(oidResult, 0, 0);
        QString fieldSize = PQgetvalue(oidResult, 0, 1);
        PQclear(oidResult);

        sql = "select oid from pg_class where relname = '" + mTableName + "' and relnamespace = ("
	  "select oid from pg_namespace where nspname = '" + mSchemaName + "')";
        PGresult *tresult= PQexec(pd, (const char *)(sql.utf8()));
        QString tableoid = PQgetvalue(tresult, 0, 0);
        PQclear(tresult);

        sql = "select attnum from pg_attribute where attrelid = " + tableoid + " and attname = '" + fieldName + "'";
        tresult = PQexec(pd, (const char *)(sql.utf8()));
        QString attnum = PQgetvalue(tresult, 0, 0);
        PQclear(tresult);

#ifdef QGISDEBUG
        std::cerr << "Field: " << attnum.toLocal8Bit().data() << " maps to " << i << " " << fieldName.toLocal8Bit().data() << ", " 
          << fieldType.toLocal8Bit().data() << " (" << fldtyp << "),  " << fieldSize.toLocal8Bit().data() << ", "  
          << fieldModifier << std::endl;
#endif
        attributeFieldsIdMap[attnum.toInt()] = i;

        if(fieldName!=geometryColumn)
        {
          attributeFields.push_back(QgsField(fieldName, fieldType, fieldSize.toInt(), fieldModifier));
        }
      }
      PQclear(result);

      // 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
      PQexec(connection, "set client_min_messages to error");
#endif

      // Kick off the long running threads

#ifdef POSTGRESQL_THREADS
      std::cout << "QgsPostgresProvider: About to touch mExtentThread" << std::endl;
      mExtentThread.setConnInfo( connInfo );
      mExtentThread.setTableName( mTableName );
      mExtentThread.setSqlWhereClause( sqlWhereClause );
      mExtentThread.setGeometryColumn( geometryColumn );
      mExtentThread.setCallback( this );
      std::cout << "QgsPostgresProvider: About to start mExtentThread" << std::endl;
      mExtentThread.start();
      std::cout << "QgsPostgresProvider: Main thread just dispatched mExtentThread" << std::endl;

      std::cout << "QgsPostgresProvider: About to touch mCountThread" << std::endl;
      mCountThread.setConnInfo( connInfo );
      mCountThread.setTableName( mTableName );
      mCountThread.setSqlWhereClause( sqlWhereClause );
      mCountThread.setGeometryColumn( geometryColumn );
      mCountThread.setCallback( this );
      std::cout << "QgsPostgresProvider: About to start mCountThread" << std::endl;
      mCountThread.start();
      std::cout << "QgsPostgresProvider: Main thread just dispatched mCountThread" << std::endl;
#endif
    } 
    else 
    {
      // the table is not a geometry table
      numberFeatures = 0;
      valid = false;
#ifdef QGISDEBUG
      std::cerr << "Invalid Postgres layer" << std::endl;
#endif
    }

    ready = false; // not ready to read yet cuz the cursor hasn't been created

  } else {
    valid = false;
    //--std::cout << "Connection to database failed\n";
  }

  //create a boolean vector and set every entry to false

  /*  if (valid) {
      selected = new std::vector < bool > (ogrLayer->GetFeatureCount(), false);
      } else {
      selected = 0;
      } */
  //  tabledisplay=0;

  //fill type names into lists
  mNumericalTypes.push_back("double precision");
  mNumericalTypes.push_back("int4");
  mNumericalTypes.push_back("int8");
  mNonNumericalTypes.push_back("text");
  mNonNumericalTypes.push_back("varchar(30)");

}

QgsMySQLProvider::~QgsMySQLProvider()
{
  mFile->close();
  delete mFile;
  for (int i = 0; i < fieldCount(); i++)
  {
    delete mMinMaxCache[i];
  }
  delete[]mMinMaxCache;
}


QString QgsMySQLProvider::storageType()
{
  return "Delimited text file";
}


/**
 * Get the first feature resutling from a select operation
 * @return QgsFeature
 */
QgsFeature * QgsMySQLProvider::getFirstFeature(bool fetchAttributes)
{
    QgsFeature *f = new QgsFeature;

    reset();                    // reset back to first feature

    if ( getNextFeature_( *f, fetchAttributes ) )
    {
        return f;
    }

    delete f;

    return 0x0;
} // QgsMySQLProvider::getFirstFeature(bool fetchAttributes)

/**

  insure double value is properly translated into locate endian-ness

*/
static
double
translateDouble_( double d )
{
    union
    {
        double fpval;
        char   char_val[8];
    } from, to;

    // break double into byte sized chunks
    from.fpval = d;

    to.char_val[7] = from.char_val[0];
    to.char_val[6] = from.char_val[1];
    to.char_val[5] = from.char_val[2];
    to.char_val[4] = from.char_val[3];
    to.char_val[3] = from.char_val[4];
    to.char_val[2] = from.char_val[5];
    to.char_val[1] = from.char_val[6];
    to.char_val[0] = from.char_val[7];

    return to.fpval;

} // translateDouble_


bool
QgsMySQLProvider::getNextFeature_( QgsFeature & feature, 
                                           bool getAttributes,
                                           std::list<int> const * desiredAttributes )
{
    // before we do anything else, assume that there's something wrong with
    // the feature
    feature.setValid( false );

    QTextStream textStream( mFile );

    if ( ! textStream.atEnd() )
    {
      QString line = textStream.readLine(); // Default local 8 bit encoding

        // lex the tokens from the current data line
        QStringList tokens = QStringList::split(QRegExp(mDelimiter), line, true);

        bool xOk = false;
        bool yOk = false;

        int xFieldPos = fieldPositions[mXField];
        int yFieldPos = fieldPositions[mYField];

        double x = tokens[xFieldPos].toDouble( &xOk );
        double y = tokens[yFieldPos].toDouble( &yOk );

        if ( xOk && yOk )
        {
            // if the user has selected an area, constrain iterator to
            // features that are within that area
            if ( mSelectionRectangle && ! boundsCheck(x,y) )
            {
                bool foundFeature = false;

                while ( ! textStream.atEnd() && 
                        (xOk && yOk) )
                {
                    if ( boundsCheck(x,y) )
                    {
                        foundFeature = true;
                        break;
                    }

                    ++mFid;     // since we're skipping to next feature,
                                // increment ID

                    line = textStream.readLine();

                    tokens = QStringList::split(QRegExp(mDelimiter), line, true);

                    x = tokens[xFieldPos].toDouble( &xOk );
                    y = tokens[yFieldPos].toDouble( &yOk );
                }

                // there were no other features from the current one forward
                // that were within the selection region
                if ( ! foundFeature )
                {
                    return false;
                }