Skip to content

Commit 47097b7

Browse files
brushtylerbrushtyler
committedNov 30, 2012
[FEATURE] update SL provider to use statistics (available from spatialite v4.0).
Patch by Alessandro Furieri.
·
ltr-3_40Before-merge-new_vector_api
1 parent 85faeb3 commit 47097b7

8 files changed

+611
-24
lines changed
 

‎cmake/FindSPATIALITE.cmake

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,9 @@
1111
# SPATIALITE_INCLUDE_DIR
1212
# SPATIALITE_LIBRARY
1313

14+
# This macro checks if the symbol exists
15+
include(CheckLibraryExists)
16+
1417

1518
# FIND_PATH and FIND_LIBRARY normally search standard locations
1619
# before the specified paths. To search non-standard paths first,
@@ -60,6 +63,9 @@ IF (SPATIALITE_FOUND)
6063
MESSAGE(STATUS "Found SpatiaLite: ${SPATIALITE_LIBRARY}")
6164
ENDIF (NOT SPATIALITE_FIND_QUIETLY)
6265

66+
# Check for symbol gaiaDropTable
67+
check_library_exists("${SPATIALITE_LIBRARY}" gaiaDropTable "" SPATIALITE_RECENT_VERSION)
68+
6369
ELSE (SPATIALITE_FOUND)
6470

6571
IF (SPATIALITE_FIND_REQUIRED)

‎src/providers/spatialite/CMakeLists.txt

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,10 @@ SET(SPATIALITE_MOC_HDRS
2424

2525
QT4_WRAP_CPP(SPATIALITE_MOC_SRCS ${SPATIALITE_MOC_HDRS})
2626

27+
IF(SPATIALITE_RECENT_VERSION)
28+
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS}-DSPATIALITE_RECENT_VERSION")
29+
ENDIF(SPATIALITE_RECENT_VERSION)
30+
2731

