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?

Answer 1

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.