[SOLVED] QSqlTableModel behaviour since 4.8

[le-roy_a]

Hi everybody,

i have found a big difference of behaviour with the qsqltablemodel on 4.7.1 and 4.8.2, i will try to describe it.

it occurs when the strategy is onfieldChange and when insert a new row like that :

@
QsqlTableModel *_model;
....
_model->setTable("matable");
QSqlRecord _rec = _model->record();
_rec.setValue("REFPARAM",__idparam);
_rec.setValue("REFNODE",__idnode);
_rec.setValue("FOREIGNID", -1);
_rec.setValue("PRMVAL_VALUE", _value);
_model->insertRecord(-1,_rec);
@

the fields description on table "matable"
@
IDMATABLE
REFPARAM
REFNODE
FOREIGNID
PRMVAL_VALUE
@

i don' t set the first field (IDMATABLE) because is the primary key and it's fill by trigger into the database.

with the 4.7.1 and before it works fine but with 4.8.2 it doesn' t work (violation of primary key).
After some investigations i have found the problem => the insert statment auto-generated is different on each version :
4.7.1 :
INSERT (REFPARAM,REFNODE,FOREIGNID,PRMVAL_VALUE) VALUES (?,?,?,?)
4.8.2
INSERT (IDMATABLE,REFPARAM,REFNODE,FOREIGNID,PRMVAL_VALUE) VALUES (?,?,?,?,?)

in fact the previous version of 4.8 checked if the value of a field was a valid QVariant :
@
file source : src/sql/kernels/qsqldriver.cpp
functions :
QString QSqlDriver::sqlStatement(StatementType type, const QString &tableName,
const QSqlRecord &rec, bool preparedStatement) const
CODE :
if (!rec.isGenerated(i) || !rec.value(i).isValid())
continue;
@

but in 4.8.2 the in the same file the code in the same function is :

@
if (!rec.isGenerated(i))
continue;
@

so all field are put into the insert statement and it's problematic for autogenrated primary key and computed field.

i have try to do

@
QsqlTableModel *_model;
....
_model->setTable("matable");
QSqlRecord _rec = _model->record();
rec.setGenerated(0, false); // try to force the behaviour
_rec.setValue("REFPARAM",__idparam);
_rec.setValue("REFNODE",__idnode);
_rec.setValue("FOREIGNID", -1);
_rec.setValue("PRMVAL_VALUE", _value);
_model->insertRecord(-1,_rec); => set to generated into it
@

but it doesn' t work because it is set to true during the insertRecord (personaly i think it ' s not normal).

so the only solution that i have found is to remove in each record that i want to insert the primary key column.

@
QsqlTableModel *_model;
....
_model->setTable("matable");
QSqlRecord _rec = _model->record();
_rec.remove(0); // remove the primary key field
_rec.setValue("REFPARAM",__idparam);
_rec.setValue("REFNODE",__idnode);
_rec.setValue("FOREIGNID", -1);
_rec.setValue("PRMVAL_VALUE", _value);
_model->insertRecord(-1,_rec);
@

i don't want to remove the column directly into the model because i need the primary key in the view.

I think we need a solution for removing fields for autogenerated statement like insert and update, maybe it exists but i find only the way by removing the field on record but we must do that on each record. I think it' s more natural to define it directly into the model.
I don't have test with computed field but i think it's the same problem (this kind of field are read only).

[lgeyer]

This change was on purpose, see "QTBUG-13211":https://bugreports.qt-project.org/browse/QTBUG-13211 and "0f15ab4e":http://qt.gitorious.org/qt/qt/commit/0f15ab4e750690bdb2b649332d5c3276bf24c440/diffs.

Why don't you just set the generated flag for the field? It should serve exactly this purpose.

[le-roy_a]

thanks i don't see the QTBUG-13211,

Why don’t you just set the generated flag for the field? It should serve exactly this purpose.

i have try it but don't work because the flag is reset during the insert record.
@
QsqlTableModel *_model;
....
_model->setTable("matable");
QSqlRecord _rec = _model->record();
rec.setGenerated(0, false); // try to force the behaviour
_rec.setValue("REFPARAM",__idparam);
_rec.setValue("REFNODE",__idnode);
_rec.setValue("FOREIGNID", -1);
_rec.setValue("PRMVAL_VALUE", _value);
qWarning() << _rec.isGenerated(0); // return false;
_model->insertRecord(-1,_rec); => set to generated into it
qWarning() << _rec.isGenerated(0); // return true;
@

