UPDATE

I went and built this into my add-in for SSDT, more details https://the.agilesql.club/blog/Ed-Elliott/2015-11-17/Create-Stub-tSQLt-t… and https://the.agilesql.club/Projects/SSDT-Dev-Pack

Generating tSQLt tests from a dacpac

I use SSDT and tSQLt in my work and I am always interested in productivity tools to help speed up development and make it more streamlined, if there is something that can help us develop better or faster then we should use it.

I have been a great fan of the DacFx for a while and the sample Dacpac Explorer seems to get quite a lot of visits with one mention from ms devs internally using it as a learning reference!

The API that allows you to query dacpacs was always a little hard for developers to get to grips with, instead of having objects with properties such as a table having a list of columns which had a specific type (int, varchar etc) what

happened was you get a TSqlObject and you queried that for relationships and properties. It wasn’t always clear what was a property and what was a relationship and to find a specific property you had to pass in a reference to the

property class or name - it actually worked well if you wanted to enumerate the whole schema but finding specific bits of information was honestly a little odd.

I say the API “was a little odd” in the past tense because there has recently been a sample in the DacPublicSamples which takes the existing API and wraps a set of classes around it to help use it in a more conventional way. The new

code is called the DacFxStronglyTypedModel and is generated using T4 templates, which is the first time I can say that I have been happy to see T4 templates!

What happens now is that you can either create a typed object such as a TSqlTable which has things like an IEnumerable of TSqlColumn and properties like “MemoryOptimized” or “IsReplicated” so it is much simpler to find the objects you

need, plus if you still want to enumerate all the properties and relationships you still have access to the TSqlObject underneath so you can do everything you could always do, it really is a win win and a great addition to the DacFx

API.

So let’s do something

Ok great so we need a little project to a) take the new wrapper for a spin and b) start looking at using the ScriptDom can be used to generate code, there are a few examples on how to use the visitor pattern to parse T-SQL but not so

much on actually generating code which is more involved than I would have thought, then again I keep intending to read a book on compiler theory but don’t seem to have the time!

Getting the new wrapper

First of all you will need to download at latest SSDT which is currently the Feb 2015 SSDT + DacFx packages, if you are reading this in the future then just grab the latest versions.

Then you will need the wrapper which is mixed in with the other DacFx samples which have recently moved to github. If you don’t have git installed go and install git, I would recommend getting git bash and just using teh standard git

bash shell rather than installing the explorer extension or getting it to take over your existing command prompt (that would be bad!).

Once that is done, start git bash and change to a directory where you want to download the samples, I store my code in the c:\dev folder with sub directories for each project so I would type “cd /c/dev” (just prepend the path with a /,

lose the : and change \’s to /’s), this moves me to the right directory, I can verify the path is correct by doing “pwd”.

To download the samples do “git clone https://github.com/Microsoft/DACExtensions”

This should download the samples and you can open the “DacPublicSamples.sln” in the root directory.

Let’s build!

So first of all you should test that the samples have downloaded correctly and you have the correct dependencies etc by building the solution, we are only really interested in the DacFxStronglyTypedModel for this so go ahead and build

that.

Hold on, what are we building?

Ok, so like I said I am interested in using tooling to help the development process and one thing I see are Sql database projects without any tSQLt tests, I will let you catch your breath back while that sinks in…..ok so it would be

good to have a way to auto-generate a load of tests for us which we can then flesh out properly plus it would be cool if someone could generate a stub test including faking the dependent tables in a SSDT / Visual Studio package so this

could lead into that in the future.

What we will do in this blog is to get a list of all of the stored procedures in a dacpac using the new strongly typed model and then for each stored procedure, get a list of the tables it depends on - we will use these to generate

FakeTable statements and also for each parameter that the stored procedure has, we will create a variable and pass that variable in when we call it, so for example we will take the following stored procedure:

CREATE PROC schema.RaiseAnAlarm(@message varchar(max), @reason_id int)
as

INSERT INTO schemab.table(date, reason_id, message)
SELECT GetDate(), @readon_id, @message;

and we will generate a stub tSQLt test for it that looks something like:

CREATE PROCEDURE RaiseAnAlarm.[test to be implemented]
AS

DECLARE @message varchar(max) = ‘’;
DECLARE @reason_id int = 0;
EXECUTE FakeTable ’table’, ‘schemab’;
EXECUTE schema.RaiseAnAlarm @message, @reason_id;
EXECUTE tSQLt.AssertEquals ‘TRUE’, ‘FALSE’, N’Error Not Implemented’;

we will also need to create a new schema for the test:

CREATE SCHEMA RaiseAnAlarm
AUTHORIZATION dbo
GO
EXECUTE sp_addextendedproperty @name = ’tSQLt.TestClass’, @value = 1, @level0type = ‘SCHEMA’, @level0name = ‘RaiseAnAlarm’

What is that code we need to create?

If you already know about how tSQLt creates and manages tests then please skip ahead, otherwise…

In tSQLt you create a schema which is basically your test class, I recommend you put all the tests for one procedure in one schema - this way you logically group them together and if you want to you can use a SetUp procedure which runs

before all of the tests in that schema. You can also run all the tests that are in a schema together so you don’t have to run the entire test suite to check the changes on one procedure.

The schema has to have an extended property on it otherwise tSQLt won’t pick it up as a schema that contains tests so nothing will run.

Once you have the schema, you need to create the tests - typically you will have one or more test per stored procedure. To create a test you need to create it in a schema which has the extended property set and also it has to begin with

the lower case “test” so for example: [RaiseAnAlarm].[test alarm is not raised by accident] is a great name whilst [RaiseAnAlarm].[should raise an alarm] will result in hair pulling out when it doesn’t run.

FakeTable takes the table that is in the database, renames it to something else and then creates a new table which allows all columns to be NULL and removes all foreign keys - what this means is that you can fake the table, then insert

the columns and data you need to verify the test without having to worry about columns that do not matter (for this particular test) or foreign keys - should you wish you can of course enable the foreign keys if they are essential to

the code you are testing.

The Assert just fails the test so that we know that this particular test is still to be implemented.

The idea is to end up with enough code to make writing tests actually quicker, there is lots more that we could do such as checking if the proc returned a query and building a temp table to hold the results but not every test would use

that.

Enough overview let’s write some code!

Ok so we have cloned the samples directory and compiled the solution, you should have built the DacFxStronglyTypedModel and it should have compiled, if it didn’t you need to figure out why and get it built!

If you look at the DacFxStronglyTypedModel project you will see that there are a number of T4 templates (.tt files) it is these templates that build the new API so when you compile the project you are actually compiling the new API

which incidentially gives you the ability to modify it if you wanted to, cool!

To make using the new API simple, add a new C# Console project to the solution (if you want to use another .Net language you can but this post is C#).

The first thing we want to do is open a dacpac and grab a copy of the new model:

var model = new TSqlTypedModel(args[0]);

We will pass two arguments into our console app and I have ommitted the code to validate that the arguments are valid and available (see CheckArgs in the sample on https://github.com/GOEddie/Dac-To-tSQLt-Tests). The first argument is

the path to the dacpac and the second is the path to the output folder where we will write the schemas and the tests themselves).

var procedures = model.GetObjects(DacQueryScopes.UserDefined);

So far this is similar to the old way of getting all procedures except in this case we have an IEnumerable of TSqlProcedure instead of TSqlObject which, believe me, makes a massive difference.

I will cover the rest of the useful parts to the new API before we dig into using the ScriptDom to actually create the T-SQL code. To create the schema, all we need to do is grab the name of the procedure we want to test which is

straight forward (that has always been pretty simple):

procedure.Name.Parts.Last()

