DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.
I can't find where I saw it now, sadly. I'll try this structure tonight and see how I get on.
Do you think it's sensible to keep the constraints separate to the table creation as I've suggested? That way it ensures all the necessary tables are in place before foreign keys are added.
I found all the issues related to that... DbUp/DbUp#142 & DbUp/DbUp#346
I'm using 4.3.1. with EnsureDatabase.For.SqlDatabase(connectionString); Then I run a script that does a bunch of alter statements.
I was trying to do something like this:
var upgradeEngine = DeployChanges.To
.MySqlDatabase(Helpers.Utils.GetRDSConnectionString(ConfigurationService))
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole()
.WithTransaction()
.JournalTo(new MySqlTableJournal(() => new MySqlConnectionManager(Helpers.Utils.GetRDSConnectionString(ConfigurationService)), () => new CaptureLogsLogger(), "meta", "schemaversions"))
.Build();
[error]Script block number: -1; Message: Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.
[error]System.Data.SqlClient.SqlException (0x80131904): Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint you must include the NOT ENFORCED syntax as part of your statement.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
program.csusing DbUp;
using System;
using System.Configuration;
using System.Reflection;
namespace MIReport.Warehouse
{
class Program
{
static int Main(string[] args)
{
var connectionString = @"Server=tcp:" + args[0] + ",1433;Initial Catalog=" + args[1] + ";Persist Security Info=False;User ID=" + args[2] + ";Password=" + args[3] + ";MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';";
var upgrader = DeployChanges.To
.SqlDatabase(connectionString)
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole()
.LogScriptOutput()
.Build();
var result = upgrader.PerformUpgrade();
if (!result.Successful)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(result.Error);
Console.ResetColor();
return -1;
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Success!");
Console.ResetColor();
return 0;
}
}
}
i tried the above code, it's thrown out the sql exception for any command
Does DbUp for PostgreSQL work with NodaTime? It's generating a patch script but when I try to execute it I get the following error: System.NotSupportedException: The CLR type System.DateTime isn't natively supported by Npgsql or your PostgreSQL. To use it with a PostgreSQL composite you need to specify DataTypeName or to map it, please refer to the documentation.
I set: NpgsqlConnection.GlobalTypeMapper.UseNodaTime() before creating the upgrader as specified here: https://www.npgsql.org/doc/types/nodatime.html