Feature request: add autocompletion for columns names in the Execute SQL window

[SilvioGrosso]

Hello everyone,

Please, add the autocompletion for columns names in the Execute SQL window.
This is especially handy with Sqlite tables with huge amounts of columns :-)

In essence, whenever you type your SQL code in the Execute SQL window, it would be useful to get automatically the option to choose, by pressing enter on your keyboard, the name of your column.
With PostgreSQL, I have worked with pgcli in the past [1] with the autocompletion nicely implemented.
As regards, open source softwares, Dbeaver implements this feature beautifully [2] as well.

Here is a short video I have recorded to show this option with Dbeaver (on Windows 10) together with Sqlite databases:
https://www.dropbox.com/s/r1vmzjfw05ifhi2/DBEAVER_AUTOCOMPLETION_COLUMNS_NAMES-SQLITE_TABLE.mp4?dl=0

P.s: YEP, I am aware this is a geeky teachie request: bear with me :-)

[1] https://github.com/dbcli/pgcli
[2] https://dbeaver.jkiss.org/

[chrisjlocke]

There is autocomplete already, but it does take approx 3 characters to kick in.

[chrisjlocke]

Interestingly, if the table contains a space (as in my example) then selecting the entry doesn't correctly insert the field name - only up to (not including) the first space.

Edit: Thinking abotu it (OK, it took a while...) it wouldn't make valid SQL, but DB4S should still select the table to give the user an opportunity to put quotes around it.

