Index Page - Previous Module - Next Module

Module 5: Downloading and storing data

Our shopping list app has grown a lot, but if you close the app, all your data are gone. In this module we’ll add persistent storage, and we’ll also teach you how to enrich your app with data downloaded from online sources.

You can find this module’s code of the app in its own GitLab repository.

1. Introduction

Adding items to your shopping list is nice, and removing them when you’ve bought the items is even nicer. But until now this only works as long as you keep the app open. If you close the app, its state is lost. Every time you start the app, the list model is initialized with empty data.

In this module we’ll introduce a database. Every time we change the list model, we’ll also write the changes to this database. And when the app starts, we’ll initialize the list model from this database. Under the hood, this database is just stored as a file.

We’ll also show you how to download data from online sources and use these data in your app. This is useful to download prices for the items you add to your shopping list.

2. Using JSON as a data format

Before we start downloading data, it’s good to take a step back. We need a data interchange format so the server knows what data to send and the client knows what to expect. A popular data interchange format for communication between (web) applications and (web) servers is JSON (JavaScript Object Notation).

JSON is both easy for machines to parse and generate and easy for humans to read and write. It’s built on two structures:

A collection of name/value pairs
An ordered list of values (array)

Each JSON object begins with a left brace and end with a right brace. Each name is a string (between quotes), followed by a colon (:) and then its value. This value can be a string, number, boolean, array or even an object itself. Name/value pairs are separated by a comma. An array begins with a left bracket and ends with a right bracket, and its values are separated by a comma.

You already saw an example of JSON in our shopping list app. In the addItem method we added to the list model, we called the list model’s append method with the following argument: {"name": name, "selected": selected}. For readability purposes JSON is often formatted with each name/value pair on its own line and some indentation. Then this becomes:

{
    "name": name,
    "selected": selected
}

You see the braces at the beginning and end of the object, and the two name/value pairs. In this case there’s a name name (formatted as a string with the quotes around it) that has name as its value, and a name selected (formatted as a string) that has selected as its value. Both values come from the arguments of the addItem method. So if you call this method as shoppinglistModel.addItem("apples", false), the JSON object becomes:

{
    "name": "apples",
    "selected": false
}

How do you know that the list model’s append method accepts a JSON object? Just consult the ListModel.append method. It shows a jsobject data type for its argument.

3. Downloading data with an XMLHttpRequest

Many apps communicate with an accompanying server to get some information. This can be a server operated by the app developer, or a third-party server with an open API (application programming interface).

QML defines the XMLHttpRequest object to obtain data over a network. It implements the XMLHttpRequest standard implemented by many popular web browsers.

For the purpose of this course we’ve set up a PHP script on http://apishoppinglist.codefounders.nl/itemprice.php that returns a JSON object with the name and (random) price of your shopping list item if you initiate a request.

You can try this out: just visit http://apishoppinglist.codefounders.nl/itemprice.php?itemname=apples in your web browser, and it will show you the result. It probably shows this in a tree view or a tabular form, but in most web browsers you can also ask for the raw data. This will then show:

{"name":"apples","price":8.59}

This is a JSON object with names name and price.

3.1. Getting an item’s price with an XMLHttpRequest

Let’s see how we can get this JSON object from our QML app with the XMLHttpRequest object.

We’ll extend the MainView object with a new property for the URL and a getItemPrice method to download the price for a given item:

property string itemPriceURL: "http://apishoppinglist.codefounders.nl/itemprice.php?itemname="

function getItemPrice(item) {
	var xhr = new XMLHttpRequest();
	xhr.onreadystatechange = function() {
		if(xhr.readyState === XMLHttpRequest.DONE) {
			var result = JSON.parse(xhr.responseText.toString());
			item.price = result.price;
		}
	}

	xhr.open("GET", itemPriceURL + encodeURIComponent(item.name));
	xhr.send();
}

There’s a lot happening in this code, so let’s dissect this line by line.

First we add a new string property to the MainView object to store the URL of the service to get the item price. This isn’t strictly necessary, but it’s cleaner code than just using the URL directly further. If later the URL of the service changes, you’ll easily find where you need to change it in your code. Especially if you’re using this URL in multiple places, defining it in a property is recommended: you only need to change it in one place then.

Then we define the function getItemPrice, which has one argument: an item from our shopping list. In this function, we create a new XMLHttpRequest object and call it xhr.

After this, we assign a function to the xhr object’s onreadystatechange event handler. This function doesn’t have a name: we define it just as function() with its body between braces. This anonymous function is called every time the xhr object’s readyState changes value.

In this case, we’re just interested in one value: XMLHttpRequest.DONE. According to the XMLHttpRequest standard’s documentation, the object is in this state when the data transfer has been completed or something went wrong during the transfer.

