SQLite3 generated column does not show up in QSqlRelationalTableModel

Question

I have a QTableView connected to a QSqlRelationalTableModel. Everything works as expected, however, it seems to be impossible to have a sqlite3 generated column in my model:

bhd [INTEGER] GENERATED ALWAYS AS (round(d_mess * 130 / bhd_hoehe)) STORED

The above statement is the default way to add a generated column in sqlite3. But it does not appear in my model. When I change the column to a standard integer column, it's there. It does not matter if the generated column is of type STORED or VIRTUAL or if I omit the GENERATED ALWAYS keyword.

Is this by design? Or a sqlite3 driver problem?

Answer 1

Views seem not to be affected by this issue. I'm aware it's more a workaround than a solution, but you could create a dummy view just with your foreign key and the generated field.

For example, I have a simple table User like this:

CREATE TABLE User (
    ID        INTEGER PRIMARY KEY AUTOINCREMENT
                      UNIQUE,
    name      TEXT,
    surname   TEXT,
    title      TEXT,
    full_name         GENERATED ALWAYS AS (title || ' ' || surname || ' ' || name) STORED
);

As is, no QSqlRelation pointing to User.full_name will work, as you pointed out. However, I've created a simple view like

CREATE VIEW UserFullNameView AS
    SELECT ID,
           full_name
      FROM User;

Now the following binding works fine

model: QSqlRelationalTableModel
model.setRelation(foreign_field, QSqlRelation('UserFullNameView', 'ID', 'full_name')

where foreign_field is the index of User.full_name that in turn will be mapped as the UserFullNameView.full_name field.

Answer 2

In SQLite it is a bit more difficult to get information about database objects, because it does not have an information_schema. SQLite has the sqlite_schema table which holds raw CREATE statements for all tables/indexes/views/triggers. So the most common way to find all the columns in a table is to use PRAGMA. Unfortunately PRAGMA table_info() will not display GENERATED columns. But there is PRAGMA table_xinfo(), which does display GENERATED columns (and information if they are stored or not).

QSqlTableModel (and QsqlRelationalTableModel) internally rely on the database driver to get the columns of the table. In this case QSQLiteDriverPrivate::getTableInfo in src/plugins/sqldrivers/sqlite/qsql_sqlite.cpp uses PRAGMA table_info instead of table_xinfo to get the columns of the table. I consider this a bug in the implementation of the SQLite driver.

I opened QTBUG-120382.