OpenDBDiff – OS tool for SQL Server Schema Comparisons
I can’t think of the last project I was involved in where I didn’t have to do schema comparisons between differing databases to generate diff scripts. Sometimes I have a local copy of the DB on my laptop that I need to synch with a dev database. Other times I have to create the scripts that we will use to deploy schema changes between dev and staging. Sometimes I just want to see if there are any differences between my local DB and another to see why some tests aren’t passing. Regardless, I find myself consistently managing this task..
There are a variety of tools available to help with this task: RedGate and Data Dude to name a couple. Recently a couple of colleagues put me on to OpenDBDiff. I say a couple of colleagues because the lot I work with tell me about “helpful” OS tools and projects almost daily. When I hear 2 or more folks use the same tool, my antennas go up.
OpenDbDiff is an open source database schema comparison tool for SQL Server 2005 / 2008. In my opinion, the beauty of OpenDBDiff is the simplicity. Without looking at ANY documentation, you can do a full schema comparison between 2 databases, choose the object types you want to script and generate schema comparisons. Let’s have a look:
Getting Started
Here are the simple steps to get started with OpenDBDiff:
- Download OpenDBDiffl Simply go to the codeplex site and click download. You will download a zip file.
- Unzip the contents into a folder in your Tools directory (or wherever you keep your tools).
- Run DbDiff.exe. It is that easy…
The Demo
I have both SQL Server and SQL Server Express running on my box and this will do for our simple demo. On my SQL Server instance, I have a database called HelpDeskDB that I used in my Entity Framework Training Series. The first step is for me to is to create an empty HelpDeskDB on my express instance and spin up DBDiff. I’ll use it to copy the schema to my new database. Here is a screenshot:
The Options gives you typical SQL Compare options. Here is a view of the filter options:
Once you press “Compare”, you can see a summary of the differences in the left hand list box:
Or you can have a look at the sync script:
In my case, I am going to copy the script to the clipboard and run it on my target database to sync them.
Syncing Changes
With that done, I now have 2 databases with the same schema. Next, I want to show you an example of syncing changes between 2 databases. The first thing I will do is make a schema change to my new database. I am going to add a non-nullable** column to my Contacts table. Here is the change:
Now, I will reverse the databases in OpenDBDiff, setting my “new” database (with the updated schema) on SqlExpress as the source and my original database as the destination and press “Compare”. Here is the SQL Script that was generated (omitting the dropping and creating constraints for brevity sake):
As you can see, the SQL generated does the following:
- (not shown here) Drop the Constraints
- Creates a temp table with the new, non-nullable column
- Inserts the data from the original table into our new table (it is even smart enough to default the datetime with GetDate())
- Drop the original table
- Rename the new table
- (not shown here) Create the Constraints
Conclusion
As you can likely see, OpenDBDiff is a handy tool for your toolbox. It is open source, easy to use and does a great job at schema comparisons.
**Note: you might not want to introduce a new column as non-nullable in one release. This is a topic for a future post.
Do You NCrunch Yet?
If you haven’t heard of NCrunch, you are in for a great surprise. Over the past few years, a friend and colleague of mine, Remco Mulder, has been slaving away on NCrunch. What is NCrunch? In my opinion, it is the most powerful Visual Studio plugin available. It is designed to help developers with TDD and automated testing in general. The plug-in runs automated tests in the background while a developer writes code, and shows code coverage inline in real time as this code is written.
You may be wondering if you read that correctly. Does NCrunch run your tests while you are writing code? Yes it does. NCrunch sandboxes the entire process of building, executing and testing a transient snapshot of a .NET solution – without a developer even needing to save their code to the hard disk. Feedback and code coverage from the tests is presented tidily and unobtrusively alongside the source code with any thrown exceptions or test failures shown inline. Let’s have a look at a snapshot of one of my projects in VS 2010 (with NCrunch).
Notice the green dots on the left hand side. They tell me that the line of code next to it is covered by at least one test and all of the tests covering that line are passing. If it wasn’t covered, the dot would be black. Let’s see what it would look like for a failing test. I am going to change the last line in IncrementIndex() to decrement the index by one instead of increment it. Have a look:
As you can see, NCrunch is now letting me know that all the lines of code with red dots are covered by failing tests. By the way, all I did was to replace the “+” with a “-“. I did not compile, or even save my change.
I have some options now. If I click on a red dot, the following context menu shows up:
From here, I can see the tests that are covering the line. I can navigate to the specific tests by simply clicking on it in this menu.
If I right click on a red dot, the following menu shows up for me:
As you can see, I can run the tests that are covering the code (if I don’t believe NCrunch), I can choose to ignore any failing tests (shame on me) or I can pin them to the tests window.
Why use NCrunch?
Some of you, like me, practice TDD. Others may not. However, all of you should be writing automated tests! NCrunch helps both groups. Before I started using NCrunch, I never realized how disruptive running my unit tests was. It was just a way of life. Write a test… Stop… Run my test… Stop… Write Code to make test pass… Stop… Run my test… Refactor… Repeat… Now, I almost never run my unit tests manually. They are always running behind the scenes. I don’t need to stop developing and do a mental context shift. I’m guessing that this saves me at least an hour of productivity a day.
The other beauty of NCrunch is the real-time feedback you get. First of all, it is very apparent where you have holes in your test coverage. Secondly, you know immediately if the code you are writing broke any tests. The nice thing is that you get this feedback when you are most prepared to address the issue. The code is fresh in your mind (you just wrote it)
Where do you get it and how much?
You can download NCrunch at www.ncrunch.net. Right now, it is in a beta state and Remco has decided to make it freely available in exchange for feedback from the .NET community. Good luck and get NCrunching.
Using the Builder Pattern in tests
One of the most painful parts of writing tests is creating test data. Whether you use mocking frameworks or write your own fakes, one thing is constant… you will find yourself creating object instances over and over. You have to create inputs to the methods you are testing, return values for stubs or mocks, data in fake repositories, etc. The Builder Pattern can greatly ease this burden. In this post, through example, I will show how.
The Scenario
The following is a simplified version of Coupon and Order types from DevEducate.com:
public class Coupon
{
public int Id { get; private set; }
public string CouponCode { get; set; }
public decimal Discount { get; set; }
public DateTime ExpirationDate { get; set; }
public string Description { get; set; }
public int ExecuteMaxTimes { get; set; }
public decimal MinimumOrderAmt { get; set; }
public CouponType CouponType { get; set; }
…
}
public enum CouponType
{
PercentOffOrder = 1,
AmountOffOrder = 2
}
public interface IOrder
{
decimal SubTotal { get; }
}
public class Order : IOrder
{
public decimal SubTotal
{
get
{
…
}
}
…
}
In this simplified scenario, we have a Coupon that can represent a percent off an order (25% off orders of $200 or more) or a fixed amount off an order ($25 off orders $100 or more). Notice that we can use the MinimumOrderAmt property to ensure the order is of a certain value. We can also use ExecuteMaxTimes to limit the number of people that can cash in on the coupon and we can set an ExpirationDate to limit the lengh of time the coupon is executable.
Now, suppose we want to start implementing the CalculateDiscount method for our Coupon type. One test may look something like this:
[Test]
public void CalculateDiscount_TenPercentOffOrderOfTwoHundred_ReturnsTwenty()
{
//Arrange
var coupon = new Coupon()
{
CouponCode = "TestCode",
CouponType = CouponType.PercentOffOrder,
Discount = .10m,
ExecuteMaxTimes = 100,
ExpirationDate = DateTime.Today.AddYears(1),
MinimumOrderAmt = 0
};
var order = MockRepository.GenerateStub<IOrder>();
order.Stub(x => x.SubTotal).Return(200m);
//Act
var result = coupon.CalculcateDiscount(order);
//Assert
Assert.AreEqual(20, result);
}
The object under test here is obviously the Coupon, specifically the CalculateDiscount method. The first thing I do here is to create an instance of our Coupon. That is quite a bit of code just to create an instance of our Coupon. You might argue that I have set some properties here that are unnecessary for the test (CouponCode, for example) just to make the example look worse. However, it is likely that I would have to set most of those properties.
What if instead of all of that code I could have done the following:
//Arrange
var coupon = new CouponBuilder()
.WithCouponType(CouponType.PercentOffOrder)
.WithDiscount(.10m)
.Build();
Ore even:
//Arrange
var coupon = new CouponBuilder().AsValid().WithPercentageDiscount(.10m).Build();
The Builder Pattern
The Builder Pattern is a construction pattern. It’s purpose is to abstract away the construction of (complex) objects and enable you to easily construct / build an object in a step-by-step process. And, if done just so, like above, it can yield a nice, fluent interface (notice how I simply chain methods together). So let’s get started implementing our CouponBuilder:
A Simple Builder
public class CouponBuilder
{
private readonly Coupon _coupon;
public CouponBuilder()
{
_coupon = new Coupon();
}
public Coupon Build()
{
return _coupon;
}
public CouponBuilder WithCouponCode(string couponCode)
{
_coupon.CouponCode = couponCode;
return this;
}
public CouponBuilder WithDiscount(decimal discount)
{
_coupon.Discount = discount;
return this;
}
public CouponBuilder WithExpirationDate(DateTime expirationDate)
{
_coupon.ExpirationDate = expirationDate;
return this;
}
public CouponBuilder WithDescription(string description)
{
_coupon.Description = description;
return this;
}
public CouponBuilder WithExecuteMaxTimes(int executeMaxTimes)
{
_coupon.ExecuteMaxTimes = executeMaxTimes;
return this;
}
public CouponBuilder WithMinOrderAmount(decimal minOrderAmount)
{
_coupon.MinimumOrderAmt = minOrderAmount;
return this;
}
public CouponBuilder WithCouponType(CouponType couponType)
{
_coupon.CouponType = couponType;
return this;
}
}
In it’s simplest form, it is that simple. Here is the breakdown:
- You start by creating a readonly field representing the type you want to construct
- You initialize that field in the constructor of your builder
- You create a series of methods that act on the instance stored in that field. Each method returns an instance of the Builder. This allows for that fluent interface.
More complex builder methods
I mentioned that this is the simplest form – a one-to-one relationship between builder method and property I want to set. You need not do that. You can add builder methods that logically bundle up the setting of multiple properties. Some simple examples for our example would be adding an AsValid() and WithDiscount(decimal discount). Let’s see that:
public CouponBuilder AsValid()
{
return WithExecuteMaxTimes(100)
.WithExpirationDate(DateTime.Today.AddYears(1))
.WithMinOrderAmount(0);
}
public CouponBuilder WithPercentageDiscount(decimal discount)
{
return WithCouponType(CouponType.PercentOffOrder)
.WithDiscount(discount);
}
Smart Defaults
In most of my builders, I set some smart defaults in the constructor. That is, I set the defaults to be those most commonly used in my tests. Here is the updated CouponBuilder ctor:
public CouponBuilder()
{
_coupon = new Coupon();
WithCouponCode("TestCode")
.WithCouponType(CouponType.PercentOffOrder)
.WithDiscount(.25m)
.WithDescription("Test Description")
.WithExecuteMaxTimes(100)
.WithExpirationDate(DateTime.Today.AddYears(1))
.WithId(1)
.WithMinOrderAmount(0);
}
Private Setters
Notice how my Coupon class has a private setter. It is a common and good practice to make ids immutable. However, you may want to have the ability to set your id for testing purposes. Our builder class is a perfect place for that. (*** remember that this will just be used in our tests). Have a look at my WithId builder method:
public CouponBuilder WithId(int id)
{
_coupon.GetType().GetProperty("Id").SetValue(_coupon, id, null);
return this;
}
Conclusion
The Builder Pattern is a great tool for, among other things, constructing objects for your tests. Writing them up front will save you loads of time throughout the lifetime of your project. Personally, I have a simple ReSharper template I use to create the Builder methods. Feel free to email me and I will send it to you…
Twas the Release Before Christmas
A geekier rendition of Twas the Night Before Christmas was just posted on the devEducate blog.
Entity Framework Modeling: Table Splitting
I have released a new post in the Entity Framework series at deveducate.com. The post illustrates how to take advantage of Table Splitting in Entity Framework 4.
Follow me on twitter
Email Me
