Donnerstag, 26. November 2009

And now using the data to generate sql INSERT-statements

Before SQL-Serve 2008 sql bulks of SQL Insert-Statements are so redundant, that they are not readable.
Of corse, if you are too clever you ommit the column list after INSERT INTO.
Try it and learn it the hard way.

One of my common tasks is generating Insert Statements. Perhaps this way:


$list = (             
( 1, 'PS', 'PowerShell is the most inovative scripting language'),
( 2, 'Python', 'has the most concise formating'),
( 3, 'Algol60', 'the mother of quoted identifiers'),
( 4, 'Algol68', 'the root of array slicing'),
( 5, 'c#', 'if only you were caseinsentitiv casesensitivity is ORACLE''s greates fault'),
( 6, 'SQL', 'has an Insert syntax which is worse than Street Basic''s Data-Statement
(where we put the assembler code you remember)'
)
)

foreach ($item in $list)
{
$id, $code, $note = $item
$note = $note -replace "'", "''" # you have to double embedded apostrophes
"insert into Computer_Languages ( Id, Code, Note) values ( $id, '$code', '$note')"
}

Background to problem with nested data structures

Thanks Jeff for the quick response. For some reasons I memorized the following pattern:

$list = @(            
(1,2)
(3,4)
)
$list.count



which flattens the structure and yields 4 instaed of the correct syntax

$list = (            
(1,2),
(3,4)
)
$list.count



which yields 2

Once using the wrong pattern there is no escape without help.

In Python it would be basic. in PowerShell ???

I hope there is a better solution in Powershell to build $list

$list = @()            

function add-Tupple
{
$index = $list.count
$global:list += 1
$global:list[$index] = $args
}


add-Tupple 'Ps' 'has object pipelines'
add-Tupple 'Python' 'has natural nested datastructures'

$list.count

foreach ($item in $list)
{
$code, $name = $item
"Code: $code"
"Name: $name"
}

# in python (('Ps', 'has object pipelines'), ('Python', 'has natural nested datastructures'))

Sonntag, 15. November 2009

Manage Modules The ISE Way

Hello again

Last month we got a lot of modules to try. Example given PowerShellPack or BSonPosh Powershell Module.

Of corse you do not want to add all the modules you find to your profile.
To be honest isepack is the only module I load in my profile.
I just added a smal script to my personal toolbox of ISE Add-On Extensions.

It adds and removes modules, list the currently loaded and shows the files of a module.

Pleae be warned, not all modules are writen to be removed, e.g. ispack does not remove its menus, when removed. But you have a starting point to play with.

And thanks again to James Brundage, whoes Copy-ColoredHTML (it's in the ISEpack module of the PowerShellPack) makes posting PowerShell code so easy.

function Update-ModuleMenu()            
{
$all_modules = get-module -list
$loaded_modules = get-module

$remove_items = @{}
$loaded_modules | % {
$name = $_.name
$remove_items[$_.name] = "Remove-module $name; Update-ModuleMenu"
}

$add_items = @{}
$all_modules | ? {! ($loaded_modules -contains $_ )} |
% {
$name = $_.name
$add_items[$_.name] = "Import-module $name; Update-ModuleMenu"

$list_items = @{}
$all_modules | % {
$list_items[$_.name] = "gci (split-path (get-module $_ -list).Path) | select Name"
}

$items = @{
" loaded" = {Get-Module | Sort-object | select name, path | fl}
" add" = $add_items
" remove" = $remove_items
"files" = $list_items
}
}

#$items

Add-IseMenu -name 'Module' $items
}


Update-ModuleMenu

Donnerstag, 12. November 2009

Reload-ISE - Part III

I added some code to save using different encodings and to show the encoding of the current file.

I thought default for new files was utf8. Surprize it is BigEndianUnicode. But as long as it uses a BOM-Mark i will not complain. (Otherwise read my comments. ;-) )

Play and learn.


            
[cmdletbinding()]
param(
# use this switch, when you have ISEpack installed and want a default menu added
# import-module isepack
# . -ISEpackCX
[switch]$ISEpackCX
)


