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");
}
}
}
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);
}
}
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);
}
/// <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 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 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);
}
/// <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);
}
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);
}
}
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 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);
}
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 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.");
}
}
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);
}
}
