Storing your data in a database

Now that the data classes are ready, we can proceed to implement the database layer. Qt provides a ready-to-use sql module. Various databases are supported in Qt using SQL database drivers. In gallery-desktop, we will use the SQLITE3 driver, which is included in the sql module and perfectly fits the use case:

• A very simple database schema: No need for complex queries
• Very few or no concurrent transactions: No need for a complex transaction model
• A single-purpose database: No need to spawn a system service, the database is stored in a single file and does not need to be accessed by multiple applications

The database will be accessed from multiple locations; we need to have a single entry point for it. Create a new C++ class named DatabaseManager and modify DatabaseManager.h to look like this:

#include <QString> 
 
class QSqlDatabase; 
 
const QString DATABASE_FILENAME = "gallery.db"; 
 
class DatabaseManager 
{ 
public: 
    static DatabaseManager& instance(); 
    ~DatabaseManager(); 
 
protected: 
    DatabaseManager(const QString& path = DATABASE_FILENAME); 
    DatabaseManager& operator=(const DatabaseManager& rhs); 
 
private: 
    QSqlDatabase* mDatabase; 
}; 

The first thing to notice is that we implement the singleton pattern in the DatabaseManager class, like we did in the Transforming SysInfo in a singleton section from Chapter 10, Discovering QMake Secrets. The DatabaseManager class will open the connection in the mDatabase field and lend it to other possible classes.

Also, QSqlDatabase is forward-declared and used as a pointer for the mDatabase field. We could have included the QSqlDatabase header, but we would have had a non-desired side-effect: every file, which includes DatabaseManager, must also include QSqlDatabase. Thus, if we ever have some transitive inclusion in our application (which links to the gallery-core library), the application is forced to enable the sql module. As a consequence, the storage layer leaks through the library. The application should not have any knowledge about the storage layer implementation. For all the application cares, it could be in SQL, XML, or anything else; the library is a black box that should honor the contract and persist the data.

Let's switch to DatabaseManager.cpp and open the database connection:

#include "DatabaseManager.h" 
 
#include <QSqlDatabase> 
 
DatabaseManager& DatabaseManager::instance() 
{ 
    static DatabaseManager singleton; 
    return singleton; 
} 
 
DatabaseManager::DatabaseManager(const QString& path) : 
    mDatabase(new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE"))) 
{ 
    mDatabase->setDatabaseName(path); 
    mDatabase->open(); 
} 
 
DatabaseManager::~DatabaseManager() 
{ 
    mDatabase->close(); 
    delete mDatabase; 
} 

The correct database driver is selected on the mDatabase field initialization with the QSqlDatabase::addDatabase("QSQLITE") function call. The following steps are just a matter of configuring the database name (which is incidentally the file path in SQLITE3) and opening the connection with the mDatabase->open() function. In the DatabaseManager destructor, the connection is closed and the mDatabase pointer is properly deleted.

The database link is now opened; all we have to do is to execute our Album and Picture queries. Implementing the CRUD (Create/Read/Update/Delete) for both our data classes in DatabaseManager would quickly push DatabaseManager.cpp to be several hundreds of lines long. Add a few more tables and you can already see what a monster DatabaseManager would turn into.

For this reason, each of our data classes will have a dedicated database class, responsible for all the database CRUD operations. We will start with the Album class; create a new C++ class named AlbumDao (data access object) and update AlbumDao.h:

class QSqlDatabase; 
 
class AlbumDao 
{ 
public: 
    AlbumDao(QSqlDatabase& database); 
    void init() const; 
 
private: 
    QSqlDatabase& mDatabase; 
}; 

The AlbumDao class's constructor takes a QSqlDatabase& parameter. This parameter is the database connection that will be used for all the SQL queries done by the AlbumDao class. The init() function aims to create the albums table and should be called when mDatabase is opened.

Let's see the implementation of AlbumDao.cpp:

#include <QSqlDatabase> 
#include <QSqlQuery> 
 
#include "DatabaseManager.h" 
 
AlbumDao::AlbumDao(QSqlDatabase& database) : 
    mDatabase(database) 
{ 
} 
 
void AlbumDao::init() const 
{ 
    if (!mDatabase.tables().contains("albums")) { 
        QSqlQuery query(mDatabase); 
        query.exec("CREATE TABLE albums (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)"); 
    } 
} 

As usual, the mDatabase field is initialized with the database parameter. In the init() function, we can see a real SQL request in action. If the table albums class does not exist, a QSqlQuery query is created that will use the mDatabase connection to be executed. If you omit mDatabase, the query will use a default anonymous connection. The query.exec() function is the simplest manner of executing a query: you simply pass the QString type of your query and it's done. Here we create the albums table with the fields matching the data class Album (id and name).

The skeleton of AlbumDao class is done. The next step is to link it to the DatabaseManager class. Update the DatabaseManager class like so:

// In DatabaseManager.h 
#include "AlbumDao.h" 
 
... 
 
private: 
    QSqlDatabase* mDatabase; 
 
public: 
    const AlbumDao albumDao; 
}; 
 
