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?

That's not the problem. All is wired according to the docs. To put it in another way: All ordinary columns show up as expected. When I change a column to be calculated (As addressed above), this column disappears from my model alltogether. — Oliver Bienert, Aug 09 '22 at 11:58
The command: `model.record().count` returns 16, when I declare the column in question as `bhd INTEGER`. It returns 15, when I change the ddl for that column to `bhd [INTEGER] GENERATED ALWAYS AS ...`. — Oliver Bienert, Aug 09 '22 at 12:05
Have the same problem. I think this is a big lack! It is hard to find any related issue on the web while i think dealing with generated columns is quite a common task. Have you solved this problem? — zigma12, Jan 26 '23 at 13:13

score 0 · Answer 1 · answered Feb 05 '23 at 19:42

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')

SQLite3 generated column does not show up in QSqlRelationalTableModel

1 Answers1