Fake a temporal table

[Michael Hotek]

This extension consists of 4 new stored procedures for the tSQLt framework which together will allow you to fake a temporal table and preserve the temporal definition.  This is NOT backward compatible to versions prior to SQL Server 2016.  

It has ONLY been tested on an on-premise SQL Server 2016 and SQL Server 2017.

It has NOT been tested on any other edition/permutation of SQL Server such as Azure.

• tSQLt.FakeTableTemporal
• Main proc and would be used in place of tSQLt.FakeTable
• Has 3 added parameters that allow you to specify whether you want to:
• Preserve nullabilty specification when faking the table
• Preserve the primary key when faking the table
• Preserve the temporal definition when faking the table
• The "deprecation" of @SchemaName has been removed
• It is left up to YOU as to whether you want to use @SchemaName + @TableName or just slam both parts of the name into the @TableName parameter
• The proc has logic to prevent mismatches in the parameters you send
• i.e. Setting @PreserveNullability = 0 when you have to have a not null column on the primary key
• The proc also has code to prevent attempting to set a configuration that does already exist
• i.e. Setting @PreservePrimaryKey = 1 on a table that does not originally have a primary key
• i.e. Setting @PreserveTemporal = 1 on a table that is not already defined as a temporal table
• tSQLt.Private_CreateFakeOfTablePreserveNullability
  
• Helper proc that mimics tSQLt.Private_CreateFakeOfTable but lets you wind up with a fake table with the original nullability definition
• I really have no idea why this wasn't an option from the very beginning.  It was the most trivial change in this entire stack, simply requiring an additional WHEN in the CASE statement
• tSQLt.Private_CreatePrimayKey
  
• Helper proc that adds the primary key to the fake table
• tSQLt.Private_FakeTableAddTemporal
  
• Helper proc that alters the fake table and adds the temporal specification

[Andy Jones]

This sounds great.

Have you written unit tests to cover this new functionality?

This is an open source project, have you submitted a pull request to include these changes?

[Michael Hotek]

Yes, I did unit testing on it.

No, I didn't submit a pull request.  I don't have the inclination to set up an environment to connect to the source control for this and do all of the pull request, etc.  If someone wants to use it, go ahead.  If someone wants to shove it into the rest of the code base, go ahead.  Don't care if anyone uses or how they use it or not use it.

[Bryant McClellan]

Michael,

FWIW I am actively testing this extension on SQL Server 2019. Results are promising. The only changes I have made are:

1. Placing your code in our private schema. That is the repository of other extensions we've made (SCHEMABINDING handling, test generators and the like)

2. Added a call to our SCHEMABINDING handler

Thank you for taking the time to work through this. We just started looking into temporal tables seriously. We are a TDD shop and probably have more tSQLt test objects than non-test objects. I was not about to venture into temporal tables if we could not unit test and you've handled that hurdle.

[Bryant McClellan]

Michael,

I did end up making one change. Private_CreatePrimaryKey.sql does not correctly screen for primary key. When the table in question has NCIs it is possible to get a result that puts the same columns in the new PK more than once. I changed the WHERE clause to refer to sys.index_columns and test for index_id=1. Only primary keys can have index_id=1.

[Sebastian Meine]

Bryant,

I believe that assumption might be wrong. You can have non-clustered primary keys which then would have an index_id > 1.

sys.indexes has an is_primary_key column, that should be safer. 

Sebastian

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/598281f2-d348-46ab-be77-71887e27f28bn%40googlegroups.com.

--

Thanks,

Sebastian

[Bryant McClellan]

Sebastian,

The original code used is_primary_key column and I got some columns repeated. It also picked up every include column from the NCIs so the faked PK would be different from the original PK even if the duplicate column names did not occur. Seems that neither answer is complete.

[Bryant McClellan]

Sebastian,

Suppose, instead, that the SELECT also added the index_id columns to the ON constraint between the first 2 tables. In other words

            SELECT      c.name, ic.key_ordinal

            FROM        sys.indexes i

            INNER JOIN  sys.index_columns ic

            ON          i.object_id = ic.object_id

            INNER JOIN  sys.columns c

            ON          ic.object_id = c.object_id

            AND         ic.column_id = c.column_id

            WHERE       ic.object_id = @OriginalTableObjectID

            AND         i.is_primary_key = 1

becomes

            SELECT      c.name, ic.key_ordinal

            FROM        sys.indexes i

            INNER JOIN  sys.index_columns ic

            ON          i.object_id = ic.object_id

            AND         i.index_id = ic.index_id

            INNER JOIN  sys.columns c

            ON          ic.object_id = c.object_id

            AND         ic.column_id = c.column_id

            WHERE       ic.object_id = @OriginalTableObjectID

            AND         i.is_primary_key = 1

That retains the primary key filter as is but makes a more correct association between sys.indexes and sys.index_columns for this purpose.

[Sebastian Meine]

Without looking at the code, that sounds like there's a problem in the join condition between the sys.indexes, sys.index_columns, and sys.columns (in the original).

Thanks,

Sebastian

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/d8182f87-6a76-4977-b4bc-db8f78a07ea4n%40googlegroups.com.