Thursday, May 29, 2008

What gives MediaDefender the right to launch a DoS attack against Revision3

I just started reading Molly Wood's blog and that's where I first heard about the Denial of Service (DoS) attack that Revision3 suffered from over the Memorial Day weekend.  Jim Louderback, the CEO of Revision3, wrote a detailed explanation of what happened and who was responsible for the attack on a Revision3 blog post.

Before I go any further, Revision3 uses Bittorrent (BT) to distribute their own content to their viewers so that bandwidth demands on Revision3's site are greatly reduced.  What Revison3 is doing is legal and is what Bittorent was designed for.

In a nutshell, an anti-piracy organization named MediaDefender had been illegally storing Bittorrent (BT) tracker files on Revision3's BT server for months.  They used a back door into the Revision3 server.  At some point, the illegally placed files were detected by Revision3 and they closed the security hole that allowed MediaDefender to gain unauthorized access to their servers. 

That's when the bovine excrement hit the rotating air circulation device.  The MediaDefender server lost contact with their BT Tracker files and launched a DoS attack.  What they did was flood the Revision3 servers with SYN requests, causing a "SYN Flood" type of attack.  In layman's terms, it's the equivalent of kids ringing your door bell and running away before you open it.  Except here it was happening to Revision3 8000 times a second.

This attack basically stopped anything Internet related at Revision3.  Their web servers, RSS feeds, BT feeds, and everything else outwardly facing was just dead in the water.  It also took out their email.  This caused real and measurable costs to Revision3.

I'm not a lawyer, but I'm pretty sure that it's not legal to gain unauthorized access to another company's servers.  The DoS attack was definitely illegal and the FBI has already been brought in.  They suffered measurable losses and MediaDefender should be held legally responsible for the damage they caused.  They should pay compensatory damages for the employee time wasted, for the extra bandwidth costs, and for the lost revenue while the Revision3 sites were down.  In addition, they should pay punitive damages to give them a strong financial incentive not to launch any more DoS attacks.

Jim's description of the events was well written and very even handed and should be read by everyone.  Any company using BT or other forms of P2P technology to distribute their own content is at risk being attacked by MediaDefender.  This isn't the first time that MediaDefender has been accused of launching DoS attacks.  What MediaDefender did was wrong, both morally and legally.  How can this type of attack be prevented in the future?  To quote from an old episode of Star Trek:

"...I've found that evil usually triumphs...unless good is very, very careful." - Dr McCoy.

You can't beat an outfit like MediaDefender by resorting to their tactics with DoS attacks against their servers.  First of all it wouldn't work, and more importantly it's just as wrong as their attack.  I think Revision3 should sue for damages and we should all contact the companies that use MediaDefender.  We should tell them that we don't want to buy products from companies that are associated with MediaDefender.

Getting back to Molly, I first heard about her blog when she mentioned it on an episode of Cranky Geeks.  I get Cranky Geeks as a TiVoCast download to my my TiVo and I just started watching it.  After two episodes, I've had enough of Dvorak but it was worth listening to the viewpoints of the other debaters on the show.  Molly's blog is well written and is focused on digital rights and copyright issues (and abuses).

postscript:
The inspiration for the Star Trek quote came from the comments for a post on The Coding Horror blog.  That post discussed the issues of how to block comment spamming on blogs and forum sites.  It's a great quote and I hope the guy who first used it in the Coding Horror comments doesn't mind me using it here.

Brian Peeks new book is already listed on Amazon

Brian Peek (probably the most well known member of our TVUG user group) has a book coming out, "10 Coding4Fun Projects with .NET for Programmers, Hobbyists, and Game Developers", and it's listed on Amazon for pre-orders.  Brian is probably best known for his managed code API for working with the Nintendo Wiimote.  You can get the current version of the code from it's CodePlex page.  His blog is worth reading if you want to control hardware devices with .NET.

Not too long ago Brian demonstrated his code at a TVUG meeting.  He used the Wiimote to control a radio controlled car.  It was pretty cool watching the car turn when he rotated the Wiimote.  You can download the code from his presentation from his blog at this page.  Other people have used his library to do some interesting things.  It sounds like this book would be a cool Christmas gift.

Tuesday, May 27, 2008

