Qt: QSqlDatabase object in class (how to declare?)

Question

I am trying to create a class which should handle all the data from and to a sqlite database. However, I am pretty new to QT and C++ and wondering about the declaration of the database object in the class. I could need some tips on what I am doing right and wrong and how it normally should or could be done. My goal was, to create a single QSqlDatabase for the class and use it for every function within the class.

At the moment, I have the following code:

main.cpp

#include "mainwindow.h"
#include "database.h"
#include <QApplication>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    Database db;
    MainWindow w;

    if(db.createStructure())
    {
        w.show();
    }

    return a.exec();
}

database.h

#ifndef DATABASE_H
#define DATABASE_H

#include <QObject>
#include <QSqlDatabase>

class Database : public QObject
{
    Q_OBJECT
public:
    explicit Database(QObject *parent = 0);

    // FUNCTIONS
    bool createStructure();

signals:

public slots:

private:
    // VARIABLES
    QSqlDatabase m_db;

    // FUNCTIONS
    bool open();
    void close();
    bool transaction();
    bool commit();
};

#endif // DATABASE_H

database.cpp

#include "database.h"
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QList>

Database::Database(QObject *parent) :
    QObject(parent)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setHostName("localhost");
    m_db.setDatabaseName(QCoreApplication::applicationDirPath() + "/events.db");
}

// PRIVATE

bool Database::open()
{
    return m_db.open();
}

void Database::close()
{
    return m_db.close();
}

bool Database::transaction()
{
    return m_db.transaction();
}

bool Database::commit()
{
    return m_db.commit();
}

// PUBLIC

bool Database::createStructure()
{
    bool prepared;
    QList<QString> commands;

    commands.append("CREATE TABLE...;");
    commands.append("CREATE TABLE...;");
    commands.append("CREATE TABLE...;");

    if (!Database::open())
    {
        return false;
    }
    else
    {
        if (!Database::transaction())
        {
            Database::close();
            return false;
        }
        else
        {
            foreach(QString command, commands)
            {
                QSqlQuery query;
                prepared = query.prepare(command);

                if(!prepared)
                {
                    if (!Database::commit())
                    {
                        Database::close();
                        return false;
                    }
                    else
                    {
                        Database::close();
                        return false;
                    }
                }
                else
                {
                    if(!query.exec())
                    {
                        if (!Database::commit())
                        {
                            Database::close();
                            return false;
                        }
                        else
                        {
                            Database::close();
                            return false;
                        }
                    }
                }
            }

            if (!Database::commit())
            {
                Database::close();
                return false;
            }
            else
            {
                Database::close();
                return true;
            }
        }
    }
}

This code is working.

However, the QSQLITE database is not added a single time to the m_db object, but every time a function in the class is called, because the...

Database::Database(QObject *parent) :
    QObject(parent)
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setHostName("localhost");
    m_db.setDatabaseName(QCoreApplication::applicationDirPath() + "/events.db");
}

...codeblock is executed every time. The current default connection is just replaced and since the new one is the same, that doesn’t have any effect on the program, but it doesn’t look like a neat solution.

So I tried to replace this codeblock with a declare-function I can call from main.cpp once...

main.cpp

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    Database db;
    MainWindow w;

    db.declare(“QSQLITE”, “localhost”, QCoreApplication::applicationDirPath() + "/events.db");

    if(db.createStructure())
    {
        w.show();
    }

    return a.exec();
}

database.cpp

void Database::declare(QString driver, QString host, QString path)
{
    m_db = QSqlDatabase::addDatabase(driver);
    m_db.setHostName(host);
    m_db.setDatabaseName(path);
}

...but the values for the m_db object are of course only available within the declare-function and not for the other functions I call afterwards.

My best guess for a solution would be to declare the QSqlDatabase in main.cpp and give it to the function it should call:

main.cpp

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    QSqlDatabase qdb = QSqlDatabase::addDatabase("QSQLITE");
    qdb.setHostName("localhost");
    qdb.setDatabaseName(QCoreApplication::applicationDirPath() + "/events.db");

    Database db;
    MainWindow w;

    if(db.createStructure(qdb))
    {
        w.show();
    }

    return a.exec();
}

database.cpp

bool Database::open(QSqlDatabase qdb)
{
    return qdb.open();
}

void Database::close(QSqlDatabase qdb)
{
    return qdb.close();
}

bool Database::transaction(QSqlDatabase qdb)
{
    return qdb.transaction();
}

bool Database::commit(QSqlDatabase qdb)
{
    return qdb.commit();
}

bool Database::createStructure(QSqlDatabase qdb)
{
    bool prepared;
    QList<QString> commands;

    commands.append("CREATE TABLE...;");
    commands.append("CREATE TABLE...;");
    commands.append("CREATE TABLE...;");

    if (!Database::open(qdb))
    {
        return false;
    }
    else
    {
        if (!Database::transaction(qdb))
        {
            Database::close(qdb);
            return false;
        }
        else
        {
            foreach(QString command, commands)
            {
                QSqlQuery query;
                prepared = query.prepare(command);

                if(!prepared)
                {
                    if (!Database::commit(qdb))
                    {
                        Database::close(qdb);
                        return false;
                    }
                    else
                    {
                        Database::close(qdb);
                        return false;
                    }
                }
                else
                {
                    if(!query.exec())
                    {
                        if (!Database::commit(qdb))
                        {
                            Database::close(qdb);
                            return false;
                        }
                        else
                        {
                            Database::close(qdb);
                            return false;
                        }
                    }
                }
            }

            if (!Database::commit(qdb))
            {
                Database::close(qdb);
                return false;
            }
            else
            {
                Database::close(qdb);
                return true;
            }
        }
    }
}

Is it possible to somehow store a reusable QSqlDatabase object in a class? If so, how? Really appreciate your help!

EDIT 1

Some code created from the designer I am using a function in.

mainwindows.cpp

void MainWindow::on_pushButton_24_clicked()
{
    Database db;
    bool b = db.createStructure();

    QMessageBox::information(this, "test", QString(b));
}

Answer 1

I'll stick to your original code for the explanations.

---

Disclaimer: I didn't compile any of my suggestions, forgive me if there are syntax errors.

---

First of all, what you are probably looking for is the Singleton Pattern (which I don't really like that much anymore, but for your purpose one could argue that it can be considered appropriate):

You have to have the following in your class definition:

class Database : public QObject
{
    Q_OBJECT

public:
    static Database* instance();
private:
    static Database* m_instance;
    Database();
    ~Database() {}; // it can be necessary to have this public in some cases, if 
                    // you ever get a linker error related to deletion, this is 
                    // probably the reason.

public: 

    // FUNCTIONS
    ...
};

And the following in your .cpp file:

// init singleton pointer to NULL
Database* Database::m_instance = NULL;

Database* Database::instance()
{
    if( !m_instance )
    {
        m_instance = new Database();
    }
    return m_instance;
}

You can then access that singleton using e.g.

if( Database::instance()->createStructure() )
{
    w.show();
}

What does this do? At the start of the program, the line

Database* Database::m_instance = NULL;

initialises your m_instance variable to NULL. The first time you call Database::instance() it realizes that m_instance is still NULL and creates a new object and makes m_instance point to that object. From that point on, the pointer to that object will always be returned, but no more Database object will be created.

---

In your createStructure() function you commit() your database even when there is an error. The usual procedure is to commit() upon success and rollback() upon failure. Before fixing that, be sure to read the next point though:

---

The third thing I would recommend is getting used to being suspicious whenever you see multiple occurrences of the same lines a lot. That usually cries for a sub function.

I'm talking about

Database::close();
return false;

Take a look at how I rewrote your createStructure() method by introducing another method and leaving out else{ } where it was not necessary:

bool Database::createStructure()
{
    QStringList commands;

    commands.append("CREATE TABLE...;");
    commands.append("CREATE TABLE...;");
    commands.append("CREATE TABLE...;");

    if (!Database::open()) return false;

    // at this point you can be sure the database is open

    if (!Database::transaction())
    { 
        Database::close();
        return false;
    }

    // at this point you can be sure the database is open and a transaction was started

    if (!Database::executeCommands(commands))
    {
        // an error occurred - we need to rollback what we did so far
        Database::rollback();
        Database::close();
        return false;
    }

    // everything was executed properly, but the transaction is still active
    // => commit the changes we've made
    bool committed = Database::commit();

    // no matter if the commit was successful or not, close the database,
    // then return the result we've stored
    Database::close();
    return committed;
}

bool Database::executeCommands(const QStringList& commands)
{
    // This method simply executes the queries and is relieved from
    // transaction-related code.

    foreach(QString command, commands)
    {
        QSqlQuery query;
        bool prepared = query.prepare(command);

        if(!prepared) return false;

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

This could be further refactored, it is just an example of making your code easier to follow and thus usually less error-prone.