The first thing that everyone learns about PowerShell Aliases is that they just replace the name of a command, aliases don’t do parameters. DIR
is an alias for Get-ChildItem
; you can’t make an alias RDIR
for Get-ChildItem –Recurse
. If you want that you need a function.
To quote Redmond’s most famous resident* I canna change the laws of physics, Captain, but I can find ye a loophole.
I wrote a function which I use a lot – 100+ times some days – named
Given an unknown command “Get-SQL.
”, PowerShell will see if there is a command “xxx
before reporting an “Not recognized” errorGet-XXX”
so I usually just run it as “,
” without the to SQL
. The function talks to databases and sessions it keeps connections open between calls: connections tend to named after places in South America, so to open a session I might run Get-
> SQL -Session Peru -Connection DSN=PERU
and then to find out the definition of a table I use
> SQL -Session Peru -Describe Projects
I’ve previously written about Jason Shirk’s tab expansion++ which gets a list of tables available to -Describe
(or tables which can be Selected from, or updated or Inserted into) in that session, and provides tab expansion or an intellisense pick list: this is incredibly useful when you can’t remember if the table is named “project”, or “Projects”, and tab expansion++ does the same for field names so I don’t have to remember if the field I want is named “ID_Project”, “Project_ID”, “ProjectID” or simply “ID”
has a default session: I might use-Session Peru 20 times in a row but still want to leave the default connection alone Get-SQL
I found myself thinking ‘I want “Peru” to be an alias for Get-SQL -Session Peru.
One line – New-Alias -Name $session -Value something
– inside
could set it all up for me when I make the connection.’ Get-SQL
As I said we all know you can’t do that with an alias, but doing this with functions is – bluntly – a nightmare, creating functions on the fly is possible but awkward, and Tab expansion++ wouldn’t know it was supposed to work with them (it does figure out aliases). Defining the functions in advance for each data source is would give me a maintenance headache…
Then I had a flash of inspiration: if I needed this to work for a built-in cmdlet, I’d need to create a proxy function … but
is already a function. So, if I can write something in the function to check how it was invoked it can say “A-ha! I was called as ‘Peru’ and ‘Peru’ is the name of a database session, so I should set $session to ‘Peru’.” Whatever the alias is, provided there is a connection of the same name it will get used. This turns out to be almost laughably easy. Get-SQL
In my
function the $session Parameter is declared like this Get-SQL
[ValidateNotNullOrEmpty()]
[string]$Session = "Default"
A function can find out the name was used to invoke it by looking at $MyInvocation.InvocationName.
If
is invoked with no value provided forGet-SQL
-session
the value of
will be set to ‘$Session
Default’
: if that is the case and there is a database session whose name matches the invocation name then that name should go into
like this:$Session,
if ($Session -eq "Default" -and $Global:DbSessions[$MyInvocation.InvocationName])
{$Session = $MyInvocation.InvocationName}
Of course the parameter is not part of the alias definition – but the function can detect the alias and set the parameter internally – the laws stand, but I have my loophole. Although it’s split here into two lines I think of the IF statement as one line of code. When
creates a connection it finishes by calling Get-SQL
New-Alias -Name $session -Value Get-SQL
–
So two lines give me what I wanted. Force.
Tab expansion++ was so valuable, but stopping here would mean its argument completers don’t know what to do – when they need a lists for fields or tables they call
, and this worked fine when aGet-SQL
-session
parameter was passed but I’ve gone to all this trouble to get rid of that parameter, so now the completers will try to get a list by calling the function using its canonical name and the default connection. There is a different way to find the invocation name inside an argument completer – by getting it from the parameter which holds the Command Abstract Syntax Tree, like this:
$cmdnameused = $commandAst.toString() -replace "^(.*?)\s.*$",'$1'
if ($Global:DbSessions[$cmdnameused]) {$session = $cmdnameused}
else {set $session the same way as before}
> Peru –Describe
Will pop up the list of tables in that database for me to choose “Projects”. There was a fair amount of thinking about it, but as you can see, only four lines of code. Result
* James Doohan – the actor who played “Scotty” in Star Trek actually lived in Redmond – ‘home’ of Microsoft, though Bill Gates and other famous Microsoft folk lived elsewhere around greater Seattle. So I think it’s OK to call him the most famous resident of the place.