[PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer

[stardiviner <numbchild@gmail.com>]

[-- Attachment #1: Type: text/plain, Size: 7291 bytes --]


I tried to write an alist of all database names. and write an function to used
to match name to possible names. But I found this solution is a little kind of
complicated.

Another simple solution is just add an duplicate code of "postgresql" for alias
"postgres". Because I found only one exception "postgresql" is "postgres" in
~sql-mode~ products alist. Other database names are matched with ~sql-mode~.

So I decided to take the simpler solution. I attached patch in the email attachment.

# ==============================================================================

BTW, I'm still not good at Elisp, can't write out a good solution for translate
and matching for database names.

Here is my temporary databases alist:

#+begin_src diff
modified   lisp/ob-sql.el
@@ -87,6 +87,52 @@ (defconst org-babel-header-args:sql
     (database	       . :any))
   "SQL-specific header arguments.")
 
+(defcustom org-babel-sql-engines-alist nil
+  "Alist of engine names for :engine header argument.
+
+It is an alist data structure: (<engine_identity> . (list of alias names)).
+Merged with sql-mode's `sql-product-alist'."
+  :type '(alist :key-type symbol :value-type list)
+  :group 'org-babel)
+
+;;; initialize `org-babel-sql-engines-alist'
+(dolist (product (mapcar 'car sql-product-alist))
+  (pcase product
+    (`ansi
+     (add-to-list 'org-babel-sql-engines-alist (cons 'ansi (list 'ansi))))
+    (`sqlite
+     (add-to-list 'org-babel-sql-engines-alist (cons 'sqlite (list 'sqlite))))
+    (`mysql
+     (add-to-list 'org-babel-sql-engines-alist (cons 'mysql (list 'mysql))))
+    (`mariadb
+     (add-to-list 'org-babel-sql-engines-alist (cons 'mariadb (list 'mariadb 'maria))))
+    (`postgresql
+     (add-to-list 'org-babel-sql-engines-alist (cons 'postgresql (list 'postgresql 'postgres))))
+    (`oracle
+     (add-to-list 'org-babel-sql-engines-alist (cons 'oracle (list 'oracle))))
+    (`mssql
+     (add-to-list 'org-babel-sql-engines-alist (cons 'mssql (list 'mssql 'ms))))
+    (`dbi
+     (add-to-list 'org-babel-sql-engines-alist (cons 'dbi (list 'dbi))))
+    (`monetdb
+     (add-to-list 'org-babel-sql-engines-alist (cons 'monetdb (list 'monetdb))))
+    (`sqsh
+     (add-to-list 'org-babel-sql-engines-alist (cons 'sqsh (list 'sqsh))))
+    (`vertica
+     (add-to-list 'org-babel-sql-engines-alist (cons 'vertica (list 'vertica))))
+    (`db2
+     (add-to-list 'org-babel-sql-engines-alist (cons 'db2 (list 'db2))))
+    (`infomix
+     (add-to-list 'org-babel-sql-engines-alist (cons 'infomix (list 'infomix))))
+    (`ingres
+     (add-to-list 'org-babel-sql-engines-alist (cons 'ingres (list 'ingres))))
+    (`interbase
+     (add-to-list 'org-babel-sql-engines-alist (cons 'interbase (list 'interbase))))
+    (`solid
+     (add-to-list 'org-babel-sql-engines-alist (cons 'solid (list 'solid))))
+    (`sybase
+     (add-to-list 'org-babel-sql-engines-alist (cons 'sybase (list 'sybase))))))
+
 (defun org-babel-expand-body:sql (body params)
   "Expand BODY according to the values of PARAMS."
   (org-babel-sql-expand-vars

#+end_src

I don't know how to write a translation function for alist to be used in function ~org-babel-execute:sql~.

#+begin_src emacs-lisp :eval no
(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 (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-"))
         (out-file (or (cdr (assq :out-file params))
                       (org-babel-temp-file "sql-out-")))
	 (header-delim "")
         (command (pcase (intern engine)
                    (`dbi (format "dbish --batch %s < %s | sed '%s' > %s"
				  (or cmdline "")
				  (org-babel-process-file-name in-file)
				  "/^+/d;s/^|//;s/(NULL)/ /g;$d"
				  (org-babel-process-file-name out-file)))
                    (`monetdb (format "mclient -f tab %s < %s > %s"
				      (or cmdline "")
				      (org-babel-process-file-name in-file)
				      (org-babel-process-file-name out-file)))
		    (`mssql (format "sqlcmd %s -s \"\t\" %s -i %s -o %s"
				    (or cmdline "")
				    (org-babel-sql-dbstring-mssql
				     dbhost dbuser dbpassword database)
				    (org-babel-sql-convert-standard-filename
				     (org-babel-process-file-name in-file))
				    (org-babel-sql-convert-standard-filename
				     (org-babel-process-file-name out-file))))
                    (`mysql (format "mysql %s %s %s < %s > %s"
				    (org-babel-sql-dbstring-mysql
				     dbhost dbport dbuser dbpassword database)
				    (if colnames-p "" "-N")
				    (or cmdline "")
				    (org-babel-process-file-name in-file)
				    (org-babel-process-file-name out-file)))
		    (`postgresql (format
				  "%spsql --set=\"ON_ERROR_STOP=1\" %s -A -P \
footer=off -F \"\t\"  %s -f %s -o %s %s"
				  (if dbpassword
				      (format "PGPASSWORD=%s " dbpassword)
				    "")
				  (if colnames-p "" "-t")
				  (org-babel-sql-dbstring-postgresql
				   dbhost dbport dbuser database)
				  (org-babel-process-file-name in-file)
				  (org-babel-process-file-name out-file)
				  (or cmdline "")))
		    (`postgres (format
				"%spsql --set=\"ON_ERROR_STOP=1\" %s -A -P \
footer=off -F \"\t\"  %s -f %s -o %s %s"
				(if dbpassword
				    (format "PGPASSWORD=%s " dbpassword)
				  "")
				(if colnames-p "" "-t")
				(org-babel-sql-dbstring-postgresql
				 dbhost dbport dbuser database)
				(org-babel-process-file-name in-file)
				(org-babel-process-file-name out-file)
				(or cmdline "")))
		    (`sqsh (format "sqsh %s %s -i %s -o %s -m csv"
				   (or cmdline "")
				   (org-babel-sql-dbstring-sqsh
				    dbhost dbuser dbpassword database)
				   (org-babel-sql-convert-standard-filename
				    (org-babel-process-file-name in-file))
				   (org-babel-sql-convert-standard-filename
				    (org-babel-process-file-name out-file))))
		    (`vertica (format "vsql %s -f %s -o %s %s"
				    (org-babel-sql-dbstring-vertica
				     dbhost dbport dbuser dbpassword database)
				    (org-babel-process-file-name in-file)
				    (org-babel-process-file-name out-file)
				    (or cmdline "")))
                    (`oracle (format
			      "sqlplus -s %s < %s > %s"
			      (org-babel-sql-dbstring-oracle
			       dbhost dbport dbuser dbpassword database)
			      (org-babel-process-file-name in-file)
			      (org-babel-process-file-name out-file)))
                    (_ (error "No support for the %s SQL engine" engine)))))
       ......
#+end_src

-- 
[ stardiviner ]
       I try to make every word tell the meaning what I want to express.

       Blog: https://stardiviner.github.io/
       IRC(freenode): stardiviner, Matrix: stardiviner
       GPG: F09F650D7D674819892591401B5DF1C95AE89AC3
      

[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: ob-sql.el auto set sql-product --]
[-- Type: text/x-patch, Size: 2661 bytes --]

From ebd48be2c95f4e99ddd7810ba5e82685299d3da1 Mon Sep 17 00:00:00 2001
From: stardiviner <numbchild@gmail.com>
Date: Wed, 5 Feb 2020 00:06:31 +0800
Subject: [PATCH] ob-sql.el: Auto set sql-product in editing sql-mode src block
 buffer.

* lisp/ob-sql.el (org-babel-execute:sql): Support :engine postgres alias
of postgresql. For consistence with `sql-mode' variable
`sql-product-alist'.

* lisp/ob-sql.el (org-babel-edit-prep:sql): Support set sql-product in
editing sql-mode src block buffer.

This can improve editing sql-mode src block buffer syntax highlighting
and other related stuffs.
---
 lisp/ob-sql.el | 23 +++++++++++++++++++++--
 1 file changed, 21 insertions(+), 2 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 59cf19568..3e0ffafbd 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -55,7 +55,7 @@
 ;; - dbi
 ;; - mssql
 ;; - sqsh
-;; - postgresql
+;; - postgresql (postgres)
 ;; - oracle
 ;; - vertica
 ;;
@@ -92,6 +92,13 @@ (defun org-babel-expand-body:sql (body params)
   (org-babel-sql-expand-vars
    body (org-babel--get-vars params)))
 
+(defun org-babel-edit-prep:sql (info)
+  "Set `sql-product' in Org edit buffer.
+Set `sql-product' in Org edit buffer according to the
+corresponding :engine source block header argument."
+  (let ((product (cdr (assq :engine (nth 2 info)))))
+    (sql-set-product product)))
+
 (defun org-babel-sql-dbstring-mysql (host port user password database)
   "Make MySQL cmd line args for database connection.  Pass nil to omit that arg."
   (combine-and-quote-strings
@@ -248,6 +255,18 @@ (defun org-babel-execute:sql (body params)
 				  (org-babel-process-file-name in-file)
 				  (org-babel-process-file-name out-file)
 				  (or cmdline "")))
+		    (`postgres (format
+				"%spsql --set=\"ON_ERROR_STOP=1\" %s -A -P \
+footer=off -F \"\t\"  %s -f %s -o %s %s"
+				(if dbpassword
+				    (format "PGPASSWORD=%s " dbpassword)
+				  "")
+				(if colnames-p "" "-t")
+				(org-babel-sql-dbstring-postgresql
+				 dbhost dbport dbuser database)
+				(org-babel-process-file-name in-file)
+				(org-babel-process-file-name out-file)
+				(or cmdline "")))
 		    (`sqsh (format "sqsh %s %s -i %s -o %s -m csv"
 				   (or cmdline "")
 				   (org-babel-sql-dbstring-sqsh
@@ -301,7 +320,7 @@ (defun org-babel-execute:sql (body params)
 	(progn (insert-file-contents-literally out-file) (buffer-string)))
       (with-temp-buffer
 	(cond
-	 ((memq (intern engine) '(dbi mysql postgresql sqsh vertica))
+	 ((memq (intern engine) '(dbi mysql postgresql postgres sqsh vertica))
 	  ;; Add header row delimiter after column-names header in first line
 	  (cond
 	   (colnames-p
-- 
2.25.0