Query building feature enhancement

[SirClickALot]

I am teaching beginners with a very simple database comprising thre tables, one of which is named person.

I have noticed that when building a simple query from scratch with no table open, we cannot auto-complete column names.

For example in a simple person table described as...

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int          | NO   | PRI | NULL    | auto_increment |
| last_name     | varchar(255) | YES  |     | NULL    |                |
| first_name    | varchar(255) | YES  |     | NULL    |                |
| email_address | varchar(255) | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

When I build a query using the query builder, I cannot get it to autocomplete...

select first_ for me - As a beginner, I would have expected it to know about that column identifier by searching all tables?

After I have done a SELECT whatever FROM person in my query the build DOES then seem to know about it - ow that of course makes sense but really, it ought to be able to have a good go autocompleting it even if, given ambiguity of column identifier, it might have suggest more than one this ...

person.first_name
**ANOtherTable**.first_name

I am using Antares for teaching absolute beginners precisely because it is so simply and beautifully presented and so it's a shame it doesn't have that extra intelligence.

STRANGLEY though, even without a FROM clause, later Anatares' query DOES pick up on the fact that I mean the first_name column - so it feels like it's aware of the history of my auto-completes?

Thanks

[Fabio286]

Hi @SirClickALot,

I am very pleased to read that you are using Antares to teach beginners!

In short the auto-complete works that when a word is typed it searches for a match on the list of tables it has in memory. If it finds match then it runs a query to extract the list of columns and pushes them into the suggestions. If a table disappears from the query text the columns are removed.

Unfortunately, it can be a time-consuming and heavy operation to cache all table columns of a database, not so much when it is a dozen tables but in those cases where the tables are a few hundred.

Certainly the feature you request is useful, especially to beginners, and that is why I will consider it, however, before implementing it I need to find a way to make it safe for all use cases of Antares (maybe something like an incremental persistent cache).

[SirClickALot]

Thanks @Fabio286,

I do understand your point about it being potentially time-consuming but, for the sake of a short-lived hourglass, I think the benefit would be well worth it from everyone not just beginners.

Could it not be done once at the point of opening the database - with (say) a bit of feedback saying "analysing metadata" or something like that.

The cache could then simply 'updated' as and when the structure changes?

Failing that, what about added the column data to your cache for all tables for which you have tabs open?

[bart-schaefer]

Could it not be done once at the point of opening the database

Just for comparison, I am using Antares with a database containing more than fifty thousand tables. Even doing this once at opening would take an unreasonably long time and too much memory. If this is added as a feature, it needs to be one configured off by default.

[Fabio286]

I am using Antares with a database containing more than fifty thousand tables.

To improve performance in these use cases I'm implementing this.