Feature: built-in table viewer (pager...) for things like multi-sort

[0-issue]

There is a case to be made for a native table viewer (pager) in usql. It might sound to break the unix philosophy, but I don't think that is necessarily the case. For instance once a user is already viewing a table, they might want to do a multi-column sort. Pagers like pspg reimplement limited functionality of PostgreSQL to do sorts on a single column, but that is only so much an external pager can do. In an ideal world, the interface to database like psql/usql/etc would have the ability to show tables, and help the user do tricks like multi-column sort with a nifty set of keybindings -- all without having to go to the the prompt to change the query. Here's a description of a multi-column sort feature that I originally requested in pspg: https://github.com/okbob/pspg/issues/225. But as rightly pointed out by its developer @okbob, such a feature would be better implemented by a system that does SQL queries and perhaps can leverage existing application state to do the job right. Such an architecture will also avoid errors where sort done by PostgreSQL is different from one done by the pager, as the query application will just do real SQL queries under the hood to do sorts.

[kenshaw]

There's already the \crosstab functionality, you'd be able to implement another different *View in the tblfmt package easily to support this. I don't think it'd be quite difficult, and I'd support a well implemented version. Sorting on the client wouldn't be difficult. In terms "pager" functionality, though, I'm not sure if you mean not using the system's less / more, or what though.

[kenshaw]

Also, if you're just looking for certain ways to find data, off-the-cuff, one can easily do this by piping usql output to tools like sed, awk, perl, ...

[0-issue]

@kenshaw Here's a 14 second screencast that shows US Census projections for 2019 preloaded in a table. In the screencast, I do the following steps:

1. run a simple PostgreSQL command table <table_name>; to view the table. It loads it in pspg which provides limited sort capability.
2. then I select the state column, and sort it in descending order by pressing d. All data for Wyoming shows up on top, but the count_names are yet unsorted.
3. finally I select the county column, and try to sort it ascending order by pressing a. But that sorts the counties across states, as pspg doesn't have a feature (for reasons mentioned above) to do multi-column sort.

Not sure how else would a table viewer support multi-column sort with such simple keybindings without also folding the functionality of pager within its state. Like how would usql know which column has been selected by the user with a mouse click or key navigation? Perhaps a specialized pager like pspg can communicate with usql using some IPC mechanism?

https://github.com/xo/usql/assets/25681815/520c5dc3-102a-4a9d-9bc2-08ad11176af6

[kenshaw]

Yes, I 100% understood what you meant. Is there a reason you don't just do this directly with your SQL query?

[0-issue]

The reason to not do this with SQL query is more of a UI question. If a user is already looking at a table, pressing a key to sort and unsort can show them in-place how the data changes. Also, another obvious reason would be speed: in my original request for such a feature in pspg, I asked if one can sequentially apply such sort operations and undo them too with simple keystrokes a, d, u, arrow keys. Btw, I updated my last comment with a suggestion for a IPC channel between pspg and usql. Perhaps pspg can exchange command information with usql on a socket to run queries on behalf of user, that way usql wouldn't have to reimplement pspg's functionality, and pspg wouldn't have to reimplement usql's functionality. @okbob

[kenshaw]

I understand your ask, there are reasons this isn't done currently in psql, which is: this isn't how psql was designed to be used. You could hypothetically be retrieving millions, or hundreds of millions of records at a time. Keeping that data on the client side, to allow for easy reordering, would be problematic. In my opinion, humble as it is, the way psql and usql are made to be used is to easily and quickly do small queries at a time, or design "big queries" that are part of larger reporting systems. I can only speak for myself, and note: I'm open to enabling usql to help others how they see best, but for what you're describing, I personally would be doing all of that from SQL directly.

[okbob]

st 28. 6. 2023 v 8:42 odesílatel amanvm @.***> napsal:

The reason to not do this with SQL query is more of a UI question. If a user is already looking at a table, pressing a key to sort and unsort can show them in-place how the data changes. Also, another obvious reason would be speed: in my original request for such a feature in pspg, I asked if one can sequentially apply such sort operations and undo them too with simple keystrokes a, d, u, arrow keys. Btw, I updated my last comment with a suggestion for a IPC channel between pspg and usql. Perhaps pspg can exchange command information on a socket to run queries on behalf of user, that way usql wouldn't have to reimplement pspg's functionality, and pspg wouldn't have to reimplement usql's functionality. @okbob https://github.com/okbob

The communication between psql and pspg is only one direct. psql just sends formatted results (no query, no data types), and pspg reads it. pspg cannot return any information.

Unfortunately, this is around 50 years old technology.

— Reply to this email directly, view it on GitHub https://github.com/xo/usql/issues/419#issuecomment-1610854698, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO45GFU6GSPMSF3WMN3TXNPG6FANCNFSM6AAAAAAZWQVODY . You are receiving this because you were mentioned.Message ID: @.***>

