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_sqlite2_p.h"
41
42 #include <qcoreapplication.h>
43 #include <qvariant.h>
44 #include <qdatetime.h>
45 #include <qfile.h>
46 #include <qsqlerror.h>
47 #include <qsqlfield.h>
48 #include <qsqlindex.h>
49 #include <qsqlquery.h>
50 #include <QtSql/private/qsqlcachedresult_p.h>
51 #include <QtSql/private/qsqldriver_p.h>
52 #include <qstringlist.h>
53 #include <qvector.h>
54
55 #if !defined Q_OS_WIN
56 # include <unistd.h>
57 #endif
58 #include <sqlite.h>
59
60 typedef struct sqlite_vm sqlite_vm;
61
62 Q_DECLARE_OPAQUE_POINTER(sqlite_vm*)
Q_DECLARE_METATYPE(sqlite_vm *)63 Q_DECLARE_METATYPE(sqlite_vm*)
64
65 Q_DECLARE_OPAQUE_POINTER(sqlite*)
66 Q_DECLARE_METATYPE(sqlite*)
67
68 QT_BEGIN_NAMESPACE
69
70 static QVariant::Type nameToType(const QString& typeName)
71 {
72 QString tName = typeName.toUpper();
73 if (tName.startsWith(QLatin1String("INT")))
74 return QVariant::Int;
75 if (tName.startsWith(QLatin1String("FLOAT")) || tName.startsWith(QLatin1String("NUMERIC")))
76 return QVariant::Double;
77 if (tName.startsWith(QLatin1String("BOOL")))
78 return QVariant::Bool;
79 // SQLite is typeless - consider everything else as string
80 return QVariant::String;
81 }
82
83 class QSQLite2DriverPrivate : public QSqlDriverPrivate
84 {
85 Q_DECLARE_PUBLIC(QSQLite2Driver)
86
87 public:
88 QSQLite2DriverPrivate();
89 sqlite *access;
90 bool utf8;
91 };
92
QSQLite2DriverPrivate()93 QSQLite2DriverPrivate::QSQLite2DriverPrivate() : QSqlDriverPrivate(), access(0)
94 {
95 utf8 = (qstrcmp(sqlite_encoding, "UTF-8") == 0);
96 dbmsType = QSqlDriver::SQLite;
97 }
98
99 class QSQLite2ResultPrivate;
100
101 class QSQLite2Result : public QSqlCachedResult
102 {
103 Q_DECLARE_PRIVATE(QSQLite2Result)
104 friend class QSQLite2Driver;
105
106 public:
107 explicit QSQLite2Result(const QSQLite2Driver* db);
108 ~QSQLite2Result();
109 QVariant handle() const override;
110
111 protected:
112 bool gotoNext(QSqlCachedResult::ValueCache &row, int idx) override;
113 bool reset(const QString &query) override;
114 int size() override;
115 int numRowsAffected() override;
116 QSqlRecord record() const override;
117 void detachFromResultSet() override;
118 void virtual_hook(int id, void *data) override;
119 };
120
121 class QSQLite2ResultPrivate: public QSqlCachedResultPrivate
122 {
123 Q_DECLARE_PUBLIC(QSQLite2Result)
124
125 public:
126 Q_DECLARE_SQLDRIVER_PRIVATE(QSQLite2Driver);
127 QSQLite2ResultPrivate(QSQLite2Result *q, const QSQLite2Driver *drv);
128 void cleanup();
129 bool fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch);
130 bool isSelect();
131 // initializes the recordInfo and the cache
132 void init(const char **cnames, int numCols);
133 void finalize();
134
135 // and we have too keep our own struct for the data (sqlite works via
136 // callback.
137 const char *currentTail;
138 sqlite_vm *currentMachine;
139
140 bool skippedStatus; // the status of the fetchNext() that's skipped
141 bool skipRow; // skip the next fetchNext()?
142 QSqlRecord rInf;
143 QVector<QVariant> firstRow;
144 };
145
QSQLite2ResultPrivate(QSQLite2Result * q,const QSQLite2Driver * drv)146 QSQLite2ResultPrivate::QSQLite2ResultPrivate(QSQLite2Result *q, const QSQLite2Driver *drv)
147 : QSqlCachedResultPrivate(q, drv),
148 currentTail(0),
149 currentMachine(0),
150 skippedStatus(false),
151 skipRow(false)
152 {
153 }
154
cleanup()155 void QSQLite2ResultPrivate::cleanup()
156 {
157 Q_Q(QSQLite2Result);
158 finalize();
159 rInf.clear();
160 currentTail = 0;
161 currentMachine = 0;
162 skippedStatus = false;
163 skipRow = false;
164 q->setAt(QSql::BeforeFirstRow);
165 q->setActive(false);
166 q->cleanup();
167 }
168
finalize()169 void QSQLite2ResultPrivate::finalize()
170 {
171 Q_Q(QSQLite2Result);
172 if (!currentMachine)
173 return;
174
175 char* err = 0;
176 int res = sqlite_finalize(currentMachine, &err);
177 if (err) {
178 q->setLastError(QSqlError(QCoreApplication::translate("QSQLite2Result",
179 "Unable to fetch results"), QString::fromLatin1(err),
180 QSqlError::StatementError,
181 res != -1 ? QString::number(res) : QString()));
182 sqlite_freemem(err);
183 }
184 currentMachine = 0;
185 }
186
187 // called on first fetch
init(const char ** cnames,int numCols)188 void QSQLite2ResultPrivate::init(const char **cnames, int numCols)
189 {
190 Q_Q(QSQLite2Result);
191 if (!cnames)
192 return;
193
194 rInf.clear();
195 if (numCols <= 0)
196 return;
197 q->init(numCols);
198
199 for (int i = 0; i < numCols; ++i) {
200 const char* lastDot = strrchr(cnames[i], '.');
201 const char* fieldName = lastDot ? lastDot + 1 : cnames[i];
202
203 //remove quotations around the field name if any
204 QString fieldStr = QString::fromLatin1(fieldName);
205 QLatin1Char quote('\"');
206 if ( fieldStr.length() > 2 && fieldStr.startsWith(quote) && fieldStr.endsWith(quote)) {
207 fieldStr = fieldStr.mid(1);
208 fieldStr.chop(1);
209 }
210 rInf.append(QSqlField(fieldStr,
211 nameToType(QString::fromLatin1(cnames[i+numCols]))));
212 }
213 }
214
fetchNext(QSqlCachedResult::ValueCache & values,int idx,bool initialFetch)215 bool QSQLite2ResultPrivate::fetchNext(QSqlCachedResult::ValueCache &values, int idx, bool initialFetch)
216 {
217 Q_Q(QSQLite2Result);
218 // may be caching.
219 const char **fvals;
220 const char **cnames;
221 int colNum;
222 int res;
223 int i;
224
225 if (skipRow) {
226 // already fetched
227 Q_ASSERT(!initialFetch);
228 skipRow = false;
229 for(int i=0;i<firstRow.count(); i++)
230 values[i] = firstRow[i];
231 return skippedStatus;
232 }
233 skipRow = initialFetch;
234
235 if (!currentMachine)
236 return false;
237
238 // keep trying while busy, wish I could implement this better.
239 while ((res = sqlite_step(currentMachine, &colNum, &fvals, &cnames)) == SQLITE_BUSY) {
240 // sleep instead requesting result again immidiately.
241 #if defined Q_OS_WIN
242 Sleep(1000);
243 #else
244 sleep(1);
245 #endif
246 }
247
248 if(initialFetch) {
249 firstRow.clear();
250 firstRow.resize(colNum);
251 }
252
253 switch(res) {
254 case SQLITE_ROW:
255 // check to see if should fill out columns
256 if (rInf.isEmpty())
257 // must be first call.
258 init(cnames, colNum);
259 if (!fvals)
260 return false;
261 if (idx < 0 && !initialFetch)
262 return true;
263 for (i = 0; i < colNum; ++i)
264 values[i + idx] = drv_d_func()->utf8 ? QString::fromUtf8(fvals[i]) : QString::fromLatin1(fvals[i]);
265 return true;
266 case SQLITE_DONE:
267 if (rInf.isEmpty())
268 // must be first call.
269 init(cnames, colNum);
270 q->setAt(QSql::AfterLastRow);
271 return false;
272 case SQLITE_ERROR:
273 case SQLITE_MISUSE:
274 default:
275 // something wrong, don't get col info, but still return false
276 finalize(); // finalize to get the error message.
277 q->setAt(QSql::AfterLastRow);
278 return false;
279 }
280 return false;
281 }
282
QSQLite2Result(const QSQLite2Driver * db)283 QSQLite2Result::QSQLite2Result(const QSQLite2Driver* db)
284 : QSqlCachedResult(*new QSQLite2ResultPrivate(this, db))
285 {
286 }
287
~QSQLite2Result()288 QSQLite2Result::~QSQLite2Result()
289 {
290 Q_D(QSQLite2Result);
291 d->cleanup();
292 }
293
virtual_hook(int id,void * data)294 void QSQLite2Result::virtual_hook(int id, void *data)
295 {
296 QSqlCachedResult::virtual_hook(id, data);
297 }
298
299 /*
300 Execute \a query.
301 */
reset(const QString & query)302 bool QSQLite2Result::reset (const QString& query)
303 {
304 Q_D(QSQLite2Result);
305 // this is where we build a query.
306 if (!driver())
307 return false;
308 if (!driver()-> isOpen() || driver()->isOpenError())
309 return false;
310
311 d->cleanup();
312
313 // Um, ok. callback based so.... pass private static function for this.
314 setSelect(false);
315 char *err = 0;
316 int res = sqlite_compile(d->drv_d_func()->access,
317 d->drv_d_func()->utf8 ? query.toUtf8().constData()
318 : query.toLatin1().constData(),
319 &(d->currentTail),
320 &(d->currentMachine),
321 &err);
322 if (res != SQLITE_OK || err) {
323 setLastError(QSqlError(QCoreApplication::translate("QSQLite2Result",
324 "Unable to execute statement"), QString::fromLatin1(err),
325 QSqlError::StatementError, res));
326 sqlite_freemem(err);
327 }
328 //if (*d->currentTail != '\000' then there is more sql to eval
329 if (!d->currentMachine) {
330 setActive(false);
331 return false;
332 }
333 // we have to fetch one row to find out about
334 // the structure of the result set
335 d->skippedStatus = d->fetchNext(d->firstRow, 0, true);
336 if (lastError().isValid()) {
337 setSelect(false);
338 setActive(false);
339 return false;
340 }
341 setSelect(!d->rInf.isEmpty());
342 setActive(true);
343 return true;
344 }
345
gotoNext(QSqlCachedResult::ValueCache & row,int idx)346 bool QSQLite2Result::gotoNext(QSqlCachedResult::ValueCache& row, int idx)
347 {
348 Q_D(QSQLite2Result);
349 return d->fetchNext(row, idx, false);
350 }
351
size()352 int QSQLite2Result::size()
353 {
354 return -1;
355 }
356
numRowsAffected()357 int QSQLite2Result::numRowsAffected()
358 {
359 Q_D(QSQLite2Result);
360 return sqlite_changes(d->drv_d_func()->access);
361 }
362
record() const363 QSqlRecord QSQLite2Result::record() const
364 {
365 Q_D(const QSQLite2Result);
366 if (!isActive() || !isSelect())
367 return QSqlRecord();
368 return d->rInf;
369 }
370
detachFromResultSet()371 void QSQLite2Result::detachFromResultSet()
372 {
373 Q_D(QSQLite2Result);
374 d->finalize();
375 }
376
handle() const377 QVariant QSQLite2Result::handle() const
378 {
379 Q_D(const QSQLite2Result);
380 return QVariant::fromValue(d->currentMachine);
381 }
382
383 /////////////////////////////////////////////////////////
384
QSQLite2Driver(QObject * parent)385 QSQLite2Driver::QSQLite2Driver(QObject *parent)
386 : QSqlDriver(*new QSQLite2DriverPrivate, parent)
387 {
388 }
389
QSQLite2Driver(sqlite * connection,QObject * parent)390 QSQLite2Driver::QSQLite2Driver(sqlite *connection, QObject *parent)
391 : QSqlDriver(*new QSQLite2DriverPrivate, parent)
392 {
393 Q_D(QSQLite2Driver);
394 d->access = connection;
395 setOpen(true);
396 setOpenError(false);
397 }
398
399
~QSQLite2Driver()400 QSQLite2Driver::~QSQLite2Driver()
401 {
402 }
403
hasFeature(DriverFeature f) const404 bool QSQLite2Driver::hasFeature(DriverFeature f) const
405 {
406 Q_D(const QSQLite2Driver);
407 switch (f) {
408 case Transactions:
409 case SimpleLocking:
410 return true;
411 case Unicode:
412 return d->utf8;
413 default:
414 return false;
415 }
416 }
417
418 /*
419 SQLite dbs have no user name, passwords, hosts or ports.
420 just file names.
421 */
open(const QString & db,const QString &,const QString &,const QString &,int,const QString &)422 bool QSQLite2Driver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &)
423 {
424 Q_D(QSQLite2Driver);
425 if (isOpen())
426 close();
427
428 if (db.isEmpty())
429 return false;
430
431 char* err = 0;
432 d->access = sqlite_open(QFile::encodeName(db), 0, &err);
433 if (err) {
434 setLastError(QSqlError(tr("Error opening database"), QString::fromLatin1(err),
435 QSqlError::ConnectionError));
436 sqlite_freemem(err);
437 err = 0;
438 }
439
440 if (d->access) {
441 setOpen(true);
442 setOpenError(false);
443 return true;
444 }
445 setOpenError(true);
446 return false;
447 }
448
close()449 void QSQLite2Driver::close()
450 {
451 Q_D(QSQLite2Driver);
452 if (isOpen()) {
453 sqlite_close(d->access);
454 d->access = 0;
455 setOpen(false);
456 setOpenError(false);
457 }
458 }
459
createResult() const460 QSqlResult *QSQLite2Driver::createResult() const
461 {
462 return new QSQLite2Result(this);
463 }
464
beginTransaction()465 bool QSQLite2Driver::beginTransaction()
466 {
467 Q_D(QSQLite2Driver);
468 if (!isOpen() || isOpenError())
469 return false;
470
471 char* err;
472 int res = sqlite_exec(d->access, "BEGIN", 0, this, &err);
473
474 if (res == SQLITE_OK)
475 return true;
476
477 setLastError(QSqlError(tr("Unable to begin transaction"),
478 QString::fromLatin1(err), QSqlError::TransactionError, res));
479 sqlite_freemem(err);
480 return false;
481 }
482
commitTransaction()483 bool QSQLite2Driver::commitTransaction()
484 {
485 Q_D(QSQLite2Driver);
486 if (!isOpen() || isOpenError())
487 return false;
488
489 char* err;
490 int res = sqlite_exec(d->access, "COMMIT", 0, this, &err);
491
492 if (res == SQLITE_OK)
493 return true;
494
495 setLastError(QSqlError(tr("Unable to commit transaction"),
496 QString::fromLatin1(err), QSqlError::TransactionError, res));
497 sqlite_freemem(err);
498 return false;
499 }
500
rollbackTransaction()501 bool QSQLite2Driver::rollbackTransaction()
502 {
503 Q_D(QSQLite2Driver);
504 if (!isOpen() || isOpenError())
505 return false;
506
507 char* err;
508 int res = sqlite_exec(d->access, "ROLLBACK", 0, this, &err);
509
510 if (res == SQLITE_OK)
511 return true;
512
513 setLastError(QSqlError(tr("Unable to rollback transaction"),
514 QString::fromLatin1(err), QSqlError::TransactionError, res));
515 sqlite_freemem(err);
516 return false;
517 }
518
tables(QSql::TableType type) const519 QStringList QSQLite2Driver::tables(QSql::TableType type) const
520 {
521 QStringList res;
522 if (!isOpen())
523 return res;
524
525 QSqlQuery q(createResult());
526 q.setForwardOnly(true);
527 if ((type & QSql::Tables) && (type & QSql::Views))
528 q.exec(QLatin1String("SELECT name FROM sqlite_master WHERE type='table' OR type='view'"));
529 else if (type & QSql::Tables)
530 q.exec(QLatin1String("SELECT name FROM sqlite_master WHERE type='table'"));
531 else if (type & QSql::Views)
532 q.exec(QLatin1String("SELECT name FROM sqlite_master WHERE type='view'"));
533
534 if (q.isActive()) {
535 while(q.next())
536 res.append(q.value(0).toString());
537 }
538
539 if (type & QSql::SystemTables) {
540 // there are no internal tables beside this one:
541 res.append(QLatin1String("sqlite_master"));
542 }
543
544 return res;
545 }
546
primaryIndex(const QString & tblname) const547 QSqlIndex QSQLite2Driver::primaryIndex(const QString &tblname) const
548 {
549 QSqlRecord rec(record(tblname)); // expensive :(
550
551 if (!isOpen())
552 return QSqlIndex();
553
554 QSqlQuery q(createResult());
555 q.setForwardOnly(true);
556 QString table = tblname;
557 if (isIdentifierEscaped(table, QSqlDriver::TableName))
558 table = stripDelimiters(table, QSqlDriver::TableName);
559 // finrst find a UNIQUE INDEX
560 q.exec(QLatin1String("PRAGMA index_list('") + table + QLatin1String("');"));
561 QString indexname;
562 while(q.next()) {
563 if (q.value(2).toInt()==1) {
564 indexname = q.value(1).toString();
565 break;
566 }
567 }
568 if (indexname.isEmpty())
569 return QSqlIndex();
570
571 q.exec(QLatin1String("PRAGMA index_info('") + indexname + QLatin1String("');"));
572
573 QSqlIndex index(table, indexname);
574 while(q.next()) {
575 QString name = q.value(2).toString();
576 QVariant::Type type = QVariant::Invalid;
577 if (rec.contains(name))
578 type = rec.field(name).type();
579 index.append(QSqlField(name, type, tblname));
580 }
581 return index;
582 }
583
record(const QString & tbl) const584 QSqlRecord QSQLite2Driver::record(const QString &tbl) const
585 {
586 if (!isOpen())
587 return QSqlRecord();
588 QString table = tbl;
589 if (isIdentifierEscaped(tbl, QSqlDriver::TableName))
590 table = stripDelimiters(table, QSqlDriver::TableName);
591
592 QSqlQuery q(createResult());
593 q.setForwardOnly(true);
594 q.exec(QLatin1String("SELECT * FROM ") + tbl + QLatin1String(" LIMIT 1"));
595 return q.record();
596 }
597
handle() const598 QVariant QSQLite2Driver::handle() const
599 {
600 Q_D(const QSQLite2Driver);
601 return QVariant::fromValue(d->access);
602 }
603
escapeIdentifier(const QString & identifier,IdentifierType) const604 QString QSQLite2Driver::escapeIdentifier(const QString &identifier, IdentifierType /*type*/) const
605 {
606 QString res = identifier;
607 if(!identifier.isEmpty() && !identifier.startsWith(QLatin1Char('"')) && !identifier.endsWith(QLatin1Char('"')) ) {
608 res.replace(QLatin1Char('"'), QLatin1String("\"\""));
609 res.prepend(QLatin1Char('"')).append(QLatin1Char('"'));
610 res.replace(QLatin1Char('.'), QLatin1String("\".\""));
611 }
612 return res;
613 }
614
615 QT_END_NAMESPACE
616