SQLite

General purpose database technology has fragmented in the recent years with the explosion of NoSQL and Graph databases. However, SQL databases are still fighting fit and absolutely an appropriate choice in a lot of applications. Qt comes with built-in support for several SQL database driver types, and can be extended with custom drivers. MySQL and PostgreSQL are very popular open source SQL database engines and are both supported by default, but are intended for use on servers and require administration, which makes them a bit unnecessarily complicated for our purposes. Instead, we will use the much more lightweight SQLite, which is commonly used as a client-side database and is very popular in mobile applications due to its small footprint.

According to the official website at https://www.sqlite.org, "SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. SQLite is the most used database engine in the world". Paired with Qt's SQL related classes, it's a snap to create a database and store your data.

The first thing we need to do is add the SQL module to our library project to get access to all of Qt’s SQL goodness. In cm-lib.pro, add the following:

QT += sql

Next, we’ll take onboard what we discussed in the previous chapter and implement our database-related functionality behind an interface. Create a new i-database-controller.h header file in cm-lib/source/controllers:

#ifndef IDATABASECONTROLLER_H
#define IDATABASECONTROLLER_H

#include <QJsonArray>
#include <QJsonObject>
#include <QList>
#include <QObject>
#include <QString>

#include <cm-lib_global.h>

namespace cm {
namespace controllers {

class CMLIBSHARED_EXPORT IDatabaseController : public QObject
{
    Q_OBJECT

public:
    IDatabaseController(QObject* parent) : QObject(parent){}
    virtual ~IDatabaseController(){}

    virtual bool createRow(const QString& tableName, const QString& id, 
 const QJsonObject& jsonObject) const = 0;
    virtual bool deleteRow(const QString& tableName, const QString& id) 
 const = 0;
    virtual QJsonArray find(const QString& tableName, const QString& 
 searchText) const = 0;
    virtual QJsonObject readRow(const QString& tableName, const 
 QString& id) const = 0;
    virtual bool updateRow(const QString& tableName, const QString& id, 
 const QJsonObject& jsonObject) const = 0;
};

}}

#endif

Here, we are implementing the four basic functions of (Create, Read, Update, and Delete) CRUD, which are relevant to persistent storage in general, not just SQL databases. We supplement these functions with an additional find() method that we will use to find an array of matching clients based on supplied search text.

Now, let’s create a concrete implementation of the interface. Create a new DatabaseController class in cm-lib/source/controllers.

database-controller.h:

#ifndef DATABASECONTROLLER_H
#define DATABASECONTROLLER_H

#include <QObject>
#include <QScopedPointer>

#include <controllers/i-database-controller.h>

#include <cm-lib_global.h>

namespace cm {
namespace controllers {

class CMLIBSHARED_EXPORT DatabaseController : public IDatabaseController
{
    Q_OBJECT

public:
    explicit DatabaseController(QObject* parent = nullptr);
    ~DatabaseController();

    bool createRow(const QString& tableName, const QString& id, const 
 QJsonObject& jsonObject) const override;
    bool deleteRow(const QString& tableName, const QString& id) const 
 override;
    QJsonArray find(const QString& tableName, const QString& 
 searchText) const override;
    QJsonObject readRow(const QString& tableName, const QString& id) 
 const override;
    bool updateRow(const QString& tableName, const QString& id, const 
 QJsonObject& jsonObject) const override;

private:
    class Implementation;
    QScopedPointer<Implementation> implementation;
};

}}

#endif

Now, let's walk through each of the key implementation details in database-controller.cpp:

class DatabaseController::Implementation
{
public:
    Implementation(DatabaseController* _databaseController)
 : databaseController(_databaseController)
    {
 if (initialise()) {
            qDebug() << "Database created using Sqlite version: " + 
 sqliteVersion();
 if (createTables()) {
         qDebug() << "Database tables created";
 } else {
 qDebug() << "ERROR: Unable to create database tables";
 }
 } else {
 qDebug() << "ERROR: Unable to open database";
 }
    }

    DatabaseController* databaseController{nullptr};
    QSqlDatabase database;

private:
    bool initialise()
    {
 database = QSqlDatabase::addDatabase("QSQLITE", "cm");
 database.setDatabaseName( "cm.sqlite" );
 return database.open();
    }

    bool createTables()
    {
 return createJsonTable( "client" );
    }

    bool createJsonTable(const QString& tableName) const
    {
 QSqlQuery query(database);
 QString sqlStatement = "CREATE TABLE IF NOT EXISTS " + 
 tableName + " (id text primary key, json text not null)";

 if (!query.prepare(sqlStatement)) return false;

 return query.exec();
    }

    QString sqliteVersion() const
    {
     QSqlQuery query(database);

 query.exec("SELECT sqlite_version()");

 if (query.next()) return query.value(0).toString();

 return QString::number(-1);
    }
};

Starting with the private implementation, we’ve broken the initialization into two operations: initialise() instantiates a connection to a SQLite database with a file named cm.sqlite, and this operation will first create the database file for us if it doesn’t already exist. The file will be created in the same folder as the application executable, createTables(), then creates any tables that we need which don’t already exist in the database. Initially, we only need a single table named client, but this can be easily extended later. We delegate the actual work of creating the named table to the createJsonTable() method so that we can reuse it for multiple tables.

