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

Last change on this file since 651 was 651, checked in by Dmitry A. Kuminov, 15 years ago

trunk: Merged in qt 4.6.2 sources.
File size: 21.3 KB
Line 
1/****************************************************************************
2**
3** Copyright (C) 2010 Nokia Corporation and/or its subsidiary(-ies).
4** All rights reserved.
5** Contact: Nokia Corporation ([email protected])
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 [email protected].
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::fromUtf16(static_cast<const ushort *>(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::fromUtf16(
166 static_cast<const ushort *>(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::fromUtf16(
171 static_cast<const ushort *>(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::fromUtf16(static_cast<const ushort *>(
249 sqlite3_column_text16(stmt, i)),
250 sqlite3_column_bytes16(stmt, i) / sizeof(ushort));
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_ERROR:
263 // SQLITE_ERROR is a generic error code and we must call sqlite3_reset()
264 // to get the specific error message.
265 res = sqlite3_reset(stmt);
266 q->setLastError(qMakeError(access, QCoreApplication::translate("QSQLiteResult",
267 "Unable to fetch row"), QSqlError::ConnectionError, res));
268 q->setAt(QSql::AfterLastRow);
269 return false;
270 case SQLITE_MISUSE:
271 case SQLITE_BUSY:
272 default:
273 // something wrong, don't get col info, but still return false
274 q->setLastError(qMakeError(access, QCoreApplication::translate("QSQLiteResult",
275 "Unable to fetch row"), QSqlError::ConnectionError, res));
276 sqlite3_reset(stmt);
277 q->setAt(QSql::AfterLastRow);
278 return false;
279 }
280 return false;
281}
282
283QSQLiteResult::QSQLiteResult(const QSQLiteDriver* db)
284 : QSqlCachedResult(db)
285{
286 d = new QSQLiteResultPrivate(this);
287 d->access = db->d->access;
288}
289
290QSQLiteResult::~QSQLiteResult()
291{
292 d->cleanup();
293 delete d;
294}
295
296void QSQLiteResult::virtual_hook(int id, void *data)
297{
298 switch (id) {
299 case QSqlResult::DetachFromResultSet:
300 if (d->stmt)
301 sqlite3_reset(d->stmt);
302 break;
303 default:
304 QSqlCachedResult::virtual_hook(id, data);
305 }
306}
307
308bool QSQLiteResult::reset(const QString &query)
309{
310 if (!prepare(query))
311 return false;
312 return exec();
313}
314
315bool QSQLiteResult::prepare(const QString &query)
316{
317 if (!driver() || !driver()->isOpen() || driver()->isOpenError())
318 return false;
319
320 d->cleanup();
321
322 setSelect(false);
323
324#if (SQLITE_VERSION_NUMBER >= 3003011)
325 int res = sqlite3_prepare16_v2(d->access, query.constData(), (query.size() + 1) * sizeof(QChar),
326 &d->stmt, 0);
327#else
328 int res = sqlite3_prepare16(d->access, query.constData(), (query.size() + 1) * sizeof(QChar),
329 &d->stmt, 0);
330#endif
331
332 if (res != SQLITE_OK) {
333 setLastError(qMakeError(d->access, QCoreApplication::translate("QSQLiteResult",
334 "Unable to execute statement"), QSqlError::StatementError, res));
335 d->finalize();
336 return false;
337 }
338 return true;
339}
340
341bool QSQLiteResult::exec()
342{
343 const QVector<QVariant> values = boundValues();
344
345 d->skippedStatus = false;
346 d->skipRow = false;
347 d->rInf.clear();
348 clearValues();
349 setLastError(QSqlError());
350
351 int res = sqlite3_reset(d->stmt);
352 if (res != SQLITE_OK) {
353 setLastError(qMakeError(d->access, QCoreApplication::translate("QSQLiteResult",
354 "Unable to reset statement"), QSqlError::StatementError, res));
355 d->finalize();
356 return false;
357 }
358 int paramCount = sqlite3_bind_parameter_count(d->stmt);
359 if (paramCount == values.count()) {
360 for (int i = 0; i < paramCount; ++i) {
361 res = SQLITE_OK;
362 const QVariant value = values.at(i);
363
364 if (value.isNull()) {
365 res = sqlite3_bind_null(d->stmt, i + 1);
366 } else {
367 switch (value.type()) {
368 case QVariant::ByteArray: {
369 const QByteArray *ba = static_cast<const QByteArray*>(value.constData());
370 res = sqlite3_bind_blob(d->stmt, i + 1, ba->constData(),
371 ba->size(), SQLITE_STATIC);
372 break; }
373 case QVariant::Int:
374 res = sqlite3_bind_int(d->stmt, i + 1, value.toInt());
375 break;
376 case QVariant::Double:
377 res = sqlite3_bind_double(d->stmt, i + 1, value.toDouble());
378 break;
379 case QVariant::UInt:
380 case QVariant::LongLong:
381 res = sqlite3_bind_int64(d->stmt, i + 1, value.toLongLong());
382 break;
383 case QVariant::String: {
384 // lifetime of string == lifetime of its qvariant
385 const QString *str = static_cast<const QString*>(value.constData());
386 res = sqlite3_bind_text16(d->stmt, i + 1, str->utf16(),
387 (str->size()) * sizeof(QChar), SQLITE_STATIC);
388 break; }
389 default: {
390 QString str = value.toString();
391 // SQLITE_TRANSIENT makes sure that sqlite buffers the data
392 res = sqlite3_bind_text16(d->stmt, i + 1, str.utf16(),
393 (str.size()) * sizeof(QChar), SQLITE_TRANSIENT);
394 break; }
395 }
396 }
397 if (res != SQLITE_OK) {
398 setLastError(qMakeError(d->access, QCoreApplication::translate("QSQLiteResult",
399 "Unable to bind parameters"), QSqlError::StatementError, res));
400 d->finalize();
401 return false;
402 }
403 }
404 } else {
405 setLastError(QSqlError(QCoreApplication::translate("QSQLiteResult",
406 "Parameter count mismatch"), QString(), QSqlError::StatementError));
407 return false;
408 }
409 d->skippedStatus = d->fetchNext(d->firstRow, 0, true);
410 if (lastError().isValid()) {
411 setSelect(false);
412 setActive(false);
413 return false;
414 }
415 setSelect(!d->rInf.isEmpty());
416 setActive(true);
417 return true;
418}
419
420bool QSQLiteResult::gotoNext(QSqlCachedResult::ValueCache& row, int idx)
421{
422 return d->fetchNext(row, idx, false);
423}
424
425int QSQLiteResult::size()
426{
427 return -1;
428}
429
430int QSQLiteResult::numRowsAffected()
431{
432 return sqlite3_changes(d->access);
433}
434
435QVariant QSQLiteResult::lastInsertId() const
436{
437 if (isActive()) {
438 qint64 id = sqlite3_last_insert_rowid(d->access);
439 if (id)
440 return id;
441 }
442 return QVariant();
443}
444
445QSqlRecord QSQLiteResult::record() const
446{
447 if (!isActive() || !isSelect())
448 return QSqlRecord();
449 return d->rInf;
450}
451
452QVariant QSQLiteResult::handle() const
453{
454 return qVariantFromValue(d->stmt);
455}
456
457/////////////////////////////////////////////////////////
458
459QSQLiteDriver::QSQLiteDriver(QObject * parent)
460 : QSqlDriver(parent)
461{
462 d = new QSQLiteDriverPrivate();
463}
464
465QSQLiteDriver::QSQLiteDriver(sqlite3 *connection, QObject *parent)
466 : QSqlDriver(parent)
467{
468 d = new QSQLiteDriverPrivate();
469 d->access = connection;
470 setOpen(true);
471 setOpenError(false);
472}
473
474
475QSQLiteDriver::~QSQLiteDriver()
476{
477 delete d;
478}
479
480bool QSQLiteDriver::hasFeature(DriverFeature f) const
481{
482 switch (f) {
483 case BLOB:
484 case Transactions:
485 case Unicode:
486 case LastInsertId:
487 case PreparedQueries:
488 case PositionalPlaceholders:
489 case SimpleLocking:
490 case FinishQuery:
491 case LowPrecisionNumbers:
492 return true;
493 case QuerySize:
494 case NamedPlaceholders:
495 case BatchOperations:
496 case EventNotifications:
497 case MultipleResultSets:
498 return false;
499 }
500 return false;
501}
502
503static int qGetSqliteTimeout(QString opts)
504{
505 enum { DefaultTimeout = 5000 };
506
507 opts.remove(QLatin1Char(' '));
508 foreach(QString option, opts.split(QLatin1Char(';'))) {
509 if (option.startsWith(QLatin1String("QSQLITE_BUSY_TIMEOUT="))) {
510 bool ok;
511 int nt = option.mid(21).toInt(&ok);
512 if (ok)
513 return nt;
514 }
515 }
516 return DefaultTimeout;
517}
518
519static int qGetSqliteOpenMode(QString opts)
520{
521 opts.remove(QLatin1Char(' '));
522 foreach(QString option, opts.split(QLatin1Char(';'))) {
523 if (option == QLatin1String("QSQLITE_OPEN_READONLY"))
524 return SQLITE_OPEN_READONLY;
525 }
526 return SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
527}
528
529/*
530 SQLite dbs have no user name, passwords, hosts or ports.
531 just file names.
532*/
533bool QSQLiteDriver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &conOpts)
534{
535 if (isOpen())
536 close();
537
538 if (db.isEmpty())
539 return false;
540
541 if (sqlite3_open_v2(db.toUtf8().constData(), &d->access, qGetSqliteOpenMode(conOpts), NULL) == SQLITE_OK) {
542 sqlite3_busy_timeout(d->access, qGetSqliteTimeout(conOpts));
543 setOpen(true);
544 setOpenError(false);
545 return true;
546 } else {
547 setLastError(qMakeError(d->access, tr("Error opening database"),
548 QSqlError::ConnectionError));
549 setOpenError(true);
550 return false;
551 }
552}
553
554void QSQLiteDriver::close()
555{
556 if (isOpen()) {
557 if (sqlite3_close(d->access) != SQLITE_OK)
558 setLastError(qMakeError(d->access, tr("Error closing database"),
559 QSqlError::ConnectionError));
560 d->access = 0;
561 setOpen(false);
562 setOpenError(false);
563 }
564}
565
566QSqlResult *QSQLiteDriver::createResult() const
567{
568 return new QSQLiteResult(this);
569}
570
571bool QSQLiteDriver::beginTransaction()
572{
573 if (!isOpen() || isOpenError())
574 return false;
575
576 QSqlQuery q(createResult());
577 if (!q.exec(QLatin1String("BEGIN"))) {
578 setLastError(QSqlError(tr("Unable to begin transaction"),
579 q.lastError().databaseText(), QSqlError::TransactionError));
580 return false;
581 }
582
583 return true;
584}
585
586bool QSQLiteDriver::commitTransaction()
587{
588 if (!isOpen() || isOpenError())
589 return false;
590
591 QSqlQuery q(createResult());
592 if (!q.exec(QLatin1String("COMMIT"))) {
593 setLastError(QSqlError(tr("Unable to commit transaction"),
594 q.lastError().databaseText(), QSqlError::TransactionError));
595 return false;
596 }
597
598 return true;
599}
600
601bool QSQLiteDriver::rollbackTransaction()
602{
603 if (!isOpen() || isOpenError())
604 return false;
605
606 QSqlQuery q(createResult());
607 if (!q.exec(QLatin1String("ROLLBACK"))) {
608 setLastError(QSqlError(tr("Unable to rollback transaction"),
609 q.lastError().databaseText(), QSqlError::TransactionError));
610 return false;
611 }
612
613 return true;
614}
615
616QStringList QSQLiteDriver::tables(QSql::TableType type) const
617{
618 QStringList res;
619 if (!isOpen())
620 return res;
621
622 QSqlQuery q(createResult());
623 q.setForwardOnly(true);
624
625 QString sql = QLatin1String("SELECT name FROM sqlite_master WHERE %1 "
626 "UNION ALL SELECT name FROM sqlite_temp_master WHERE %1");
627 if ((type & QSql::Tables) && (type & QSql::Views))
628 sql = sql.arg(QLatin1String("type='table' OR type='view'"));
629 else if (type & QSql::Tables)
630 sql = sql.arg(QLatin1String("type='table'"));
631 else if (type & QSql::Views)
632 sql = sql.arg(QLatin1String("type='view'"));
633 else
634 sql.clear();
635
636 if (!sql.isEmpty() && q.exec(sql)) {
637 while(q.next())
638 res.append(q.value(0).toString());
639 }
640
641 if (type & QSql::SystemTables) {
642 // there are no internal tables beside this one:
643 res.append(QLatin1String("sqlite_master"));
644 }
645
646 return res;
647}
648
649static QSqlIndex qGetTableInfo(QSqlQuery &q, const QString &tableName, bool onlyPIndex = false)
650{
651 QString schema;
652 QString table(tableName);
653 int indexOfSeparator = tableName.indexOf(QLatin1Char('.'));
654 if (indexOfSeparator > -1) {
655 schema = tableName.left(indexOfSeparator).append(QLatin1Char('.'));
656 table = tableName.mid(indexOfSeparator + 1);
657 }
658 q.exec(QLatin1String("PRAGMA ") + schema + QLatin1String("table_info (") + _q_escapeIdentifier(table) + QLatin1String(")"));
659
660 QSqlIndex ind;
661 while (q.next()) {
662 bool isPk = q.value(5).toInt();
663 if (onlyPIndex && !isPk)
664 continue;
665 QString typeName = q.value(2).toString().toLower();
666 QSqlField fld(q.value(1).toString(), qGetColumnType(typeName));
667 if (isPk && (typeName == QLatin1String("integer")))
668 // INTEGER PRIMARY KEY fields are auto-generated in sqlite
669 // INT PRIMARY KEY is not the same as INTEGER PRIMARY KEY!
670 fld.setAutoValue(true);
671 fld.setRequired(q.value(3).toInt() != 0);
672 fld.setDefaultValue(q.value(4));
673 ind.append(fld);
674 }
675 return ind;
676}
677
678QSqlIndex QSQLiteDriver::primaryIndex(const QString &tblname) const
679{
680 if (!isOpen())
681 return QSqlIndex();
682
683 QString table = tblname;
684 if (isIdentifierEscaped(table, QSqlDriver::TableName))
685 table = stripDelimiters(table, QSqlDriver::TableName);
686
687 QSqlQuery q(createResult());
688 q.setForwardOnly(true);
689 return qGetTableInfo(q, table, true);
690}
691
692QSqlRecord QSQLiteDriver::record(const QString &tbl) const
693{
694 if (!isOpen())
695 return QSqlRecord();
696
697 QString table = tbl;
698 if (isIdentifierEscaped(table, QSqlDriver::TableName))
699 table = stripDelimiters(table, QSqlDriver::TableName);
700
701 QSqlQuery q(createResult());
702 q.setForwardOnly(true);
703 return qGetTableInfo(q, table);
704}
705
706QVariant QSQLiteDriver::handle() const
707{
708 return qVariantFromValue(d->access);
709}
710
711QString QSQLiteDriver::escapeIdentifier(const QString &identifier, IdentifierType type) const
712{
713 Q_UNUSED(type);
714 return _q_escapeIdentifier(identifier);
715}
716
717QT_END_NAMESPACE
Note: See TracBrowser for help on using the repository browser.