James O'Neill's Blog

August 24, 2011

Improving my score at PowerShell Golf

Filed under: Powershell — jamesone111 @ 9:58 am

There is mindset found in among scripting and programming people known as “Golf”. The objective is to get to the end using as few [key]strokes as possible. It’s not something I like to see in scripts and functions, but most of us don’t like to type any more than we have to at the command line. Tab expansion in PowerShell is a great feature for cutting down keystrokes (not beyond improvement, but truly great).

I’ve mentioned a couple of times recently that PowerShell’s parser gives GET- commands a sort of “automatic alias”: if you type “Service” where a command is expected, tab expansion won’t work but if you carry on and run the command, then  before it gives up and reports “The term 'service' is not recognized as the name of a cmdlet, function, script file, or operable program.” PowerShell looks for “Get-Service” finds it and calls that instead.  This works for user defined functions as well , so the Get-SQL command I wrote to send SQL commands via ODBC can be shortened to “SQL”, which is very fine and good and only has two things wrong with it.

  1. Commands need to wrapped in quotes. It doesn’t matter if they are single or double quotes, but if you are pasting in something which was written for MySQL you need to have your wits about you because like PowerShell it allows you to use single or double quotes in the command .
  2. It’s still more keystrokes than using the MySQL Monitor in an SSH session.

What I wanted to do was use a symbol to mean “Send this to SQL”.”PowerShell already uses most of the symbols on the keyboard, although its parser is flexible enough to allow some to be used as aliases – for example when it sees “40 % 13” the % sign is interpreted as the “modulo” operator but when it sees “dir | % {…% is an alias for “ForEach-Object” . Possible though it is, making the $ or > signs into aliases feels like the first dancing step down the path to madness, so for SQL I used ¬ (I’ve used the same technique for the Invoke-RemoteCommand wrapper I wrote for the netCmdlets Invoke-SSH command for which I use ~ )

I want to be able to type
¬ select * from mytable where name="james"
and have my command go into the default ODBC connection (or the default SSH connection or whatever) .
But it won’t work if I simply use Set-Alias -Name "¬" -Value "Get-SQL" , because “Select”,”*”,”from”, “mytable” and so on each get treated as distinct parameters and the quote marks will be lost around james. PowerShell has a “ValueFromRemainingArguments” option for a parameter but that won’t deal with the quotes issue (and there will be an problems deciding what belongs to other parameters like the ODBC connection string ).

My solution was to alias ¬ to an intermediate function named Hide-GetSql which contains 1 line
Function Hide-GetSQL {
  Get-Sql -sql ($MyInvocation.line.substring($MyInvocation.OffsetInLine))
Set-Alias -Name ¬ -Value Hide-GetSQL

Normally I hate seeing references to $Args or $MyInvocation.line but here I have to make an exception, this gets the line which was used as a single string and truncates it after the function / alias name.  PowerShell’s parser will try to split this line into sections if it contains semi colons – the function still works, but any attempt to process a second command will almost certainly cause a spurious error. For my use it hasn’t been a problem – your mileage may vary.


Blog at WordPress.com.

%d bloggers like this: