qgsspatialitesourceselect.cpp

/***************************************************************************
                             qgsspatialitesourceselect.cpp
       Dialog to select SpatiaLite layer(s) and add it to the map canvas
                              -------------------
begin                : Dec 2008
copyright            : (C) 2008 by Sandro Furieri
email                : a.furieri@lqt.it
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/

#include "qgsspatialitesourceselect.h"

#include "qgisapp.h"
#include "qgslogger.h"
#include "qgsapplication.h"
#include "qgscontexthelp.h"
#include "qgsquerybuilder.h"
#include "qgsdatasourceuri.h"
#include "qgsvectorlayer.h"

#include <QInputDialog>
#include <QMessageBox>
#include <QSettings>
#include <QTextStream>
#include <QTableWidgetItem>
#include <QHeaderView>
#include <QStringList>
#include <QPushButton>

#ifdef _MSC_VER
#define strcasecmp(a,b) stricmp(a,b)
#endif

QgsSpatiaLiteSourceSelect::QgsSpatiaLiteSourceSelect( QgisApp * app, Qt::WFlags fl ):
    QDialog( app, fl ), qgisApp( app )
{
  setupUi( this );
  setWindowTitle( tr( "Add SpatiaLite Table(s)" ) );
  connectionsGroupBox->setTitle( tr( "Databases" ) );
  btnEdit->hide();  // hide the edit button

  mAddButton = new QPushButton( tr( "&Add" ) );
  buttonBox->addButton( mAddButton, QDialogButtonBox::ActionRole );
  connect( mAddButton, SIGNAL( clicked() ), this, SLOT( addClicked() ) );
  mAddButton->setEnabled( false );

  mBuildQueryButton = new QPushButton( tr( "&Build Query" ) );
  buttonBox->addButton( mBuildQueryButton, QDialogButtonBox::ActionRole );
  connect( mBuildQueryButton, SIGNAL( clicked() ), this, SLOT( buildQuery() ) );
  mBuildQueryButton->setEnabled( false );

  populateConnectionList();

  mSearchModeComboBox->addItem( tr( "Wildcard" ) );
  mSearchModeComboBox->addItem( tr( "RegExp" ) );

  mSearchColumnComboBox->addItem( tr( "All" ) );
  mSearchColumnComboBox->addItem( tr( "Table" ) );
  mSearchColumnComboBox->addItem( tr( "Type" ) );
  mSearchColumnComboBox->addItem( tr( "Geometry column" ) );
  mSearchColumnComboBox->addItem( tr( "Sql" ) );

  mProxyModel.setParent( this );
  mProxyModel.setFilterKeyColumn( -1 );
  mProxyModel.setFilterCaseSensitivity( Qt::CaseInsensitive );
  mProxyModel.setDynamicSortFilter( true );
  mProxyModel.setSourceModel( &mTableModel );
  mTablesTreeView->setModel( &mProxyModel );
  mTablesTreeView->setSortingEnabled( true );

  //for Qt < 4.3.2, passing -1 to include all model columns
  //in search does not seem to work
  mSearchColumnComboBox->setCurrentIndex( 1 );

  //hide the search options by default
  //they will be shown when the user ticks
  //the search options group box
  mSearchLabel->setVisible( false );
  mSearchColumnComboBox->setVisible( false );
  mSearchColumnsLabel->setVisible( false );
  mSearchModeComboBox->setVisible( false );
  mSearchModeLabel->setVisible( false );
  mSearchTableEdit->setVisible( false );
  cbxAllowGeometrylessTables->setVisible( false );
}

// Slot for performing action when the Add button is clicked
void QgsSpatiaLiteSourceSelect::addClicked()
{
  addTables();
}

/** End Autoconnected SLOTS **/

// Remember which database is selected
void QgsSpatiaLiteSourceSelect::on_cmbConnections_activated( int )
{
  dbChanged();
}

void QgsSpatiaLiteSourceSelect::buildQuery()
{
  setSql( mTablesTreeView->currentIndex() );
}

void QgsSpatiaLiteSourceSelect::on_mTablesTreeView_clicked( const QModelIndex &index )
{
  mBuildQueryButton->setEnabled( index.parent().isValid() );
}

