1 /****************************************************************************
2 **
3 ** Copyright (C) 2016 The Qt Company Ltd.
4 ** Contact: https://www.qt.io/licensing/
5 **
6 ** This file is part of the QtSql module of the Qt Toolkit.
7 **
8 ** $QT_BEGIN_LICENSE:LGPL$
9 ** Commercial License Usage
10 ** Licensees holding valid commercial Qt licenses may use this file in
11 ** accordance with the commercial license agreement provided with the
12 ** Software or, alternatively, in accordance with the terms contained in
13 ** a written agreement between you and The Qt Company. For licensing terms
14 ** and conditions see https://www.qt.io/terms-conditions. For further
15 ** information use the contact form at https://www.qt.io/contact-us.
16 **
17 ** GNU Lesser General Public License Usage
18 ** Alternatively, this file may be used under the terms of the GNU Lesser
19 ** General Public License version 3 as published by the Free Software
20 ** Foundation and appearing in the file LICENSE.LGPL3 included in the
21 ** packaging of this file. Please review the following information to
22 ** ensure the GNU Lesser General Public License version 3 requirements
23 ** will be met: https://www.gnu.org/licenses/lgpl-3.0.html.
24 **
25 ** GNU General Public License Usage
26 ** Alternatively, this file may be used under the terms of the GNU
27 ** General Public License version 2.0 or (at your option) the GNU General
28 ** Public license version 3 or any later version approved by the KDE Free
29 ** Qt Foundation. The licenses are as published by the Free Software
30 ** Foundation and appearing in the file LICENSE.GPL2 and LICENSE.GPL3
31 ** included in the packaging of this file. Please review the following
32 ** information to ensure the GNU General Public License requirements will
33 ** be met: https://www.gnu.org/licenses/gpl-2.0.html and
34 ** https://www.gnu.org/licenses/gpl-3.0.html.
35 **
36 ** $QT_END_LICENSE$
37 **
38 ****************************************************************************/
39
40 #include "qsql_spatialite.h"
41
42 #include <qcoreapplication.h>
43 #include <qdatetime.h>
44 #include <qvariant.h>
45 #include <qsqlerror.h>
46 #include <qsqlfield.h>
47 #include <qsqlindex.h>
48 #include <qsqlquery.h>
49 #include <QtSql/private/qsqlcachedresult_p.h>
50 #include <QtSql/private/qsqldriver_p.h>
51 #include <qstringlist.h>
52 #include <qvector.h>
53 #include <qdebug.h>
54 #include <QTimeZone>
55
56 #include "qgsspatialiteutils.h"
57
58 #if defined Q_OS_WIN
59 # include <qt_windows.h>
60 #else
61 # include <unistd.h>
62 #endif
63
64 #include <sqlite3.h>
65 #include <functional>
66
67 QT_BEGIN_NAMESPACE
68
_q_escapeIdentifier(const QString & identifier)69 static QString _q_escapeIdentifier( const QString &identifier )
70 {
71 QString res = identifier;
72 if ( !identifier.isEmpty() && !identifier.startsWith( QLatin1Char( '"' ) ) && !identifier.endsWith( QLatin1Char( '"' ) ) )
73 {
74 res.replace( QLatin1Char( '"' ), QLatin1String( "\"\"" ) );
75 res.prepend( QLatin1Char( '"' ) ).append( QLatin1Char( '"' ) );
76 res.replace( QLatin1Char( '.' ), QLatin1String( "\".\"" ) );
77 }
78 return res;
79 }
80
qGetColumnType(const QString & tpName)81 static QVariant::Type qGetColumnType( const QString &tpName )
82 {
83 const QString typeName = tpName.toLower();
84
85 if ( typeName == QLatin1String( "integer" )
86 || typeName == QLatin1String( "int" ) )
87 return QVariant::Int;
88 if ( typeName == QLatin1String( "double" )
89 || typeName == QLatin1String( "float" )
90 || typeName == QLatin1String( "real" )
91 || typeName.startsWith( QLatin1String( "numeric" ) ) )
92 return QVariant::Double;
93 if ( typeName == QLatin1String( "blob" ) )
94 return QVariant::ByteArray;
95 return QVariant::String;
96 }
97
qMakeError(const spatialite_database_unique_ptr & access,const QString & descr,QSqlError::ErrorType type,int errorCode=-1)98 static QSqlError qMakeError( const spatialite_database_unique_ptr &access, const QString &descr, QSqlError::ErrorType type,
99 int errorCode = -1 )
100 {
101 return QSqlError( descr, access.errorMessage(), type, QString::number( errorCode ) );
102 }
103
104 class QSpatiaLiteResultPrivate;
105
106 class QSpatiaLiteResult : public QSqlCachedResult
107 {
108 Q_DECLARE_PRIVATE( QSpatiaLiteResult )
109 friend class QSpatiaLiteDriver;
110
111 public:
112 explicit QSpatiaLiteResult( const QSpatiaLiteDriver *db );
113 ~QSpatiaLiteResult();
114 protected:
115 bool gotoNext( QSqlCachedResult::ValueCache &row, int idx ) Q_DECL_OVERRIDE;
116 bool reset( const QString &query ) Q_DECL_OVERRIDE;
117 bool prepare( const QString &query ) Q_DECL_OVERRIDE;
118 bool exec() Q_DECL_OVERRIDE;
119 int size() Q_DECL_OVERRIDE;
120 int numRowsAffected() Q_DECL_OVERRIDE;
121 QVariant lastInsertId() const Q_DECL_OVERRIDE;
122 QSqlRecord record() const Q_DECL_OVERRIDE;
123 void detachFromResultSet() Q_DECL_OVERRIDE;
124 void virtual_hook( int id, void *data ) Q_DECL_OVERRIDE;
125 };
126
127 class QSpatiaLiteDriverPrivate : public QSqlDriverPrivate
128 {
129 Q_DECLARE_PUBLIC( QSpatiaLiteDriver )
130
131 public:
QSpatiaLiteDriverPrivate()132 inline QSpatiaLiteDriverPrivate() : QSqlDriverPrivate() { dbmsType = QSqlDriver::SQLite; }
133 spatialite_database_unique_ptr access;
134 QList <QSpatiaLiteResult *> results;
135 QStringList notificationid;
136 };
137
138
139 class QSpatiaLiteResultPrivate: public QSqlCachedResultPrivate
140 {
141 Q_DECLARE_PUBLIC( QSpatiaLiteResult )
142
143 public:
144 Q_DECLARE_SQLDRIVER_PRIVATE( QSpatiaLiteDriver )
145 QSpatiaLiteResultPrivate( QSpatiaLiteResult *q, const QSpatiaLiteDriver *drv );
146 void cleanup();
147 bool fetchNext( QSqlCachedResult::ValueCache &values, int idx, bool initialFetch );
148 // initializes the recordInfo and the cache
149 void initColumns( bool emptyResultset );
150 void finalize();
151
152 sqlite3_statement_unique_ptr stmt;
153
154 bool skippedStatus; // the status of the fetchNext() that's skipped
155 bool skipRow; // skip the next fetchNext()?
156 QSqlRecord rInf;
157 QVector<QVariant> firstRow;
158 };
159
QSpatiaLiteResultPrivate(QSpatiaLiteResult * q,const QSpatiaLiteDriver * drv)160 QSpatiaLiteResultPrivate::QSpatiaLiteResultPrivate( QSpatiaLiteResult *q, const QSpatiaLiteDriver *drv )
161 : QSqlCachedResultPrivate( q, drv )
162 , skippedStatus( false )
163 , skipRow( false )
164 {
165 }
166
cleanup()167 void QSpatiaLiteResultPrivate::cleanup()
168 {
169 Q_Q( QSpatiaLiteResult );
170 finalize();
171 rInf.clear();
172 skippedStatus = false;
173 skipRow = false;
174 q->setAt( QSql::BeforeFirstRow );
175 q->setActive( false );
176 q->cleanup();
177 }
178
finalize()179 void QSpatiaLiteResultPrivate::finalize()
180 {
181 stmt.reset();
182 }
183
initColumns(bool emptyResultset)184 void QSpatiaLiteResultPrivate::initColumns( bool emptyResultset )
185 {
186 Q_Q( QSpatiaLiteResult );
187 int nCols = stmt.columnCount();
188 if ( nCols <= 0 )
189 return;
190
191 q->init( nCols );
192
193 for ( int i = 0; i < nCols; ++i )
194 {
195 QString colName = stmt.columnName( i ).remove( QLatin1Char( '"' ) );
196
197 // must use typeName for resolving the type to match QSqliteDriver::record
198 QString typeName = QString( reinterpret_cast<const QChar *>(
199 sqlite3_column_decltype16( stmt.get(), i ) ) );
200 // sqlite3_column_type is documented to have undefined behavior if the result set is empty
201 int stp = emptyResultset ? -1 : sqlite3_column_type( stmt.get(), i );
202
203 QVariant::Type fieldType;
204
205 if ( !typeName.isEmpty() )
206 {
207 fieldType = qGetColumnType( typeName );
208 }
209 else
210 {
211 // Get the proper type for the field based on stp value
212 switch ( stp )
213 {
214 case SQLITE_INTEGER:
215 fieldType = QVariant::Int;
216 break;
217 case SQLITE_FLOAT:
218 fieldType = QVariant::Double;
219 break;
220 case SQLITE_BLOB:
221 fieldType = QVariant::ByteArray;
222 break;
223 case SQLITE_TEXT:
224 fieldType = QVariant::String;
225 break;
226 case SQLITE_NULL:
227 default:
228 fieldType = QVariant::Invalid;
229 break;
230 }
231 }
232
233 QSqlField fld( colName, fieldType );
234 fld.setSqlType( stp );
235 rInf.append( fld );
236 }
237 }
238
fetchNext(QSqlCachedResult::ValueCache & values,int idx,bool initialFetch)239 bool QSpatiaLiteResultPrivate::fetchNext( QSqlCachedResult::ValueCache &values, int idx, bool initialFetch )
240 {
241 Q_Q( QSpatiaLiteResult );
242 int res;
243 int i;
244
245 if ( skipRow )
246 {
247 // already fetched
248 Q_ASSERT( !initialFetch );
249 skipRow = false;
250 for ( int i = 0; i < firstRow.count(); i++ )
251 values[i] = firstRow[i];
252 return skippedStatus;
253 }
254 skipRow = initialFetch;
255
256 if ( initialFetch )
257 {
258 firstRow.clear();
259 firstRow.resize( stmt.columnCount() );
260 }
261
262 if ( !stmt )
263 {
264 q->setLastError( QSqlError( QCoreApplication::translate( "QSpatiaLiteResult", "Unable to fetch row" ),
265 QCoreApplication::translate( "QSpatiaLiteResult", "No query" ), QSqlError::ConnectionError ) );
266 q->setAt( QSql::AfterLastRow );
267 return false;
268 }
269 res = stmt.step();
270
271 switch ( res )
272 {
273 case SQLITE_ROW:
274 // check to see if should fill out columns
275 if ( rInf.isEmpty() )
276 // must be first call.
277 initColumns( false );
278 if ( idx < 0 && !initialFetch )
279 return true;
280 for ( i = 0; i < rInf.count(); ++i )
281 {
282 switch ( sqlite3_column_type( stmt.get(), i ) )
283 {
284 case SQLITE_BLOB:
285 values[i + idx] = QByteArray( static_cast<const char *>(
286 sqlite3_column_blob( stmt.get(), i ) ),
287 sqlite3_column_bytes( stmt.get(), i ) );
288 break;
289 case SQLITE_INTEGER:
290 values[i + idx] = stmt.columnAsInt64( i );
291 break;
292 case SQLITE_FLOAT:
293 switch ( q->numericalPrecisionPolicy() )
294 {
295 case QSql::LowPrecisionInt32:
296 values[i + idx] = stmt.columnAsInt64( i );
297 break;
298 case QSql::LowPrecisionInt64:
299 values[i + idx] = stmt.columnAsInt64( i );
300 break;
301 case QSql::LowPrecisionDouble:
302 case QSql::HighPrecision:
303 default:
304 values[i + idx] = stmt.columnAsDouble( i );
305 break;
306 };
307 break;
308 case SQLITE_NULL:
309 values[i + idx] = QVariant( QVariant::String );
310 break;
311 default:
312 values[i + idx] = stmt.columnAsText( i );
313 break;
314 }
315 }
316 return true;
317 case SQLITE_DONE:
318 if ( rInf.isEmpty() )
319 // must be first call.
320 initColumns( true );
321 q->setAt( QSql::AfterLastRow );
322 sqlite3_reset( stmt.get() );
323 return false;
324 case SQLITE_CONSTRAINT:
325 case SQLITE_ERROR:
326 // SQLITE_ERROR is a generic error code and we must call sqlite3_reset()
327 // to get the specific error message.
328 res = sqlite3_reset( stmt.get() );
329 q->setLastError( qMakeError( drv_d_func()->access, QCoreApplication::translate( "QSpatiaLiteResult",
330 "Unable to fetch row" ), QSqlError::ConnectionError, res ) );
331 q->setAt( QSql::AfterLastRow );
332 return false;
333 case SQLITE_MISUSE:
334 case SQLITE_BUSY:
335 default:
336 // something wrong, don't get col info, but still return false
337 q->setLastError( qMakeError( drv_d_func()->access, QCoreApplication::translate( "QSpatiaLiteResult",
338 "Unable to fetch row" ), QSqlError::ConnectionError, res ) );
339 sqlite3_reset( stmt.get() );
340 q->setAt( QSql::AfterLastRow );
341 return false;
342 }
343 #ifndef _MSC_VER // avoid warning
344 return false;
345 #endif
346 }
347
QSpatiaLiteResult(const QSpatiaLiteDriver * db)348 QSpatiaLiteResult::QSpatiaLiteResult( const QSpatiaLiteDriver *db )
349 : QSqlCachedResult( *new QSpatiaLiteResultPrivate( this, db ) )
350 {
351 Q_D( QSpatiaLiteResult );
352 const_cast<QSpatiaLiteDriverPrivate *>( d->drv_d_func() )->results.append( this );
353 }
354
~QSpatiaLiteResult()355 QSpatiaLiteResult::~QSpatiaLiteResult()
356 {
357 Q_D( QSpatiaLiteResult );
358 if ( d->drv_d_func() )
359 const_cast<QSpatiaLiteDriverPrivate *>( d->drv_d_func() )->results.removeOne( this );
360 d->cleanup();
361 }
362
virtual_hook(int id,void * data)363 void QSpatiaLiteResult::virtual_hook( int id, void *data )
364 {
365 QSqlCachedResult::virtual_hook( id, data );
366 }
367
reset(const QString & query)368 bool QSpatiaLiteResult::reset( const QString &query )
369 {
370 if ( !prepare( query ) )
371 return false;
372 return exec();
373 }
374
prepare(const QString & query)375 bool QSpatiaLiteResult::prepare( const QString &query )
376 {
377 Q_D( QSpatiaLiteResult );
378 if ( !driver() || !driver()->isOpen() || driver()->isOpenError() )
379 return false;
380
381 d->cleanup();
382
383 setSelect( false );
384
385 int res;
386 d->stmt = d->drv_d_func()->access.prepare( query, res );
387 if ( res != SQLITE_OK )
388 {
389 setLastError( qMakeError( d->drv_d_func()->access, QCoreApplication::translate( "QSpatiaLiteResult",
390 "Unable to execute statement" ), QSqlError::StatementError, res ) );
391 d->finalize();
392 return false;
393 }
394 return true;
395 }
396
secondsToOffset(int seconds)397 static QString secondsToOffset( int seconds )
398 {
399 const QChar sign = ushort( seconds < 0 ? '-' : '+' );
400 seconds = qAbs( seconds );
401 const int hours = seconds / 3600;
402 const int minutes = ( seconds % 3600 ) / 60;
403
404 return QString( QStringLiteral( "%1%2:%3" ) ).arg( sign ).arg( hours, 2, 10, QLatin1Char( '0' ) ).arg( minutes, 2, 10, QLatin1Char( '0' ) );
405 }
406
timespecToString(const QDateTime & dateTime)407 static QString timespecToString( const QDateTime &dateTime )
408 {
409 switch ( dateTime.timeSpec() )
410 {
411 case Qt::LocalTime:
412 return QString();
413 case Qt::UTC:
414 return QStringLiteral( "Z" );
415 case Qt::OffsetFromUTC:
416 return secondsToOffset( dateTime.offsetFromUtc() );
417 case Qt::TimeZone:
418 return secondsToOffset( dateTime.timeZone().offsetFromUtc( dateTime ) );
419 default:
420 return QString();
421 }
422 }
423
exec()424 bool QSpatiaLiteResult::exec()
425 {
426 Q_D( QSpatiaLiteResult );
427 const QVector<QVariant> values = boundValues();
428
429 d->skippedStatus = false;
430 d->skipRow = false;
431 d->rInf.clear();
432 clearValues();
433 setLastError( QSqlError() );
434
435 int res = sqlite3_reset( d->stmt.get() );
436 if ( res != SQLITE_OK )
437 {
438 setLastError( qMakeError( d->drv_d_func()->access, QCoreApplication::translate( "QSpatiaiteResult",
439 "Unable to reset statement" ), QSqlError::StatementError, res ) );
440 d->finalize();
441 return false;
442 }
443 int paramCount = sqlite3_bind_parameter_count( d->stmt.get() );
444 if ( paramCount == values.count() )
445 {
446 for ( int i = 0; i < paramCount; ++i )
447 {
448 res = SQLITE_OK;
449 const QVariant value = values.at( i );
450
451 if ( value.isNull() )
452 {
453 res = sqlite3_bind_null( d->stmt.get(), i + 1 );
454 }
455 else
456 {
457 switch ( value.type() )
458 {
459 case QVariant::ByteArray:
460 {
461 const QByteArray *ba = static_cast<const QByteArray *>( value.constData() );
462 res = sqlite3_bind_blob( d->stmt.get(), i + 1, ba->constData(),
463 ba->size(), SQLITE_STATIC );
464 break;
465 }
466 case QVariant::Int:
467 case QVariant::Bool:
468 res = sqlite3_bind_int( d->stmt.get(), i + 1, value.toInt() );
469 break;
470 case QVariant::Double:
471 res = sqlite3_bind_double( d->stmt.get(), i + 1, value.toDouble() );
472 break;
473 case QVariant::UInt:
474 case QVariant::LongLong:
475 res = sqlite3_bind_int64( d->stmt.get(), i + 1, value.toLongLong() );
476 break;
477 case QVariant::DateTime:
478 {
479 const QDateTime dateTime = value.toDateTime();
480 const QString str = dateTime.toString( QStringLiteral( "yyyy-MM-ddThh:mm:ss.zzz" ) + timespecToString( dateTime ) );
481 res = sqlite3_bind_text16( d->stmt.get(), i + 1, str.utf16(),
482 str.size() * sizeof( ushort ), SQLITE_TRANSIENT );
483 break;
484 }
485 case QVariant::Time:
486 {
487 const QTime time = value.toTime();
488 const QString str = time.toString( QStringLiteral( "hh:mm:ss.zzz" ) );
489 res = sqlite3_bind_text16( d->stmt.get(), i + 1, str.utf16(),
490 str.size() * sizeof( ushort ), SQLITE_TRANSIENT );
491 break;
492 }
493 case QVariant::String:
494 {
495 // lifetime of string == lifetime of its qvariant
496 const QString *str = static_cast<const QString *>( value.constData() );
497 res = sqlite3_bind_text16( d->stmt.get(), i + 1, str->utf16(),
498 ( str->size() ) * sizeof( QChar ), SQLITE_STATIC );
499 break;
500 }
501 default:
502 {
503 QString str = value.toString();
504 // SQLITE_TRANSIENT makes sure that sqlite buffers the data
505 res = sqlite3_bind_text16( d->stmt.get(), i + 1, str.utf16(),
506 ( str.size() ) * sizeof( QChar ), SQLITE_TRANSIENT );
507 break;
508 }
509 }
510 }
511 if ( res != SQLITE_OK )
512 {
513 setLastError( qMakeError( d->drv_d_func()->access, QCoreApplication::translate( "QSpatiaLiteResult",
514 "Unable to bind parameters" ), QSqlError::StatementError, res ) );
515 d->finalize();
516 return false;
517 }
518 }
519 }
520 else
521 {
522 setLastError( QSqlError( QCoreApplication::translate( "QSpatiaLiteResult",
523 "Parameter count mismatch" ), QString(), QSqlError::StatementError ) );
524 return false;
525 }
526 d->skippedStatus = d->fetchNext( d->firstRow, 0, true );
527 if ( lastError().isValid() )
528 {
529 setSelect( false );
530 setActive( false );
531 return false;
532 }
533 setSelect( !d->rInf.isEmpty() );
534 setActive( true );
535 return true;
536 }
537
gotoNext(QSqlCachedResult::ValueCache & row,int idx)538 bool QSpatiaLiteResult::gotoNext( QSqlCachedResult::ValueCache &row, int idx )
539 {
540 Q_D( QSpatiaLiteResult );
541 return d->fetchNext( row, idx, false );
542 }
543
size()544 int QSpatiaLiteResult::size()
545 {
546 return -1;
547 }
548
numRowsAffected()549 int QSpatiaLiteResult::numRowsAffected()
550 {
551 Q_D( const QSpatiaLiteResult );
552 return sqlite3_changes( d->drv_d_func()->access.get() );
553 }
554
lastInsertId() const555 QVariant QSpatiaLiteResult::lastInsertId() const
556 {
557 Q_D( const QSpatiaLiteResult );
558 if ( isActive() )
559 {
560 qint64 id = sqlite3_last_insert_rowid( d->drv_d_func()->access.get() );
561 if ( id )
562 return id;
563 }
564 return QVariant();
565 }
566
record() const567 QSqlRecord QSpatiaLiteResult::record() const
568 {
569 Q_D( const QSpatiaLiteResult );
570 if ( !isActive() || !isSelect() )
571 return QSqlRecord();
572 return d->rInf;
573 }
574
detachFromResultSet()575 void QSpatiaLiteResult::detachFromResultSet()
576 {
577 Q_D( QSpatiaLiteResult );
578 if ( d->stmt )
579 sqlite3_reset( d->stmt.get() );
580 }
581
582 /////////////////////////////////////////////////////////
583
QSpatiaLiteDriver(QObject * parent)584 QSpatiaLiteDriver::QSpatiaLiteDriver( QObject *parent )
585 : QSqlDriver( *new QSpatiaLiteDriverPrivate, parent )
586 {
587 }
588
~QSpatiaLiteDriver()589 QSpatiaLiteDriver::~QSpatiaLiteDriver()
590 {
591 close();
592 }
593
hasFeature(DriverFeature f) const594 bool QSpatiaLiteDriver::hasFeature( DriverFeature f ) const
595 {
596 switch ( f )
597 {
598 case BLOB:
599 case Transactions:
600 case Unicode:
601 case LastInsertId:
602 case PreparedQueries:
603 case PositionalPlaceholders:
604 case SimpleLocking:
605 case FinishQuery:
606 case LowPrecisionNumbers:
607 case EventNotifications:
608 return true;
609 case QuerySize:
610 case NamedPlaceholders:
611 case BatchOperations:
612 case MultipleResultSets:
613 case CancelQuery:
614 return false;
615 }
616 return false;
617 }
618
619 /*
620 SQLite dbs have no user name, passwords, hosts or ports.
621 just file names.
622 */
open(const QString & db,const QString &,const QString &,const QString &,int,const QString & conOpts)623 bool QSpatiaLiteDriver::open( const QString &db, const QString &, const QString &, const QString &, int, const QString &conOpts )
624 {
625 Q_D( QSpatiaLiteDriver );
626 if ( isOpen() )
627 close();
628
629
630 int timeOut = 5000;
631 bool sharedCache = false;
632 bool openReadOnlyOption = false;
633 bool openUriOption = false;
634
635 const auto opts = conOpts.splitRef( QLatin1Char( ';' ) );
636 for ( auto option : opts )
637 {
638 option = option.trimmed();
639 if ( option.startsWith( QLatin1String( "QSQLITE_BUSY_TIMEOUT" ) ) )
640 {
641 option = option.mid( 20 ).trimmed();
642 if ( option.startsWith( QLatin1Char( '=' ) ) )
643 {
644 bool ok;
645 const int nt = option.mid( 1 ).trimmed().toInt( &ok );
646 if ( ok )
647 timeOut = nt;
648 }
649 }
650 else if ( option == QLatin1String( "QSQLITE_OPEN_READONLY" ) )
651 {
652 openReadOnlyOption = true;
653 }
654 else if ( option == QLatin1String( "QSQLITE_OPEN_URI" ) )
655 {
656 openUriOption = true;
657 }
658 else if ( option == QLatin1String( "QSQLITE_ENABLE_SHARED_CACHE" ) )
659 {
660 sharedCache = true;
661 }
662 }
663
664 int openMode = ( openReadOnlyOption ? SQLITE_OPEN_READONLY : ( SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE ) );
665 if ( openUriOption )
666 openMode |= SQLITE_OPEN_URI;
667
668 sqlite3_enable_shared_cache( sharedCache );
669
670 if ( d->access.open_v2( db.toUtf8().constData(), openMode, nullptr ) == SQLITE_OK )
671 {
672 sqlite3_busy_timeout( d->access.get(), timeOut );
673 setOpen( true );
674 setOpenError( false );
675
676 return true;
677 }
678 else
679 {
680 setLastError( qMakeError( d->access, tr( "Error opening database" ),
681 QSqlError::ConnectionError ) );
682 setOpenError( true );
683 return false;
684 }
685 }
686
close()687 void QSpatiaLiteDriver::close()
688 {
689 Q_D( QSpatiaLiteDriver );
690 if ( isOpen() )
691 {
692 for ( QSpatiaLiteResult *result : qAsConst( d->results ) )
693 result->d_func()->finalize();
694
695 if ( d->access && ( d->notificationid.count() > 0 ) )
696 {
697 d->notificationid.clear();
698 sqlite3_update_hook( d->access.get(), NULL, NULL );
699 }
700
701 d->access.reset();
702
703 setOpen( false );
704 setOpenError( false );
705 }
706 }
707
createResult() const708 QSqlResult *QSpatiaLiteDriver::createResult() const
709 {
710 return new QSpatiaLiteResult( this );
711 }
712
beginTransaction()713 bool QSpatiaLiteDriver::beginTransaction()
714 {
715 if ( !isOpen() || isOpenError() )
716 return false;
717
718 QSqlQuery q( createResult() );
719 if ( !q.exec( QLatin1String( "BEGIN" ) ) )
720 {
721 setLastError( QSqlError( tr( "Unable to begin transaction" ),
722 q.lastError().databaseText(), QSqlError::TransactionError ) );
723 return false;
724 }
725
726 return true;
727 }
728
commitTransaction()729 bool QSpatiaLiteDriver::commitTransaction()
730 {
731 if ( !isOpen() || isOpenError() )
732 return false;
733
734 QSqlQuery q( createResult() );
735 if ( !q.exec( QLatin1String( "COMMIT" ) ) )
736 {
737 setLastError( QSqlError( tr( "Unable to commit transaction" ),
738 q.lastError().databaseText(), QSqlError::TransactionError ) );
739 return false;
740 }
741
742 return true;
743 }
744
rollbackTransaction()745 bool QSpatiaLiteDriver::rollbackTransaction()
746 {
747 if ( !isOpen() || isOpenError() )
748 return false;
749
750 QSqlQuery q( createResult() );
751 if ( !q.exec( QLatin1String( "ROLLBACK" ) ) )
752 {
753 setLastError( QSqlError( tr( "Unable to rollback transaction" ),
754 q.lastError().databaseText(), QSqlError::TransactionError ) );
755 return false;
756 }
757
758 return true;
759 }
760
tables(QSql::TableType type) const761 QStringList QSpatiaLiteDriver::tables( QSql::TableType type ) const
762 {
763 QStringList res;
764 if ( !isOpen() )
765 return res;
766
767 QSqlQuery q( createResult() );
768 q.setForwardOnly( true );
769
770 QString sql = QLatin1String( "SELECT name FROM sqlite_master WHERE %1 "
771 "UNION ALL SELECT name FROM sqlite_temp_master WHERE %1" );
772 if ( ( type & QSql::Tables ) && ( type & QSql::Views ) )
773 sql = sql.arg( QLatin1String( "type='table' OR type='view'" ) );
774 else if ( type & QSql::Tables )
775 sql = sql.arg( QLatin1String( "type='table'" ) );
776 else if ( type & QSql::Views )
777 sql = sql.arg( QLatin1String( "type='view'" ) );
778 else
779 sql.clear();
780
781 if ( !sql.isEmpty() && q.exec( sql ) )
782 {
783 while ( q.next() )
784 res.append( q.value( 0 ).toString() );
785 }
786
787 if ( type & QSql::SystemTables )
788 {
789 // there are no internal tables beside this one:
790 res.append( QLatin1String( "sqlite_master" ) );
791 }
792
793 return res;
794 }
795
qGetTableInfo(QSqlQuery & q,const QString & tableName,bool onlyPIndex=false)796 static QSqlIndex qGetTableInfo( QSqlQuery &q, const QString &tableName, bool onlyPIndex = false )
797 {
798 QString schema;
799 QString table( tableName );
800 int indexOfSeparator = tableName.indexOf( QLatin1Char( '.' ) );
801 if ( indexOfSeparator > -1 )
802 {
803 schema = tableName.left( indexOfSeparator ).append( QLatin1Char( '.' ) );
804 table = tableName.mid( indexOfSeparator + 1 );
805 }
806 q.exec( QLatin1String( "PRAGMA " ) + schema + QLatin1String( "table_info (" ) + _q_escapeIdentifier( table ) + QLatin1Char( ')' ) );
807
808 QSqlIndex ind;
809 while ( q.next() )
810 {
811 bool isPk = q.value( 5 ).toInt();
812 if ( onlyPIndex && !isPk )
813 continue;
814 QString typeName = q.value( 2 ).toString().toLower();
815 QSqlField fld( q.value( 1 ).toString(), qGetColumnType( typeName ) );
816 if ( isPk && ( typeName == QLatin1String( "integer" ) ) )
817 // INTEGER PRIMARY KEY fields are auto-generated in sqlite
818 // INT PRIMARY KEY is not the same as INTEGER PRIMARY KEY!
819 fld.setAutoValue( true );
820 fld.setRequired( q.value( 3 ).toInt() != 0 );
821 fld.setDefaultValue( q.value( 4 ) );
822 ind.append( fld );
823 }
824 return ind;
825 }
826
primaryIndex(const QString & tblname) const827 QSqlIndex QSpatiaLiteDriver::primaryIndex( const QString &tblname ) const
828 {
829 if ( !isOpen() )
830 return QSqlIndex();
831
832 QString table = tblname;
833 if ( isIdentifierEscaped( table, QSqlDriver::TableName ) )
834 table = stripDelimiters( table, QSqlDriver::TableName );
835
836 QSqlQuery q( createResult() );
837 q.setForwardOnly( true );
838 return qGetTableInfo( q, table, true );
839 }
840
record(const QString & tbl) const841 QSqlRecord QSpatiaLiteDriver::record( const QString &tbl ) const
842 {
843 if ( !isOpen() )
844 return QSqlRecord();
845
846 QString table = tbl;
847 if ( isIdentifierEscaped( table, QSqlDriver::TableName ) )
848 table = stripDelimiters( table, QSqlDriver::TableName );
849
850 QSqlQuery q( createResult() );
851 q.setForwardOnly( true );
852 return qGetTableInfo( q, table );
853 }
854
escapeIdentifier(const QString & identifier,IdentifierType type) const855 QString QSpatiaLiteDriver::escapeIdentifier( const QString &identifier, IdentifierType type ) const
856 {
857 Q_UNUSED( type );
858 return _q_escapeIdentifier( identifier );
859 }
860
handle_sqlite_callback(void * qobj,int aoperation,char const * adbname,char const * atablename,sqlite3_int64 arowid)861 static void handle_sqlite_callback( void *qobj, int aoperation, char const *adbname, char const *atablename,
862 sqlite3_int64 arowid )
863 {
864 Q_UNUSED( aoperation );
865 Q_UNUSED( adbname );
866 QSpatiaLiteDriver *driver = static_cast<QSpatiaLiteDriver *>( qobj );
867 if ( driver )
868 {
869 QMetaObject::invokeMethod( driver, "handleNotification", Qt::QueuedConnection,
870 Q_ARG( QString, QString::fromUtf8( atablename ) ), Q_ARG( qint64, arowid ) );
871 }
872 }
873
subscribeToNotification(const QString & name)874 bool QSpatiaLiteDriver::subscribeToNotification( const QString &name )
875 {
876 Q_D( QSpatiaLiteDriver );
877 if ( !isOpen() )
878 {
879 qWarning( "Database not open." );
880 return false;
881 }
882
883 if ( d->notificationid.contains( name ) )
884 {
885 qWarning( "Already subscribing to '%s'.", qPrintable( name ) );
886 return false;
887 }
888
889 //sqlite supports only one notification callback, so only the first is registered
890 d->notificationid << name;
891 if ( d->notificationid.count() == 1 )
892 sqlite3_update_hook( d->access.get(), &handle_sqlite_callback, reinterpret_cast<void *>( this ) );
893
894 return true;
895 }
896
unsubscribeFromNotification(const QString & name)897 bool QSpatiaLiteDriver::unsubscribeFromNotification( const QString &name )
898 {
899 Q_D( QSpatiaLiteDriver );
900 if ( !isOpen() )
901 {
902 qWarning( "Database not open." );
903 return false;
904 }
905
906 if ( !d->notificationid.contains( name ) )
907 {
908 qWarning( "Not subscribed to '%s'.", qPrintable( name ) );
909 return false;
910 }
911
912 d->notificationid.removeAll( name );
913 if ( d->notificationid.isEmpty() )
914 sqlite3_update_hook( d->access.get(), NULL, NULL );
915
916 return true;
917 }
918
subscribedToNotifications() const919 QStringList QSpatiaLiteDriver::subscribedToNotifications() const
920 {
921 Q_D( const QSpatiaLiteDriver );
922 return d->notificationid;
923 }
924
handleNotification(const QString & tableName,qint64 rowid)925 void QSpatiaLiteDriver::handleNotification( const QString &tableName, qint64 rowid )
926 {
927 Q_D( const QSpatiaLiteDriver );
928 if ( d->notificationid.contains( tableName ) )
929 {
930 emit notification( tableName, QSqlDriver::UnknownSource, QVariant( rowid ) );
931 }
932 }
933
934 QT_END_NAMESPACE
935