function Reload-ISE ()
{
<#
.Synopsis
Reload file asuming given encoding
.Description
Reload the file in the current editor asuming given encoding,
the file is not saved automatically. You can try other encondings on
the unmodified file.
.Example
Reload-ISE utf8
Reloads the file asuming utf8 encoding. This is useful when the file
has no BOM
.Example
Reload-ISE
Prompts for a codepage number and reloads the file using it
.LINK
Script posted to:
http://pauerschell.blogspot.com
by Bernd Kriszio (twitter: bernd_k)
.Parameter encoding
encodings known to Get-Content:
Unknown, String, Unicode, Byte, BigEndianUnicode, UTF8, UTF7, Ascii
for a list of valid codepages see
http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx
.NOTES
use -verbose to show the current encoding of the reloaded file
and RMFC read my f... comments
#>

[cmdletbinding()]
param(
# encoding to use for reloading
[Parameter(Position = 0, Mandatory=$True)]
$encoding
)
$path = $psise.CurrentFile
$fullpath = $path.FullPath

if ( ("String", "UTF8", "UTF7", "Ascii", "Unicode", "BigEndianUnicode") -contains $encoding)
{
$text = Get-Content -Encoding $encoding $fullpath -ReadCount 0 -totalcount -1
$text = $text -join "`r`n"
}
else
{
$encoding = [int]$encoding
$bytes = get-content $fullpath -encoding byte
$encoding_obj = [System.Text.Encoding]::GetEncoding( $encoding )
$text = $encoding_obj.GetString($bytes)
}

$loadToNewEditor = $False
if ($loadToNewEditor )
{
$count = $psise.CurrentPowerShellTab.Files.count
$psIse.CurrentPowerShellTab.Files.Add()
$Newfile = $psIse.CurrentPowerShellTab.Files[$count]
$Newfile.Editor.Text = $text
}
else
{
$psise.CurrentFile.Editor.Text = $text
}
$psISE.CurrentFile.Encoding | Write-Verbose
}


if ($ISEpackCX)
{
# I'm beginning to dislike Add-IseMenu because I can't control the order of the items, I'm using blanks as workaround. Dirty old...
Add-IseMenu -name 'Reload/Save' @{
" Show encoding" = {$psISE.CurrentFile.Encoding| Select BodyName, IsSingleByte, EncodingName, CodePage| fl}
"Reload Codepage..." = { Reload-ISE } # in most cases you can't save back in this encoding, only export
"Reload Ascii" = { Reload-ISE Ascii }
"Reload BigEndianUnicode" = { Reload-ISE BigEndianUnicode } # this seems to be the default for new files in ISE
"Reload OEM850" = { Reload-ISE 850 } # you can't save back in this encoding, only export
"Reload String" = { Reload-ISE String } # seems to be the same as unicode
"Reload Unicode" = { Reload-ISE Unicode }
"Reload utf7" = { Reload-ISE UTF7 }
"Reload utf8" = { Reload-ISE UTF8 }
# To save means use an encoding, which ISE can read back again
"Save File ASCII" = {$psISE.CurrentFile.Save([Text.Encoding]::ascii)} # if you use this, you are insulting most Europeans
"Save File ANSI" = {$psISE.CurrentFile.Save([Text.Encoding]::default)} # my personal favorite ;-)
# I hope the following write BOM's. Unicode without BOM is more evil than OEM437 or OEM850
# I sentence everyone who uses unicode without BOM to 10 years VB3 coding
# (without PowerShell and on Vista to make it a real punishment ;-) )
"Save File utf7" = {$psISE.CurrentFile.Save([Text.Encoding]::utf7)}
"Save File utf8" = {$psISE.CurrentFile.Save([Text.Encoding]::utf8)}
"Save File unicode" = {$psISE.CurrentFile.Save([Text.Encoding]::unicode)}
"Save File BigEndianUnicode" = {$psISE.CurrentFile.Save([Text.Encoding]::BigEndianUnicode)} # as far as I know, that's ISE's default for new files
# Todo: Export in arbritary encodings into another file
}
}

Mittwoch, 4. November 2009

Reload-ISE - part II

I have extended my function, so that it now handles all Code Page numbers, like OEM 850, OEM 437 or whatever is listed in http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx. I even tried to make the header more standard.

This file is written in a special way, which I call ISEpackCX.
It can use ISEpack's Add-IseMenu to extend the Add-ons menu. If you wish, you can do without this and take care of the menu by your own.

Add the following lines to your profile

import-module isepack            
#. <your path>\Reload-ISE.ps1 -ISEpackCX


and save the following as Reload-ISE.ps1.

Happy ISE-extending

Bernd


          
[cmdletbinding()]
param(
# use this switch, when you have ISEpack installed and want a default menu added
# import-module isepack
# . -ISEpackCX
[switch]$ISEpackCX
)


