Crafty Code

Creating a simple Sql Server Express Database Backup

bchapman@ifinity.com.au — Fri, 22 Jan 2010 06:36:00 GMT

A lot of people use Sql Server express for the reason of it’s generous licensing. One of the drawbacks of Sql Server express is that it comes without the Sql Agent – which is the batch-running part of Sql Server. Sql Agent is used to run jobs to do all sorts of things with Sql Server, and one of the most common jobs is to run a backup schedule. And, as everyone knows, you need to have a daily backup schedule on your database, or one day, you’re going to pay for it.

This blog post will cover how to setup a backup procedure which will backup databases, zip them up, copy them to a location, ftp them to a backup server for long term storage, and clean up after itself. I’ve done this without purchasing a single piece of backup software, and relied on in-built tools or shareware.

It’s targeted towards people who are running their own web server, whether it’s co-located, hosted or a VPS. If the thought of command line utilities, scheduled tasks and file permissions stresses you, then I’d suggest the purchase of a nice backup system to ease your troubles.

If you’re on a shared hosting plan, all you need is a signature in stone that your host can provide you with last nights backup at short notice. If you are on a hosting plan, it’s worthwhile testing them out randomly by asking for a database backup, just to check out if they can lay their hands on one. The time you need it is not the time to hear ‘sorry, don’t have one’.

This setup covers a pretty common scenario where the web server doesn’t have a lot of disk space (common with VPS machines) and is not connected by an internal network to any other computers (hence the FTP part). The first part of the script runs on the server, and the second part runs on a different machine. The second machine could be another server, it could be your workstation, as long as it will be running when the script is scheduled to run. All of the various scripts are included in the attached zip file, but you’ll need to modify them, as I have taken out all the private information.

Disclaimer : by all means copy what I have done, but you’re on your own when it comes to setting this up and installing it. I don’t make any promises that it will work for you, or that it is the most secure, or most robust, or most perfect way. It’s a simple setup and not suitable for mission critical apps. In other words : if you lose data or get hacked, the responsibility is yours.

First, I’ll cover what each piece does, then I’ll cover how to install it.

Step 1 : Setup

Download the code : Database_Backup_Scripts.zip

Background

The Sql Server backups will be done by running a Sql query against the database to do the backups. This is actually what the native Sql Agent in the ‘Full’ Sql Server does anyway, only you don’t have a Guid or pretty Wizard to help you. The rest of the actions are done by batch commands run against either tools or utilities on your server.

How To Install

On your server with Sql Server, create a directory called c:\db\backups and c:\db\scripts. This server must have FTP enabled on it, or be able to copy to a server with FTP enabled (a common scenario with a web server also masquerading as a database server, or web server and sql server in the same DMZ). Create a directory which has permissions for an FTP user to read from : C:\inetpub\ftp.

On your server used to store the backups, create a directory called d:\db\ (note, this can be c:\db, but I’ve made it d:\db to make it easy to tell apart)

Note down, and test the FTP user/password that you will use to copy the database backups from the server.

Extract the contents of the zip file somewhere where you can easily obtain them.

Step 2 : Running a database backup

Background

All Sql Server databases can be backed up by running a Sql script, which calls the in-built stored procedures to backup the files.

I also created a specific user called ‘dbbackup’ on the server to run the backups. With this user, I went through and granted the ‘db_backupoperator’ role membership through the ‘User Mapping’ screen. This can’t be scripted because it depends on the individual databases.

I found a neat little stored procedure script from this page : Using T-Sql to Backup and restore Sql Server databases

I added some details to this script, which includes the creation of a specific user to run the scripts.

How to Install

First, either create your database backup user on your Sql Server, or select a user and grant them the ‘db_backupoperator’ role on each database you’d like to backup.

Open up a Sql query window in your target server, and select the ‘master’ database. Open the ‘Create_Backup_Proc.sql’ file from the zip package. If you have a different user, change the ‘dbbackup’ user permission to your chosen user (use find/replace).

Execute the script, which will create the stored procedure. Test out the procedure to see if it works, by running this script in a Query window:
exec sp_backup_databases 'c:\db\backups'

Note that this backs up to the aforementioned c:\db\backups location. I prefer a simple backup location, rather than the convoluted default that Sql server provides. The backup location for a Sql Server database must always be on the local server. You can’t back up to a remote machine.

Check your c:\db\backups location : you should have a databasename.bak file for each database in your list. If not, troubleshoot this until your script is working.

Step 3: Setting up your backup, database copy and zip into a batch file

Background

As the process is designed to use FTP to transfer the backup(s) to another computer, the smaller the file, the better. One of my database backups runs to over 400 mb, and this would take too long and use up too much bandwidth to be useful. Therefore, you need to compress the backups into a single file for easy transfer.

For this, I’m using the PkZip command line utility. Now, this is an old shareware application that dates back over 10 years, and I already had a copy. You’ll have to source your own (I can’t distribute it) and make sure that you comply with the licensing terms. If it helps, the install for mine is called ‘pkzc400s.msi’. You could also use any other command-line compression utility if you have one.

Once the backup zip file is created, it will need the FTP user read permissions in order to be downloaded. This is achieved by running the cacls utility to grant the required permission.

All of the steps are run through a batch file, which runs the Sql to do the backups, runs the zip utility to put the backups into one zip file, then sets the permissions on the file and copies it to the FTP folder.

How to Install

