Finally figuring out some ob-sqlite stuff -- for worg?

Finally figuring out some ob-sqlite stuff -- for worg?

[Eric Abrahamsen]

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

Hi all,

This is about putting some more example stuff on Worg about using Org
and SQLite...

Years ago I realized that Org tables weren't really practical as
"proper" databases, storing large quantities of information. As tempting
as it was to keep everything in-Org, the tables get pretty unusable
after a hundred rows or so.

The obvious solution seemed to be using Org in conjunction with SQLite,
but the stuff on Worg was pretty basic and just didn't quite do what I
wanted it to do.

Years later... I finally went and figured out how to do what I wanted,
namely 1) using an Org table to insert new values into an existing
SQLite table, and 2) using a SQLite block to pull some rows out of a SQL
table into an Org table, then editing the values in the Org table and
inserting the rows back into the SQL table. In other words, using an
intermediary Org table as an editable view on the SQL table.

Those two things together have made Org+SQLite enormously useful to me,
and I'd like to add this information to Worg, with the attached patch
(or something like it).

Does anyone have any comments on it? Would someone help me apply it?

Thanks,
Eric


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-Add-more-examples-to-the-ob-sqlite-section.patch --]
[-- Type: text/x-patch, Size: 2489 bytes --]

From 50fe1a6319dc13b41326702f6ea566f2241e7e52 Mon Sep 17 00:00:00 2001
From: Eric Abrahamsen <eric@ericabrahamsen.net>
Date: Thu, 7 Nov 2019 16:08:54 -0800
Subject: [PATCH] Add more examples to the ob-sqlite section

* org-contrib/babel/languages/ob-doc-sqlite.org: Add tips on inserting
new values into existing tables, and using an intermediary Org table
to edit SQL tables.
---
 org-contrib/babel/languages/ob-doc-sqlite.org | 40 +++++++++++++++++++
 1 file changed, 40 insertions(+)

diff --git a/org-contrib/babel/languages/ob-doc-sqlite.org b/org-contrib/babel/languages/ob-doc-sqlite.org
index 2dafa1ab..0512c9a4 100644
--- a/org-contrib/babel/languages/ob-doc-sqlite.org
+++ b/org-contrib/babel/languages/ob-doc-sqlite.org
@@ -212,4 +212,44 @@ select n, count(*) from testtable group by n;
 | 10 |        2 |
 #+END_EXAMPLE
 
+If dropping/overwriting a table is undesirable, a temporary SQL table
+can be used to insert new values into an existing table:
 
+#+BEGIN_EXAMPLE
+,#+begin_src sqlite :db /tmp/rip.db :var orgtable=tableexample :colnames yes
+create temporary table temp_table(id int, n int);
+.mode csv testtable
+.import $orgtable temp_table
+insert into existing_table (id, n) select id,n from temp_table;
+,#+end_src
+#+END_EXAMPLE
+** Using Org tables as an updatable "view" on SQLite tables
+Org tables can be used to conveniently display some data from a SQLite
+table, allow the user to edit it in Org, and the re-insert the updated
+data into the underlying SQLite table. Do this by naming the results
+table, then using it as input to another SQLite block that updates
+rows. If your table has a primary key, you'll definitely want to use
+it to make sure the correct rows are edited.
+
+#+BEGIN_EXAMPLE
+,#+begin_src sqlite :db /tmp/reviews.db
+select id,title,rating from bookreview where rating is null;
+,#+end_src
+
+,#+name: ratings
+,#+RESULTS:
+|  5 | To Kill a Mockingbird | null |
+| 12 | Three Body Problem    | null |
+
+,#+begin_src sqlite :db /tmp/reviews.db :var ratings=ratings
+create temporary table updates (id, title, rating);
+.mode csv updates
+.import $ratings updates
+update bookreview set rating = (select rating from updates
+where bookreview.id = updates.id)
+where exists (select * from updates where updates.id = bookreview.id);
+#+END_EXAMPLE
+
+By editing the intermediary table to replace "null" values with a
+numerical rating, and then running the second source block, the SQLite
+table will be updated correctly.
-- 
2.24.0

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Fraga, Eric]

