QSqlTableModel Class

The QSqlTableModel class provides an editable data model for a single database table. More…

List of all members, including inherited members

Public Types

Public Functions

Reimplemented Public Functions

Public Slots

Signals

Protected Functions

Reimplemented Protected Functions

Detailed Description

A QSqlTableModel reads the rows of one table and writes back the items that are changed, the rows that are inserted and the rows that are removed, without any SQL in the application:

QSqlTableModel model;
model.setTable("employee");
model.setFilter("salary > 50000");
model.setSort(model.fieldIndex("name"), Qt::AscendingOrder);
model.setEditStrategy(QSqlTableModel::OnManualSubmit);
model.select();

model.setData(model.index(0, model.fieldIndex("salary")), 62000);
if (!model.submitAll())
    qWarning() << model.lastError().text();

setTable() picks the table and reads its fields; select() reads the rows, the ones that setFilter() lets through, in the order of setSort(). The edit strategy says when changes go to the database. Until they are written they are kept in the model: isDirty() tells that there are some, and the vertical header shows * for a row that waits to be inserted and ! for one that waits to be deleted. revertAll() drops them.

A row is found again in the table by its primary key. For a table without one, all the fields of the row are compared, so two rows that are the same are changed together.

The filter is a piece of SQL that is put into the statement as it is. Do not build it from text that comes from outside the program.

With the strategies OnFieldChange and OnRowChange, a row that was written is read again by itself: the model is not reset, so a view keeps its selection and its place, and the row shows what the database made of it, such as a key that it assigned or a default value. The row stays where it is, even if the sort order or the filter would now put it elsewhere, until the next select(). submitAll() with OnManualSubmit, and deleting a row, read the whole table again.

See also QSqlQueryModel, QSqlDatabase and Using Nt SQL.

Differences from Qt

Member Type Documentation

enum QSqlTableModel::EditStrategy

When the changes that are made to the model are written to the database.

Member Function Documentation

[explicit] QSqlTableModel::QSqlTableModel(QObject *parent = nullptr, const QSqlDatabase &db = QSqlDatabase())

Constructs an empty model with the given parent, on the connection db, or on the default connection if db is not valid.

[override] QSqlTableModel::~QSqlTableModel()

Destroys the model. Changes that were not submitted are lost.

[signal] void QSqlTableModel::beforeDelete(int row)

This signal is emitted before the row row is deleted from the table of the database.

[signal] void QSqlTableModel::beforeInsert(QSqlRecord &record)

This signal is emitted before a new row is inserted into the table of the database, with the values that are about to be written in record. A slot can still change them.

[signal] void QSqlTableModel::beforeUpdate(int row, QSqlRecord &record)

This signal is emitted before the row row is updated in the table of the database, with the values that are about to be written in record: the fields that are marked as generated. A slot can still change them.

[override] void QSqlTableModel::clear()

Reimplements QSqlQueryModel::clear().

Empties the model: forgets the table, the filter, the sort order, the rows and all pending changes. The edit strategy is kept.

[override] bool QSqlTableModel::clearItemData(const QModelIndex &index)

Reimplements QSqlQueryModel::clearItemData().

Sets the value of the item at index to null and returns true on success, as setData() does with a null value.

[override] QVariant QSqlTableModel::data(const QModelIndex &idx, int role = Qt::DisplayRole) const

Reimplements QSqlQueryModel::data().

Returns the value of the item at idx for the display role or the edit role: the pending value if the item was changed, and what was read from the table otherwise. Returns an invalid QVariant for another role.

See also setData().

QSqlDatabase QSqlTableModel::database() const

Returns the connection of the model.

[virtual, protected] bool QSqlTableModel::deleteRowFromTable(int row)

Deletes the row row from the table of the database and returns true on success. The row is found by primaryValues(). Called by submitAll().

See also updateRowInTable(), insertRowInTable() and beforeDelete().

EditStrategy QSqlTableModel::editStrategy() const

Returns when changes are written to the database.

See also setEditStrategy().

int QSqlTableModel::fieldIndex(const QString &fieldName) const

Returns the column of the field called fieldName, or -1 if the model has no such field.

QString QSqlTableModel::filter() const

Returns the filter of the model.

See also setFilter().

[override] Qt::ItemFlags QSqlTableModel::flags(const QModelIndex &index) const

Reimplements QSqlQueryModel::flags().

Returns the flags of the item at index: those of the base class, and Qt::ItemIsEditable unless the row waits to be deleted or the field is read-only.

[override] QVariant QSqlTableModel::headerData(int section, Qt::Orientation orientation, int role = Qt::DisplayRole) const

Reimplements QSqlQueryModel::headerData().

