[PATCH] ob-sqlite: Use a transient in-memory database by default

[PATCH] ob-sqlite: Use a transient in-memory database by default

[Rudolf Adamkovič]

* etc/ORG-NEWS (New features): Add a news entry.
* lisp/ob-sqlite.el (org-babel-execute:sqlite): Default ':db' to ":memory:".
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-file): Test the old behavior.
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-memory): Test the new behavior.
---
 etc/ORG-NEWS                   |  6 ++++++
 lisp/ob-sqlite.el              |  3 +--
 testing/lisp/test-ob-sqlite.el | 36 ++++++++++++++++++++++++++++++++--
 3 files changed, 41 insertions(+), 4 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 03894f128..42bfc2be0 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -181,6 +181,12 @@ official [[https://clojure.org/guides/deps_and_cli][Clojure CLI tools]].
 The command can be customized with ~ob-clojure-cli-command~.
 
 ** New features
+*** Make =ob-sqlite= use in-database by default
+
+SQLite source blocks with no =:db= argument now execute against a
+transient in-memory database.  This makes SQLite source blocks more
+practical, and it matches the default behavior of the =sqlite3= shell.
+
 *** Add support for ~logind~ idle time in ~org-user-idle-seconds~
 
 When Emacs is built with =dbus= support and
diff --git a/lisp/ob-sqlite.el b/lisp/ob-sqlite.el
index 526b73ebd..60da488f7 100644
--- a/lisp/ob-sqlite.el
+++ b/lisp/ob-sqlite.el
@@ -66,7 +66,7 @@
   "Execute a block of Sqlite code with Babel.
 This function is called by `org-babel-execute-src-block'."
   (let ((result-params (split-string (or (cdr (assq :results params)) "")))
-	(db (cdr (assq :db params)))
+	(db (or (cdr (assq :db params)) ":memory:"))
 	(separator (cdr (assq :separator params)))
 	(nullvalue (cdr (assq :nullvalue params)))
 	(headers-p (equal "yes" (cdr (assq :colnames params))))
@@ -74,7 +74,6 @@ This function is called by `org-babel-execute-src-block'."
 			   (lambda (arg) (car (assq arg params)))
 			   (list :header :echo :bail :column
 				 :csv :html :line :list)))))
-    (unless db (error "ob-sqlite: can't evaluate without a database"))
     (with-temp-buffer
       (insert
        (org-babel-eval
diff --git a/testing/lisp/test-ob-sqlite.el b/testing/lisp/test-ob-sqlite.el
index 72d75c9b7..b76a82d98 100644
--- a/testing/lisp/test-ob-sqlite.el
+++ b/testing/lisp/test-ob-sqlite.el
@@ -39,8 +39,40 @@
   .import $tb TestTable
   select * from TestTable;
 #+end_src"
-	    (org-babel-next-src-block)
-	    (org-babel-execute-src-block)))))
+	   (org-babel-next-src-block)
+	   (org-babel-execute-src-block)))))
+
+(ert-deftest ob-sqlite/in-memory ()
+  "Test in-memory temporariness."
+  (should
+   (equal 0
+          (progn
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version = 1;
+#+END_SRC"
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     "#+begin_src sqlite
+PRAGMA user_version;
+#+end_src"
+	     (org-babel-execute-src-block))))))
+
+(ert-deftest ob-sqlite/in-file ()
+  "Test in-file permanency."
+  (should
+   (equal 1
+          (let ((file (org-babel-temp-file "test" ".sqlite")))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version = 1;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))))))
 
 (provide 'test-ob-sqlite)
 ;;; test-ob-sqlite.el ends here
-- 
2.40.1

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Max Nikulin]

On 03/05/2023 19:59, Rudolf Adamkovič wrote:
> +++ b/etc/ORG-NEWS

Thank you for the patch.

>   ** New features
> +*** Make =ob-sqlite= use in-database by default
------------------------------^
Looks like a typo

> +++ b/lisp/ob-sqlite.el
...> -	(db (cdr (assq :db params)))
> +	(db (or (cdr (assq :db params)) ":memory:"))

I am unsure what is better, to add a fallback here or to add :db 
":memory" to the org-babel-default-header-args:sqlite variable.

> +  "Test in-file permanency."

Maybe persistence?

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Max Nikulin]

On 03/05/2023 22:12, Max Nikulin wrote:
> On 03/05/2023 19:59, Rudolf Adamkovič wrote:
>> +++ b/lisp/ob-sqlite.el
...
>> -    (db (cdr (assq :db params)))
>> +    (db (or (cdr (assq :db params)) ":memory:"))
> 
> I am unsure what is better, to add a fallback here or to add :db 
> ":memory" to the org-babel-default-header-args:sqlite variable.

Perhaps it is better to keep current behavior with error by default and 
just to recommend more prominently using file local header arguments

#+PROPERTY: header-args:sqlite :db ":memory"

Changing defaults is a compromise. Perhaps you mostly use src blocks as 
playground and in-memory database is really convenient for you. However 
missed :db file in a block creating tables may cause a *delayed* error 
that will happen on attempt to insert some data to these tables later. 
It is harder to figure out that :db parameter was forgotten several 
screens above. The cost of debugging might be higher than adding a 
property line to files where a user is experimenting with sqlite features.

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Ihor Radchenko]

Max Nikulin <manikulin@gmail.com> writes:

>> I am unsure what is better, to add a fallback here or to add :db 
>> ":memory" to the org-babel-default-header-args:sqlite variable.
>
> Perhaps it is better to keep current behavior with error by default and 
> just to recommend more prominently using file local header arguments
>
> #+PROPERTY: header-args:sqlite :db ":memory"
>
> Changing defaults is a compromise. Perhaps you mostly use src blocks as 
> playground and in-memory database is really convenient for you. However 
> missed :db file in a block creating tables may cause a *delayed* error 
> that will happen on attempt to insert some data to these tables later. 
> It is harder to figure out that :db parameter was forgotten several 
> screens above. The cost of debugging might be higher than adding a 
> property line to files where a user is experimenting with sqlite features.

We can simply display a warning when :db is not specified. Something
like

"ob-sql: :db header argument not specified.  Using in-memory database"

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Rudolf Adamkovič]

Max Nikulin <manikulin@gmail.com> writes:

> Perhaps it is better to keep current behavior with error by default
> and just to recommend [...] a user is experimenting with sqlite
> features.

This is exactly the case where the user SHOULD use

#+PROPERTY: header-args:sqlite :db <FILE>

to avoid mistyping <FILE> across their SQLite blocks.

[Hence, defaulting to ":memory:" would NOT hurt.]

Org is primarily about INTERACTIVE use, not batch processing, so I think
it would make sense to match the usefulness of the official INTERACTIVE
SQLite shell, which defaults to in-memory for good reasons.

Rudy
-- 
"Thinking is a momentary dismissal of irrelevancies."
-- Richard Buckminster Fuller, 1969

Rudolf Adamkovič <salutis@me.com> [he/him]
Studenohorská 25
84103 Bratislava
Slovakia

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Max Nikulin]

On 07/05/2023 03:40, Rudolf Adamkovič wrote:
> SQLite shell, which defaults to in-memory for good reasons.

Explicit :memory argument still suppresses the following warning:

> Connected to a transient *in-memory* *database*.
> Use ".open FILENAME" to reopen on a persistent database.

I am not an active sqlite user and I have never tried it from Org. 
However mostly I used it with files. The case for in-memory database was 
unit tests for python functions.

Babel allows to compute values of header arguments and I am not in favor 
of silently treat nil (due to e.g. a error in the called function) as 
disabled persistence. Ihor's idea with a warning is better from my point 
of view.

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Ihor Radchenko]

Rudolf Adamkovič <salutis@me.com> writes:

> Max Nikulin <manikulin@gmail.com> writes:
>
>> Perhaps it is better to keep current behavior with error by default
>> and just to recommend [...] a user is experimenting with sqlite
>> features.
>
> This is exactly the case where the user SHOULD use
>
> #+PROPERTY: header-args:sqlite :db <FILE>
>
> to avoid mistyping <FILE> across their SQLite blocks.
>
> [Hence, defaulting to ":memory:" would NOT hurt.]

I do not mind falling back to ":memory:" when :db is not specified, but
we should display a warning to notify the users. Maybe not as a warning
every time src block is executed, but at least via org-lint.

Rudolf, WDYT?

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Rudolf Adamkovič]

Ihor Radchenko <yantar92@posteo.net> writes:

> I do not mind falling back to ":memory:" when :db is not specified, but
> we should display a warning to notify the users. Maybe not as a warning
> every time src block is executed, but at least via org-lint.
>
> Rudolf, WDYT?

I am not a fan of making the use of in-memory databases into a "smell",
as per Org Lint, because it would communicate to the user that "this is
a potential issue that you should probably fix".  I think that is not
true, given that SQLite defaults to in-memory for interactive use. It is
common and useful.  Showing a warning has the same problem.

Now, showing a message, such as "Using in-memory database", could do,
but it would feel like spam.  The SQLite shell prints "Connected to a
transient in-memory database" exactly once, not on every (re-)query.
Perhaps there is a precedent somewhere in Org Babel, and we could do
something similar?

Rudy
-- 
"Music is the mathematics of the sense, mathematics is the music of the reason."
-- James Joseph Sylvester, 1814-1897

Rudolf Adamkovič <salutis@me.com> [he/him]
Studenohorská 25
84103 Bratislava
Slovakia

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Ihor Radchenko]

Rudolf Adamkovič <salutis@me.com> writes:

> I am not a fan of making the use of in-memory databases into a "smell",
> as per Org Lint, because it would communicate to the user that "this is
> a potential issue that you should probably fix".  I think that is not
> true, given that SQLite defaults to in-memory for interactive use. It is
> common and useful.  Showing a warning has the same problem.

As Max described, it might be a potential issue.
The safest way is when the user sets :db ":memory:"
org-babel-default-header-args:sqlite explicitly. We should not throw a
warning if :db ":memory:" is set by the user.

> Now, showing a message, such as "Using in-memory database", could do,
> but it would feel like spam.  The SQLite shell prints "Connected to a
> transient in-memory database" exactly once, not on every (re-)query.
> Perhaps there is a precedent somewhere in Org Babel, and we could do
> something similar?

Effectively, ob-sqlite starts a new SQLite shell for every src blocks
(sessions are not supported).

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Rudolf Adamkovič]

Ihor Radchenko <yantar92@posteo.net> writes:

> As Max described, it might be a potential issue.

How about (1) we merge the patch, and then
(2) we add the lint warning if/when someone
has the [hypothesized] problem?

Rudy
-- 
"One can begin to reason only when a clear picture has been formed in
the imagination."
-- Walter Warwick Sawyer, Mathematician's Delight, 1943

Rudolf Adamkovič <salutis@me.com> [he/him]
Studenohorská 25
84103 Bratislava
Slovakia

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Ihor Radchenko]

Rudolf Adamkovič <salutis@me.com> writes:

> Ihor Radchenko <yantar92@posteo.net> writes:
>
>> As Max described, it might be a potential issue.
>
> How about (1) we merge the patch, and then
> (2) we add the lint warning if/when someone
> has the [hypothesized] problem?

Maybe. After re-thinking, your change should not break any existing Org
files, except those that were already broken because of the missing :db.

However, I think that it will provide more freedom to users if you alter
org-babel-default-header-args:sqlite instead of hard-coding the default.
May you update the patch accordingly?

Also, can you update the docs at
https://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sqlite.html
? The current docs declare :db header arg as mandatory.

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Rudolf Adamkovič]

[-- Attachment #1: Type: text/plain, Size: 1051 bytes --]

Ihor Radchenko <yantar92@posteo.net> writes:

> However, I think that it will provide more freedom to users if you alter
> org-babel-default-header-args:sqlite instead of hard-coding the default.
> May you update the patch accordingly?

Please see the attached patch.

> Also, can you update the docs at
> https://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sqlite.html
> ? The current docs declare :db header arg as mandatory.

Ditto, please see the second attached patch.

P.S. #1: This is my first contribution to the
WORG, and I am not sure if the patch is OK.
I do ever not plan to contribute to the WORG,
as I am not a fan of the idea and think that
all built-in backends (and more!) should be
documented in the Org manual.

P.S. #2: The Table of Contents (TOC) on the
WORG is "jumpy" on Safari.  In fact, it has
always been problematic for me, in one way or
another.  Why cannot TOC be included at the
beginning of the document, like in standard
Org exports?  Org and Emacs manuals, with no
"smart" side bars, get it right, IMO.

Rudy


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-ob-sqlite-Use-a-transient-in-memory-database-by-defa.patch --]
[-- Type: text/x-patch, Size: 3244 bytes --]

From aac17ad21bf5fe85a57efd4183b80fdc5221d3aa Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Rudolf=20Adamkovi=C4=8D?= <salutis@me.com>
Date: Wed, 3 May 2023 14:59:03 +0200
Subject: [PATCH] ob-sqlite: Use a transient in-memory database by default

* etc/ORG-NEWS (New features): Add a news entry.
* lisp/ob-sqlite.el (org-babel-default-header-args:sqlite): Default
':db' to ":memory:".
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-file): Test the old behavior.
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-memory): Test the new behavior.
---
 etc/ORG-NEWS                   |  7 +++++++
 lisp/ob-sqlite.el              |  2 +-
 testing/lisp/test-ob-sqlite.el | 36 ++++++++++++++++++++++++++++++++--
 3 files changed, 42 insertions(+), 3 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 4f16eda24..40a4e79bd 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -489,6 +489,13 @@ Final hooks are added to the following commands:
 
 The prefix arguments are passed to ~org-insert-todo-heading~.
 
