FakeTable

 

Syntax

tSQLt.FakeTable [@TableName = ] 'table name'
                , [[@SchemaName = ] 'schema name']
                , [[@Identity = ] 'preserve identity']
                , [[@ComputedColumns = ] 'preserve computed columns']
                , [[@Defaults = ] 'preserve default constraints']

Arguments

[@TableName = ] ‘table name’
The name of the table for which to create a fake table. Should contain both the schema name and the table name.
[@SchemaName = ] ‘schema name’ – Deprecated: do not use, will be removed in future version
[@Identity = ] ‘preserve identity’ – Indicates if the identity properties of an identity column should be preserved. If @Identity = 1, the identity properties will be preserved, otherwise the faked table will have the identity properties removed. The default is @Identity = 0.
[@ComputedColumns = ] ‘preserve computed columns’ – Indicates if the computations on computed columns should be preserved. If @ComputedColumns = 1, the computations will be preserved, otherwise the faked table will have computations removed from computed columns. The default is @ComputedColumns = 0.
[@Defaults = ] ‘preserve default constraints’ – Indicates if the default constraints on columns should be preserved. If @Defaults = 1, default constraints will be preserved for all columns on the faked table, otherwise the faked table will not contain default constraints. The default is @Defaults = 0.

Return Code Values

Returns 0

Error Raised

If the specified table does not exist an error is thrown: FakeTable could not resolve the object name, ‘%s’.

Result Sets

None

Overview

We want to keep our test cases focused and do not want to insert data which is irrelevant to the current test. However, table and column constraints can make this difficult.

FakeTable allows tests to be written in isolation of the constraints on a table. FakeTable creates an empty version of the table without the constraints in place of the specified table. Therefore any statements which access the table during the execution of the test case are actually working against the fake table with no constraints. When the test case completes, the original table is put back in place because of the rollback which tSQLt performs at the end of each test case.

Limitations

FakeTable cannot be used with temporary tables (tables whose names begin with # or ##).

Warnings

Remember if you are faking a table, you are not testing the constraints on the table. You will want test cases that also exercise the constraints on the table. See Also: ApplyConstraint.

Examples

Example: Faking a table to create test data to test a view
In this example, we have a FinancialApp.ConvertCurrencyUsingLookup view which accesses the FinancialApp.CurrencyConversion table. In order to test the view, we want to freely insert data into the table. FakeTable is used to remove the constraints from the CurrencyConversion table so that the data record can be inserted.

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrencyUsingLookup converts using conversion rate in CurrencyConversion table]
AS
BEGIN
    DECLARE @expected MONEY; SET @expected = 3.2;
    DECLARE @actual MONEY;
    DECLARE @amount MONEY; SET @amount = 2.00;
    DECLARE @sourceCurrency CHAR(3); SET @sourceCurrency = 'EUR';
    DECLARE @destCurrency CHAR(3); SET @destCurrency = 'USD';

------Fake Table
    EXEC tSQLt.FakeTable 'FinancialApp.CurrencyConversion';

    INSERT INTO FinancialApp.CurrencyConversion (id, SourceCurrency, DestCurrency, ConversionRate)
                                         VALUES (1, @sourceCurrency, @destCurrency, 1.6);
------Execution
    SELECT @actual = amount FROM FinancialApp.ConvertCurrencyUsingLookup(@sourceCurrency, @destCurrency, @amount);

------Assertion
    EXEC tSQLt.assertEquals @expected, @actual;
END;
GO

Example: Calling FakeTable with @Identity = 1
We need to specify the @Identity parameter explicitly because the second parameter (@SchemaName) has been deprecated. This same pattern applies to @ComputedColumns and @Defaults.

EXEC tSQLt.FakeTable 'FinancialApp.CurrencyConversion', @Identity = 1;

See Also

ApplyConstraint

Since

1.0.4462.23207

 Posted by at 5:23 pm

  6 Responses to “FakeTable”

  1. I am trying to preserve ‘not null’ constraints on columns using FakeTable with @Defaults=1 and it is not working. Has this functionality been added in yet? BTW, very good work so far on this project. It has become very useful to me.

    Thanks,
    Travis

    • Hi Travis.

      There is currently no way to preserve NOT NULL constraints. (They are not really constraints but column attributes, so tSQLt.ApplyConstraint cannot be used.)

      The usual use case for tSQLt.FakeTable is a test that is testing a module (e.g. stored procedure) that uses that table. In that case your “object under test” is that procedure, not the table. So when writing your tests make sure that the procedure does not insert a NULL in a not-nullable column. You don’t need the column to be not-nullable to write this test. In fact, in that test you should not rely on column nullability but instead use its own assertions.

      The reason we use nullability constraints is to protect the data from stray programming or ad hoc alterations. So the only test you need to write that is concerned with column nullability is the one test that confirms that you cannot insert a NULL into the column in question. For that case however we have not found or implemented a good answer yet. So, currently you have to assert the correct nullability status of a column by looking at the is_nullable column in sys.columns.

      I hope this helps.

  2. [...] test also introduces a tSQLt feature not yet covered in this series: tSQLt.FakeTable. This is one more great reason why I like [...]

  3. [...] The tSQLt project has a unique solution to this, EXEC tSQLt.FakeTable ‘dbo.OrderDetail’. The FakeTable procedure temporarily renames the original OrderDetail table, creates a copy of the table with the [...]

  4. [...] the test.  One of the best ways to fully isolate such unit tests from the database would be to fake the tables under test. However at the time of writing (v1.0.4413.31717), whilst tSQL does have [...]

  5. [...] One of the features of this framework I really like is the ability to fake or mock a table. FakeTable temporarily re-names the “production” table then creates an empty copy with the [...]

 Leave a Reply

(required)

(required)

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Bad Behavior has blocked 965 access attempts in the last 7 days.