Posted on

Connect To Your Database With Emacs

The recent Emacs post made me think of a useful SQL trick I recently learned that I wanted to share. Emacs refers to different SQL engines as ‘products’, e.g. MySQL, SQLite, and so on. You can connect to an SQL database through Emacs but you have a call a different command based on the product.

An easier way to do this, as of version 24, is to use the keyboard shortcut C-u C-c TAB when viewing an SQL document. This will prompt you first for the product, and then for any connection information. So if you are working with an SQLite database you can press C-u C-c TAB, type sqlite, and then the filename of the database; that will open an interactive window connected to the database. Or if you are using PostgreSQL then Emacs will ask for a username, port, etc. The supported products are:

  • db2
  • informix
  • ingres
  • interbase
  • linter
  • ms
  • mysql
  • oracle
  • postgres
  • solid
  • sqlite
  • sybase

I don’t even know what some of those are…

Anyways, sometimes I find this faster than opening a terminal if I need to run some test queries or copy output into a document. (This is very useful with SQLite’s .mode html command, which will print the results of queries in <table> form.)


This article was originally posted on “The List” by EJMR.

Posted on

Tab Completion for Custom Commands

A lot of commands in Emacs take input from the minibuffer, the little one-line area at the bottom of the editor. When you are writing your own commands you will often make use of the same facility. In this article I want to show you one way of doing that which will provide tab completion for your custom functions.

I’m going to use a simple example out of my own dot Emacs file. This is the function that was in my last email, my function for opening org-mode log files. I keep one org-mode file for each project, in a log directory, split up by year. So I wanted a simple command I could bind to a key that would let me type in a project name and open the file. So I have this:

(defun open-project-log-file (project)
      "Opens a log file for a project."
      (interactive "sProject: ")
      (find-file (concat "~/Documents/Logs/2010/" project ".org")))

Then when I press C-H-x l I get the ‘Project:’ prompt in the minibuffer. I type in something like ‘Foo’, press Enter, and there’s my org-mode file.

This short example demonstrates the easiest way of getting a string of input in your commands: interactive. Emacs makes a distinction between functions which are called interactively, and those which are not. When you call a function non-interactively that means you are calling it from inside of Lisp code. If I wrote

(open-project-log-file "Foo")

then that is a non-interactive call. But when I run the command from my key-binding, or if I did M-x open-project-log-file, then that is an interactive call. When I do that Emacs looks at the ‘interactive’ form at the top of the function to figure out how to get the argument for the function.

We can use interactive in a couple of different ways. The simpliest is by giving it a string that describes how we want to prompt for and read in arguments. The parts of the string are separated by ‘
’ characters. The first character of each part tells Emacs what kind of input to read in. Then everything from that character until the next ‘
’ or end of string is the prompt.

So in the case of “sProject: “ that means to read in a string (‘s’) from the prompt ‘Project: ‘. Whatever I type in is assigned to the ‘project’ parameter of the function. Another example:

(defun interactive-example (foo bar baz)
      (interactive "bFoo: 
fBaz: ")

This example has three prompts, setting ‘foo’ to a buffer name (‘b’), ‘bar’ to the name of a function (‘a’), and ‘baz’ to the name of a file (‘f’). In all cases Emacs knows what we are wanting to read in and provides us with completion help. For example, it knows what buffers and files are open, and what functions exist, so we can tab-complete on all of these.

If you look at the documentation on interactive you will see that it accepts a large number of codes for defining your input. But they can’t cover everything. There comes a time when you’ll want to have tab-completion available for some known set of values. This is where completing-read comes into the picture.

Let’s say I want to redefine my log function so that Emacs will know which files I might be looking for. That is, what log files exist in my log directory? Easy way to find out:

(directory-files "~/Documents/Logs/2010/")

Returns all of the files in that directory as a list of strings. I can use that as my set of possible inputs. Whenever I open a log file, the file I want will always be in the list. So what I want is for Emacs to tab-complete based on the values in that list. The function completing-read does this exactly.

(completing-read "Project: "
                     (directory-files "~/Documents/Logs/2010/"))

This line of code will print out the ‘Project: ‘ prompt as usual and read in input from the minibuffer. The second argument is the ‘collection’, a list of strings which represent valid input values, and which Emacs can tab-complete on. This is the minimum needed to use completing-read. However, it takes six additional, optional arguments. If you want to restrict completion to a sub-set of those values, or allow input outside of that set, then you’ll need to pass values to those optional arguments. As always, C-h f is the fastest way to find this information.

So let’s look at a smarter version of open-project-log-file:

(defun open-project-log-file (project)
      "Opens a log file for a project."
        (completing-read "Project: " (directory-files "~/Documents/Logs/2010/"))))
      (find-file (concat "~/Documents/Logs/2010/" project)))

Here is an example of the other form of interactive: using Lisp code instead of strings. In this form, the code given to interactive needs to return a list of values that will be assigned to the function parameters. The easiest way to create a list in Lisp is with the ‘list’ function.

(list 10 20 (+ 100 1)) => ‘(10 20 101)

In my case, I’m just creating a list of one value, the result of completing-read.

The rest of the function is the same. But now when I run it and type ‘F’, I get the benefit of hitting Tab and having ‘’ show up in the minibuffer. If you are writing a custom command and can think of a way to define the set of inputs that function will take, even if it is incomplete, then you can use completing-read to get the benefits of tab-completion in your functions.

Posted on