Copy the ‘Run_Backups.cmd’ file to the C:\db\script location on your database server. Open the file in notepad and set the server name (replace dbserver), username (replace dbbackup if different user) and password (replace password) .

If you have a different command line zip utility, change the command for the Pkzipc call. It should still create a zip file called c:\db\backups\db_backups.zip.

Update the FTP folder location to where your FTP logon can read the file.

Update the FTP user in the cacls command (search for ftp_user). You may need to prefix the server name or domain name if applicable.

Set up a scheduled task to run the batch file on a daily basis. You can use this command line, or go through the scheduled task application on your server.

schtasks /create /sc Daily /st 00:00:00 /tn DbBackups /tr "c:\db\scripts\run_backups.cmd"

Note the above command line creates a daily task which runs at midnight, and runs the run_backups.cmd file.

Test out your backup by brining up a command line window can calling : c:\db\scripts\run_backups.cmd. Check the .txt log files created to see if all steps work correctly, and do an inspection on the created zip file to make sure it contains the database backups. Try using an FTP program to download the file to make sure you have both the location and permission right for the FTP step.

Step 4 : Setting up your backup server

Background

In my case, I used an internet connected machine which has an external Terabyte disk on it. I use this for backups of system drives, data, email archives, and for backups of live websites. This machine is used to contact the database server and download the backups via FTP.

Again, this is achieved through a scheduled task which runs the individual tasks, including copying the file via FTP, and cleaning up the directory of backups older than a certain date.

How to Install

Copy the contents of the Backup_server_Scripts to your d:\db\ directory. First, open up the ‘Archive_Backups.cmd’ file and change the Z:\backup\database directory. This is the local path where you want to archive your database backups. This might be an external drive, a Network Attached Storage device, or anywhere with lots of disk space and low risk of it getting deleted or lost.

Also, review how long you would like to keep the file for. The last task in the batch file runs the delfiles.vbs script, which deletes any files older than a specified number of days. By default it is set to 10 days, to allow for ‘oops’ moments. You can change the value to whatever number of days you like. This should be calculated as a function of disk space, backup size, and likely time period before you realise a mistake has been made.

Open the Ftp_Backups.txt file. This contains a series of batched commands to provide to the native Windows FTP program. The edits are (line by line)

ftp.yoursite.com –> replace with your ftp server address

ftp_user –> replace with your ftp username

password –> replace with your password for the ftp user

cd ftp –> replace ftp with the directory where your file gets copied to. If it’s in the root ftp directory, delete this line altogether. If it’s more than one level down, adjust as necessary.

I got a lot of help from this resource: Ftp Script Examples

Save the changes to both files, and then test out the process by running the file in a command line window by typing in ‘d:\db\archive_backups.cmd’. You should see the results of this run, and your database backups copied to your archiving location. For an extra test, copy an old file into that location and see if the cleanup script deletes it. Note, that your windows firewall (or other firewall) may block the FTP port – you may have to adjust this. Check the log files created to ascertain if this is the case.

You will note that the archive task does not overwrite the same file every day, but rather copies it with the date and time, like this : 2010-01-22-Fri_db_Backups.zip. I use the YYYY-MM-DD format because it is easy to order the files by filename and have them in chronological order. I got help with the batch file date renaming from this article on creating a Batch file to append date to file name. Note that this code may be dependent on local date/time format settings- you might have to modify it to get the result you want.

Lastly, setup a scheduled task on your workstation to run the archive_backups.cmd file. This can be done with:

schtasks /create /sc Daily /st 01:00:00 /tn DbBackupsArchive /tr "d:\db\archive_backups.cmd"

Note that this runs 1 hour later than the database, backup, to leave ample time for the first backup to run and finish.

Modifying for your own purposes.

There are many ways you can expand this basic setup. The first, most obvious one is to save bandwidth by only running the ‘archive’ run once a week, so you only get the latest backup. This gives you a two-step backup – if you just have a local deletion/corruption of the database, then you’ve always got the last night’s backup on the server (in the zip file) to restore from. If you have a total server loss, the most you’re going to lose is 7 days worth of data. You can adjust this as you like, for example, running every second day. It’s a tradeoff between potential data loss and bandwidth, and depends on the individual site involved. For many sites, the bandwidth will be small and it’s worth it to do every day. If you have a relatively static site, consider your needs there. If you have an active site with forum posts and user updates happening every day, you may need to do a daily/half daily backup.

Note that you can also combine other user data into this process – it doesn’t just have to be database files (though the database is most important for a CMS application like DotNetNuke). If you have other user-created files (uploaded images, etc) you could combine the zip process in a similar way.

Results?

Please let me know via the comments if you find faults with the attached code, or see glaring flaws, or have suggestions on making the process better. It’s my intention for this to provide a starting point for people putting together their own customised backup process and getting a better understanding of the tools available to achieve this.

Thoughts on Debugging when you have no debugger

bchapman@ifinity.com.au — Tue, 12 Jan 2010 03:24:14 GMT

This is a philosophical post targeted at anyone who messes about with code, and from time to time comes across intractable problems that they just can’t get around.

When I first started writing code for a living (as opposed to messing about) I had a mentor who got placed into the role by simple fact that she sat at the desk behind me in my very first job. As the newbie on the team, I was pretty much less than useless, because not only could I not produce anything worthwhile, I actively took up peoples time trying to figure out why I couldn’t produce anything worthwhile. I knew this for a fact when I was assigned to ‘tidy up the team bookshelf’ – then a mess of programming and system manuals in those natty clip-open binders, so you could plug in the latest updates. Remember those?

