How to use SQL Query in emacs org-mode

- How to use SQL Query in emacs org-mode
  (org-mode 에서 Postgres SQL 사용)

* Install Postgres
   - omitted


* set up for SQL-mode
   - not always necessary

;;   (load (concat emacs-lib-path "dot/init-sql.el")) ; postgres v.1

#+begin_src emacs-lisp
;;
;; usage:  M-x sql-connect --> "if fail" --> retry again!
;; M-x sql-postgres
;; sql-send-region : C-c C-r
;; sql-send-buffer : C-c C-b
;;
;-- 1. Open sql file  ( chagne buffer sql-mode )
;-- 2. M-x sql-connect --> emacsdb
;-- input buffer *SQL-postgres* or *SQL*
;-- C-c C-r : send region
;-- C-c C-b : send buffer
;-- output is --> *SQL* buffer

;-- DB:emacsdb / Table:headers
;select title, sender, receiver, datetime_txt, size, sent_received from headers where datetime_txt like '%12-10%';
;select title, sender, receiver, datetime_txt, size, sent_received from headers where title like '%체크%';

;-- DB:emacsdb / Table:phonebook
;SELECT d_project, string_agg(fullname, ', '), string_agg(email, ',') AS title_list FROM phonebook where d_project like '%cctv%' GROUP  BY 1 ;
;SELECT d_project, string_agg(fullname, ', ') AS title_list FROM phonebook GROUP  BY 1 ;
;SELECT d_project, string_agg(email, ', ') AS title_list FROM phonebook GROUP  BY 1 ;
;SELECT d_project, string_agg(email, ', ') AS email_list FROM phonebook where d_project like '%drone%' GROUP  BY 1 ;
;SELECT fullname, email FROM phonebook where d_attend='y' order by fullname;
;select column_name, data_type, character_maximum_length  from INFORMATION_SCHEMA.COLUMNS where table_name = 'phonebook';
;-- quit  --> \q

(setq sql-postgres-login-params
      '((user :default "kys")
        (database :default "emacsdb")
        (server :default "localhost")
        (port :default 5432)))

;; call toggle-truncate-lines in sql-interactive-mode-hook.
(add-hook 'sql-interactive-mode-hook
          (lambda ()
            (toggle-truncate-lines t)))


(setq sql-connection-alist
 '((emacsdb (sql-product 'postgres)
           (sql-port 5432)
           (sql-server "localhost")
           (sql-user "kys")
           (sql-database "emacsdb")
   )
   (kysdb2 (sql-product 'postgres)
           (sql-port 5432)
           (sql-server "localhost")
           (sql-user "kys")
           (sql-database "kysdb")
   )
 )
)

(defun my-sql-server1 ()
  (interactive)
  (my-sql-connect 'postgres 'server1))

(defun my-sql-server2 ()
  (interactive)
  (my-sql-connect 'postgres 'server2))

(defun my-sql-connect (product connection)
  ;; remember to set the sql-product, otherwise, it will fail for the first time
  ;; you call the function
  (setq sql-product product)
  (sql-connect connection))

