Qt/C++ - Lesson 005. QSqlRelationalTableModel - The work with relational tables

QSqlRelationalTableModel example, qt, sql, sqlite, QSqlRelationalTableModel, qt таблицы

In Qt to represent table fields, which involve foreign keys to other tables database, QSqlRelationalTableModel can be used, which is a more advanced version QSqlTableModel class, which was considered in the previous article .

This class allows you to set relationships between tables and substitute values in the representation of the table formed by the values of the connected tables.

This article discusses the option of two tables. One table contains information about the devices (host name and IP address), and the second table ID of the device, which in the second table to be substituted host name and IP address of the device, respectively.

Project Structure for QSqlRelationalTableModel

Структура проекта The project for this tutorial is a modified version of the preceding Article and remains unchanged.

mainwindow.ui

When creating molds are added to a table that will be specified device. The names of the following tables:

• tableView
• tableViewDevice

DataBase.pro

This file remains the same as in the previous article.

main.cpp

The file used in the project, being created by default.

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

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    return a.exec();
}

mainwindow.h

Instead QSqlTableModel library we connect QSqlRelationalTableModel library. Also is added an additional method, which is responsible for initializing the devices table view model.

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlRelationalTableModel>
#include <QSqlRelationalDelegate>
#include <QSqlRelation>

/* Connect the header file for work with the database */
#include "database.h"

namespace Ui {
class MainWindow;
}

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    explicit MainWindow(QWidget *parent = 0);
    ~MainWindow();

private:
    Ui::MainWindow  *ui;
    /* The project uses objects to interact with the database and model representation database table
     * */
    DataBase                    *db;
    QSqlRelationalTableModel    *modelMain;
    QSqlRelationalTableModel    *modelDevice;

private:
    /* Also present are two methods that form the model and appearance TableView
     * */
    void setupMainModel(const QString &tableName, const QStringList &headers);
    void setupDeviceModel(const QString &tableName, const QStringList &headers);
    void createUI();
};

#endif // MAINWINDOW_H

mainwindow.cpp

Basically, the original file initialize the data model representation and appearance of the main window. In the method for initializing the main table will be installed communication for which data from the devices of the table will be selected.

When you run the program in a database introduced three devices, then 9 records with random assignment device ID from 1 to 3. The first time you start the program creates the database and devices are introduced into an empty table with the device ID from one to three. The second run will be the ID other than 1, 2 or 3 but as this case study, we restrict ourselves to the first one you run the application.

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    /* The first step is to create an object for the database and initialize the database connection
     * */
    db = new DataBase();
    db->connectToDataBase();

    /* After that produce content database tables 
     * this content will displayed in the tableView and tableViewDevice
     * */
    for(int i = 1; i < 4; i++){
        QVariantList data;
        data.append("Device " + QString::number(i));
        data.append("192.168.0." + QString::number(i));
        db->inserIntoDeviceTable(data);
    }

    for(int i = 0; i < 10; i++){
        QVariantList data;
        QString random = QString::number(qrand() % ((4 + 1) - 1) + 1);
        data.append(QDate::currentDate());
        data.append(QTime::currentTime());
        data.append(random);
        data.append(random);
        db->inserIntoMainTable(data);
    }

    /* Initialize the model to represent the data indicating the names of the columns
     * */
    this->setupMainModel(TABLE,
                     QStringList() << trUtf8("id")
                                   << trUtf8("Дата")
                                   << trUtf8("Время")
                                   << trUtf8("Имя хоста")
                                   << trUtf8("IP адрес")
               );

    this->setupDeviceModel(DEVICE,
                     QStringList() << trUtf8("id")
                                   << trUtf8("Имя хоста")
                                   << trUtf8("IP адрес")
               );
    /* Initialize the appearance of a table with data
     * */
    this->createUI();
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::setupMainModel(const QString &tableName, const QStringList &headers)
{
    /* Initializes the data model representation with the installation name 
     * in the database table, on which will be accessed in the table
     * */
    modelMain = new QSqlRelationalTableModel(this);
    modelMain->setTable(tableName);
    /* Set the connection device table, which will be made data substitution. 
     * The method setRelation specified column number in which substitution is made, 
     * as well as through QSqlRelation class name of the table, 
     * the option for which the sample line and column 
     * from which the data will be taken will be made
     * */
    modelMain->setRelation(3, QSqlRelation(DEVICE, "id", DEVICE_HOSTNAME));
    modelMain->setRelation(4, QSqlRelation(DEVICE, "id", DEVICE_IP));

    /* Set the columns names in a table with sorted data
     * */
    for(int i = 0, j = 0; i < modelMain->columnCount(); i++, j++){
        modelMain->setHeaderData(i,Qt::Horizontal,headers[j]);
    }
    // Set Sort Ascending column zero data
    modelMain->setSort(0,Qt::AscendingOrder);
    modelMain->select(); // Делаем выборку данных из таблицы
}

void MainWindow::setupDeviceModel(const QString &tableName, const QStringList &headers)
{
    /* Initializes the data model representation 
     * with the installation name in the database table, 
     * on which will be accessed in the table
     * */
    modelDevice = new QSqlRelationalTableModel(this);
    modelDevice->setTable(tableName);

    for(int i = 0, j = 0; i < modelDevice->columnCount(); i++, j++){
        modelDevice->setHeaderData(i,Qt::Horizontal,headers[j]);
    }

    modelDevice->setSort(0,Qt::AscendingOrder);
    modelDevice->select(); 
}

