QgsSqlAnywhereFeatureIterator::QgsSqlAnywhereFeatureIterator( QgsSqlAnywhereProvider* p, const QgsFeatureRequest & request )
: QgsAbstractFeatureIterator( request ), P( p )
, mStmt( NULL ), mStmtRect()
{
mClosed = false;
QString whereClause = P->getWhereClause();
if ( request.filterType() == QgsFeatureRequest::FilterRect && !P->mGeometryColumn.isNull() )
{
mStmtRect = mRequest.filterRect();
SaDebugMsg( "initial rectangle =" + mStmtRect.toString() );
mStmtRect = mStmtRect.intersect( &P->mSrsExtent );
SaDebugMsg( "rectangle clipped to SRS extent =" + mStmtRect.toString() );
whereClause += " AND " + whereClauseRect();
}
else if ( request.filterType() == QgsFeatureRequest::FilterFid )
{
whereClause += " AND " + whereClauseFid();
}
if ( !prepareStatement( whereClause ) )
{
mStmt = NULL;
mClosed = true;
return;
}
}
void SaSourceSelect::setSql( const QModelIndex &index )
{
if ( !index.parent().isValid() )
{
SaDebugMsg( "schema item found" );
return;
}
QgsVectorLayer *vlayer = new QgsVectorLayer( layerURI( mProxyModel.mapToSource( index ) ), "querybuilder", "sqlanywhere" );
if ( !vlayer->isValid() )
{
delete vlayer;
return;
}
// create a query builder object
SaQueryBuilder *qb = new SaQueryBuilder( vlayer, this );
if ( qb->exec() )
{
mTableModel.setSql( mProxyModel.mapToSource( index ), qb->sql() );
}
delete qb;
delete vlayer;
}
bool
QgsSqlAnywhereFeatureIterator::nextFeature( QgsFeature& feature )
{
if ( mClosed )
return false;
feature.setValid( false );
if ( !P->isValid() )
{
SaDebugMsg( "Read attempt on an invalid SQL Anywhere data source" );
return false;
}
if ( mStmt == NULL || !mStmt->isValid() )
{
SaDebugMsg( "nextFeature() called with invalid cursor()" );
return false;
}
return nextFeature( feature, mStmt );
}
bool
QgsSqlAnywhereFeatureIterator::prepareStatement( QString whereClause )
// adapted from QgsSpatialLiteFeatureIterator::prepareStatement
{
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) && P->mGeometryColumn.isNull() )
return false;
if ( !P->ensureConnRO() )
{
SaDebugMsg( "No read-only database connection." );
return false;
}
QString sql = QString( "SELECT %1" ).arg( quotedPrimaryKey() ); // Column 0 is primary key
// Column 1 is geometry, if applicable
if ( !( mRequest.flags() & QgsFeatureRequest::NoGeometry ) )
{
sql += QString( ", %1 .ST_AsBinary('WKB(Version=1.1;endian=%2)') " )
.arg( P->mGeometryColumn )
.arg( QgsApplication::endian() == QgsApplication::XDR ? "xdr" : "ndr" );
}
// Add the rest of the columns
if ( mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes )
{
const QgsAttributeList& fetchAttributes = mRequest.subsetOfAttributes();
for ( QgsAttributeList::const_iterator it = fetchAttributes.constBegin(); it != fetchAttributes.constEnd(); ++it )
{
QString name = P->field( *it ).name();
if ( !name.isEmpty() /*&& name != P->mKeyColumn*/ )
{
sql += "," + P->quotedIdentifier( name );
}
}
}
else
{
// fetch all attributes
for ( int idx = 0; idx < P->mAttributeFields.count(); ++idx )
{
QString name = P->mAttributeFields[idx].name();
if ( !name.isEmpty() /*&& name != P->mKeyColumn*/ )
{
sql += "," + P->quotedIdentifier( name );
}
}
}
sql += QString( " FROM %1 " ).arg( P->mQuotedTableName );
if ( !whereClause.isEmpty() )
sql += QString( " WHERE %1 OPTIONS(FORCE OPTIMIZATION)" ).arg( whereClause );
if ( mStmt )
delete mStmt;
mStmt = P->mConnRO->prepare( sql );
if ( !mStmt->isValid() )
{
// (Error msg will be printed using SaDebugMsg in prepare())
rewind();
return false;
}
// bind parameters if necessary
if ( !mStmtRect.isEmpty() )
{
double xmin = mStmtRect.xMinimum();
double ymin = mStmtRect.yMinimum();
double xmax = mStmtRect.xMaximum();
double ymax = mStmtRect.yMaximum();
a_sqlany_bind_param xminParam;
a_sqlany_bind_param yminParam;
a_sqlany_bind_param xmaxParam;
a_sqlany_bind_param ymaxParam;
size_t xminLen = sizeof( double );
size_t yminLen = sizeof( double );
size_t xmaxLen = sizeof( double );
size_t ymaxLen = sizeof( double );
if ( !mStmt->describe_bind_param( 0, xminParam )
|| !mStmt->describe_bind_param( 1, yminParam )
|| !mStmt->describe_bind_param( 2, xmaxParam )
|| !mStmt->describe_bind_param( 3, ymaxParam ) )
{
P->reportError( QObject::tr( "Error describing bind parameters" ), mStmt );
return false;
}
xminParam.value.buffer = ( char * ) & xmin;
yminParam.value.buffer = ( char * ) & ymin;
xmaxParam.value.buffer = ( char * ) & xmax;
ymaxParam.value.buffer = ( char * ) & ymax;
xminParam.value.length = &xminLen;
yminParam.value.length = &yminLen;
xmaxParam.value.length = &xmaxLen;
ymaxParam.value.length = &ymaxLen;
xminParam.value.type = A_DOUBLE;
yminParam.value.type = A_DOUBLE;
xmaxParam.value.type = A_DOUBLE;
ymaxParam.value.type = A_DOUBLE;
if ( !mStmt->bind_param( 0, xminParam )
|| !mStmt->bind_param( 1, yminParam )
|| !mStmt->bind_param( 2, xmaxParam )
|| !mStmt->bind_param( 3, ymaxParam ) )
{
P->reportError( QObject::tr( "Error binding parameters" ), mStmt );
return false;
}
}
// Execute the statement
if ( !mStmt->execute() )
{
// (Error msg will be printed using SaDebugMsg in execute())
rewind();
return false;
}
return true;
}
bool QgsSqlAnywhereFeatureIterator::nextFeature( QgsFeature& feature, SqlAnyStatement *stmt )
{
feature.setValid( false );
bool fetchGeometry = !( mRequest.flags() & QgsFeatureRequest::NoGeometry );
bool subsetAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
if ( mClosed )
return false;
if ( !P->mConnRO || !P->mConnRO->isAlive() )
{
SaDebugMsg( "No database connection." );
return false;
}
bool ok;
int id;
a_sqlany_data_value geom;
unsigned char *geomBuf = NULL;
ok = ( stmt != NULL && stmt->fetchNext() );
// if no more rows...
if ( !ok )
return false;
if ( !fetchGeometry )
feature.setGeometryAndOwnership( 0, 0 );
int numAttributes = P->fields().count(); // also used later for sanity check
feature.initAttributes( numAttributes );
feature.setFields( &P->mAttributeFields ); // allow name-based attribute lookups
int i = 0;
int numcols = stmt->numCols();
int colidx = 0; // Refers to which column we're on in "feature" (the row)
for ( i = 0; i < numcols; i++ )
{
if ( i == 0 )
{
// first column always contains primary key
ok = stmt->getInt( i, id );
if ( !ok ) break;
QgsDebugMsgLevel( QString( "pid=%1" ).arg( id ), 3 );
feature.setFeatureId( id );
}
else if ( i == 1 && fetchGeometry )
{
// second column contains QKB geometry value
ok = stmt->getColumn( i, &geom );
if ( !ok ) break;
QgsDebugMsgLevel( QString( "retrieved geometry column" ), 3 );
geomBuf = new unsigned char[ *geom.length + 1 ];
memset( geomBuf, '\0', *geom.length );
memcpy( geomBuf, geom.buffer, *geom.length );
feature.setGeometryAndOwnership( geomBuf, *geom.length + 1 );
}
else
{
if ( i == 1 )
{
feature.setGeometryAndOwnership( 0, 0 ); // no geometry to fetch
}
int attrIndex = subsetAttributes ? mRequest.subsetOfAttributes()[colidx++] : colidx++;
QVariant val;
ok = stmt->getQVariant( i, val ); // ok may be false if value was NULL, but this is a valid return
// Sanity check before setting the attribute value
if ( colidx - 1 == i // First column is always pk, so colidx should be at least 1 behind
|| ( colidx - 1 == i - 1 && fetchGeometry ) // if fetchGeometry is true, colidx should be 2 behind
|| attrIndex >= numAttributes ) // index should always be less than the count
{
SaDebugMsg( QString( "Error retrieving feature column %1 with attribute index %2" ).arg( i ).arg( attrIndex ) );
return false;
}
// So now this should not crash.
feature.setAttribute( attrIndex, val );
}
}
feature.setValid( true );
return true;
}
// Accepts ownership of given connection pointer.
bool SaSourceSelect::getTableInfo( SqlAnyConnection *conn, bool searchOtherSchemas )
{
QString sql;
SqlAnyStatement *stmt;
int n = 0;
QApplication::setOverrideCursor( Qt::WaitCursor );
sql = "SELECT g.table_schema, g.table_name, g.column_name, "
"COALESCE( UCASE(g.geometry_type_name), 'ST_GEOMETRY' ), "
"g.srs_id, "
"IF s.round_earth = 'Y' THEN 'ROUND EARTH' ELSE 'PLANAR' ENDIF "
"FROM SYS.ST_GEOMETRY_COLUMNS g "
"LEFT OUTER JOIN SYS.ST_SPATIAL_REFERENCE_SYSTEMS s "
"ON g.srs_id = s.srs_id ";
if ( !searchOtherSchemas )
{
sql += QString( "WHERE g.table_schema=USER " );
}
stmt = conn->execute_direct( sql );
if ( stmt->isValid() )
{
for ( ; stmt->fetchNext() ; n++ )
{
QString schema;
QString tabname;
QString colname;
int srid = -1;
QString sridstr;
QString lineinterp;
QString geomtype;
bool waiting = false;
stmt->getString( 0, schema );
stmt->getString( 1, tabname );
stmt->getString( 2, colname );
stmt->getString( 3, geomtype );
stmt->getInt( 4, srid );
stmt->getString( 5, lineinterp );
if ( srid == -1 ) // null srid and lineinterp
{
sridstr = lineinterp = "WAITING";
waiting = true;
}
else
{
sridstr = QString::number( srid );
}
if ( geomtype == "ST_GEOMETRY" )
{
geomtype = "WAITING";
waiting = true;
}
if ( waiting )
{
addSearchGeometryColumn( schema, tabname, colname, geomtype, sridstr, lineinterp );
}
mTableModel.addTableEntry( geomtype, schema, tabname, sridstr, lineinterp, colname, "" );
}
}
else
{
SaDebugMsg( QString( "SQL Anywhere error %1: %2" )
.arg( stmt->errCode() )
.arg( stmt->errMsg() ) );
}
delete stmt;
conn->release();
QApplication::restoreOverrideCursor();
if ( n == 0 )
{
QMessageBox::warning( this,
tr( "No accessible tables found" ),
tr( "Database connection was successful, but no tables "
"containing geometry columns were %1." )
.arg( searchOtherSchemas ?
tr( "found" ) : tr( "found in your schema" ) )
);
}
return n > 0;
}
void SaSourceSelect::on_btnConnect_clicked()
{
if ( mColumnTypeThread )
{
mColumnTypeThread->stop();
mColumnTypeThread = 0;
}
QModelIndex rootItemIndex = mTableModel.indexFromItem( mTableModel.invisibleRootItem() );
mTableModel.removeRows( 0, mTableModel.rowCount( rootItemIndex ), rootItemIndex );
// populate the table list
QSettings settings;
// load the SQL Anywhere interface
if ( !SqlAnyConnection::initApi() )
{
QMessageBox::information( this,
tr( "Failed to load interface" ),
tr( SqlAnyConnection::failedInitMsg() ) );
return;
}
// compute connection information
QString key = "/SQLAnywhere/connections/" + cmbConnections->currentText();
mEstimateMetadata = settings.value( key + "/estimateMetadata", false ).toBool();
mOtherSchemas = settings.value( key + "/otherSchemas", false ).toBool();
mConnInfo = SqlAnyConnection::makeUri( key
, settings.value( key + "/host" ).toString()
, settings.value( key + "/port" ).toString()
, settings.value( key + "/server" ).toString()
, settings.value( key + "/database" ).toString()
, settings.value( key + "/parameters" ).toString()
, settings.value( key + "/username" ).toString()
, settings.value( key + "/password" ).toString()
, settings.value( key + "/simpleEncryption", false ).toBool()
, mEstimateMetadata );
SaDebugMsg( "Connection info: " + mConnInfo );
// 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 )
{
// get the list of suitable tables and columns and populate the UI
geomCol details;
if ( getTableInfo( conn->addRef(), mOtherSchemas ) )
{
// Start the thread that gets the geometry type for relations that
// may take a long time to return
if ( mColumnTypeThread != NULL )
{
connect( mColumnTypeThread, SIGNAL( setLayerType( QString, QString, QString, QString, QString, QString ) ),
this, SLOT( setLayerType( QString, QString, QString, QString, QString, QString ) ) );
// Do it in a thread.
mColumnTypeThread->start();
}
}
else
{
SaDebugMsg( "Unable to get list of spatially enabled tables "
"from the database" );
}
if ( cmbConnections->count() > 0 ) mAddButton->setEnabled( true );
conn->release();
}
else
{
QMessageBox::warning( this, tr( "Connection failed" ),
tr( "Connection to database %1 failed. "
"Check settings and try again.\n\n"
"SQL Anywhere error code: %2\n"
"Description: %3" )
.arg( settings.value( key + "/database" ).toString() )
.arg( code )
.arg( errbuf ) );
}
mTablesTreeView->sortByColumn( SaDbTableModel::dbtmTable, Qt::AscendingOrder );
mTablesTreeView->sortByColumn( SaDbTableModel::dbtmSchema, Qt::AscendingOrder );
//if we have only one schema item, expand it by default
int numTopLevelItems = mTableModel.invisibleRootItem()->rowCount();
if ( numTopLevelItems < 2 || mTableModel.tableCount() < 20 )
{
//expand all the toplevel items
for ( int i = 0; i < numTopLevelItems; ++i )
{
mTablesTreeView->expand( mProxyModel.mapFromSource( mTableModel.indexFromItem( mTableModel.invisibleRootItem()->child( i ) ) ) );
}
}
}
// Slot called when the menu item is triggered
void SqlAnywhere::addSqlAnywhereLayer()
{
QgsMapCanvas *mMapCanvas = mQGisIface->mapCanvas();
if ( mMapCanvas && mMapCanvas->isDrawing() )
{
return;
}
// show the data source dialog
SaSourceSelect *dbs = new SaSourceSelect( mQGisIface->mainWindow() );
mMapCanvas->freeze();
if ( dbs->exec() )
{
// add files to the map canvas
QStringList tables = dbs->selectedTables();
SaDebugMsg( "Selected tables:\n" + tables.join( "\n" ) + "\n\n" );
QApplication::setOverrideCursor( Qt::WaitCursor );
// retrieve database connection string
QString connectionInfo = dbs->connectionInfo();
// create a new map layer for each selected table and register it
for ( QStringList::Iterator it = tables.begin() ; it != tables.end() ; it++ )
{
// create the layer
SaDebugMsg( "Creating layer " + *it );
SaLayer *layer = new SaLayer( connectionInfo + " " + *it, *it );
if ( layer->isValid() )
{
// set initial layer name to table name
SaDebugMsg( "Beautifying layer name. old: " + layer->name() );
QgsDataSourceURI layerUri = QgsDataSourceURI( *it );
QString newName = QString( "%1 (%2)" )
.arg( layerUri.table() )
.arg( layerUri.geometryColumn() );
if ( QgsMapLayerRegistry::instance()->mapLayers().contains( newName ) )
{
newName = QString( "%1.%2 (%3)" )
.arg( layerUri.schema() )
.arg( layerUri.table() )
.arg( layerUri.geometryColumn() );
if ( QgsMapLayerRegistry::instance()->mapLayers().contains( newName ) )
{
// give up and revert to original name
newName = layer->name();
}
}
layer->setLayerName( newName );
SaDebugMsg( "Beautifying layer name. new: " + layer->name() );
// register this layer with the central layers registry
QgsMapLayerRegistry::instance()->addMapLayer(( QgsVectorLayer* )layer );
}
else
{
SaDebugMsg(( *it ) + " is an invalid layer - not loaded" );
QMessageBox::critical( mQGisIface->mainWindow(), tr( "Invalid Layer" ), tr( "%1 is an invalid layer and cannot be loaded." ).arg( *it ) );
delete layer;
}
}
QApplication::restoreOverrideCursor();
(( QMainWindow * ) mQGisIface->mainWindow() )->statusBar()->showMessage( mMapCanvas->extent().toString( 2 ) );
}
delete dbs;
// update UI
qApp->processEvents();
// draw the map
mMapCanvas->freeze( false );
mMapCanvas->refresh();
} // SqlAnywhere::addSqlAnywhereLayer()
