SqlBeautify

This page provides a way to format SQL in Emacs.

Backend

SqlBeautify is a shell utility which uses blancoSqlFormatter as a backend.

See SqlBeautify(https://github.com/ldeck/SqlBeautify) on github.

Note: It seems like the underlying ant script for the repo above is broken. An alternative is to install a ruby gem called anbt-sql-formatter. You can run the following command to install the cli tool:

gem install anbt-sql-formatter

SqlBeautify is quick and easy to install and use.

Example usage:

$ echo "select * from foo;" | bin/beautify-sql 
SELECT
        *
    FROM
        foo
;

Emacs Frontend

Write following definitions to .emacs:

(defun sql-beautify-region (beg end)
  "Beautify SQL in region between beg and END."
  (interactive "r")
  (save-excursion
    (shell-command-on-region beg end "sqlbeautify" nil t)))
    ;; change sqlbeautify to anbt-sql-formatter if you
    ;;ended up using the ruby gem

(defun sql-beautify-buffer ()
 "Beautify SQL in buffer."
 (interactive)
 (sql-beautify-region (point-min) (point-max)))

And you can format SQL with M-x sql-beautify-region or M-x sql-beautify-buffer.

Alternatively, provide another function to automatically beautify the entire buffer or the marked region, if one is active.

(defun sql-beautify-region-or-buffer ()
  "Beautify SQL for the entire buffer or the marked region between beg and end"
  (interactive)
  (if (use-region-p)
      (sql-beautify-region (region-beginning) (region-end))
    (sql-beautify-buffer)))

Then provide a hook to assign a key command when in sql-mode:

(add-hook 'sql-mode-hook '(lambda ()                                                                               
  ;; beautify region or buffer                                                           
  (local-set-key (kbd "C-M-]") 'sql-beautify-region-or-buffer))) 

Joseph 's Emacs Frontend

 (define-key sql-mode-map "\C-\M-\\" 'sql-beautify)
(define-key sql-interactive-mode-map "\C-\M-\\" 'sql-beautify)
(eval-after-load 'sqlplus
  '(progn (define-key sqlplus-mode-map  "\C-\M-\\" 'sql-beautify)))
(defun sql-beautify()
  "Beautify SQL. in region or current sql sentence."
  (interactive)
  (unless mark-active
    (let ((sql-bounds (bounds-of-sql-at-point) ))
      (set-mark (car  sql-bounds))
      (goto-char (cdr sql-bounds))))
  (sql-beautify-region (region-beginning) (region-end)))

(defun sql-beautify-region (beg end)
  "Beautify SQL in region between beg and END."
  ;;  (interactive "r")
  (if (equal system-type 'windows-nt)
      (setenv "CLASSPATH" (concat (getenv "CLASSPATH") ";" "d:\\.emacs.d\\script\\sqlbeautify\\blancosqlformatter-0.1.1.jar"))
    (setenv "CLASSPATH" (concat (getenv "CLASSPATH") ":" (getenv "HOME") "/.emacs.d/script/sqlbeautify/blancosqlformatter-0.1.1.jar")))
  (cd "~/.emacs.d/script/sqlbeautify/")
  (let ((beautified-sql))
    (shell-command-on-region beg end "java SqlBeautify" "*sqlbeautify*" nil)
    (with-current-buffer  "*sqlbeautify*"
      (goto-char (point-min))
(while (search-forward "\^M" nil t) ;;delete ^m
        (replace-match "" nil nil))  
      (setq beautified-sql (buffer-string)))
    (goto-char beg)
    (kill-region beg end)
    (insert beautified-sql)
    (kill-buffer"*sqlbeautify*")
    ))

(defun bounds-of-sql-at-point()
  "get start and end point of current sql."
  (let ((pt (point))begin end empty-line-p empty-line-p next-line-included tail-p)
    (when (and (looking-at "[ \t]*\\(\n\\|\\'\\)")
               (looking-back "[ \t]*;[ \t]*" (beginning-of-line)))
      (search-backward-regexp "[ \t]*;[ \t]*" (beginning-of-line) t))
    (save-excursion
      (skip-chars-forward " \t\n\r")
      ;;(end-of-line)
      (re-search-backward ";[ \t\n\r]*\\|\\`\\|\n[\r\t ]*\n[^ \t]" nil t)
      (skip-syntax-forward "-")
      (setq begin (match-end 0)))
    (save-excursion
      (skip-chars-forward " \t\n\r")
      (re-search-forward "\n[\r\t ]*\n[^ \t]\\|\\'\\|[ \t\n\r]*;" nil t)
      (unless (zerop (length (match-string 0)))
        (backward-char 1))
      (skip-syntax-backward "-")
      (setq end   (match-beginning 0)))
    (goto-char pt)
    (cons begin end)
    )
  )

you should change this code in (sql-beautify-region) .

 
 (if (equal system-type 'windows-nt)
        (setenv "CLASSPATH" (concat (getenv "CLASSPATH") ";" "d:\\.emacs.d\\script\\sqlbeautify\\blancosqlformatter-0.1.1.jar"))
      (setenv "CLASSPATH" (concat (getenv "CLASSPATH") ":" (getenv "HOME") "/.emacs.d/script/sqlbeautify/blancosqlformatter-0.1.1.jar")))
    (cd "~/.emacs.d/script/sqlbeautify/")
 I put  blancosqlformatter-0.1.1.jar and SqlBeautify.class  in d:\\.emacs.d\\script\\sqlbeautify\\

so you should change it depending on your path.


CategorySql