Indent on Save, Maybe

While working on a .NET project, I have noticed that when I do many things, Visual Studio takes that time to re-indent large amounts of my code. But most of the time I’m writing it out in Emacs, so this makes my diffs a pain in the ass. But it got me to thinking about indenting on save. And then—lo—the very subject came up today on the Emacs mailing list. A teacher was asking for a way to make C++ mode automatically re-indent the whole file on save, to make his students use. I don’t know if he was sick of seeing horrible indentation or what.

There is a hook in Emacs called the before-save-hook, to which you can add functions to be called just before saving the contents of a buffer. A great example:

(add-hook 'before-save-hook 'delete-trailing-whitespace)

Will call delete-trailing-whitespace whenever you save any file.

So with that hook in mind, here was my first idea, which I sent back to the guy.

(add-hook 'before-save-hook
          (lambda ()
            (when (eq 'c++-mode
                      (buffer-local-value 'major-mode (current-buffer)))
              (indent-region (point-min) (point-max)))))

Each buffer has a local variable called major-mode that names the current major mode—appropriately enough. This anonymous function checks to see if that mode is c++-mode, and if it does then it calls indent-region with two arguments: the start and end points of the region we want to indent. The functions point-min and point-max return the minimum and maximum points of the buffer. So this indents the whole thing.

But a more experienced Emacs Lisp developer on the mailing list pointed out to me a better way. His code:

(defun c++-indent-buffer-maybe ()
  (when (and (string-match
             (y-or-n-p "Indent buffer before saving ?"))
    (indent-region (point-min)

(defun my-c++-hook ()
  (add-hook 'before-save-hook 'c++-indent-buffer-maybe nil t))

(add-hook 'c++-mode-hook 'my-c++-hook)

His code looks at the filename of the buffer to decide whether or not the file should be indented. I prefer checking the major mode still, but the teacher who asked the original question also wanted to see a filename-based solution.

This version also prompts—via the function y-or-n-p—to make sure the user wants to indent the whole buffer. If the user types ‘n’, the buffer is still saved, but not indented. This is useful, but adds an extra key-press on every save. Personally, I think I would go without it.

But the best way in which this code is better is how it sets the hook as a mode hook for C++ mode. One problem with using before-save-hook is it is called for every buffer. So my original code would be checking every time I save anything. Which is overkill. The function my-c++-hook above adds the indenting function to before-save-hook, but sets it as a buffer-local hook. This is done by the last argument. If we look at the definition of add-hook:

(add-hook HOOK FUNCTION &optional APPEND LOCAL)

The first optional argument APPEND decides where in the hook list our function goes. The LOCAL argument, if true, makes the hook local to the current buffer.

So via a little indirection, the code above makes sure that the indented code is only run when saving C++ buffers, instead of checking it when saved everywhere.

A useful combination of these would be:

(defun c++-indent-buffer-maybe ()
  (if (y-or-n-p "Indent buffer before saving?")
      (indent-region (point-min)

(add-hook 'c++-mode-hook
          (lambda ()
            (add-hook 'before-save-hook 'c++-indent-buffer-maybe nil t)))

Not hard to imagine how to extend this to other modes.

Posted on

SQL Movement Commands

Most programming modes in Emacs support two commands, beginning-of-defun and end-of-defun.  These move you to the beginning and end of the nearest function, and by default are bound to C-M-a and C-M-e, respectively.

However, sql-mode does not have anything similar.  I have the above two commands bound to C-up and C-down for quickly moving around functions, but I find myself often hitting them when viewing an SQL file to move through tables—which doesn’t work.  Fortunately that functionality is pretty easy to add.

For my fellow Emacs users, here is some code I whipped up that you can use to easily move between tables and views.

(defconst sql-beginning-of-table-or-view-regexp
"Regular expression for matching the start of an SQL table or view definition.")

(defun sql-prev-table-or-view ()
"Move backwards to the beginning of the nearest table or view
from point."
(re-search-backward sql-beginning-of-table-or-view-regexp
nil 'noerror))

(defun sql-next-table-or-view ()
"Move forwards to the beginning of the nearest table or view from point."
(re-search-forward sql-beginning-of-table-or-view-regexp
nil 'noerror))

Then you can add a hook for sql-mode to bind those commands to keys whenever sql-mode is first loaded. I know you guys already know about using global-set-key, but you can also create key bindings specific for modes. Each mode in Emacs has an associated “mode map”, for example, sql-mode has the sql-mode-map, php-mode has the php-mode-map, et cetera. You can use the function define-key to create a binding for a specific mode map. Like I said, I have C-up and C-down globally bound to move functions, but I use define-key like this to make the keys move by tables/views is sql-mode:

(global-set-key (kbd "<C-up>") 'beginning-of-defun)
(global-set-key (kbd "<C-down>") 'end-of-defun)

(add-hook 'sql-mode-hook
(lambda ()
(define-key sql-mode-map (kbd "<C-up>")
(define-key sql-mode-map (kbd "<C-down>")

I put my global key settings in there just for comparison. The call to define-key is almost identical, except the first argument is the mode map.

While I’m on the subject, there are two similar commands for text-mode that have no default binding, which I find come in handy:

(add-hook 'text-mode-hook
(lambda ()
(define-key text-mode-map (kbd "<C-down>")
(define-key text-mode-map (kbd "<C-up>")

So there’s some hopefully useful commands for SQL work, and for configuring key bindings in general.