Archive for June, 2008

Dropping Unique Constraints in SQL Server

The problem

I got started with relational databases with mysql,  so I’m in the habit of making database changes with SQL scripts,  rather than using a GUI.  Microsoft SQL Server requires that we specify the name of a unique constraint when we want to drop it.  If you’re thinking ahead,  you can specify a name when you create the constraint;  if you don’t,  SQL Server will make up an unpredictable name,  so you can’t write a simple script to drop the constraint.

A Solution

In the spirit of “How To Drop A Primary Key in SQL Server“,  here’s a stored procedure that queries the data dictionary to find the names of any unique constraint on a specific table and column and drop them:

CREATE PROCEDURE [dbo].[DropUniqueConstraint]
    @tableName NVarchar(255),
    @columnName NVarchar(255)
AS
    DECLARE @IdxNames CURSOR
   
    SET @IdxNames = CURSOR FOR
        select sysindexes.name from sysindexkeys,syscolumns,sysindexes
            WHERE
                syscolumns.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND sysindexkeys.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND sysindexes.[id] = OBJECT_ID(N'[dbo].['+@tableName+N']')
                AND syscolumns.name=@columnName
                AND sysindexkeys.colid=syscolumns.colid
                AND sysindexes.[indid]=sysindexkeys.[indid]
                AND (
                    SELECT COUNT(*) FROM sysindexkeys AS si2
                    WHERE si2.id=sysindexes.id
                    AND si2.indid=sysindexes.indid
                )=1
    OPEN @IdxNames
    DECLARE @IdxName Nvarchar(255)
    FETCH NEXT FROM @IdxNames INTO @IdxName    

    WHILE @@FETCH_STATUS = 0 BEGIN
        DECLARE @dropSql Nvarchar(4000)

        SET @dropSql=
            N'ALTER TABLE  ['+@tableName+ N']
                DROP CONSTRAINT ['+@IdxName+ N']'
        EXEC(@dropSql)
           
        FETCH NEXT FROM @IdxNames
        INTO @IdxName
    END
CLOSE @IdxNames
DEALLOCATE @IdxNames

Usage is straightforward:

EXEC [dbo].[DropUniqueConstraint]
    @tableName='TargetTable',
    @columnName='TargetColumn'

This script has a limitation:  it only drops unique constraints that act on a single column,  not constraints that act on multiple columns.   It is smart enough,  however,  to not drop multiple-column constraints in case one of them involves @columnName.

Feedback from SQL stored procedure wizards would be mostly certainly welcome.

kick it on DotNetKicks.com

Getting back to the UI Thread in Silverlight 2 Beta 2

The problem

Silverlight 2 Beta 2 has changed the concurrency model for asynchronous communications.  In Silverlight 2 Beta 1,  asynchronous requests always returned on the UI Thread.  This was convenient,  since updates to the user interface can only be done via the UI thread.  As of Silverlight 2 Beta 2,  asynchronous callbacks are fired in worker threads that come from a thread pool:  although this potentially allows for better performance via concurrency,  it increases the risk for race conditions between callbacks –  more importantly,  some mechanism is necessary to make code that updates the user interface run in the UI thread.

A solution

It’s straightforward to execute a function in the UI thread by using the Dispatcher property of any ScriptObject The tricky part is that ScriptObjects are part of the user interface,  so you can only access the Dispatcher property from the UI thread.  At first this seems like a chicken-and-egg situation:  you need a Dispatcher to get to the UI thread,  but you need to be in the UI thread to get a Dispatcher.

This dilemma can be resolved by accessing a Dispatcher in your App.xaml.cs file and stashing it away in a static variable on application startup:

private void Application_Startup(object sender, StartupEventArgs e) {
    ...
    UIThread.Dispatcher = RootVisual.Dispatcher;
}

UIThread is a simple static class:

public static class UIThread {
    public static Dispatcher Dispatcher {get; set;}
    public static void Run(Action a) {
        Dispatcher.BeginInvoke(a);
    }
}

At some point in the future,  you can transfer execution to the UIThread by scheduling a function to run in it.

public class ProcessHttpResponse(...) {
   ...
   UIThread.Run(UpdateUserInterface);
}