This looks quite useful and would be nice to have on Worg.  Thanks.
-- 
Eric S Fraga via Emacs 27.0.50, Org release_9.2.6-552-g8c5a78

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Thomas S. Dye]

Aloha Eric,

Good news.  Yes, please feel free to update the Worg SQLite page.

IIRC, you can get permission from Bastien to push changes and then 
you can edit Worg at will.

All the best,
Tom

--
Thomas S. Dye
http://tsdye.online/tsdye

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Eric Abrahamsen]

"Thomas S. Dye" <tsd@tsdye.online> writes:

> Aloha Eric,
>
> Good news.  Yes, please feel free to update the Worg SQLite page.
>
> IIRC, you can get permission from Bastien to push changes and then you
> can edit Worg at will.

Cool! Bastien, can you help me get write access to Worg?

Thanks,
Eric

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Eric Abrahamsen]

"Fraga, Eric" <e.fraga@ucl.ac.uk> writes:

> This looks quite useful and would be nice to have on Worg.  Thanks.

Okay, it's up. If anyone wants to explain to me the point of the "where
exists" clause in the SQL, I would be interested to hear. It works as
expected this way, but is that clause necessary?

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Stefan Nobis]

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> Okay, it's up. If anyone wants to explain to me the point of the
> "where exists" clause in the SQL, I would be interested to hear. It
> works as expected this way, but is that clause necessary?

Yes, very necessary. Without it, all ratings would be changed - the
two example rows without ratings (ids 5 and 12) would get the values
from the intermediary org table, every other row in table bookreview
would get its rating attribute set to null (because there is no
matching entry in the temporary updates table).

Remember: update without a where clause always touches every single
row of the complete table.

The "where exists" clause ensures that only those rows of bookreviews
are touched that are present in the intermediary org table. If you do
not like "where exists" you could say "where bookreview.id in (select
id from udpates)".

-- 
Until the next mail...,
Stefan.

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Thomas S. Dye]

> Okay, it's up. If anyone wants to explain to me the point of the 
> "where
> exists" clause in the SQL, I would be interested to hear. It 
> works as
> expected this way, but is that clause necessary?

The 'if exists' clause protects against an SQLite error raised if 
you ask to delete a table that doesn't exist.  The code will work 
without it if the table exists, but will exit without creating the 
table if not.

Thanks for a useful addition to the Org babel SQLite 
documentation.  I agree with you that Org mode tables are a 
convenient way to enter SQL data, keeping in mind that table 
columns can't be rearranged without changes to the SQL code.

All the best,
Tom

--
Thomas S. Dye
http://tsdye.online/tsdye

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Eric Abrahamsen]

Stefan Nobis <stefan-ml@snobis.de> writes:

> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>
>> Okay, it's up. If anyone wants to explain to me the point of the
>> "where exists" clause in the SQL, I would be interested to hear. It
>> works as expected this way, but is that clause necessary?
>
> Yes, very necessary. Without it, all ratings would be changed - the
> two example rows without ratings (ids 5 and 12) would get the values
> from the intermediary org table, every other row in table bookreview
> would get its rating attribute set to null (because there is no
> matching entry in the temporary updates table).
>
> Remember: update without a where clause always touches every single
> row of the complete table.
>
> The "where exists" clause ensures that only those rows of bookreviews
> are touched that are present in the intermediary org table. If you do
> not like "where exists" you could say "where bookreview.id in (select
> id from udpates)".

Beautiful, this explains it perfectly. I had the sense that was the
purpose, but my very straightforward programming brain was insisting
that that job should be done with something like your final tip above:
update only if the id is in the updates table.

