static void AssertMigration(MigrationScript migration, string version, string name, string description)
{
Assert.Equal(MetadataType.Migration, migration.Type);
Assert.Equal(version, migration.Version.Label);
Assert.Equal(name, migration.Name);
Assert.Equal(description, migration.Description);
}
public void ShouldReplaceMax()
{
var processor = new FixSqliteMax(8000);
var migrationScript = new MigrationScript {
Name = "test.sql",
Script = @"CREATE TABLE TestTable
(
Id UNIQUEIDENTIFIER PRIMARY KEY,
Title NVARCHAR(256) NOT NULL,
Content NVARCHAR(MAX) NULL
)"
};
var processedScript = processor.Process(null, migrationScript);
var expectedScript = @"CREATE TABLE TestTable
(
Id UNIQUEIDENTIFIER PRIMARY KEY,
Title NVARCHAR(256) NOT NULL,
Content NVARCHAR(8000) NULL
)";
Assert.Equal(expectedScript, processedScript.Script);
Assert.Equal(migrationScript.Name, processedScript.Name);
}
public void CalculateChecksum_should_not_return_null()
{
var script = new MigrationScript(TestContext.ValidMigrationScriptPath, "1.3.1", "Migration description");
string checksum = script.CalculateChecksum();
Assert.False(string.IsNullOrEmpty(checksum));
}
public MigrationScript Process(IDbConnection connection, MigrationScript script)
{
return(new MigrationScript
{
Name = script.Name,
Script = script.Script.Replace("(MAX)", $"({_valueOfMax.ToString()})"),
});
}
public void SaveMigration(MigrationScript migration, bool success)
{
Check.NotNull(migration, nameof(migration));
Execute(() =>
{
InternalSave(new MigrationMetadata(migration.Version.Label, migration.Description, migration.Name, MetadataType.Migration)
{
Checksum = migration.CalculateChecksum(),
Success = success
});
});
}
public void LoadSQL_returns_string_and_replace_placeholders()
{
var script = new MigrationScript(TestContext.ValidMigrationScriptPath, "1.3.1", "Migration description");
var placeholders = new Dictionary <string, string>
{
["${schema}"] = "my_schema",
["${nothing}"] = "nil",
};
string sql = script.LoadSqlStatements(placeholders, Encoding.UTF8, null).First();
Assert.DoesNotContain("${schema}", sql);
Assert.Contains("my_schema", sql);
}
public void SaveMigration_works()
{
var migration = new MigrationScript(TestContext.ValidMigrationScriptPath, "1.0.0", "desc");
using (var connection = TestUtil.GetInMemorySQLiteWrappedConnection())
{
var db = DatabaseHelperFactory.GetDatabaseHelper(DBMS.SQLite, connection);
var metadataTable = db.GetMetadataTable("", TestContext.DefaultMetadataTableName);
metadataTable.SaveMigration(migration, true);
Assert.True(metadataTable.GetAllMigrationMetadata().First().Id > 0);
}
}
public void Can_execute_one_create_Table_Script_in_empty_Db()
{
Connection.WithCleanDbConnection((c) =>
{
var migration = new MigrationScript
{
Name = "create bla table",
Script = "CREATE TABLE bla (Id varchar(1) NOT NULL)"
};
Migrator.ExecuteMigrations(c, new[] { migration }, true);
Assert.Empty(c.Query <object>("SELECT * FROM bla"));
});
}
/// <summary>
/// Returns a <see cref="List{SqlStatement}"/> given a <paramref name="migrationScript"/>.
/// </summary>
/// <remarks>
/// Placeholders are replaced by their values in the migration script.
/// The result is then parsed in sql statements: <see cref="Parse(string)"/>.
/// </remarks>
/// <param name="migrationScript"> The sql script to parse. </param>
/// <param name="placeholders"> The placeholders to replace. </param>
/// <returns> A <see cref="List{SqlStatement}"/> to execute individually in a command. </returns>
public virtual IEnumerable <SqlStatement> LoadSqlStatements(MigrationScript migrationScript, Dictionary <string, string> placeholders)
{
Check.NotNull(migrationScript, nameof(migrationScript));
Check.NotNull(placeholders, nameof(placeholders));
string sql = migrationScript.Content; // copy the content of the migration script
foreach (var entry in placeholders)
{
sql = sql.Replace(entry.Key, entry.Value);
}
return(Parse(sql, migrationScript.IsTransactionEnabled));
}
public void InitMigrationTable()
{
MigrationScript script = this._migrationScriptConverter.GetMigrationScript(this._initMigrationScriptFullPath);
if (script == null)
{
throw new FileNotFoundException("Initial Migration Table Script Not Found");
}
// make sure table exists
using (IDatabase db = this._databaseConnectionService.GetConnection())
{
db.Execute(new Sql(script.Sql));
}
}
public void UpdateChecksum_works()
{
var migration = new MigrationScript(TestContext.ValidMigrationScriptPath, "1.0.0", "desc");
using (var connection = TestUtil.GetInMemorySQLiteWrappedConnection())
{
var db = DatabaseHelperFactory.GetDatabaseHelper(DBMS.SQLite, connection);
var metadataTable = db.GetMetadataTable("", TestContext.DefaultMetadataTableName);
metadataTable.SaveMigration(migration, true);
var appliedMigration = metadataTable.GetAllMigrationMetadata().First();
metadataTable.UpdateChecksum(appliedMigration.Id, "Hi !");
Assert.Equal("Hi !", metadataTable.GetAllMigrationMetadata().First().Checksum);
}
}
public void SaveMigration(MigrationScript migration, bool success, TimeSpan?elapsed = null)
{
Check.NotNull(migration, nameof(migration));
Execute(() =>
{
string description = elapsed is null
? migration.Description
: $"{migration.Description} ({Math.Round(elapsed.Value.TotalMilliseconds)} ms)";
InternalSave(new MigrationMetadata(migration.Version?.Label, description, migration.Name, migration.Type)
{
Checksum = migration.CalculateChecksum(),
Success = success
});
});
}
public void Does_not_execute_the_same_script_twice()
{
Connection.WithCleanDbConnection((c) =>
{
var migration = new MigrationScript
{
Name = "create bla table",
Script = "CREATE TABLE bla (Id varchar(1) NOT NULL)"
};
Migrator.ExecuteMigrations(c, new[] { migration }, true);
Migrator.ExecuteMigrations(c, new[] { migration }, true);
// Migrations table + bla
Assert.Equal(2, c.Query <string>("SELECT Filename FROM Migrations").ToList().Count);
});
}
public void Upgrade(MigrationScript migrationScript)
{
if (migrationScript == null)
{
throw new ArgumentNullException("migrationScript");
}
ValidateMigration(migrationScript);
using (IDatabase db = this._databaseConnectionService.GetConnection())
{
db.BeginTransaction();
db.Execute(new Sql(migrationScript.Sql));
db.Execute(GetInsertStatement(migrationScript.Name, migrationScript.Version));
db.CompleteTransaction();
}
}
private void WriteDiffs(MigrationScript migration)
{
var diffs = Diff.Compute(migration.Migration.Content, migration.Script.Content);
foreach (var diff in diffs)
{
switch (diff.Operation)
{
case Operation.Equal:
Logger.Info(diff.Text);
break;
case Operation.Delete:
Logger.Write(ConsoleColor.Red, "(" + diff.Text + ")");
break;
case Operation.Insert:
Logger.Write(ConsoleColor.Green, diff.Text);
break;
}
}
}
public void GetAllMigrationMetadata_works()
{
var migrationScript = new MigrationScript(TestContext.ValidMigrationScriptPath, "1.0.0", "desc");
using (var connection = TestUtil.GetInMemorySQLiteWrappedConnection())
{
var db = DatabaseHelperFactory.GetDatabaseHelper(DBMS.SQLite, connection);
var metadataTable = db.GetMetadataTable("", TestContext.DefaultMetadataTableName);
metadataTable.SaveMigration(migrationScript, true);
var migrationMetadata = metadataTable.GetAllMigrationMetadata().First();
Assert.Equal(migrationScript.Description, migrationMetadata.Description);
Assert.Equal(migrationScript.Name, migrationMetadata.Name);
Assert.Equal(migrationScript.CalculateChecksum(), migrationMetadata.Checksum);
Assert.Equal(migrationScript.Version, migrationMetadata.Version);
Assert.True(migrationMetadata.Success);
Assert.Equal(string.Empty, migrationMetadata.InstalledBy);
Assert.True(migrationMetadata.Id > 0);
Assert.True(migrationMetadata.InstalledOn.Date == DateTime.Now.Date);
}
}
private void ValidateMigration(MigrationScript migrationScript)
{
switch (this._migrationStrategy)
{
case MigrationStrategy.Forward:
MigrationVersion currentVersion = this.GetCurrentVersion();
if (currentVersion.Version >= migrationScript.Version.Version)
{
throw new ApplicationException(string.Format("Attempt to target the database to version {0}; the current version {1} is higher",
migrationScript.Version.ToString(), currentVersion.ToString()));
}
break;
case MigrationStrategy.Version:
// ignore
break;
default:
throw new ApplicationException(string.Format("Migration strategy not recognized {0}", this._migrationStrategy.ToString()));
}
}
public bool CanProcess(IDbConnection connection, MigrationScript script)
{
return(connection.IsSqlite());
}
public void Run_all_SQLServer_integration_tests_work()
{
// Open a connection to the SQLServer database
var cnn = new SqlConnection($"Server=127.0.0.1;Database={DbName};User Id={TestContext.DbUser};Password={TestContext.DbPwd};");
cnn.Open();
Assert.True(cnn.State == ConnectionState.Open, "Cannot open a connection to the database.");
// Initiate a connection to the database
var wcnn = new WrappedConnection(cnn);
// Validate DBMS.SQLServer
Assert.Equal(DBMS.SQLServer, wcnn.GetDatabaseServerType());
// Init the DatabaseHelper
DatabaseHelper db = DatabaseHelperFactory.GetDatabaseHelper(DBMS.SQLServer, wcnn);
// Get default schema name
string metadataSchemaName = db.GetCurrentSchemaName();
Assert.True(metadataSchemaName == "dbo", "The default SQLServer schema should be 'dbo'.");
// Get MetadataTable
string metadataTableName = "changelog";
var metadata = db.GetMetadataTable(metadataSchemaName, metadataTableName);
// Create MetadataTable
Assert.False(metadata.IsExists(), "MetadataTable sould not already exist.");
Assert.True(metadata.CreateIfNotExists(), "MetadataTable creation failed.");
Assert.True(metadata.IsExists(), "MetadataTable sould exist.");
Assert.False(metadata.CreateIfNotExists(), "MetadataTable already exists. Creation should return false.");
Assert.True(metadata.GetAllMigrationMetadata().Count() == 0, "No migration metadata should be found.");
// Lock MetadataTable
metadata.Lock();
// Save EmptySchema metadata
metadata.Save(MetadataType.EmptySchema, "0", "Empty schema found.", metadataSchemaName);
Assert.False(metadata.CanDropSchema(metadataSchemaName), $"[{metadataSchemaName}] should not be droppable.");
Assert.True(metadata.CanEraseSchema(metadataSchemaName), $"[{metadataSchemaName}] should be erasable.");
// Add metadata migration
var migrationScript = new MigrationScript(TestContext.EmptyMigrationScriptPath, "1_3_2", "Migration_description");
metadata.SaveMigration(migrationScript, true);
var migrationMetadata = metadata.GetAllMigrationMetadata().FirstOrDefault();
Assert.True(migrationMetadata != null, "One migration metadata should be found.");
Assert.True(migrationMetadata.Version == migrationScript.Version, "Metadata version is not the same.");
Assert.True(migrationMetadata.Checksum == migrationScript.CalculateChecksum(), "Metadata checksum is not the same.");
Assert.True(migrationMetadata.Description == migrationScript.Description, "Metadata descritpion is not the same.");
Assert.True(migrationMetadata.Name == migrationScript.Name, "Metadata name is not the same.");
Assert.True(migrationMetadata.Success == true, "Metadata success is not true.");
Assert.True(migrationMetadata.Id > 0, "Metadata id is not set.");
// Assert.True(migrationMetadata.InstalledOn.Date == DateTime.Now.Date, "Installed date is not set.");
// Update checksum
metadata.UpdateChecksum(migrationMetadata.Id, "Hi !");
Assert.Equal("Hi !", metadata.GetAllMigrationMetadata().First().Checksum);
// Assert metadata schema is not empty
Schema metadataSchema = new SQLServerSchema(metadataSchemaName, wcnn);
Assert.False(metadataSchema.IsEmpty(), $"[{metadataSchemaName}] should not be empty.");
// Erase schema
metadataSchema.Erase();
Assert.True(metadataSchema.IsEmpty(), $"The schema [{metadataSchemaName}] should be empty.");
Assert.True(metadataSchema.IsExists(), $"The schema [{metadataSchemaName}] should exist.");
}
public void Run_all_PostgreSQL_integration_tests_work()
{
// Open a connection to the PostgreSQL database
var cnn = new NpgsqlConnection($"Server=127.0.0.1;Port={_pgFixture.HostPort};Database={_pgFixture.DbName};User Id={_pgFixture.DbUser};Password={_pgFixture.DbPwd};");
cnn.Open();
Assert.True(cnn.State == ConnectionState.Open, "Cannot open a connection to the database.");
// Initiate a connection to the database
var wcnn = new WrappedConnection(cnn);
// Validate DBMS.PostgreSQL
Assert.Equal(DBMS.PostgreSQL, wcnn.GetDatabaseServerType());
// Init the DatabaseHelper
DatabaseHelper db = DatabaseHelperFactory.GetDatabaseHelper(DBMS.PostgreSQL, wcnn);
// Test default schema name
Assert.True(db.GetCurrentSchemaName() == "public", "The default PostgreSQL schema should be 'public'.");
// Create schema
string metadataSchemaName = "My metadata schema";
Schema metadataSchema = new PostgreSQLSchema(metadataSchemaName, wcnn);
Assert.False(metadataSchema.IsExists(), $"The schema [{metadataSchemaName}] should not already exist.");
Assert.True(metadataSchema.Create(), $"Creation of the schema [{metadataSchemaName}] failed.");
Assert.True(metadataSchema.IsExists(), $"The schema [{metadataSchemaName}] should be created.");
Assert.True(metadataSchema.IsEmpty(), $"The schema [{metadataSchemaName}] should be empty.");
// Get MetadataTable
string metadataTableName = "changelog";
var metadata = db.GetMetadataTable(metadataSchemaName, metadataTableName);
// Create MetadataTable
Assert.False(metadata.IsExists(), "MetadataTable sould not already exist.");
Assert.True(metadata.CreateIfNotExists(), "MetadataTable creation failed.");
Assert.True(metadata.IsExists(), "MetadataTable sould exist.");
Assert.False(metadata.CreateIfNotExists(), "MetadataTable already exists. Creation should return false.");
Assert.True(metadata.GetAllMigrationMetadata().Count() == 0, "No migration metadata should be found.");
// Lock MetadataTable
metadata.Lock();
// Save NewSchema metadata
metadata.Save(MetadataType.NewSchema, "0", "New schema created.", metadataSchemaName);
Assert.True(metadata.CanDropSchema(metadataSchemaName), $"[{metadataSchemaName}] should be droppable.");
Assert.False(metadata.CanEraseSchema(metadataSchemaName), $"[{metadataSchemaName}] should not be erasable.");
// Add metadata migration
var migrationScript = new MigrationScript(TestContext.EmptyMigrationScriptPath, "1_3_2", "Migration_description");
metadata.SaveMigration(migrationScript, true);
var migrationMetadata = metadata.GetAllMigrationMetadata().FirstOrDefault();
Assert.True(migrationMetadata != null, "One migration metadata should be found.");
Assert.True(migrationMetadata.Version == migrationScript.Version, "Metadata version is not the same.");
Assert.True(migrationMetadata.Checksum == migrationScript.CalculateChecksum(), "Metadata checksum is not the same.");
Assert.True(migrationMetadata.Description == migrationScript.Description, "Metadata descritpion is not the same.");
Assert.True(migrationMetadata.Name == migrationScript.Name, "Metadata name is not the same.");
Assert.True(migrationMetadata.Success == true, "Metadata success is not true.");
Assert.True(migrationMetadata.Id > 0, "Metadata id is not set.");
Assert.True(migrationMetadata.InstalledOn.Date == DateTime.UtcNow.Date, "Installed date is not set.");
// Update checksum
metadata.UpdateChecksum(migrationMetadata.Id, "Hi !");
Assert.Equal("Hi !", metadata.GetAllMigrationMetadata().First().Checksum);
// Assert metadata schema is not empty
Assert.False(metadataSchema.IsEmpty(), $"[{metadataSchemaName}] should not be empty.");
// Erase schema
metadataSchema.Erase();
Assert.True(metadataSchema.IsEmpty(), $"The schema [{metadataSchemaName}] should be empty.");
Assert.True(metadataSchema.IsExists(), $"The schema [{metadataSchemaName}] should exist.");
// Drop schema
metadataSchema.Drop();
Assert.False(metadataSchema.IsExists(), $"The schema [{metadataSchemaName}] should not exist.");
// Acquisition du lock applicatif
while (true)
{
if (db.TryAcquireApplicationLock())
{
break;
}
Thread.Sleep(TimeSpan.FromSeconds(1));
}
Assert.True(db.TryAcquireApplicationLock(), "Cannot acquire application lock.");
// Can not acquire lock while it is taken by another connection
var cnn2 = new NpgsqlConnection($"Server=127.0.0.1;Port={_pgFixture.HostPort};Database={_pgFixture.DbName};User Id={_pgFixture.DbUser};Password={_pgFixture.DbPwd};");
var wcnn2 = new WrappedConnection(cnn2);
var db2 = DatabaseHelperFactory.GetDatabaseHelper(DBMS.PostgreSQL, wcnn2);
Assert.False(db2.TryAcquireApplicationLock(), "Application lock could not have been acquired.");
// Release the lock
db.ReleaseApplicationLock();
db.CloseConnection();
Assert.True(db.WrappedConnection.DbConnection.State == ConnectionState.Closed, "SQL connection should be closed.");
}
public MigrationScriptBuilder WithNext(int i)
{
_next = Default(i).MigrationScript;
return this;
}
