protected override int ExecuteCommand(CommandLineApplication app, IConsole console)
{
string basePath = Parent.Path;
var scriptDir = Path.Combine(basePath, "scripts");
var config = SquealConfig.GetConfig(Parent);
var connectionString = config.ConnectionString;
if (String.IsNullOrEmpty(connectionString))
{
console.Error.WriteLine("Connection string not set. Use --connection-string option or set ConnectionString property in squeal.json.");
return(-1);
}
string scriptPath = Path.Combine(scriptDir, $"{ScriptName}.sql");
if (!File.Exists(scriptPath))
{
console.Error.WriteLine($"Script {ScriptName} does not exist.");
return(-1);
}
using (var conn = ConnectionFactory.CreateConnection(config))
{
console.WriteLine($"Executing script {ScriptName}...");
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
var template = File.ReadAllText(scriptPath);
var ps = Parameters.Select(p => p.Split("=")).ToDictionary(k => k[0], v => v[1]);
foreach (var key in ps.Keys)
{
template = template.Replace("${" + key + "}", ps[key]);
}
var rows = conn.Execute(template, transaction: trans);
console.WriteLine($"{rows} rows affected.");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine("An error occurred executing the script:");
Console.WriteLine("\tMessage: {0}", e.Message);
Console.WriteLine("\tScript: {0}", ScriptName);
Console.WriteLine("\tScript Path: {0}", scriptPath);
Console.WriteLine("Rolling back transaction");
trans.Rollback();
return(-1);
}
}
}
return(0);
}
public static IDbConnection CreateConnection(SquealConfig config)
{
string accessToken = null;
if (!String.IsNullOrEmpty(config.AzureAdAuthentication.TenantId) &&
!String.IsNullOrEmpty(config.AzureAdAuthentication.ClientId))
{
var credential = new ClientSecretCredential(config.AzureAdAuthentication.TenantId,
config.AzureAdAuthentication.ClientId, config.AzureAdAuthentication.ClientSecret);
var token = credential.GetToken(new Azure.Core.TokenRequestContext(
new string[] { "https://database.windows.net/.default" }));
accessToken = token.Token;
}
var connection = new SqlConnection(config.ConnectionString);
connection.AccessToken = accessToken;
return(connection);
}
public static SquealConfig GetConfig(Squeal root)
{
string basePath = root.Path;
var configPath = Path.Combine(basePath, "squeal.json");
SquealConfig config = new SquealConfig();
if (File.Exists(configPath))
{
config = JsonConvert.DeserializeObject <SquealConfig>(File.ReadAllText(configPath));
}
// Handle command line overrides
config.ConnectionString = !String.IsNullOrEmpty(root.ConnectionString)
? root.ConnectionString : config.ConnectionString;
config.AzureAdAuthentication.TenantId = !String.IsNullOrEmpty(root.AzureAdTenantId)
? root.AzureAdTenantId : config.AzureAdAuthentication.TenantId;
config.AzureAdAuthentication.ClientId = !String.IsNullOrEmpty(root.AzureAdClientId)
? root.AzureAdClientId : config.AzureAdAuthentication.ClientId;
config.AzureAdAuthentication.ClientSecret = !String.IsNullOrEmpty(root.AzureAdClientSecret)
? root.AzureAdClientSecret : config.AzureAdAuthentication.ClientSecret;
return(config);
}
protected override int ExecuteCommand(CommandLineApplication app, IConsole console)
{
string basePath = Parent.Path;
var migrationDir = Path.Combine(basePath, "migrations");
var config = SquealConfig.GetConfig(Parent);
var connectionString = config.ConnectionString;
if (String.IsNullOrEmpty(connectionString))
{
console.Error.WriteLine("Connection string not set. Use --connection-string option or set ConnectionString property in squeal.json.");
return(-1);
}
using (var conn = ConnectionFactory.CreateConnection(config))
{
conn.Open();
// Create metadata table if it doesn't exist
conn.Execute(@"IF NOT EXISTS(SELECT TOP 1 * FROM sys.tables WHERE NAME = N'__squealmetadata')
BEGIN
CREATE TABLE __squealmetadata
(
MigrationId INT PRIMARY KEY NOT NULL,
MigrationName NVARCHAR(256) NOT NULL,
DateApplied DATETIME NOT NULL
)
END");
// Get latest applied migration id
int currentMigrationId = conn.ExecuteScalar <int?>("SELECT TOP 1 MigrationId FROM __squealmetadata ORDER BY DateApplied DESC") ?? 0;
Console.WriteLine("Database is currently at migration {0}", currentMigrationId);
var migrationRepo = new MigrationRepository(migrationDir);
var operationsToApply = migrationRepo.GetOperations(currentMigrationId, TargetMigration);
if (operationsToApply.Any())
{
Console.WriteLine("Beginning update");
using (var trans = conn.BeginTransaction())
{
foreach (var operation in operationsToApply)
{
try
{
Console.Write("Executing {0} {1}...", operation.Type.ToString(), operation.Name);
conn.Execute(File.ReadAllText(operation.Path), transaction: trans);
if (operation.Type == OperationType.Upgrade)
{
conn.Execute("INSERT INTO __squealmetadata (MigrationId, MigrationName, DateApplied) " +
"VALUES (@Id, @Name, @Date)",
new { operation.Id, operation.Name, Date = DateTime.UtcNow }, transaction: trans);
}
else if (operation.Type == OperationType.Rollback)
{
conn.Execute("DELETE FROM __squealmetadata WHERE MigrationId = @Id",
new { operation.Id }, transaction: trans);
}
Console.WriteLine(" complete");
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine("An error occurred executing the operation:");
Console.WriteLine("\tMessage: {0}", e.Message);
Console.WriteLine("\tMigration: {0}", operation.Name);
Console.WriteLine("\tScript Path: {0}", operation.Path);
Console.WriteLine("Rolling back transaction");
trans.Rollback();
return(-1);
}
}
trans.Commit();
}
}
else
{
Console.WriteLine("Database is already up to date");
}
}
return(0);
}