Building a .sql file with `vim`

by Quintessence

While playing around with MySQL today I had an idea that generating various mysql statements would actually be a good way to practice getting familiar with vim. I remember back when I was getting started that I actually found vim really daunting, almost so much so I nearly didn't get the habit off the ground, but then I found myself doing a ton of sysad-esque tasks and it was just so much easier to use once I got past the fear.

It's worth mentioning that there's a lot of new information for the beginning vim user in this post. The idea is not for you to remember it all, it's to give you an arsenal of common commands that you can make use of by practicing getting a file from practically nothing to a more useful form.

Prerequisities

To see if your computer already has vim, please open Teminal / iTerm / your terminal app of choice and enter the following command:

vim mysql-vim-playground

This will create an empty file named mysql-vim-playground if you have vim installed. If you do not, and you see a command not recognized error, please install vim with your package manager (yum / apt for most Linux distros and brew for macOS).

Windows Users

If you are running a Windows machine, this exercise is for the Unix-based OS learner in you. If you are running Windows 10 you can try installing the Linux Subsystem for Windows. Alternatively, if you have access you can spin up a DigitalOcean droplet or Linode instance with your Linux distro of choice.

A quick note

Although you can copy and paste commands directly from this blog post, to help with your muscle memory I do recommend actually typing them out each time.

Put your right foot in...

Now that you have vim opened, let's quickly get familiar with the basic-basics. There are two "modes" called "command mode" and "insert mode" (i.e. "edit mode"). vim will open in command mode by default, so to enter insert mode you simply hit the i key. To return to command mode, hit the ESC key.

To get started, our users are Jayne Cobb, Inara Serra, George Parley, and Antimony Carver. You can copy and paste them, like so:

Jayne Cobb, Inara Serra, George Parley, and Antimony Carver

When you go to paste them in make sure you're in insert mode by hitting i, otherwise you'll enter insert mode when vim hits the a character in Jayne -> a is for append, which basically changes the cursor position when you enter insert mode.

Make some newlines

In vim you can make substitutions with :s. Please enter the following when in command mode:

:s/, /\r/g