void QgsSpatiaLiteSourceSelect::on_mTablesTreeView_doubleClicked( const QModelIndex &index )
{
  setSql( index );
}

void QgsSpatiaLiteSourceSelect::on_mSearchTableEdit_textChanged( const QString & text )
{
  if ( mSearchModeComboBox->currentText() == tr( "Wildcard" ) )
  {
    mProxyModel._setFilterWildcard( text );
  }
  else if ( mSearchModeComboBox->currentText() == tr( "RegExp" ) )
  {
    mProxyModel._setFilterRegExp( text );
  }
}

void QgsSpatiaLiteSourceSelect::on_mSearchColumnComboBox_currentIndexChanged( const QString & text )
{
  if ( text == tr( "All" ) )
  {
    mProxyModel.setFilterKeyColumn( -1 );
  }
  else if ( text == tr( "Table" ) )
  {
    mProxyModel.setFilterKeyColumn( 0 );
  }
  else if ( text == tr( "Type" ) )
  {
    mProxyModel.setFilterKeyColumn( 1 );
  }
  else if ( text == tr( "Geometry column" ) )
  {
    mProxyModel.setFilterKeyColumn( 2 );
  }
  else if ( text == tr( "Sql" ) )
  {
    mProxyModel.setFilterKeyColumn( 3 );
  }
}

void QgsSpatiaLiteSourceSelect::on_mSearchModeComboBox_currentIndexChanged( const QString & text )
{
  on_mSearchTableEdit_textChanged( mSearchTableEdit->text() );
}

void QgsSpatiaLiteSourceSelect::setLayerType( QString table, QString column, QString type )
{
  mTableModel.setGeometryTypesForTable( table, column, type );
  mTablesTreeView->sortByColumn( 0, Qt::AscendingOrder );
}

sqlite3 *QgsSpatiaLiteSourceSelect::openSpatiaLiteDb( QString path )
{
  sqlite3 *handle = NULL;
  bool gcSpatiaLite = false;
  bool rsSpatiaLite = false;
  bool tableName = false;
  bool geomColumn = false;
  bool coordDims = false;
  bool gcSrid = false;
  bool type = false;
  bool spatialIndex = false;
  bool srsSrid = false;
  bool authName = false;
  bool authSrid = false;
  bool refSysName = false;
  bool proj4text = false;
  int ret;
  const char *name;
  int i;
  char **results;
  int rows;
  int columns;
  char *errMsg = NULL;
  QString errCause;

  // trying to open the SQLite DB
  mSqlitePath = path;

  ret = sqlite3_open_v2( path.toUtf8().constData(), &handle, SQLITE_OPEN_READWRITE, NULL );
  if ( ret )
  {
    // failure
    errCause = sqlite3_errmsg( handle );
    QMessageBox::critical( this, tr( "SpatiaLite DB Open Error" ),
                           tr( "Failure while connecting to: %1\n\n%2" ).arg( mSqlitePath ).arg( errCause ) );
    mSqlitePath = "";
    return NULL;
  }
  // checking if table GEOMETRY_COLUMNS exists and has the expected layout
  ret = sqlite3_get_table( handle, "PRAGMA table_info(geometry_columns)", &results, &rows, &columns, &errMsg );
  if ( ret != SQLITE_OK )
    goto error;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      name = results[( i * columns ) + 1];
      if ( strcasecmp( name, "f_table_name" ) == 0 )
        tableName = true;
      if ( strcasecmp( name, "f_geometry_column" ) == 0 )
        geomColumn = true;
      if ( strcasecmp( name, "coord_dimension" ) == 0 )
        coordDims = true;
      if ( strcasecmp( name, "srid" ) == 0 )
        gcSrid = true;
      if ( strcasecmp( name, "type" ) == 0 )
        type = true;
      if ( strcasecmp( name, "spatial_index_enabled" ) == 0 )
        spatialIndex = true;
    }
  }
  sqlite3_free_table( results );
  if ( tableName && geomColumn && type && coordDims && gcSrid && spatialIndex )
    gcSpatiaLite = true;

  // checking if table SPATIAL_REF_SYS exists and has the expected layout
  ret = sqlite3_get_table( handle, "PRAGMA table_info(spatial_ref_sys)", &results, &rows, &columns, &errMsg );
  if ( ret != SQLITE_OK )
    goto error;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      name = results[( i * columns ) + 1];
      if ( strcasecmp( name, "srid" ) == 0 )
        srsSrid = true;
      if ( strcasecmp( name, "auth_name" ) == 0 )
        authName = true;
      if ( strcasecmp( name, "auth_srid" ) == 0 )
        authSrid = true;
      if ( strcasecmp( name, "ref_sys_name" ) == 0 )
        refSysName = true;
      if ( strcasecmp( name, "proj4text" ) == 0 )
        proj4text = true;
    }
  }
  sqlite3_free_table( results );
  if ( srsSrid && authName && authSrid && refSysName && proj4text )
    rsSpatiaLite = true;

  // OK, this one seems to be a valid SpatiaLite DB
  if ( gcSpatiaLite && rsSpatiaLite )
    return handle;

  // this one cannot be a valid SpatiaLite DB - no Spatial MetaData where found
  closeSpatiaLiteDb( handle );
  errCause = tr( "seems to be a valid SQLite DB, but not a SpatiaLite's one ..." );
  QMessageBox::critical( this, tr( "SpatiaLite DB Open Error" ),
                         tr( "Failure while connecting to: %1\n\n%2" ).arg( mSqlitePath ).arg( errCause ) );
  mSqlitePath = "";
  return NULL;

