SQLCMD Script Support

[ghost]

DbUp's SQL Server support should allow the processing of .sql scripts that make use of sqlcmd utility commands and scripting variables.

sqlcmd Scripting Variables

sqlcmd scripting variables are referenced using $(variableName). This is only slightly different than a DbUp variable reference which uses $variableName$. In order for DbUp to properly handle sqlcmd scripting variable references, two DbUp classes must be changed.

• DbUp.Engine.Preprocessors.StripSchemaPreprocessor.cs
  This class removes references to the "schema" variable when no value is provided by the caller. In addition to removing DbUp references to the variable, e.g., $schema$., it must also remove sqlcmd references, e.g., $(schema).
• DbUp.Engine.Preprocessors.VariableSubstitutionPreprocessor.cs.
  This class replaces variable references with their values from the variables array passed to the constructor. This class must recognize both sqlcmd and DbUp variable references for replacement.

Note that these changes should be non-breaking. That is to say that scripts that use the existing DbUp variables references will still be handled properly. In fact, the use of both DbUp-style variable references, e.g., $variableName$, and sqlcmd-style variable references, e.g., $(variableName) should be supported.

sqlcmd Commands

sqlcmd supports a number of commands. These commands are processed by sqlcmd before the Transact-SQL script is submitted to the SQL Server engine for processing.

These sqlcmd commands must start in column 1 of the line and should be prefaced with a colon character, ":". Technically, the colon character is optional in order to support backward compatibility with the osql command-line utility. However, in the real world, sqlcmd commands almost always begin with a colon character, e.g., :setvar or :r.

DbUp should not attempt to support the functionality of the sqlcmd commands. Rather, DbUp should convert every sqlcmd command to a double-dash comment and return it as part of the content of the script to be processed by SQL Server. So, the following sqlcmd commands

:setvar ScriptPath "C:\Scripts\"
:r $(ScriptPath)ScriptFile.sql

becomes the following.

-- :setvar ScriptPath "C:\Scripts\"
-- :r $(ScriptPath)ScriptFile.sql

This requires the following changes.

• DbUp.Support.SqlServer.SqlCommandReader.cs This class reads individual Transact-SQL commands out of a script file. When parsing the script text, this class must recognize asqlcmd` command and convert it to a comment before returning it.

My apologies for three pull requests on this issue, @JakeGinnivan... that's my inexperience again. I realize now that, instead of editing the markdown files directly on GitHub, I should have done the editing locally in the same branch as the code changes... then you'd just have one pull request to deal with. 😜 Next time for sure.

The good news is that I think I got the code changes right this time. All the unit tests are green and I even added a new BDDfy test for the sqlcmd variable substitution.

Looking forward to your feedback.

@JakeGinnivan please mark as an enhancement for 4.0 and assign to me

[jamiebarrow]

sqlcmd support would be great. bumping this :)

[HasAndries]

It would be awesome if SQLCMD was supported. Is there any chance of reviving this?

[michalporeba]

Well spotted @droyad, I'll have a look what was done previously, it seems like some work has been done, but it was too much for a single PR. I'll see if I can make something for the SQLCMD only.

[dazinator]

Any more news on this? I have a question about how this is going to work:

sqlcmd supports a number of commands. These commands are processed by sqlcmd before the Transact-SQL script is submitted to the SQL Server engine for processing.

DbUp should not attempt to support the functionality of the sqlcmd commands. Rather, DbUp should convert every sqlcmd command to a double-dash comment and return it as part of the content of the script to be processed by SQL Server.

If DbUp is actually just commenting out the sqlcmd commands in scripts before submitting them via ADO.Net, - I am missing the bit on how the sqlcmd commands themselves actually get executed - will DbUp do this also? How does that bit work?

[movedproved]

bumping it again. Would be great if SQLCMD supported.

[mduiveman84]

Bump again, the use of SQLCMD script support would be greatly appreciated

[AdrianJSClark]

@mduiveman84 I think that @dazinator's question above stands. How would this support work?

From what I can tell on the original issue, the request is simply to comment-out the SQLCMD statements. That should be a pretty simple script pre-processor to develop if you needed to do it yourself, however I'll happily review & merge a PR which introduces such a thing as an optional extension to DbUp.

When you say that the "...use of SQLCMD script support..." would be good, do you mean for DbUp to actually parse and implement the commands? If so, that's a much more complicated proposition and will require some extensive work I'd imagine. For starters there would need to be some additional work done on the script parser to properly identify the commands & all their parts. Then implementations made for each... You can see how this gets complicated.

I've never used SQLCMD statements so don't know what's available or how they are useful, so I at least am looking to those who would like this functionality to expand on what is necessary and how it would be beneficial. Someone who is familiar and uses SQLCMD would also need to implement it, as I don't believe I have the proper context to do so.

[mduiveman84]

@AdrianJSClark The same goes for me, I also haven'tworked witch SQLCMD statements before, only T-SQL. And for the latter, where I couldn't use Entity Framework Migrations to propogate changes to the target database, I used dbup.... primarily to register processed scripts. I understand now that adding support for SQLCMD in dbUp is complicated, which explains why this issue is open for such a long time :) I'm affraid I'm just a fan of dbUp and not the right person to implement this feature. For now, I resorted to writing a ps-script to suit my needs.

[hidegh]

I generate the script via SqlPackage.exe
With params:
/p:CommentOutSetVarDeclarations=True
/p:ScriptDatabaseOptions=False

So :setvar is not an issue for me, but the :on error and SQLCMD guard clause is.

public class SqlCmdCleaner : IScriptPreprocessor
{
    public string Process(string contents)
    {
        var lines = contents
            .Split(new[] { "\r\n", "\n" }, StringSplitOptions.None)
            .Where(l =>
                !l.TrimStart().StartsWith(":on error", StringComparison.OrdinalIgnoreCase) &&
                !l.TrimStart().StartsWith(":setvar", StringComparison.OrdinalIgnoreCase) &&
                !l.Contains("__IsSqlCmdEnabled", StringComparison.OrdinalIgnoreCase))
            .ToArray();

        return string.Join(Environment.NewLine, lines);
    }
}

var upgrader =
    DeployChanges.To
        .SqlDatabase(connectionString)
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        .WithPreprocessor(new SqlCmdCleaner())   // 👈 here
        .Build();

var result = upgrader.PerformUpgrade();