qgspostgresprovider.cpp

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

  // Check to see if the given column contains unique data

  bool isUnique = false;

  QString sql = QString("select count(distinct %1)=count(%1) from %2.%3") 
                  .arg( quotedIdentifier(colName) )
                  .arg( quotedIdentifier(schemaName) )
                  .arg( quotedIdentifier(tableName) );

  PGresult* unique = PQexec(connection, sql.toUtf8());

  if (PQntuples(unique)==1 && QString::fromUtf8(PQgetvalue(unique, 0, 0)).startsWith("t"))
    isUnique = true;

  PQclear(unique);

  return isUnique;
}

int QgsPostgresProvider::SRCFromViewColumn(const QString& ns, const QString& relname, const QString& attname_table, const QString& attname_view, const QString& viewDefinition, SRC& result) const
{
  QString newViewDefSql = QString("SELECT definition FROM pg_views WHERE schemaname=%1 AND viewname=%2")
                            .arg( quotedValue(ns) ).arg( quotedValue(relname) );
  PGresult* newViewDefResult = PQexec(connection, newViewDefSql.toUtf8());
  int numEntries = PQntuples(newViewDefResult);

  if(numEntries > 0) //relation is a view
  {
    QString newViewDefinition(QString::fromUtf8(PQgetvalue(newViewDefResult, 0, 0)));

    QString newAttNameView = attname_table;
    QString newAttNameTable = attname_table;

    //find out the attribute name of the underlying table/view
    if (newViewDefinition.contains(" AS "))
    {
      QRegExp s("(\\w+)" + QString(" AS ") + QRegExp::escape(attname_table));
      if (s.indexIn(newViewDefinition) != -1)
      {
        newAttNameTable = s.cap(1);
      }
    }

    QString viewColumnSql =
      QString("SELECT "
                 "table_schema,"
                 "table_name,"
                 "column_name"
              " FROM "
                "("
                  "SELECT DISTINCT "
                    "current_database()::information_schema.sql_identifier AS view_catalog,"
                    "nv.nspname::information_schema.sql_identifier AS view_schema,"
                    "v.relname::information_schema.sql_identifier AS view_name,"
                    "current_database()::information_schema.sql_identifier AS table_catalog,"
                    "nt.nspname::information_schema.sql_identifier AS table_schema,"
                    "t.relname::information_schema.sql_identifier AS table_name,"
                    "a.attname::information_schema.sql_identifier AS column_name"
                  " FROM "
                    "pg_namespace nv,"
                    "pg_class v,"
                    "pg_depend dv,"
                    "pg_depend dt,"
                    "pg_class t,"
                    "pg_namespace nt,"
                    "pg_attribute a"
                  " WHERE "
                    "nv.oid=v.relnamespace AND "
                    "v.relkind='v'::\"char\" AND "
                    "v.oid=dv.refobjid AND "
                    "dv.refclassid='pg_class'::regclass::oid AND "
                    "dv.classid='pg_rewrite'::regclass::oid AND "
                    "dv.deptype='i'::\"char\" AND "
                    "dv.objid = dt.objid AND "
                    "dv.refobjid<>dt.refobjid AND "
                    "dt.classid='pg_rewrite'::regclass::oid AND "
                    "dt.refclassid='pg_class'::regclass::oid AND "
                    "dt.refobjid=t.oid AND "
                    "t.relnamespace = nt.oid AND "
                    "(t.relkind=ANY (ARRAY['r'::\"char\", 'v'::\"char\"])) AND "
                    "t.oid=a.attrelid AND "
                    "dt.refobjsubid=a.attnum"
                  " ORDER BY "
                    "current_database()::information_schema.sql_identifier,"
                    "nv.nspname::information_schema.sql_identifier,"
                    "v.relname::information_schema.sql_identifier,"
                    "current_database()::information_schema.sql_identifier,"
                    "nt.nspname::information_schema.sql_identifier,"
                    "t.relname::information_schema.sql_identifier,"
                    "a.attname::information_schema.sql_identifier"
                ") x"
               " WHERE "
                 "view_schema=%1 AND "
                 "view_name=%2 AND "
                 "column_name=%3")
             .arg( quotedValue(ns) )
             .arg( quotedValue(relname) )
             .arg( quotedValue(newAttNameTable) );

    PGresult* viewColumnResult = PQexec(connection, viewColumnSql.toUtf8());
    if(PQntuples(viewColumnResult) > 0)
    {
      QString newTableSchema = QString::fromUtf8(PQgetvalue(viewColumnResult, 0, 0));
      QString newTableName = QString::fromUtf8(PQgetvalue(viewColumnResult, 0, 1));
      int retvalue = SRCFromViewColumn(newTableSchema, newTableName, newAttNameTable, newAttNameView, newViewDefinition, result);
      PQclear(viewColumnResult);
      return retvalue;
    }
    else
    {
      PQclear(viewColumnResult);
      return 1;
    }

  }

  PQclear(newViewDefResult);

  //relation is table, we just have to add the type
  QString typeSql = QString("SELECT "
                              "pg_type.typname"
                            " FROM "
                              "pg_attribute,"
                              "pg_class,"
                              "pg_namespace,"
                              "pg_type"
                            " WHERE "
                              "pg_class.relname=%1 AND "
                              "pg_namespace.nspname=%2 AND "
                              "pg_attribute.attname=%3 AND "
                              "pg_attribute.attrelid=pg_class.oid AND "
                              "pg_class.relnamespace=pg_namespace.oid AND "
                              "pg_attribute.atttypid=pg_type.oid")
                      .arg( quotedValue(relname ) )
                      .arg( quotedValue(ns) )
                      .arg( quotedValue(attname_table) );
  
  PGresult* typeSqlResult = PQexec(connection, typeSql.toUtf8());
  if(PQntuples(typeSqlResult) < 1)
  {
    return 1;
  }
  QString type = QString::fromUtf8(PQgetvalue(typeSqlResult, 0, 0));
  PQclear(typeSqlResult);

  result.schema=ns;
  result.relation=relname;
  result.column=attname_table;
  result.type=type;
  return 0;
}

