Commit | Line | Data |
---|---|---|
86fbb8ca CD |
1 | ;;; ob-sql.el --- org-babel functions for sql evaluation |
2 | ||
cbd20947 | 3 | ;; Copyright (C) 2009-2011 Free Software Foundation, Inc. |
86fbb8ca CD |
4 | |
5 | ;; Author: Eric Schulte | |
6 | ;; Keywords: literate programming, reproducible research | |
7 | ;; Homepage: http://orgmode.org | |
3ab2c837 | 8 | ;; Version: 7.7 |
86fbb8ca CD |
9 | |
10 | ;; This file is part of GNU Emacs. | |
11 | ||
12 | ;; GNU Emacs is free software: you can redistribute it and/or modify | |
13 | ;; it under the terms of the GNU General Public License as published by | |
14 | ;; the Free Software Foundation, either version 3 of the License, or | |
15 | ;; (at your option) any later version. | |
16 | ||
17 | ;; GNU Emacs is distributed in the hope that it will be useful, | |
18 | ;; but WITHOUT ANY WARRANTY; without even the implied warranty of | |
19 | ;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | |
20 | ;; GNU General Public License for more details. | |
21 | ||
22 | ;; You should have received a copy of the GNU General Public License | |
23 | ;; along with GNU Emacs. If not, see <http://www.gnu.org/licenses/>. | |
24 | ||
25 | ;;; Commentary: | |
26 | ||
27 | ;; Org-Babel support for evaluating sql source code. | |
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 | ;; | |
c7015153 | 35 | ;; For now let's just allow a generic ':cmdline' header argument. |
86fbb8ca CD |
36 | ;; |
37 | ;; TODO: | |
38 | ;; | |
39 | ;; - support for sessions | |
40 | ;; - add more useful header arguments (user, passwd, database, etc...) | |
41 | ;; - support for more engines (currently only supports mysql) | |
42 | ;; - what's a reasonable way to drop table data into SQL? | |
c7015153 | 43 | ;; |
86fbb8ca CD |
44 | |
45 | ;;; Code: | |
46 | (require 'ob) | |
47 | (eval-when-compile (require 'cl)) | |
48 | ||
49 | (declare-function org-table-import "org-table" (file arg)) | |
acedf35c | 50 | (declare-function orgtbl-to-csv "org-table" (TABLE PARAMS)) |
86fbb8ca CD |
51 | |
52 | (defvar org-babel-default-header-args:sql '()) | |
53 | ||
acedf35c CD |
54 | (defun org-babel-expand-body:sql (body params) |
55 | "Expand BODY according to the values of PARAMS." | |
56 | (org-babel-sql-expand-vars | |
57 | body (mapcar #'cdr (org-babel-get-header params :var)))) | |
58 | ||
86fbb8ca CD |
59 | (defun org-babel-execute:sql (body params) |
60 | "Execute a block of Sql code with Babel. | |
61 | This function is called by `org-babel-execute-src-block'." | |
afe98dfa | 62 | (let* ((result-params (cdr (assoc :result-params params))) |
86fbb8ca CD |
63 | (cmdline (cdr (assoc :cmdline params))) |
64 | (engine (cdr (assoc :engine params))) | |
afe98dfa | 65 | (in-file (org-babel-temp-file "sql-in-")) |
86fbb8ca | 66 | (out-file (or (cdr (assoc :out-file params)) |
afe98dfa | 67 | (org-babel-temp-file "sql-out-"))) |
3ab2c837 | 68 | (header-delim "") |
86fbb8ca | 69 | (command (case (intern engine) |
3ab2c837 BG |
70 | ('msosql (format "osql %s -s \"\t\" -i %s -o %s" |
71 | (or cmdline "") | |
72 | (org-babel-process-file-name in-file) | |
73 | (org-babel-process-file-name out-file))) | |
74 | ('mysql (format "mysql %s < %s > %s" | |
afe98dfa | 75 | (or cmdline "") |
3ab2c837 BG |
76 | (org-babel-process-file-name in-file) |
77 | (org-babel-process-file-name out-file))) | |
78 | ('postgresql (format | |
79 | "psql -A -P footer=off -F \"\t\" -f %s -o %s %s" | |
80 | (org-babel-process-file-name in-file) | |
81 | (org-babel-process-file-name out-file) | |
82 | (or cmdline ""))) | |
86fbb8ca CD |
83 | (t (error "no support for the %s sql engine" engine))))) |
84 | (with-temp-file in-file | |
acedf35c | 85 | (insert (org-babel-expand-body:sql body params))) |
86fbb8ca CD |
86 | (message command) |
87 | (shell-command command) | |
88 | (with-temp-buffer | |
3ab2c837 BG |
89 | ;; need to figure out what the delimiter is for the header row |
90 | (with-temp-buffer | |
91 | (insert-file-contents out-file) | |
92 | (goto-char (point-min)) | |
93 | (when (re-search-forward "^\\(-+\\)[^-]" nil t) | |
94 | (setq header-delim (match-string-no-properties 1))) | |
95 | (goto-char (point-max)) | |
96 | (forward-char -1) | |
97 | (while (looking-at "\n") | |
98 | (delete-char 1) | |
99 | (goto-char (point-max)) | |
100 | (forward-char -1)) | |
101 | (write-file out-file)) | |
acedf35c | 102 | (org-table-import out-file '(16)) |
86fbb8ca | 103 | (org-babel-reassemble-table |
3ab2c837 BG |
104 | (mapcar (lambda (x) |
105 | (if (string= (car x) header-delim) | |
106 | 'hline | |
107 | x)) | |
108 | (org-table-to-lisp)) | |
afe98dfa CD |
109 | (org-babel-pick-name (cdr (assoc :colname-names params)) |
110 | (cdr (assoc :colnames params))) | |
111 | (org-babel-pick-name (cdr (assoc :rowname-names params)) | |
112 | (cdr (assoc :rownames params))))))) | |
86fbb8ca | 113 | |
acedf35c CD |
114 | (defun org-babel-sql-expand-vars (body vars) |
115 | "Expand the variables held in VARS in BODY." | |
116 | (mapc | |
117 | (lambda (pair) | |
118 | (setq body | |
119 | (replace-regexp-in-string | |
120 | (format "\$%s" (car pair)) | |
121 | ((lambda (val) | |
122 | (if (listp val) | |
123 | ((lambda (data-file) | |
124 | (with-temp-file data-file | |
125 | (insert (orgtbl-to-csv | |
126 | val '(:fmt (lambda (el) (if (stringp el) | |
127 | el | |
128 | (format "%S" el))))))) | |
129 | data-file) | |
130 | (org-babel-temp-file "sql-data-")) | |
131 | (if (stringp val) val (format "%S" val)))) | |
132 | (cdr pair)) | |
133 | body))) | |
134 | vars) | |
135 | body) | |
86fbb8ca CD |
136 | |
137 | (defun org-babel-prep-session:sql (session params) | |
138 | "Raise an error because Sql sessions aren't implemented." | |
139 | (error "sql sessions not yet implemented")) | |
140 | ||
141 | (provide 'ob-sql) | |
142 | ||
5b409b39 | 143 | |
86fbb8ca CD |
144 | |
145 | ;;; ob-sql.el ends here |