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); }
// 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)); }
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); }