Using tSQLt FakeTable with table referenced by indexed views

tSQLt_Database_Unit_Testing_for_SQL_Server__Logo_210x160If you tried to use tSQLt FakeTable to fake a table that is referenced by an object -in most cases: indexed view- is declared using a WITH SCHEMABINDING clause, your test case will fail with this SQL exception:

System.Data.SqlClient.SqlException: Object ‘table name’ cannot be
renamed because the object participates in enforced dependencies

What WITH SCHEMABINDING means is to notify SQL server that this view is tied to the schema of this one or more tables. Thats why you can not make indexed view unless you declare the view with WITH SCHEMABINDING clause.

A quick solution for this problem could be done simply by dropping these referenced objects inside the unit test code:

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';

------Drop the view that is using this table
    DROP VIEW FinancialApp.CurrencyConversionView;

------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

Of course you should know already that tSQLt executes unit test stored procs inside of a transaction that is always rollbacked, so no need to re create the view again.

But what if you are using this view in your unit test logic? not mentioning that you will have to manually add this drop logic to all of your unit tests which is not handy if you are working on a large application. based on this post which most of the code in this post is based on: How to FakeTable when table is referenced by objects with schemabinding clause?; we can alter the definition of these objects and remove the WITH SCHEMABINDING clause, this will not affect our unit tests at all because the views are still there for us to use. However and because you cannot have indexes on objects that are not declared using WITH SCHEMABINDING; we will have to remove all clustered indexes on indexed views, what I noticed is that when dropping clustered indexes in that case, non-clustered indexes are dropped as well.

A complete sample for this process here:


CREATE PROCEDURE [tSQLt].[PrepareTableForFaking]
        @TableName NVARCHAR(MAX),
        @SchemaName NVARCHAR(MAX)
AS
BEGIN

  --remove brackets
  select @TableName = (REPLACE(REPLACE(@TableName, '[', ''), ']', ''));
  select @SchemaName = (REPLACE(REPLACE(@SchemaName, '[', ''), ']', ''));

-- delete temptable
  IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#temp'))
    DROP TABLE #TEMP

  --recursively get all referencing dependencies
;WITH ReferencedDependencies (parentId, name, LEVEL)
  AS(
      SELECT DISTINCT o.object_id AS parentId, o.name, 0 AS LEVEL
        FROM sys.sql_expression_dependencies AS d
        JOIN sys.objects AS o
          ON d.referencing_id = o.object_id
            AND o.type IN ('FN','IF','TF', 'V', 'P')
            AND is_schema_bound_reference = 1
        WHERE
          d.referencing_class = 1 AND referenced_entity_name = @TableName AND referenced_schema_name = @SchemaName
      UNION ALL
      SELECT o.object_id AS parentId, o.name, LEVEL +1
        FROM sys.sql_expression_dependencies AS d
        JOIN sys.objects AS o
                ON d.referencing_id = o.object_id
            AND o.type IN ('FN','IF','TF', 'V', 'P')
            AND is_schema_bound_reference = 1
        JOIN ReferencedDependencies AS RD
                ON d.referenced_id = rd.parentId
  )

  -- select all objects referencing this table in reverse level order
  SELECT DISTINCT IDENTITY(INT, 1,1) AS id, name, OBJECT_DEFINITION(parentId) as obj_def, parentId as obj_Id , LEVEL
  INTO #TEMP
  FROM ReferencedDependencies
  WHERE OBJECT_DEFINITION(parentId) LIKE '%SCHEMABINDING%'
  ORDER BY LEVEL DESC
  OPTION (Maxrecursion 1000);

  --prepere the query to remove all dependent indexes (this is nessesary to removing (with schemabinding) later)
  DECLARE @qryRemoveIndexes NVARCHAR(MAX);
  SELECT @qryRemoveIndexes = (
  SELECT 'DROP INDEX ' + i.name + ' ON ' + OBJECT_NAME(o.id) + '; ' FROM sys.sysobjects AS o
  INNER JOIN #TEMP ON o.id = #TEMP.obj_Id
  INNER JOIN sys.sysindexes AS i ON i.id = o.id
  where i.indid = 1 -- 1 = Clustered index (we are only interested in clusterd indexes)
  FOR XML PATH(''));
  --excute @qryRemoveIndexes
  exec sp_executesql @qryRemoveIndexes;

  --change the definition for removing (with schemabinding) from those objects
  DECLARE @currentRecord INT
  DECLARE @qryRemoveWithSchemabinding NVARCHAR(MAX)
  SET @currentRecord = 1
  WHILE (@currentRecord <= (SELECT COUNT(1) FROM #TEMP) )
  BEGIN
          SET @qryRemoveWithSchemabinding = ''
          SELECT @qryRemoveWithSchemabinding = #TEMP.obj_def
            FROM #TEMP
            WHERE #TEMP.id = @currentRecord
          SET @qryRemoveWithSchemabinding = REPLACE(@qryRemoveWithSchemabinding,'CREATE', 'ALTER')
          SET @qryRemoveWithSchemabinding = REPLACE(@qryRemoveWithSchemabinding,'with schemabinding', ''); -- remove schema binding
          --excute @qryRemoveWithSchemabinding
          EXEC sp_executeSQL @qryRemoveWithSchemabinding;
          SET @currentRecord = @currentRecord + 1
  END

END

What we can do now is just to add this line of code inside [tSQLt].[FakeTable] stored proc:

   --after executing Private_ValidateFakeTableParameters
   EXEC tSQLt.PrepareTableForFaking @TableName, @SchemaName;

enjoy!

Advertisements
This entry was posted in Software and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s