Emacs, Org Mode, and a database like no other

Like any married man, I suppose, I have a Honeydew List. It’s a list that forever changes but never goes away.

Shopping lists, Honeydew lists, radio program lists…. I keep my lists in Emacs, and in particular I utilize a rockin’ good hunk of Elisp code called Org Mode. In Org Mode, you can fold lines in and out, and you use the number of asterisks to identify the level of the line: its place in the hierarchy, as you will. Here is a classic Org Mode top level:

With your cursor on that top line, hit the [Tab] key. The next level (everything with two asterisks) will be revealed.

If you stay on that top line and hit the [Tab] key again, everything will open up.

Note that one of the items has some sub-sub-sub-headings, and one has some lines that don’t seem to be part of any hierarchy at all. The lines without asterisks are sort of a bottom level; you can’t hide anything else under them. In a relatively modest Org Mode outline like this one, they work for all practical purposes identically to the ones with four asterisks.

Hit [Tab] again, and you’re back to just the single top-level line.

Now is probably as good a time as any to blurt out a few words about formatting. This blog post is long. Even by my standards, it’s long. I like to think that everything I write is interesting, but paragraphs that look like this one will be relatively off-topic, and you can skip them if you’re in a hurry.

If you have a reasonably up-to-date Emacs, Org Mode is included, and so is a lovely reference manual, which you can access by going C-h i (which is Emacspeak for [Ctrl-H], [i]; the “i” stands for “info”) and scrolling down the list of manuals until you get to the O’s. The following excerpt, with keystrokes in classical Emacspeak, will get you started in basic navigation.

2.4 Motion

The following commands jump to other headlines in the buffer.

`C-c C-n'
     Next heading.

`C-c C-p'
     Previous heading.

`C-c C-f'
     Next heading same level.

`C-c C-b'
     Previous heading same level.

`C-c C-u'
     Backward to higher level heading.

`C-c C-j'
     Jump to a different place without changing the current outline
     visibility. Shows the document structure in a temporary buffer,
     where you can use the following keys to find your destination:
            <TAB>   Cycle visibility.
            <down>/<up>     Next/previous visible headline.
            <RET>    Select this location.
            /    Do a Sparse-tree search
         The following keys work if you turn off `org-goto-auto-isearch'
            n / p Next/previous visible headline.
            f / b Next/previous headline same level.
            u One level up.
            0-9 Digit argument.
            q Quit
         See also the variable `org-goto-interface'.

If you don’t understand all that, don’t worry. I don’t either.

You can run Emacs within your X environment (that’s where my screenshots come from), or within a terminal emulator, or in text mode by going to one of your TTY screens. If you want to run it in a terminal emulator, you can run it as emacs -nw, and it will stay in text mode, like so:

Emacs, when run in your X desktop environment, does support mouse movements. But Emacs dates back to the 1970s, before mice were even invented, or at least before they were seen outside of the laboratory (didja see what I just did?), and it is still designed to be run completely from your keyboard. The very fact that Emacs predates Linux, mice, Windows 3.1, Windows 2.1, the Space Shuttle, MTV, the 20¢ first-class letter rate, and the deaths of Marshal Josip Broz Tito and Leonid Brezhnev give it an unusually rich folklore and culture and more add-ons than most humans could even count. But it also gives it some ambiguous legacies: a set of keystrokes that aren’t like anything else on earth, a very strange vocabulary both written and spoken, and a symbiosis with a programming language—LISP—that was developed at MIT for artificial intelligence, and which at least for a brief while required its own machines to run on. No fewer than 17 “significant dialects” are noted in its Wikipedia article, and none of them are Emacs LISP (Elisp). And, of course, people who have been using Emacs for 30 years or more, or ever since a fella name of Richard Stallman started hacking around with a pair of existing text editors called E and TECO (sounds like a Butch Cassidy spinoff, doesn’t it?), would go into a coma if one day Emacs just up and started acting like the rest of the world. Not that I am criticizing them. Switching from the WordPerfect DOS keyboard to the Windows CUA keyboard took me to school regarding muscle memory.

BTW, you may be aware that for most of that time, a flamewar has been going on between users of Emacs and users of Vim. I remember once at a Linux users’ group meeting, a guy I’d been talking to and getting along with was actually a bit afraid to tell me that he used Vim at work; I’d been waxing rhapsodic about some damn keystroke combination or another, I guess. I do not hesitate to say that I am ecumenical. I learned Emacs because my supervisor at a job I had for 10 years knew Emacs. Had he known Vim, I’m sure I would have learned Vim, and I’m sure that I would have been very happy. Vim is a terrific program; like Emacs, you can run it in an X environment or at the command line. And more so than Emacs, it is ubiquitous. If you are running Linux, the chances are about 98% that you have at least a vi or a vim-tiny, if not a Vim or a Gvim, at your disposal. Emacs was never tiny. Anyway, I don’t like flamewars, and I wish I could learn Vim, but that’s just too many keystrokes to carry around in the ol’ shoulder pumpkin. Hell, I’ve still got WordPerfect DOS keystrokes up there! And, besides, I do love Emacs and it has never failed me; I have no reason to learn anything else.

