A new org-mode exporter to Word for scimax

| categories: emacs, orgmode, export | tags: | View Comments

I am continuing to chip away to getting a reasonable export behavior for org-mode to MS Word. I have previously made some progress with Pandoc here and here, but those solutions never stuck with me. So here is another go. Here I leverage Pandoc again, but use a path through LaTeX to get citations without modifying the org-ref cite link syntax. The code for this can be found here: https://github.com/jkitchin/scimax/blob/master/ox-word.el. The gist is you use org-ref like you always do, and you specify the bibliography style for Pandoc like this:

You can download other csl files at https://www.zotero.org/styles. Then you can simply export the org-doc to a Word document with the key-binding C-c C-e w p.

Here is an example document to illustrate the exporter. I have written about data sharing in catalysis kitchin-2015-examp and surface science kitchin-2015-data-surfac-scien.

Here is an example source block.

%matplotlib inline
import matplotlib.pyplot as plt

plt.plot([1, 2, 3, 4, 5, 6])

See Ref. fig:line for example. These do not work. That might require additional pre-processing to replace them with numbers.

Here is the Word document that is generated: 2017-04-15.docx

As a penultimate result it might be ok. The references are reasonably formatted, but not compatible with Endnote, or other bibliography manager software. There are still some issues with Figure numbering and cross-references, but it is not too bad. The main benefit of this seems to be that one source generates HTML and the Word document.

Bibliography

Copyright (C) 2017 by John Kitchin. See the License for information about copying.

org-mode source

Org-mode version = 9.0.5

Read and Post Comments

Autoformatting ordinal numbers and fractions in orgmode

| categories: emacs, orgmode | tags: | View Comments

MS Word has a few things I like. One of them is the ability to autoformat things to make an ordinal number string like 1st to the superscripted version 1st while you type or a 1/2 to ½. I thought it would be pretty easy to implement that for org-mode. It turns out it was not so easy!

There does not appear to be a way to specify a regexp pattern as an abbreviation, or an abbrev that starts with a number. What we need for ordinal numbers is to recognize a sequence of numbers followed by "st", "nd", "rd" or "th" followed by a space or punctuation, and then superscript the letters. In case you didn't want the replacement to occur, you should be able to undo it and get back the original string. This addition was a little hard won, so I am sharing the lessons here.

The logic I used is to put a function in the post-self-insert-hook. The function only works in org-mode, when not in a codeblock and when looking back at a regexp that matches a pattern to be replaced. Getting it to undo was trickier than expected. Eventually I worked out that you put an undo boundary in place before the change, and then it seems like you can undo the changes. I created a minor mode so it is easy to toggle this on and off.

Here is the implementation:

(defcustom scimax-autoformat-ordinals t
  "Determines if scimax autoformats ordinal numbers."
  :group 'scimax)

(defun scimax-org-autoformat-ordinals ()
  "Expand ordinal words to superscripted versions in org-mode.
1st to 1^{st}.
2nd to 2^{nd}
3rd to 3^{rd}
4th to 4^{th}"
  (interactive)
  (when (and scimax-autoformat-ordinals
             (eq major-mode 'org-mode)
             (not (org-in-src-block-p))
             (looking-back "\\(?3:\\<\\(?1:[0-9]+\\)\\(?2:st\\|nd\\|rd\\|th\\)\\>\\)\\(?:[[:punct:]]\\|[[:space:]]\\)"
                           (line-beginning-position)))
    (undo-boundary)
    (save-excursion
      (replace-match "\\1^{\\2}" nil nil nil 3))))


(defcustom scimax-autoformat-fractions t
  "Determines if scimax autoformats fractions."
  :group 'scimax)


(defun scimax-org-autoformat-fractions ()
  "Expand fractions to take up space."
  (interactive)
  (when (and scimax-autoformat-fractions
             (eq major-mode 'org-mode)
             (not (org-in-src-block-p))
             (looking-back "\\(?3:\\<\\(1/4\\|1/2\\|3/4\\)\\>\\)\\(?:[[:punct:]]\\|[[:space:]]\\)"
                           (line-beginning-position)))
    (undo-boundary)
    (save-excursion
      (replace-match (cdr (assoc (match-string 3) '(("1/4" . "¼")
                                                    ("1/2" . "½")
                                                    ("3/4" . "¾"))))
                     nil nil nil 3))))

(defun scimax-org-autoformat ()
  "Autoformat functions."
  (scimax-org-autoformat-ordinals)
  (scimax-org-autoformat-fractions))

(define-minor-mode scimax-autoformat-mode
  "Toggle `scimax-autoformat-mode'.  Converts 1st to 1^{st} as you type."
  :init-value nil
  :lighter (" om")
  (if scimax-ordinal-mode
      (add-hook 'post-self-insert-hook #'scimax-org-autoformat nil 'local)
    (remove-hook 'post-self-insert-hook #'scimax-org-autoformat 'local)))

This is now a feature in scimax. This marks the 500th blog post! That is ½ way to 1000. At the current rate of posting, it will be at least 5 years until I hit that!

Copyright (C) 2017 by John Kitchin. See the License for information about copying.

org-mode source

Org-mode version = 9.0.5

Read and Post Comments

A better return in org-mode

| categories: emacs, orgmode | tags: | View Comments

Over on Stackoverflow someone wanted a better return in org-mode. They wanted return to add items in a list (instead of M-Ret). Someone posted a partial solution, and here I improve on it to add new items to lists, new headings after a heading, and new rows to tables. In each case, a double return on an empty item, headline or table row will delete that line, and terminate the list, headlines or table. You can still use M-Ret, and this function falls through to org-return like it did before. You can use a prefix arg to get a regular return if you want one (e.g. you want to press enter on a headline to push it down).

Here is the function. Give it a try. It is a small but helpful addition I think. I have not used it for long, so if you come across issues leave a comment!

(require 'org-inlinetask)

(defun scimax/org-return (&optional ignore)
  "Add new list item, heading or table row with RET.
A double return on an empty element deletes it.
Use a prefix arg to get regular RET. "
  (interactive "P")
  (if ignore
      (org-return)
    (cond

     ((eq 'line-break (car (org-element-context)))
      (org-return-indent))

     ;; Open links like usual, unless point is at the end of a line.
     ;; and if at beginning of line, just press enter.
     ((or (and (eq 'link (car (org-element-context))) (not (eolp)))
          (bolp))
      (org-return))

     ;; It doesn't make sense to add headings in inline tasks. Thanks Anders
     ;; Johansson!
     ((org-inlinetask-in-task-p)
      (org-return))

     ;; checkboxes too
     ((org-at-item-checkbox-p)
      (org-insert-todo-heading nil))

     ;; lists end with two blank lines, so we need to make sure we are also not
     ;; at the beginning of a line to avoid a loop where a new entry gets
     ;; created with only one blank line.
     ((org-in-item-p)
      (if (save-excursion (beginning-of-line) (org-element-property :contents-begin (org-element-context)))
          (org-insert-heading)
        (beginning-of-line)
        (delete-region (line-beginning-position) (line-end-position))
        (org-return)))

     ;; org-heading
     ((org-at-heading-p)
      (if (not (string= "" (org-element-property :title (org-element-context))))
          (progn (org-end-of-meta-data)
                 (org-insert-heading-respect-content)
                 (outline-show-entry))
        (beginning-of-line)
        (setf (buffer-substring
               (line-beginning-position) (line-end-position)) "")))

     ;; tables
     ((org-at-table-p)
      (if (-any?
           (lambda (x) (not (string= "" x)))
           (nth
            (- (org-table-current-dline) 1)
            (org-table-to-lisp)))
          (org-return)
        ;; empty row
        (beginning-of-line)
        (setf (buffer-substring
               (line-beginning-position) (line-end-position)) "")
        (org-return)))

     ;; fall-through case
     (t
      (org-return)))))


(define-key org-mode-map (kbd "RET")
  'scimax/org-return)

Here are a few tests:

  1. numbered item
  2. second item
    1. nested number
    2. second number
  • [ ] check 1
  • [ ] check 2
  • [ ] check 3
an inline task

With some content

1 a subheading

2 another Subheading

Copyright (C) 2017 by John Kitchin. See the License for information about copying.

org-mode source

Org-mode version = 9.0.5

Read and Post Comments

Exporting org-mode to Jupyter notebooks

| categories: python, emacs, jupyter, orgmode | tags: | View Comments

I am going to use Jupyter notebooks to teach from this semester. I really dislike preparing notebooks though. A browser is a really poor editor, and I really dislike Markdown. Notebooks do not seem to have any real structure in them, e.g. the collapsible outline that I am used to in org-mode, so for long notebooks, it is difficult to get a sense for the structure. I am anticipating spending up to 80 hours preparing notebooks this semester, so today I worked out some code to export org-mode to an ipython notebook!

This will let me use the power tools I am accustomed to for the creation of IPython notebooks for my students, and perhaps others who do not use org-mode.

Jupyter notebooks are just json files, so all we need to do is generate it from an org document. The basic strategy was to build up a lisp data structure that represents the notebook and then just convert that data structure to json. I split the document up into sequential markdown and code cells, and then encode those in the format required for the notebook (json).

So, here is an example of what can be easily written in org-mode, posted to this blog, and exported to an IPython notebook, all from one org-document.

Check out the notebook: exporting-orgmode-to-ipynb.ipynb .

1 Solve a nonlinear problem

Consider the equation \(x^2 = 4\). Find a solution to it in Python using a nonlinear solver.

To do that, we need to define an objective function that will be equal to zero at the solution. Here is the function:

def objective(x):
    return x**2 - 4

Next, we use fsolve with an initial guess. We get fsolve from scipy.optimize.

from scipy.optimize import fsolve

ans = fsolve(objective, 3)
print(ans)
[ 2.]

That should have been an obvious answer. The answer is in brackets because fsolve returns an array. In the next block we will unpack the solution into the answer using the comma operator. Also, we can see that using a different guess leads to a different answer. There are, of course, two answers: \(x = \pm 2\)

ans, = fsolve(objective, -3)
print(ans)
-2.0

Now you see we get a float answer!

Here are some other ways to get a float:

ans = fsolve(objective, -3)

print(float(ans))
print(ans[0])
-2.0000000000000084
-2.0

It is worth noting from the first result that fsolve is iterative and stops when it reaches zero within a tolerance. That is why it is not exactly -2.

2 Benefits of export to ipynb

  1. I can use org-mode
  2. And emacs
  3. and ipynb for teaching.

The export supports org-markup: bold, italic, underlined, and ~~strike~~.

We can use tables:

Table 1: A table of squares.
x y
1 2
2 4
3 9
4 16

We can make plots.

import numpy as np

t = np.linspace(0, 2 * np.pi)

x = np.cos(t)
y = np.sin(t)

import matplotlib.pyplot as plt
plt.plot(x, y)
plt.axis('equal')
plt.xlabel('x')
plt.ylabel('y')
plt.savefig('circle.png')

Even include HTML: <font color="red">Pay special attention to the axis labels!</font>

3 Limitations

  • Only supports iPython blocks
  • Does not do inline images in results
  • Will not support src-block variables
  • Currently only supports vanilla output results

4 Summary

The code that does this is here: ox-ipynb.el . After I use it a while I will put it in scimax. There are some tricks in it to fix up some markdown export of latex fragments and links with no descriptions.

I just run this command in Emacs to get the notebook. Even it renders reasonably in the notebook.

(export-ipynb-buffer)

Overall, this looks extremely promising to develop lecture notes and assignments in org-mode, but export them to Ipython notebooks for the students.

Copyright (C) 2017 by John Kitchin. See the License for information about copying.

org-mode source

Org-mode version = 9.0.3

Read and Post Comments

Find stuff in org-mode anywhere

| categories: database, emacs, orgmode | tags: | View Comments

I use org-mode extensively. I write scientific papers, keep notes on meetings, write letters of recommendation, notes on scientific articles, keep TODO lists in projects, help files for software, write lecture notes, students send me homework solutions in it, it is a contact database, … Some files are on Dropbox, Google Drive, Box, some in git repos, etc. The problem is that leads to org-files everywhere on my hard drive. At this point I have several thousand org-files that span about five years of work.

It is not that easy after a while to find them. Yes there are things like recent-files, bookmarks, counsel-find-file, helm-for-files, counsel/helm-locate, helm/counsel-grep/ag/pt, projectile for searching within a project, a slew of tools to search open buffers, there is recoll, etc… There are desktop search tools, and of course, good organization habits. Over a five year time span though, these change, and I have yet to find a solution to finding what I want. What about a file I made a year ago that is not in the current directory or this project, and not in my org-agenda-files list? How do I get a dynamic todo list across all these files? Or find all the files that cite a particular bibtex entry, or that were authored by a particular student?

Previously, I indexed org files with Swish-e to make it easy to search them, with an ability to search just headlines, or paragraphs, etc. The problem with that is the nightly indexing was slow since I basically had to regenerate the database each time due to limitations in Swish-e. Finally I have gotten around to the next iteration of this idea, which is a better database. In this post, I explore using sqlite to store headlines and links in org-files.

The idea is that anytime I open or save any org file, it will be added/updated in the database. The database will store the headlines and its properties and content, as well as the location and properties of all links and file keywords. That means I should be able to efficiently query all org files I have ever visited to find TODO headlines, tagged headlines, different types of links, etc. Here we try it out and see if it is useful.

1 The database design

I used emacsql to create and interact with a sqlite3 database. It is a lispy way to generate SQL queries. I will not talk about the code much here, you can see this version org-db.el . The database design consists of several tables that contain the filenames, headlines, tags, properties, (optionally) headline-content, headline-tags, headline-properties, and links. The lisp code is a work in progress, and not something I use on a daily basis yet. This post is a proof of concept to see how well this approach works.

I use hooks to update the database when an org-file is opened (only if it is different than what is in the database based on an md5 hash) and when it is saved. Basically, these functions delete the current entries in the database for a file, then use regular expressions to go to each headline or link in the file, and add data back to the database. I found this to be faster than parsing the org-file with org-element especially for large files. Since this is all done by a hook, anytime I open an org-file anywhere it gets added/updated to the database. The performance of this is ok. This approach will not guarantee the database is 100% accurate all the time (e.g. if something modifies the file outside of emacs, like a git pull), but it doesn't need to be. Most of the files do not change often, the database gets updated each time you open a file, and you can always reindex the database from files it knows about. Time will tell how often that seems necessary.

emacsql lets you use lisp code to generate SQL that is sent to the database. Here is an example:

(emacsql-flatten-sql [:select [name] :from main:sqlite_master :where (= type table)])
SELECT name FROM main.sqlite_master WHERE type = "table";

There are some nuances, for example, main:sqlite_master gets converted to main.sqlite_master. You use vectors, keywords, and sexps to setup the command. emacsql will turn a name like filename-id into filename_id. It was not too difficulty to figure out, and the author of emacsql was really helpful on a few points. I will be referring to this post in the future to remember some of these nuances!

Here is a list of tables in the database. There are a few primary tables, and then some that store tags, properties, and keywords on the headlines. This is typical of emacsql code; it is a lisp expression that generates SQL. In this next expression org-db is a variable that stores the database connection created in org-db.el.

(emacsql org-db [:select [name] :from main:sqlite_master :where (= type table)])
files
tags
properties
keywords
headlines
headline_content
headline_content_content
headline_content_segments
headline_content_segdir
headline_content_docsize
headline_content_stat
headline_tags
headline_properties
file_keywords
links

Here is a description of the columns in the files table:

(emacsql org-db [:pragma (funcall table_info files)])
0 rowid INTEGER 0 nil 1
1 filename 0 nil 0  
2 md5 0 nil 0  

and the headlines table.

(emacsql org-db [:pragma (funcall table_info headlines)])
0 rowid INTEGER 0 nil 1
1 filename_id 0 nil 0  
2 title 0 nil 0  
3 level 0 nil 0  
4 todo_keyword 0 nil 0  
5 todo_type 0 nil 0  
6 archivedp 0 nil 0  
7 commentedp 0 nil 0  
8 footnote_section_p 0 nil 0  
9 begin 0 nil 0  

Tags and properties on a headline are stored in headline-tags and headline-properties.

The database is not large if all it has is headlines and links (no content). It got up to half a GB with content, and seemed a little slow, so for this post I leave the content out.

du -hs ~/org-db/org-db.sqlite
56M /Users/jkitchin/org-db/org-db.sqlite

Here we count how many files are in the database. These are just the org-files in my Dropbox folder. There are a lot of them! If I include all the org-files from my research and teaching projects this number grows to about 10,000! You do not want to run org-map-entries on that. Note this also includes all of the org_archive files.

(emacsql org-db [:select (funcall count) :from files])
1569

Here is the headlines count. You can see there is no chance of remembering where these are because there are so many!

(emacsql org-db [:select (funcall count) :from headlines])
38587

And the links. So many links!

(emacsql org-db [:select (funcall count) :from links])
303739

That is a surprising number of links.

2 Querying the link table

Let's see how many are cite links from org-ref there are.

(emacsql org-db [:select (funcall count) :from links :where (= type "cite")])
14766

Wow, I find that to also be surprisingly large! I make a living writing proposals and scientific papers, and I wrote org-ref to make that easier, so maybe it should not be so surprising. We can search the link database for files containing citations of "kitchin-2015-examp" like this. The links table only stores the filename-id, so we join it with the files table to get useful information. Here we show the list of files that contain a citation of that reference. It is a mix of manuscripts, proposals, presentations, documentation files and notes.

(emacsql org-db [:select :distinct [files:filename]
                 :from links :inner :join files :on (= links:filename-id files:rowid) 
                 :where (and (= type "cite") (like path "%kitchin-2015-examp%"))])
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/Research_Data_Publishing_Paper/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/Research_Data_Publishing_Paper/manuscript-2015-06-29/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/Research_Data_Publishing_Paper/manuscript-2015-10-10/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/Research_Data_Publishing_Paper/manuscript-2016-03-09/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/Research_Data_Publishing_Paper/manuscript-2016-04-18/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/human-readable-data/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/@archive/2015/Research_Data_Publishing_Paper/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/@archive/2015/Research_Data_Publishing_Paper/manuscript-2015-06-29/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/@archive/2015/Research_Data_Publishing_Paper/manuscript-2015-10-10/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/@archive/2015/Research_Data_Publishing_Paper/manuscript-2016-03-09/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/@archive/2015/Research_Data_Publishing_Paper/manuscript-2016-04-18/manuscript.org
/Users/jkitchin/Dropbox/CMU/manuscripts/@archive/2015/human-readable-data/manuscript.org
/Users/jkitchin/Dropbox/CMU/meetings/@archive/2015/BES-2015/doe-bes-wed-data-briefing/doe-bes-wed-data-sharing.org
/Users/jkitchin/Dropbox/CMU/meetings/@archive/2015/NIST-july-2015/data-sharing.org
/Users/jkitchin/Dropbox/CMU/meetings/@archive/2015/UD-webinar/ud-webinar.org
/Users/jkitchin/Dropbox/CMU/meetings/@archive/2016/AICHE/data-sharing/data-sharing.org
/Users/jkitchin/Dropbox/CMU/meetings/@archive/2016/Spring-ACS/data-sharing/data-sharing.org
/Users/jkitchin/Dropbox/CMU/projects/DOE-Early-Career/annual-reports/final-report/kitchin-DESC0004031-final-report.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2015/DOE-renewal/proposal-v2.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2015/DOE-renewal/archive/proposal.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2016/DOE-single-atom-alloy/proposal.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2016/MRSEC/MRSEC-IRG-metastable-materials-preproposal/IRG-concept.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2016/ljaf-open-science/kitchin-proposal.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2016/nsf-germination/project-description.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2016/nsf-reu-supplement/project-description.org
/Users/jkitchin/Dropbox/CMU/proposals/@archive/2016/proctor-and-gamble-education/proposal.org
/Users/jkitchin/Dropbox/bibliography/notes.org
/Users/jkitchin/Dropbox/kitchingroup/jmax/org-ref/citeproc/readme.org
/Users/jkitchin/Dropbox/kitchingroup/jmax/org-ref/citeproc/readme-unsrt.org
/Users/jkitchin/Dropbox/kitchingroup/jmax/org-ref/citeproc/readme-author-year.org
/Users/jkitchin/Dropbox/kitchingroup/jmax/org-ref/tests/test-1.org
/Users/jkitchin/Dropbox/kitchingroup/jmax/org-ref/tests/sandbox/elpa/org-ref-20160122.1725/citeproc/readme.org

Obviously we could use this to generate candidates for something like helm or ivy like this.

(ivy-read "Open: " (emacsql org-db [:select [files:filename links:begin]
                                    :from links :inner :join files :on (= links:filename-id files:rowid) 
                                    :where (and (= type "cite") (like path "%kitchin-2015-examp%"))])
          :action '(1 ("o"
                       (lambda (c)
                         (find-file (car c))
                         (goto-char (nth 1 c))
                         (org-show-entry)))))
/Users/jkitchin/Dropbox/CMU/manuscripts/2015/human-readable-data/manuscript.org

Now, you can find every org-file containing any bibtex key as a citation. Since SQL is the query language, you should be able to build really sophisticated queries that combine filters for multiple citations, different kinds of citations, etc.

3 Headline queries

Every headline is stored, along with its location, tags and properties. We can use the database to find headlines that are tagged or with certain properties. You can see here I have 293 tags in the database.

(emacsql org-db [:select (funcall count) :from tags])
293

Here we find headlines tagged with electrolyte. I tagged some papers I read with this at some point.

(emacsql org-db [:select :distinct [files:filename headlines:title]
                 :from headlines :inner :join headline-tags :on (=  headlines:rowid headline-tags:headline-id)
                 :inner :join tags :on (= tags:rowid headline-tags:tag-id)
                 :inner :join files :on (= headlines:filename-id files:rowid)
                 :where (= tags:tag "electrolyte") :limit 5])
/Users/jkitchin/Dropbox/org-mode/prj-doe-early-career.org 2010 - Nickel-borate oxygen-evolving catalyst that functions under benign conditions
/Users/jkitchin/Dropbox/bibliography/notes.org 1971 - A Correlation of the Solution Properties and the Electrochemical Behavior of the Nickel Hydroxide Electrode in Binary Aqueous Alkali Hydroxides
/Users/jkitchin/Dropbox/bibliography/notes.org 1981 - Studies concerning charged nickel hydroxide electrodes IV. Reversible potentials in LiOH, NaOH, RbOH and CsOH
/Users/jkitchin/Dropbox/bibliography/notes.org 1986 - The effect of lithium in preventing iron poisoning in the nickel hydroxide electrode
/Users/jkitchin/Dropbox/bibliography/notes.org 1996 - The role of lithium in preventing the detrimental effect of iron on alkaline battery nickel hydroxide electrode: A mechanistic aspect

Here we see how many entries have an EMAIL property. These could serve as contacts to send email to.

(emacsql org-db [:select [(funcall count)] :from
                 headlines :inner :join headline-properties :on (=  headlines:rowid headline-properties:headline-id)
                 :inner :join properties :on (= properties:rowid headline-properties:property-id)
                 :where (and (= properties:property "EMAIL") (not (null headline-properties:value)))])
7452

If you want to see the ones that match "jkitchin", here they are.

(emacsql org-db [:select :distinct [headlines:title headline-properties:value] :from
             headlines :inner :join headline-properties :on (=  headlines:rowid headline-properties:headline-id)
             :inner :join properties :on (= properties:rowid headline-properties:property-id)
             :where (and (= properties:property "EMAIL") (like headline-properties:value "%jkitchin%"))])
John Kitchin jkitchin@andrew.cmu.edu
John Kitchin jkitchin@cmu.edu
Kitchin, John jkitchin@andrew.cmu.edu

Here is a query to find the number of headlines where the deadline matches 2017. Looks like I am already busy!

(emacsql org-db [:select (funcall count) :from
             headlines :inner :join headline-properties :on (=  headlines:rowid headline-properties:headline-id)
             :inner :join properties :on (= properties:rowid headline-properties:property-id)
             :where (and (= properties:property "DEADLINE") (glob headline-properties:value "*2017*"))])
50

4 Keyword queries

We also store file keywords, so we can search on document titles, authors, etc. Here are five documents with titles longer than 35 characters sorted in descending order.

(emacsql org-db [:select :distinct [value] :from
                 file-keywords :inner :join keywords :on (= file-keywords:keyword-id keywords:rowid)
                 :where (and (> (funcall length value) 35) (= keywords:keyword "TITLE"))
                 :order :by value :desc
                 :limit 5])
pycse - Python3 Computations in Science and Engineering
org-show - simple presentations in org-mode
org-mode - A Human Readable, Machine Addressable Approach to Data Archiving and Sharing in Science and Engineering
modifying emacs to make typing easier.
jmax - John's customizations to maximize Emacs

It is possible to search on AUTHOR, and others. My memos have a #+SUBJECT keyword, so I can find memos on a subject. They also use the LATEX_CLASS of cmu-memo, so I can find all of them easily too:

(emacsql org-db [:select [(funcall count)] :from
                 file-keywords :inner :join keywords :on (= file-keywords:keyword-id keywords:rowid)
                 :where (and (= value "cmu-memo") (= keywords:keyword "LATEX_CLASS"))
                 :limit 5])
119

How about that, 119 memos… Still it sure is nice to be able to find them.

5 Full text search

In theory, the database has a table for the headline content, and it should be fully searchable. I found the database got a little sluggish, and nearly 1/2 a GB in size when using it so I am leaving it out for now.

6 Summary

The foundation for something really good is here. It is still a little tedious to wrote the queries with all the table joins, but some of that could be wrapped into a function for a query. I like the lispy style of the queries, although it can be tricky to map all the concepts onto SQL. A function that might wrap this could look like this:

(org-db-query (and (= properties:property "DEADLINE") (glob headline-properties:value "*2017*")))

This is what it would ideally look like using the org tag/property match syntax. Somehow that string would have to get expanded to generate the code above. I do not have a sense for how difficult that would be. It might not be hard with a recursive descent parser, written by the same author as emacsql.

(org-db-query "DEADLINE={2017}")

The performance is only ok. For large org files there is a notable lag in updating the database, which is notable because while updating, Emacs is blocked. I could try using an idle timer for updates with a queue, or get more clever about when to update. It is not essential that the updates be real-time, only that they are reasonably accurate or done by the time I next search. For now, it is not too annoying though. As a better database, I have had my eye on xapian since that is what mu4e (and notmuch) uses. It might be good to have an external library for parsing org-files, i.e. not through emacs, for this. It would certainly be faster. It seems like a big project though, maybe next summer ;)

Another feature this might benefit from is ignore patterns, or some file feature that prevents it from being indexed. For example, I keep an encrypted password file in org-mode, but as soon as I opened it, it got indexed right into the database, in plain text. If you walk your file system, it might make sense to avoid some directories, like .dropbox.cache. Otherwise, this still looks like a promising approach.

Copyright (C) 2017 by John Kitchin. See the License for information about copying.

org-mode source

Org-mode version = 9.0.3

Read and Post Comments

« Previous Page -- Next Page »