Unit Testing & Primary Keys

Question

Hi,

I am new to Unit Testing and think I might have dug myself into a corner.

In your Unit Tests, what is the better way to handle primary keys?

Hopefully an example will paint some context. If create several instances of an object (Lets' say Person).

My unit test is to test the correct relationships are being created.

My code is to create Homer, he children Bart and Lisa. He also has a friend Barney, Karl & Lenny.

I've seperated my data layer with an Interface. My preference is to keep the primary key simple. Eg On Save, Person.ProductID = new Random().Next(10000); instead of say Barney.PersonID = 9110 Homer.PersonID = 3243 etc.

It doesn't matter what the primary key is, it just needs to be unique.

Any thoughts???

EDIT:

Sorry I haven't made it clear. My project is setup to use Dependency Injection. The data layer is totally separate. The focus of my question is, what is practical?

Answer 1

Why use random numbers? Does the numeric value of the key matter? I would just use a sequence in the database and call nextval.

Answer 2

Consider using GUIDs. They're unique across space and time, meaning that even if two different computers generated them at the same exact instance in time, they will be different. In other words, they're guaranteed to be unique. Random numbers are never good, there is a considerable risk of collision.

You can generate a Guid using the static class and method:

Guid.NewGuid();

Assuming this is C#.

Edit:

Another thing, if you just want to generate a lot of test data without having to code it by hand or write a bunch of for loops, look into NBuilder. It might be a bit tough to get started with (Fluent methods with method chaining aren't always better for readability), but it's a great way to create a huge amount of test data.

Answer 3

There are several possible corners you may have dug yourself into that could ultimately lead to the question that you're asking.

1. Maybe you're worried about re-using primary keys and overwriting or incorrectly loading data that's already in the database (say, if you're testing against a dev database as opposed to a clean test database). In that case, I'd recommend you set up your unit tests to create their records' PKs using whatever sequence a normal application would or to test in a clean, dedicated testing database.

2. Maybe you're concerned about the efficacy of your code with PKs beyond a simple 1,2,3. Rest assured, this isn't something one would typically test for in a straightforward application, because most of it is outside the concern of your application: generating a number from a sequence is the DB vendor's problem, keeping track of a number in memory is the runtime/VM's problem.

3. Maybe you're just trying to learn what the best practice is for this sort of thing. I would suggest you set up the database by inserting records before executing your test cases using the same facilities that your application itself will use to insert records; presumably your application code will rely on a database-vended sequence number for PKs, and if so, use that. Finally, after your test cases have executed, your tests should roll back any changes they made to the database to ensure the test is idempotent over multiple executions. This is my sorry attempt of describing a design pattern called test fixtures.

Answer 4

The essential problem with database unit testing is that primary keys do not get reused. Rather, the database creates a new key each time you create a new record, even if you delete the record with the original key.

There are two basic ways to deal with this:

1. Read the generated Primary Key, from the database and use it in your tests, or
2. Use a fresh copy of the database each time you test.

You could put each test in a transaction and roll the transaction back when the test completes, but rolling back transactions doesn't always work with Primary Keys; the database engine will still not reuse keys that have been generated once (in SQL Server anyway).

Answer 5

When a test executes against a database through another piece of code it ceases to be an unit test. It is called an "integration test" because you are testing the interactions of different pieces of code and how they "integrate" together. Not that it really matters, but its fun to know.

When you perform a test, the following things should occur:

1. Begin a db transaction
2. Insert known (possibly bogus) test items/entities
3. Call the (one and only one) function to be tested
4. Test the results
5. Rollback the transaction

These things should happen for each and every test. With NUnit, you can get away with writing step 1 and 5 just once in a base class and then inheriting from that in each test class. NUnit will execute Setup and Teardown decorated methods in a base class.

In step 2, if you're using SQL, you'll have to write your queries such that they return the PK numbers back to your test code.

INSERT INTO Person(FirstName, LastName)
VALUES ('Fred', 'Flintstone');
SELECT SCOPE_IDENTITY(); --SQL Server example, other db vendors vary on this.

Then you can do this

INSERT INTO Person(FirstName, LastName, SpouseId)
VALUES('Wilma', 'Flintstone', @husbandId);
SET @wifeId = SCOPE_IDENTITY();

UPDATE Person SET SpouseId = @wifeId
WHERE Person.Id = @husbandId;
SELECT @wifeId;

or whatever else you need.

In step 4, if you use SQL, you have to re-SELECT your data and test the values returned.

Steps 2 and 4 are less complicated if you are lucky enough to be able to use a decent ORM like (N)Hibernate (or whatever).

Answer 6

I have a class called "Unique" which produces unique objects (strings, integers, etc). It makes sure they're unique per-test by keeping a internal static counter. That counter value is incremented per key generated, and included in the key somehow.

So when I'm setting up my test

var Foo = {
    ID = Unique.Integer()
}

I like this as it communicates that the value is not important for this test, just the uniqueness.

I have a similar class 'Some' that does not guarantee uniqueness. I use it when I need an arbitrary value for a test. Its useful for enums and entity objects.

None of these are threadsafe or anything like that, its strictly test code.