Commit changes to SQLite database, that can be seen between QTabs

Question

I have a simple application, where I can log in / out users. When user logs in, application shows appropriate tab on main window (employee/admin/customer). I have a QMainWindow with QTabWidget on it. In my QMainWindow I create a database (I implemented a special class for this):

class DataBase
{
public:

    DataBase();

    void initDatabase();
    void closeDatabase();

private:

    QSqlDatabase db;
};

DataBase::DataBase()
{
}

void DataBase::initDatabase()
{
    QString filename = "database.sql";
    QFile file(filename);

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setHostName("localhost");
    db.setDatabaseName(filename);

    // create users table
    if(this->db.open())
    {
        QSqlQuery usersTableQuery;
        QString usersTableQueryStr = "CREATE TABLE IF NOT EXISTS USERS (ID INTEGER PRIMARY KEY NOT NULL, "
                                 "LOGIN TEXT,"
                                 "PASSWORD TEXT,"
                                 "FIRSTNAME TEXT,"
                                 "LASTNAME TEXT,"
                                 "EMAIL TEXT,"
                                 "ACCOUNT_TYPE INTEGER" 
                                 ");";

        if(usersTableQuery.exec(usersTableQueryStr))
        {
            qDebug() << "Create USERS table OK";
        }
        else
        {
            qDebug() << usersTableQuery.lastError().text();
        }
    }
    else
    {
        qDebug() << "DB is not opened!\n";
    }

    // create service table
    if(this->db.open())
    {
        QSqlQuery serviceTableQuery;
        QString serviceTableQueryStr = "CREATE TABLE IF NOT EXISTS SERVICE (ID INTEGER PRIMARY KEY NOT NULL, "
                                 "NAME TEXT,"
                                 "PRICE REAL"
                                 ");";
        if(serviceTableQuery.exec(serviceTableQueryStr))
        {
            qDebug() << "Create SERVICE table OK";
        }
        else
        {
            qDebug() << serviceTableQuery.lastError().text();
        }
    }
    else
    {
        qDebug() << "DB is not opened!\n";
    }
}

void DataBase::closeDatabase()
{
    db.close();
}

My tabs for employee, admin, client look like this one:

class AdminTab : public QWidget
{
    Q_OBJECT
public:
    explicit AdminTab(DataBase *db, QWidget *parent = 0);
//...

Everyone (employee,client,admin) can make changes in database (for instance, admin can insert services, users can check available services, etc). However, when admin adds a service (I make an insert operation on an open database), and logs out, when the client logs in, it can't see the changes made by the admin. When I start application again, and client logs in, it can see new added service.

Adding service looks like this:

bool DataBase::insertService(QString name, double price)
{
    if(!db.isOpen())
    {
        qDebug() << query.lastError();
        return false;
    }
    else
    {
        QSqlQuery query;
        query.prepare("INSERT INTO SERVICE (NAME, PRICE) "
                      "VALUES (:NAME, :PRICE)");
        query.bindValue(":NAME", name);
        query.bindValue(":PRICE", price);

        if(query.exec())
        {
            return true;
        }
        else
        {
            qDebug() << query.lastError();
        }
    }
    return false;
}

I guess it's the problem that the database is all the time opened, but how can I make the changes to be available just after I insert/remove something in database? I open the database when I create QMainWindow and close it in its destructor.

I thought about opening/closing the database every time I need to use it, but I can't say if it's a good solution.

Even adding :

if(query.exec())
                {
                    query.clear();
                    db.commit();
                    return true;
                }

Does not help.

Client has: QVector<Service*> availableServices; and QComboBox *servicesComboBox;, checking for all the available services, when client logs in :

void ClientTab::updateAllServices()
{
    availableServices.clear();
    availableServices = db->selectAllServices();

    servicesComboBox->clear();
    for(int i=0; i<availableServices.size(); i++)
        servicesComboBox->addItem(availableServices[i]->getServiceName(), QVariant::fromValue(availableServices[i]));

    servicesComboBox->setCurrentIndex(-1);
}

Service class:

#ifndef SERVICE_H
#define SERVICE_H

#include <QString>
#include <QMetaType>
#include <QVariant>

class Service : public QObject
{

   Q_OBJECT

public:
    Service(int id, QString name, double price);
    Service(){ id = -1; name = ""; price = 0;}

    QString getServiceName() const;
    void setServiceName(const QString &value);

    double getServicePrice() const;
    void setServicePrice(double value);

    int getId() const;
    void setId(int value);

private:

    QString name;
    double price;
    int id;
};

Q_DECLARE_METATYPE(Service*)

#endif // SERVICE_H

And finally, selecting all services from the database (I use this method to populate combobox on ClientTab):

QVector<Service*> DataBase::selectAllServices()
{
    QVector<Service*> services;

    if(!db.isOpen())
    {
        return services;
    }
    else
    {
        QSqlQuery query;
        if(query.exec("SELECT * FROM SERVICE;"))
        {
            while( query.next() )
            {
                int id = query.value(0).toInt();
                QString name = query.value(1).toString();
                double price = query.value(2).toDouble();

                Service *s = new Service(id, name, price);
                services.push_back(s);
            }
        }
        else
        {
            qDebug() << "DataBase::selectAllServices " << query.lastError();
        }
    }

    return services;
}

Have you tried calling `db.transaction()` before insert and `db.commit()` after? — Giedrius D, Feb 22 '16 at 23:04
@GiedriusD: I haven't actually, just wait a sec, I will do it in a moment. **EDIT**: no, theres no effect too — yak, Feb 22 '16 at 23:13
Can you show the code that checks for new service after client logs in? — Giedrius D, Feb 22 '16 at 23:23

score 1 · Answer 1 · answered Feb 23 '16 at 01:32

Could you doublecheck that

void ClientTab::updateAllServices()

is called every time as the client logs in (not only at application start)?

SQLite database has autocommit on by default, therefore you don't need to commit anything or use any transaction for this simple thing.

Can you see the new service added in sqlite command line with a select * from service? If so, then adding a service works well, and you need to check when do you call updateAllServices().

score 1 · Answer 2 · answered Feb 29 '16 at 15:03

If I understand your problem correctly, it's about how to synchronize multiple views of a database. This is indeed a difficult task.

If only one of the views is visible at any given time (like it seems in your case to be with the different tabs), just reload the data from the database and repopulate the tabs. How would you do this? Add a signal contentChanged() to your main window and let all views reload the data when they see it. (Or clear their data and reload when the user switches to the specific tab.)

In case this is too slow or you can see the same content in multiple views at the same time: You should use the models provided by Qt, e.g. QListView + QAbstractListModel instead of QListWidget. If you use those, synchronization is for free, but you should not access your SQL database directly anymore and only change it through the model.

Commit changes to SQLite database, that can be seen between QTabs

2 Answers2