ISE Find hotkeys in ISE custom menus

I (bernd_k) just ask on twitter

#powershell #ise is there a way to see which hotkeys are used or available?

Nearly at once I got a reply from

$psise.CurrentPowerShellTab.AddOnsMenu.Submenus |
select Action,DisplayName,@{n='Modifiers';e={$_.Shortcut.Modifiers}},@{n='Key';e={$_.Shortcut.Key}}

This is a good start, it lists the toplevel custommenu items. Some hotkeys are by ISEs own menus. I don't know how to handle that yet, but I extended this to include the submenus:

function flaten-Menus ($m = $psise.CurrentPowerShellTab.AddOnsMenu.Submenus)
{
$m | Where-object {$_.Submenus -ne $null} | % {flaten-Menus $_.Submenus}

$m | Where-object {!$_.Submenus -ne $null} |
select Action,DisplayName,@{n='Modifiers';e={$_.Shortcut.Modifiers}},@{n='Key';e={$_.Shortcut.Key}}

}


flaten-Menus |sort key

New-InsertStatement(4)

I just uploaded the code (SQL-Server only) to
http://code.google.com/p/poshcodegen/source/browse/trunk/New-InsertStatement.ps1
Minor extensions are still to be done.

The generated Functions are an example of parameterized queries.

New-InsertStatement (3)

Well meanwhile the script is working for some tables. But will do some further testing.

But now I'll show you an example of the style I will use to wrap Insert-statements

<#
Create Table Main_faults_some_Programming_languages(
language varchar(50),
main_fault varchar(50),
id int,
creator varchar(50)
)

#>


New-Connection 'Data Source=MyServer;Database=MyDatabase;user=sa;password=secret'


New-InsertStatement Main_faults_some_Programming_languages -columns (
'id', 'language', 'main_fault'
) -defaults @{
creator = 'Install'
}


I-Main_faults_some_Programming_languages 1 COBOL 'optional keywords'
I-Main_faults_some_Programming_languages 2 'ALGOL 60' 'blanks in keywords'
I-Main_faults_some_Programming_languages 3 LISP 'unpairable braces'
I-Main_faults_some_Programming_languages 4 SQL 'brain dead INSERT Statements'
I-Main_faults_some_Programming_languages 5 Oracle 'needs an administrator (perhaps thats no fault)'
I-Main_faults_some_Programming_languages 6 'T_SQL' 'no include scripts'
I-Main_faults_some_Programming_languages 7 PowerShell 'V2 not yet available for XP'


Close-Connection

New-InsertStatement (2)

Meanwhile I have a handcoded prototyp of the kind of function i want to generate running. It took some time to get aroung the ADO.NET stuff. The MSDN Dokumentation just isn't written for me. But the nice thing about ADO.NET is that as soon as it is properly wrapped it does a nice job.

And I'm using parameterized Queries. Yes that is the influence of the neverending talk about bind variables from some Oracle Admin.

Using parameterized queries is a two-part buisiness. You an initialization part to run once und afterwards you just bind the parameters and execute the query.

In C-like programming languages I would use a static variable within the function to remember if it is allready initialized.

In PowerShell I miss this feature. I get my job done using script scope, but that exposes the variables too much.

Now I'm going to see if it could be done adding a noteProperty. I do not feel that that is the natural way, but it may be the PowerShell way.

New-InsertStatement

Hello,
I' m back after some time. Had to do a lot of Ms-SQL -> Oracle conversions and optimizations.

Now I have a clear vision, of what has to be wrapped by PowerShell.

Lets look at the SQL Insert-statement. It looks rather innocent.

What can go wrong?

A common way to execute Insert-Statement is the use of the DBMS's on board tools like SQL-Management-Studio (resp. Query-Analyzer) or SQL*Plus.

When your varchars grow to moderate sizes SQL*PLUS nags you with insane linesize restrictions.

Further it requires special treatment of the character '@' which it uses to include further scripts from the current script (usefull feature by the way).

Querry-Analyzer is more tolerant, even when you insert into Text columns.

But don't think you can back at your long data using simple select-statments. There is a limit of I think 8000 characters for the output of each single column. And when your data contain linefeeds neither text nor grid display will satisfy you.


The next problem is more subtile and become evident when using Oracle.

It occurs when you are using lots of inserts into the same table.
This time the problem is performance.

Oracle individually compiles each insert-statement and stores its text in a limited resource called the shared Pool. There replacing valuable information by one time used garbage.

(Oracle DBAs find it unthinkable that we SQL-Server user can be lucky in a world without bind-variables. I joined some funny meetings in the past.)

To be honest there are some mystic settings to help here (cusor sharing etc..).
And SQL gains by use of transactions.

SQL by itself waited a long time a enhance the syntax with inserts of multiple rows using one statement.
SQL-Server 2008 has the feature, but this version isn't yet at our customers.

The lack of a general standard led the individual sellers of RDBMS solutions invent their own incompatible tools.

ms-sql bcp: I remember a never ending story of inserting using the wrong code-page.

ms-sql bulc-insert. Your sql-server needs read permitions to your data. Often you are forced to beg an admin.

Oracle DataLoader. That is the Orale kind of the solution. I like it least.

Those restrictions belong to the tools, not to the RDBMS.

When you access your Database using a programming language these restrictions vanish.

And PowerShell is a programming language. It has even the advantage that it doesn't require a compile step.

Next advantage is the possibility to hide differences in syntax like the (in SQL-Server optional fill word into) and least but not last the differences for datetime (Date) literals.

(Thank a lot Mister Snover for your vision of the object oriented pipeline in PowerShell. That said about SQL and Dateformats).


That is not the end of the story. If you have a grown database system, you would presumably change the order of some columns in your table to make the content more human readable.

Or some columns nearly allways happen to have the same value, which is not the defined default value.

All those I hope to attac using PowerShell as generator for Insert functions wrapping the INSERT-Statement.

I'll start with the SQL-SERVER solution, but I have identity columns, which have to be mapped to Oracle sequences in some way on my todo list.