Ob-sql-session

https://github.com/flintforge/ob-sql-session

Orb Babel functions for SQL, with session

Overview

:PROPERTIES:
:header-args: sql-session :engine postgres :dbhost localhost :database test :dbuser (getenv "pguser")
:END:

In the absence of session, the scope of setting this search path is limited to one query

#+begin_src sql
  set search_path to test, public;
  show search_path;
#+end_src

SET
search_path
test, public

Then it gets back to default

#+begin_src sql
  show search_path;
#+end_src

search_path
“$user”, public

While of course it will be kept inside a continuing session

#+begin_src sql-session :var path="test" :session PG :results table
  set search_path to $path, public;
  show search_path;
#+end_src

#+begin_src sql-session :session PG :results table
  show search_path;
#+end_src
    

SET
search_path
test, public

search_path
test, public

Explanations

Org/ob-sql.el does not provide a session mode because source blocks are passed as an input file along the connexion arguments without any terminal, which is fast (see for instance man:psql, option -c).

ob-sql-mode.el was proposed as an alternative. It relies on sql-session to open a client connection, then performs a simple sql-redirect as execution of the sql source block, before cleaning the prompt.

But more interesting is this comment in comint: file:/usr/local/share/emacs/29.3/lisp/comint.el.gz::3570

Which brings several remarks:

• Session mode is only required when keeping a state.
• sql-redirect can perfectly handle many batches of commands at once, but relies on accept-process-output which is not the best way to handle redirections through comint since it can get clunky when not managing bursts of outputs or termination or longer execution times correctly. The problem comes from properly handling accept-process-output and termination.
• Relying on the detection of the prompt should not be necessary as long as comint can tell where last output began.
• What happen if we rely only on the prompt to detect a command termination but for batches of commands with buffered output and a prompt showing up on each command? Then there’s no way to detect when a batch finishes, except with some sort of IPC and by giving the job enough time to complete. Or adding a special command in the end.

We have two situations related to the client:

1. It returns a message from a command.
2. It is silent on the output, and we don’t want to echo every input, example: a drop on sqlite, a \set or quiet mode on psql.

We can conclude there are two solutions to run a SQL batch:

1. Split the batch, run commands one by one, Identify silent commands (starting with \ for psql), no semi-column at the end…), keep the execution on hold while the next command did not output, and add short frames for commands to complete buffered output.
2. add a termination command to the batch: an echo command for instance, that stays in the client, and not given to the db.

   Here we opted for solution 2).

   One drawback is that some client will report an error as related to the entire block of commands, and not narrowed to a given line. For such a case, where spotting precisely where an error occurs, it’s always possible to switch into the interactive buffer and provide the command one line at a time.

The following is reported to work on emacs 27 to 30, org-mode 9.6 and 9.7

DB tested:

• Sqlite
• Postgres

Comparison with the build-in ob-sql

ob-sql-session exists for session support, which is in the TODOs list of ob-sql.

• ob-sql command execution relies on org-babel-eval (→ process-file → call-process).
• ob-sql-session runs an inferior process (in which sqli-interactive-mode can be activated when needed). The process output is filtered (e.g. results and prompts). When a session is demanded, this shell stays open for further commands and can keep a state (typically, when given special SQL commands).

	ob-sql	ob-sql-session
Feat.	- cmdline	- support for sessions
	- colnames as header arg	- optionnal colnames
TODO
	- support for sessions	- colnames as header arg
	- support for more engines	- support for more engines
engines
supported
	- mysql	- Postgresql
	- dbi	- sqlite
	- mssql
	- sqsh
	- postgresql
	- oracle
	- vertica
	- saphana

• ob-sql defines org-babel-sql-dbstring-[engine] to be provided on a shell command line.
• ob-sql-session, likewise, has to define
  • a connection string,
  • the prompt,
  • and the terminal command prefix for a every supported SQL client shell (or “engines”)
  • requires sql.el. With the above defined, it should be compatible with most database of the sql.el’s zoo. maybe.
  • adapts sql-connect of sql.el by declaring a function ob-sql-connect, in order to prompt only for missing connection parameters.

Comparison with ob-sql-mode

ob-sql-mode :

• is simple : forward the sql source through `sql-redirect’
• has test suite
• but gives clunky output
• no :results table
• does not handle special sql engine client commands
• prompt again for connection parameters when restarting a session

ob-sql-session :

• handle large results
• results as tables
• header variables (:var)
• accept special commands given to a specific sql shell
• memorize login parameters
• prompt for interactive authentication only if there is a parameter left blank
• can provide password with-environment-variables
• provide some more tests

usage

#+begin_src elisp
  (load-file "./ob-sql-session.el")
#+end_src

Skip confirmations

#+begin_src elisp
  (defun do-org-confirm-babel-evaluations (lang body)
    (not
     (or
      (string= lang "elisp")
      (string= lang "sql-session"))))
  (setq org-confirm-babel-evaluate 'do-org-confirm-babel-evaluations)
#+end_src

sql-comint-sqlite in sql.el needs to accept nil database in order to run sqlite in memory (ob-sqlite has no session support either and requires a database (commit 68aa43885 merged in org 9.7: ob-sqlite: Use a transient in-memory database by default).

Test it:

#+begin_src sql-session :engine sqlite :results table :database test.db
  .headers on
  drop table test;
  create table test(a,b);
  insert into test values ("sqlite",sqlite_version());
  insert into test values (date(),time());
  select * from test;
#+end_src

Displaying header.

#+begin_src sql-session :engine sqlite  :database test.db :results table
  .headers on
  --create table test(x,y);
  delete from test;
  insert into test values ("sqlite",sqlite_version());
  insert into test values (date(),time());
  select * from test;
#+end_src

one	two
sqlite	3.40.1
2024-06-05	14:42:01

#+begin_src sql-session :engine sqlite :results table :database test.db :session A
  --delete from test;
  insert into test values ('sqlite','3.40');
  insert into test values (1,2);
  select * from test;
#+end_src

sqlite	3.40
1	2

#+begin_src sql-session :engine sqlite
  --drop table test;
  create table test(one text, two int);
  select format("sqlite %s",sqlite_version()), date(), time();
#+end_src

sqlite 3.40.1|2024-06-05|14:42:03

Returning error

#+begin_src sql-session :engine sqlite :database test.db
  create table test(a, b);
  drop table test;
#+end_src

Parse error: table test already exists
  create table test(a, b); drop table test;
               ^--- error here

#+begin_src sql-session :engine sqlite :database test.db :results output
  drop table test;
  create table test(one varchar(10), two smallint);
  insert into test values('hello', 1);
  insert into test values('world', 2);
  select * from test;
#+end_src

:

hello|1
world|2

In order to run sqlite in memory (for older versions of progmodes/sql.el)

sql-database can be nil and no option given to sql-comint-sqlite

(defun sql-comint-sqlite (product &optional options buf-name)
  "Create comint buffer and connect to SQLite."
  ;; Put all parameters to the program (if defined) in a list and call
  ;; make-comint.
  (let ((params
         (append options
                 (if (and sql-database ;; allows connection to in-memory database.
                          (not (string-empty-p sql-database)))
                     `(,(expand-file-name sql-database))))))
    (sql-comint product params buf-name)))

modified   lisp/progmodes/sql.el
@@ -5061,14 +5061,15 @@ sql-sqlite
   (interactive "P")
   (sql-product-interactive 'sqlite buffer))

-(defun sql-comint-sqlite (product options &optional buf-name)
+(defun sql-comint-sqlite (product &optional options buf-name)
   "Create comint buffer and connect to SQLite."
   ;; Put all parameters to the program (if defined) in a list and call
   ;; make-comint.
   (let ((params
          (append options
-                 (if (not (string= "" sql-database))
-                     `(,(expand-file-name sql-database))))))
+                 (if (and sql-database
+                         (not (string= "" sql-database)))
+                         `(,(expand-file-name sql-database))))))
     (sql-comint product params buf-name)))

Test it:

#+begin_src sql-session :engine sqlite

  create table test(an int, two char);
  SELECT *
    FROM sqlite_schema;
  select format("sqlite %s",sqlite_version()), date(), time();

#+end_src

:

table|test|test|2|CREATE TABLE test(an int, two char)
sqlite 3.40.1|2024-06-05|01:46:55

On a session

#+begin_src sql-session :engine sqlite :session A
  create table test(an int, two char);
#+end_src

#+begin_src sql-session :engine sqlite :session A
  select format("sqlite %s",sqlite_version()), date(), time();
#+end_src

Once a session is opened

#+begin_src sql-session :session PG :engine postgres :dbuser user :dbpassword password :dbhost host :databse db
  select current_user
#+end_src

The connexion parameters may be discarded when recalling an opened session

#+begin_src sql-session :session PG
  select current_user
#+end_src

They’ll be of course needed if the commands and queries are to be run independently and need to be able to initiate the connexion.

Test it on postgres

#+begin_src sql-session :dbhost ""

  select inet_client_addr(); -- no host=socket, empty result
  select localtime(0);
  select current_date, 'hello world';

#+end_src

inet_client_addr
localtime
17:09:35
current_date	?column?
2024-06-05	hello world

Session starts

#+begin_src sql-session :session A

  select inet_client_addr();
  select localtime(0), current_date;

#+end_src

inet_client_addr
localtime	current_date
17:10:16	2024-06-05

Error handling

#+begin_src sql-session :session A
  select current_date, 1;
  select err;
  select 'ok';
#+end_src

current_date	?column?
2024-06-05	1
ERROR: column “err” does not exist
LINE 1: select err;
^

Stored procedure

create or replace function test(valid boolean) returns text as
$$
  begin
    if valid then return true;
    else
      RAISE EXCEPTION '%', 'woops';
    end if;
  end

$$ stable language plpgsql;

select test(true);
select test(false);

CREATE FUNCTION

test

true

ERROR: woops

CONTEXT: PL/pgSQL function test(boolean) line 4 at RAISE

Variables

#+begin_src sql-session :engine sqlite :var x="3.0"
  select 1/$x;
#+end_src

0.333333333333333

Variables will also be substitued in litteral strings (eg ‘$var’).

Test against large output

drop sequence serial2;
Create sequence serial2 start $x;
select nextval('serial2'),array(select generate_series(0, 200)) from generate_series(0, 250);

• [X] pass

[1/3] TODO >

• [X] Provide password with-environment-variables
  • additionnal enviro if needed
• [ ] port number please
• [ ] merge into ob-sql

Publishing an org file on github

Turn code blocks to example

(save-excursion
  (replace-regexp "^#\\+RESULTS:\n" "" nil nil nil t)
  (goto-char (point-max))
  (replace-regexp "\\(\\#\\+begin_src sql.*$\\)"
                  "#+begin_example\n,\\1" nil nil nil t)
  (goto-char (point-max))
  (replace-regexp "\\(\\#\\+end_src\s*$\\)"
                  ",\\1\n#+end_example" nil nil nil t))

or vice-versa

(save-excursion
  (replace-regexp "#\\+begin_example\n\\(,#\\+begin_src sql.*$\\)"
                  "\\1" nil nil nil t)
  (goto-char (point-max))
  (replace-regexp "\\(,#\\+end_src\s*\n\\)#\\+end_example"
                  "\\1" nil nil nil t))