Simplify working with databases in Qt using QSqlRelationalTableModel

Good day, Habrovchane! In this article I want to talk about my experience of simplifying interaction with SQL databases when developing a desktop application using the QSqlRelationalTableModel class of the Qt cross-platform library.

Prologue

I got acquainted with Qt while still a 1st year student, just starting to program in C ++, at the same time I became seriously interested in the library and, since then, I've been following its updates. A few months ago, at work, I was given a ToR, in which I needed to develop an application that interacts with the SQLite database. The structure of the base is fixed and is known to me from the TK.

The application should be able to conveniently for the operator to present the data stored in the database, allow adding new records, delete and modify existing ones.
')
Next, I will briefly describe the development process with casting pieces of code and try to reasonably explain why in this case the choice was made in favor of QSqlRelationalTableModel .

Start of development

Initially, it was decided to establish interaction with the database using simple database queries, i.e. SELECT , INSERT , DELETE , which allow you to implement all the necessary functions of the application.

To do this, we need the QSqlDatabase and QSQlQuery classes :

QSqlDatabase db; //    db = QSqlDatabase::addDatabase("QSQLITE"); //    db.setHostName("localhost"); //  ,    db.setDatabaseName(path); //QString path -    //   if(db.open()){ qDebug() << "db opened OK..."; }else{ qDebug() << " db opening failed..."; } }else{ qDebug() << "file doesnot exist"; exit(0); // ,     , //   } 

After that, all operations on the base are performed as follows:

 // ,    <b>QLineEdit</b>' QString query = "INSERT INTO Table (column) VALUES ('" + ui->Input->text() + "')"; QSqlQuery sqlQuery(db); //c   qDebug() << "QUERY: " << query; // //   if(sqlQuery.exec(query)){ qDebug() << "query failed..."; } else{ qDebug() << "query failed..."; } 

Select statement 's are executed similarly, except that the data still needs to be received and put somewhere:

 QString query = "SELECT id, ka FROM Table"; QSqlQuery sqlQ(db); if(!sqlQ.exec(query)) { qDebug() << "query failed..."; return; } //     // ,        while (sqlQ.next()){ //           ui->ComboBox->addItem(sqlQ.value(1).toString(),sqlQ.value(0).toInt()); } 

Delete-statement 's are executed exactly as insert because nothing is returned.

All right, what's the problem?

And the truth is, because you can implement everything through these expressions and queries, why do we need models?

When we have one unrelated table, everything seems very simple and does not require the introduction of additional tools. Now imagine that we have such tables, for example, 5, each with 5 columns, not including id. And each has a connection with the previous one with the help of a foreign key via id , i.e. when deleting, it is necessary to cascadely delete all "child" records. This leads to a huge number of requests, the application slows down, moreover, it is necessary to update the table and its presentation in the interface each time, which leads to writing additional functions for updating, the appearance of bugs or the risk of their occurrence, and generally a decrease in readability code.

It is for this reason that the development process had to abandon the concept of using bare SQL queries.

A further choice was made in favor of QSqlRelationalTableModel in conjunction with QTableView . There is an even simpler version of the model implementation - QSqlTableModel , the first is inherited from it, has all the same methods, but adds the ability to create a QSqlRelation connection, which is very convenient if the user needs to show not the id of the record, but the name of the parent record connected.

Let's look at the implementation with the models

I will give pod fragments showing the implementation of model / view.

In the header file:

 QSqlRelationalTableModel *model; 

In the constructor:

 //  ,    //  QTableView  ,    //QModelIndex     ,      connect(ui->tableView, SIGNAL(clicked(const QModelIndex &)), this, SLOT(onTableClicked(const QModelIndex &))); model = new QSqlRelationalTableModel(parent, db); // , QSqlDatabase    model->setTable("Table"); //    

In the line below is one of the most convenient features and advantages of a model over sql queries - it edits, adds, deletes, depending on the context, the data in the sql table when changing from to QTableView. Convenience is that you no longer need to control the correctness of cascading data deletion and updating them within one QTableView.

 model->setEditStrategy(QSqlRelationalTableModel::OnFieldChange); 

Next comes another handy feature provided by this class: a connection is established between two columns of different tables:

 //ParentTable - ,      //id - ,         //name - ,        model->setRelation(1,QSqlRelation("ParentTable", "id", "name")); 

Then everything is more standard: select () will execute the SELECT expression, and setHeaderData () will set the text in the QTableView headers:

 model->select(); model->setHeaderData(0, Qt::Horizontal, tr("id")); model->setHeaderData(1, Qt::Horizontal, tr("id_sub")); model->setHeaderData(2, Qt::Horizontal, tr("count")); model->setHeaderData(3, Qt::Horizontal, tr("number")); model->setHeaderData(4, Qt::Horizontal, tr("data_word")); model->setHeaderData(5, Qt::Horizontal, tr("time")); model->setHeaderData(6, Qt::Horizontal, tr("name")); model->setHeaderData(7, Qt::Horizontal, tr("description")); ui->tableView->setModel(model); //     QTableView 

Now the model and tableView work together and perform their functions. By reference to github , all the sources will be left; in them I implemented adding an entry to the model, deleting it, as well as filters.

Conclusion

In this article, I wanted to encourage all those who are already working with the database in Qt to abandon bare sql queries for projects of at least medium difficulty and go to work with models to simplify their lives, make the code more readable and universal, and just do something good and new.

That's all! I hope that my experience with these classes will help readers to successfully solve a similar problem!