qgspostgresprovider.cpp

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

               fieldTypeName == "char" ||
               fieldTypeName == "bpchar" || 
               fieldTypeName == "varchar" ||
               fieldTypeName == "bool" ||
               fieldTypeName == "money" ||
               fieldTypeName.startsWith("time") ||
               fieldTypeName.startsWith("date") )
        fieldType = QVariant::String;
      else
      {
        QgsDebugMsg( "Field " + fieldName + " ignored, because of unsupported type " + fieldTypeName);
        continue;
      }

      if(isArray)
      {
        fieldTypeName = "_" + fieldTypeName;
        fieldType = QVariant::String;
      }
        
      attributeFields.insert(i, QgsField(fieldName, fieldType, fieldTypeName, fieldSize.toInt(), fieldModifier, fieldComment));
    }
  }
  PQclear(result);
}

QString QgsPostgresProvider::getPrimaryKey()
{
  // check to see if there is an unique index on the relation, which
  // can be used as a key into the table. Primary keys are always
  // unique indices, so we catch them as well.

  QString sql = QString("select indkey from pg_index where indisunique='t' and indrelid=regclass(%1)::oid")
                  .arg( quotedValue(mSchemaTableName) );

  QgsDebugMsg("Getting unique index using '" + sql + "'");

  PGresult *pk = executeDbCommand(connection, sql);

  QgsDebugMsg("Got " + QString::number(PQntuples(pk)) + " rows.");

  QStringList log;

  // if we got no tuples we ain't got no unique index :)
  if (PQntuples(pk) == 0)
  {
    QgsDebugMsg("Relation has no unique index -- investigating alternatives");

    // Two options here. If the relation is a table, see if there is
    // an oid column that can be used instead.
    // If the relation is a view try to find a suitable column to use as
    // the primary key.

    sql = QString("SELECT relkind FROM pg_class WHERE oid=regclass(%1)::oid")
            .arg( quotedValue(mSchemaTableName) );
    PGresult* tableType = executeDbCommand(connection, sql);
    QString type = QString::fromUtf8(PQgetvalue(tableType, 0, 0));
    PQclear(tableType);

    primaryKey = "";

    if (type == "r") // the relation is a table
    {
      QgsDebugMsg("Relation is a table. Checking to see if it has an oid column.");

      // If there is an oid on the table, use that instead,
      // otherwise give up
      sql = QString("SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)")
              .arg( quotedValue(mSchemaTableName) );

      PGresult* oidCheck = executeDbCommand(connection, sql);

      if (PQntuples(oidCheck) != 0)
      {
        // 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)
        primaryKey = "oid";
        primaryKeyType = "int4";
      }
      else
      {
        showMessageBox(tr("No suitable key column in table"),
            tr("The table has no column suitable for use as a key.\n\n"
              "Qgis requires that the table either has a column of type\n"
              "int4 with a unique constraint on it (which includes the\n"
              "primary key) or has a PostgreSQL oid column.\n"));
      }
      PQclear(oidCheck);
    }
    else if (type == "v") // the relation is a view
    {
      // Have a poke around the view to see if any of the columns
      // could be used as the primary key.
      tableCols cols;
      // Given a schema.view, populate the cols variable with the
      // schema.table.column's that underly the view columns.
      findColumns(cols);
      // From the view columns, choose one for which the underlying
      // column is suitable for use as a key into the view.
      primaryKey = chooseViewColumn(cols);
    }
    else
      QgsDebugMsg("Unexpected relation type of '" + type + "'.");
  }
  else // have some unique indices on the table. Now choose one...
  {
    // choose which (if more than one) unique index to use
    std::vector<std::pair<QString, QString> > suitableKeyColumns;
    for (int i = 0; i < PQntuples(pk); ++i)
    {
      QString col = QString::fromUtf8(PQgetvalue(pk, i, 0));
      QStringList columns = QStringList::split(" ", col);
      if (columns.count() == 1)
      {
        // Get the column name and data type
        sql = QString("select attname,pg_type.typname from pg_attribute,pg_type where atttypid=pg_type.oid and attnum=%1 and attrelid=regclass(%2)")
                .arg( col ).arg( quotedValue(mSchemaTableName) );
        PGresult* types = executeDbCommand(connection, sql);

        if( PQntuples(types) > 0 )
        {
          QString columnName = QString::fromUtf8(PQgetvalue(types, 0, 0));
          QString columnType = QString::fromUtf8(PQgetvalue(types, 0, 1));

          if (columnType != "int4")
            log.append(tr("The unique index on column") + 
                " '" + columnName + "' " +
                tr("is unsuitable because Qgis does not currently support"
                  " non-int4 type columns as a key into the table.\n"));
          else
            suitableKeyColumns.push_back(std::make_pair(columnName, columnType));
        }
        else
        {
          //QgsDebugMsg( QString("name and type of %3. column of %1.%2 not found").arg(mSchemaName).arg(mTables).arg(col) );
        }

        PQclear(types);
      }
      else
      {
        sql = QString("select attname from pg_attribute, pg_type where atttypid=pg_type.oid and attnum in (%1) and attrelid=regclass(%2)::oid")
                .arg( col.replace(" ", ",") )
                .arg( quotedValue(mSchemaTableName) );

        PGresult* types = executeDbCommand(connection, sql);
        QString colNames;
        int numCols = PQntuples(types);
        for (int j = 0; j < numCols; ++j)
        {
          if (j == numCols-1)
            colNames += tr("and ");
          colNames += quotedValue( QString::fromUtf8(PQgetvalue(types, j, 0)) );
          if ( j < numCols-2 )
            colNames+= ",";
        }

        log.append(tr("The unique index based on columns ") + colNames + 
            tr(" is unsuitable because Qgis does not currently support"
              " multiple columns as a key into the table.\n"));
      }
    }

    // suitableKeyColumns now contains the name of columns (and their
    // data type) that
    // are suitable for use as a key into the table. If there is
    // more than one we need to choose one. For the moment, just
    // choose the first in the list.

    if (suitableKeyColumns.size() > 0)
    {
      primaryKey = suitableKeyColumns[0].first;
      primaryKeyType = suitableKeyColumns[0].second;
    }
    else
    {
      // If there is an oid on the table, use that instead,
      // otherwise give up
      sql = QString("select attname from pg_attribute where attname='oid' and attrelid=regclass(%1)::oid").arg( quotedValue(mSchemaTableName) );
      PGresult* oidCheck = executeDbCommand(connection, sql);

      if (PQntuples(oidCheck) != 0)
      {
        primaryKey = "oid";
        primaryKeyType = "int4";
      }
      else
      {
        log.prepend("There were no columns in the table that were suitable "
            "as a qgis key into the table (either a column with a "
            "unique index and type int4 or a PostgreSQL oid column.\n");
      }
      PQclear(oidCheck);
    }

    // Either primaryKey has been set by the above code, or it
    // hasn't. If not, present some info to the user to give them some
    // idea of why not.
    if (primaryKey.isEmpty())
    {
      // Give some info to the user about why things didn't work out.
      valid = false;
      showMessageBox(tr("Unable to find a key column"), log);
    }
  }
  PQclear(pk);

  if (primaryKey.length() > 0) {
    QgsDebugMsg("Qgis row key is " + primaryKey);
  } else {
    QgsDebugMsg("Qgis row key was not set.");
  }

  return primaryKey;
}