Returns the header data for section, orientation and role. The vertical header shows * for a row that waits to be inserted and ! for a row that waits to be deleted; everything else comes from QSqlQueryModel.

[override, protected] QModelIndex QSqlTableModel::indexInQuery(const QModelIndex &item) const

Reimplements QSqlQueryModel::indexInQuery().

Returns the index in the query of the item item of the model: the rows that wait to be inserted are not in the query, so the rows after them have a smaller row number there. Returns an invalid index for an item in such a row.

bool QSqlTableModel::insertRecord(int row, const QSqlRecord &record)

Inserts a row before the row row, or at the end if row is negative, gives it the values of record, and returns true on success. With the strategies OnFieldChange and OnRowChange the row is inserted into the table at once.

See also insertRows() and setRecord().

[virtual, protected] bool QSqlTableModel::insertRowInTable(const QSqlRecord &values)

Inserts a row with the fields of values that are marked as generated into the table of the database, and returns true on success. A row without such fields is inserted with the default values of the table. Called by submitAll().

See also updateRowInTable(), deleteRowFromTable() and beforeInsert().

[override] bool QSqlTableModel::insertRows(int row, int count, const QModelIndex &parent = QModelIndex())

Reimplements QSqlQueryModel::insertRows().

Inserts count empty rows before the row row and returns true on success. parent has to be invalid, and with the strategies OnFieldChange and OnRowChange only one row can be inserted at a time, after pending changes of other rows were written. The rows are not in the table of the database yet: primeInsert() is emitted for each of them, setData() or setRecord() fill them in, and submit() or submitAll() insert them.

See also insertRecord(), removeRows() and primeInsert().

bool QSqlTableModel::isDirty() const

Returns true if the model has changes that were not written to the database; otherwise returns false.

See also submitAll() and revertAll().

bool QSqlTableModel::isDirty(const QModelIndex &index) const

This is an overloaded function.

Returns true if the item at index has a value that was not written to the database, or is in a row that waits to be inserted or deleted; otherwise returns false.

[virtual, protected] QString QSqlTableModel::orderByClause() const

Returns the ORDER BY clause for the column and the order of setSort(), or an empty string if no sort column is set.

See also setSort() and selectStatement().

QSqlIndex QSqlTableModel::primaryKey() const

Returns the primary key of the table, or an empty index if the table has none or no table is set.

See also setPrimaryKey().

[protected] QSqlRecord QSqlTableModel::primaryValues(int row) const

Returns the fields that find the row row in the table, with the values that the row has there, whatever was changed in the model: the fields of the primary key, or all the fields if the table has none. The record is empty for a row that is not in the table yet.

See also primaryKey(), updateRowInTable() and deleteRowFromTable().

[signal] void QSqlTableModel::primeInsert(int row, QSqlRecord &record)

This signal is emitted by insertRows() for each new row, with its position row and its empty record. A slot can give the fields of record their first values; a field that it sets is written when the row is inserted into the table.

QSqlRecord QSqlTableModel::record() const

Returns the fields of the table, without values: an empty record before setTable().

QSqlRecord QSqlTableModel::record(int row) const

This is an overloaded function.

Returns the fields of the model with the values of the row row, pending changes included. A field is marked as generated if its value was changed and not submitted yet. The values are null if there is no such row.

[override] bool QSqlTableModel::removeColumns(int column, int count, const QModelIndex &parent = QModelIndex())

Reimplements QSqlQueryModel::removeColumns().

Removes count columns starting with the column column from the model and returns true on success. parent has to be invalid. The table of the database keeps its columns; the model no longer reads or writes them. Pending changes are dropped.

[override] bool QSqlTableModel::removeRows(int row, int count, const QModelIndex &parent = QModelIndex())

Reimplements QSqlQueryModel::removeRows().

Removes count rows starting with the row row and returns true on success. parent has to be invalid. A row that waits to be inserted is dropped. Another row is deleted from the table: at once, unless the strategy is OnManualSubmit, with which the row stays in the model, marked with ! in the vertical header, until submitAll() deletes it or revertAll() keeps it.

See also insertRows(), deleteRowFromTable() and beforeDelete().

[slot, override] void QSqlTableModel::revert()

Reimplements QSqlQueryModel::revert().

Drops the pending changes if the strategy is OnRowChange or OnFieldChange. With OnManualSubmit it does nothing: use revertAll().

See also revertAll() and submit().

[slot] void QSqlTableModel::revertAll()

Drops all pending changes: rows that wait to be inserted are removed, and the other rows are shown as they are in the table.

See also submitAll() and revertRow().

[virtual] void QSqlTableModel::revertRow(int row)

Drops the pending changes of the row row: a row that waits to be inserted is removed from the model, and another row gets back the values and the state that it has in the table.

