5

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?

2
+50

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 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 5: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
  • 1
    @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

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.