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