New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Fully qualified completion #1433

Closed
tlhackque opened this Issue Jun 17, 2018 · 9 comments

Comments

Projects
None yet
3 participants
@tlhackque
Copy link

tlhackque commented Jun 17, 2018

Describe the new feature

I may be overly paranoid, but I write SQL with fully-qualified names. That is, with all
database fields qualified by table name and both the table name and field in double quotes.

This is future-proofing - it guarantees that new SQL keywords and table fields won't require
code changes. Whether it's clearer or simply verbose is a matter of preference.

Completion in the editor should support this style. Currently, it's awkward - accept a
field name, then go back and add the double quotes, and the "table" dot before each one.

What I'd like is an editor preference that would cause autocompletion to:

  • If the field name is unique in the schema, automatically prefix the correct table name
  • If the field name is not unique, show it with each possible table name so I can click on the intended one
  • In any case, the completion should include the table name and double-quote both.

E.g., "tablea"."key" "tableb"."key"

For people who work with databases maintained by people with different opinions, it would be ideal if the preference was remembered per-database - but that's not essential.

Additional info

Please answer these questions before submitting your feature request.

Is your feature request related to an issue? Please include the issue number.

No - unless you consider usability an issue.

Does this feature exist in another product or project? Please provide a link.

Don't know.

Do you have a screenshot? Please add screenshots to help explain your idea.

No.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jun 17, 2018

The concept sounds fine. I'm personally not sure how much effort it would take to implement, as it sounds like it'll be something that hooks into the QScintilla side of things. One of our C++ developers would have to chime in. 😄

@mgrojo mgrojo self-assigned this Sep 5, 2018

mgrojo added a commit that referenced this issue Sep 9, 2018

Completion of qualified table names
This adds the schema names to the possible completions and adds support
for cascading completion of schema.table.field.

See related issue #1433

mgrojo added a commit that referenced this issue Sep 9, 2018

Better qualified completion support for quoted identifiers
The quotes around identifiers was hindering the automatic proposal of
fields when the table is completed or of tables when the schema name is
completed. By adding the dot surrounded by quotes (parent's end-quote and child's
begin-quote) as word separator, the child completion menu is activated
when the user presses the child's begin-quote.

See related issue #1433

mgrojo added a commit that referenced this issue Sep 9, 2018

Improvements for drag and drop of items from the DB Schema dock to ed…
…itor

Two new options editable from a new context menu of the dock:
- Drag & Drop Qualified Names: add table name to fields and schema name
to other objects (except for "main" schema)
- Drag & Drop Enquoted Names: whether to surround the identifiers by the
configured quoting characters for identifiers.

Support for dragging & dropping of attached databases names.

Add "." as separator for multiple dropped objects other than fields (since
they are not usually used in SQL as a list). This allows to compose
qualified names by dropping the parent and the child items together. This
is only generally useful when the "Qualified Names" option is disabled.

See related issue #1433

mgrojo added a commit that referenced this issue Sep 9, 2018

Improvements for drag and drop of items from the DB Schema dock to ed…
…itor

Two new options editable from a new context menu of the dock:
- Drag & Drop Qualified Names: add table name to fields and schema name
to other objects (except for "main" schema)
- Drag & Drop Enquoted Names: whether to surround the identifiers by the
configured quoting characters for identifiers.

Support for dragging & dropping of attached databases names.

Add "." as separator for multiple dropped objects other than fields (since
they are not usually used in SQL as a list). This allows to compose
qualified names by dropping the parent and the child items together. This
is only generally useful when the "Qualified Names" option is disabled.

See related issue #1433
@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 10, 2018

Hi @tlhackque,

I've made some changes that might be of interest for you. They should help with the style that you are using, although it is not implemented exactly as you propose. The details are in the commits linked to this issue, but summarizing:

  • Schemas (attached databases) are now provided for completion
  • The list of fields of a table is proposed for completion after typing: "table"." (previously the quotes were interfering in the completion)
  • The same for "schema"." for suggesting the tables in that schema.
  • An option in the DB Schema dock for configuring how names are dragged and dropped to the editor. There is an option for dropping qualified names ("schema"."table" or "table.field"). When the option is not enabled, one can still select (with Ctrl+click) the levels that one wants to drop to the editor and they are joined with the dot. For example: select schema, press ctrl, click on table, click on field, release Ctrl, drag and drop in the editor to get: "schema"."table"."field". Please note, that there is not much intelligence in this, you can drop absurd names if you want. User is still responsible for writing correct names, but if the selected objects are fields, the joining character is ", " as this is expected to be used as a list of fields in a SELECT or similar query (this case was already implemented).

Could you give it a try with the tomorrow's nightly and provide your feedback?

I'd also like to add completion for the quotes, that is, if the identifier was started with a quote, the completion should also add the closing quote, but that doesn't seem to be supported by QScintilla, the editor library, that we are using, so the user is still responsible to add the starting and ending quotes. The drag and drop will help on that regard, though. Nevertheless I've raised a question in their mailing list, and they might suggest a solution.

@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Sep 14, 2018

Thank you for the effort. Sounds like progress. I will give it a try on real data when I can - I'm away from my databases.

I'm not sure I'll like drag and drop on a machine with a touchpad (tap and a half) - it can be temperamental, and adding the manipulation of Ctrl likely makes it more difficult.

I created a toy database (one table, two fields) on my notebook PC.

I found some unexpected behaviors.