// In DatabaseManager.cpp 
DatabaseManager::DatabaseManager(const QString& path) : 
    mDatabase(new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE"))), 
    albumDao(*mDatabase) 
{ 
    mDatabase->setDatabaseName(path); 
    mDatabase->open(); 
 
    albumDao.init(); 
} 

The albumDao field is declared as a public const AlbumDao in the DatabaseManager.h file. This needs some explanation:

• The public visibility is to give access to DatabaseManager clients to the albumDao field. The API becomes intuitive enough; if you want to make a database operation on an album, just call DatabaseManager::instance().albumDao.
• The const keyword is to make sure that nobody can modify albumDao. Because it is public, we cannot guarantee the safety of the object (anybody could modify the object). As a side-effect, we force every public function of AlbumDao to be const. This makes sense; after all, the AlbumDao field could have been a namespace with a bunch of functions. It is more convenient for it to be a class because we can keep the reference to the database connection with the mDatabase field.

In the DatabaseManager constructor, the albumDao class is initialized with the mDatabase dereferenced pointer. The albumDao.init() function is called after the database connection has opened.

We can now proceed to implement more interesting SQL queries. We can start with the creation of a new album in the AlbumDao class:

// In AlbumDao.h 
class QSqlDatabase; 
class Album; 
 
class AlbumDao 
{ 
public: 
    AlbumDao(QSqlDatabase& database); 
    void init() const; 
     
    void addAlbum(Album& album) const; 
    ... 
}; 
 
// In AlbumDao.cpp 
 
#include <QSqlDatabase> 
#include <QSqlQuery> 
#include <QVariant> 
 
... 
 
void AlbumDao::addAlbum(Album& album) const 
{ 
    QSqlQuery query(mDatabase); 
    query.prepare("INSERT INTO albums (name) VALUES (:name)"); 
    query.bindValue(":name", album.name()); 
    query.exec(); 
    album.setId(query.lastInsertId().toInt()); 
} 

The addAlbum() function takes an album parameter to extract its information and execute the corresponding query. Here, we approach the prepared query notion: the query.prepare() function takes a query parameter which contains placeholders for parameters provided later. We will provide the name parameter with the syntax :name. Two syntaxes are supported: Oracle style with a colon-name (for example, :name) or ODBC style with a question mark (for example, ?name).

We then bind the bind :name syntax to the value of the album.name() function. Because QSqlQuery::bind() expects a QVariant as a parameter value, we have to add the include directive to this class.

In a nutshell, a QVariant is a generic data holder that accepts a wide range of primitive types (char, int, double, and so on) and complex types (QString, QByteArray, QUrl, and so on).

When the query.exec() function is executed, the bound values are properly replaced. The prepare() statement technique makes the code more robust to SQL injection (injecting a hidden request would fail) and more readable.

The execution of the query modifies the state of the object query itself. The QSqlQuery query is not simply a SQL query executor, it also contains the state of the active query. We can retrieve information about the query with the query.lastInsertId() function, which returns a QVariant value containing the ID of the album row we just inserted. This id is given to the album provided in the addAlbum() parameter. Because we modify album, we cannot mark the parameter as const. Being strict about the const correctness of your code is a good hint for a fellow developer, who can deduce that your function might (or not) modify the passed parameter.

The remaining update and delete operations follow strictly the same pattern used for addAlbum(). We will just provide the expected function signatures in the next code snippet. Please refer to the source code of the chapter for the complete implementation. However, we need to implement the request to retrieve all the albums in the database. This one deserves a closer look:

// In AlbumDao.h 
#include <QVector> 
 
    ... 
    void addAlbum(Album& album) const; 
    void updateAlbum(const Album& album) const; 
    void removeAlbum(int id) const; 
    QVector<Album*> albums() const; 
    ... 
}; 
 
