If you spent any time in Emacs you will sooner or later run into keyboard
macros. In this article I will cover the basics of keyboard macros by going
through a few practical, real-world examples. If you are new to Emacs or don't
know about keyboard macros, then this is a great place to start.
In my work I am querying databases and writing reports for people on a daily
basis. I like to use Emacs SQL mode as my front end for connecting to a
Microsoft SQL Server backend because it allows me to write queries and
manipulate the results in very powerful ways.
A scenario where I use keyboard macros goes something like this: A business user
gives me a list of invoices and she needs to check if they where processed in
our invoicing application. She would send the list in email or IM and it looks
like this:
001000403456
001000404567
001000405678
001000405679
001000405680
001000406789
001000406790
001000407890
001000407891
001000407892
In the database we have a table called InvoiceHeader where we keep the invoice
data. The InvoiceNumber column is of a nvarchar(30) type. This means that if
I want to use the list of invoices above in a IN clause of a transact SQL
query, I need to wrap each item with single quotes and delimit then with a
comma. That's not too much work if this is a one-off and your list has only 10
items like the list above. But what if the list has 100 or 1000 items and you
were doing these types of queries several times per week? Well, this is where
keyboard macros come to the rescue.
Suppose that your SQL code looks like this:
SELECT h.InvoiceNumber
, h.CustomerName
, h.DueDate
, h.Status
, SUM(d.Cost)
FROM InvoiceHeader h (NOLOCK)
JOIN InvoiceDetail d (NOLOCK) ON h.RowID = d.InvoiceID
WHERE h.InvoiceNumber IN (
-- PASTE INVOICE NUMBER LIST HERE
) GROUP BY h.InvoiceNumber, h.CustomerName, h.DueDate, h.Status
I paste the list of invoice numbers from the email to where I indicated in the
comment in the query above. The invoice numbers are structured one per line and
each looking like this 001000407892 but I need them to look like this
'001000407892',. To achieve that using Emacs keyboard macros, place the point
on the line with the first invoice number and follow these keystrokes:
- C-x (
- Start macro recording
- C-a
- Anchor the point at the beggining of line
- '
- Insert a single quote
- C-e
- Anchor the point at end of line
- ' ,
- Insert a single quote and a comma
- C-n
- Move the point to the next line
- C-x )
- Stop the macro recording
Go ahead and try it now. Once completed, the key sequences above allowed you to
record the macro, and now you will want to apply the macro to all the lines in
the rest of list of invoices. You do that by keying in the following chord: C-x
e. This key sequence invokes the Emacs command kmacro-end-and-call-macro
which calls the last defined keyboard macro.
This is certainly a lot more efficient than applying the single quotes and
commas to each line manually. And this is just one way to make macros work for
you. As you probably already know, there is more than one way to skin a cat in
Emacs. Lets see how we can make our macro example even more efficient for our
SQL coding situation.
Macros Reloaded
What I really want to do in the situation above is to yank my list of invoice
numbers into the WHERE clause of the query and then mark the region. Once I have
the region marked, I want to apply the macro to the whole region in one key
chord. Starting with the following SQL code:
SELECT h.InvoiceNumber
, h.CustomerName
, h.DueDate
, h.Status
, SUM(d.Cost)
FROM InvoiceHeader h (NOLOCK)
JOIN InvoiceDetail d (NOLOCK) ON h.RowID = d.InvoiceID
WHERE h.InvoiceNumber IN (
001000403456
001000404567
001000405678
001000405679
001000405680
001000406789
001000406790
001000407890
001000407891
001000407892
) GROUP BY h.InvoiceNumber, h.CustomerName, h.DueDate, h.Status
This is what I do:
- Place the point a the start of the line of the first invoice number
- Record the macro by follonwing the key chords in the table above (if you haven't already done so)
- Undo the editing you done by recording the macro
- Mark the region encompassing all the invoice numbers
- Use the key chord C-x C-k r to apply the macro to each line in the region
- Delete that last comma behind the last invoice number so you won't get a syntax error in you SQL code
This is the result of performing the steps above:
SELECT h.InvoiceNumber
, h.CustomerName
, h.DueDate
, h.Status
, SUM(d.Cost)
FROM InvoiceHeader h (NOLOCK)
JOIN InvoiceDetail d (NOLOCK) ON h.RowID = d.InvoiceID
WHERE h.InvoiceNumber IN (
'001000403456',
'001000404567',
'001000405678',
'001000405679',
'001000405680',
'001000406789',
'001000406790',
'001000407890',
'001000407891',
'001000407892'
) GROUP BY h.InvoiceNumber, h.CustomerName, h.DueDate, h.Status
This is a simple example of the power of keyboard macros in Emacs. There are
many other more complex tasks you can accomplish using keyboard macros that I
have not covered here. Things such as parsing log files and outputing lines
based on a regular expression or doing string substitution on a buffer while
asking the user for the input to replace the tokens with.
Things to Remember
In order to write effective and flexible macros that work everywhere always
remember to use your anchor key chords. These key chords allow you to place the
point in the buffer with a great degree of flexibility. To name a few, they are:
C-a, C-e, C-n, C-p, etc.
If you will perform the task you captured in the macro on a regular basis, then
you will want to save it to your .emacs file and maybe even bind it to a key
chord of your choosing. First, key in the chord C-x C-k n and give your
keyboard macro a unique name. Open up your .emacs file and place the point in
the buffer where you can yank some content. Use the elisp function to yank the
macro definition from the macro ring to the point location by typing M-x
insert-kbd-macro and then providing the name you just created. If you are
saving the macro definition we have been working on in the article, then you
should see code similar to this:
(fset 'prep-list-items-for-sql
(lambda (&optional arg) "Keyboard macro."
(interactive "p")
(kmacro-exec-ring-item
(quote ("^A'^E',^N" 0 "%d")) arg)))
Finally, you can bind your macro to a key chord to make it easily accessible in
any situation. I have the following code in my .emacs file to bind Shift
plus the F10 key to the macro:
(global-set-key [S-f10] 'prep-list-items-for-sql)
Until next time and happy emacs hacking!
