* [2025-02-15 16:25 +0000] Ihor Radchenko <yanta...@posteo.net>:
> Ihor Radchenko <yanta...@posteo.net> writes:
>
> May you please update your latest patch for ob-sql.el, converting it
> into (1) patch to sql.el; (2) patch for ob-sql.el that assumes changes
> to sql.el?
Here they are
1) the patch of sql.el
2) the list of changes in ob-sql.el against release_9.7.30.
The patch that assumes the change in sql.el is n°13.
Cheers
--
Phil Estival
>From d83ee2c93d249673281c401bb78eb0569cd17e07 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Tue, 13 May 2025 15:39:51 +0200
Subject: [PATCH 1/2] * lisp/progmodes/sql.el: login without prompting
New optional parameter 'no-prompt' to `sql-product-interactive'.
When set with all connection parameters provided, skip the call to
`sql-get-login' to connect without opening the prompt, but still prompt
if any required parameter is missing. Also skip the prompt for SQLite
when no database name is provided in order to connect to the in-memory
transient database.
---
lisp/progmodes/sql.el | 18 ++++++++++++++----
1 file changed, 14 insertions(+), 4 deletions(-)
diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index 58fbf3c51e7..af5b1a399cb 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -4524,13 +4524,15 @@ sql-connection-menu-filter
;;; Entry functions for different SQL interpreters.
;;;###autoload
-(defun sql-product-interactive (&optional product new-name)
- "Run PRODUCT interpreter as an inferior process.
+(defun sql-product-interactive (&optional product new-name no-prompt)
+ "Run PRODUCT interpreter as an inferior process in a NEW-NAME buffer.
If buffer `*SQL*' exists but no process is running, make a new process.
If buffer exists and a process is running, just make sure buffer `*SQL*'
is displayed.
+When NO-PROMPT is set, try to connect without prompting
+with the already supplied parameters.
To specify the SQL product, prefix the call with
\\[universal-argument]. To set the buffer name as well, prefix
the call to \\[sql-product-interactive] with
@@ -4572,8 +4574,16 @@ sql-product-interactive
new-sqli-buffer rpt)
;; Get credentials.
- (apply #'sql-get-login
- (sql-get-product-feature product :sqli-login))
+ (when (or (not no-prompt) ; default
+ (and no-prompt ; or a parameter missing
+ (cl-some #'(lambda (s) (or (null s) (string= (format "%s" s) "")))
+ (list sql-server sql-user sql-password sql-database))
+ ;; sqlite allows in-memory db, w/o login
+ (not (and (or (null sql-database)
+ (string= "" sql-database))
+ (eq product 'sqlite)))))
+ (apply #'sql-get-login
+ (sql-get-product-feature product :sqli-login)))
;; Connect to database.
(setq rpt (sql-make-progress-reporter nil "Login"))
--
2.39.5
>From 18c84feeb1d3f368a7e0e90692efe7a6a5b7c64f Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Tue, 13 May 2025 15:57:24 +0200
Subject: [PATCH 2/2] * lisp/progmodes/sql.el: sql-comint-sqlite allows nil
database name
Connecting to SQLite with no database name provided (be it nil or empty
string), means to connect to the in-memory transient database. Since no
parameter can be given, they are made optional.
---
lisp/progmodes/sql.el | 8 +++++---
1 file changed, 5 insertions(+), 3 deletions(-)
diff --git a/lisp/progmodes/sql.el b/lisp/progmodes/sql.el
index af5b1a399cb..296f0336081 100644
--- a/lisp/progmodes/sql.el
+++ b/lisp/progmodes/sql.el
@@ -5075,13 +5075,15 @@ sql-sqlite
(interactive "P")
(sql-product-interactive 'sqlite buffer))
-(defun sql-comint-sqlite (product options &optional buf-name)
- "Create comint buffer and connect to SQLite."
+(defun sql-comint-sqlite (product &optional options buf-name)
+ "Create a comint buffer and connect to SQLite (PRODUCT).
+See `sql-comint' for the OPTIONS and BUF-NAME parameters."
;; Put all parameters to the program (if defined) in a list and call
;; make-comint.
(let ((params
(append options
- (if (not (string= "" sql-database))
+ (if (and sql-database
+ (not (string= "" sql-database)))
`(,(expand-file-name sql-database))))))
(sql-comint product params buf-name)))
--
2.39.5
>From ec8ebf2cad6a6ca20596e85d38f68c5c75d34f1f Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:31:35 +0200
Subject: [PATCH 01/14] ob-sql: session support. Introduces new variables and
functions
* lisp/ob-sql.el: new variables and functions for session support for
postgres and sqlite. Requires sql.el for a connection to a session.
SQL clients are configured by a preamble of commands given to the SQL
shell. Custom variables are declared in a new sub-group ob-babel-sql.
The echo of an SQL ANSI comment is to be appended to the source block
of SQL commands for comint to detect when the commands terminate,
instead of relying on prompt detection.
---
lisp/ob-sql.el | 70 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 70 insertions(+)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index ca6512783..3552d46da 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -4,6 +4,7 @@
;; Author: Eric Schulte
;; Maintainer: Daniel Kraus <dan...@kraus.my>
+;; Maintainer: Philippe Estival <p...@7d.nz>
;; Keywords: literate programming, reproducible research
;; URL: https://orgmode.org
@@ -75,6 +76,33 @@
(org-assert-version)
(require 'ob)
+(require 'sql)
+
+(defvar org-babel-sql-session-start-time)
+(defvar org-sql-session-preamble
+ (list
+ 'postgres "\\set ON_ERROR_STOP 1
+\\pset footer off
+\\pset pager off
+\\pset format unaligned" )
+ "Command preamble to run upon shell start.")
+(defvar org-sql-session-command-terminated nil)
+(defvar org-sql-session--batch-terminate "---#" "To print at the end of a command batch.")
+(defvar org-sql-batch-terminate
+ (list 'sqlite (format ".print %s\n" org-sql-session--batch-terminate)
+ 'postgres (format "\\echo %s\n" org-sql-session--batch-terminate))
+ "Print the command batch termination as last command.")
+(defvar org-sql-terminal-command-prefix
+ (list 'sqlite "\\."
+ 'postgres "\\\\")
+ "Identify a command for the SQL shell.")
+(defvar org-sql-environment
+ (list 'postgres '(("PGPASSWORD" sql-password))))
+(defvar org-sql-session-clean-output nil
+ "Store the regexp used to clear output (prompt1|termination|prompt2).")
+(defvar org-sql-session-start-time)
+(defvar org-sql-session-command-terminated nil)
+(defvar org-sql-session--batch-terminate "---#" "To print at the end of a command batch.")
(declare-function org-table-import "org-table" (file arg))
(declare-function orgtbl-to-csv "org-table" (table params))
@@ -85,6 +113,25 @@
(defvar sql-connection-alist)
(defvar org-babel-default-header-args:sql '())
+(defcustom org-sql-run-comint-p 'nil
+ "Run non-session SQL commands through comint if not nil."
+ :type '(boolean)
+ :group 'org-babel-sql
+ :safe t)
+
+(defcustom org-sql-timeout '5.0
+ "Abort on timeout."
+ :type '(number)
+ :group 'org-babel-sql
+ :safe t)
+
+(defcustom org-sql-close-out-temp-buffer-p 'nil
+ "To automatically close sql-out-temp buffer."
+ :type '(boolean)
+ :group 'org-babel-sql
+ :safe t)
+
+
(defconst org-babel-header-args:sql
'((engine . :any)
(out-file . :any)
@@ -419,6 +466,29 @@ argument mechanism."
"Raise an error because Sql sessions aren't implemented."
(error "SQL sessions not yet implemented"))
+
+(defun org-sql-session-comint-output-filter (_proc string)
+ "Process output STRING of PROC gets redirected to a temporary buffer.
+It is called several times consecutively as the shell outputs and flush
+its message buffer"
+
+ ;; Inserting a result in the sql process buffer (to read it as a
+ ;; regular prompt log) inserts it to the terminal, and as a result the
+ ;; ouput would get passed as input onto the next command line; See
+ ;; `comint-redirect-setup' to possibly fix that,
+ ;; (with-current-buffer (process-buffer proc) (insert output))
+
+ (when (or (string-match org-sql-session--batch-terminate string)
+ (> (time-to-seconds
+ (time-subtract (current-time)
+ org-sql-session-start-time))
+ org-sql-timeout))
+ (setq org-sql-session-command-terminated t))
+
+ (with-current-buffer (get-buffer-create "*ob-sql-result*")
+ (insert string)))
+
+
(provide 'ob-sql)
;;; ob-sql.el ends here
--
2.39.5
>From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:32:59 +0200
Subject: [PATCH 02/14] ob-sql: default header arguments are declared in a
custom variable
* lisp/ob-sql.el: default header arguments are declared in a custom
variable with :options of composite types.
---
lisp/ob-sql.el | 8 +++++++-
1 file changed, 7 insertions(+), 1 deletion(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 3552d46da..51f029b36 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -111,7 +111,13 @@
(declare-function sql-set-product "sql" (product))
(defvar sql-connection-alist)
-(defvar org-babel-default-header-args:sql '())
+(defcustom org-babel-default-header-args:sql '((:engine . "unset"))
+ "Default header args."
+ :type '(alist :key-type symbol :value-type string
+ :options ("dbi" "sqlite" "mysql" "postgres"
+ "sqsh" "mssql" "vertica" "oracle" "saphana" ))
+ :group 'org-babel-sql
+ :safe t)
(defcustom org-sql-run-comint-p 'nil
"Run non-session SQL commands through comint if not nil."
--
2.39.5
>From 5ea82bf324bfd147aa5ed9a6dd8df0c39539b167 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:34:27 +0200
Subject: [PATCH 03/14] ob-sql: remove org-assert-version to stay compatible
with org 9.5
* lisp/ob-sql.el: removal of org-assert-version makes org-macs no
longer needed.
---
lisp/ob-sql.el | 4 ----
1 file changed, 4 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 51f029b36..5ef4bd014 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -71,10 +71,6 @@
;;
;;; Code:
-
-(require 'org-macs)
-(org-assert-version)
-
(require 'ob)
(require 'sql)
--
2.39.5
>From 0b1adbbfc7c52f70dd5156c2b5cdf30757ae6682 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:35:59 +0200
Subject: [PATCH 04/14] ob-sql: realign variables for improved readability.
---
lisp/ob-sql.el | 8 ++++----
1 file changed, 4 insertions(+), 4 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 5ef4bd014..194c3941b 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -288,12 +288,12 @@ database connections."
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))
+ (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))
(dbinstance (org-babel-find-db-connection-param params :dbinstance))
- (database (org-babel-find-db-connection-param params :database))
+ (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.39.5
>From 3f28c76f9c29d175d0543a2ce7dfbe3aaa03454f Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:43:31 +0200
Subject: [PATCH 05/14] ob-sql: fix docstring.
---
lisp/ob-sql.el | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 194c3941b..7abdf608c 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -284,7 +284,7 @@ database connections."
(cdr (assoc-string dbconnection sql-connection-alist t))))))))
(defun org-babel-execute:sql (body params)
- "Execute a block of Sql code with Babel.
+ "Execute a BODY of SQL code with given PARAMS.
This function is called by `org-babel-execute-src-block'."
(let* ((result-params (cdr (assq :result-params params)))
(cmdline (cdr (assq :cmdline params)))
--
2.39.5
>From 7f32afffc89ea2442b6e9ec9f4697641c2078755 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:45:44 +0200
Subject: [PATCH 06/14] ob-sql: turn a unique (cond expression) to a (when
expression)
---
lisp/ob-sql.el | 16 +++++++---------
1 file changed, 7 insertions(+), 9 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 7abdf608c..568de9bf6 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -401,15 +401,13 @@ SET COLSEP '|'
(cond
((memq (intern engine) '(dbi mysql postgresql postgres saphana sqsh vertica))
;; Add header row delimiter after column-names header in first line
- (cond
- (colnames-p
- (with-temp-buffer
- (insert-file-contents out-file)
- (goto-char (point-min))
- (forward-line 1)
- (insert "-\n")
- (setq header-delim "-")
- (write-file out-file)))))
+ (when colnames-p (with-temp-buffer
+ (insert-file-contents out-file)
+ (goto-char (point-min))
+ (forward-line 1)
+ (insert "-\n")
+ (setq header-delim "-")
+ (write-file out-file))))
(t
;; Need to figure out the delimiter for the header row
(with-temp-buffer
--
2.39.5
>From dfbd8cb3834a9efc24a66ff153d0e8a456fd1136 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:49:52 +0200
Subject: [PATCH 07/14] ob-sql: new function for session and db connection
initiation
* lisp/ob-sql.el: introduces `org-babel-sql-session-connect'. It
calls the updated `sql-product-interactive' which accept 3 arguments
in order to connect without prompting when all parameters are provided
in the code block header.
---
lisp/ob-sql.el | 58 ++++++++++++++++++++++++++++++++++++++++++++++----
1 file changed, 54 insertions(+), 4 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 568de9bf6..880e8cdba 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -462,10 +462,60 @@ argument mechanism."
vars)
body)
-(defun org-babel-prep-session:sql (_session _params)
- "Raise an error because Sql sessions aren't implemented."
- (error "SQL sessions not yet implemented"))
-
+(defun org-babel-sql-session-connect (in-engine params session)
+ "Start the SQL client of IN-ENGINE if it has not.
+PARAMS provides the sql connection parameters for a new or
+existing SESSION. Clear the intermediate buffer from previous
+output, and set the process filter. Return the comint process
+buffer."
+ (let* ((buffer-name (format "%s" (if (string= session "none") ""
+ (format "[%s]" session))))
+ (ob-sql-buffer (format "*SQL: %s*" buffer-name)))
+
+ ;; initiate a new connection
+ (when (not (org-babel-comint-buffer-livep ob-sql-buffer))
+ ;; store the regexp used to clear output (prompt1|indicator|prompt2)
+ (let ((prompt-regexp (sql-get-product-feature in-engine :prompt-regexp ))
+ (prompt-cont-regexp (sql-get-product-feature in-engine :prompt-cont-regexp)))
+ (setq org-sql-session-clean-output
+ (plist-put org-sql-session-clean-output in-engine
+ (concat "\\(" prompt-regexp "\\)"
+ "\\|\\(" org-sql-session--batch-terminate "\n\\)"
+ (when prompt-cont-regexp
+ (concat "\\|\\(" prompt-cont-regexp "\\)"))))))
+
+ (let ((sql-server (cdr (assoc :dbhost params)))
+ (port (cdr (assoc :port params)))
+ (sql-database (cdr (assoc :database params)))
+ (sql-user (cdr (assoc :dbuser params)))
+ (sql-password (cdr (assoc :dbpassword params))))
+ (when port (setq-local sql-port port))
+ (save-window-excursion
+ ;; provides environment expressions to the comint service
+ (let ((process-environment (copy-sequence process-environment))
+ (variables (sql-get-product-feature in-engine :sql-environment)))
+ (mapc (lambda (elem) ; evaluate environment expressions
+ (setenv (car elem) (eval (cadr elem))))
+ variables)
+ (sql-product-interactive in-engine buffer-name t)))
+
+ (let ((sql-term-proc (get-buffer-process ob-sql-buffer)))
+ (unless sql-term-proc
+ (user-error (format "SQL %s didn't start" in-engine)))
+
+ (with-current-buffer (get-buffer ob-sql-buffer)
+ ;; preamble commands
+ (let ((preamble (plist-get org-sql-session-preamble in-engine)))
+ (when preamble
+ (process-send-string ob-sql-buffer preamble)
+ (comint-send-input))))
+ ;; let the preamble execution finish and be filtered
+ (sleep-for 0.1))))
+
+ ;; set the redirection filter and return the SQL client buffer
+ (set-process-filter (get-buffer-process ob-sql-buffer)
+ #'org-sql-session-comint-output-filter)
+ (get-buffer ob-sql-buffer)))
(defun org-sql-session-comint-output-filter (_proc string)
"Process output STRING of PROC gets redirected to a temporary buffer.
--
2.39.5
>From dfe081ced1410e128d83393fb044149051e7b63f Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:54:06 +0200
Subject: [PATCH 08/14] ob-sql: simplify docstring.
---
lisp/ob-sql.el | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 880e8cdba..6fa4d38b3 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -284,7 +284,7 @@ database connections."
(cdr (assoc-string dbconnection sql-connection-alist t))))))))
(defun org-babel-execute:sql (body params)
- "Execute a BODY of SQL code with given PARAMS.
+ "Execute SQL BODY with PARAMS.
This function is called by `org-babel-execute-src-block'."
(let* ((result-params (cdr (assq :result-params params)))
(cmdline (cdr (assq :cmdline params)))
--
2.39.5
>From 53052417c00d3d1464d0823adee4d637b16b8fea Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 14:57:33 +0200
Subject: [PATCH 09/14] ob-sql: add support for sessions in
`org-babel-execute:sql'
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
* lisp/ob-sql.el: regular code block execution can also be run through
the same path as sessions — e.g. functions of sql.el and a comint
buffer — when the custom predicate `org-sql-run-comint-p' is set.
---
lisp/ob-sql.el | 45 ++++++++++++++++++++++++++++++++++++++++++++-
1 file changed, 44 insertions(+), 1 deletion(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 6fa4d38b3..42b30b85c 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -295,11 +295,52 @@ This function is called by `org-babel-execute-src-block'."
(dbinstance (org-babel-find-db-connection-param params :dbinstance))
(database (org-babel-find-db-connection-param params :database))
(engine (cdr (assq :engine params)))
+ (in-engine (intern (or engine (user-error "Missing :engine"))))
(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 "")
+ (session (cdr (assoc :session params)))
+ (session-p (not (string= session "none"))))
+
+ (if (or session-p org-sql-run-comint-p) ; run through comint
+ (let ((sql--buffer
+ (org-babel-sql-session-connect in-engine params session)))
+ (with-current-buffer (get-buffer-create "*ob-sql-result*")
+ (erase-buffer))
+ (setq org-sql-session-start-time (current-time))
+ (setq org-sql-session-command-terminated nil)
+
+ (with-current-buffer (get-buffer sql--buffer)
+ (process-send-string (current-buffer)
+ (org-sql-session-format-query
+ (org-babel-expand-body:sql body params)
+ in-engine))
+ (while (or (not org-sql-session-command-terminated)
+ (> (time-to-seconds
+ (time-subtract (current-time)
+ org-sql-session-start-time))
+ org-sql-timeout))
+ (sleep-for 0.03))
+ ;; command finished, remove filter
+ (set-process-filter (get-buffer-process sql--buffer) nil)
+
+ (when (not session-p)
+ (comint-quit-subjob)
+ ;; despite this quit signal, the process may not be finished yet
+ (let ((kill-buffer-query-functions nil))
+ (kill-this-buffer))))
+
+ (with-current-buffer (get-buffer-create "*ob-sql-result*")
+ (goto-char (point-min))
+ ;; clear the output or prompt and termination
+ (let ((clean-output (plist-get org-sql-session-clean-output in-engine)))
+ (while (re-search-forward clean-output nil t)
+ (replace-match "")))
+ (write-file out-file)))
+
+ (let ( ; else run one command line
(command (cl-case (intern engine)
(dbi (format "dbish --batch %s < %s | sed '%s' > %s"
(or cmdline "")
@@ -393,7 +434,7 @@ SET COLSEP '|'
(org-babel-expand-body:sql body params)
;; "sqsh" requires "go" inserted at EOF.
(if (string= engine "sqsh") "\ngo" "")))
- (org-babel-eval command "")
+ (org-babel-eval command "")))
(org-babel-result-cond result-params
(with-temp-buffer
(progn (insert-file-contents-literally out-file) (buffer-string)))
@@ -423,6 +464,8 @@ SET COLSEP '|'
(forward-char -1))
(write-file out-file))))
(org-table-import out-file (if (string= engine "sqsh") '(4) '(16)))
+ (when org-sql-close-out-temp-buffer-p
+ (kill-buffer (get-file-buffer out-file)))
(org-babel-reassemble-table
(mapcar (lambda (x)
(if (string= (car x) header-delim)
--
2.39.5
>From 95ad7b5797c6efc924fa2bd2bf995a5b786f1943 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 15:00:24 +0200
Subject: [PATCH 10/14] ob-sql: Fix `org-babel-expand-body' returning extra \n
* lisp/ob-sql.el: empty prologue or epilogue or expanded variables
are no longer replaced by a newline.
---
lisp/ob-sql.el | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 42b30b85c..8cdb0848a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -150,11 +150,11 @@
(let ((prologue (cdr (assq :prologue params)))
(epilogue (cdr (assq :epilogue params))))
(mapconcat 'identity
- (list
+ (delq nil (list
prologue
(org-babel-sql-expand-vars
body (org-babel--get-vars params))
- epilogue)
+ epilogue))
"\n")))
(defun org-babel-edit-prep:sql (info)
--
2.39.5
>From 92e8201e4ee8d4d1f16740d9b8ba23590eec0810 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 15:01:48 +0200
Subject: [PATCH 11/14] ob-sql: replace call to (intern engine) by the
previously declared in-engine
TINYCHANGE
---
lisp/ob-sql.el | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 8cdb0848a..7ee6b1a9c 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -341,7 +341,7 @@ This function is called by `org-babel-execute-src-block'."
(write-file out-file)))
(let ( ; else run one command line
- (command (cl-case (intern engine)
+ (command (cl-case in-engine
(dbi (format "dbish --batch %s < %s | sed '%s' > %s"
(or cmdline "")
(org-babel-process-file-name in-file)
@@ -410,7 +410,7 @@ footer=off -F \"\t\" %s -f %s -o %s %s"
(t (user-error "No support for the %s SQL engine" engine)))))
(with-temp-file in-file
(insert
- (pcase (intern engine)
+ (pcase in-engine
(`dbi "/format partbox\n")
(`oracle "SET PAGESIZE 50000
SET NEWPAGE 0
@@ -440,7 +440,7 @@ SET COLSEP '|'
(progn (insert-file-contents-literally out-file) (buffer-string)))
(with-temp-buffer
(cond
- ((memq (intern engine) '(dbi mysql postgresql postgres saphana sqsh vertica))
+ ((memq in-engine '(dbi mysql postgresql postgres saphana sqsh vertica))
;; Add header row delimiter after column-names header in first line
(when colnames-p (with-temp-buffer
(insert-file-contents out-file)
--
2.39.5
>From 69f06cc99636312198c767f25f4ca8bec477081f Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 15:05:28 +0200
Subject: [PATCH 12/14] ob-sql: restore support for sqlite
---
lisp/ob-sql.el | 3 +++
1 file changed, 3 insertions(+)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 7ee6b1a9c..671097b47 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -347,6 +347,9 @@ This function is called by `org-babel-execute-src-block'."
(org-babel-process-file-name in-file)
"/^+/d;s/^|//;s/(NULL)/ /g;$d"
(org-babel-process-file-name out-file)))
+ (sqlite (format "sqlite3 < %s > %s"
+ (org-babel-process-file-name in-file)
+ (org-babel-process-file-name out-file)))
(monetdb (format "mclient -f tab %s < %s > %s"
(or cmdline "")
(org-babel-process-file-name in-file)
--
2.39.5
>From 581eafcfa39dd9ffd07f0e5c3f295b7afb26e1f3 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 15:13:18 +0200
Subject: [PATCH 13/14] ob-sql: code block in run by interacting in the comint
buffer
* lisp/ob-sql.el: replaces `process-send-string' by an
`insert-for-yank' followed by `comint-send-input'. Tabs are removed
or else would be interpreted as interactives tabs in the shell buffer.
---
lisp/ob-sql.el | 12 ++++++++----
1 file changed, 8 insertions(+), 4 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 671097b47..b03a71bae 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -313,10 +313,14 @@ This function is called by `org-babel-execute-src-block'."
(setq org-sql-session-command-terminated nil)
(with-current-buffer (get-buffer sql--buffer)
- (process-send-string (current-buffer)
- (org-sql-session-format-query
- (org-babel-expand-body:sql body params)
- in-engine))
+ (progn
+ (goto-char (point-max))
+ (insert-for-yank
+ (format "%s\n;\n%s"
+ (org-babel-expand-body:sql
+ (replace-regexp-in-string "[\t]" "" body) params)
+ (plist-get org-sql-batch-terminate in-engine)))
+ (comint-send-input nil t))
(while (or (not org-sql-session-command-terminated)
(> (time-to-seconds
(time-subtract (current-time)
--
2.39.5
>From 49a162ee264ef6f1d8cfa6119c089b9757207ff6 Mon Sep 17 00:00:00 2001
From: Phil Estival <p...@7d.nz>
Date: Wed, 14 May 2025 16:03:14 +0200
Subject: [PATCH 14/14] ob-sql: update commentary reflecting recent changes on
session
* lisp/ob-sql.el: mentions support for session in commentary. SQLite
engine support. Current limitations in sessions: only sqlite and
postgres are configured yet. Error line number count starts as LINE 1
on each instructions.
---
lisp/ob-sql.el | 11 ++++++++---
1 file changed, 8 insertions(+), 3 deletions(-)
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index b03a71bae..980e11395 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -36,6 +36,7 @@
;;
;; Header args used:
;; - engine
+;; - session
;; - cmdline
;; - dbhost
;; - dbport
@@ -63,11 +64,15 @@
;; - vertica
;; - saphana
;;
-;; TODO:
+;; Limitation:
+;; - in sessions:
+;; - engines configured: sqlite, postgres
+;; - no error line number
;;
-;; - support for sessions
+;; TODO:
;; - support for more engines
-;; - what's a reasonable way to drop table data into SQL?
+;; - babel tables as input
+;; - raw replace result
;;
;;; Code:
--
2.39.5
