.emacs.d.git - Gitblit

Chizi123

2018-11-18 76bbd07de7add0f9d13c6914f158d19630fe2f62

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