But sometimes a list just isn’t enough. That’s when you need a database.

In the C:\> goodold -days, programs like Q&A and PC-File fit the need almost perfectly. Neither of those programs survived the Windows onslaught for very long; in the Windows world, it seemed that Microsoft Works (or, for the cultists, ClarisWorks/AppleWorks) filled this niche market well enough for most dudes and dudettes. And people who had to use Microsoft Office for work or whatever reason, and had or wanted to have a database, could use the souped-up (and pricier) Microsoft Office that included Access.

Access is a relational database—a bigger, brawnier, not to say bloatier beast than Q&A—but it is pretty ergonomic. Or it was, anyway; I haven’t looked it for a long time. You could use it as a flat-file database (that is, a database with only one table) without having its relational abilities get in your way. Paradox, which as part of the WordPerfect complex I gravitated to, was more difficult. I used it for a few projects over the years, but nothing ever really came easy. The best one I ever used was Alpha Five, which was simply a beautiful piece of software (and AFAICT is not mentioned anywhere in the WineHQ database).

Whilst researching this essay, I discovered a successor program to Q&A, Sesame, which claims to have a Linux version. The basic “Personal” version costs $79; you can download a trial version, but I couldn’t figure out how to download a Linux trial version. (Later: Never mind, I found it.) Anyway, though the latest Windows release is dated 2010, I’m not sure how actively this program is being developed; peep, if you will, the Windows logo on the page I linked to above.

Of course, many databases can be kept in spreadsheets. Spreadsheets have traditionally been easier to use, and many of us encountered them at work and learned enough to get by and even create our own.

Throughout this article, when I talk about “databases” I’m not talking about the monsters that run arrivals and departures at Heathrow and track the Human Genome Project. I’m talking about things that normal people might use for stuff like keeping an inventory of their antiques or running a mailing list for the local historical society.

Where spreadsheets break down is in what databases often call a “memo field”. Memo fields are invisible, or noted with a marker, in “table view” (which usually looks something like a spreadsheet). If you go into “record view” in a database, you get essentially a full screen to view or edit your record, including your memo field. I’ll get back to memo fields in a couple of paragraphs. You’ve run into this problem if you’ve ever had to place an extensive annotation in a cell in a spreadsheet.

Where databases break down (besides having relatively rudimentary numeric calculations) is in an ineffable quality that can be called “bloat”, “clunkiness”, “lack of portability”, or whatever. Adding a record, or changing the number of characters a record can contain, or changing the appearance of a form, typically takes an extra step or 3 over doing the same thing in Gnumeric. And sorting records is done obliquely, by creating a “report”, which you usually have to design yourself.

Many articles have been written on which one you should use. If you don’t want to read any more about it, and/or you could care less what I have to say about Kexi, you can skip this paragraph and the next couple and go down to where it says “Can Emacs help?”. (You might be thinking you can skip the whole post, but then you’d miss out on some more cool stuff about Emacs, and boy, are you ever gonna be sorry!) If you were to do an inventory of your book collection and you’re just someone who likes to read, there probably is no reason not to do it in Calc or Gnumeric. If you’re a collector of rare books, you might want to add a bunch of columns to note e.g. what condition the book is in, whether it’s a first edition, whether you bought it legitimately or stole it off of somebody, and your nice spreadsheet is all of a sudden sprawling off the edge of the screen, and you’re sitting there scratching your head and saying, “How’m I ever gonna print this baby?” (The answer, gentle reader, involves Scotch tape.) And, finally, if you’re keeping a book journal, you might keep notes whilst reading, or you might even write an essay about it, and you might want to attach them to the permanent record. Excelsior! You’ve just found a great use for a memo field and, by extension, a database.

There is at least one full-fledged database available for Linux, and that’s Kexi. It isn’t especially well-known, and it has gotten some mixed reviews over the years. (There is another one, Glom, which I haven’t tried and upon which development appears to have slowed. There is also, of course, Open/LibreOffice Base, but I have not had auspicious times with it, personally speaking from experience.) Anyway, I used Kexi for a couple of months or more: not for my book journal, but when I hear some music I like, I keep track of that, and since I listen to a lot of fairly obscure stuff I might have to kind of go out of my way for it if I’m ever going to hear it again. I ended up keeping this in Kexi, partly because you never know when you’re going to need a memo field and partly because I wanted to try out Kexi.

