emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* [QUESTION] A decision about matching ob-sql.el's :engine header argument match sql-mode product name
@ 2020-01-30  6:21 stardiviner
  2020-01-30  7:29 ` Tim Cross
  0 siblings, 1 reply; 9+ messages in thread
From: stardiviner @ 2020-01-30  6:21 UTC (permalink / raw)
  To: Org Mode, Nicolas Goaziou


I'm writing a patch which try to set ~sql-product~ when editing sql src block.

Here is the code:

#+begin_src emacs-lisp
(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)))
#+end_src

But =ob-sql.el= source code hardcoded ~:engine~ names which some are not matched
with ~sql-product-alist~ in =sql-mode=. For example =:engine postgresql=, but it's
~postgres~ in ~sql-product-alist~.

For now, I have two solutions:

1. write an adapter function which translate ~:engine~ header argument value into
   ~sql-product-alist~ product names. This is a safe way, which will not
   incompatiable with old Org document.

2. modify source code hardcoded ~:engine~ names (also need to update Org
   document), this has a minor advantage is *consistance*.

WDYT?

BTW, about writing an adapter function to translate names. I want to hear some
suggestion about use which method. Writing an hash map or assoc list etc?

Regards

-- 
[ 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
      

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: [QUESTION] A decision about matching ob-sql.el's :engine header argument match sql-mode product name
  2020-01-30  6:21 [QUESTION] A decision about matching ob-sql.el's :engine header argument match sql-mode product name stardiviner
@ 2020-01-30  7:29 ` Tim Cross
  2020-01-30 12:32   ` stardiviner
  0 siblings, 1 reply; 9+ messages in thread
From: Tim Cross @ 2020-01-30  7:29 UTC (permalink / raw)
  To: emacs-orgmode


My vote would be to modify ob-sql and change the hard coded values into
a custom variable. Then users can add/remove/modify engine names to suit
their requirements. The default should probably match the product names
supported by sql.el (you could even have multiple entries mapping to the
same product name for backwards compatibility i.e. 'postgres' =>
'postgres', 'postgresql' => 'postgres' etc.). It would also be trivial
to then add new 'products' in the future.

stardiviner <numbchild@gmail.com> writes:

> I'm writing a patch which try to set ~sql-product~ when editing sql src block.
>
> Here is the code:
>
> #+begin_src emacs-lisp
> (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)))
> #+end_src
>
> But =ob-sql.el= source code hardcoded ~:engine~ names which some are not matched
> with ~sql-product-alist~ in =sql-mode=. For example =:engine postgresql=, but it's
> ~postgres~ in ~sql-product-alist~.
>
> For now, I have two solutions:
>
> 1. write an adapter function which translate ~:engine~ header argument value into
>    ~sql-product-alist~ product names. This is a safe way, which will not
>    incompatiable with old Org document.
>
> 2. modify source code hardcoded ~:engine~ names (also need to update Org
>    document), this has a minor advantage is *consistance*.
>
> WDYT?
>
> BTW, about writing an adapter function to translate names. I want to hear some
> suggestion about use which method. Writing an hash map or assoc list etc?
>
> Regards


--
Tim Cross

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: [QUESTION] A decision about matching ob-sql.el's :engine header argument match sql-mode product name
  2020-01-30  7:29 ` Tim Cross
@ 2020-01-30 12:32   ` stardiviner
  2020-01-31 10:53     ` Bastien
  0 siblings, 1 reply; 9+ messages in thread
From: stardiviner @ 2020-01-30 12:32 UTC (permalink / raw)
  To: emacs-orgmode


Tim Cross <theophilusx@gmail.com> writes:

> My vote would be to modify ob-sql and change the hard coded values into
> a custom variable. Then users can add/remove/modify engine names to suit
> their requirements. The default should probably match the product names
> supported by sql.el (you could even have multiple entries mapping to the
> same product name for backwards compatibility i.e. 'postgres' =>
> 'postgres', 'postgresql' => 'postgres' etc.). It would also be trivial
> to then add new 'products' in the future.

Sounds great. I agree with this too.

