Test Database

TestDatabase is a ProtoPattern or PatternLanguage for mocking a database. It uses a genuine database as the MockDatabase.

One Context: (There are probably others that this pattern applies to.)

The GUI and most business logic are written in Microsoft VisualBasic 6.0 using ADO -- it expects the ConnectionStrings anyway.

The data is stored in a RelationalDatabase, such as Microsoft Sql Server.

Forces:

You do not want to mix production data and test data, for fear of:
- contaminating (or accidentally deleting) your production data
- production data changes, causing false alarms in the tests.
Possibly, your DBA may prohibit have tables in the production database that are used only for testing.
Your DBA may be willing to provide a database on a test server where you are the database owner, and you can change the test database's schema arbitrarily.
It is awkward to change SQL queries to call test tables in a production database (as opposed to production tables in that same database).
If there are separate test and production databases, and the schemas of the databases match closely enough, the same SQL queries can be used against both databases.
Visual Basic 6.0 does not support ImplementationInheritance, so it is easier to call a real database for testing purposes than to call a set of objects that pretend to be a database.

Possible Solution:

Build 2 relational databases.
1. One is the production database, and contains production data.
2. The other is the test database. Its contents can be changed arbitrarily, and the users will never need to know.

The program has 2 modes: testing and production.
- Write a single function, such as TestAll.IsInTestMode(), that returns the mode.
- Optionally, the TestAll.TestAllModules() subroutine sets the mode to test mode at the beginning of the tests, and restores the mode to production mode at the end of the tests.
- Optionally, the program executable can have a /testdb flag that sets the mode to test mode.
- By default, the released program is in production mode.

All code that calls the database makes its connections based on a ConnectionString.
- There is a single function that returns the ConnectionString. It calls TestAll.IsInTestMode() to choose which ConnectionString to return.
- The only code that hard codes or constructs a ConnectionString is in this function.
- Any code that calls the database must (via program convention) use this function.

Build the following schema verification code into your test harness:
- Does every (tested) table or query in the production database exist in the test database? And vice versa.
- Does every (tested) field in the production database exist in the test database? And vice versa.
- Does every (tested) field in the production database have the same / close-enough data type in the test database?
- Does every (tested) view in the production database have the same / close-enough SQL code as the corresponding view in the test database? And vice versa.

When needed, the test harness can delete all data in the test database table(s).
- Small amounts of test data can be replaced using hard-coded queries.
- Large amounts of test data can be loaded from text files. The text files can be (excerpts of) snapshots of the production database.

Resulting Context:

HighRisk: Maintenance programmers need to know not to hard-code ConnectionStrings to the production database. If an insert or delete query is made to the production database using a hard-coded ConnectionString, there is a HighRisk that calling the test code will modify the production database.
Duplication: Parts of the database schema are stored in three places:
- The production database.
- The TestDatabase.
- The files and hard-codings used to store the test data.
Repeatable Demo Data: Optionally, the test data can be left in the TestDatabase after the test run is completed. This allows the developers to demo the software, either using the /testdb flag, or in the debugger with the mode set to test mode. At any time, the demo data can be reset to standard values by running the tests.

Discussion:

Is TestDatabase a pattern - or is it an anti-pattern? Time will tell.

This may be a PatternLanguage, not just a single pattern.

' Large amounts of test data can be loaded from text files. The text files can be (excerpts of) snapshots of the production database. '

Or you can generate it even before you have the production to take a snapshot of. For that you need a TestDataGenerator tool.