Integrating tSQLt with Cruise Control
tSQLt test cases can be executed and included in the results of your continuous integration build. This section describes how to integrate tSQLt test results with the popular continuous integration tool, CruiseControl.
CruiseControl executes your build script on a schedule or when code is checked-in to the version control repository. It reports build errors automatically and can include test results. For this discussion, we'll setup a build in Ant, although other builders can be used.
Faking Views
We've seen FakeTable in tSQLt in action in a few demos now. Did you know that you can use FakeTable to fake a view?
You use FakeTable on a table when you want to test a procedure (or function or view) that relies on a table. It prevents you from needing to concern yourself with all of the other constraints a table might have, such as foreign keys to other tables or not-nullable columns which have nothing to do with your procedure. This is particularly useful if you have a schema where many tables are interrelated through foreign keys because you can focus on the procedure you're testing instead of trying to create test data for dozens of unrelated tables.
Similarly, you would want to fake views.
Refactoring Example: Office Hours
Supporting a Database Refactoring with Tests and Views
As we develop a database application, we often realize that decisions made early on regarding normalization need to be changed. A recent post on the Agile Databases mailing list illustrates this need.
Let's suppose we have an application that stores the office hours that employees make free for questions. The initial design allowed employees to enter their office hours in free form text and was stored in the following schema.
Constraint Controversy
It wasn't until recently that I ever thought to do away entirely with database constraints. Aside from appropriate normalization and writing fast SQL code, much of the time database professionals spend is on figuring out exactly what constraints to put on their schema. We're taught by textbooks and by some bad experiences that database constraints are a good idea. But how do they fit in with our new, agile view of the world?
Database Test Driven Development
Test Driven Development (TDD) is known to be a successful method of object oriented development. In database development however, TDD practices are not wide-spread and development teams struggle with applying the TDD principles to the SQL language. This is a problem, because it leads to poorly tested code. In turn, not having the appropriate test cases, makes it difficult to improve your existing database design. Not implementing TDD practices in the database, overtime, leads to a decaying architecture and can hinder the evolution of the overall application.
