Still time to enter the Darwin Race of Languages

Do you think that hand tweaked C code runs circles around managed code (C#, Java, VB.NET)?  Or do you think that your Just In Time compiled managed code gets the job done more securely than those bit sniffing dinosaurs?  Anxious to prove that Delphi is still relevant?  Want to win some cutting edge component libraries from DevExpress?

If you answered “yes” to any of those questions, there is still time to enter the Darwin Race of Languages.  The brainchild of Sevensteps CTO Bart Roozendaal, the Darwin Race is pitting a slate of challengers using the compiler and IDE of their choice to write a utility that will solve one of three problems.  The projects are the following:

• Temperatures and CPU load
  A utility that will measure CPU temperatures and CPU load on remote computers
• Multi clipboard utility
  The utility should hold several entries that were place on the clipboard earlier. In effect it should replace the clipboard.
• GUI to a command line program
  A GUI front end for the gbak database command line driven program for the Firebird database.

Each entry will be judged on the software itself and how you documented the design and development of the application.  The goal is not to see which development is best tool, but to see what tools are the best for different types of jobs.  To see where the strengths are and where are the weaknesses of each tool, using real world types of the problems.  The documentation part will share the development process and show how different people come up with solutions for similar problems.

There are already a bunch of sponsors lined up.  DevExpress will be giving away a a license for DXperience Enterprise or the VCL Subscription.  If you wint, you get to pick one of them.  I have DXperience and most of the VCL components, you can’t go wrong with either one of them.

The race is on already, but there is still time to join.  There are a bunch of participants already signed up, covering C#, Delphi, VB, and something on the Mac platform.  Java is not yet represented, does someone out there want to pick up the Java gauntlet?

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.