private string GetExtendedProperty(string schemaName)
{
var execExtendedProperty = new ExecuteStatement();
execExtendedProperty.ExecuteSpecification = new ExecuteSpecification();
var name = new ChildObjectName();
name.Identifiers.Add(new Identifier {
Value = _scripts
});
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", schemaName.UnQuote()));
execExtendedProperty.ExecuteSpecification.ExecutableEntity = entity;
return(GenerateScript(execExtendedProperty));
}
private void CreateAssertDefinition()
{
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("AssertEquals".ToIdentifier());
var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = procedureReference;
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("TRUE"));
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("FALSE"));
var messageParameter = new ExecuteParameter();
var messageValue = new StringLiteral {
IsNational = true, Value = "Error Not Implemented"
};
messageParameter.ParameterValue = messageValue;
entity.Parameters.Add(messageParameter);
fakeTable.ExecuteSpecification.ExecutableEntity = entity;
_testProcedure.StatementList.Statements.Add(fakeTable);
}
private void CreateFakeTableDefinition(ObjectIdentifier table)
{
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.GetSchema())));
entity.Parameters.Add(
ParametersHelper.CreateStoredProcedureParameter(string.Format("{0}", table.GetName())));
fakeTable.ExecuteSpecification.ExecutableEntity = entity;
_testProcedure.StatementList.Statements.Add(fakeTable);
}
/// <summary>
/// The CreateExecuteSql method "wraps" the provided statement script in an "sp_executesql" statement
/// Examples of statements that must be so wrapped include: stored procedures, views, and functions
/// </summary>
private static ExecuteStatement CreateExecuteSql(string statementScript)
{
// define a new Exec statement
ExecuteStatement executeSp = new ExecuteStatement();
ExecutableProcedureReference spExecute = new ExecutableProcedureReference();
executeSp.ExecuteSpecification = new ExecuteSpecification {
ExecutableEntity = spExecute
};
// define the name of the procedure that you want to execute, in this case sp_executesql
SchemaObjectName procName = new SchemaObjectName();
procName.Identifiers.Add(CreateIdentifier("sp_executesql", QuoteType.NotQuoted));
ProcedureReference procRef = new ProcedureReference {
Name = procName
};
spExecute.ProcedureReference = new ProcedureReferenceName {
ProcedureReference = procRef
};
// add the script parameter, constructed from the provided statement script
ExecuteParameter scriptParam = new ExecuteParameter();
spExecute.Parameters.Add(scriptParam);
scriptParam.ParameterValue = new StringLiteral {
Value = statementScript
};
scriptParam.Variable = new VariableReference {
Name = "@stmt"
};
return(executeSp);
}
public override void ExplicitVisit(ExecuteStatement node)
{
ExecuteSpecification executeSpec = node.ExecuteSpecification;
ExecutableProcedureReference executableEntity = (ExecutableProcedureReference)executeSpec.ExecutableEntity;
var tokenText = getTokenText(executableEntity.ProcedureReference);
StatementTargets.Add($"Execute SP: {tokenText}");
}
public override void ExplicitVisit(ExecutableProcedureReference node)
{
if (node.ProcedureReference.ProcedureReference.Name.BaseIdentifier.Value.StartsWith("usp", StringComparison.OrdinalIgnoreCase))
{
if (node.ProcedureReference.ProcedureReference.Name.SchemaIdentifier == null)
{
this.ProcedureReferences.Add(node);
}
}
}
private void CreateExecForProcUnderTest(ObjectIdentifier procUnderTest)
{
var calleeProcedure = new ProcedureReference();
calleeProcedure.Name = new SchemaObjectName();
_execProc.ExecuteSpecification = new ExecuteSpecification();
var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = calleeProcedure;
entity.ProcedureReference.ProcedureReference.Name = procUnderTest.ToSchemaObjectName();
_execProc.ExecuteSpecification.ExecutableEntity = entity;
}
private string GetExtendedProperty()
{
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);
}
private void CreateAssertDefinition()
{
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("AssertEquals".ToIdentifier());
var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = procedureReference;
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("TRUE"));
entity.Parameters.Add(ParametersHelper.CreateStoredProcedureParameter("FALSE"));
var messageParameter = new ExecuteParameter();
var messageValue = new StringLiteral {IsNational = true, Value = "Error Not Implemented"};
messageParameter.ParameterValue = messageValue;
entity.Parameters.Add(messageParameter);
fakeTable.ExecuteSpecification.ExecutableEntity = entity;
_testProcedure.StatementList.Statements.Add(fakeTable);
}
private void CreateFakeTableDefinition(SchemaObjectName table)
{
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);
}
private void CreateExecForProcUnderTest(SchemaObjectName procUnderTest)
{
var calleeProcedure = new ProcedureReference();
calleeProcedure.Name = new SchemaObjectName();
_execProc.ExecuteSpecification = new ExecuteSpecification();
var entity = new ExecutableProcedureReference();
entity.ProcedureReference = new ProcedureReferenceName();
entity.ProcedureReference.ProcedureReference = calleeProcedure;
entity.ProcedureReference.ProcedureReference.Name = procUnderTest;
_execProc.ExecuteSpecification.ExecutableEntity = entity;
}
public override void Visit(ExecutableProcedureReference node)
{
ExecutedProcedures.Add(node);
base.Visit(node);
}
public override void Visit(ExecutableProcedureReference node) { this.action(node); }
public override void ExplicitVisit(ExecutableProcedureReference fragment)
{
_fragments.Add(fragment);
}
public override void ExplicitVisit(ExecutableProcedureReference node)
{
// skip when unkown procedure. example: exec @pEventName @pParameter1, @pParameter2;
if (node.ProcedureReference.ProcedureReference == null)
{
return;
}
SchemaObjectName schemaObject = node.ProcedureReference.ProcedureReference.Name;
Identifier id = node.ProcedureReference.ProcedureReference.Name.SchemaIdentifier;
string schema;
if (schemaObject.SchemaIdentifier != null)
{
schema = schemaObject.SchemaIdentifier.Value;
}
else
{
// Assuming schema is dbo
schema = "dbo";
}
string procedure = node.ProcedureReference.ProcedureReference.Name.BaseIdentifier.Value;
TSqlObject procObject = model.GetObject(Procedure.TypeClass, new ObjectIdentifier(schema, procedure), DacQueryScopes.UserDefined);
if (procObject != null)
{
bool hasMissingMandatoryParams = false;
// Get the list of supplied parameters to match against.
IList <ExecuteParameter> suppliedParams = node.Parameters;
string missingParamString = "";
int paramIndex = 0;
// Get the parameters of the procedure
foreach (TSqlObject param in procObject.GetReferenced(Procedure.Parameters))
{
// Check if it has an extended property
object paramDefault = param.GetProperty(Parameter.DefaultExpression);
// Check if it is readonly
bool paramReadOnly = (bool)param.GetProperty(Parameter.ReadOnly);
if (paramDefault == null && !paramReadOnly)
{
// Check if we supplied it
bool suppliedMandatoryParam = false;
string paramName = param.Name.Parts[param.Name.Parts.Count - 1];
int paramsFound = 0;
foreach (ExecuteParameter suppliedParam in suppliedParams.ToList())
{
if (suppliedParam.Variable != null)
{
if (suppliedParam.Variable.Name == paramName)
{
suppliedMandatoryParam = true;
}
}
else
{
if (paramsFound == paramIndex)
{
suppliedMandatoryParam = true;
}
}
paramsFound++;
}
if (!suppliedMandatoryParam)
{
hasMissingMandatoryParams = true;
if (missingParamString == "")
{
missingParamString = paramName;
}
else
{
missingParamString += ", " + paramName;
}
}
}
paramIndex++;
}
if (hasMissingMandatoryParams)
{
ProcedureCalls.Add(node);
MissingParameters[node] = missingParamString;
}
}
}
