public bool Exists(MigratorDatabase migratorDatabase)
{
return(0 != migratorDatabase.SqlQuery <int>($@"
SELECT Count(*) FROM sys.tables AS tables
JOIN sys.schemas AS schemas on tables.schema_id = schemas.schema_id
WHERE concat(schemas.name, '.', tables.name) = '{TableName}' AND type = 'U'").Single());
}
public async Task SetVersionAsync(MigratorDatabase database, long version, CancellationToken cancellation = default)
{
if (await database.ExecuteAsync($"UPDATE {TableName} SET {DatabaseVersionColumnName} = {version}", cancellation: cancellation) == 0)
{
await database.ExecuteAsync($"INSERT INTO {TableName}(Version) VALUES({version})", cancellation : cancellation);
}
}
public async Task <bool> ExistsAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
return(0 != await database.SingleAsync <int>($@"
SELECT Count(*) FROM sys.tables AS tables
JOIN sys.schemas AS schemas on tables.schema_id = schemas.schema_id
WHERE concat(schemas.name, '.', tables.name) = '{TableName}' AND type = 'U'", cancellation : cancellation));
}
public void SetVersion(MigratorDatabase migratorDatabase, long version)
{
if (migratorDatabase.ExecuteSqlCommand($"UPDATE {TableName} SET {DatabaseVersionColumnName} = {version}") == 0)
{
migratorDatabase.ExecuteSqlCommand($"INSERT INTO {TableName}(Version) VALUES({version})");
}
}
public void CreateIfNotExisting(MigratorDatabase migratorDatabase)
{
if (!Exists(migratorDatabase))
{
Create(migratorDatabase);
}
}
private async Task ExecuteMigrationAsync(Migration migration, MigratorDatabase database, CancellationToken cancellation = default)
{
Interceptor?.PreMigrationStep(database.Name, migration);
await migration.ExecuteAsync(database, cancellation);
Interceptor?.PostMigrationStep(database.Name, migration);
}
public async Task <long> GetCurrentVersionWithLockAsync(MigratorDatabase database)
{
var version = await _versionTable.GetCurrentVersionWithLockAsync(database);
_cache.AddOrUpdate(database.Name, version, (key, value) => version);
return(version);
}
public long GetCurrentVersionWithLock(MigratorDatabase migratorDatabase)
{
var version = _versionTable.GetCurrentVersionWithLock(migratorDatabase);
_cache.AddOrUpdate(migratorDatabase.Name, version, (key, value) => version);
return(version);
}
public async Task CreateIfNotExistingAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
if (!await ExistsAsync(database, cancellation))
{
await CreateAsync(database, cancellation);
}
}
public Task CreateIfNotExistingAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
if (_cache.ContainsKey(database.Name))
{
return(Task.CompletedTask);
}
return(_versionTable.CreateIfNotExistingAsync(database, cancellation));
}
public void CreateIfNotExisting(MigratorDatabase migratorDatabase)
{
if (_cache.ContainsKey(migratorDatabase.Name))
{
return;
}
_versionTable.CreateIfNotExisting(migratorDatabase);
}
public override void Execute(MigratorDatabase migratorDatabase)
{
foreach (var commandText in ParseCommands())
{
using (var command = migratorDatabase.CreateCommand())
{
command.CommandText = commandText;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public long GetCurrentVersion(MigratorDatabase migratorDatabase)
{
if (_cache.TryGetValue(migratorDatabase.Name, out var version))
{
return(version);
}
version = _versionTable.GetCurrentVersion(migratorDatabase);
_cache.AddOrUpdate(migratorDatabase.Name, version, (key, value) => version);
return(version);
}
public async Task <long> GetCurrentVersionAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
if (_cache.TryGetValue(database.Name, out var version))
{
return(version);
}
version = await _versionTable.GetCurrentVersionAsync(database, cancellation);
_cache.AddOrUpdate(database.Name, version, (key, value) => version);
return(version);
}
private async Task MigrateAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
var migrations = LoadMigrationsIfNotLoaded();
var dbVersion = await GetDbVersionAsync(database, cancellation);
if (dbVersion >= _latestSchemaVersion)
{
return;
}
while (await MigrateNextAsync(dbVersion, database, migrations, cancellation))
{
}
}
private void Migrate(MigratorDatabase database)
{
LoadMigrationsIfNotLoaded();
var dbVersion = GetDbVersion(database);
if (dbVersion >= LatestSchemaVersion)
{
return;
}
while (MigrateNext(dbVersion, database))
{
}
;
}
public override async Task ExecuteAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
if (database == null)
{
throw new ArgumentNullException(nameof(database));
}
foreach (var commandText in CommandSeparator.Split(await _readToEndAsync()))
{
if (string.IsNullOrWhiteSpace(commandText))
{
continue;
}
await database.ExecuteAsync(commandText, 0, cancellation);
}
}
private async Task CreateAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
if (HasSchema)
{
await CreateSchemaIfNotExistingAsync(database, cancellation);
}
try
{
await database.ExecuteAsync($@"
CREATE TABLE {TableName} (
Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED,
[Version] BIGINT
)", cancellation : cancellation);
}
catch (SqlException e) when(e.Number == ThereIsAlreadyAnObjectNamedXxxInTheDatabase)
{
}
}
private void Create(MigratorDatabase migratorDatabase)
{
if (HasSchema)
{
CreateSchemaIfNotExisting(migratorDatabase);
}
try
{
migratorDatabase.ExecuteSqlCommand($@"
CREATE TABLE {TableName} (
Id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY CLUSTERED,
[Version] BIGINT
)");
}
catch (SqlException e) when(e.Number == ThereIsAlreadyAnObjectNamedXXXXInTheDatabase)
{
}
}
private async Task CreateSchemaIfNotExistingAsync(MigratorDatabase database, CancellationToken cancellation = default)
{
var schemaCount = await database.SingleAsync <int>($@"
SELECT Count(schema_name)
FROM information_schema.schemata
WHERE schema_name = '{SchemaName}'", cancellation : cancellation);
if (schemaCount == 1)
{
return;
}
try
{
await database.ExecuteAsync($"CREATE SCHEMA {SchemaName}", cancellation : cancellation);
}
catch (SqlException e) when(e.Number == ThereIsAlreadyAnObjectNamedXxxInTheDatabase)
{
}
}
private void CreateSchemaIfNotExisting(MigratorDatabase migratorDatabase)
{
var schemaCount = migratorDatabase.SqlQuery <int>($@"
SELECT Count(schema_name)
FROM information_schema.schemata
WHERE schema_name = '{SchemaName}'").Single();
if (schemaCount == 1)
{
return;
}
try
{
migratorDatabase.ExecuteSqlCommand($"CREATE SCHEMA {SchemaName}");
}
catch (SqlException e) when(e.Number == ThereIsAlreadyAnObjectNamedXXXXInTheDatabase)
{
}
}
private async Task <bool> MigrateNextAsync(long dbVersion, MigratorDatabase database, IList <Migration> migrations, CancellationToken cancellation = default)
{
using var task = await database.BeginTransactionAsync(cancellation);
dbVersion = await _versionTable.GetCurrentVersionWithLockAsync(database);
if (dbVersion >= _latestSchemaVersion)
{
return(false);
}
Interceptor?.PreMigration(database.Name, dbVersion, _latestSchemaVersion);
migrations = migrations
.Where(x => x.Version > dbVersion)
.ToList();
var firstMigration = migrations.First();
if (firstMigration.DisableTransaction)
{
using (var noTransactionDatabase = database.Clone())
await ExecuteMigrationAsync(migrations.First(), noTransactionDatabase, cancellation);
if (firstMigration.Version > dbVersion)
{
await _versionTable.SetVersionAsync(database, firstMigration.Version, cancellation);
}
await database.CommitAsync(cancellation);
if (migrations.Count == 1)
{
Interceptor?.PostMigration(database.Name, dbVersion, _latestSchemaVersion);
return(false);
}
return(true);
}
foreach (var migration in migrations)
{
cancellation.ThrowIfCancellationRequested();
if (migration.DisableTransaction)
{
await database.CommitAsync(cancellation);
return(true);
}
await ExecuteMigrationAsync(migration, database, cancellation);
if (migration.Version > dbVersion)
{
await _versionTable.SetVersionAsync(database, migration.Version, cancellation);
}
}
await database.CommitAsync(cancellation);
Interceptor?.PostMigration(database.Name, dbVersion, _latestSchemaVersion);
return(false);
}
public async Task SetVersionAsync(MigratorDatabase database, long version, CancellationToken cancellation = default)
{
await _versionTable.SetVersionAsync(database, version, cancellation);
_cache.AddOrUpdate(database.Name, version, (key, value) => version);
}
private async Task <long> GetDbVersionAsync(MigratorDatabase database, CancellationToken cancellation)
{
await _versionTable.CreateIfNotExistingAsync(database, cancellation);
return(await _versionTable.GetCurrentVersionAsync(database, cancellation));
}
public long GetCurrentVersion(MigratorDatabase migratorDatabase)
{
return(migratorDatabase.SqlQuery <long>(CurrentVersionQuery, SecondsToWaitOnFetchingTheDatabaseVersion).FirstOrDefault());
}
public abstract Task ExecuteAsync(MigratorDatabase database, CancellationToken cancellation = default);
public SqlQuery(string command, MigratorDatabase migratorDatabase)
{
_command = command ?? throw new ArgumentNullException(nameof(command));
_migratorDatabase = migratorDatabase ?? throw new ArgumentNullException(nameof(migratorDatabase));
}
public long GetCurrentVersionWithLock(MigratorDatabase migratorDatabase)
{
return(migratorDatabase.SqlQuery <long>(CurrentVersionQuery + " WITH (UPDLOCK, TABLOCK)", SecondsToWaitOnFetchingTheDatabaseVersion).FirstOrDefault());
}
public async Task <long> GetCurrentVersionWithLockAsync(MigratorDatabase database, CancellationToken cancellation = default) => await database.FirstOrDefaultAsync <long>(CurrentVersionQuery + " WITH (UPDLOCK, TABLOCK)", SecondsToWaitOnFetchingTheDatabaseVersion, cancellation);
public async Task <long> GetCurrentVersionAsync(MigratorDatabase database, CancellationToken cancellation = default) => await database.FirstOrDefaultAsync <long>(CurrentVersionQuery, SecondsToWaitOnFetchingTheDatabaseVersion, cancellation);