The parameter of Run is an Action delegate,  which is a function that returns void and takes no parameters.  That’s fine and good,  but what if you want to pass some parameters to the function that updates the UI.  The usual three choices for passing state in asynchronous applications apply,  but it’s particularly easy and fun to use a closure here:

public class ProcessHttpResponse(...) {
    ...
    var elementToUpdate=...;
    var updateWithValue=...;

    UIThread.Run(delegate() {
        UpdateUserInterface(elementToUpdate,updateWithValue)
    });
}

When to return?

If your application is complex,  and you have nested asynchronous calls,  you’re left with an interesting question:  where is the best place to switch execution to the UI thread?  You can switch to the UI Thread as soon as you get back from an HttpRequest or a WCF call and you must switch to the UI Thread before you access any methods or properties of the user interface.  What’s best?

It is simple and safe to switch to the UI Thread immediately after requests return from the server.  If you’re consistent in doing this,  you’ll never have trouble accessing the UI thread,  and you’ll never have trouble with race conditions between returning communication responses.  On the other hand,  you’ll lose the benefit of processing responses concurrently,  which can improve speed and responsiveness on today’s multi-core computers.

It’s straightforward to exploit concurrency when a requests can be processed independently.  For instance,  imagine a VRML viewer written in Silverlight.  Displaying a VRML would require the parsing of a file,  the construction of the scene graph and the initialization of a 3-d engine,  which may require building data structures such as a BSP Tree.  Doing all of this work in the UI Thread would make the application lock up while a model is loading — it would be straightforward,  instead,  to construct all of the data structures required by the 3-d engine,  and attach the fully initialized 3-d engine to the user interface as the last step.  Since the data structures would be independent of the rest of the application,  thread safety and liveness is a nonissue.

Matters can get more complicated,  however,  if the processing of a request requires access to application-wide data;  response handlers running in multiple threads will probably corrupt shared data structures unless careful attention is paid to thread safety.  One simple approach is to always access shared data from the UI Thread,  and to transfer control to the UI Thread with UIThread.Run before accessing shared variables.

Conclusion

Silverlight 2 Beta 2 introduces a major change in the concurrency model for asynchronous communication requests.  Unlike SL2B1,  where asynchronous requests executed on the user interface thread,  SL2B2 launches asynchronous callbacks on multiple threads.  Although this model offers better performance and responsiveness,  it requires Silverlight programmers to explicitly transfer execution to the UI thread before accessing UI objects:  most SL2B1 applications will need to be reworked.

This article introduces a simple static class,  UIThread,  which makes it easy to schedule execution in the UI Thread.

kick it on DotNetKicks.com

Prefix-casting versus as-casting in C#

Introduction

This is a story of two types: GenericType and SpecificType, where GenericType is a superclass of SpecificType. There are two types of explicit cast in C#:

The Prefix cast:

[01] GenericType g=...;
[02] SpecificType t=(SpecificType) g;

The as cast:

[03] GenericType g=...;
[04] SpecificType t=g as SpecificType;

Most programmers have a habit of using one or the other — this isn’t usually a conscious decision, but more of a function of which form a programmer saw first. I, for instance, programmed in Java before I learned C#, so I was already in the prefix cast habit. People with a Visual Basic background often do the opposite. There are real differences between the two casting operators which can make a difference in the reliability and performance of your application.

Prefix casting: Reliable Casting

The major difference between prefix- and as-casting is what happens when the cast fails. Imagine, for instance, that g is really an instance of AnotherSpecificType, which is not a subclass of SpecificType. In this case, the prefix-cast throws an exception at line [2] — however, the as-cast returns null when the cast fails, letting the execution of the program barrel on.

It’s easier to implement correct error handling in programs that use prefix-casting, and programs that use prefix-casting are easier to debug. Prefix-casting causes an exception to be thrown at the moment of the cast, where the problem is obvious. As-casting, on the other hand, can cause an exception to be thrown at the moment where the SpecificType t is referenced. The used of the SpecificType can be far away in the program: it can be in another method, or another class — it could even happen hours after the cast is performed. Be it in development or production, bugs caused by corrupted data structures can be maddeningly difficult to find and correct.

As-casting: Fast Casting

