최근 편집
요약: add my Emacs Frontend
변경됨:
< (while (search-forward "
< " nil t) ;;delete ^m
< (replace-match "" nil nil))
(으)로
> (while (search-forward "\^M" nil t) ;;delete ^m
> (replace-match "" nil nil))
This page provides a way to format SQL in Emacs.
SqlBeautify uses blancoSqlFormatter as a backend.
First, download blancoSqlFormatter from http://sourceforge.jp/projects/blancofw/files/?release_id=27764#27764 and install it as following:
% mkdir ~/opt % unzip balcoSqlFormatter-0.1.0.src.zip -d ~/opt
Second, write Java client program.
~/opt/blancoSqlFormatter/SqlBeautify.java:
import java.io.InputStream;
import blanco.commons.sql.format.BlancoSqlFormatter;
import blanco.commons.sql.format.BlancoSqlRule;
public class SqlBeautify {
public static void main(String[] args) throws Exception {
InputStream is = System.in;
StringBuilder sb = new StringBuilder();
byte[] buffer = new byte[4096];
int read;
while ((read = is.read(buffer)) != -1) {
sb.append(new String(buffer, 0, read));
}
System.out.println(new BlancoSqlFormatter(new BlancoSqlRule()).format(sb.toString()));
}
}
And then compile it:
% cd ~/opt/blancoSqlFormatter % CLASSPATH=$CLASSPATH:blancosqlformatter-0.1.0.jar javac SqlBeautify.java
Now, we can use it like:
% echo "SELECT name,value FROM v\$sysstat WHERE name IN ('db block gets','consistent gets', 'physical reads');" | java SqlBeautify
SELECT
name
,VALUE
FROM
v$sysstat
WHERE
name IN (
'db block gets'
,'consistent gets'
,'physical reads'
)
;
Finally, we should write a shell script wrapper.
~/bin/sqlbeautify:
#!/bin/sh BSF_HOME=$HOME/opt/blancoSqlFormatter BSF_LIB=$BSF_HOME/blancosqlformatter-0.1.0.jar env CLASSPATH=$CLASSPATH:$BSF_HOME:$BSF_LIB java SqlBeautify
% chmod +x ~/bin/sqlbeautify
% echo 'select * from dual;' | sqlbeautify
SELECT
*
FROM
dual
;
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)))
(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.
(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.