Autocompletion of schema names after FROM keyword

[amjith]

It should be possible to list other databases as schema names and provide column completions for them.

Caveats:

• This requires fetching all the databases, tables and their columns. Which could get expensive if the user has thousands of databases.

Possible Solutions:

• Fetch the current database information right away. Fetch the rest of the databases lazily after launch. This could also put undue strain on the database.
• Fetch the database metadata on demand. For eg: SELECT * FROM dbname. will parse the dbname and fetch the metadata in a background thread. Cache this data for next use. This might feel laggy to the user but the users are programmers, so I'm guessing they'll understand the concept of latency and such.

[adamchainz]

Another possible solution: local file cache of 'probably exists' stuff that gets checked in lazily in the background. Schemas rarely change, and if they do no one is going to think worse of mycli for it temporarily autosuggesting a database/table/column that was removed recently.

[amjith]

Yeah this was suggested by someone on the gitter channel. I'm not sure when to invalidate this file cache.

I think adding a background thread for fetching the completions is the first step.

[adamchainz]

It doesn't need to be invalidated - just refreshed. The background thread re-downloads database and table names and replaces whatever's in there, perhaps one table at a time. If table X was cached and no longer exists - remove it from the cache and list.

[mdsrosa]

@amjith Yes, that's what I suggested on the gitter channel and I agree with @adamchainz. It doesn't need to be invalidated - just updated. Now, when should we do the cache update? It should be the first thing to do when the program starts, right? Launch the 'update thread'. What do you think?

[amjith]

Yes. You're right it I could kick off a background thread that starts populating the file and use the file as a cache that is partially refreshed every time the user switches the databases or relaunches mycli.

I'm still concerned about the performance impact. Although for that we can add an option to disable the eager fetching.

[mdsrosa]

@amjith Exactly! That's what I was thinking. And maybe we could create a command to just update the cache file. Something like mycli --update-db-cache-file, if the user disable the eager fetching.

[tsroten]

@amjith I think I'm not quite understanding this all the way. Is this the same thing as #111?

[amjith]

Yes, this is loosely coupled with #111. I was told that it is common practice to have multiple databases in MySQL and run JOIN queries between the tables in those different databases.

So one could do something like:

mycli> USE db1
mycli> SELECT * FROM db1.users JOIN db2.customers ON db1.users.id = db2.customers.id

In Postgres, there is the concept of schemas. Where each schema has multiple tables in it and you can run a query that joins tables across multiple schemas. But MySQL doesn't have the concept of schemas. Instead, it lets you treat separate databases as schemas.

So if this gets implemented then this will be a superset of #111.

[tsroten]

@amjith Thanks for the clarification!

[phaza]

Was a bit surprised to find this wasn't implemented. We have MANY databases, and do joins across them in all imaginable ways. Without this feature, it's putting a lot of strain on my muscle memory :/

[Shu-Ji]

so 6 years later, is there any updatings?