I was confused in part because the "where exists (select *..." looks
like its main purpose is to return rows. Will the select subquery
actually restrict the values that are available for updating/comparison
in the update statement? Or does the "exists" mean the subquery is
treated as a plain yes/no boolean, and the update still has access to
anything it likes? Ie, we could write "where exists (select <foo>" to
the same effect? Hope that's clear!

In essence, the "where exists" is acting as an "inner join"...

Sorry for the off-topic SQL detour! And thanks very much for this cogent
explanation.

Eric

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Stefan Nobis]

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> I was confused in part because the "where exists (select *..." looks
> like its main purpose is to return rows.

Indeed that's the purpose: Restrict the set of rows upon which update
acts on. Here I tried to reformat the statement a bit in order to
emphasize its structure:

#+begin_src sql
  UPDATE bookreview
  SET rating = (select rating from updates
                where bookreview.id = updates.id)
  WHERE EXISTS (select * from updates
                where updates.id = bookreview.id);
#+end_src

The subselect of the "SET rating" part is a correlated subquery. So if
you imagine UPDATE as a kind of loop over the table, the subquery of
the SET part is executed once for every row UPDATE acts on (maybe the
SQL execution engine optimizes this in some kind, but the mental model
here is: run the subquery for every row we visit on our journey
throught the table).

Only the WHERE EXISTS clause belonging directly to the UPDATE
statement will reduce the set of rows to act on.

> Will the select subquery actually restrict the values that are
> available for updating/comparison in the update statement?

No.

> Or does the "exists" mean the subquery is treated as a plain yes/no
> boolean, and the update still has access to anything it likes? We
> could write "where exists (select <foo>" to the same effect?

Yes. The SELECT clause of an EXISTS subquery (as in the above example)
is rather meaningless. So somethimes you see constructs like "where
exists (select 1 from ...)". Some SQL engines are not very clever and
execute the subquery of such an EXISTS clause unchanged - meaning that
way too much data is fetched for the intermediate result (unnecessary
IO and maybe polluting caches). Thus the "select 1" as a workaround
for those unclever engines. But current engines should have no
problems with optimizing these EXISTS subqueries and in that case it
does not matter how the select clause looks like - it will be ignored.

> In essence, the "where exists" is acting as an "inner join"...

Yes, effectively we are simulating an inner join at this point. Sadly,
many SQL engines are not able to update rows of join constructs (or at
least have quite severe constraints in these cases). Thus we need to
build these kinds of workarounds to change data in more complex cases.

SQL is quite a capable language, but it has also has some rough edges.
:)

-- 
Until the next mail...,
Stefan.

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Eric Abrahamsen]

Stefan Nobis <stefan-ml@snobis.de> writes:

> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>
>> I was confused in part because the "where exists (select *..." looks
>> like its main purpose is to return rows.
>
> Indeed that's the purpose: Restrict the set of rows upon which update
> acts on. Here I tried to reformat the statement a bit in order to
> emphasize its structure:

Right -- I should have phrased that as "looks like its main purpose is
to return data from rows", which as you clarify below, isn't its main
purpose.

