private void Load(ISettingsStorage settingsStorage) {
TargetType = settingsStorage.GetSetting(TargetTypeSettingName, PublishTargetType.Dacpac);
TargetDatabaseConnection = settingsStorage.GetSetting(TargetDatabaseConnectionSettingName, string.Empty);
TargetProject = settingsStorage.GetSetting(TargetProjectSettingName, string.Empty);
TableName = settingsStorage.GetSetting(TableNameSettingName, DefaultRCodeTableName);
CodePlacement = settingsStorage.GetSetting(CodePlacementSettingName, RCodePlacement.Inline);
QuoteType = settingsStorage.GetSetting(QuoteTypeSettingName, SqlQuoteType.None);
}
private void Load(ISettingsStorage settingsStorage)
{
TargetType = (PublishTargetType)settingsStorage.GetInteger(TargetTypeSettingName, (int)PublishTargetType.Dacpac);
TargetDatabaseConnection = settingsStorage.GetString(TargetDatabaseConnectionSettingName, string.Empty);
TargetProject = settingsStorage.GetString(TargetProjectSettingName, string.Empty);
TableName = settingsStorage.GetString(TableNameSettingName, SqlSProcPublishSettings.DefaultRCodeTableName);
CodePlacement = (RCodePlacement)settingsStorage.GetInteger(CodePlacementSettingName, (int)RCodePlacement.Inline);
QuoteType = (SqlQuoteType)settingsStorage.GetInteger(QuoteTypeSettingName, (int)SqlQuoteType.None);
}
private void Load(ISettingsStorage settingsStorage)
{
TargetType = settingsStorage.GetSetting(TargetTypeSettingName, PublishTargetType.Dacpac);
TargetDatabaseConnection = settingsStorage.GetSetting(TargetDatabaseConnectionSettingName, string.Empty);
TargetProject = settingsStorage.GetSetting(TargetProjectSettingName, string.Empty);
TableName = settingsStorage.GetSetting(TableNameSettingName, DefaultRCodeTableName);
CodePlacement = settingsStorage.GetSetting(CodePlacementSettingName, RCodePlacement.Inline);
QuoteType = settingsStorage.GetSetting(QuoteTypeSettingName, SqlQuoteType.None);
}
// https://technet.microsoft.com/en-us/library/ms176027%28v=sql.105%29.aspx
public static string ToSqlName(this string name, SqlQuoteType quoteType) {
if(name.HasSpaces() && quoteType == SqlQuoteType.None) {
quoteType = SqlQuoteType.Bracket;
}
switch (quoteType) {
case SqlQuoteType.Quote:
return Invariant($"\"{name}\"");
case SqlQuoteType.Bracket:
return Invariant($"[{name}]");
default:
return name;
}
}
public void Generate(string rFile, RCodePlacement codePlacement, SqlQuoteType quoteType, string sprocName)
{
var fs = new WindowsFileSystem();
var settings = new SqlSProcPublishSettings();
var g = new SProcProjectFilesGenerator(_pss, fs);
var targetProjItem = Substitute.For <EnvDTE.ProjectItem>();
var targetProjItems = Substitute.For <EnvDTE.ProjectItems>();
targetProjItem.ProjectItems.Returns(targetProjItems);
var rootProjItems = Substitute.For <EnvDTE.ProjectItems>();
rootProjItems.Item("R").Returns((EnvDTE.ProjectItem)null);
rootProjItems.AddFolder("R").Returns(targetProjItem);
_project.ProjectItems.Returns(rootProjItems);
var templateFile = Path.Combine(_files.DestinationPath, Path.GetFileNameWithoutExtension(rFile) + SProcFileExtensions.SProcFileExtension);
var sprocFiles = new string[] {
Path.Combine(_files.DestinationPath, rFile),
Path.Combine(_files.DestinationPath, Path.GetFileNameWithoutExtension(rFile) + SProcFileExtensions.QueryFileExtension),
templateFile
};
settings.CodePlacement = codePlacement;
settings.QuoteType = quoteType;
g.Generate(settings, sprocFiles, _project);
rootProjItems.Received().AddFolder("R");
var targetFolder = Path.Combine(_files.DestinationPath, "R\\");
var rFilePath = Path.Combine(targetFolder, rFile);
var sprocFile = Path.ChangeExtension(Path.Combine(targetFolder, sprocName), ".sql");
targetProjItem.ProjectItems.Received().AddFromFile(sprocFile);
if (codePlacement == RCodePlacement.Table)
{
targetProjItem.ProjectItems.Received().AddFromFile(Path.Combine(targetFolder, SProcProjectFilesGenerator.PostDeploymentScriptName));
targetProjItem.ProjectItems.Received().AddFromFile(Path.Combine(targetFolder, SProcProjectFilesGenerator.CreateRCodeTableScriptName));
}
var mode = codePlacement == RCodePlacement.Inline ? "inline" : "table";
var baseline = fs.ReadAllText(Path.Combine(_files.DestinationPath, Invariant($"{Path.GetFileNameWithoutExtension(rFile)}.{mode}.baseline.sql")));
string actual = fs.ReadAllText(sprocFile);
BaselineCompare.CompareStringLines(baseline, actual);
}
// https://technet.microsoft.com/en-us/library/ms176027%28v=sql.105%29.aspx
public static string ToSqlName(this string name, SqlQuoteType quoteType)
{
if (name.HasSpaces() && quoteType == SqlQuoteType.None)
{
quoteType = SqlQuoteType.Bracket;
}
switch (quoteType)
{
case SqlQuoteType.Quote:
return(Invariant($"\"{name}\""));
case SqlQuoteType.Bracket:
return(Invariant($"[{name}]"));
default:
return(name);
}
}
public void Generate(string rFile, RCodePlacement codePlacement, SqlQuoteType quoteType, string sprocName) {
var fs = new FileSystem();
var settings = new SqlSProcPublishSettings();
var g = new SProcProjectFilesGenerator(_pss, fs);
var targetProjItem = Substitute.For<EnvDTE.ProjectItem>();
var targetProjItems = Substitute.For<EnvDTE.ProjectItems>();
targetProjItem.ProjectItems.Returns(targetProjItems);
var rootProjItems = Substitute.For<EnvDTE.ProjectItems>();
rootProjItems.Item("R").Returns((EnvDTE.ProjectItem)null);
rootProjItems.AddFolder("R").Returns(targetProjItem);
_project.ProjectItems.Returns(rootProjItems);
var templateFile = Path.Combine(_files.DestinationPath, Path.GetFileNameWithoutExtension(rFile) + SProcFileExtensions.SProcFileExtension);
var sprocFiles = new string[] {
Path.Combine(_files.DestinationPath, rFile),
Path.Combine(_files.DestinationPath, Path.GetFileNameWithoutExtension(rFile) + SProcFileExtensions.QueryFileExtension),
templateFile };
settings.CodePlacement = codePlacement;
settings.QuoteType = quoteType;
g.Generate(settings, sprocFiles, _project);
rootProjItems.Received().AddFolder("R");
var targetFolder = Path.Combine(_files.DestinationPath, "R\\");
var rFilePath = Path.Combine(targetFolder, rFile);
var sprocFile = Path.ChangeExtension(Path.Combine(targetFolder, sprocName), ".sql");
targetProjItem.ProjectItems.Received().AddFromFile(sprocFile);
if (codePlacement == RCodePlacement.Table) {
targetProjItem.ProjectItems.Received().AddFromFile(Path.Combine(targetFolder, SProcProjectFilesGenerator.PostDeploymentScriptName));
targetProjItem.ProjectItems.Received().AddFromFile(Path.Combine(targetFolder, SProcProjectFilesGenerator.CreateRCodeTableScriptName));
}
var mode = codePlacement == RCodePlacement.Inline ? "inline" : "table";
var baseline = fs.ReadAllText(Path.Combine(_files.DestinationPath, Invariant($"{Path.GetFileNameWithoutExtension(rFile)}.{mode}.baseline.sql")));
string actual = fs.ReadAllText(sprocFile);
BaselineCompare.CompareStringLines(baseline, actual);
}
public void ToSqlName(string name, string expected, SqlQuoteType quoteType) {
var actual = name.ToSqlName(quoteType);
actual.Should().Be(expected);
}
private string FillSprocTableTemplate(string rFilePath, string sprocName, string codeTableName, SqlQuoteType quoteType)
{
var sprocTemplateFile = rFilePath.ToSProcFilePath();
var sprocTemplate = GetSqlFileContent(sprocTemplateFile);
var declarations = Invariant(
$@"BEGIN
DECLARE @RCodeQuery NVARCHAR(max);
DECLARE @RCode NVARCHAR(max);
DECLARE @ParmDefinition NVARCHAR(max);
SET @RCodeQuery = N'SELECT @RCodeOUT = RCode FROM {codeTableName.ToSqlName(quoteType)} WHERE SProcName = ''{sprocName}''';
SET @ParmDefinition = N'@RCodeOUT NVARCHAR(max) OUTPUT';
EXEC sp_executesql @RCodeQuery, @ParmDefinition, @RCodeOUT=@RCode OUTPUT;
SELECT @RCode;
");
sprocTemplate = sprocTemplate.Replace("BEGIN", declarations);
sprocTemplate = sprocTemplate.Replace("N'_RCODE_'", "@RCode");
var sqlQuery = GetSqlFileContent(rFilePath.ToQueryFilePath()).Trim();
return(sprocTemplate.Replace(InputQueryTemplate, sqlQuery));
}
public void ToSqlName(string name, string expected, SqlQuoteType quoteType)
{
var actual = name.ToSqlName(quoteType);
actual.Should().Be(expected);
}
private string FillSprocTableTemplate(string rFilePath, string sprocName, string codeTableName, SqlQuoteType quoteType) {
var sprocTemplateFile = rFilePath.ToSProcFilePath();
var sprocTemplate = GetSqlFileContent(sprocTemplateFile);
var declarations = Invariant(
$@"BEGIN
DECLARE @RCodeQuery NVARCHAR(max);
DECLARE @RCode NVARCHAR(max);
DECLARE @ParmDefinition NVARCHAR(max);
SET @RCodeQuery = N'SELECT @RCodeOUT = RCode FROM {codeTableName.ToSqlName(quoteType)} WHERE SProcName = ''{sprocName}''';
SET @ParmDefinition = N'@RCodeOUT NVARCHAR(max) OUTPUT';
EXEC sp_executesql @RCodeQuery, @ParmDefinition, @RCodeOUT=@RCode OUTPUT;
SELECT @RCode;
");
sprocTemplate = sprocTemplate.Replace("BEGIN", declarations);
sprocTemplate = sprocTemplate.Replace("N'_RCODE_'", "@RCode");
var sqlQuery = GetSqlFileContent(rFilePath.ToQueryFilePath()).Trim();
return sprocTemplate.Replace(InputQueryTemplate, sqlQuery);
}