Kexi is certainly good at what it sets out to do, and if you’ve been using Access for something or another and want to bring that data over with you to Linux, I don’t see why you wouldn’t manage just fine. Kexi can be used as a front end to MySQL or PostgreSQL, or as a self-contained Accesslike desktop database application. It’s fairly easy to catch on to, or at least I found it to be so. On the downside, the report writer (which is essentially how you extract particular records from a database) lags a bit in maturity. Some people have reported stability problems; it has crashed on me a couple of times, but without data loss, and I was able to just restart Kexi and carry on. If you design more complex databases than I do, or if you don’t maniacally do backups, or if you really don’t like it at all when your application just flat-out disappears on you, you may not be satisfied with this.

I installed Kexi in Mageia; and since Kexi is part of the KOffice/Calligra suite, Mageia was a good place for it. Later on, when I was putting Linux Mint 12 through its paces, I thought about installing Kexi there just to see how it would work, but it wanted to pull in a total of 295 MB worth of dependencies. I’m sure it would work well—KDE apps do play nice with other DTEs, and Linux Mint is a first-rate platform—but I already had three whole desktop environments in my Linux Mint (five counting some GNOME variants; an essay about that is on its way), and I wasn’t sure I wanted to load it up further with the entire KDE core.

And it got me thinking. Supposing I could get away from the database format? I don’t necessarily mean “locked, blobby binary format”; Kexi uses the free and open-source SQLite format, and it also exports data into .csv, a cheerfully human-readable text format, so your Kexi database is essentially in no danger at all of being rendered inaccessible through obsolescence. But my needs are relatively simple, and in a way, utilizing a relational database for things like my book journals and songs I heard on some college radio station is like driving a Lamborghini to a garage sale. It’s…not exactly pretentious, but it’s a little heavy-handed.

Can Emacs help?

I looked at a venerable and popular Emacs mode called the Insidious Big Brother Database, but it works closely with Emacs email and newsgroup modes and is really designed to be your address book. I looked at GNU recutils, a cluster of command-line utilities that work on text files of a particular format; it’s a fascinating project and I almost got it to work, but ultimately it seemed a little too minimalist (well, okay, hard) for me.

“Gee,” I wondered. “I wish I could use a big ol’ Org Mode file for this! That would be so keen! But there’s no facility for extracting records! All I could do is searches on a predefined value, and…aw, heck, by the time I got done searching for Biography Music and running macros to extract the proper records to another buffer, I’ll be all grown up, and nothing will work any more!

In fact, the answer was there all along. Org Mode contains something called Agenda View. I’d glanced at it briefly and noted that it was, among other things, a way to extract all items in an Org file (or, if you’re good at discernment, in several Org files) that are tagged TODO: an important Org keyword. I keep everything that would naturally fall under that category all together in the Honeydew List, so I never needed to worry much about TODOs, and I thought—reasonably enough IMHO—that the rest of Agenda View was about, like, y’know, agendas? Like for meetings? And conferences?

But inherent within the concept of the Agenda is something Emacs calls Properties. In their basic form, Properties are like little cannisters of data that can be inserted beneath an Org Mode heading or subheading. They don’t “unfold” like regular Org Mode items as you cycle through with your Tab key; you have to put the cursor right on the line that says :PROPERTIES: and hit Tab from there to open it up. Here’s one.

   :Author: Victor Andrade
   :Title: My Missions for Revolutionary Bolivia, 1944-1962
   :Published: 1976
   :Read: 2010
   :Subject: History
   :Area: Bolivia
   :Recommended: 'Foreign Affairs' review
   :Source: Stonington Library
   :From: ILL from UConn

As you can see, the first and last lines are capitalized and encased in colons. Those have to be your first and last lines, exactly like that. In between, it’s more up to you. You can define “keys” (those are the things between colons, such as :Author: and :Subject2:), and these will be the same in every single record. You don’t have to assign a value to every key in every record; in my example above, four keys have no values, which is not to say that they are immoral in any way, of course. It’s probably a good idea to throw in a couple of extra keys, just in case you have to expand your database in the future. You can call them :Eenymeeny: and :Teenyweeny: or whatever you want, and if you need them, you can do a global search-and-replace and replace the idiotic name with something more relevant.

