bug#22016: 25.0.50; sql-interactive-mode prompts and header alignment in query output

bug#22016: 25.0.50; sql-interactive-mode prompts and header alignment in query output

[Phil Sainty]

Regarding the following Question and Answer:

http://emacs.stackexchange.com/q/13315
http://emacs.stackexchange.com/a/18403

In which multi-line SQL queries can result in output like so:

select a, b, c
from table1
where a < 10;

mydb=> mydb=> mydb-> mydb-> mydb->     a     |      b       |   c
----------+--------------+-------
         1 | 2015-01-05   | 59120
         7 | 2015-01-06   | 59121
         3 | 2015-01-07   | 59122
(3 rows)

i.e. we get multiple prompts all on a single line (continuations due
to the multi-line query), and then the header row for the output is
on that same line as well, which means that the column headers don't
line up with their data.

I'm not sure whether this issue is product-specific. Both parties
are using Postgres in this instance.

I've hacked together a workaround for both issues in the above-linked
answer, and that's doing the trick for me, but it would be great if
sql-interaction-mode handled this by default.


-Phil




In GNU Emacs 25.0.50.1 (x86_64-unknown-linux-gnu, X toolkit, Xaw3d 
scroll bars)
  of 2015-10-10
Repository revision: bb7c182fdaf8553ffdc9162f322177ae2f7fa0c2
Windowing system distributor 'The X.Org Foundation', version 11.0.11701000
System Description:	Ubuntu 15.04

Configured using:
  'configure --prefix=/home/phil/emacs/trunk/usr/local
  --with-x-toolkit=lucid --without-sound'

Configured features:
XAW3D XPM JPEG TIFF GIF PNG RSVG IMAGEMAGICK DBUS GSETTINGS NOTIFY
GNUTLS LIBXML2 FREETYPE XFT ZLIB TOOLKIT_SCROLL_BARS LUCID X11

Important settings:
   value of $LANG: en_NZ.UTF-8
   value of $XMODIFIERS: @im=ibus
   locale-coding-system: utf-8-unix

Major mode: Lisp Interaction

Minor modes in effect:
   tooltip-mode: t
   global-eldoc-mode: t
   electric-indent-mode: t
   mouse-wheel-mode: t
   tool-bar-mode: t
   menu-bar-mode: t
   file-name-shadow-mode: t
   global-font-lock-mode: t
   font-lock-mode: t
   blink-cursor-mode: t
   auto-composition-mode: t
   auto-encryption-mode: t
   auto-compression-mode: t
   line-number-mode: t
   transient-mark-mode: t

Recent messages:
For information about GNU Emacs and the GNU system, type C-h C-a.
funcall-interactively: End of buffer

Load-path shadows:
None found.

Features:
(shadow sort gnus-util mail-extr emacsbug message dired format-spec
rfc822 mml mml-sec mm-decode mm-bodies mm-encode mail-parse rfc2231
mailabbrev gmm-utils mailheader sendmail rfc2047 rfc2045 ietf-drums
mm-util help-fns help-mode easymenu cl-loaddefs pcase cl-lib mail-prsvr
mail-utils time-date mule-util tooltip eldoc electric uniquify
ediff-hook vc-hooks lisp-float-type mwheel x-win term/common-win x-dnd
tool-bar dnd fontset image regexp-opt fringe tabulated-list newcomment
elisp-mode lisp-mode prog-mode register page menu-bar rfn-eshadow timer
select scroll-bar mouse jit-lock font-lock syntax facemenu font-core
frame cl-generic cham georgian utf-8-lang misc-lang vietnamese tibetan
thai tai-viet lao korean japanese eucjp-ms cp51932 hebrew greek romanian
slovak czech european ethiopic indian cyrillic chinese charscript
case-table epa-hook jka-cmpr-hook help simple abbrev minibuffer
cl-preloaded nadvice loaddefs button faces cus-face macroexp files
text-properties overlay sha1 md5 base64 format env code-pages mule
custom widget hashtable-print-readable backquote dbusbind inotify
dynamic-setting system-font-setting font-render-setting x-toolkit x
multi-tty make-network-process emacs)

Memory information:
((conses 16 80193 6352)
  (symbols 48 18983 0)
  (miscs 40 40 125)
  (strings 32 13139 4407)
  (string-bytes 1 375848)
  (vectors 16 10590)
  (vector-slots 8 411879 8564)
  (floats 8 135 112)
  (intervals 56 232 0)
  (buffers 976 11)
  (heap 1024 24529 988))

bug#22016: 25.0.50; sql-interactive-mode prompts and header alignment in query output

[Lars Ingebrigtsen]

Phil Sainty <psainty@orcon.net.nz> writes:

> Regarding the following Question and Answer:
>
> http://emacs.stackexchange.com/q/13315
> http://emacs.stackexchange.com/a/18403
>
> In which multi-line SQL queries can result in output like so:
>
> select a, b, c
> from table1
> where a < 10;
>
> mydb=> mydb=> mydb-> mydb-> mydb->     a     |      b       |   c
> ----------+--------------+-------
>         1 | 2015-01-05   | 59120
>         7 | 2015-01-06   | 59121
>         3 | 2015-01-07   | 59122
> (3 rows)
>
> i.e. we get multiple prompts all on a single line (continuations due
> to the multi-line query), and then the header row for the output is
> on that same line as well, which means that the column headers don't
> line up with their data.

Michael sent a control message marking this bug as
unreproducible/moreinfo under the subject "Close bugs", but didn't
actually close the bug reports.  I'm not quite sure what Michael's
intentions were -- Michael, did you want the bug reporter to supply more
information, or did you mean that this bug had been fixed?

-- 
(domestic pets only, the antidote for overdose, milk.)
   bloggy blog: http://lars.ingebrigtsen.no

bug#22016: 25.0.50; sql-interactive-mode prompts and header alignment in query output

[Phil Sainty]

Hi Lars, Michael,

On 2019-10-09 18:22, Lars Ingebrigtsen wrote:
> Michael sent a control message marking this bug as
> unreproducible/moreinfo under the subject "Close bugs", but didn't
> actually close the bug reports.  I'm not quite sure what Michael's
> intentions were -- Michael, did you want the bug reporter to supply
> more information, or did you mean that this bug had been fixed?

Looking at this again... `sql-interactive-remove-continuation-prompt'
used in `comint-preoutput-filter-functions' is presumably intended to
take care of this very problem; and that pre-dates this bug report.

So there may well have been some other contributing factor responsible
for it not working in my case.

The first thing that springs to mind is that the default postgres
prompt regexps do not recognise hyphens, so I was also modifying those
patterns to recognise my hyphenated database names.

The current sql.el postgres defaults are:

      :prompt-regexp "^[[:alnum:]_]*=[#>] "
      :prompt-cont-regexp "^[[:alnum:]_]*[-(][#>] "

My config has:

     ;; Default postgres pattern was: "^\\w*=[#>] " (see 
`sql-product-alist').
     (setq sql-prompt-regexp "^\\(?:\\sw\\|\\s_\\|-\\)*=[#>] ")
     ;; Ditto for continuation prompt: "^\\w*[-(][#>] "
     (setq sql-prompt-cont-regexp "^\\(?:\\sw\\|\\s_\\|-\\)*[-(][#>] "))

I then have the following notes:

     ;; Allow symbol chars and hyphens in database names in prompt.
     ;; TODO: Try to make this *strictly* accurate, in accordance with:
     ;; 
http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
     ;; (and then submit the fix upstream).  (hmm... hyphens are not
     ;; actually mentioned in that documentation :/ )
     ;;
     ;; SQL identifiers and key words must begin with a letter (a-z,
     ;; but also letters with diacritical marks and non-Latin letters)
     ;; or an underscore (_). Subsequent characters in an identifier or
     ;; key word can be letters, underscores, digits (0-9), or dollar
     ;; signs ($). Note that dollar signs are not allowed in
     ;; identifiers according to the letter of the SQL standard, so
     ;; their use might render applications less portable. The SQL
     ;; standard will not define a key word that contains digits or
     ;; starts or ends with an underscore, so identifiers of this form
     ;; are safe against possible conflict with future extensions of
     ;; the standard.
     ;;
     ;; The system uses no more than NAMEDATALEN-1 bytes of an
     ;; identifier; longer names can be written in commands, but they
     ;; will be truncated. By default, NAMEDATALEN is 64 so the maximum
     ;; identifier length is 63 bytes. If this limit is problematic, it
     ;; can be raised by changing the NAMEDATALEN constant in
     ;; src/include/pg_config_manual.h.


Tracing my own config changes, I can see that I supported symbol chars
in `sql-prompt-regexp' before other changes, however I seemingly
didn't fix this in `sql-prompt-cont-regexp' until the same commit in
which I added my filter.

Potentially that means all I'd ever really needed was for both prompt
regexps to be compatible, but I would need to do some more testing to
figure this out.

It looks like the postgres prompt regexps are still a going concern
for sql.el itself, so that could be up for discussion in the meantime.


-Phil