QgsSpatiaLiteConnection::Error QgsSpatiaLiteConnection::fetchTables( bool loadGeometrylessTables )
{
mErrorMsg = QString();
QFileInfo fi( mPath );
if ( !fi.exists() )
{
return NotExists;
}
sqlite3* handle = openSpatiaLiteDb( fi.canonicalFilePath() );
if ( handle == NULL )
{
return FailedToOpen;
}
checkHasMetadataTables( handle );
if ( !mErrorMsg.isNull() )
{
// unexpected error; invalid SpatiaLite DB
return FailedToCheckMetadata;
}
if ( !getTableInfo( handle, loadGeometrylessTables ) )
{
return FailedToGetTables;
}
closeSpatiaLiteDb( handle );
return NoError;
}
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();
}
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 );
}
QgsSpatiaLiteConnection::Error QgsSpatiaLiteConnection::fetchTables( bool loadGeometrylessTables )
{
mErrorMsg = QString();
QFileInfo fi( mPath );
if ( !fi.exists() )
return NotExists;
sqlite3 *handle = openSpatiaLiteDb( fi.canonicalFilePath() );
if ( !handle )
return FailedToOpen;
int ret = checkHasMetadataTables( handle );
if ( !mErrorMsg.isNull() || ret == LayoutUnknown )
{
// unexpected error; invalid SpatiaLite DB
return FailedToCheckMetadata;
}
bool recentVersion = false;
#ifdef SPATIALITE_VERSION_GE_4_0_0
// only if libspatialite version is >= 4.0.0
recentVersion = true;
#endif
if ( ret == LayoutCurrent && !recentVersion )
{
// obsolete library version
mErrorMsg = tr( "obsolete libspatialite: connecting to this DB requires using v.4.0 (or any subsequent)" );
return FailedToCheckMetadata;
}
#ifdef SPATIALITE_VERSION_GE_4_0_0
// only if libspatialite version is >= 4.0.0
// using v.4.0 Abstract Interface
if ( !getTableInfoAbstractInterface( handle, loadGeometrylessTables ) )
#else
// obsolete library: still using the traditional approach
if ( !getTableInfo( handle, loadGeometrylessTables ) )
#endif
{
return FailedToGetTables;
}
closeSpatiaLiteDb( handle );
return NoError;
}
bool QgsSpatiaLiteConnection::updateStatistics()
{
#ifdef SPATIALITE_VERSION_GE_4_0_0
QFileInfo fi( mPath );
if ( !fi.exists() )
return false;
sqlite3 *handle = openSpatiaLiteDb( fi.canonicalFilePath() );
if ( !handle )
return false;
bool ret = update_layer_statistics( handle, nullptr, nullptr );
closeSpatiaLiteDb( handle );
return ret;
#else
return false;
#endif
}
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;
}
