Local Storage - SQL

Qt Quick supports a local storage API known from the web browsers the local storage API. the API is available under “import QtQuick.LocalStorage 2.0”.

In general, it stores the content into an SQLite database in a system-specific location in a unique ID based file based on the given database name and version. It is not possible to list or delete existing databases. You can find the storage location from QQmlEngine::offlineStoragePath().

You use the API by first creating a database object and then creating transactions on the database. Each transaction can contain one or more SQL queries. The transaction will roll-back when a SQL query will fail inside the transaction.

For example, to read from a simple notes table with a text column you could use the local storage like this:

  1. import QtQuick
  2. import QtQuick.LocalStorage 2.0
  4. Item {
  5.     Component.onCompleted: {
  6.         var db = LocalStorage.openDatabaseSync("MyExample", "1.0", "Example database", 10000);
  7.         db.transaction( function(tx) {
  8.             var result = tx.executeSql('select * from notes');
  9.             for(var i = 0; i < result.rows.length; i++) {
  10.                     print(result.rows[i].text);
  11.             }
  12.         });
  13.     }
  14. }

Crazy Rectangle

As an example assume we would like to store the position of a rectangle on our scene.

image

Here is the base of the example. It contains a rectange called crazy that is draggable and shows its current x and y position as text.

  1. Item {
  2.     width: 400
  3.     height: 400
  5.     Rectangle {
  6.         id: crazy
  7.         objectName: 'crazy'
  8.         width: 100
  9.         height: 100
  10.         x: 50
  11.         y: 50
  12.         color: "#53d769"
  13.         border.color: Qt.lighter(color, 1.1)
  14.         Text {
  15.             anchors.centerIn: parent
  16.             text: Math.round(parent.x) + '/' + Math.round(parent.y)
  17.         }
  18.         MouseArea {
  19.             anchors.fill: parent
  20.             drag.target: parent
  21.         }
  22.     }
  23.     // ...

You can drag the rectangle freely around. When you close the application and launch it again the rectangle is at the same position.

Now we would like to add that the x/y position of the rectangle is stored inside the SQL DB. For this, we need to add an init, read and store database function. These functions are called when on component completed and on component destruction.

  1. import QtQuick
  2. import QtQuick.LocalStorage 2.0
  4. Item {
  5.     // reference to the database object
  6.     property var db;
  8.     function initDatabase() {
  9.         // initialize the database object
  10.     }
  12.     function storeData() {
  13.         // stores data to DB
  14.     }
  16.     function readData() {
  17.         // reads and applies data from DB
  18.     }
  21.     Component.onCompleted: {
  22.         initDatabase();
  23.         readData();
  24.     }
  26.     Component.onDestruction: {
  27.         storeData();
  28.     }
  29. }

You could also extract the DB code in an own JS library, which does all the logic. This would be the preferred way if the logic gets more complicated.

In the database initialization function, we create the DB object and ensure the SQL table is created. Notice that the database functions are quite talkative so that you can follow along on the console.

  1. function initDatabase() {
  2.     // initialize the database object
  3.     print('initDatabase()')
  4.     db = LocalStorage.openDatabaseSync("CrazyBox", "1.0", "A box who remembers its position", 100000);
  5.     db.transaction( function(tx) {
  6.         print('... create table')
  7.         tx.executeSql('CREATE TABLE IF NOT EXISTS data(name TEXT, value TEXT)');
  8.     });
  9. }

The application next calls the read function to read existing data back from the database. Here we need to differentiate if there is already data in the table. To check we look into how many rows the select clause has returned.

  1. function readData() {
  2.     // reads and applies data from DB
  3.     print('readData()')
  4.     if(!db) { return; }
  5.     db.transaction( function(tx) {
  6.         print('... read crazy object')
  7.         var result = tx.executeSql('select * from data where name="crazy"');
  8.         if(result.rows.length === 1) {
  9.             print('... update crazy geometry')
  10.             // get the value column
  11.             var value = result.rows[0].value;
  12.             // convert to JS object
  13.             var obj = JSON.parse(value)
  14.             // apply to object
  15.             crazy.x = obj.x;
  16.             crazy.y = obj.y;
  17.         }
  18.     });
  19. }

We expect the data is stored in a JSON string inside the value column. This is not typical SQL like, but works nicely with JS code. So instead of storing the x,y as properties in the table, we store them as a complete JS object using the JSON stringify/parse methods. In the end, we get a valid JS object with x and y properties, which we can apply on our crazy rectangle.

To store the data, we need to differentiate the update and insert cases. We use update when a record already exists and insert if no record under the name “crazy” exists.

  1. function storeData() {
  2.     // stores data to DB
  3.     print('storeData()')
  4.     if(!db) { return; }
  5.     db.transaction( function(tx) {
  6.         print('... check if a crazy object exists')
  7.         var result = tx.executeSql('SELECT * from data where name = "crazy"');
  8.         // prepare object to be stored as JSON
  9.         var obj = { x: crazy.x, y: crazy.y };
  10.         if(result.rows.length === 1) {// use update
  11.             print('... crazy exists, update it')
  12.             result = tx.executeSql('UPDATE data set value=? where name="crazy"', [JSON.stringify(obj)]);
  13.         } else { // use insert
  14.             print('... crazy does not exists, create it')
  15.             result = tx.executeSql('INSERT INTO data VALUES (?,?)', ['crazy', JSON.stringify(obj)]);
  16.         }
  17.     });
  18. }

Instead of selecting the whole recordset we could also use the SQLite count function like this: SELECT COUNT(\*) from data where name = "crazy" which would return use one row with the number of rows affected by the select query. Otherwise, this is common SQL code. As an additional feature, we use the SQL value binding using the ? in the query.

Now you can drag the rectangle and when you quit the application the database stores the x/y position and applies it on the next application run.