Return to Revisions

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)

(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.