Monday, August 25, 2008

Automatically generating PowerShell *.Types.ps1xml and *.Format.ps1xml

I helped a friend of mine a lot with this, so I thought I should share this. He currently works at a customer's site to create a PowerShell snap-in for administrating the customer its home-grown LOB. It's quite an application, used by 5000+ users and supported by about 20 administrators.

So far, the administrators are freaking out because administration this LOB is a snap with the cmdlets he has written. As more and more administrators use the PowerShell to administrate the LOB, more features requests are coming in and a lot of them are about how the objects are displayed inside PowerShell (see http://msdn.microsoft.com/en-us/library/cc136149(VS.85).aspx for more details).

The PowerShell needs two files to format the returned objects proberly: [NAME]. Types.ps1xml (general information) and [NAME].Format.ps1xml (table based output). These are very simple structured XML files but keeping these files manually in sync with your development and the feature requests can be a pain.

Because it was my idea that my friend should design a PowerShell snap-in instead of the originally planned COM interfaces, I searched for a solution. Since I found none, I wrote it myself (surprise!).

The good: The solution works as expected and my friend is happy. The bad: The code has no unit testing, works by using string constants and string.Format() and has some limitations (e.g. only a single Alias per property while the PS could support 100+ alias for the same property). However, you can use the code a starting point for your own solution.

My solution is based around the custom attributes [xPSObjectAttribute], [xPSPropertyAttribute] and [xPSSortPropertyAttribute]. During development, you simply decorate an object that is returned by a cmdlets with [xPSObject]. Oh, BTW: the xPS simple stands for "PowerShell Extension".

Within the object you then decorate a property with the [xPSProperty] attribute and define several properties on it. For example:

[xPSProperty(Default = true, Alias = "FullName")]

public string Name

When you later on run the PSTypeXMLFormatXMLGenerator:

xPSXMLOutput output = PSTypeXMLFormatXMLGenerator.GetPS1XML(typeof(TestClass));

You will get a type XML (some parts have been omitted):

<Type>

<Name>TestApp1.TestClass</Name>

<Members>

<MemberSet>

<Name>PsStandardMembers</Name>

<Members>

<NoteProperty>

<Name>DefaultDisplayProperty</Name>

<Value>FullName</Value>

</NoteProperty>

<PropertySet>

<Name>DefaultDisplayPropertySet</Name>

<ReferencedProperties>

<Name>FullName</Name>

...

<AliasProperty>

<Name>FullName</Name>

<ReferencedMemberName>Name</ReferencedMemberName>

</AliasProperty>

You can also change in which sequence the properties should be listed by PowerShell:

[xPSProperty(Default = true, Sequence = 10, Alias = "FullName")]

public string Name

[xPSProperty(Sequence = 20)]

public int Size

The result XML:

...
<Name>DefaultDisplayPropertySet</Name>

<ReferencedProperties>

<Name>FullName</Name>

<Name>Size</Name>
...

To provide on which properties the PowerShell should sort a list of this object, use the [xPSSortProperty] attribute (can be applied to several properties):

[xPSSortProperty(SortID = 1)]

public string Name

[xPSSortProperty(SortID = 20)]

public int Size

XML:

<Name>DefaultKeyPropertySet</Name>

<ReferencedProperties>

<Name>Name</Name>

<Name>Size</Name>

</ReferencedProperties>

For the table based output, PSTypeXMLFormatXMLGenerator does also generate a format XML.

[xPSProperty(Default = true, Sequence = 1, Alias = "FullName", ColumnWidth = 30)]

public string Name

[xPSProperty(Sequence = 2, ColumnName = "Size (K)", ColumnRightAligned = true, ColumnScript = "[int]($_.Size / 1024)")]

public int Size

Resulting XML:

<View>

<Name>TestClass</Name>

<ViewSelectedBy>

<TypeName>TestApp1.TestClass</TypeName>

</ViewSelectedBy>

<TableControl>

<TableHeaders>

<TableColumnHeader>

<Label>FullName</Label>

<Width>30</Width>

</TableColumnHeader>

<TableColumnHeader>

<Label>Size (K)</Label>

<Alignment>Right</Alignment>

</TableColumnHeader>

...

<TableRowEntry>

<TableColumnItem>

<PropertyName>Name</PropertyName>

</TableColumnItem>

<TableColumnItem>

<ScriptBlock>[int]($_.Size / 1024)</ScriptBlock>

</TableColumnItem>

...

I guess you are getting the basic idea how this class works. For your reference, here's a screen shot what the [xPSProperty] Attribute supports:




Once again, this class is not fully tested and should be seen as a basis for your own solution. If you use it, you would make me happy to leave a comment or provide a back link. Download the source and a sample application here.

Enjoy!

Friday, August 22, 2008

SQL Server string filter and replacement function

Often when I need to transmogrify data in SQL Server, I create custom stored procedures or update statement that filters incoming data. For example, a raw data import includes part numbers with letters while the system where this should go only allows 0-9 and “_”.

For a current project, I had to filter a lot of data so I decided to write a reusable function that NOT defines what is NOT allowed but simple the other way around: Define what you want.

I came across a interesting forum discussion that about such an function, but none of the solutions was exactly was I wanted. The main reason was that I wanted the function to be used as a filter and replacement at the same time. I give you a quick example.

Suppose you need a replacement that allows numbers only:

Input is “a12345abc”, replacement character is “*”, return should be “*12345*”. Please note that a single replacement character (“*”) is at the end, although the string ends with three non-numeric characters.

Now the same as a filter: Input is “a12345abc”, replacement character is “” (empty) , return should be “12345”.

I ended up by creating my own function “funcFilterString_ASCII” (roughly based on a solution in the forum by Peter Larsson from Helsingborg, Sweden). Usage is rather simple: @Source is the string that should be filtered, @Filter is the regex like filter and @Replacement is the character any no match should be replaced with (or empty if no matches should be removed).

Some examples:

print dbo.funcFilterString_ASCII('John Doe - 12345 New York _10', '[0-9]', '')
-- returns 1234510

print dbo.funcFilterString_ASCII('John Doe - 12345 New York _10', '[0-9]', '*')

-- returns *12345*10

print dbo.funcFilterString_ASCII('John Doe - 12345 New York _10', '[0-9_]', '')
-- returns 12345_10

print dbo.funcFilterString_ASCII('John Doe - 12345 New York _10', '[23]', '*')
-- returns *23*

print dbo.funcFilterString_ASCII('John Doe - 12345 New York _10', '[^0-9]', '*')
-- returns John Doe - * New York _*

To use it, simply copy the source below to a query window, change to the database where you want to have it and execute the SQL. Enjoy!



Here is the source:


CREATE FUNCTION [dbo].[funcFilterString_ASCII]
(
@Source VARCHAR(max), -- the string that contains characters to be replaced
@Filter VARCHAR(max), -- the regex what should be filterd
@Replacement VARCHAR(1) -- replace anything that does not match @Filter with this char (set to '' to remove non-matches)
)
/*
Example

declare @s varchar(500)
print '--Test string ---'
select @s='John Doe - 12345 New York - YYY_XXX - No 10 USD$' CHAR(13) CHAR(10) 'New Line'
print @s
print '--Results---'
print dbo.funcFilterString_ASCII(@s, '[0-9]', '') -- returns only 12345
print dbo.funcFilterString_ASCII(@s, '[0-9]', '*') -- returns *12345*
print dbo.funcFilterString_ASCII(@s, '[0-9_]', '') -- returns only numbers and "_" = 12345_10
print dbo.funcFilterString_ASCII(@s, '[0-9a-zA-z]', '') -- returns the entire string but without any special chars
print dbo.funcFilterString_ASCII(@s, '[0-9a-zA-z]', '*') -- returns the entire string but any special chars have been replaced
print dbo.funcFilterString_ASCII(@s, '[^' CHAR(13) CHAR(10) ']', '_') -- replace CR/LF with _
print dbo.funcFilterString_ASCII(@s, '[^' CHAR(13) CHAR(10) ']', '') -- remove CR/LF
print dbo.funcFilterString_ASCII(@s, '[e]', '*') -- returns only *e*e*e*e
print dbo.funcFilterString_ASCII(@s, '[^e]', '*') -- entire string, but all "e" are "*"

*/

RETURNS VARCHAR(max)
AS
BEGIN
-- Created by TeX HeX of Xteq System
-- http://www.texhex.info/

DECLARE @Index int
SET @Index = DATALENGTH(@Source)

DECLARE @dest varchar(max)
DECLARE @curr varchar(1)
DECLARE @last_add varchar(1)

SET @DEST=''
SET @last_add=''

-- Pass #1: Replacement
WHILE @Index > 0 BEGIN
SET @curr=SUBSTRING(@Source, @Index, 1)

-- collate is only needed for VARCHAR types
IF @curr COLLATE Latin1_General_BIN LIKE @Filter BEGIN
-- no replacement, copy from source
SET @DEST=@DEST @curr

SET @last_add=@curr
--SET @last_add_ignored=0
END ELSE BEGIN
-- do replacement. If @Replacement is empty, do not add anything
IF @Replacement!='' BEGIN
-- check if the last char we added was @Replacement
-- if so, do not add it again
IF @last_add!=@Replacement BEGIN
SET @DEST=@DEST @Replacement

SET @last_add=@Replacement
END
END
END

SET @Index=@Index-1
END

-- Pass #2: Reverse the string, because we build @Dest from reverse
SET @DEST=Reverse(@Dest)

-- Pass #3: Remove any left over spaces
SET @DEST=RTRIM(LTRIM(@Dest))


RETURN @DEST
END

Friday, August 15, 2008

Thanks for the eh, "detailed" description

The 100.000$ question: What does this error message mean?

A) Nothing!

