4

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?

Improve this question
7
  • 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
    Commented Jan 12, 2016 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
    Commented Jan 13, 2016 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
    Commented Jan 13, 2016 at 7:23
  • @phils I use Emacs 25.0.50 if that makes any difference. My sql-mode doesn't do this.
    – ReneFroger
    Commented Jan 13, 2016 at 14:02
  • 1
    This is not about indentation (adding leading whitespace), but alignment (lining up identifiers into the same column).
    – wasamasa
    Commented Jan 24, 2016 at 15:02

2 Answers 2

Reset to default
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))
Improve this answer
3
  • 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
    Commented Mar 6, 2016 at 0:10
  • It's no wonder you don't if you keep changing the requirements...
    – wasamasa
    Commented Mar 6, 2016 at 8:21
  • Perhaps I was yet unclear on this then.
    – ReneFroger
    Commented Mar 6, 2016 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.

Improve this answer
3
  • 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
    Commented Jan 25, 2016 at 21:24
  • Perhaps any update on this?
    – ReneFroger
    Commented Mar 6, 2016 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
    Commented Mar 8, 2016 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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