ob-sql.el 5.1 KB

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