I can't find the option that you mentioned in the schema dock, or in preferences. I downloaded today (14-Sep), but the version is 8-Sep, Perhaps the nightly builds are stalled? In any case, it's still after your note.

image

In each of the following, I'm in an Execute SQL window, SELECT has been typed.

Drag and drop does quote the field names.

If I type a double quote, then drag a field name, I get ""Field2" - the first quote remains. (Interestingly, typing " puts the cursor to the left of the "; dragging switches the cursor to the right.

If I type Field, I get a selection box (Field1 and Field2). Sometimes neither tab nor single click accepts the field. Double click does. This isn't intuitive, When it happens, it is reproducible. Not sure how I got it into that state.

If I start to type the table name (glorp), I have a choice of glob and glorp at "o" - not sure why no choice at "g" or "l" - perhaps you have a minimum length. No problem. If I down arrow to glorp and TAB, it completes. Given that it's a table name & there is no field of that name, I was slightly surprised not to have the "." added,.

If i add the ., I get Field1,Field2 offered. If I type F backspace F, no choice is offered. But if I add "ie", the choice is offered again. The behavior resets if I backspace over the ".', then add it again.

If I drag "glorp" to the select, it is highlighted in blue as if it is selected. But backspace doesn't clear the field. If I tab enough, the SQL tab gets the focus and backspace works. So it looks as though drag & drop doesn't set the focus to the target tab.

If I select (with control/click) glorp and either field, then drag and drop them, they appear quoted, but joined with comma, not dot. Perhaps I need the hidden option.

image

If I want quoted everything, might it work to put "identifier" in the selection list (instead of identifier? That might get the editor to insert the quote.

Hope this helps - I'm out of time.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Sep 14, 2018

Perhaps the nightly builds are stalled?

Yeah, that's me. I'm moving countries at the moment, and the nightly build server is a mac mini which needs fully setting up at the new location. It'll probably be a few days until I get around to it, as I'm putting time into higher priority (for me) stuff atm. 😄

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 14, 2018

Sorry, @tlhackque , but I didn't remembered that the nightly builds weren't being generated. The commit is from 9 September, so it isn't in the version you've used. Some of the behaviour that you've described would still be present, though. I'll take a deeper look at your comments and answer them later.

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 14, 2018

Drag and drop does quote the field names.

It did so without option. After my commit it can be configured.
"Table1"."Name"

If I type a double quote, then drag a field name, I get ""Field2" - the first quote remains.

This will still happen, unless you configure drag and drop for not quoting the identifier.

(Interestingly, typing " puts the cursor to the left of the "; dragging switches the cursor to the right.

I don't understand this. Indeed the drag puts the cursor at the left of the dropped text. This is the behaviour of QScintilla. It's strange, because other widgets from Qt or other toolkits set the cursor at the right of the dropped text. I don't like that either. I'll ask in the QScintilla list, but my last question didn't get a response 😞

If I type Field, I get a selection box (Field1 and Field2). Sometimes neither tab nor single click accepts the field. Double click does. This isn't intuitive, When it happens, it is reproducible. Not sure how I got it into that state.

It happens when you single click an item in the completion menu. I think it should then apply the completion to that item, but it seems to break the use of the keyboard for completion. This is performed also by QScintilla.

If I start to type the table name (glorp), I have a choice of glob and glorp at "o" - not sure why no choice at "g" or "l" - perhaps you have a minimum length. No problem. If I down arrow to glorp and TAB, it completes.

Yes, there is a threshold of 3 characters to trigger completion.

Given that it's a table name & there is no field of that name, I was slightly surprised not to have the "." added,.

There isn't any kind of parsing of the query while parsing, so it cannot be so intelligent to know that in that context a field is expected and not a table.

If i add the ., I get Field1,Field2 offered. If I type F backspace F, no choice is offered. But if I add "ie", the choice is offered again. The behavior resets if I backspace over the ".', then add it again.

The completion of "child items" is immediate after typing parent.or "parent". but it seems to reset back to general independent completion when you delete the child. Not the greatest of issues 😄

If I drag "glorp" to the select, it is highlighted in blue as if it is selected. But backspace doesn't clear the field. If I tab enough, the SQL tab gets the focus and backspace works. So it looks as though drag & drop doesn't set the focus to the target tab.

This is again the standard behaviour for QScintilla and other Qt widgets. The focus is kept in the source of the drag. For our use case it would indeed make more sense to change to the editor.

If I select (with control/click) glorp and either field, then drag and drop them, they appear quoted, but joined with comma, not dot. Perhaps I need the hidden option.

This is the version thing. I can report it here when the nightlies are back again.

If I want quoted everything, might it work to put "identifier" in the selection list (instead of identifier? That might get the editor to insert the quote.

I tried to provide QScintilla with quoted identifiers, but it was ignoring the quotes. This is one of my questions to the mailing list, but no answer for the moment.

mgrojo added a commit that referenced this issue Sep 23, 2018

Fix logic issues in name composition for drag & drop
Some combinations of options and dropped name weren't working as expected
with f33943f

See related issue #1433
@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 26, 2018

@tlhackque The nightly builds are being generated again. You might like to try the new functionality now.

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 28, 2018

@tlhackque Did you have time to check the last changes? If there are pending enhancements that you'd like to be addressed, it would be better to open a new fresh issue, so this one can be closed for our next stable release.

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Feb 8, 2019

Closing this as we considered it solved in the new v3.11.0 release. If you consider there are still some rough edges, you are welcome to reopen this again or open a new issue as you see fit.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment