ob-sql-session.org

#+TITLE: Ob-sql-session
[[https://github.com/flintforge/ob-sql-session/actions][file:https://github.com/flintforge/ob-sql-session/actions/workflows/CI.yml/badge.svg]]
#+author: Phil Estival pe@7d.nz
# date : [2024-05-29 Wed]
#+License: GPL3
https://github.com/flintforge/ob-sql-session

** Org Babel functions for SQL, with session
*** Overview
:PROPERTIES:
:header-args: sql-session :engine postgres :dbhost localhost :database test :session PG :results table
:END:

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


In the absence of session,
the scope of setting this search path is
limited to one query
 #+begin_example
,#+begin_src sql
  set search_path to test, public;
  show search_path;
,#+end_src
#+end_example


| SET          |
|--------------|
| search_path  |
| test, public |

Then it gets back to default
 #+begin_example
,#+begin_src sql
  show search_path;
,#+end_src
#+end_example


| search_path     |
|-----------------|
| "$user", public |


While of course it will be kept inside a continuing session
 #+begin_example
,#+begin_src sql-session :var path="test" :session PG :results table
  set search_path to $path, public;
  show search_path;
,#+end_src
#+end_example


| SET          |
| search_path  |
|--------------|
| test, public |

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


| 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_example
,#+begin_src elisp
  (load-file "./ob-sql-session.el")
,#+end_src
#+end_example

Skip confirmations
 #+begin_example
,#+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
#+end_example


=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_example
,#+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
#+end_example

#+RESULTS:
| a          |        b |
| sqlite     |   3.40.1 |
| 2024-06-24 | 11:57:56 |

Displaying header.
#+begin_example
,#+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
#+end_example

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


#+begin_example
,#+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
#+end_example

| sqlite | 3.40 |
|      1 |    2 |

#+begin_example
,#+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
#+end_example

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

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

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

#+begin_example
,#+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
#+end_example

:
: 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=

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



#+begin_src patch
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)))

#+end_src




Test it:
#+begin_example
,#+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
#+end_example

:
: 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_example
,#+begin_src sql-session :engine sqlite :session A
  create table test(an int, two char);
,#+end_src
#+end_example

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


*** Once a session is opened
#+begin_example
,#+begin_src sql-session :session PG :engine postgres :dbuser user :dbpassword password :dbhost host :databse db
  select current_user
,#+end_src
#+end_example

The connexion parameters may be discarded when recalling an opened session
#+begin_example
,#+begin_src sql-session :session PG
  select current_user
,#+end_src
#+end_example

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
:PROPERTIES:
:header-args: sql-session :engine postgres :database test :results table
:END:

#+begin_example
,#+begin_src sql-session :dbhost ""

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

,#+end_src
#+end_example

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


Session starts
#+begin_example
,#+begin_src sql-session :session A

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

,#+end_src
#+end_example

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


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

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


Stored procedure
#+begin_src sql-session :session A
  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);

#+end_src

| CREATE FUNCTION                                           |
| test                                                      |
| true                                                      |
| ERROR:  woops                                             |
| CONTEXT:  PL/pgSQL function test(boolean) line 4 at RAISE |

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

: 0.333333333333333

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

** Test against large output

#+begin_src sql :engine postgres :database test :var x=33
  drop sequence serial2;
  Create sequence serial2 start $x;
  select nextval('serial2'),array(select generate_series(0, 200)) from generate_series(0, 250);
#+end_src

- [X] pass

** [1/3] TODO >
- [X] Provide password [[file:/usr/share/emacs/28.2/lisp/env.el.gz::defmacro with-environment-variables][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
#+name: src->example
#+begin_src elisp
  (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))
#+end_src

or vice-versa
#+name: example->src
#+begin_src elisp

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

#+call: src->example()
#+call: example->src()