source: trunk/src/sql/drivers/sqlite/qsql_sqlite.cpp

Last change on this file was 846, checked in by Dmitry A. Kuminov, 14 years ago

trunk: Merged in qt 4.7.2 sources from branches/vendor/nokia/qt.
File size: 21.2 KB
Line 
1/****************************************************************************
2**
3** Copyright (C) 2011 Nokia Corporation and/or its subsidiary(-ies).
4** All rights reserved.
5** Contact: Nokia Corporation (qt-info@nokia.com)
6**
7** This file is part of the QtSql module of the Qt Toolkit.
8**
9** $QT_BEGIN_LICENSE:LGPL$
10** Commercial Usage
11** Licensees holding valid Qt Commercial licenses may use this file in
12** accordance with the Qt Commercial License Agreement provided with the
13** Software or, alternatively, in accordance with the terms contained in
14** a written agreement between you and Nokia.
15**
16** GNU Lesser General Public License Usage
17** Alternatively, this file may be used under the terms of the GNU Lesser
18** General Public License version 2.1 as published by the Free Software
19** Foundation and appearing in the file LICENSE.LGPL included in the
20** packaging of this file. Please review the following information to
21** ensure the GNU Lesser General Public License version 2.1 requirements
22** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
23**
24** In addition, as a special exception, Nokia gives you certain additional
25** rights. These rights are described in the Nokia Qt LGPL Exception
26** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
27**
28** GNU General Public License Usage
29** Alternatively, this file may be used under the terms of the GNU
30** General Public License version 3.0 as published by the Free Software
31** Foundation and appearing in the file LICENSE.GPL included in the
32** packaging of this file. Please review the following information to
33** ensure the GNU General Public License version 3.0 requirements will be
34** met: http://www.gnu.org/copyleft/gpl.html.
35**
36** If you have questions regarding the use of this file, please contact
37** Nokia at qt-info@nokia.com.
38** $QT_END_LICENSE$
39**
40****************************************************************************/
41
42#include "qsql_sqlite.h"
43
44#include <qcoreapplication.h>
45#include <qvariant.h>
46#include <qsqlerror.h>
47#include <qsqlfield.h>
48#include <qsqlindex.h>
49#include <qsqlquery.h>
50#include <qstringlist.h>
51#include <qvector.h>
52#include <qdebug.h>
53
54#if defined Q_OS_WIN
55# include <qt_windows.h>
56#else
57# include <unistd.h>
58#endif
59
60#include <sqlite3.h>
61
62Q_DECLARE_METATYPE(sqlite3*)
63Q_DECLARE_METATYPE(sqlite3_stmt*)
64
65QT_BEGIN_NAMESPACE
66
67static QString _q_escapeIdentifier(const QString &identifier)
68{
69 QString res = identifier;
70 if(!identifier.isEmpty() && identifier.left(1) != QString(QLatin1Char('"')) && identifier.right(1) != QString(QLatin1Char('"')) ) {
71 res.replace(QLatin1Char('"'), QLatin1String("\"\""));
72 res.prepend(QLatin1Char('"')).append(QLatin1Char('"'));
73 res.replace(QLatin1Char('.'), QLatin1String("\".\""));
74 }
75 return res;
76}
77
78static QVariant::Type qGetColumnType(const QString &tpName)
79{
80 const QString typeName = tpName.toLower();
81
82 if (typeName == QLatin1String("integer")
83 || typeName == QLatin1String("int"))
84 return QVariant::Int;
85 if (typeName == QLatin1String("double")
86 || typeName == QLatin1String("float")
87 || typeName.startsWith(QLatin1String("numeric")))
88 return QVariant::Double;
89 if (typeName == QLatin1String("blob"))
90 return QVariant::ByteArray;
91 return QVariant::String;
92}
93
94static QSqlError qMakeError(sqlite3 *access, const QString &descr, QSqlError::ErrorType type,
95 int errorCode = -1)
96{
97 return QSqlError(descr,
98 QString(reinterpret_cast<const QChar *>(sqlite3_errmsg16(access))),
99 type, errorCode);
100}
101
102class QSQLiteDriverPrivate
103{
104public:
105 inline QSQLiteDriverPrivate() : access(0) {}
106 sqlite3 *access;
107};
108
109
110class QSQLiteResultPrivate
111{
112public:
113 QSQLiteResultPrivate(QSQLiteResult *res);
114 void cleanup();
115 bool fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch);
116 // initializes the recordInfo and the cache
117 void initColumns(bool emptyResultset);
118 void finalize();
119
120 QSQLiteResult* q;
121 sqlite3 *access;
122
123 sqlite3_stmt *stmt;
124
125 bool skippedStatus; // the status of the fetchNext() that's skipped
126 bool skipRow; // skip the next fetchNext()?
127 QSqlRecord rInf;
128 QVector<QVariant> firstRow;
129};
130
131QSQLiteResultPrivate::QSQLiteResultPrivate(QSQLiteResult* res) : q(res), access(0),
132 stmt(0), skippedStatus(false), skipRow(false)
133{
134}
135
136void QSQLiteResultPrivate::cleanup()
137{
138 finalize();
139 rInf.clear();
140 skippedStatus = false;
141 skipRow = false;
142 q->setAt(QSql::BeforeFirstRow);
143 q->setActive(false);
144 q->cleanup();
145}
146
147void QSQLiteResultPrivate::finalize()
148{
149 if (!stmt)
150 return;
151
152 sqlite3_finalize(stmt);
153 stmt = 0;
154}
155
156void QSQLiteResultPrivate::initColumns(bool emptyResultset)
157{
158 int nCols = sqlite3_column_count(stmt);
159 if (nCols <= 0)
160 return;
161
162 q->init(nCols);
163
164 for (int i = 0; i < nCols; ++i) {
165 QString colName = QString(reinterpret_cast<const QChar *>(
166 sqlite3_column_name16(stmt, i))
167 ).remove(QLatin1Char('"'));
168
169 // must use typeName for resolving the type to match QSqliteDriver::record
170 QString typeName = QString(reinterpret_cast<const QChar *>(
171 sqlite3_column_decltype16(stmt, i)));
172
173 int dotIdx = colName.lastIndexOf(QLatin1Char('.'));
174 QSqlField fld(colName.mid(dotIdx == -1 ? 0 : dotIdx + 1), qGetColumnType(typeName));
175
176 // sqlite3_column_type is documented to have undefined behavior if the result set is empty
177 int stp = emptyResultset ? -1 : sqlite3_column_type(stmt, i);
178 fld.setSqlType(stp);
179 rInf.append(fld);
180 }
181}
182
183bool QSQLiteResultPrivate::fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch)
184{
185 int res;
186 int i;
187
188 if (skipRow) {
189 // already fetched
190 Q_ASSERT(!initialFetch);
191 skipRow = false;
192 for(int i=0;i<firstRow.count();i++)
193 values[i]=firstRow[i];
194 return skippedStatus;
195 }
196 skipRow = initialFetch;
197
198 if(initialFetch) {
199 firstRow.clear();
200 firstRow.resize(sqlite3_column_count(stmt));
201 }
202
203 if (!stmt) {
204 q->setLastError(QSqlError(QCoreApplication::translate("QSQLiteResult", "Unable to fetch row"),
205 QCoreApplication::translate("QSQLiteResult", "No query"), QSqlError::ConnectionError));
206 q->setAt(QSql::AfterLastRow);
207 return false;
208 }
209 res = sqlite3_step(stmt);
210
211 switch(res) {
212 case SQLITE_ROW:
213 // check to see if should fill out columns
214 if (rInf.isEmpty())
215 // must be first call.
216 initColumns(false);
217 if (idx < 0 && !initialFetch)
218 return true;
219 for (i = 0; i < rInf.count(); ++i) {
220 switch (sqlite3_column_type(stmt, i)) {
221 case SQLITE_BLOB:
222 values[i + idx] = QByteArray(static_cast<const char *>(
223 sqlite3_column_blob(stmt, i)),
224 sqlite3_column_bytes(stmt, i));
225 break;
226 case SQLITE_INTEGER:
227 values[i + idx] = sqlite3_column_int64(stmt, i);
228 break;
229 case SQLITE_FLOAT:
230 switch(q->numericalPrecisionPolicy()) {
231 case QSql::LowPrecisionInt32:
232 values[i + idx] = sqlite3_column_int(stmt, i);
233 break;
234 case QSql::LowPrecisionInt64:
235 values[i + idx] = sqlite3_column_int64(stmt, i);
236 break;
237 case QSql::LowPrecisionDouble:
238 case QSql::HighPrecision:
239 default:
240 values[i + idx] = sqlite3_column_double(stmt, i);
241 break;
242 };
243 break;
244 case SQLITE_NULL:
245 values[i + idx] = QVariant(QVariant::String);
246 break;
247 default:
248 values[i + idx] = QString(reinterpret_cast<const QChar *>(
249 sqlite3_column_text16(stmt, i)),
250 sqlite3_column_bytes16(stmt, i) / sizeof(QChar));
251 break;
252 }
253 }
254 return true;
255 case SQLITE_DONE:
256 if (rInf.isEmpty())
257 // must be first call.
258 initColumns(true);
259 q->setAt(QSql::AfterLastRow);
260 sqlite3_reset(stmt);
261 return false;
262 case SQLITE_CONSTRAINT:
263 case SQLITE_ERROR:
264 // SQLITE_ERROR is a generic error code and we must call sqlite3_reset()
265 // to get the specific error message.
266 res = sqlite3_reset(stmt);
267 q->setLastError(qMakeError(access, QCoreApplication::translate("QSQLiteResult",
268 "Unable to fetch row"), QSqlError::ConnectionError, res));
269 q->setAt(QSql::AfterLastRow);
270 return false;
271 case SQLITE_MISUSE:
272 case SQLITE_BUSY:
273 default:
274 // something wrong, don't get col info, but still return false
275 q->setLastError(qMakeError(access, QCoreApplication::translate("QSQLiteResult",
276 "Unable to fetch row"), QSqlError::ConnectionError, res));
277 sqlite3_reset(stmt);
278 q->setAt(QSql::AfterLastRow);
279 return false;
280 }
281 return false;
282}
283
284QSQLiteResult::QSQLiteResult(const QSQLiteDriver* db)
285 : QSqlCachedResult(db)
286{
287 d = new QSQLiteResultPrivate(this);
288 d->access = db->d->access;
289}
290
291QSQLiteResult::~QSQLiteResult()
292{
293 d->cleanup();
294 delete d;
295}
296
297void QSQLiteResult::virtual_hook(int id, void *data)
298{
299 switch (id) {
300 case QSqlResult::DetachFromResultSet:
301 if (d->stmt)
302 sqlite3_reset(d->stmt);
303 break;
304 default:
305 QSqlCachedResult::virtual_hook(id, data);
306 }
307}
308
309bool QSQLiteResult::reset(const QString &query)
310{
311 if (!prepare(query))
312 return false;
313 return exec();
314}
315
316bool QSQLiteResult::prepare(const QString &query)
317{
318 if (!driver() || !driver()->isOpen() || driver()->isOpenError())
319 return false;
320
321 d->cleanup();
322
323 setSelect(false);
324
325#if (SQLITE_VERSION_NUMBER >= 3003011)
326 int res = sqlite3_prepare16_v2(d->access, query.constData(), (query.size() + 1) * sizeof(QChar),
327 &d->stmt, 0);
328#else
329 int res = sqlite3_prepare16(d->access, query.constData(), (query.size() + 1) * sizeof(QChar),
330 &d->stmt, 0);
331#endif
332
333 if (res != SQLITE_OK) {
334 setLastError(qMakeError(d->access, QCoreApplication::translate("QSQLiteResult",
335 "Unable to execute statement"), QSqlError::StatementError, res));
336 d->finalize();
337 return false;
338 }
339 return true;
340}
341
342bool QSQLiteResult::exec()
343{
344 const QVector<QVariant> values = boundValues();
345
346 d->skippedStatus = false;
347 d->skipRow = false;
348 d->rInf.clear();
349 clearValues();
350 setLastError(QSqlError());
351
352 int res = sqlite3_reset(d->stmt);
353 if (res != SQLITE_OK) {
354 setLastError(qMakeError(d->access, QCoreApplication::translate("QSQLiteResult",
355 "Unable to reset statement"), QSqlError::StatementError, res));
356 d->finalize();
357 return false;
358 }
359 int paramCount = sqlite3_bind_parameter_count(d->stmt);
360 if (paramCount == values.count()) {
361 for (int i = 0; i < paramCount; ++i) {
362 res = SQLITE_OK;
363 const QVariant value = values.at(i);
364
365 if (value.isNull()) {
366 res = sqlite3_bind_null(d->stmt, i + 1);
367 } else {
368 switch (value.type()) {
369 case QVariant::ByteArray: {
370 const QByteArray *ba = static_cast<const QByteArray*>(value.constData());
371 res = sqlite3_bind_blob(d->stmt, i + 1, ba->constData(),
372 ba->size(), SQLITE_STATIC);
373 break; }
374 case QVariant::Int:
375 res = sqlite3_bind_int(d->stmt, i + 1, value.toInt());
376 break;
377 case QVariant::Double:
378 res = sqlite3_bind_double(d->stmt, i + 1, value.toDouble());
379 break;
380 case QVariant::UInt:
381 case QVariant::LongLong:
382 res = sqlite3_bind_int64(d->stmt, i + 1, value.toLongLong());
383 break;
384 case QVariant::String: {
385 // lifetime of string == lifetime of its qvariant
386 const QString *str = static_cast<const QString*>(value.constData());
387 res = sqlite3_bind_text16(d->stmt, i + 1, str->utf16(),
388 (str->size()) * sizeof(QChar), SQLITE_STATIC);
389 break; }
390 default: {
391 QString str = value.toString();
392 // SQLITE_TRANSIENT makes sure that sqlite buffers the data
393 res = sqlite3_bind_text16(d->stmt, i + 1, str.utf16(),
394 (str.size()) * sizeof(QChar), SQLITE_TRANSIENT);
395 break; }
396 }
397 }
398 if (res != SQLITE_OK) {
399 setLastError(qMakeError(d->access, QCoreApplication::translate("QSQLiteResult",
400 "Unable to bind parameters"), QSqlError::StatementError, res));
401 d->finalize();
402 return false;
403 }
404 }
405 } else {
406 setLastError(QSqlError(QCoreApplication::translate("QSQLiteResult",
407 "Parameter count mismatch"), QString(), QSqlError::StatementError));
408 return false;
409 }
410 d->skippedStatus = d->fetchNext(d->firstRow, 0, true);
411 if (lastError().isValid()) {
412 setSelect(false);
413 setActive(false);
414 return false;
415 }
416 setSelect(!d->rInf.isEmpty());
417 setActive(true);
418 return true;
419}
420
421bool QSQLiteResult::gotoNext(QSqlCachedResult::ValueCache& row, int idx)
422{
423 return d->fetchNext(row, idx, false);
424}
425
426int QSQLiteResult::size()
427{
428 return -1;
429}
430
431int QSQLiteResult::numRowsAffected()
432{
433 return sqlite3_changes(d->access);
434}
435
436QVariant QSQLiteResult::lastInsertId() const
437{
438 if (isActive()) {
439 qint64 id = sqlite3_last_insert_rowid(d->access);
440 if (id)
441 return id;
442 }
443 return QVariant();
444}
445
446QSqlRecord QSQLiteResult::record() const
447{
448 if (!isActive() || !isSelect())
449 return QSqlRecord();
450 return d->rInf;
451}
452
453QVariant QSQLiteResult::handle() const
454{
455 return qVariantFromValue(d->stmt);
456}
457
458/////////////////////////////////////////////////////////
459
460QSQLiteDriver::QSQLiteDriver(QObject * parent)
461 : QSqlDriver(parent)
462{
463 d = new QSQLiteDriverPrivate();
464}
465
466QSQLiteDriver::QSQLiteDriver(sqlite3 *connection, QObject *parent)
467 : QSqlDriver(parent)
468{
469 d = new QSQLiteDriverPrivate();
470 d->access = connection;
471 setOpen(true);
472 setOpenError(false);
473}
474
475
476QSQLiteDriver::~QSQLiteDriver()
477{
478 delete d;
479}
480
481bool QSQLiteDriver::hasFeature(DriverFeature f) const
482{
483 switch (f) {
484 case BLOB:
485 case Transactions:
486 case Unicode:
487 case LastInsertId:
488 case PreparedQueries:
489 case PositionalPlaceholders:
490 case SimpleLocking:
491 case FinishQuery:
492 case LowPrecisionNumbers:
493 return true;
494 case QuerySize:
495 case NamedPlaceholders:
496 case BatchOperations:
497 case EventNotifications:
498 case MultipleResultSets:
499 return false;
500 }
501 return false;
502}
503
504/*
505 SQLite dbs have no user name, passwords, hosts or ports.
506 just file names.
507*/
508bool QSQLiteDriver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &conOpts)
509{
510 if (isOpen())
511 close();
512
513 if (db.isEmpty())
514 return false;
515 bool sharedCache = false;
516 int openMode = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, timeOut=5000;
517 QStringList opts=QString(conOpts).remove(QLatin1Char(' ')).split(QLatin1Char(';'));
518 foreach(const QString &option, opts) {
519 if (option.startsWith(QLatin1String("QSQLITE_BUSY_TIMEOUT="))) {
520 bool ok;
521 int nt = option.mid(21).toInt(&ok);
522 if (ok)
523 timeOut = nt;
524 }
525 if (option == QLatin1String("QSQLITE_OPEN_READONLY"))
526 openMode = SQLITE_OPEN_READONLY;
527 if (option == QLatin1String("QSQLITE_ENABLE_SHARED_CACHE"))
528 sharedCache = true;
529 }
530
531 sqlite3_enable_shared_cache(sharedCache);
532
533 if (sqlite3_open_v2(db.toUtf8().constData(), &d->access, openMode, NULL) == SQLITE_OK) {
534 sqlite3_busy_timeout(d->access, timeOut);
535 setOpen(true);
536 setOpenError(false);
537 return true;
538 } else {
539 setLastError(qMakeError(d->access, tr("Error opening database"),
540 QSqlError::ConnectionError));
541 setOpenError(true);
542 return false;
543 }
544}
545
546void QSQLiteDriver::close()
547{
548 if (isOpen()) {
549 if (sqlite3_close(d->access) != SQLITE_OK)
550 setLastError(qMakeError(d->access, tr("Error closing database"),
551 QSqlError::ConnectionError));
552 d->access = 0;
553 setOpen(false);
554 setOpenError(false);
555 }
556}
557
558QSqlResult *QSQLiteDriver::createResult() const
559{
560 return new QSQLiteResult(this);
561}
562
563bool QSQLiteDriver::beginTransaction()
564{
565 if (!isOpen() || isOpenError())
566 return false;
567
568 QSqlQuery q(createResult());
569 if (!q.exec(QLatin1String("BEGIN"))) {
570 setLastError(QSqlError(tr("Unable to begin transaction"),
571 q.lastError().databaseText(), QSqlError::TransactionError));
572 return false;
573 }
574
575 return true;
576}
577
578bool QSQLiteDriver::commitTransaction()
579{
580 if (!isOpen() || isOpenError())
581 return false;
582
583 QSqlQuery q(createResult());
584 if (!q.exec(QLatin1String("COMMIT"))) {
585 setLastError(QSqlError(tr("Unable to commit transaction"),
586 q.lastError().databaseText(), QSqlError::TransactionError));
587 return false;
588 }
589
590 return true;
591}
592
593bool QSQLiteDriver::rollbackTransaction()
594{
595 if (!isOpen() || isOpenError())
596 return false;
597
598 QSqlQuery q(createResult());
599 if (!q.exec(QLatin1String("ROLLBACK"))) {
600 setLastError(QSqlError(tr("Unable to rollback transaction"),
601 q.lastError().databaseText(), QSqlError::TransactionError));
602 return false;
603 }
604
605 return true;
606}
607
608QStringList QSQLiteDriver::tables(QSql::TableType type) const
609{
610 QStringList res;
611 if (!isOpen())
612 return res;
613
614 QSqlQuery q(createResult());
615 q.setForwardOnly(true);
616
617 QString sql = QLatin1String("SELECT name FROM sqlite_master WHERE %1 "
618 "UNION ALL SELECT name FROM sqlite_temp_master WHERE %1");
619 if ((type & QSql::Tables) && (type & QSql::Views))
620 sql = sql.arg(QLatin1String("type='table' OR type='view'"));
621 else if (type & QSql::Tables)
622 sql = sql.arg(QLatin1String("type='table'"));
623 else if (type & QSql::Views)
624 sql = sql.arg(QLatin1String("type='view'"));
625 else
626 sql.clear();
627
628 if (!sql.isEmpty() && q.exec(sql)) {
629 while(q.next())
630 res.append(q.value(0).toString());
631 }
632
633 if (type & QSql::SystemTables) {
634 // there are no internal tables beside this one:
635 res.append(QLatin1String("sqlite_master"));
636 }
637
638 return res;
639}
640
641static QSqlIndex qGetTableInfo(QSqlQuery &q, const QString &tableName, bool onlyPIndex = false)
642{
643 QString schema;
644 QString table(tableName);
645 int indexOfSeparator = tableName.indexOf(QLatin1Char('.'));
646 if (indexOfSeparator > -1) {
647 schema = tableName.left(indexOfSeparator).append(QLatin1Char('.'));
648 table = tableName.mid(indexOfSeparator + 1);
649 }
650 q.exec(QLatin1String("PRAGMA ") + schema + QLatin1String("table_info (") + _q_escapeIdentifier(table) + QLatin1String(")"));
651
652 QSqlIndex ind;
653 while (q.next()) {
654 bool isPk = q.value(5).toInt();
655 if (onlyPIndex && !isPk)
656 continue;
657 QString typeName = q.value(2).toString().toLower();
658 QSqlField fld(q.value(1).toString(), qGetColumnType(typeName));
659 if (isPk && (typeName == QLatin1String("integer")))
660 // INTEGER PRIMARY KEY fields are auto-generated in sqlite
661 // INT PRIMARY KEY is not the same as INTEGER PRIMARY KEY!
662 fld.setAutoValue(true);
663 fld.setRequired(q.value(3).toInt() != 0);
664 fld.setDefaultValue(q.value(4));
665 ind.append(fld);
666 }
667 return ind;
668}
669
670QSqlIndex QSQLiteDriver::primaryIndex(const QString &tblname) const
671{
672 if (!isOpen())
673 return QSqlIndex();
674
675 QString table = tblname;
676 if (isIdentifierEscaped(table, QSqlDriver::TableName))
677 table = stripDelimiters(table, QSqlDriver::TableName);
678
679 QSqlQuery q(createResult());
680 q.setForwardOnly(true);
681 return qGetTableInfo(q, table, true);
682}
683
684QSqlRecord QSQLiteDriver::record(const QString &tbl) const
685{
686 if (!isOpen())
687 return QSqlRecord();
688
689 QString table = tbl;
690 if (isIdentifierEscaped(table, QSqlDriver::TableName))
691 table = stripDelimiters(table, QSqlDriver::TableName);
692
693 QSqlQuery q(createResult());
694 q.setForwardOnly(true);
695 return qGetTableInfo(q, table);
696}
697
698QVariant QSQLiteDriver::handle() const
699{
700 return qVariantFromValue(d->access);
701}
702
703QString QSQLiteDriver::escapeIdentifier(const QString &identifier, IdentifierType type) const
704{
705 Q_UNUSED(type);
706 return _q_escapeIdentifier(identifier);
707}
708
709QT_END_NAMESPACE
Note: See TracBrowser for help on using the repository browser.