2832
INCLUDE_DIRECTORIES(
2933
../../core

‎src/providers/spatialite/qgsspatialiteconnection.cpp

Lines changed: 188 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -71,13 +71,38 @@ QgsSpatiaLiteConnection::Error QgsSpatiaLiteConnection::fetchTables( bool loadGe
7171
return FailedToOpen;
7272
}
7373

74-
checkHasMetadataTables( handle );
75-
if ( !mErrorMsg.isNull() )
74+
int ret = checkHasMetadataTables( handle );
75+
if ( !mErrorMsg.isNull() || ret == LayoutUnknown )
7676
{
7777
// unexpected error; invalid SpatiaLite DB
7878
return FailedToCheckMetadata;
7979
}
8080

81+
bool recentVersion = false;
82+
#ifdef SPATIALITE_RECENT_VERSION
83+
// only if libspatialite version is >= 4.0.0
84+
recentVersion = true;
85+
#endif
86+
87+
if ( ret == LayoutCurrent && recentVersion == false )
88+
{
89+
// obsolete library version
90+
mErrorMsg = tr( "obsolete libspatialite: connecting to this DB requires using v.4.0 (or any subsequent)" );
91+
return FailedToCheckMetadata;
92+
}
93+
94+
#ifdef SPATIALITE_RECENT_VERSION
95+
// only if libspatialite version is >= 4.0.0
96+
// using v.4.0 Abstract Interface
97+
if (!getTableInfoAbstractInterface( handle, loadGeometrylessTables ) )
98+
{
99+
return FailedToGetTables;
100+
}
101+
closeSpatiaLiteDb( handle );
102+
return NoError;
103+
#endif
104+
105+
// obsolete library: still using the traditional approach
81106
if ( !getTableInfo( handle, loadGeometrylessTables ) )
82107
{
83108
return FailedToGetTables;
@@ -87,11 +112,40 @@ QgsSpatiaLiteConnection::Error QgsSpatiaLiteConnection::fetchTables( bool loadGe
87112
return NoError;
88113
}
89114

115+
bool QgsSpatiaLiteConnection::updateStatistics()
116+
{
117+
QFileInfo fi( mPath );
118+
if ( !fi.exists() )
119+
{
120+
return false;
121+
}
122+
123+
sqlite3* handle = openSpatiaLiteDb( fi.canonicalFilePath() );
124+
if ( handle == NULL )
125+
{
126+
return false;
127+
}
128+
129+
// checking the library version
130+
bool recentVersion = false;
131+
const char *version = spatialite_version();
132+
if ( isdigit(*version) && *version >= '4' )
133+
recentVersion = true;
134+
135+
bool ret = update_layer_statistics ( handle, NULL, NULL );
136+
137+
closeSpatiaLiteDb( handle );
138+
139+
return ret;
140+
}
90141

91142
sqlite3 *QgsSpatiaLiteConnection::openSpatiaLiteDb( QString path )
92143
{
93144
sqlite3 *handle = NULL;
94145
int ret;
146+
// activating the SpatiaLite library
147+
spatialite_init(0);
148+
95149
// trying to open the SQLite DB
96150
ret = sqlite3_open_v2( path.toUtf8().constData(), &handle, SQLITE_OPEN_READWRITE, NULL );
97151
if ( ret )
@@ -109,21 +163,25 @@ void QgsSpatiaLiteConnection::closeSpatiaLiteDb( sqlite3 * handle )
109163
sqlite3_close( handle );
110164
}
111165

112-
bool QgsSpatiaLiteConnection::checkHasMetadataTables( sqlite3* handle )
166+
int QgsSpatiaLiteConnection::checkHasMetadataTables( sqlite3* handle )
113167
{
114168
bool gcSpatiaLite = false;
115169
bool rsSpatiaLite = false;
170+
bool gcSpatiaLite4 = false;
171+
bool rsSpatiaLite4 = false;
116172
bool tableName = false;
117173
bool geomColumn = false;
118174
bool coordDims = false;
119175
bool gcSrid = false;
120176
bool type = false;
177+
bool geometry_type = false;
121178
bool spatialIndex = false;
122179
bool srsSrid = false;
123180
bool authName = false;
124181
bool authSrid = false;
125182
bool refSysName = false;
126183
bool proj4text = false;
184+
bool srtext = false;
127185
int ret;
128186
const char *name;
129187
int i;
@@ -153,13 +211,17 @@ bool QgsSpatiaLiteConnection::checkHasMetadataTables( sqlite3* handle )
153211
gcSrid = true;
154212
if ( strcasecmp( name, "type" ) == 0 )
155213
type = true;
214+
if ( strcasecmp( name, "geometry_type" ) == 0 )
215+
geometry_type = true;
156216
if ( strcasecmp( name, "spatial_index_enabled" ) == 0 )
157217
spatialIndex = true;
158218
}
159219
}
160220
sqlite3_free_table( results );
161221
if ( tableName && geomColumn && type && coordDims && gcSrid && spatialIndex )
162222
gcSpatiaLite = true;
223+
if ( tableName && geomColumn && geometry_type && coordDims && gcSrid && spatialIndex )
224+
gcSpatiaLite4 = true;
163225

164226
// checking if table SPATIAL_REF_SYS exists and has the expected layout
165227
ret = sqlite3_get_table( handle, "PRAGMA table_info(spatial_ref_sys)", &results, &rows, &columns, &errMsg );
@@ -182,18 +244,139 @@ bool QgsSpatiaLiteConnection::checkHasMetadataTables( sqlite3* handle )
182244
refSysName = true;
183245
if ( strcasecmp( name, "proj4text" ) == 0 )
184246
proj4text = true;
247+
if ( strcasecmp( name, "srtext" ) == 0 )
248+
srtext = true;
185249
}
186250
}
187251
sqlite3_free_table( results );
188252
if ( srsSrid && authName && authSrid && refSysName && proj4text )
189253
rsSpatiaLite = true;
254+
if ( srsSrid && authName && authSrid && refSysName && proj4text )
255+
rsSpatiaLite4 = true;
190256

191257
// OK, this one seems to be a valid SpatiaLite DB
258+
if ( gcSpatiaLite4 && rsSpatiaLite4 )
259+
return LayoutCurrent;
192260
if ( gcSpatiaLite && rsSpatiaLite )
193-
return true;
261+
return LayoutLegacy;
194262