Anyway, my self-appointed mentor (shamefully I can’t remember her name, though I’d run with Christina) basically got to the point where she told me “you can solve the problem, just keep tracing it until it presents itself”. I’m paraphrasing here, I’m pretty sure it was along the lines of “go away, you annoying person, and figure out your own problems. Why don’t you try using the debugger?”. However, in those days, point-and-click debuggers with nice little yellow highlighters and instant variable values were features not available to me. It was code, complile, and check to see if you got it right. That, and piling in lots of little logging statements to spit out what the program was doing. Eventually this paid off, because my manager figured she’d put me onto an esoteric bug which had so far eluded all the actual developers on the team. I had nothing else to do, so I mercilessly tracked this bug until my desk was covered in dot-matrix printouts of programs with little annotations on the side, flow charts – probably even an ER diagram or two. The one day, I actually found the bug and worked out a fix. It was a revelation. I added value to the team. A manager came by to thank me. I walked out of the building and thought ‘yeah, I’m a programmer now’.

Quite aside from my reminiscing of times long gone by, this early lesson taught me something that has stuck with me the whole time. No matter how frustrating the bug, no matter how elusive, all bugs can be tracked, found and fixed. It’s just a matter of persistence, tools and lots of thinking. My first bug was solved not by my genius, but because I just kept plugging away at it. Sadly, not all of us are freshly-minted graduates with days of spare time to fill, so sometimes bugs go into the ‘I’ll never sort that out’ pile. But you can’t pretend they are solved even if the official status is ‘unable to reproduce’ – eventually that bug is going to re-appear somewhere along the line.

Tracking Down a PayPal IPN Bug

If you read my prior post on this blog, you’ll know I’ve been doing some work with PayPal IPN listeners. An IPN listener waits for PayPal to send a Http Post to a Url on your site, called an ‘Instant Payment Notification’. Your listener must respond to this post, and tell PayPal that it received it OK. You must send the entire contents of the post back to PayPal, so that PayPal knows it sent the post (and not some nefarious scammer). If you respond correctly, you get a ‘VERIFIED’ response.

If you’re writing your IPN listener, in C#, there’s a handy code sample to use at https://cms.paypal.com/cms_content/US/en_US/files/developer/IPN_ASP_NET_C.txt

How this relates to this debugging themed post? I took the PayPal example, implemented it ; all working OK. I then added bits and pieces to the sample code, refactored it for my own purposes, generally started to build my listener. That’s when I hit the date problem in the previous post. The problem with debugging programs like this, is that when you’re developing an IPN listener, you’ve got to have a valid Url on the internet for PayPal to send the IPN to. You can’t host it on ‘localhost’, because the PayPal server can’t send the IPN to that. You have to host it on the internet somewhere. And that means no handy debugger. No stepping through code, no breakpoints, none of that. Back to where I started ; code listings, in-line logging and some head scratching and figuring things out.*

But the date problem isn’t what prompted this post : after I figured out the difference between the Sandbox and Live dates, I uploaded my new listener to the server, and consistently got responses of ‘INVALID’. This means that PayPal didn’t like the response I was sending back to them to verify what they sent. But the code, as much as I could tell, was much like their sample. Here’s where I would like to find a handy intern or graduate, assign them the task of tracking down why it isn’t working, and wait for a ‘huzzah’ to come from their cubicle. But there certainly is no graduate or intern around here. Equally, there was no possibility of endless days of debugging, with so many other more important things to do.

This is where a principle I now subscribe to frequently comes in : scrap it and start again. Whenever debugging something that doesn’t seem to work, it often takes less time to start again, and incrementally add the changes back, this time carefully testing each iteration.

So while I was paddling around in the Pacific Ocean this morning (marvelling the fact that mid-summer ocean temperatures in my part of the world are a balmy 25 degrees Celsius) - why keep trying to figure out where the problem is, when it would just take 20 minutes to build a whole new version. I would build a new one based on the PayPal example, and then incrementally add the other changes back in and check that it was working between each iteration. That’s just what I did, and it worked perfectly. I’ve now junked the old code. I don’t know what was wrong with it, and I never will. Total time to solve the problem was less than 30 minutes.

The moral of this story are the two point I’ve made here:

1) Never assume a bug is a one-off. There are very few one-off bugs, and chances are your bug isn’t one of those. It will re-appear.

2) All bugs can be tracked down with the right tools, approach and persistence

3) It’s usually quicker to take a different approach and start again, rather than try and work out where your code is flawed

4) Blog posts are a great way to procrastinate against further bug fixing.

That last statement is there because I realise this whole post is probably preaching to the converted, but I’m hoping someday one person will read it, find the energy to tackle an unsolved bug, and solve it with a new approach.

*I know you can do remote debugging of servers. You also have to open security holes everywhere on your server in order to make it happen. You just about have to sign a disclaimer in binary before Microsoft will let you do that. I figured the server would be full of bots and viruses faster than I could possibly fix it, and the performance would be horrible.

Keeping Querystring values out of the Friendly Url Path in DotNetNuke with Url Master

bchapman@ifinity.com.au — Fri, 08 Jan 2010 01:03:00 GMT

This blog post is going to cover a pretty small subset of problems that people have with auto-generated Urls using Friendly Urls in DotNetNuke. All Friendly Url solutions in DNN, regardless of what module used to generate them, approach the problem of making Urls better by converting some of the querystring values into the actual Url path.