So when the xhr object is in the XMLHttpRequest.DONE state, we get its response text, convert it to a string and then parse it as a JSON object with the built-in JavaScript function JSON.parse(). We store this JSON object in the variable result. We can then access the value of the price name as result.price, and we assign it to item.price to set the item’s price.

Now this was all just an event handler. Nothing is happening yet. We first have to set the request method and request URL. The former is "GET" and the latter is composed of the URL we defined as the property itemPriceURL with the item’s name added.

Every time you add a text that’s not under your control to a URL, you need to call the built-in JavaScript function encodeURIComponent() on it to replace invalid characters by escape sequences.

Then, at last, the xhr object’s send() method initiates the request. Now when the data transfer is done, the onreadystatechange event handler is called and the item’s price is set to the price download from the URL.

3.2. Adding the price to the list model

So, now we have this function to download the price of an item, but we don’t do anything with it. Our list model doesn’t even include a price. So let’s change this.

First we add a role price to the list elements. So the method addItem of the ListModel object becomes:

function addItem(name, selected) {
	shoppinglistModel.append({"name": name, "price": 0, "selected": selected});
	getItemPrice(shoppinglistModel.get(shoppinglistModel.count - 1));
}

First we append a new item with roles name and selected set according to the arguments with the same names, and a role price with value 0. Then we call the getItemPrice function with the last element of the list model as its argument. We know the item we just added is the last element because the append method adss the item to the end of the list.

Now we need tho show the price next to the name of the item. Add the following Text object in the ListItem object, between the existing Text object and the MouseArea object:

Text {
	text: price
	anchors {
		right: parent.right
		rightMargin: units.gu(2)
		verticalCenter: parent.verticalCenter
	}
}

Rebuild the app again and run it on your desktop. If you add a new item to your shopping list and then click on the button next to the text field, the onClicked signal handler calls shoppinglistModel.addItem(textFieldInput.text, false);. This appends the new item to the list model with a price 0, and then gets the item’s price from the online source and changes the item’s price to this downloaded price. You’ll see the price appearing next to the item’s name immediately.

3.3. Giving your app permission to access the network

We’ve glossed over one important detail: app permissions. If you test your app on your desktop machine with clickable desktop, this doesn’t matter. But if you tested the app on your Ubuntu Touch phone, you’d have noticed that it always shows 0 as the price for any item you add to your shopping list.

The reason is that Ubuntu Touch apps are confined by default with quite restrictive permissions. This doesn’t even include permission to access the network by default. So our XMLHttpRequest to the backend server won’t be allowed.

Ubuntu Touch uses AppArmor policy groups to specify the permissions given to an app. So if you need network access in your app, you need to add the networking permission to your app. This is done in the AppArmor file, which has your app’s name and the apparmor extension, in this case shoppinglist.apparmor. By default it looks like this:

{
    "policy_groups": [],
    "policy_version": 16.04
}

Now if you want to add the networking policy group, change this file to this:

{
    "policy_groups": ["networking"],
    "policy_version": 16.04
}

If you now rebuild your app and run it on your phone with clickable, it will be able to reach the backend server to show prices next to the shopping list items.

4. The basics of an SQL database

We want to store our list elements so our app doesn’t lose our shopping list when we close it. A natural way to do this is with a database. The most common sort of database is a relational database, which presents data as a collection of one or more tables. Each table has a set of rows and columns.

This type of database fits our list model naturally: you could consider each list element as a row in a table, with columns for the item’s name, price and selection status. However, we shouldn’t store the item’s price, as the canonical source of the price is the URL we used earlier. So if we load items from our database, we can just redownload the price from this source.

So our database table could look like this:

Table 1. A shopping list in a relational database
name	selected
apples	true
water	false
bread	false
bananas	true
milk	false

A row is called a record in a relational database.

Many relational databases work with SQL (Structured Query Language, pronounced 'sequel') for querying and maintaining the database. It’s a standard language to create new databases and tables, retrieve data, insert, update and delete records. Often applications that need a database use SQLite, a lightweight SQL database engine.

Most tables in a typical SQLite database schema are rowid tables. In such a table, every record in the table has a unique, non-NULL, signed 64-bit integer rowid that is used as the access key for the record.

The previous table would look like this in an SQL database with rowid:

Table 2. A shopping list in an SQLite database
rowid	name	selected
1	apples	true
2	water	false
3	bread	false
4	bananas	true
5	milk	false

Now let’s try this with a temporary SQLite database. You can install SQLite in Ubuntu with:

sudo apt install sqlite3

Make sure to install the package sqlite3 and not sqlite. The latter is an older version that doesn’t support all SQL statements we need.

Now just run the following command to connect to an in-memory database that we can use to try out some stuff:

sqlite3

This shows you the following prompt:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Let’s say we want to create a table with name and selected columns. Then we can do this with the following SQL statement:

CREATE TABLE IF NOT EXISTS ShoppingList (name TEXT, selected BOOLEAN);

This creates a table with name ShoppingList where each record has a name column of type TEXT and a selected column of type BOOLEAN. Moreover, this table is only created if it doesn’t exist already.

Now that we have this table in our SQLite database, we can add an item with the following SQL statement:

INSERT INTO ShoppingList (name, selected) VALUES ("apples", true);

In this INSERT statement you specify the table name, column names and then the values for these columns.

Now add the other records too. After this, what if you want to see all these values, including their rowid? Just execute the following SQL statement:

SELECT rowid, name, selected FROM ShoppingList;

This shows you:

1|apples|1
2|water|0
3|bread|0
4|bananas|1
5|milk|0

The first number is the rowid, while the last number is the value of selected. Note that SQLite has translated the boolean value true to 1 and false to 0.

What if you want to change an existing record, for instance its selection status? Then you first need to know the record’s rowid, and then you can update the record with the following SQL statement:

UPDATE ShoppingList SET selected=true WHERE rowid=3;

If you now show the records again with the previous SELECT statement, you see that the selection status of the bread (with rowid 3) is changed to 1.

And what if you want to remove a specific record? Just use the DELETE statement with the correct rowid:

DELETE FROM ShoppingList WHERE rowid=3;

If you now show all records, you’ll see that the bread with rowid 3 has vanished.

The rowid of the records following the deleted record doesn’t change. So you now have records with rowid 1, 2, 4 and 5.

With the same DELETE statement you can also remove all records with their selection state set to true:

DELETE FROM ShoppingList WHERE selected=true;

You now only have two records left: water and milk.

And if you don’t add any condition to the DELETE statement, it just removes every record from the table:

DELETE FROM ShoppingList;

Now that you have experimented with SQL in a temporary SQLite database, just quit the SQLite command line with Ctrl+D or the .quit command.

5. Using an SQLite database in QML with LocalStorage

QML offers access to an SQLite database in your app with the LocalStorage object. Just import it like this in the beginning of your Main.qml:

import QtQuick.LocalStorage 2.7

Now add some properties to your MainView object:

property string dbName: "ShoppingListDB"
property string dbVersion: "1.0"
property string dbDescription: "Database for shopping list app"
property int dbEstimatedSize: 10000
property var db: LocalStorage.openDatabaseSync(dbName, dbVersion, dbDescription, dbEstimatedSize)
property string shoppingListTable: "ShoppingList"

With the openDatabaseSync method from the LocalStorage object you open or create a database with the specified arguments:

dbName: The name of the file in which the SQLite database is stored.
dbVersion: The version of the database. You can use this if you change the database schema and want to upgrade it later, but we’re not using it in this app.
dbDescription: A description of the database. This is not used by Qt.
dbEstimatedSize: The estimated size of the database in bytes. This is not used by Qt.

If the database does not already exist, the method creates one and writes its properties to an INI file. It the database does exist, the method opens the existing database.

The last property we added contains the name of the table for the shopping list items in the database.

If you run your app with these properties added, it creates two files in /home/username/.clickable/home/.local/share/shoppinglist.username/Databases/: a file with .sqlite extension that holds the (now empty) database, and a file with .ini extension with some properties of the database. The latter looks like this:

[General]
Description=Database for shopping list app
Driver=QSQLITE
EstimatedSize=10000
Name=ShoppingListDB
Version=1.0

You’ll notice the properties you’ve set in your QML file.

Now that your app has a database, we need to do something with it, like creating a table, adding items, seeing items, updating items and removing items. You’ve already seen the SQL statements you need to do these actions in the previous section.

5.1. Reading list items from the database

The first thing that needs to happen when your app starts up is creating the database table if it doesn’t exist yet, getting all data from the table and add an item to the list model for each record in the table. Let’s implement this as the following function in your MainView element:

function initializeShoppingList() {

	db.transaction(function(tx) {
			tx.executeSql('CREATE TABLE IF NOT EXISTS ' + shoppingListTable + ' (name TEXT, selected BOOLEAN)');
			var results = tx.executeSql('SELECT rowid, name, selected FROM ' + shoppingListTable);

			// Update ListModel
			for (var i = 0; i < results.rows.length; i++) {
				shoppinglistModel.append({"rowid": results.rows.item(i).rowid,
											"name": results.rows.item(i).name,
											"price": 0,
											"selected": Boolean(results.rows.item(i).selected)
										});
				getItemPrice(shoppinglistModel.get(shoppinglistModel.count - 1));
			}
		}
	)
}