// This function will return in the cols variable the 
// underlying view and columns for each column in
// mSchemaName.mTableName.

void QgsPostgresProvider::findColumns(tableCols& cols)
{
  QString viewColumnSql =
    QString("SELECT "
              "table_schema,"
              "table_name,"
              "column_name"
            " FROM "
              "("
                "SELECT DISTINCT "
                  "current_database() AS view_catalog,"
                  "nv.nspname AS view_schema,"
                  "v.relname AS view_name,"
                  "current_database() AS table_catalog,"
                  "nt.nspname AS table_schema,"
                  "t.relname AS table_name,"
                  "a.attname AS column_name"
                " FROM "
                  "pg_namespace nv,"
                  "pg_class v,"
                  "pg_depend dv,"
                  "pg_depend dt,"
                  "pg_class t,"
                  "pg_namespace nt,"
                  "pg_attribute a"
                " WHERE "
                  "nv.oid=v.relnamespace AND "
                  "v.relkind='v'::\"char\" AND "
                  "v.oid=dv.refobjid AND "
                  "dv.refclassid='pg_class'::regclass::oid AND "
                  "dv.classid='pg_rewrite'::regclass::oid AND "
                  "dv.deptype='i'::\"char\" AND "
                  "dv.objid=dt.objid AND "
                  "dv.refobjid<>dt.refobjid AND "
                  "dt.classid='pg_rewrite'::regclass::oid AND "
                  "dt.refclassid='pg_class'::regclass::oid AND "
                  "dt.refobjid=t.oid AND "
                  "t.relnamespace=nt.oid AND "
                  "(t.relkind = ANY (ARRAY['r'::\"char\",'v'::\"char\"])) AND "
                  "t.oid=a.attrelid AND "
                  "dt.refobjsubid=a.attnum"
                " ORDER BY "
                  "current_database(),"
                  "nv.nspname,"
                  "v.relname,"
                  "current_database(),"
                  "nt.nspname,"
                  "t.relname,"
                  "a.attname"
                ") x"
              " WHERE "
                "view_schema=%1 AND view_name=%2")
              .arg( quotedValue(mSchemaName) )
              .arg( quotedValue(mTableName) );
  PGresult* viewColumnResult = PQexec(connection, viewColumnSql.toUtf8());

  //find out view definition
  QString viewDefSql = QString("SELECT definition FROM pg_views WHERE schemaname=%1 AND viewname=%2")
                         .arg( quotedValue( mSchemaName ) )
                         .arg( quotedValue( mTableName ) );
  PGresult* viewDefResult = PQexec(connection, viewDefSql.toUtf8());
  if(PQntuples(viewDefResult) < 1)
  {
    PQclear(viewDefResult);
    return;
  }
  QString viewDefinition(QString::fromUtf8(PQgetvalue(viewDefResult, 0, 0)));
  PQclear(viewDefResult);

  QString ns, relname, attname_table, attname_view;
  SRC columnInformation;

  for(int i = 0; i < PQntuples(viewColumnResult); ++i)
  {
    ns = QString::fromUtf8(PQgetvalue(viewColumnResult, i, 0));
    relname = QString::fromUtf8(PQgetvalue(viewColumnResult, i, 1));
    attname_table = QString::fromUtf8(PQgetvalue(viewColumnResult, i, 2));

    //find out original attribute name
    attname_view = attname_table;

    //examine if the column name has been renamed in the view with AS
    if (viewDefinition.contains(" AS "))
    {
      // This regular expression needs more testing. Since the view
      // definition comes from postgresql and has been 'standardised', we
      // don't need to deal with everything that the user could put in a view
      // definition. Does the regexp have to deal with the schema??

      QRegExp s(".* \"?" + QRegExp::escape(relname) +
          "\"?\\.\"?" + QRegExp::escape(attname_table) +
          "\"? AS \"?(\\w+)\"?,* .*");

      QgsDebugMsg(viewDefinition + "\n" + s.pattern());

      if (s.indexIn(viewDefinition) != -1)
      {
        attname_view = s.cap(1);
        qWarning("original view column name was: " + attname_view);
      }
    }

    SRCFromViewColumn(ns, relname, attname_table, attname_view, viewDefinition, columnInformation);
    cols.insert(std::make_pair(attname_view, columnInformation));
    QgsDebugMsg("Inserting into cols (for key " + attname_view + " ): " + columnInformation.schema + "." + columnInformation.relation + "." + columnInformation.column + "." + columnInformation.type);
  }
  PQclear(viewColumnResult);

}