Explained simply, a url which looks like this :

/default.aspx?tabid=64&key=value&text=description

will be converted to a url which looks like this (assume tabid 64 is ‘DNNPage’)

/Tabid/64/DNNPage/key/value/text/description/default.aspx or /DNNPage/key/value/text/description.aspx

The differences between the DNN Friendly Url Provider and the Url Master module is that the DNN provider will keep the tabid and the /default.aspx on the end, whereas the Url Master disposes of these parts of the Url. However, the main similarity remains : the querystring items are converted to be part of the Url path.

In most cases this is advantageous, it provides a simpler, cleaner Url that is easier for humans to understand, and search engines have no trouble parsing. It has to be said, though, that the previous problems with search engines not being able to index querystrings are a thing of the past – so the original reasons for doing this have moved on.

Keeping Querystring Items in the Querystring

However, in some cases, the querystring needs to stay on the querystring. This is a common occurrence with the inclusion of tracking parameters, such as Google Analytics campaign tracking parameters (&utm=xyz) and other affiliate and advertising linked tracking. The other problem is that analytics packages like Google Analytics and others ignore the querystring, but separate out Urls for tracking purposes by path.

To illustrate this problem, let’s assume I have setup an advertising campaign, and I want to track the effectiveness of it in Google Analytics. To do so, I will tag the Url that I am going to send to an email list with special terms in the querystring. The Url will look like this:

http://mysite.com/Buy-this-thing.aspx?utm_medium=email&utm_source=subscribers

When users click on this link in an email I send out to them, the ‘email’ and ‘subscribers’ tracking values will be traceable within Google Analytics, and I’ll be able to tell how successful this emailing campaign was.

However, if I’m running Url Master on my site, there’s a chance that the Url could end up being:

http://mysite.com/Buy-this-thing/utm_medium/email/utm_source/subscribers.aspx

Now, normally if the Url is valid, the Url Master module won’t convert the requested querystring into a friendly url path, but if there is a redirect triggered then the undesired Url can be generated. The redirect reason could be : lower case change, page relocated, incorrect subdomain, explicit redirect, etc – most redirects would trigger this behaviour. While the new Url will work just as well as before, an Analytics package will treat this new url as a completely different Url from the ‘original’ Url. And that means the tracking data will be messed up and split across different Urls. This is because the tracking data works off the actual Url entered in the browser – it’s a client-side (javascript) technology, so can’t see the server-side (rewritten) Url, which is what the DNN platform uses.

New Regex Value for Maintaining Querystrings

The solution for this problem has been introduced into the Url Master module from version 1.15 onwards. This takes the form of a regex value to match any items in the querystring that should not be removed from the querystring. Note that this works for any Url, anywhere in DNN, and not just for the narrow problem I have described.

This new Regex field appears in the ‘Advanced Regex Settings’ section of the ‘Friendly Url Settings’ page. When a Regex value is entered into this string, it compares the components of a querystring that is to be converted into a Friendly Url path, and if a match is found, that portion of the match is excluded from the Friendly Url Path, and is left as a querystring. This flexibility allows picking and choosing which parts of a querystring to exclude, and which parts to leave in.

Now, Regex isn’t the easiest of things to wrap ones head around without some prior exposure and practice. But fortunately the problem is pretty rare and usually relates to key/value pairs in the querystring used for analytics tracking. So it shouldn’t take too long before a good catalogue of solutions is published on common problems.

For the example above, to keep the ‘utm=medium&utm-source=subscribers’ in the querystring, I’ll need to create a Regex pattern that will match the full querystring. It’s tempting to think you can just enter ‘utm’ and leave it at that – however, this would leave the ‘utm’ parts in the querystring, and leave behind the rest of it, resulting in a Url that looked like this:

_medium/email/_source/subscribers.aspx?utm&utm

That’s even worse than what we started with, because the Url would no longer work at all. You’ll note that the ‘utm’ match has been left in the querystring, and everything else has been shuffled into the path.

Instead, what you need to do is expand the match to locate and find all parts to keep in the querystring.

Here’s a version that works as we want:

/utm_[^/]+/[^/]+

The difference is that this pattern expands to match any querystring parameter starting with utm_, and matches the path of it as well.

There’s a couple of things to note here:

1. Because it’s a non specific match, it repeats and captures both the utm_medium and utm_source values. You don’t have to explicitly match each if you can do a partial match (utm_) that works.

2. The match is actually done on the friendly url path – not the original querystring. In other words, the key/value pairs are split with path separators (/) rather than the & = of a Url querystring. You need to write a regex that matches what the incorrect friendly url path is, not what the original querystring is.

If you’re interested in how the regex match works, it’s pretty simple. The first part matches a part of a path that starts with /_utm – which is what we want. The next part matches whatever comes between _utm and the next / character. This is done by repeating a character match, as long as that character isn’t a /. This is the [^/]+ part of the match. It means – match at least one character (+) as long as it’s not the / [^/] – the ^ is a ‘not’ character match. The next part of the expression says match the next part of the path, up to the next ‘/’ character. This is the /[^/]+ part of the expression – it is the same as the prior part, but just looks for a string of characters of any type, as long as they aren’t ‘/’. By understanding this expression, it should be easy to develop an expression that matches the part of the Url.

Setting up the Expression in Url Master

Simply go to the ‘Friendly Url Settings’ page, and enter the regex expression in the textbox preceeded by the label:

