Using MSSQL to define distinct cross-column relationships...
Someone asked me this morning how they could determine distinct relationships. They wanted to be able to retrieve all the distinct rows where someone had defined another user as a friend or any users who had defined them as a friend. The table schema looked like this:
| friend_user1 | friend_user2 |
| 1 | 10 |
| 2 | 6 |
| 10 | 1 |
| 6 | 1 |
| 2 | 1 |
| 10 | 2 |
As you can see friend 1 is pretty popular amongst other users. However, he only has 1 person defined as friend of his—friend 10. What we need to do is to find all distinct cross-column relationships in the table—which means only one row should be returned for the relationship between friend 1 and friend 10.
CommitMonitor notifies you of changes to your SVN repositories
Phill Nacelli blogged about a great tool for monitoring SVN repositories called CommitMonitor from Stefan's Tools. Just last week I set up a post-commit script to e-mail me when updates were commited to a few of my SVN repositories. When you do all your work via telecommuting, staying in touch with updates to your repositories can be somewhat problematic—especially when you have lots of repositories.
I like being proactive and knowing when other developers are adding new committing new code. Some of our repositories aren't frequently updated, so proactive monitoring really helps me stay up-to-date. Up to now my choices were to either configure a post-commit script to e-mail changes or to check the repository logs for changes.
That's all changed with CommitMonitor. It's basically an RSS reader for your SVN repositories. 
You can set it up to monitor any number of repositories and you can configure how frequently updates are checked for. When CommitMonitor checks the repository any changes will be shown to you via a small pop-up window in the lower right hand corner.
CommitMonitor is a single executable that you can download in a zip file. I've always favored applications that allow you to just unzip and run—it makes it very easy to remove the application if don't like it.
From my brief experience with CommitMonitor I see this tool as being extremely helpful to my workflow. Go download it now and see how it helps you!
Dynamic Template Plug-in for Windows Live Writer
I've been using Windows Live Writer for posting all my blogs. Overall it's a terrific program. It's got a very simple, easy to use interface and it drastically eases the process of blogging.
However, the one complaint I've had with the program is there's no easy way to "add" common HTML tags that you commonly use to the interface. I use the <samp> tag quite frequently when showing inline sample code and for variable names (although I suppose I should use the <var> tag instead.) To use these tags I've had to drop to the HTML Code view and manually add the tags. This is a pain to do—especially on longer posts where I need to add the tag frequently.
To solve my dilemma, I've been periodically searching for a Live Writer Plug-in that might allow me to add some custom HTML snippets. Well today I just happened to stumble on a great little plug-in called Dynamic Template Plugin for Windows Live Writer.
You can use this plug-in for doing something as inserting static text or for modifying the selected text. The real power comes from the fact that the plug-in allows you to use C# code snippets. You can even have the template prompt you for input.
To get you started, here are a couple of templates I just wrote to help me out with a few tasks:
SVN post-commit for Windows
As I mentioned early I've been working on a SVN post-commit script. We've got a SVN repository that will be modified by several remote developers and I really need to keep an eye on this repository and I need to closely monitor changes to this repository.
There are two major functions that I needed in my post-commit script:
- I needed to update the working copy on my server
- I needed to e-mail the changes to myself, so I know when developers are making changes
There are an abundant of examples showing off how to do this in various *nix flavors, but I couldn't find any good Windows-based solutions that didn't require Perl to be installed on the server. That led me to create the following post-commit.bat script.
Debugging Subversion Repository Hooks in Windows
I've been working on a post-commit hook for our Subversion install and was running in to a number of issues. The post-commit.bat file would run fine from command line, but I just could get things to work as I expected from SVN. After much debugging and scouring Google for answers, I've found a few tips that will hopefully help you to troubleshoot your own SVN repository hooks.
1) Subversion executes all hook programs with an empty environment
This was the biggest issue I was running in to, because I was expecting the my script to be able to find any programs in my %PATH% statement. That's the main reason my scripts were working fine from command line, but were breaking when executing from my SVN hook.
Converting MSSQL XML to a native ColdFusion Query
Every now and again I have the need to test some code against against some live data. The reasons vary from simply trying to recreate a bug, testing a piece of code for performance or for testing a UI widget.
In the past the quick and dirty way I've done was to move a template into production that would perform the query and then convert the query object to WDDX. This would allow me to create a "copy" of the live data that I could port to development. The problem with this method is it makes you move temporary code to a production environment that you then have to remove.
Using AntiSamy to protect your CFM pages from XSS hacks
I recently posted about a new open source Java project called AntiSamy—which allows you to protect your websites from XSS hacks. I also promised that I'd soon show you some code examples that show you how you can use AntiSamy within ColdFusion.
I've only tested this code under ColdFusion 8. It should theoretically work on any ColdFusion installation, provided you're using a JDK version that supports the compiled version of the AntiSamy code (which is compiled to Java v1.5.)
Before you can actually use AntiSamy, there are a few quick steps you need to make.
Using Eclipse to restart your Web Server
Scott Stroz (who's always up for a round of golf) today asked How Do You Set Up Your Development Environment? In his article, he has an excellent tip for restarting your local install of Apache using an Ant script. This is a great tip and something I quite frankly never thought to do.
But what if you're using Windows and want to stop a service on remote server?
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:
-- get root of the tree
select
employeeId, employee, managerId
from
Employee
where
managerId is null
union all
-- do a recursive lookup
select
child.employeeId, child.employee, child.managerId
from
Employee as child
inner join
EmployeeTree
on
EmployeeTree.employeeId = child.managerId
)
-- now grab the data from the CTE table
select
employeeId, employee, managerId
from
EmployeeTree
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.
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.
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.
jQuery: Understanding the "chain"
Yesterday I was writing some jQuery code and I thought I came across a bug—until I realized it was a bug in my way of thinking and not with jQuery.
What I was trying to do is to dynamically append to the body tag some html that looked like this:
outer
<div>
inner
</div>
</div>
Using CFMAIL to send attachments stored in memory
Awhile back I was looking for a way to send e-mail attachments in CFMX without writing data to disk. That solution uses the Javamail API to directly send a e-mail with attachments to an SMTP server. This method also completely bypasses the CF mail spool.
Charlie Arehart linked to the article from an article he wrote about storing CFDOCUMENT and CFREPORT contents in a variable. In the comments on his post, Jon Wolski posted a solution that uses the built-in ColdFusion tags CFMAIL and CFMAILPART. So, I thought I'd re-do my original example using Jon's technique in this post—just so you can see an example of both methods.
Below is some source code that will show you how to use the CFMAIL tag to send a multipart message that contains:
Google's JSON library...
As part of Google's Caja project, they have released a JSON library (for JavaScript) that looks like it's very solid. For more information on the Caja project, check out this Ajaxian article.
Expiring Session via AJAX using HTTP Response Headers
Raymond Camden recently asked on his blog How can you timeout a session in an Ajax-based application?. Most of the comments on the entry relate to doing some kind of server-ping, but my suggestion would be to just let your application tell your Ajax code when the session actually expires.
The concept is to use HTTP response headers to inform your Ajax request when the session has actually expired. This allows you to do everything in one single call, without having to worry writing other code. Besides you should be adding some failure code to your Ajax calls anyway, so this fits right in with good Ajax practices.
There are two basic approaches you can take. Using a "custom" response header or sending back HTTP status code to indicate the user is no longer authorized to view content.