1 /***************************************************************************
2 qgsspatialiteconnection.cpp
3 ---------------------
4 begin : October 2011
5 copyright : (C) 2011 by Martin Dobias
6 email : wonder dot sk at gmail dot com
7 ***************************************************************************
8 * *
9 * This program is free software; you can redistribute it and/or modify *
10 * it under the terms of the GNU General Public License as published by *
11 * the Free Software Foundation; either version 2 of the License, or *
12 * (at your option) any later version. *
13 * *
14 ***************************************************************************/
15 #include "qgsspatialiteconnection.h"
16 #include "qgssettings.h"
17 #include "qgslogger.h"
18 #include "qgsspatialiteutils.h"
19 #include "qgssqliteutils.h"
20
21 #include <QFileInfo>
22 #include <QRegularExpression>
23 #include <cstdlib> // atoi
24
25 #ifdef _MSC_VER
26 #define strcasecmp(a,b) stricmp(a,b)
27 #endif
28
29 const QString QgsSpatiaLiteConnection::SPATIALITE_ARRAY_PREFIX = QStringLiteral( "json" );
30 const QString QgsSpatiaLiteConnection::SPATIALITE_ARRAY_SUFFIX = QStringLiteral( "list" );
31
connectionList()32 QStringList QgsSpatiaLiteConnection::connectionList()
33 {
34 QgsSettings settings;
35 settings.beginGroup( QStringLiteral( "SpatiaLite/connections" ) );
36 return settings.childGroups();
37 }
38
deleteConnection(const QString & name)39 void QgsSpatiaLiteConnection::deleteConnection( const QString &name )
40 {
41 QgsSettings settings;
42 QString key = "/SpatiaLite/connections/" + name;
43 settings.remove( key + "/sqlitepath" );
44 settings.remove( key );
45 }
46
connectionPath(const QString & name)47 QString QgsSpatiaLiteConnection::connectionPath( const QString &name )
48 {
49 QgsSettings settings;
50 return settings.value( "/SpatiaLite/connections/" + name + "/sqlitepath" ).toString();
51 }
52
53 // -------
54
QgsSpatiaLiteConnection(const QString & name)55 QgsSpatiaLiteConnection::QgsSpatiaLiteConnection( const QString &name )
56 {
57 // "name" can be either a saved connection or a path to database
58
59 QgsSettings settings;
60 mPath = settings.value( QStringLiteral( "SpatiaLite/connections/%1/sqlitepath" ).arg( name ) ).toString();
61 if ( mPath.isNull() )
62 mPath = name; // not found in settings - probably it's a path
63 }
64
fetchTables(bool loadGeometrylessTables)65 QgsSpatiaLiteConnection::Error QgsSpatiaLiteConnection::fetchTables( bool loadGeometrylessTables )
66 {
67 mErrorMsg = QString();
68
69 QFileInfo fi( mPath );
70 if ( !fi.exists() )
71 return NotExists;
72
73 spatialite_database_unique_ptr database;
74 int ret = database.open( fi.canonicalFilePath() );
75 if ( ret )
76 return FailedToOpen;
77
78 ret = checkHasMetadataTables( database.get() );
79 if ( !mErrorMsg.isNull() || ret == LayoutUnknown )
80 {
81 // unexpected error; invalid SpatiaLite DB
82 return FailedToCheckMetadata;
83 }
84
85 if ( !getTableInfoAbstractInterface( database.get(), loadGeometrylessTables ) )
86 {
87 return FailedToGetTables;
88 }
89
90 return NoError;
91 }
92
updateStatistics()93 bool QgsSpatiaLiteConnection::updateStatistics()
94 {
95 QFileInfo fi( mPath );
96 if ( !fi.exists() )
97 return false;
98
99 spatialite_database_unique_ptr database;
100 int ret = database.open( fi.canonicalFilePath() );
101 if ( ret )
102 return false;
103
104 ret = update_layer_statistics( database.get(), nullptr, nullptr );
105
106 return ret;
107 }
108
nativeTypes()109 QList<QgsVectorDataProvider::NativeType> QgsSpatiaLiteConnection::nativeTypes()
110 {
111 return QList<QgsVectorDataProvider::NativeType>()
112 << QgsVectorDataProvider::NativeType( tr( "Binary object (BLOB)" ), QStringLiteral( "BLOB" ), QVariant::ByteArray )
113 << QgsVectorDataProvider::NativeType( tr( "Text" ), QStringLiteral( "TEXT" ), QVariant::String )
114 << QgsVectorDataProvider::NativeType( tr( "Decimal number (double)" ), QStringLiteral( "FLOAT" ), QVariant::Double )
115 << QgsVectorDataProvider::NativeType( tr( "Whole number (integer)" ), QStringLiteral( "INTEGER" ), QVariant::LongLong )
116 << QgsVectorDataProvider::NativeType( tr( "Date" ), QStringLiteral( "DATE" ), QVariant::Date )
117 << QgsVectorDataProvider::NativeType( tr( "Date & Time" ), QStringLiteral( "TIMESTAMP" ), QVariant::DateTime )
118
119 << QgsVectorDataProvider::NativeType( tr( "Array of text" ), SPATIALITE_ARRAY_PREFIX.toUpper() + "TEXT" + SPATIALITE_ARRAY_SUFFIX.toUpper(), QVariant::StringList, 0, 0, 0, 0, QVariant::String )
120 << QgsVectorDataProvider::NativeType( tr( "Array of decimal numbers (double)" ), SPATIALITE_ARRAY_PREFIX.toUpper() + "REAL" + SPATIALITE_ARRAY_SUFFIX.toUpper(), QVariant::List, 0, 0, 0, 0, QVariant::Double )
121 << QgsVectorDataProvider::NativeType( tr( "Array of whole numbers (integer)" ), SPATIALITE_ARRAY_PREFIX.toUpper() + "INTEGER" + SPATIALITE_ARRAY_SUFFIX.toUpper(), QVariant::List, 0, 0, 0, 0, QVariant::LongLong );
122 }
123
checkHasMetadataTables(sqlite3 * handle)124 int QgsSpatiaLiteConnection::checkHasMetadataTables( sqlite3 *handle )
125 {
126 bool gcSpatiaLite = false;
127 bool rsSpatiaLite = false;
128 bool gcSpatiaLite4 = false;
129 bool rsSpatiaLite4 = false;
130 bool tableName = false;
131 bool geomColumn = false;
132 bool coordDims = false;
133 bool gcSrid = false;
134 bool type = false;
135 bool geometry_type = false;
136 bool spatialIndex = false;
137 bool srsSrid = false;
138 bool authName = false;
139 bool authSrid = false;
140 bool refSysName = false;
141 bool proj4text = false;
142 bool srtext = false;
143 int ret;
144 const char *name = nullptr;
145 int i;
146 char **results = nullptr;
147 int rows;
148 int columns;
149 char *errMsg = nullptr;
150
151 // checking if table GEOMETRY_COLUMNS exists and has the expected layout
152 ret = sqlite3_get_table( handle, "PRAGMA table_info(geometry_columns)", &results, &rows, &columns, &errMsg );
153 if ( ret != SQLITE_OK )
154 {
155 mErrorMsg = tr( "table info on %1 failed" ).arg( QLatin1String( "geometry_columns" ) );
156 goto error;
157 }
158 if ( rows < 1 )
159 ;
160 else
161 {
162 for ( i = 1; i <= rows; i++ )
163 {
164 name = results[( i * columns ) + 1];
165 if ( strcasecmp( name, "f_table_name" ) == 0 )
166 tableName = true;
167 if ( strcasecmp( name, "f_geometry_column" ) == 0 )
168 geomColumn = true;
169 if ( strcasecmp( name, "coord_dimension" ) == 0 )
170 coordDims = true;
171 if ( strcasecmp( name, "srid" ) == 0 )
172 gcSrid = true;
173 if ( strcasecmp( name, "type" ) == 0 )
174 type = true;
175 if ( strcasecmp( name, "geometry_type" ) == 0 )
176 geometry_type = true;
177 if ( strcasecmp( name, "spatial_index_enabled" ) == 0 )
178 spatialIndex = true;
179 }
180 }
181 sqlite3_free_table( results );
182 if ( tableName && geomColumn && type && coordDims && gcSrid && spatialIndex )
183 gcSpatiaLite = true;
184 if ( tableName && geomColumn && geometry_type && coordDims && gcSrid && spatialIndex )
185 gcSpatiaLite4 = true;
186
187 // checking if table SPATIAL_REF_SYS exists and has the expected layout
188 ret = sqlite3_get_table( handle, "PRAGMA table_info(spatial_ref_sys)", &results, &rows, &columns, &errMsg );
189 if ( ret != SQLITE_OK )
190 {
191 mErrorMsg = tr( "table info on %1 failed" ).arg( QLatin1String( "spatial_ref_sys" ) );
192 goto error;
193 }
194 if ( rows < 1 )
195 ;
196 else
197 {
198 for ( i = 1; i <= rows; i++ )
199 {
200 name = results[( i * columns ) + 1];
201 if ( strcasecmp( name, "srid" ) == 0 )
202 srsSrid = true;
203 if ( strcasecmp( name, "auth_name" ) == 0 )
204 authName = true;
205 if ( strcasecmp( name, "auth_srid" ) == 0 )
206 authSrid = true;
207 if ( strcasecmp( name, "ref_sys_name" ) == 0 )
208 refSysName = true;
209 if ( strcasecmp( name, "proj4text" ) == 0 )
210 proj4text = true;
211 if ( strcasecmp( name, "srtext" ) == 0 )
212 srtext = true;
213 }
214 }
215 sqlite3_free_table( results );
216 if ( srsSrid && authName && authSrid && refSysName && proj4text )
217 rsSpatiaLite = true;
218 if ( srsSrid && authName && authSrid && refSysName && proj4text && srtext )
219 rsSpatiaLite4 = true;
220
221 // OK, this one seems to be a valid SpatiaLite DB
222 if ( gcSpatiaLite4 && rsSpatiaLite4 )
223 return LayoutCurrent;
224 if ( gcSpatiaLite && rsSpatiaLite )
225 return LayoutLegacy;
226
227 // this seems to be a valid SQLite DB, but not a SpatiaLite's one
228 return LayoutUnknown;
229
230 error:
231 // unexpected IO error
232 if ( errMsg )
233 {
234 mErrorMsg += '\n';
235 mErrorMsg += errMsg;
236 sqlite3_free( errMsg );
237 }
238 return false;
239 }
240
getTableInfoAbstractInterface(sqlite3 * handle,bool loadGeometrylessTables)241 bool QgsSpatiaLiteConnection::getTableInfoAbstractInterface( sqlite3 *handle, bool loadGeometrylessTables )
242 {
243 int ret;
244 int i;
245 char **results = nullptr;
246 int rows;
247 int columns;
248 char *errMsg = nullptr;
249 QString sql;
250 gaiaVectorLayersListPtr list;
251
252 const char *version = spatialite_version();
253 if ( isdigit( *version ) && *version >= '4' )
254 ; // OK, linked against libspatialite v.4.0 (or any subsequent)
255 else
256 {
257 mErrorMsg = tr( "obsolete libspatialite: AbstractInterface is unsupported" );
258 return false;
259 }
260
261 // List of system tables not to be shown if geometryless tables are requested
262 QStringList ignoreTableNames = QgsSqliteUtils::systemTables();
263
264 // attempting to load the VectorLayersList
265 list = gaiaGetVectorLayersList( handle, nullptr, nullptr, GAIA_VECTORS_LIST_FAST );
266 if ( list )
267 {
268 gaiaVectorLayerPtr lyr = list->First;
269 while ( lyr )
270 {
271 // populating the QGIS own Layers List
272 if ( lyr->AuthInfos )
273 {
274 if ( lyr->AuthInfos->IsHidden )
275 {
276 // skipping any Hidden layer
277 lyr = lyr->Next;
278 continue;
279 }
280 }
281
282 QString tableName = QString::fromUtf8( lyr->TableName );
283 ignoreTableNames << tableName;
284 QString column = QString::fromUtf8( lyr->GeometryName );
285 ignoreTableNames << QStringLiteral( "idx_%1_%2" ).arg( tableName, column )
286 << QStringLiteral( "idx_%1_%2_node" ).arg( tableName, column )
287 << QStringLiteral( "idx_%1_%2_parent" ).arg( tableName, column )
288 << QStringLiteral( "idx_%1_%2_rowid" ).arg( tableName, column );
289 QString type = tr( "UNKNOWN" );
290 switch ( lyr->GeometryType )
291 {
292 case GAIA_VECTOR_GEOMETRY:
293 type = tr( "GEOMETRY" );
294 break;
295 case GAIA_VECTOR_POINT:
296 type = tr( "POINT" );
297 break;
298 case GAIA_VECTOR_LINESTRING:
299 type = tr( "LINESTRING" );
300 break;
301 case GAIA_VECTOR_POLYGON:
302 type = tr( "POLYGON" );
303 break;
304 case GAIA_VECTOR_MULTIPOINT:
305 type = tr( "MULTIPOINT" );
306 break;
307 case GAIA_VECTOR_MULTILINESTRING:
308 type = tr( "MULTILINESTRING" );
309 break;
310 case GAIA_VECTOR_MULTIPOLYGON:
311 type = tr( "MULTIPOLYGON" );
312 break;
313 case GAIA_VECTOR_GEOMETRYCOLLECTION:
314 type = tr( "GEOMETRYCOLLECTION" );
315 break;
316 }
317 mTables.append( TableEntry( tableName, column, type ) );
318
319 lyr = lyr->Next;
320 }
321 gaiaFreeVectorLayersList( list );
322 }
323
324 if ( loadGeometrylessTables )
325 {
326 // get all tables
327 sql = "SELECT name "
328 "FROM sqlite_master "
329 "WHERE type in ('table', 'view')";
330 ret = sqlite3_get_table( handle, sql.toUtf8(), &results, &rows, &columns, &errMsg );
331 if ( ret != SQLITE_OK )
332 goto error;
333 if ( rows < 1 )
334 ;
335 else
336 {
337 for ( i = 1; i <= rows; i++ )
338 {
339 QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
340 if ( !ignoreTableNames.contains( tableName, Qt::CaseInsensitive ) )
341 mTables.append( TableEntry( tableName, QString(), QStringLiteral( "qgis_table" ) ) );
342 }
343 }
344 sqlite3_free_table( results );
345 }
346
347 return true;
348
349 error:
350 // unexpected IO error
351 mErrorMsg = tr( "unknown error cause" );
352 if ( errMsg )
353 {
354 mErrorMsg = errMsg;
355 sqlite3_free( errMsg );
356 }
357 return false;
358 }
359
getTableInfo(sqlite3 * handle,bool loadGeometrylessTables)360 bool QgsSpatiaLiteConnection::getTableInfo( sqlite3 *handle, bool loadGeometrylessTables )
361 {
362 int ret;
363 int i;
364 char **results = nullptr;
365 int rows;
366 int columns;
367 char *errMsg = nullptr;
368 QString sql;
369
370 // the following query return the tables containing a Geometry column
371 sql = "SELECT f_table_name, f_geometry_column, type "
372 "FROM geometry_columns";
373 ret = sqlite3_get_table( handle, sql.toUtf8(), &results, &rows, &columns, &errMsg );
374 if ( ret != SQLITE_OK )
375 goto error;
376 for ( i = 1; i <= rows; i++ )
377 {
378 if ( isRasterlite1Datasource( handle, results[( i * columns ) + 0] ) )
379 continue;
380 QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
381 QString column = QString::fromUtf8( results[( i * columns ) + 1] );
382 QString type = results[( i * columns ) + 2];
383 if ( isDeclaredHidden( handle, tableName, column ) )
384 continue;
385
386 mTables.append( TableEntry( tableName, column, type ) );
387 }
388 sqlite3_free_table( results );
389
390 if ( checkViewsGeometryColumns( handle ) )
391 {
392 // the following query return the views supporting a Geometry column
393 sql = "SELECT view_name, view_geometry, type "
394 "FROM views_geometry_columns "
395 "JOIN geometry_columns USING (f_table_name, f_geometry_column)";
396 ret = sqlite3_get_table( handle, sql.toUtf8(), &results, &rows, &columns, &errMsg );
397 if ( ret != SQLITE_OK )
398 goto error;
399 for ( i = 1; i <= rows; i++ )
400 {
401 QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
402 QString column = QString::fromUtf8( results[( i * columns ) + 1] );
403 QString type = results[( i * columns ) + 2];
404 if ( isDeclaredHidden( handle, tableName, column ) )
405 continue;
406
407 mTables.append( TableEntry( tableName, column, type ) );
408 }
409 sqlite3_free_table( results );
410 }
411
412 if ( checkVirtsGeometryColumns( handle ) )
413 {
414 // the following query return the VirtualShapefiles
415 sql = "SELECT virt_name, virt_geometry, type "
416 "FROM virts_geometry_columns";
417 ret = sqlite3_get_table( handle, sql.toUtf8(), &results, &rows, &columns, &errMsg );
418 if ( ret != SQLITE_OK )
419 goto error;
420 for ( i = 1; i <= rows; i++ )
421 {
422 QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
423 QString column = QString::fromUtf8( results[( i * columns ) + 1] );
424 QString type = results[( i * columns ) + 2];
425 if ( isDeclaredHidden( handle, tableName, column ) )
426 continue;
427
428 mTables.append( TableEntry( tableName, column, type ) );
429 }
430 sqlite3_free_table( results );
431 }
432
433 if ( loadGeometrylessTables )
434 {
435 // get all tables
436 sql = "SELECT name "
437 "FROM sqlite_master "
438 "WHERE type in ('table', 'view')";
439 ret = sqlite3_get_table( handle, sql.toUtf8(), &results, &rows, &columns, &errMsg );
440 if ( ret != SQLITE_OK )
441 goto error;
442 for ( i = 1; i <= rows; i++ )
443 {
444 QString tableName = QString::fromUtf8( results[( i * columns ) + 0] );
445 mTables.append( TableEntry( tableName, QString(), QStringLiteral( "qgis_table" ) ) );
446 }
447 sqlite3_free_table( results );
448 }
449
450 return true;
451
452 error:
453 // unexpected IO error
454 mErrorMsg = tr( "unknown error cause" );
455 if ( errMsg )
456 {
457 mErrorMsg = errMsg;
458 sqlite3_free( errMsg );
459 }
460 return false;
461 }
462
checkGeometryColumnsAuth(sqlite3 * handle)463 bool QgsSpatiaLiteConnection::checkGeometryColumnsAuth( sqlite3 *handle )
464 {
465 int ret;
466 int i;
467 char **results = nullptr;
468 int rows;
469 int columns;
470 bool exists = false;
471
472 // checking the metadata tables
473 QString sql = QStringLiteral( "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'geometry_columns_auth'" );
474
475 ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, nullptr );
476 if ( ret != SQLITE_OK )
477 return false;
478 if ( rows < 1 )
479 ;
480 else
481 {
482 for ( i = 1; i <= rows; i++ )
483 {
484 if ( results[( i * columns ) + 0] )
485 {
486 const char *name = results[( i * columns ) + 0];
487 if ( name )
488 exists = true;
489 }
490 }
491 }
492 sqlite3_free_table( results );
493 return exists;
494 }
495
496
checkViewsGeometryColumns(sqlite3 * handle)497 bool QgsSpatiaLiteConnection::checkViewsGeometryColumns( sqlite3 *handle )
498 {
499 int ret;
500 int i;
501 char **results = nullptr;
502 int rows;
503 int columns;
504 bool exists = false;
505
506 // checking the metadata tables
507 QString sql = QStringLiteral( "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'views_geometry_columns'" );
508
509 ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, nullptr );
510 if ( ret != SQLITE_OK )
511 return false;
512 if ( rows < 1 )
513 ;
514 else
515 {
516 for ( i = 1; i <= rows; i++ )
517 {
518 if ( results[( i * columns ) + 0] )
519 {
520 const char *name = results[( i * columns ) + 0];
521 if ( name )
522 exists = true;
523 }
524 }
525 }
526 sqlite3_free_table( results );
527 return exists;
528 }
529
checkVirtsGeometryColumns(sqlite3 * handle)530 bool QgsSpatiaLiteConnection::checkVirtsGeometryColumns( sqlite3 *handle )
531 {
532 int ret;
533 int i;
534 char **results = nullptr;
535 int rows;
536 int columns;
537 bool exists = false;
538
539 // checking the metadata tables
540 QString sql = QStringLiteral( "SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'virts_geometry_columns'" );
541
542 ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, nullptr );
543 if ( ret != SQLITE_OK )
544 return false;
545 if ( rows < 1 )
546 ;
547 else
548 {
549 for ( i = 1; i <= rows; i++ )
550 {
551 if ( results[( i * columns ) + 0] )
552 {
553 const char *name = results[( i * columns ) + 0];
554 if ( name )
555 exists = true;
556 }
557 }
558 }
559 sqlite3_free_table( results );
560 return exists;
561 }
562
isRasterlite1Datasource(sqlite3 * handle,const char * table)563 bool QgsSpatiaLiteConnection::isRasterlite1Datasource( sqlite3 *handle, const char *table )
564 {
565 // testing for RasterLite-1 datasources
566 int ret;
567 int i;
568 char **results = nullptr;
569 int rows;
570 int columns;
571 bool exists = false;
572 char table_raster[4192];
573 char sql[4258];
574
575 strncpy( table_raster, table, sizeof table_raster );
576 table_raster[ sizeof table_raster - 1 ] = '\0';
577
578 size_t len = strlen( table_raster );
579 if ( strlen( table_raster ) < 9 )
580 return false;
581 if ( strcmp( table_raster + len - 9, "_metadata" ) != 0 )
582 return false;
583 // OK, possible candidate
584 strcpy( table_raster + len - 9, "_rasters" );
585
586 // checking if the related "_RASTERS table exists
587 sprintf( sql, "SELECT name FROM sqlite_master WHERE type = 'table' AND name = '%s'", table_raster );
588
589 ret = sqlite3_get_table( handle, sql, &results, &rows, &columns, nullptr );
590 if ( ret != SQLITE_OK )
591 return false;
592 if ( rows < 1 )
593 ;
594 else
595 {
596 for ( i = 1; i <= rows; i++ )
597 {
598 if ( results[( i * columns ) + 0] )
599 {
600 const char *name = results[( i * columns ) + 0];
601 if ( name )
602 exists = true;
603 }
604 }
605 }
606 sqlite3_free_table( results );
607 return exists;
608 }
609
isDeclaredHidden(sqlite3 * handle,const QString & table,const QString & geom)610 bool QgsSpatiaLiteConnection::isDeclaredHidden( sqlite3 *handle, const QString &table, const QString &geom )
611 {
612 int ret;
613 int i;
614 char **results = nullptr;
615 int rows;
616 int columns;
617 char *errMsg = nullptr;
618 bool isHidden = false;
619
620 if ( !checkGeometryColumnsAuth( handle ) )
621 return false;
622 // checking if some Layer has been declared as HIDDEN
623 QString sql = QString( "SELECT hidden FROM geometry_columns_auth"
624 " WHERE f_table_name=%1 and f_geometry_column=%2" ).arg( QgsSqliteUtils::quotedString( table ),
625 QgsSqliteUtils::quotedString( geom ) );
626
627 ret = sqlite3_get_table( handle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
628 if ( ret != SQLITE_OK )
629 goto error;
630 if ( rows < 1 )
631 ;
632 else
633 {
634 for ( i = 1; i <= rows; i++ )
635 {
636 if ( results[( i * columns ) + 0] )
637 {
638 if ( atoi( results[( i * columns ) + 0] ) != 0 )
639 isHidden = true;
640 }
641 }
642 }
643 sqlite3_free_table( results );
644
645 return isHidden;
646
647 error:
648 // unexpected IO error
649 mErrorMsg = tr( "unknown error cause" );
650 if ( errMsg )
651 {
652 mErrorMsg = errMsg;
653 sqlite3_free( errMsg );
654 }
655 return false;
656 }
657
658
659
660
661
fcnRegexp(sqlite3_context * ctx,int,sqlite3_value * argv[])662 static void fcnRegexp( sqlite3_context *ctx, int /*argc*/, sqlite3_value *argv[] )
663 {
664 QRegularExpression re( reinterpret_cast<const char *>( sqlite3_value_text( argv[0] ) ) );
665 QString string( reinterpret_cast<const char *>( sqlite3_value_text( argv[1] ) ) );
666
667 if ( !re.isValid() )
668 return sqlite3_result_error( ctx, "invalid operand", -1 );
669
670 sqlite3_result_int( ctx, string.contains( re ) );
671 }
672
673
674
675
676 QMap < QString, QgsSqliteHandle * > QgsSqliteHandle::sHandles;
677 QMutex QgsSqliteHandle::sHandleMutex;
678
679
checkMetadata(sqlite3 * handle)680 bool QgsSqliteHandle::checkMetadata( sqlite3 *handle )
681 {
682 int ret;
683 int i;
684 char **results = nullptr;
685 int rows;
686 int columns;
687 int spatial_type = 0;
688 ret = sqlite3_get_table( handle, "SELECT CheckSpatialMetadata()", &results, &rows, &columns, nullptr );
689 if ( ret != SQLITE_OK )
690 goto skip;
691 if ( rows < 1 )
692 ;
693 else
694 {
695 for ( i = 1; i <= rows; i++ )
696 spatial_type = atoi( results[( i * columns ) + 0] );
697 }
698 sqlite3_free_table( results );
699 skip:
700 if ( spatial_type == 1 || spatial_type == 3 )
701 return true;
702 return false;
703 }
704
openDb(const QString & dbPath,bool shared)705 QgsSqliteHandle *QgsSqliteHandle::openDb( const QString &dbPath, bool shared )
706 {
707 //QMap < QString, QgsSqliteHandle* >&handles = QgsSqliteHandle::handles;
708 QMutexLocker locker( &sHandleMutex );
709
710 if ( shared && sHandles.contains( dbPath ) )
711 {
712 QgsDebugMsg( QStringLiteral( "Using cached connection for %1" ).arg( dbPath ) );
713 sHandles[dbPath]->ref++;
714 return sHandles[dbPath];
715 }
716
717 QgsDebugMsg( QStringLiteral( "New sqlite connection for " ) + dbPath );
718 spatialite_database_unique_ptr database;
719 if ( database.open_v2( dbPath, shared ? SQLITE_OPEN_READWRITE : SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX, nullptr ) )
720 {
721 // failure
722 QgsDebugMsg( QStringLiteral( "Failure while connecting to: %1\n%2" )
723 .arg( dbPath,
724 QString::fromUtf8( sqlite3_errmsg( database.get() ) ) ) );
725 return nullptr;
726 }
727
728 // checking the DB for sanity
729 if ( !checkMetadata( database.get() ) )
730 {
731 // failure
732 QgsDebugMsg( QStringLiteral( "Failure while connecting to: %1\n\ninvalid metadata tables" ).arg( dbPath ) );
733 return nullptr;
734 }
735
736 // add REGEXP function
737 sqlite3_create_function( database.get(), "REGEXP", 2, SQLITE_UTF8, nullptr, fcnRegexp, nullptr, nullptr );
738
739 // activating Foreign Key constraints
740 ( void )sqlite3_exec( database.get(), "PRAGMA foreign_keys = 1", nullptr, nullptr, nullptr );
741
742 QgsDebugMsg( QStringLiteral( "Connection to the database was successful" ) );
743
744 QgsSqliteHandle *handle = new QgsSqliteHandle( std::move( database ), dbPath, shared );
745 if ( shared )
746 sHandles.insert( dbPath, handle );
747
748 return handle;
749 }
750
closeDb(QgsSqliteHandle * & handle)751 void QgsSqliteHandle::closeDb( QgsSqliteHandle *&handle )
752 {
753 if ( handle->ref == -1 )
754 {
755 // not shared
756 delete handle;
757 }
758 else
759 {
760 QMutexLocker locker( &sHandleMutex );
761 QMap < QString, QgsSqliteHandle * >::iterator i;
762 for ( i = sHandles.begin(); i != sHandles.end() && i.value() != handle; ++i )
763 ;
764
765 Q_ASSERT( i.value() == handle );
766 Q_ASSERT( i.value()->ref > 0 );
767
768 if ( --i.value()->ref == 0 )
769 {
770 delete i.value();
771 i = sHandles.erase( i );
772 }
773 }
774
775 handle = nullptr;
776 }
777
closeAll()778 void QgsSqliteHandle::closeAll()
779 {
780 QMutexLocker locker( &sHandleMutex );
781 qDeleteAll( sHandles );
782 sHandles.clear();
783 }
784