2

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?

  • 1
    If I'm TABbing 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 3:54
  • 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 7:23
  • @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
  • 1
    This is not about indentation (adding leading whitespace), but alignment (lining up identifiers into the same column). – wasamasa Jan 24 '16 at 15:02
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))
  • 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 6 '16 at 0:10
  • It's no wonder you don't if you keep changing the requirements... – wasamasa Mar 6 '16 at 8:21
  • Perhaps I was yet unclear on this then. – ReneFroger Mar 6 '16 at 15:26
1

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
  • Perhaps any update on this? – ReneFroger Mar 6 '16 at 0:05
  • @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 8 '16 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.