vim

All posts tagged vim

I do everything in VIM. I expect that quite a few of my posts will dwell on my love affair with that app. Anyway, sometimes I want to run a block of text (SQL) against my database, and have the results returned to the text file that i’m working on.

For this I knocked up three shell scripts; dbe, ebd and rr.

dbe and ebd are mirrors of one another. The dbe script will take something like this from stdin

select count(*) as c
from customer
where name like '%blah%';

…and will output this to stdout
/* BEGIN QUERY ================================================== */
select count(*) as c
from customer
where name like '%blah%';
/* BEGIN RESULT ------------------------------------------------- */
+-----+
| c   |
+-----+
| 862 | 
+-----+
/* END RESULT (0) =============================================== */

The reason that there are those comments in the results (with ‘BEGIN QUERY’ and ‘END RESULT’ in them) is so that ebd has a change to take all of that in from stdin and give the original back to you on stdout.
Where rr comes in is that you can pass that block of output (with the ‘BEGIN QUERY’ etc. in it) into rr along with the query command that you want to re-run. If you were to pass this…
/* BEGIN QUERY ================================================== */
select count(*) as c
from customer
where name like '%blah%';
/* BEGIN RESULT ------------------------------------------------- */
+-----+
| c   |
+-----+
| 862 | 
+-----+
/* END RESULT (0) =============================================== */

…into rr dbe as stdin, you’d get same output (unless you had changed the query or the data had changed in the meantime). If you wanted to rerun that query after adjusting the query, you could easily pass the whole block (query and result) into rr, and get roughly the same thing back, only with an updated answer…
/* BEGIN QUERY ================================================== */
select count(*) as c
from customer
where name like '%blah blah%';
/* BEGIN RESULT ------------------------------------------------- */
+-----+
| c   |
+-----+
| 101 | 
+-----+
/* END RESULT (0) =============================================== */

Right, if you’re still reading you must love VIM as much as me! Those three commands aren’t very useful by themselves. They become very useful in VIM when you use the ! operator. In VIM you can nominate a block of text and hand it out to a command and have that block of text replaced with the output.
Before you do any of this, you will want to issue the :set number command to show line numbers in your file. Then, in a file like this…
1 Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor
2 incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud
3 exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute
4 irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla
5 pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui
6 officia deserunt mollit anim id est laborum.

…you can issue :2,4!wc
1 Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor
2       3      39     246
3 pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui
4 officia deserunt mollit anim id est laborum.

The 2,4 part nominated the three lines from the 2nd to the 4th. The ! (bang) operator is like a unix | (pipe) operator. In this example we’re passing those three lines to the wc (word-count) command. The answer is that those three lines contained three lines, thirty-nine words and 246 characters.
So, if you had dbe, ebd and rr in your PATH, you could take…
1
2 select count(*) as c
3 from customer
4 where name like '%blah%';
5

…and issue 2,4!dbe, and get…
 1
 2 /* BEGIN QUERY ================================================== */
 3 select count(*) as c
 4 from customer
 5 where name like '%blah%';
 6 /* BEGIN RESULT ------------------------------------------------- */
 7 +-----+
 8 | c   |
 9 +-----+
10 | 862 | 
11 +-----+
12 /* END RESULT (0) =============================================== */
13

You could then edit line 5 to look like this…
 5 where name like '%blah blah%';

…and then you can issue 2,12!rr dbe to see the new results…
 1
 2 /* BEGIN QUERY ================================================== */
 3 select count(*) as c
 4 from customer
 5 where name like '%blah blah%';
 6 /* BEGIN RESULT ------------------------------------------------- */
 7 +-----+
 8 | c   |
 9 +-----+
10 | 101 | 
11 +-----+
12 /* END RESULT (0) =============================================== */
13

In my environment I have named my script after the database that I want to query. I made it short and also made it easy to bash out with one hand so that I can be quick on my keyboard. Making ebd the reverse of dbe is important because it is how rr works out how to strip all of the markup out before re-querying.

I need one for Microsoft SQL Server soon, so I think i’ll have to write the equivalent of dbe and ebd in PHP.

dbe

#!/bin/bash

# Copyright (c) 2013, James Downie 
# 
# Permission to use, copy, modify, and/or distribute this
# software for any purpose with or without fee is hereby
# granted, provided that the above copyright notice and this
# permission notice appear in all copies.
# 
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL
# WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
# WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL
# THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR
# CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING
# FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF
# CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

S="$1"
OPTS="-t"
if [ "$S" == "-v" ]; then
  OPTS="$OPTS -E"
fi

T="`mktemp --tmpdir=/tmp/ dbe.XXXXX.sql`"
cat - > "$T"
echo "/* BEGIN QUERY ================================================== */"
cat "$T"
echo "/* BEGIN RESULT ------------------------------------------------- */"
T0="`date +%s`"
mysql $OPTS -u user -psecret -e "source $T;" mydb 2>&1
T1="`date +%s`"
let TD=T1-T0;
echo "/* END RESULT ($TD) =============================================== */"
rm "$T"

ebd

#!/bin/bash

# Copyright (c) 2013, James Downie 
# 
# Permission to use, copy, modify, and/or distribute this
# software for any purpose with or without fee is hereby
# granted, provided that the above copyright notice and this
# permission notice appear in all copies.
# 
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL
# WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
# WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL
# THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR
# CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING
# FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF
# CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

T="`mktemp --tmpdir=/tmp/ ebd.XXXXX`"
cat - > "$T"
BQ="`cat "$T" | nl -b a | grep "\/\* BEGIN QUERY " | head -n 1 | cut -f 1 | tr -d ' '`"
BR="`cat "$T" | nl -b a | grep "\/\* BEGIN RESULT " | head -n 1 | cut -f 1 | tr -d ' '`"
ER="`cat "$T" | nl -b a | grep "\/\* END RESULT " | head -n 1 | cut -f 1 | tr -d ' '`"
let h=BR-1
let t=BR-BQ-1
head -n $h "$T" | tail -n $t
rm "$T"

rr

#!/bin/bash

# Copyright (c) 2013, James Downie 
# 
# Permission to use, copy, modify, and/or distribute this
# software for any purpose with or without fee is hereby
# granted, provided that the above copyright notice and this
# permission notice appear in all copies.
# 
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL
# WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED
# WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL
# THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR
# CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING
# FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF
# CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.

T="`mktemp --tmpdir=/tmp/ rr.XXXXX`"
cat - > "$T"
if [ "$1" != "" ]; then
  A="$1"
  B="`echo "$1" | rev`"
  cat "$T" | $B | $A
fi
rm "$T"