// Given the table and column that each column in the view refers to,
// choose one. Prefers column with an index on them, but will
// otherwise choose something suitable.

QString QgsPostgresProvider::chooseViewColumn(const tableCols& cols)
{
  // For each relation name and column name need to see if it
  // has unique constraints on it, or is a primary key (if not,
  // it shouldn't be used). Should then be left with one or more
  // entries in the map which can be used as the key.

  QString sql, key;
  QStringList log;
  tableCols suitable;
  // Cache of relation oid's
  std::map<QString, QString> relOid;

  std::vector<tableCols::const_iterator> oids;
  tableCols::const_iterator iter = cols.begin();
  for (; iter != cols.end(); ++iter)
  {
    QString viewCol   = iter->first;
    QString schemaName = iter->second.schema;
    QString tableName = iter->second.relation;
    QString tableCol  = iter->second.column;
    QString colType   = iter->second.type;

    // Get the oid from pg_class for the given schema.relation for use
    // in subsequent queries.
    sql = QString("select regclass(%1)::oid").arg( quotedValue( quotedIdentifier(schemaName) + "." + quotedIdentifier(tableName) ) );
    PGresult* result = PQexec(connection, sql.toUtf8());
    QString rel_oid;
    if (PQntuples(result) == 1)
    {
      rel_oid = PQgetvalue(result, 0, 0);
      // Keep the rel_oid for use later one.
      relOid[viewCol] = rel_oid;
    }
    else
    {
      QgsDebugMsg("Relation " + schemaName + "." + tableName +
          " doesn't exist in the pg_class table."
          "This shouldn't happen and is odd.");
      PQclear(result);
      continue;
    }
    PQclear(result);

    // This sql returns one or more rows if the column 'tableCol' in 
    // table 'tableName' and schema 'schemaName' has one or more
    // columns that satisfy the following conditions:
    // 1) the column has data type of int4.
    // 2) the column has a unique constraint or primary key constraint
    //    on it.
    // 3) the constraint applies just to the column of interest (i.e.,
    //    it isn't a constraint over multiple columns.
    sql = QString("select * from pg_constraint where "
                  "conkey[1]=(select attnum from pg_attribute where attname=%1 and attrelid=%2) "
                  "and conrelid=%2 and (contype='p' or contype='u') "
                  "and array_dims(conkey)='[1:1]'").arg( quotedValue(tableCol) ).arg( rel_oid );

    result = PQexec(connection, sql.toUtf8());
    if (PQntuples(result) == 1 && colType == "int4")
      suitable[viewCol] = iter->second;

    QString details = "'" + viewCol + "'" + tr(" derives from ") 
      + "'" + schemaName + "." + tableName + "." + tableCol + "' ";

    if (PQntuples(result) == 1 && colType == "int4")
    {
      details += tr("and is suitable.");
    }
    else
    {
      details += tr("and is not suitable ");
      details += "(" + tr("type is ") + colType;
      if (PQntuples(result) == 1)
        details += tr(" and has a suitable constraint)");
      else
        details += tr(" and does not have a suitable constraint)");
    }

    log << details;

    PQclear(result);
    if (tableCol == "oid")
      oids.push_back(iter);
  }

  // 'oid' columns in tables don't have a constraint on them, but
  // they are useful to consider, so add them in if not already
  // here.
  for (uint i = 0; i < oids.size(); ++i)
  {
    if (suitable.find(oids[i]->first) == suitable.end())
    {
      suitable[oids[i]->first] = oids[i]->second;

      QgsDebugMsg("Adding column " + oids[i]->first + " as it may be suitable.");
    }
  }

  // Now have a map containing all of the columns in the view that
  // might be suitable for use as the key to the table. Need to choose
  // one thus:
  //
  // If there is more than one suitable column pick one that is
  // indexed, else pick one called 'oid' if it exists, else
  // pick the first one. If there are none we return an empty string. 

  // Search for one with an index
  tableCols::const_iterator i = suitable.begin();
  for (; i != suitable.end(); ++i)
  {
    // Get the relation oid from our cache.
    QString rel_oid = relOid[i->first];
    // And see if the column has an index
    sql = QString( "select * from pg_index where indrelid=%1 and indkey[0]=(select attnum from pg_attribute where attrelid=%1 and attname=%2)")
                    .arg( rel_oid )
                    .arg( quotedValue( i->second.column ) );
    PGresult* result = PQexec(connection, sql.toUtf8());

    if (PQntuples(result) > 0 && uniqueData(mSchemaName, mTableName, i->first))
    { // Got one. Use it.
      key = i->first;

      QgsDebugMsg("Picked column '" + key + "' because it has an index.");
      break;
    }
    PQclear(result);
  }

  if (key.isEmpty())
  {
    // If none have indices, choose one that is called 'oid' (if it
    // exists). This is legacy support and could be removed in
    // future. 
    i = suitable.find("oid");
    if (i != suitable.end() && uniqueData(mSchemaName, mTableName, i->first))
    {
      key = i->first;

      QgsDebugMsg("Picked column " + key +
          " as it is probably the postgresql object id "
          " column (which contains unique values) and there are no"
          " columns with indices to choose from.");
    }
    // else choose the first one in the container that has unique data
    else
    {
      tableCols::const_iterator i = suitable.begin();
      for (; i != suitable.end(); ++i)
      {
        if (uniqueData(mSchemaName, mTableName, i->first))
        {
          key = i->first;

          QgsDebugMsg("Picked column " + key +
              " as it was the first suitable column found"
              " with unique data and were are no"
              " columns with indices to choose from");
          break;
        }
        else
        {
          log << QString(tr("Note: ") + "'" + i->first + "' "
              + tr("initially appeared suitable but does not "
                "contain unique data, so is not suitable.\n"));
        }
      }
    }
  }

  if (key.isEmpty())
  {
    valid = false;
    // Successive prepends means that the text appears in the dialog
    // box in the reverse order to that seen here.
    log.prepend(tr("The view you selected has the following columns, none "
          "of which satisfy the above conditions:"));
    log.prepend(tr("Qgis requires that the view has a column that can be used "
          "as a unique key. Such a column should be derived from "
          "a table column of type int4 and be a primary key, "
          "have a unique constraint on it, or be a PostgreSQL "
          "oid column. To improve "
          "performance the column should also be indexed.\n"));
    log.prepend(tr("The view ") + "'" + mSchemaName + '.' + mTableName + "' " +
        tr("has no column suitable for use as a unique key.\n"));
    showMessageBox(tr("No suitable key column in view"), log);
  }

  return key;
}

bool QgsPostgresProvider::uniqueData(QString schemaName, 
    QString tableName, QString colName)
{