Do not include matching items in friendly url path - force to be in the querystring (doNotIncludeInPathRegex)

You can then test it out on the page (before saving the changes) by going to the ‘Test Friendly Url Settings’ box above the regex fields. Select a page from a site, and, in the ‘Add on the querystring’ section, type in the raw querystring value you would expect to see on the end of the ‘normal’ DNN Url. In the example shown in this post, this would be &utm_medium=email&utm_source=subscribers

Once you have done this, click on the ‘Show Friendly Url Example’ button. If you have set it up correctly, you should see the Url generated below the box. In this Url, you should see that the part of the querystring that you’re trying to match in the correct place in the generated Url.

Once you’re done, click on the ‘Apply Changes’ button, and test the Urls in your browser, ensuring that the Querystring items don’t get transformed into the Friendly Url path.

Converting PayPal Dates to .Net DateTime using Regex

bchapman@ifinity.com.au — Wed, 06 Jan 2010 08:27:40 GMT

I recently had a problem writing a PayPal Instant Payment Notification (IPN) listener. The problem was that the PayPal Sandbox environment (now puzzlingly called x.com) would process my IPN requests OK, but any ‘live’ request that came through my site ended in error.

Now the PayPal support people assured me that this wasn’t possible, but after dumping most of the actual code out of my module, and replacing it with highly-sensitive ‘gotcha’ error capturing code, I worked out the problem was due to a slight difference in the date formats between the Sandbox and Live environments.

The Sandbox date looked like this:

22:40:29 Dec. 16, 2009 PST

The live date looked like this:

13:02:26 Dec 21, 2009 PST

Eagle eyed readers will detect the difference straight away. It was a bit more difficult for me, as I was looking through raw logs and all I could see was 22%3A40%3A29+Dec.+16%2C+2009+PST as the values are encoded. It was a surprise for me, but I guess I should have been more prepared for wide and varied date formats – they’re always the problem child of data handling and validation. At least they have a text month to differentiate from dd/mm and mm/dd formats – they’re always fun.

Converting a PayPal Date to a .NET DateTime value

The format of the PayPal date isn’t one of the ones that you can easily just convert to a .NET DateTime type by doing a DateTime.TryParse call. I looked into writing a customised date parser to supply with the DateTime.Parse routine, but it all just looked too hard for something that was (supposed) to be coming in with a standard format.

What I originally wrote (and which caused all the problems between Sandbox and Live environments) was a date parsing routine that worked out which bit of the date was which, all done by splitting up the string into substrings – one for the time, date and timezone. This worked on the sandbox because it didn’t have the ‘.’ at the end of the month. But move it into the live environment, and it didn’t work at all, because it was out by one character. The resulting error wasn’t handled well (my fault) and so the whole thing mysteriously failed.

A better .NET / PayPal Date Parsing routine

Instead of fiddling around with brittle substring functions, I decided to turn to my old friend Regex, which thrives on this sort of fixed-format string parsing. I quickly wrote this regex expression, which worked almost straight away. The routine splits out the time, date and timezone, and within those individual groups, split out the hours, minutes, days, months and years. Turns out I didn’t need the individual pieces, but by then the expression was written anyway.

Here’s the expression:

(?\d{1,2}:\d{2}:\d{2})\s(?(?[A-Za-z\.]{3,5})\s(?

\d{1,2}),?\s(?\d{4}))\s(?[A-Z]{0,3})

A routine to convert a PayPal date to a .NET DateTime value using Regex

Here’s the full routine in C# to convert a PayPal date into a .NET DateTime value

/// 
/// Converts a PayPal datestring into a valid .net datetime value
/// 
/// a string containing a PayPal date
/// the number of hours from UTC/GMT the local 
/// time is (ie, the timezone where the computer is)
/// Valid DateTime value if successful, DateTime.MinDate if not
private static DateTime ConvertFromPayPalDate(string rawPayPalDate, int localUtcOffset)
{
    /* regex pattern splits paypal date into
     * time : hh:mm:ss
     * date : Mmm dd yyyy
     * timezone : PST/PDT
     */
     const string payPalDateRegex = @"(?\d{1,2}:\d{2}:\d{2})\s(?(?<
Mmm>[A-Za-z\.]{3,5})\s(?\d{1,2}),?\s(?\d{4}))\s(?[A-Z]{0,3})";  
    //!important : above line broken over two lines for formatting - rejoin in code editor
    //example 05:49:56 Oct. 18, 2009 PDT
    //        20:48:22 Dec 25, 2009 PST
    Match dateMatch = Regex.Match(rawPayPalDate, payPalDateRegex, RegexOptions.IgnoreCase);
    DateTime time, date = DateTime.MinValue;
    //check to see if the regex pattern matched the supplied string
    if (dateMatch.Success)
    {
        //extract the relevant parts of the date from regex match groups
        string rawDate = dateMatch.Groups["date"].Value;
        string rawTime = dateMatch.Groups["time"].Value;
        string tz = dateMatch.Groups["tz"].Value;

        //create date and time values
        if (DateTime.TryParse(rawTime, out time) && DateTime.TryParse(rawDate, out date))
        {
            //add the time to the date value to get the datetime value
            date = date.Add(new TimeSpan(time.Hour, time.Minute, time.Second));
            //adjust for the pdt timezone.  Pass 0 to localUtcOffset to get UTC/GMT
            int offset = localUtcOffset + 7; //pdt = utc-7, pst = utc-8
            if (tz == "PDT")//pacific daylight time
                date = date.AddHours(offset);
            else  //pacific standard time
                date = date.AddHours(offset + 1);
        }
    }
    return date;
}

