Package: emacs;
Reported by: Phil Estival <pe <at> 7d.nz>
Date: Mon, 3 Feb 2025 05:20:02 UTC
Severity: wishlist
Tags: patch
Message #73 received at 76025 <at> debbugs.gnu.org (full text, mbox):
From: Phil Estival <pe <at> 7d.nz> To: Ihor Radchenko <yantar92 <at> posteo.net> Cc: Michael Mauger <mmauger <at> protonmail.com>, Org Mode List <emacs-orgmode <at> gnu.org>, 76025 <at> debbugs.gnu.org Subject: Re: [PATCH] ob-sql: session Date: Sun, 15 Jun 2025 17:16:08 +0200
[Message part 1 (text/plain, inline)]
I'm submitting again the patches for ob-sql.el, taking into
considerations the previous review, commented below. I also took good
note of Michael remarks. The attached series of patches will work
without any modification to sql.el. I'll introduce later a local
`sql-connection' bound to the session name. In the absence of it,
sessions will work, but will ask to confirm provided connection
parameter upon establishing connection.
* [2025-05-17 18:05 +0000] Ihor Radchenko <yantar92 <at> posteo.net>:
> Phil Estival <pe <at> 7d.nz> 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.
>
> Thanks! I will comment on the Org-related part.
>
>> 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,l
>> instead of relying on prompt detection.
>
> This is not a complete changelog of your changes. Please check out
> https://orgmode.org/worg/org-contribute.html#commit-messages and follow
> the format.
>
> In general, because you plan to be the maintainer, please familiarize
> yourself with all the conventions discussed in
> https://orgmode.org/worg/org-contribute.html and
> https://orgmode.org/worg/org-maintenance.html
>
>> ;; Author: Eric Schulte
>> ;; Maintainer: Daniel Kraus <daniel <at> kraus.my>
>> +;; Maintainer: Philippe Estival <pe <at> 7d.nz>
>> ;; Keywords: literate programming, reproducible research
>> ;; URL: https://orgmode.org
>
> I think adding you as a maintainer should be a separate patch to make
> the change distinct.
Patch follows
>
>> (require 'ob)
>> +(require 'sql)
>
> Do we need to load sql.el early? May we do it dynamically instead?
>
I see that calling `org-require-package' when establishing a new
connection is more rational. Changed in the following patch.
>> +(defvar org-babel-sql-session-start-time)
>
> This variable is unused. What is it for?
>
Starting time of one session block execution, used to exit on timeout.
Fixed in the following patch.
What naming convention to opt for ? org-babel-[lang]-var ?
or ob-[lang]-var ?
>> +(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.")
>
> Should it be a defcustom?
>
Yes. It is now in the next patch.
>> +(defvar org-sql-session-command-terminated nil)
>
> AFAIU, this is some kind of flag to be used inside sql comit buffer.
> Should it be buffer-local?
Yes, it is now too.
> Also, please add a docstring.
>
>> +(defvar org-sql-session--batch-terminate "---#" "To print at the end of a command batch.")
>
> This is a bit weird formatting. In Elisp, the docstring is usually
> placed on a separate line. Also, please check out
> https://www.gnu.org/software/emacs/manual/html_node/elisp/Documentation-Tips.html
> Your docstring here reads confusing.
I hope the formatting and style of the others docstrings is now fine.
>
>> +(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.")
>
> This implies that you treat `org-sql-session--batch-terminate' as a
> constant. If so, maybe declare it as such.
>
> Also, should it be a defcustom?
>
>> +(defvar org-sql-terminal-command-prefix
>> + (list 'sqlite "\\."
>> + 'postgres "\\\\")
>> + "Identify a command for the SQL shell.")
>
> This variable is unused.
>
Right, it is no longer in the present mileage.
>> +(defvar org-sql-environment
>> + (list 'postgres '(("PGPASSWORD" sql-password))))
>
> Also unused.
>
removed.
>> +(defvar org-sql-session-clean-output nil
>> + "Store the regexp used to clear output (prompt1|termination|prompt2).")
>
> Should it be buffer-local in session buffer?
>
Yes.
>> +(defvar org-sql-session-start-time)
>> +(defvar org-sql-session-command-terminated nil)
>
> Same question.
>
>> +(defvar org-sql-session--batch-terminate "---#" "To print at the end of a command batch.")
>
> Duplicate defvar.
>
>> +(defcustom org-sql-run-comint-p 'nil
>
> There is no need to quote nil and numbers.
> They are self-quoting.
>
>> + "Run non-session SQL commands through comint if not nil."
>> + :type '(boolean)
>
> :type 'boolean
>
>> +(defcustom org-sql-timeout '5.0
>> + "Abort on timeout."
>> + :type '(number)
>
> :type 'number
>
>> +(defcustom org-sql-close-out-temp-buffer-p 'nil
>> + "To automatically close sql-out-temp buffer."
>
> The docstring is not very clear.
>
Changed to "When non-nil, close the temporary buffer holding output results."
>> + :type '(boolean)
>
> :type 'boolean
>
>> +(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)))
>
> Using global `org-sql-session-command-terminated',
> `org-sql-session-start-time', and "*ob-sql-result*" buffer will not be
> reliable when there are multiple comint sessions. It will also make it
> impossible to implement async sessions.
> Please use some other approach (for example, buffer-local or
> process-local variables)
>
>> * lisp/ob-sql.el: removal of org-assert-version makes org-macs no
>> longer needed.
>> ...
>> -
>> -(require 'org-macs)
>> -(org-assert-version)
>> -
>
> (org-assert-version) is needed to detect mixed Org versions.
> (require 'org-macs) there is mostly to provide `org-assert-version'.
>
I reintroduced `org-macs to provide `org-require-package in the following
patches. But shouldn't `org-assert-version be checked only once when
org core loads instead of subsequent modules?
>> Subject: [PATCH 04/14] ob-sql: realign variables for improved readability.
>
> We do not allow whitespace-only commits. See
> https://orgmode.org/worg/org-contribute.html#orgbbd6fd6
>
>> + (when colnames-p (with-temp-buffer
>
> nitpick: It is unusual to place
> (when condition body-line1
> ...)
>
> The common practice is
> (when condition
> body)
>
Ok, fixed.
>> + (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 "\\)"))))))
>
> This will be broken when multiple sessions for different engines are
> used in parallel. Please avoid global state.
>
Set as local now.
>> + (let ((sql--buffer
>> + (org-babel-sql-session-connect in-engine params session)))
>
> Why `sql--bufer'? (double slash) It is not a variable sql.el uses.
>
Replaced by `ob-sql-buffer'.
IIUC, a double dash indicates a variable used by the prefixed package?
>> + (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)
>
> This looks repetitive with the code in
> `org-sql-session-comint-output-filter'. Could it be possible to avoid
> code duplication?
>
True, the evaluation of the timeout was dup.
>> + (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))))
>
> Maybe we should wait until it is finished then? You can check
> `process-status'.
To be fixed next time
>> + (insert-for-yank
>
> Why do you need `insert-for-yank'?
No need. It's rather insert instead.
Cheers,
--
Phil
[0001-ob-sql-maintainer.patch (text/x-diff, attachment)]
[0002-ob-sql-vars-local.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001 From: Phil Estival <pe <at> 7d.nz> Date: Wed, 13 June 2025 17:00:00 +0200 Subject: [PATCH 02/08] ob-sql: add session support lisp/ob-sql.el: variables for session support to ob-sql * ob-sql.el: declare variables and local variables for termination of a command batch, starting time of command execution for timeout, regexp to clear returned output --- diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el index 14ca6bc48..caee4502a 100644 --- a/lisp/ob-sql.el +++ b/lisp/ob-sql.el @@ -76,6 +77,23 @@ (require 'ob) +(defvar org-sql-session--batch-terminate "---#" + "String echoed by the SQL 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-local org-sql-session-command-terminated nil + "Non-nil when a command batch has completed.") + +(defvar-local org-sql-session-start-time nil + "Starting time of a session code block execution, used to exit on timeout.") + +(defvar-local org-sql-session-clean-output nil + "Store the regexp used to clear output (prompt1|termination|prompt2).") + (declare-function org-table-import "org-table" (file arg)) (declare-function orgtbl-to-csv "org-table" (table params)) (declare-function org-table-to-lisp "org-table" (&optional txt))
[0003-ob-sql-custom-vars.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe <at> 7d.nz>
Date: Wed, 13 June 2025 17:00:00 +0200
Subject: [PATCH 03/08] ob-sql: add session support
lisp/ob-sql.el: custom variables for session support to ob-sql
* ob-sql.el: declare custom variables
default header argument per SQL engine,
if non-session commands should also be run through comint,
timeout duration of commands in seconds,
if the temporary buffers of results are closed automatically
---
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..caee4502a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -83,7 +99,49 @@
(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-session-preamble
+ (list
+ 'postgres "
+\\set ON_ERROR_STOP 1
+\\pset footer off
+\\pset pager off
+\\pset format unaligned"
+ 'sqlite "
+.header off")
+ "Command preamble to run upon shell start."
+
+ :type '(plist :key-type symbol :value-type string
+ :options ('postgres 'sqlite 'dbi 'mysql
+ '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."
+ :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
+ "When non-nil, close the temporary buffer holding output results."
+ :type 'boolean
+ :group 'org-babel-sql
+ :safe t)
(defconst org-babel-header-args:sql
'((engine . :any)
[0004-ob-sql-fix-prologue-epilogue.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe <at> 7d.nz>
Date: Wed, 13 June 2025 17:00:00 +0200
Subject: [PATCH 04/08] ob-sql: clear nil values from prologue and epilogue
---
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..caee4502a 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -101,11 +159,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)
[0005-ob-sql-factorize-shell-quoting.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe <at> 7d.nz>
Date: Wed, 13 June 2025 17:00:00 +0200
Subject: [PATCH 05/08] ob-sql: add session support
lisp/ob-sql.el: factorize shell quoting of connection parameters to SQL clients
@@ -117,11 +175,10 @@ corresponding :engine source block header argument."
(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."
- (mapconcat
- #'identity
+ (combine-and-quote-strings
(delq nil
- (list (when host (concat "-h" (shell-quote-argument host)))
+ (list (when host (concat "-h" host))
(when port (format "-P%d" port))
- (when user (concat "-u" (shell-quote-argument user)))
- (when password (concat "-p" (shell-quote-argument password)))
- (when database (concat "-D" (shell-quote-argument database))))) " "))
+ (when user (concat "-u" user))
+ (when password (concat "-p" password))
+ (when database (concat "-D" database))))))
(defun org-babel-sql-dbstring-postgresql (host port user database)
"Make PostgreSQL command line args for database connection.
Pass nil to omit that arg."
- (mapconcat
- #'identity
+ (combine-and-quote-strings
(delq nil
- (list (when host (concat "-h" (shell-quote-argument host)))
+ (list (when host (concat "-h" host))
(when port (format "-p%d" port))
- (when user (concat "-U" (shell-quote-argument user)))
- (when database (concat "-d" (shell-quote-argument database))))) " "))
+ (when user (concat "-U" user))
+ (when database (concat "-d" database))))))
(defun org-babel-sql-dbstring-oracle (host port user password database)
"Make Oracle command line arguments for database connection.
[0006-ob-sql-connect-and-comint-filter.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe <at> 7d.nz>
Date: Wed, 13 June 2025 17:00:00 +0200
Subject: [PATCH 06/08] ob-sql: add session support
lisp/ob-sql.el: preparation of session support to ob-sql
* ob-sql.el: add `org-babel-sql-session-connect' and `org-sql-session-comint-output-filter'
@@ -418,6 +508,79 @@ argument mechanism."
-(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)))
+ (org-require-package 'sql)
+ ;; 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-local 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.
+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-local org-sql-session-command-terminated t))
+
+ (with-current-buffer (get-buffer-create "*ob-sql-result*")
+ (insert string)))
+
(provide 'ob-sql)
[0007-ob-sql-session-exec.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe <at> 7d.nz>
Date: Wed, 13 June 2025 17:00:00 +0200
Subject: [PATCH 07/08] ob-sql: add session support
lisp/ob-sql.el: add execution of session support to ob-sql
* ob-sql.el: connect to an SQL shell and execute command block.
Replaces calls to (intern engine) by variable `in-engine',
Replace a unique cond by a when
Close temporary buffer when org-sql-close-out-temp-buffer-p is non-nil.
@@ -245,28 +293,71 @@ 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 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)))
- (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)))
+ (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 "")
- (command (cl-case (intern engine)
+ (session (cdr (assoc :session params)))
+ (session-p (not (string= session "none"))))
+
+ (if (or session-p org-sql-run-comint-p) ; run through comint
+ (let* ((ob-sql-buffer (org-babel-sql-session-connect in-engine params session))
+ (proc (get-buffer-process ob-sql-buffer)))
+ (setq-local org-sql-session-start-time (current-time))
+ (setq-local org-sql-session-command-terminated nil)
+ (with-current-buffer (get-buffer ob-sql-buffer)
+ (progn
+ (goto-char (point-max))
+ (insert
+ (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 (not org-sql-session-command-terminated)
+ (sleep-for 0.03))
+ ;; command finished, remove filter
+ (set-process-filter proc nil)
+
+ (when (not session-p)
+ (comint-quit-subjob)
+ (while (not (eq 'exit (process-status proc)))
+ (sleep-for 0.1))
+ ;; 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 in-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)))
+ (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)
@@ -332,7 +421,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
@@ -356,23 +445,22 @@ 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)))
(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
- (cond
- (colnames-p
- (with-temp-buffer
+ (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)))))
+ (write-file out-file))))
(t
;; Need to figure out the delimiter for the header row
(with-temp-buffer
@@ -388,6 +476,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)
[0008-ob-sql-session-commentary.patch (text/x-diff, inline)]
From e27f8231c2f97e6f7f84b6acb793eec7060b8396 Mon Sep 17 00:00:00 2001 From: Phil Estival <pe <at> 7d.nz> Date: Wed, 13 June 2025 17:00:00 +0200 Subject: [PATCH 08/08] ob-sql: add session support lisp/ob-sql.el: Commentary of session support to ob-sql @@ -36,6 +37,7 @@ ;; Header args used: ;; - engine ;; - cmdline +;; - sessions ;; - dbhost ;; - dbport ;; - dbuser @@ -58,13 +60,20 @@ ;; - mssql ;; - sqsh ;; - postgresql (postgres) +;; - sqlite ;; - oracle ;; - vertica ;; - saphana ;; +;; Limitation: +;; - sessions: +;; - engines configured: sqlite, postgres +;; - no error line number (stays as LINE 1) +;; - default port number only +;; ;; TODO: ;; -;; - support for sessions ;; - support for more engines -;; - what's a reasonable way to drop table data into SQL? -;; +;; - babel tables as input +;; - raw replace result +;; - port number configuration for sessions ;;; Code:
GNU bug tracking system
Copyright (C) 1999 Darren O. Benham,
1997,2003 nCipher Corporation Ltd,
1994-97 Ian Jackson.