Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
speed up loading of projects with (complex) views:
save key column of postgresql layers to projects file and try it first on
reload.



git-svn-id: http://svn.osgeo.org/qgis/trunk@10657 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
jef committed Apr 26, 2009
1 parent 3cbf135 commit 468550c
Show file tree
Hide file tree
Showing 4 changed files with 74 additions and 19 deletions.
7 changes: 6 additions & 1 deletion python/core/qgsdatasourceuri.sip
Expand Up @@ -43,7 +43,8 @@ public:
void setDataSource(const QString& aSchema,
const QString& aTable,
const QString& aGeometryColumn,
const QString& aSql = QString());
const QString& aSql = QString(),
const QString& aKeyColumn = QString());

/** Removes password from uris
* @note this method was added in QGIS 1.1
Expand All @@ -64,4 +65,8 @@ public:
SSLmode sslMode() const;

void setSql(QString sql);

// added in 1.2
QString keyColumn() const;
void setKeyColumn(QString column);
};
23 changes: 20 additions & 3 deletions src/core/qgsdatasourceuri.cpp
Expand Up @@ -23,12 +23,12 @@
#include <QStringList>
#include <QRegExp>

QgsDataSourceURI::QgsDataSourceURI() : mSSLmode( SSLprefer )
QgsDataSourceURI::QgsDataSourceURI() : mSSLmode( SSLprefer ), mKeyColumn( "" )
{
// do nothing
}

QgsDataSourceURI::QgsDataSourceURI( QString uri ) : mSSLmode( SSLprefer )
QgsDataSourceURI::QgsDataSourceURI( QString uri ) : mSSLmode( SSLprefer ), mKeyColumn( "" )
{
int i = 0;
while ( i < uri.length() )
Expand Down Expand Up @@ -104,6 +104,10 @@ QgsDataSourceURI::QgsDataSourceURI( QString uri ) : mSSLmode( SSLprefer )
i++;
}
}
else if ( pname == "key" )
{
mKeyColumn = pval;
}
else if ( pname == "service" )
{
QgsDebugMsg( "service keyword ignored" );
Expand Down Expand Up @@ -257,6 +261,16 @@ QString QgsDataSourceURI::geometryColumn() const
return mGeometryColumn;
}

QString QgsDataSourceURI::keyColumn() const
{
return mKeyColumn;
}

void QgsDataSourceURI::setKeyColumn( QString column )
{
mKeyColumn = column;
}

void QgsDataSourceURI::setSql( QString sql )
{
mSql = sql;
Expand Down Expand Up @@ -376,7 +390,8 @@ QString QgsDataSourceURI::connectionInfo() const
QString QgsDataSourceURI::uri() const
{
return connectionInfo()
+ QString( " table=%1 (%2) sql=%3" )
+ QString( " key='%1' table=%2 (%3) sql=%4" )
.arg( keyColumn() )
.arg( quotedTablename() )
.arg( mGeometryColumn )
.arg( mSql );
Expand Down Expand Up @@ -408,10 +423,12 @@ void QgsDataSourceURI::setConnection( const QString &host,
void QgsDataSourceURI::setDataSource( const QString &schema,
const QString &table,
const QString &geometryColumn,
const QString &keyColumn,
const QString &sql )
{
mSchema = schema;
mTable = table;
mGeometryColumn = geometryColumn;
mKeyColumn = keyColumn;
mSql = sql;
}
9 changes: 8 additions & 1 deletion src/core/qgsdatasourceuri.h
Expand Up @@ -61,7 +61,8 @@ class CORE_EXPORT QgsDataSourceURI
void setDataSource( const QString& aSchema,
const QString& aTable,
const QString& aGeometryColumn,
const QString& aSql = QString() );
const QString& aSql = QString(),
const QString& aKeyColumn = QString() );

//! Removes password element from uris
static QString removePassword( const QString& aUri );
Expand All @@ -82,6 +83,10 @@ class CORE_EXPORT QgsDataSourceURI
QString password() const;
enum SSLmode sslMode() const;

// added in version 1.2
QString keyColumn() const;
void setKeyColumn( QString column );

private:
void skipBlanks( const QString &uri, int &i );
QString getValue( const QString &uri, int &i );
Expand All @@ -108,6 +113,8 @@ class CORE_EXPORT QgsDataSourceURI
QString mPassword;
//! ssl mode
enum SSLmode mSSLmode;
//! key column
QString mKeyColumn;
};

#endif //QGSDATASOURCEURI_H
Expand Down
54 changes: 40 additions & 14 deletions src/providers/postgres/qgspostgresprovider.cpp
Expand Up @@ -74,6 +74,7 @@ QgsPostgresProvider::QgsPostgresProvider( QString const & uri )
mTableName = mUri.table();
geometryColumn = mUri.geometryColumn();
sqlWhereClause = mUri.sql();
primaryKey = mUri.keyColumn();

// Keep a schema qualified table name for convenience later on.
mSchemaTableName = mUri.quotedTablename();
Expand Down Expand Up @@ -483,15 +484,15 @@ bool QgsPostgresProvider::getFeature( PGresult *queryResult, int row, bool fetch

if ( block > 0xffff )
{
qWarning( "block number %x exceed 16 bit", block );
QgsDebugMsg( QString( "block number %1 exceeds 16 bit" ).arg( block ) );
return false;
}

oid = ( block << 16 ) + offset;
}
else
{
qWarning( "expecting 6 bytes for tid (found %d bytes)", PQgetlength( queryResult, row, 0 ) );
QgsDebugMsg( QString( "expecting 6 bytes for tid (found %1 bytes)" ).arg( PQgetlength( queryResult, row, 0 ) ) );
return false;
}

Expand Down Expand Up @@ -623,7 +624,7 @@ bool QgsPostgresProvider::nextFeature( QgsFeature& feature )
QString fetch = QString( "fetch forward %1 from %2" ).arg( mFeatureQueueSize ).arg( cursorName );
if ( connectionRO->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
qWarning( "PQsendQuery failed (1)" );
QgsDebugMsg( "PQsendQuery failed (1)" );
}

Result queryResult;
Expand Down Expand Up @@ -948,12 +949,12 @@ QString QgsPostgresProvider::getPrimaryKey()
Result tableType = connectionRO->PQexec( sql );
QString type = QString::fromUtf8( PQgetvalue( tableType, 0, 0 ) );

primaryKey = "";

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

primaryKey = "";

// 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)" )
Expand Down Expand Up @@ -1008,15 +1009,40 @@ QString QgsPostgresProvider::getPrimaryKey()
}
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 );
if ( !primaryKey.isEmpty() )
{
// check last used candidate
sql = QString( "select pg_type.typname from pg_attribute,pg_type where atttypid=pg_type.oid and attname=%1 and attrelid=regclass(%2)" )
.arg( quotedValue( primaryKey ) ).arg( quotedValue( mSchemaTableName ) );

QgsDebugMsg( "checking candidate: " + sql );

Result result = connectionRO->PQexec( sql );

if ( PQresultStatus( result ) != PGRES_TUPLES_OK ||
PQntuples( result ) != 1 ||
QString( PQgetvalue( result, 0, 0 ) ) != "int4" ||
!uniqueData( mSchemaName, mTableName, primaryKey ) )
{
primaryKey = "";
}
}

if ( primaryKey.isEmpty() )
{
// 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 );

mUri.setKeyColumn( primaryKey );
setDataSourceUri( mUri.uri() );
}
}
else
QgsDebugMsg( "Unexpected relation type of '" + type + "'." );
Expand Down

0 comments on commit 468550c

Please sign in to comment.