Jay Harris is Cpt. LoadTest

With our new nightly database restore we now have the desire to automatically run all of the change scripts associated with a project. We’ve found a way; I created a NAnt script that will parse the Visual Studio Database Project (or "DBP") and execute all of the change scripts in it. Here’s how we got there.

Problem 1: Visual Studio Command Files are worthless

Our first idea was to have everyone update a command file in the DBP, and have NAnt run it every night. Visual Studio command files are great and all, but we have discovered a problem with them: they do not keep the files in order. We have named all of our folders (01 DDL, 02 DML, etc) and our change scripts (0001 Create MyTable.sql, 0002 AddInfoColumn to MyTable.sql) accordingly so that they should run in order. We have found that the command file feature of VS.Net 2003 does not keep them in order but rather seems to sort them first by extension, then by order, or some similar oddness. Obviously, if I try to at InfoColumn to MyTable before MyTable exists, I’m going to have a problem. So, the command file idea was axed.

Problem 2: Visual SourceSafe contents can’t be trusted

Our second idea was to VSSGET the DBP directory in VSS and execute every script in it. However, the VSS store cannot be trusted. If a developer creates a script in VS.Net called ‘0001 Crate MyTable.sql’ and checks it in to the project, then proceeds to correct the spelling error in VS.Net to ‘0001 Create MyTable.sql’, VS does not rename the old file in VSS. Instead, it removes the old file from the project, renames it locally, then adds the new name to the project and to VSS. It also never deletes the old file name from the VSS store. Now, both files (’0001 Crate MyTable.sql’ and ‘0001 Create MyTable.sql’) exist in VSS. Performing a VSSGET and executing all scripts will run both scripts, which could lead to more troubles.

So, we can’t use a command file, because it won’t maintain the order. We can’t trust VSS, since it can have obsolete files. We can only trust the project, but how do we get a list of files, ourselves?

Fortunately, DBP files are just text in a weird XML-wannabe format. The NAnt script will open the file and run through it looking for every ‘SCRIPT’ entry in the file. If it finds a ‘BEGIN something’ entry, it assumes that ’something’ is a folder name, and appends it to the working path until it finds ‘END’, at which time it returns to the parent directory.

It’s not perfect. It still runs in to some problems, but here it is in v0.1 form.

<project name="RunDBPScripts" default="RunScripts">
<!–-
Execute all scripts in a VS.Net DBP
Author: Jay Harris, http://www.cptloadtest.com, (c) 2005 Jason Harris
License: This work is licensed under a  
   Creative Commons Attribution 3.0 United States License.  
   http://creativecommons.org/licenses/by/3.0/us/ 

This script is offered as-is.
I am not responsible for any misfortunes that may arise from its use.
Use at your own risk.
-–>
<!-– Project: The path of the DBP file –->
<property name="project" value="Scripts.dbp" overwrite="false" />
<!-– Server: The machine name of the Database Server –->
<property name="server" value="localhost" overwrite="false" />
<!-– Database: The database that the scripts will be run against –->
<property name="database" value="Northwind" overwrite="false" />
<target name="RunScripts">
        <property name="currentpath"
            value="${directory::get-parent-directory(project)}" />
        <foreach item="Line" property="ProjectLineItem" in="${project}">
            <if test="${string::contains(ProjectLineItem, 'Begin Folder = ')}">
                <regex pattern="Folder = &quot;(?’ProjectFolder’.*)&quot;$"
                    input="${string::trim(ProjectLineItem)}" />
                <property name="currentpath"
                    value="${path::combine(currentpath, ProjectFolder)}" />
            </if>
            <if test="${string::contains(ProjectLineItem, 'Script = ')}">
                <regex pattern="Script = &quot;(?’ScriptName’.*)&quot;$"
                    input="${string::trim(ProjectLineItem)}" />
                <echo message="Executing Change Script (${server+"\"+database}): ${path::combine(currentpath, ScriptName)}" />
                <exec workingdir="${currentpath}" program="osql"
                    basedir="C:\Program Files\Microsoft SQL Server\80\Tools\Binn"
                    commandline=’-S ${server} -d ${database} -i “${ScriptName}" -n -E -b’ />
            </if>
            <if test="${string::trim(ProjectLineItem) == 'End’}">
                <property name="currentpath"
                    value="${directory::get-parent-directory(currentpath)}" />
            </if>
        </foreach>
    </target>
