public virtual void CreateDatabaseAndSchema(DatabaseCreationOptions options)
{
var dataDefinitionExpressions = BuildDataDefinitonExpressions(options);
CreateDatabaseOnly(dataDefinitionExpressions, options);
CreateDatabaseSchema(dataDefinitionExpressions, options);
}
public virtual void CreateDatabaseAndSchema(DatabaseCreationOptions options)
{
var dataDefinitionExpressions = this.BuildDataDefinitonExpressions(options);
this.CreateDatabaseOnly(dataDefinitionExpressions, options);
this.CreateDatabaseSchema(dataDefinitionExpressions, options);
}
protected virtual void CreateDatabaseSchema(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
using (var scope = new DataAccessScope())
{
using (var dataTransactionContext = this.SqlDatabaseContext.CreateSqlTransactionalCommandsContext(null))
{
using (this.SqlDatabaseContext.AcquireDisabledForeignKeyCheckContext(dataTransactionContext))
{
var result = this.SqlDatabaseContext.SqlQueryFormatterManager.Format(dataDefinitionExpressions);
using (var command = dataTransactionContext.CreateCommand(SqlCreateCommandOptions.Default | SqlCreateCommandOptions.UnpreparedExecute))
{
command.CommandText = result.CommandText;
Logger.Info(command.CommandText);
command.ExecuteNonQuery();
}
}
dataTransactionContext.Commit();
}
scope.Complete();
}
}
public static Expression Build(SqlDataTypeProvider sqlDataTypeProvider, SqlDialect sqlDialect, DataAccessModel model, DatabaseCreationOptions options, string tableNamePrefix, SqlDataDefinitionBuilderFlags flags)
{
var builder = new SqlDataDefinitionExpressionBuilder(sqlDialect, sqlDataTypeProvider, model, options, tableNamePrefix, flags);
var retval = builder.Build();
return retval;
}
public virtual void Create(DatabaseCreationOptions options)
{
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
this.GetCurrentSqlDatabaseContext().SchemaManager.CreateDatabaseAndSchema(options);
scope.Complete();
}
}
public virtual void Create(DatabaseCreationOptions options)
{
using (var scope = new DataAccessScope(DataAccessIsolationLevel.Unspecified, DataAccessScopeOptions.RequiresNew, TimeSpan.FromMinutes(10)))
{
this.GetCurrentSqlDatabaseContext().SchemaManager.CreateDatabaseAndSchema(options);
scope.Complete();
}
}
public virtual void CreateDatabaseAndSchema(DatabaseCreationOptions options)
{
var dataDefinitionExpressions = this.BuildDataDefinitonExpressions(options);
if (!this.CreateDatabaseOnly(dataDefinitionExpressions, options))
{
return;
}
this.CreateDatabaseSchema(dataDefinitionExpressions, options);
}
private SqlDataDefinitionExpressionBuilder(SqlDialect sqlDialect, SqlDataTypeProvider sqlDataTypeProvider, DataAccessModel model, DatabaseCreationOptions options, string tableNamePrefix, SqlDataDefinitionBuilderFlags flags)
{
this.model = model;
this.options = options;
this.sqlDialect = sqlDialect;
this.tableNamePrefix = tableNamePrefix;
this.flags = flags;
this.sqlDataTypeProvider = sqlDataTypeProvider;
this.currentTableConstraints = new List<Expression>();
}
private SqlDataDefinitionExpressionBuilder(SqlDialect sqlDialect, SqlDataTypeProvider sqlDataTypeProvider, DataAccessModel model, DatabaseCreationOptions options, string tableNamePrefix, SqlDataDefinitionBuilderFlags flags)
{
this.model = model;
this.options = options;
this.sqlDialect = sqlDialect;
this.tableNamePrefix = tableNamePrefix;
this.flags = flags;
this.sqlDataTypeProvider = sqlDataTypeProvider;
this.currentTableConstraints = new List <Expression>();
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var databaseName = this.SqlDatabaseContext.DatabaseName;
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
this.SqlDatabaseContext.DropAllConnections();
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
dbConnection.Open();
IDbCommand command;
if (overwrite)
{
var drop = false;
using (command = dbConnection.CreateCommand())
{
command.CommandText = String.Format("SELECT datname FROM pg_database;");
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var s = reader.GetString(0);
if (s.Equals(databaseName))
{
drop = true;
break;
}
}
}
}
if (drop)
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"DROP DATABASE \"{databaseName}\";";
command.ExecuteNonQuery();
}
}
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
command.ExecuteNonQuery();
}
retval = true;
}
else
{
try
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
command.ExecuteNonQuery();
}
retval = true;
}
catch
{
retval = false;
}
}
}
return(retval);
}
public static Expression Build(DataAccessModel dataAccessModel, SqlQueryFormatterManager formatterManager, SqlDataTypeProvider sqlDataTypeProvider, SqlDialect sqlDialect, DataAccessModel model, DatabaseCreationOptions options, string tableNamePrefix, bool indexNamesShouldIncludeIncludedProperties, SqlDataDefinitionBuilderFlags flags)
{
var builder = new SqlDataDefinitionExpressionBuilder(dataAccessModel, formatterManager, sqlDialect, sqlDataTypeProvider, model, options, tableNamePrefix, indexNamesShouldIncludeIncludedProperties, flags);
var retval = builder.Build();
return(retval);
}
protected virtual Expression BuildDataDefinitonExpressions(DatabaseCreationOptions options)
{
return(SqlDataDefinitionExpressionBuilder.Build(this.SqlDatabaseContext.SqlDataTypeProvider, this.SqlDatabaseContext.SqlDialect, this.SqlDatabaseContext.DataAccessModel, options, this.SqlDatabaseContext.TableNamePrefix, this.GetBuilderFlags()));
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var deleteDatabaseDropsTablesOnly = ((SqlServerSqlDatabaseContext)this.SqlDatabaseContext).DeleteDatabaseDropsTablesOnly;
using (var connection = factory.CreateConnection())
{
connection.ConnectionString = deleteDatabaseDropsTablesOnly ? this.SqlDatabaseContext.ConnectionString : this.SqlDatabaseContext.ServerConnectionString;
connection.Open();
if (deleteDatabaseDropsTablesOnly)
{
using (var command = (SqlCommand) connection.CreateCommand())
{
command.CommandTimeout = Math.Min((int)this.SqlDatabaseContext.CommandTimeout.TotalSeconds, 300);
command.CommandText =
@"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
DECLARE @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
EXEC (@sql)
PRINT @sql
END
";
command.ExecuteNonQuery();
}
using (var command = (SqlCommand)connection.CreateCommand())
{
command.CommandTimeout = Math.Min((int)this.SqlDatabaseContext.CommandTimeout.TotalSeconds, 300);
command.CommandText =
@"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'))
BEGIN
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'
EXEC (@sql)
PRINT @sql
END
";
command.ExecuteNonQuery();
}
return true;
}
using (var command = (SqlCommand)connection.CreateCommand())
{
try
{
var databaseName = this.SqlDatabaseContext.DatabaseName.Trim();
if (options == DatabaseCreationOptions.DeleteExistingDatabase)
{
command.CommandText = string.Format("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '{0}') DROP DATABASE [{0}];", databaseName);
command.ExecuteNonQuery();
}
command.CommandText = string.Format("CREATE DATABASE [{0}];", databaseName);
command.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
}
}
protected override void CreateDatabaseSchema(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
if (!string.IsNullOrEmpty(this.SqlDatabaseContext.SchemaName))
{
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
dbConnection.Open();
dbConnection.ChangeDatabase(this.SqlDatabaseContext.DatabaseName);
using (var command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE SCHEMA IF NOT EXISTS \"{this.SqlDatabaseContext.SchemaName}\";";
command.ExecuteNonQuery();
}
}
}
base.CreateDatabaseSchema(dataDefinitionExpressions, options);
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var retval = false;
var sqliteSqlDatabaseContext = (SqliteSqlDatabaseContext)this.SqlDatabaseContext;
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
var path = sqliteSqlDatabaseContext.FileName;
if (sqliteSqlDatabaseContext.IsInMemoryConnection)
{
if (overwrite)
{
var connection = sqliteSqlDatabaseContext.OpenConnection();
if (sqliteSqlDatabaseContext.IsSharedCacheConnection)
{
// Keeping a reference around so that the in-memory DB survives
this.inMemoryConnection = sqliteSqlDatabaseContext.OpenConnection();
}
using (var command = connection.CreateCommand())
{
command.CommandText =
@"
PRAGMA writable_schema = 1;
delete from sqlite_master where type = 'table';
PRAGMA writable_schema = 0;
VACUUM;
" ;
command.ExecuteNonQuery();
}
}
return(true);
}
if (overwrite)
{
try
{
File.Delete(path);
}
catch (FileNotFoundException)
{
}
catch (DirectoryNotFoundException)
{
}
for (var i = 0; i < 2; i++)
{
try
{
this.CreateFile(path);
break;
}
catch (FileNotFoundException)
{
}
catch (DirectoryNotFoundException)
{
}
var directoryPath = Path.GetDirectoryName(path);
if (!String.IsNullOrEmpty(directoryPath))
{
try
{
Directory.CreateDirectory(directoryPath);
}
catch
{
}
}
}
retval = true;
}
else
{
if (!File.Exists(path))
{
for (var i = 0; i < 2; i++)
{
try
{
this.CreateFile(path);
break;
}
catch (FileNotFoundException)
{
}
catch (DirectoryNotFoundException)
{
}
var directoryPath = Path.GetDirectoryName(path);
if (!String.IsNullOrEmpty(directoryPath))
{
try
{
Directory.CreateDirectory(directoryPath);
}
catch
{
}
}
}
retval = true;
}
else
{
retval = false;
}
}
return(retval);
}
public virtual Expression BuildDataDefinitonExpressions(DatabaseCreationOptions options)
{
return(SqlDataDefinitionExpressionBuilder.Build(this.SqlDatabaseContext.DataAccessModel, this.SqlDatabaseContext.SqlQueryFormatterManager, this.SqlDatabaseContext.SqlDataTypeProvider, this.SqlDatabaseContext.SqlDialect, this.SqlDatabaseContext.DataAccessModel, options, this.SqlDatabaseContext.TableNamePrefix, this.SqlDatabaseContext.IndexNamesShouldIncludeIncludedProperties, GetBuilderFlags()));
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
dbConnection.Open();
using (var command = dbConnection.CreateCommand())
{
if (overwrite)
{
var drop = false;
command.CommandText = String.Format("SHOW DATABASES;", this.SqlDatabaseContext.DatabaseName);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var s = reader.GetString(0);
if (s.Equals(this.SqlDatabaseContext.DatabaseName) ||
s.Equals(this.SqlDatabaseContext.DatabaseName.ToLower()))
{
drop = true;
break;
}
}
}
if (drop)
{
command.CommandText = String.Concat("DROP DATABASE ", this.SqlDatabaseContext.DatabaseName);
command.ExecuteNonQuery();
}
command.CommandText = String.Concat("CREATE DATABASE ", this.SqlDatabaseContext.DatabaseName, "\nDEFAULT CHARACTER SET = utf8\nDEFAULT COLLATE = utf8_general_ci;");
command.ExecuteNonQuery();
retval = true;
}
else
{
try
{
command.CommandText = String.Concat("CREATE DATABASE ", this.SqlDatabaseContext.DatabaseName, "\nDEFAULT CHARACTER SET = utf8\nDEFAULT COLLATE = utf8_general_ci;");
command.ExecuteNonQuery();
retval = true;
}
catch
{
retval = false;
}
}
}
}
return(retval);
}
private SqlDataDefinitionExpressionBuilder(DataAccessModel dataAccessModel, SqlQueryFormatterManager formatterManager, SqlDialect sqlDialect, SqlDataTypeProvider sqlDataTypeProvider, DataAccessModel model, DatabaseCreationOptions options, string tableNamePrefix, SqlDataDefinitionBuilderFlags flags)
{
this.dataAccessModel = dataAccessModel;
this.formatterManager = formatterManager;
this.model = model;
this.sqlDialect = sqlDialect;
this.flags = flags;
this.sqlDataTypeProvider = sqlDataTypeProvider;
this.currentTableConstraints = new List <SqlConstraintExpression>();
}
protected override void CreateDatabaseSchema(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
if (!string.IsNullOrEmpty(this.SqlDatabaseContext.SchemaName))
{
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
dbConnection.Open();
dbConnection.ChangeDatabase(this.SqlDatabaseContext.DatabaseName);
using (var command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE SCHEMA IF NOT EXISTS \"{this.SqlDatabaseContext.SchemaName}\";";
command.ExecuteNonQuery();
}
}
}
base.CreateDatabaseSchema(dataDefinitionExpressions, options);
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var deleteDatabaseDropsTablesOnly = ((SqlServerSqlDatabaseContext)this.SqlDatabaseContext).DeleteDatabaseDropsTablesOnly;
using (var connection = factory.CreateConnection())
{
connection.ConnectionString = deleteDatabaseDropsTablesOnly ? this.SqlDatabaseContext.ConnectionString : this.SqlDatabaseContext.ServerConnectionString;
connection.Open();
if (deleteDatabaseDropsTablesOnly)
{
using (var command = (SqlCommand)connection.CreateCommand())
{
command.CommandTimeout = Math.Min((int)this.SqlDatabaseContext.CommandTimeout.TotalSeconds, 300);
command.CommandText =
@"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
DECLARE @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
EXEC (@sql)
PRINT @sql
END
" ;
command.ExecuteNonQuery();
}
using (var command = (SqlCommand)connection.CreateCommand())
{
command.CommandTimeout = Math.Min((int)this.SqlDatabaseContext.CommandTimeout.TotalSeconds, 300);
command.CommandText =
@"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'))
BEGIN
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'
EXEC (@sql)
PRINT @sql
END
" ;
command.ExecuteNonQuery();
}
return(true);
}
using (var command = (SqlCommand)connection.CreateCommand())
{
try
{
var databaseName = this.SqlDatabaseContext.DatabaseName.Trim();
if (options == DatabaseCreationOptions.DeleteExistingDatabase)
{
command.CommandText = string.Format("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '{0}') DROP DATABASE [{0}];", databaseName);
command.ExecuteNonQuery();
}
command.CommandText = string.Format("CREATE DATABASE [{0}];", databaseName);
command.ExecuteNonQuery();
return(true);
}
catch
{
return(false);
}
}
}
}
protected override Task<bool> CreateDatabaseOnlyAsync(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
return CreateDatabaseOnlyAsync(dataDefinitionExpressions, options, CancellationToken.None);
}
protected override async Task <bool> CreateDatabaseOnlyAsync(Expression dataDefinitionExpressions, DatabaseCreationOptions options, CancellationToken cancellationToken)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var databaseName = this.SqlDatabaseContext.DatabaseName;
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
this.SqlDatabaseContext.DropAllConnections();
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
await dbConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
IDbCommand command;
if (overwrite)
{
var drop = false;
using (command = dbConnection.CreateCommand())
{
command.CommandText = "SELECT datname FROM pg_database;";
using (var reader = (await command.ExecuteReaderExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false)))
{
while ((await reader.ReadExAsync(cancellationToken).ConfigureAwait(false)))
{
var s = reader.GetString(0);
if (s.Equals(databaseName))
{
drop = true;
break;
}
}
}
}
if (drop)
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"DROP DATABASE \"{databaseName}\";";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
}
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
retval = true;
}
else
{
try
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
retval = true;
}
catch
{
retval = false;
}
}
}
return(retval);
}
protected override Task <bool> CreateDatabaseOnlyAsync(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
return(CreateDatabaseOnlyAsync(dataDefinitionExpressions, options, CancellationToken.None));
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var databaseName = this.SqlDatabaseContext.DatabaseName;
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
this.SqlDatabaseContext.DropAllConnections();
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
dbConnection.Open();
IDbCommand command;
if (overwrite)
{
var drop = false;
using (command = dbConnection.CreateCommand())
{
command.CommandText = String.Format("SELECT datname FROM pg_database;");
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var s = reader.GetString(0);
if (s.Equals(databaseName))
{
drop = true;
break;
}
}
}
}
if (drop)
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"DROP DATABASE \"{databaseName}\";";
command.ExecuteNonQuery();
}
}
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
command.ExecuteNonQuery();
}
retval = true;
}
else
{
try
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
command.ExecuteNonQuery();
}
retval = true;
}
catch
{
retval = false;
}
}
}
return retval;
}
protected override async Task<bool> CreateDatabaseOnlyAsync(Expression dataDefinitionExpressions, DatabaseCreationOptions options, CancellationToken cancellationToken)
{
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var deleteDatabaseDropsTablesOnly = ((SqlServerSqlDatabaseContext)this.SqlDatabaseContext).DeleteDatabaseDropsTablesOnly;
using (var connection = factory.CreateConnection())
{
if (connection == null)
{
throw new InvalidOperationException($"Unable to create connection from {factory}");
}
try
{
var databaseName = this.SqlDatabaseContext.DatabaseName.Trim();
var context = (SqlServerSqlDatabaseContext)this.SqlDatabaseContext;
connection.ConnectionString = deleteDatabaseDropsTablesOnly ? this.SqlDatabaseContext.ConnectionString : this.SqlDatabaseContext.ServerConnectionString;
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
using (var command = (SqlCommand)connection.CreateCommand())
{
if (options == DatabaseCreationOptions.DeleteExistingDatabase)
{
if (deleteDatabaseDropsTablesOnly)
{
command.CommandTimeout = Math.Min((int)(this.SqlDatabaseContext.CommandTimeout?.TotalSeconds ?? SqlDatabaseContextInfo.DefaultCommandTimeout), 300);
command.CommandText = @"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
DECLARE @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
EXEC (@sql)
END
";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
command.CommandText = @"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'))
BEGIN
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'
EXEC (@sql)
END
";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
command.CommandText = $"IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE NAME = '{databaseName}') CREATE DATABASE [{databaseName}];";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
else
{
command.CommandText = $"IF EXISTS (SELECT 1 FROM sys.databases WHERE NAME = '{databaseName}') DROP DATABASE [{databaseName}];";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
command.CommandText = $"CREATE DATABASE [{databaseName}];";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
}
else
{
command.CommandText = $"IF EXISTS (SELECT 1 FROM sys.databases WHERE NAME = '{databaseName}') DROP DATABASE [{databaseName}];";
command.CommandText = $"CREATE DATABASE [{databaseName}];";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
command.CommandText = $"ALTER DATABASE [{databaseName}] SET ALLOW_SNAPSHOT_ISOLATION {(context.AllowSnapshotIsolation ? "ON" : "OFF")};";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
command.CommandText = $"ALTER DATABASE [{databaseName}] SET READ_COMMITTED_SNAPSHOT {(context.ReadCommittedSnapshop ? "ON" : "OFF")};";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
return true;
}
}
catch (Exception e)
{
Logger.Log(Logging.LogLevel.Error, () => "Exception creating database: " + e);
throw;
}
}
}
protected override async Task<bool> CreateDatabaseOnlyAsync(Expression dataDefinitionExpressions, DatabaseCreationOptions options, CancellationToken cancellationToken)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var databaseName = this.SqlDatabaseContext.DatabaseName;
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
this.SqlDatabaseContext.DropAllConnections();
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
await dbConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
IDbCommand command;
if (overwrite)
{
var drop = false;
using (command = dbConnection.CreateCommand())
{
command.CommandText = "SELECT datname FROM pg_database;";
using (var reader = (await command.ExecuteReaderExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false)))
{
while (await reader.ReadExAsync(cancellationToken).ConfigureAwait(false))
{
var s = reader.GetString(0);
if (s.Equals(databaseName))
{
drop = true;
break;
}
}
}
}
if (drop)
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"DROP DATABASE \"{databaseName}\";";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
}
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
retval = true;
}
else
{
try
{
using (command = dbConnection.CreateCommand())
{
command.CommandText = $"CREATE DATABASE \"{databaseName}\" WITH ENCODING 'UTF8';";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
retval = true;
}
catch
{
retval = false;
}
}
}
return retval;
}
protected virtual Expression BuildDataDefinitonExpressions(DatabaseCreationOptions options)
{
return SqlDataDefinitionExpressionBuilder.Build(this.SqlDatabaseContext.SqlDataTypeProvider, this.SqlDatabaseContext.SqlDialect, this.SqlDatabaseContext.DataAccessModel, options, this.SqlDatabaseContext.TableNamePrefix, this.GetBuilderFlags());
}
public static Expression Build(SqlDataTypeProvider sqlDataTypeProvider, SqlDialect sqlDialect, DataAccessModel model, DatabaseCreationOptions options, string tableNamePrefix, SqlDataDefinitionBuilderFlags flags)
{
var builder = new SqlDataDefinitionExpressionBuilder(sqlDialect, sqlDataTypeProvider, model, options, tableNamePrefix, flags);
var retval = builder.Build();
return(retval);
}
protected abstract bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options);
protected override async Task <bool> CreateDatabaseOnlyAsync(Expression dataDefinitionExpressions, DatabaseCreationOptions options, CancellationToken cancellationToken)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
await dbConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
using (var command = dbConnection.CreateCommand())
{
if (overwrite)
{
var drop = false;
command.CommandText = String.Format("SHOW DATABASES;", this.SqlDatabaseContext.DatabaseName);
using (var reader = (await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false)))
{
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
var s = reader.GetString(0);
if (s.Equals(this.SqlDatabaseContext.DatabaseName) || s.Equals(this.SqlDatabaseContext.DatabaseName.ToLower()))
{
drop = true;
break;
}
}
}
if (drop)
{
command.CommandText = $"DROP DATABASE {this.SqlDatabaseContext.DatabaseName}";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
}
command.CommandText = $"CREATE DATABASE {this.SqlDatabaseContext.DatabaseName}\nDEFAULT CHARACTER SET = utf8\nDEFAULT COLLATE = utf8_general_ci;";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
retval = true;
}
else
{
try
{
command.CommandText = $"CREATE DATABASE {this.SqlDatabaseContext.DatabaseName}\nDEFAULT CHARACTER SET = utf8\nDEFAULT COLLATE = utf8_general_ci;";
await command.ExecuteNonQueryExAsync(this.SqlDatabaseContext.DataAccessModel, cancellationToken, true).ConfigureAwait(false);
retval = true;
}
catch
{
retval = false;
}
}
}
}
return(retval);
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var retval = false;
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
using (var dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = this.SqlDatabaseContext.ServerConnectionString;
dbConnection.Open();
using (var command = dbConnection.CreateCommand())
{
if (overwrite)
{
var drop = false;
command.CommandText = String.Format("SHOW DATABASES;", this.SqlDatabaseContext.DatabaseName);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var s = reader.GetString(0);
if (s.Equals(this.SqlDatabaseContext.DatabaseName) ||
s.Equals(this.SqlDatabaseContext.DatabaseName.ToLower()))
{
drop = true;
break;
}
}
}
if (drop)
{
command.CommandText = $"DROP DATABASE {this.SqlDatabaseContext.DatabaseName}";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
}
command.CommandText = $"CREATE DATABASE {this.SqlDatabaseContext.DatabaseName}\nDEFAULT CHARACTER SET = utf8\nDEFAULT COLLATE = utf8_general_ci;";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
retval = true;
}
else
{
try
{
command.CommandText = $"CREATE DATABASE {this.SqlDatabaseContext.DatabaseName}\nDEFAULT CHARACTER SET = utf8\nDEFAULT COLLATE = utf8_general_ci;";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
retval = true;
}
catch
{
retval = false;
}
}
}
}
return retval;
}
protected abstract bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options);
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var factory = this.SqlDatabaseContext.CreateDbProviderFactory();
var deleteDatabaseDropsTablesOnly = ((SqlServerSqlDatabaseContext)this.SqlDatabaseContext).DeleteDatabaseDropsTablesOnly;
using (var connection = factory.CreateConnection())
{
if (connection == null)
{
throw new InvalidOperationException($"Unable to create connection from {factory}");
}
try
{
var databaseName = this.SqlDatabaseContext.DatabaseName.Trim();
var context = (SqlServerSqlDatabaseContext)this.SqlDatabaseContext;
connection.ConnectionString = deleteDatabaseDropsTablesOnly ? this.SqlDatabaseContext.ConnectionString : this.SqlDatabaseContext.ServerConnectionString;
connection.Open();
using (var command = (SqlCommand)connection.CreateCommand())
{
if (options == DatabaseCreationOptions.DeleteExistingDatabase)
{
if (deleteDatabaseDropsTablesOnly)
{
command.CommandTimeout = Math.Min((int)(this.SqlDatabaseContext.CommandTimeout?.TotalSeconds ?? SqlDatabaseContextInfo.DefaultCommandTimeout), 300);
command.CommandText =
@"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
DECLARE @sql nvarchar(2000)
SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
EXEC (@sql)
END
" ;
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
command.CommandText =
@"
WHILE(exists(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'))
BEGIN
declare @sql nvarchar(2000)
SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'sys' AND TABLE_TYPE = 'BASE TABLE'
EXEC (@sql)
END
" ;
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
command.CommandText = $"IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE NAME = '{databaseName}') CREATE DATABASE [{databaseName}];";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
}
else
{
command.CommandText = $"IF EXISTS (SELECT 1 FROM sys.databases WHERE NAME = '{databaseName}') DROP DATABASE [{databaseName}];";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
command.CommandText = $"CREATE DATABASE [{databaseName}];";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
}
}
else
{
command.CommandText = $"IF EXISTS (SELECT 1 FROM sys.databases WHERE NAME = '{databaseName}') DROP DATABASE [{databaseName}];";
command.CommandText = $"CREATE DATABASE [{databaseName}];";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
}
command.CommandText = $"ALTER DATABASE [{databaseName}] SET ALLOW_SNAPSHOT_ISOLATION {(context.AllowSnapshotIsolation ? "ON" : "OFF")};";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
command.CommandText = $"ALTER DATABASE [{databaseName}] SET READ_COMMITTED_SNAPSHOT {(context.ReadCommittedSnapshot ? "ON" : "OFF")};";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
return(true);
}
}
catch (Exception e)
{
Logger.Log(Logging.LogLevel.Error, () => "Exception creating database: " + e);
throw;
}
}
}
protected virtual void CreateDatabaseSchema(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
using (var dataTransactionContext = this.SqlDatabaseContext.CreateSqlTransactionalCommandsContext(null))
{
using (this.SqlDatabaseContext.AcquireDisabledForeignKeyCheckContext(dataTransactionContext))
{
var result = this.SqlDatabaseContext.SqlQueryFormatterManager.Format(dataDefinitionExpressions);
using (var command = dataTransactionContext.CreateCommand(SqlCreateCommandOptions.Default | SqlCreateCommandOptions.UnpreparedExecute))
{
command.CommandText = result.CommandText;
Logger.Debug(command.CommandText);
command.ExecuteNonQuery();
}
}
}
scope.Complete();
}
}
protected override bool CreateDatabaseOnly(Expression dataDefinitionExpressions, DatabaseCreationOptions options)
{
var retval = false;
var sqliteSqlDatabaseContext = (SqliteSqlDatabaseContext)this.SqlDatabaseContext;
var overwrite = options == DatabaseCreationOptions.DeleteExistingDatabase;
var path = sqliteSqlDatabaseContext.FileName;
if (sqliteSqlDatabaseContext.IsInMemoryConnection)
{
if (overwrite)
{
var connection = sqliteSqlDatabaseContext.OpenConnection();
if (sqliteSqlDatabaseContext.IsSharedCacheConnection)
{
// Keeping a reference around so that the in-memory DB survives
this.inMemoryConnection = sqliteSqlDatabaseContext.OpenConnection();
}
using (var command = connection.CreateCommand())
{
command.CommandText =
@"
PRAGMA writable_schema = 1;
delete from sqlite_master where type = 'table';
PRAGMA writable_schema = 0;
VACUUM;
";
command.ExecuteNonQueryEx(this.SqlDatabaseContext.DataAccessModel, true);
}
}
return true;
}
if (overwrite)
{
try
{
File.Delete(path);
}
catch (FileNotFoundException)
{
}
catch (DirectoryNotFoundException)
{
}
for (var i = 0; i < 2; i++)
{
try
{
this.CreateFile(path);
break;
}
catch (FileNotFoundException)
{
}
catch (DirectoryNotFoundException)
{
}
var directoryPath = Path.GetDirectoryName(path);
if (!String.IsNullOrEmpty(directoryPath))
{
try
{
Directory.CreateDirectory(directoryPath);
}
catch
{
}
}
}
retval = true;
}
else
{
if (!File.Exists(path))
{
for (var i = 0; i < 2; i++)
{
try
{
this.CreateFile(path);
break;
}
catch (FileNotFoundException)
{
}
catch (DirectoryNotFoundException)
{
}
var directoryPath = Path.GetDirectoryName(path);
if (!String.IsNullOrEmpty(directoryPath))
{
try
{
Directory.CreateDirectory(directoryPath);
}
catch
{
}
}
}
retval = true;
}
else
{
retval = false;
}
}
return retval;
}
