Tarin Gamberini

A software engineer and a passionate java programmer

The first time I have used Vim at work

Some time has passed since I started learning Vim. I didn’t wanted to use it at work because I don’t think I’m as productive as I’m with the editor I usually use… till this morning.

Indice

An SQL insert statement

Today I was asked to execute about 3000 SQL inserts in a database. The inserts were provided to me in a text file insert_statements.sql generated from a database at which I have no access. The insert statements looked like the following once:

insert_statements.sql
1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO Customers
            (id, CustomerName, ContactName, Address, City, PostalCode, Country)
     VALUES (1,'Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
INSERT INTO Customers
            (id, CustomerName, ContactName, Address, City, PostalCode, Country)
     VALUES (2, ... );
...
...
...
INSERT INTO Customers
            (id, CustomerName, ContactName, Address, City, PostalCode, Country)
     VALUES (3000, ... );

The Customers table had been already populated with other important data, therefore I would have inserted the new data after the existing once.

It was not a SQL problem, because it was sufficient replacing all the id values (1, 2, …, 3000) with a CustomersSeq.nextval: a sequence which would have generated the right id values. Instead it was an editing problem! How to replace an always different number for 3000 times?

As a developer you are likely thinking to a program which parses the insert_statements.sql input file generating a modified_insert_statements.sql output file. Instead I opened insert_statements.sql in Vim and I typed:

qrjjf(lcwCustomerSeq.nextval<ESC>j0q2999@r

and that’s all!!! The 3000 statements looked like as I needed:

modified_insert_statements.sql
1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO Customers
            (id, CustomerName, ContactName, Address, City, PostalCode, Country)
     VALUES (CustomerSeq.nextval,'Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
INSERT INTO Customers
            (id, CustomerName, ContactName, Address, City, PostalCode, Country)
     VALUES (CustomerSeq.nextval, ... );
...
...
...
INSERT INTO Customers
            (id, CustomerName, ContactName, Address, City, PostalCode, Country)
     VALUES (CustomerSeq.nextval, ... );

It took me about 30 seconds in total, not bad :-)

Vim magic explained

Clearly qrjjf(lcwCustomerSeq.nextval<ESC>j0q2999@r is not a wizardry chant, let me explain it.

Opening Vim the cursor is over the I of the first INSERT statement. Then I typed:

  1. qr starts recording a macro (a repeatable set of commands) called r, which in my mind stands for «replace»;
  2. jj moves the cursor two lines down, because j in Vim is like the down arrow key;
  3. f( finds the first ( and move the cursor over it (the cursor is over the ( after the VALUES string);
  4. l moves the cursor one character right, because l in Vim is like the right arrow key (the cursor is over the first digit of the id value);
  5. cw (change word) deletes the characters from the cursor position to the end of the word (therefore it deletes the id value) and put the editor in «insert» mode;
  6. CustomerSeq.nextval inserts the text CustomerSeq.nextval;
  7. <ESC> makes the editor exiting from «insert» mode reputing it in «normal» mode;
  8. j moves the cursor one line down (on the next insert statement);
  9. 0 moves the cursor to the begin of the line (over the I of the INSERT statement);
  10. q stops recording the macro. Now we are in a similar situation like we were just after opening Vim: the cursor is over the I of the second INSERT statement;
  11. 2999@r repeats 2999 times the «replace» macro.

Vim rocks!

If you don’t know Vim you might asking why the heck someone should ever learn such strange character sequences. Yes, they seem strange but they are not. In fact, the first learning steps a new Vim user is recommended to do is vimtutor, a basic 30 minutes tutorial, in which you learn much more than the commands described above at points 2..9. Indeed the only commands I used, not cover by vimtutor, are the once at points 1, 10 and 11, all related to Vim macros.

Post a comment

A comment is submitted by an ordinary e-mail. Your e-mail address will not be published or broadcast.

This blog is moderated, therefore some comments might not be published. Comments are usually approved by the moderator in one/three days.