The miscellaneous ramblings and thoughts of Dan G. Switzer, II

Finding the path in a hierarchical tree in MSSQL 2005

I'm working on an table that uses the adjacency list model to store its hierarchical relationship. In a nutshell, it means my table has a parent-to-child relationship using the a foreign key to point to the parent primary key. While this is efficient for storage (and easy to understand,) this model was always extremely inefficient for retrieving the tree structure in MSSQL—that is until SQL Server 2005.

In SQL Server 2005, Microsoft implemented a new featured called Common Table Expressions (CTE,) which among other things allow you to transverse an adjacency list model table by using recursion.

A simple example looks like this:

with EmployeeTree as (
    -- get root of the tree
        employeeId, employee, managerId
        managerId is null
    union all
    -- do a recursive lookup
        child.employeeId, child.employee, child.managerId
        Employee as child
            inner join
        EmployeeTree.employeeId = child.managerId
-- now grab the data from the CTE table
    employeeId, employee, managerId
order by
    managerId, employeeId

Today I was trying to figure out a good way to determine the path through the tree. I started thinking that the MSSQL 2005's ROW_NUMBER() function would be a good way to do that. A quick Google search brought up an excellent article by Adam Machanic titled Swinging From Tree to Tree Using CTEs, Part 2: Adjacency to Nested Intervals.

It gives pretty thorough examples and even shows how using a CTE you can convert an adjacency list model table into a nested set model table.

Wow! 4GBs of DDR2 RAM for $92.99 (US)...

I can't believe how cheap RAM is right now. I was online shopping for a gift for my Dad and came across 4GBs of PQI DDR2 PC2 5400 RAM for $92.99. That's a pretty good way to cheaply upgrade a Development Workstation or Server.

Just 5 months ago I paid $120 for 1GB of DDR 500 (PC 4000) RAM to upgrade my Desktop box to 2GBs. I know I paid more for that RAM because I had to match the older, high performance RAM I already had in my system, but I just can't believe you can get 4GBs of RAM for under a $100.

I remember paying almost $400 dollar to get 16MBs of RAM in my old 486 PC (it was four, 4MB SIMM sticks.) That was at a time when 16MBs of RAM was a huge amount of RAM and DOS and Windows for Workgroups still ruled the PC world. I eventually jammed all that RAM into a Promise EIDE Caching Controller card as RAM got cheaper and I needed more RAM for Windows 95.

If you're looking for a cheap way to upgrade the performance on your computer, you might think about buying some RAM now. If you use VMs or have a Development Server where you have lots of services running (like SQL Server and multiple versions of ColdFusion,) adding RAM can really help your performance.

Searching for an Eclipse Color Picker...

I was trying to find an Eclipse-based color picker that worked as a view this afternoon. I can't believe nobody has created one, but from the looks of my Google searches no one has. It would really be great to have a color wheel with the features of Adobe's Kuler available right from Eclipse.

If anyone knows of a really good color swatch/picker for Eclipse, please let me know.

However, I did come across ColorCop in my search—which I'm going to try out and see how it works for me. There's no installer and it's free, with an option to donate via PayPal if you find the program useful.


Yahoo! Sirius Tuner Widget Skin Modification

Probably my favorite Yahoo! Widget is the SIRIUS Satellite Tuner by Tanner Jepsen. He does a really good job keeping it up-to-date and fixing problems that crop up when Sirius changes their online streaming code—which seems to be too often.

Using this widget, I don't need to worry about loading up Sirius' online streaming site and using their clunky interface. The widget behaves just like the actual radio units. Since it's right on my desktop, it's always just a click away. I'm able to quickly fire up Sirius radio in the morning when I start working.

One of the many features it offers is custom skinning. My favorite skin is the Sirius Black skin:

Sirius Black Skin

The problem with this skin is that the target area for each "button" was limited to just the visual blue element. This is because the skin only uses the non-transparent portion of the skin images to calculate the clickable areas. This makes clicking some of the buttons like the "1" channel or the "-" (decrease volume) button very difficult to click.

To fix the problem I modded the skin and added a colored background to each button image to increase the target click space. Feel free to download my modification of the Sirius Black skin.

My Grandma

A week ago Sunday (November 18, 2007) my Grandma (my father's mom) passed away. She had been battling dementia for the last several years and the good days were far and few between. She did quietly in her sleep around 1am EST.

Her death did not come as a surprise to us. I think we've all been preparing ourselves for the day—I know I had been. Regardless, losing a loved one is always hard.

I've been really worried about my father. For the past year (and maybe longer) he had been going to see my grandmother at the Assisted Living facility almost every day. My grandfather passed away over 17 years ago, so my grandmother's death means both of his parents are now deceased. So while I'm sure he's been preparing himself for the day when my grandmother would pass, it still must be a difficult situation to go through—and hopefully something I won't have to go through for many years. From all accounts though, my dad is doing well.


New Blog Live!

Well after several weeks of tinkering and tweaking, I finally got the new blog live. This endeavor took a lot longer than I thought it would take. I knew it would take some time to create the conversion scripts to migrate my old entries to the new blog software, but I didn't plan on the amount of work I'd end up doing on the BlogCFC code to get everything working the way I wanted.

I ended up making a lot of modifications to the source code that I plan on sending to Raymond Camden so he can do whatever he sees fit with the code. I know he's working on v6—which is a major re-write of the software.

Some of the biggest changes I made were in the XML-RPC support. One of the key reasons I wanted to migrate off the software I wrote was so that I could use a 3rd party blogging software. I'm currently writing this entry using Microsoft's Live Writer.


WinMerge is a great Open Source Diff tool

In the past I've blogged about some free Windows-based Diff tools—like ExamDiff. However I recently came across WinMerge—an "Open Source visual text file differencing and merging tool for Win32 platforms."

I've only been playing around with it for a couple of days, but I'm very impressed so far. It does a very good job visualizing the differences in files and has a lot of configuration options (such as how to interpret whitespace.) You create patch files, merge the changes together and even compare folder contents.

The feature I really like is Windows Explorer Context Menu integration. The context menu allows you to select two files in Windows Explorer and right-click and select the "Compare" option and instantly get a diff from those two files. There's also an option called "Compare To" which allows you to select a single file and then you can explore to another folder highlight a second file and select the "Compare" option to compare those two files. I really like this option. For me this is generally much more convienent than open the program and using an "Open" dialog to compare two files. I just find when I need to compare two files and I'm not already in Eclipse, then I'm probably looking at the files in Explorer—so the context menu integration is extremely convienent.


Steelers 38, Ravens 7 (Nov 5, 2007 / MNF)

In case you missed it, or just want to see it again (like I do) here are some NFL Video highlights from last nights Steelers/Ravens game.

Steelers 38, Ravens 7
Week 9: Ben Roethlisberger highlights
NFLTA: Ravens vs. Steelers analysis
Steelers postgame press conference
NFLTA: Ravens vs. Steelers highlights

This was the game the Steelers needed to put together against the Ravens. The Ravens really physically dominated the Steelers in both games last year and the Steelers returned the favor last night.


Invoking Components (CFCs) with a relative path

Back in March of 2003 I posted a UDF which allows you to initiate a CFC from a relative path. Anyway, since it's been over 4 years at 2 major releases of ColdFusion, I just wanted to let everyone know that this UDF does indeed still work in ColdFusion 8.

While it is a hack and I can't guarentee it won't cause server problems, I can say I've been using this in production code since I wrote the UDF and have never found a problem.

Amazing Flash Game - Get The Glass

My brother sent me this link just a few minutes ago. This has to be one of the most impressive pieces of Flash I've seen in a long, long time. Kudos to the firm that did this work for the California Milk Processor Board (aka Got Milk?)


Looks like the old URL is now taken over by spammers. The game can still be found here: http://www.crazygames.com/game/get-the-glass

Configuring Windows Authentication with Apache 2.2.x and Subversion

I've been working on our development server at work to try to help streamline some process. This included upgrade Subversion and Apache to the latest versions and installing Trac (so we can better track software issues.)

In order to really simplify being able to give developers and contractors access to the appropriate areas, I wanted to try and simplify the process by configuring Apache to use Windows Authentication. This allows us to control who has access to what, just by making them members of the appropriate groups.

Since I'm new to Apache, this whole process has taken longer than what I wanted—but I certainly learned a lot in the process.