improved "explorability" - new feature?

[ahillio]

I would LOVE to be able to show tables and scroll through that list (using j / k vi-style bindings) and hit enter on a selected table to automatically run describe selected_table_name. Then from that view of the table's structure, to be able to choose columns for a select query that's automatically crafted with the from selected_table_name.

I don't know what it would take to implement a workflow like that, but it would be amazing, thrilling.

I just discovered mycli and it's a wonderful step towards how mysql on the commandline "should just work". Thank you <3

[gfrlv]

You can get pretty close to this workflow right now:

1. type SELECT FROM -> scroll through the list of suggested tables with arrow keys, select with Enter
2. go back to the last character of SELECT, hit space -> a list of column names will be suggested

Selecting in the query result can be supported, in principle, but it is a long conversation, and it needs more compelling examples. Currently, the results are fed to an external pager application (LESS by default, configured with the PAGER variable in your ~/.myclirc), and then never passed back to mycli.

[ahillio]

Thanks! Yes, I've seen how the autosuggesting works in this way; it's really great, I wish I discovered it years ago!

What I'm wishing for here is enhancement in how the pager works and supplying a query for mycli to then run when the pager is closed.

To go from reading something in less and then closing it and typing the command is a disconnect that I find cognitively challenging, when the words disappear I'm prone to forget and then have to remember ("what was the thing?" "oh yeah" then start typing the next query), and it'd be great to eliminate that disconnect. I could change the config option so the less output stays on the screen (instead of being erased) but that's not desirable.

It would be so slick and smooth to be able to navigate in a more exploratory way without the cognitive interruption. I'd be happy to discuss this if it's a conversation you'd like to engage in :)

[ahillio]

So could this be achieved through the pager alone? Would mycli be able to accept a command from the pager if the pager was able to supply the command when it closes?

[gfrlv]

The idea is that the single point of interaction between mycli and the pager application should be piping text into its standard input. This way any application that reads stdin and writes to stdout can act as a pager (a particularly nice example is pspg). On the other hand, the pager will not know anything about the database structure. The most it can do is copy strings to the clipboard. For example, if we teach the pager to copy values in the form myTable.myColumn = 111, we can then paste it onto the mycli prompt and quickly build a query around that.

Another facet of explorability would be automatic completion of joins: you type select * from myTable join and get a list of possible relations. It already works (with some limitations) in pgcli and is super useful, right now we are working on porting this mechanism to mycli as well.

[gfrlv]

The problem with such projects is that they can quickly lead to the idea of emulating your typical GUI application, just drawn on terminal. That's very different from what mycli is trying to be. If you can imagine how to naturally incorporate such "exploratory" behavior into a strictly CLI app, that could be very interesting.

[ahillio]

Automatic completion of joins sounds great too :)

No need to go overboard and emulate a GUI app, but to be able to do the following would make a big difference in my experience looking for data and exploring tables...

1) in mycli I type show tables. This opens up the list of tables in the separate pager program. 2) that pager allows me to scroll through the list in a way where one table name is highlighted/selected. When I get to a table I want to do something with... 2A) I hit "Enter" key. This loads the results of describe selected_table_name. So I guess the pager would have to send that back to mycli which would then open the results again in the pager. 2B) Hitting another key (not sure which, Q and S keys are already taken in the current pager) then sends back to mycli prompt where select | from selected_table_name is in the prompt and the | indicates where the cursor is so the user starts typing and column names for the table are auto-suggested 2C) Hitting Q or whichever exits the pager and returns to mycli normally. 3) From the table information view (the results of describe table_name) the pager would allow you to scroll down the list selecting columns (for example by hitting "Enter") and then hit Shift+Enter to send back to mycli with the query started like select col1,col3,col7 from table_name| where the cursor (again represented by |) is at the end. (no additional functionality or 3B)

[ahillio]

As a side note, though on the topic of enhancing the pager, it could be nice to have column-width sized according to the data and wrapping the column name onto multiple lines in instances like this to more efficiently use available screen space (I had to expand the terminal window to fit all this data without weird linewrapping). But again that's not exactly on topic with the goal of this issue.