// In AlbumDao.cpp 
QVector<Album*> AlbumDao::albums() const 
{ 
    QSqlQuery query("SELECT * FROM albums", mDatabase); 
    query.exec(); 
    QVector<Album*> list; 
    while(query.next()) { 
        Album* album = new Album(); 
        album->setId(query.value("id").toInt()); 
        album->setName(query.value("name").toString()); 
        list.append(album); 
    } 
    return list; 
} 

The albums() function must return a QVector<Album*> value. If we take a look at the body of the function, we see yet another property of QSqlQuery. To walk through multiple rows for a given request, query handles an internal cursor pointing to the current row. We can then proceed to create a new Album*() function and fill it with the row data with the query.value() statement, which takes a column name parameter and returns a QVariant value that is casted to the proper type. This new album parameter is appended to the list and, finally, this list is returned to the caller.

The PictureDao class is very similar to the AlbumDao class, both in usage and implementation. The main difference is that a picture has a foreign key to an album. The PictureDao function must be conditioned by an albumId parameter. The following code snippet shows the PictureDao header and the init() function:

// In PictureDao.h 
#include <QVector> 
 
class QSqlDatabase; 
class Picture; 
 
class PictureDao 
{ 
public: 
    explicit PictureDao(QSqlDatabase& database); 
    void init() const; 
 
    void addPictureInAlbum(int albumId, Picture& picture) const; 
    void removePicture(int id) const; 
    void removePicturesForAlbum(int albumId) const; 
    QVector<Picture*> picturesForAlbum(int albumId) const; 
 
private: 
    QSqlDatabase& mDatabase; 
}; 
 
// In PictureDao.cpp 
void PictureDao::init() const 
{ 
    if (!mDatabase.tables().contains("pictures")) { 
        QSqlQuery query(mDatabase); 
        query.exec(QString("CREATE TABLE pictures") 
        + " (id INTEGER PRIMARY KEY AUTOINCREMENT, " 
        + "album_id INTEGER, " 
        + "url TEXT)"); 
    } 
} 

As you can see, multiple functions take an albumId parameter to make the link between the picture and the owning album parameter. In the init() function, the foreign key is expressed in the album_id INTEGER syntax. SQLITE3 does not have a proper foreign key type. It is a very simple database and there is no strict constraint for this type of field; a simple integer is used.

Finally, the PictureDao function is added in the DatabaseManager class exactly as we did for albumDao. One could argue that, if there are a lot of Dao classes, adding a const Dao member in the DatabaseManager class and calling the init() function quickly becomes cumbersome.

A possible solution could be to make an abstract Dao class, with a pure virtual init() function. The DatabaseManager class would have a Dao registry, which maps each Dao to a QString key with a QHash<QString, const Dao> mDaos. The init() function call would then be called in a for loop and a Dao object would be accessed using the QString key. This is outside the scope of this project, but is nevertheless an interesting approach.