// Returns the minimum value of an attribute
QVariant QgsPostgresProvider::minValue(int index)
{
  try
  {
    // get the field name 
    const QgsField &fld = field(index);
    QString sql;
    if(sqlWhereClause.isEmpty())
    {
      sql = QString("select min(%1) from %2")
        .arg(quotedIdentifier(fld.name()))
        .arg(mSchemaTableName);
    }
    else
    {
      sql = QString("select min(%1) from %2 where %3")
        .arg(quotedIdentifier(fld.name()))
        .arg(mSchemaTableName)
        .arg(sqlWhereClause);
    }
    PGresult *rmin = PQexec(connection, sql.toUtf8());
    QString minValue = QString::fromUtf8(PQgetvalue(rmin,0,0));
    PQclear(rmin);
    return minValue.toDouble();
  }
  catch(PGFieldNotFound)
  {
    return QVariant(QString::null);
  }
}

// Returns the list of unique values of an attribute
void QgsPostgresProvider::getUniqueValues(int index, QStringList &uniqueValues)
{
  uniqueValues.clear();

  try
  {
    // get the field name 
    const QgsField &fld = field(index);
    QString sql;
    if(sqlWhereClause.isEmpty())
    {
      sql = QString("select distinct %1 from %2 order by %1")
        .arg(quotedIdentifier(fld.name()))
        .arg(mSchemaTableName);
    }
    else
    {
      sql = QString("select distinct %1 from %2 where %3 order by %1")
        .arg(quotedIdentifier(fld.name()))
        .arg(mSchemaTableName)
        .arg(sqlWhereClause);
    }

    PGresult *res= PQexec(connection, sql.toUtf8());
    if (PQresultStatus(res) == PGRES_TUPLES_OK)
    {
      for(int i=0; i<PQntuples(res); i++)
        uniqueValues.append( QString::fromUtf8(PQgetvalue(res,i,0)) );
    }
    PQclear(res);
  }
  catch(PGFieldNotFound)
  { 
  }
}

// Returns the maximum value of an attribute

QVariant QgsPostgresProvider::maxValue(int index)
{
  try
  {
    // get the field name 
    const QgsField &fld = field(index);
    QString sql;
    if(sqlWhereClause.isEmpty())
    {
      sql = QString("select max(%1) from %2")
        .arg(quotedIdentifier(fld.name()))
        .arg(mSchemaTableName);
    }
    else
    {
      sql = QString("select max(%1) from %2 where %3")
        .arg(quotedIdentifier(fld.name()))
        .arg(mSchemaTableName)
        .arg(sqlWhereClause);
    } 
    PGresult *rmax = PQexec(connection, sql.toUtf8());
    QString maxValue = QString::fromUtf8(PQgetvalue(rmax,0,0));
    PQclear(rmax);
    return maxValue.toDouble();
  }
  catch(PGFieldNotFound)
  {
    return QVariant(QString::null);
  }
}


int QgsPostgresProvider::maxPrimaryKeyValue()
{
  QString sql;

  sql = QString("select max(%1) from %2")
    .arg(quotedIdentifier(primaryKey))
    .arg(mSchemaTableName);

  PGresult *rmax = PQexec(connection, sql.toUtf8());
  QString maxValue = QString::fromUtf8(PQgetvalue(rmax,0,0));
  PQclear(rmax);

  return maxValue.toInt();
}


bool QgsPostgresProvider::isValid(){
  return valid;
}

QVariant QgsPostgresProvider::getDefaultValue(int fieldId)
{
  try
  {
    // Get the default column value from the Postgres information
    // schema. If there is no default we return an empty string.

    // Maintaining a cache of the results of this query would be quite
    // simple and if this query is called lots, could save some time.
    QString fieldName = field(fieldId).name();

    QString sql("SELECT column_default FROM"
      " information_schema.columns WHERE"
      " column_default IS NOT NULL"
      " AND table_schema = " + quotedValue(mSchemaName) +
      " AND table_name = " + quotedValue(mTableName) +
      " AND column_name = " + quotedValue(fieldName) );

    QVariant defaultValue(QString::null);

    PGresult* result = PQexec(connection, sql.toUtf8());

    if (PQntuples(result)==1 && !PQgetisnull(result, 0, 0) )
      defaultValue = QString::fromUtf8(PQgetvalue(result, 0, 0));