+*** Make ~ob-sqlite~ use in-database by default
+
+SQLite source blocks with no ~:db~ argument now execute against a
+transient in-memory database by default.  This makes Org match the
+default behavior of the ~sqlite3~ shell and makes SQLite blocks more
+practical out of the box.
+
 *** Add support for ~logind~ idle time in ~org-user-idle-seconds~
 
 When Emacs is built with =dbus= support and
diff --git a/lisp/ob-sqlite.el b/lisp/ob-sqlite.el
index 526b73ebd..75ef50913 100644
--- a/lisp/ob-sqlite.el
+++ b/lisp/ob-sqlite.el
@@ -39,7 +39,7 @@
 (declare-function orgtbl-to-csv "org-table" (table params))
 (declare-function org-table-to-lisp "org-table" (&optional txt))
 
-(defvar org-babel-default-header-args:sqlite '())
+(defvar org-babel-default-header-args:sqlite '((:db . ":memory:")))
 
 (defvar org-babel-header-args:sqlite
   '((db        . :any)
diff --git a/testing/lisp/test-ob-sqlite.el b/testing/lisp/test-ob-sqlite.el
index 72d75c9b7..621a11b0b 100644
--- a/testing/lisp/test-ob-sqlite.el
+++ b/testing/lisp/test-ob-sqlite.el
@@ -39,8 +39,40 @@
   .import $tb TestTable
   select * from TestTable;
 #+end_src"
-	    (org-babel-next-src-block)
-	    (org-babel-execute-src-block)))))
+	   (org-babel-next-src-block)
+	   (org-babel-execute-src-block)))))
+
+(ert-deftest ob-sqlite/in-memory ()
+  "Test in-memory temporariness."
+  (should
+   (equal 0
+          (progn
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version = 1;
+#+END_SRC"
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version;
+#+END_SRC"
+	     (org-babel-execute-src-block))))))
+
+(ert-deftest ob-sqlite/in-file ()
+  "Test in-file permanency."
+  (should
+   (equal 1
+          (let ((file (org-babel-temp-file "test" ".sqlite")))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version = 1;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))))))
 
 (provide 'test-ob-sqlite)
 ;;; test-ob-sqlite.el ends here
-- 
2.37.1 (Apple Git-137.1)


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #3: 0001-Document-the-new-default-value-of-the-Babel-SQLite-d.patch --]
[-- Type: text/x-patch, Size: 1226 bytes --]

From 2d756f51299131727a978da3bbdfbaaa3c67d36d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Rudolf=20Adamkovi=C4=8D?= <salutis@me.com>
Date: Sat, 5 Aug 2023 00:36:07 +0200
Subject: [PATCH] Document the new default value of the Babel/SQLite ':db'
 argument

---
 org-contrib/babel/languages/ob-doc-sqlite.org | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/org-contrib/babel/languages/ob-doc-sqlite.org b/org-contrib/babel/languages/ob-doc-sqlite.org
index d7febb0c..981eca34 100644
--- a/org-contrib/babel/languages/ob-doc-sqlite.org
+++ b/org-contrib/babel/languages/ob-doc-sqlite.org
@@ -91,7 +91,8 @@ There are no language-specific default header arguments for SQLite.
 There are 11 SQLite-specific header arguments.
 
  - db :: a string with the name of the file that holds the SQLite
-         database. Babel requires this header argument. 
+   database. Defaults to =":memory:"=, a special "file name" that
+   makes SQLite use a temporary in-memory database.
  - header :: if present, turn on headers in the output format. Headers
              are also output with the header argument =:colnames yes=.
  - echo :: if present, set the SQLite dot command =.echo= to =ON=.
-- 
2.37.1 (Apple Git-137.1)


[-- Attachment #4: Type: text/plain, Size: 304 bytes --]

-- 
"We shall not cease from exploration
 And the end of all our exploring
 Will be to arrive where we started
 And know the place for the first time"
--- T. S. Eliot, Little Gidding, Four Quarters, 1943

Rudolf Adamkovič <salutis@me.com> [he/him]
Studenohorská 25
84103 Bratislava
Slovakia

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Max Nikulin]

On 05/08/2023 05:57, Rudolf Adamkovič wrote:
> +*** Make ~ob-sqlite~ use in-database by default

"use in-memory database"

> +
> +SQLite source blocks with no ~:db~ argument now execute against a
> +transient in-memory database by default.

I am unsure, but perhaps it would be more clear to say that the default 
value of the header argument has changed and it is possible to omit 
setting :db per-block or as a header argument. It should give a hint how 
to revert this change in local configuration if somebody wish it.

>  This makes Org match the
> +default behavior of the ~sqlite3~ shell and makes SQLite blocks more
> +practical out of the box.
> +

Feel free to just ignore the following. Perhaps to get *really* default 
behavior ob-sqlite should not check :db value and should not pass it to 
the command in the case of nil. As a result the command spits a warning.

Earlier I was thinking on buffer-local variables whether the warning has 
been shown to the user (to do it once), but I think it would lead to 
unreasonable complication of code with a little value for users.

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Rudolf Adamkovič]