void MainWindow::createUI()
{
    ui->tableView->setModel(modelMain);     // We set the model on the TableView
    ui->tableView->setColumnHidden(0, true);    // Hide the column id Records
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->resizeColumnsToContents();
    ui->tableView->setItemDelegate(new QSqlRelationalDelegate(ui->tableView));
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->horizontalHeader()->setStretchLastSection(true);

    modelMain->select(); // Fetches the data from the table

    ui->tableViewDevice->setModel(modelDevice);     
    ui->tableViewDevice->setColumnHidden(0, true);    
    ui->tableViewDevice->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->tableViewDevice->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableViewDevice->resizeColumnsToContents();
    ui->tableViewDevice->setItemDelegate(new QSqlRelationalDelegate(ui->tableViewDevice));
    ui->tableViewDevice->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableViewDevice->horizontalHeader()->setStretchLastSection(true);

    modelDevice->select();
}

database.h

Compared with the example about QSqlTableModel in this example adds two new methods related to the devices table, namely the creation table with createDeviceTable() and insert records into the table of devices with insertIntoDeviceTable() . As well as adding new devices to the directive define tables and processed guidelines for the main table.

#ifndef DATABASE_H
#define DATABASE_H

#include <QObject>
#include <QSql>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDatabase>
#include <QFile>
#include <QDate>
#include <QDebug>

#define DATABASE_HOSTNAME   "ExampleDataBase"
#define DATABASE_NAME       "DataBase.db"

#define TABLE                   "MainTable"
#define TABLE_DATE              "Date"
#define TABLE_TIME              "Time"
#define TABLE_IP                "IP"
#define TABLE_HOSTNAME          "Hostname"

#define DEVICE                  "DeviceTable"
#define DEVICE_IP               "IP"
#define DEVICE_HOSTNAME         "Hostname"

class DataBase : public QObject
{
    Q_OBJECT
public:
    explicit DataBase(QObject *parent = 0);
    ~DataBase();
    /* Methods to work directly with the class. 
     * Connect to the database and insert records into the table
     * */
    void connectToDataBase();
    bool inserIntoMainTable(const QVariantList &data);
    bool inserIntoDeviceTable(const QVariantList &data);

private:
    QSqlDatabase    db;

private:
    bool openDataBase();
    bool restoreDataBase();
    void closeDataBase();
    bool createMainTable();
    bool createDeviceTable();
};

#endif // DATABASE_H

database.cpp

Only part of the methods as well as adding two new methods compared with the previous article have undergone changes in this file.

#include "database.h"

DataBase::DataBase(QObject *parent) : QObject(parent)
{

}

DataBase::~DataBase()
{

}

void DataBase::connectToDataBase()
{
    /* см. статью про QSqlTableModel */
}

bool DataBase::restoreDataBase()
{
    if(this->openDataBase()){
        if((!this->createMainTable()) || (!this->createDeviceTable())){
            return false;
        } else {
            return true;
        }
    } else {
        qDebug() << "Не удалось восстановить базу данных";
        return false;
    }
    return false;
}

bool DataBase::openDataBase()
{
    /* cm. article QSqlTableModel */
}

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

bool DataBase::createMainTable()
{
    QSqlQuery query;
    if(!query.exec( "CREATE TABLE " TABLE " ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                            TABLE_DATE      " DATE            NOT NULL,"
                            TABLE_TIME      " TIME            NOT NULL,"
                            TABLE_HOSTNAME  " INTEGER         NOT NULL,"
                            TABLE_IP        " INTEGER         NOT NULL"
                        " )"
                    )){
        qDebug() << "DataBase: error of create " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

bool DataBase::createDeviceTable()
{
    QSqlQuery query;
    if(!query.exec( "CREATE TABLE " DEVICE " ("
                            "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                            DEVICE_HOSTNAME  " VARCHAR(255)    NOT NULL,"
                            DEVICE_IP        " VARCHAR(16)     NOT NULL"
                        " )"
                    )){
        qDebug() << "DataBase: error of create " << DEVICE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

bool DataBase::inserIntoMainTable(const QVariantList &data)
{

    QSqlQuery query;
    query.prepare("INSERT INTO " TABLE " ( " TABLE_DATE ", "
                                             TABLE_TIME ", "
                                             TABLE_HOSTNAME ", "
                                             TABLE_IP " ) "
                  "VALUES (:Date, :Time, :Hostname, :IP )");
    query.bindValue(":Date",        data[0].toDate());
    query.bindValue(":Time",        data[1].toTime());
    query.bindValue(":Hostname",    data[2].toInt());
    query.bindValue(":IP",          data[3].toInt());

    if(!query.exec()){
        qDebug() << "error insert into " << TABLE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

bool DataBase::inserIntoDeviceTable(const QVariantList &data)
{

    QSqlQuery query;
    query.prepare("INSERT INTO " DEVICE " ( " DEVICE_HOSTNAME ", "
                                              DEVICE_IP " ) "
                  "VALUES (:Hostname, :IP )");
    query.bindValue(":Hostname",    data[0].toString());
    query.bindValue(":IP",          data[1].toString());
    // После чего выполняется запросом методом exec()
    if(!query.exec()){
        qDebug() << "error insert into " << DEVICE;
        qDebug() << query.lastError().text();
        return false;
    } else {
        return true;
    }
    return false;
}

Result

As a result of the work done and application will contains two tables. The basic table from which that will contain the link to the recording device table and a second table that will contain entries for devices, respectively.

QSqlRelationTableModel Application