1// Copyright (C) 2016 The Qt Company Ltd.
2// SPDX-License-Identifier: LicenseRef-Qt-Commercial OR LGPL-3.0-only OR GPL-2.0-only OR GPL-3.0-only
3
4#include "qsql_sqlite_p.h"
5
6#include <qcoreapplication.h>
7#include <qdatetime.h>
8#include <qdebug.h>
9#include <qlist.h>
10#include <qloggingcategory.h>
11#include <qsqlerror.h>
12#include <qsqlfield.h>
13#include <qsqlindex.h>
14#include <qsqlquery.h>
15#include <QtSql/private/qsqlcachedresult_p.h>
16#include <QtSql/private/qsqldriver_p.h>
17#include <qstringlist.h>
18#include <qvariant.h>
19#if QT_CONFIG(regularexpression)
20#include <qcache.h>
21#include <qregularexpression.h>
22#endif
23#include <QScopedValueRollback>
24
25#if defined Q_OS_WIN
26# include <qt_windows.h>
27#else
28# include <unistd.h>
29#endif
30
31#include <sqlite3.h>
32#include <functional>
33
34Q_DECLARE_OPAQUE_POINTER(sqlite3*)
35Q_DECLARE_METATYPE(sqlite3*)
36
37Q_DECLARE_OPAQUE_POINTER(sqlite3_stmt*)
38Q_DECLARE_METATYPE(sqlite3_stmt*)
39
40QT_BEGIN_NAMESPACE
41
42static Q_LOGGING_CATEGORY(lcSqlite, "qt.sql.sqlite")
43
44using namespace Qt::StringLiterals;
45
46static int qGetColumnType(const QString &tpName)
47{
48 const QString typeName = tpName.toLower();
49
50 if (typeName == "integer"_L1 || typeName == "int"_L1)
51 return QMetaType::Int;
52 if (typeName == "double"_L1
53 || typeName == "float"_L1
54 || typeName == "real"_L1
55 || typeName.startsWith(s: "numeric"_L1))
56 return QMetaType::Double;
57 if (typeName == "blob"_L1)
58 return QMetaType::QByteArray;
59 if (typeName == "boolean"_L1 || typeName == "bool"_L1)
60 return QMetaType::Bool;
61 return QMetaType::QString;
62}
63
64static QSqlError qMakeError(sqlite3 *access, const QString &descr, QSqlError::ErrorType type,
65 int errorCode)
66{
67 return QSqlError(descr,
68 QString(reinterpret_cast<const QChar *>(sqlite3_errmsg16(access))),
69 type, QString::number(errorCode));
70}
71
72class QSQLiteResultPrivate;
73
74class QSQLiteResult : public QSqlCachedResult
75{
76 Q_DECLARE_PRIVATE(QSQLiteResult)
77 friend class QSQLiteDriver;
78
79public:
80 explicit QSQLiteResult(const QSQLiteDriver* db);
81 ~QSQLiteResult();
82 QVariant handle() const override;
83
84protected:
85 bool gotoNext(QSqlCachedResult::ValueCache& row, int idx) override;
86 bool reset(const QString &query) override;
87 bool prepare(const QString &query) override;
88 bool execBatch(bool arrayBind) override;
89 bool exec() override;
90 int size() override;
91 int numRowsAffected() override;
92 QVariant lastInsertId() const override;
93 QSqlRecord record() const override;
94 void detachFromResultSet() override;
95 void virtual_hook(int id, void *data) override;
96};
97
98class QSQLiteDriverPrivate : public QSqlDriverPrivate
99{
100 Q_DECLARE_PUBLIC(QSQLiteDriver)
101
102public:
103 inline QSQLiteDriverPrivate() : QSqlDriverPrivate(QSqlDriver::SQLite) {}
104 bool isIdentifierEscaped(QStringView identifier) const;
105 QSqlIndex getTableInfo(QSqlQuery &query, const QString &tableName,
106 bool onlyPIndex = false) const;
107
108 sqlite3 *access = nullptr;
109 QList<QSQLiteResult *> results;
110 QStringList notificationid;
111};
112
113bool QSQLiteDriverPrivate::isIdentifierEscaped(QStringView identifier) const
114{
115 return identifier.size() > 2
116 && ((identifier.startsWith(c: u'"') && identifier.endsWith(c: u'"'))
117 || (identifier.startsWith(c: u'`') && identifier.endsWith(c: u'`'))
118 || (identifier.startsWith(c: u'[') && identifier.endsWith(c: u']')));
119}
120
121QSqlIndex QSQLiteDriverPrivate::getTableInfo(QSqlQuery &query, const QString &tableName,
122 bool onlyPIndex) const
123{
124 Q_Q(const QSQLiteDriver);
125 QString schema;
126 QString table = q->escapeIdentifier(identifier: tableName, type: QSqlDriver::TableName);
127 const auto indexOfSeparator = table.indexOf(ch: u'.');
128 if (indexOfSeparator > -1) {
129 auto leftName = QStringView{table}.first(n: indexOfSeparator);
130 auto rightName = QStringView{table}.sliced(pos: indexOfSeparator + 1);
131 if (isIdentifierEscaped(identifier: leftName) && isIdentifierEscaped(identifier: rightName)) {
132 schema = leftName.toString() + u'.';
133 table = rightName.toString();
134 }
135 }
136
137 query.exec(query: "PRAGMA "_L1 + schema + "table_info ("_L1 + table + u')');
138 QSqlIndex ind;
139 while (query.next()) {
140 bool isPk = query.value(i: 5).toInt();
141 if (onlyPIndex && !isPk)
142 continue;
143 QString typeName = query.value(i: 2).toString().toLower();
144 QString defVal = query.value(i: 4).toString();
145 if (!defVal.isEmpty() && defVal.at(i: 0) == u'\'') {
146 const int end = defVal.lastIndexOf(c: u'\'');
147 if (end > 0)
148 defVal = defVal.mid(position: 1, n: end - 1);
149 }
150
151 QSqlField fld(query.value(i: 1).toString(), QMetaType(qGetColumnType(tpName: typeName)), tableName);
152 if (isPk && (typeName == "integer"_L1))
153 // INTEGER PRIMARY KEY fields are auto-generated in sqlite
154 // INT PRIMARY KEY is not the same as INTEGER PRIMARY KEY!
155 fld.setAutoValue(true);
156 fld.setRequired(query.value(i: 3).toInt() != 0);
157 fld.setDefaultValue(defVal);
158 ind.append(field: fld);
159 }
160 return ind;
161}
162
163class QSQLiteResultPrivate : public QSqlCachedResultPrivate
164{
165 Q_DECLARE_PUBLIC(QSQLiteResult)
166
167public:
168 Q_DECLARE_SQLDRIVER_PRIVATE(QSQLiteDriver)
169 using QSqlCachedResultPrivate::QSqlCachedResultPrivate;
170 void cleanup();
171 bool fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch);
172 // initializes the recordInfo and the cache
173 void initColumns(bool emptyResultset);
174 void finalize();
175
176 sqlite3_stmt *stmt = nullptr;
177 QSqlRecord rInf;
178 QList<QVariant> firstRow;
179 bool skippedStatus = false; // the status of the fetchNext() that's skipped
180 bool skipRow = false; // skip the next fetchNext()?
181};
182
183void QSQLiteResultPrivate::cleanup()
184{
185 Q_Q(QSQLiteResult);
186 finalize();
187 rInf.clear();
188 skippedStatus = false;
189 skipRow = false;
190 q->setAt(QSql::BeforeFirstRow);
191 q->setActive(false);
192 q->cleanup();
193}
194
195void QSQLiteResultPrivate::finalize()
196{
197 if (!stmt)
198 return;
199
200 sqlite3_finalize(pStmt: stmt);
201 stmt = nullptr;
202}
203
204void QSQLiteResultPrivate::initColumns(bool emptyResultset)
205{
206 Q_Q(QSQLiteResult);
207 int nCols = sqlite3_column_count(pStmt: stmt);
208 if (nCols <= 0)
209 return;
210
211 q->init(colCount: nCols);
212
213 for (int i = 0; i < nCols; ++i) {
214 QString colName = QString(reinterpret_cast<const QChar *>(
215 sqlite3_column_name16(stmt, N: i))
216 ).remove(c: u'"');
217 const QString tableName = QString(reinterpret_cast<const QChar *>(
218 sqlite3_column_table_name16(stmt, i))
219 ).remove(c: u'"');
220 // must use typeName for resolving the type to match QSqliteDriver::record
221 QString typeName = QString(reinterpret_cast<const QChar *>(
222 sqlite3_column_decltype16(stmt, i)));
223 // sqlite3_column_type is documented to have undefined behavior if the result set is empty
224 int stp = emptyResultset ? -1 : sqlite3_column_type(stmt, iCol: i);
225
226 int fieldType;
227
228 if (!typeName.isEmpty()) {
229 fieldType = qGetColumnType(tpName: typeName);
230 } else {
231 // Get the proper type for the field based on stp value
232 switch (stp) {
233 case SQLITE_INTEGER:
234 fieldType = QMetaType::Int;
235 break;
236 case SQLITE_FLOAT:
237 fieldType = QMetaType::Double;
238 break;
239 case SQLITE_BLOB:
240 fieldType = QMetaType::QByteArray;
241 break;
242 case SQLITE_TEXT:
243 fieldType = QMetaType::QString;
244 break;
245 case SQLITE_NULL:
246 default:
247 fieldType = QMetaType::UnknownType;
248 break;
249 }
250 }
251
252 QSqlField fld(colName, QMetaType(fieldType), tableName);
253 rInf.append(field: fld);
254 }
255}
256
257bool QSQLiteResultPrivate::fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch)
258{
259 Q_Q(QSQLiteResult);
260
261 if (skipRow) {
262 // already fetched
263 Q_ASSERT(!initialFetch);
264 skipRow = false;
265 for(int i=0;i<firstRow.size();i++)
266 values[i]=firstRow[i];
267 return skippedStatus;
268 }
269 skipRow = initialFetch;
270
271 if (initialFetch) {
272 firstRow.clear();
273 firstRow.resize(size: sqlite3_column_count(pStmt: stmt));
274 }
275
276 if (!stmt) {
277 q->setLastError(QSqlError(QCoreApplication::translate(context: "QSQLiteResult", key: "Unable to fetch row"),
278 QCoreApplication::translate(context: "QSQLiteResult", key: "No query"), QSqlError::ConnectionError));
279 q->setAt(QSql::AfterLastRow);
280 return false;
281 }
282 int res = sqlite3_step(stmt);
283 switch(res) {
284 case SQLITE_ROW:
285 // check to see if should fill out columns
286 if (rInf.isEmpty())
287 // must be first call.
288 initColumns(emptyResultset: false);
289 if (idx < 0 && !initialFetch)
290 return true;
291 for (int i = 0; i < rInf.count(); ++i) {
292 switch (sqlite3_column_type(stmt, iCol: i)) {
293 case SQLITE_BLOB:
294 values[i + idx] = QByteArray(static_cast<const char *>(
295 sqlite3_column_blob(stmt, iCol: i)),
296 sqlite3_column_bytes(stmt, iCol: i));
297 break;
298 case SQLITE_INTEGER:
299 values[i + idx] = sqlite3_column_int64(stmt, iCol: i);
300 break;
301 case SQLITE_FLOAT:
302 switch(q->numericalPrecisionPolicy()) {
303 case QSql::LowPrecisionInt32:
304 values[i + idx] = sqlite3_column_int(stmt, iCol: i);
305 break;
306 case QSql::LowPrecisionInt64:
307 values[i + idx] = sqlite3_column_int64(stmt, iCol: i);
308 break;
309 case QSql::LowPrecisionDouble:
310 case QSql::HighPrecision:
311 default:
312 values[i + idx] = sqlite3_column_double(stmt, iCol: i);
313 break;
314 };
315 break;
316 case SQLITE_NULL:
317 values[i + idx] = QVariant(QMetaType::fromType<QString>());
318 break;
319 default:
320 values[i + idx] = QString(reinterpret_cast<const QChar *>(
321 sqlite3_column_text16(stmt, iCol: i)),
322 sqlite3_column_bytes16(stmt, iCol: i) / sizeof(QChar));
323 break;
324 }
325 }
326 return true;
327 case SQLITE_DONE:
328 if (rInf.isEmpty())
329 // must be first call.
330 initColumns(emptyResultset: true);
331 q->setAt(QSql::AfterLastRow);
332 sqlite3_reset(pStmt: stmt);
333 return false;
334 case SQLITE_CONSTRAINT:
335 case SQLITE_ERROR:
336 // SQLITE_ERROR is a generic error code and we must call sqlite3_reset()
337 // to get the specific error message.
338 res = sqlite3_reset(pStmt: stmt);
339 q->setLastError(qMakeError(access: drv_d_func()->access, descr: QCoreApplication::translate(context: "QSQLiteResult",
340 key: "Unable to fetch row"), type: QSqlError::ConnectionError, errorCode: res));
341 q->setAt(QSql::AfterLastRow);
342 return false;
343 case SQLITE_MISUSE:
344 case SQLITE_BUSY:
345 default:
346 // something wrong, don't get col info, but still return false
347 q->setLastError(qMakeError(access: drv_d_func()->access, descr: QCoreApplication::translate(context: "QSQLiteResult",
348 key: "Unable to fetch row"), type: QSqlError::ConnectionError, errorCode: res));
349 sqlite3_reset(pStmt: stmt);
350 q->setAt(QSql::AfterLastRow);
351 return false;
352 }
353 return false;
354}
355
356QSQLiteResult::QSQLiteResult(const QSQLiteDriver* db)
357 : QSqlCachedResult(*new QSQLiteResultPrivate(this, db))
358{
359 Q_D(QSQLiteResult);
360 const_cast<QSQLiteDriverPrivate*>(d->drv_d_func())->results.append(t: this);
361}
362
363QSQLiteResult::~QSQLiteResult()
364{
365 Q_D(QSQLiteResult);
366 if (d->drv_d_func())
367 const_cast<QSQLiteDriverPrivate*>(d->drv_d_func())->results.removeOne(t: this);
368 d->cleanup();
369}
370
371void QSQLiteResult::virtual_hook(int id, void *data)
372{
373 QSqlCachedResult::virtual_hook(id, data);
374}
375
376bool QSQLiteResult::reset(const QString &query)
377{
378 if (!prepare(query))
379 return false;
380 return exec();
381}
382
383bool QSQLiteResult::prepare(const QString &query)
384{
385 Q_D(QSQLiteResult);
386 if (!driver() || !driver()->isOpen() || driver()->isOpenError())
387 return false;
388
389 d->cleanup();
390
391 setSelect(false);
392
393 const void *pzTail = nullptr;
394 const auto size = int((query.size() + 1) * sizeof(QChar));
395
396#if (SQLITE_VERSION_NUMBER >= 3003011)
397 int res = sqlite3_prepare16_v2(db: d->drv_d_func()->access, zSql: query.constData(), nByte: size,
398 ppStmt: &d->stmt, pzTail: &pzTail);
399#else
400 int res = sqlite3_prepare16(d->access, query.constData(), size,
401 &d->stmt, &pzTail);
402#endif
403
404 if (res != SQLITE_OK) {
405 setLastError(qMakeError(access: d->drv_d_func()->access, descr: QCoreApplication::translate(context: "QSQLiteResult",
406 key: "Unable to execute statement"), type: QSqlError::StatementError, errorCode: res));
407 d->finalize();
408 return false;
409 } else if (pzTail && !QString(reinterpret_cast<const QChar *>(pzTail)).trimmed().isEmpty()) {
410 setLastError(qMakeError(access: d->drv_d_func()->access, descr: QCoreApplication::translate(context: "QSQLiteResult",
411 key: "Unable to execute multiple statements at a time"), type: QSqlError::StatementError, SQLITE_MISUSE));
412 d->finalize();
413 return false;
414 }
415 return true;
416}
417
418bool QSQLiteResult::execBatch(bool arrayBind)
419{
420 Q_UNUSED(arrayBind);
421 Q_D(QSqlResult);
422 QScopedValueRollback<QList<QVariant>> valuesScope(d->values);
423 QList<QVariant> values = d->values;
424 if (values.size() == 0)
425 return false;
426
427 for (int i = 0; i < values.at(i: 0).toList().size(); ++i) {
428 d->values.clear();
429 QScopedValueRollback<QHash<QString, QList<int>>> indexesScope(d->indexes);
430 auto it = d->indexes.constBegin();
431 while (it != d->indexes.constEnd()) {
432 bindValue(placeholder: it.key(), val: values.at(i: it.value().first()).toList().at(i), type: QSql::In);
433 ++it;
434 }
435 if (!exec())
436 return false;
437 }
438 return true;
439}
440
441bool QSQLiteResult::exec()
442{
443 Q_D(QSQLiteResult);
444 QList<QVariant> values = boundValues();
445
446 d->skippedStatus = false;
447 d->skipRow = false;
448 d->rInf.clear();
449 clearValues();
450 setLastError(QSqlError());
451
452 int res = sqlite3_reset(pStmt: d->stmt);
453 if (res != SQLITE_OK) {
454 setLastError(qMakeError(access: d->drv_d_func()->access, descr: QCoreApplication::translate(context: "QSQLiteResult",
455 key: "Unable to reset statement"), type: QSqlError::StatementError, errorCode: res));
456 d->finalize();
457 return false;
458 }
459
460 int paramCount = sqlite3_bind_parameter_count(d->stmt);
461 bool paramCountIsValid = paramCount == values.size();
462
463#if (SQLITE_VERSION_NUMBER >= 3003011)
464 // In the case of the reuse of a named placeholder
465 // We need to check explicitly that paramCount is greater than or equal to 1, as sqlite
466 // can end up in a case where for virtual tables it returns 0 even though it
467 // has parameters
468 if (paramCount >= 1 && paramCount < values.size()) {
469 const auto countIndexes = [](int counter, const QList<int> &indexList) {
470 return counter + indexList.size();
471 };
472
473 const int bindParamCount = std::accumulate(first: d->indexes.cbegin(),
474 last: d->indexes.cend(),
475 init: 0,
476 binary_op: countIndexes);
477
478 paramCountIsValid = bindParamCount == values.size();
479 // When using named placeholders, it will reuse the index for duplicated
480 // placeholders. So we need to ensure the QList has only one instance of
481 // each value as SQLite will do the rest for us.
482 QList<QVariant> prunedValues;
483 QList<int> handledIndexes;
484 for (int i = 0, currentIndex = 0; i < values.size(); ++i) {
485 if (handledIndexes.contains(t: i))
486 continue;
487 const char *parameterName = sqlite3_bind_parameter_name(d->stmt, currentIndex + 1);
488 if (!parameterName) {
489 paramCountIsValid = false;
490 continue;
491 }
492 const auto placeHolder = QString::fromUtf8(utf8: parameterName);
493 const auto &indexes = d->indexes.value(key: placeHolder);
494 handledIndexes << indexes;
495 prunedValues << values.at(i: indexes.first());
496 ++currentIndex;
497 }
498 values = prunedValues;
499 }
500#endif
501
502 if (paramCountIsValid) {
503 for (int i = 0; i < paramCount; ++i) {
504 res = SQLITE_OK;
505 const QVariant &value = values.at(i);
506
507 if (QSqlResultPrivate::isVariantNull(variant: value)) {
508 res = sqlite3_bind_null(d->stmt, i + 1);
509 } else {
510 switch (value.userType()) {
511 case QMetaType::QByteArray: {
512 const QByteArray *ba = static_cast<const QByteArray*>(value.constData());
513 res = sqlite3_bind_blob(d->stmt, i + 1, ba->constData(),
514 n: ba->size(), SQLITE_STATIC);
515 break; }
516 case QMetaType::Int:
517 case QMetaType::Bool:
518 res = sqlite3_bind_int(d->stmt, i + 1, value.toInt());
519 break;
520 case QMetaType::Double:
521 res = sqlite3_bind_double(d->stmt, i + 1, value.toDouble());
522 break;
523 case QMetaType::UInt:
524 case QMetaType::LongLong:
525 res = sqlite3_bind_int64(d->stmt, i + 1, value.toLongLong());
526 break;
527 case QMetaType::QDateTime: {
528 const QDateTime dateTime = value.toDateTime();
529 const QString str = dateTime.toString(format: Qt::ISODateWithMs);
530 res = sqlite3_bind_text16(d->stmt, i + 1, str.data(),
531 int(str.size() * sizeof(ushort)),
532 SQLITE_TRANSIENT);
533 break;
534 }
535 case QMetaType::QTime: {
536 const QTime time = value.toTime();
537 const QString str = time.toString(format: u"hh:mm:ss.zzz");
538 res = sqlite3_bind_text16(d->stmt, i + 1, str.data(),
539 int(str.size() * sizeof(ushort)),
540 SQLITE_TRANSIENT);
541 break;
542 }
543 case QMetaType::QString: {
544 // lifetime of string == lifetime of its qvariant
545 const QString *str = static_cast<const QString*>(value.constData());
546 res = sqlite3_bind_text16(d->stmt, i + 1, str->unicode(),
547 int(str->size()) * sizeof(QChar),
548 SQLITE_STATIC);
549 break; }
550 default: {
551 const QString str = value.toString();
552 // SQLITE_TRANSIENT makes sure that sqlite buffers the data
553 res = sqlite3_bind_text16(d->stmt, i + 1, str.data(),
554 int(str.size()) * sizeof(QChar),
555 SQLITE_TRANSIENT);
556 break; }
557 }
558 }
559 if (res != SQLITE_OK) {
560 setLastError(qMakeError(access: d->drv_d_func()->access, descr: QCoreApplication::translate(context: "QSQLiteResult",
561 key: "Unable to bind parameters"), type: QSqlError::StatementError, errorCode: res));
562 d->finalize();
563 return false;
564 }
565 }
566 } else {
567 setLastError(QSqlError(QCoreApplication::translate(context: "QSQLiteResult",
568 key: "Parameter count mismatch"), QString(), QSqlError::StatementError));
569 return false;
570 }
571 d->skippedStatus = d->fetchNext(values&: d->firstRow, idx: 0, initialFetch: true);
572 if (lastError().isValid()) {
573 setSelect(false);
574 setActive(false);
575 return false;
576 }
577 setSelect(!d->rInf.isEmpty());
578 setActive(true);
579 return true;
580}
581
582bool QSQLiteResult::gotoNext(QSqlCachedResult::ValueCache& row, int idx)
583{
584 Q_D(QSQLiteResult);
585 return d->fetchNext(values&: row, idx, initialFetch: false);
586}
587
588int QSQLiteResult::size()
589{
590 return -1;
591}
592
593int QSQLiteResult::numRowsAffected()
594{
595 Q_D(const QSQLiteResult);
596 return sqlite3_changes(d->drv_d_func()->access);
597}
598
599QVariant QSQLiteResult::lastInsertId() const
600{
601 Q_D(const QSQLiteResult);
602 if (isActive()) {
603 qint64 id = sqlite3_last_insert_rowid(d->drv_d_func()->access);
604 if (id)
605 return id;
606 }
607 return QVariant();
608}
609
610QSqlRecord QSQLiteResult::record() const
611{
612 Q_D(const QSQLiteResult);
613 if (!isActive() || !isSelect())
614 return QSqlRecord();
615 return d->rInf;
616}
617
618void QSQLiteResult::detachFromResultSet()
619{
620 Q_D(QSQLiteResult);
621 if (d->stmt)
622 sqlite3_reset(pStmt: d->stmt);
623}
624
625QVariant QSQLiteResult::handle() const
626{
627 Q_D(const QSQLiteResult);
628 return QVariant::fromValue(value: d->stmt);
629}
630
631/////////////////////////////////////////////////////////
632
633#if QT_CONFIG(regularexpression)
634static void _q_regexp(sqlite3_context* context, int argc, sqlite3_value** argv)
635{
636 if (Q_UNLIKELY(argc != 2)) {
637 sqlite3_result_int(context, 0);
638 return;
639 }
640
641 const QString pattern = QString::fromUtf8(
642 utf8: reinterpret_cast<const char*>(sqlite3_value_text(argv[0])));
643 const QString subject = QString::fromUtf8(
644 utf8: reinterpret_cast<const char*>(sqlite3_value_text(argv[1])));
645
646 auto cache = static_cast<QCache<QString, QRegularExpression>*>(sqlite3_user_data(context));
647 auto regexp = cache->object(key: pattern);
648 const bool wasCached = regexp;
649
650 if (!wasCached)
651 regexp = new QRegularExpression(pattern, QRegularExpression::DontCaptureOption);
652
653 const bool found = subject.contains(re: *regexp);
654
655 if (!wasCached)
656 cache->insert(key: pattern, object: regexp);
657
658 sqlite3_result_int(context, int(found));
659}
660
661static void _q_regexp_cleanup(void *cache)
662{
663 delete static_cast<QCache<QString, QRegularExpression>*>(cache);
664}
665#endif
666
667static void _q_lower(sqlite3_context* context, int argc, sqlite3_value** argv)
668{
669 if (Q_UNLIKELY(argc != 1)) {
670 sqlite3_result_text(context, nullptr, 0, nullptr);
671 return;
672 }
673 const QString lower = QString::fromUtf8(
674 utf8: reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))).toLower();
675 const QByteArray ba = lower.toUtf8();
676 sqlite3_result_text(context, ba.data(), ba.size(), SQLITE_TRANSIENT);
677}
678
679static void _q_upper(sqlite3_context* context, int argc, sqlite3_value** argv)
680{
681 if (Q_UNLIKELY(argc != 1)) {
682 sqlite3_result_text(context, nullptr, 0, nullptr);
683 return;
684 }
685 const QString upper = QString::fromUtf8(
686 utf8: reinterpret_cast<const char*>(sqlite3_value_text(argv[0]))).toUpper();
687 const QByteArray ba = upper.toUtf8();
688 sqlite3_result_text(context, ba.data(), ba.size(), SQLITE_TRANSIENT);
689}
690
691QSQLiteDriver::QSQLiteDriver(QObject * parent)
692 : QSqlDriver(*new QSQLiteDriverPrivate, parent)
693{
694}
695
696QSQLiteDriver::QSQLiteDriver(sqlite3 *connection, QObject *parent)
697 : QSqlDriver(*new QSQLiteDriverPrivate, parent)
698{
699 Q_D(QSQLiteDriver);
700 d->access = connection;
701 setOpen(true);
702 setOpenError(false);
703}
704
705
706QSQLiteDriver::~QSQLiteDriver()
707{
708 close();
709}
710
711bool QSQLiteDriver::hasFeature(DriverFeature f) const
712{
713 switch (f) {
714 case BLOB:
715 case Transactions:
716 case Unicode:
717 case LastInsertId:
718 case PreparedQueries:
719 case PositionalPlaceholders:
720 case SimpleLocking:
721 case FinishQuery:
722 case LowPrecisionNumbers:
723 case EventNotifications:
724 return true;
725 case QuerySize:
726 case BatchOperations:
727 case MultipleResultSets:
728 case CancelQuery:
729 return false;
730 case NamedPlaceholders:
731#if (SQLITE_VERSION_NUMBER < 3003011)
732 return false;
733#else
734 return true;
735#endif
736
737 }
738 return false;
739}
740
741/*
742 SQLite dbs have no user name, passwords, hosts or ports.
743 just file names.
744*/
745bool QSQLiteDriver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &conOpts)
746{
747 Q_D(QSQLiteDriver);
748 if (isOpen())
749 close();
750
751
752 int timeOut = 5000;
753 bool sharedCache = false;
754 bool openReadOnlyOption = false;
755 bool openUriOption = false;
756 bool useExtendedResultCodes = true;
757 bool useQtVfs = false;
758 bool useQtCaseFolding = false;
759 bool openNoFollow = false;
760#if QT_CONFIG(regularexpression)
761 static const auto regexpConnectOption = "QSQLITE_ENABLE_REGEXP"_L1;
762 bool defineRegexp = false;
763 int regexpCacheSize = 25;
764#endif
765
766 const auto opts = QStringView{conOpts}.split(sep: u';', behavior: Qt::SkipEmptyParts);
767 for (auto option : opts) {
768 option = option.trimmed();
769 if (option.startsWith(s: "QSQLITE_BUSY_TIMEOUT"_L1)) {
770 option = option.mid(pos: 20).trimmed();
771 if (option.startsWith(c: u'=')) {
772 bool ok;
773 const int nt = option.mid(pos: 1).trimmed().toInt(ok: &ok);
774 if (ok)
775 timeOut = nt;
776 }
777 } else if (option == "QSQLITE_USE_QT_VFS"_L1) {
778 useQtVfs = true;
779 } else if (option == "QSQLITE_OPEN_READONLY"_L1) {
780 openReadOnlyOption = true;
781 } else if (option == "QSQLITE_OPEN_URI"_L1) {
782 openUriOption = true;
783 } else if (option == "QSQLITE_ENABLE_SHARED_CACHE"_L1) {
784 sharedCache = true;
785 } else if (option == "QSQLITE_NO_USE_EXTENDED_RESULT_CODES"_L1) {
786 useExtendedResultCodes = false;
787 } else if (option == "QSQLITE_ENABLE_NON_ASCII_CASE_FOLDING"_L1) {
788 useQtCaseFolding = true;
789 } else if (option == "QSQLITE_OPEN_NOFOLLOW"_L1) {
790 openNoFollow = true;
791 }
792#if QT_CONFIG(regularexpression)
793 else if (option.startsWith(s: regexpConnectOption)) {
794 option = option.mid(pos: regexpConnectOption.size()).trimmed();
795 if (option.isEmpty()) {
796 defineRegexp = true;
797 } else if (option.startsWith(c: u'=')) {
798 bool ok = false;
799 const int cacheSize = option.mid(pos: 1).trimmed().toInt(ok: &ok);
800 if (ok) {
801 defineRegexp = true;
802 if (cacheSize > 0)
803 regexpCacheSize = cacheSize;
804 }
805 }
806 }
807#endif
808 else
809 qCWarning(lcSqlite, "Unsupported option '%ls'", qUtf16Printable(option.toString()));
810 }
811
812 int openMode = (openReadOnlyOption ? SQLITE_OPEN_READONLY : (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE));
813 openMode |= (sharedCache ? SQLITE_OPEN_SHAREDCACHE : SQLITE_OPEN_PRIVATECACHE);
814 if (openUriOption)
815 openMode |= SQLITE_OPEN_URI;
816 if (openNoFollow) {
817#if defined(SQLITE_OPEN_NOFOLLOW)
818 openMode |= SQLITE_OPEN_NOFOLLOW;
819#else
820 qCWarning(lcSqlite, "SQLITE_OPEN_NOFOLLOW not supported with the SQLite version %s", sqlite3_libversion());
821#endif
822 }
823
824 openMode |= SQLITE_OPEN_NOMUTEX;
825
826 const int res = sqlite3_open_v2(filename: db.toUtf8().constData(), ppDb: &d->access, flags: openMode, zVfs: useQtVfs ? "QtVFS" : nullptr);
827
828 if (res == SQLITE_OK) {
829 sqlite3_busy_timeout(d->access, ms: timeOut);
830 sqlite3_extended_result_codes(d->access, onoff: useExtendedResultCodes);
831 setOpen(true);
832 setOpenError(false);
833#if QT_CONFIG(regularexpression)
834 if (defineRegexp) {
835 auto cache = new QCache<QString, QRegularExpression>(regexpCacheSize);
836 sqlite3_create_function_v2(db: d->access, zFunctionName: "regexp", nArg: 2, SQLITE_UTF8, pApp: cache,
837 xFunc: &_q_regexp, xStep: nullptr,
838 xFinal: nullptr, xDestroy: &_q_regexp_cleanup);
839 }
840#endif
841 if (useQtCaseFolding) {
842 sqlite3_create_function_v2(db: d->access, zFunctionName: "lower", nArg: 1, SQLITE_UTF8, pApp: nullptr,
843 xFunc: &_q_lower, xStep: nullptr, xFinal: nullptr, xDestroy: nullptr);
844 sqlite3_create_function_v2(db: d->access, zFunctionName: "upper", nArg: 1, SQLITE_UTF8, pApp: nullptr,
845 xFunc: &_q_upper, xStep: nullptr, xFinal: nullptr, xDestroy: nullptr);
846 }
847 return true;
848 } else {
849 setLastError(qMakeError(access: d->access, descr: tr(s: "Error opening database"),
850 type: QSqlError::ConnectionError, errorCode: res));
851 setOpenError(true);
852
853 if (d->access) {
854 sqlite3_close(d->access);
855 d->access = nullptr;
856 }
857
858 return false;
859 }
860}
861
862void QSQLiteDriver::close()
863{
864 Q_D(QSQLiteDriver);
865 if (isOpen()) {
866 for (QSQLiteResult *result : std::as_const(t&: d->results))
867 result->d_func()->finalize();
868
869 if (d->access && (d->notificationid.size() > 0)) {
870 d->notificationid.clear();
871 sqlite3_update_hook(d->access, nullptr, nullptr);
872 }
873
874 const int res = sqlite3_close(d->access);
875
876 if (res != SQLITE_OK)
877 setLastError(qMakeError(access: d->access, descr: tr(s: "Error closing database"), type: QSqlError::ConnectionError, errorCode: res));
878 d->access = nullptr;
879 setOpen(false);
880 setOpenError(false);
881 }
882}
883
884QSqlResult *QSQLiteDriver::createResult() const
885{
886 return new QSQLiteResult(this);
887}
888
889bool QSQLiteDriver::beginTransaction()
890{
891 if (!isOpen() || isOpenError())
892 return false;
893
894 QSqlQuery q(createResult());
895 if (!q.exec(query: "BEGIN"_L1)) {
896 setLastError(QSqlError(tr(s: "Unable to begin transaction"),
897 q.lastError().databaseText(), QSqlError::TransactionError));
898 return false;
899 }
900
901 return true;
902}
903
904bool QSQLiteDriver::commitTransaction()
905{
906 if (!isOpen() || isOpenError())
907 return false;
908
909 QSqlQuery q(createResult());
910 if (!q.exec(query: "COMMIT"_L1)) {
911 setLastError(QSqlError(tr(s: "Unable to commit transaction"),
912 q.lastError().databaseText(), QSqlError::TransactionError));
913 return false;
914 }
915
916 return true;
917}
918
919bool QSQLiteDriver::rollbackTransaction()
920{
921 if (!isOpen() || isOpenError())
922 return false;
923
924 QSqlQuery q(createResult());
925 if (!q.exec(query: "ROLLBACK"_L1)) {
926 setLastError(QSqlError(tr(s: "Unable to rollback transaction"),
927 q.lastError().databaseText(), QSqlError::TransactionError));
928 return false;
929 }
930
931 return true;
932}
933
934QStringList QSQLiteDriver::tables(QSql::TableType type) const
935{
936 QStringList res;
937 if (!isOpen())
938 return res;
939
940 QSqlQuery q(createResult());
941 q.setForwardOnly(true);
942
943 QString sql = "SELECT name FROM sqlite_master WHERE %1 "
944 "UNION ALL SELECT name FROM sqlite_temp_master WHERE %1"_L1;
945 if ((type & QSql::Tables) && (type & QSql::Views))
946 sql = sql.arg(a: "type='table' OR type='view'"_L1);
947 else if (type & QSql::Tables)
948 sql = sql.arg(a: "type='table'"_L1);
949 else if (type & QSql::Views)
950 sql = sql.arg(a: "type='view'"_L1);
951 else
952 sql.clear();
953
954 if (!sql.isEmpty() && q.exec(query: sql)) {
955 while(q.next())
956 res.append(t: q.value(i: 0).toString());
957 }
958
959 if (type & QSql::SystemTables) {
960 // there are no internal tables beside this one:
961 res.append(t: "sqlite_master"_L1);
962 }
963
964 return res;
965}
966
967QSqlIndex QSQLiteDriver::primaryIndex(const QString &tablename) const
968{
969 Q_D(const QSQLiteDriver);
970 if (!isOpen())
971 return QSqlIndex();
972
973 QSqlQuery q(createResult());
974 q.setForwardOnly(true);
975 return d->getTableInfo(query&: q, tableName: tablename, onlyPIndex: true);
976}
977
978QSqlRecord QSQLiteDriver::record(const QString &tablename) const
979{
980 Q_D(const QSQLiteDriver);
981 if (!isOpen())
982 return QSqlRecord();
983
984 QSqlQuery q(createResult());
985 q.setForwardOnly(true);
986 return d->getTableInfo(query&: q, tableName: tablename);
987}
988
989QVariant QSQLiteDriver::handle() const
990{
991 Q_D(const QSQLiteDriver);
992 return QVariant::fromValue(value: d->access);
993}
994
995QString QSQLiteDriver::escapeIdentifier(const QString &identifier, IdentifierType type) const
996{
997 Q_D(const QSQLiteDriver);
998 if (identifier.isEmpty() || isIdentifierEscaped(identifier, type))
999 return identifier;
1000
1001 const auto indexOfSeparator = identifier.indexOf(ch: u'.');
1002 if (indexOfSeparator > -1) {
1003 auto leftName = QStringView{identifier}.first(n: indexOfSeparator);
1004 auto rightName = QStringView{identifier}.sliced(pos: indexOfSeparator + 1);
1005 const QStringView leftEnclose = d->isIdentifierEscaped(identifier: leftName) ? u"" : u"\"";
1006 const QStringView rightEnclose = d->isIdentifierEscaped(identifier: rightName) ? u"" : u"\"";
1007 if (leftEnclose.isEmpty() || rightEnclose.isEmpty())
1008 return (leftEnclose + leftName + leftEnclose + u'.' + rightEnclose + rightName
1009 + rightEnclose);
1010 }
1011 return u'"' + identifier + u'"';
1012}
1013
1014bool QSQLiteDriver::isIdentifierEscaped(const QString &identifier, IdentifierType type) const
1015{
1016 Q_D(const QSQLiteDriver);
1017 Q_UNUSED(type);
1018 return d->isIdentifierEscaped(identifier: QStringView{identifier});
1019}
1020
1021QString QSQLiteDriver::stripDelimiters(const QString &identifier, IdentifierType type) const
1022{
1023 Q_D(const QSQLiteDriver);
1024 const auto indexOfSeparator = identifier.indexOf(ch: u'.');
1025 if (indexOfSeparator > -1) {
1026 auto leftName = QStringView{identifier}.first(n: indexOfSeparator);
1027 auto rightName = QStringView{identifier}.sliced(pos: indexOfSeparator + 1);
1028 const auto leftEscaped = d->isIdentifierEscaped(identifier: leftName);
1029 const auto rightEscaped = d->isIdentifierEscaped(identifier: rightName);
1030 if (leftEscaped || rightEscaped) {
1031 if (leftEscaped)
1032 leftName = leftName.sliced(pos: 1).chopped(n: 1);
1033 if (rightEscaped)
1034 rightName = rightName.sliced(pos: 1).chopped(n: 1);
1035 return leftName + u'.' + rightName;
1036 }
1037 }
1038
1039 if (isIdentifierEscaped(identifier, type))
1040 return identifier.mid(position: 1, n: identifier.size() - 2);
1041
1042 return identifier;
1043}
1044
1045static void handle_sqlite_callback(void *qobj,int aoperation, char const *adbname, char const *atablename,
1046 sqlite3_int64 arowid)
1047{
1048 Q_UNUSED(aoperation);
1049 Q_UNUSED(adbname);
1050 QSQLiteDriver *driver = static_cast<QSQLiteDriver *>(qobj);
1051 if (driver) {
1052 QMetaObject::invokeMethod(obj: driver, member: "handleNotification", c: Qt::QueuedConnection,
1053 Q_ARG(QString, QString::fromUtf8(atablename)), Q_ARG(qint64, arowid));
1054 }
1055}
1056
1057bool QSQLiteDriver::subscribeToNotification(const QString &name)
1058{
1059 Q_D(QSQLiteDriver);
1060 if (!isOpen()) {
1061 qCWarning(lcSqlite, "QSQLiteDriver::subscribeToNotification: Database not open.");
1062 return false;
1063 }
1064
1065 if (d->notificationid.contains(str: name)) {
1066 qCWarning(lcSqlite, "QSQLiteDriver::subscribeToNotification: Already subscribing to '%ls'.",
1067 qUtf16Printable(name));
1068 return false;
1069 }
1070
1071 //sqlite supports only one notification callback, so only the first is registered
1072 d->notificationid << name;
1073 if (d->notificationid.size() == 1)
1074 sqlite3_update_hook(d->access, &handle_sqlite_callback, reinterpret_cast<void *> (this));
1075
1076 return true;
1077}
1078
1079bool QSQLiteDriver::unsubscribeFromNotification(const QString &name)
1080{
1081 Q_D(QSQLiteDriver);
1082 if (!isOpen()) {
1083 qCWarning(lcSqlite, "QSQLiteDriver::unsubscribeFromNotification: Database not open.");
1084 return false;
1085 }
1086
1087 if (!d->notificationid.contains(str: name)) {
1088 qCWarning(lcSqlite, "QSQLiteDriver::unsubscribeFromNotification: Not subscribed to '%ls'.",
1089 qUtf16Printable(name));
1090 return false;
1091 }
1092
1093 d->notificationid.removeAll(t: name);
1094 if (d->notificationid.isEmpty())
1095 sqlite3_update_hook(d->access, nullptr, nullptr);
1096
1097 return true;
1098}
1099
1100QStringList QSQLiteDriver::subscribedToNotifications() const
1101{
1102 Q_D(const QSQLiteDriver);
1103 return d->notificationid;
1104}
1105
1106void QSQLiteDriver::handleNotification(const QString &tableName, qint64 rowid)
1107{
1108 Q_D(const QSQLiteDriver);
1109 if (d->notificationid.contains(str: tableName))
1110 emit notification(name: tableName, source: QSqlDriver::UnknownSource, payload: QVariant(rowid));
1111}
1112
1113QT_END_NAMESPACE
1114
1115#include "moc_qsql_sqlite_p.cpp"
1116

source code of qtbase/src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp