Labels: Alt.net, alt.net.uk

SerialSeb, one of the attendees at Alt.Net.UK conference earlier in the year, is arranging a few drinks in a London bar (to be announced via upcoming link) as a chance to catch up, meet and have an informal chat (no sessions or anything like that) with people who are interested in the alt.net ideas.  Should be a great way to unwind after a hard days work.

More information on his post - http://serialseb.blogspot.com/2008/04/announcing-altnet-london-beers-event.html

I'm planning to attend and it would be a great chance to catch up with everyone again.  I look forward to seeing you there.

Labels: Alt.net, alt.net.uk

Last month we released SQL Data Generator which is a great tool for generating your dev\test data. One of the decisions we made for 1.0 was not to include an API. I wanted to be able to incorporate the data generation into my automated tests, so I have created an extension to SQL Data Generator as a side project which allows you to incorporate the generation process into your unit (well Integration) tests. You can now generate data as part of your unit tests.

I have wrote an article on my approach and how to use the framework over at Simple Talk - http://www.simple-talk.com/sql/sql-tools/using-sql-data-generator-with-your-unit-tests/

For the extension, I took two approaches. One approach was just a POCO (Plain Old CLR Object) which would execute the command line application for a given project file. This could be placed anywhere in your code, ideally I was thinking in the TestFixtureSetup.

[TestFixtureSetUp]
public void TestFixtureSetup()
{
    SDGConsoleWrapper sdgConsoleWrapper = new SDGConsoleWrapper(@"Database\NorthwindEmployees_Orders.sqlgen");
    sdgConsoleWrapper.Execute();
}

As a parameter, you pass in the filename for the project file created via the UI.

The second approach is using custom attributes. By adding an attribute 'SDG' to the TestFixture and inheriting from 'SDGContext', we hooked into the calls for each method.  At the top of your test method, you can add the 'SDGExecute' with the project file name, this will then be executed before that test started.

[TestFixture]
[SDG]
public class DataAccessTests : SDGContext
{
   [Test]
   [SDGExecute("Database\\NorthwindCustomers_Orders.sqlgen")]
   public void GetCustomerByID_SDGProject_1000Rows()
   {
      DataTable dt = DataAccess.GetCustomerByID("00040");

      Assert.AreEqual("00040", dt.Rows[0]["CustomerID"]);
      Assert.AreEqual("Suppebentor Holdings ", dt.Rows[0]["CompanyName"]);
      Assert.AreEqual("Bobbi Yates", dt.Rows[0]["ContactName"]);
      Assert.AreEqual("Web", dt.Rows[0]["ContactTitle"]);
      Assert.AreEqual("564 White Old Freeway", dt.Rows[0]["Address"]);
   }
}

Best of all - this works with all of the unit testing frameworks! NUnit, MbUnit, XUnit, MSTest! They all can take advantage.  Feel free to download (from our CodePlex project) and start using it, I suggest you read the article for more of an in-depth overview of what is going on and the possible ways you can take advantage of it.

Finally, we would love to hear your feedback on this. I created this in my free time and is not a supported solution. However, do you think the application should have a fully supported API? Are you happy with the approach I have taken or do you think there are better ways of doing this?  Maybe your comments will help shape SQL Data Generator 2.0.

Article Link: http://www.simple-talk.com/sql/sql-tools/using-sql-data-generator-with-your-unit-tests/

Application Download Link: Red Gate SQL Data Generator

Download and Source: http://www.codeplex.com/SDGGenerators

Labels: Red Gate, SQL Server

In my previous post I talked about how to get started with IronPython, in this post I want to follow up with some very basic information about the actual language and some tips to enable you to start reading sample code. This post won't make you an expert, but it should be enough to get you going.

One of the great things about IronPython and IronRuby is that there are already a huge amount of resources online about the language. A good starting point (apart form this post of course) is http://docs.python.org/ which contains the documentation for the Python language.  One problem with that approach is that there are minor differences different Python and IronPython.

Defining a variable

Defining a variable in IronPython seems a logical place to start.  The easiest way to play around with the language is by using the interactive console (ipy.exe).

To define a variable, you simply say <var name> = <value>, for example:

>> x = 1

This creates a variable called x, of type integer (it has inferred the type) with the value of 1. As Python is a dynamic language, the way it handles variables is sightly different than you would expect compared to a static language like C#.  While Python is still a strongly typed language, it is perfectly legal to do this following:

>>> x = 1
>>> print x
1
>>> x = "Hello World"
>>> print x
Hello World
>>>

What happens is that within the first statement we create an integer object and assign it to x, in the second statement we create a string object and assign it to x, removing the reference to x. However, if we try and mix the types then it throws an exception, here we are trying to add an int to a string.

>>> x = "Hello World" + 1
Traceback (most recent call last):
  File , line unknown, in Initialize##24
TypeError: unsupported operand type(s) for +: 'str' and 'int'

Python does know the type of the variable, it only uses it when it is actually required at runtime (late-binding) as far as I can tell.  For example, using the type function will return the correct type of the variable.

>>> print type(x)
<type 'str'>

So what does this mean while you are developing, well, it makes unit testing a lot easier as you don't need to worry so much about getting an object of the extract type - it just needs to look like the type. Also, code is more generic as it doesn't depend on the extract type.   However, you do run the risk of the type being wrong which is only found at runtime - but your automated tests will catch that right?

Creating a function

Now we can define variables, the next step is to define functions (methods). While Python is a dynamic language, it still has very strict language rules which must be followed, including it's important on whitespace and tabbing unlike C#.

To define a method, we start with def, we then provide a name for our method, we add a number of parameters separated by commas, no need to include the type as Python will work it out for itself. Finally, we need to end the line with a colon :.  Next, we need to fill in the body of the method, the most important thing is to tab in to indicate the body, we can then write a number of lines of code, if the method should return a value then we can include that as the last line. To mark the end of the method, you have another carriage return.

Below we create a function called x, with the parameters y and z. In the method body, we just add the two and print them to the output (the console).

>>> def x(y, z):
...     print y + z
...

We can then simply call this method, notice that we can use both ints and strings and the correct action is taken.
>>> x(1,2)
3
>>> x("Hello", "World")
HelloWorld

As we create the method, if we get something wrong then an error will be returned to alert us. Below I attempted to create a method but I forgot to include the colon at the end of the method.

>>> def x(y, z)
  File "", line 1
    def x(y, z)
            ^
SyntaxError: unexpected token '<eof>'

Similarly, if we do not indent the function body an error is returned.

>>> def x(y, x):
... print y + x
  File "", line 2
    print y + x
    ^
IndentationError: expected an indented block

Below is just an example of how to return a value from a method.

>>> def x(y,x):
...     return y + x
...
>>> y = x(1,2)
>>> print y

Finally, python allows you to create functions within functions, which will have local scope. Great for code re-use and readability!

>>> def y():
...     def x(y, z):
...             print y + z
...     x(1,2)
...     x("Hello", "World")
...
>>> y()
3
HelloWorld

Creating a Class

Creating a class is very similar to creating a function. The syntax is class <Name>(<InheritFrom>):  In the brackets, we can simply define all of the base classes.

>>> class MyClass(object):

We can then define function on the class in the same way, however one change is that we need to include self (similar to this in C#) as the first parameter of the method.
...     def PrintHello(self):
...             print "Hello!"
...     def Method2(self, x):
...             print "TWO TWO TWO"
...

To initialise the class, we simply say this:
>>> x = MyClass()

We can then call methods on it:

>>> x.PrintHello()
Hello!
>>> x.Method2("Parameter")
TWO TWO TWO

If we want to know all the methods on a class, we can do a dir.  Everything with a double underscore is an internal method. In the class we can define the actions to take when they get called.

>>> dir (x)
['Method2', 'PrintHello', '__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__', '__hash__', '__init__', '__module__', '__name__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__']

For example:

>>> class InitClass:
...     def __init__(self):
...             print "Created"
...
>>> i = InitClass()
Created

Just to sum up, Python is a full featured language, I haven't even touch onto some of the features of the language but hopefully it will provide you some insight into the code.

Labels: DLR, IronPython

Partition Tables is a feature of SQL Server 2005 which allows you to separate your data into blocks (partitions) of data to improve performance and manageability as each partition can be stored in its own filegroup, which in turn can be on a separate disk array. In this post, I will cover how to partition your table, the table will contain a series of orders which I want to partition based on their order date.

The first thing I want to do is create a database.

CREATE DATABASE PartitionTest
GO

USE PartitionTest
GO

Next, I want to create a table which will contain all of my 'orders'.

CREATE TABLE Orders (ID INT NOT NULL, OrderDate DATETIME NOT NULL)

Next, I got SQL Data Generator to generate me 10 million 'Orders' for the table.  This gave me a large number of dates between 01/01/1953 and 30/12/2010 (values I had set) - so a nice big table which really needs to be partitioned. With that in place, we can create our partition. 

First, we need to create a partition function, give it a name and the data type we are partitioning based on - in this case DateTime. We need say RANGE LEFT (anything on the left hand side goes into the partition), we then list the series of values we want to split the data based on.  In this case, anything below 31/12/1950 goes into partition 1, between 1/1/1951 and 31/12/1960 into partition 2 etc

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('19501231 23:59:59.997', --YYYYMMDD
                        '19601231 23:59:59.997',
                        '19701231 23:59:59.997',
                        '19801231 23:59:59.997',
                        '19901231 23:59:59.997',
                        '20101231 23:59:59.997',
                        '20201231 23:59:59.997')

At this point, we create a partition schema.  In this case, I'm using saying all the partitions should be stored in the primary filegroup, however this is where we would allocate the partitions to separate filegroups.

CREATE PARTITION SCHEME OrderDateRangeScheme AS
PARTITION OrderDateRangePFN
ALL TO ([PRIMARY])

At this point, we have a table with 10 millions rows, a partition function and a partition schema. However, nothing links the partition to the table.  What we need to do is create a new Primary Key which we can set the partition schema on. If you have any existing keys, they will need to be dropped and re-created with the partition schema included.

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
   PRIMARY KEY CLUSTERED (OrderDate, ID)
  ON OrderDateRangeScheme(OrderDate)
GO

If the table wasn't already created, we could include the partition schema in the create table script.

CREATE TABLE Orders (ID int, OrderDate DATETIME) ON OrderDateRangeScheme(OrderDate)
GO

In the above function, the year 2000 partition was missing. If we wanted to include this we would alter the function and include a split range

ALTER PARTITION FUNCTION OrderDateRangePFN () split RANGE ('20001231 23:59:59.997')

After executing all of this, your table will successfully be partitioned. To verify the details of the partition, there are a few queries we can execute.

The system view partitons contains all of the information, we can query this to return all the partition information for a table.

SELECT * FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('Orders')

This returns the partition number and how many rows are in each partition.  This is useful for identifying if your partitions are split correctly or if one partition needs to be split down more.

The other way is to use a $Partition, this gives the partition number for the row of data. This allows you to see what data is in what partiton

SELECT *, $Partition.OrderDateRangePFN(OrderDate) AS [Partition] FROM [Orders] ORDER BY [Partition]

More information visit http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/

Labels: SQL Server