Here’s a quick shortcut (which I think we can all agree is better than an achingly, grindingly slow shortcut): Type out the form you want to use for your database record. Include all the keys, but don’t include any values at all. Type it out exactly how you want to see it. Go to the beginning of it. Hit [Ctrl]-[Spacebar]. Arrow down to the end of the form. Hit [Ctrl-W], which is “cut”, or what almost every other application in the known universe would do if you hit [Ctrl-X]. Then type something you want to use as an abbreviation for the form; for instance, I use 8books as an abbreviation for the books database record. At the end of the string of characters you want to use as an abbreviation, go [Ctrl-X], a, i, g. At the bottom of the screen, Emacs will prompt you: Global expansion for (whatever your abbreviation is):. Go [Ctrl-Y], which is Emacspeak for “Yank the last piece of text you cut out of the killring” (I told you Emacspeak was odd!), and then hit [Enter]. When you close Emacs, it will prompt you to save the abbreviation in a file called ~/.abbrev_defs. Say yes, and every time you type your chosen abbreviation and follow it with a space, [Enter], a comma, or a period, the text will pop out like a Jack-in-a-box-in-an-accordion. This abbreviation will be yours until the end of time, or until you find yourself without a working copy of ~/.abbrev-defs, whichever comes first.

Anyway, here is a short primer on how to locate records in an Org Mode database. This will be very simplistic; I won’t get into searching multiple databases, and in fact I’ll give just one example that kindasorta replicates how you would use a filter or a simple query in a more conventional spreadsheet or database. Here is my book journal, all folded up:

A [Tab] on the only visible line opens up all the second-level lines. (I use authors, rather than book titles, as individual entries; for some reason they have always been easier for me to remember.) Oh, by the way: the ellipsis at the end of a line indicates that that line has a folded-up level (or levels) below it. It’s not editable, but that’s okay, because it loves you and it’s there to help.

Hit C-c / (a.k.a. [Ctrl-C], /.). See the dialogue at the little area—the minibuffer—at the bottom of the screen?

To see what other books about Bolivia besides this one by Victor Andrade are here, hit p for “property”. Type Area. (Remember, properties are the things stuck in between :colons: and which remain the same in every record. Elsewhere, they are sometimes called “keys”, I just noticed.)

Next, Emacs will prompt you for the value. Type Bolivia.

Wait a little while, and you’ll see something that looks like this:

You can unfold the whole list, and the six Bolivian titles will remain partially highlighted, just as they are here. Pretty cool, huh?

On my Play Computer, with 8 GB of memory, this procedure took 16 seconds. On my laptop, with a 1.6 GHz processor and a mere 1 GB of memory, the same search took 24 seconds. That’s going through over 2,500 entries. (Yeah. I read a lot, I remember what I read, and I’ve kept a book journal for a long time.) The book journal .org file is a text file of some 550 KB in size. I would never complain about that being too slow of a search.

To learn more—and there’s so much more to explore!—simply visit orgmode.org. From that site, you can download a 200-page .pdf of the Org Mode Manual if reading it in Info format in Emacs doesn’t float your boat, or for about $17 after shipping you can purchase a rather nicely printed copy of same, of which a buck goes into orgmode.org’s coffers. You can even buy an Org Mode T-shirt. I don’t get anything out of it; I’ve never even met anybody from Org Mode, never mind asked them for a cut. But once in a while, a piece of software comes along that makes a difference. Not only has Org Mode organized my book journals, it has even made me like my Honeydew Lists—and that, dear reader, is an endorsement you won’t hear every day.

This entry was posted in Uncategorized. Bookmark the permalink.

6 Responses to Emacs, Org Mode, and a database like no other

  1. feedelli says:

    I have to try org mode.

  2. steve says:

    I too have a honey-do list, but my fear of storing it in a database, as opposed to my head, is that it is much easier to claim I forgot than it is to claim I lost a row in my Database ;)

    I am always amazed at what emacs, or vim(*sorry*), can do! It can be just a text editor or a complete IDE, or apparently a database front end!

    To be honest, I am kind of in the other boat, where I have tried getting started in emacs a few times, but always fall back to the big V. However, then I see posts like this and it only makes me wish I was more comfortable with it.

  3. Justin Gordon says:

    I use org-mode with vim keybindings, and it ROCKS. Check out evil, and emacs 24 is amazing.

  4. Eddie says:

    Steve, you just hit upon one of the hazards of Linux. Under one of those proprietary operating systems using the preferred office software for today’s modern enterprises, you could always claim that the bit bucket ate your Honeydew. And you’d probably be right :) .

    Justin, I was sort of perplexed by your invitation to “check out evil”, until I discovered that you meant the extensible vi layer for Emacs. What a relief; my heart can remain pure! :) I’m curious if adding a layer like that causes a performance hit at all?

  5. York says:

    Thank you very much for this article. I’m an Emacs user and org-mode is part of my everyday life. I was thinking about whether to use this approach before reading your article, and by the way there is a “invoice.el” in the “org-mode/contrib/” which is pretty much the same idea as yours. However, after reading this article, I tend to give up this idea as IMO searching over 2,500 records takes 14 seconds is a bit too slow and unacceptable.

  6. Erik says:

    Also worth noting is that when doing a property search, you get tab completion on both property names and values. Nice!
    Thanks for writing about this.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Captcha Garb (1.5)