[-- Attachment #1: Type: text/plain, Size: 981 bytes --]

Max Nikulin <manikulin@gmail.com> writes:

> On 05/08/2023 05:57, Rudolf Adamkovič wrote:
>> +*** Make ~ob-sqlite~ use in-database by default
>
> "use in-memory database"

Oops!  Fixed.

>> +SQLite source blocks with no ~:db~ argument now execute against a
>> +transient in-memory database by default.
>
> I am unsure, but perhaps it would be more clear to say that the default 
> value of the header argument has changed and it is possible to omit 
> setting :db per-block or as a header argument. It should give a hint how 
> to revert this change in local configuration if somebody wish it.

Please see the attached Patch 0001A.

> Feel free to just ignore the following. Perhaps to get *really* default 
> behavior ob-sqlite should not check :db value and should not pass it to 
> the command in the case of nil.

This is a good idea!  Please see the attached
alternative Patch 0001B.

> As a result the command spits a warning.

It does?

Rudy

[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001A-ob-sqlite-Use-a-transient-in-memory-database-by-defa.patch --]
[-- Type: text/x-patch, Size: 3318 bytes --]

From 144bd75183a9185786248d9404ddeee4b93ed30f Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Rudolf=20Adamkovi=C4=8D?= <salutis@me.com>
Date: Wed, 3 May 2023 14:59:03 +0200
Subject: [PATCH] ob-sqlite: Use a transient in-memory database by default

* etc/ORG-NEWS (New features): Add a news entry.
* lisp/ob-sqlite.el (org-babel-default-header-args:sqlite): Default
':db' to ":memory:".
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-file): Test the old behavior.
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-memory): Test the new behavior.
---
 etc/ORG-NEWS                   |  8 ++++++++
 lisp/ob-sqlite.el              |  2 +-
 testing/lisp/test-ob-sqlite.el | 36 ++++++++++++++++++++++++++++++++--
 3 files changed, 43 insertions(+), 3 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 4f16eda24..569ec651b 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -489,6 +489,14 @@ Final hooks are added to the following commands:
 
 The prefix arguments are passed to ~org-insert-todo-heading~.
 
+*** Make ~ob-sqlite~ use in-memory databases by default
+
+The ~:db~ header argument of ~sqlite~ source blocks now defaults to
+~":memory:"~ which stands for a temporary in-memory database.  As a
+result, ~sqlite~ source blocks are usable out of the box, with no
+header arguments, matching the behavior and practicality of the
+official ~sqlite3~ shell.
+
 *** Add support for ~logind~ idle time in ~org-user-idle-seconds~
 
 When Emacs is built with =dbus= support and
diff --git a/lisp/ob-sqlite.el b/lisp/ob-sqlite.el
index 526b73ebd..75ef50913 100644
--- a/lisp/ob-sqlite.el
+++ b/lisp/ob-sqlite.el
@@ -39,7 +39,7 @@
 (declare-function orgtbl-to-csv "org-table" (table params))
 (declare-function org-table-to-lisp "org-table" (&optional txt))
 
