Nigel Morse

The Making of SQL Response : Battle of the Testers

Nigel Morse — Fri, 12 Sep 2008 16:54:00 GMT

I’ve just closed my last bug on SQL Response (or at least the last one that’s left on my plate for this version). Code freeze is tomorrow after which it gets a complete going over by the testers and pending any major dramas should be released in the next few weeks.

When I joined Red Gate they said they tended to do short projects – a few months or so before shipping the new version of the product. Great! This was going to be a sharp contrast to my previous employment where we worked on essentially one large product (parts of which were 15-20 years old). Projects there tended to be on the long side, but Response has been a 14 month project which is quite a while for Red Gate.

So the length of project wasn’t new to me, however the major contrast is that the team I’ve been on were responsible for the whole of the product and not just one little bit of it. This means my sense of involvement and responsibility has been much greater. I’m also one of the people who have been on this project fulltime since the outset which is probably why I think of it more fondly. It’s my first major piece of work at Red Gate and as such it’s also shown me a very new and, in my opinion, a much better way to build software. Which is why I’ve really (really, really) enjoyed working here so far.

So what was new (to me) about the process? Well in terms of the overall structure –Design, Implement, Test, rinse and repeat – not much. It’s more the depth and execution of each of those steps

Design

Initially it started with our designer (Dom), the product manager (Colin), the initial project manager (Dan A) and myself going out and talking to customers and trying to build up a picture of what they wanted.

For a start the very idea of having a user experience designer was a whole new one, in the past I had to come up with my own UI and given art is the probably the only exam I’ve ever failed you can rest assured that my aesthetic ability is fairly minimal. The best way to describe it is that Dom designs the “User Experience” and it’s very much this design that drives the way we build the product. What does the user want to see, what do they want to do when they see it. Obviously you can’t just throw this together – there’s some sketching and ideas and research about what we can/can’t do. I dug up the fruits of our very first ideas meeting.

Dom doesn’t write code – instead he mocks things up in Visio (he doesn’t even feel the need to be constrained by Visual Studio’s UI designer!). Every dialog is constructed, discussed as to what is actually possible and can be implemented before the heat death of the universe and refined. Then we have to actually cut some code.

Implement

At first it was Dan and myself on the coding. Dan was also project manager but he left Red Gate sometime after our initial beta having helped with the re-design. David then came aboard and took over from Dan as project manager/coder and also Daniel joined as well (a different Dan/Daniel obviously!). As far as coding goes that’s pretty much of muchness I guess – it’s what all software companies do which is why there is so much software out there. The problem is that I think a lot of places just do the cutting code and ship the result, or pay merely lip service to the other parts. Here, however, Dom remained involved throughout as new issues came out to tweak or even redesign parts and to make sure each dialog was just the right shade of pink.

Here also there was another stark difference to previous jobs. Our technical author (Brian) was already involved. Rather than being asked to just knock up some documentation at the end of the release cycle he was already going over the designs and early builds making sure that what we wrote was not only English (another of my failings) but hopefully understandable by users rather than just by the developers. We have terminology documents to make sure the language is consistent and pretty much any text in the product has come from Brian. This means he also has a good understanding of the product and is able to put the interactive help in the right places and write actually helpful online documentation.

Testing

However… by far the biggest change and for me the best one is the testing. Almost as soon as the first code hit the build server and a build came out, Jason was all over it.

To get this kind of feedback so early was a revelation as it means that someone is essentially looking over your shoulder and pointing out your errors straight away. The benefit of this is enormous because the earlier on you can find the issue, the earlier you can fix it and the easier it is to modify the structure and design of code if necessary. This is far better then finding it several months later when you discover that one thing you either didn’t realise or plain forgot about means you have to rip out and refactor your code. Of course by that point there’s no time or it’s just too painful to do that so you hack round the problem. Catching the issues early is so much less painful.

