Archives
-
[SQL] Finding the Boss with Hierarchical Queries
Never thought I was going to blog about SQL... but I'm doing this mostly for myself because I need to use this at work on monday. I know enough to do database design and whip up a few necessary stored procs, but when it comes to functions and the new Common Table Expression (CTE) in SQL 2005 I'm quite lost. I got a tip about CTE from my buddy Jan-Erik so I sat down with Google and SQL Books online to see if it could help me out.
Most examples out there lists the employees below a manager, and their employess etc. What I needed to do was go the other way - to create a view or something which showed all the employees in a table and who their manager on a certain level is. Not just their immediate boss, but the boss on some upper level.
To test this I started out with a small, simple table called Employee with 4 columns in it (EmpId, Name, BossId, Type), where BossID is the immediate boss, or the "parent":
CREATE TABLE [dbo].[Employee](
TABLE [dbo].[Employee](
[EmpId] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[BossId] [int] NULL,
[Type] [varchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
Or something like that J I'm also setting a FK between BossId and EmpId:
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([BossId])
REFERENCES [dbo].[Employee] ([EmpId])
Next I filled it with some data:
1 Helen NULL Mega boss
2 Lisa 1 Boss
3 Otto 1 Boss
4 Carin 2 Team lead
5 Carl 2 Team lead
6 Niel 4 Project manager
7 Thomas 3 Team lead
8 Johan 4 Code monkey
Pretty simple, but enough to test with. This organization has 4 levels, from 'Mega boss' to 'Code monkey'. To iterate "up" in the hierarchy I'm using CTE and the WITH <table(columns)> AS syntax, and I'm putting it into a scalar function which takes the EmpID and the manager Type as parameters:CREATE FUNCTION [dbo].[GetBossByType]
(
@empid int,
@type varchar(20)
)
RETURNS int
AS
BEGIN
DECLARE @bossid int;
WITH reports(EmpId,name,bossid,type) AS
(
-- set the starting point, a specific employee (from in-parameter)
SELECT EmpiD,name,bossid,type FROM Employee WHERE empid = @empid
UNION ALL
-- get the empid from the person (e) who has empid which corresponds the the previous person's (r) bossid
SELECT e.empid,e.name,e.bossid,e.type FROM employee e JOIN reports r
ON r.bossid = e.empid WHERE r.bossid IS NOT NULL
)
-- from the above we have a hierarchical list of managers in a table called "reports",
-- get the id from the boss with the type I'm looking for
SELECT @bossid = empid FROM reports WHERE TYPE = @type;
RETURN @bossid
END
Now, to create a view that has empid, name and boss id for a specific "level" I used a SELECT statement like this:
SELECT EmpId, Name, dbo.GetBossByType(EmpId, 'boss') AS BossId
FROM dbo.Employee
WHERE (BossId IS NOT NULL)
As you can see, I'm calling my function (GetBossByType) as part of the SELECT, passing in the EmpID and the type of manager I want to list. The result of the query/view is this (EmpId, Name, BossId):
2 Lisa 2
3 Otto 3
4 Carin 2
5 Carl 2
6 Niel 2
7 Thomas 3
8 Johan 2
Works for me. Lisa and Otto are of the manager type 'Boss' already, that's why they have their own EmpId as BossId. The hierarchical query can be a bit difficult to grasp first, but it's powerful. Sorry about the bad syntax and not sticking to upper/lower cases everywhere :)
-
[MsBuild] Writing and Debugging Custom Tasks
Writing a custom task for MSBuild is very simple, here's the bare minimum code you would need to get you going:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
namespace IRM.Tasks
{
public class HelloTask : Task
{
private string name;
[Required]
public string Name
{
get { return name; }
set { name = value; }
}
public override bool Execute()
{
Log.LogMessage("Hello " + name);
return true;
}
}
}
The task will obviously print out the value of the "Name" property to the log.
To test it, create a test-project like a dummy console app and add this to the end of the .proj file:
<UsingTask TaskName="IRM.Tasks.HelloTask" AssemblyFile="C:\code\HelloTask\bin\Debug\IRM.Tasks.dll" />
<Target Name="AfterBuild">
<HelloTask Name="Johan" />
</Target>
If you unload the project from the Solution Explorer window you can Edit it directly in VS.
But if you want to debug the task and set breakpoints in it? It was described by Neil Enns of the MSBuild team quite some time ago. After you have created a test-project and added the custom task to the .proj file as described above, do this:
- Open your custom task project
- Go to Project > projectname Properties…
- Go to the Debug tab
- Change the start action to “Start external program” and put in the full path to MSBuild as the program. In my case the path is “C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\MSBuild.exe”
- Change the “Command line arguments” to the full path to your test project
- Close the property window
Now just start debugging your custom task, MSBuild should start up and start to build your test-project and will stop at any breakpoints you set in your custom task code. Neat!
-
[Team System] Team Foundation Server Power Tools February Release
Rob Caron just wrote:
The February release of the Team Foundation Server Power Tool is now available for download. Formerly known as the Team Foundation Power Toy, these utilities pack too much punch to be considered toys.
I'm very interested in the "Test Tools Build Task". Seems to be what I'm looking for.
-
[Team System] Team Building Web Application Projects and Deploying
I've been battling with Team Build and Web Application Project (WAP) solutions. I first thought that Web Deployment Project (WDP) was the way to go, but I found out the hard way that WDP + WAP + Team Build isn't best mates.
What I want to do is to build a number of solutions (which includes a bunch of projects) and after the build has completed, xcopy the web applications to a specific place, like under IIS webroot.
There are many, many different solutions to this scenario, but I think it's a very common one. Here's what I'm thinking of doing now. Step by step dummy:
1. I create a TFS project "WebApplication1"
2. I create a solution called "WebApplication1" with 2 projects in it; "WebApplication1" (WAP) and "LibraryX" (Class library), I create some dummy code where a web page calls some code in the class library (mostly for fun).
3. Compile and test.
4. I add the solution to SourceControl.
5. I then create a new Team Build called "WebApp1".
6. Go to Source Control Explorer and check out the TFSBuild.proj file for edit, then double click it to edit.
7. Page down to the end of the build file and add (before the </Project> tag):
<ItemGroup>
<WebFiles Include="$(BinariesRoot)\Release\_PublishedWebsites\**\*.*"></WebFiles>
</ItemGroup>
<PropertyGroup>
<DeployWebDir>c:\websites</DeployWebDir>
</PropertyGroup>
<Target Name = "AfterDropBuild" >
<RemoveDir Directories="$(DeployWebDir)" Condition="Exists('$(DeployWebDir)')" />
<MakeDir Directories="$(DeployWebDir)" Condition="!Exists('$(DeployWebDir)')" />
<Copy
SourceFiles="@(WebFiles)"
DestinationFiles="@(WebFiles->'$(DeployWebDir)\%(RecursiveDir)%(Filename)%(Extension)')"
/>
</Target>What the stuff above does is that it declares what files to copy (WebFiles) and where these files should go (DeployDir). Then there's a Target section which is called by Team Build after the drop has been made (AfterDropBuild) that removes the old files and copies the files from WebFiles to DeployDir. The $(BinariesRoot) property points to where the files have been built. WAP files are always built to the _PublishedWebsites\<project> directory.
My web application is now deployed to c:\websites\WebApplication1\ and I'm manually creating an IIS virtual directory which will always be pointing at this directory.
There are a number of additional things you might want to do as well, but I just wanted to show you a very simple way of always copying WAP files to the same place after a build.
If you don't want to use the Tean Build drop directory at all, you should be able to skip that step by setting the SkipDropBuild property to true in the TFSBuild.proj file.
MSBuild / Team Build Resources:
List of targets that can be customized in Team Build (like AfterDropBuild in my sample):
http://msdn.microsoft.com/en-us/library/aa337604(VS.80).aspx
MSBuild Task reference:
http://msdn.microsoft.com/en-us/library/7z253716.aspx
Brennan's blog with a 7 step tutorial on MSBuild:
http://brennan.offwhite.net/blog/2006/11/29/msbuild-basics-1of7/
How to create your own MSBuild task:
http://msdn.microsoft.com/en-us/library/ms400767(VS.80).aspx
-
[Service Factory] How the Service Factory Guys Think
Don Smith put a video up on the Service Factory Codeplex site about how they think about services, where they believe Service Factory is today and where they are heading. I thought the message about the different types of services was quite interesting because it is close to the way we're thinking when we're identifying service components for an enterprise. Note that the type of (web) services you may create as part of a specific solution because it may help you scale out, tier and get better availability and so on is something different to me. I'm more thinking of enterprise class services here.
Don mentions 3 types of services; Entity/Capability, Utility/Messaging and Process/Activity.
The first one is easy to understand and I think that's where most organisations start on their SOA roadmap. An Entity Service is usually very simple and may represent one or more business entities with data stored in a database or perhaps wrapped within a (legacy) system. You often se CRUD-like interfaces on these, but I recommend you to try and stay away from too granular methods. If you have an entity service for say a Person or a Customer, consider to not expose methods to get just the phone number or the email address, but rather get a complete Person or Customer object. These objects could be based on well known XML Schemas owned by perhaps information architecture group or something.
The second type of services I usually call "Infrastructure Services" and are typically Security Token Services (STS), message routers and such. Could also be metadata/taxonomy/ontology services to help out with information integration cross services and systems. If anyone was using UDDI, I think it could also be placed in this category ;) Having said that, I think this category of services are very, important. It's hard to maintain and govern enterprise class services without an STS and a proper service catalogue. Not sure I understand their view on "Message" in this type of services. "Simple" Message routing services based perhaps on ws-addressing and such could be placed here, but more complex message handling, perhaps handled by a workflow engine, I think would go into the third and final category of services...
...called Activity and/or Process Services, which usually (for us anyway) contains methods and interfaces that map to scenarios, use cases, routines/functions (depending on which methodology and terms you're using). Also good for long running processes which may involve message queues, perhaps BizTalk and Windows Workflow Foundation. These services most often use other services, like Entity Services, to orchestrate and aggregate data needed by service consumers. They may not have their own database like Entity Services, unless you need to save state for a workflow or to keep replicated and denormalized data for aggregated search or perhaps masterdata for partitioned entities. See the Entity Aggregation pattern for a detailed description. One could argue that services that implement the this pattern should go into the first category of services, but I usually put them into the Activity Service category anyway, for reasons I'll probably blog about some other time (it's getting late).
I'm happy to hear about where the Service Factory team is heading, to have better support for Utility/Messaging (Infrastructure) Services and move into support for Activity/Process Services. I wish there were some good guidance for activity and process services already. I'm quite interested in seeing what this team will do with LINQ in the near future. I'm not too fond of the data access code in current Data Access Guidance Package.
Anyway, go check out the video, it's short and the sound is a bit so, so, but it's a good message.
-
Modify the Paperclip Theme on weblogs.asp.net
The CS themes available on weblogs.asp.net are quite good, but maybe you want to add that extra personal touch to it. Say you want to change the big "masthead" picture on top of the screen of the paperclip themes. If you just have somewhere to host your own picture, it's not that difficult to override the theme settings.
To change the masthead picture of the paperclip theme, first have a look at the css for the theme and see how the picture is referenced. View source and find the stylesheet link:
<link rel="stylesheet" href="/Themes/Blogs/paperclip/style/cactus.css" type="text/css" media="screen" />
Then download that css file and have a look at it. Search for the name of the picture you want to override, like "winter-title.jpg":
/* The masthead sits at the top of the page.*/
#masthead
{
background: #ffffff;
background-image: url(../images/cactus-title.jpg);
}This tells me I need to override the css declaration called #masthead. Next, you create your own background-image for this section, and it's best if you use the old image as a template, or else you need to override other css settings, like where the paperclip text is written, width, height and so on. Personally I just saved cactus-title.jpg file and used Photoshop to create something new on top of it. When you're happy with the picture, upload it to wherever you want to host that file. Last thing is to go into the blog dashboard of weblogs.asp.net, click to "Global Settings" and "Change How My Blog Looks". Now go to the second tab of that page named "CSS Overrides" and past in something like this:
#masthead
{
background-image: url(http://www.somehost.com/pictures/my-title.jpg);
}
That's it. I'm about to create a new picture now :) -
Note to Self
I've not blogged much lately, and it's not because I don't have anything to blog about - quite the contrary. Too much to do right now. I got a few things I would like to blog about, just as a note to myself:
- The use of Software Factory to build WCF web services in a structured way (it's helping us out in a large SOA project now), and why you must be careful of using too long project names :(
- How you could modify Software Factory to fit your needs, add new recipies and tweak the code that gets generated (my colleague Eric has done some cool work with this, and I hope he will blog about how to enable DataContract generation from an XSD-schema)
- A few things to think about when writing XSD-schemas to be used for generating WCF DataContracts.
- The (what seems to be) lack of support for calling Workflows (WF) från WCF and vice versa. It wasn't as easy as dragging a WCF Activity från the toolbox... I'm still struggling with this one.
- Some experience from using DataDude with Software Factory and Team Foundation Server
- And some CI with TFS notes that may help other people out.
Sorry for not posting anything useful about the topics above right now, but I'll get to it, I promise. Stay tuned :)
