Development for Sailfish OS: Work with LocalStorage

Hello! This article is a continuation of a series of articles devoted to the development of the mobile platform Sailfish OS. This time we decided to talk about the application for the control of finance, allowing the user to keep a journal of income and expenses, as well as save funds for the implementation of the objectives. It is worth mentioning that this application is one of the winners of the hackathon by Sailfish OS in Yaroslavl, organized by the Open Mobile Platform company and the FRUCT association.

Description

Our application includes two separate modules. The first one is designed to work directly with operations. The second allows the user to create goals and track progress of accumulation.

The module for working with operations allows the user to record income and expenses, as well as display these operations in the form of a log:

As you can see from the screenshot of the add screen, for each operation a category is defined. This classification helps the user to more easily navigate their finances. In addition to the standard categories, the user can add their own, thereby adjusting the application to your lifestyle.

In addition, the application provides the ability to view statistics for different periods of time, so that the user can analyze their expenses and further optimize them:

The second module of the application provides the ability to create tasks for the accumulation of funds for any purpose. The user can record in the application information about how much he spends funds for a particular purpose,
and track progress, thereby further motivating yourself to perform your tasks:

Work with database

In this article, it was decided to focus on working with the database directly from QML files. To accomplish this task, the LocalStorage library was used, which allows organizing access to SQLite databases stored on the device.
')
To separate the logic of working with the database from the view elements, a QML object was created for data access (data access object or simply DAO ), which controls all connections to the database and provides a more convenient interface for working with data. Database connections are opened using the LocalStorage global singleton object . It calls the openDatabaseSync () method, which directly opens the connection or creates a database if it was not created earlier. All connections are automatically closed when garbage collection. The following is a portion of the Dao.qml file code:

import QtQuick 2.0 import QtQuick.LocalStorage 2.0 Item { Component.onCompleted: { database = LocalStorage.openDatabaseSync("SaveYourMoneyDatabase", "1.0") } //... } 

Transactions and requests for working with the database are designed as JS functions: to receive
results callback functions are required that are called upon completion of operations. On the resulting connection object, you can call the readTransaction () and transaction () methods, which create a transaction to read or modify data and pass it to the callback function specified as an argument to these methods. Inside these functions, executeSql () methods containing SQL queries can be called.

In our application, we needed to create a database with three tables: TransactionsTable for storing operations, GoalTable for purposes and CategoriesTable for categories of operations:

 Component.onCompleted: { database = LocalStorage.openDatabaseSync("SaveYourMoneyDatabase", "1.0") database.transaction(function(tx) { tx.executeSql("CREATE TABLE IF NOT EXISTS TransactionsTable( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, sum INTEGER, category_id INTEGER, type INTEGER, goal_id INTEGER, description TEXT)"); tx.executeSql("CREATE TABLE IF NOT EXISTS CategoriesTable( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, type INTEGER)"); tx.executeSql("CREATE TABLE IF NOT EXISTS GoalTable( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, sum INTEGER, isFinished INTEGER)"); // ... } }); } 

It is worth mentioning that the database creation and initialization operations need to be placed inside the Component.onCompleted signal handler for execution at the time the Dao object is created.

The method that adds a new entry to the table of operations, looks like this:

 function createTransaction(date, sum, category_id, type, description) { database.transaction(function(tx) { tx.executeSql("INSERT INTO TransactionsTable(date, sum, category_id, type, description) VALUES(?, ?, ?, ?, ?)", [date, sum, category_id, type, description]); }); } 

The method that extracts the list of targets will look like this:

 function retrieveGoals(isFinished, callback) { database = LocalStorage.openDatabaseSync("SaveYourMoneyDatabase", "1.0"); database.readTransaction(function(tx) { var result = tx.executeSql("SELECT * FROM GoalTable WHERE isFinished = ? ORDER BY id ASC", [isFinished]); callback(result.rows) }); } 

Here, the result of executeSql () is the object containing the rows property with a list of all the resulting records. To get the i-th element, just call the rows.item (i) method. The number of elements is available by rows.length property. An example of using the described method:

 Dao { id: dao } SilicaListView { id: listView model: GoalListModel { id: goalsListModel } delegate: ListItem { // ... } // ... } function displayGoals() { listView.model.clear(); dao.retrieveGoals(true, function(goals) { for (var i = 0; i < goals.length; i++) { var goal = goals.item(i); listView.model.addGoal(goal.id, goal.name, goal.sum); } }); } Component.onCompleted: displayGoals(); 

In this code, it is clear that the field values ​​of the record from the database are available as properties of the object. Given this, a query with a calculated field will look like:

 function retrieveGoalStatistics(callback) { database = LocalStorage.openDatabaseSync("SaveYourMoneyDatabase", "1.0"); database.readTransaction(function(tx) { var result = tx.executeSql("SELECT SUM(sum) AS goalSum FROM GoalTable WHERE isFinished = 0"); callback(result.rows.item(0).goalSum) }); } 

Also in our application added filtering records by date. As you know, SQLite does not have a standard type for working with dates. Instead, SQLite supports five functions for working with date and time. All dates inside the database are stored as strings in ISO8601 format. An example of a request to retrieve the date of the earliest operation is as follows:

 function retrieveDateOfFirstTransaction(callback) { database = LocalStorage.openDatabaseSync("SaveYourMoneyDatabase", "1.0"); database.readTransaction(function(tx) { var result = tx.executeSql("SELECT MIN(date(date)) as minDate FROM TransactionsTable"); callback(result.rows.item(0).minDate) }); } 

On the side of QML, when working with dates, the QML class Date is inherited from the Date class from Javascript. To continue working with the result of the query, you must perform:

 dao.retrieveDateOfFirstTransaction(function(result){ startDate = new Date(result); }); 

To convert an object back to a string in ISO8601 format, you must use the toISOString () method.

Conclusion

As a result, an application was created that allows you to store information about the user's finances. The application was published in the app store Jolla Harbor called Save Your money and is available for download to everyone. Sources of the application are available on Bitbucket .

Technical issues can also be discussed on the Sailfish OS Russian-speaking community channel in a Telegram or VKontakte group .

The author: Daria Roychikova