To generate the test, we need to know the name of the procedure, which parameters it has so we can create DECLARE statements for each of these, we need to know the names as well as the types of these and then we need to know what tables

are referenced by the body of the procedure - it is here that we realise that using an existing API is probably safer than trying to work it out ourselves!

To get a list of the parameters, it is simply a case of enumerating them:

foreach (var param in procedure.Parameters)
{
foreach (var type in param.DataType)
{
builder.AddParameter(param.Name.Parts.Last(), type.SqlDataType);
break;
}

}

By enumerating the parameters of the procedure, we again get a typed object which has a name (that is common to all TSqlObjects which the typed objects inherit from) and in this case a SqlDataType. I haven’t quite figured out why a

parameter returns a collection of DataTypes but I just grab the first and move on.

Getting a list of tables referenced by the procedure is just as simple:

foreach (var dependency in procedure.BodyDependencies)
{
if (dependency.ObjectType == ModelSchema.Table)
{
var table = new TSqlTable(dependency);
builder.AddTable(table.Name.ToSchemaObjectName());
}
}

We iterate through all of the BodyDependencies which gives us a TSqlObject, oh no back in the bad old world - well it is ok, if the type is ModelSchema.Table we can simply pass the TSqlObject to the TSqlTable constructor and we get

ourselves back into the comfy new world with a typed table, phew!

If we wanted to know more about the table such as the columns and their types we could just enumerate the TSqlTable.Columns which gives us objects of type TSqlColumn (see it really is easy!)

That is it for the new strongly typed model API, getting to this point without having to explain what the property classes are and how to get to a data type is a real joy!

ScriptDom

I have used and there are examples of how to use the ScriptDom to enumerate scripts and retrieve information like retrieving all the insert statements in a batch but there isn’t much written, that I can find, on how to do the opposite,

how to create statements using the ScriptDom so I will say up front that I do not know if this is the best way to create objects, I have tried a few ways and have settled on this approach for now but would welcome any advice anyone has

on improving the code to make it easier to write or read!

The first thing we will do is to create the schema and then the extended property, in the ScriptDom we create TSqlFragments which equate to a statement and as this is actually two statements, we create them and then use string.Format to

put them together.

I have a SchemaBuilder class which creates a “CreateSchemaStatement”:

var createSchema = new CreateSchemaStatement();
createSchema.Name = new Identifier(){Value = _procedureName};
createSchema.Owner = new Identifier(){Value = “dbo”};

We set the name and the owner for the schema and then we use a SqlScriptGenerator, these are versioned and for simplicity I will just use the Sql 2014 version, there will be nothing in our scripts that shouldn’t be backwards compatible

with older versions:

public class ScriptBuilder
{
private readonly Sql120ScriptGenerator _generator = new Sql120ScriptGenerator(new SqlScriptGeneratorOptions
{
AlignClauseBodies = true,
IncludeSemicolons = true,
IndentViewBody = true,
AsKeywordOnOwnLine = true,
IndentationSize = 4,
AlignColumnDefinitionFields = true
});

protected string GenerateScript(TSqlFragment fragment)
{
string script;
_generator.GenerateScript(fragment, out script);
return script;
}

protected IList GetTokens(TSqlFragment fragment)
{
return _generator.GenerateTokens(fragment);
}
}

The GenerateScript method calls the generator to create the script.

Fairly straight forward I think you will agree! Now to create the extended property, we start to see that building is quite involved, we want to end up with:

EXECUTE sp_addextendedproperty @name = ’tSQLt.TestClass’, @value = 1, @level0type = ‘SCHEMA’, @level0name = ‘TheProc’

So we need to create the EXECUTE statement, tell it the name of the proc and then create each of the parameters:

var execExtendedProperty = new ExecuteStatement();
execExtendedProperty.ExecuteSpecification = new ExecuteSpecification();

var name = new ChildObjectName();
name.Identifiers.Add(new Identifier(){Value = _procedureName});

