Configuration for continuation prompt (like PS2 or PROMPT2 in some shells)

[apstndb]

Sometimes, we want to reuse queries by copy-and-paste, but multi-line prompts make it harder.

spanner> SELECT *
      -> FROM Singers
      -> WHERE SingerId = 1;

It is harder to reuse than indentation like

spanner> SELECT *
         FROM Singers
         WHERE SingerId = 1;

Currently, the prompt string for continuous line is hard coded.
https://github.com/cloudspannerecosystem/spanner-cli/blob/v0.10.4/cli.go#L353

It would be nice to have configuration like PROMPT2 in some shells or bool flag (--only-indent-multiline?).

[apstndb]

I have implemented the prototype.

[yfuruyama]

Sorry for the delayed comment.

The reason why I chose -> for the multiline prompt is just because it's used in mysql command. There is no any other good reason for that.

I have used spanner-cli and experienced several times that the current prompt (->) is hard to use for copy/paste, so I'm not hesitate to change it.

Do you have any good example we should keep using -> as a prompt? If not, I think we can just remove it.

[apstndb]

I'm not confident enough to say that prompt2 is harmful.
It can be more useful in interactive query writing if it can display current processing information.

e.g. %_ of zsh PROMPT2

$ ls '
quote> ' "
dquote> " `
bquote> ` (
> ps ) $(
cmdsubst> ls )

It seems psql has similar functionality(%R)
https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PROMPTING

In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis.

[yfuruyama]

Thank you for sharing the reference, but still I'm not sure if it's really useful or not.

I checked this blog article about Prompt2 usage for Postgres, but I feel it's more complicated and hard to understand what that prompt means.

Excerpt from the article:

[local] gabe@my_database=# SELECT
[more] - > '
[more] ' > name
[more] ' > '
[more] - > FROM users;

Of course, it depends on the prompt the user sets, but instead of letting the user to choose the prompt, I want to provide the best option that's useful for most of the use cases.

In that sense I still lean toward the option that removes the prompt at all.

[yfuruyama]

Maybe we can 1) change the default prompt2 as an empty string and 2) provide the command line option to allow users to change the prompt2.

But I'm wondering how many users will use that command line option.

[apstndb]

I checked this blog article about Prompt2 usage for Postgres, but I feel it's more complicated and hard to understand what that prompt means.

I think the example of that article is not practical because multiline string literals are not popular in regular SQL queries.
Personally speaking, mismatched parentheses in subqueries is a daily problem.

sqlite has similar functionality in continuation prompt.

sqlite> SELECT '
'  ...> 1' FROM (
(x1...> SELECT 2
(x1...> )
   ...> ;

[apstndb]

After investigating, I think parser status in continuation prompt is common and useful feature in SQL interactive shells in ideal, but it is not low hanging fruits.

I'm worried that removing the continuation prompt completely in default will confuse users because they won't know if they're still in the middle of typing.
So I think that adding --prompt2 option and keep current behavior in default is a conservative choice.

[apstndb]

After investigating, I think parser status in continuation prompt is common and useful feature in SQL interactive shells in ideal, but it is not low hanging fruits.

Currently, the statement separator (gsqlsep) only considers quotes and comments, so some change is required to show if they are inside parentheses.

[apstndb]

I have come to the following thought:

• Without the ability to show parser status, there is no particular reason to customize the continuation prompt string.
• People who care about copy-and-paste will probably use ~/.spanner_cli.cnf to default the continuation prompt to just whitespace.
• Given the conventions of interactive shells, it would be nice for users to see the continuation prompt (->) by default.

So I think spanner-cli needs a boolean flag to set the continuation prompt to whitespace.
Typical use case:

• no_prompt2 = true in .spanner_cli.cnf
• merely used --no-prompt2=false to override setting in spanner_cli.cnf

[yfuruyama]

Adding the option to disable the prompt2 is fine, but I still doubt that it can address the user's pain points when they want to copy/paste the multi-line queries. This is because there is less chance the (light) users will be aware of the existence of the option and they might keep using the default prompt.

This is not an urgent task, so let's keep this discussion open and here the feedback from other users as well.

[apstndb]

I suspect that feedback on issues is biased towards only heavy users.

I think we need to go back to why continuation prompts exist as a convention.

I don't yet do deep research about retionale of PROMPT2, but I think this question is an example of the need for continuation prompts for novice users.
https://stackoverflow.com/questions/19710968/sqlite3-prompting-instead-of-sqlite
Novice users (and sometimes I) can forget to input terminating semicolon(;).

Without a continuation prompt, a novice user would not aware that spanner-cli was continuing to read and wait for something.

spanner> SELECT 1
                 
                 broken?
                 ^C

spanner> SELECT 1
             -> 
             -> (What is wanted? Let me see...)

I found that PostgreSQL ML contains a similar topic.
https://www.postgresql.org/message-id/5467.1304089798@sss.pgh.pa.us

[apstndb]

MySQL document also have many sentence about the prompt.
https://dev.mysql.com/doc/refman/8.4/en/entering-queries.html

[yfuruyama]

I understand it's not a good idea to remove the continuation prompt by default as it confuses users when the terminating semicolon is not entered.

Given that removing the continuation prompt likely confuses users (even heavy users as well), I feel it's not also a good idea to provide the option to remove the continuation prompt.

If there is a situation when a user wants to copy the multi-line query itself, it can be accomplished by looking back the query history (Ctrl-P or ↑ key) and get the folded single-line query. It's not a perfect solution as it cannot copy the query result together, but I think it can be used as a workable solution.