Re:Emacs でインタラクティブに SQL する

• vallog: Emacs でインタラクティブに SQL する （Microsoft SQL Server 2005 編）

書き換えたので再掲。

• sqlcmd を使ってるつもりが、osql だったので sqlcmd に
• ちゃんと追求してないけど、2度目以降の run-mssql で sql-ms-options がおかしくなるので場当たり的な対応 reset/run-mssql 追加
• sql-indent.el のインデントのための正規表現に join 系他を追加
• hook を追加

あとは、改行する時に自動でインデントしてくれると嬉しいんだけどなぁ。。
それと、なぜか sql-indent-buffer, sql-indent-region するとインデントがなくなる・・・。

;; C-c C-c : 'sql-send-paragraph
;; C-c C-r : 'sql-send-region
;; C-c C-s : 'sql-send-string
;; C-c C-b : 'sql-send-buffer
(require 'sql)

;; starting SQL mode loading sql-indent / sql-complete
(eval-after-load "sql"
  '(progn
     (load-library "sql-indent")))

(custom-set-variables
 `(sql-indent-first-column-regexp
   ,(concat "^\\s-*" (regexp-opt
                      '("select" "update" "insert" "delete"
                        "union" "intersect"
                        "from" "where" "into" "group" "having" "order by"
                        "set"
                        "create" "drop" "truncate"
                        "left join" "right join" "inner join"  "cross join" "on"
                        "left outer join" "right outer join" "inner join"
                        "begin" "rollback" "commit"
                        "and" "or"
                        "go"
                        "--")
                      t)
            "\\(\\b\\|\\s-\\)")))

(setq auto-mode-alist
      (cons '("\\.sql$" . sql-mode) auto-mode-alist))
(sql-set-product-feature
 'ms :font-lock 'sql-mode-ms-font-lock-keywords)

(custom-set-variables
 '(sql-ms-program "sqlcmd"))
(custom-set-variables
 '(sql-ms-options  '("-U" "-P" "-S" "-d")))

(sql-set-product-feature
 'ms :sql-program 'sql-ms-program)
(sql-set-product-feature
 'ms :sqli-prompt-regexp "^[0-9]*>")
(sql-set-product-feature
 'ms :sqli-prompt-length 5)
(sql-set-product-feature
 'ms :sqli-login 'sql-ms-login-params)
(sql-set-product-feature
 'ms :sqli-connect 'sql-connect-ms)

(add-hook 'sql-mode-hook
          #'(lambda ()
              (setq sql-indent-offset 2)
              (setq sql-indent-maybe-tab t)
              (local-set-key "\C-cu" 'sql-to-update)
              (setq sql-pop-to-buffer-after-send-region nil)))
(add-hook 'sql-interactive-mode-hook
          #'(lambda ()
              (set-buffer-process-coding-system 'sjis-unix 'sjis-unix )
              (setq show-trailing-whitespace nil)
              (setq comint-buffer-maximum-size 500)
              (setq comint-input-autoexpand t)
              (setq comint-output-filter-functions
                    'comint-truncate-buffer)))

(defun sql-connect-ms ()
  (let ((login-params (mapcar #'(lambda (e)
                                  (symbol-value (symb 'sql- e)))
                              sql-ms-login-params)))
    (let* ((options (mapcar* #'cons
                             sql-ms-options
                             login-params))
           (params (fold-right
                    #'(lambda (e acc)
                        (if (cdr e)
                            (append (list (car e)(cdr e))
                                    acc)
                          acc))
                    nil options)))
      ;; (setq sql-ms-options (append params (list "-W")))
      (setq sql-ms-options params)
      (sql-connect-1 sql-ms-program sql-ms-options))))

(defun run-mssql ()
  (interactive)
  (sql-product-interactive 'ms))

(defvar backup:sql-ms-options sql-ms-options)
(defvar backup:sql-ms-login-params sql-ms-login-params)

(defun sql:reset-parameters ()
  (interactive)
  (setq sql-ms-options backup:sql-ms-options)
  (setq sql-ms-login-params backup:sql-ms-login-params)
  (setq sql-user nil)
  (setq sql-password nil)
  (setq sql-server nil)
  (setq sql-database))

(defun reset/run-mssql ()
  (interactive)
  (sql:reset-parameters)
  (run-mssql))

他に必要なもの

• Emacs Lisp

(defun fold (kons knil ls &rest more)
  (do ((ls ls (cdr ls)) (more more (mapcar #'cdr more))
       (knil knil (apply
                   kons
                   (apply #'list (car ls) (append (mapcar #'car more) (list knil))))))
      ((null ls) knil)))

(defun fold-right (kons knil ls &rest more)
  (apply #'fold kons knil (reverse ls) (mapcar #'reverse more)))

• LoLのdefmacro!をelispに移植 - podhmoの日記

(defun mkstr (&rest args)
  (with-output-to-string
    (dolist (a args) (princ a))))

参考

• "Trumps by the roadside" - sql-mode: Emacs から SQL 文を実行する
• sql