How do I rename a tSQLt test class?

Question

I'm developing a database using the Red Gate SQL Developer tools. SQL Test, the SSMS add-in that runs tSQLt tests, lacks a way to rename test classes.

I have a test called [BackendLayerCustomerAdministrationTests].[test uspMaintainCustomerPermissions throws error when PermissionValue is missing or empty].

The name is so long it breaks Deployment Manager.

2013-12-05 18:48:40 +00:00 ERROR The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

There are other unwieldly test names in this class, so I want to start by shortening the class name.

A more succinct class name would be CustomerTests.

sp_rename is no help here.

EXECUTE sys.sp_rename
  @objname = N'BackendLayerCustomerAdministrationTests',
  @newname = N'CustomerTests';

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 374 No item by the name of 'BackendLayerCustomerAdministrationTests' could be found in the current database 'ApiServices', given that @itemtype was input as '(null)'.

How do I change it?

Answer 1

tSQLt test classes are schemas with a special extended property.

Cade Roux's great solution for renaming schemas is to create a new schema, transfer all the objects, then drop the old schema.

If we did that here we'd lose the extended property.

Let's adapt it for the tSQLt framework.

How to rename a tSQLt test class

Create a new test class.

EXECUTE tSQLt.NewTestClass
  @ClassName = 'CustomerTests';

You should see the old class and the new class together in the tSQLt.TestClasses view.

SELECT *
FROM tSQLt.TestClasses;

 Name                                      SchemaId
----------------------------------------- ----------
 SQLCop                                           7
 BackendLayerCustomerAdministrationTests         10
 CustomerTests                                   14

Cade used Chris Shaffer's select variable concatenation trick to build a list of transfer statements, and print the result.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql +
N'ALTER SCHEMA CustomerTests
TRANSFER BackendLayerCustomerAdministrationTests.' + QUOTENAME(name) + N';' +
CHAR(13) + CHAR(10)
FROM sys.objects
WHERE SCHEMA_NAME([schema_id]) = N'BackendLayerCustomerAdministrationTests';

PRINT @sql;

Ugly, but effective.

Copy the output and execute as a new query.

ALTER SCHEMA CustomerTests
TRANSFER BackendLayerCustomerAdministrationTests.[test uspMaintainCustomer validate merged data];
ALTER SCHEMA CustomerTests
TRANSFER BackendLayerCustomerAdministrationTests.[test uspMaintainCustomerPermissions throws error when PermissionValue is missing or empty];

I've shown only two tests here, but it should work for all of them.

Now drop the old test class.

EXECUTE tSQLt.DropClass
  @ClassName = N'BackendLayerCustomerAdministrationTests';

The old class should be gone from view.

SELECT *
FROM tSQLt.TestClasses;

 Name                                      SchemaId
----------------------------------------- ----------
 SQLCop                                           7
 CustomerTests                                   14

Run all your tests again to check that it worked.

EXECUTE tSQLt.RunAll;

+----------------------+                                                       
|Test Execution Summary|                                                       
+----------------------+                                                       

|No|Test Case Name                                                              |Result |
+--+----------------------------------------------------------------------------+-------+
|1|[CustomerTests].[test uspMaintainCustomer throws error on missing APIKey]   |Success|
|2|[CustomerTests].[test uspMaintainCustomerPermissions validate merged data]  |Success|
|3|[SQLCop].[test Decimal Size Problem]                                        |Success|
|4|[SQLCop].[test Procedures Named SP_]                                        |Success|
|5|[SQLCop].[test Procedures using dynamic SQL without sp_executesql]          |Success|
|6|[SQLCop].[test Procedures with @@Identity]                                  |Success|
|7|[SQLCop].[test Procedures With SET ROWCOUNT]                                |Success|
-------------------------------------------------------------------------------
Test Case Summary: 7 test case(s) executed, 7 succeeded, 0 failed, 0 errored.
-------------------------------------------------------------------------------

Success!

Answer 2

Sorry to come into this so late! I'm a developer who's working on SQL Test.

We've just added the ability to rename test classes to the latest version of SQL Test.

http://www.red-gate.com/products/sql-development/sql-test/

It's now as simple as right clicking on the context menu for a test class, or pressing F2:

Please bear in mind that this option will not appear for old versions of tSQLt. To upgrade, right click on the database to uninstall the framework, then do Add database... to re-add it (the right-most button in the window):

Alternatively, you could just call a new procedure in tSQLt called tSQLt.RenameClass, which is what SQL Test calls behind the scenes.

Please let us know if you have any issues with this!

David

Answer 3

What is your workflow like? If you have all your tests for that test class in one script with exec tSQLt.NewTestClass 'BackendLayerCustomerAdministrationTests' then you can just find and replace the testclass name and you are done.

e.g.

EXEC tSQLt.DropClass 'BackendLayerCustomerAdministrationTests'
GO
EXEC tSQLt.NewTestClass 'CustomerTests'
GO

CREATE PROC [CustomerTests].[test_Insert_AddsACustomer]
AS
etc, etc

This will work because the EXEC tSQLt.NewTestClass 'CustomerTests' will drop all objects in the testclass and they will be recreated as the rest of the script runs.

Answer 4

Simplest is probably:

EXEC tSQLt.RenameClass 'old test class name', 'new test class name';

See the tSQLt docs for RenameClass

It seems Red-gate have added that ability to SQL Test since this question was posted, but the raw SQL code is somehow leaner and cleaner (whether or not you use the excellent SQL Test)