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 Get-SQL.xxxGet-XXX” ,SQLGet-
> 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”
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 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 Get-SQL
In my Get-SQL[ValidateNotNullOrEmpty()]
[string]$Session = "Default"
A function can find out the name was used to invoke it by looking at $MyInvocation.InvocationName. If Get-SQL -session the value of $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 $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 Get-SQL New-Alias -Name $session -Value Get-SQL –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 Get-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.