</project>

I used an <EXEC> NAnt task rather than <SQL>. I found that a lot of the scripts would not execute in the SQL task because of their design. VS Command Files use OSQL, so that’s what I used. I guess those command files were worth something after all.

If you know of a better way, or have any suggestions or comments, please let me know.

With all that we stuff into the database on the QA environment, we need to perform a regular database restore. This way, we also get a fresh DB without any of the corruption from the previous day’s QA attacks.

I created a NAnt script to automate the process, including restoring security access when we restore from a backup created on a different machine. Centerting around the NAnt code below, my script disconnects all current connections to the database in question (we can not restore the DB without dropping it, and we can not drop it while connections are open), drops and restores the database, refreshes security, and performs a few other tasks such as setting all email addresses to internal addresses to prevent spamming the client and truncating the log since our server is a little short on disk space.

if exists (Select * from master.dbo.sysdatabases where name = '${database}')
Begin
    DROP DATABASE [${database}]
End
 
RESTORE DATABASE [${database}]
    FROM DISK = N'${backupfile}'
    WITH FILE = 1,
    NOUNLOAD ,
    STATS = 10,
    RECOVERY,
    – changes file locations from what was in the backup
    MOVE '${dataname}' TO '${path::combine(datadirectory,database+'.mdf')}',
    MOVE '${logname}' TO '${path::combine(logdirectory,database+'_Log.ldf')}'

As Lead QA, I have the fun responsibility of screening resumes and conducting phone interviews. I weed out the hackers from the script kiddies before we bring them in to face the firing squad. It never fails to amaze me how people embellish their resume beyond reasonable limits. I am particularly fond of people that list skills they can not define, and of people who don’t proof read their resume when applying for a detail-oriented position.

As I run through my stack of paper I came across one unfortunate soul that did both. I was quite amused in a genuinely entertained sense. He proclaimed is proficiency in ‘Quick Teat Professional 8.0′, presumably an application through which you can automate cow milking, complete with data drivers and checkpoints. “OK. So he missed the ’s’ and didn’t catch it. So what?” Well, he also bolded the misspelling, perhaps to point out his attentiveness. This was only slightly before listing its usage in 2003 for a former employer that he also misspelled. (Note: QTP v8.0 was not available until the summer of 2004.)

However, and forgivably, my recruiter is not aware of such things and had already scheduled a phone interview for me and my entertaining candidate; I honored the call, giving the prospective a chance at redemption.

He failed.

Question number two asks the candidate to list the types of testing with which s/he has experience. This reply included integration testing (also stated in his resume, correctly spelled). My follow-up asked him to define integration testing; a common ploy to make sure I’m not just being fed buzz-words. It was a definition he could not supply, or even attempt.

A candidate should be able to define every ‘word’ he claims experience with. If you can not define it you obviously do not have enough experience in it to make it applicable. If you can not define ‘integration testing’, I will not hold it against you providing you do not list experience in it. Similarly, if you do not list it, and I ask you what you know about it, be straight; tell me straight-up that you cannot define it. You will rate higher in my book than someone who stumbles through an obviously concocted and blatantly incorrect response.

BTW, if you are looking for a position as a quality analyst, and can work in the Brighton, Michigan area, drop me a line and a resume. I would be happy to hear from you. Ability to define ‘integration testing’ a plus.

Hopefully this will save a few of you some time: I have created a registry entry that will create file associations and commands for your NAnt .build files. It will associate .build files as “NAnt Build Files” and create two commands for right-clicking a .build file in Explorer: “Edit” will open the file in Notepad; “Run” will execute the file in NAnt using a persistent command window (the window won’t disappear when the script is finished).

NAnt Build File Associations

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\.build]
@=”build_auto_file”