> #+begin_src sql
>   UPDATE bookreview
>   SET rating = (select rating from updates
>                 where bookreview.id = updates.id)
>   WHERE EXISTS (select * from updates
>                 where updates.id = bookreview.id);
> #+end_src
>
> The subselect of the "SET rating" part is a correlated subquery. So if
> you imagine UPDATE as a kind of loop over the table, the subquery of
> the SET part is executed once for every row UPDATE acts on (maybe the
> SQL execution engine optimizes this in some kind, but the mental model
> here is: run the subquery for every row we visit on our journey
> throught the table).
>
> Only the WHERE EXISTS clause belonging directly to the UPDATE
> statement will reduce the set of rows to act on.
>
>> Will the select subquery actually restrict the values that are
>> available for updating/comparison in the update statement?
>
> No.
>
>> Or does the "exists" mean the subquery is treated as a plain yes/no
>> boolean, and the update still has access to anything it likes? We
>> could write "where exists (select <foo>" to the same effect?
>
> Yes. The SELECT clause of an EXISTS subquery (as in the above example)
> is rather meaningless. So somethimes you see constructs like "where
> exists (select 1 from ...)". Some SQL engines are not very clever and
> execute the subquery of such an EXISTS clause unchanged - meaning that
> way too much data is fetched for the intermediate result (unnecessary
> IO and maybe polluting caches). Thus the "select 1" as a workaround
> for those unclever engines. But current engines should have no
> problems with optimizing these EXISTS subqueries and in that case it
> does not matter how the select clause looks like - it will be ignored.
>
>> In essence, the "where exists" is acting as an "inner join"...
>
> Yes, effectively we are simulating an inner join at this point. Sadly,
> many SQL engines are not able to update rows of join constructs (or at
> least have quite severe constraints in these cases). Thus we need to
> build these kinds of workarounds to change data in more complex cases.
>
> SQL is quite a capable language, but it has also has some rough edges.
> :)

Really interesting! Thanks again for the in-depth explanation

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Eric Abrahamsen]

Stefan Nobis <stefan-ml@snobis.de> writes:

> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>
>> I was confused in part because the "where exists (select *..." looks
>> like its main purpose is to return rows.
>
> Indeed that's the purpose: Restrict the set of rows upon which update
> acts on. Here I tried to reformat the statement a bit in order to
> emphasize its structure:

Right -- I should have phrased that as "looks like its main purpose is
to return data from rows", which as you clarify below, isn't its main
purpose.

> #+begin_src sql
>   UPDATE bookreview
>   SET rating = (select rating from updates
>                 where bookreview.id = updates.id)
>   WHERE EXISTS (select * from updates
>                 where updates.id = bookreview.id);
> #+end_src
>
> The subselect of the "SET rating" part is a correlated subquery. So if
> you imagine UPDATE as a kind of loop over the table, the subquery of
> the SET part is executed once for every row UPDATE acts on (maybe the
> SQL execution engine optimizes this in some kind, but the mental model
> here is: run the subquery for every row we visit on our journey
> throught the table).
>
> Only the WHERE EXISTS clause belonging directly to the UPDATE
> statement will reduce the set of rows to act on.
>
>> Will the select subquery actually restrict the values that are
>> available for updating/comparison in the update statement?
>
> No.
>
>> Or does the "exists" mean the subquery is treated as a plain yes/no
>> boolean, and the update still has access to anything it likes? We
>> could write "where exists (select <foo>" to the same effect?
>
> Yes. The SELECT clause of an EXISTS subquery (as in the above example)
> is rather meaningless. So somethimes you see constructs like "where
> exists (select 1 from ...)". Some SQL engines are not very clever and
> execute the subquery of such an EXISTS clause unchanged - meaning that
> way too much data is fetched for the intermediate result (unnecessary
> IO and maybe polluting caches). Thus the "select 1" as a workaround
> for those unclever engines. But current engines should have no
> problems with optimizing these EXISTS subqueries and in that case it
> does not matter how the select clause looks like - it will be ignored.
>
>> In essence, the "where exists" is acting as an "inner join"...
>
> Yes, effectively we are simulating an inner join at this point. Sadly,
> many SQL engines are not able to update rows of join constructs (or at
> least have quite severe constraints in these cases). Thus we need to
> build these kinds of workarounds to change data in more complex cases.
>
> SQL is quite a capable language, but it has also has some rough edges.
> :)

Really interesting! Thanks again for the in-depth explanation.

Re: Finally figuring out some ob-sqlite stuff -- for worg?

[Bastien]

Hi Eric,

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> This is about putting some more example stuff on Worg about using Org
> and SQLite...

Please go ahead and add this -- no need to ask for permission, Worg is
not too sensible :)

Thanks,

-- 
 Bastien