[0-issue]

@kenshaw

You could hypothetically be retrieving millions, or hundreds of millions of records at a time. Keeping that data on the client side, to allow for easy reordering, would be problematic.

I agree, but that's not what I was thinking... Data will remain on server side, and won't be reordered on client side. usql would update the query on request of pspg each time user request a sort. usql already knows what table a user is currently viewing, it does not need to send the data regarding SQL query and data types to pspg (@okbob). pspg would send a note to usql with a list of tuples containing column numbers and sort direction. As a response, usql would send a new (modified) query to the server, and notify pspg to repage its input. pspg just needs to send a list of tuples that have column numbers and direction IMO.

[okbob]

st 28. 6. 2023 v 9:11 odesílatel amanvm @.***> napsal:

@kenshaw https://github.com/kenshaw

You could hypothetically be retrieving millions, or hundreds of millions of records at a time. Keeping that data on the client side, to allow for easy reordering, would be problematic.

I agree, but that's not what I was thinking... data will remain on server side, but usql would update the query on request of pspg each time user request a sort. usql already knows what table a user is currently viewing, it does not need to send the data regarding SQL query and data types to pspg @.*** https://github.com/okbob). pspg would send a note to usql with a list of tuples containing column numbers and sort direction. As a response, usql would send a new (modified) query to the server, and notify pspg to repage its input. pspg just needs to send a list of tuples that have column numbers and direction IMO.

Theoretically. Practically, there can be significant performance issues. Sort for a different column, columns can be tens time slower. Rewriting the query (changing ORDER BY clause) can be difficult too (to be result syntactically and semantically valid correct). SQL is a terrible language for parsing. We had a similar discussion a few years ago, when we talked about the possibility of sorting the output of \d.+ commands in psql. It is easy if you have some query builder, and you have a query in some structured form, but usual SQL clients don't do it.

— Reply to this email directly, view it on GitHub https://github.com/xo/usql/issues/419#issuecomment-1610890096, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO45AID2VKKLLSQYYC23XNPKL7ANCNFSM6AAAAAAZWQVODY . You are receiving this because you were mentioned.Message ID: @.***>

[0-issue]

We can avoid query parsing if it is a special command like (pick a better name perhaps): table_interactive <table_name> instead of table <table_name>. Then the command sent to the SQL server can be made by just appending order by <column-a> arc, <column-b> desc, ... to basic table <table_name> command. Though idk if there is a better option...

[okbob]

čt 29. 6. 2023 v 6:24 odesílatel amanvm @.***> napsal:

We can avoid query parsing if it is a special command like (pick a better name perhaps): table_interactive instead of table

, where i is for interactive. Then the command sent to the SQL server can be made by just appending order by arc, desc, ... to basic table command. isn't more simple to write a simple table browser just for this purpose? — Reply to this email directly, view it on GitHub , or unsubscribe . You are receiving this because you were mentioned.Message ID: ***@***.***>

[0-issue]

Maybe it is, idk. In my understanding and time with IPC mechanisms like sockets to act as command channels between processes, they can enrich existing CLI tools to avoid code and effort duplication. You already have great working pager pspg that currently has 1-way data channel (pipe) IPC mechanism, if it has an option --socket to have a command channel (socket) then a modern sql shell like usql need not reimplement table features of pspg.

[okbob]

čt 29. 6. 2023 v 6:41 odesílatel amanvm @.***> napsal:

Maybe it is, idk. In my understanding and time with IPC mechanisms like sockets to act as command channels between processes, they can enrich existing CLI tools to avoid code and effort duplication. You already have great working pager pspg that currently has 1-way data channel (pipe) IPC mechanism, if it has an option --socket to have a control channel (socket) then there a modern sql shell like usql need not implement the features.

It is possible, but it is not simple. You need to synchronize two applications, and both communication should be asynchronous.

Then it is much easier to implement your own data browser instead of calling an external pager. Routines related to printing content are maybe less than 20% of pspg. IPC can work, but it needs to be all components written from start to end for using IPC. Without it, it is terrible work. Writing pspg was not funny or easy. I did it, because there is not any other way. But if I can choose to complete architecture for something like this, I probably started to write a complete SQL client from scratch (with internal browsing).

— Reply to this email directly, view it on GitHub https://github.com/xo/usql/issues/419#issuecomment-1612421256, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO432MGSWPKSM4D3AI7LXNUBPPANCNFSM6AAAAAAZWQVODY . You are receiving this because you were mentioned.Message ID: @.***>

[kenshaw]

Closing this, as there is no intention to add a native pager to usql, as I believe that's best left to third-party implementations. I'm more than open to someone authoring one and making a PR though!