How to change the indentation of major mode (SQL-mode)?

Question

Configuration:

Emacs 25.0.50 on Ubuntu 15.04.

How to produce?

Do emacs -Q, then M-x sql-mode.

Paste the following in the buffer:

SELECT  SUM(T01.foobar)
FROM   db_foo.bar T01
WHERE   T01.fooID = 1
AND    T01.barID = 2
AND  T01.foo IS NOT NULL'

When I do C-x h, and then M-x indent-region, it says the indent region is done. But the SQL statement is indented in an another way:

SELECT  SUM(T01.foobar)
FROM    db_foo.bar T01
WHERE   T01.fooID = 1
    AND T01.barID = 2
    AND T01.foo IS NOT NULL

I would like to change the indentation it in the following:

 SELECT  SUM(T01.foobar)
 FROM    db_foo.bar T01
 WHERE   T01.fooID = 1
 AND     T01.barID = 2
 AND     T01.foo IS NOT NULL

So I decided to change the indentation myself. When looking on the source code of SQL mode, I don't see any hints where I could change the indentation for words and for "AND".

Any suggestion how I could change the indentation of SQL string?

If I'm `TAB`bing from the first column to the second, `sql-mode` (Emacs 24.5.1) produces your second (preferred) format precisely. — phils, Jan 12 '16 at 23:48
Same here, I get the same behaviour as @phils. Where is the cursor when you hit `TAB` (if you are using `TAB` to indent)? Also, do you get the same tabbing behaviour when you start emacs with the `-Q` option? — elethan, Jan 13 '16 at 03:54
@phils I use Emacs 25.0.50 if that makes any difference. My sql-mode doesn't do this. — ReneFroger, Jan 13 '16 at 14:02
@elethan thanks for the suggestion, I get another behaviour, but it's still not aligned properly. So I made an update to my post above. — ReneFroger, Jan 13 '16 at 14:11
This is not from sql-mode. Assume key used just inserts a TAB, but doesn't indent. Please check key-settings. — Andreas Röhler, Jan 13 '16 at 07:23
This is not about indentation (adding leading whitespace), but alignment (lining up identifiers into the same column). — wasamasa, Jan 24 '16 at 15:02

score 1 · Answer 1 · answered Jan 24 '16 at 15:39

1

Emacs provides commands for lining up text, including the very powerful align-regexp. Select the text to align, then execute C-u M-x align-regexp with ^[[:upper:]]+\(\s-+\) for the regex and use 1 for both group and spacing. It doesn't matter what you answer with regards to repetition.

This can be easily turned into a command:

(defun my-align-sql-statement ()
  (interactive)
  (align-regexp (region-beginning) (region-end)
                "^[[:upper:]]+\\(\\s-+\\)" 1 nil))

answered Jan 24 '16 at 15:39

wasamasa

21,803
1
65
97

Thanks for the explaination and the code snippet, but I still don't get the desired alignment. See the update, when the keywords are a little bit moved to right? – ReneFroger Mar 06 '16 at 00:10
It's no wonder you don't if you keep changing the requirements... – wasamasa Mar 06 '16 at 08:21
Perhaps I was yet unclear on this then. – ReneFroger Mar 06 '16 at 15:26

score 1 · Answer 2 · edited Apr 13 '17 at 12:52

I've got kind of deja vu from How align tabs inside block of string?. ;-)

Almost everything you need is already mentioned there. The only thing left to say is that you can combine indentation and alignment by setting indent-line-function to a function that does both. Just press tab for indentation and you get the current line not only indented but also the current section/group aligned.

I am using sql-indent-line from the package sql-indent again. So the AND is indented relative to WHERE.

(require 'align)
(require 'sql-indent)

(defun ReneFroger-indent-relative-and-align ()
  "Like `sql-indent-line' but also calls `align'."
  (prog1 ;; variable `indent-line-function' wants `noindent' when indentation is not possible
      (sql-indent-line)
      (align nil nil)))

(defcustom align-sql-indent-rules-list
  '((space (regexp . "^\\s-*[[:alnum:]]+\\(\\s-+\\)[^[:space:]]")) ;; alignment of the first second word on line
    (equal-sign (regexp . "\\(\\s-*\\)=") (separate . group))) ;; alignment of assignment groups
  "Alignment rules for `sql-mode'."
  :type align-rules-list-type
  :group 'sql-indent)

(defun ReneFroger-sql-mode-hook-function ()
  "All your add-ons to `sql-mode'."
  (setq indent-tabs-mode nil) ;; Do as you like. When set to t the distance of = is rather large (depending on `tab-width' or `tab-to-tab-stop').
  (setq align-mode-rules-list align-sql-indent-rules-list) ;; HERE we say how to align in `sql-mode'
  (setq indent-line-function #'ReneFroger-indent-relative-and-align))

(add-hook 'sql-mode-hook #'ReneFroger-sql-mode-hook-function)

Ah yes, before you ask: You can also add (sqlup-mode 1) to ReneFroger-sql-mode-hook-function to get SQL keywords capitalized.

Note, that you can also add the alignment rules to align-rules-list instead of align-mode-rules-list. The difference is that align-rules-list is global while align-mode-rules-list is buffer local (set when the major mode is activated).

In the case that you use align-rules-list you should add the modes attribute to the alignment rule, e.g., (modes sql-mode).

I think adding the alignment rules to align-rules-list is the official way. But, it makes the customization a bit more cumbersome.

Hi Tobias, thanks for your reply. Yes, your function worked. But I have also noticed that the `AND` keyword is aligned to left instead right below the `WHERE` keyword which I prefer. Since your solution in the previous issue question works, this question was more targeting on how the aligning of the `AND` keyword works and how to modify it. — ReneFroger, Jan 25 '16 at 21:24
@ReneFroger You can try to remove `(require 'sql-indent)` and replace `(sql-indent-line)` by `(let ((indent-line-function 'indent-relative)) (indent-according-to-mode))`. I thought the logical indentation of `sql-indent` would be good. But, maybe, it isn't what you want for some reason... — Tobias, Mar 08 '16 at 09:39

How to change the indentation of major mode (SQL-mode)?

2 Answers2