Does knowing C make you a better programmer?

Joel and Jeff had a conversation about the merits of knowing how to program in C.  Joel's take was knowing how to program in C makes you a better program.  I'm in that camp, with some other people (Darren and Eric).

After college, I did mostly C programming.  That was back in the days when 640K wasn't the punch-line to a joke, but a real limit that drove you nuts.  Having a good working knowledge of C gave you a better understanding how memory was allocated and how it was used.  Pointer arithmetic was part of the natural order of programming.  It made you a careful with what you allocated and how you managed it.

There's a still a place for unmanaged code, whether it's C, C++, Delphi, or some other language.  You get a lot functionality with managed code (more ways of accessing data, garbage collection, etc), but there's no free lunch and there's a substantial footprint for each process that uses the .NET Framework.  I'm typing this with Windows Live Writer, an app that's written for the .NET Framework.  It's using 54MB right now as I type.  That's a lot of memory for a WYSIWG blog tool.  Just because you have a garbage collector doesn't mean that it's being used effectively.  It's still good to clean up after yourself and a good C/C++ programmer does that.

Eric Sink compared knowing C with knowing Morse code.  It's a flawed argument in that Morse code is not the underpinnings of the technology that came after it, but it's close enough to the spirit of the argument to have some merit.  My dad learned Morse code 30+ years ago when he got his ham license.  It was a big deal back then and you couldn't get your ham license with being able to demonstrate your proficiency with Morse.

Once he got his license, he rarely if ever used Morse to send out a message.  But every now and then, he would pick up a faint signal that had bounced around the ionosphere and it would be in Morse.  He would sit at his radio and pick out the words that of the dashes and dots.  

But how can you use that analogy to demonstrate the value of C to a managed code programmer?  For many programmers, I'm sure it doesn't mean anything to them.  If you are just doing web programming for some giant LOB app, only knowing VB.NET or C# probably isn't much a of a liability.

On the other hand, If you do any P/Invoke work, you'll calling DLLs that were mostly written in C or C++.  Having a knowledge of those languages makes it a little easier to understand the data structures and calling conventions used by the DLL.  If you working with binary data that came from unmanaged code, knowing how that data is represented in it's native environment will make it easier to understand what it is and how to use it.

Darren Stokes made the assertion that programmers who know C seem to solve complicated problems faster than those who don't know C.  I don't know of you can make that case.  With the push to teach only managed code at school, the programmers that came up knowing C are a bit older than the programmers who don't know C.  That programmer who has the C experience is probably someone who has been around longer and has that much more experience to draw from.  I wonder if anyone has down any actual studies that compare the level of productivity from programmers with C experience compared with programmers that only know only managed code.

Ack! The Windows Installer does not permit installation from a Remote Desktop Connection

I'm testing a installer that I had migrated from Wise For Windows to InstallAware. I testing the installer from inside a virtual machine. I used do it with VMware Workstation, but now I'm using virtual machines hosted on our ESX server. That's usually much faster and doesn't my bog down my development machine.

I connected to a virtual Server 2003 session using Terminals. If you spend anymore of time using Remote Desktop, then you'll want Terminals. It lets you have multiple sessions open in a single instance of the client by displaying each session in a tab. In addition to RDP, it supports VNC, Telnet/SSH, and a few other protocols. And it's an open source project, you can't beat the price tag.

With a RDP connection, you have the ability to automagically have the remote connection access your local resources (drives, printers, serial ports, etc). I was using the local access to disks to allow the remote connection to access my hard drive. I figured that would be a quick way to compile the installer on my local machine and run it remotely through the RDP connection.

Or so I thought. As a prerequisite for this app, I need to install the ASP.NET 2.0 AJAX Extensions. That comes as a Windows Installer .msi files. When I ran that .msi, I got the following error message:


In other words: FAIL. To add insult to injury, that's not even a standard Windows error dialog. With most Windows error dialog boxes, you can press Ctrl-C and the contents of the dialog box will be copied to the clipboard. You'll get all of the information from the dialog, something like this (from a different installer):

---------------------------
Microsoft .NET Framework 2.0 SP1 Setup
---------------------------
The Windows Installer package:


c:\c6b28b8b6e56383fbc455e9977dd00\vs_setup.msi