function Reload-ISE ()
{
<# .Synopsis Reload file asuming given encoding .Description Reload the file in the current editor asuming given encoding, the file is not saved automatically. You can try other encondings on the unmodified file. .Example Reload-ISE utf8 Reloads the file asuming utf8 encoding. This is useful when the file has no BOM .Example Reload-ISE Prompts for a codepage number and reloads the file using it .LINK Script posted to: http://pauerschell.blogspot.com by Bernd Kriszio (twitter: bernd_k) .Parameter encoding encodings known to Get-Content: Unknown, String, Unicode, Byte, BigEndianUnicode, UTF8, UTF7, Ascii for a list of valid codepages see http://msdn.microsoft.com/en-us/library/system.text.encodinginfo.aspx .NOTES use -verbose to show the current encoding of the reloaded file #>
[cmdletbinding()]
param(
# encoding to use for reloading
[Parameter(Position = 0, Mandatory=$True)]
$encoding
)
$path = $psise.CurrentFile
$fullpath = $path.FullPath

if ( ("String", "UTF8", "UTF7", "Ascii", "Unicode", "BigEndianUnicode") -contains $encoding)
{
$text = Get-Content -Encoding $encoding $fullpath -ReadCount 0 -totalcount -1
$text = $text -join "`r`n"
}
else
{
$encoding = [int]$encoding
$bytes = get-content $fullpath -encoding byte
$encoding_obj = [System.Text.Encoding]::GetEncoding( $encoding )
$text = $encoding_obj.GetString($bytes)
}

$loadToNewEditor = $False
if ($loadToNewEditor )
{
$count = $psise.CurrentPowerShellTab.Files.count
$psIse.CurrentPowerShellTab.Files.Add()
$Newfile = $psIse.CurrentPowerShellTab.Files[$count]
$Newfile.Editor.Text = $text
}
else
{
$psise.CurrentFile.Editor.Text = $text
}
$psISE.CurrentFile.Encoding | Write-Verbose
}


if ($ISEpackCX)
{
# activate only those you need
# this is not stupid SQL-Management-Studio with no selection or select from all posible encodings
Add-IseMenu -name 'Reload' @{
" Codepage..." = { Reload-ISE }
"Ascii" = { Reload-ISE Ascii }
"BigEndianUnicode" = { Reload-ISE BigEndianUnicode }
"String" = { Reload-ISE String }
"Unicode" = { Reload-ISE Unicode }
"UTF7" = { Reload-ISE UTF7 }
"UTF8" = { Reload-ISE UTF8 }
}
}

Dienstag, 3. November 2009

Reload-ISE

Just had to work with some data in utf-8 without BOM. I want to extend this to OEM-850 later, but here the first draft.

And if you are really using ISE for productive things please send me (bernd_k) a note at twitter.

Sometimes I believe I'm the only one. At least in https://twitter.com/bernd_k/ise-users. I want to add you.

Feedback will improve my presentation. Otherwise you get it quick and dirty.

The function Add-IseMenu is in ISEpack http://code.msdn.microsoft.com/PowerShellPack.

Have fun with ISE

Bernd

           
function Reload-ISE ($encoding = 'UTF8')
{

# ToDo OEM 850 requires .NET
# Get-Content Encodings: Unknown, String, Unicode, Byte, BigEndianUnicode, UTF8, UTF7, Ascii

$path = $psise.CurrentFile
# $path
$fullpath = $path.FullPath
# $fullpath
$text = Get-Content -Encoding $encoding $fullpath -ReadCount 0 -totalcount -1

$text = $text -join "`r`n"

# $count = $psise.CurrentPowerShellTab.Files.count
# $psIse.CurrentPowerShellTab.Files.Add()
# $Newfile = $psIse.CurrentPowerShellTab.Files[$count]
# $Newfile.Editor.Text = $text

$psise.CurrentFile.Editor.Text = $text
}


Add-IseMenu -name 'Reload' @{
"String" = {Reload-ISE "String"}
"UTF8" = {Reload-ISE "UTF8"}
"UTF7" = {Reload-ISE "UTF7"}
"Ascii" = {Reload-ISE "Ascii"}
"Unicode" = {Reload-ISE "Unicode"}
"BigEndianUnicode" = {Reload-ISE "BigEndianUnicode"}
#"OEM850" = {Reload-ISE "OEM850"}
}