You can use this routine quite simply by extracting a field which contains a PayPal date, and transforming it into a .NET DateTime value, like this:

    //Get the payment date of the request.  Note 0 hours to return UTC time of payment
    DateTime paymentDate = ConvertFromPayPalDate(Request.Form["payment_date"], 0);

Feel free to use the code as-is, if you find any problems let me know via the comments.

Unit Testing DotNetNuke 5 Modules in Visual Studio with a Mock ASP.NET Http Context

bchapman@ifinity.com.au — Thu, 03 Dec 2009 03:29:00 GMT

When I was doing the rounds at the recent DotNetNuke OpenForce conference, I was surprised at the number of people who wanted to talk to me about the DNN Unit Testing framework I put together a few years back. I use this on an almost daily basis, but it’s become part of the plumbing for all projects so I don’t really think about it as much as I used to. I was recently contacted and asked to update it all for DNN 5, and I thought I would spend some time bringing it up to latest standards and generally re-visit the topic again. It would seem there is a lot of interest in doing Test Driven Development with DNN, so this post will revisit all of that.

Should you be doing Test Driven Development with DotNetNuke?

Test Driven Development (TDD) is a worthy goal of any software project that aspires to good quality and the flexibility to change over time. If this is true of your DotNetNuke module, then the answer is yes. All non-trivial iFinity modules have Unit Test projects that are run before releases and after changes to check for breaking changes. This has caught a number of errors over the years, and prevented them from showing up in an actually released software (sadly the unit test coverage is not 100%, hence some bugs do get out, rotten little things!)

What will you need?

All of the information presented in this post covers DotNetNuke 5.1.2 and later, uses Visual Studio 2008 Team Edition and was done on a Windows 7 machine. None of this is really necessary : you don’t have to use the in-built unit testing of Visual Studio (nUnit will do) and you can really use any version of Visual Studio you like, as long you’ve hooked up a unit testing framework. I will suggest, however, that you have loads of fast RAM and a blazing fast processor, or you will soon bore of the constant compiling, running and copying that is part of unit testing. I’m not going to pretend that the process of setting up unit testing with DNN is straightforward, so any amount of frustration you can remove by throwing hardware at the problem will be justified (see, print that bit out and go ask your boss for a new computer).

Note, if you aren’t using the Visual Studio test tools, then you’ll have to do some refactoring to the code to get it to work. If you do manage to, say, get it working with nUnit, let me know via the contents and I can make your work available (or write a blog post somewhere and I’ll reference it).

Contents of the Download

Download the code

Zip file containing example test projects

In my previous posts on this topic, I provided some DLLs and general instructions on setting up some procedures. While this was useful, I decided to go a bit further and actually provide a complete example that you can download and setup, and (hopefully) press the magic ‘go’ button and see some tests running. This is probably a very lofty goal given the territory, but everything should be there.

Solution Contents

When you download the solution, you’ll see the following projects:

Solution items : the testSetupScript.cmd file, which prepares the test output for running a mock DNN environment, the ‘testrunconfig’ file, which references items to be deployed with the test, and the .vsmdi file, which contains a list of unit tests in the solution
iFinity.Basic.UnitTest.Example project. This is the unit test project. It contains a single test class file, as well as the various config files required to make it work with DNN
iFinity.DNN.Utilities : This is the Unit Testing framework I built to work with DNN. It contains the code to create the Mock DNN environment in which to run unit tests
iFinity.ExampleModule, iFInity.ExampleModule.Data : these two projects are pretend module DLLs. This article is all about unit testing Private Assembly modules, and this setup is the standard way to construct a DNN PA module. Note there is no UI code in these projects, just a class, a module controller and a data provider. The idea is just to test the parts of the code that use the DNN framework (namely, reading/writing to the database)

How to use the download

1) Download the code, extract the zip file, open it up in your Visual Studio (note, you’ll need a version which contains the Unit Testing framework, this won’t work in the ‘free’ editions).

2) Update the connection string in the app.config file to point at a DNN database that you have lying about somewhere. If you don’t have a local test DNN database, then create a new instance of DNN just for unit testing purposes. Actually, that’s not a bad idea anyway. You can either use a .mdf file in the ‘user instance’ mode, or connect to a sql server database somewhere. It depends on your objectives with testing and what you’ve got available.

3) Find the 01.00.00.SqlDataProvider file in the ‘iFinity.ExampleModule.Data’ project, under the ‘SqlDataProvider’ directory. Then run this through the ‘host->sql’ page of the DNN install that belongs to your DNN database you’re going to use for testing. If you can’t do that, then run the Sql in a normal sql query editor, but remember to find/replace the ‘{objectQualifier}’ and ‘{databaseOwner}’ tokens first.

4) Start the Unit tests with alt-shift-x, or find and click the ‘run tests’ button in Visual Studio (hint, it’s not the green “play” button that starts debugging)

5) Check the test results to see if you got it to work OK.

If you have any custom modules in your DNN install that need configuration changes, you might need to change the app.config file in your test project to match the web.config file of the associated DNN install. It’s very important to realise that you are running a mock DNN application, and that mock application is using the app.config file of the test project, not the web.config file of the DNN project.

Test Setup