(defun my-sql-connect (product connection)
  ;; load the password
  (require my-password "my-pass")

  ;; update the password to the sql-connection-alist
  (let ((connection-info (assoc connection sql-connection-alist))
        (sql-password (car (last (assoc connection my-sql-password)))))
    (delete sql-password connection-info)
    (nconc connection-info `((sql-password ,sql-password)))
    (setq sql-connection-alist (assq-delete-all connection sql-connection-alist))
    (add-to-list 'sql-connection-alist connection-info))

  ;; connect to database
  (setq sql-product product)
  (sql-connect connection))
#+end_src



* enable sql mode with org-mode initial file

- init-org.el
#+begin_src emacs-lisp
(org-babel-do-load-languages
 'org-babel-load-languages
  '(
     (css . t)
;    (dot . t)
     (emacs-lisp . t)
     (js . t)
     (gnuplot . t)
;    (latex . t)
     (ledger . t)
;    (makefile . t)
     (sh . t)
     (python . t)
     (ruby . t)
     (R . t)
     (sh . t)
     (sql . t)  ;; <-- this is for SQL-MODE
;    (sqlite . t)
   ))
#+end_src



* org file - ex. sample.org


** Method-1. All of sql will be setted by the header Properties
   단락 헤더 아래에 모든 쿼리가 Property 에 속성에 대입되어 처리 된다.

 * Postgres
  :PROPERTIES:
  :LANGUAGE: sql
  :engine: postgresql
  :exports: result
  :cmdline: -p 5432 -h localhost -U kys -d emacsdb PGPASSWORD=my-pass
  :END:

#+END_SRC sql
SELECT d_project, string_agg(email, ', ') AS email_list FROM phonebook where d_project like '%drone%' GROUP  BY 1 ;
#+END_SRC


** Method-2. Property is will be setted by per src
 개발 src 속성으로 처리 된다.
#+BEGIN_SRC sql :engine postgresql :exports results :cmdline -p 5432 -h localhost -U kys -d emacsdb PGPASSWORD=my-pass
select fullname, email from phonebook limit 3;
#+END_SRC

#+RESULTS:
| fullname | email             |
|----------+-------------------|
| 임--K    | bin--@--lus.co.kr |
| 방--D    | yhb--@--us.co.kr  |
| 엄--S    | syo--@--\.com     |

#+BEGIN_SRC sql :engine postgresql :exports results :cmdline -p 5432 -h otherhost.com -U kys -d emacsdb PGPASSWORD=my-pass
select fullname, email from phonebook limit 3;
#+END_SRC

#+RESULTS:
| fullname | email             |
|----------+-------------------|
| 임--K    | bin--@--lus.co.kr |
| 방--D    | yhb--@--us.co.kr  |
| 엄--S    | syo--@--\.com     |


* UTF-8
   International Language support


** MS-Windows - UTF-8 with Msys+MinGW

- Hangul with org-mode babel
http://www.iac.es/sieinvens/siepedia/pmwiki.php?n=SIEminars.EnvironmentVariables


** ruby check
- plan text
#+begin_src ruby :exports both :results output
puts "Howdy!"
#+end_src

#+RESULTS:
: Howdy!

- hangul
#+begin_src ruby :exports: both :results output
puts "Howdy!"
puts "한글로처리해라"
#+end_src


evalute
(setenv "LANG" "ko_KR.UTF-8")
(setenv "LC_ALL" "ko_KR.UTF-8")
or
(setenv "LANG"   "C.UTF-8")
(setenv "LC_ALL" "C.UTF-8")

#+BEGIN_SRC ruby :exports both :results output
  puts RUBY_VERSION
  puts __ENCODING__
#+END_SRC


-- shell test
(setenv "LANG" "cp949")
(setenv "LC_ALL" "cp949")

#+BEGIN_SRC ruby :exports both :results output
  puts RUBY_VERSION
  puts __ENCODING__
#+END_SRC

#+RESULTS:
: 1.9.3
: UTF-8

(setenv "LANG" "EUC-KR")
(setenv "LC_ALL" "EUC-KR")

#+BEGIN_SRC ruby :exports both :results output
  puts RUBY_VERSION
  puts __ENCODING__
#+END_SRC

#+name:
: 1.9.3
: UTF-8

- shell test

#+begin_src sh :exports both :results output
printenv |grep LANG
#+end_src

#+RESULTS:
: NLS_LANG=KOREAN_KOREA.KO16MSWIN949
: LANG=ko_KR.UTF-8

> check filename
#+begin_src sh :exports both :results output
ls ~/download/*.txt
#+end_src
#+RESULTS:
: /d/home/download/set_var.txt
: /d/home/download/test.txt


- .cshrc
#+begin_src
setenv LANG C.UTF-8
setenv LC_ALL C.UTF-8
setenv LC_CTYPE C.UTF-8
setenv LC_NUMERIC C.UTF-8
setenv LC_TIME C.UTF-8
setenv LC_COLLATE C.UTF-8
setenv LC_MONETARY C.UTF-8
setenv LC_MESSAGES C.UTF-8
#+end_src

- .emacs_bash
#+begin_src
export TERM=msys
# gettext
export OUTPUT_CHARSET=UTF-8
export TZ=KST+9
#export LANG=ko_KR.UTF-8
#export LANG=C.UTF-8
export LC_ALL='C'
export TPUT_COLORS=256
export HOSTNAME=msys-bash

#export PATH=~/.packer:$PATH:/c/Program\ Files\ \(x86\)/Git/bin
alias open="explorer"
export MSYSTEM=MSYS
export PATH=.:/usr/local/bin:/bin:/mingw/bin:$PATH
#+end_src


.bashrc
#+begin_src
# .bashrc
export SHELL='d:/Pkg/MinGW/msys/1.0/bin/bash.exe'
export LANG="C.UTF-8"
export LC_CTYPE="C.UTF-8"
export LC_NUMERIC="C.UTF-8"
export LC_TIME="C.UTF-8"
export LC_COLLATE="C.UTF-8"
export LC_MONETARY="C.UTF-8"
export LC_MESSAGES="C.UTF-8"
export LC_ALL="C.UTF-8"
#+end_src