public async Task Update_InvalidDBInfo()
{
// Verify that update detects when the target database has a DBINFO
// table but that it's invalid.
var databaseName = GetUniqueDatabaseName();
var scripts = new string[]
{
"CREATE DATABASE ${database};",
"CREATE TABLE people (name text, age integer);"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
Assert.True(await schemaManager.CreateDatabaseAsync());
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(1, status.MaxVersion);
Assert.False(status.IsCurrent);
// Set a negative version in DBINFO and verify the exception.
await schemaManager.TargetConnection.ExecuteNonQueryAsync($"UPDATE {SchemaManager.DbInfoTableName} SET version = -1;");
await Assert.ThrowsAsync <SchemaManagerException>(async() => await schemaManager.GetStatusAsync());
}
}
}
public async Task Update_MissingScript()
{
// Verify that we detect the situation where the script folder has a database
// creation script but there's a version gap in the remaining scripts.
var databaseName = GetUniqueDatabaseName();
var scripts =
new string[]
{
"CREATE DATABASE ${database};",
"CREATE my_table-0 (name text);",
"CREATE my_table-1 (name text);",
"CREATE my_table-2 (name text);"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
File.Delete(Path.Combine(tempFolder.Path, "schema-2.script"));
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
await schemaManager.CreateDatabaseAsync();
await Assert.ThrowsAsync <FileNotFoundException>(async() => await schemaManager.UpgradeDatabaseAsync());
}
}
}
public async Task Create()
{
// Verify that the schema manager can create a database.
var databaseName = GetUniqueDatabaseName();
var scripts = new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
Assert.True(await schemaManager.CreateDatabaseAsync());
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(0, status.MaxVersion);
Assert.True(status.IsCurrent);
}
}
}
public async Task EmbeddedScripts()
{
// Verify that we can process scripts loaded from embedded resources.
var databaseName = GetUniqueDatabaseName();
using (var schemaManager = new SchemaManager(postgres, databaseName, Assembly.GetExecutingAssembly().GetResourceFileSystem("Test.Neon.Postgres.Scripts")))
{
await schemaManager.CreateDatabaseAsync();
await schemaManager.UpgradeDatabaseAsync();
}
}
public async Task Create_DatabaseExists()
{
// Verify that the schema manager database creation handles the case
// where the database already exists and has a proper DBINFO table.
var databaseName = GetUniqueDatabaseName();
var scripts = new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
Assert.True(await schemaManager.CreateDatabaseAsync());
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(0, status.MaxVersion);
Assert.True(status.IsCurrent);
// CreateDatabaseAsync() should return FALSE here
// because that database already exists.
Assert.False(await schemaManager.CreateDatabaseAsync());
status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(0, status.MaxVersion);
Assert.True(status.IsCurrent);
}
}
}
public async Task Update_MissingDBInfo()
{
// Verify that update detects when the target database doesn't
// have a DBINFO table.
var databaseName = GetUniqueDatabaseName();
var scripts = new string[]
{
"CREATE DATABASE ${database};",
"CREATE TABLE people (name text, age integer);"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
Assert.True(await schemaManager.CreateDatabaseAsync());
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(1, status.MaxVersion);
Assert.False(status.IsCurrent);
// Drop the DBINFO table for the test.
await schemaManager.TargetConnection.ExecuteNonQueryAsync($"DROP TABLE {SchemaManager.DbInfoTableName};");
status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsNoSchema, status.SchemaStatus);
await Assert.ThrowsAsync <SchemaManagerException>(async() => await schemaManager.UpgradeDatabaseAsync());
}
}
}
public async Task Updater_Error()
{
// Verify that we can detect when another updater appears to be
// failed due to a simulated script execution error.
var databaseName = GetUniqueDatabaseName();
var scripts =
new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
// Create the database.
await schemaManager.CreateDatabaseAsync();
var status = await schemaManager.GetStatusAsync();
Assert.True(status.IsCurrent);
// Update the DBINFO table to make it appear that another updater failed.
await schemaManager.TargetConnection.ExecuteNonQueryAsync($"UPDATE {SchemaManager.DbInfoTableName} SET updater = 'another-updater', update_start_utc = (now() at time zone 'utc'), update_finish_utc = NULL, error = 'Something bad happened!';");
}
}
// Create a new schema manager, attempt an update and verify that
// we detect the updater conflict.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (1);",
"UPDATE my_table SET version = 2;",
"UPDATE my_table SET version = 3;",
"UPDATE my_table SET version = 4;",
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.UpgradeError, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(4, status.MaxVersion);
Assert.Equal("Something bad happened!", status.Error);
// Attempt to apply the updates. This should fail because another updater
// appears to be updating.
await Assert.ThrowsAsync <SchemaManagerException>(async() => await schemaManager.UpgradeDatabaseAsync());
// Try updating again with [force=true]. It should work this time.
Assert.Equal(4, await schemaManager.UpgradeDatabaseAsync(force: true));
status = await schemaManager.GetStatusAsync();
Assert.Equal(4, status.Version);
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
}
public async Task Scripts_WithLeadingZeros()
{
// Verify that we support script file names with leading zeros in
// the version numbers.
// Create the initial database and verify that it's up to date.
var databaseName = GetUniqueDatabaseName();
var scripts =
new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsWithZerosAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
await schemaManager.CreateDatabaseAsync();
var status = await schemaManager.GetStatusAsync();
Assert.True(status.IsCurrent);
}
}
// Create a new schema manager with upgrade scripts and ensure that
// the updates are applied.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (1);",
"UPDATE my_table SET version = 2;",
"UPDATE my_table SET version = 3;",
"UPDATE my_table SET version = 4;",
};
using (var tempFolder = await PersistSchemaScriptsWithZerosAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(4, status.MaxVersion);
Assert.Equal(4, await schemaManager.UpgradeDatabaseAsync());
// Verify that the updates were actually applied.
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
// Add a couple additional upgrade scripts and verify.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (1);",
"UPDATE my_table SET version = 2;",
"UPDATE my_table SET version = 3;",
"UPDATE my_table SET version = 4;",
"UPDATE my_table SET version = 5;",
"UPDATE my_table SET version = 6;",
};
using (var tempFolder = await PersistSchemaScriptsWithZerosAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(4, status.Version);
Assert.Equal(6, status.MaxVersion);
Assert.Equal(6, await schemaManager.UpgradeDatabaseAsync());
// Verify that the updates were actually applied.
Assert.Equal(6, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
}
public async Task Update_Stop_Error()
{
// Verify that we're not allowed to stop at a version lower
// than the current database version.
var databaseName = GetUniqueDatabaseName();
var scripts =
new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
await schemaManager.CreateDatabaseAsync();
var status = await schemaManager.GetStatusAsync();
Assert.True(status.IsCurrent);
}
}
// Create a new schema manager with upgrade scripts and ensure that
// the updates are applied.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (1);",
"UPDATE my_table SET version = 2;",
"UPDATE my_table SET version = 3;",
"UPDATE my_table SET version = 4;",
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(4, status.MaxVersion);
// Apply all of the updates.
Assert.Equal(4, await schemaManager.UpgradeDatabaseAsync());
status = await schemaManager.GetStatusAsync();
Assert.Equal(4, status.Version);
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
// Verify that we're not allowed to stop at an update that's
// already been applied.
await Assert.ThrowsAsync <SchemaManagerException>(async() => await schemaManager.UpgradeDatabaseAsync(stopVersion: 2));
// Verify that the database version hasn't changed.
status = await schemaManager.GetStatusAsync();
Assert.Equal(4, status.Version);
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
}
public async Task Update_Stop()
{
// Verify that we can stop updates at a specific version.
var databaseName = GetUniqueDatabaseName();
var scripts =
new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
await schemaManager.CreateDatabaseAsync();
var status = await schemaManager.GetStatusAsync();
Assert.True(status.IsCurrent);
}
}
// Create a new schema manager with upgrade scripts and ensure that
// the updates are applied.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (1);",
"UPDATE my_table SET version = 2;",
"UPDATE my_table SET version = 3;",
"UPDATE my_table SET version = 4;",
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(4, status.MaxVersion);
// Verify that only the updates up to version=2 are applied.
Assert.Equal(2, await schemaManager.UpgradeDatabaseAsync(stopVersion: 2));
status = await schemaManager.GetStatusAsync();
Assert.Equal(2, status.Version);
Assert.Equal(2, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
// Apply the remaining updates and verify.
Assert.Equal(4, await schemaManager.UpgradeDatabaseAsync());
status = await schemaManager.GetStatusAsync();
Assert.Equal(4, status.Version);
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
}
public async Task Update_NotRequired()
{
// Verify that update does not apply updates that have already
// been applied.
var databaseName = GetUniqueDatabaseName();
var scripts =
new string[]
{
"CREATE DATABASE ${database};"
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
await schemaManager.CreateDatabaseAsync();
var status = await schemaManager.GetStatusAsync();
Assert.True(status.IsCurrent);
}
}
// Create a new schema manager with upgrade scripts and ensure that
// the updates are applied.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (1);",
"UPDATE my_table SET version = 2;",
"UPDATE my_table SET version = 3;",
"UPDATE my_table SET version = 4;",
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(0, status.Version);
Assert.Equal(4, status.MaxVersion);
Assert.Equal(4, await schemaManager.UpgradeDatabaseAsync());
// Verify that the updates were actually applied.
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
// Add a modify the upgrade scripts to set differnt values and then
// verify that the scripts weren't executed again during an upgrade.
scripts =
new string[]
{
"CREATE DATABASE ${database};",
@"CREATE TABLE my_table (version integer);
GO
INSERT INTO my_table (version) values (101);",
"UPDATE my_table SET version = 102;",
"UPDATE my_table SET version = 103;",
"UPDATE my_table SET version = 104;",
};
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsWithSchema, status.SchemaStatus);
Assert.Equal(4, status.Version);
Assert.Equal(4, status.MaxVersion);
Assert.Equal(4, await schemaManager.UpgradeDatabaseAsync());
// Verify that the updates were not applied.
Assert.Equal(4, (int)await schemaManager.TargetConnection.ExecuteScalarAsync("SELECT version FROM my_table;"));
}
}
}
public async Task Create_DatabaseExists_NoDBInfo()
{
// Verify that create throws an exception when the database already
// exists but doesn't have a valid DBINFO table.
var databaseName = GetUniqueDatabaseName();
var scripts = new string[]
{
"CREATE DATABASE ${database};"
};
await postgres.ExecuteNonQueryAsync($"CREATE DATABASE {databaseName};");
using (var tempFolder = await PersistSchemaScriptsAsync(scripts))
{
using (var schemaManager = new SchemaManager(postgres, databaseName, tempFolder.Path))
{
var status = await schemaManager.GetStatusAsync();
Assert.Equal(SchemaStatus.ExistsNoSchema, status.SchemaStatus);
Assert.Equal(-1, status.Version);
Assert.Equal(0, status.MaxVersion);
Assert.False(status.IsCurrent);
await Assert.ThrowsAsync <SchemaManagerException>(async() => await schemaManager.CreateDatabaseAsync());
}
}
}