var procedureReference = new ProcedureReference();
procedureReference.Name = “sp_addextendedproperty”.ToSchemaObjectName();

var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = procedureReference;

entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("@name", “tSQLt.TestClass”));
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("@value", 1));
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("@level0type", “SCHEMA”));
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("@level0name",

_procedureName));

execExtendedProperty.ExecuteSpecification.ExecutableEntity = entity;

return GenerateScript(execExtendedProperty);

The first thing to talk about is the use of Identifiers and names, different parts of the API’s use different objects to store names and identifiers - these aren’t compatible between the dac model API’s and the ScriptDom so you find

yourself converting them quite a lot, also the ScriptDom has a real penchant for using properties over constructors so you will often find yourself creating objects then setting properties, often doing this for child objects so it gets

quite messy quite quickly and involves quite a lot of cruft. An example is where you need to create a SchemaObjectName, you create a new one and it has a List of Identifiers, to create Identifiers you create the object and set the Value

to the string, so you end up with something like:

var nameObject = new SchemaObjectName();
nameObject.Identifiers.Add(
new Identifier()
{
Value = value
});

I have chosen to create some extension methods (see ObjectNameExtensions) to make it simpler, maybe inheriting from the objects and providing better constructors would be better.

The thing to you really need to understand with the ScriptDom is that you are not just building a statement but you are building up a chain of objects so there is quite a lot you need to do to get the full statement but it is all fairly

obvious what you need to add where and when. If you ever get stuck with not knowing how to add something or you are getting the wrong output, I find it helps to “go the other way” write the T-SQL as you want it to appear and then use

the ScriptDom to parse the T-SQL and see what you get back, this gives you the types of objects and their properties that you should create at each step.

This gives us the two statements but we need a batch, we could put them into different files but I prefer to keep the schema and extended property together so we simply do:

return string.Format("{0}\r\nGO\r\n{1}", GetSchema(), GetExtendedProperty());

We then move onto the actual test procedure itself and to create this we need to create the “CREATE PROCEDURE” that defines the test as a procedure and then add the statements we want to the body:

private void CreateTestProcedureDefinition(string testSchema, string testName)
{
var testProcedureReference = _testProcedure.ProcedureReference = new ProcedureReference();
testProcedureReference.Name = new SchemaObjectName();
testProcedureReference.Name.Identifiers.Add(testSchema.ToIdentifier());
testProcedureReference.Name.Identifiers.Add(testName.ToIdentifier());
}

This is an example of where creating the SchemaObjectName could be more succint and if this was one line of code, I think it would be more readable.

You will notice that the testProcedureReference is the result of setting the ProcedureReference

var testProcedureReference = _testProcedure.ProcedureReference = new ProcedureReference();

This isn’t code I would normally write but find myself doing it quite often with the ScriptDom.

Once we have the “CREATE PROCEDURE” statement we need to start adding the body of the procedure. The different parts are, the fake tables, the variable declarations, the exec of the proc under test and the assert at the end.

Fake Table

The fake table statements are EXECUTE statements with two parameters:

var fakeTable = new ExecuteStatement();
fakeTable.ExecuteSpecification = new ExecuteSpecification();

var procedureReference = new ProcedureReference();
procedureReference.Name = new SchemaObjectName();
procedureReference.Name.Identifiers.Add(“tSQLt”.ToIdentifier());
procedureReference.Name.Identifiers.Add(“FakeTable”.ToIdentifier());

var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = procedureReference;

entity.Parameters.Add(
ParametersHelper.CreateStoredProcedureParameter(string.Format("{0}", table.BaseIdentifier.Value)));
entity.Parameters.Add(
ParametersHelper.CreateStoredProcedureParameter(string.Format("{0}",

table.SchemaIdentifier.Value)));

fakeTable.ExecuteSpecification.ExecutableEntity = entity;

