QStringList QgsOracleConn::pkCandidates( QString ownerName, QString viewName )
{
QStringList cols;
QSqlQuery qry( mDatabase );
if ( !exec( qry, QString( "SELECT column_name FROM all_tab_columns WHERE owner=%1 AND table_name=%2 ORDER BY column_id" )
.arg( quotedValue( ownerName ) ).arg( quotedValue( viewName ) ) ) )
{
QgsMessageLog::logMessage( tr( "SQL:%1\nerror:%2\n" ).arg( qry.lastQuery() ).arg( qry.lastError().text() ), tr( "Oracle" ) );
return cols;
}
while ( qry.next() )
{
cols << qry.value( 0 ).toString();
}
qry.finish();
return cols;
}
QString PyQtProjectItem::pyQtBinaryFilePath( PyQtProjectItem::Binary binary ) const
{
#if defined( Q_OS_WIN )
const QString path = quotedValue( pyQtFilePath() );
switch ( binary ) {
case PyQtProjectItem::Python:
return QString( "%1\\pythonw.exe" ).arg( path );
case PyQtProjectItem::LRelease:
return QString( "%1\\Lib\\site-packages\\PyQt4\\lrelease.exe" ).arg( path );
case PyQtProjectItem::LUpdate:
return QString( "%1\\Lib\\site-packages\\PyQt4\\pylupdate4.exe" ).arg( path );
case PyQtProjectItem::Rcc:
return QString( "%1\\Lib\\site-packages\\PyQt4\\pyrcc4.exe" ).arg( path );
case PyQtProjectItem::Uic:
return QString( "%1 /C %2\\Lib\\site-packages\\PyQt4\\pyuic4.bat" )
.arg( QProcessEnvironment::systemEnvironment().value( "COMSPEC" ) )
.arg( path )
;
}
#else
switch ( binary ) {
case PyQtProjectItem::Python:
return "python";
case PyQtProjectItem::LRelease:
return "lrelease";
case PyQtProjectItem::LUpdate:
return "pylupdate4";
case PyQtProjectItem::Rcc:
return "pyrcc4";
case PyQtProjectItem::Uic:
return "pyuic4";
}
#endif
return QString::null;
}
bool QgsOSMDatabase::createSpatialIndex( const QString& tableName )
{
QString sqlSpatialIndex = QString( "SELECT CreateSpatialIndex(%1, 'geometry')" ).arg( quotedValue( tableName ) );
char *errMsg = nullptr;
int ret = sqlite3_exec( mDatabase, sqlSpatialIndex.toUtf8().constData(), nullptr, nullptr, &errMsg );
if ( ret != SQLITE_OK )
{
mError = "Unable to create spatial index:\n" + QString::fromUtf8( errMsg );
sqlite3_free( errMsg );
return false;
}
return true;
}
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;
}
bool QgsCustomProjectionDialog::saveCRS( QgsCoordinateReferenceSystem myCRS, QString myName, QString myId, bool newEntry )
{
QString mySql;
int return_id;
QString myProjectionAcronym = myCRS.projectionAcronym();
QString myEllipsoidAcronym = myCRS.ellipsoidAcronym();
QgsDebugMsg( QString( "Saving a CRS:%1, %2, %3" ).arg( myName ).arg( myCRS.toProj4() ).arg( newEntry ) );
if ( newEntry )
{
return_id = myCRS.saveAsUserCRS( myName );
if ( return_id == -1 )
return false;
else
myId = QString::number( return_id );
}
else
{
mySql = "update tbl_srs set description="
+ quotedValue( myName )
+ ",projection_acronym=" + quotedValue( myProjectionAcronym )
+ ",ellipsoid_acronym=" + quotedValue( myEllipsoidAcronym )
+ ",parameters=" + quotedValue( myCRS.toProj4() )
+ ",is_geo=0" // <--shamelessly hard coded for now
+ " where srs_id=" + quotedValue( myId )
;
QgsDebugMsg( mySql );
sqlite3 *myDatabase;
const char *myTail;
sqlite3_stmt *myPreparedStatement;
int myResult;
//check if the db is available
myResult = sqlite3_open( QgsApplication::qgisUserDbFilePath().toUtf8(), &myDatabase );
if ( myResult != SQLITE_OK )
{
QgsDebugMsg( QString( "Can't open database: %1 \n please notify QGIS developers of this error \n %2 (file name) " ).arg( sqlite3_errmsg( myDatabase ) ).arg( QgsApplication::qgisUserDbFilePath() ) );
// XXX This will likely never happen since on open, sqlite creates the
// database if it does not exist.
Q_ASSERT( myResult == SQLITE_OK );
}
myResult = sqlite3_prepare( myDatabase, mySql.toUtf8(), mySql.toUtf8().length(), &myPreparedStatement, &myTail );
sqlite3_step( myPreparedStatement );
// XXX Need to free memory from the error msg if one is set
if ( myResult != SQLITE_OK )
{
QgsDebugMsg( QString( "failed to write to database in custom projection dialog: %1 [%2]" ).arg( mySql ).arg( sqlite3_errmsg( myDatabase ) ) );
}
sqlite3_finalize( myPreparedStatement );
// close sqlite3 db
sqlite3_close( myDatabase );
if ( myResult != SQLITE_OK )
return false;
}
existingCRSparameters[myId] = myCRS.toProj4();
existingCRSnames[myId] = myName;
QgsCRSCache::instance()->updateCRSCache( QString( "USER:%1" ).arg( myId ) );
// If we have a projection acronym not in the user db previously, add it.
// This is a must, or else we can't select it from the vw_srs table.
// Actually, add it always and let the SQL PRIMARY KEY remove duplicates.
insertProjection( myProjectionAcronym );
return true;
}
void QgsCustomProjectionDialog::insertProjection( QString myProjectionAcronym )
{
sqlite3 *myDatabase;
sqlite3_stmt *myPreparedStatement;
sqlite3 *srsDatabase;
QString mySql;
const char *myTail;
//check the db is available
int myResult = sqlite3_open( QgsApplication::qgisUserDbFilePath().toUtf8(), &myDatabase );
if ( myResult != SQLITE_OK )
{
QgsDebugMsg( QString( "Can't open database: %1 \n please notify QGIS developers of this error \n %2 (file name) " ).arg( sqlite3_errmsg( myDatabase ) ).arg( QgsApplication::qgisUserDbFilePath() ) );
// XXX This will likely never happen since on open, sqlite creates the
// database if it does not exist.
Q_ASSERT( myResult == SQLITE_OK );
}
int srsResult = sqlite3_open( QgsApplication::srsDbFilePath().toUtf8(), &srsDatabase );
if ( myResult != SQLITE_OK )
{
QgsDebugMsg( QString( "Can't open database %1 [%2]" ).arg( QgsApplication::srsDbFilePath() ).arg( sqlite3_errmsg( srsDatabase ) ) );
}
else
{
// Set up the query to retrieve the projection information needed to populate the PROJECTION list
QString srsSql = "select acronym,name,notes,parameters from tbl_projection where acronym=" + quotedValue( myProjectionAcronym );
const char *srsTail;
sqlite3_stmt *srsPreparedStatement;
srsResult = sqlite3_prepare( srsDatabase, srsSql.toUtf8(), srsSql.length(), &srsPreparedStatement, &srsTail );
// XXX Need to free memory from the error msg if one is set
if ( srsResult == SQLITE_OK )
{
if ( sqlite3_step( srsPreparedStatement ) == SQLITE_ROW )
{
QgsDebugMsg( "Trying to insert projection" );
// We have the result from system srs.db. Now insert into user db.
mySql = "insert into tbl_projection(acronym,name,notes,parameters) values ("
+ quotedValue( QString::fromUtf8(( char * )sqlite3_column_text( srsPreparedStatement, 0 ) ) )
+ "," + quotedValue( QString::fromUtf8(( char * )sqlite3_column_text( srsPreparedStatement, 1 ) ) )
+ "," + quotedValue( QString::fromUtf8(( char * )sqlite3_column_text( srsPreparedStatement, 2 ) ) )
+ "," + quotedValue( QString::fromUtf8(( char * )sqlite3_column_text( srsPreparedStatement, 3 ) ) )
+ ")"
;
myResult = sqlite3_prepare( myDatabase, mySql.toUtf8(), mySql.length(), &myPreparedStatement, &myTail );
sqlite3_step( myPreparedStatement );
if ( myResult != SQLITE_OK )
{
QgsDebugMsg( QString( "Update or insert failed in custom projection dialog: %1 [%2]" ).arg( mySql ).arg( sqlite3_errmsg( myDatabase ) ) );
}
sqlite3_finalize( myPreparedStatement );
}
sqlite3_finalize( srsPreparedStatement );
}
else
{
QgsDebugMsg( QString( "prepare failed: %1 [%2]" ).arg( srsSql ).arg( sqlite3_errmsg( srsDatabase ) ) );
}
sqlite3_close( srsDatabase );
}
// close sqlite3 db
sqlite3_close( myDatabase );
}
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;
}
QString QgsExpression::quotedValue( const QVariant &value )
{
return quotedValue( value, value.type() );
}
QgsSqlExpressionCompiler::Result QgsDb2ExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
{
QgsDebugMsg( QStringLiteral( "nodeType: %1" ).arg( nodeType( node ) ) );
if ( node->nodeType() == QgsExpressionNode::ntColumnRef )
{
const QgsExpressionNodeColumnRef *n( static_cast<const QgsExpressionNodeColumnRef *>( node ) );
QgsDebugMsg( QStringLiteral( "column ref node: " ) + n->dump() );
// TODO - consider escaped names - not sure how to handle
QString upperName = n->name().toUpper();
int idx = mFields.indexFromName( upperName );
QgsDebugMsg( QStringLiteral( "%1 - %2" ).arg( idx ).arg( upperName ) );
if ( idx > -1 )
{
result = upperName;
QgsDebugMsg( QStringLiteral( "return Complete" ) );
return Complete;
}
QgsDebugMsg( QStringLiteral( "return Fail" ) );
return Fail;
}
// Seemed necessary in initial Python testing but can't identify failing case now
#if 0
if ( node->nodeType() == QgsExpressionNode::ntLiteral )
{
const QgsExpression::NodeLiteral *n = static_cast<const QgsExpression::NodeLiteral *>( node );
bool ok = false;
if ( n->dump().toUpper() == "NULL" ) // expression compiler doesn't handle this correctly
{
result = "NULL";
ok = true;
}
else
{
result = quotedValue( n->value(), ok );
}
QgsDebugMsg( QStringLiteral( "ok: %1; literal node: " ).arg( ok ) + n->value().toString() + "; result: " + result );
QgsDebugMsg( QStringLiteral( "n->dump: " ) + n->dump() );
QgsDebugMsg( QStringLiteral( "type: %1; typeName: %2" ).arg( n->value().type() ).arg( n->value().typeName() ) );
if ( ok )
{
QgsDebugMsg( QStringLiteral( "return Complete" ) );
return Complete;
}
else
{
QgsDebugMsg( QStringLiteral( "return Fail" ) );
return Fail;
}
}
#endif
if ( node->nodeType() == QgsExpressionNode::ntUnaryOperator )
{
const QgsExpressionNodeUnaryOperator *n = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
Result rr = Fail;
switch ( n->op() )
{
case QgsExpressionNodeUnaryOperator::uoNot:
rr = compileNode( n->operand(), result );
if ( "NULL" == result.toUpper() )
{
result.clear();
return Fail;
}
result = "NOT " + result;
QgsDebugMsg( QStringLiteral( "NOT; result: %1; right: %2" ).arg( resultType( rr ), result ) );
return rr;
case QgsExpressionNodeUnaryOperator::uoMinus:
break;
}
}
if ( node->nodeType() == QgsExpressionNode::ntBinaryOperator )
{
const QgsExpressionNodeBinaryOperator *bin( static_cast<const QgsExpressionNodeBinaryOperator *>( node ) );
QString left, right;
Result lr = compileNode( bin->opLeft(), left );
Result rr = compileNode( bin->opRight(), right );
Result compileResult;
QgsDebugMsg( "left: '" + left + "'; right: '" + right +
QString( "'; op: %1; lr: %2; rr: %3" ).arg( bin->op() ).arg( lr ).arg( rr ) );
if ( lr == Fail || rr == Fail )
return Fail;
// NULL can not appear on the left, only as part of IS NULL or IS NOT NULL
if ( "NULL" == left.toUpper() ) return Fail;
// NULL can only be on the right for IS and IS NOT
if ( "NULL" == right.toUpper() && ( bin->op() != QgsExpressionNodeBinaryOperator::boIs && bin->op() != QgsExpressionNodeBinaryOperator::boIsNot ) )
return Fail;
switch ( bin->op() )
{
case QgsExpressionNodeBinaryOperator::boMod:
result = QStringLiteral( "MOD(%1,%2)" ).arg( left, right );
compileResult = ( lr == Partial || rr == Partial ) ? Partial : Complete;
QgsDebugMsg( QStringLiteral( "MOD compile status: %1" ).arg( compileResult ) + "; " + result );
return compileResult;
case QgsExpressionNodeBinaryOperator::boPow:
result = QStringLiteral( "power(%1,%2)" ).arg( left, right );
compileResult = ( lr == Partial || rr == Partial ) ? Partial : Complete;
QgsDebugMsg( QStringLiteral( "POWER compile status: %1" ).arg( compileResult ) + "; " + result );
return compileResult;
case QgsExpressionNodeBinaryOperator::boRegexp:
return Fail; //not supported, regexp syntax is too different to Qt
case QgsExpressionNodeBinaryOperator::boConcat:
result = QStringLiteral( "%1 || %2" ).arg( left, right );
compileResult = ( lr == Partial || rr == Partial ) ? Partial : Complete;
QgsDebugMsg( QStringLiteral( "CONCAT compile status: %1" ).arg( compileResult ) + "; " + result );
return compileResult;
case QgsExpressionNodeBinaryOperator::boILike:
QgsDebugMsg( QStringLiteral( "ILIKE is not supported by DB2" ) );
return Fail;
/*
result = QString( "%1 LIKE %2" ).arg( left, right );
compileResult = (lr == Partial || rr == Partial) ? Partial : Complete;
QgsDebugMsg(QString("ILIKE compile status: %1").arg(compileResult) + "; " + result);
return compileResult;
*/
case QgsExpressionNodeBinaryOperator::boNotILike:
QgsDebugMsg( QStringLiteral( "NOT ILIKE is not supported by DB2" ) );
return Fail;
/*
result = QString( "%1 NOT LIKE %2" ).arg( left, right );
compileResult = (lr == Partial || rr == Partial) ? Partial : Complete;
QgsDebugMsg(QString("NOT ILIKE compile status: %1").arg(compileResult) + "; " + result);
return compileResult;
*/
// We only support IS NULL if the operand on the left is a column
case QgsExpressionNodeBinaryOperator::boIs:
if ( "NULL" == right.toUpper() )
{
if ( bin->opLeft()->nodeType() != QgsExpressionNode::ntColumnRef )
{
QgsDebugMsg( "Failing IS NULL with non-column on left: " + left );
return Fail;
}
}
break;
// We only support IS NULL if the operand on the left is a column
case QgsExpressionNodeBinaryOperator::boIsNot:
if ( "NULL" == right.toUpper() )
{
if ( bin->opLeft()->nodeType() != QgsExpressionNode::ntColumnRef )
{
QgsDebugMsg( "Failing IS NOT NULL with non-column on left: " + left );
return Fail;
}
}
break;
default:
break;
}
}
//fallback to default handling
QgsDebugMsg( QStringLiteral( "fallback: %1 - " ).arg( nodeType( node ) ) );
QgsSqlExpressionCompiler::Result rc = QgsSqlExpressionCompiler::compileNode( node, result );
QgsDebugMsg( QStringLiteral( "fallback: %1 - " ).arg( resultType( rc ) ) + result );
return rc;
}
bool QgsCustomProjectionDialog::saveCrs( QgsCoordinateReferenceSystem parameters, const QString &name, const QString &existingId, bool newEntry )
{
QString id = existingId;
QString sql;
int returnId;
QString projectionAcronym = parameters.projectionAcronym();
QString ellipsoidAcronym = parameters.ellipsoidAcronym();
QgsDebugMsg( QString( "Saving a CRS:%1, %2, %3" ).arg( name, parameters.toProj4() ).arg( newEntry ) );
if ( newEntry )
{
returnId = parameters.saveAsUserCrs( name );
if ( returnId == -1 )
return false;
else
id = QString::number( returnId );
}
else
{
sql = "update tbl_srs set description="
+ quotedValue( name )
+ ",projection_acronym=" + quotedValue( projectionAcronym )
+ ",ellipsoid_acronym=" + quotedValue( ellipsoidAcronym )
+ ",parameters=" + quotedValue( parameters.toProj4() )
+ ",is_geo=0" // <--shamelessly hard coded for now
+ " where srs_id=" + quotedValue( id )
;
QgsDebugMsg( sql );
sqlite3_database_unique_ptr database;
//check if the db is available
int result = database.open( QgsApplication::qgisUserDatabaseFilePath() );
if ( result != SQLITE_OK )
{
QgsDebugMsg( QString( "Can't open database: %1 \n please notify QGIS developers of this error \n %2 (file name) " ).arg( database.errorMessage(),
QgsApplication::qgisUserDatabaseFilePath() ) );
// XXX This will likely never happen since on open, sqlite creates the
// database if it does not exist.
Q_ASSERT( result == SQLITE_OK );
}
sqlite3_statement_unique_ptr preparedStatement = database.prepare( sql, result );
if ( result != SQLITE_OK || preparedStatement.step() != SQLITE_DONE )
{
QgsDebugMsg( QString( "failed to write to database in custom projection dialog: %1 [%2]" ).arg( sql, database.errorMessage() ) );
}
preparedStatement.reset();
if ( result != SQLITE_OK )
return false;
}
mExistingCRSparameters[id] = parameters.toProj4();
mExistingCRSnames[id] = name;
QgsCoordinateReferenceSystem::invalidateCache();
QgsCoordinateTransformCache::instance()->invalidateCrs( QStringLiteral( "USER:%1" ).arg( id ) );
// If we have a projection acronym not in the user db previously, add it.
// This is a must, or else we can't select it from the vw_srs table.
// Actually, add it always and let the SQL PRIMARY KEY remove duplicates.
insertProjection( projectionAcronym );
return true;
}
QgsOracleConn::QgsOracleConn( QgsDataSourceUri uri )
: mRef( 1 )
, mCurrentUser( QString::null )
, mHasSpatial( -1 )
{
QgsDebugMsg( QString( "New Oracle connection for " ) + uri.connectionInfo() );
QString database = databaseName( uri.database(), uri.host(), uri.port() );
QgsDebugMsg( QString( "New Oracle database " ) + database );
mDatabase = QSqlDatabase::addDatabase( "QOCISPATIAL", QString( "oracle%1" ).arg( snConnections++ ) );
mDatabase.setDatabaseName( database );
QString options = uri.hasParam( "dboptions" ) ? uri.param( "dboptions" ) : "OCI_ATTR_PREFETCH_ROWS=1000";
QString workspace = uri.hasParam( "dbworkspace" ) ? uri.param( "dbworkspace" ) : QString::null;
mDatabase.setConnectOptions( options );
mDatabase.setUserName( uri.username() );
mDatabase.setPassword( uri.password() );
QgsDebugMsg( QString( "Connecting with options: " ) + options );
if ( !mDatabase.open() )
{
QString username = uri.username();
QString password = uri.password();
QString realm( database );
if ( !username.isEmpty() )
realm.prepend( username + "@" );
QgsCredentials::instance()->lock();
while ( !mDatabase.open() )
{
bool ok = QgsCredentials::instance()->get( realm, username, password, mDatabase.lastError().text() );
if ( !ok )
break;
if ( !username.isEmpty() )
{
uri.setUsername( username );
realm = username + "@" + database;
}
if ( !password.isEmpty() )
uri.setPassword( password );
QgsDebugMsg( "Connecting to " + database );
mDatabase.setUserName( username );
mDatabase.setPassword( password );
}
if ( mDatabase.isOpen() )
QgsCredentials::instance()->put( realm, username, password );
QgsCredentials::instance()->unlock();
}
if ( !mDatabase.isOpen() )
{
mDatabase.close();
QgsMessageLog::logMessage( tr( "Connection to database failed" ), tr( "Oracle" ) );
mRef = 0;
return;
}
if ( !workspace.isNull() )
{
QSqlQuery qry( mDatabase );
if ( !qry.exec( QString( "BEGIN\nDBMS_WM.GotoWorkspace(%1);\nEND;" ).arg( quotedValue( workspace ) ) ) )
{
mDatabase.close();
QgsMessageLog::logMessage( tr( "Could not switch to workspace %1 [%2]" ).arg( workspace, qry.lastError().databaseText() ), tr( "Oracle" ) );
mRef = 0;
return;
}
}
}
/*
* check if srs is a geocs or a proj cs (using ogr isGeographic)
* then sequentially walk through the database (first users qgis.db srs tbl then
* system srs.db tbl), converting each entry into an ogr srs and using isSame
* or isSameGeocs (essentially calling the == overloaded operator). We'll try to
* be smart about this and first parse out the proj and ellpse strings and only
* check for a match in entities that have the same ellps and proj entries so
* that it doesnt munch yer cpu so much.
*/
long QgsCoordinateReferenceSystem::findMatchingProj()
{
QgsDebugMsg( "entered." );
if ( mEllipsoidAcronym.isNull() || mProjectionAcronym.isNull() || !mIsValidFlag )
{
QgsDebugMsg( "QgsCoordinateReferenceSystem::findMatchingProj will only work if prj acr ellipsoid acr and proj4string are set"
" and the current projection is valid!" );
return 0;
}
sqlite3 *myDatabase;
const char *myTail;
sqlite3_stmt *myPreparedStatement;
int myResult;
// Set up the query to retrieve the projection information needed to populate the list
QString mySql = QString( "select srs_id,parameters from tbl_srs where projection_acronym=%1 and ellipsoid_acronym=%2" )
.arg( quotedValue( mProjectionAcronym ) )
.arg( quotedValue( mEllipsoidAcronym ) );
// Get the full path name to the sqlite3 spatial reference database.
QString myDatabaseFileName = QgsApplication::srsDbFilePath();
//check the db is available
myResult = openDb( myDatabaseFileName, &myDatabase );
if ( myResult != SQLITE_OK )
{
return 0;
}
myResult = sqlite3_prepare( myDatabase, mySql.toUtf8(), mySql.toUtf8().length(), &myPreparedStatement, &myTail );
// XXX Need to free memory from the error msg if one is set
if ( myResult == SQLITE_OK )
{
while ( sqlite3_step( myPreparedStatement ) == SQLITE_ROW )
{
QString mySrsId = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 0 ) );
QString myProj4String = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 1 ) );
if ( equals( myProj4String ) )
{
QgsDebugMsg( "-------> MATCH FOUND in srs.db srsid: " + mySrsId );
// close the sqlite3 statement
sqlite3_finalize( myPreparedStatement );
sqlite3_close( myDatabase );
return mySrsId.toLong();
}
else
{
// QgsDebugMsg(QString(" Not matched : %1").arg(myProj4String));
}
}
}
QgsDebugMsg( "no match found in srs.db, trying user db now!" );
// close the sqlite3 statement
sqlite3_finalize( myPreparedStatement );
sqlite3_close( myDatabase );
//
// Try the users db now
//
myDatabaseFileName = QgsApplication::qgisUserDbFilePath();
//check the db is available
myResult = openDb( myDatabaseFileName, &myDatabase );
if ( myResult != SQLITE_OK )
{
return 0;
}
myResult = sqlite3_prepare( myDatabase, mySql.toUtf8(), mySql.toUtf8().length(), &myPreparedStatement, &myTail );
// XXX Need to free memory from the error msg if one is set
if ( myResult == SQLITE_OK )
{
while ( sqlite3_step( myPreparedStatement ) == SQLITE_ROW )
{
QString mySrsId = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 0 ) );
QString myProj4String = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 1 ) );
if ( equals( myProj4String ) )
{
QgsDebugMsg( "-------> MATCH FOUND in user qgis.db srsid: " + mySrsId );
// close the sqlite3 statement
sqlite3_finalize( myPreparedStatement );
sqlite3_close( myDatabase );
return mySrsId.toLong();
}
else
{
// QgsDebugMsg(QString(" Not matched : %1").arg(myProj4String));
}
}
}
QgsDebugMsg( "no match found in user db" );
// close the sqlite3 statement
sqlite3_finalize( myPreparedStatement );
sqlite3_close( myDatabase );
return 0;
}
bool QgsCoordinateReferenceSystem::createFromProj4( const QString theProj4String )
{
//
// Examples:
// +proj=tmerc +lat_0=0 +lon_0=-62 +k=0.999500 +x_0=400000 +y_0=0
// +ellps=clrk80 +towgs84=-255,-15,71,0,0,0,0 +units=m +no_defs
//
// +proj=lcc +lat_1=46.8 +lat_0=46.8 +lon_0=2.337229166666664 +k_0=0.99987742
// +x_0=600000 +y_0=2200000 +a=6378249.2 +b=6356515.000000472 +units=m +no_defs
//
mIsValidFlag = false;
QRegExp myProjRegExp( "\\+proj=\\S+" );
int myStart = 0;
int myLength = 0;
myStart = myProjRegExp.indexIn( theProj4String, myStart );
if ( myStart == -1 )
{
QgsDebugMsg( "error proj string supplied has no +proj argument" );
return mIsValidFlag;
}
else
{
myLength = myProjRegExp.matchedLength();
}
mProjectionAcronym = theProj4String.mid( myStart + PROJ_PREFIX_LEN, myLength - PROJ_PREFIX_LEN );
QRegExp myEllipseRegExp( "\\+ellps=\\S+" );
myStart = 0;
myLength = 0;
myStart = myEllipseRegExp.indexIn( theProj4String, myStart );
if ( myStart != -1 )
{
myLength = myEllipseRegExp.matchedLength();
mEllipsoidAcronym = theProj4String.mid( myStart + ELLPS_PREFIX_LEN, myLength - ELLPS_PREFIX_LEN );
}
QRegExp myAxisRegExp( "\\+a=\\S+" );
myStart = 0;
myLength = 0;
myStart = myAxisRegExp.indexIn( theProj4String, myStart );
if ( myStart == -1 && mEllipsoidAcronym.isNull() )
{
QgsDebugMsg( "proj string supplied has no +ellps or +a argument" );
return mIsValidFlag;
}
/*
* We try to match the proj string to and srsid using the following logic:
*
* - perform a whole text search on srs name (if not null). The srs name will
* have been set if this method has been delegated to from createFromWkt.
* Normally we wouldnt expect this to work, but its worth trying first
* as its quicker than methods below..
*/
long mySrsId = 0;
QgsCoordinateReferenceSystem::RecordMap myRecord;
// *** Matching on descriptions feels iffy. Different projs can have same description. Homann ***
// if ( !mDescription.trimmed().isEmpty() )
//{
// myRecord = getRecord( "select * from tbl_srs where description=" + quotedValue( mDescription.trimmed() ) );
//}
/*
* - if the above does not match perform a whole text search on proj4 string (if not null)
*/
// QgsDebugMsg( "wholetext match on name failed, trying proj4string match" );
myRecord = getRecord( "select * from tbl_srs where parameters=" + quotedValue( theProj4String.trimmed() ) );
if ( !myRecord.empty() )
{
mySrsId = myRecord["srs_id"].toLong();
QgsDebugMsg( "proj4string match search for srsid returned srsid: " + QString::number( mySrsId ) );
if ( mySrsId > 0 )
{
createFromSrsId( mySrsId );
}
}
else
{
// Ticket #722 - aaronr
// Check if we can swap the lat_1 and lat_2 params (if they exist) to see if we match...
// First we check for lat_1 and lat_2
QRegExp myLat1RegExp( "\\+lat_1=\\S+" );
QRegExp myLat2RegExp( "\\+lat_2=\\S+" );
int myStart1 = 0;
int myLength1 = 0;
int myStart2 = 0;
int myLength2 = 0;
QString lat1Str = "";
QString lat2Str = "";
myStart1 = myLat1RegExp.indexIn( theProj4String, myStart1 );
myStart2 = myLat2RegExp.indexIn( theProj4String, myStart2 );
if (( myStart1 != -1 ) && ( myStart2 != -1 ) )
{
myLength1 = myLat1RegExp.matchedLength();
myLength2 = myLat2RegExp.matchedLength();
lat1Str = theProj4String.mid( myStart1 + LAT_PREFIX_LEN, myLength1 - LAT_PREFIX_LEN );
lat2Str = theProj4String.mid( myStart2 + LAT_PREFIX_LEN, myLength2 - LAT_PREFIX_LEN );
}
// If we found the lat_1 and lat_2 we need to swap and check to see if we can find it...
if (( lat1Str != "" ) && ( lat2Str != "" ) )
{
// Make our new string to check...
QString theProj4StringModified = theProj4String;
// First just swap in the lat_2 value for lat_1 value
theProj4StringModified.replace( myStart1 + LAT_PREFIX_LEN, myLength1 - LAT_PREFIX_LEN, lat2Str );
// Now we have to find the lat_2 location again since it has potentially moved...
myStart2 = 0;
myStart2 = myLat2RegExp.indexIn( theProj4String, myStart2 );
theProj4StringModified.replace( myStart2 + LAT_PREFIX_LEN, myLength2 - LAT_PREFIX_LEN, lat1Str );
QgsDebugMsg( "trying proj4string match with swapped lat_1,lat_2" );
myRecord = getRecord( "select * from tbl_srs where parameters=" + quotedValue( theProj4StringModified.trimmed() ) );
if ( !myRecord.empty() )
{
// Success! We have found the proj string by swapping the lat_1 and lat_2
setProj4String( theProj4StringModified );
mySrsId = myRecord["srs_id"].toLong();
QgsDebugMsg( "proj4string match search for srsid returned srsid: " + QString::number( mySrsId ) );
if ( mySrsId > 0 )
{
createFromSrsId( mySrsId );
}
}
}
else
{
// Last ditch attempt to piece together what we know of the projection to find a match...
QgsDebugMsg( "globbing search for srsid from this proj string" );
setProj4String( theProj4String );
mySrsId = findMatchingProj();
QgsDebugMsg( "globbing search for srsid returned srsid: " + QString::number( mySrsId ) );
if ( mySrsId > 0 )
{
createFromSrsId( mySrsId );
}
else
{
mIsValidFlag = false;
}
}
}
// if we failed to look up the projection in database, don't worry. we can still use it :)
if ( !mIsValidFlag )
{
QgsDebugMsg( "Projection is not found in databases." );
setProj4String( theProj4String );
// Is the SRS is valid now, we know it's a decent +proj string that can be entered into the srs.db
if ( mIsValidFlag )
{
// but the proj.4 parsed string might already be in our database
myRecord = getRecord( "select * from tbl_srs where parameters=" + quotedValue( toProj4() ) );
if ( myRecord.empty() )
{
// It's not, so try to add it
QgsDebugMsg( "Projection appears to be valid. Save to database!" );
mIsValidFlag = saveAsUserCRS();
if ( mIsValidFlag )
{
// but validate that it's there afterwards
myRecord = getRecord( "select * from tbl_srs where parameters=" + quotedValue( toProj4() ) );
}
}
if ( !myRecord.empty() )
{
// take the srid from the record
mySrsId = myRecord["srs_id"].toLong();
QgsDebugMsg( "proj4string match search for srsid returned srsid: " + QString::number( mySrsId ) );
if ( mySrsId > 0 )
{
createFromSrsId( mySrsId );
}
else
{
QgsDebugMsg( QString( "invalid srid %1 found" ).arg( mySrsId ) );
mIsValidFlag = false;
}
}
else
{
QgsDebugMsg( "Couldn't find newly added proj string?" );
mIsValidFlag = false;
}
}
}
return mIsValidFlag;
}
bool QgsCoordinateReferenceSystem::loadFromDb( QString db, QString expression, QString value )
{
QgsDebugMsgLevel( "load CRS from " + db + " where " + expression + " is " + value, 3 );
mIsValidFlag = false;
QFileInfo myInfo( db );
if ( !myInfo.exists() )
{
QgsDebugMsg( "failed : " + db + " does not exist!" );
return mIsValidFlag;
}
sqlite3 *myDatabase;
const char *myTail;
sqlite3_stmt *myPreparedStatement;
int myResult;
//check the db is available
myResult = openDb( db, &myDatabase );
if ( myResult != SQLITE_OK )
{
QgsDebugMsg( "failed : " + db + " could not be opened!" );
return mIsValidFlag;
}
/*
srs_id INTEGER PRIMARY KEY,
description text NOT NULL,
projection_acronym text NOT NULL,
ellipsoid_acronym NOT NULL,
parameters text NOT NULL,
srid integer NOT NULL,
auth_name varchar NOT NULL,
auth_id integer NOT NULL,
is_geo integer NOT NULL);
*/
QString mySql = "select srs_id,description,projection_acronym,ellipsoid_acronym,parameters,srid,auth_name||':'||auth_id,is_geo from tbl_srs where " + expression + "=" + quotedValue( value );
myResult = sqlite3_prepare( myDatabase, mySql.toUtf8(), mySql.toUtf8().length(), &myPreparedStatement, &myTail );
// XXX Need to free memory from the error msg if one is set
if ( myResult == SQLITE_OK && sqlite3_step( myPreparedStatement ) == SQLITE_ROW )
{
mSrsId = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 0 ) ).toLong();
mDescription = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 1 ) );
mProjectionAcronym = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 2 ) );
mEllipsoidAcronym = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 3 ) );
QString toProj4 = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 4 ) );
mSRID = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 5 ) ).toLong();
mAuthId = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 6 ) );
int geo = QString::fromUtf8(( char * )sqlite3_column_text( myPreparedStatement, 7 ) ).toInt();
mGeoFlag = ( geo == 0 ? false : true );
setProj4String( toProj4 );
setMapUnits();
}
else
{
QgsDebugMsg( "failed : " + mySql );
}
sqlite3_finalize( myPreparedStatement );
sqlite3_close( myDatabase );
return mIsValidFlag;
}