[HKEY_CLASSES_ROOT\build_auto_file]
@=”NAnt Build File”
“EditFlags”=dword:00000000
“BrowserFlags”=dword:00000008

[HKEY_CLASSES_ROOT\build_auto_file\shell]
@=”Edit”

[HKEY_CLASSES_ROOT\build_auto_file\shell\&Run]
@=”Run”

[HKEY_CLASSES_ROOT\build_auto_file\shell\&Run\command]
@=”C:\WINDOWS\system32\CMD.EXE /k “C:\Program Files\NAnt\bin\NAnt.exe” -buildfile:%1″

[HKEY_CLASSES_ROOT\build_auto_file\shell\&Run\ddeexec]

[HKEY_CLASSES_ROOT\build_auto_file\shell\&Run\ddeexec\Application]
@=”NAnt”

[HKEY_CLASSES_ROOT\build_auto_file\shell\&Run\ddeexec\Topic]
@=”System”

[HKEY_CLASSES_ROOT\build_auto_file\shell\edit]
@=”&Edit”

[HKEY_CLASSES_ROOT\build_auto_file\shell\edit\command]
@=”C:\WINDOWS\system32\NOTEPAD.EXE %1″

[HKEY_CLASSES_ROOT\build_auto_file\shell\edit\ddeexec]

[HKEY_CLASSES_ROOT\build_auto_file\shell\edit\ddeexec\Application]
@=”NOTEPAD”

[HKEY_CLASSES_ROOT\build_auto_file\shell\edit\ddeexec\Topic]
@=”System”

Use this code/file at your own risk. I offer it as is, without any support. By downloading this file or using this code you take full responsibility for any repercussions that it may have on your computer.

The default settings of NUnit, TestRunner, and Test Driven Development all want different copies of the app.config at different locations. If ProjectName creates ProjectName.dll, then NUnit wants ProjectName.config, TR wants ProjectName.dll.config, and TDD wants TargetDir\ProjectName.dll.config. This is a lot of work to put in the post-build event of every unit test project, and can be even more work when another testing tool comes along that wants yet a new config filename. The best way to manage all of these file copies is through a common post-build event call.

Many probably opt for a NAnt script, but we found that passing in the required paths can sometimes cause NAnt to get confused, and it won’t properly parse the parameter listing. So, we went with a command file, instead.

CopyConfigs.cmd

VS.Net Post Build Event

VS.Net already includes a series of NAnt-like properties for project names, project directories, target [assembly] filenames, etc; these come in handy for creating a universal script. Placing the path references in quotes allows for spaces and other characters (Except more quotes) in the path. Executing the command file through a call allows us a little more versatility with the argument references (%~1 removes the surrounding quotes from the argument value, allowing us to append a few together without jacking the subsequent path).

Must Have Tools

• Notepad2: A vital, essential, notepad replacement. Color-codes your text using syntax schemes identified by the file’s extention. I followed Scott Hanselman and “renamed ‘Notepad2.exe’ to ‘n.exe’ which saves me a few dozen ‘otepad’s a day.”
• PsExec: [blog entry] Execute remote applications, remotely. Great for installing an MSI on a remote box without resorting to Remote Desktop. Also great for launching solitaire on your buddy’s machine and harrassing him for slacking at work.

Testing Tools

• Screen Hunter 4.0 Free: [blog entry] Free screen capture tool that is a requirement in any tester’s toolbelt.
• Watir: [blog entry] Web Application Testing In Ruby. An automated functional testing tool for automated browser tests in IE. Scripts are written in Ruby.

Continuous Integration Tools

• CruiseControl.Net: Monitor your source. Can be used to manage automated builds, build status, and reports from NUnit, FXCop, etc.
• NAnt: Free build tool for .Net. Use with CruiseControl.net to automatically build nightlies or whenever a code change occurs.
• NantContrib: An extention for NAnt. Adds some useful tasks that NAnt does not include, such as integration with VSS.

