1/****************************************************************************
2**
3** Copyright (C) 2019 The Qt Company Ltd.
4** Contact: https://www.qt.io/licensing/
5**
6** This file is part of the test suite 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 "qquicklocalstorage_p.h"
41
42#include <QtQml/private/qqmlengine_p.h>
43#include <QtQml/private/qv4global_p.h>
44#include <QtQml/private/qv4scopedvalue_p.h>
45#include <QtQml/private/qv4object_p.h>
46#include <QtQml/private/qv4sqlerrors_p.h>
47#include <QtQml/private/qv4jscall_p.h>
48#include <QtQml/private/qv4objectiterator_p.h>
49
50#include <QtCore/qfileinfo.h>
51#include <QtCore/qdir.h>
52
53#include <QtSql/qsqldatabase.h>
54#include <QtSql/qsqlquery.h>
55#include <QtSql/qsqlrecord.h>
56#include <QtSql/qsqlerror.h>
57
58#if QT_CONFIG(settings)
59#include <QtCore/qsettings.h>
60#endif
61
62QT_BEGIN_NAMESPACE
63
64#define V4THROW_SQL(error, desc) { \
65 QV4::ScopedString v(scope, scope.engine->newString(desc)); \
66 QV4::ScopedObject ex(scope, scope.engine->newErrorObject(v)); \
67 ex->put(QV4::ScopedString(scope, scope.engine->newIdentifier(QStringLiteral("code"))).getPointer(), QV4::ScopedValue(scope, Value::fromInt32(error))); \
68 scope.engine->throwError(ex); \
69 RETURN_UNDEFINED(); \
70}
71
72#define V4THROW_SQL2(error, desc) { \
73 QV4::ScopedString v(scope, scope.engine->newString(desc)); \
74 QV4::ScopedObject ex(scope, scope.engine->newErrorObject(v)); \
75 ex->put(QV4::ScopedString(scope, scope.engine->newIdentifier(QStringLiteral("code"))).getPointer(), QV4::ScopedValue(scope, Value::fromInt32(error))); \
76 args->setReturnValue(scope.engine->throwError(ex)); \
77 return; \
78}
79
80#define V4THROW_REFERENCE(string) { \
81 QV4::ScopedString v(scope, scope.engine->newString(string)); \
82 scope.engine->throwReferenceError(v); \
83 RETURN_UNDEFINED(); \
84}
85
86
87class QQmlSqlDatabaseData : public QV4::ExecutionEngine::Deletable
88{
89public:
90 QQmlSqlDatabaseData(QV4::ExecutionEngine *engine);
91 ~QQmlSqlDatabaseData() override;
92
93 QV4::PersistentValue databaseProto;
94 QV4::PersistentValue queryProto;
95 QV4::PersistentValue rowsProto;
96};
97
98V4_DEFINE_EXTENSION(QQmlSqlDatabaseData, databaseData)
99
100namespace QV4 {
101
102namespace Heap {
103 struct QQmlSqlDatabaseWrapper : public Object {
104 enum Type { Database, Query, Rows };
105 void init()
106 {
107 Object::init();
108 type = Database;
109 database = new QSqlDatabase;
110 version = new QString;
111 sqlQuery = new QSqlQuery;
112 }
113
114 void destroy() {
115 delete database;
116 delete version;
117 delete sqlQuery;
118 Object::destroy();
119 }
120
121 Type type;
122 QSqlDatabase *database;
123
124 QString *version; // type == Database
125
126 bool inTransaction; // type == Query
127 bool readonly; // type == Query
128
129 QSqlQuery *sqlQuery; // type == Rows
130 bool forwardOnly; // type == Rows
131 };
132}
133
134class QQmlSqlDatabaseWrapper : public Object
135{
136public:
137 V4_OBJECT2(QQmlSqlDatabaseWrapper, Object)
138 V4_NEEDS_DESTROY
139
140 static Heap::QQmlSqlDatabaseWrapper *create(QV4::ExecutionEngine *engine)
141 {
142 return engine->memoryManager->allocate<QQmlSqlDatabaseWrapper>();
143 }
144
145 ~QQmlSqlDatabaseWrapper() {
146 }
147
148 static ReturnedValue virtualGet(const Managed *m, PropertyKey id, const Value *receiver, bool *hasProperty);
149};
150
151}
152
153using namespace QV4;
154
155DEFINE_OBJECT_VTABLE(QV4::QQmlSqlDatabaseWrapper);
156
157
158
159static ReturnedValue qmlsqldatabase_version(const FunctionObject *b, const Value *thisObject, const QV4::Value *, int)
160{
161 Scope scope(b);
162 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
163 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Database)
164 V4THROW_REFERENCE("Not a SQLDatabase object");
165
166 RETURN_RESULT(Encode(scope.engine->newString(*r->d()->version)));
167}
168
169static ReturnedValue qmlsqldatabase_rows_length(const FunctionObject *b, const Value *thisObject, const QV4::Value *, int)
170{
171 Scope scope(b);
172 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
173 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Rows)
174 V4THROW_REFERENCE("Not a SQLDatabase::Rows object");
175
176 int s = r->d()->sqlQuery->size();
177 if (s < 0) {
178 // Inefficient
179 if (r->d()->sqlQuery->last()) {
180 s = r->d()->sqlQuery->at() + 1;
181 } else {
182 s = 0;
183 }
184 }
185 RETURN_RESULT(Encode(s));
186}
187
188static ReturnedValue qmlsqldatabase_rows_forwardOnly(const FunctionObject *b, const Value *thisObject, const QV4::Value *, int)
189{
190 Scope scope(b);
191 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
192 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Rows)
193 V4THROW_REFERENCE("Not a SQLDatabase::Rows object");
194 RETURN_RESULT(Encode(r->d()->sqlQuery->isForwardOnly()));
195}
196
197static ReturnedValue qmlsqldatabase_rows_setForwardOnly(const FunctionObject *b, const Value *thisObject, const Value *argv, int argc)
198{
199 Scope scope(b);
200 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
201 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Rows)
202 V4THROW_REFERENCE("Not a SQLDatabase::Rows object");
203 if (argc < 1)
204 RETURN_RESULT(scope.engine->throwTypeError());
205
206 r->d()->sqlQuery->setForwardOnly(argv[0].toBoolean());
207 RETURN_UNDEFINED();
208}
209
210QQmlSqlDatabaseData::~QQmlSqlDatabaseData()
211{
212}
213
214static ReturnedValue qmlsqldatabase_rows_index(const QQmlSqlDatabaseWrapper *r, ExecutionEngine *v4, quint32 index, bool *hasProperty = nullptr)
215{
216 Scope scope(v4);
217
218 if (r->d()->sqlQuery->at() == (int)index || r->d()->sqlQuery->seek(i: index)) {
219 QSqlRecord record = r->d()->sqlQuery->record();
220 // XXX optimize
221 ScopedObject row(scope, v4->newObject());
222 for (int ii = 0; ii < record.count(); ++ii) {
223 QVariant v = record.value(i: ii);
224 ScopedString s(scope, v4->newIdentifier(text: record.fieldName(i: ii)));
225 ScopedValue val(scope, v.isNull() ? Encode::null() : v4->fromVariant(v));
226 row->put(name: s.getPointer(), v: val);
227 }
228 if (hasProperty)
229 *hasProperty = true;
230 return row.asReturnedValue();
231 } else {
232 if (hasProperty)
233 *hasProperty = false;
234 return Encode::undefined();
235 }
236}
237
238ReturnedValue QQmlSqlDatabaseWrapper::virtualGet(const Managed *m, PropertyKey id, const Value *receiver, bool *hasProperty)
239{
240 if (!id.isArrayIndex())
241 return Object::virtualGet(m, id, receiver, hasProperty);
242
243 uint index = id.asArrayIndex();
244 Q_ASSERT(m->as<QQmlSqlDatabaseWrapper>());
245 const QQmlSqlDatabaseWrapper *r = static_cast<const QQmlSqlDatabaseWrapper *>(m);
246 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Rows)
247 return Object::virtualGet(m, id, receiver, hasProperty);
248
249 return qmlsqldatabase_rows_index(r, v4: r->engine(), index, hasProperty);
250}
251
252static ReturnedValue qmlsqldatabase_rows_item(const FunctionObject *b, const Value *thisObject, const Value *argv, int argc)
253{
254 Scope scope(b);
255 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
256 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Rows)
257 V4THROW_REFERENCE("Not a SQLDatabase::Rows object");
258
259 RETURN_RESULT(qmlsqldatabase_rows_index(r, scope.engine, argc ? argv[0].toUInt32() : 0));
260}
261
262static QVariant toSqlVariant(QV4::ExecutionEngine *engine, const QV4::ScopedValue &value)
263{
264 // toVariant() maps a null JS value to QVariant(VoidStar), but the SQL module
265 // expects a null variant. (this is because of QTBUG-40880)
266 if (value->isNull())
267 return QVariant();
268 return engine->toVariant(value, /*typehint*/typeHint: -1);
269}
270
271static ReturnedValue qmlsqldatabase_executeSql(const FunctionObject *b, const Value *thisObject, const Value *argv, int argc)
272{
273 Scope scope(b);
274 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
275 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Query)
276 V4THROW_REFERENCE("Not a SQLDatabase::Query object");
277
278 if (!r->d()->inTransaction)
279 V4THROW_SQL(SQLEXCEPTION_DATABASE_ERR,QQmlEngine::tr("executeSql called outside transaction()"));
280
281 QSqlDatabase db = *r->d()->database;
282
283 QString sql = argc ? argv[0].toQString() : QString();
284
285 if (r->d()->readonly && !sql.startsWith(s: QLatin1String("SELECT"),cs: Qt::CaseInsensitive)) {
286 V4THROW_SQL(SQLEXCEPTION_SYNTAX_ERR, QQmlEngine::tr("Read-only Transaction"));
287 }
288
289 QSqlQuery query(db);
290 bool err = false;
291
292 ScopedValue result(scope, Value::undefinedValue());
293
294 if (query.prepare(query: sql)) {
295 if (argc > 1) {
296 ScopedValue values(scope, argv[1]);
297 if (values->as<ArrayObject>()) {
298 ScopedArrayObject array(scope, values);
299 quint32 size = array->getLength();
300 QV4::ScopedValue v(scope);
301 for (quint32 ii = 0; ii < size; ++ii) {
302 query.bindValue(pos: ii, val: toSqlVariant(engine: scope.engine, value: (v = array->get(idx: ii))));
303 }
304 } else if (values->as<Object>()) {
305 ScopedObject object(scope, values);
306 ObjectIterator it(scope, object, ObjectIterator::EnumerableOnly);
307 ScopedValue key(scope);
308 QV4::ScopedValue val(scope);
309 while (1) {
310 key = it.nextPropertyName(value: val);
311 if (key->isNull())
312 break;
313 QVariant v = toSqlVariant(engine: scope.engine, value: val);
314 if (key->isString()) {
315 query.bindValue(placeholder: key->stringValue()->toQString(), val: v);
316 } else {
317 Q_ASSERT(key->isInteger());
318 query.bindValue(pos: key->integerValue(), val: v);
319 }
320 }
321 } else {
322 query.bindValue(pos: 0, val: toSqlVariant(engine: scope.engine, value: values));
323 }
324 }
325 if (query.exec()) {
326 QV4::Scoped<QQmlSqlDatabaseWrapper> rows(scope, QQmlSqlDatabaseWrapper::create(engine: scope.engine));
327 QV4::ScopedObject p(scope, databaseData(engine: scope.engine)->rowsProto.value());
328 rows->setPrototypeUnchecked(p.getPointer());
329 rows->d()->type = Heap::QQmlSqlDatabaseWrapper::Rows;
330 *rows->d()->database = db;
331 *rows->d()->sqlQuery = query;
332
333 ScopedObject resultObject(scope, scope.engine->newObject());
334 result = resultObject.asReturnedValue();
335 // XXX optimize
336 ScopedString s(scope);
337 ScopedValue v(scope);
338 resultObject->put(name: (s = scope.engine->newIdentifier(text: "rowsAffected")).getPointer(), v: (v = Value::fromInt32(i: query.numRowsAffected())));
339 resultObject->put(name: (s = scope.engine->newIdentifier(text: "insertId")).getPointer(), v: (v = scope.engine->newString(s: query.lastInsertId().toString())));
340 resultObject->put(name: (s = scope.engine->newIdentifier(text: "rows")).getPointer(), v: rows);
341 } else {
342 err = true;
343 }
344 } else {
345 err = true;
346 }
347 if (err)
348 V4THROW_SQL(SQLEXCEPTION_DATABASE_ERR,query.lastError().text());
349
350 RETURN_RESULT(result->asReturnedValue());
351}
352
353struct TransactionRollback {
354 QSqlDatabase *db;
355 bool *inTransactionFlag;
356
357 TransactionRollback(QSqlDatabase *database, bool *transactionFlag)
358 : db(database)
359 , inTransactionFlag(transactionFlag)
360 {
361 if (inTransactionFlag)
362 *inTransactionFlag = true;
363 }
364
365 ~TransactionRollback()
366 {
367 if (inTransactionFlag)
368 *inTransactionFlag = false;
369 if (db)
370 db->rollback();
371 }
372
373 void clear() {
374 db = nullptr;
375 if (inTransactionFlag)
376 *inTransactionFlag = false;
377 inTransactionFlag = nullptr;
378 }
379};
380
381
382static ReturnedValue qmlsqldatabase_changeVersion(const FunctionObject *b, const Value *thisObject, const Value *argv, int argc)
383{
384 Scope scope(b);
385 if (argc < 2)
386 RETURN_UNDEFINED();
387
388 Scoped<QQmlSqlDatabaseWrapper> r(scope, *thisObject);
389 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Database)
390 V4THROW_REFERENCE("Not a SQLDatabase object");
391
392 QSqlDatabase db = *r->d()->database;
393 QString from_version = argv[0].toQString();
394 QString to_version = argv[1].toQString();
395 ScopedFunctionObject callback(scope, argc > 2 ? argv[2] : Value::undefinedValue());
396
397 if (from_version != *r->d()->version)
398 V4THROW_SQL(SQLEXCEPTION_VERSION_ERR, QQmlEngine::tr("Version mismatch: expected %1, found %2").arg(from_version).arg(*r->d()->version));
399
400 bool ok = true;
401 if (!!callback) {
402 Scoped<QQmlSqlDatabaseWrapper> query(scope, QQmlSqlDatabaseWrapper::create(engine: scope.engine));
403 ScopedObject p(scope, databaseData(engine: scope.engine)->queryProto.value());
404 query->setPrototypeUnchecked(p.getPointer());
405 query->d()->type = Heap::QQmlSqlDatabaseWrapper::Query;
406 *query->d()->database = db;
407 *query->d()->version = *r->d()->version;
408
409 ok = false;
410 db.transaction();
411
412 JSCallData jsCall(scope, 1);
413 *jsCall->thisObject = scope.engine->globalObject;
414 jsCall->args[0] = query;
415
416 TransactionRollback rollbackOnException(&db, &query->d()->inTransaction);
417 callback->call(data: jsCall);
418 rollbackOnException.clear();
419 if (!db.commit()) {
420 db.rollback();
421 V4THROW_SQL(SQLEXCEPTION_UNKNOWN_ERR,QQmlEngine::tr("SQL transaction failed"));
422 } else {
423 ok = true;
424 }
425 }
426
427 if (ok) {
428 Scoped<QQmlSqlDatabaseWrapper> w(scope, QQmlSqlDatabaseWrapper::create(engine: scope.engine));
429 ScopedObject p(scope, databaseData(engine: scope.engine)->databaseProto.value());
430 w->setPrototypeUnchecked(p.getPointer());
431 w->d()->type = Heap::QQmlSqlDatabaseWrapper::Database;
432 *w->d()->database = db;
433 *w->d()->version = to_version;
434#if QT_CONFIG(settings)
435 const QQmlEnginePrivate *enginePrivate = QQmlEnginePrivate::get(e: scope.engine->qmlEngine());
436 QSettings ini(enginePrivate->offlineStorageDatabaseDirectory() + db.connectionName() + QLatin1String(".ini"), QSettings::IniFormat);
437 ini.setValue(key: QLatin1String("Version"), value: to_version);
438#endif
439 RETURN_RESULT(w.asReturnedValue());
440 }
441
442 RETURN_UNDEFINED();
443}
444
445static ReturnedValue qmlsqldatabase_transaction_shared(const FunctionObject *b, const Value *thisObject, const Value *argv, int argc, bool readOnly)
446{
447 Scope scope(b);
448 QV4::Scoped<QQmlSqlDatabaseWrapper> r(scope, thisObject->as<QQmlSqlDatabaseWrapper>());
449 if (!r || r->d()->type != Heap::QQmlSqlDatabaseWrapper::Database)
450 V4THROW_REFERENCE("Not a SQLDatabase object");
451
452 const FunctionObject *callback = argc ? argv[0].as<FunctionObject>() : nullptr;
453 if (!callback)
454 V4THROW_SQL(SQLEXCEPTION_UNKNOWN_ERR, QQmlEngine::tr("transaction: missing callback"));
455
456 QSqlDatabase db = *r->d()->database;
457
458 Scoped<QQmlSqlDatabaseWrapper> w(scope, QQmlSqlDatabaseWrapper::create(engine: scope.engine));
459 QV4::ScopedObject p(scope, databaseData(engine: scope.engine)->queryProto.value());
460 w->setPrototypeUnchecked(p.getPointer());
461 w->d()->type = Heap::QQmlSqlDatabaseWrapper::Query;
462 *w->d()->database = db;
463 *w->d()->version = *r->d()->version;
464 w->d()->readonly = readOnly;
465
466 db.transaction();
467 if (callback) {
468 JSCallData jsCall(scope, 1);
469 *jsCall->thisObject = scope.engine->globalObject;
470 jsCall->args[0] = w;
471 TransactionRollback rollbackOnException(&db, &w->d()->inTransaction);
472 callback->call(data: jsCall);
473 rollbackOnException.clear();
474
475 if (!db.commit())
476 db.rollback();
477 }
478
479 RETURN_UNDEFINED();
480}
481
482static ReturnedValue qmlsqldatabase_transaction(const FunctionObject *f, const Value *thisObject, const Value *argv, int argc)
483{
484 return qmlsqldatabase_transaction_shared(b: f, thisObject, argv, argc, readOnly: false);
485}
486
487static ReturnedValue qmlsqldatabase_read_transaction(const FunctionObject *f, const Value *thisObject, const Value *argv, int argc)
488{
489 return qmlsqldatabase_transaction_shared(b: f, thisObject, argv, argc, readOnly: true);
490}
491
492QQmlSqlDatabaseData::QQmlSqlDatabaseData(ExecutionEngine *v4)
493{
494 Scope scope(v4);
495 {
496 ScopedObject proto(scope, v4->newObject());
497 proto->defineDefaultProperty(QStringLiteral("transaction"), code: qmlsqldatabase_transaction);
498 proto->defineDefaultProperty(QStringLiteral("readTransaction"), code: qmlsqldatabase_read_transaction);
499 proto->defineAccessorProperty(QStringLiteral("version"), getter: qmlsqldatabase_version, setter: nullptr);
500 proto->defineDefaultProperty(QStringLiteral("changeVersion"), code: qmlsqldatabase_changeVersion);
501 databaseProto = proto;
502 }
503
504 {
505 ScopedObject proto(scope, v4->newObject());
506 proto->defineDefaultProperty(QStringLiteral("executeSql"), code: qmlsqldatabase_executeSql);
507 queryProto = proto;
508 }
509 {
510 ScopedObject proto(scope, v4->newObject());
511 proto->defineDefaultProperty(QStringLiteral("item"), code: qmlsqldatabase_rows_item);
512 proto->defineAccessorProperty(QStringLiteral("length"), getter: qmlsqldatabase_rows_length, setter: nullptr);
513 proto->defineAccessorProperty(QStringLiteral("forwardOnly"),
514 getter: qmlsqldatabase_rows_forwardOnly, setter: qmlsqldatabase_rows_setForwardOnly);
515 rowsProto = proto;
516 }
517}
518
519/*
520HTML5 "spec" says "rs.rows[n]", but WebKit only impelments "rs.rows.item(n)". We do both (and property iterator).
521We add a "forwardOnly" property that stops Qt caching results (code promises to only go forward
522through the data.
523*/
524
525
526/*!
527 \qmlmodule QtQuick.LocalStorage 2.\QtMinorVersion
528 \title Qt Quick Local Storage QML Types
529 \ingroup qmlmodules
530 \brief Provides a JavaScript object singleton type for accessing a local
531 SQLite database
532
533 This is a singleton type for reading and writing to SQLite databases.
534
535
536 \section1 Methods
537
538 \list
539 \li object \b{\l{#openDatabaseSync}{openDatabaseSync}}(string name, string version, string description, int estimated_size, jsobject callback(db))
540 \endlist
541
542
543 \section1 Detailed Description
544
545 To use the types in this module, import the module and call the
546 relevant functions using the \c LocalStorage type:
547
548 \qml \QtMinorVersion
549 import QtQuick 2.\1
550 import QtQuick.LocalStorage 2.\1
551
552 Item {
553 Component.onCompleted: {
554 var db = LocalStorage.openDatabaseSync(...)
555 }
556 }
557 \endqml
558
559
560These databases are user-specific and QML-specific, but accessible to all QML applications.
561They are stored in the \c Databases subdirectory
562of QQmlEngine::offlineStoragePath(), currently as SQLite databases.
563
564Database connections are automatically closed during Javascript garbage collection.
565
566The API can be used from JavaScript functions in your QML:
567
568\snippet qml/localstorage/hello.qml 0
569
570The API conforms to the Synchronous API of the HTML5 Web Database API,
571\link http://www.w3.org/TR/2009/WD-webdatabase-20091029/ W3C Working Draft 29 October 2009\endlink.
572
573The \l{Qt Quick Examples - Local Storage}{SQL Local Storage example} demonstrates the basics of
574using the Offline Storage API.
575
576\section3 Open or Create a Database
577
578\qml \QtMinorVersion
579import QtQuick.LocalStorage 2.\1 as Sql
580
581db = Sql.openDatabaseSync(identifier, version, description, estimated_size, callback(db))
582\endqml
583
584The above code returns the database identified by \e identifier. If the database does not already exist, it
585is created, and the function \e callback is called with the database as a parameter. \e identifier is the
586name of the physical file (with or without full path) containing the database. \e description and
587\e estimated_size are written to the INI file (described below), but are currently unused.
588
589May throw exception with code property SQLException.DATABASE_ERR, or SQLException.VERSION_ERR.
590
591When a database is first created, an INI file is also created specifying its characteristics:
592
593\table
594\header \li \b {Key} \li \b {Value}
595\row \li Identifier \li The name of the database passed to \c openDatabase()
596\row \li Version \li The version of the database passed to \c openDatabase()
597\row \li Description \li The description of the database passed to \c openDatabase()
598\row \li EstimatedSize \li The estimated size (in bytes) of the database passed to \c openDatabase()
599\row \li Driver \li Currently "QSQLITE"
600\endtable
601
602This data can be used by application tools.
603
604\section3 db.changeVersion(from, to, callback(tx))
605
606This method allows you to perform a \e{Scheme Upgrade}. If it succeeds it returns a new
607database object of version \e to. Otherwise it returns \e undefined.
608
609If the current version of \e db is not \e from, then an exception is thrown.
610
611Otherwise, a database transaction is created and passed to \e callback. In this function,
612you can call \e executeSql on \e tx to upgrade the database.
613
614May throw exception with code property SQLException.DATABASE_ERR or SQLException.UNKNOWN_ERR.
615
616See example below.
617
618\badcode
619 var db = LocalStorage.openDatabaseSync("ActivityTrackDB", "", "Database tracking sports activities", 1000000);
620 if (db.version == "0.1") {
621 db.changeVersion("0.1", "0.2", function(tx) {
622 tx.executeSql("INSERT INTO trip_log VALUES(?, ?, ?)",
623 [ "01/10/2016","Sylling - Vikersund", "53" ]);
624 }
625 });
626\endcode
627
628\section3 db.transaction(callback(tx))
629
630This method creates a read/write transaction and passed to \e callback. In this function,
631you can call \e executeSql on \e tx to read and modify the database.
632
633If the callback throws exceptions, the transaction is rolled back.
634Below you will find an example of a database transaction which catches exceptions.
635
636
637\quotefromfile localstorage/localstorage/Database.js
638\skipuntil dbInit()
639\printto dbGetHandle
640
641In the example you can see an \c insert statement where values are assigned to the fields,
642and the record is written into the table. That is an \c insert statement with a syntax that is usual
643for a relational database. It is however also possible to work with JSON objects and
644store them in a table.
645
646Let's suppose a simple example where we store trips in JSON format using \c date as the unique key.
647An example of a table that could be used for that purpose:
648
649\badcode
650 create table trip_log(date text, data text)
651\endcode
652
653The assignment of values to a JSON object:
654
655\badcode
656 var obj = {description = "Vikersund - Noresund", distance = "60"}
657\endcode
658
659In that case, the data could be saved in the following way:
660
661\badcode
662 db.transaction(function(tx) {
663 result = tx.executeSQL("insert into trip_log values (?,?)",
664 ["01/11/2016", JSON.stringify(obj)])
665
666\endcode
667
668\section3 db.readTransaction(callback(tx))
669
670This method creates a read-only transaction and passed to \e callback. In this function,
671you can call \e executeSql on \e tx to read the database (with \c select statements).
672
673\section3 results = tx.executeSql(statement, values)
674
675This method executes an SQL \e statement, binding the list of \e values to SQL positional parameters ("?").
676
677It returns a results object, with the following properties:
678
679\table
680\header \li \b {Type} \li \b {Property} \li \b {Value} \li \b {Applicability}
681\row \li int \li rows.length \li The number of rows in the result \li SELECT
682\row \li var \li rows.item(i) \li Function that returns row \e i of the result \li SELECT
683\row \li int \li rowsAffected \li The number of rows affected by a modification \li UPDATE, DELETE
684\row \li string \li insertId \li The id of the row inserted \li INSERT
685\endtable
686
687May throw exception with code property SQLException.DATABASE_ERR, SQLException.SYNTAX_ERR, or SQLException.UNKNOWN_ERR.
688
689See below for an example:
690
691\quotefromfile localstorage/localstorage/Database.js
692\skipto dbReadAll()
693\printto dbUpdate(Pdate
694
695\section1 Method Documentation
696
697\target openDatabaseSync
698\code
699object openDatabaseSync(string name, string version, string description, int estimated_size, jsobject callback(db))
700\endcode
701
702Opens or creates a local storage sql database by the given parameters.
703
704\list
705\li \c name is the database name
706\li \c version is the database version
707\li \c description is the database display name
708\li \c estimated_size is the database's estimated size, in bytes
709\li \c callback is an optional parameter, which is invoked if the database has not yet been created.
710\endlist
711
712Returns the created database object.
713
714*/
715
716void QQuickLocalStorage::openDatabaseSync(QQmlV4Function *args)
717{
718#if QT_CONFIG(settings)
719 QV4::Scope scope(args->v4engine());
720 if (scope.engine->qmlEngine()->offlineStoragePath().isEmpty())
721 V4THROW_SQL2(SQLEXCEPTION_DATABASE_ERR, QQmlEngine::tr("SQL: can't create database, offline storage is disabled."));
722
723 QV4::ScopedValue v(scope);
724 QString dbname = (v = (*args)[0])->toQStringNoThrow();
725 QString dbversion = (v = (*args)[1])->toQStringNoThrow();
726 QString dbdescription = (v = (*args)[2])->toQStringNoThrow();
727 int dbestimatedsize = (v = (*args)[3])->toInt32();
728 FunctionObject *dbcreationCallback = (v = (*args)[4])->as<FunctionObject>();
729 QString basename = args->v4engine()->qmlEngine()->offlineStorageDatabaseFilePath(databaseName: dbname);
730 QFileInfo dbFile(basename);
731 if (!QDir().mkpath(dirPath: dbFile.dir().absolutePath())) {
732 const QString message = QQmlEngine::tr(s: "LocalStorage: can't create path %1").
733 arg(a: QDir::toNativeSeparators(pathName: dbFile.dir().absolutePath()));
734 V4THROW_SQL2(SQLEXCEPTION_DATABASE_ERR, message);
735 }
736 QString dbid = dbFile.fileName();
737 bool created = false;
738 QString version = dbversion;
739 QSqlDatabase database;
740
741 {
742 QSettings ini(basename+QLatin1String(".ini"),QSettings::IniFormat);
743
744 if (QSqlDatabase::connectionNames().contains(str: dbid)) {
745 database = QSqlDatabase::database(connectionName: dbid);
746 version = ini.value(key: QLatin1String("Version")).toString();
747 if (version != dbversion && !dbversion.isEmpty() && !version.isEmpty())
748 V4THROW_SQL2(SQLEXCEPTION_VERSION_ERR, QQmlEngine::tr("SQL: database version mismatch"));
749 } else {
750 created = !QFile::exists(fileName: basename+QLatin1String(".sqlite"));
751 if (created) {
752 ini.setValue(key: QLatin1String("Name"), value: dbname);
753 if (dbcreationCallback)
754 version = QString();
755 ini.setValue(key: QLatin1String("Version"), value: version);
756 ini.setValue(key: QLatin1String("Description"), value: dbdescription);
757 ini.setValue(key: QLatin1String("EstimatedSize"), value: dbestimatedsize);
758 ini.setValue(key: QLatin1String("Driver"), value: QLatin1String("QSQLITE"));
759 } else {
760 if (!dbversion.isEmpty() && ini.value(key: QLatin1String("Version")) != dbversion) {
761 // Incompatible
762 V4THROW_SQL2(SQLEXCEPTION_VERSION_ERR,QQmlEngine::tr("SQL: database version mismatch"));
763 }
764 version = ini.value(key: QLatin1String("Version")).toString();
765 }
766 database = QSqlDatabase::addDatabase(type: QLatin1String("QSQLITE"), connectionName: dbid);
767 database.setDatabaseName(basename+QLatin1String(".sqlite"));
768 }
769 if (!database.isOpen())
770 database.open();
771 }
772
773 QV4::Scoped<QQmlSqlDatabaseWrapper> db(scope, QQmlSqlDatabaseWrapper::create(engine: scope.engine));
774 QV4::ScopedObject p(scope, databaseData(engine: scope.engine)->databaseProto.value());
775 db->setPrototypeUnchecked(p.getPointer());
776 *db->d()->database = database;
777 *db->d()->version = version;
778
779 if (created && dbcreationCallback) {
780 JSCallData jsCall(scope, 1);
781 *jsCall->thisObject = scope.engine->globalObject;
782 jsCall->args[0] = db;
783 dbcreationCallback->call(data: jsCall);
784 }
785
786 args->setReturnValue(db.asReturnedValue());
787#else
788 Q_UNUSED(args)
789#endif // settings
790}
791
792QT_END_NAMESPACE
793

source code of qtdeclarative/src/imports/localstorage/qquicklocalstorage.cpp