B) Go figure!

C) Enterprise NX-01 hull breach!

D) Maybe something went wrong, but I won't tell you!


Thursday, August 7, 2008

Improving the SGH-i780

After nearly three years with a Palm Treo 650, I decided to buy a new mobile Email machine. Because I now have a full Exchange account, I needed to get a Windows Mobile Device this time.

Evaluating several devices (HTC S730, Motorola Q9, Palm Treo 750) I thought the phone that best suits me would be the Samsung SGH-i780. Especially because it has no D-Pad, but instead a touch pad that is easy to use and makes handling a snap.

However, as soon as you want to scroll down a longer email, or viewing a normal website there is one thing were the touch pad sucks: Scrolling. Because of the mouse-like handling you always have to move the cursor to the scrollbars and click on it. This is just plain time consuming. After a short while you will miss a simple D-Pad desperately.

Good: The SGH-i780 has a two buttons on the left side (Volume Up and Down) and another button on the right side (Short press: Mini Media Player, Long press: Camera) that could be used to simulate Up/Down and Enter. Bad: You can't reconfigure these buttons with the normal settings Samsung offers.

... until you get AE Button Plus from Alexander Eltsyn which allows you to reconfigure any (yes, really any) key your device has. You can even double and triple (!) assign any key.

With this software for just 8$ I simply reconfigured the three button to be up, down and Enter (Long press on the Camera key is still the camera) and now I can work on my emails without using the touch pad.