A conventional normalized relational database approach would be to persist each of our data models in their own table, with fields that match the properties of the class. Recall the model diagram back in Chapter 5, Data, which is as follows:

We could create a Client table with the "reference" and "name" fields, a contact table with the "type", "address", and other fields. However, we will instead leverage the JSON serialization code we’ve already implemented and implement a pseudo document-style database. We will utilize a single client table that will store a unique ID for the client along with the whole client object hierarchy serialized to JSON.

Finally, we've also added a sqliteVersion() utility method to identify which version of SQLite the database is using:

bool DatabaseController::createRow(const QString& tableName, const QString& id, const QJsonObject& jsonObject) const
{
    if (tableName.isEmpty()) return false;
    if (id.isEmpty()) return false;
    if (jsonObject.isEmpty()) return false;

    QSqlQuery query(implementation->database);

    QString sqlStatement = "INSERT OR REPLACE INTO " + tableName + " 
                            (id, json) VALUES (:id, :json)";

    if (!query.prepare(sqlStatement)) return false;

    query.bindValue(":id", QVariant(id));
    query.bindValue(":json",    
   QVariant(QJsonDocument(jsonObject).toJson(QJsonDocument::Compact)));

    if(!query.exec()) return false;

    return query.numRowsAffected() > 0;
}

bool DatabaseController::deleteRow(const QString& tableName, const QString& id) const
{
    if (tableName.isEmpty()) return false;
    if (id.isEmpty()) return false;

    QSqlQuery query(implementation->database);

    QString sqlStatement = "DELETE FROM " + tableName + " WHERE 
                            id=:id";

    if (!query.prepare(sqlStatement)) return false;

    query.bindValue(":id", QVariant(id));

    if(!query.exec()) return false;

    return query.numRowsAffected() > 0;
}

QJsonObject DatabaseController::readRow(const QString& tableName, const QString& id) const
{
    if (tableName.isEmpty()) return {};
    if (id.isEmpty()) return {};

    QSqlQuery query(implementation->database);

    QString sqlStatement = "SELECT json FROM " + tableName + " WHERE 
                            id=:id";

    if (!query.prepare(sqlStatement)) return {};

    query.bindValue(":id", QVariant(id));

    if (!query.exec()) return {};

    if (!query.first()) return {};

    auto json = query.value(0).toByteArray();
    auto jsonDocument = QJsonDocument::fromJson(json);

    if (!jsonDocument.isObject()) return {};

    return jsonDocument.object();
}

bool DatabaseController::updateRow(const QString& tableName, const QString& id, const QJsonObject& jsonObject) const
{
    if (tableName.isEmpty()) return false;
    if (id.isEmpty()) return false;
    if (jsonObject.isEmpty()) return false;

    QSqlQuery query(implementation->database);

    QString sqlStatement = "UPDATE " + tableName + " SET json=:json 
                            WHERE id=:id";

    if (!query.prepare(sqlStatement)) return false;

    query.bindValue(":id", QVariant(id));
    query.bindValue(":json", 
   QVariant(QJsonDocument(jsonObject).toJson(QJsonDocument::Compact)));

    if(!query.exec()) return false;

    return query.numRowsAffected() > 0;
}

The CRUD operations are all based around the QSqlQuery class and prepared sqlStatements. In all cases, we first perform some perfunctory checks on the parameters to ensure that we’re not trying to do something silly. We then concatenate the table name into a SQL string, representing parameters with the :myParameter syntax. After preparing the statement, parameters are subsequently substituted in using the bindValue() method on the query object.

When creating, deleting, or updating rows, we simply return a true/false success indicator on query execution. Assuming that the query prepares and executes without error, we check that the number of rows affected by the operation is greater than 0. The read operation returns a JSON object parsed from the JSON text stored in the matching record. If no record is found or if the JSON cannot be parsed, then we return a default JSON object:

QJsonArray DatabaseController::find(const QString& tableName, const QString& searchText) const
{
    if (tableName.isEmpty()) return {};
    if (searchText.isEmpty()) return {};

    QSqlQuery query(implementation->database);

    QString sqlStatement = "SELECT json FROM " + tableName + " where 
                            lower(json) like :searchText";

    if (!query.prepare(sqlStatement)) return {};

    query.bindValue(":searchText", QVariant("%" + searchText.toLower() 
                                                             + "%"));

    if (!query.exec()) return {};

    QJsonArray returnValue;

    while ( query.next() ) {
        auto json = query.value(0).toByteArray();
        auto jsonDocument = QJsonDocument::fromJson(json);
        if (jsonDocument.isObject()) {
            returnValue.append(jsonDocument.object());
        }
    }

    return returnValue;
}

Finally, the find() method does essentially the same thing as the CRUD operations but compiles an array of JSON objects as there may be more than one match. Note that we use the like keyword in the SQL statement, combined with the % wildcard character, to find any JSON that contains the search text. We also convert both sides of the comparison to lowercase to make the search effectively case-insensitive.