error:
  // unexpected IO error
  closeSpatiaLiteDb( handle );
  errCause = tr( "unknown error cause" );
  if ( errMsg != NULL )
  {
    errCause = errMsg;
    sqlite3_free( errMsg );
  }
  QMessageBox::critical( this, tr( "SpatiaLite DB Open Error" ),
                         tr( "Failure while connecting to: %1\n\n%2" ).arg( mSqlitePath ).arg( errCause ) );
  mSqlitePath = "";
  return NULL;
}

void QgsSpatiaLiteSourceSelect::closeSpatiaLiteDb( sqlite3 * handle )
{
  if ( handle )
    sqlite3_close( handle );
}

void QgsSpatiaLiteSourceSelect::populateConnectionList()
{
  QSettings settings;
  settings.beginGroup( "/SpatiaLite/connections" );
  QStringList keys = settings.childGroups();
  QStringList::Iterator it = keys.begin();
  cmbConnections->clear();
  while ( it != keys.end() )
  {
    // retrieving the SQLite DB name and full path
    QString text = *it + tr( "@" );
    text += settings.value( *it + "/sqlitepath", "###unknown###" ).toString();
    cmbConnections->addItem( text );
    ++it;
  }
  settings.endGroup();
  setConnectionListPosition();

  btnConnect->setDisabled( cmbConnections->count() == 0 );
  btnDelete->setDisabled( cmbConnections->count() == 0 );

  cmbConnections->setDisabled( cmbConnections->count() == 0 );
}

void QgsSpatiaLiteSourceSelect::on_btnNew_clicked()
{
// Retrieve last used project dir from persistent settings
  sqlite3 *handle;
  QSettings settings;
  QString lastUsedDir = settings.value( "/UI/lastSpatiaLiteDir", "." ).toString();

  QString myFile = QFileDialog::getOpenFileName( this,
                   tr( "Choose a SpatiaLite/SQLite DB to open" ),
                   lastUsedDir, QObject::tr( "SQLite DB (*.sqlite *.db);;All files (*)" ) );

  if ( myFile.isEmpty() )
    return;

  QFileInfo myFI( myFile );
  QString myPath = myFI.path();
  QString myName = myFI.fileName();

  handle = openSpatiaLiteDb( myFI.canonicalFilePath() );
  if ( !handle )
    return;

  // OK, this one is a valid SpatiaLite DB
  closeSpatiaLiteDb( handle );

  // Persist last used SpatiaLite dir
  settings.setValue( "/UI/lastSpatiaLiteDir", myPath );
  // inserting this SQLite DB path
  QString baseKey = "/SpatiaLite/connections/";
  settings.setValue( baseKey + "selected", myName );
  baseKey += myName;
  settings.setValue( baseKey + "/sqlitepath", myFI.canonicalFilePath() );

  populateConnectionList();
}

