From mboxrd@z Thu Jan 1 00:00:00 1970 From: Eric Abrahamsen Subject: Finally figuring out some ob-sqlite stuff -- for worg? Date: Thu, 07 Nov 2019 16:10:45 -0800 Message-ID: <87bltn5it6.fsf@ericabrahamsen.net> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Return-path: Received: from eggs.gnu.org ([2001:470:142:3::10]:52995) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1iSrrl-0002S7-09 for emacs-orgmode@gnu.org; Thu, 07 Nov 2019 19:11:06 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1iSrrh-0005jN-2v for emacs-orgmode@gnu.org; Thu, 07 Nov 2019 19:11:03 -0500 Received: from 195-159-176-226.customer.powertech.no ([195.159.176.226]:51706 helo=blaine.gmane.org) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1iSrrg-0005gF-QL for emacs-orgmode@gnu.org; Thu, 07 Nov 2019 19:11:01 -0500 Received: from list by blaine.gmane.org with local (Exim 4.89) (envelope-from ) id 1iSrrc-000CE9-AW for emacs-orgmode@gnu.org; Fri, 08 Nov 2019 01:10:56 +0100 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: emacs-orgmode@gnu.org --=-=-= Content-Type: text/plain 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 --=-=-= Content-Type: text/x-patch Content-Disposition: attachment; filename=0001-Add-more-examples-to-the-ob-sqlite-section.patch >From 50fe1a6319dc13b41326702f6ea566f2241e7e52 Mon Sep 17 00:00:00 2001 From: Eric Abrahamsen 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 --=-=-=--