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

[Eric Abrahamsen <eric@ericabrahamsen.net>]

[-- 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