If it’s harder to write correct programs with as-casting, why would anybody use it? For one thing, as-casting is faster than prefix casting by quite a lot. Benchmarks show that as-casting is about five times faster than prefix casting. That said, the performance of casting is only going to matter in the innermost of loops of most programs. The fastest kind of casting is no casting at all: it’s best to use generics to eliminate the need for casting when possible and to move casting outside loops. (Generics also improve the reliability of your code because they help C#’s static type checking catch mistakes.)

There are some cases where as-casting could be convenient, for instance, when you expect the cast to fail sometimes. Often I like to ‘tag’ classes with interfaces that specify certain behaviors. For example,

[05] public Interface IBoppable {
[06]     void Bop();
[07] }

Now i might want to loop through a bunch of Thing objects, and bop all the ones that implement IBoppable: it’s reasonable to use as-casting here:

[08] List<Thing> list=...
[09] foreach(Thing thing in list) {
[10]    List boppable=thing as IBoppable;
[11]    if (boppable !=null) {
[12]        boppable.Bop()
[13]    }
[14] }

It’s OK to use as-casting if you’re going to check to see if the value is null immediately after the cast. The above code is correct, but has the bad smell that the boppable variable continues to exist in the block after the cast… It’s still there for a later maintainer to use erroneously. In cases like this, code can be made clearer with the is operator:

[15] List<Thing> list=...
[16] foreach(Thing thing in list) {
[17]    if(thing is IBoppable) {
[18]        ((IBoppable) boppable).Bop()
[19]    }
[20] }

(Speed freaks can use as-cast on line 18, as we know it’s not going to fail.)

The pattern of testing for null after an as-cast has a few problems. (i) It doesn’t distinguish between the case of the original object being null from the case of the original object being the wrong type and (ii) correct error handling often requires more contorted logic than using an exception — and once you added test logic, you’ve lost the speed advantage of as-casting.

Conclusion

C# offers two casting operators: the prefix-cast and the as-cast. Although the two operators compile to different op-codes in the CLR, the practical difference between them is in how they handle failed casts. Prefix-cast throws an exception on cast failure, while as-cast returns null. It’s easier to implement correct error handling when you use prefix cast, because it doesn’t require manual checks for null values that can cause problems in distant parts of your program. Prefix-cast should be the default cast operator on your fingertips, that you use for everyday situations — reserve as-cast for special cases where performance matters. (For best performance, however, eliminate the cast entirely.)

kick it on DotNetKicks.com

How to Drop A Primary Key in Microsoft SQL Server

One of my pet peeves with Microsoft’s SQL Server is that it requires you to know the name of constraints and indexes that you’re dropping. This isn’t a problem when you’re working with the management studio, because it looks at the database metadata and generates code like

ALTER TABLE [dbo].[someTable]  DROP CONSTRAINT [PK__someTabl__3214EC07271AA44F]

It’s less convenient, however, when you’re writing a set of migration scripts in SQL to implement changes that you make over the database in time. Specifically, if you create the table twice in two different databases, the hexadecimal string in the name of the key will be different — the ALTER TABLE statement will fail when you try to drop the index later, since the name of the key won’t match.

Here’s a stored procedure that looks up the name of the primary key in the system catalog and uses dynamic SQL to drop the index:

CREATE PROCEDURE [dbo].[DropPrimaryKey]
    @tableName Varchar(255)
AS
    /*
       Drop the primary key on @TableName

http://gen5.info/q/

       Version 1.1
       June 9, 2008
    */

    DECLARE @pkName Varchar(255)

    SET @pkName= (
        SELECT [name] FROM sysobjects
            WHERE [xtype] = 'PK'
            AND [parent_obj] = OBJECT_ID(N'[dbo].['+@tableName+N']')
    )
    DECLARE @dropSql varchar(4000)

    SET @dropSql=
        'ALTER TABLE [dbo].['+@tableName+']
            DROP CONSTRAINT ['+@PkName+']'
    EXEC(@dropSql)
GO
Once you've loaded this stored procedure,  you can write
EXEC [dbo].[DropPrimaryKey] @TableName='someTable'

It’s just that simple. Similar stored procedures can be written to convert fields from NOT NULL to NULL and do other operation which required a named constraint.

Keeping Track Of State In Asynchronous Callbacks

When you’re writing applications that use asynchronous callbacks (i.e. Silverlight, AJAX, or GWT) you’ll eventually run into the problem of keeping track of the context that a request is being done in. This isn’t a problem in synchronous programming, because local variables continue to exist after one function calls another function synchronously:

int AddToCount(int amount,string countId)  {
   int countValue=GetCount(countId);
   return countValue+amount;
}

This doesn’t work if the GetCount function is asynchronous, where we need to write something like

int AddToCountBegin(int amount,string countId,CountCallback outerCallback) {
     GetCountBegin(countId,AddToCountCallback);
}

void AddToCountCallback(int countValue) {
    ... some code to get the values of amount and outerCallback ...
    outerCallback(countValue+amount);
}

Several things change in this example: (i) the AddToCount function gets broken up into two functions: one that does the work before the GetCount invocation, and one that does the work after GetCount completes. (ii) We can’t return a meaningful value from AddToCountCallback, so it needs to ‘return’ a value via a specified callback function. (iii) Finally, the values of outerCallback and amount aren’t automatically shared between the functions, so we need to make sure that they are carried over somehow.
There are three ways of passing context from a function that calls and asynchronous function to the callback function:

  1. As an argument to the callback function
  2. As an instance variable of the class of which the callback function is a class
  3. Via a closure

Let’s talk about these alternatives:

1. Argument to the Callback Function

In this case, a context object is passed to the asynchronous function, which passes the context object to the callback. The advantage here is that there aren’t any constraints on how the callback function is implemented, other than by accepting the context object as a callback. In particular, the callback function can be static. A major disadvantage is that the asynchronous function has to support this: it has to accept a state object which it later passes to the callback function.

The implementation of HttpWebRequest.BeginGetResponse(AsyncCallback a,Object state) in the Silverlight libraries is a nice example. If you wish to pass a context object to the AsyncCallback, you can pass it in the second parameter, state. Your callback function will implement the AsyncCallback delegate, and will get something that implements IAsyncResult as a parameter. The state that you passed into BeginGetResponse will come back in the IAsyncResult.AsyncState property. For example:

class MyHttpContext {
	public HttpWebRequest Request;
        public SomeObject FirstContextParameter;
        public AnotherObject AnotherContextParameter;
}

protected void myHttpCallback(IAsyncResult abstractResult) {
	MyHttpContext context = (MyHttpContext) abstractResult.AsyncState;
	HttpWebResponse Response=(HttpWebResponse) context.Request.EndGetResponse(abstractResult);
}

public doHttpRequest(...) {
	...
        MyHttpContext context=new MyHttpContext();
	context.Request=Request;
	context.FirstContextParameter = ... some value ...;
	context.AnotherContextParameter = .. another value ...;
	Request.BeginGetResponse();
	Request.Callback(myHttpCallback,context);
}

Note that, in this API, the Request object needs to be available in myHttpCallback because myHttpCallbacks get the response by calling the HttpWebResponse.EndGetResponse() method. We could simply pass the Request object in the state parameter, but we’re passing an object we defined, myHttpCallback, because we’d like to carry additional state into myHttpCallback.

Note that the corresponding method for doing XMLHttpRequests in GWT, the use of a RequestBuilder object doesn’t allow using method (1) to pass context information — there is no state parameter. in GWT you need to use method (2) or (3) to pass context at the RequestBuilder or GWT RPC level. You’re free, of course, to use method (1) when you’re chaining asynchronous callbacks: however, method (2) is more natural in Java where, instead of a delegate, you need to pass an object reference to designate a callback function.

2. Instance Variable Of The Callback Function’s Class

Functions (or Methods) are always attached to a class in C# and Java: thus, the state of a callback function can be kept in either static or instance variables of the associated class. I don’t advise using static variables for this, because it’s possible for more than one asynchronous request to be flight at a time: if two request store state in the same variables, you’ll introduce race conditions that will cause a world of pain. (see how race conditions arise in asynchronous communications.)

Method 2 is particularly effective when both the calling and the callback functions are methods of the same class. Using objects whose lifecycle is linked to a single asynchronous request is an effective way to avoid conflicts between requests (see the asynchronous command pattern and asynchronous functions.)

Here’s an example, lifted from the asynchronous functions article:

    public class HttpGet : IAsyncFunction<String>
    {
        private Uri Path;
        private CallbackFunction<String> OuterCallback;
        private HttpWebRequest Request;

        public HttpGet(Uri path)
        {
            Path = path;
        }

        public void Execute(CallbackFunction<String> outerCallback)
        {
            OuterCallback = outerCallback;
            try
            {
                Request = (HttpWebRequest)WebRequest.Create(Path);
                Request.Method = "GET";
                Request.BeginGetRequestStream(InnerCallback,null);
            }
            catch (Exception ex)
            {
                OuterCallback(CallbackReturnValue<String>.CreateError(ex));
            }
        }

        public void InnerCallback(IAsyncResult result)
        {
            try
            {
                HttpWebResponse response = (HttpWebResponse) Request.EndGetResponse(result);
                TextReader reader = new StreamReader(response.GetResponseStream());
                OuterCallback(CallbackReturnValue<String>.CreateOk(reader.ReadToEnd()));
            } catch(Exception ex) {
                OuterCallback(CallbackReturnValue<String>.CreateError(ex));
            }
        }
    }

Note that two pieces of context are being passed into the callback function: an HttpWebRequest object named Request (necessary to get the response) and a CallbackFunction<String> delegate named OuterCallback that receives the return value of the asynchronous function.

Unlike Method 1, Method 2 makes it possible to keep an unlimited number of context variables that are unique to a particular case in a manner that is both typesafe and oblivious to the function being called — you don’t need to cast an Object to something more specific, and you don’t need to create a new class to hold multiple variables that you’d like to pass into the callback function.

Method 2 comes into it’s own when it’s used together with polymorphism, inheritance and initialization patterns such as the factory pattern: if the work done by the requesting and callback methods can be divided into smaller methods, a hierarchy of asynchronous functions or commands can reuse code efficiently.

3. Closures

In both C# and Java, it’s possible for a method defined inside a method to have access to variables in the enclosing method. In C# this is a matter of creating an anonymous delegate, while in Java it’s necessary to create an anonymous class.

Using closures results in the shortest code, if not the most understandable code. In some cases, execution proceeds in a straight downward line through the code — much like a synchronous version of the code. However, people sometimes get confused the indentation, and, more seriously, parameters after the closure definition and code that runs immediately after the request is fired end up in an awkward place (after the definition of the callback function.)

    public class HttpGet : IAsyncFunction<String>
    {
        private Uri Path;

        public HttpGet(Uri path)
        {
            Path = path;
        }

        public void Execute(CallbackFunction<String> outerCallback)
        {
            OuterCallback = outerCallback;
            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Path);
                Request.Method = "GET";
                Request.BeginGetRequestStream(delegate(IAsyncResult result) {
	            try {
                        response = request.EndGetResponse(result);
                        TextReader reader = new StreamReader(response.GetResponseStream());
                        outerCallback(CallbackReturnValue<String>.CreateOk(reader.ReadToEnd()));
                    } catch(Exception ex) {
                        outerCallback(CallbackReturnValue<String>.CreateError(ex));
                    }
            },null); // <--- note parameter value after delegate definition
            }
            catch (Exception ex)
            {
                outerCallback(CallbackReturnValue<String>.CreateError(ex));
            }
        }
    }

The details are different in C# and Java: anonymous classes in Java can access local, static and instance variables from the enclosing context that are declared final — this makes it impossible for variables to be stomped on while an asynchronous request is in flight. C# closures, on the other hand, can access only local variables: most of the time this prevents asynchronous requests from interfering with one another, unless a single method fires multiple asynchronous requests, in which case counter-intuitive things can happen.

Conclusion

In addition to receiving return value(s), callback functions need to know something about the context they run in: to write reliable applications, you need to be conscious of where this information is; better yet, a strategy for where you’re going to put it. Closures, created with anonymous delegates (C#) or classes (Java) produce the shortest code, but not necessarily the clearest. Passing context in an argument to the callback function requires the cooperation of the called function, but it makes few demands on the calling and callback functions: the calling and callback functions can both be static. When a single object contains both calling and callback functions, context can be shared in a straightforward and typesafe manner; and when the calling and callback functions can be broken into smaller functions, opportunities for efficient code reuse abound.

kick it on DotNetKicks.com