public void TestUninstall(
[ValueSource("ConnectionStrings")] string connectionString,
[ValueSource("Schemas")] IEnumerable <string> schema)
{
TestWithRollback(connectionString, connection =>
{
// try to install the schema and verify that they are there
Install(connection, schema);
VerifyObjectsAndRegistry(schema, connection);
// uninstall it
SchemaInstaller installer = new SchemaInstaller(connection);
installer.Uninstall(TestSchemaGroup);
// make sure the registry is empty
SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup);
Assert.IsTrue(!registry.Entries.Any());
// make sure all of the objects exist in the database
foreach (var schemaObject in schema.Select(s => new SchemaObject(s)))
{
Assert.False(schemaObject.Exists(connection), "Object {0} is not deleted from database", schemaObject.Name);
}
});
}
public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
{
// we can drop a udt unless it is used in a table
return connection.ExecuteScalarSql<int>(
"SELECT COUNT(*) FROM sys.types t JOIN sys.columns c ON (t.user_type_id = c.user_type_id) WHERE t.Name = @Name",
new Dictionary<string, object>() { { "Name", Name.Object } }) == 0;
}
private static void CreateOrModifyDbSchema()
{
var connStr = ConfigurationManager.ConnectionStrings["ad.util.GeneralPurposeTree"].ConnectionString;
if (!SchemaInstaller.DatabaseExists(connStr))
{
_logger.Info("ad.util.GeneralPurposeTree is about to create a database.");
SchemaInstaller.CreateDatabase(connStr);
_logger.Info("ad.util.GeneralPurposeTree has just created a database.");
}
using (var conn = new SqlConnection(connStr)) {
conn.Open();
var installer = new SchemaInstaller(conn);
installer.CreatingObject += SchemaInstaller_CreatingObject;
installer.CreatedObject += SchemaInstaller_CreatedObject;
installer.DroppingObject += SchemaInstaller_DroppingObject;
installer.DropFailed += SchemaInstaller_DropFailed;
_logger.Info("Database modification starting for database '{0}' and schema '{1}'.", conn.Database, DbSchemaName);
var saveCultureInfo = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
try {
var schema = new SchemaObjectCollection();
schema.Load(System.Reflection.Assembly.GetExecutingAssembly());
installer.Install(DbSchemaName, schema);
}
finally {
Thread.CurrentThread.CurrentCulture = saveCultureInfo;
}
_logger.Info("Database modification ended for database '{0}' and schema '{1}'.", conn.Database, DbSchemaName);
}
}
static void Main(string[] args)
{
SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder("Database=.;Initial Catalog=InsightTest;Integrated Security=true");
SchemaInstaller.CreateDatabase(connectionString.ConnectionString);
using (SqlConnection connection = new SqlConnection(connectionString.ConnectionString))
{
connection.Open();
// make sure our database exists
SchemaInstaller installer = new SchemaInstaller(connection);
new SchemaEventConsoleLogger().Attach(installer);
// load the schema from the embedded resources in this project
SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Load(Assembly.GetExecutingAssembly());
// install the schema
Console.WriteLine("Installing");
installer.Install("BeerGarten", schema);
// uninstall the schema
if (args.Length > 0 && args[0].ToUpperInvariant() == "UNINSTALL")
{
Console.WriteLine("Uninstalling");
installer.Uninstall("BeerGarten");
}
}
}
public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
{
// we can drop a function as long as there are no schemes using it
return connection.ExecuteScalarSql<int>(
"SELECT COUNT(*) FROM sys.partition_functions p JOIN sys.partition_schemes s ON (p.function_id = s.function_id) WHERE p.name = @Name",
new Dictionary<string, object>() { { "Name", Name.Object } }) == 0;
}
/// <summary>
/// Run a test and clean up the databases when complete.
/// </summary>
/// <param name="connectionString">The connection string for the database.</param>
/// <param name="action">The test to run.</param>
internal static void TestWithRollback(string connectionString, Action <RecordingDbConnection> action)
{
// make sure the database exists
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
// do all of the work in a transaction so we can clean up our changes
using (TransactionScope transaction = new TransactionScope())
using (SqlConnection connection = new SqlConnection(connectionString))
using (RecordingDbConnection recordingConnection = new RecordingDbConnection(connection))
{
recordingConnection.Open();
try
{
action(recordingConnection);
}
finally
{
Console.WriteLine("== BEGIN SCRIPT ============================");
Console.WriteLine(recordingConnection.ScriptLog.ToString());
Console.WriteLine("== END SCRIPT ============================");
}
}
}
public void DatabaseExistsReturnsTrueForInvalid([ValueSource("ConnectionStrings")] string connectionString)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
builder.InitialCatalog = "fhjasjkl";
Assert.False(SchemaInstaller.DatabaseExists(builder.ConnectionString));
}
public void DatabaseExistsReturnsTrueForMaster([ValueSource("ConnectionStrings")] string connectionString)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
builder.InitialCatalog = "master";
Assert.True(SchemaInstaller.DatabaseExists(builder.ConnectionString));
}
public void ShouldIndicateThatSchemaDoesNotExists()
{
var creator = new SchemaInstaller();
var result = creator.HangfireStorageSchemaExists("nonExistingSchema", ConnectionString);
Assert.False(result);
}
public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return 0 == connection.ExecuteScalarSql<int>(String.Format(@"
SELECT COUNT(*)
FROM sys.service_queues q
JOIN sys.service_queue_usages u ON (q.object_id = u.service_queue_id)
WHERE q.object_id = OBJECT_ID('{0}')",
Name.SchemaQualifiedObject));
}
public void ShouldCreateSchema()
{
var creator = new SchemaInstaller();
creator.InstallHangfireStorageSchema("hangfiretestschema", ConnectionString);
using var conn = SelectDialect <DbConnection>(() => new SqlConnection(ConnectionString), () => new NpgsqlConnection(ConnectionString));
Assert.AreEqual("hangfiretestschema", conn.ExecuteScalar <string>("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'hangfiretestschema'"));
}
public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return 0 == connection.ExecuteScalarSql<int>(String.Format(@"
SELECT COUNT(*)
FROM sys.service_contract_message_usages c
JOIN sys.service_message_types m ON (c.message_type_id = m.message_type_id)
WHERE m.name = '{0}'",
Name.Object));
}
private void SetUpDatabase(SqlConnection connection)
{
SchemaInstaller installer = new SchemaInstaller(connection);
SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Add("CREATE TABLE Beer ([id] [int] NOT NULL IDENTITY, [name] varchar(100))");
schema.Add("ALTER TABLE Beer ADD CONSTRAINT PK_Beer PRIMARY KEY ([ID])");
schema.Add("-- AUTOPROC All Beer");
installer.Install("default", schema);
}
public void ShouldIndicateThatSchemaExists()
{
var creator = new SchemaInstaller();
creator.InstallHangfireStorageSchema("schema", ConnectionString);
var result = creator.HangfireStorageSchemaExists("schema", ConnectionString);
Assert.True(result);
}
public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
{
// we can drop a scheme as long as there are no tables using it
return connection.ExecuteScalarSql<int>(@"SELECT COUNT(*) FROM sys.partition_schemes s
WHERE s.name = @Name AND (
s.data_space_id IN (SELECT data_space_id FROM sys.indexes) OR
s.data_space_id IN (SELECT lob_data_space_id FROM sys.tables) OR
s.data_space_id IN (SELECT filestream_data_space_id FROM sys.tables))
", new Dictionary<string, object>() { { "Name", Name.Object } }) == 0;
}
/// <summary>
/// Run a test and clean up the databases when complete.
/// </summary>
/// <param name="connectionString">The connection string for the database.</param>
/// <param name="action">The test to run.</param>
protected static void TestWithDrop(string connectionString, Action action)
{
try
{
action();
}
finally
{
SchemaInstaller.DropDatabase(connectionString);
}
}
public void ShouldCreateSchemaWithDefaultSchemaName()
{
var creator = new SchemaInstaller();
creator.InstallHangfireStorageSchema("", ConnectionString);
var expected = SelectDialect(() => "HangFire", () => "hangfire");
using var conn = SelectDialect <DbConnection>(() => new SqlConnection(ConnectionString), () => new NpgsqlConnection(ConnectionString));
Assert.AreEqual(expected, conn.ExecuteScalar <string>($"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{expected}'"));
}
public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
{
// azure can't drop the clustered index, so we have to warn if we are attempting to modify that
if (context.IsAzure)
{
if (Sql.IndexOf("NONCLUSTERED", StringComparison.OrdinalIgnoreCase) == -1 && Sql.IndexOf("CLUSTERED", StringComparison.OrdinalIgnoreCase) != -1)
return false;
}
return true;
}
/// <summary>
/// Detach from the events of a SchemaInstaller
/// </summary>
/// <param name="installer">The installer to detach from</param>
public void Detach(SchemaInstaller installer)
{
if (installer == null)
{
throw new ArgumentNullException("installer");
}
installer.DroppingObject -= _onSchemaChange;
installer.CreatingObject -= _onSchemaChange;
installer.CreatedObject -= _onSchemaChange;
installer.DropFailed -= _onSchemaChange;
}
public void ShouldInstallConfigurationSchema()
{
var creator = new SchemaInstaller();
creator.InstallHangfireConfigurationSchema(ConnectionString);
var expected = "hangfireconfiguration";
using var conn = SelectDialect <DbConnection>(() => new SqlConnection(ConnectionString), () => new NpgsqlConnection(ConnectionString));
var actual = conn.ExecuteScalar <string>($"SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{expected}'");
Assert.AreEqual(expected.ToLower(), actual.ToLower());
}
public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
// azure can't drop the clustered index, so we have to warn if we are attempting to modify that
if (context.IsAzure)
{
if (Sql.IndexOf("NONCLUSTERED", StringComparison.OrdinalIgnoreCase) == -1 && Sql.IndexOf("CLUSTERED", StringComparison.OrdinalIgnoreCase) != -1)
return false;
}
return 0 == connection.ExecuteScalarSql<int>(String.Format(@"SELECT COUNT(*)
FROM sys.xml_indexes i
WHERE i.object_id = OBJECT_ID('{0}')",
Name.SchemaQualifiedTable));
}
/// <summary>
/// Make sure the database exists.
/// </summary>
/// <returns></returns>
static bool CreateDatabase()
{
// make sure the database does not exist
if (SchemaInstaller.DatabaseExists(ConnectionString.ConnectionString))
{
Console.WriteLine("The database {0} already exists. Verify needs an empty database.", Database);
return(false);
}
// create the database
Console.WriteLine("Creating database");
SchemaInstaller.CreateDatabase(ConnectionString.ConnectionString);
return(true);
}
/// <summary>
/// Install the schema.
/// </summary>
static void Install()
{
SchemaInstaller.CreateDatabase(ConnectionString);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
Console.WriteLine("Beginning install...");
SchemaInstaller installer = new SchemaInstaller(connection);
installer.AllowRepair = AllowRepair;
new SchemaEventConsoleLogger().Attach(installer);
installer.Install(SchemaGroup, Schema);
Console.WriteLine("Done.");
}
}
public void ShouldThrowExceptionWhenNoDatabase()
{
var creator = new SchemaInstaller();
var connectionString = SelectDialect(
() => new SqlConnectionStringBuilder(ConnectionString)
{
InitialCatalog = "Does_Not_Exist"
}.ToString(),
() => new NpgsqlConnectionStringBuilder(ConnectionString)
{
Database = "Does_Not_Exist"
}.ToString());
var exception = Assert.Catch(() => creator.TryConnect(connectionString));
exception.Message.Should().Contain("Does_Not_Exist");
}
public void UpsertShouldReturnRowsInOrder()
{
var connectionString = base.ConnectionStrings.First();
TestWithDrop(connectionString, () =>
{
using (var c = new SqlConnection(connectionString))
{
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
c.Open();
SchemaInstaller installer = new SchemaInstaller(c);
SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Add("CREATE TABLE Beer ([id] [int] NOT NULL IDENTITY, [name] varchar(100))");
schema.Add("ALTER TABLE Beer ADD CONSTRAINT PK_Beer PRIMARY KEY ([ID])");
schema.Add("-- AUTOPROC All Beer");
installer.Install("test", schema);
using (var reader = c.GetReaderSql(@"
truncate table Beer
declare @b BeerTable
insert into @b (id, name) values (null, 'one')
insert into @b (id, name) values (null, 'two')
exec upsertBeers @b
delete from @b
insert into @b (id, name) values (1, 'one')
insert into @b (id, name) values (2, 'two')
insert into @b (id, name) values (null, 'three')
exec upsertBeers @b
" ))
{
reader.NextResult();
reader.Read(); Assert.AreEqual(1, reader.GetInt32(0));
reader.Read(); Assert.AreEqual(2, reader.GetInt32(0));
reader.Read(); Assert.AreEqual(3, reader.GetInt32(0));
}
}
});
}
public static void SetUpFixture()
{
#if !NET35
// insight.schema requires 4.0, so let's assume that in 35, the setup is already done
// let's do all of our work in the test database
if (!SchemaInstaller.DatabaseExists(BaseTest.ConnectionString))
{
SchemaInstaller.CreateDatabase(BaseTest.ConnectionString);
}
var schema = new SchemaObjectCollection(Assembly.GetExecutingAssembly());
using (var connection = new SqlConnection(BaseTest.ConnectionString))
{
connection.Open();
var installer = new SchemaInstaller(connection);
installer.Install("Test", schema);
}
#endif
}
private void Test(Action <SqlConnection> action)
{
var connectionString = base.ConnectionStrings.First();
TestWithDrop(connectionString, () =>
{
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
using (var c = new SqlConnection(connectionString))
{
c.Open();
SetUpDatabase(c);
action(c);
}
});
}
private static void InitializeSchemas(
StoreSettings settings,
EventTypeRegistry typesRegistry)
{
var schemas = settings.Contexts
.SelectMany(c => c.Value.GetSchemas(c.Key))
.ToArray();
using var connection = new NpgsqlConnection(settings.Connections.Default);
connection.Open();
var installer = new SchemaInstaller(connection);
foreach (var schema in schemas)
{
installer.Install(schema);
}
typesRegistry.Load(connection, schemas);
}
public void TestCreateDatabaseWithPath([ValueSource("ConnectionStrings")] string connectionString)
{
TestWithDrop(connectionString, () =>
{
// drop the database if it already exists
if (SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.DropDatabase(connectionString);
}
// create the database
Assert.True(SchemaInstaller.CreateDatabase(connectionString, ConfigurationManager.AppSettings["filepath"] ?? Environment.GetEnvironmentVariable("TEMP")));
// make sure the database exises
Assert.True(SchemaInstaller.DatabaseExists(connectionString));
// create the database again, it should return false
Assert.False(SchemaInstaller.CreateDatabase(connectionString));
});
}
public void TestDropDatabase([ValueSource("ConnectionStrings")] string connectionString)
{
TestWithDrop(connectionString, () =>
{
// create the database if it doesn't exist
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
// drop the database
Assert.True(SchemaInstaller.DropDatabase(connectionString));
// make sure the database doesn't exist
Assert.False(SchemaInstaller.DatabaseExists(connectionString));
// drop the database again, it should return false
Assert.False(SchemaInstaller.DropDatabase(connectionString));
});
}
static void Main()
{
var schema = new SchemaObjectCollection();
schema.Load(Assembly.GetExecutingAssembly());
// automatically create the database
var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var databaseName = "Thomas-test-db";
SchemaInstaller.CreateDatabase(connectionString);
// automatically install it, or upgrade it
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var installer = new SchemaInstaller(connection);
new SchemaEventConsoleLogger().Attach(installer);
installer.Install(databaseName, schema);
}
}
/// <summary>
/// Install a schema into a database.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="sql"the SQL to install.</param>
private static void Install(DbConnection connection, IEnumerable <string> sql)
{
SchemaInstaller installer = new SchemaInstaller(connection);
SchemaObjectCollection schema = new SchemaObjectCollection();
if (sql != null)
{
foreach (string s in sql)
{
// azure doesn't support xml index, so lets comment those out
if (s.Contains("XML INDEX") && connection.IsAzure())
{
continue;
}
schema.Add(s);
}
}
installer.Install("test", schema);
}
/// <summary>
/// Construct an event logger and attach it to a schema installer.
/// </summary>
/// <param name="installer">The installer to monitor</param>
protected SchemaEventLogger(SchemaInstaller installer) : this()
{
Attach(installer);
}
public void ShouldConnect()
{
var creator = new SchemaInstaller();
creator.TryConnect(ConnectionString);
}
public virtual bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return true;
}
static void Main(string[] args)
{
for (int i = 0; i < args.Length; i++)
{
switch (args[i].ToUpperInvariant())
{
case "-SERVER":
Server = args[++i];
break;
case "-DATABASE":
Database = args[++i];
break;
case "-SKIPTO":
Skip = Int32.Parse(args[++i]) - 1;
break;
case "-TAKE":
Take = Int32.Parse(args[++i]);
break;
case "-CLEAN":
Clean = true;
break;
case "-FILTER":
TypeFilter = (SchemaObjectType)Enum.Parse(typeof(SchemaObjectType), args[++i]);
break;
case "-SMO":
SMOTest = true;
break;
case "-SCRIPT":
ScriptOnly = true;
break;
default:
if (Filename == null)
Filename = args[i];
break;
}
}
// set up the connection string
ConnectionString.InitialCatalog = Database;
ConnectionString.DataSource = Server;
ConnectionString.IntegratedSecurity = true;
// drop the database if starting clean
if (Clean)
SchemaInstaller.DropDatabase(ConnectionString.ConnectionString);
// make sure we are always working with an empty database
if (!CreateDatabase())
return;
// load the schema
SchemaObjectCollection schema = LoadSchema();
try
{
// install the schema as is
using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
{
connection.Open();
// install it the first time
SchemaInstaller installer = new SchemaInstaller(connection);
installer.Install("Test", schema);
schema.Verify(connection);
// script the result through SMO
Console.WriteLine("Scripting database");
List<string> originalScript = null;
if (SMOTest)
{
originalScript = ScriptDatabaseWithSMO().ToList();
originalScript.Sort();
}
// run test cases that modify each of the elements
for (int i = Skip; i < schema.Count; i++)
{
if (Take-- <= 0)
return;
// if a type filter is defined, then filter by that type
if (TypeFilter.HasValue && schema[i].SchemaObjectType != TypeFilter.Value)
continue;
// if the type can't be modified, then don't test it
if (!schema[i].CanModify(connection))
{
Console.WriteLine();
Console.WriteLine("Not testing modification of {0} {1}", schema[i].SchemaObjectType, schema[i].SqlName.FullName);
continue;
}
// make sure all of the objects are there
try
{
Console.Write('\r');
Console.Write(new String(' ', Console.WindowWidth - 1));
Console.Write('\r');
Console.Write("Testing modifications {0}/{1}", (i + 1), schema.Count);
// modify the schema and re-install it
schema[i] = new SchemaObject(schema[i].Sql + " -- MODIFIED");
if (ScriptOnly)
Console.WriteLine(installer.ScriptChanges("Test", schema));
else
installer.Install("Test", schema);
// make sure all of the objects are there
if (SMOTest)
{
// script the whole database
var updatedScript = ScriptDatabaseWithSMO().ToList();
updatedScript.Sort();
MatchScripts(originalScript, updatedScript);
}
else
{
// just verify the dependencies
schema.Verify(connection);
}
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine("ERROR While modifying:");
Console.WriteLine(schema[i].Name);
Console.WriteLine(e.ToString());
throw;
}
}
Console.WriteLine();
}
}
finally
{
Console.WriteLine("Dropping database");
SchemaInstaller.DropDatabase(ConnectionString.ConnectionString);
}
}
public void TestUninstall(
[ValueSource("ConnectionStrings")] string connectionString,
[ValueSource("Schemas")] IEnumerable<string> schema)
{
TestWithRollback(connectionString, connection =>
{
// try to install the schema and verify that they are there
Install(connection, schema);
VerifyObjectsAndRegistry(schema, connection);
// uninstall it
SchemaInstaller installer = new SchemaInstaller(connection);
installer.Uninstall(TestSchemaGroup);
// make sure the registry is empty
SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup);
Assert.IsTrue(!registry.Entries.Any());
// make sure all of the objects exist in the database
foreach (var schemaObject in schema.Select(s => new SchemaObject(s)))
Assert.False(schemaObject.Exists(connection), "Object {0} is not deleted from database", schemaObject.Name);
});
}
public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return false;
}
public virtual bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return CanDrop(context, connection);
}
static void Main(string[] args)
{
for (int i = 0; i < args.Length; i++)
{
switch (args[i].ToUpperInvariant())
{
case "-SERVER":
Server = args[++i];
break;
case "-DATABASE":
Database = args[++i];
break;
case "-SKIPTO":
Skip = Int32.Parse(args[++i]) - 1;
break;
case "-TAKE":
Take = Int32.Parse(args[++i]);
break;
case "-CLEAN":
Clean = true;
break;
case "-FILTER":
TypeFilter = (SchemaObjectType)Enum.Parse(typeof(SchemaObjectType), args[++i]);
break;
case "-SMO":
SMOTest = true;
break;
case "-SCRIPT":
ScriptOnly = true;
break;
default:
if (Filename == null)
{
Filename = args[i];
}
break;
}
}
// set up the connection string
ConnectionString.InitialCatalog = Database;
ConnectionString.DataSource = Server;
ConnectionString.IntegratedSecurity = true;
// drop the database if starting clean
if (Clean)
{
SchemaInstaller.DropDatabase(ConnectionString.ConnectionString);
}
// make sure we are always working with an empty database
if (!CreateDatabase())
{
return;
}
// load the schema
SchemaObjectCollection schema = LoadSchema();
try
{
// install the schema as is
using (SqlConnection connection = new SqlConnection(ConnectionString.ConnectionString))
{
connection.Open();
// install it the first time
SchemaInstaller installer = new SchemaInstaller(connection);
installer.Install("Test", schema);
schema.Verify(connection);
// script the result through SMO
Console.WriteLine("Scripting database");
List <string> originalScript = null;
if (SMOTest)
{
originalScript = ScriptDatabaseWithSMO().ToList();
originalScript.Sort();
}
// run test cases that modify each of the elements
for (int i = Skip; i < schema.Count; i++)
{
if (Take-- <= 0)
{
return;
}
// if a type filter is defined, then filter by that type
if (TypeFilter.HasValue && schema[i].SchemaObjectType != TypeFilter.Value)
{
continue;
}
// if the type can't be modified, then don't test it
if (!schema[i].CanModify(connection))
{
Console.WriteLine();
Console.WriteLine("Not testing modification of {0} {1}", schema[i].SchemaObjectType, schema[i].SqlName.FullName);
continue;
}
// make sure all of the objects are there
try
{
Console.Write('\r');
Console.Write(new String(' ', Console.WindowWidth - 1));
Console.Write('\r');
Console.Write("Testing modifications {0}/{1}", (i + 1), schema.Count);
// modify the schema and re-install it
schema[i] = new SchemaObject(schema[i].Sql + " -- MODIFIED");
if (ScriptOnly)
{
Console.WriteLine(installer.ScriptChanges("Test", schema));
}
else
{
installer.Install("Test", schema);
}
// make sure all of the objects are there
if (SMOTest)
{
// script the whole database
var updatedScript = ScriptDatabaseWithSMO().ToList();
updatedScript.Sort();
MatchScripts(originalScript, updatedScript);
}
else
{
// just verify the dependencies
schema.Verify(connection);
}
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine("ERROR While modifying:");
Console.WriteLine(schema[i].Name);
Console.WriteLine(e.ToString());
throw;
}
}
Console.WriteLine();
}
}
finally
{
Console.WriteLine("Dropping database");
SchemaInstaller.DropDatabase(ConnectionString.ConnectionString);
}
}
/// <summary>
/// Install a schema into a database.
/// </summary>
/// <param name="connection">The connection to use.</param>
/// <param name="sql"the SQL to install.</param>
private static void Install(DbConnection connection, IEnumerable<string> sql)
{
SchemaInstaller installer = new SchemaInstaller(connection);
SchemaObjectCollection schema = new SchemaObjectCollection();
if (sql != null)
{
foreach (string s in sql)
{
// azure doesn't support xml index, so lets comment those out
if (s.Contains("XML INDEX") && connection.IsAzure())
continue;
schema.Add(s);
}
}
installer.Install("test", schema);
}
public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
// assume that logins can be used across the server
return false;
}
public override bool CanModify(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return true;
}
public override bool CanDrop(SchemaInstaller.InstallContext context, IDbConnection connection)
{
return 0 == connection.ExecuteScalarSql<int>(String.Format("SELECT COUNT(*) FROM sys.objects o WHERE o.schema_id = SCHEMA_ID('{0}')", Name.Object));
}
public void UpsertShouldReturnRowsInOrder()
{
var connectionString = base.ConnectionStrings.First();
TestWithDrop(connectionString, () =>
{
using (var c = new SqlConnection(connectionString))
{
if (!SchemaInstaller.DatabaseExists(connectionString))
SchemaInstaller.CreateDatabase(connectionString);
c.Open();
SchemaInstaller installer = new SchemaInstaller(c);
SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Add("CREATE TABLE Beer ([id] [int] NOT NULL IDENTITY, [name] varchar(100))");
schema.Add("ALTER TABLE Beer ADD CONSTRAINT PK_Beer PRIMARY KEY ([ID])");
schema.Add("-- AUTOPROC All Beer");
installer.Install("test", schema);
using (var reader = c.GetReaderSql(@"
truncate table Beer
declare @b BeerTable
insert into @b (id, name) values (null, 'one')
insert into @b (id, name) values (null, 'two')
exec upsertBeers @b
delete from @b
insert into @b (id, name) values (1, 'one')
insert into @b (id, name) values (2, 'two')
insert into @b (id, name) values (null, 'three')
exec upsertBeers @b
"))
{
reader.NextResult();
reader.Read(); Assert.AreEqual(1, reader.GetInt32(0));
reader.Read(); Assert.AreEqual(2, reader.GetInt32(0));
reader.Read(); Assert.AreEqual(3, reader.GetInt32(0));
}
}
});
}
/// <summary>
/// Install the schema.
/// </summary>
static void Install()
{
SchemaInstaller.CreateDatabase(ConnectionString);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
Console.WriteLine("Beginning install...");
SchemaInstaller installer = new SchemaInstaller(connection);
installer.AllowRepair = AllowRepair;
new SchemaEventConsoleLogger().Attach(installer);
installer.Install(SchemaGroup, Schema);
Console.WriteLine("Done.");
}
}