could not be opened.



Choose Retry to try again. Choose Cancel for exit setup.
---------------------------
Retry Cancel
---------------------------



That's very handy for reporting errors or for researching with the text from the dialog. I hate it when I see a bug logged in our internal bug tracking system that has embedded a screen shot of an error message. They screen shot the whole page (hello? Alt-Prtscn anyone), save the image, then attach it as a file attachment. Instead, they have press CTRL-C and then CTRL-V and be done with it. Rant over, back to original rant.


For some reason, the AJAX installer team chose not to follow that convention. So I went to Google and manually typed in the text of the error message (Oh, the huge manatee!) and start viewing the results. Sure enough, it's in the MS Knowledge base as 927063. Windows Installer does not allow installs in Server 2003 from a RDP connection shared drive. When you allow access to your local C: drive to the remote session, it sees it as \\TSClient\c and flat out disallows installs from anything in the \\TSClient namespace. This was determined to be a security risk and the tssclient blocking was added Windows Installer 2.0 at the time of the Windows Server 2003 release.


This KB article lists two resolutions, use the standard UNC notation to reference that drive or map a drive letter to that location. To that list, I'll a third option. Copy the file from the \\tsclient share to the remote desktop and run it from there. For one off tasks, that's usually faster than accessing a UNC named object.


Side note: While writing this post, I read on the Terminals home page on CodePlex that Microsoft will be removing the ability to connect to the console session through the RDP protocol in version 6.1. Being able to connect to the console is a little known feature of the RDP client, mstsc.exe. I use it when people leave their RDP sessions hanging on a 2003 Server box that only has the admin remote connections enabled. I can connect over the console session and blow away the left over sessions. I can query and kill the remote sessions via the command line, but sometimes you just need one session open. If you want to see this feature left in the RDP stack, place your vote here.

Saturday, May 24, 2008

IPv6 and Vista

The other day, I read a post that Carl Franklin had made on his blog about a new Vista laptop that he had to buy on short notice.  The gist of his post that Vista ran pretty well, but he did tweak some of the settings.

One of the settings that Carl had tweaked was to disable IPv6 on all of the network adapters.  For some reason that jumped out at me.  I posted a comment on his blog asking why he did it.  I'm all for disabling services that are not being used, but I like to know what the reason is behind it. 

I did a quick scan through Google, and initially found many hits for how to disable IPv6 with very little explanation on why you should do it.  Carl responded to my comment that he had heard anecdotal evidence that IPv6 can cause conflicts under Vista.

For me, the decision to disable IPv6 on my Vista boxes boils down to two questions:  Do I need IPv6 functionality?  If I don't need IPv6, does having IPv6 enabled cause any problems under Vista?  Time to go back to Google and due some more searching.

One bit of trivia:  While I was searching for why you would want to disable IPv6 on Vista, I came across a few articles about how Google owns a large block of IPv6 space that works out to be 7.9 x 1028 IP addresses. That's a large number, no matter how you slice it.   Why does our Google Overlord need that many addresses?

I've seen the badge below displayed on a few sites. 

Pretty scary looking, huh?  There is a real concern with the number of IPv4 addresses being used up.  At the current rate of assignment, we just have a few years left before the all of the addresses have been assigned.  That's why they came up with IPv6, to provide for a virtually unlimited number of IP addresses.  Even with Google hogging billion and billions of IPv6 addresses, we'll still more than enough to serve this planets needs for the for seeable future.  Right now, the continued use of NAT allows more users to share the same IP address. 

We are seeing all of the IPv4 addresses being assigned, but that doesn't mean that they are all being used.  If you look at the global IPv4 allocation list, you'll see that large chunks of the IPv4 space is in the hands of a small number of companies.  For example, Apple owns all the 017.xxx.xxx.xxx addresses.  That's over 16 million distinct, global IP addresses.  What are they doing with all those addresses?  And some people still question the need for IPv6 in the first place. 

But I digress, I think we'll be safe with just IPv4 addresses for the next few years.  At this point, there are no IPv6 only addresses that I want to visit.  They exist, I just don't need to visit them.  So the answer to my first question is: No, I don't need IPv6.

