Commit | Line | Data |
---|---|---|
86fbb8ca CD |
1 | ;;; ob-sql.el --- org-babel functions for sql evaluation |
2 | ||
ba318903 | 3 | ;; Copyright (C) 2009-2014 Free Software Foundation, Inc. |
86fbb8ca CD |
4 | |
5 | ;; Author: Eric Schulte | |
6 | ;; Keywords: literate programming, reproducible research | |
7 | ;; Homepage: http://orgmode.org | |
86fbb8ca CD |
8 | |
9 | ;; This file is part of GNU Emacs. | |
10 | ||
11 | ;; GNU Emacs is free software: you can redistribute it and/or modify | |
12 | ;; it under the terms of the GNU General Public License as published by | |
13 | ;; the Free Software Foundation, either version 3 of the License, or | |
14 | ;; (at your option) any later version. | |
15 | ||
16 | ;; GNU Emacs is distributed in the hope that it will be useful, | |
17 | ;; but WITHOUT ANY WARRANTY; without even the implied warranty of | |
18 | ;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | |
19 | ;; GNU General Public License for more details. | |
20 | ||
21 | ;; You should have received a copy of the GNU General Public License | |
22 | ;; along with GNU Emacs. If not, see <http://www.gnu.org/licenses/>. | |
23 | ||
24 | ;;; Commentary: | |
25 | ||
26 | ;; Org-Babel support for evaluating sql source code. | |
153ae947 | 27 | ;; (see also ob-sqlite.el) |
86fbb8ca CD |
28 | ;; |
29 | ;; SQL is somewhat unique in that there are many different engines for | |
30 | ;; the evaluation of sql (Mysql, PostgreSQL, etc...), so much of this | |
31 | ;; file will have to be implemented engine by engine. | |
32 | ;; | |
33 | ;; Also SQL evaluation generally takes place inside of a database. | |
34 | ;; | |
271672fa BG |
35 | ;; Header args used: |
36 | ;; - engine | |
37 | ;; - cmdline | |
38 | ;; - dbhost | |
39 | ;; - dbuser | |
40 | ;; - dbpassword | |
41 | ;; - database | |
42 | ;; - colnames (default, nil, means "yes") | |
43 | ;; - result-params | |
44 | ;; - out-file | |
45 | ;; The following are used but not really implemented for SQL: | |
46 | ;; - colname-names | |
47 | ;; - rownames | |
48 | ;; - rowname-names | |
86fbb8ca CD |
49 | ;; |
50 | ;; TODO: | |
51 | ;; | |
52 | ;; - support for sessions | |
86fbb8ca CD |
53 | ;; - support for more engines (currently only supports mysql) |
54 | ;; - what's a reasonable way to drop table data into SQL? | |
14e1337f | 55 | ;; |
86fbb8ca CD |
56 | |
57 | ;;; Code: | |
58 | (require 'ob) | |
59 | (eval-when-compile (require 'cl)) | |
60 | ||
61 | (declare-function org-table-import "org-table" (file arg)) | |
bdebdb64 BG |
62 | (declare-function orgtbl-to-csv "org-table" (table params)) |
63 | (declare-function org-table-to-lisp "org-table" (&optional txt)) | |
86fbb8ca CD |
64 | |
65 | (defvar org-babel-default-header-args:sql '()) | |
66 | ||
271672fa BG |
67 | (defconst org-babel-header-args:sql |
68 | '((engine . :any) | |
69 | (out-file . :any) | |
70 | (dbhost . :any) | |
71 | (dbuser . :any) | |
72 | (dbpassword . :any) | |
73 | (database . :any)) | |
74 | "SQL-specific header arguments.") | |
e66ba1df | 75 | |
acedf35c CD |
76 | (defun org-babel-expand-body:sql (body params) |
77 | "Expand BODY according to the values of PARAMS." | |
78 | (org-babel-sql-expand-vars | |
79 | body (mapcar #'cdr (org-babel-get-header params :var)))) | |
80 | ||
271672fa BG |
81 | (defun dbstring-mysql (host user password database) |
82 | "Make MySQL cmd line args for database connection. Pass nil to omit that arg." | |
83 | (combine-and-quote-strings | |
84 | (remq nil | |
85 | (list (when host (concat "-h" host)) | |
86 | (when user (concat "-u" user)) | |
87 | (when password (concat "-p" password)) | |
88 | (when database (concat "-D" database)))))) | |
89 | ||
86fbb8ca CD |
90 | (defun org-babel-execute:sql (body params) |
91 | "Execute a block of Sql code with Babel. | |
92 | This function is called by `org-babel-execute-src-block'." | |
afe98dfa | 93 | (let* ((result-params (cdr (assoc :result-params params))) |
86fbb8ca | 94 | (cmdline (cdr (assoc :cmdline params))) |
271672fa BG |
95 | (dbhost (cdr (assoc :dbhost params))) |
96 | (dbuser (cdr (assoc :dbuser params))) | |
97 | (dbpassword (cdr (assoc :dbpassword params))) | |
98 | (database (cdr (assoc :database params))) | |
86fbb8ca | 99 | (engine (cdr (assoc :engine params))) |
271672fa | 100 | (colnames-p (not (equal "no" (cdr (assoc :colnames params))))) |
afe98dfa | 101 | (in-file (org-babel-temp-file "sql-in-")) |
86fbb8ca | 102 | (out-file (or (cdr (assoc :out-file params)) |
afe98dfa | 103 | (org-babel-temp-file "sql-out-"))) |
3ab2c837 | 104 | (header-delim "") |
86fbb8ca | 105 | (command (case (intern engine) |
271672fa | 106 | ('dbi (format "dbish --batch %s < %s | sed '%s' > %s" |
8223b1d2 BG |
107 | (or cmdline "") |
108 | (org-babel-process-file-name in-file) | |
271672fa | 109 | "/^+/d;s/^\|//;s/(NULL)/ /g;$d" |
8223b1d2 BG |
110 | (org-babel-process-file-name out-file))) |
111 | ('monetdb (format "mclient -f tab %s < %s > %s" | |
112 | (or cmdline "") | |
113 | (org-babel-process-file-name in-file) | |
114 | (org-babel-process-file-name out-file))) | |
3ab2c837 BG |
115 | ('msosql (format "osql %s -s \"\t\" -i %s -o %s" |
116 | (or cmdline "") | |
117 | (org-babel-process-file-name in-file) | |
118 | (org-babel-process-file-name out-file))) | |
271672fa BG |
119 | ('mysql (format "mysql %s %s %s < %s > %s" |
120 | (dbstring-mysql dbhost dbuser dbpassword database) | |
121 | (if colnames-p "" "-N") | |
afe98dfa | 122 | (or cmdline "") |
3ab2c837 BG |
123 | (org-babel-process-file-name in-file) |
124 | (org-babel-process-file-name out-file))) | |
125 | ('postgresql (format | |
126 | "psql -A -P footer=off -F \"\t\" -f %s -o %s %s" | |
8223b1d2 BG |
127 | (org-babel-process-file-name in-file) |
128 | (org-babel-process-file-name out-file) | |
129 | (or cmdline ""))) | |
130 | (t (error "No support for the %s SQL engine" engine))))) | |
86fbb8ca | 131 | (with-temp-file in-file |
8223b1d2 BG |
132 | (insert |
133 | (case (intern engine) | |
134 | ('dbi "/format partbox\n") | |
135 | (t "")) | |
136 | (org-babel-expand-body:sql body params))) | |
86fbb8ca | 137 | (message command) |
271672fa BG |
138 | (org-babel-eval command "") |
139 | (org-babel-result-cond result-params | |
140 | (with-temp-buffer | |
e66ba1df | 141 | (progn (insert-file-contents-literally out-file) (buffer-string))) |
3ab2c837 | 142 | (with-temp-buffer |
271672fa BG |
143 | (cond |
144 | ((or (eq (intern engine) 'mysql) | |
145 | (eq (intern engine) 'dbi) | |
146 | (eq (intern engine) 'postgresql)) | |
147 | ;; Add header row delimiter after column-names header in first line | |
148 | (cond | |
149 | (colnames-p | |
150 | (with-temp-buffer | |
151 | (insert-file-contents out-file) | |
152 | (goto-char (point-min)) | |
153 | (forward-line 1) | |
154 | (insert "-\n") | |
155 | (setq header-delim "-") | |
156 | (write-file out-file))))) | |
157 | (t | |
158 | ;; Need to figure out the delimiter for the header row | |
159 | (with-temp-buffer | |
160 | (insert-file-contents out-file) | |
161 | (goto-char (point-min)) | |
162 | (when (re-search-forward "^\\(-+\\)[^-]" nil t) | |
163 | (setq header-delim (match-string-no-properties 1))) | |
164 | (goto-char (point-max)) | |
165 | (forward-char -1) | |
166 | (while (looking-at "\n") | |
167 | (delete-char 1) | |
168 | (goto-char (point-max)) | |
169 | (forward-char -1)) | |
170 | (write-file out-file)))) | |
e66ba1df BG |
171 | (org-table-import out-file '(16)) |
172 | (org-babel-reassemble-table | |
173 | (mapcar (lambda (x) | |
174 | (if (string= (car x) header-delim) | |
175 | 'hline | |
176 | x)) | |
177 | (org-table-to-lisp)) | |
178 | (org-babel-pick-name (cdr (assoc :colname-names params)) | |
179 | (cdr (assoc :colnames params))) | |
180 | (org-babel-pick-name (cdr (assoc :rowname-names params)) | |
181 | (cdr (assoc :rownames params)))))))) | |
86fbb8ca | 182 | |
acedf35c CD |
183 | (defun org-babel-sql-expand-vars (body vars) |
184 | "Expand the variables held in VARS in BODY." | |
185 | (mapc | |
186 | (lambda (pair) | |
187 | (setq body | |
188 | (replace-regexp-in-string | |
666ffc7e SM |
189 | (format "\$%s" (car pair)) ;FIXME: "\$" == "$"! |
190 | (let ((val (cdr pair))) | |
191 | (if (listp val) | |
192 | (let ((data-file (org-babel-temp-file "sql-data-"))) | |
193 | (with-temp-file data-file | |
194 | (insert (orgtbl-to-csv | |
195 | val '(:fmt (lambda (el) (if (stringp el) | |
196 | el | |
197 | (format "%S" el))))))) | |
198 | data-file) | |
199 | (if (stringp val) val (format "%S" val)))) | |
acedf35c CD |
200 | body))) |
201 | vars) | |
202 | body) | |
86fbb8ca CD |
203 | |
204 | (defun org-babel-prep-session:sql (session params) | |
205 | "Raise an error because Sql sessions aren't implemented." | |
8223b1d2 | 206 | (error "SQL sessions not yet implemented")) |
86fbb8ca CD |
207 | |
208 | (provide 'ob-sql) | |
209 | ||
5b409b39 | 210 | |
86fbb8ca CD |
211 | |
212 | ;;; ob-sql.el ends here |