QString QgsSpatiaLiteSourceSelect::layerURI( const QModelIndex &index )
{
  QString tableName = mTableModel.itemFromIndex( index.sibling( index.row(), 0 ) )->text();
  QString geomColumnName = mTableModel.itemFromIndex( index.sibling( index.row(), 2 ) )->text();
  QString sql = mTableModel.itemFromIndex( index.sibling( index.row(), 3 ) )->text();

  if ( geomColumnName.contains( " AS " ) )
  {
    int a = geomColumnName.indexOf( " AS " );
    QString typeName = geomColumnName.mid( a + 4 ); //only the type name
    geomColumnName = geomColumnName.left( a ); //only the geom column name
    QString geomFilter;

    if ( typeName == "POINT" )
    {
      geomFilter = QString( "geometrytype(\"%1\") IN ('POINT','MULTIPOINT')" ).arg( geomColumnName );
    }
    else if ( typeName == "LINESTRING" )
    {
      geomFilter = QString( "geometrytype(\"%1\") IN ('LINESTRING','MULTILINESTRING')" ).arg( geomColumnName );
    }
    else if ( typeName == "POLYGON" )
    {
      geomFilter = QString( "geometrytype(\"%1\") IN ('POLYGON','MULTIPOLYGON')" ).arg( geomColumnName );
    }

    if ( !geomFilter.isEmpty() && !sql.contains( geomFilter ) )
    {
      if ( !sql.isEmpty() )
      {
        sql += " AND ";
      }

      sql += geomFilter;
    }
  }

  QgsDataSourceURI uri( connectionInfo() );
  uri.setDataSource( "", tableName, geomColumnName, sql, "" );
  return uri.uri();
}

// Slot for deleting an existing connection
void QgsSpatiaLiteSourceSelect::on_btnDelete_clicked()
{
  QSettings settings;
  QString subKey = cmbConnections->currentText();
  int idx = subKey.indexOf( "@" );
  if ( idx > 0 )
    subKey.truncate( idx );

  QString key = "/SpatiaLite/connections/" + subKey;
  QString msg = tr( "Are you sure you want to remove the %1 connection and all associated settings?" ).arg( subKey );
  QMessageBox::StandardButton result =
    QMessageBox::information( this, tr( "Confirm Delete" ), msg, QMessageBox::Ok | QMessageBox::Cancel );
  if ( result != QMessageBox::Ok )
    return;

  settings.remove( key + "/sqlitepath" );
  settings.remove( key );

  populateConnectionList();
}

void QgsSpatiaLiteSourceSelect::addTables()
{
  m_selectedTables.clear();

  typedef QMap < int, bool >schemaInfo;
  QMap < QString, schemaInfo > dbInfo;

  QItemSelection selection = mTablesTreeView->selectionModel()->selection();
  QModelIndexList selectedIndices = selection.indexes();
  QStandardItem *currentItem = 0;

  QModelIndexList::const_iterator selected_it = selectedIndices.constBegin();
  for ( ; selected_it != selectedIndices.constEnd(); ++selected_it )
  {
    if ( !selected_it->parent().isValid() )
    {
      //top level items only contain the schema names
      continue;
    }
    currentItem = mTableModel.itemFromIndex( mProxyModel.mapToSource( *selected_it ) );
    if ( !currentItem )
    {
      continue;
    }

    QString currentSchemaName = currentItem->parent()->text();

    int currentRow = currentItem->row();
    if ( !dbInfo[currentSchemaName].contains( currentRow ) )
    {
      dbInfo[currentSchemaName][currentRow] = true;
      m_selectedTables << layerURI( mProxyModel.mapToSource( *selected_it ) );
    }
  }

  if ( m_selectedTables.empty() )
  {
    QMessageBox::information( this, tr( "Select Table" ), tr( "You must select a table in order to add a Layer." ) );
  }
  else
  {
    accept();
  }
}

