[PATCH] ob-sql.el: Option to reference connections in `sql-connection-alist'

[Stefano Rodighiero <stefano.rodighiero@gmail.com>]

[-- Attachment #1.1: Type: text/plain, Size: 1370 bytes --]

Hi,

[This is the first patch I ever submitted.  I hope it complies with
your standards: if it does not, I'll be happy to work on it until it's
fine.  I am not sure it qualifies as a tiny change.]

Org-babel allows SQL snippets to be run on a database connection that
can be specified in the source block header using parameters such as
:dbhost, :dbuser, :dbpassword and so forth.

This is very useful, but I'd also like to be able to use symbolic
references to connections defined elsewhere, so that for example one
does not have to specify the password every time, interactively or,
worse, in the .org file itself.

I am also a user of sql.el, that provides a custom variable
`sql-connection-alist', where users can define a mapping between
connection names and connection details.

The patch I'm submitting extends the behavior of
org-babel-execute:sql so that it's possible to specify a new param
:dbconnection containing a connection name, used for looking up
`sql-connection-alist'.

For example, if `sql-connection-alist' contains something like:

  (("mydb" (sql-product 'postgres)
     (sql-post 5432)
   (sql-server "mydb.server")
   (sql-user "stefano")
   (sql-password "supersecret")))

Then it's possible to use:

  #+begin_src sql :engine postgresql :dbconnection mydb
    select foo, bar, baz from mytable
  #+end_src

s.


-- 
www.stefanorodighiero.net

[-- Attachment #1.2: Type: text/html, Size: 2230 bytes --]

[-- Attachment #2: 0001-ob-sql.el-Option-to-reference-connections-in-sql-con.patch --]
[-- Type: text/x-patch, Size: 3051 bytes --]

From 176cf648f9f38de1b9c35bf1fae06c23187db8b7 Mon Sep 17 00:00:00 2001
From: Stefano Rodighiero <stefano.rodighiero@gmail.com>
Date: Sun, 24 Mar 2019 11:35:21 +0100
Subject: [PATCH] ob-sql.el: Option to reference connections in
 `sql-connection-alist'

* ob-sql.el: Provide a new param called :dbconnection, that can be
used to reference connections defined in `sql-connection-alist', a
custom variable defined in sql.el.
---
 lisp/ob-sql.el | 31 ++++++++++++++++++++++++++-----
 1 file changed, 26 insertions(+), 5 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 2a58188..0e1d4f0 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -39,6 +39,7 @@
 ;; - dbport
 ;; - dbuser
 ;; - dbpassword
+;; - dbconnection (to reference connections in sql-connection-alist)
 ;; - database
 ;; - colnames (default, nil, means "yes")
 ;; - result-params
@@ -174,16 +175,36 @@ Otherwise, use Emacs' standard conversion function."
 	((string= "windows-nt" system-type) file)
 	(t (format "%S" (convert-standard-filename file)))))
 
+(defun org-babel-find-db-connection-param (params name)
+  "Return db connection param NAME.
+Given a param NAME, if :dbconnection is defined in PARAMS then
+look for the param into the corresponding connection defined in
+`sql-connection-alist`, otherwise look into PARAMS.  Look
+`sql-connection-alist` (part of SQL mode) for how to define
+database connections."
+  (if (assq :dbconnection params)
+      (let* ((dbconnection (cdr (assq :dbconnection params)))
+             (name-mapping '((dbhost . sql-server)
+                             (dbport . sql-port)
+                             (dbuser . sql-user)
+                             (dbpassword . sql-password)
+                             (database . sql-database)))
+             (mapped-name (cdr (assq name name-mapping))))
+        (cadr (assq mapped-name
+                    (cdr (assoc dbconnection
+                                sql-connection-alist)))))
+    (cdr (assq name params))))
+
 (defun org-babel-execute:sql (body params)
   "Execute a block of Sql code with Babel.
 This function is called by `org-babel-execute-src-block'."
   (let* ((result-params (cdr (assq :result-params params)))
          (cmdline (cdr (assq :cmdline params)))
-         (dbhost (cdr (assq :dbhost params)))
-         (dbport (cdr (assq :dbport params)))
-         (dbuser (cdr (assq :dbuser params)))
-         (dbpassword (cdr (assq :dbpassword params)))
-         (database (cdr (assq :database params)))
+         (dbhost (org-babel-find-db-connection-param params 'dbhost))
+         (dbport (org-babel-find-db-connection-param params 'dbport))
+         (dbuser (org-babel-find-db-connection-param params 'dbuser))
+         (dbpassword (org-babel-find-db-connection-param params 'dbpassword))
+         (database (org-babel-find-db-connection-param params 'database))
          (engine (cdr (assq :engine params)))
          (colnames-p (not (equal "no" (cdr (assq :colnames params)))))
          (in-file (org-babel-temp-file "sql-in-"))
-- 
2.7.4