Create the database class

The final task in our tutorial is to create the QuotesDbHelper class, which lets us access the SQL database where our quote data is stored. This class uses Qt functions and classes to access the file system on the device directly. It also uses Cascades data access APIs (such as the SqlDataAccess class) in conjunction with Qt SQL APIs (such as the QSqlDatabase class) to read and update our SQL database.

In this section, we won't go into detail about all of the classes and functions that are used in QuotesDbHelper. Several comments are included in the code samples to help guide you through the code. If you'd like to learn more about accessing the file system and storing data, see Working with the file system and Data.

Create the QuotesDbHelper class

In the src folder of your project, create a class called QuotesDbHelper. Similar to when you created the QuotesApp class, don't worry about adding method stubs. We'll start by implementing our functions in the .cpp file, so open the QuotesDbHelper.cpp file.

At the top of the file, we include the associated header, QuotesDbHelper.h, and use the bb::data namespace. The class constructor is empty. The class destructor performs a couple of clean-up operations on our SQL databases to make sure that our app frees its resources properly. The mDb object is an instance of the QSqlDatabase class, which we use to interact with the database in the file system.

#include "quotesdbhelper.h"

using namespace bb::data;

QuotesDbHelper::QuotesDbHelper()
{
}

QuotesDbHelper::~QuotesDbHelper()
{
    if (mDb.isOpen()) {
        QSqlDatabase::removeDatabase(mDbNameWithPath);
        mDb.removeDatabase("QSQLITE");
    }
}

Implement the copy function

Depending on the location of files in your project, your app might have read-only access to the files, or it might have read-write access. For example, for a file that's included in the assets folder of your project, your app typically has read-only access to this file. In our app, we placed our database file, quotes.db, in the assets/sql folder. Because we need to have read-write access to the database file to insert, update, or delete quote records, we create a function called copyDbToDataFolder(). This function copies our database file from the assets folder to the data folder. After the file is copied to the data folder, our app has full read-write access to the file.

bool QuotesDbHelper::copyDbToDataFolder(const QString
                                                   databaseName)
{
    // First, we check to see if the file already exists in the
    // data folder (that is, the file was copied already).
    QString dataFolder = QDir::homePath();
    QString newFileName = dataFolder + "/" + databaseName;
    QFile newFile(newFileName);

    if (!newFile.exists()) {
        // If the file is not already in the data folder, we copy
        // it from the assets folder (read-only) to the data folder
        // (read-write).
        QString appFolder(QDir::homePath());
        appFolder.chop(4);
        QString originalFileName = appFolder + "app/native/assets/sql/"
                                   + databaseName;
        QFile originalFile(originalFileName);

        if (originalFile.exists()) {
            return originalFile.copy(newFileName);
        } else {
            qDebug() << "Failed to copy file, database file does not exist.";
            return false;
        }
    }

    return true;
}

Implement the load function

Next, we implement the loadDataBase() function. We copy the database file to the data folder using the copyDbToDataFolder() function above, and then set up an SqlDataAccess object that points to the database file. This object lets us execute SQL queries, such as SELECT, on the database. We call execute() to retrieve all of the entries in the specified table (in our case, the quotes table), and we store them in a QVariantList. We also make sure that no errors have occurred.

QVariantList QuotesDbHelper::loadDataBase(const QString
                                                    databaseName,
                                                    const QString table)
{
    QVariantList sqlData;

    if (copyDbToDataFolder(databaseName)) {
        // Load database entries using an SqlDataAccess object into a
        // QVariantList, which can be used in a GroupDataModel to
        // display a sorted list.
        mDbNameWithPath = "data/" + databaseName;

        // Set up an SqlDataAccess object.
        SqlDataAccess sqlDataAccess(mDbNameWithPath);

        // Set a query to obtain all entries in the table and load into
        // our QVariantList.
        sqlData = sqlDataAccess.execute("select * from " + table)
                               .value<QVariantList>();

        if (sqlDataAccess.hasError()) {
            DataAccessError err = sqlDataAccess.error();
            qWarning() << "SQL error: type=" << err.errorType() << ": "
                       << err.errorMessage();
            return sqlData;
        }

We can use our SqlDataAccess object to read entries from the database file, but we need to set up another database connection (using QSqlDatabase) to allow us to insert, update, and delete database entries. By using QSqlDatabase to set up another connection, we won't conflict with the connection that's already set up using SqlDataAccess. We make sure that this second connection was created successfully, and then we open the database using this connection.

mDb = QSqlDatabase::addDatabase("QSQLITE",
                                    "database_helper_connection");
        mDb.setDatabaseName(mDbNameWithPath);

        if (!mDb.isValid()) {
            qWarning() << "Could not set database name, probably due to an invalid driver.";
            return sqlData;
        }

        bool success = mDb.open();

        if (!success) {
            qWarning() << "Could not open database.";
            return sqlData;
        }

        // Store the name of the table (used in the insert/update/delete
        // functions).
        mTable = table;
    }

    return sqlData;
}