>
> stardiviner <numbchild@gmail.com> writes:
>
>> I'm writing a patch which try to set ~sql-product~ when editing sql src block.
>>
>> Here is the code:
>>
>> #+begin_src emacs-lisp
>> (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)))
>> #+end_src
>>
>> But =ob-sql.el= source code hardcoded ~:engine~ names which some are not matched
>> with ~sql-product-alist~ in =sql-mode=. For example =:engine postgresql=, but it's
>> ~postgres~ in ~sql-product-alist~.
>>
>> For now, I have two solutions:
>>
>> 1. write an adapter function which translate ~:engine~ header argument value into
>>    ~sql-product-alist~ product names. This is a safe way, which will not
>>    incompatiable with old Org document.
>>
>> 2. modify source code hardcoded ~:engine~ names (also need to update Org
>>    document), this has a minor advantage is *consistance*.
>>
>> WDYT?
>>
>> BTW, about writing an adapter function to translate names. I want to hear some
>> suggestion about use which method. Writing an hash map or assoc list etc?
>>
>> Regards


-- 
[ 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
      

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: [QUESTION] A decision about matching ob-sql.el's :engine header argument match sql-mode product name
  2020-01-30 12:32   ` stardiviner
@ 2020-01-31 10:53     ` Bastien
  2020-02-04 16:29       ` [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer stardiviner
  0 siblings, 1 reply; 9+ messages in thread
From: Bastien @ 2020-01-31 10:53 UTC (permalink / raw)
  To: stardiviner; +Cc: emacs-orgmode

Hi Stardiviner,

thanks for bring up this discussion.

Feel free to commit the patch on master when it's ready.

Thanks,

-- 
 Bastien

^ permalink raw reply	[flat|nested] 9+ messages in thread

* [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer
  2020-01-31 10:53     ` Bastien
@ 2020-02-04 16:29       ` stardiviner
  2020-02-05 15:46         ` stardiviner
  2020-02-12  7:11         ` stardiviner
  0 siblings, 2 replies; 9+ messages in thread
From: stardiviner @ 2020-02-04 16:29 UTC (permalink / raw)
  To: Bastien; +Cc: emacs-orgmode

[-- 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


^ permalink raw reply related	[flat|nested] 9+ messages in thread

* Re: [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer
  2020-02-04 16:29       ` [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer stardiviner
@ 2020-02-05 15:46         ` stardiviner
  2020-02-12  7:11         ` stardiviner
  1 sibling, 0 replies; 9+ messages in thread
From: stardiviner @ 2020-02-05 15:46 UTC (permalink / raw)
  To: Org Mode; +Cc: Bastien Guerry, Nicolas Goaziou


About this patch, patch, what do you think? Nicolas and Bastien.

stardiviner <numbchild@gmail.com> writes:

> 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
      

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer
  2020-02-04 16:29       ` [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer stardiviner
  2020-02-05 15:46         ` stardiviner
@ 2020-02-12  7:11         ` stardiviner
  2020-02-12  8:06           ` Bastien
  1 sibling, 1 reply; 9+ messages in thread
From: stardiviner @ 2020-02-12  7:11 UTC (permalink / raw)
  To: Bastien; +Cc: emacs-orgmode

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256


Hmmm.. Just a gentle ping ... This might is been missed .... Let me resume this thread.

stardiviner <numbchild@gmail.com> writes:

> 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
      
-----BEGIN PGP SIGNATURE-----

iQFIBAEBCAAyFiEE8J9lDX1nSBmJJZFAG13xyVromsMFAl5DpR8UHG51bWJjaGls
ZEBnbWFpbC5jb20ACgkQG13xyVromsMpdwgAnoi3snJMb0j6J54yoFqsly7jrg5L
z2DYZGZtg9lHloogAn+qbkso314PkVgrmDYDtHqYgVw67N6rbkP/Sj8NRGZMSa5J
RBOXOHx/svurGmz8ICHVAvnDumFBl23ApoHCVY8NJWE7ofpXTkNi33NbShXKsrZb
wCiuZFYuKESQ4rwgIasBVn/y1f/chTASMNV2rFVQLW3rL+lIfehw8aRVOTa5azJw
Nbnv7nJb4nmQQt/czhy0Bxn2XiOF4CC53iPQW/6zpr428zFPGAw+YlxoQG8qB77q
ldmnMIW4L7E9ExCge2EjXSRfpChgkOUzZUXva3DS0TMbtyQ76ptzfm97vA==
=HARo
-----END PGP SIGNATURE-----

^ permalink raw reply	[flat|nested] 9+ messages in thread

* Re: [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer
  2020-02-12  7:11         ` stardiviner
@ 2020-02-12  8:06           ` Bastien
  2020-02-12  8:41             ` [SOLVED] " stardiviner
  0 siblings, 1 reply; 9+ messages in thread
From: Bastien @ 2020-02-12  8:06 UTC (permalink / raw)
  To: stardiviner; +Cc: emacs-orgmode

Hi Stardiviner,

stardiviner <numbchild@gmail.com> writes:

> Hmmm.. Just a gentle ping ... This might is been missed .... Let me
> resume this thread.

I've seen this suggestion and the previous ping but did not really
understand the change.

I have edited your patch a little bit to avoid redundant lines and
pushed it in master :

https://code.orgmode.org/bzg/org-mode/commit/7cd9ad8f75

Let me know if it looks good to you.

Thanks for your contribution,

-- 
 Bastien

^ permalink raw reply	[flat|nested] 9+ messages in thread

* [SOLVED] Re: [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer
  2020-02-12  8:06           ` Bastien
@ 2020-02-12  8:41             ` stardiviner
  0 siblings, 0 replies; 9+ messages in thread
From: stardiviner @ 2020-02-12  8:41 UTC (permalink / raw)
  To: Bastien; +Cc: emacs-orgmode

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256


Bastien <bzg@gnu.org> writes:

> Hi Stardiviner,
>
> stardiviner <numbchild@gmail.com> writes:
>
>> Hmmm.. Just a gentle ping ... This might is been missed .... Let me
>> resume this thread.
>
> I've seen this suggestion and the previous ping but did not really
> understand the change.
>
> I have edited your patch a little bit to avoid redundant lines and
> pushed it in master :
>
> https://code.orgmode.org/bzg/org-mode/commit/7cd9ad8f75
>
> Let me know if it looks good to you.
>
> Thanks for your contribution,

This is great. I don't know ~cl-case~'s KEYLIST can be a list. This solved my
problem totally. Still have more to learn. Thanks Bastien. :)

- -- 
[ 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
      
-----BEGIN PGP SIGNATURE-----

iQFIBAEBCAAyFiEE8J9lDX1nSBmJJZFAG13xyVromsMFAl5Duk0UHG51bWJjaGls
ZEBnbWFpbC5jb20ACgkQG13xyVromsMiZQf/TO/2Bn6xmX8txp8icgb1zRX7f9z0
DFSKhlY782ihdWUKLuPpwJdV1zo0sAhoDRTkZSPyaa+8V2zwISyYmVrH72uxyfWV
vGBQOtTT7t68xPqdRAGlM10OZISE9xAISDjxB/HX6RRi3byg9z+15CyjMQTqSbW3
3beeEY+HZf9Rmhcxr8RXqiXgL37Cxk2zVSZwOyeNFZzjTXhJOnb6BEXg+lsUv/vo
/pGjOliFE12fshF0Rox9jX7HmA7fxNnq//CL7afh4zrolYYlJ79VA5Tixrv94M56
O15dPW/O6W0wBz7/ItGSTD3vxgPYx+ZDE5Q/WtHbFH00SV0NoqfxAIlwyw==
=16GH
-----END PGP SIGNATURE-----

^ permalink raw reply	[flat|nested] 9+ messages in thread

end of thread, other threads:[~2020-02-12  8:42 UTC | newest]

Thread overview: 9+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2020-01-30  6:21 [QUESTION] A decision about matching ob-sql.el's :engine header argument match sql-mode product name stardiviner
2020-01-30  7:29 ` Tim Cross
2020-01-30 12:32   ` stardiviner
2020-01-31 10:53     ` Bastien
2020-02-04 16:29       ` [PATCH] ob-sql.el support auto set sql-product in editing sql-mode src block buffer stardiviner
2020-02-05 15:46         ` stardiviner
2020-02-12  7:11         ` stardiviner
2020-02-12  8:06           ` Bastien
2020-02-12  8:41             ` [SOLVED] " stardiviner

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).