it's reset in qsqltablemodel.cpp :
@
bool QSqlTableModel::setData(const QModelIndex &index, const QVariant &value, int role)
{
Q_D(QSqlTableModel);
if (role != Qt::EditRole)
return QSqlQueryModel::setData(index, value, role);

if (!index.isValid() || index.column() >= d->rec.count() || index.row() >= rowCount())
    return false;

bool isOk = true;
switch (d->strategy) {
case OnFieldChange: {
    if (index.row() == d->insertIndex) {
        QSqlTableModelPrivate::setGeneratedValue(d->editBuffer, index.column(), value);
        return true;
    }

@

this behaviour occurs only with insertRecord method and onfieldchange strategy

[lgeyer]

I don't think setData() is the problem. If you take a look at "QSqlTableModelPrivate::setRecord()":http://qt.gitorious.org/qt/qt/blobs/4.8/src/sql/models/qsqltablemodel.cpp#line72, setData() isn't even called for fields having isGenerated() not set.

The generated flag is only modified in insertRecord() resp. setRecord() for OnManualSubmit.

Are you quite sure that you have OnFieldChange set?
Have you stepped through to find out where the flag is actually modified?

[quote author="le-roy_a" date="1343287675"]i don't see the QTBUG-13211[/quote]<code>git blame</code> is your friend ;-)

[le-roy_a]

Are you quite sure that you have OnFieldChange set?

sure sure :)

Have you stepped through to find out where the flag is actually modified?
yes, i trace it :

1. bool QSqlTableModelPrivate::setRecord(int row, const QSqlRecord &record)

2. @ if (oldValue.isNull() || oldValue != value)
   isOk &= q->setData(cIndex, value, Qt::EditRole); // next step@

3. bool QSqlTableModel::setData(const QModelIndex &index, const QVariant &value, int role)

4. @case OnFieldChange: {
   if (index.row() == d->insertIndex) {
   QSqlTableModelPrivate::setGeneratedValue(d->editBuffer, index.column(), value); // next step
   return true;
   }@

5. void QSqlTableModelPrivate::setGeneratedValue(QSqlRecord &rec, int c, QVariant v)

6. @rec.setValue(c, v);
   rec.setGenerated(c, true); // here is reset@

[le-roy_a]

i found why you don't see the problem is not the QSqlTableModelPrivate::setRecord() that is call but the "QSqlTableModel::setRecord":http://qt.gitorious.org/qt/qt/blobs/4.8/src/sql/models/qsqltablemodel.cpp#line72 after the call to insertRecord

[lgeyer]

Alright.

The problem is that the recent changes didn't make it in 4.8.2 and the fixed code which can be seen on Gitorious will not be in until 4.8.3 (see also "QTBUG-23592":https://bugreports.qt-project.org/browse/QTBUG-23592).

Interestingly enough the generated flag still shouldn't change. setGeneratedValue() modifies d->editBuffer, not the record you pass to insertRecord(). I've tried a "small example":http://pastebin.com/PtcSJmVP using 4.8.2 and in fact the generated flag does, as expected, not change (obviously at least for me).

[le-roy_a]

bq. Interestingly enough the generated flag still shouldn’t change. setGeneratedValue() modifies d-bq.editBuffer, not the record you pass to insertRecord(). I’ve tried a small example [pastebin.com] using bq.4.8.2 and in fact the generated flag does, as expected, not change (obviously at least for me).

yes i agree with you my example is wrong. effectively it only affect the d->editBuffer. sorry.

bq. The problem is that the recent changes didn’t make it in 4.8.2 and the fixed code which can be seen on Gitorious will not be in until 4.8.3 (see also QTBUG-23592 [bugreports.qt-project.org]).

ok

But what do you think about add a setGenerated method directly in the qsqltablemodel like that :
@
QsqlTableModel *_model;
_model->setTable("matable");
_model->setGenerated(0, false); // new method
...
QSqlRecord _rec = _model->record();
_rec.setValue("REFPARAM",__idparam);
_rec.setValue("REFNODE",__idnode);
_rec.setValue("FOREIGNID", -1);
_rec.setValue("PRMVAL_VALUE", _value);
_model->insertRecord(-1,_rec);
@

like that the definittion of generated columns are defined only once

[lgeyer]

Undecided. It might be a good addition.

Feel free to file a "feature request":http://qt-project.org/wiki/ReportingBugsInQt, bring this topic to the "mailing list":http://lists.qt-project.org/mailman/listinfo/development or (better) do the changes on your own and push it to "Gerrit":http://qt-project.org/wiki/Gerrit-Introduction.