대문 최근에 바뀐 글 새소식 찾기 하우투 문제 제안

SqlBeautify

최근 편집

요약: 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.

Backend

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
;

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)))

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

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