When you hit the magic ‘test it all for me’ button, there’s several things that go on. In the background, the Unit Testing framework is compiling the versions of your components, and copying them to a special directory, created underneath the ‘TestResults’ directory. By default this is in the solution folder. However, this is just a flat directory with a pile of DLL’s in it : not unlike the \bin directory of a standard DNN install. Our aim here is to make the DNN code run in a test context, and to do that, some parts of the DNN framework have to be re-created.

This is achieved by the ‘TestSetupScript.cmd’ file. This file simply creates a couple of bare-bones directories in the OUT directory of the test, to make it look a bit more like a DNN install to the DotNetNuke code that will be running in the test. Note that the portal is hard-coded in this script file, to match the entries in the ‘dnnUnitTest’ section. This file needs to be hooked up to the ‘TestRunConfig’ file in the solution containing the Test project. This tells the Test framework to run the file before testing starts.

If you don’t set up this file, the code in the DNNUnitTest class coughs up a helpful error message, which will look something like this:

Unable to create instance of class iFinity.DNN.Modules.ExampleModule.Test.ExampleRecordInfoDBTest. Error: System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: Supplied HostMapPath [..\iFinity.UnitTesting.Examples\TestResults\Bruce_WOMBAT 2009-12-02 14_35_39\Out\Website\portals\0\] is not valid. Specify a valid path on the target machine (Did you forget to run the testSetupScript.cmd file to create the portals directory??)

This message is supposed to be a slap to the forehead “Hey, wake up and include the TestSetupScript.cmd file!”

There are other files that DotNetNuke needs to run, as well. These include the DotNetNuke.config file, and the SiteUrls.config file. Both of these files will be searched for by the DNN framework as it is loaded. So, these two files are also specified as required items in the .testrunconfig file, in the ‘Deployment’ section. This ensures that they will be copied to the OUTPUT directory before testing starts.

A note on project referencing and Test setup

You’ll see that the UnitTest project contains a reference to the ‘Data’ project : namely the iFinity.ExampleModule.Data project (and assembly). Strictly speaking the Unit Test project shouldn’t have a reference to this project, the database calls should go through the ModuleController class. However, referencing the project gives two shortcuts. The first is that when a module is referenced by the test project, the compiled assembly is automatically copied to the OUT directory for testing. The second is that it allows us to run cleanup database code directly against the database by utilising the connectionString reference that the SqlDataProvider object contains. If this all makes you feel uncomfortable in a ‘impure architecture’ way, feel free to concoct your own solution. But I’m here to tell you that getting the assembly manually copied is inelegant as well, and opening the app.config file to look for the connection string is ‘wordy’ as well.

How it Works

This framework works by creating a Mock (or fake) ASP.NET runtime context to run your unit tests within. Tests normally run in the Visual Studio Test context – and if you do this, you can’t access server objects like HttpContext : which are vital for a web-based application like DotNetNuke.

This fits together because the TestClass inherits from the DNNUnitTest class contained within the ‘iFinity.DNN.Utilities’ project. When the TestClass is instantiated to run the tests contained within, the constructor code in the DNNUnitTest class kicks off the type of initialisation normally run within the DNN startup code. It also constructs a mock HttpContext object, and stores some of the key DNN global objects (like PortalSettings and HostSettings) in the HttpContext items. It also loads some of the Providers used by DNN for key tasks such as database access, data caching and others.

Thus when your code runs and makes calls to items within the DotNetNuke namespace, the actual objects are there to take the calls and return the results. This allows you to create unit tests to run pieces of code within your module that would otherwise fail because there was no DotNetNuke object there to receive and process them.

Issues to Understand

Windows Vista/7/2008 Security

If you’re like me and have upgraded to the latest Windows offerings, you’ll soon run aground on the fact that Unit Testing is considered a highly risky business by the born-again security converts on the Windows team. You may get an error like this one:

Test Run deployment issue: The location of the file or directory 'D:\DotNetNuke\Custom\Common\iFinity.UnitTesting.Examples\dnn510\DotNetNuke.dll' is not trusted.

This is because the DLL’s weren’t compiled on the machine you’re testing on, so you’re guilty of running code that someone else wrote.

The generally accepted solution is to ‘unblock’ the file(s) that cause the problem. You should do this in the location they are referenced from rather than the location noted in the error message. This is because when you debug and test, the new copies will be taken from the reference location and copied into the destination location. To unblock the file, right click on the offending file, select ‘Properties’ and go to the ‘General’ tab. At the bottom you’ll see a message looking something like this:

Click ‘unblock’ at the bottom, and you should be OK. You might have to restart Visual Studio to fix this problem, so I’d suggest unblocking all of the referenced files at once, then doing a restart of VS – just to be sure. A big thanks to whomever at Microsoft decided this was the way to go.

If that doesn’t solve the problem for you, take a look at this StackOverflow topic, and see if you can glean some help. You might also try recompiling the DNN solution on your local computer, and using the output from that. This also has the benefit of giving up some up-to-date .pdb files to help with any debugging.

Changes for DNN 5 : the ‘Cannot register or retrieve components until ComponentFactory.Container is set’ error

As previously mentioned, this blog post came about as a result of people asking me about a DNN 5 compatible version of this code. The big change comes about through the use of the ‘ComponentFactory’ component used to handle the loading of providers and components in DNN 5. This completely changes the way that 4.x DNN used to load components on startup.

I was helped immensely by Charles Nurse, who has an excellent series on creating testable modules on his blog : http://www.charlesnurse.com/post/Creating-Testable-Modules-Part-1.aspx