So what about leaving IPv6 enabled in Vista?  What does that cost me?  I read about some reports early in the Vista release with IPv6 issues.  Some of that could be attributed to "version 1.0" network drivers.  I did read some reports of http://localhost not working with IPv6 enabled. 

On an IPv6 enabled box, localhost will evaluate to ::1, not the 127.0.0.1 that we call home.  That is what defined for localhost in %SystemRoot%\system32\drivers\etc\hosts as the IPv6 adddress for localhost.  This apparently causes a problem when testing locally hosted websites.  If you remove (or better yet, just comment out the ::1 entry from the hosts file, that was reported to allow web development to continue using localhost and IPv6 installed.

Klaus Graefensteiner has a pretty good explanation of why this is happening.  As noted elsewhere, he recommends editing the hosts file as the work around. I haven't done any web development on my Vista box, I can't verify that this is still an issue.  I'm actually planning on doing some web development work from my home machine in the near future, issues with localhost will pop up pretty much immediately. 

As far as I can tell, the answer to my second question is "maybe".  If the only issue is the localhost issue, then I'll address it through editing the hosts file as opposed to disabling IPv6 altogether.

I did see the value of one point that Carl made.  If the service is not being used, then disable it.  It's not enough of an issue for me, but resources are limited, then you want to pick off the low hanging fruits from the list of running services.  If you do actually want to disable IPv6 in Vista, it's pretty easy to disable or enable IPv6 support (from numerous sources, this one from TechSupportForum.Com)

  1. Go to Start and type in "ncpa.cpl" (without the quotes) and press Enter
  2. Right click on each network connection and select "Properties"
  3. Remove the checkmark from the box next to "Internet Protocol Version 6 (TCP/IPv6)
  4. Click OK to exit the dialog

Lather, rinse, and repeat for each network connection. IPv6 will still remains active for routing and tunneling.

To complete disable IPv^ in the system, you'll need to add a registry.  Go to registry and create DWORD parameter "DisabledComponents" in HKLM\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters and set it to a value of 0xff and restart the system.

Just remember one thing, if you disable IPv6, you will not be able to use Windows Meeting Space or any application that relies on the Windows Peer-to-Peer Networking platform or the Teredo transition technology.

Bonus Firefox tip:  You can disable Firefox's usage of IPv6 with out disabling IPv6 for the rest of the system.

In Firefox, type about:config and then in the filter box type dns. You'll see an entry named "network.dns.disableIPv6".  Double-click that line so that it reads True, which will effectively disabled IPv6 for Firefox.  You'll need to restart Firefox for this change to take effect.

Thursday, May 22, 2008

Checking for the presence of .Net Framework 2.0 SP1

Installware has a lot of built in support for checking for the presence of the various flavors of the .NET Framework.  In it's current incarnation, 7.5, it doesn't have anything for checking for the presence of Service Pack 1 of the .NET Framework 1.0.  It turned out to be pretty easy to add that check to my installer script.

A couple of years back Heath Stewart did a blog post about how to check for the presence of a .NET Service Pack.  You just look for the the value of "SP" in the registry key for the version of the .NET runtime that you want to check.  For .NET 2.0, that key would be labeled:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v2.0.50727

If you want to check against a language, you just check for the language id.  For example, English is ID 1033.  The key to check for the English version of the .NET 2.0 runtime would be.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v2.0.50727\1033

The SP is DWORD, so you will get back an integer result.  If the .NET runtime has been installed, it will have the SP value.   For the RTM release, it will be 0.

With InstallAware, I use the following syntax to check for .NET 2.0 SP1 and terminate the install if SP1 (or later) has not been installed.

Read Registry Key HKLM\SOFTWARE\Microsoft\NET Framework Setup\NDP\v2.0.50727\SP into CHECKSYSTEM
if Variable CHECKSYSTEM not Greater Than 0
MessageBox: $TITLE$ Setup Error, This product requires that the Microsoft .Net Framework 2.0 Service Pack 1 has been installed.$NEWLINE$$NEWLINE$Setup cannot continue.
Terminate Installation
end




For some reason, InstallAware does not have an "Less Than" operator, you have to do "not Greater Than".  InstallAware is a leaky abstraction of the Windows Installer experience, I just shrug at those little oddities.


[Updated on 5/27/08]
I had a typo in the line that starts with "if Variable CHECKSYSTEM" where it should have been comparing against the value of 0, not 1.  My bad.

InstallAware doesn't automatically remove assemblies from the GAC at uninstall time

I am updating the installer for one of our products to check for the presence of the SP1 version of the .NET 2.0 Framework, when I noticed something odd.  The assemblies that I was putting into the GAC were still there after an uninstall.  The guys at InstallAware seem to think that this is not a problem.  They refer to a topic in the MSDN, Removal of Assemblies from the Global Assembly Cache, which describes how the Windows Installer is not responsible for the removal of entries in the GAC:

The Windows Installer determines whether to allow the removal of a common language runtime assembly based upon a client list it keeps independently of the assembly. The Windows Installer keeps one pin bit to represent Windows Installer clients of the assembly. The installer pins the assembly for the first Windows Installer client and unpins it when the last Windows Installer client is removed. The assembly maintains a pin bit for every client of an assembly.

The Windows Installer is not directly responsible for the physical removal of common language runtime assemblies from the computer. The installer unpins the assembly when the last Windows Installer client is removed. If the Windows Installer is the last client of the assembly, the common language runtime provides the option to force a synchronous cleanup of the assembly. The cleanup process is atomic and there is no provision for a "rollback" at this point. All unpinning of common language runtime assemblies must occur after the user has had a chance to cancel the installation or removal.

That being said, the users want to uninstall everything.  Wise and InstallShield apparently do this.  With InstallShield, there is a property for the installed file called "Permanent".  If it's not set, it will be removed from the GAC if there are no other references to the assembly.

What InstallAware wants you to do is to explicitly remove the assembly during the uninstall portion of the install script.  They provide a command named "Remove Unpinned Assemblies" (it's listed as "Remove Assemblies").  This command will remove all unpinned assemblies that your installer had places into the GAC.  The code should look like this: 

  Apply Uninstall (get result into variable SUCCESS)
Remove Unpinned Assemblies


I'm still not sure if that will always work.  According to InstallAware, there is a bug in Windows Installer where sometimes the installer corrupts the Global Registry cache on .NET 2.0 assembles and there will always be a reference to unreferenced assembly.  The only work around is to delete the (Default) registry key for the assembly in the user and local machine hives.  This mess is documented here in the InstallAware support forum.


The work around sounds worse than the problem.  I'm not going to do a strafing run on the registry to deal with a Windows Installer bug.  Not when the only consequence is that a few assemblies (in my case, it's only a few) get left in the GAC.  There are other ways of removing assemblies from the GAC.  The gacutil.exe utility can be used to check the reference count and to remove the assembly,  The "/lr" command line parameter will list the reference counts for each assembly.  The "/u" parameter will remove an assenbly from the GAC.  For the command-line shy, the free tool GacView provides an Explorer like view of the GAC and it's easy to use to remove assemblies from the GAC.

Wednesday, May 14, 2008

Showing installed app versions with BgInfo

I love it when you can use one cool tool to enhance another cool tool.  Duncan Epping posted an English translation of a Arne Fokkema post about how to use BgInfo to display the version number of VMware Tools that has been installed inside a VMWare virtual machine.

If you have never used BgInfo, it's a great little utility written by Bryce Cogswell of Sysinternals fame.  BgInfo will write a desktop bitmap that will contain useful information about the machine: machine name, ip address, version stuff, free space, etc.  You can set BgInfo to run when you login by placing a shortcut to it in the user's or shared startup folder.

When you work with multiple servers, real or virtual, their desktops all tend to look alike.  BgInfo makes it easy to see at glance which machine you are currently connected to.

What Arne wrote about was that in addition to displaying predefined variable, BgInfo can display version information from any file that has version information.  You point BgInfo to the location of the VMWareServer executable and select version formation and you are done.  The screen shot from Arne's blog shows how simple it is:

BgInfo is very handy for displaying static information.  Since the desktop background is only being rendered when you login in, you wouldn't want to use it to display a dynamic variable, like CPU load.  I can see where you would use this to display other version type of information.  If you are testing against multiple service pack versions of SQL Server, you could display that information.   While I've been talking about using this for a VMware virtual image, this is handy for Virtual PC images or any server that you would remote in to.

Thursday, May 08, 2008

Uninstall IE8 Beta before installing XP SP3

If you running the beta version of Internet Explorer 8 (IE8) under Windows XP, the word is to uninstall IE8 before installing Windows XP Service Pack 3 (SP3).  Once SP3 has been installed, you will be unable to remove IE8 (and IE7).  The reason for this is documented on the IEBlog with this post.  Basically, when you install IE7 or IE8, the existing IE6 (version of IE that comes with XP) is backed up to an uninstall folder.  SP3 includes an updated set pf IE6 files.  If you were to uninstall IE7 or IE8 after installing SP3, you would have a mixture of IE6 files from pre-SP3 and the bits installed as part of SP3.  That would cause all sorts of problems and Microsoft disables the uninstall option for IE7 and IE8 after SP3 is installed.

Not being able to remove IE7 isn't a big issue.  If you have IE7 in place, the odds are that you are used to it and will not be removing it.  The beta version of IE8 is another situation altogether.  When the next beta or release candidate or even the actual release comes out, you'll want to uninstall the beta version first.  It's just the prudent thing to do.  Well not exactly, it's much safer to install anything labeled beta in a virtual machine.  But if you have the IE8 beta installed on a XP box and you are planning on installing SP3, you'll want to remove the beta before installing SP3.  In fact, Microsoft will not offer SP3 through Windows Update if it detects the IE8 beta on your machine.  Once SP3 has been installed, you can install the IE8 beta again.

Tuesday, May 06, 2008

Dealing with the Duplicate Resource: Type 24 when enabling theme support in Delphi 2007

I was writing a little utility in Delphi 2007 to make it easy to work with data stored as the XML data type in SQL Server. While I have been using Delphi 2007 since it was released last fall, this was the first desktop app that I had written from scratch. I threw some components up on the form and I did a quick compile to see how the visual elements looked at runtime. I got the following error message:

[DCC Error] E2161 Warning: Duplicate resource: Type 24 (user-defined), ID 1; File C:\dev\RawDataQuery\RawDataQuery.res resource kept; file c:\program files\codegear\rad studio\5.0\lib\WindowsXP.res resource discarded.

I was dragging and dropping components from an app that I had originally written in Delphi 7 and one of the components was a TXPManifest component. The TXPManifest is component that only appears at designtime. When your code is compiled, it includes the WindowsXP.res resource file, which contains the manifest resource needed to enable XP style themes. In Delphi 2007, you can enable theme support in the project options. It's a checkbox labeled "Enable runtime themes" on the Application page of the Project Options dialog.

You can't have both "Enable runtime themes" checked and an instance of TXPManifest in your application. They both try to pull in WindowsXP.res and the second attempt will FAIL. The fix for this is easy. You can disable the "Enable runtime themes" setting or remove the TXPManifest component from your form and remove the reference to xpman in that form's uses clause.

My personal preference is to remove the component and unit references and just use the project setting. When there are two methods of doing the same thing, I tend to pick the simpler solution. The fewer the number of moving parts, the less likely it is for something to break.

Monday, May 05, 2008

Identity hijacking on Twitter.

The other day, I got an email from Twitter announcing that someone new is following me.

SIOOMA! (Siooma) is now following your updates on Twitter.

Check out SIOOMA!'s profile here:

 http://twitter.com/Siooma

You may follow SIOOMA! as well by clicking on the "follow" button.

Best,
Twitter

If you are on Twitter, then you have seen that type of message before.  It means that a user with the handle "Siooma" is now following my site.  I took a peek at Siooma's Twitter page and sure enough it lists a Fake Steve Jobs blog post as the home page.  Siooma is one of the FSJ's favorite sayings, read that blog post for the full definition - I don't want to spoil the fun by taking it out of context.  I read some of Siooma's previous "tweet" and I don't think it's the real deal.  They were sort of FSJ-like, but they were missing the sharp edge that a real FSJ post would have.  Part of what makes FSJ so fun to read is how close to the truth he appears to get at.

If you do a search on Twitter for FSJ, you get a couple of hits.  None of them are the authentic FSJ.  His Twitter handle is FakeSteveTwit, which oddly enough doesn't come up in that Twitter search.  Doing a parody of a famous person can be entertaining, the "real" Fake Steve Jobs is both entertaining and informative.  He does more than a Mad Magazine style of parody, FSJ covers actual Apple events and applies the FSJ twist to the analysis.  The man behind the satire, Daniel Lyons, is a real journalist and does a good job covering the stock options backdating scandal that involved the real Steve Jobs a while back.  When you read FSJ, you wonder how to close he gets to the actual Steve Jobs and does the real Steve Jobs ever wish he could say some of things that the FSJ says.  None of the FSJ clones on Twitter have that magic.  Even if they could, it would hard to produce that in 140 character limit of a Twitter post.

Siooma is welcome to follow my Twitter feed, but I'm not planning on reciprocating.  FSJ has but some serious time and effort in his blog.  The FSJ wannabees are just riding on his coattails.  When they list his blog as their web site, it comes off that they are pretending to be the FSJ.  Lyons does not have a monopoly on being a fake Steve Jobs, but it's not right to claim his site as your own.  If Siooma and the other clones want to be another FSJ, then they should get their own FSJ blogs and develop their own personas.

Friday, May 02, 2008

How to rename database objects to comply with naming conventions

We have guidelines for the naming conventions of our database objects.  It's pretty much the common pattern you see all over the place. For example, we use the following convention for default constraints:

DF_TableName_ColumnName, such as DF_Student_School

Pinal Dave has great set of database coding standards on his site, more in-depth than ours actually.  We have found that using coding standards makes the database schema easier to read and makes it easier to perform schema updates.  If you don't explicitly name a default, SQL Server will name it for you.  And it wont be pretty or consistent across databases.  We have our own tool for pushing out schema updates to our customers and it assumes the object names will be the same for each database.

When we submit our schema updates internally, we usually catch any deviation from our naming conventions.  It's not a perfect process and every now and then, something slips through the cracks.  We then correct the schema update to use the appropriate naming convention.  if we have been using the schema changes internally, we may have some databases that don't match the published schema for the object names.  When that happens, we run a simple T-SQL script that cleans house.  The following T-SQL will scan the database for default constraints that do not match our naming conventions and rename the ones that it finds.

DECLARE @OldName nvarchar(512)
DECLARE @NewName nvarchar(512)
DECLARE @OldToNewName nvarchar(512)

-- We use a table variable to contain the list of objects to be renamed.
DECLARE @FixSchema TABLE(
sys_OldName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
sys_NewName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)

-- Get the list of current column defaults
-- The OldName column represents the current name of the default
-- The NewName column represents the name of the default using
-- our naming convention
INSERT INTO @FixSchema(sys_OldName, sys_NewName)
select o.name AS OldName
,'DF_' + Object_Name(o.parent_obj) + '_' + c.Name AS NewName
FROM sysobjects o
JOIN syscolumns c ON o.parent_obj = c.id AND c.cdefault = o.id
WHERE o.type = 'D'

-- Declare cursor on this table variable to access on the non-compliant
-- constraints
DECLARE AddDrop CURSOR FOR
SELECT sys_OldName, sys_NewName
FROM @FixSchema
where sys_OldName <> sys_NewName

OPEN AddDrop

-- Loop through the list of defaults where the default name is not
-- strongly typed
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Assenble a T-SQL command that can be executed dynamically
-- to rename the constraint
SELECT @OldToNewName = 'sp_rename ' + QUOTENAME(@OldName,'''') +
', ' + QUOTENAME(@NewName,'''') + ', ' + QUOTENAME('object','''')

-- Print the command to be executed. Useful for seeing
-- what is being done
PRINT @OldToNewName

-- Execute the T-SQL to rename the object. If the EXEC line is
-- commented out, the code will display what would be changed
-- with making any actual changes to the schema
EXEC(@OldToNewName)
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName
END

-- Close and deallocate the cursor
CLOSE AddDrop
DEALLOCATE AddDrop

I usually use the INFORMATION_SCHEMA views to peek at the table and column structures.  The INFORMATION_SCHEMA.COLUMNS view will tell which columns have default constraints, but not the name of the constraint. In this case I needed to access the sysobjects table to retrieve the name of the constraint.  The same type of code can be used to rename other objects like check constraints.