-(defvar org-babel-default-header-args:sqlite '())
+(defvar org-babel-default-header-args:sqlite '((:db . ":memory:")))
 
 (defvar org-babel-header-args:sqlite
   '((db        . :any)
diff --git a/testing/lisp/test-ob-sqlite.el b/testing/lisp/test-ob-sqlite.el
index 72d75c9b7..621a11b0b 100644
--- a/testing/lisp/test-ob-sqlite.el
+++ b/testing/lisp/test-ob-sqlite.el
@@ -39,8 +39,40 @@
   .import $tb TestTable
   select * from TestTable;
 #+end_src"
-	    (org-babel-next-src-block)
-	    (org-babel-execute-src-block)))))
+	   (org-babel-next-src-block)
+	   (org-babel-execute-src-block)))))
+
+(ert-deftest ob-sqlite/in-memory ()
+  "Test in-memory temporariness."
+  (should
+   (equal 0
+          (progn
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version = 1;
+#+END_SRC"
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version;
+#+END_SRC"
+	     (org-babel-execute-src-block))))))
+
+(ert-deftest ob-sqlite/in-file ()
+  "Test in-file permanency."
+  (should
+   (equal 1
+          (let ((file (org-babel-temp-file "test" ".sqlite")))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version = 1;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))))))
 
 (provide 'test-ob-sqlite)
 ;;; test-ob-sqlite.el ends here
-- 
2.37.1 (Apple Git-137.1)


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #3: 0001B-ob-sqlite-Use-a-transient-in-memory-database-by-defa.patch --]
[-- Type: text/x-patch, Size: 3655 bytes --]

From 34f28236366affb510bfdb70a3577e765d9e0abb Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Rudolf=20Adamkovi=C4=8D?= <salutis@me.com>
Date: Wed, 3 May 2023 14:59:03 +0200
Subject: [PATCH] ob-sqlite: Use a transient in-memory database by default

* etc/ORG-NEWS (New features): Add a news entry.
* lisp/ob-sqlite.el (org-babel-execute:sqlite): Default ':db' to
":memory:" instead of throwing an error.
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-file): Test the old behavior.
* testing/lisp/test-ob-sqlite.el (ob-sqlite/in-memory): Test the new behavior.
---
 etc/ORG-NEWS                   |  8 ++++++++
 lisp/ob-sqlite.el              |  3 +--
 testing/lisp/test-ob-sqlite.el | 36 ++++++++++++++++++++++++++++++++--
 3 files changed, 43 insertions(+), 4 deletions(-)

diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index 4f16eda24..3426a5bc0 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -489,6 +489,14 @@ Final hooks are added to the following commands:
 
 The prefix arguments are passed to ~org-insert-todo-heading~.
 
+*** Make ~ob-sqlite~ use in-memory databases by default
+
+~sqlite~ source blocks with no ~:db~ header argument now make SQLite
+use a temporary in-memory database instead of throwing an error,
+matching the behavior of the official ~sqlite3~ shell.  As a result,
+~sqlite~ source blocks are now usable out of the box, that is with no
+header arguments.
+
 *** Add support for ~logind~ idle time in ~org-user-idle-seconds~
 
 When Emacs is built with =dbus= support and
diff --git a/lisp/ob-sqlite.el b/lisp/ob-sqlite.el
index 526b73ebd..7510e5158 100644
--- a/lisp/ob-sqlite.el
+++ b/lisp/ob-sqlite.el
@@ -74,7 +74,6 @@ This function is called by `org-babel-execute-src-block'."
 			   (lambda (arg) (car (assq arg params)))
 			   (list :header :echo :bail :column
 				 :csv :html :line :list)))))
-    (unless db (error "ob-sqlite: can't evaluate without a database"))
     (with-temp-buffer
       (insert
        (org-babel-eval
@@ -97,7 +96,7 @@ This function is called by `org-babel-execute-src-block'."
 			      (member :html others) separator)
 			  ""
 			"-csv"))
-	  (cons "db " db)))
+          (cons "db" (or db ""))))
 	;; body of the code block
 	(org-babel-expand-body:sqlite body params)))
       (org-babel-result-cond result-params
diff --git a/testing/lisp/test-ob-sqlite.el b/testing/lisp/test-ob-sqlite.el
index 72d75c9b7..621a11b0b 100644
--- a/testing/lisp/test-ob-sqlite.el
+++ b/testing/lisp/test-ob-sqlite.el
@@ -39,8 +39,40 @@
   .import $tb TestTable
   select * from TestTable;
 #+end_src"
-	    (org-babel-next-src-block)
-	    (org-babel-execute-src-block)))))
+	   (org-babel-next-src-block)
+	   (org-babel-execute-src-block)))))
+
+(ert-deftest ob-sqlite/in-memory ()
+  "Test in-memory temporariness."
+  (should
+   (equal 0
+          (progn
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version = 1;
+#+END_SRC"
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     "#+BEGIN_SRC sqlite
+PRAGMA user_version;
+#+END_SRC"
+	     (org-babel-execute-src-block))))))
+
+(ert-deftest ob-sqlite/in-file ()
+  "Test in-file permanency."
+  (should
+   (equal 1
+          (let ((file (org-babel-temp-file "test" ".sqlite")))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version = 1;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))
+            (org-test-with-temp-text
+	     (format "#+BEGIN_SRC sqlite :db %s
+PRAGMA user_version;
+#+END_SRC" file)
+	     (org-babel-execute-src-block))))))
 
 (provide 'test-ob-sqlite)
 ;;; test-ob-sqlite.el ends here
-- 
2.37.1 (Apple Git-137.1)


[-- Attachment #4: Type: text/plain, Size: 188 bytes --]

-- 
"'Obvious' is all too often a synonym for 'wrong'."
-- Jeff Erickson, Algorithms, 2019

Rudolf Adamkovič <salutis@me.com> [he/him]
Studenohorská 25
84103 Bratislava
Slovakia

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Ihor Radchenko]

Rudolf Adamkovič <salutis@me.com> writes:

> From 34f28236366affb510bfdb70a3577e765d9e0abb Mon Sep 17 00:00:00 2001
> From: =?UTF-8?q?Rudolf=20Adamkovi=C4=8D?= <salutis@me.com>
> Date: Wed, 3 May 2023 14:59:03 +0200
> Subject: [PATCH] ob-sqlite: Use a transient in-memory database by default
>
> * etc/ORG-NEWS (New features): Add a news entry.
> * lisp/ob-sqlite.el (org-babel-execute:sqlite): Default ':db' to
> ":memory:" instead of throwing an error.
> * testing/lisp/test-ob-sqlite.el (ob-sqlite/in-file): Test the old behavior.
> * testing/lisp/test-ob-sqlite.el (ob-sqlite/in-memory): Test the new behavior.

Thanks! Applied, onto main.
https://git.savannah.gnu.org/cgit/emacs/org-mode.git/commit/?id=68aa43885

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Max Nikulin]

