void QgsPostgresConn::deduceEndian()
{
// need to store the PostgreSQL endian format used in binary cursors
// since it appears that starting with
// version 7.4, binary cursors return data in XDR whereas previous versions
// return data in the endian of the server
QgsPostgresResult res = PQexec( "select regclass('pg_class')::oid" );
QString oidValue = res.PQgetvalue( 0, 0 );
QgsDebugMsg( "Creating binary cursor" );
// get the same value using a binary cursor
openCursor( "oidcursor", "select regclass('pg_class')::oid" );
QgsDebugMsg( "Fetching a record and attempting to get check endian-ness" );
res = PQexec( "fetch forward 1 from oidcursor" );
mSwapEndian = true;
if ( res.PQntuples() > 0 )
{
// get the oid value from the binary cursor
qint64 oid = getBinaryInt( res, 0, 0 );
QgsDebugMsg( QString( "Got oid of %1 from the binary cursor" ).arg( oid ) );
QgsDebugMsg( QString( "First oid is %1" ).arg( oidValue ) );
// compare the two oid values to determine if we need to do an endian swap
if ( oid != oidValue.toLongLong() )
mSwapEndian = false;
}
closeCursor( "oidcursor" );
}
bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature& feature )
{
feature.setValid( false );
if ( mClosed )
return false;
if ( mFeatureQueue.empty() )
{
QString fetch = QString( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
QgsDebugMsgLevel( QString( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
if ( mConn->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
}
QgsPostgresResult queryResult;
for ( ;; )
{
queryResult = mConn->PQgetResult();
if ( !queryResult.result() )
break;
if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName, mConn->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
break;
}
int rows = queryResult.PQntuples();
if ( rows == 0 )
continue;
for ( int row = 0; row < rows; row++ )
{
mFeatureQueue.enqueue( QgsFeature() );
getFeature( queryResult, row, mFeatureQueue.back() );
} // for each row in queue
}
}
if ( mFeatureQueue.empty() )
{
QgsDebugMsg( QString( "Finished after %1 features" ).arg( mFetched ) );
close();
mSource->mShared->ensureFeaturesCountedAtLeast( mFetched );
return false;
}
feature = mFeatureQueue.dequeue();
mFetched++;
feature.setValid( true );
feature.setFields( mSource->mFields ); // allow name-based attribute lookups
return true;
}
bool QgsPostgresConn::PQexecNR( QString query, bool retry )
{
QgsPostgresResult res = PQexec( query, false );
ExecStatusType errorStatus = res.PQresultStatus();
if ( errorStatus == PGRES_COMMAND_OK )
return true;
QgsMessageLog::logMessage( tr( "Query: %1 returned %2 [%3]" )
.arg( query )
.arg( errorStatus )
.arg( res.PQresultErrorMessage() ),
tr( "PostGIS" ) );
if ( mOpenCursors )
{
QgsMessageLog::logMessage( tr( "%1 cursor states lost.\nSQL: %2\nResult: %3 (%4)" )
.arg( mOpenCursors ).arg( query ).arg( errorStatus )
.arg( res.PQresultErrorMessage() ), tr( "PostGIS" ) );
mOpenCursors = 0;
}
if ( PQstatus() == CONNECTION_OK )
{
PQexecNR( "ROLLBACK" );
}
else if ( retry )
{
QgsMessageLog::logMessage( tr( "resetting bad connection." ), tr( "PostGIS" ) );
::PQreset( mConn );
if ( PQstatus() == CONNECTION_OK )
{
if ( PQexecNR( query, false ) )
{
QgsMessageLog::logMessage( tr( "retry after reset succeeded." ), tr( "PostGIS" ) );
return true;
}
else
{
QgsMessageLog::logMessage( tr( "retry after reset failed again." ), tr( "PostGIS" ) );
return false;
}
}
else
{
QgsMessageLog::logMessage( tr( "connection still bad after reset." ), tr( "PostGIS" ) );
}
}
else
{
QgsMessageLog::logMessage( tr( "bad connection, not retrying." ), tr( "PostGIS" ) );
}
return false;
}
bool QgsPostgresTransaction::executeSql( const QString &sql, QString &errorMsg )
{
if ( !mConn )
{
return false;
}
QgsDebugMsg( QString( "Transaction sql: %1" ).arg( sql ) );
mConn->lock();
QgsPostgresResult r = mConn->PQexec( sql, true );
mConn->unlock();
if ( r.PQresultStatus() != PGRES_COMMAND_OK )
{
errorMsg = QString( "Status %1 (%2)" ).arg( r.PQresultStatus() ).arg( r.PQresultErrorMessage() );
QgsDebugMsg( errorMsg );
return false;
}
QgsDebugMsg( QString( "Status %1 (OK)" ).arg( r.PQresultStatus() ) );
return true;
}
QStringList QgsPostgresConn::pkCandidates( QString schemaName, QString viewName )
{
QStringList cols;
QString sql = QString( "SELECT attname FROM pg_attribute JOIN pg_type ON atttypid=pg_type.oid WHERE pg_type.typname IN ('int2','int4','int8','oid','serial','serial8') AND attrelid=regclass('%1.%2')" )
.arg( quotedIdentifier( schemaName ) )
.arg( quotedIdentifier( viewName ) );
QgsDebugMsg( sql );
QgsPostgresResult colRes = PQexec( sql );
if ( colRes.PQresultStatus() == PGRES_TUPLES_OK )
{
for ( int i = 0; i < colRes.PQntuples(); i++ )
{
QgsDebugMsg( colRes.PQgetvalue( i, 0 ) );
cols << colRes.PQgetvalue( i, 0 );
}
}
else
{
QgsMessageLog::logMessage( tr( "SQL:%1\nresult:%2\nerror:%3\n" ).arg( sql ).arg( colRes.PQresultStatus() ).arg( colRes.PQresultErrorMessage() ), tr( "PostGIS" ) );
}
return cols;
}
bool QgsPostgresFeatureIterator::nextFeature( QgsFeature& feature )
{
feature.setValid( false );
if ( mClosed )
return false;
#if 0
// featureAtId used to have some special checks - necessary?
if ( !mUseQueue )
{
QgsPostgresResult queryResult = P->mConnectionRO->PQexec( QString( "FETCH FORWARD 1 FROM %1" ).arg( mCursorName ) );
int rows = queryResult.PQntuples();
if ( rows == 0 )
{
QgsMessageLog::logMessage( tr( "feature %1 not found" ).arg( featureId ), tr( "PostGIS" ) );
P->mConnectionRO->closeCursor( cursorName );
return false;
}
else if ( rows != 1 )
{
QgsMessageLog::logMessage( tr( "found %1 features instead of just one." ).arg( rows ), tr( "PostGIS" ) );
}
bool gotit = getFeature( queryResult, 0, feature );
feature.setValid( gotit );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
return gotit;
}
#endif
if ( mFeatureQueue.empty() )
{
QString fetch = QString( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
QgsDebugMsgLevel( QString( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
if ( P->mConnectionRO->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
}
QgsPostgresResult queryResult;
for ( ;; )
{
queryResult = P->mConnectionRO->PQgetResult();
if ( !queryResult.result() )
break;
if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
break;
}
int rows = queryResult.PQntuples();
if ( rows == 0 )
continue;
for ( int row = 0; row < rows; row++ )
{
mFeatureQueue.enqueue( QgsFeature() );
getFeature( queryResult, row, mFeatureQueue.back() );
} // for each row in queue
}
}
if ( mFeatureQueue.empty() )
{
QgsDebugMsg( QString( "Finished after %1 features" ).arg( mFetched ) );
close();
if ( P->mFeaturesCounted < mFetched )
{
QgsDebugMsg( QString( "feature count adjusted from %1 to %2" ).arg( P->mFeaturesCounted ).arg( mFetched ) );
P->mFeaturesCounted = mFetched;
}
return false;
}
// Now return the next feature from the queue
if ( mRequest.flags() & QgsFeatureRequest::NoGeometry )
{
feature.setGeometryAndOwnership( 0, 0 );
}
else
{
QgsGeometry* featureGeom = mFeatureQueue.front().geometryAndOwnership();
feature.setGeometry( featureGeom );
}
feature.setFeatureId( mFeatureQueue.front().id() );
feature.setAttributes( mFeatureQueue.front().attributes() );
mFeatureQueue.dequeue();
mFetched++;
feature.setValid( true );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
return true;
}
bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int row, QgsFeature &feature )
{
try
{
feature.initAttributes( P->fields().count() );
int col = 0;
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) )
{
int returnedLength = ::PQgetlength( queryResult.result(), row, col );
if ( returnedLength > 0 )
{
unsigned char *featureGeom = new unsigned char[returnedLength + 1];
memset( featureGeom, 0, returnedLength + 1 );
memcpy( featureGeom, PQgetvalue( queryResult.result(), row, col ), returnedLength );
feature.setGeometryAndOwnership( featureGeom, returnedLength + 1 );
}
else
{
feature.setGeometryAndOwnership( 0, 0 );
}
col++;
}
QgsFeatureId fid = 0;
bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
switch ( P->mPrimaryKeyType )
{
case QgsPostgresProvider::pktOid:
case QgsPostgresProvider::pktTid:
case QgsPostgresProvider::pktInt:
fid = P->mConnectionRO->getBinaryInt( queryResult, row, col++ );
if ( P->mPrimaryKeyType == QgsPostgresProvider::pktInt &&
( !subsetOfAttributes || fetchAttributes.contains( P->mPrimaryKeyAttrs[0] ) ) )
feature.setAttribute( P->mPrimaryKeyAttrs[0], fid );
break;
case QgsPostgresProvider::pktFidMap:
{
QList<QVariant> primaryKeyVals;
foreach ( int idx, P->mPrimaryKeyAttrs )
{
const QgsField &fld = P->field( idx );
QVariant v = P->convertValue( fld.type(), queryResult.PQgetvalue( row, col ) );
primaryKeyVals << v;
if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
feature.setAttribute( idx, v );
col++;
}
fid = P->lookupFid( QVariant( primaryKeyVals ) );
}
break;
case QgsPostgresProvider::pktUnknown:
Q_ASSERT( !"FAILURE: cannot get feature with unknown primary key" );
return false;
}
feature.setFeatureId( fid );
QgsDebugMsgLevel( QString( "fid=%1" ).arg( fid ), 4 );
// iterate attributes
if ( subsetOfAttributes )
{
foreach ( int idx, fetchAttributes )
getFeatureAttribute( idx, queryResult, row, col, feature );
}
else
{
for ( int idx = 0; idx < P->mAttributeFields.count(); ++idx )
getFeatureAttribute( idx, queryResult, row, col, feature );
}
return true;
}
qint64 QgsPostgresConn::getBinaryInt( QgsPostgresResult &queryResult, int row, int col )
{
quint64 oid;
char *p = PQgetvalue( queryResult.result(), row, col );
size_t s = PQgetlength( queryResult.result(), row, col );
#ifdef QGISDEBUG
if ( QgsLogger::debugLevel() >= 4 )
{
QString buf;
for ( size_t i = 0; i < s; i++ )
{
buf += QString( "%1 " ).arg( *( unsigned char * )( p + i ), 0, 16, QLatin1Char( ' ' ) );
}
QgsDebugMsg( QString( "int in hex:%1" ).arg( buf ) );
}
#endif
switch ( s )
{
case 2:
oid = *( quint16 * )p;
if ( mSwapEndian )
oid = ntohs( oid );
break;
case 6:
{
quint64 block = *( quint32 * ) p;
quint64 offset = *( quint16 * )( p + sizeof( quint32 ) );
if ( mSwapEndian )
{
block = ntohl( block );
offset = ntohs( offset );
}
oid = ( block << 16 ) + offset;
}
break;
case 8:
{
quint32 oid0 = *( quint32 * ) p;
quint32 oid1 = *( quint32 * )( p + sizeof( quint32 ) );
if ( mSwapEndian )
{
QgsDebugMsgLevel( QString( "swap oid0:%1 oid1:%2" ).arg( oid0 ).arg( oid1 ), 4 );
oid0 = ntohl( oid0 );
oid1 = ntohl( oid1 );
}
QgsDebugMsgLevel( QString( "oid0:%1 oid1:%2" ).arg( oid0 ).arg( oid1 ), 4 );
oid = oid0;
QgsDebugMsgLevel( QString( "oid:%1" ).arg( oid ), 4 );
oid <<= 32;
QgsDebugMsgLevel( QString( "oid:%1" ).arg( oid ), 4 );
oid |= oid1;
QgsDebugMsgLevel( QString( "oid:%1" ).arg( oid ), 4 );
}
break;
default:
QgsDebugMsg( QString( "unexpected size %1" ).arg( s ) );
case 4:
oid = *( quint32 * )p;
if ( mSwapEndian )
oid = ntohl( oid );
break;
}
return oid;
}
/* Functions for determining available features in postGIS */
QString QgsPostgresConn::postgisVersion()
{
if ( mGotPostgisVersion )
return mPostgisVersionInfo;
mPostgresqlVersion = PQserverVersion( mConn );
QgsPostgresResult result = PQexec( "SELECT postgis_version()" );
if ( result.PQntuples() != 1 )
{
QgsMessageLog::logMessage( tr( "Retrieval of postgis version failed" ), tr( "PostGIS" ) );
return QString::null;
}
mPostgisVersionInfo = result.PQgetvalue( 0, 0 );
QgsDebugMsg( "PostGIS version info: " + mPostgisVersionInfo );
QStringList postgisParts = mPostgisVersionInfo.split( " ", QString::SkipEmptyParts );
// Get major and minor version
QStringList postgisVersionParts = postgisParts[0].split( ".", QString::SkipEmptyParts );
if ( postgisVersionParts.size() < 2 )
{
QgsMessageLog::logMessage( tr( "Could not parse postgis version string '%1'" ).arg( mPostgisVersionInfo ), tr( "PostGIS" ) );
return QString::null;
}
mPostgisVersionMajor = postgisVersionParts[0].toInt();
mPostgisVersionMinor = postgisVersionParts[1].toInt();
mUseWkbHex = mPostgisVersionMajor < 1;
// apparently postgis 1.5.2 doesn't report capabilities in postgis_version() anymore
if ( mPostgisVersionMajor > 1 || ( mPostgisVersionMajor == 1 && mPostgisVersionMinor >= 5 ) )
{
result = PQexec( "SELECT postgis_geos_version(),postgis_proj_version()" );
mGeosAvailable = result.PQntuples() == 1 && !result.PQgetisnull( 0, 0 );
mProjAvailable = result.PQntuples() == 1 && !result.PQgetisnull( 0, 1 );
QgsDebugMsg( QString( "geos:%1 proj:%2" )
.arg( mGeosAvailable ? result.PQgetvalue( 0, 0 ) : "none" )
.arg( mProjAvailable ? result.PQgetvalue( 0, 1 ) : "none" ) );
mGistAvailable = true;
}
else
{
// assume no capabilities
mGeosAvailable = false;
mGistAvailable = false;
mProjAvailable = false;
// parse out the capabilities and store them
QStringList geos = postgisParts.filter( "GEOS" );
if ( geos.size() == 1 )
{
mGeosAvailable = ( geos[0].indexOf( "=1" ) > -1 );
}
QStringList gist = postgisParts.filter( "STATS" );
if ( gist.size() == 1 )
{
mGistAvailable = ( geos[0].indexOf( "=1" ) > -1 );
}
QStringList proj = postgisParts.filter( "PROJ" );
if ( proj.size() == 1 )
{
mProjAvailable = ( proj[0].indexOf( "=1" ) > -1 );
}
}
// checking for topology support
QgsDebugMsg( "Checking for topology support" );
mTopologyAvailable = false;
if ( mPostgisVersionMajor > 1 )
{
QgsPostgresResult result = PQexec( "SELECT count(c.oid) FROM pg_class AS c JOIN pg_namespace AS n ON c.relnamespace=n.oid WHERE n.nspname='topology' AND c.relname='topology'" );
if ( result.PQntuples() >= 1 )
{
mTopologyAvailable = true;
}
}
mGotPostgisVersion = true;
return mPostgisVersionInfo;
}
bool QgsPostgresConn::getTableInfo( bool searchGeometryColumnsOnly, bool searchPublicOnly, bool allowGeometrylessTables )
{
int nColumns = 0;
int foundInTables = 0;
QgsPostgresResult result;
QgsPostgresLayerProperty layerProperty;
QgsDebugMsg( "Entering." );
mLayersSupported.clear();
for ( int i = 0; i < 3; i++ )
{
QString sql, tableName, schemaName, columnName, typeName, sridName, gtableName;
QgsPostgresGeometryColumnType columnType = sctGeometry;
if ( i == 0 )
{
tableName = "l.f_table_name";
schemaName = "l.f_table_schema";
columnName = "l.f_geometry_column";
typeName = "upper(l.type)";
sridName = "l.srid";
gtableName = "geometry_columns";
columnType = sctGeometry;
}
else if ( i == 1 )
{
tableName = "l.f_table_name";
schemaName = "l.f_table_schema";
columnName = "l.f_geography_column";
typeName = "upper(l.type)";
sridName = "l.srid";
gtableName = "geography_columns";
columnType = sctGeography;
}
else if ( i == 2 )
{
schemaName = "l.schema_name";
tableName = "l.table_name";
columnName = "l.feature_column";
typeName = "CASE "
"WHEN l.feature_type = 1 THEN 'MULTIPOINT' "
"WHEN l.feature_type = 2 THEN 'MULTILINESTRING' "
"WHEN l.feature_type = 3 THEN 'MULTIPOLYGON' "
"WHEN l.feature_type = 4 THEN 'GEOMETRYCOLLECTION' "
"END AS type";
sridName = "(SELECT srid FROM topology.topology t WHERE l.topology_id=t.id)";
gtableName = "topology.layer";
columnType = sctTopoGeometry;
}
// The following query returns only tables that exist and the user has SELECT privilege on.
// Can't use regclass here because table must exist, else error occurs.
sql = QString( "SELECT %1,%2,%3,%4,%5,c.relkind"
" FROM %6 l,pg_class c,pg_namespace n"
" WHERE c.relname=%1"
" AND %2=n.nspname"
" AND n.oid=c.relnamespace"
" AND has_schema_privilege(n.nspname,'usage')"
" AND has_table_privilege('\"'||n.nspname||'\".\"'||c.relname||'\"','select')" // user has select privilege
)
.arg( tableName ).arg( schemaName ).arg( columnName ).arg( typeName ).arg( sridName ).arg( gtableName );
if ( searchPublicOnly )
sql += " AND n.nspname='public'";
sql += QString( " ORDER BY n.nspname,c.relname,%1" ).arg( columnName );
QgsDebugMsg( "getting table info: " + sql );
result = PQexec( sql, i == 0 );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
PQexecNR( "COMMIT" );
continue;
}
for ( int idx = 0; idx < result.PQntuples(); idx++ )
{
QString tableName = result.PQgetvalue( idx, 0 );
QString schemaName = result.PQgetvalue( idx, 1 );
QString column = result.PQgetvalue( idx, 2 );
QString type = result.PQgetvalue( idx, 3 );
QString srid = result.PQgetvalue( idx, 4 );
QString relkind = result.PQgetvalue( idx, 5 );
QgsDebugMsg( QString( "%1 : %2.%3.%4: %5 %6 %7" )
.arg( gtableName )
.arg( schemaName ).arg( tableName ).arg( column )
.arg( type )
.arg( srid )
.arg( relkind ) );
layerProperty.pkCols.clear();
layerProperty.type = type;
layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
layerProperty.geometryColName = column;
layerProperty.geometryColType = columnType;
if ( relkind == "v" )
{
layerProperty.pkCols = pkCandidates( schemaName, tableName );
if ( layerProperty.pkCols.isEmpty() )
{
QgsDebugMsg( "no key columns found." );
continue;
}
}
layerProperty.srid = srid;
layerProperty.sql = "";
mLayersSupported << layerProperty;
nColumns++;
}
foundInTables |= 1 << i;
}
if ( nColumns == 0 )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined." ), tr( "PostGIS" ) );
}
//search for geometry columns in tables that are not in the geometry_columns metatable
if ( !searchGeometryColumnsOnly )
{
// Now have a look for geometry columns that aren't in the geometry_columns table.
QString sql = "SELECT"
" c.relname"
",n.nspname"
",a.attname"
",c.relkind"
",CASE WHEN t.typname IN ('geometry','geography','topogeometry') THEN t.typname ELSE b.typname END AS coltype"
" FROM pg_attribute a"
" JOIN pg_class c ON c.oid=a.attrelid"
" JOIN pg_namespace n ON n.oid=c.relnamespace"
" JOIN pg_type t ON t.oid=a.atttypid"
" LEFT JOIN pg_type b ON b.oid=t.typbasetype"
" WHERE c.relkind IN ('v','r')"
" AND has_schema_privilege( n.nspname, 'usage' )"
" AND has_table_privilege( '\"' || n.nspname || '\".\"' || c.relname || '\"', 'select' )"
" AND (t.typname IN ('geometry','geography','topogeometry') OR b.typname IN ('geometry','geography','topogeometry'))";
// user has select privilege
if ( searchPublicOnly )
sql += " AND n.nspname='public'";
// skip columns of which we already derived information from the metadata tables
if ( nColumns > 0 )
{
if ( foundInTables & 1 )
{
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geometry_column FROM geometry_columns)";
}
if ( foundInTables & 2 )
{
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT f_table_schema,f_table_name,f_geography_column FROM geography_columns)";
}
if ( foundInTables & 4 )
{
sql += " AND (n.nspname,c.relname,a.attname) NOT IN (SELECT schema_name,table_name,feature_column FROM topology.layer)";
}
}
QgsDebugMsg( "sql: " + sql );
result = PQexec( sql );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined. The error message from the database was:\n%1\n" )
.arg( result.PQresultErrorMessage() ),
tr( "PostGIS" ) );
PQexecNR( "COMMIT" );
return false;
}
for ( int i = 0; i < result.PQntuples(); i++ )
{
// Have the column name, schema name and the table name. The concept of a
// catalog doesn't exist in postgresql so we ignore that, but we
// do need to get the geometry type.
// Make the assumption that the geometry type for the first
// row is the same as for all other rows.
QString tableName = result.PQgetvalue( i, 0 ); // relname
QString schemaName = result.PQgetvalue( i, 1 ); // nspname
QString column = result.PQgetvalue( i, 2 ); // attname
QString relkind = result.PQgetvalue( i, 3 ); // relation kind
QString coltype = result.PQgetvalue( i, 4 ); // column type
QgsDebugMsg( QString( "%1.%2.%3: %4" ).arg( schemaName ).arg( tableName ).arg( column ).arg( relkind ) );
layerProperty.type = QString::null;
layerProperty.schemaName = schemaName;
layerProperty.tableName = tableName;
layerProperty.geometryColName = column;
if ( coltype == "geometry" )
{
layerProperty.geometryColType = sctGeometry;
}
else if ( coltype == "geography" )
{
layerProperty.geometryColType = sctGeography;
}
else if ( coltype == "topogeometry" )
{
layerProperty.geometryColType = sctTopoGeometry;
}
else
{
Q_ASSERT( !"Unknown geometry type" );
}
if ( relkind == "v" )
{
layerProperty.pkCols = pkCandidates( schemaName, tableName );
if ( layerProperty.pkCols.isEmpty() )
{
QgsDebugMsg( "no key columns found." );
continue;
}
}
layerProperty.sql = "";
mLayersSupported << layerProperty;
nColumns++;
}
}
if ( allowGeometrylessTables )
{
QString sql = "SELECT "
"pg_class.relname"
",pg_namespace.nspname"
",pg_class.relkind"
" FROM "
" pg_class"
",pg_namespace"
" WHERE pg_namespace.oid=pg_class.relnamespace"
" AND has_schema_privilege(pg_namespace.nspname,'usage')"
" AND has_table_privilege('\"' || pg_namespace.nspname || '\".\"' || pg_class.relname || '\"','select')"
" AND pg_class.relkind IN ('v','r')";
// user has select privilege
if ( searchPublicOnly )
sql += " AND pg_namespace.nspname='public'";
QgsDebugMsg( "sql: " + sql );
result = PQexec( sql );
if ( result.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but the accessible tables could not be determined.\nThe error message from the database was:\n%1" )
.arg( result.PQresultErrorMessage() ),
tr( "PostGIS" ) );
return false;
}
for ( int i = 0; i < result.PQntuples(); i++ )
{
QString table = result.PQgetvalue( i, 0 ); // relname
QString schema = result.PQgetvalue( i, 1 ); // nspname
QString relkind = result.PQgetvalue( i, 2 ); // relation kind
QgsDebugMsg( QString( "%1.%2: %3" ).arg( schema ).arg( table ).arg( relkind ) );
layerProperty.type = QString::null;
layerProperty.schemaName = schema;
layerProperty.tableName = table;
layerProperty.geometryColName = QString::null;
layerProperty.geometryColType = sctNone;
layerProperty.pkCols = relkind == "v" ? pkCandidates( schema, table ) : QStringList();
layerProperty.srid = "";
layerProperty.sql = "";
mLayersSupported << layerProperty;
nColumns++;
}
}
if ( nColumns == 0 )
{
QgsMessageLog::logMessage( tr( "Database connection was successful, but no accessible tables were found. Please verify that you have SELECT privilege on a table carrying PostGIS geometry." ), tr( "PostGIS" ) );
}
return true;
}
void QgsPostgresConn::retrieveLayerTypes( QgsPostgresLayerProperty &layerProperty, bool useEstimatedMetadata )
{
QString table;
if ( !layerProperty.schemaName.isEmpty() )
{
table = QString( "%1.%2" )
.arg( quotedIdentifier( layerProperty.schemaName ) )
.arg( quotedIdentifier( layerProperty.tableName ) );
}
else
{
// Query
table = layerProperty.tableName;
}
// our estimatation ignores that a where clause might restrict the feature type or srid
if ( useEstimatedMetadata )
{
table = QString( "(SELECT %1 FROM %2 WHERE %1 IS NOT NULL%3 LIMIT %4) AS t" )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( table )
.arg( layerProperty.sql.isEmpty() ? "" : QString( " AND (%1)" ).arg( layerProperty.sql ) )
.arg( sGeomTypeSelectLimit );
}
else if ( !layerProperty.sql.isEmpty() )
{
table += QString( " WHERE %1" ).arg( layerProperty.sql );
}
QString query = QString( "SELECT DISTINCT"
" CASE"
" WHEN %1 THEN 'POINT'"
" WHEN %2 THEN 'LINESTRING'"
" WHEN %3 THEN 'POLYGON'"
" END,"
" %4(%5%6)"
" FROM %7" )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPoint, layerProperty.geometryColType == sctGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBLineString, layerProperty.geometryColType == sctGeography ) )
.arg( postgisTypeFilter( layerProperty.geometryColName, QGis::WKBPolygon, layerProperty.geometryColType == sctGeography ) )
.arg( majorVersion() < 2 ? "srid" : "st_srid" )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( layerProperty.geometryColType == sctGeography ? "::geometry" : "" )
.arg( table );
QgsDebugMsg( "Retrieving geometry types: " + query );
QgsPostgresResult gresult = PQexec( query );
QString type;
QString srid;
if ( gresult.PQresultStatus() == PGRES_TUPLES_OK )
{
QStringList types;
QStringList srids;
for ( int i = 0; i < gresult.PQntuples(); i++ )
{
QString type = gresult.PQgetvalue( i, 0 );
QString srid = gresult.PQgetvalue( i, 1 );
if ( type.isEmpty() )
continue;
types << type;
srids << srid;
}
type = types.join( "," );
srid = srids.join( "," );
}
QgsDebugMsg( QString( "type:%1 srid:%2" ).arg( type ).arg( srid ) );
layerProperty.type = type;
layerProperty.srid = srid;
}
bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int row, QgsFeature &feature )
{
feature.initAttributes( mSource->mFields.count() );
int col = 0;
if ( mFetchGeometry )
{
int returnedLength = ::PQgetlength( queryResult.result(), row, col );
if ( returnedLength > 0 )
{
unsigned char *featureGeom = new unsigned char[returnedLength + 1];
memcpy( featureGeom, PQgetvalue( queryResult.result(), row, col ), returnedLength );
memset( featureGeom + returnedLength, 0, 1 );
unsigned int wkbType;
memcpy( &wkbType, featureGeom + 1, sizeof( wkbType ) );
QgsWKBTypes::Type newType = QgsPostgresConn::wkbTypeFromOgcWkbType( wkbType );
if (( unsigned int )newType != wkbType )
{
// overwrite type
unsigned int n = newType;
memcpy( featureGeom + 1, &n, sizeof( n ) );
}
// PostGIS stores TIN as a collection of Triangles.
// Since Triangles are not supported, they have to be converted to Polygons
const int nDims = 2 + ( QgsWKBTypes::hasZ( newType ) ? 1 : 0 ) + ( QgsWKBTypes::hasM( newType ) ? 1 : 0 );
if ( wkbType % 1000 == 16 )
{
unsigned int numGeoms;
memcpy( &numGeoms, featureGeom + 5, sizeof( unsigned int ) );
unsigned char *wkb = featureGeom + 9;
for ( unsigned int i = 0; i < numGeoms; ++i )
{
const unsigned int localType = QgsWKBTypes::singleType( newType ); // polygon(Z|M)
memcpy( wkb + 1, &localType, sizeof( localType ) );
// skip endian and type info
wkb += sizeof( unsigned int ) + 1;
// skip coordinates
unsigned int nRings;
memcpy( &nRings, wkb, sizeof( int ) );
wkb += sizeof( int );
for ( unsigned int j = 0; j < nRings; ++j )
{
unsigned int nPoints;
memcpy( &nPoints, wkb, sizeof( int ) );
wkb += sizeof( nPoints ) + sizeof( double ) * nDims * nPoints;
}
}
}
feature.setGeometryAndOwnership( featureGeom, returnedLength + 1 );
}
else
{
feature.setGeometryAndOwnership( 0, 0 );
}
col++;
}
QgsFeatureId fid = 0;
bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
switch ( mSource->mPrimaryKeyType )
{
case pktOid:
case pktTid:
case pktInt:
fid = mConn->getBinaryInt( queryResult, row, col++ );
if ( mSource->mPrimaryKeyType == pktInt &&
( !subsetOfAttributes || fetchAttributes.contains( mSource->mPrimaryKeyAttrs[0] ) ) )
feature.setAttribute( mSource->mPrimaryKeyAttrs[0], fid );
break;
case pktFidMap:
{
QList<QVariant> primaryKeyVals;
Q_FOREACH ( int idx, mSource->mPrimaryKeyAttrs )
{
const QgsField &fld = mSource->mFields.at( idx );
QVariant v = QgsPostgresProvider::convertValue( fld.type(), queryResult.PQgetvalue( row, col ) );
primaryKeyVals << v;
if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
feature.setAttribute( idx, v );
col++;
}
fid = mSource->mShared->lookupFid( QVariant( primaryKeyVals ) );
}
break;
case pktUnknown:
Q_ASSERT( !"FAILURE: cannot get feature with unknown primary key" );
return false;
}
feature.setFeatureId( fid );
QgsDebugMsgLevel( QString( "fid=%1" ).arg( fid ), 4 );
// iterate attributes
if ( subsetOfAttributes )
{
Q_FOREACH ( int idx, fetchAttributes )
getFeatureAttribute( idx, queryResult, row, col, feature );
}
else
{
for ( int idx = 0; idx < mSource->mFields.count(); ++idx )
bool QgsPostgresFeatureIterator::fetchFeature( QgsFeature& feature )
{
feature.setValid( false );
if ( mClosed )
return false;
if ( mFeatureQueue.empty() )
{
QString fetch = QString( "FETCH FORWARD %1 FROM %2" ).arg( mFeatureQueueSize ).arg( mCursorName );
QgsDebugMsgLevel( QString( "fetching %1 features." ).arg( mFeatureQueueSize ), 4 );
if ( P->mConnectionRO->PQsendQuery( fetch ) == 0 ) // fetch features asynchronously
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
}
QgsPostgresResult queryResult;
for ( ;; )
{
queryResult = P->mConnectionRO->PQgetResult();
if ( !queryResult.result() )
break;
if ( queryResult.PQresultStatus() != PGRES_TUPLES_OK )
{
QgsMessageLog::logMessage( QObject::tr( "Fetching from cursor %1 failed\nDatabase error: %2" ).arg( mCursorName ).arg( P->mConnectionRO->PQerrorMessage() ), QObject::tr( "PostGIS" ) );
break;
}
int rows = queryResult.PQntuples();
if ( rows == 0 )
continue;
for ( int row = 0; row < rows; row++ )
{
mFeatureQueue.enqueue( QgsFeature() );
getFeature( queryResult, row, mFeatureQueue.back() );
} // for each row in queue
}
}
if ( mFeatureQueue.empty() )
{
QgsDebugMsg( QString( "Finished after %1 features" ).arg( mFetched ) );
close();
/* only updates the feature count if it was already once.
* Otherwise, this would lead to false feature count if
* an existing project is open at a restrictive extent.
*/
if ( P->mFeaturesCounted > 0 && P->mFeaturesCounted < mFetched )
{
QgsDebugMsg( QString( "feature count adjusted from %1 to %2" ).arg( P->mFeaturesCounted ).arg( mFetched ) );
P->mFeaturesCounted = mFetched;
}
return false;
}
// Now return the next feature from the queue
if ( mRequest.flags() & QgsFeatureRequest::NoGeometry )
{
feature.setGeometryAndOwnership( 0, 0 );
}
else
{
QgsGeometry* featureGeom = mFeatureQueue.front().geometryAndOwnership();
feature.setGeometry( featureGeom );
}
feature.setFeatureId( mFeatureQueue.front().id() );
feature.setAttributes( mFeatureQueue.front().attributes() );
mFeatureQueue.dequeue();
mFetched++;
feature.setValid( true );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
return true;
}
bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int row, QgsFeature &feature )
{
try
{
feature.initAttributes( P->fields().count() );
int col = 0;
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) )
{
int returnedLength = ::PQgetlength( queryResult.result(), row, col );
if ( returnedLength > 0 )
{
unsigned char *featureGeom = new unsigned char[returnedLength + 1];
memset( featureGeom, 0, returnedLength + 1 );
memcpy( featureGeom, PQgetvalue( queryResult.result(), row, col ), returnedLength );
// modify 2.5D WKB types to make them compliant with OGR
unsigned int wkbType;
memcpy( &wkbType, featureGeom + 1, sizeof( wkbType ) );
// convert unsupported types to supported ones
switch ( wkbType )
{
case 15:
// 2D polyhedral => multipolygon
wkbType = 6;
break;
case 1015:
// 3D polyhedral => multipolygon
wkbType = 1006;
break;
case 17:
// 2D triangle => polygon
wkbType = 3;
break;
case 1017:
// 3D triangle => polygon
wkbType = 1003;
break;
case 16:
// 2D TIN => multipolygon
wkbType = 6;
break;
case 1016:
// TIN => multipolygon
wkbType = 1006;
break;
}
// convert from postgis types to qgis types
if ( wkbType >= 1000 )
{
wkbType = wkbType - 1000 + QGis::WKBPoint25D - 1;
}
memcpy( featureGeom + 1, &wkbType, sizeof( wkbType ) );
// change wkb type of inner geometries
if ( wkbType == QGis::WKBMultiPoint25D ||
wkbType == QGis::WKBMultiLineString25D ||
wkbType == QGis::WKBMultiPolygon25D )
{
unsigned int numGeoms = *(( int* )( featureGeom + 5 ) );
unsigned char* wkb = featureGeom + 9;
for ( unsigned int i = 0; i < numGeoms; ++i )
{
unsigned int localType;
memcpy( &localType, wkb + 1, sizeof( localType ) );
switch ( localType )
{
case 15:
// 2D polyhedral => multipolygon
localType = 6;
break;
case 1015:
// 3D polyhedral => multipolygon
localType = 1006;
break;
case 17:
// 2D triangle => polygon
localType = 3;
break;
case 1017:
// 3D triangle => polygon
localType = 1003;
break;
case 16:
// 2D TIN => multipolygon
localType = 6;
break;
case 1016:
// TIN => multipolygon
localType = 1006;
break;
}
if ( localType >= 1000 )
{
localType = localType - 1000 + QGis::WKBPoint25D - 1;
}
memcpy( wkb + 1, &localType, sizeof( localType ) );
// skip endian and type info
wkb += sizeof( unsigned int ) + 1;
// skip coordinates
switch ( wkbType )
{
case QGis::WKBMultiPoint25D:
wkb += sizeof( double ) * 3;
break;
case QGis::WKBMultiLineString25D:
{
unsigned int nPoints = *(( int* ) wkb );
wkb += sizeof( nPoints );
wkb += sizeof( double ) * 3 * nPoints;
}
break;
default:
case QGis::WKBMultiPolygon25D:
{
unsigned int nRings = *(( int* ) wkb );
wkb += sizeof( nRings );
for ( unsigned int j = 0; j < nRings; ++j )
{
unsigned int nPoints = *(( int* ) wkb );
wkb += sizeof( nPoints );
wkb += sizeof( double ) * 3 * nPoints;
}
}
break;
}
}
}
feature.setGeometryAndOwnership( featureGeom, returnedLength + 1 );
}
else
{
feature.setGeometryAndOwnership( 0, 0 );
}
col++;
}
QgsFeatureId fid = 0;
bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
switch ( P->mPrimaryKeyType )
{
case QgsPostgresProvider::pktOid:
case QgsPostgresProvider::pktTid:
case QgsPostgresProvider::pktInt:
fid = P->mConnectionRO->getBinaryInt( queryResult, row, col++ );
if ( P->mPrimaryKeyType == QgsPostgresProvider::pktInt &&
( !subsetOfAttributes || fetchAttributes.contains( P->mPrimaryKeyAttrs[0] ) ) )
feature.setAttribute( P->mPrimaryKeyAttrs[0], fid );
break;
case QgsPostgresProvider::pktFidMap:
{
QList<QVariant> primaryKeyVals;
foreach ( int idx, P->mPrimaryKeyAttrs )
{
const QgsField &fld = P->field( idx );
QVariant v = P->convertValue( fld.type(), queryResult.PQgetvalue( row, col ) );
primaryKeyVals << v;
if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
feature.setAttribute( idx, v );
col++;
}
fid = P->lookupFid( QVariant( primaryKeyVals ) );
}
break;
case QgsPostgresProvider::pktUnknown:
Q_ASSERT( !"FAILURE: cannot get feature with unknown primary key" );
return false;
}
feature.setFeatureId( fid );
QgsDebugMsgLevel( QString( "fid=%1" ).arg( fid ), 4 );
// iterate attributes
if ( subsetOfAttributes )
{
foreach ( int idx, fetchAttributes )
getFeatureAttribute( idx, queryResult, row, col, feature );
}
else
{
for ( int idx = 0; idx < P->mAttributeFields.count(); ++idx )
getFeatureAttribute( idx, queryResult, row, col, feature );
}
return true;
}
bool QgsPostgresFeatureIterator::getFeature( QgsPostgresResult &queryResult, int row, QgsFeature &feature )
{
feature.initAttributes( mSource->mFields.count() );
int col = 0;
if ( mFetchGeometry )
{
int returnedLength = ::PQgetlength( queryResult.result(), row, col );
if ( returnedLength > 0 )
{
unsigned char *featureGeom = new unsigned char[returnedLength + 1];
memcpy( featureGeom, PQgetvalue( queryResult.result(), row, col ), returnedLength );
memset( featureGeom + returnedLength, 0, 1 );
// modify 2.5D WKB types to make them compliant with OGR
unsigned int wkbType;
memcpy( &wkbType, featureGeom + 1, sizeof( wkbType ) );
wkbType = QgsPostgresConn::wkbTypeFromOgcWkbType( wkbType );
memcpy( featureGeom + 1, &wkbType, sizeof( wkbType ) );
// change wkb type of inner geometries
if ( wkbType == QGis::WKBMultiPoint25D ||
wkbType == QGis::WKBMultiLineString25D ||
wkbType == QGis::WKBMultiPolygon25D )
{
unsigned int numGeoms;
memcpy( &numGeoms, featureGeom + 5, sizeof( unsigned int ) );
unsigned char *wkb = featureGeom + 9;
for ( unsigned int i = 0; i < numGeoms; ++i )
{
unsigned int localType;
memcpy( &localType, wkb + 1, sizeof( localType ) );
localType = QgsPostgresConn::wkbTypeFromOgcWkbType( localType );
memcpy( wkb + 1, &localType, sizeof( localType ) );
// skip endian and type info
wkb += sizeof( unsigned int ) + 1;
// skip coordinates
switch ( wkbType )
{
case QGis::WKBMultiPoint25D:
wkb += sizeof( double ) * 3;
break;
case QGis::WKBMultiLineString25D:
{
unsigned int nPoints;
memcpy( &nPoints, wkb, sizeof( int ) );
wkb += sizeof( int ) + sizeof( double ) * 3 * nPoints;
}
break;
default:
case QGis::WKBMultiPolygon25D:
{
unsigned int nRings;
memcpy( &nRings, wkb, sizeof( int ) );
wkb += sizeof( int );
for ( unsigned int j = 0; j < nRings; ++j )
{
unsigned int nPoints;
memcpy( &nPoints, wkb, sizeof( int ) );
wkb += sizeof( nPoints ) + sizeof( double ) * 3 * nPoints;
}
}
break;
}
}
}
feature.setGeometryAndOwnership( featureGeom, returnedLength + 1 );
}
else
{
feature.setGeometryAndOwnership( 0, 0 );
}
col++;
}
QgsFeatureId fid = 0;
bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
switch ( mSource->mPrimaryKeyType )
{
case pktOid:
case pktTid:
case pktInt:
fid = mConn->getBinaryInt( queryResult, row, col++ );
if ( mSource->mPrimaryKeyType == pktInt &&
( !subsetOfAttributes || fetchAttributes.contains( mSource->mPrimaryKeyAttrs[0] ) ) )
feature.setAttribute( mSource->mPrimaryKeyAttrs[0], fid );
break;
case pktFidMap:
{
QList<QVariant> primaryKeyVals;
Q_FOREACH ( int idx, mSource->mPrimaryKeyAttrs )
{
const QgsField &fld = mSource->mFields[idx];
QVariant v = QgsPostgresProvider::convertValue( fld.type(), queryResult.PQgetvalue( row, col ) );
primaryKeyVals << v;
if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
feature.setAttribute( idx, v );
col++;
}
fid = mSource->mShared->lookupFid( QVariant( primaryKeyVals ) );
}
break;
case pktUnknown:
Q_ASSERT( !"FAILURE: cannot get feature with unknown primary key" );
return false;
}
feature.setFeatureId( fid );
QgsDebugMsgLevel( QString( "fid=%1" ).arg( fid ), 4 );
// iterate attributes
if ( subsetOfAttributes )
{
Q_FOREACH ( int idx, fetchAttributes )
getFeatureAttribute( idx, queryResult, row, col, feature );
}
else
{
for ( int idx = 0; idx < mSource->mFields.count(); ++idx )