195263
// this seems to be a valid SQLite DB, but not a SpatiaLite's one
264+
return LayoutUnknown;
265+
266+
error:
267+
// unexpected IO error
268+
mErrorMsg = tr( "unknown error cause" );
269+
if ( errMsg != NULL )
270+
{
271+
mErrorMsg = errMsg;
272+
sqlite3_free( errMsg );
273+
}
196274
return false;
275+
}
276+
277+
#ifdef SPATIALITE_RECENT_VERSION
278+
// only if libspatialite version is >= 4.0.0
279+
bool QgsSpatiaLiteConnection::getTableInfoAbstractInterface( sqlite3 * handle, bool loadGeometrylessTables )
280+
{
281+
int ret;
282+
int i;
283+
char **results;
284+
int rows;
285+
int columns;
286+
char *errMsg = NULL;
287+
bool ok = false;
288+
QString sql;
289+
gaiaVectorLayersListPtr list;
290+
291+
const char *version = spatialite_version();
292+
if ( isdigit(*version) && *version >= '4' )
293+
; // OK, linked against libspatialite v.4.0 (or any subsequent)
294+
else
295+
{
296+
mErrorMsg = tr( "obsolete libspatialite: AbstractInterface is unsupported" );
297+
return false;
298+
}
299+
300+
// attempting to load the VectorLayersList
301+
list = gaiaGetVectorLayersList (handle, NULL, NULL, GAIA_VECTORS_LIST_FAST);
302+
if (list != NULL)
303+
{
304+
gaiaVectorLayerPtr lyr = list->First;
305+
while ( lyr != NULL )
306+
{
307+
// populating the QGIS own Layers List
308+
if (lyr->AuthInfos)
309+
{
310+
if ( lyr->AuthInfos->IsHidden )
311+
{
312+
// skipping any Hidden layer
313+
lyr = lyr->Next;
314+
continue;
315+
}
316+
}
317+
318+
QString tableName = QString::fromUtf8( lyr->TableName );
319+
QString column = QString::fromUtf8( lyr->GeometryName );
320+
QString type = tr( "UNKNOWN" );
321+
switch (lyr->GeometryType)
322+
{
323+
case GAIA_VECTOR_GEOMETRY:
324+
type = tr( "GEOMETRY" );
325+
break;
326+
case GAIA_VECTOR_POINT:
327+
type = tr( "POINT" );
328+
break;
329+
case GAIA_VECTOR_LINESTRING:
330+
type = tr( "LINESTRING" );
331+
break;
332+
case GAIA_VECTOR_POLYGON:
333+
type = tr( "POLYGON" );
334+
break;
335+
case GAIA_VECTOR_MULTIPOINT:
336+
type = tr( "MULTIPOINT" );
337+
break;
338+
case GAIA_VECTOR_MULTILINESTRING:
339+
type = tr( "MULTILINESTRING" );
340+
break;
341+
case GAIA_VECTOR_MULTIPOLYGON:
342+
type = tr( "MULTIPOLYGON" );
343+
break;
344+
case GAIA_VECTOR_GEOMETRYCOLLECTION:
345+
type = tr( "GEOMETRYCOLLECTION" );
346+
break;
347+
};
348+
mTables.append( TableEntry( tableName, column, type ) );
349+
ok = true;
350+
351+
lyr = lyr->Next;
352+
}
353+
gaiaFreeVectorLayersList (list);
354+
}
355+
356+
if ( loadGeometrylessTables )
357+
{
358+
// get all tables
359+
sql = "SELECT name "
360+
"FROM sqlite_master "
361+
"WHERE type in ('table', 'view')";
362+
ret = sqlite3_get_table( handle, sql.toUtf8(), &results, &rows, &columns, &errMsg );
363+
if ( ret != SQLITE_OK )
364+
goto error;
365+
if ( rows < 1 )
366+
;
367+
else
368+
{
369+
for ( i = 1; i <= rows; i++ )
370+
{
371+
QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
372+
mTables.append( TableEntry( tableName, QString(), "qgis_table" ) );
373+
}
374+
ok = true;
375+
}
376+
sqlite3_free_table( results );
377+
}
378+
379+
return ok;
197380

198381
error:
199382
// unexpected IO error
@@ -205,6 +388,7 @@ bool QgsSpatiaLiteConnection::checkHasMetadataTables( sqlite3* handle )
205388
}
206389
return false;
207390
}
391+
#endif
208392

