From: Eric Abrahamsen <eric@ericabrahamsen.net>
To: emacs-orgmode@gnu.org
Subject: Finally figuring out some ob-sqlite stuff -- for worg?
Date: Thu, 07 Nov 2019 16:10:45 -0800 [thread overview]
Message-ID: <87bltn5it6.fsf@ericabrahamsen.net> (raw)
[-- 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
next reply other threads:[~2019-11-08 0:11 UTC|newest]
Thread overview: 12+ messages / expand[flat|nested] mbox.gz Atom feed top
2019-11-08 0:10 Eric Abrahamsen [this message]
2019-11-08 6:59 ` Finally figuring out some ob-sqlite stuff -- for worg? Fraga, Eric
2019-11-09 16:50 ` Eric Abrahamsen
2019-11-09 17:28 ` Stefan Nobis
2019-11-09 17:58 ` Eric Abrahamsen
2019-11-10 9:44 ` Stefan Nobis
2019-11-10 19:48 ` Eric Abrahamsen
2019-11-10 19:48 ` Eric Abrahamsen
2020-02-12 9:15 ` Bastien
-- strict thread matches above, loose matches on Subject: below --
2019-11-08 7:06 Thomas S. Dye
2019-11-08 16:20 ` Eric Abrahamsen
2019-11-09 17:38 Thomas S. Dye
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://www.orgmode.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=87bltn5it6.fsf@ericabrahamsen.net \
--to=eric@ericabrahamsen.net \
--cc=emacs-orgmode@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).