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 QgsOSMDatabase::createSpatialTable( const QString& tableName, const QString& geometryType, const QStringList& tagKeys )
{
QString sqlCreateTable = QString( "CREATE TABLE %1 (id INTEGER PRIMARY KEY" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlCreateTable += QString( ", %1 TEXT" ).arg( quotedIdentifier( tagKeys[i] ) );
sqlCreateTable += ')';
char *errMsg = nullptr;
int ret = sqlite3_exec( mDatabase, sqlCreateTable.toUtf8().constData(), nullptr, nullptr, &errMsg );
if ( ret != SQLITE_OK )
{
mError = "Unable to create table:\n" + QString::fromUtf8( errMsg );
sqlite3_free( errMsg );
return false;
}
QString sqlAddGeomColumn = QString( "SELECT AddGeometryColumn(%1, 'geometry', 4326, %2, 'XY')" )
.arg( quotedValue( tableName ),
quotedValue( geometryType ) );
ret = sqlite3_exec( mDatabase, sqlAddGeomColumn.toUtf8().constData(), nullptr, nullptr, &errMsg );
if ( ret != SQLITE_OK )
{
mError = "Unable to add geometry column:\n" + QString::fromUtf8( errMsg );
sqlite3_free( errMsg );
return false;
}
return true;
}
QString QgsOracleConn::databaseTypeFilter( QString alias, QString geomCol, QGis::WkbType geomType )
{
geomCol = quotedIdentifier( alias ) + "." + quotedIdentifier( geomCol );
switch ( geomType )
{
case QGis::WKBPoint:
case QGis::WKBPoint25D:
case QGis::WKBMultiPoint:
case QGis::WKBMultiPoint25D:
return QString( "mod(%1.sdo_gtype,100) IN (1,5)" ).arg( geomCol );
case QGis::WKBLineString:
case QGis::WKBLineString25D:
case QGis::WKBMultiLineString:
case QGis::WKBMultiLineString25D:
return QString( "mod(%1.sdo_gtype,100) IN (2,6)" ).arg( geomCol );
case QGis::WKBPolygon:
case QGis::WKBPolygon25D:
case QGis::WKBMultiPolygon:
case QGis::WKBMultiPolygon25D:
return QString( "mod(%1.sdo_gtype,100) IN (3,7)" ).arg( geomCol );
case QGis::WKBNoGeometry:
return QString( "%1 IS NULL" ).arg( geomCol );
case QGis::WKBUnknown:
Q_ASSERT( !"unknown geometry unexpected" );
return QString::null;
}
Q_ASSERT( !"unexpected geomType" );
return QString::null;
}
QString QgsPostgresConn::postgisTypeFilter( QString geomCol, QGis::WkbType geomType, bool isGeography )
{
geomCol = quotedIdentifier( geomCol );
if ( isGeography )
geomCol += "::geometry";
switch ( geomType )
{
case QGis::WKBPoint:
case QGis::WKBPoint25D:
case QGis::WKBMultiPoint:
case QGis::WKBMultiPoint25D:
return QString( "upper(geometrytype(%1)) IN ('POINT','MULTIPOINT','POINTM','MULTIPOINTM')" ).arg( geomCol );
case QGis::WKBLineString:
case QGis::WKBLineString25D:
case QGis::WKBMultiLineString:
case QGis::WKBMultiLineString25D:
return QString( "upper(geometrytype(%1)) IN ('LINESTRING','MULTILINESTRING','LINESTRINGM','MULTILINESTRINGM')" ).arg( geomCol );
case QGis::WKBPolygon:
case QGis::WKBPolygon25D:
case QGis::WKBMultiPolygon:
case QGis::WKBMultiPolygon25D:
return QString( "upper(geometrytype(%1)) IN ('POLYGON','MULTIPOLYGON','POLYGONM','MULTIPOLYGONM')" ).arg( geomCol );
case QGis::WKBNoGeometry:
return QString( "geometrytype(%1) IS NULL" ).arg( geomCol );
case QGis::WKBUnknown:
Q_ASSERT( !"unknown geometry unexpected" );
return QString::null;
}
Q_ASSERT( !"unexpected geomType" );
return QString::null;
}
void QgsOSMDatabase::exportSpatiaLiteNodes( const QString& tableName, const QStringList& tagKeys, const QStringList& notNullTagKeys )
{
QString sqlInsertPoint = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlInsertPoint += QString( ",?" );
sqlInsertPoint += ", GeomFromWKB(?, 4326))";
sqlite3_stmt* stmtInsert;
if ( sqlite3_prepare_v2( mDatabase, sqlInsertPoint.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK )
{
mError = "Prepare SELECT FROM nodes failed.";
return;
}
QgsOSMNodeIterator nodes = listNodes();
QgsOSMNode n;
while (( n = nodes.next() ).isValid() )
{
QgsOSMTags t = tags( false, n.id() );
// skip untagged nodes: probably they form a part of ways
if ( t.count() == 0 )
continue;
//check not null tags
bool skipNull = false;
for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i )
if ( !t.contains( notNullTagKeys[i] ) )
skipNull = true;
if ( skipNull )
continue;
QgsGeometry geom = QgsGeometry::fromPoint( n.point() );
int col = 0;
sqlite3_bind_int64( stmtInsert, ++col, n.id() );
// tags
for ( int i = 0; i < tagKeys.count(); ++i )
{
if ( t.contains( tagKeys[i] ) )
sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
else
sqlite3_bind_null( stmtInsert, ++col );
}
sqlite3_bind_blob( stmtInsert, ++col, geom.asWkb(), ( int ) geom.wkbSize(), SQLITE_STATIC );
int insertRes = sqlite3_step( stmtInsert );
if ( insertRes != SQLITE_DONE )
{
mError = QString( "Error inserting node %1 [%2]" ).arg( n.id() ).arg( insertRes );
break;
}
sqlite3_reset( stmtInsert );
sqlite3_clear_bindings( stmtInsert );
}
sqlite3_finalize( stmtInsert );
}
QString QgsOracleConn::fieldExpression( const QgsField &fld )
{
#if 0
const QString &type = fld.typeName();
if ( type == "money" )
{
return QString( "cash_out(%1)" ).arg( quotedIdentifier( fld.name() ) );
}
else if ( type.startsWith( "_" ) )
{
return QString( "array_out(%1)" ).arg( quotedIdentifier( fld.name() ) );
}
else if ( type == "bool" )
{
return QString( "boolout(%1)" ).arg( quotedIdentifier( fld.name() ) );
}
else if ( type == "geometry" )
{
return QString( "%1(%2)" )
.arg( majorVersion() < 2 ? "asewkt" : "st_asewkt" )
.arg( quotedIdentifier( fld.name() ) );
}
else if ( type == "geography" )
{
return QString( "st_astext(%1)" ).arg( quotedIdentifier( fld.name() ) );
}
else
{
return quotedIdentifier( fld.name() ) + "::text";
}
#else
return quotedIdentifier( fld.name() );
#endif
}
QString QgsSQLStatement::quotedIdentifierIfNeeded( const QString &name )
{
// This might not be complete, but it must be at least what we recognize
static const char *const RESERVED_KEYWORDS[] =
{
"AND", "OR", "NOT", "LIKE", "IN", "IS", "BETWEEN", "NULL", "SELECT", "ALL", "DISTINCT", "CAST", "AS",
"FROM", "JOIN", "ON", "USING", "WHERE", "ORDER", "BY", "ASC", "DESC",
"LEFT", "RIGHT", "INNER", "OUTER", "CROSS", "FULL", "NATURAL", "UNION",
"OFFSET", "LIMIT", "GROUP", "HAVING"
};
for ( size_t i = 0; i < sizeof( RESERVED_KEYWORDS ) / sizeof( RESERVED_KEYWORDS[0] ); ++i )
{
if ( name.compare( QString( RESERVED_KEYWORDS[i] ), Qt::CaseInsensitive ) == 0 )
{
return quotedIdentifier( name );
}
}
return IDENTIFIER_RE.exactMatch( name ) ? name : quotedIdentifier( name );
}
QgsSqlExpressionCompiler::Result QgsPostgresExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
{
switch ( node->nodeType() )
{
case QgsExpressionNode::ntFunction:
{
const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
QgsExpressionFunction *fd = QgsExpression::Functions()[n->fnIndex()];
if ( fd->name() == "$geometry" )
{
result = quotedIdentifier( mGeometryColumn );
return Complete;
}
#if 0
/*
* These methods are tricky
* QGIS expression versions of these return ellipsoidal measurements
* based on the project settings, and also convert the result to the
* units specified in project properties.
*/
else if ( fd->name() == "$area" )
{
result = QStringLiteral( "ST_Area(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
return Complete;
}
else if ( fd->name() == "$length" )
{
result = QStringLiteral( "ST_Length(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
return Complete;
}
else if ( fd->name() == "$perimeter" )
{
result = QStringLiteral( "ST_Perimeter(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
return Complete;
}
else if ( fd->name() == "$x" )
{
result = QStringLiteral( "ST_X(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
return Complete;
}
else if ( fd->name() == "$y" )
{
result = QStringLiteral( "ST_Y(%1)" ).arg( quotedIdentifier( mGeometryColumn ) );
return Complete;
}
#endif
}
default:
return QgsSqlExpressionCompiler::compileNode( node, result );
}
return Fail;
}
void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName,
const QStringList& tagKeys,
const QStringList& notNullTagKeys )
{
Q_UNUSED( tagKeys );
QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlInsertLine += QString( ",?" );
sqlInsertLine += ", GeomFromWKB(?, 4326))";
sqlite3_stmt* stmtInsert;
if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, nullptr ) != SQLITE_OK )
{
mError = "Prepare SELECT FROM ways failed.";
return;
}
QgsOSMWayIterator ways = listWays();
QgsOSMWay w;
while (( w = ways.next() ).isValid() )
{
QgsOSMTags t = tags( true, w.id() );
QgsPolyline polyline = wayPoints( w.id() );
if ( polyline.count() < 2 )
continue; // invalid way
bool isArea = ( polyline.first() == polyline.last() ); // closed way?
// filter out closed way that are not areas through tags
if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) )
{
// make sure tags that indicate areas are taken into consideration when deciding on a closed way is or isn't an area
// and allow for a closed way to be exported both as a polygon and a line in case both area and non-area tags are present
if (( t.value( "area" ) != "yes" && !t.contains( "amenity" ) && !t.contains( "landuse" ) && !t.contains( "building" ) && !t.contains( "natural" ) && !t.contains( "leisure" ) && !t.contains( "aeroway" ) ) || !closed )
isArea = false;
}
if ( closed != isArea )
continue; // skip if it's not what we're looking for
//check not null tags
bool skipNull = false;
for ( int i = 0; i < notNullTagKeys.count() && !skipNull; ++i )
if ( !t.contains( notNullTagKeys[i] ) )
skipNull = true;
if ( skipNull )
continue;
QgsGeometry geom = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline );
int col = 0;
sqlite3_bind_int64( stmtInsert, ++col, w.id() );
// tags
for ( int i = 0; i < tagKeys.count(); ++i )
{
if ( t.contains( tagKeys[i] ) )
sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
else
sqlite3_bind_null( stmtInsert, ++col );
}
if ( !geom.isEmpty() )
sqlite3_bind_blob( stmtInsert, ++col, geom.asWkb(), ( int ) geom.wkbSize(), SQLITE_STATIC );
else
sqlite3_bind_null( stmtInsert, ++col );
int insertRes = sqlite3_step( stmtInsert );
if ( insertRes != SQLITE_DONE )
{
mError = QString( "Error inserting way %1 [%2]" ).arg( w.id() ).arg( insertRes );
break;
}
sqlite3_reset( stmtInsert );
sqlite3_clear_bindings( stmtInsert );
}
sqlite3_finalize( stmtInsert );
}
void QgsOSMDatabase::exportSpatiaLiteWays( bool closed, const QString& tableName, const QStringList& tagKeys )
{
Q_UNUSED( tagKeys );
QString sqlInsertLine = QString( "INSERT INTO %1 VALUES (?" ).arg( quotedIdentifier( tableName ) );
for ( int i = 0; i < tagKeys.count(); ++i )
sqlInsertLine += QString( ",?" );
sqlInsertLine += ", GeomFromWKB(?, 4326))";
sqlite3_stmt* stmtInsert;
if ( sqlite3_prepare_v2( mDatabase, sqlInsertLine.toUtf8().constData(), -1, &stmtInsert, 0 ) != SQLITE_OK )
{
mError = "Prepare SELECT FROM ways failed.";
return;
}
QgsOSMWayIterator ways = listWays();
QgsOSMWay w;
while (( w = ways.next() ).isValid() )
{
QgsOSMTags t = tags( true, w.id() );
QgsPolyline polyline = wayPoints( w.id() );
if ( polyline.count() < 2 )
continue; // invalid way
bool isArea = ( polyline.first() == polyline.last() ); // closed way?
// some closed ways are not really areas
if ( isArea && ( t.contains( "highway" ) || t.contains( "barrier" ) ) )
{
if ( t.value( "area" ) != "yes" ) // even though "highway" is line by default, "area"="yes" may override that
isArea = false;
}
if ( closed != isArea )
continue; // skip if it's not what we're looking for
QgsGeometry* geom = closed ? QgsGeometry::fromPolygon( QgsPolygon() << polyline ) : QgsGeometry::fromPolyline( polyline );
int col = 0;
sqlite3_bind_int64( stmtInsert, ++col, w.id() );
// tags
for ( int i = 0; i < tagKeys.count(); ++i )
{
if ( t.contains( tagKeys[i] ) )
sqlite3_bind_text( stmtInsert, ++col, t.value( tagKeys[i] ).toUtf8().constData(), -1, SQLITE_TRANSIENT );
else
sqlite3_bind_null( stmtInsert, ++col );
}
sqlite3_bind_blob( stmtInsert, ++col, geom->asWkb(), geom->wkbSize(), SQLITE_STATIC );
int insertRes = sqlite3_step( stmtInsert );
if ( insertRes != SQLITE_DONE )
{
mError = QString( "Error inserting way %1 [%2]" ).arg( w.id() ).arg( insertRes );
break;
}
sqlite3_reset( stmtInsert );
sqlite3_clear_bindings( stmtInsert );
delete geom;
}
sqlite3_finalize( stmtInsert );
}
QgsOracleFeatureIterator::QgsOracleFeatureIterator( QgsOracleFeatureSource* source, bool ownSource, const QgsFeatureRequest &request )
: QgsAbstractFeatureIteratorFromSource( source, ownSource, request )
, mRewind( false )
{
mConnection = QgsOracleConn::connectDb( mSource->mUri.connectionInfo() );
if ( !mConnection )
{
close();
return;
}
mQry = QSqlQuery( *mConnection );
if ( mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes )
{
mAttributeList = mRequest.subsetOfAttributes();
if ( mAttributeList.isEmpty() )
mAttributeList = mSource->mFields.allAttributesList();
}
else
mAttributeList = mSource->mFields.allAttributesList();
QString whereClause;
switch ( request.filterType() )
{
case QgsFeatureRequest::FilterExpression:
break;
case QgsFeatureRequest::FilterRect:
if ( !mSource->mGeometryColumn.isNull() )
{
QgsRectangle rect( mRequest.filterRect() );
QString bbox = QString( "mdsys.sdo_geometry(2003,%1,NULL,"
"mdsys.sdo_elem_info_array(1,1003,3),"
"mdsys.sdo_ordinate_array(%2,%3,%4,%5)"
")" )
.arg( mSource->mSrid < 1 ? "NULL" : QString::number( mSource->mSrid ) )
.arg( qgsDoubleToString( rect.xMinimum() ) )
.arg( qgsDoubleToString( rect.yMinimum() ) )
.arg( qgsDoubleToString( rect.xMaximum() ) )
.arg( qgsDoubleToString( rect.yMaximum() ) );
if ( !mSource->mSpatialIndex.isNull() )
{
whereClause = QString( "sdo_filter(%1,%2)='TRUE'" ).arg( QgsOracleProvider::quotedIdentifier( mSource->mGeometryColumn ) ).arg( bbox );
#if 0
if ( mRequest.flags() & QgsFeatureRequest::ExactIntersect )
{
whereClause += QString( " AND sdo_relate(%1,%2,'mask=ANYINTERACT')='TRUE'" )
.arg( quotedIdentifier( P->mGeometryColumn ) )
.arg( bbox );
}
#endif
}
}
break;
case QgsFeatureRequest::FilterFid:
whereClause = QgsOracleUtils::whereClause( request.filterFid(), mSource->mFields, mSource->mPrimaryKeyType, mSource->mPrimaryKeyAttrs, mSource->mShared );
break;
case QgsFeatureRequest::FilterFids:
whereClause = QgsOracleUtils::whereClause( request.filterFids(), mSource->mFields, mSource->mPrimaryKeyType, mSource->mPrimaryKeyAttrs, mSource->mShared );
break;
case QgsFeatureRequest::FilterNone:
break;
}
if ( mSource->mRequestedGeomType != QGis::WKBUnknown && mSource->mRequestedGeomType != mSource->mDetectedGeomType )
{
if ( !whereClause.isEmpty() )
whereClause += " AND ";
whereClause += QgsOracleConn::databaseTypeFilter( "featureRequest", mSource->mGeometryColumn, mSource->mRequestedGeomType );
}
if ( !mSource->mSqlWhereClause.isEmpty() )
{
if ( !whereClause.isEmpty() )
whereClause += " AND ";
whereClause += "(" + mSource->mSqlWhereClause + ")";
}
if ( !openQuery( whereClause ) )
return;
}
bool QgsNewSpatialiteLayerDialog::apply()
{
// Build up the sql statement for creating the table
QString sql = QStringLiteral( "create table %1(" ).arg( quotedIdentifier( leLayerName->text() ) );
QString delim;
if ( checkBoxPrimaryKey->isChecked() )
{
sql += QLatin1String( "pkuid integer primary key autoincrement" );
delim = QStringLiteral( "," );
}
QTreeWidgetItemIterator it( mAttributeView );
while ( *it )
{
sql += delim + QStringLiteral( "%1 %2" ).arg( quotedIdentifier( ( *it )->text( 0 ) ), ( *it )->text( 1 ) );
delim = QStringLiteral( "," );
++it;
}
// complete the create table statement
sql += ')';
QgsDebugMsg( QStringLiteral( "Creating table in database %1" ).arg( mDatabaseComboBox->currentText() ) );
QgsDebugMsg( sql );
spatialite_database_unique_ptr database;
int rc = database.open( mDatabaseComboBox->currentText() );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "SpatiaLite Database" ),
tr( "Unable to open the database: %1" ).arg( mDatabaseComboBox->currentText() ) );
return false;
}
char *errmsg = nullptr;
// create the table
rc = sqlite3_exec( database.get(), sql.toUtf8(), nullptr, nullptr, &errmsg );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "Error Creating SpatiaLite Table" ),
tr( "Failed to create the SpatiaLite table %1. The database returned:\n%2" ).arg( leLayerName->text(), errmsg ) );
sqlite3_free( errmsg );
return false;
}
// create the geometry column and the spatial index
if ( mGeometryTypeBox->currentIndex() != 0 )
{
QString sqlAddGeom = QStringLiteral( "select AddGeometryColumn(%1,%2,%3,%4,%5)" )
.arg( QgsSqliteUtils::quotedString( leLayerName->text() ),
QgsSqliteUtils::quotedString( leGeometryColumn->text() ) )
.arg( mCrsId.split( ':' ).value( 1, QStringLiteral( "0" ) ).toInt() )
.arg( QgsSqliteUtils::quotedString( selectedType() ) )
.arg( QgsSqliteUtils::quotedString( selectedZM() ) );
QgsDebugMsg( sqlAddGeom );
rc = sqlite3_exec( database.get(), sqlAddGeom.toUtf8(), nullptr, nullptr, &errmsg );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "Error Creating Geometry Column" ),
tr( "Failed to create the geometry column. The database returned:\n%1" ).arg( errmsg ) );
sqlite3_free( errmsg );
return false;
}
QString sqlCreateIndex = QStringLiteral( "select CreateSpatialIndex(%1,%2)" )
.arg( QgsSqliteUtils::quotedString( leLayerName->text() ),
QgsSqliteUtils::quotedString( leGeometryColumn->text() ) );
QgsDebugMsg( sqlCreateIndex );
rc = sqlite3_exec( database.get(), sqlCreateIndex.toUtf8(), nullptr, nullptr, &errmsg );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "Error Creating Spatial Index" ),
tr( "Failed to create the spatial index. The database returned:\n%1" ).arg( errmsg ) );
sqlite3_free( errmsg );
return false;
}
}
const QgsVectorLayer::LayerOptions options { QgsProject::instance()->transformContext() };
QgsVectorLayer *layer = new QgsVectorLayer( QStringLiteral( "dbname='%1' table='%2'%3 sql=" )
.arg( mDatabaseComboBox->currentText(),
leLayerName->text(),
mGeometryTypeBox->currentIndex() != 0 ? QStringLiteral( "(%1)" ).arg( leGeometryColumn->text() ) : QString() ),
leLayerName->text(), QStringLiteral( "spatialite" ), options );
if ( layer->isValid() )
{
// Reload connections to refresh browser panel
QgisApp::instance()->reloadConnections();
// register this layer with the central layers registry
QList<QgsMapLayer *> myList;
myList << layer;
//addMapLayers returns a list of all successfully added layers
//so we compare that to our original list.
if ( myList == QgsProject::instance()->addMapLayers( myList ) )
return true;
}
else
{
QgsDebugMsg( leLayerName->text() + " is an invalid layer - not loaded" );
QMessageBox::critical( this, tr( "SpatiaLite Database" ), tr( "%1 is an invalid layer and cannot be loaded." ).arg( leLayerName->text() ) );
delete layer;
}
return false;
}
QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::compileNode( const QgsExpression::Node* node, QString& result )
{
switch ( node->nodeType() )
{
case QgsExpression::ntUnaryOperator:
{
const QgsExpression::NodeUnaryOperator* n = static_cast<const QgsExpression::NodeUnaryOperator*>( node );
switch ( n->op() )
{
case QgsExpression::uoNot:
{
QString right;
if ( compileNode( n->operand(), right ) == Complete )
{
result = "( NOT " + right + ')';
return Complete;
}
return Fail;
}
case QgsExpression::uoMinus:
{
if ( mFlags.testFlag( NoUnaryMinus ) )
return Fail;
QString right;
if ( compileNode( n->operand(), right ) == Complete )
{
result = "( - (" + right + "))";
return Complete;
}
return Fail;
}
}
break;
}
case QgsExpression::ntBinaryOperator:
{
const QgsExpression::NodeBinaryOperator* n = static_cast<const QgsExpression::NodeBinaryOperator*>( node );
QString op;
bool partialCompilation = false;
bool failOnPartialNode = false;
switch ( n->op() )
{
case QgsExpression::boEQ:
if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->opLeft()->nodeType() == QgsExpression::ntColumnRef && n->opRight()->nodeType() == QgsExpression::ntColumnRef )
{
// equality between column refs results in a partial compilation, since provider is performing
// case-insensitive matches between strings
partialCompilation = true;
}
op = QStringLiteral( "=" );
break;
case QgsExpression::boGE:
op = QStringLiteral( ">=" );
break;
case QgsExpression::boGT:
op = QStringLiteral( ">" );
break;
case QgsExpression::boLE:
op = QStringLiteral( "<=" );
break;
case QgsExpression::boLT:
op = QStringLiteral( "<" );
break;
case QgsExpression::boIs:
op = QStringLiteral( "IS" );
break;
case QgsExpression::boIsNot:
op = QStringLiteral( "IS NOT" );
failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
break;
case QgsExpression::boLike:
op = QStringLiteral( "LIKE" );
partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
break;
case QgsExpression::boILike:
if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
op = QStringLiteral( "LIKE" );
else
op = QStringLiteral( "ILIKE" );
break;
case QgsExpression::boNotLike:
op = QStringLiteral( "NOT LIKE" );
partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
break;
case QgsExpression::boNotILike:
failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
op = QStringLiteral( "NOT LIKE" );
else
op = QStringLiteral( "NOT ILIKE" );
break;
case QgsExpression::boOr:
if ( mFlags.testFlag( NoNullInBooleanLogic ) )
{
if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
return Fail;
}
op = QStringLiteral( "OR" );
break;
case QgsExpression::boAnd:
if ( mFlags.testFlag( NoNullInBooleanLogic ) )
{
if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
return Fail;
}
op = QStringLiteral( "AND" );
break;
case QgsExpression::boNE:
failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
op = QStringLiteral( "<>" );
break;
case QgsExpression::boMul:
op = QStringLiteral( "*" );
break;
case QgsExpression::boPlus:
op = QStringLiteral( "+" );
break;
case QgsExpression::boMinus:
op = QStringLiteral( "-" );
break;
case QgsExpression::boDiv:
return Fail; // handle cast to real
case QgsExpression::boMod:
op = QStringLiteral( "%" );
break;
case QgsExpression::boConcat:
op = QStringLiteral( "||" );
break;
case QgsExpression::boIntDiv:
return Fail; // handle cast to int
case QgsExpression::boPow:
op = QStringLiteral( "^" );
break;
case QgsExpression::boRegexp:
op = QStringLiteral( "~" );
break;
}
if ( op.isNull() )
return Fail;
QString left;
Result lr( compileNode( n->opLeft(), left ) );
QString right;
Result rr( compileNode( n->opRight(), right ) );
if ( failOnPartialNode && ( lr == Partial || rr == Partial ) )
return Fail;
result = '(' + left + ' ' + op + ' ' + right + ')';
if ( lr == Complete && rr == Complete )
return ( partialCompilation ? Partial : Complete );
else if (( lr == Partial && rr == Complete ) || ( lr == Complete && rr == Partial ) || ( lr == Partial && rr == Partial ) )
return Partial;
else
return Fail;
}
case QgsExpression::ntLiteral:
{
const QgsExpression::NodeLiteral* n = static_cast<const QgsExpression::NodeLiteral*>( node );
bool ok = false;
if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->value().type() == QVariant::String )
{
// provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
// double check results using QGIS' expression engine
result = quotedValue( n->value(), ok );
return ok ? Partial : Fail;
}
else
{
result = quotedValue( n->value(), ok );
return ok ? Complete : Fail;
}
}
case QgsExpression::ntColumnRef:
{
const QgsExpression::NodeColumnRef* n = static_cast<const QgsExpression::NodeColumnRef*>( node );
if ( mFields.indexFromName( n->name() ) == -1 )
// Not a provider field
return Fail;
result = quotedIdentifier( n->name() );
return Complete;
}
case QgsExpression::ntInOperator:
{
const QgsExpression::NodeInOperator* n = static_cast<const QgsExpression::NodeInOperator*>( node );
QStringList list;
Result inResult = Complete;
Q_FOREACH ( const QgsExpression::Node* ln, n->list()->list() )
{
QString s;
Result r = compileNode( ln, s );
if ( r == Complete || r == Partial )
{
list << s;
if ( r == Partial )
inResult = Partial;
}
else
return r;
}
QString nd;
Result rn = compileNode( n->node(), nd );
if ( rn != Complete && rn != Partial )
return rn;
result = QStringLiteral( "%1 %2IN(%3)" ).arg( nd, n->isNotIn() ? "NOT " : "", list.join( QStringLiteral( "," ) ) );
return ( inResult == Partial || rn == Partial ) ? Partial : Complete;
}
case QgsExpression::ntFunction:
case QgsExpression::ntCondition:
break;
}
return Fail;
}
void SaGeomColTypeThread::getLayerTypes()
{
mStopped = false;
// establish read-only connection to the database
char errbuf[SACAPI_ERROR_SIZE];
sacapi_i32 code;
SqlAnyConnection *conn = SqlAnyConnection::connect( mConnInfo, true, code, errbuf, sizeof( errbuf ) );
if ( conn )
{
for ( uint i = 0; i < schemas.size() && !mStopped; i++ )
{
QString geomtype = geomtypes[i];
QString sridstr = sridstrs[i];
QString lineinterp = lineinterps[i];
QString sql;
QString quotedTableName;
QString fromStr;
SqlAnyStatement *stmt;
quotedTableName = QString( "%1.%2" )
.arg( quotedIdentifier( schemas[i] ) )
.arg( quotedIdentifier( tables[i] ) );
if ( mEstimateMetadata )
{
fromStr = QString( "(SELECT TOP %1 %2 FROM %3 WHERE %2 IS NOT NULL ) AS sampleGeoms " )
.arg( sGeomTypeSelectLimit )
.arg( quotedIdentifier( columns[i] ) )
.arg( quotedTableName );
}
else
{
fromStr = quotedTableName;
}
// retrieve distinct geometry types
if ( geomtype == "WAITING" )
{
QStringList types;
sql = QString(
"SELECT DISTINCT "
"CASE "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_POINT','ST_MULTIPOINT') THEN 'ST_POINT' "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_LINESTRING','ST_MULTILINESTRING') THEN 'ST_LINESTRING' "
"WHEN UCASE(%1.ST_GeometryType()) IN ('ST_POLYGON','ST_MULTIPOLYGON') THEN 'ST_POLYGON' "
"ELSE 'ST_GEOMETRY' "
"END "
"FROM %2 " )
.arg( quotedIdentifier( columns[i] ) )
.arg( fromStr );
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
while ( stmt->fetchNext() )
{
QString type;
stmt->getString( 0, type );
types += type;
}
}
delete stmt;
if ( types.isEmpty() )
{
geomtype = "ST_GEOMETRY";
}
else
{
geomtype = types.join( "," );
}
}
// retrieve distinct srids
if ( sridstr == "WAITING" )
{
QStringList srids;
QStringList interps;
sql = QString(
"SELECT srid, "
"IF round_earth = 'Y' THEN 'ROUND EARTH' ELSE 'PLANAR' ENDIF "
"FROM ( "
"SELECT DISTINCT %1.ST_SRID() AS srid FROM %2 "
") AS sridlist, SYS.ST_SPATIAL_REFERENCE_SYSTEMS "
"WHERE srid = srs_id " )
.arg( quotedIdentifier( columns[i] ) )
.arg( fromStr );
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
while ( stmt->fetchNext() )
{
int srid;
QString interp;
stmt->getInt( 0, srid );
stmt->getString( 1, interp );
srids += QString::number( srid );
if ( !interps.contains( interp ) )
{
interps += interp;
}
}
}
delete stmt;
if ( srids.isEmpty() )
{
sridstr = "UNKNOWN";
lineinterp = "UNKNOWN";
}
else
{
sridstr = srids.join( "," );
lineinterp = interps.join( "," );
}
}
// Now tell the layer list dialog box...
emit setLayerType( schemas[i], tables[i], columns[i], geomtype, sridstr, lineinterp );
}
conn->release();
}
}
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 QgsNewSpatialiteLayerDialog::apply()
{
// Build up the sql statement for creating the table
QString sql = QString( "create table %1(" ).arg( quotedIdentifier( leLayerName->text() ) );
QString delim = "";
if ( checkBoxPrimaryKey->isChecked() )
{
sql += "pkuid integer primary key autoincrement,";
}
QTreeWidgetItemIterator it( mAttributeView );
while ( *it )
{
sql += delim + QString( "%1 %2" ).arg( quotedIdentifier(( *it )->text( 0 ) ) ).arg(( *it )->text( 1 ) );
delim = ",";
++it;
}
// complete the create table statement
sql += ")";
QgsDebugMsg( QString( "Creating table in database %1" ).arg( mDatabaseComboBox->currentText() ) );
QgsDebugMsg( sql ); // OK
QString sqlAddGeom = QString( "select AddGeometryColumn(%1,%2,%3,%4,2)" )
.arg( quotedValue( leLayerName->text() ) )
.arg( quotedValue( leGeometryColumn->text() ) )
.arg( mCrsId.split( ':' ).value( 1, "0" ).toInt() )
.arg( quotedValue( selectedType() ) );
QgsDebugMsg( sqlAddGeom ); // OK
QString sqlCreateIndex = QString( "select CreateSpatialIndex(%1,%2)" )
.arg( quotedValue( leLayerName->text() ) )
.arg( quotedValue( leGeometryColumn->text() ) );
QgsDebugMsg( sqlCreateIndex ); // OK
sqlite3 *db;
int rc = QgsSLConnect::sqlite3_open( mDatabaseComboBox->currentText().toUtf8(), &db );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "SpatiaLite Database" ),
tr( "Unable to open the database: %1" ).arg( mDatabaseComboBox->currentText() ) );
}
else
{
char * errmsg;
rc = sqlite3_exec( db, sql.toUtf8(), NULL, NULL, &errmsg );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "Error Creating SpatiaLite Table" ),
tr( "Failed to create the SpatiaLite table %1. The database returned:\n%2" ).arg( leLayerName->text() ).arg( errmsg ) );
sqlite3_free( errmsg );
}
else
{
// create the geometry column and the spatial index
rc = sqlite3_exec( db, sqlAddGeom.toUtf8(), NULL, NULL, &errmsg );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "Error Creating Geometry Column" ),
tr( "Failed to create the geometry column. The database returned:\n%1" ).arg( errmsg ) );
sqlite3_free( errmsg );
}
else
{
// create the spatial index
rc = sqlite3_exec( db, sqlCreateIndex.toUtf8(), NULL, NULL, &errmsg );
if ( rc != SQLITE_OK )
{
QMessageBox::warning( this,
tr( "Error Creating Spatial Index" ),
tr( "Failed to create the spatial index. The database returned:\n%1" ).arg( errmsg ) );
sqlite3_free( errmsg );
}
QgsVectorLayer *layer = new QgsVectorLayer( QString( "dbname='%1' table='%2'(%3) sql=" )
.arg( mDatabaseComboBox->currentText() )
.arg( leLayerName->text() )
.arg( leGeometryColumn->text() ), leLayerName->text(), "spatialite" );
if ( layer->isValid() )
{
// register this layer with the central layers registry
QList<QgsMapLayer *> myList;
myList << layer;
//addMapLayers returns a list of all successfully added layers
//so we compare that to our original list.
if ( myList == QgsMapLayerRegistry::instance()->addMapLayers( myList ) )
return true;
}
else
{
QgsDebugMsg( leLayerName->text() + " is an invalid layer - not loaded" );
QMessageBox::critical( this, tr( "Invalid Layer" ), tr( "%1 is an invalid layer and cannot be loaded." ).arg( leLayerName->text() ) );
delete layer;
}
}
}
QgsSLConnect::sqlite3_close( db );
}
return false;
}
QgsOracleFeatureIterator::QgsOracleFeatureIterator( QgsOracleProvider *p, const QgsFeatureRequest &request )
: QgsAbstractFeatureIterator( request )
, P( p )
, mRewind( false )
{
P->mActiveIterators << this;
mQry = QSqlQuery( *P->mConnection );
if ( mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes )
{
mAttributeList = mRequest.subsetOfAttributes();
if ( mAttributeList.isEmpty() )
mAttributeList = P->attributeIndexes();
}
else
mAttributeList = P->attributeIndexes();
QString whereClause;
switch ( request.filterType() )
{
case QgsFeatureRequest::FilterExpression:
break;
case QgsFeatureRequest::FilterRect:
if ( !P->mGeometryColumn.isNull() )
{
QgsRectangle rect( mRequest.filterRect() );
QString bbox = QString( "mdsys.sdo_geometry(2003,%1,NULL,"
"mdsys.sdo_elem_info_array(1,1003,3),"
"mdsys.sdo_ordinate_array(%2,%3,%4,%5)"
")" )
.arg( P->mSrid < 1 ? "NULL" : QString::number( P->mSrid ) )
.arg( qgsDoubleToString( rect.xMinimum() ) )
.arg( qgsDoubleToString( rect.yMinimum() ) )
.arg( qgsDoubleToString( rect.xMaximum() ) )
.arg( qgsDoubleToString( rect.yMaximum() ) );
if ( !P->mSpatialIndex.isNull() )
{
whereClause = QString( "sdo_filter(%1,%2)='TRUE'" ).arg( P->quotedIdentifier( P->mGeometryColumn ) ).arg( bbox );
#if 0
if ( mRequest.flags() & QgsFeatureRequest::ExactIntersect )
{
whereClause += QString( " AND sdo_relate(%1,%2,'mask=ANYINTERACT')='TRUE'" )
.arg( quotedIdentifier( P->mGeometryColumn ) )
.arg( bbox );
}
#endif
}
}
break;
case QgsFeatureRequest::FilterFid:
whereClause = P->whereClause( request.filterFid() );
break;
case QgsFeatureRequest::FilterFids:
whereClause = P->whereClause( request.filterFids() );
break;
case QgsFeatureRequest::FilterNone:
break;
}
if ( P->mRequestedGeomType != QGis::WKBUnknown && P->mRequestedGeomType != P->mDetectedGeomType )
{
if ( !whereClause.isEmpty() )
whereClause += " AND ";
whereClause += QgsOracleConn::databaseTypeFilter( "featureRequest", P->mGeometryColumn, P->mRequestedGeomType );
}
if ( !P->mSqlWhereClause.isEmpty() )
{
if ( !whereClause.isEmpty() )
whereClause += " AND ";
whereClause += "(" + P->mSqlWhereClause + ")";
}
if ( !openQuery( whereClause ) )
return;
}
void QgsOracleConn::retrieveLayerTypes( QgsOracleLayerProperty &layerProperty, bool useEstimatedMetadata, bool onlyExistingTypes )
{
if ( layerProperty.geometryColName.isEmpty() )
return;
QgsDebugMsg( "entering: " + layerProperty.toString() );
QString table;
QString where;
if ( useEstimatedMetadata )
{
table = QString( "(SELECT %1 FROM %2.%3 WHERE %1 IS NOT NULL%4 AND rownum<=%5)" )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( quotedIdentifier( layerProperty.ownerName ) )
.arg( quotedIdentifier( layerProperty.tableName ) )
.arg( layerProperty.sql.isEmpty() ? "" : QString( " AND (%1)" ).arg( layerProperty.sql ) )
.arg( sGeomTypeSelectLimit );
}
else if ( !layerProperty.ownerName.isEmpty() )
{
table = QString( "%1.%2" )
.arg( quotedIdentifier( layerProperty.ownerName ) )
.arg( quotedIdentifier( layerProperty.tableName ) );
where = layerProperty.sql;
}
else
{
table = quotedIdentifier( layerProperty.tableName );
where = layerProperty.sql;
}
QGis::WkbType detectedType = layerProperty.types.value( 0, QGis::WKBUnknown );
int detectedSrid = layerProperty.srids.value( 0, -1 );
Q_ASSERT( detectedType == QGis::WKBUnknown || detectedSrid <= 0 );
QSqlQuery qry( mDatabase );
int idx = 0;
QString sql = "SELECT DISTINCT ";
if ( detectedType == QGis::WKBUnknown )
{
sql += "t.%1.SDO_GTYPE";
if ( detectedSrid <= 0 )
{
sql += ",";
idx = 1;
}
}
if ( detectedSrid <= 0 )
{
sql += "t.%1.SDO_SRID";
}
sql += " FROM %2 t WHERE NOT t.%1 IS NULL%3";
if ( !exec( qry, sql
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( table )
.arg( where.isEmpty() ? "" : QString( " AND (%1)" ).arg( where ) ) ) )
{
QgsMessageLog::logMessage( tr( "SQL:%1\nerror:%2\n" )
.arg( qry.lastQuery() )
.arg( qry.lastError().text() ),
tr( "Oracle" ) );
return;
}
layerProperty.types.clear();
layerProperty.srids.clear();
QSet<int> srids;
while ( qry.next() )
{
if ( detectedType == QGis::WKBUnknown )
{
QGis::WkbType type = wkbTypeFromDatabase( qry.value( 0 ).toInt() );
if ( type == QGis::WKBUnknown )
{
QgsMessageLog::logMessage( tr( "Unsupported geometry type %1 in %2.%3.%4 ignored" )
.arg( qry.value( 0 ).toInt() )
.arg( layerProperty.ownerName ).arg( layerProperty.tableName ).arg( layerProperty.geometryColName ),
tr( "Oracle" ) );
continue;
}
QgsDebugMsg( QString( "add type %1" ).arg( type ) );
layerProperty.types << type;
}
else
{
layerProperty.types << detectedType;
}
int srid = detectedSrid != -1 ? detectedSrid : ( qry.value( idx ).isNull() ? -1 : qry.value( idx ).toInt() );
layerProperty.srids << srid;
srids << srid;
}
qry.finish();
if ( !onlyExistingTypes )
{
layerProperty.types << QGis::WKBUnknown;
layerProperty.srids << ( srids.size() == 1 ? *srids.constBegin() : 0 );
}
if ( layerProperty.isView )
{
layerProperty.pkCols = pkCandidates( layerProperty.ownerName, layerProperty.tableName );
if ( layerProperty.pkCols.isEmpty() )
{
QgsMessageLog::logMessage( tr( "View %1.%2 doesn't have integer columns for use as keys." )
.arg( layerProperty.ownerName ).arg( layerProperty.tableName ),
tr( "Oracle" ) );
}
}
QgsDebugMsg( "leaving." );
}