We've just swapped the , (that's a comma and a space) with a newline, which is signified by \r (for "carriage return). The g signifies a global change on that line, so every , is replaced with a newline instead of only the first.

Pesky words

Although this was mostly successful, on our last line we have and Antimony Carver. We want just the name, so in command mode navigate to the last line by hitting shift+G and dw.

d is the delete character, and w means "word". How vim breaks up words can be a topic unto itself when discussing code lines, but in general when you are using "regular ol' written language" a word is what you'd expect.

Making usernames out of users

Now to do some next-level deletes. The usernames for these people are going to be their first names and last initials. We're going to delete the last word of every line except for the first character. The most brain-friendly way to do this is with a macro, unless you want to also make regex your friend on your first foray into vim 😉

Making a macro will allow you to perform the action on the first line, then repeat it on other lines. To get started recording, when in command mode enter qq. You'll see at the bottom of the screen you now have an @q. The first q is to record, the second q is the name of the register where it's being stored. What's a register? Think of it as "Save to Slot Q".

On the first line do the following commands:

  • w
  • right arrow (to go over one character)
  • dw
  • left arrow
  • x

Now to stop recording hit q (yes, again). What we just did was go from the start of line one, move forward a word, move forward a character, delete to the end of the word, move back a character, and delete the character under the cursor with x.

Now to repeat.

:2,$norm @q

2,$ tells vim to start on line 2 and repeat until the last line of the file, $. You'll encounter a lot of scenarios where $ means "end". As a quick side exercise, hit 0 to go to the beginning of your current line and $ to go to the end. Oscillate between the two a couple of times to get a feel for it.

norm @q plays the macro "stored in slot Q".

Herd some camels

At this juncture, there's just one more step to getting our usernames: removing the camel casing. Do this, we're going to make use of visual mode. Go to the beginning of line 1 and do the following:

  • v
  • $
  • shift+G
  • $
  • u

Visual mode allows you to visually select a series of text and make changes on the whole selected text. u makes all the selected characters lowercase and U would make them all uppercase. If you did not want to use visual mode, you could alternatively navigate to each character and use ~ to flip its case.

Before we proceed

At this point you may have noticed a few things about vim commands. That they usually come in individual letters or short words, like d, w, s, and norm, and that they are stackable. For example, dw stacks to delete a word. You can also specify ranges on command lines, like 2,$ for s or norm. Keep these properties in mind when you start branching out to learn new vim commands, e.g. e to hop words but landing on the last letter rather than the first.

MySQL commands

We're going to create our users on a MySQL schema with multiple databases and going to grant a few different commands to give our users read only or read and write access to different databases.

Please note that since the primary goal of this exercise is to practice vim with a real world example, that no MySQL databases have been set up. You're more than free to practice on your own, though, if you have some handy.

We're going to plan ahead and know that the databases in question are firefly and gunnerkrigg so we'll need to create the users once and apply permissions to two different databases.

Do No Repeat Yourself ... well maybe this one time

Planning is important. Since we'll need to create users once and then add them to two databases, it'll help us if we copy out our usernames a few times. To yank a few lines of text, in command mode entery the following:

:1,$y

Navigate to the bottom of the file with shift+G and without entering insert mode paste your copied items with p. Go to the bottom with shift+G and paste with p again. It's important not to just paste twice with pp (although you can) due to where your cursor falls: you'll have a couple of usernames not be in order any more.

To make the lines a little more readable, I've inserted newlines between each block of usernames. So my text file now looks like this:

jaynec
inaras
georgep
antimonyc

jaynec
inaras
georgep
antimonyc

jaynec
inaras
georgep
antimonyc

The line numbers I'll use on subsequent steps will assume you have done the same.

Creation

Let's set up our creates on lines 1-4. Skipping over the finer points of MySQL, the command for creating a user looks like this:

create user 'USER'@'%' identified by 'SOOPERPASSWORD';

So let's do some swapping:

:1,4s/^/create user '/

Just like $ means "end", ^ means "beginning". So here we're replacing the beginning of each line with create user '. Now for the rest:

:1,4s/$/'@'%' identified by 'SOOPERPASSWORD';/

Now for the next block. The Firefly crew need RW access to their own ship's database, but maybe the students of Gunnerkrigg do not. They can have RO (read only) access though. The syntax for this, again skipping over MySQL learnings, is:

grant all privileges on <database>.<table> to 'USER'@'%';
grant select on <database>.<table> to 'USER'@'%';

For our file, let's enable line numberings with :set nu. There, that'll make this next bit easier! Since in each case we're going to allow access to all tables in a database, instead of naming table <table> we're going to use the wildcard character *:

:6,$s/^/grant all privileges on firefly.* to '/

And to finish the command:

:6,$s/$/'@'%';

So now your file should look like this:

create user 'jaynec'@'%' identified by 'SOOPERPASSWORD';
create user 'inaras'@'%' identified by 'SOOPERPASSWORD';
create user 'georgep'@'%' identified by 'SOOPERPASSWORD';
create user 'antimonyc'@'%' identified by 'SOOPERPASSWORD';

grant all privileges on firefly.* to 'jaynec'@'%';
grant all privileges on firefly.* to 'inaras'@'%';
grant all privileges on firefly.* to 'georgep'@'%';
grant all privileges on firefly.* to 'antimonyc'@'%';
grant all privileges on firefly.* to ''@'%';
grant all privileges on firefly.* to 'jaynec'@'%';
grant all privileges on firefly.* to 'inaras'@'%';
grant all privileges on firefly.* to 'georgep'@'%';
grant all privileges on firefly.* to 'antimonyc'@'%';

Ah, looks like we were a little overzealous with our 6,$.

Always moving forward

Next step: delete the line where there was no username at all by navgiating to line 10 and entering d$. Now, you recall that we don't want George and Antimony to have unchecked access to all of Firefly's database, so we'll fix that with:

:8,9s/all privileges/select/

Now to clean up the next batch for gunnerkrigg. First:

:11,14s/firefly/gunnerkrigg/

And you may have guessed it, but:

:11,12s/all privileges/select/

Your final form

The final form of your file should look like this:

create user 'jaynec'@'%' identified by 'SOOPERPASSWORD';
create user 'inaras'@'%' identified by 'SOOPERPASSWORD';
create user 'georgep'@'%' identified by 'SOOPERPASSWORD';
create user 'antimonyc'@'%' identified by 'SOOPERPASSWORD';

grant all privileges on firefly.* to 'jaynec'@'%';
grant all privileges on firefly.* to 'inaras'@'%';
grant select on firefly.* to 'georgep'@'%';
grant select on firefly.* to 'antimonyc'@'%';

grant select on gunnerkrigg.* to 'jaynec'@'%';
grant select on gunnerkrigg.* to 'inaras'@'%';
grant all privileges on gunnerkrigg.* to 'georgep'@'%';
grant all privileges on gunnerkrigg.* to 'antimonyc'@'%';

Hardly recognizable from where we started, isn't it?

I hope this helps you ease into more vim explorations 😁