How to use a macro for vertical alignment of code

[mattmc3]

From a programming perspective, aligning similar statements vertically is an often overlooked improvement to code maintainability. While there are undoubtedly some more robust and generic methods for doing this sort of thing automatically, sometimes you just want the quick-and-dirty solution. This is where a TextPad temporary macro can come in handy, but knowing how to make it work takes a little ingenuity and some practice. Hopefully this tutorial will serve as a good starting point.

First off, what do I mean by vertical alignment? Well, lets take a look at some SQL from a fake scenario I mocked up. The specific technologies aren't at all important, as this technique will work for lots of different vertical alignment scenarios. But, for the sake of making the tutorial interesting let me give you a little story.

Lets say that you are testing the usage of Ruby on Rails against an existing database. RoR offers you some great benefits if you follow their naming scheme for your database tables, but you really don't want to go and rename everything (even though you're surely not doing this in a production environment first!), so you decide to create some SQL views to simulate the desired naming like this:

Code: Select all

1    -- mask tblEmployee using the Ruby on Rails standard
2    -- for RoR feasibility assessment
3    create view employees as
4        select
5            EmployeeId as id,
6            FName as first_name,
7            MI as middle_initial,
8            LName as last_name,
9            RecordCreateDt as created_on,
10           LastAccessed as last_accessed_at
11       from
12           tblEmployee

Then, once you create these views, you go to look at all the aliased names you've chosen, but reading those out of the view proves to be more difficult than necessary. Especially for views with 10 or more columns. This is where vertical alignment can help. Instead of the format above, perhaps you'd prefer something more like this:

Code: Select all

1   -- mask tblEmployee using the Ruby on Rails standard
2   -- for RoR feasibility assessment
3   create view employees as
4       select
5           EmployeeId     as id,
6           FName          as first_name,
7           MI             as middle_initial,
8           LName          as last_name,
9           RecordCreateDt as created_on,
10          LastAccessed   as last_accessed_at
11      from
12          tblEmployee

Here are step-by-step instructions detailing how to automate the process of turning the first SQL statement into the second one using a temporary macro in TextPad.

1.) Determine what the starting column should be for the item you're vertically aligning. In this case, it's the "as" keyword. Since RecordCreateDt is the longest column name, we want to use the column position of "as" on that line as the alignment point for all lines. In this case, the "as" starts at position 24. Remember the number 24 because you'll need it in step 8.

2.) Turn on Block Select Mode in TextPad by choosing "Configure… Block Select Mode" from the toolbar, or by hitting CTRL+SHIFT+F8.

3.) Position yourself to begin recording your macro. Move your cursor to column 1 of line 5 (the line with EmployeeId). It's always a good idea to start your macros from column 1 because you can easily press the "Home" key twice followed by the "Down arrow" at the end of your recording to ensure that you can play your macro over-and-over without interruption.

4.) Open the replace dialog box from the Search menu, or hit CTRL+H. Enter the text " as" without the quotes in the Find what dropdown (notice the prefixed space before the "as"). Enter "\nas" without the quotes in the Replace with dropdown (notice I removed the prefixed space this time). Make sure that the Regular expression checkbox option is checked. Once the macro begins recording, we'll use this find & replace operation.

5.) Begin recording your macro by hitting CTRL+SHIFT+R.

6.) Hit Find Next, then hit Replace. You should see the " as" drop to the next line. Now hit Close to close the dialog.

7.) You should have a text selection that spans two lines. Hit the left arrow to unselect it.

8.) Hit CTRL+G to open the GoTo dialog. Use your mouse since mouse clicks are not recorded and tell the dialog you want to go to column 24 (see step 1 for why to use 24 in this example).

9.) Because you had block select mode turned on, you could go to column 24 even though it extended beyond the end of the line. Press the "Delete" key to condense the "as" area up from the next line.

10.) I end all macros by pressing "Home", "Home", then "Down arrow".

11.) Hit CTRL+SHIFT+R to stop recording.

12.) Repeat hitting the Play button as desired until all the lines align vertically.