Implement the delete function

Our next function, deleteById(), simply deletes a record by using the DELETE query. To perform the actual deletion, we pass the query to another function, queryDatabase(), which we'll implement a little later on.

bool QuotesDbHelper::deleteById(QVariant id)
{
    // Query for deleting an entry in the table.
    if (id.canConvert(QVariant::String)) {
        QString query = "DELETE FROM " + mTable + " WHERE id=" +
                        id.toString();
        return queryDatabase(query);
    }

    qWarning() << "Failed to delete item with id: " << id;

    return false;
}

Implement the insert and update functions

The two functions that write to the database, insert() and update(), both have a similar structure. We use the prepare() function of the QSqlQuery class to prepare our query. This function makes it easier to construct and prepare an SQL query, especially when the query is complex. For example, a single quotation mark (') inside a double quotation mark (") is difficult to handle if you don't bind your values using the prepare() function. Here's how to implement the insert() function:

QVariant QuotesDbHelper::insert(QVariantMap map)
{

    QSqlQuery sqlQuery(mDb);

    sqlQuery.prepare("INSERT INTO " + mTable + " (firstname, lastname, quote)" "VALUES(:firstName, :lastName, :quote)");

    sqlQuery.bindValue(":firstName", map["firstname"]);
    sqlQuery.bindValue(":lastName", map["lastname"]);
    sqlQuery.bindValue(":quote", map["quote"]);
    sqlQuery.exec();

    QSqlError err = sqlQuery.lastError();

    if (err.isValid()) {
        qWarning() << "SQL reported an error : " << err.text();
    }

    return sqlQuery.lastInsertId();
}

Here's how we construct the update() function:

bool QuotesDbHelper::update(QVariantMap map)
{
    QSqlQuery sqlQuery(mDb);


    sqlQuery.prepare("UPDATE " + mTable + " SET firstname=:firstName, lastname=:lastName, quote=:quote WHERE id=:id");

    sqlQuery.bindValue(":firstName", map["firstname"]);
    sqlQuery.bindValue(":lastName", map["lastname"]);
    sqlQuery.bindValue(":quote", map["quote"]);
    sqlQuery.bindValue(":id", map["id"].toString());
    sqlQuery.exec();

    QSqlError err = sqlQuery.lastError();

    if (!err.isValid()) {
        return true;
    }

    qWarning() << "SQL reported an error : " << err.text();

    return false;
}

Implement the query function

We have one last function to implement, queryDatabase(). This function performs the actual deletion for the deleteById() function above.

bool QuotesDbHelper::queryDatabase(const QString query)
{

    // Execute the query.
    QSqlQuery sqlQuery(query, mDb);

    QSqlError err = sqlQuery.lastError();

    if (err.isValid()) {
        qWarning() << "SQL reported an error for query: "
                   << query << " error: "
                   << mDb.lastError().text();
        return false;
    }

    return true;
}

Complete the QuotesDbHelper header file

Now that we've finished the function implementations, we need to complete the associated QuotesDbHelper.h file. Open the QuotesDbHelper.h file in your project's src folder. Similar to the QuotesApp.h file, the contents of this file are straightforward.

#ifndef _QUOTESDBHELPER_H_
#define _QUOTESDBHELPER_H_

#include <QtSql/QtSql>
#include <bb/data/SqlDataAccess>

using namespace bb::data;

class QuotesDbHelper
{
public:
    QuotesDbHelper();
    ~QuotesDbHelper();

    QVariantList loadDataBase(const QString databaseName,
                              const QString table);
    bool deleteById(QVariant id);
    QVariant insert(QVariantMap map);
    bool update(QVariantMap map);

private:
    bool copyDbToDataFolder(const QString databaseName);
    bool queryDatabase(const QString query);

    QSqlDatabase mDb;
    QString mTable;
    QString mDbNameWithPath;
};

#endif

Add the data library to your project

The code for our Quotes app is complete, but there's one additional thing that we need to do. To use classes in the bb::data namespace, we need to add the appropriate library to our project. You can add additional libraries in the .pro file that's included in the root folder of the project.

Open the .pro file in your project. This file should have the same name as the project itself. In this file, add the following line below the CONFIG line:

LIBS   += -lbbdata

That's it! Build and run the project to see the final result.

Screen showing the main screen of the Quotes app.
Screen showing an individual quote in the Quotes app.

Last modified: 2014-06-24



Got questions about leaving a comment? Get answers from our Disqus FAQ.

comments powered by Disqus