void QgsSpatiaLiteSourceSelect::on_btnConnect_clicked()
{
  sqlite3 *handle;

  QSettings settings;
  QString subKey = cmbConnections->currentText();
  int idx = subKey.indexOf( "@" );
  if ( idx > 0 )
    subKey.truncate( idx );

  QFileInfo fi( settings.value( QString( "/SpatiaLite/connections/%1/sqlitepath" ).arg( subKey ) ).toString() );

  if ( !fi.exists() )
    // db doesn't exists
    return;

  // trying to connect to SpatiaLite DB
  handle = openSpatiaLiteDb( fi.canonicalFilePath() );
  if ( handle == NULL )
  {
    // unexpected error; invalid SpatiaLite DB
    return;
  }

  QModelIndex rootItemIndex = mTableModel.indexFromItem( mTableModel.invisibleRootItem() );
  mTableModel.removeRows( 0, mTableModel.rowCount( rootItemIndex ), rootItemIndex );

  // populate the table list
  // get the list of suitable tables and columns and populate the UI
  geomCol details;

  if ( !getTableInfo( handle ) )
  {
    QgsDebugMsg( QString( "Unable to get list of spatially enabled tables from the database\n%1" ).arg( sqlite3_errmsg( handle ) ) );
  }
  closeSpatiaLiteDb( handle );

  if ( cmbConnections->count() > 0 )
    mAddButton->setEnabled( true );

  mTablesTreeView->sortByColumn( 0, Qt::AscendingOrder );

  //expand all the toplevel items
  int numTopLevelItems = mTableModel.invisibleRootItem()->rowCount();
  for ( int i = 0; i < numTopLevelItems; ++i )
  {
    mTablesTreeView->expand( mProxyModel.mapFromSource( mTableModel.indexFromItem( mTableModel.invisibleRootItem()->child( i ) ) ) );
  }
  mTablesTreeView->resizeColumnToContents( 0 );
  mTablesTreeView->resizeColumnToContents( 1 );
}

QStringList QgsSpatiaLiteSourceSelect::selectedTables()
{
  return m_selectedTables;
}

QString QgsSpatiaLiteSourceSelect::connectionInfo()
{
  return QString( "dbname='%1'" ).arg( QString( mSqlitePath ).replace( "'", "\\'" ) );
}

void QgsSpatiaLiteSourceSelect::setSql( const QModelIndex &index )
{
  QModelIndex idx = mProxyModel.mapToSource( index );
  QString tableName = mTableModel.itemFromIndex( idx.sibling( idx.row(), 0 ) )->text();

  QgsVectorLayer *vlayer = new QgsVectorLayer( layerURI( idx ), tableName, "spatialite" );

  if ( !vlayer->isValid() )
  {
    delete vlayer;
    return;
  }

  // create a query builder object
  QgsQueryBuilder *gb = new QgsQueryBuilder( vlayer, this );
  if ( gb->exec() )
  {
    mTableModel.setSql( mProxyModel.mapToSource( index ), gb->sql() );
  }

  delete gb;
  delete vlayer;
}