As you see, we first create a table if it doesn’t exist and then select the rowid, name and selection status from all records in the shopping list table. Then we’re iterating over all elements from these results, extract the needed columns and add a new list element to the list model with these values.

Note that we need to add the rowid as a role to the list element to reliably link your list items to records in the tablie. We’ll have to change other functions in the code accordingly, but we’ll get to that.

Now we want this function to be called when your app starts. How do we do this? Luckily every QML object emits the Component.completed attached signal when its instantiation is complete. The JavaScript code in the corresponding Component.onCompleted attached signal handler runs after the object is instantiated. So let’s add this to the `Page' element:

Component.onCompleted: initializeShoppingList()

Now whenever the page is ready, the shopping list gets initialized with data from the database.

5.2. Adding an item to the database

The next step is adding an item to the database. Every time you click the Add button to add an item, this item should not only be added to the list model, but also as a record in the database table. So change the addItem method of your list model to:

function addItem(name, selected) {
	db.transaction(function(tx) {
			var result = tx.executeSql('INSERT INTO ' + shoppingListTable + ' (name, selected) VALUES( ?, ? )', [name, selected]);
			var rowid = Number(result.insertId);
			shoppinglistModel.append({"rowid": rowid, "name": name, "price": 0, "selected": selected});
			getItemPrice(shoppinglistModel.get(shoppinglistModel.count - 1));
		}
	)
}

So this executes a database transaction with an SQL INSERT statement. The result of an INSERT statement always has an insertId property with the rowid of the inserted element. So then we add an element to the shopping list model with this rowid, the name and selection status. After this, we update the price.

5.3. Removing items from the database

Next we’re going to remove a list item. Remember that we added this functionality as a leading action for the list items. the onTriggered signal handler just removed the item with the current index from the list model. Now that we have to synchronize this state with the database, let’s change this signal handler to:

onTriggered: shoppinglistModel.removeItem(index)

Then add the following method to the list model:

function removeItem(index) {
	var rowid = shoppinglistModel.get(index).rowid;
	db.transaction(function(tx) {
			tx.executeSql('DELETE FROM ' + shoppingListTable + ' WHERE rowid=?', [rowid]);
		}
	)
	shoppinglistModel.remove(index);
}

This first gets the rowid property of the list element, then deletes the record with this rowid from the database table, and finally removes the element from the list model.

Next is the function removeSelectedItems. The approach is the same: first execute the database transaction, and then synchronize this state with the list model.

function removeSelectedItems() {
	db.transaction(function(tx) {
			tx.executeSql('DELETE FROM ' + shoppingListTable + ' WHERE selected=?', [Boolean(true)]);
		}
	)
	for(var i=shoppinglistModel.count-1; i>=0; i--) {
		if(shoppinglistModel.get(i).selected)
			shoppinglistModel.remove(i);
	}
}

We also had a dialog to remove all elements. The onDoAction signal handler just called shoppinglistModel.clear(). Replace this by the following method call:

onDoAction: shoppinglistModel.removeAllItems()

And then create this method:

function removeAllItems() {
	db.transaction(function(tx) {
			tx.executeSql('DELETE FROM ' + shoppingListTable);
		}
	)
	shoppinglistModel.clear();
}

5.4. Updating the selection status in the database

We’re almost there. We just have to implement one situation: updating the selection status. This happens in the onClicked signal handler of the mouse area. Change this to this method call:

onClicked: shoppinglistModel.toggleSelectionStatus(index)

And then create the following method:

function toggleSelectionStatus(index) {
	if(root.selectionMode) {
		var rowid = shoppinglistModel.get(index).rowid;
		var selected = !shoppinglistModel.get(index).selected;

		db.transaction(function(tx) {
				tx.executeSql('UPDATE ' + shoppingListTable + ' SET selected=? WHERE rowid=?', [Boolean(selected), rowid]);
			}
		)

		shoppinglistModel.get(index).selected = selected;
		shoppinglistView.refresh();
	}
}

So this first gets the rowid from the clicked element and toggles its selection status. Then it updates the corresponding record with the new selection status, changes the selected role of the list element and then refreshes the list view. All this is only done if the app is in selection mode.

5.5. Testing your app’s database access

Now with all these database transactions set up, rebuild your app and run it. Try all these actions (adding an item, removing a single item, removing selected items, removing all items, selecting items), and close and reopen your app. Your shopping list should be reinitialized on starting the app as you’ve left if after closing the app.

Open the SQLite database with the command sqlite3 /home/username/.clickable/home/.local/share/shoppinglist.username/Databases/uuid.sqlite (adapt the path to your own situation) after your application has been closed and have a look at the contents of the database table with the SELECT rowid, name, selected FROM ShoppingList; statement.

Index Page - Previous Module - Next Module