From mboxrd@z Thu Jan 1 00:00:00 1970 From: stardiviner Subject: [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer Date: Wed, 05 Feb 2020 00:29:05 +0800 Message-ID: <87ftfqb9ke.fsf@gmail.com> References: <87tv4d5us5.fsf@gmail.com> <878slppfke.fsf@gmail.com> <877e195dmk.fsf@gmail.com> <87pnezc2xo.fsf@gnu.org> Reply-To: numbchild@gmail.com Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Return-path: Received: from eggs.gnu.org ([2001:470:142:3::10]:51977) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1iz14l-0007sQ-Hn for emacs-orgmode@gnu.org; Tue, 04 Feb 2020 11:29:25 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1iz14j-0002Ah-EV for emacs-orgmode@gnu.org; Tue, 04 Feb 2020 11:29:23 -0500 Received: from [211.138.116.143] (port=25470 helo=dark.localdomain) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1iz14i-0001mW-LN for emacs-orgmode@gnu.org; Tue, 04 Feb 2020 11:29:21 -0500 In-reply-to: <87pnezc2xo.fsf@gnu.org> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane-mx.org@gnu.org Sender: "Emacs-orgmode" To: Bastien Cc: emacs-orgmode@gnu.org --=-=-= Content-Type: text/plain 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: ( . (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 --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=0001-ob-sql.el-Auto-set-sql-product-in-editing-sql-mode-s.patch Content-Description: ob-sql.el auto set sql-product >From ebd48be2c95f4e99ddd7810ba5e82685299d3da1 Mon Sep 17 00:00:00 2001 From: stardiviner 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 --=-=-=--