Keyboard Macros in the Wild: The Mundane SQL Fix

April 5th, 2007 by Rob Christie · 21 Comments

When you first start using emacs, there is normally someone who says something like keyboard macros in emacs rock. At that moment in time, they show you some quick little example, it looks neat but it probably doesn’t register with the true magnitude that it should. This series shows examples of macros “in the wild”. I hope it helps reinforce the value of the keyboard macro.

Problem

I have 80 lines of sql code that need to be updated with new unique ids starting at 500 and a new group id of 5. An example of the original sql is below:

INSERT INTO group_perms (id, groupid, permid) VALUES (248, 4, 1); 
INSERT INTO group_perms (id, groupid, permid) VALUES (249, 4, 1); 
INSERT INTO group_perms (id, groupid, permid) VALUES (250, 4, 1); 
INSERT INTO group_perms (id, groupid, permid) VALUES (251, 4, 1);

Macro

My solution was to move point to the start of the unique id 248 in the first row and to set a goal column (C-x C-n) at column 65 (this location). I then created the following macro by calling M-5 M-0 M-0 M-x kmacro-start-macro-or-insert-counter (in my case M-x kmacro-start-macro-or-insert-counter is bound to S-f7). This starts recording the macro and initializes the counter to 500. The macro I recorded is shown below. The macro kills the existing value of the unique id, and then replaces it with the value of the counter. The call to kmacro-start-macro-or-insert-counter also increments the counter. I then move to the next line which because of setting the goal column brings me back to column 65 which is the beginning of the next unique id that I need to replace.

C-SPC			;; set-mark-command
M-f			;; forward-word
C-w			;; kill-region
<S-f7>			;; kmacro-start-macro-or-insert-counter
M-f			;; forward-word
DEL			;; backward-delete-char-untabify
5			;; self-insert-command
C-n			;; next-line

I then run the macro for each line by holding down my f7 key that is bound to M-x kmacro-end-or-call-macro. The total number of key chords for the entire process is around 15. Here is a sample of the resulting output:

INSERT INTO group_perms (id, groupid, permid) VALUES (500, 5, 1); 
INSERT INTO group_perms (id, groupid, permid) VALUES (501, 5, 1); 
INSERT INTO group_perms (id, groupid, permid) VALUES (502, 5, 1); 
INSERT INTO group_perms (id, groupid, permid) VALUES (503, 5, 1);

Since the macro has saved me much more time than if I had done it line by line, I decide to get a cup of coffee. In all seriousness though, the macro way is faster than the alternatives.

Note: I think the kmacro-start-or-insert-counter and kmacro-end-or-call-macro are Emacs 22 specific functions, but you could do the same thing in Emacs 21 with a few more key strokes. Also, by default Emacs 22 binds the fancy start and end macro functions to f3 and f4. I have f3 and f4 bound to other functions, so I use S-f7 and f7.

Tags: kb-macros · tips

21 responses so far ↓

1 ugglan // Apr 6, 2007 at 9:10 am

Nice! I didn’t know about the new macro-commands in Emacs 22, that is incredibly useful.
2 Ryan McGeary // Apr 6, 2007 at 1:04 pm
For even fewer keystrokes, consider the kill-word (M-d) function. With it, the first three commands of the macro:
```
C-SPC			;; set-mark-command
M-f			;; forward-word
C-w			;; kill-region
```
Can turn into this:
```
M-d			;; kill-word
```
3 Russell // Apr 6, 2007 at 3:45 pm

For emacs 21, could use the increment-register instead of kmacro-start-or-insert-counter.

It does sound like I need to look into emacs 22.
4 Ben Karel // Apr 6, 2007 at 10:29 pm

Just for completeness, Vim users would do the following:

1) Position cursor at any digit in “248”.
2) Type `qq` to begin recording keystrokes.
3) Type `252 C-a j Esc` to turn 248 into 500 and move the cursor down a line
4) Type `79@q` to change the next 79 lines.

13 keypresses total (C-a counts as two keys pressed). For emacs… 26, with Ryan’s fix.
5 Ryan McGeary // Apr 7, 2007 at 3:04 am

Ben, I like your idea of of adding 252 to each id value as part of the macro, but it makes the assumption that the old id values were all in order and always incremented by one. That assumption is probably a good one, but makes it difficult to compare apples to apples. Don’t forget about updating the groupid value.

The truth is that powerful macros in any editor is just cool and rewarding. Regardless, thanks for the vim tip; I like knowing the ways of other power editors.
6 Dave Benjamin // Apr 7, 2007 at 6:50 am

I have these two keyboard shortcuts defined:

(global-set-key “\M-n” ‘bs-cycle-next)
(global-set-key “\M-p” ‘bs-cycle-previous)

They allow you to cycle back and forward through all the regular buffers that exist. Amazingly, these commands actually get recorded in keyboard macros.

So, one trick I like to use is to open a bunch of files that I need to make the same operation to, record a macro ending in M-n, and then repeat the macro for each buffer. For those times when I need to do something complicated and don’t really want to think in regex, this can be quite handy.
7 Ben Karel // Apr 7, 2007 at 6:50 pm

Ryan: Well, that’s true, but in the example, they were auto-incrementing already. If you wanted to turn a group of random IDs into incrementing IDs, the vim way would be something along these lines:

1) Position cursor at start value.
2) `cw 500 Esc b`
3) `qi` – start recording (9)
4) `“r yw j dw “rP Esc ^a b` – copy value, down a line, replace this line’s value, increment it, back to start column.
5) `q 78@i` – Repeat for 78 lines.

Twenty… eight characters now? Oh noes! grin

To update the group id, I’d do a replace in visual block mode. Assuming the cursor is positioned on the first 4:
`^V 80j r5`

Best to keep separate functions separate, I figure.
8 nirs // Apr 8, 2007 at 1:21 am

Nice, but this is not a good solution.

What you need here is a tiny function that will generate this 80 lines using serial number/constants, etc.

(btw – mail is required but it is not marked as such)
9 Ryan McGeary // Apr 8, 2007 at 1:54 am

nirs, Thanks. I updated our site template to show that name and email are required when posting comments.
10 Rob Christie // Apr 8, 2007 at 3:28 am
nirs – The sql was simplified for the post (for formatting reasons), although the macro used to modify it was exactly the same. The actual sql looked like this (2 of the 80 lines):
```
INSERT INTO TGROUPPERMISSION ( UID_PK, GROUPUID, PERMISSIONUID, ACCESSIBLE ) VALUES (250, 4, 3, 1); 
INSERT INTO TGROUPPERMISSION ( UID_PK, GROUPUID, PERMISSIONUID, ACCESSIBLE ) VALUES (251, 4, 4, 0);
```
The 80 lines of sql define permissions for a given group in the system. Values of PERMISSIONUID and ACCESSIBLE change on on each line. Functionally in the system I was creating a new user group with similar permissions as the old group. It’s very much a one off change, so I don’t think there would be a good way to make a simple function to perform it. I think this is where a keyboard macro shines.
11 Nikolaj Schumacher // Apr 10, 2007 at 2:16 pm

I must admit, I didn’t know about counters. Is there also a built-in way to do the first vim thing that Ben listed, i.e. increment all numbers by the same value? I’ve found I need that a lot.

I have written my own package that helps with that sort of tasks …
http://nschum.de/src/emacs/macro-math/
12 Justin Gombos // Apr 14, 2007 at 3:05 pm

I’m using an older version of emacs. What’s a substitute for the kmacro-insert-counter macro?
13 Rob Christie // Apr 23, 2007 at 3:33 am
Justin – Sorry it took so long to respond… the day job has been busy. Here is an example of how to do it in emacs 21. First, set the column goal as above at the start of the first number that you want to replace. Then perform C-u 500 C-x r n a where a is the register name. This sets the value of 500 in register a. Start the macro, C-x (. The following is a listing of the macro I used.
```
C-SPC			;; set-mark-command
M-f			;; forward-word
DEL			;; backward-delete-char-untabify
C-x r g			;; insert-register
a			;; self-insert-command
2*M-f			;; forward-word
DEL			;; backward-delete-char-untabify
5			;; self-insert-command
C-u 1 C-x r +		;; increment-register
a			;; self-insert-command
C-n			;; next-line
```
As you can see in the macro above, I insert the register value and then increment it. I then moved to the next line and stopped recording the macro, C-x ), I run the previous macro by calling C-x e.
14 Vj // May 18, 2007 at 4:26 pm

Hi in Emacs 22 you can call a function for each regexp match – per Steve Yegge.

Wondering if we could use a regexp+function to do the above increment?

15 Vj // May 18, 2007 at 4:53 pm

Ok, I had to try it and this is what I have :

Input text :
INSERT INTO temp (col1, col2)  VALUES (   30, 50)
INSERT INTO temp (col1, col2)  VALUES (   40, 60)
 
REGEXP search pattern:
^\(INSERT.*VALUES.*([ ^I]*\)\(\w+\),\(.*$\)
 
REGEXP replacement(increments BY 1): 
\1\,(1+ (string-to-number \2)),\3)
 
 
Output text :
INSERT INTO temp (col1, col2)  VALUES (   31, 50)
INSERT INTO temp (col1, col2)  VALUES (   41, 60)

16 Vj // May 18, 2007 at 4:54 pm

Thanks again to Steve Yegge.

Please note above that insert-dot-star-values-dot-star- shows up as a bolded values in the blog.
17 Rob Christie // May 18, 2007 at 5:36 pm

Cool. More than one way to skin the cat.

I added a
<pre lang=“sql”>
...
</pre>

Just as an fyi. We use textile for formatting and I think you can use it in your comments here.
18 Rob Christie // May 18, 2007 at 5:41 pm

We are using the WP Syntax plugin for wordpress to do syntax highlighting.
19 oOgerryOo // Dec 16, 2007 at 5:37 pm

Hi,
I’m Gerald.

Just saying hi – I’m new.
20 Luke Hoersten // Nov 30, 2008 at 6:47 am

I never knew you could send arguments to procedures called with key commands by preceding the key command with meta escaped characters! (The M-5 M-0 M-0 for example). Thanks for the tip! I now am able to jump around like vim users!
21 audilller // Dec 18, 2009 at 7:39 am

Hi.
My PC worked slowly, many mistakes and buggs. Please, help me to fix errors on my PC.
My operation system is Win Vista.
With best regards,
audilller

M-x all-things-emacs