Foreign key combobox in table browser #614 by mgrojo · Pull Request #1550 · sqlitebrowser/sqlitebrowser

mgrojo · 2018-09-29T19:20:19Z

This adds a combobox for the editor delegate when the associated column
has a foreign key constraint. In this way the user can choose the key value
based from a combo-box.

Known problems:

When the editor delegate is activated the combobox switches to the first
value. It should preserve the current value, as intended in setEditorData.
The encoding of the foreign table is the one from the current table, but
they might be different (we allow that, although it's unconventional).

This adds a combobox for the editor delegate when the associated column has a foreign key constraint. In this way the user can choose the key value based from a combo-box. Known problems: - When the editor delegate is activated the combobox switches to the first value. It should preserve the current value, as intended in setEditorData. -The encoding of the foreign table is the one from the current table, but they might be different (we allow that, although it's unconventional).

mgrojo · 2018-09-29T19:24:55Z

Apart from the known problems, I'm unsure about the interface change in sqlitetablemodel.h, but the alternative way to get the references was very cumbersome.

The first mentioned knwon-problem is weird. I'm following this example and it doesn't work. I've tried also calling the parent implementation or different ways to find the data in the combo without success.

The concurrent run of the query made the setEditorData function to not find the current value in the combo-box, because it was not actually any data loaded. Using completeCache() the data is loaded before trying to set the data in the editor. To minimise possible performance problems, the model only loads the foreign key field, instead of the whole related table.

# Conflicts: # src/sqlitetablemodel.h

mgrojo · 2018-12-08T15:44:55Z

I'm made a commit that solves the first problem that I had with the combo-box not getting the current value in the cell. I've also merged and solved the conflict with the master branch.

I think this is in shape to be squashed and merged into the master branch, unless the second problem is considered a blocker. @MKleusberg do you want to review it or shall I merge it as is?

MKleusberg · 2018-12-09T11:54:19Z

+        SqliteTableModel* fkModel = new SqliteTableModel(m->db(), parent, m->chunkSize(), m->encoding());
+        sqlb::ObjectIdentifier tableId = sqlb::ObjectIdentifier(m->currentTableName().schema(), fk.table());
+
+        QString column = fk.columns().at(0);


This line crashes when there are no columns. Like in this database:

CREATE TABLE a(x INTEGER PRIMARY KEY, y INTEGER); CREATE TABLE b(p INTEGER, q INTEGER, FOREIGN KEY(q) REFERENCES a);

MKleusberg · 2018-12-09T11:55:35Z

I noticed a crash during my tests (just added a comment about that) which I think should be fixed before merging. But other than that I see no reason to not merge this 😄

MKleusberg · 2018-12-09T12:05:36Z

One more thing: When the column allows NULL values we might want to add them as an option to the combobox. If not you are forced to select a value as soon as you edit the field. You can always set it to NULL later by using a keyboard shortcut or the context menu, so it's not super important to have it in the combobox but I think with the NULL option in there it would be easier to use and understand 😄

Except for not-null columns, a NULL value is given as possible value in the foreign-key combo-box. Avoid crash for tables referencing other without specifying the column.

mgrojo · 2018-12-09T21:59:15Z

@MKleusberg Thank you for the review. I've reproduced the crash and fixed it. Regarding the foreign keys that can be NULL, I've also added that value to the list as you suggests. It totally makes sense and it wasn't difficult to implement. It's already in the branch, in case you want to take a final look.

SilvioGrosso · 2018-12-10T19:37:56Z

Hello @mgrojo

THANKS a lot for this new great improvement!

Just out of curiosity...
I am wondering whether is it already implemented the option to delete or update some records and get this change "cascaded" into the drop-down list?

For instance, I have a drop-down list with 3 items to pick:

apple;
pear;
strawberry.
Whenever I add later a new record in my table, e.g. orange, this new item is available in the drop-down list as well. The same occurs when I delete an item (e.g. I delete apple and this item is no longer available afterwards in the drop-down list).

mgrojo · 2018-12-10T20:09:22Z

@SilvioGrosso The values are indeed obtained from the referenced table each time the drop-down list is used. For example, reusing the tables defined by Martin:

CREATE TABLE a(x INTEGER PRIMARY KEY, y INTEGER);
CREATE TABLE b(p INTEGER, q INTEGER, FOREIGN KEY(q) REFERENCES a);

Every value inserted in primary key a.x is used when editing b.q.

If you delete, insert or update values in a.x, the updated values will be in the drop-down list the next time you edit b.q.

The NULL value is also added because it doesn't have a not-null constraint. But if you define b in this way:

CREATE TABLE b(p INTEGER NOT NULL, q INTEGER, FOREIGN KEY(q) REFERENCES a);

Then the dropdown won't have NULL included.

If you want to try this tomorrow, I think there won't be any problem in merging this now.

SilvioGrosso · 2018-12-10T20:33:29Z

@mgrojo

It will be a HUGE pleasure to test it! 👍

mgrojo · 2018-12-10T20:37:01Z

@SilvioGrosso Great!
@MKleusberg If you see anything more, we can fix it in the master branch. I don't want to make Silvio to wait 😄

MKleusberg · 2018-12-10T20:39:32Z

I don't see any more problems 😄 Feel free to merge this any time 😉

SilvioGrosso · 2018-12-11T20:51:07Z

Hello @mgrojo

Today, I have tested on Window 7 (32 bit) and Windows 10 (64 bit) this new very COOL feature!
I have downloaded today's installers (.msi 32 bit + .msi 64 bit).

Here are my SQL steps to test this new option:

CREATE TABLE SAMPLE (
ID_SAMPLES INTEGER NOT NULL,
SAMPLE_DESC TEXT NOT NULL PRIMARY KEY);
--I have imported into this table many records from a csv table

CREATE TABLE DISEASE
(ID_DISEASE INTEGER NOT NULL,
DISEASE_DESC TEXT NOT NULL PRIMARY KEY);
--I have imported into this table many records from a csv table

CREATE TABLE ANFI (
ID_ANFI INTEGER NOT NULL PRIMARY KEY,
SAMPLE_DESCRIZ TEXT,
DISEASE_DESCRIZ TEXT,
FOREIGN KEY(SAMPLE_DESCRIZ) REFERENCES SAMPLE(SAMPLE_DESC)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (DISEASE_DESCRIZ) REFERENCES DISEASE (DISEASE_DESC)
ON UPDATE CASCADE
ON DELETE CASCADE);
--this is the table where I am going to test the drop-downs lists (on both column sample_descriz and disease_descriz)

Everything works fine!
I have updated and deleted many records by writing some SQL code into the Execute SQL tab
In addition, I have repeated the same steps through the Browse Data tab.
No problem whatsoever with the foreign keys! 👍
All update and delete on cascade works as well.

Here is a screenshot on Windows 10 (64 bit) of the drop-down list:

THANKS a lot indeed for all your efforts!

mgrojo · 2018-12-11T22:37:07Z

@SilvioGrosso Thanks a lot for testing and reporting back. All the credit about "cascading" has to go to SQLite 😄 I suppose we can close now your issue #1523.

mgrojo added 3 commits September 30, 2018 02:02

Adapt foreign key comobox to new sqlitetypes interface

d92c477

Merge branch 'master' into fk_combobox

1b9cf5d

# Conflicts: # src/sqlitetablemodel.h

MKleusberg reviewed Dec 9, 2018

View reviewed changes

NULL added in FK combo-box for columns allowing it

11330a6

Except for not-null columns, a NULL value is given as possible value in the foreign-key combo-box. Avoid crash for tables referencing other without specifying the column.

mgrojo merged commit 87e9732 into master Dec 10, 2018

mgrojo deleted the fk_combobox branch December 11, 2018 22:33

mgrojo mentioned this pull request Dec 15, 2018

Foreign keys: drop-down menu to select correct values (feature request) #1523

Closed

mgrojo restored the fk_combobox branch October 14, 2023 21:27

mgrojo deleted the fk_combobox branch October 14, 2023 21:45

jess-garnett mentioned this pull request May 13, 2025

Combobox Foreign Key Help Page #3310

Open

Navigation Menu

Repository navigation

Foreign key combobox in table browser #614 - #1550

Foreign key combobox in table browser #614#1550
mgrojo merged 5 commits into
masterfrom
fk_combobox

mgrojo commented Sep 29, 2018 •

edited

Loading

Uh oh!

mgrojo commented Sep 29, 2018

Uh oh!

mgrojo commented Dec 8, 2018

Uh oh!

MKleusberg Dec 9, 2018

Uh oh!

MKleusberg commented Dec 9, 2018

Uh oh!

MKleusberg commented Dec 9, 2018

Uh oh!

mgrojo commented Dec 9, 2018

Uh oh!

SilvioGrosso commented Dec 10, 2018

Uh oh!

mgrojo commented Dec 10, 2018

Uh oh!

SilvioGrosso commented Dec 10, 2018

Uh oh!

mgrojo commented Dec 10, 2018

Uh oh!

MKleusberg commented Dec 10, 2018

Uh oh!

SilvioGrosso commented Dec 11, 2018

Uh oh!

mgrojo commented Dec 11, 2018

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

mgrojo commented Sep 29, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

mgrojo commented Sep 29, 2018

Uh oh!

mgrojo commented Dec 8, 2018

Uh oh!

MKleusberg Dec 9, 2018

Choose a reason for hiding this comment

Uh oh!

MKleusberg commented Dec 9, 2018

Uh oh!

MKleusberg commented Dec 9, 2018

Uh oh!

mgrojo commented Dec 9, 2018

Uh oh!

SilvioGrosso commented Dec 10, 2018

Uh oh!

mgrojo commented Dec 10, 2018

Uh oh!

SilvioGrosso commented Dec 10, 2018

Uh oh!

mgrojo commented Dec 10, 2018

Uh oh!

MKleusberg commented Dec 10, 2018

Uh oh!

SilvioGrosso commented Dec 11, 2018

Uh oh!

mgrojo commented Dec 11, 2018

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

mgrojo commented Sep 29, 2018 •

edited

Loading