Feature Request: Dropdown for Foreign Keys on New Record Entry

[tsunosekai]

Thank you for the wonderful plugin. I think it would be incredibly useful to have a dropdown menu for selecting data from the referenced table when adding a new record to a table with a foreign key. The existing feature, where hovering the mouse over an element with a foreign key displays the corresponding row from the referenced table, is very handy and I anticipate a similar look and feel for this proposed feature.

[gdaunlam]

I think one alternative could be to implement in the input field a suggestion for autocompleting values that belong to the referenced table. (input autocomplete)

[yy0931]

I prototyped a part of the feature a while ago so I can release it under an experimental flag now, though there should be bugs since I haven't tested much.

https://github.com/yy0931/sqlite3-editor/assets/54441600/23cc3225-a38f-4c4d-8d89-53c0d8865aff

[yy0931]

You can use the feature on v1.0.99 by adding "sqlite3-editor.ui.experimental.foreignKeyDropdown": true to VSCode's settings.

[gdaunlam]

¿Is it possible that this feature is not available for custom selects?

[yy0931]

Do you mean that you want to change the data in a table by clicking a cell then a dropdown after executing a SELECT statement that contains a rowid column (like SELECT rowid, * FROM table1)? If so, then yes, it's possible to implement that.

[tsunosekai]

Thank you. I turned on the settings and used it and it works ideal! This editor has become the best sqlite editor for me!

[tsunosekai]

I agree with gdaunlam's request. It will be more convenient.

[gdaunlam]

I just reread your message, and I realize that I had understood something different. What I was referring to is that the suggestions for foreign keys are currently only visible when editing a table. However, when trying to edit a view or a custom select, the suggestions don't work, even for custom selects that display the same information as the query editor. I've attached examples.

Here it works.

Here it doesn't.

In any case, this would be unnecessary if the feature you're suggesting of clicking on a cell and then on a dropdown menu to execute a SELECT statement containing a rowid column could be done from a view or a custom select. However, I believe that would be much more complex.

[yy0931]

when trying to edit a view or a custom select, the suggestions don't work

Yes, that's the issue I was referencing in my previous comment.

In my previous comment, I was trying to describe the following sequence of events:

1. First, you open a query editor by clicking the "Query Editor" button.
2. Next, you run a SELECT statement that includes a rowid column in the editor.
3. After you do this, you select a table cell.
4. Then you want to modify data in the table using a dropdown menu that is shown under the UPDATE statement.

(By the way I'll fix the broken HTML in "Updating data through ..." in next version.)

[gdaunlam]

Okay, it's clear, thank you very much. This plugin is greatly improving my day-to-day work today. Any contribution or help that we can give you, let us know. Greetings!

[yy0931]

I've implemented https://github.com/yy0931/sqlite3-editor/issues/20#issuecomment-1711110657, along with other improvements, in v1.0.100.

[gdaunlam]

It works perfectly, the feature is greatly appreciated. I'm not sure how it will work with multiple rowids, but it feels very clean at first glance. Thank you very much!

[yy0931]

It works perfectly, the feature is greatly appreciated.

Thanks!

multiple rowids

Perhaps the help message, which says you need to add a rowid to edit, was misleading? Every table that doesn't use the WITHOUT ROWID option has an implicit ROWID column. Even if you create a rowid column, as you do in #20 (comment), there is a separate implicit ROWID associated with the table that can be referred to as oid or _rowid_. I wanted to say in the message that you need to SELECT the implicit rowid column, not that you need to create a rowid column.

sqlite> create table t(x);
sqlite> insert into t values (1);
sqlite> select rowid, * from t;
rowid|x
1|1

[gdaunlam]

I interpret what you mentioned, but when trying it, the error continued to appear. I'll share my table schema with you in case it helps in any way.

CREATE TABLE "record" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT, 
    "title" TEXT NOT NULL,
    "description" TEXT,
    "status" TEXT NOT NULL DEFAULT 'nth' ,
    "type" TEXT NOT NULL DEFAULT 'daft',
    "order" INTEGER,
    "expiration" TEXT, 
    "topic" TEXT, 
    "eta" TEXT,
    "created_at" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(status) REFERENCES status(name)
    FOREIGN KEY(type) REFERENCES type(name)
    FOREIGN KEY(topic) REFERENCES topic(path)
);
(I recently changed from 'rowid' to 'id' to confirm my issue.)

[yy0931]

I was misunderstanding SQLite's behavior, specifically the following part. I can fix that.

In the exception, the INTEGER PRIMARY KEY becomes an alias for the rowid. https://www.sqlite.org/rowidtable.html

[gdaunlam]

Okay, since my use is more personal, it's not a problem for me initially, but I'll keep it in mind. I'll let you know if I find anything else!

[yy0931]

The bug should be fixed in v1.0.107.

[gdaunlam]

I see that now it's not necessary to query using rowid; great job, thank you very much! I tried to look at some of the code. Are you migrating your backend to Rust? How's the experience so far?

[yy0931]

I migrated the Python code to Rust in v1.0.91, although it remains on an independent branch. I chose Rust largely due to my familiarity with the language, having contributed to (non-OSS) small Rust projects in the past, so the experience so far has been positive.

[yy0931]

I have been considering of adding dropdowns within table cells, but after prototyping, I noticed the overlap between click actions of cell selection and dropdown opening made the UI harder to use, so I close this issue for now.