public void ScanReturnValues_ForExtraNewlinesAndWhiteSpace_Ignores()
{
// Setup
var scanner = new StoredProcedureReturnValueScanner();
var sqlParser = new TSql120Parser(false);
var outReport = new StoredProcedureReport
{
ReturnValues = new List <ReturnSqlReportEntry>()
};
var sql = SqlSamples.LotsOfWhitespace;
var parsedSql =
sqlParser.Parse(new StringReader(sql), out var parseErrors);
var script = parsedSql as TSqlScript;
var createSpStatement =
script?.Batches[0].Statements[0] as CreateProcedureStatement;
// Act
scanner.ScanReturnValues(outReport, createSpStatement);
// Assert
Assert.That(parseErrors.Count, Is.EqualTo(0));
Assert.That(outReport.ReturnValues.Count, Is.EqualTo(2));
Assert.That(outReport.ReturnValues[0].ReturnValueName, Is.EqualTo("'Hello' AS SomeName"));
Assert.That(outReport.ReturnValues[1].ReturnValueName, Is.EqualTo("'Word555' AS SomeWord"));
}
public void SerializeSqlReportElement(StoredProcedureReport entry)
{
var paramSerializer =
new XmlSerializer(typeof(StoredProcedureReport));
paramSerializer.Serialize(_xmlWriter, entry, _ns);
}
private void CheckForMissingReturnValues(StoredProcedureReport masterSp,
StoredProcedureReport newSp, List <string> errors)
{
var count = 1;
var isChangedLiteral = false;
foreach (var masterReturnValue in masterSp.ReturnValues)
{
var newReturnValue = newSp.ReturnValues.FirstOrDefault(
x => Regex.Replace(x.ReturnValueName, @"\s+", " ") == Regex.Replace(masterReturnValue.ReturnValueName, @"\s+", " "));
if (masterReturnValue.IsLiteral)
{
if (newSp.ReturnValues.Count >= count &&
newSp.ReturnValues[count - 1].IsLiteral &&
newSp.ReturnValues[count - 1].ColumnNamedAs == masterReturnValue.ColumnNamedAs)
{
isChangedLiteral = true;
}
}
if (newReturnValue == null &&
!isChangedLiteral)
{
errors.Add($"{masterSp.SpUniqueName}\\{masterReturnValue.ReturnValueName}|existing return value is missing from new code");
}
count++;
}
}
public void CompareReports_ForDuplicatedSp_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2,
sp3
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\|sp defined 2 times"));
}
public void CompareReports_ForNewNonDefaultInNewSp_DoesNotReturnError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2,
sp3
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
private void CheckForNewNonDefaultedParameters(StoredProcedureReport masterSp,
StoredProcedureReport newSp, List <string> errors)
{
var newParams =
newSp.Parameters.Where(
x => masterSp.Parameters.All(y => x.ParameterName != y.ParameterName));
errors.AddRange(from newParam in newParams where !newParam.IsDefaulted select $"{newSp.SpUniqueName}\\{newParam.ParameterName}|new parameter has no default");
}
public void CompareReports_ForMultipleSelectsWithNewValuesAtEnd_ReturnsNoErrors()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.SomeAmountExt", 2, false, ""),
new ReturnSqlReportEntry("C.NameExt", 2, false, ""),
new ReturnSqlReportEntry("C.IdExt", 2, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.SomethingElse", 1, false, ""),
new ReturnSqlReportEntry("C.SomeAmountExt", 2, false, ""),
new ReturnSqlReportEntry("C.NameExt", 2, false, ""),
new ReturnSqlReportEntry("C.IdExt", 2, false, ""),
new ReturnSqlReportEntry("C.SomethingElseExt", 2, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
private void CheckParameterOrder(StoredProcedureReport masterSp,
StoredProcedureReport newSp, List <string> errors)
{
if (newSp.Parameters.Count < masterSp.Parameters.Count)
{
return;
}
errors.AddRange(masterSp.Parameters.Where((t, i) => t.ParameterName != newSp.Parameters[i].ParameterName)
.Select(t => $"{masterSp.SpUniqueName}\\{t.ParameterName}|existing parameter is out of order"));
}
public void ScanReturnValues(StoredProcedureReport spReport,
CreateProcedureStatement sp)
{
var returnValueVisitor = new SelectElementVisitor();
var spText = ParseSpText(sp);
sp.Accept(returnValueVisitor);
foreach (var returnValue in returnValueVisitor.ReturnValues)
{
ParseSelectElement(returnValue, spText, spReport);
}
}
public void CompareReports_ForRenameStringLiteral_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello' AS TheName", 1, true, "TheName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello' AS DifferentName", 1, true, "DifferentName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\StoredProcedure1\\'Hello' AS TheName|existing return value is missing from new code"));
}
public void CompareReports_ForChangeFieldCase_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.Phone", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.PhonE", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\StoredProcedure1\\C.Phone|existing return value is missing from new code"));
}
private void CheckParameterType(StoredProcedureReport masterSp,
StoredProcedureReport newSp, List <string> errors)
{
var masterBigIntParameters = masterSp.Parameters
.Where(x => x.ParameterType.ToUpper() == SqlDbType.BigInt.ToUpperString());
var newShrunkParameters =
from newParam in newSp.Parameters
join oldParam in masterBigIntParameters
on newParam.ParameterName equals oldParam.ParameterName
where newParam.ParameterType.ToUpper() != SqlDbType.BigInt.ToUpperString()
select newParam;
errors.AddRange(newShrunkParameters.Select(shrunkParameter => $"{masterSp.SpUniqueName}\\{shrunkParameter.ParameterName}|existing BIGINT parameter was changed to {shrunkParameter.ParameterType}"));
}
public void CompareReports_ForBigIntToTinyInt_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "bigint", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "TINYINT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@id|existing BIGINT parameter was changed to TINYINT"));
}
public void CompareReports_ForChangeStringLiteralWithName_ReturnsNoError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello' AS TheName", 1, true, "TheName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("'Hello 123' AS TheName", 1, true, "TheName"),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
public void CompareReports_ForNoLongerDefaultedParam_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", false)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@data|parameter is defaulted in master but not in new code"));
}
public void CompareReports_StarReplacesWithParamNames_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("*", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("Param1", 1, false, ""),
new ReturnSqlReportEntry("Param2", 1, false, ""),
new ReturnSqlReportEntry("Param3", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("Database1\\dbo\\StoredProcedure1\\*|existing return value is missing from new code"));
}
public void WriteMasterReport_ForValidReport_WritesDataToFile()
{
// Setup
var xmlFactory = Substitute.For <IXmlStreamWrapperFactory>();
var fileWrapper = Substitute.For <IFileWrapper>();
var streamWriter = Substitute.For <IXmlStreamWriterWrapper>();
xmlFactory.CreateXmlWriter("c:\\output\\fakeReport1.xml")
.Returns(streamWriter);
var storedProcedureReport = new StoredProcedureReport()
{
Db = "MyDB",
Schema = "Schema1",
SpName = "SomeSp1",
Parameters = new List <ParamSqlReportEntry>()
{
new ParamSqlReportEntry("@Id", "INT", "N/A", false)
},
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("P.Name", 1, false, ""),
}
};
var sqlReport = new SqlReport
{
TimeStamp = "6/21/2019 6:45:00 PM",
StoredProcedures = new List <StoredProcedureReport>()
{
storedProcedureReport
}
};
var writer = new SqlReportWriter(xmlFactory, fileWrapper);
// Act
writer.WriteMasterReport("c:\\output\\fakeReport1.xml",
sqlReport);
// Assert
streamWriter.Received(1).WriteStartElement("SqlReport");
streamWriter.Received(1).WriteElementString("TimeStamp", Arg.Any <string>());
streamWriter.Received(1).SerializeSqlReportElement(storedProcedureReport);
streamWriter.Received(1).WriteEndElement();
streamWriter.Received(1).Flush();
}
public void CompareReports_ForBigIntRemoved_ReturnsCorrectError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "BIGINT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@id|existing parameter is missing from new code"));
}
private void CheckForRemovedParameters(StoredProcedureReport masterSp,
StoredProcedureReport newSp, List <string> errors)
{
foreach (var masterParam in masterSp.Parameters)
{
var newParam = newSp.Parameters.FirstOrDefault(
x => x.ParameterName == masterParam.ParameterName);
if (newParam == null)
{
errors.Add($"{masterSp.SpUniqueName}\\{masterParam.ParameterName}|existing parameter is missing from new code");
}
else if (masterParam.IsDefaulted && !newParam.IsDefaulted)
{
errors.Add($"{masterSp.SpUniqueName}\\{masterParam.ParameterName}|parameter is defaulted in master but not in new code");
}
}
}
public void CompareReports_SelectStatementRemoved_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("A.Name1", 1, false, "Name1"),
new ReturnSqlReportEntry("A.Name2", 1, false, "Name2")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1
}
};
var sp2 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp2
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(2));
Assert.That(errors[0], Is.EqualTo("Database1\\dbo\\StoredProcedure1\\A.Name1|existing return value is missing from new code"));
Assert.That(errors[1], Is.EqualTo("Database1\\dbo\\StoredProcedure1\\A.Name2|existing return value is missing from new code"));
}
private void ScanSp(TSqlScript tSqlScript, string fullPath,
IgnoreList ignoreList)
{
var parameterScanner = new StoredProcedureParameterScanner();
var returnValueScanner = new StoredProcedureReturnValueScanner();
var solutionSchemaOffset = 3;
var dbOffset = 4;
var scriptParts = fullPath.Split('\\');
var schema = scriptParts[scriptParts.Length - solutionSchemaOffset];
var db = scriptParts[scriptParts.Length - dbOffset];
if (ignoreList.IsSchemaIgnored(db, schema))
{
return;
}
foreach (var batch in tSqlScript.Batches)
{
foreach (var statement in batch.Statements)
{
if (!(statement is CreateProcedureStatement sp))
{
continue;
}
var spFormattedName =
sp.ProcedureReference.Name.BaseIdentifier.Value;
var spReport =
new StoredProcedureReport(db, schema, spFormattedName);
parameterScanner.ScanSpParameters(spReport, sp);
returnValueScanner.ScanReturnValues(spReport, sp);
_sqlReport.StoredProcedures.Add(spReport);
}
}
}
public void ScanSpParameters(StoredProcedureReport spReport,
CreateProcedureStatement sp)
{
if (sp.Parameters.Count == 0)
{
return;
}
foreach (var p in sp.Parameters)
{
var isDefaulted = p.Value != null;
var length = ParseLength(p);
var entry =
new ParamSqlReportEntry(p.VariableName.Value,
p.DataType.Name.BaseIdentifier.Value,
length,
isDefaulted);
spReport.Parameters.Add(entry);
}
}
private void ParseSelectElement(SelectElementWrapper result,
string spText, StoredProcedureReport spReport)
{
var valueName =
spText.Substring(result.SelectElementHolder.StartOffset,
result.SelectElementHolder.FragmentLength);
valueName =
valueName
.Replace("\r", " ")
.Replace("\n", " ")
.Replace("\t", " ");
valueName =
Regex.Replace(valueName, @"\s+", " ");
var entry =
new ReturnSqlReportEntry(valueName, result.QueryExpressionId,
result.IsLiteral, result.ColumnName);
spReport.ReturnValues.Add(entry);
}
private void CheckReturnValueOrder(StoredProcedureReport masterSp,
StoredProcedureReport newSp, List <string> errors)
{
var uniqueStatements =
masterSp.ReturnValues.Select(x => x.StatementId)
.Distinct()
.ToList();
foreach (var statementId in uniqueStatements)
{
var masterReturns =
masterSp.ReturnValues.Where(x => x.StatementId == statementId)
.ToList();
var newReturns =
newSp.ReturnValues.Where(x => x.StatementId == statementId)
.ToList();
if (newReturns.Count < masterReturns.Count)
{
return;
}
for (var i = 0; i < masterReturns.Count; i++)
{
if (newReturns[i].ReturnValueName == masterReturns[i].ReturnValueName ||
masterReturns[i].IsLiteral)
{
continue;
}
if (newReturns.Any(x => x.ReturnValueName == masterReturns[i].ReturnValueName))
{
errors.Add(
$"{masterSp.SpUniqueName}\\{masterSp.ReturnValues[i].ReturnValueName}|existing return value is out of order");
}
}
}
}
public void CompareReports_ForDuplicatedSpWithDifferentValues_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp4 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp3,
sp4
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(3));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@id|new parameter has no default"));
Assert.That(errors[1], Is.EqualTo("DB1\\dbo\\LoadSomething1\\@data|existing parameter is out of order"));
Assert.That(errors[2], Is.EqualTo("DB1\\dbo\\LoadSomething1\\|sp defined 2 times"));
}
public void CompareReports_ForNewValueInMiddle_ReturnsError()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp2 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2
}
};
var sp3 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp4 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.NewValue", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, ""),
new ReturnSqlReportEntry("C.Phone", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp3,
sp4
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0], Is.EqualTo("Database2\\schema1\\StoredProcedure2\\C.Id|existing return value is out of order"));
}
public void CompareReports_ForSpCaseChanged_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@info", "VARCHAR", "200", false)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2,
sp3
}
};
var sp4 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp5 = new StoredProcedureReport("DB1", "dbo", "loadsomethinG2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false)
}
};
var sp6 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@info", "VARCHAR", "200", false)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp4,
sp5,
sp6
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(1));
Assert.That(errors[0],
Is.EqualTo("DB1\\dbo\\LoadSomething2:loadsomethinG2\\|sp was renamed with different case"));
}
public void CompareReports_ForArityChange_ReturnsError()
{
// Setup
var comparer = new ParamReportComparer();
var sp1 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp2 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false),
new ParamSqlReportEntry("@address", "VARCHAR", "200", true),
new ParamSqlReportEntry("@phone", "VARCHAR", "200", false)
}
};
var sp3 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", true),
new ParamSqlReportEntry("@style", "VARCHAR", "200", true)
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2,
sp3
}
};
var sp4 = new StoredProcedureReport("DB1", "dbo", "LoadSomething1")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", false),
new ParamSqlReportEntry("@data", "VARCHAR", "200", true)
}
};
var sp5 = new StoredProcedureReport("DB1", "dbo", "LoadSomething2")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@name", "VARCHAR", "200", false),
new ParamSqlReportEntry("@phone", "VARCHAR", "200", false),
new ParamSqlReportEntry("@address", "VARCHAR", "200", true)
}
};
var sp6 = new StoredProcedureReport("DB1", "dbo", "LoadSomething3")
{
Parameters = new List <ParamSqlReportEntry>
{
new ParamSqlReportEntry("@id", "INT", "N/A", true),
new ParamSqlReportEntry("@style", "VARCHAR", "200", true)
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp4,
sp5,
sp6
}
};
var errors = new List <string>();
// Act
comparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(2));
Assert.That(errors[0], Is.EqualTo("DB1\\dbo\\LoadSomething2\\@address|existing parameter is out of order"));
Assert.That(errors[1], Is.EqualTo("DB1\\dbo\\LoadSomething2\\@phone|existing parameter is out of order"));
}
public void CompareReports_ForSameSpNameInDifferentDb_ReturnsNoErrors()
{
// Setup
var returnComparer = new ReturnReportComparer();
var errors = new List <string>();
var sp1 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp2 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var sp3 = new StoredProcedureReport("Database3", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var masterReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp1,
sp2,
sp3
}
};
var sp4 = new StoredProcedureReport("Database1", "dbo", "StoredProcedure1")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry(
"[FieldB] = CAST(SUBSTRING(ISNULL([SomeTable].[text],''), 1, false, 1, false024) AS VARCHAR(1024))", 1, false, "FieldB")
}
};
var sp5 = new StoredProcedureReport("Database2", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var sp6 = new StoredProcedureReport("Database3", "schema1", "StoredProcedure2")
{
ReturnValues = new List <ReturnSqlReportEntry>()
{
new ReturnSqlReportEntry("C.SomeAmount", 1, false, ""),
new ReturnSqlReportEntry("C.Name", 1, false, ""),
new ReturnSqlReportEntry("C.Id", 1, false, "")
}
};
var newReport = new SqlReport
{
StoredProcedures = new List <StoredProcedureReport>
{
sp4,
sp5,
sp6
}
};
// Act
returnComparer.CompareReports(masterReport, newReport, errors);
// Assert
Assert.That(errors.Count, Is.EqualTo(0));
}