bool QgsSpatiaLiteSourceSelect::getTableInfo( sqlite3 * handle )
{
  int ret;
  int i;
  char **results;
  int rows;
  int columns;
  char *errMsg = NULL;
  bool ok = false;
  char sql[1024];
  QApplication::setOverrideCursor( Qt::WaitCursor );

  // setting the SQLite DB name
  QFileInfo myFI( mSqlitePath );
  QString myName = myFI.fileName();
  mTableModel.setSqliteDb( myName );

  // the following query return the tables containing a Geometry column
  strcpy( sql, "SELECT f_table_name, f_geometry_column, type " );
  strcat( sql, "FROM geometry_columns" );
  ret = sqlite3_get_table( handle, sql, &results, &rows, &columns, &errMsg );
  if ( ret != SQLITE_OK )
    goto error;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      if ( isRasterlite1Datasource( handle, results[( i * columns ) + 0] ) )
        continue;
      QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
      QString column = QString::fromUtf8( results[( i * columns ) + 1] );
      QString type = results[( i * columns ) + 2];
      if ( isDeclaredHidden( handle, tableName, column ) )
        continue;

      mTableModel.addTableEntry( type, tableName, column, "" );
    }
    ok = true;
  }
  sqlite3_free_table( results );

  if ( checkViewsGeometryColumns( handle ) )
  {
    // the following query return the views supporting a Geometry column
    strcpy( sql, "SELECT view_name, view_geometry, type " );
    strcat( sql, "FROM views_geometry_columns " );
    strcat( sql, "JOIN geometry_columns USING (f_table_name, f_geometry_column)" );
    ret = sqlite3_get_table( handle, sql, &results, &rows, &columns, &errMsg );
    if ( ret != SQLITE_OK )
      goto error;
    if ( rows < 1 )
      ;
    else
    {
      for ( i = 1; i <= rows; i++ )
      {
        QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
        QString column = QString::fromUtf8( results[( i * columns ) + 1] );
        QString type = results[( i * columns ) + 2];
        if ( isDeclaredHidden( handle, tableName, column ) )
          continue;

        mTableModel.addTableEntry( type, tableName, column, "" );
      }
      ok = true;
    }
    sqlite3_free_table( results );
  }

  if ( checkVirtsGeometryColumns( handle ) )
  {
    // the following query return the VirtualShapefiles
    strcpy( sql, "SELECT virt_name, virt_geometry, type " );
    strcat( sql, "FROM virts_geometry_columns" );
    ret = sqlite3_get_table( handle, sql, &results, &rows, &columns, &errMsg );
    if ( ret != SQLITE_OK )
      goto error;
    if ( rows < 1 )
      ;
    else
    {
      for ( i = 1; i <= rows; i++ )
      {
        QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
        QString column = QString::fromUtf8( results[( i * columns ) + 1] );
        QString type = results[( i * columns ) + 2];
        if ( isDeclaredHidden( handle, tableName, column ) )
          continue;

        mTableModel.addTableEntry( type, tableName, column, "" );
      }
      ok = true;
    }
    sqlite3_free_table( results );
  }

  QApplication::restoreOverrideCursor();
  return ok;

error:
  // unexpected IO error
  QString errCause = tr( "unknown error cause" );
  if ( errMsg != NULL )
  {
    errCause = errMsg;
    sqlite3_free( errMsg );
  }
  QMessageBox::critical( this, tr( "SpatiaLite getTableInfo Error" ),
                         tr( "Failure exploring tables from: %1\n\n%2" ).arg( mSqlitePath ).arg( errCause ) );
  return false;
}

QString QgsSpatiaLiteSourceSelect::quotedValue( QString value ) const
{
  if ( value.isNull() )
    return "NULL";

  value.replace( "'", "''" );
  return value.prepend( "'" ).append( "'" );
}

bool QgsSpatiaLiteSourceSelect::checkGeometryColumnsAuth( sqlite3 * handle )
{
  int ret;
  int i;
  char **results;
  int rows;
  int columns;
  bool exists = false;

  // checking the metadata tables
  QString sql = QString( "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'geometry_columns_auth'" );

  ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, NULL );
  if ( ret != SQLITE_OK )
    return false;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      if ( results[( i * columns ) + 0] != NULL )
      {
        const char *name = results[( i * columns ) + 0];
        if ( name )
          exists = true;
      }
    }
  }
  sqlite3_free_table( results );
  return exists;
}

bool QgsSpatiaLiteSourceSelect::checkViewsGeometryColumns( sqlite3 * handle )
{
  int ret;
  int i;
  char **results;
  int rows;
  int columns;
  bool exists = false;

  // checking the metadata tables
  QString sql = QString( "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'views_geometry_columns'" );

  ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, NULL );
  if ( ret != SQLITE_OK )
    return false;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      if ( results[( i * columns ) + 0] != NULL )
      {
        const char *name = results[( i * columns ) + 0];
        if ( name )
          exists = true;
      }
    }
  }
  sqlite3_free_table( results );
  return exists;
}

bool QgsSpatiaLiteSourceSelect::checkVirtsGeometryColumns( sqlite3 * handle )
{
  int ret;
  int i;
  char **results;
  int rows;
  int columns;
  bool exists = false;

  // checking the metadata tables
  QString sql = QString( "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'virts_geometry_columns'" );

  ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, NULL );
  if ( ret != SQLITE_OK )
    return false;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      if ( results[( i * columns ) + 0] != NULL )
      {
        const char *name = results[( i * columns ) + 0];
        if ( name )
          exists = true;
      }
    }
  }
  sqlite3_free_table( results );
  return exists;
}