See also revertAll() and revert().

[override] int QSqlTableModel::rowCount(const QModelIndex &parent = QModelIndex()) const

Reimplements QSqlQueryModel::rowCount().

Returns the number of rows of the model: the rows that were read from the table, and the rows that wait to be inserted. Returns 0 for a valid parent.

[slot, virtual] bool QSqlTableModel::select()

Reads the rows of the table again, with the filter and the sort order that are set, and returns true on success. The model is reset and pending changes are lost. lastError() tells what went wrong.

See also setTable(), setFilter(), setSort() and selectStatement().

[slot, virtual] bool QSqlTableModel::selectRow(int row)

Reads the row row from the table again and returns true on success. Pending changes of the row are dropped; the other rows are left alone, and the model is not reset. Returns false if there is no such row, if it waits to be inserted, or if it is no longer in the table.

See also select().

[virtual, protected] QString QSqlTableModel::selectStatement() const

Returns the SELECT statement that select() runs: the fields of the model from the table, with the filter and the sort order. Returns an empty string, and sets lastError(), if no table is set or the table has no fields.

See also select(), orderByClause() and setFilter().

[override] bool QSqlTableModel::setData(const QModelIndex &index, const QVariant &value, int role = Qt::EditRole)

Reimplements QSqlQueryModel::setData().

Sets the value of the item at index to value and returns true on success. role has to be Qt::EditRole. With the strategy OnFieldChange the change is written to the database at once, and the result tells whether that worked; with OnRowChange, a pending change of another row is written first; with OnManualSubmit nothing is written.

See also data(), submit(), submitAll() and isDirty().

[virtual] void QSqlTableModel::setEditStrategy(EditStrategy strategy)

Sets when changes are written to the database to strategy. Pending changes are dropped first.

See also editStrategy() and revertAll().

[virtual] void QSqlTableModel::setFilter(const QString &filter)

Sets the filter to filter: the condition of an SQL WHERE clause, without the word WHERE, such as "age > 30". Only the rows that meet it are read. If the rows have been read already, they are read again at once; otherwise the filter is used by the next select().

The filter becomes part of the statement as it is written.

See also filter() and select().

[protected] void QSqlTableModel::setPrimaryKey(const QSqlIndex &key)

Sets the fields that find a row in the table to key. This is for a table whose primary key the driver does not report, such as a view.

See also primaryKey() and primaryValues().

bool QSqlTableModel::setRecord(int row, const QSqlRecord &record)

Gives the row row the values of the fields of record that are marked as generated, and returns true if every one of them could be set. Fields are matched by name; a field of record that the model does not have makes the function return false, after the others were set. With the strategy OnFieldChange the row is written to the database at once.

See also record(), insertRecord() and setData().

[virtual] void QSqlTableModel::setSort(int column, Qt::SortOrder order)

Sets the column and the order that the next select() sorts the rows by, to column and order. A negative column means no order.

See also sort() and orderByClause().

[virtual] void QSqlTableModel::setTable(const QString &tableName)

Makes the model work on the table tableName and reads the fields of the table, but not its rows: call select() for those. The filter, the sort order and pending changes are dropped. lastError() tells if the connection has no such table.

See also select() and tableName().

[override] void QSqlTableModel::sort(int column, Qt::SortOrder order)

Reimplements QSqlQueryModel::sort().

Sorts the rows by the column column in the order order, by reading the table again with select(). Pending changes are lost.

See also setSort().

[slot, override] bool QSqlTableModel::submit()

Reimplements QSqlQueryModel::submit().

Writes the pending changes to the database if the strategy is OnRowChange or OnFieldChange, and returns true on success. With OnManualSubmit it does nothing and returns true: use submitAll(). Views call this when the current row changes.

See also submitAll(), revert() and lastError().

[slot] bool QSqlTableModel::submitAll()

Writes all pending changes to the database and returns true on success. With the strategy OnManualSubmit, and whenever a row was deleted, the table is then read again, which resets the model; otherwise only the rows that were written are read again. On failure it stops at the change that failed, returns false and leaves the changes that remain pending; lastError() tells what went wrong. Changes that were written before the failure stay written: wrap submitAll() in a transaction of the connection to make it all or nothing.

See also revertAll(), submit(), QSqlDatabase::transaction() and lastError().

QString QSqlTableModel::tableName() const

Returns the name of the table of the model.

See also setTable().

[virtual, protected] bool QSqlTableModel::updateRowInTable(int row, const QSqlRecord &values)

Updates the row row in the table of the database with the fields of values that are marked as generated, and returns true on success. The row is found by primaryValues(). Called by submitAll(); a subclass can write the row in another way.

See also insertRowInTable(), deleteRowFromTable() and beforeUpdate().