209393
bool QgsSpatiaLiteConnection::getTableInfo( sqlite3 * handle, bool loadGeometrylessTables )
210394
{

‎src/providers/spatialite/qgsspatialiteconnection.h

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,8 @@
2121
extern "C"
2222
{
2323
#include <sqlite3.h>
24+
#include <spatialite/gaiageo.h>
25+
#include <spatialite.h>
2426
}
2527

2628
class QgsSpatiaLiteConnection : public QObject
@@ -54,6 +56,13 @@ class QgsSpatiaLiteConnection : public QObject
5456
FailedToGetTables,
5557
};
5658

59+
enum DbLayoutVersion
60+
{
61+
LayoutUnknown,
62+
LayoutLegacy,
63+
LayoutCurrent,
64+
};
65+
5766
Error fetchTables( bool loadGeometrylessTables );
5867

5968
/** return list of tables. fetchTables() function has to be called before */
@@ -62,17 +71,34 @@ class QgsSpatiaLiteConnection : public QObject
6271
/** return additional error message (if an error occurred before) */
6372
QString errorMessage() { return mErrorMsg; }
6473

74+
/**Updates the Internal Statistics*/
75+
bool updateStatistics();
76+
6577
protected:
6678
// SpatiaLite DB open / close
6779
sqlite3 *openSpatiaLiteDb( QString path );
6880
void closeSpatiaLiteDb( sqlite3 * handle );
6981

7082
/**Checks if geometry_columns and spatial_ref_sys exist and have expected layout*/
71-
bool checkHasMetadataTables( sqlite3* handle );
83+
int checkHasMetadataTables( sqlite3* handle );
7284

7385
/**Inserts information about the spatial tables into mTables*/
7486
bool getTableInfo( sqlite3 * handle, bool loadGeometrylessTables );
7587

88+
#ifdef SPATIALITE_RECENT_VERSION
89+
// only if libspatialite version is >= 4.0.0
90+
/**
91+
Inserts information about the spatial tables into mTables
92+
please note: this method is fully based on the Abstract Interface
93+
implemented in libspatialite starting since v.4.0
94+
95+
using the Abstract Interface is highly reccommended, because all
96+
version-dependent implementation details become completly transparent,
97+
thus completely freeing the client application to take care of them.
98+
*/
99+
bool getTableInfoAbstractInterface( sqlite3 * handle, bool loadGeometrylessTables );
100+
#endif
101+
76102
/**cleaning well-formatted SQL strings*/
77103
QString quotedValue( QString value ) const;
78104

‎src/providers/spatialite/qgsspatialiteprovider.cpp

Lines changed: 339 additions & 19 deletions
Large diffs are not rendered by default.