Scott Hanselman is my new hero. He is filling the hole—the one thing preventing Watir from becoming real competitor in the automated functional test market: script recording. Watch out Mercury; by creating WatirMaker, Scott is opening the flood gates, and Watir is going to come pouring through.

This changes everything.

I started out my career as a developer, but as I noted in an earlier blog, I get much more enjoyment from breaking things than I do building things, so I jumped ship. With my development experience I can delve in to making some rather wicked scripts for QTP, LoadRunner, and lately, Watir. However, my testers don’t share my skill set. My biggest hurdle in ousting QTP and making Watir our standard is the lack of recording; I can not expect every tester to start coding away in Ruby. It should come as no surprise that when I opened Scott’s blog this morning, I was so excited that I nearly wet myself.

It is a work in progress, but soon Scott hopes to have a fully functional recording tool for Watir. With WatirMaker, my testers can hit a button and start clicking away in IE; the tool will happily watch like a little kid on the sidelines, learning every move. My testers can all adopt Watir with open arms, and we can wave goodbye to that Mercury maintenance contract.

The only thing left to say is: “Scott…thanks!”

In code deployment, it is often necessary to perform tasks that must be executed locally on the destination box, such as installing through an MSI or installing assemblies to the GAC through GACUtils. Thankfully, there is a way that this can all be done remotely, with any process, as long as it can be accessed through a command prompt. The destination computer will think that you are working on it directly, though it may just be your NAnt script doing the work for you.

SysInternals publishes a tool called PsExec. It allows you to execute a program remotely on a remote machine. To the remote machine the process is running locally. Because of this, you can use traditional command line tools to run programs and utilities, such as GACUtil to install a new assembly to the GAC on a remote box–a feature that most other options don’t support.

PSExec \MyServer "C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\GACUtil.exe" "C:\bin\MyAssembly.dll"

Note: the paths are all ‘local’ paths on \\MyServer, just as you would enter from a command prompt in an RDP session to MyServer. You will also need to customize the paths to include whatever location and framework version your remote machine uses.

As for me, because our web applications make thorough use of the GAC, our only deployment method is a VS.Net Deployment project to create a MSI. We have NAnt scripts that upload the MSI to remote machines then execute PsExec run the installation (MSIExec) in unattended mode. It has brought our deployment time down from a manual 30-45 minutes to an automated 15 minutes, and allows code managers to spend those 30-45 minutes doing something else. We save even more time when we deploy to production, which contains an 8-server web farm.

If you are anything like me, you probably have the latest version of Internet Explorer and/or Firefox on your machine.
If you are anything like me, you have clients that don’t. They are often still supporting Internet Explorer 5, or some archaic version of Netscape.

Though it is a little dated, I found a rather helpful post on semicolon, today. The post on multiple Internet Explorer versions in Windows discusses stand-alone versions of Internet Explorer available through an Internet Explorer browser archive from evolt.com. The post goes one step further, identifying a defect in IE where every version uses common registry settings causing it to always identify itself as v6, even if you are using a different version. The post contains a workaround; drag this Version bookmarklet to your links toolbar, and when you click it, it will show your actual version.

I would also like to take his post one step further. The full browser archive, which semicolon does not mention. Not only does evolt include Internet Explorer, but seemingly every browser ever available, such as Netscape Navigator, Opera, and Lynx.

Scott Hanselman has a good post today about the HttpOnly cookie attribute. It secures the cookie from access via the DOM. “The value of this property is questionable since any sniffer or Fiddler could easily remove it. That said, it could slow down the average script kiddie for 15 seconds.”

Read Scott’s full blog entry.

Here’s the meat-and-potatoes of what Scott came up with; it’s for your global.asax:

protected void Application_EndRequest(Object sender, EventArgs e)
{
    foreach(string cookie in Response.Cookies)
    {
        const string HTTPONLY = ";HttpOnly";
        string path = Response.Cookies[cookie].Path;
        if (path.EndsWith(HTTPONLY) == false)
        {
            //force HttpOnly to be added to the cookie
            Response.Cookies[cookie].Path += HTTPONLY;
        }
    }
}