Schema Drift

Table of Contents

This testmethod monitors the schema (metadata) of a datasource and makes the testcase fail if there were changes.

The following changes get monitored:

  • Creating or Deletion of Tables, Views, and Columns.
  • Renaming of Tables, Views and Columns.
  • Changes on Columns:
    • Datatype
    • Length
    • Scale
    • Precision
    • Nullable
  • Ordering of Columns in Tables and Views.

When run, the testmethod reads the database-schema of a datasource and compares it against the schema extracted in the previous run. So the baseline is always the schema of the previous run. That’s why the testmethod only works starting with the second run.

When analyzing the testresults, you can download the schema of the datasource as an attachment of the testresult. There you also find the differences detected.

Open the attachment as following:

  1. Open the details of the testresult.
  2. Open the contextmenu of the testresult by clicking on the arrow of the “Details”-button.
  3. Click on “Download Attachment”.

Requirements

  • The testmethod only works with a OLE DB datasource. You cannot choose another datasource-type.

Parameters

DatasourceSelect the OLE DB datasource whose schema should be monitored.
IncludesEnter a semicolon-separated list of database-object-names that should be included into the monitoring. Use the syntax described below.
ExcludesEnter a semicolon-separated list of database-object-names that should be execluted from the monitoring. Use the syntax described below.

Results

SuccessThere are no differences between the current schema and the schema extracted in the previous testcase-execution.
FailedThere are at least one difference between the current schema and the schema extracted in the previous testcase-execution.

Syntax of Includes and Excludes

Every database-object has a unique name that is composed by different elements like following:

ObjectNamingExample
TableDatabase.Schema.TableAW2014.dbo.Products
ViewDatabase.Schema.ViewAW2014.dbo.vAccounts
Column of TableDatabase.Schema.Table.ColumnAW2014.dbo.Products.Name
Column of ViewDatabase.Schema.View.ColumnAW2014.dbo.vAccounts.Name

Enter a semicolon-separated list of Includes or Excludes into the parameter-fields of the testmethod and use the object-names as defined above.

You can even use placeholders (wildcards) in these names:

*All objects.
*.dbo.*All objects within the database-schema “dbo”.
*Product*All objects that have the string “Product” anywhere within their name.
*AccountAll objects whose name ends with “Account”.
Category*All objects whose database-name starts with “Category”.
*dbo.Category*All objects whose table- or view-name starts with “Category” and that are within any database-schema that ends with “dbo”.
*Product*;*Account*All objects whose name either contains “Product” or “Account”. Note that there is a semicolon in between.