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

2019-11-07 Thread 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,

Thomas S. Dye

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

2019-11-07 Thread 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

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

2019-11-07 Thread Eric Abrahamsen
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?


>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/ Add tips on inserting
new values into existing tables, and using an intermediary Org table
to edit SQL tables.
 org-contrib/babel/languages/ | 40 +++
 1 file changed, 40 insertions(+)

diff --git a/org-contrib/babel/languages/ b/org-contrib/babel/languages/
index 2dafa1ab..0512c9a4 100644
--- a/org-contrib/babel/languages/
+++ b/org-contrib/babel/languages/
@@ -212,4 +212,44 @@ select n, count(*) from testtable group by n;
 | 10 |2 |
+If dropping/overwriting a table is undesirable, a temporary SQL table
+can be used to insert new values into an existing table:
+,#+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;
+** 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_src sqlite :db /tmp/reviews.db
+select id,title,rating from bookreview where rating is null;
+,#+name: ratings
+|  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 =
+where exists (select * from updates where =;
+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.

Bug: ob-plantuml: using @start prefix in buffer returns nil body due to nil assignments [N/A (N/A @ /nix/store/1hmx5h1kn25p7s31h31dz7g3kn1dc6qx-emacs-packages-deps/share/emacs/site-lisp/org/)]

2019-11-07 Thread Terje Larsen

Remember to cover the basics, that is, what you expected to happen and
what in fact did happen.  You don't know how to make a good report?  See

Your bug report will be posted to the Org mailing list.

I noticed that I cannot run C-c C-c to convert plantuml source blocks
since the commit (4e854974be9788f029f2d73f829c4d51d2b83faf), see changes

If I have a simple source block as such as:
#+BEGIN_SRC plantuml :file output.png
actor Foo1
actor Foo2
Foo1 -> Foo2 : Test

When I try to run C-c C-c I receive the following error message:
org-babel-execute-src-block: Wrong type argument: char-or-string-p, nil

During my debugging I found out that assignments inside the function
org-babel-plantuml-make-body returns nil and that causes the function to
return nil which is not expected behavior. I remember this working
before, so I went looking through the history and noticed the addition
of this code. To me it looks like the logic is wrong. As the expression:
(org-babel-variable-assignments:plantuml params)

Could not possibly not return the body and without the body the content
will be invalid. I have verified that reverting this commit fixes the
issue and lets me generate PlantUML files.

Emacs  : GNU Emacs 27.0.50 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.12)
Package: Org mode version N/A (N/A @ 

Re: Anyone use 3rd party search tools w/org-mode?

2019-11-07 Thread Eric Abrahamsen
John Kitchin  writes:

> The way I got Swish to index org files was to create a script that
> generated an xml file
> (
> or html
> (
> that it could index. This is probably a general strategy for these tools.

That seems unfortunately roundabout, but I don't know enough about the
various FTS engines to know if they could be taught to read Org files 

[PATCH] Add support for month trees

2019-11-07 Thread Jason

Please find my attached patch which implements a way to capture entries
grouped by month.

I was searching for this option, but only found a way to group entries by
week or day.

I found the following posts where other people also need this feature:

Jason Dunsmore
From 9858c2acc3aef0561dacd740d2ee353f77239fe2 Mon Sep 17 00:00:00 2001
From: Jason Dunsmore 
Date: Wed, 6 Nov 2019 21:49:43 -0600
Subject: [PATCH] org-capture.el: Add support for month trees

* doc/ Add `:tree-type month' option for capture
* etc/ORG-NEWS: Document new `:tree-type month' option.
* lisp/org-capture.el (org-capture-set-target-location): Add
  `:tree-type month' option to capture templates to group entries by
* lisp/org-datetree.el (org-datetree-find-month-create): Add
  `org-datetree-find-month-create' function to add datetree entries
  grouped by month.
* testing/lisp/test-org-datetree.el
  (test-org-datetree/find-month-create): Add test for new function.
 doc/|  7 ---
 etc/ORG-NEWS  |  5 -
 lisp/org-capture.el   | 10 +++---
 lisp/org-datetree.el  | 27 +++
 testing/lisp/test-org-datetree.el | 11 +++
 5 files changed, 49 insertions(+), 11 deletions(-)

diff --git a/doc/ b/doc/
index 5e69ef074..cd133739d 100644
--- a/doc/
+++ b/doc/
@@ -7592,9 +7592,10 @@ Now lets look at the elements of a template definition.  Each entry in
   - ~:tree-type~ ::
-When ~week~, make a week tree instead of the month tree, i.e.,
-place the headings for each day under a heading with the current
-ISO week.
+Use ~week~ to make a week tree instead of the month-day tree,
+i.e., place the headings for each day under a heading with the
+current ISO week. Use @code{month} to group entries by month
+only. Default is to group entries by day.
   - ~:unnarrowed~ ::
diff --git a/etc/ORG-NEWS b/etc/ORG-NEWS
index bdcfc24fd..35e8d2864 100644
--- a/etc/ORG-NEWS
+++ b/etc/ORG-NEWS
@@ -335,7 +335,9 @@ the headline to use for making the table of contents.
 ,* Another section
 ,#+TOC: headlines 1 :target "#TargetSection"
+*** New option to group captured datetime entries by month
+A new `:tree-type month' option was added to org-capture-templates to
+group new datetime entries by month.
 ** New functions
 *** ~org-dynamic-block-insert-dblock~
@@ -348,6 +350,7 @@ dynamic block in ~org-dynamic-block-alist~.
 *** ~org-table-cell-left~
 *** ~org-table-cell-right~
 *** ~org-habit-toggle-display-in-agenda~
+*** ~org-datetree-find-month-create~
 ** Removed functions and variables
 *** Removed Org Drill
diff --git a/lisp/org-capture.el b/lisp/org-capture.el
index 4f97e17ea..ab0db0c27 100644
--- a/lisp/org-capture.el
+++ b/lisp/org-capture.el
@@ -998,9 +998,13 @@ Store them in the capture property list."
 	   ;; Make a date/week tree entry, with the current date (or
 	   ;; yesterday, if we are extending dates for a couple of hours)
-	(if (eq (org-capture-get :tree-type) 'week)
-		#'org-datetree-find-iso-week-create
-	  #'org-datetree-find-date-create)
+	(cond
+	 ((eq (org-capture-get :tree-type) 'week)
+	  #'org-datetree-find-iso-week-create)
+	 ((eq (org-capture-get :tree-type) 'month)
+	  #'org-datetree-find-month-create)
+	 (t
+	  #'org-datetree-find-date-create))
diff --git a/lisp/org-datetree.el b/lisp/org-datetree.el
index b4797de1e..0ae3cf6ec 100644
--- a/lisp/org-datetree.el
+++ b/lisp/org-datetree.el
@@ -51,11 +51,29 @@ Added time stamp is active unless value is `inactive'."
 (defun org-datetree-find-date-create (d  keep-restriction)
-  "Find or create an entry for date D.
+  "Find or create a day entry for date D.
 If KEEP-RESTRICTION is non-nil, do not widen the buffer.
 When it is nil, the buffer will be widened to make sure an existing date
 tree can be found.  If it is the symbol `subtree-at-point', then the tree
 will be built under the headline at point."
+  (org-datetree--find-create-group d 'day keep-restriction))
+(defun org-datetree-find-month-create (d  keep-restriction)
+  "Find or create a month entry for date D.
+Compared to `org-datetree-find-date-create' this function creates
+entries grouped by month instead of days.
+If KEEP-RESTRICTION is non-nil, do not widen the buffer.
+When it is nil, the buffer will be widened to make sure an existing date
+tree can be found.  If it is the symbol `subtree-at-point', then the tree
+will be built under the headline at point."
+  (org-datetree--find-create-group d 'month keep-restriction))