After a month or two Priya also joined in testing and then they would start to tag-team you. One would open a bug, you’d “fix” it and then the other would come back with some extra small detail and you had to “fix” it again

Obviously when coding you try to think of edge cases early on and plan to handle them, you might even write test cases to make sure. However it’s still you thinking about the same problem. The testers aren’t you and they come at things with a different perspective. They can see a whole world of cases you don’t think about, they have access to several versions of Windows and SQL server and they use every synapse to bring your code to its knees.

It’s painful but you know it makes sense and it changes the way you code. You start to think not only how you would usually, but also how to outwit the testers, how will they test this and how can I deny them opening an issue. You don’t just think of corner cases but you make sure you handle them right now and do the bit of extra work just to avoid seeing the look of glee of the their face as their mouse heads towards the “Create new issue” button.

Ultimately it’s a battle you will lose because there’s always something they can find. You can at least try to make it hard and laugh at their desperation when all they have left is to ask you to add a period character to some text or just align this label 1 pixel to the left but you still fix them because, although it hurts, on the inside you know they are right.

Jason and Priya have to put up with a lot, from me at least. I joke that my first response to any bug is simply to close it and see how many actually come back (sometimes it’s even true). Sometimes I’ll close it saying “that’s as designed – there’s no way we can change that” and I know that if they call me over to “discuss” it that I’ll end up fixing it somehow because they’ll think of a way it can be done. I’ll argue anyway because at least it distracts them from finding other issues and you can’t let them have it to easy.

So whilst this battle continues with thrust and parry, ultimately you’re on the same side trying to write a product that people actually want and like to use (which is probably why the tester nearly always wins). The team works with testers and developers in a symbiosis with new features being tested early and retested often for regressions. I’ve said to Jason that I couldn’t do his job and he made the same claim about mine, but working closely together we hopefully get the best out of both of use and hopefully that reflects in the product.

Thanks to all the team.

Clockwise from back left: David (project manager/developer), Claire (marketing), Dom (designer), Helen (manager), Colin (product manager), Brian (technical author), Daniel (developer), Nigel (developer), Priya (tester), Eddie (support champion). Not in the photo: Jason (tester) – he’s afraid making images of him will divest him of his supreme ability to find bugs or something.

So I hope you get to try out SQL Response and I hope you like it. There is a release candidate out right now and a survey you fill in to win a $100 Amazon voucher. I’ll be heading for a beer or several very soon.

Anonymous delegates rock

Nigel Morse — Tue, 18 Mar 2008 23:33:00 GMT

I'm finding I really like C#'s anonymous delegates. I've used them several times now in various ways. Normal delegates are already a Good Thing, however basically they are C(++) function pointers with a much nicer syntax. Anonymous delegates do clever things like having the ability to access local variables. This just provides some syntactic sugar and the compiler under the hood creates classes to hold the variables and puts the anonymous method on that class. Take a look with a decompiler and you can see what gets generated.

One "obvious" pattern I've seen people here come up with independently is to invoke something on a worker thread from a UI thread, or the other way round (and sometimes both by nesting one delegate inside another).

However another use I like is what I want to share today.

I've been using WMI a bit recently. WMI can be used (amongst other things) to get information about remote computers. I won't go into the detail here - there's plenty on the web about it (http://en.wikipedia.org/wiki/Windows_Management_Instrumentation). I may even do a blog myself on it one day.

To access WMI via .NET you use the System.Management namespace. There are various ways to call and get the information, but a common example is to run a query in a SQL-esque way - e.g. "select TotalPhysicalMemory from Win32_ComputerSystem" will get you the total physical memory on a machine. However to run a query like this requires quite verbose code, not least because a lot of objects you create need Dispose()ing of afterwards.


ManagementScope scope = new ManagementScope( "\\\\mymachinename\\root\\cimv2" );

scope.Connect();

objQry = new ObjectQuery( "select TotalPhysicalMemory from Win32_ComputerSystem" );

