private void CreateColumn(AdHocSqlRunner sqlRunner)
{
var fullTableName = SqlTableJournal.CreateTableName(Schema, SqlTableJournal.Table);
log().WriteInformation($"Adding Version column to the {fullTableName} table");
sqlRunner.ExecuteNonQuery($@"alter table {fullTableName} add [Version] varchar(100) null");
log().WriteInformation($"The Version columns was added to the {fullTableName} table");
}
/// <summary>
/// Verifies the existence of targeted schema. If schema is not verified, will check for the existence of the dbo schema.
/// </summary>
public void VerifySchema()
{
if (string.IsNullOrEmpty(Schema))
{
return;
}
var sqlRunner = new AdHocSqlRunner(connectionFactory, Schema, () => true);
sqlRunner.ExecuteNonQuery(string.Format(
@"IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}') Exec('CREATE SCHEMA {0}')", Schema));
}
/// <summary>
/// Verifies the existence of targeted schema. If schema is not verified, will check for the existence of the dbo schema.
/// </summary>
public void VerifySchema()
{
if (string.IsNullOrEmpty(Schema)) return;
connectionManagerFactory().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
var sqlRunner = new AdHocSqlRunner(dbCommandFactory, Schema, () => true);
sqlRunner.ExecuteNonQuery(string.Format(
@"IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}') Exec('CREATE SCHEMA [{0}]')", Schema));
});
}
/// <summary>
/// Verifies the existence of targeted schema. If schema is not verified, will check for the existence of the dbo schema.
/// </summary>
public void VerifySchema()
{
if (string.IsNullOrEmpty(Schema))
{
return;
}
connectionManagerFactory().ExecuteCommandsWithManagedConnection(dbCommandFactory =>
{
var sqlRunner = new AdHocSqlRunner(dbCommandFactory, sqlObjectParser, Schema, () => true);
var sql = GetVerifySchemaSql(Schema);
sqlRunner.ExecuteNonQuery(sql);
});
}
/// <summary>
/// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class.
/// </summary>
/// <param name="name">The name.</param>
public TemporarySQLiteDatabase(string name)
{
dataSourcePath = Path.Combine(Directory.GetCurrentDirectory(), name);
var connectionStringBuilder = new SqliteConnectionStringBuilder
{
DataSource = name
};
sqLiteConnection = new SqliteConnection(connectionStringBuilder.ConnectionString);
sqLiteConnection.Open();
SharedConnection = new SharedConnection(sqLiteConnection);
SqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), new SQLiteObjectParser(), null, () => true);
}
/// <summary>
/// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class.
/// </summary>
public InMemorySQLiteDatabase()
{
var connectionStringBuilder = new SqliteConnectionStringBuilder
{
DataSource = ":memory:"
};
ConnectionString = connectionStringBuilder.ToString();
connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString);
sharedConnection = new SqliteConnection(connectionStringBuilder.ConnectionString);
sharedConnection.Open();
SqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), new SQLiteObjectParser(), null, () => true);
}
/// <summary>
/// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class.
/// </summary>
public InMemorySQLiteDatabase()
{
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:",
Version = 3,
DefaultTimeout = 5,
JournalMode = SQLiteJournalModeEnum.Memory,
UseUTF16Encoding = true
};
sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sharedConnection.OpenAndReturn();
sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true);
}
private void CreateTable(AdHocSqlRunner sqlRunner)
{
var fullTableName = SqlTableJournal.CreateTableName(Schema, SqlTableJournal.Table);
var primaryKeyConstraintName = CreatePrimaryKeyName(SqlTableJournal.Table);
log().WriteInformation($"Creating the {fullTableName} table");
sqlRunner.ExecuteNonQuery($@"create table {fullTableName} (
[Id] int identity(1,1) not null constraint {primaryKeyConstraintName} primary key,
[ScriptName] nvarchar(255) not null,
[Applied] datetime not null,
[Version] varchar(100) not null
)");
log().WriteInformation($"The {fullTableName} table has been created");
}
/// <summary>
/// Creates a new <see cref="TemporarySqlDatabase"/> using the specified <see cref="SqlConnectionStringBuilder"/>.
/// </summary>
/// <param name="connectionStringBuilder"><see cref="SqlConnectionStringBuilder"/> specifying which database to create.</param>
public TemporarySqlDatabase(SqlConnectionStringBuilder connectionStringBuilder)
{
var builder = new SqlConnectionStringBuilder(connectionStringBuilder.ToString()); //so we don't mangle the connectionStringBuilder coming in
builder.Pooling = false; // make sure connection pooling is disabled so the connection is actually closed as expected
// set the temporary database information
databaseName = builder.InitialCatalog;
connectionString = builder.ConnectionString;
sqlConnection = new SqlConnection(connectionString);
database = new AdHocSqlRunner(sqlConnection.CreateCommand, new SqlServer.SqlServerObjectParser(), "dbo", () => true);
// set the master database information
builder.InitialCatalog = "master";
masterSqlConnection = new SqlConnection(builder.ToString());
master = new AdHocSqlRunner(() => masterSqlConnection.CreateCommand(), new SqlServerObjectParser(), "dbo", () => true);
}
public SqlCeTemporaryDatabase()
{
databaseProviderName = "sqlce";
databaseProvider = new SqlCeDatabaseProvider();
databaseFile = Path.Combine(Path.GetTempPath(), "FunnelWeb.sdf");
connectionString = string.Format("Data Source={0}; Persist Security Info=False", databaseFile);
database = new AdHocSqlRunner(() => new SqlCeConnection(connectionString), null);
var containerBuilder = new ContainerBuilder();
containerBuilder.RegisterInstance(this).As <IConnectionStringSettings>();
containerBuilder.RegisterModule(new InternalProviderRegistrationModule());
containerBuilder.RegisterModule(new RepositoriesModule());
containerBuilder.RegisterModule(new DatabaseModule());
container = containerBuilder.Build();
}
/// <summary>
/// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class.
/// </summary>
/// <param name="name">The name.</param>
public TemporarySQLiteDatabase(string name)
{
dataSourcePath = Path.Combine(Environment.CurrentDirectory, name);
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = name,
Version = 3,
DefaultTimeout = 5,
JournalMode = SQLiteJournalModeEnum.Memory,
UseUTF16Encoding = true
};
sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sharedConnection = new SharedConnection(sqLiteConnection.OpenAndReturn());
sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), null, () => true);
}
public void VerifyDatabaseRequirements()
{
var sqlRunner = new AdHocSqlRunner(connectionManager, Schema, () => true);
var parameters = new Dictionary <string, object> {
{ "@schema", Schema }, { "@table", SqlTableJournal.Table }
};
if (!SchemaExists(sqlRunner, parameters))
{
throw new Exception($"'{Schema}' does not exist. Contact DBA for help, build process deploy users will not have permissions to create schemas.");
}
if (!TableExists(sqlRunner, parameters))
{
CreateTable(sqlRunner);
}
if (!ColumnExists(sqlRunner, parameters))
{
CreateColumn(sqlRunner);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class.
/// </summary>
public InMemorySQLiteDatabase()
{
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:",
Version = 3,
DefaultTimeout = 5,
#if MONO
JournalMode = SQLiteJournalModeEnum.Off,
#else
JournalMode = SQLiteJournalModeEnum.Memory,
#endif
UseUTF16Encoding = true
};
ConnectionString = connectionStringBuilder.ToString();
connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString);
sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sharedConnection.Open();
sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true);
}
/// <summary>
/// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class.
/// </summary>
/// <param name="name">The name.</param>
public TemporarySQLiteDatabase(string name)
{
dataSourcePath = Path.Combine(Directory.GetCurrentDirectory(), name);
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
DataSource = name,
#if !NETCORE
Version = 3,
DefaultTimeout = 5,
#if MONO
JournalMode = SQLiteJournalModeEnum.Off,
#else
JournalMode = SQLiteJournalModeEnum.Memory,
#endif
UseUTF16Encoding = true
#endif
};
sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString);
sqLiteConnection.Open();
sharedConnection = new SharedConnection(sqLiteConnection);
sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), new SQLiteObjectParser(), null, () => true);
}
private bool TableExists(AdHocSqlRunner sqlRunner, Dictionary <string, object> parameters)
{
var result = sqlRunner.ExecuteScalar($"select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @table and TABLE_SCHEMA = @schema", parameters) as int?;
return(result == 1);
}
/// <summary>
/// Verifies the existence of targeted schema. If schema is not verified, will check for the existence of the dbo schema.
/// </summary>
private bool SchemaExists(AdHocSqlRunner sqlRunner, Dictionary <string, object> parameters)
{
var result = sqlRunner.ExecuteScalar($@"SELECT 1 FROM sys.schemas WHERE name = @schema", parameters) as int?;
return(result == 1);
}
