SQL filtering.

When you hold a hammer you may end up seeing nails everywhere. I won’t pretend that I don’t have this problem. PowerShell is my hammer and “I see nails”. For example: when I have to work with a product that doesn’t allow me to perform some system-wide searches but allows me to export entire configuration to XML document I will improve my XPath skills and use my hammer for this nail. My XPath series is side effect of that.

I have more ‘nails’ around me though. Like ticketing system (lets keep it nameless) that has (for my needs) very poor search mechanism, but has SQL backend. I could probably use API that this product has (I think? Frankly, I didn’t even try to find it…) but writing something SQL-aware seemed more natural and reusable. After all – I may use the same ‘skeleton’ for anything else that has SQL backend.

At first I wrote something very basic, but after a while I felt that this general tool (with only one search parameter, Filter) is not good enough. I observed few issues with it:

• views I was using had columns with long names, often with spaces
• to make it more “PowerShellish” I used different names for properties (using T-SQL ‘AS’), but that complicated things when I wanted to filter using plain T-SQL syntax – I had to use column names in filter, so I had to learn by hard what properties columns mapped to
• I hate using quotes. The only thing I hate more that using quotes is going back in command to change single quotes (surrounding Filter) to double quotes, so that I can use single quotes to pass values for LIKEs
• I don’t like to write code. If I can ‘generalize’, I will

All that was a driver to re-write my function. Once I did it I thought: maybe it’s worth sharing? So it all ended up as another series of tips for PowerShell Magazine. I think it was first thing I did after I officially joined wonderful PSMag team. Series has 4 parts:

1. Kicking quotes out.
2. Using WildcardPatter.ToWql as MyCommandPattern.ToTSQL and some help improvements that can follow.
3. Smarter mapping of ugly column names to pretty property names in a way that doesn’t affect filtering.
4. Using switch to cover broader range of filter types, including date/ integer filters.

If you notice that grammar/ punctuation/ logic in this series is way better there than here – you know whom to “blame”. Yes, I had nothing to do with it. You have to blame great team of PowerShell Magazine editors. Again, my PowerShell hammer saved (my) day.

Advertisement