org-sql-blog

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

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

enable sql mode with org-mode initial file

init-org.el

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

org file - ex. sample.org

Method-1. All of sql will be setted by the header Properties

단락 헤더 아래에 모든 쿼리가 Property 에 속성에 대입되어 처리 된다.

Postgres

#+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 속성으로 처리 된다.

fullname	email
임–K	bin–@–lus.co.kr
방–D	yhb–@–us.co.kr
엄–S	syo–@–\.com

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

puts "Howdy!"

Howdy!

hangul

puts "Howdy!"
puts "한글로처리해라"

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

  puts RUBY_VERSION
  puts __ENCODING__

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

  puts RUBY_VERSION
  puts __ENCODING__

1.9.3
UTF-8

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

  puts RUBY_VERSION
  puts __ENCODING__

1.9.3
UTF-8

shell test

printenv |grep LANG

NLS_LANG=KOREAN_KOREA.KO16MSWIN949
LANG=ko_KR.UTF-8

> check filename

ls ~/download/*.txt

/d/home/download/set_var.txt
/d/home/download/test.txt

.cshrc

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

.emacs_bash

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

.bashrc

# .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"

04월24일(금)_00:05:56+UTC_Y2015

The Hitchhiker's note in the Galaxy

Thursday, April 23, 2015

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