emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* Finally figuring out some ob-sqlite stuff -- for worg?
@ 2019-11-08  0:10 Eric Abrahamsen
  2019-11-08  6:59 ` Fraga, Eric
  2020-02-12  9:15 ` Bastien
  0 siblings, 2 replies; 12+ messages in thread
From: Eric Abrahamsen @ 2019-11-08  0:10 UTC (permalink / raw)
  To: emacs-orgmode

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


^ permalink raw reply related	[flat|nested] 12+ messages in thread
* Re: Finally figuring out some ob-sqlite stuff -- for worg?
@ 2019-11-08  7:06 Thomas S. Dye
  2019-11-08 16:20 ` Eric Abrahamsen
  0 siblings, 1 reply; 12+ messages in thread
From: Thomas S. Dye @ 2019-11-08  7:06 UTC (permalink / raw)
  To: eric; +Cc: emacs-org list

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

^ permalink raw reply	[flat|nested] 12+ messages in thread
* Re: Finally figuring out some ob-sqlite stuff -- for worg?
@ 2019-11-09 17:38 Thomas S. Dye
  0 siblings, 0 replies; 12+ messages in thread
From: Thomas S. Dye @ 2019-11-09 17:38 UTC (permalink / raw)
  To: Eric Abrahamsen; +Cc: emacs-org list


> 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

^ permalink raw reply	[flat|nested] 12+ messages in thread

end of thread, other threads:[~2020-02-12  9:15 UTC | newest]

Thread overview: 12+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2019-11-08  0:10 Finally figuring out some ob-sqlite stuff -- for worg? Eric Abrahamsen
2019-11-08  6:59 ` 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

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).