bool QgsSpatiaLiteSourceSelect::isRasterlite1Datasource( sqlite3 * handle, const char *table )
{
// testing for RasterLite-1 datasources
  int ret;
  int i;
  char **results;
  int rows;
  int columns;
  bool exists = false;
  int len;
  char table_raster[4192];
  char sql[4192];

  strcpy( table_raster, table );
  len =  strlen( table_raster );
  if ( strlen( table_raster ) < 9 )
    return false;
  if ( strcmp( table_raster + len - 9, "_metadata" ) != 0 )
    return false;
  // ok, possible candidate
  strcpy( table_raster + len - 9, "_rasters" );

  // checking if the related "_RASTERS table exists
  sprintf( sql, "SELECT name FROM sqlite_master WHERE type = 'table' AND name = '%s'", table_raster );

  ret = sqlite3_get_table( handle, sql, &results, &rows, &columns, NULL );
  if ( ret != SQLITE_OK )
    return false;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      if ( results[( i * columns ) + 0] != NULL )
      {
        const char *name = results[( i * columns ) + 0];
        if ( name )
          exists = true;
      }
    }
  }
  sqlite3_free_table( results );
  return exists;
}

bool QgsSpatiaLiteSourceSelect::isDeclaredHidden( sqlite3 * handle, QString table, QString geom )
{
  int ret;
  int i;
  char **results;
  int rows;
  int columns;
  char *errMsg = NULL;
  bool isHidden = false;

  if ( !checkGeometryColumnsAuth( handle ) )
    return false;
  // checking if some Layer has been declared as HIDDEN
  QString sql = QString( "SELECT hidden FROM geometry_columns_auth"
                         " WHERE f_table_name=%1 and f_geometry_column=%2" ).arg( quotedValue( table ) ).
                arg( quotedValue( geom ) );

  ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
  if ( ret != SQLITE_OK )
    goto error;
  if ( rows < 1 )
    ;
  else
  {
    for ( i = 1; i <= rows; i++ )
    {
      if ( results[( i * columns ) + 0] != NULL )
      {
        if ( atoi( results[( i * columns ) + 0] ) != 0 )
          isHidden = true;
      }
    }
  }
  sqlite3_free_table( results );

  return isHidden;

error:
  // unexpected IO error
  QString errCause = tr( "unknown error cause" );
  if ( errMsg != NULL )
  {
    errCause = errMsg;
    sqlite3_free( errMsg );
  }
  QMessageBox::critical( this, tr( "SpatiaLite getTableInfo Error" ),
                         tr( "Failure exploring tables from: %1\n\n%2" ).arg( mSqlitePath ).arg( errCause ) );
  return false;
}

QString QgsSpatiaLiteSourceSelect::fullDescription( QString table, QString column, QString type )
{
  QString full_desc = "";
  full_desc += table + "\" (" + column + ") " + type;
  return full_desc;
}

void QgsSpatiaLiteSourceSelect::dbChanged()
{
  // Remember which database was selected.
  QSettings settings;
  settings.setValue( "/SpatiaLite/connections/selected", cmbConnections->currentText() );
}

void QgsSpatiaLiteSourceSelect::setConnectionListPosition()
{
  QSettings settings;
  // If possible, set the item currently displayed database
  QString toSelect = settings.value( "/SpatiaLite/connections/selected" ).toString();
  // Does toSelect exist in cmbConnections?
  bool set = false;
  for ( int i = 0; i < cmbConnections->count(); ++i )
    if ( cmbConnections->itemText( i ) == toSelect )
    {
      cmbConnections->setCurrentIndex( i );
      set = true;
      break;
    }
  // If we couldn't find the stored item, but there are some,
  // default to the last item (this makes some sense when deleting
  // items as it allows the user to repeatidly click on delete to
  // remove a whole lot of items).
  if ( !set && cmbConnections->count() > 0 )
  {
    // If toSelect is null, then the selected connection wasn't found
    // by QSettings, which probably means that this is the first time
    // the user has used qgis with database connections, so default to
    // the first in the list of connetions. Otherwise default to the last.
    if ( toSelect.isNull() )
      cmbConnections->setCurrentIndex( 0 );
    else
      cmbConnections->setCurrentIndex( cmbConnections->count() - 1 );
  }
}

void QgsSpatiaLiteSourceSelect::setSearchExpression( const QString & regexp )
{
}