Interestingly enough, he mainly advocates a completely different approach than what I do (in terms of the mock objects and environment, not the methodology) which is definitely worth reading and understanding. One of the largest differences is that he is discussing the MVP (Model View Presenter) pattern, whereas this article discusses more the traditional pattern of DNN module development.

However, the key item that I took away from this blog series was the changes in the way that DNN 5 loads up providers and other components. This is actually all done in the ‘global.asax.vb’ file (which runs at application start). This is one of the reasons many people had DNN 5 upgrade problems (and encountered the ‘componentFactory.Container’ error) – some modules and developers replace the global.asax.vb file with one of their own making – or fail to copy in the new one. This removes the important instantiation of the Component Factory Container, and results in lots of bad errors all over the place. Don’t modify the core code!

The end result for the iFinity.DNN.Utilities is that the startup code had to change. This was done by copying the relevant startup code from the global.asax.vb file, which creates the container and loads the components for the various types of providers supported in DNN. However, not all the various components are loaded : some are commented out in the ‘InstallComponents’ list. These can be re-installed, but you’ll also need to reference the matching DLL from the DotNetNuke \bin directory. For example, if you enable the ‘friendlyUrl’ provider, you’ll need to copy in and reference the matching Friendly Url Provider, as configured in your app.config ‘friendlyUrl’ section.

However, as an upshot of this, the previous problems I had encountered with the BuildManager.GetType() calls in DNN 4.8.0 have disappeared. So this code has been removed from the DNNUnitTest class, and replaced with the aforementioned DNN 5 method.

Visual Studio 2008 Web Context / Host Adapter

Within Visual Studio 2008 Unit Testing, there is a new feature for running Unit Tests in the context of an ASP.NET website. This solution doesn’t take advantage of this : primarily because it’s an evolution of my work done in Visual Studio 2005, which didn’t have this feature in the Unit Testing. Instead, this creates a mock HttpContext to host the DNN application. The end result is similar, although I suspect the Visual Studio version would probably be superior. If time and inclination permits, I may investigate this in the future and modify the DNNUnitTest code to match.

Recommendations for Testing Module Code

Hopefully you have managed to setup the example test project and have got the code running. You can step through the individual tests and investigate how it all works. About now is the time you should start thinking about how to adapt this to your projects. If you develop your modules in a similar way, then you can just remove the ‘iFinity.Example’ module, and replace with your own project. Or, more realistically, either include the iFinity.DNN.Utilities module in with your existing project solution, or just build and reference the DLL once you have it working. You can then add a test project to your solution, copy across the various components of the example test, and start writing your tests.

In the example module tests, you’ll see there is a test for each property set/get. You may think this is overkill, but you’d be surprised how many times this will catch a small but lethal bug from a mistyped property accessor. Also, in this example module, you’ll see that a flag is kept over whether the data in the object has changed since the last check. The logic determines if this code is running correctly.

You’ll also see that the module CRUD code is tested against the database. This is an obvious choice, but don’t forget all the other bits and pieces your module may do, such as reviewing saving/retrieving module settings, generating Urls, creating emails : you should test as much as you can.

Include jQuery in a DotNetNuke Module with version independent code

bchapman@ifinity.com.au — Sun, 25 Oct 2009 05:33:32 GMT

Count me amongst the legions of jQuery fans. I might be a bit later to the party than others, but to my defence I really like writing server based code, so I do as much of that as possible. It’s only when faced with the need to ship code that the UI starts getting the attention it deserves.

Adding jQuery into DNN is an obvious choice, although made somewhat problematic with the standard Prototype library inclusion, and the fact that DNN modules are built with ASP User Controls rather than pages. You’ll see plenty of advice on the internet to either use the built-in DNN 5 jQuery inclusion method, or to just add the includes into the page where your jQuery code is. The first is problematic if you’re building for a broad range of DNN versions, the second is problematic if you want to distribute your code without having a long list of post-install steps to get it working. Another issue is that DNN modules can (and will) be copied to the page more than once, so your code has to be careful of including some sort of duplication check, so that you don't end up with 5 references to the jQuery library next time someone gets a little click-happy with the 'Add Module' button.

With that background, I set about developing a small routine which can be used with any DNN Version, which would provide version-independent, one-line inclusion of the jQuery libaries.

Programmatically Including JavaScript and CSS files in ASP.NET

The first thing to solve in this task is a simple inclusion of javascript includes in the header of the page. This is one of those things that you would think is really easy in ASP.NET, but unfortunately it can be a bit of a trick for new players. Most people start looking around the ClientScriptManager object, but in my opinion it's overblown and messy. You'll often see people using the ClientScriptManager to do the duplication check by adding in an empty bit of javascript - a brutal way to save effort. The easiest (and cleanest) way to do this is to create a specific html tag, and then actually add that to the header file, like this:

      HtmlGenericControl scriptInclude = (HtmlGenericControl)page.Header.FindControl(id);
      if (scriptInclude == null)
      {
           scriptInclude = new HtmlGenericControl("script");
           scriptInclude.Attributes.Add("src", src);
           scriptInclude.Attributes.Add("type", "text/javascript");
           scriptInclude.ID = id;
           page.Header.Controls.Add(scriptInclude);
      }

This code looks for a specific html script include tag in the page header, and if not found, creates the tag and adds it in. Couldn't be simpler really - and no messy, empty