ob-sql.el 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333
  1. ;;; ob-sql.el --- Babel Functions for SQL -*- lexical-binding: t; -*-
  2. ;; Copyright (C) 2009-2018 Free Software Foundation, Inc.
  3. ;; Author: Eric Schulte
  4. ;; Keywords: literate programming, reproducible research
  5. ;; Homepage: https://orgmode.org
  6. ;; This file is part of GNU Emacs.
  7. ;; GNU Emacs is free software: you can redistribute it and/or modify
  8. ;; it under the terms of the GNU General Public License as published by
  9. ;; the Free Software Foundation, either version 3 of the License, or
  10. ;; (at your option) any later version.
  11. ;; GNU Emacs is distributed in the hope that it will be useful,
  12. ;; but WITHOUT ANY WARRANTY; without even the implied warranty of
  13. ;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14. ;; GNU General Public License for more details.
  15. ;; You should have received a copy of the GNU General Public License
  16. ;; along with GNU Emacs. If not, see <https://www.gnu.org/licenses/>.
  17. ;;; Commentary:
  18. ;; Org-Babel support for evaluating sql source code.
  19. ;; (see also ob-sqlite.el)
  20. ;;
  21. ;; SQL is somewhat unique in that there are many different engines for
  22. ;; the evaluation of sql (Mysql, PostgreSQL, etc...), so much of this
  23. ;; file will have to be implemented engine by engine.
  24. ;;
  25. ;; Also SQL evaluation generally takes place inside of a database.
  26. ;;
  27. ;; Header args used:
  28. ;; - engine
  29. ;; - cmdline
  30. ;; - dbhost
  31. ;; - dbport
  32. ;; - dbuser
  33. ;; - dbpassword
  34. ;; - database
  35. ;; - colnames (default, nil, means "yes")
  36. ;; - result-params
  37. ;; - out-file
  38. ;;
  39. ;; The following are used but not really implemented for SQL:
  40. ;; - colname-names
  41. ;; - rownames
  42. ;; - rowname-names
  43. ;;
  44. ;; Engines supported:
  45. ;; - mysql
  46. ;; - dbi
  47. ;; - mssql
  48. ;; - sqsh
  49. ;; - postgresql
  50. ;; - oracle
  51. ;; - vertica
  52. ;;
  53. ;; TODO:
  54. ;;
  55. ;; - support for sessions
  56. ;; - support for more engines
  57. ;; - what's a reasonable way to drop table data into SQL?
  58. ;;
  59. ;;; Code:
  60. (require 'ob)
  61. (declare-function org-table-import "org-table" (file arg))
  62. (declare-function orgtbl-to-csv "org-table" (table params))
  63. (declare-function org-table-to-lisp "org-table" (&optional txt))
  64. (declare-function cygwin-convert-file-name-to-windows "cygw32.c" (file &optional absolute-p))
  65. (defvar org-babel-default-header-args:sql '())
  66. (defconst org-babel-header-args:sql
  67. '((engine . :any)
  68. (out-file . :any)
  69. (dbhost . :any)
  70. (dbport . :any)
  71. (dbuser . :any)
  72. (dbpassword . :any)
  73. (database . :any))
  74. "SQL-specific header arguments.")
  75. (defun org-babel-expand-body:sql (body params)
  76. "Expand BODY according to the values of PARAMS."
  77. (org-babel-sql-expand-vars
  78. body (org-babel--get-vars params)))
  79. (defun org-babel-sql-dbstring-mysql (host port user password database)
  80. "Make MySQL cmd line args for database connection. Pass nil to omit that arg."
  81. (combine-and-quote-strings
  82. (delq nil
  83. (list (when host (concat "-h" host))
  84. (when port (format "-P%d" port))
  85. (when user (concat "-u" user))
  86. (when password (concat "-p" password))
  87. (when database (concat "-D" database))))))
  88. (defun org-babel-sql-dbstring-postgresql (host port user database)
  89. "Make PostgreSQL command line args for database connection.
  90. Pass nil to omit that arg."
  91. (combine-and-quote-strings
  92. (delq nil
  93. (list (when host (concat "-h" host))
  94. (when port (format "-p%d" port))
  95. (when user (concat "-U" user))
  96. (when database (concat "-d" database))))))
  97. (defun org-babel-sql-dbstring-oracle (host port user password database)
  98. "Make Oracle command line args for database connection."
  99. (format "%s/%s@%s:%s/%s" user password host port database))
  100. (defun org-babel-sql-dbstring-mssql (host user password database)
  101. "Make sqlcmd command line args for database connection.
  102. `sqlcmd' is the preferred command line tool to access Microsoft
  103. SQL Server on Windows and Linux platform."
  104. (mapconcat #'identity
  105. (delq nil
  106. (list (when host (format "-S \"%s\"" host))
  107. (when user (format "-U \"%s\"" user))
  108. (when password (format "-P \"%s\"" password))
  109. (when database (format "-d \"%s\"" database))))
  110. " "))
  111. (defun org-babel-sql-dbstring-sqsh (host user password database)
  112. "Make sqsh command line args for database connection.
  113. \"sqsh\" is one method to access Sybase or MS SQL via Linux platform"
  114. (mapconcat #'identity
  115. (delq nil
  116. (list (when host (format "-S \"%s\"" host))
  117. (when user (format "-U \"%s\"" user))
  118. (when password (format "-P \"%s\"" password))
  119. (when database (format "-D \"%s\"" database))))
  120. " "))
  121. (defun org-babel-sql-dbstring-vertica (host port user password database)
  122. "Make Vertica command line args for database connection. Pass nil to omit that arg."
  123. (mapconcat #'identity
  124. (delq nil
  125. (list (when host (format "-h %s" host))
  126. (when port (format "-p %d" port))
  127. (when user (format "-U %s" user))
  128. (when password (format "-w %s" (shell-quote-argument password) ))
  129. (when database (format "-d %s" database))))
  130. " "))
  131. (defun org-babel-sql-convert-standard-filename (file)
  132. "Convert FILE to OS standard file name.
  133. If in Cygwin environment, uses Cygwin specific function to
  134. convert the file name. In a Windows-NT environment, do nothing.
  135. Otherwise, use Emacs' standard conversion function."
  136. (cond ((fboundp 'cygwin-convert-file-name-to-windows)
  137. (format "%S" (cygwin-convert-file-name-to-windows file)))
  138. ((string= "windows-nt" system-type) file)
  139. (t (format "%S" (convert-standard-filename file)))))
  140. (defun org-babel-execute:sql (body params)
  141. "Execute a block of Sql code with Babel.
  142. This function is called by `org-babel-execute-src-block'."
  143. (let* ((result-params (cdr (assq :result-params params)))
  144. (cmdline (cdr (assq :cmdline params)))
  145. (dbhost (cdr (assq :dbhost params)))
  146. (dbport (cdr (assq :dbport params)))
  147. (dbuser (cdr (assq :dbuser params)))
  148. (dbpassword (cdr (assq :dbpassword params)))
  149. (database (cdr (assq :database params)))
  150. (engine (cdr (assq :engine params)))
  151. (colnames-p (not (equal "no" (cdr (assq :colnames params)))))
  152. (in-file (org-babel-temp-file "sql-in-"))
  153. (out-file (or (cdr (assq :out-file params))
  154. (org-babel-temp-file "sql-out-")))
  155. (header-delim "")
  156. (command (pcase (intern engine)
  157. (`dbi (format "dbish --batch %s < %s | sed '%s' > %s"
  158. (or cmdline "")
  159. (org-babel-process-file-name in-file)
  160. "/^+/d;s/^|//;s/(NULL)/ /g;$d"
  161. (org-babel-process-file-name out-file)))
  162. (`monetdb (format "mclient -f tab %s < %s > %s"
  163. (or cmdline "")
  164. (org-babel-process-file-name in-file)
  165. (org-babel-process-file-name out-file)))
  166. (`mssql (format "sqlcmd %s -s \"\t\" %s -i %s -o %s"
  167. (or cmdline "")
  168. (org-babel-sql-dbstring-mssql
  169. dbhost dbuser dbpassword database)
  170. (org-babel-sql-convert-standard-filename
  171. (org-babel-process-file-name in-file))
  172. (org-babel-sql-convert-standard-filename
  173. (org-babel-process-file-name out-file))))
  174. (`mysql (format "mysql %s %s %s < %s > %s"
  175. (org-babel-sql-dbstring-mysql
  176. dbhost dbport dbuser dbpassword database)
  177. (if colnames-p "" "-N")
  178. (or cmdline "")
  179. (org-babel-process-file-name in-file)
  180. (org-babel-process-file-name out-file)))
  181. (`postgresql (format
  182. "%spsql --set=\"ON_ERROR_STOP=1\" %s -A -P \
  183. footer=off -F \"\t\" %s -f %s -o %s %s"
  184. (if dbpassword
  185. (format "PGPASSWORD=%s " dbpassword)
  186. "")
  187. (if colnames-p "" "-t")
  188. (org-babel-sql-dbstring-postgresql
  189. dbhost dbport dbuser database)
  190. (org-babel-process-file-name in-file)
  191. (org-babel-process-file-name out-file)
  192. (or cmdline "")))
  193. (`sqsh (format "sqsh %s %s -i %s -o %s -m csv"
  194. (or cmdline "")
  195. (org-babel-sql-dbstring-sqsh
  196. dbhost dbuser dbpassword database)
  197. (org-babel-sql-convert-standard-filename
  198. (org-babel-process-file-name in-file))
  199. (org-babel-sql-convert-standard-filename
  200. (org-babel-process-file-name out-file))))
  201. (`vertica (format "vsql %s -f %s -o %s %s"
  202. (org-babel-sql-dbstring-vertica
  203. dbhost dbport dbuser dbpassword database)
  204. (org-babel-process-file-name in-file)
  205. (org-babel-process-file-name out-file)
  206. (or cmdline "")))
  207. (`oracle (format
  208. "sqlplus -s %s < %s > %s"
  209. (org-babel-sql-dbstring-oracle
  210. dbhost dbport dbuser dbpassword database)
  211. (org-babel-process-file-name in-file)
  212. (org-babel-process-file-name out-file)))
  213. (_ (error "No support for the %s SQL engine" engine)))))
  214. (with-temp-file in-file
  215. (insert
  216. (pcase (intern engine)
  217. (`dbi "/format partbox\n")
  218. (`oracle "SET PAGESIZE 50000
  219. SET NEWPAGE 0
  220. SET TAB OFF
  221. SET SPACE 0
  222. SET LINESIZE 9999
  223. SET ECHO OFF
  224. SET FEEDBACK OFF
  225. SET VERIFY OFF
  226. SET HEADING ON
  227. SET MARKUP HTML OFF SPOOL OFF
  228. SET COLSEP '|'
  229. ")
  230. ((or `mssql `sqsh) "SET NOCOUNT ON
  231. ")
  232. (`vertica "\\a\n")
  233. (_ ""))
  234. (org-babel-expand-body:sql body params)
  235. ;; "sqsh" requires "go" inserted at EOF.
  236. (if (string= engine "sqsh") "\ngo" "")))
  237. (org-babel-eval command "")
  238. (org-babel-result-cond result-params
  239. (with-temp-buffer
  240. (progn (insert-file-contents-literally out-file) (buffer-string)))
  241. (with-temp-buffer
  242. (cond
  243. ((memq (intern engine) '(dbi mysql postgresql sqsh vertica))
  244. ;; Add header row delimiter after column-names header in first line
  245. (cond
  246. (colnames-p
  247. (with-temp-buffer
  248. (insert-file-contents out-file)
  249. (goto-char (point-min))
  250. (forward-line 1)
  251. (insert "-\n")
  252. (setq header-delim "-")
  253. (write-file out-file)))))
  254. (t
  255. ;; Need to figure out the delimiter for the header row
  256. (with-temp-buffer
  257. (insert-file-contents out-file)
  258. (goto-char (point-min))
  259. (when (re-search-forward "^\\(-+\\)[^-]" nil t)
  260. (setq header-delim (match-string-no-properties 1)))
  261. (goto-char (point-max))
  262. (forward-char -1)
  263. (while (looking-at "\n")
  264. (delete-char 1)
  265. (goto-char (point-max))
  266. (forward-char -1))
  267. (write-file out-file))))
  268. (org-table-import out-file (if (string= engine "sqsh") '(4) '(16)))
  269. (org-babel-reassemble-table
  270. (mapcar (lambda (x)
  271. (if (string= (car x) header-delim)
  272. 'hline
  273. x))
  274. (org-table-to-lisp))
  275. (org-babel-pick-name (cdr (assq :colname-names params))
  276. (cdr (assq :colnames params)))
  277. (org-babel-pick-name (cdr (assq :rowname-names params))
  278. (cdr (assq :rownames params))))))))
  279. (defun org-babel-sql-expand-vars (body vars)
  280. "Expand the variables held in VARS in BODY."
  281. (mapc
  282. (lambda (pair)
  283. (setq body
  284. (replace-regexp-in-string
  285. (format "$%s" (car pair))
  286. (let ((val (cdr pair)))
  287. (if (listp val)
  288. (let ((data-file (org-babel-temp-file "sql-data-")))
  289. (with-temp-file data-file
  290. (insert (orgtbl-to-csv
  291. val '(:fmt (lambda (el) (if (stringp el)
  292. el
  293. (format "%S" el)))))))
  294. data-file)
  295. (if (stringp val) val (format "%S" val))))
  296. body)))
  297. vars)
  298. body)
  299. (defun org-babel-prep-session:sql (_session _params)
  300. "Raise an error because Sql sessions aren't implemented."
  301. (error "SQL sessions not yet implemented"))
  302. (provide 'ob-sql)
  303. ;;; ob-sql.el ends here