additionally, if I type 'this, the autocomplete pops up, but again, only 'this' is accepted - it doesn't give me the full field name. Neither if I start the field with `

[SilvioGrosso]

Hello @chrisjlocke ,

Thanks a lot indeed for your reply ! :-)

There is autocomplete already, but it does take approx 3 characters to kick in.

I would have never ever suspected this was possible indeed because this feature has never worked for me on Windows 7 - 10 (x64.exe installers ) :-)

Just take a look at this video recorded tonight on Windows 10 (64 bit).
INDUFF_2017 table has 77 columns (ID_PROG, NAME_TECHNICIAN, etc).
It is indeed possible to autocomplete SQL commands (e.g. SELECT, FROM etc) and also tables' names (e.g. INDUFF_2017) but no column name is ever available, no matter how many characters I type.
I have even tried to press the TAB on my keyboard to unleash this option to no avail.
This same INDUFF_2017 works fine with Dbeaver as regards the autocompletion columns.

Here is my video recording:
https://www.dropbox.com/s/i9frjfd0elc4sy7/DB-BROWSER_AUTOCOMPLETION_COLUMNS.mp4?dl=0

[chrisjlocke]

Could be a language thing.
Can you either upload the database (without data is fine) to dbhub.io or email it to me? DB4S!chrisjlocke.co.uk (replace ! with @)

[SilvioGrosso]

Hello @chrisjlocke !

Can you either upload the database (without data is fine) to dbhub.io or email it to me?

Sure :-)
Here it is (I have only left 1 single record because the original table has 6000 rows...):
https://www.dropbox.com/s/4irep9xam6081r0/CREA_2017.db?dl=0

Encoding is UTF-8 (Italian is my native language, BTW).
Its CSV has been created through LibreOffice Calc (on Windows 10 - 64 bit) before importing it into Db Browser for Sqlite.

This same table (INDUFF_2017) works fine with DBeaver (Windows 10 - 64 bit)
Usually, as soon as you reach the under-score character (e.g. NAME_TECHNICIAN column) the autocompletion name column feature always pops up with Dbeaver.

Thanks a lot indeed for your help !

[chrisjlocke]

This sounds similar to #1194 where DB4S was struggling with different languages/encoding.
Are you using the latest nightly? When raising issues its useful to keep the template provided as that gives us details on what system you're using, etc.

[SilvioGrosso]

Hello @chrisjlocke ,

Thanks a lot for your reply and help : it is much appreciated ! :-)

I always work with nightly builds (x64.exe) on Windows 10 - 64 bit.
The encoding for my tables is always UTF-8 and I import my tables as CSV (fields are separated by | ).

I have just downloaded today's build (x64.exe) and it doesn't work as regards the autocompletion name for columns. Only commands (SELECT, FROM etc) and the names of the tables are proposed.
Same tables work fine with Dbeaver on Windows 10 - 64 bit.
If you take a look at my above pasted screenshot, this Java open source software even nicely shows the number of column and its type (text, integer etc): which is extremely useful with huge amounts of columns.

Just tried, with today's build (x64.exe on Windows 10) this SQL code:

CREATE TABLE users (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT,
surname TEXT,
age INTEGER,
assunzione DATE
) ;

INSERT INTO users VALUES
(1, 'marco', 'costanzo', 33, '2017-10-30'),
(2, 'sergio', 'sacco', 28, '2017-10-31') ;

When I try, in the Execute SQL window, to SELECT these data (TABLE users) the autocomplete option for columns never works (only commands and name of tables are proposed).
This feature has never worked for me on Windows 7 - 10 (64 bit) and I have thought it was not implemented on Sqlite Browser yet :-)

[chrisjlocke]

What happens if you type the table name first, then press '.' (period). Do the columns appear then?

[SilvioGrosso]

Hello @chrisjlocke ,

As you suggested, whenever I type my table name with a period I am indeed able to choose my column name later on 👍

However, this is far from being perfect because with tables with long names I end up with very long SQL code because of this...
In case my table is named aaaaaaaaaa I get aaaaaaaaaa.ColumnNameA in my code.
Just multiply this aaaaaaaaaa. for every column in your code and you are in trouble :-)

On top of this, for colums names autocompletion, it also looks like Sqlite Browser does not accept alias for tables' names.
For instance, If I type:
SELECT
FROM mytable as t --where t is the alias for mytable
Afterwards, Sqlite Browser does not propose anymore the choice for picking my column names whenever I type SELECT t. (instead of SELECT mytable.)

This being said, THANKS a bunch for your help : it has been extremely useful.
All in all, Sqlite Browser is already extremely powerful and I do enjoy working with this software !

[justinclift]

Ahhh. I guess we'd need to populate the list of autocompletion choices a bit differently, so typing just the first few letter of a column name displays the list of potential names from all tables.

That's not a part of the code I've personally ever touched. @MKleusberg has though, so might be able to guess how doable that is. 😄

[MKleusberg]

That should be no problem at all. I think the only reason why we're not doing this already is because it might result in a very long list of very confusing names especially when different tables have similar column names. The only way we could avoid that is by parsing the SELECT statement which we currently can't do (that would also fix @SilvioGrosso's use case of SELECT t.).

So what do you all think? Leave it as-is or always suggest all column names?

[mgrojo]

Would we know from what table is the column? If not, it would be a bit confusing to have completion for all the columns in the database without knowing the context.

Another option to improve the situation is implementing the feature requested in issue #119. It might be easier to implement than an intelligent completion for any column name. Although it has some challenges too 😄 See unfinished pull request #1250.

By the way, I'm not getting any Table completion when I open the database or project from the command line. That might be worth of its own issue 😄

[justinclift]

@mgrojo Hmmm, kind of thinking that it's probably better to add all the columns from all the tables in the list, even without knowing the context.

All it's doing is providing a drop down list of options, and doing it this way provides the list sooner rather than later. Not seeing a downside... in concept anyway. 😄

[mgrojo]

Yeah, you're probably right. Anyway, it would be great to have more information than just the name and the icon. Don't know if that is feasible.

[mgrojo]

I've made the change and I'm now convinced that having all the columns is an improvement. I tried to add the table information to the completion tooltip but it is not elegant. QScintilla is not thought for that and the possible hacks are ugly. So I will commit the change to complete all table fields in isolation, if you all don't mind.

[justinclift]

So I will commit the change to complete all table fields in isolation, if you all don't mind.

Sure, go for it. 😄

[mgrojo]

@SilvioGrosso The auto-completion is now suggesting all the column names in isolation, without writing first the table name. Could you give it a try in the next nightly build and provide some feedback?

[justinclift]

@mgrojo Was it pushed? Just asking because the commit history for today doesn't seem to include auto-completion things. 😄

[mgrojo]

@justinclift No, it wasn't 😄 It was not even committed. Oh, my god, today I've been totally clumsy with git. Now it's fixed.

[SilvioGrosso]

Hello everyone,

I have downloaded the 32.exe installer to run it on Windows Xp (professional).
The autocompletion works fine !
No problem whatsoever.

Afterwards, I have installed the 64.exe installer on Windows 10 (64 bit): my personal computer.
Again, the autocompletion works fine !
I have tested different tables.

On both versions (32 - 64 bit) I have tested also the dragging of columns into the Execute SQL window (from the DB Schema window) and everything works nice as well !

THANKS a lot for these last improvements :-)

[justinclift]

Awesome @SilvioGrosso, thanks for testing and letting us know. 😄

Good stuff @mgrojo! Another solid win. 😀

[bk322]

Autocompleting table names with spaces doesn't work on Linux in 3.11.0.

[chrisjlocke]

@bk322 - The final version of 3.11.0 or one of the earlier betas?

This works in Windows.

I can't see why this would vary depending on the operating system. Can you list the tables and their schema (don't need any data, obviously)

[justinclift]

The betas were only made for macOS and Windows, so guessing this is either a custom compiled thing or the Linux package repositories have been updated.

@bk322 Which version of Qt was it compiled with? It should say in the Help -> About dialog for our application. 😄

[bk322]

@justinclift I compiled it today from github release. "About" dialog says:

Version 3.11.0
Qt Version 5.11.1
SQLite Version 3.24.0

Maybe I should compile the master?

[bk322]

@chrisjlocke I just tried it for tables. Previously I tried it for fields. The browser suggests the correct name, but if I press enter -- only first word gets entered:

[justinclift]

Hmm.... the commit which should have made this work is in both the v3.11.x and master branches. So, compiling from master probably won't change things.

To me, it sounds like this is just not yet working 100% after all, and needs a bit of adjusting so to correctly handle things like "white space in table names" (etc) too. 😄

[chrisjlocke]

@chrisjlocke I just tried it for tables. Previously I tried it for fields.

Aah, sorry - thought you'd put, "Autocompleting table names with spaces doesn't work ", which is why I tried it with tables.

Yes, pressing Enter or Tab on a selection with spaces doesn't select the whole selection. Additionally, don't forget even if it did, you'd need quotation marks around 'this table'.

select * from "this table"

While it is an issue (SQLite obviously supports tables with spaces) from a DBA point of view, its a logistical nightmare, so I would personally avoid tables and fields with spaces in them. I'd either use underscores (this_is_a_table) or my personal preference, camelCase (thisIsATable). Its not only easier to read, but causes far less issues in the long run.

[bk322]

@chrisjlocke I mostly work with CSV files, and these are made of Excel files. The headings there most of time contain spaces.

I'm dreaming of a completion that would put the whole field in double quotes.

[mgrojo]

I asked for completing identifiers with embedded spaces in the QScintilla mailing list. The answer was:

The format is of a "word" (eg. a function declaration) followed an optional explaination.

You can achieve what you want in both cases but you would need to bypass the high-level API and use the low-level Scintilla API.

So it's possible, but not very straightforward.

[mgrojo]

@bk322

I'm dreaming of a completion that would put the whole field in double quotes.

You can try dragging&dropping the fields from the "DB Schema" panel. See #1433 for some comments on use.

[justinclift]

So it's possible, but not very straightforward.

Thanks @mgrojo, that's very well done. Something for a future time maybe.

[mgrojo]

I'm going to close this issue, since the main enhancements and fixes are already done and for the identifiers containing space, we already have a specific one: #2061. If I've missed something, feel free to reopen it.

[SharkTal]

What happens if you type the table name first, then press '.' (period). Do the columns appear then?

I had this problem solved by using this method.

[bk322]

@SharkTal the columns appear, but if you select a column with a space in the title - only part of the column name is inserted (specificially, the part up to a first space).

[justinclift]

Interesting. Sounds like there's more we need to do to make this 100% working well. 😉