_testProcedure.StatementList.Statements.Add(fakeTable);

If we break this down, we have a new ExecuteStatement, then this statement has something called a ExecuteSpecification - this is a classic example of a statement, you have an object that is the statement and then that has a

*Specification which is where you actually set all the properties.

After we have the actual statement we add a ProcedureReference which points to a procedure name, defined using a SchemaObjectName, I would suggest creating some extension methods to handle this better:

var procedureReference = new ProcedureReference();
procedureReference.Name = new SchemaObjectName();
procedureReference.Name.Identifiers.Add(“tSQLt”.ToIdentifier());
procedureReference.Name.Identifiers.Add(“FakeTable”.ToIdentifier());

The ProcedureReference is actually a property on a ExecutableProcedureReference:

var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = procedureReference;

So far to create the execute statement, we have one ExecuteStatement, one ExecuteSpecification, one ProcedureReference, two identifiers (schema and name), one ExecutableProcedureReference, one ProcedureReferenceName and we don’t even

have the parameters yet!

To add the parameters I have created some helper methods:

public static ExecuteParameter CreateStoredProcedureParameter(string value)
{
return new ExecuteParameter
{
ParameterValue = new StringLiteral
{
Value = value
}
};
}

The ParameterValue doesn’t just take a StringLiteral, you can literally add any type of Literal! [/bad pun alert]

When you put it all together you end up with an EXECUTE statement which passes in a set of parameters:

EXECUTE tSQLt.FakeTable ‘TheTable’, ‘dbo’;

We then need to add the DECLARE statements, we will be reckless and allow the declare to also initialize the variable, I think we have had the ability long enough now!

DECLARE @param1 AS INT = 0;

The total code to create the declare is:

var declare = new DeclareVariableStatement();
var declareElement = new DeclareVariableElement();

var dataType = GetDataType(type);
declareElement.Value = GetDefaultValue(dataType);
declareElement.DataType = dataType;
declareElement.VariableName = name.ToIdentifier();
declare.Declarations.Add(declareElement);

_testProcedure.StatementList.Statements.Add(declare);

The thing to point out here is that when we set the Value, we pass in a ScalarExpression which is inherited by lots of cool things such as ScalarSubquery, StringLiteral, IntegerLiteral and BinaryExpression - I will let you explore these

but they are really exciting (BinaryExpression for example lets you create a set of IF statements).

We then need to execute the procedure under test, this is exactly the same as the FakeTable call except we pass in different parameters.

Finally we end up with the final EXECUTE statement which is the assert, again similar to the other EXECUTE statements.

Phew, this turned out to be a lot longer than I had intended and I would be surprised if anyone reads this far, well done if you have!

Lastly

I have created a repro with the entire sample:

https://github.com/GoEddie/Dacpac-2-tSQLtTests

and also the compiled version:

https://github.com/GoEddie/Dacpac-2-tSQLtTests/raw/master/Download/Dacpa…

It would be really cool if someone took this and made it into a VS add-in, I might do it myself one day if I feel the urge!

---

Comments:

Anonymous

May 1, 2015 - 15:03

CI integration?

I haven’t read this in great detail, but I think I get a flavour of what is going on.

Would this mean that SSDT tests could be picked up and ran by a CI server? allowing the developer to test locally and then the build server to run the same tests in the build environment after each commit?

It sounds like it would require a fair bit of scripting to convert all the tests each time plus introducing the risk that the conversion process fails. Assuming all that happens, this is a really useful thing to have!

Ed Elliott

May 1, 2015 - 16:24

Hi Anonymous (if that is your

Hi Anonymous (if that is your real name - ha ha),

Not quite, the point of this is that you can take a SSDT project with stored procedures and generate stub tSQLt unit tests - you would need to make the tests actually test something manually and then deploy the tests and run them.

If you are looking for help on deploying tSQLt tests then give me a shout on email or twitter (see the about page) and I can help you get set up.

ed