How I use :dbconnection in org files

In the post "Followup on secrets in my work notes" Magnus Therning mentions a feature in Org Babel I contributed to (in ob-sql.el, to be more precise).

You can see my old post for details about that feature, but basically, the patch allows one to use a :dbconnection header argument in a source block to reference a connection defined in sql-connection-alist.

The question in Magnus' post is a signal somebody is actually using this feature, so I am pleased. Perhaps I should also follow up, describing how I use this feature in my workflow. This will constitute another example of how to manage secrets.

I use Org, among other things, to keep work "lab notes" that usually contain SQL queries on different databases.

At work, pretty much all databases are Postgresql or Redshift, and I keep connection details in ~/.pgpass, following this format:

In other words, every DB definition is made of two lines: the first is a comment, with the name of the database (with no spaces); the second contains the actual connection details.

In my Emacs configuration, then, I have this function:

(defun get-connection-alist (filename)
  "Gets PG connections details from ~/.pgpass file (FILENAME)."
  (with-current-buffer (find-file-noselect filename)
        (let ((lines (split-string (buffer-string) "\n" t)))
          (when lines
            (cl-loop for (k v) in (seq-partition lines 2)
                  collect (cl-destructuring-bind (host port db user password)
                              (split-string v ":" nil)
                            `(,(replace-regexp-in-string "^#\s+" "" k)
                              (sql-product 'postgres)
                              (sql-port ,(string-to-number port))
                              (sql-server ,host)
                              (sql-user ,user)
                              (sql-database ,db)
                              (sql-password ,password))))))))

and

(setq sql-connection-alist (get-connection-alist "~/.pgpass"))

I use Emacs in daemon mode and it's not unusual Emacs to stay up for weeks, so I also have an automatic way to incorporate .pgpass changes, using filenotify.

(file-notify-add-watch
 "~/.pgpass" '(change)
 (lambda (evt)
   (setq sql-connection-alist
         (get-connection-alist "~/.pgpass"))))

Exporting content from org-roam to arbitrary org files

I adopted org-roam to collect and manage my personal notes. I also use Org for managing the pages of my personal website (this site). My notes are mostly personal, as I said, but sometimes I want to publish them on the website.

So far the process has been manual: when I had significant updates to publish I just copied text from one org file (in org-roam) to another. But I wanted something more systematic.

I therefore added this two functions in my Emacs configuration

(defun my/remove-org-roam-links (buffer-as-string)
  (replace-regexp-in-string
   "\\[\\[id:.*\\]\\[\\(.*\\)\\]\\]" "\\1" buffer-as-string))

(defun my/paste-org-roam-node (initial-input &key no-links)
  (interactive)
  (let* ((file (org-roam-node-file (org-roam-node-read initial-input)))
         (raw-buffer (with-current-buffer (find-file-noselect file)
                       (goto-char (point-min))
                       (buffer-string))))
    (if no-links (my/remove-org-roam-links raw-buffer)
      raw-buffer)))

Then I can use a snippet like this in the destination buffer

#+begin_src emacs-lisp :results value raw append
  (my/paste-org-roam-node "through" :no-links t)
#+end_src

And this appends to the file the contents of the original org-roam note (I happen to have a note titled "Through the Language Glass", so that 'through' is enough to identify a node), after stripping, if requested, the links to other notes.

There's still some manual work to do before one can post, but this solution is already an useful improvement.

Switching from stream to blog

I wanted to transform my stream for a long time, switching from a single .org file where I kept adding things, to a more appropriate blog structure, with individual posts, index, archive, tag pages, a feed and so on. Thanks to Org, it was relatively easy to use the original material to produce the files I then fed to org-static-blog.

Here the code I used (I just omitted a few service functions)

(defun split-org-file (input-file output-dir)
  "Split an .org file into separate files for each top-level entry.
INPUT-FILE is the path to the .org file.
OUTPUT-DIR is the directory where the separate files will be saved."
  (with-current-buffer (find-file-noselect input-file)
    (goto-char (point-min))
    (org-map-entries
     (lambda ()
       (let* ((entry-title (nth 4 (org-heading-components)))
              (tags (parse-tags-into-filetags
                     (nth 5 (org-heading-components))))
              (ts (get-timestamp-from-title entry-title))
              (entry-title-stripped (strip-entry-title entry-title))
              (output-file (expand-file-name
                            (format "%s.org" (dirify-string entry-title-stripped))
                            output-dir)))
         (let ((body (org-copy-subtree))
               (cleaned-body (with-temp-buffer
                               (insert (car kill-ring))
                               (goto-char (point-min))
                               (when (re-search-forward "^\\*+ \\(.*\\)$" nil t)
                                 (replace-match "" nil nil))
                               (buffer-string))))
           (with-temp-file output-file
             (insert (format "#+title: %s\n" entry-title-stripped)
                     (format "#+date: %s\n" (or ts "2000-01-01"))
                     (format "#+filetags: %s\n" (or tags ""))
                     "\n\n"
                     (format "%s" cleaned-body))))))
     "LEVEL=1")))

Org-roam

I started experimenting with org-roam (v2, with org-roam-ui to have an idea of the progress). Still not entirely clear to me, but I will continue.

Changing style of Emacs' tooltips

It seems obvious now that I figured it out.

Some parts of Emacs GUI are not configurable through faces (obvious). My Emacs is compiled with GTK 3.0, so it uses that library to render some of the elements of the GUI (even more obvious). Tooltips are among those elements. So, in order to change their style, I need to intervene on the configuration of GTK. For example, I had this:

Creating a file ~/.config/gtk-3.0/gtk.css:

@define-color tooltip_bg_color #fff8dc;
@define-color tooltip_fg_color Black;

and restarting Emacs, I obtain this:

A patch for Org Babel

Org-babel allows SQL snippets to be run on a database connection that can be specified in the source block header using parameters such as :dbhost, :dbuser, :dbpassword and so forth.

This is very useful, but I'd also like to be able to use symbolic references to connections defined elsewhere, so that for example one does not have to specify the password every time, interactively or, worse, in the .org file itself.

I am also a user of sql.el, that provides a custom variable sql-connection-alist, where users can define a mapping between connection names and connection details.

The patch I submitted extends the behavior of org-babel-execute:sql so that it's possible to specify a new param :dbconnection containing a connection name, used for looking up sql-connection-alist.

eyebrowse

I started using eyebrowse more systematically. I tend to have multiple frames scattered around my xmonad workspaces, but I usually also have a workspace exclusively devoted to a fullscreen Emacs frame, and there I usually work on different projects, that require different windows configurations. So eyebrowse is useful. To be able to switch more confortably from a configuration to another, I added this little piece of code to my setup:

(loop for i from 1 upto 9
      do (define-key eyebrowse-mode-map
           (kbd (format "M-%d" i))
           `(lambda ()
              (interactive)
              (eyebrowse-switch-to-window-config ,i))))

Why I (don't) hate LaTeX

Why I (don't) hate LaTeX

(via ariis' stream)

I understand and I can sympathize with the nuisances the author mentions (the error messages!), but I do not hate LaTeX.

I use LaTeX for practically everything I need to print. Actually, for most of the things I use org-mode, using its exporter later to obtain a LaTeX or a PDF document – via LaTeX, again – when needed. When I don't need anything special, it's good enough. And it's all plain text, which is also good.

When I need something special, I can thrown in some packages doing the work for me. If I need something very special, well, it might lead me to swearing and tears and whatnot, but I know at the end I'll have something largely more manageable than any other system I know could give me.

When did you start using Emacs?

When did you start using Emacs?

Noticed this Reddit thread in one of Sacha Chua's Emacs News posts, and wondered what I could answer.

~/.emacs.d (master) $ git log --reverse --pretty=format:%ai | head -n 1
2014-05-23 10:29:04 +0200

I didn't realize it's been 3 years already since I started using Emacs.

Indirect buffers

Inspired by a post found on the excellent Emacs news curated by Sasha Chua (Replacing Scrivener with Emacs and Vim), I decided to give indirect buffers another try.

Indirect buffers are buffers that share text with another buffer (the base buffer); org-mode, for example, allows the user to display a subtree in a indirect buffer, which might be a convenient way to focus on a particular section of a document.

Convenient but not practical to me, as I keep forgetting the combination used to open that indirect buffer (C-c C-x b), plus the cursor does not move where I'd want it. Here some code I borrowed from the configuration presented in the post: it is very simple, and in my opinion it makes the usage of indirect buffers a lot more pleasant:

(defun open-subtree-in-another-window ()
  (interactive)
  (org-tree-to-indirect-buffer)
  (windmove-right))

(global-set-key (kbd "C-M-l") 'open-subtree-in-another-window)

Emacs Lisp's Future

Emacs Lisp's Future A thread from two years ago reemerged

ELisp refactoring tools

Started working on tools to help me refactor Elisp code. Here the first main function:

(defun create-new-function (function-name)
  "Creates a new function definition, given a selection. Removes
  the selection and replaces it with a call to the newly created function"
  (interactive "sFunction name: ")
  (let ((code (get-region)))
    (progn
      (save-excursion
        (progn
          (move-to-empty-point)
          (insert-new-function-definition function-name code)))
      (insert "(" function-name " )"))))

Comments welcome.

Automatic webjump list from org

Webjump is a bookmark facility for Emacs. Fed with a list of bookmarks (as an association list) it presents a menu, then open a browser page with the selected link. Simple and handy.

This function converts my list of bookmarks (expressend in a org document that is also used to build my Links page) in a data structure suitable for webjump.

(defun get-webjump-sites ()
  (with-current-buffer (get-file-buffer "~/Dropbox/stefanorodighiero.net/links.org")
    (delq nil
          (mapcar
           (lambda (i)
             (let ((item-string (cdr (assoc "ITEM" i)))
                   (regex "\\[\\[\\(.*\\)\\]\\[\\(.*\\)\\]\\]"))
               (if (posix-string-match regex item-string)
                   `(,(match-string 2 item-string) . ,(match-string 1 item-string)))))
           (org-map-entries 'org-entry-properties nil 'file)))))

(setq webjump-sites (get-webjump-sites))

(require 'cl)
(defun get-webjump-sites ()
  "converts a org document in a data structure suitable for webjump"
  (let ((regex "\\[\\[\\(.*\\)\\]\\[\\(.*\\)\\]\\]"))
    (with-current-buffer (get-file-buffer "~/Dropbox/stefanorodighiero.net/links.org")
      (loop for i in (org-map-entries 'org-entry-properties nil 'file)
            for item-string = (cdr (assoc "ITEM" i))
            if (string-match regex item-string)
            collect `(,(match-string 2 item-string) . ,(match-string 1 item-string))))))

Manipulate regions

A useful code snippet to manipulate regions.

(defun my/org-convert-region-quote ()
  "Converts selection in a QUOTE block"
  (interactive)
  (progn (insert "#+END_QUOTE\n")
         (exchange-point-and-mark)
         (insert "#+BEGIN_QUOTE\n")))

Knowing about point and mark is useful to understand the code. One defect of this code is that it doesn't work if you select a region starting from the end.

eshell and why can't I convert to you

eshell and why can't I convert to you Some interesting Emacs shell tricks in this Reddit thread.

Chris Wellons: 9 Elfeed Features You Might Not Know

Chris Wellons: 9 Elfeed Features You Might Not Know

A list of interesting elfeed tricks

John Wiegley on git

From emacs-devel mailing list:

One thing to keep in mind is that Git has several distinct layers:

• The data model
• The plumbing
• The porcelain

The data model is incredibly simple. This, I think, is Git's main attraction. I've written about the data model in my article "Git from the Bottom Up", and also via a Haskell library for interacting with this model, called gitlib (http://hackage.haskell.org/package/gitlib).

The plumbing is… unintuitive to say the least. The porcelain is… fairly bad, but slowly getting better.

Spoiled by xmonad

I'm doing some experiments with xmonad and I particularly like its mod-Space key combination to switch the window layout in a workspace. Is there something similar for Emacs?

This is similar to what I want: ThreeWindows

(defvar *larsen/split-layout-type* t)

(defun toggle-split-layout ()
  (interactive)
  (progn (change-split-type-2 *larsen/split-layout-type*)
         (if *larsen/split-layout-type*
             (setq *larsen/split-layout-type* nil)
           (setq *larsen/split-layout-type* t))))

(global-set-key (kbd "M-<f1>") 'toggle-split-layout)

Collection of CSS styles for org

Collection of CSS styles for org A thread on Reddit with pointers to CSS styles for org-mode HTML export.

Trying Spacemacs

Switching theme

When you use load-theme the chosen theme is applied together with any other theme precedently activated. This small function could be useful to switch theme instead of just pushing another one on the stack.

(defun switch-theme (theme)
  (interactive
   (list
    (intern (completing-read "Switch to custom theme: "
                             (mapcar 'symbol-name
                                     (custom-available-themes))))))
  (dolist (curr custom-enabled-themes) (disable-theme curr))
  (load-theme theme))

Typing analytics

I wish I could be able to measure my typing speed in a consistent manner, and without recurring to tools external to my flow. Ideally, it would be something sitting behind and observing my keyboard usage as I work.

• Typing Analytics in Emacs
• Emacs extension to measure typing speed in the background? [Reddit]

which-key

@manuel_uberti wrote about which-key, a package that displays available keybindings in popup. Manuel shows an example to activate it:

(which-key-mode)

(setq which-key-idle-delay 0.5
                which-key-key-replacement-alist
                '(("<\\([[:alnum:]-]+\\)>" . "\\1")
                  ("up"                  . "↑")
                  ("right"               . "→")
                  ("down"                . "↓")
                  ("left"                . "←")
                  ("DEL"                 . "⌫")
                  ("deletechar"          . "⌦")
                  ("RET"                 . "⏎")))

an observation

So, apparently the code highlighting I get publishing a org document gets affected by the current theme in Emacs (note to self: using the cyberpunk theme produces unreadable code on white background).

Measuring things

I use org-mode for registering the books I read. Here some code to produce stats.

;; Is there a better (more idiomatic) way to aggregate values?
(defun aggregate (aggregate-function lst)
  (let ((hash (make-hash-table :test 'equal)))
    (loop for key in (mapcar 'car lst)
          for value in (mapcar 'cdr lst)
          do (if (null (gethash key hash))
                 (puthash key value hash)
               (puthash key (funcall aggregate-function value (gethash key hash)) hash))
          finally return hash)))

(defun pages-per-month-raw ()
  (with-current-buffer (get-file-buffer "~/org/books.org")
    (mapcar (lambda (b)
              (let* ((month (format-time-string "%b" (date-to-time (cdr (assoc "TIMESTAMP" b)))))
                     (pages (string-to-int (cdr (assoc "PAGES" b)))))
                (cons month pages)))
            (books/in-year "2015"))))

(defun pages-per-month ()
  (let ((ppmr (pages-per-month-raw)))
    (aggregate '+ ppmr)))

(defun month-list ()
  '("Jan" "Feb" "Mar" "Apr" "May" "Jun"
    "Jul" "Aug" "Sep" "Oct" "Nov" "Dec"))

(defun complete-hash (hash)
  (let ((new-hash (make-hash-table)))
    (loop for month-name in (month-list)
          do (if (null (gethash month-name hash))
                 (puthash month-name 0 new-hash)
               (puthash month-name (gethash month-name hash) new-hash))
          finally return new-hash)))

;; Poor man's TSV export
;; TODO check the implicit assertion on the ordering
(maphash (lambda (k v) (insert (format "%s\t%s\n" k v)))
         (complete-hash (pages-per-month)))

Then, for example:

stats <- read.csv("/tmp/stats.tsv", sep = "\t", header = F)
names(stats) <- c("month", "pages")
stats$month <- factor(stats$month, month.name )
p <- ggplot( stats, aes(month, pages)) +
    geom_histogram() +
    theme(axis.text.x = element_text(angle=45, hjust=1))
p