DBUP - Running Script in Order

Question

I am trying out DBUP and can't seem to get it working. I have split my scripts into different directory and prefixed them with the number order they are suppose to be run. As far I know DBUP is suppose to run the script based on the Directory lexicographically, then process the files within. That is not happening it seems to be treating the files as if they are all in one big directory and running them in lexicographical order regardless of the the scripts directory. So I have data script trying to insert data into tables that have not been created yet.

var upgrader = DeployChanges.To.SqlDatabase(connectionString)
                                   .WithScriptsFromFileSystem(Path.Combine(dbScripts, "00-PreDeployment"))
                                   .WithScriptsFromFileSystem(Path.Combine(dbScripts, "01-Tables"))
                                   .WithScriptsFromFileSystem(Path.Combine(dbScripts, "02-Views"))
                                   .WithScriptsFromFileSystem(Path.Combine(dbScripts, "03-Functions"))
                                   .WithScriptsFromFileSystem(Path.Combine(dbScripts, "04-StoredProcedures"))
                                   .WithScriptsFromFileSystem(Path.Combine(dbScripts, "05-Data"))
                                   .JournalToSqlTable("dbo", "SchemaVersion")
                                   .LogToConsole()
                                   .Build();

Am I missing something? Is the solution just to prefix all my script with the directory order?

EXAMPLE: I have script name called Statuses.sql in the folder 01-Tables that create the Status table, and a script named Add_Status.sql in the 05-Data that populates the status table. What I expected to happen was the Status.sql get executed first then Add_Status.sql because of the directory "versioning" 01 comes before 05. But what was happening was Add_Status.sql was being excuted before the status table is created becuase it was treating every file as if they are in the same directory, 'A' comes before 'S'

If the tables are not created, your script isn't working probably? Never had this problem. Why specify each script to run? I have it setup to just run every script in a folder. DBUp keeps track of scripts that have been run or not. — David Libido, Jan 28 '23 at 01:12
@DavidLibido Sorry maybe I was not clear. What was happening was I have table called Statuses in the folder 01-Tables, and a script named Add_Status in the 05-Data. What I expected to happen was the Status table get executed first then Add_Status. But what was happening was Add_Status was being excuted before the status table is created — Jack Thor, Jan 28 '23 at 01:47

score 0 · Answer 1 · answered May 09 '23 at 19:47

By default, DbUp sorts the scripts first by RunGroupOrder (which it looks like you're not using) and then by Name use case-sensitive alphabetical ordering. Perhaps SqlScript.Name isn't being set the way you expect.

You can find out what's going on and override the default behavior by providing your own IScriptFilter:

DeployChanges.To...
    .WithScripts...
    .WithFilter(new MyScriptFilter())
    .Build()...

A basic MyScriptFilter:

public class MyScriptFilter : IScriptFilter {

    public IEnumerable<SqlScript> Filter(
            IEnumerable<SqlScript> sorted,
            HashSet<string> executedScriptNames,
            ScriptNameComparer comparer) {

        // Put a breakpoint in this function to inspect how it's been sorted.
        // You write code to reorder things here, too.

        return sorted
            .Where(s => s.SqlScriptOptions.ScriptType == ScriptType.RunAlways
                || !executedScriptNames.Contains(s.Name, comparer));

        // The above implementation is the same as DbUp's
        // built-in DefaultScriptFilter.
    }

}

DBUP - Running Script in Order

1 Answers1