UInt64 totalMemory = 0;

using( ManagementObjectSearcher searcher = new ManagementObjectSearcher( scope, 

{

    using( ManagementObjectCollection collection = searcher.Get() )

    {

        foreach( ManagementObject obj in collection )

        {

            totalMemory = (UInt64)obj["TotalMemory"];

            obj.Dispose();

        }

    }

}

So great - i've got that out, but I want to find out how much free disk it has as well. This query is "select Name,FreeSpace from Win32_LogicalDisk where DriveType=3" and could well return me more than one disk. (DriveType=3 means physical disks on this machine as opposed to mapped network drives etc.)

I can reuse the scope so the extra code is...

objQry = new ObjectQuery( "select Name,FreeSpace from Win32_LogicalDisk where DriveType=3" );

Dictionary<string,UInt64> drives = new Dictionary<string,UInt64>();

using( ManagementObjectSearcher searcher = new ManagementObjectSearcher( scope, 

{

    using( ManagementObjectCollection collection = searcher.Get() )

    {

        foreach( ManagementObject obj in collection )

        {

            drives.add( (string)obj["Name"], (UInt64)obj["FreeSpace"] );

            obj.Dispose();

        }

    }

}

But that instantly offends me - I have 13 lines of code, of which 10 are the same as above. That's 10 lines repeated whenever I want to run another query.

Instead I can define a method like this:

private delegate void UseManagementObject( ManagementObject obj );



private static void DoWmiQuery( ManagementScope scope, string qry, UseManagementObject callback )

{

    ObjectQuery objQry = new ObjectQuery( qry );            

    using( ManagementObjectSearcher searcher = new ManagementObjectSearcher( scope, objQry ) )

    {

        using( ManagementObjectCollection collection = searcher.Get() )

        {

            foreach( ManagementObject obj in collection )

            {

                callback( obj );

                obj.Dispose();

            }

        }

    }

}

Now I can reduce my 2 previous pieces to this:

ManagementScope scope = new ManagementScope( "\\\\mymachinename\\root\\cimv2" );

scope.Connect();

UInt64 totalMemory = 0;

DoWMiQuery( scope, "select TotalPhysicalMemory from Win32_ComputerSystem", delegate( ManagementObject obj )

                   {

                       totalMemory = (UInt64)obj["TotalMemory"];

                   } );



Dictionary<string,UInt64> drives = new Dictionary<string,UInt64>();

DoWMiQuery( scope, "select Name,FreeSpace from Win32_LogicalDisk where DriveType=3", delegate( ManagementObject obj )

                   {

                       drives.add( (string)obj["Name"], (UInt64)obj["FreeSpace"] );

                   } );

Jobs a good'un :)

Do you yield?

Nigel Morse — Wed, 31 Oct 2007 20:30:00 GMT

Yay! I’ve actually used the C# yield statement for the first time. I’ve wanted to use it ever since I read about it, but never really had a use for it until today.

I was writing some code to read a SQL Server error log and eventually found an undocumented stored procedure sp_readerrorlog (amazing how so much useful stuff for SQL Server seems to be “undocumented”).

This procedure works on 2000 and 2005 and returns the entire current error log. However, (probably partly due to its undocumented nature) the different versions return the results in a different format. I need to look for things in the log so my first thought was to create a Parse2000Log and a Parse2005Log and then have these call a 3^rd method (e.g. ParseLogLine) for each line. The problem with this was that the messages can span multiple lines. For example if you do a

RAISERROR( 'foo', 20, 1 ) WITH LOG

the log shows.

2007-10-30 15:11:36.07 spid52 Error: 50000, Severity: 20, State: 1
2007-10-30 15:11:36.07 spid52 foo.

This would mean I need to keep state in my ParseLogLine function to track which line I expect next, which means I’d need to make some class variables which I’d really rather avoid. I’m more in favour of using local stack variables when I can. Obviously there are numerous ways to skin this (as with most) cats, but then I hit on the idea of using yield to provide an enumerator over the results and hand back a struct for each line to a calling function. This means I can separate out the differences between the servers into 2 functions but call them from a common function that can act on the actual message.

The 2005 function is by far the simpler as it 2005 has pre-parsed the log into separate columns:

      // Struct to hold a line of the logfile
        private struct LogLine
        {
            public DateTime date;
            public string processInfo;
            public string message;
        }
        private static IEnumerable <LogLine> Parse2005Log SqlDataReader reader )
        {
            LogLine ret;
            while( reader.Read() )
            {
                ret.date = reader.GetDateTime( 0 );
                ret.processInfo = reader.GetString( 1 );
                ret.message = reader.IsDBNull( 2 ) ?
                                String.Empty : reader.GetString( 2 );
                yield return ret;
            }

And there is the yield in action. What actually happens is that the compiler generates a class for you which (in this case) inherits from IEnumerable<T> andIEnumerator<T> and has the methods necessary. This means you only have to write one method and not the whole class which I think makes for neat solution in this case. You can make the method return IEnumerable<T> orIEnumerator<T> (or the non-generic variants if you really want) and it will work it out. Here I chose IEnumerable<T> so I can use it in a foreach statement

        IEnumerable<LogLine> logreader;
        if( s_is2005Server )
            logreader = Parse2005Log( reader );
        else
            logreader = Parse2000Log( reader );
        foreach( LogLine line in logreader )
        {
          Console.WriteLine( "{0} : {1} : {2}", line.date,
                                line.processInfo, line.message );
        }

Here’s the full example:

private struct LogLine
        {
            public DateTime date;
            public string processInfo;
            public string message;
        }
        static void Main(string[] args)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = s_machineName;
            builder.IntegratedSecurity = true;
            string connStr = builder.ConnectionString;

            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        comm.CommandType = CommandType.StoredProcedure;
                      comm.CommandText = "sp_readerrorlog";
                        SqlDataReader reader = comm.ExecuteReader();
                        IEnumerable<LogLine> logreader;
                        if (s_is2005Server)
                           logreader = Parse2005Log(reader);
                        else
                            logreader = Parse2000Log(reader);
                        foreach (LogLine line in logreader)
                        {
                            Console.WriteLine("{0} : {1} : {2}", line.date,
                                          line.processInfo, line.message);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Exceptional! - " + ex.Message);
            }
        }
        private static IEnumerable<LogLine> Parse2000Log(SqlDataReader reader)
        {
            LogLine ret;
            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                    string line = reader.GetString(0);

                    // The format of the SQL 2000 log file is fairly
                    // contstrained to be a fixed length
                    // date, then fixed length process info field and then
                    // the message.
                    //
                    // .. or it can just be freeform

                    string maybedate = line.Substring(0, 22);
                    DateTime date;
                    if (DateTime.TryParse(maybedate,
                                           CultureInfo.InvariantCulture,
                                           DateTimeStyles.AssumeLocal,
                                           out date)
                               && !line.StartsWith("\t"))
                    {
                        ret.date = date;
                        ret.processInfo = line.Substring(23, 7).TrimEnd();
                        ret.message = line.Substring(33).TrimEnd();
                    }
                    else
                    {
                        ret.date = DateTime.MinValue;
                        ret.processInfo = string.Empty;
                        ret.message = line;
                    }
                    yield return ret;
                }
            }
        }
        private static IEnumerable<LogLine> Parse2005Log(SqlDataReader reader)
        {
            LogLine ret;
            while (reader.Read())
            {
                ret.date = reader.GetDateTime(0);
                ret.processInfo = reader.GetString(1);
                ret.message = reader.IsDBNull(2)
                             ? String.Empty : reader.GetString(2);
                yield return ret;
            }
        }