‎src/providers/spatialite/qgsspatialiteprovider.h

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -409,6 +409,14 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
409409
bool getQueryGeometryDetails();
410410
bool getSridDetails();
411411
bool getTableSummary();
412+
#ifdef SPATIALITE_RECENT_VERSION
413+
// only if libspatialite version is >= 4.0.0
414+
bool checkLayerTypeAbstractInterface( gaiaVectorLayerPtr lyr );
415+
bool getGeometryDetailsAbstractInterface( gaiaVectorLayerPtr lyr );
416+
bool getTableSummaryAbstractInterface( gaiaVectorLayerPtr lyr );
417+
void loadFieldsAbstractInterface( gaiaVectorLayerPtr lyr );
418+
void getViewSpatialIndexName();
419+
#endif
412420
bool prepareStatement( sqlite3_stmt *&stmt,
413421
const QgsAttributeList &fetchAttributes,
414422
bool fetchGeometry,

‎src/providers/spatialite/qgsspatialitesourceselect.cpp

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,10 @@ QgsSpatiaLiteSourceSelect::QgsSpatiaLiteSourceSelect( QWidget * parent, Qt::WFla
5353
btnSave->hide();
5454
btnLoad->hide();
5555

56+
mStatsButton = new QPushButton( tr( "&Update statistics" ) );
57+
connect( mStatsButton, SIGNAL( clicked() ), this, SLOT( updateStatistics() ) );
58+
mStatsButton->setEnabled( false );
59+
5660
mAddButton = new QPushButton( tr( "&Add" ) );
5761
connect( mAddButton, SIGNAL( clicked() ), this, SLOT( addClicked() ) );
5862
mAddButton->setEnabled( false );
@@ -69,6 +73,7 @@ QgsSpatiaLiteSourceSelect::QgsSpatiaLiteSourceSelect( QWidget * parent, Qt::WFla
6973
{
7074
buttonBox->addButton( mAddButton, QDialogButtonBox::ActionRole );
7175
buttonBox->addButton( mBuildQueryButton, QDialogButtonBox::ActionRole );
76+
buttonBox->addButton( mStatsButton, QDialogButtonBox::ActionRole );
7277
}
7378

7479
populateConnectionList();
@@ -132,6 +137,35 @@ void QgsSpatiaLiteSourceSelect::buildQuery()
132137
setSql( mTablesTreeView->currentIndex() );
133138
}
134139

140+
void QgsSpatiaLiteSourceSelect::updateStatistics()
141+
{
142+
QString subKey = cmbConnections->currentText();
143+
int idx = subKey.indexOf( "@" );
144+
if ( idx > 0 )
145+
subKey.truncate( idx );
146+
147+
QString msg = tr( "Do you confirm updating the Internal Statistics for DB: %1 ?\n\n"
148+
"This could take a long time (depending on the DB size),\n"
149+
"but implies better performances in the afterwhile" ).arg( subKey );
150+
QMessageBox::StandardButton result =
151+
QMessageBox::information( this, tr( "Confirm Update Statistics" ), msg, QMessageBox::Ok | QMessageBox::Cancel );
152+
if ( result != QMessageBox::Ok )
153+
return;
154+
155+
// trying to connect to SpatiaLite DB
156+
QgsSpatiaLiteConnection conn( subKey );
157+
if ( conn.updateStatistics() == true )
158+
{
159+
QMessageBox::information( this, tr( "Update Statistics" ),
160+
tr( "Internal Statistics succesfully updated for: %1" ).arg( subKey ) );
161+
}
162+
else
163+
{
164+
QMessageBox::critical( this, tr( "Update Statistics" ),
165+
tr( "Error while updating Internal Statistics for: %1" ).arg( subKey ) );
166+
}
167+
}
168+
135169
void QgsSpatiaLiteSourceSelect::on_cbxAllowGeometrylessTables_stateChanged( int )
136170
{
137171
on_btnConnect_clicked();
@@ -423,7 +457,10 @@ void QgsSpatiaLiteSourceSelect::on_btnConnect_clicked()
423457
}
424458

425459
if ( cmbConnections->count() > 0 )
460+
{
426461
mAddButton->setEnabled( true );
462+
mStatsButton->setEnabled( true );
463+
}
427464

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

‎src/providers/spatialite/qgsspatialitesourceselect.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,7 @@ class QgsSpatiaLiteSourceSelect: public QDialog, private Ui::QgsDbSourceSelectBa
7575
void on_btnConnect_clicked();
7676
void buildQuery();
7777
void addClicked();
78+
void updateStatistics();
7879
//! Opens the create connection dialog to build a new connection
7980
void on_btnNew_clicked();
8081
//! Deletes the selected connection
@@ -128,6 +129,7 @@ class QgsSpatiaLiteSourceSelect: public QDialog, private Ui::QgsDbSourceSelectBa
128129
QString layerURI( const QModelIndex &index );
129130
QPushButton *mBuildQueryButton;
130131
QPushButton *mAddButton;
132+
QPushButton *mStatsButton;
131133
};
132134

133135
#endif // QGSSPATIALITESOURCESELECT_H

0 commit comments

Comments
 (0)
Please sign in to comment.