On 06/08/2023 07:22, Rudolf Adamkovič wrote:
> * lisp/ob-sqlite.el (org-babel-execute:sqlite): Default ':db' to
> ":memory:" instead of throwing an error.

This commit message entry does not reflect changed approach clearly 
enough. Not passing :db is a bit different from default :memory:. Sorry 
that I have not noticed it earlier. Since the commit has been pushed, I 
do not expect any further action in response.

> @@ -97,7 +96,7 @@ This function is called by `org-babel-execute-src-block'."
>   			      (member :html others) separator)
>   			  ""
>   			"-csv"))
> -	  (cons "db " db)))
> +          (cons "db" (or db ""))))

My expectation was that sqlite3 should print a warning, but actually it 
does not.

echo "select 1" | sqlite3
1

sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

In this sense I would prefer the previous version of the patch, but I 
admit, it is my fault that I did not tried it despite a week passed.

[accessibility] worg obscures text (Re: [PATCH] ob-sqlite: Use a transient in-memory database by default)

[Max Nikulin]

On 05/08/2023 05:57, Rudolf Adamkovič wrote:
> P.S. #2: The Table of Contents (TOC) on the
> WORG is "jumpy" on Safari.  In fact, it has
> always been problematic for me, in one way or
> another.  Why cannot TOC be included at the
> beginning of the document, like in standard
> Org exports?  Org and Emacs manuals, with no
> "smart" side bars, get it right, IMO.

I have found your message in a dedicated thread

Rudolf Adamkovič to emacs-orgmode… Re: [accessibility] worg obscures 
text. Sun, 12 Jun 2022 21:35:14 +0200. 
https://list.org.mode.org/m2v8t5y8st.fsf@me.com

Somebody should propose a CSS for responsive design for Worg. Perhaps 
with ability to open/close table of contents on mobile devices.

Re: [accessibility] worg obscures text (Re: [PATCH] ob-sqlite: Use a transient in-memory database by default)

[Samuel Wales]

fwiw i agree with the non-fancy toc concept for accessibility.

On 8/4/23, Max Nikulin <manikulin@gmail.com> wrote:
> On 05/08/2023 05:57, Rudolf Adamkovič wrote:
>> P.S. #2: The Table of Contents (TOC) on the
>> WORG is "jumpy" on Safari.  In fact, it has
>> always been problematic for me, in one way or
>> another.  Why cannot TOC be included at the
>> beginning of the document, like in standard
>> Org exports?  Org and Emacs manuals, with no
>> "smart" side bars, get it right, IMO.
>
> I have found your message in a dedicated thread
>
> Rudolf Adamkovič to emacs-orgmode… Re: [accessibility] worg obscures
> text. Sun, 12 Jun 2022 21:35:14 +0200.
> https://list.org.mode.org/m2v8t5y8st.fsf@me.com
>
> Somebody should propose a CSS for responsive design for Worg. Perhaps
> with ability to open/close table of contents on mobile devices.
>
>


-- 
The Kafka Pandemic

A blog about science, health, human rights, and misopathy:
https://thekafkapandemic.blogspot.com

Re: [accessibility] worg obscures text (Re: [PATCH] ob-sqlite: Use a transient in-memory database by default)

[Samuel Wales]

[i.e. agree that the manuals and standard toc at top imo do a good job
for accessibility.]

On 8/4/23, Samuel Wales <samologist@gmail.com> wrote:
> fwiw i agree with the non-fancy toc concept for accessibility.
>
> On 8/4/23, Max Nikulin <manikulin@gmail.com> wrote:
>> On 05/08/2023 05:57, Rudolf Adamkovič wrote:
>>> P.S. #2: The Table of Contents (TOC) on the
>>> WORG is "jumpy" on Safari.  In fact, it has
>>> always been problematic for me, in one way or
>>> another.  Why cannot TOC be included at the
>>> beginning of the document, like in standard
>>> Org exports?  Org and Emacs manuals, with no
>>> "smart" side bars, get it right, IMO.
>>
>> I have found your message in a dedicated thread
>>
>> Rudolf Adamkovič to emacs-orgmode… Re: [accessibility] worg obscures
>> text. Sun, 12 Jun 2022 21:35:14 +0200.
>> https://list.org.mode.org/m2v8t5y8st.fsf@me.com
>>
>> Somebody should propose a CSS for responsive design for Worg. Perhaps
>> with ability to open/close table of contents on mobile devices.
>>
>>
>
>
> --
> The Kafka Pandemic
>
> A blog about science, health, human rights, and misopathy:
> https://thekafkapandemic.blogspot.com
>


-- 
The Kafka Pandemic

A blog about science, health, human rights, and misopathy:
https://thekafkapandemic.blogspot.com

Re: [accessibility] worg obscures text (Re: [PATCH] ob-sqlite: Use a transient in-memory database by default)

[Max Nikulin]

On 05/08/2023 10:22, Samuel Wales wrote:
> [i.e. agree that the manuals and standard toc at top imo do a good job
> for accessibility.]

Notice that Org manual is exported through ox-texinfo, not by ox-html 
directly. So somebody should confirm that default ox-html table of 
contents is suitable for various worg pages: short and long ones.

Personally I do not like CSS of the manuals (Org and Emacs) because it 
allows ~190 characters per line. For regular paragraphs it is 
recommended to have lines 60-70 characters long. Code blocks may be wider.

Re: [accessibility] worg obscures text (Re: [PATCH] ob-sqlite: Use a transient in-memory database by default)

[Samuel Wales]

yikes yes re columns.

[my fill-column is 60.  on a 32 inch.  and i cannot even do ediff side
by side with that, to my displeasure.]

[i hope to find a low-[preferably-1-nit]-minimum-brightness
flicker-free monitor someplace, and i /think/ oleds, which /might/
partly satisfy that, can come standardly in 42 inches, which /might/
allow side by side ediff at 60 finally.]


On 8/10/23, Max Nikulin <manikulin@gmail.com> wrote:
> On 05/08/2023 10:22, Samuel Wales wrote:
>> [i.e. agree that the manuals and standard toc at top imo do a good job
>> for accessibility.]
>
> Notice that Org manual is exported through ox-texinfo, not by ox-html
> directly. So somebody should confirm that default ox-html table of
> contents is suitable for various worg pages: short and long ones.
>
> Personally I do not like CSS of the manuals (Org and Emacs) because it
> allows ~190 characters per line. For regular paragraphs it is
> recommended to have lines 60-70 characters long. Code blocks may be wider.
>
>
>
>


-- 
The Kafka Pandemic

A blog about science, health, human rights, and misopathy:
https://thekafkapandemic.blogspot.com

Re: [PATCH] ob-sqlite: Use a transient in-memory database by default

[Ihor Radchenko]

Rudolf Adamkovič <salutis@me.com> writes:

> P.S. #1: This is my first contribution to the
> WORG, and I am not sure if the patch is OK.
> I do ever not plan to contribute to the WORG,
> as I am not a fan of the idea and think that
> all built-in backends (and more!) should be
> documented in the Org manual.

See https://orgmode.org/list/87lefgi58c.fsf@localhost

> P.S. #2: The Table of Contents (TOC) on the
> WORG is "jumpy" on Safari.  In fact, it has
> always been problematic for me, in one way or
> another.  Why cannot TOC be included at the
> beginning of the document, like in standard
> Org exports?  Org and Emacs manuals, with no
> "smart" side bars, get it right, IMO.

See https://orgmode.org/list/87cz7ubnxb.fsf_-_@gnu.org

-- 
Ihor Radchenko // yantar92,
Org mode contributor,
Learn more about Org mode at <https://orgmode.org/>.
Support Org development at <https://liberapay.com/org-mode>,
or support my work at <https://liberapay.com/yantar92>