sqliteqtqt6qsqlrelationaltablemodel

How to restore back a null value on a column that has a relation?


In Qt 6.8.2 I subclassed QTableView in order to catch the pressing of Key_Delete:

#include <QTableView>
#include <QKeyEvent>

class TableView : public QTableView
{
    Q_OBJECT

public:
    explicit TableView(QWidget *parent = nullptr) : QTableView(parent) { }

protected:
    void keyPressEvent(QKeyEvent *event) override
    {
        if (event->key() == Qt::Key_Delete) emit keyCancelPressed(currentIndex());
        else QTableView::keyPressEvent(event);
    }

signals:
    void keyCancelPressed(const QModelIndex &current);
};

A QSqlRelationalTableModel is assigned to the TableView and the relations are set:

QSqlDatabase db = QSqlDatabase::database(DATABASE_NAME);
QSqlRelationalTableModel *model = new QSqlRelationalTableModel(this, db);
model->setTable(TABLE_NAME);
model->setEditStrategy(QSqlRelationalTableModel::OnRowChange);
model->setRelation(MODEL_COL_ID_HUE_MODE), QSqlRelation("hueModes", "id", "name"));    
model->select();
ui->myTableView->setModel(_model);
ui->myTableView->setItemDelegate(new QSqlRelationalDelegate(ui->myTableView));

then a lambda is created to set back to null a field if the delete key is pressed:

connect(ui->myTableView, &TableView::keyCancelPressed, this, [=](const QModelIndex &current)
{
    model->setData(current, QVariant());
});

The actual behavior is the following:

Even if it should not matter since we're still at model level, my SQL columns don't have the NOT NULL clause:

CREATE TABLE timelines \
    (idProgram INT NOT NULL, \
     time INT NOT NULL, \
     h REAL, \
     s REAL, \
     l REAL, \
     idHueMode INT, \
     PRIMARY KEY (idProgram, time), \
     FOREIGN KEY (idProgram) REFERENCES programs(id) ON DELETE CASCADE ON UPDATE CASCADE, \
     FOREIGN KEY (idHueMode) REFERENCES hueModes(id) ON DELETE SET DEFAULT ON UPDATE CASCADE);";

By the way, the code works even on the time column since we're playing with the model as said. Of course, when I try to submit I will receive an error in that case.

Trying to debug I changed the code to:

qDebug() << model->setData(current, QVariant());
qDebug() << model->lastError();

and the output is:

false
QSqlError("", "", "")

not very useful, indeed. How to restore back a null value on a column that has a relation?


Solution

  • The source code provides the explanation:

    /*!
        Sets the data for the \a role in the item with the specified \a
        index to the \a value given. Depending on the edit strategy, the
        value might be applied to the database at once, or it may be
        cached in the model.
    
        Returns \c true if the value could be set, or false on error (for
        example, if \a index is out of bounds).
    
        For relational columns, \a value must be the index, not the
        display value. The index must also exist in the referenced
        table, otherwise the function returns \c false.
    
        \sa editStrategy(), data(), submit(), revertRow()
    */
    bool QSqlRelationalTableModel::setData(const QModelIndex &index, const QVariant &value,
                                           int role)
    {
        Q_D(QSqlRelationalTableModel);
        if ( role == Qt::EditRole && index.column() > 0 && index.column() < d->relations.size()
                && d->relations.at(index.column())->isValid()) {
            auto relation = d->relations.at(index.column());
            if (!relation->isDictionaryInitialized())
                relation->populateDictionary();
            if (!relation->dictionary.contains(value.toString()))
                return false;
        }
        return QSqlTableModel::setData(index, value, role);
    }
    

    The behavior is actually by design:

    For relational columns, [...] The index must also exist in the referenced table, otherwise the function returns false.

    But you can change the behavior overriding the default setData() function calling the base function in QSqlTableModel as per @musicamante's comment:

    bool setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole) override
    {
        if (value.isNull()) return QSqlTableModel::setData(index, value, role);
        else return QSqlRelationalTableModel::setData(index, value, role);
    }