How align tabs inside block of string?

Question

For example, I have the following code snippet:

$query = '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 align it as:

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

This is a very common operation, that I would like to devise a function or align regelar expression for it. I spent too much time to align it every time

The M-x align-regexp RET didn't help me out here. When I make a rectular selection block around the SQL query, and align it on tabs, it doesn't align.

Anyone have better suggestion to let Emacs align this as I mean?

Related: http://emacs.stackexchange.com/questions/13214/automatically-formatting-sql-code and the accepted answer from http://stackoverflow.com/questions/24397274/writing-php-with-sql-queries-in-emacs/24659949#24659949 seems to be the best answer so far. The corresponding question looks very similar to your problem. — Tobias, Dec 25 '15 at 21:25
@Tobias Thanks for linking that, the answer of Jordon Biondo helped a little bit, but not enough to remove the spaces and aligning. But it's surely helpful! — ReneFroger, Dec 25 '15 at 21:53

score 3 · Accepted Answer · edited May 23 '17 at 12:38

The following is the solution from Jordon Biondo at "Writing PHP with SQL queries in emacs" supplemented by alignment of the first spaces and equal signs. You can further adapt the alignment rules in align to your liking (see the description of the variable align-rules-list).

The packages expand-region and sql-indent from MELPA are required. (That is already mentioned at the above link.)

This solution does not exactly follow your specification but it brings some order into the code. Your comments look like you would be satisfied with such a solution.

For aligning an sql-string put the point into the string and call sql-indent-string. There is a nice video at the above link that demonstrates the usage.

(require 'expand-region)
(require 'sql-indent)
(require 'sqlup-mode)
(require 'cl-lib)
(require 'align)

(defcustom sql-indent-string-upcase t
  "Upcase sql keywords when running `sql-indent-string'."
  :type 'boolean
  :group 'sql-indent)

(defcustom align-sql-indent-rules-list
  '((space (regexp . "^\\s-*[[:alnum:]]+\\(\\s-+\\)[^[:space:]]"))
    (equal-sign (regexp . "\\(\\s-*\\)=") (separate . group)))
  "Alignment rules for `sql-indent-string'."
  :type align-rules-list-type
  :group 'sql-indent)

(defun sql-indent-string ()
  "Indents the string under the cursor as SQL."
  (interactive)
  (save-excursion
    (er/mark-inside-quotes)
    (let* ((-tabs (list indent-tabs-mode tab-width tab-stop-list))
           (text (buffer-substring-no-properties (region-beginning) (region-end)))
           (pos (region-beginning))
           (column (progn (goto-char pos) (current-column)))
           (start-indent (make-string column ?\ ))
           (formatted-text (with-temp-buffer
                             (cl-multiple-value-bind
                                 (indent-tabs-mode  tab-width  tab-stop-list) -tabs
                               (insert text)
                               (when sql-indent-string-upcase
                                 (sqlup-capitalize-keywords-in-region (point-min) (point-max)))
                               (delete-trailing-whitespace)
                               (goto-char (point-min))
                               (sql-indent-buffer)
                               (align (point-min) (point-max) nil align-sql-indent-rules-list)
                               (goto-char (point-min))
                               (while (progn (forward-line) (null (eobp))) (insert start-indent))
                               (buffer-string)))))
      (delete-region (region-beginning) (region-end))
      (goto-char pos)
      (insert formatted-text))))

UPDATE 1: I have added sqlup-capitalize-keywords-in-region as response to an additional requirement of Rene stated in the comments below. It seems that there is actually no problem with adding this function. (sqlup-mode version: 0.5.3)

UPDATE 2:

Made the alignment customizable.
Group-wise alignment of equal signs.
avoid replace-string since this is intended for interactive use only

NOTE: Once more for clarification: The original code is from Jordon Biondo. I have just added the alignment stuff that was wanted here.

Two (maybe, somewhat extreme) examples how formatted sql strings look like:

$query = 'SELECT  SUM(T01.foobar)
          FROM    db_foo.bar T01
          WHERE   T01.fooIsomeD = 1
              AND T01.barI      = 2
              AND T01.foo IS NOT NULL'

$query = 'SELECT  SUM(T01.foobar)
          FROM    db_foo.bar T01 AND fill IS nil
          WHERE   T01.fooID           = 1
              AND T01.barIDuhfsdhufue = 2
              AND T01.foo IS NOT NULL AND fill IS nil
              AND t01.bar1zdfjiifajjdijfuheuwihfufheuiwfheufu = 0
              AND t02.bar2                                    = 1'

Some facts can be recognized from this example:

There is some context-sensitive indentation through sql-indent (see the AND operator).
The second token on a line is aligned.
Keywords are up-cased.
Equal signs are aligned in groups.

It is not exactly what the original poster wanted but I hope it satisfies the requirements.

Tobias, thanks for your comment after my comment. However, I would like to add calling the function `sqlup-capitalize-keywords-in-region` from [SQLup](https://github.com/Trevoke/sqlup-mode.el) inside ` sql-indent-string` in order to capitalize the keywords. When I add `sqlup-capitalize-keywords-in-region` inside the function, and call it from the sql string, I got an empty result. Any tips? — ReneFroger, Dec 25 '15 at 23:54
@ReneFroger Seems there is no problem in adding `sqlup-capitalize-keywords-in-region`. See the update. — Tobias, Dec 26 '15 at 05:53
Do you know what? It's actually the answer of @Jordon-Biondo, instead an answer of you. So he would deserve the bounty actually. But propz for finding his solution and linking to it, so you should get the bounty also. Thanks for your support, it's appreciated! — ReneFroger, Dec 26 '15 at 14:54
@ReneFroger Maybe, you missed the modifications? If the answer of Jordon-Biondo did fully answer your question and if I had not changed anything I would **not** have posted it as an answer but only referred to the solution of Jordon-Biondo. I had no chance to post the modified code as comment here. Posting a new answer was just the simplest way to help. 1more: I did deactivate `indent-tabs-mode` to avoid some obscure spacing. Also transferring `tab-width` and `tab-stop-list` to the temporary buffer does not help. I am investigating this. But, my spare time is limited... — Tobias, Dec 26 '15 at 17:31
@ReneFroger UPDATE 2 adds preservation of tab-alignment and customization of alignment. Removal of `replace-string` serves clean byte code compilation. — Tobias, Dec 26 '15 at 20:11
you're totally right. I really appreciate all your help, and yes, I saw the modifications, thanks for that! — ReneFroger, Dec 26 '15 at 20:42
By the way, have you tested your code? I got the error `Symbol’s value as variable is void: align-rules-list-type` when I try to run your code. — ReneFroger, Dec 27 '15 at 12:11
@ReneFroger I have tested the code. It worked fine for me. It may be that the align library is already loaded in my setup but not in yours. Therefore, I've added `(require 'align)` to the code. Now, I have tested the code again with `emacs -Q` to make sure that all prerequisites are correct. It works for me. My `emacs-version` is 24.3.1. If we need to narrow the problem down I can also list the versions of all the other packages that are required above. In order for `sql-indent-string` to work correctly the string must be recognized by the major mode syntax. `php-mode` is fine in this regard. — Tobias, Dec 27 '15 at 13:10
the Emacs Stackexchange didn't notified me that I got a reply from you. When I added `(require 'align)` to the code, it worked like a charm! Tobias, your help for us was tremendous! Many, many thanks for that! — ReneFroger, Dec 31 '15 at 19:21

How align tabs inside block of string?

1 Answers1

Linked