async Task _TransactionTestAsync(bool withCommit)
{
using (FileLogger l = new FileLogger("MdbContextTest", new FileLogSettings()
{
DateFolderMask = "yyyy-MM-dd"
}))
{
using (var ctx = new MdbContext(_cn))
{
await ctx.BeginTransactionAsync();
await createTestTableAsync(ctx, l);
await insertTestTableAsync(ctx, l);
await selectTestTableAsync(ctx, l);
if (withCommit)
{
await ctx.CommitAsync();
await dropTestTableAsync(ctx, l);
}
else
{
//Отменяет все коммиты
ctx.RollBack();
}
}
}
}
/// <summary>
/// required teting
/// </summary>
/// <param name="dbSchema"></param>
/// <param name="objectName"></param>
/// <returns></returns>
private async Task <JMXSchema> SyncSchemaAsync(JMXObjectName objectName)
{
MdbContext mdb = Factory.GetMdbContext();
ILogger log = this.Logger;
var schema = await _schemaRepo.GetSchemaAsync(objectName);
//schema was syncronized
//change this logic
if (schema.SyncState == 1)
{
return(schema);
}
foreach (var o in GetDependences(schema))
{
await SyncSchemaAsync(o);
}
var schemaFromDb = await GetObjectSchemaAsync(schema.DbObjectName.ToString());
bool createNew = (schemaFromDb == null);
string[] sqlList;
if (createNew)
{
sqlList = CreateNewStatements(schema);
}
else
{
// Compare with previos version of schema
// error schema not found if db objects exists, but
// synced version schema don't exists
//var prevSchema = await GetSchemaInternalAsync(mdb, dbSchema, objectName, 1);
//!!! not tested
var prevSchema = schemaFromDb;
sqlList = await CompareSchemasAsync(schema, prevSchema);
}
await mdb.BeginTransactionAsync();
try
{
foreach (var sql in sqlList)
{
log.LogDebug(sql);
await mdb.ExecuteAsync(sql);
}
schema = await _schemaRepo.SetSchemaStateAsync(schema.ObjectName, 1);
await mdb.CommitAsync();
}
catch
{
mdb.RollBack();
throw;
}
return(schema);
}
static async Task createTestTableAsync(MdbContext ctx, FileLogger l)
{
l.Debug("Create Test Table Start");
int i = 0;
string sql = @"Create Table TestTable(
ID uniqueidentifier not null primary key,
Name varchar(128) not null,
DateOper datetime not null,
Notes varchar(256) null,
Handle int not null)" ;
await ctx.BeginTransactionAsync();
i = await ctx.ExecuteAsync(sql);
await ctx.CommitAsync();
}
private static async Task CreateDbSchemaAsync(MdbContext mdb, ILogger log)
{
try
{
await mdb.BeginTransactionAsync();
var scripts = SQLite.CreateSchemaObjects_12.Split(new string[] { "--go", "--GO" },
StringSplitOptions.RemoveEmptyEntries);
foreach (string statement in scripts)
{
await mdb.ExecuteAsync(statement);
}
log.LogDebug($"Schema SysCat was created in database {mdb.DbName}");
log.LogDebug("Schema tables SysCat.SysAreas and SysCat.SysSchemas was created in SysCat Schema");
string owner = await GetDefaultDbSchemaAsync(mdb);
long id = await mdb.ExecuteAsync <long>(SQLite.AddSysAreas,
new MdbParameter("@SchemaName", owner),
new MdbParameter("@SchemaOwner", owner),
new MdbParameter("@SchemaVersion", schema_version),
new MdbParameter("@SchemaConfig", "") { NullIfEmpty = true },
new MdbParameter("@IsDefault", 1),
new MdbParameter("@UpdateTime", DateTime.Now),
new MdbParameter("@DateBegin", DateTime.Now.Date),
new MdbParameter("@DateEnd", DateTime.MinValue) { NullIfEmpty = true });
id = await mdb.ExecuteAsync <long>(SQLite.AddSysAreas,
new MdbParameter("@SchemaName", "SysCat"),
new MdbParameter("@SchemaOwner", owner),
new MdbParameter("@SchemaVersion", schema_version),
new MdbParameter("@SchemaConfig", "") { NullIfEmpty = true },
new MdbParameter("@IsDefault", 1),
new MdbParameter("@UpdateTime", DateTime.Now),
new MdbParameter("@DateBegin", DateTime.Now.Date),
new MdbParameter("@DateEnd", DateTime.MinValue) { NullIfEmpty = true });
await mdb.CommitAsync();
}
catch (Exception e)
{
mdb.RollBack();
log.LogError($"CreateSchema error: {e.Message}");
throw;
}
}
private async Task CreateDbSchemaAsync()
{
MdbContext mdb = Factory.GetMdbContext();
try
{
await mdb.BeginTransactionAsync();
var scripts = (await SqlServerHelper.IsSql17(mdb)) ?
SqlServer.CreateSchemaObjects.Split(new string[] { "--go", "--GO" },
StringSplitOptions.RemoveEmptyEntries) :
SqlServer.CreateSchemaObjects_12.Split(new string[] { "--go", "--GO" },
StringSplitOptions.RemoveEmptyEntries);
foreach (string statement in scripts)
{
await mdb.ExecuteAsync(statement);
}
Logger.LogDebug($"Schema SysCat was created in database {mdb.DbName}");
Logger.LogDebug("Schema tables SysCat.SysAreas and SysCat.SysSchemas was created in SysCat Schema");
string owner = await GetDefaultDbSchemaAsync();
int id = await mdb.ExecuteAsync <int>(SqlServer.AddSysAreas,
new MdbParameter("@SchemaName", owner),
new MdbParameter("@SchemaOwner", owner),
new MdbParameter("@SchemaVersion", schema_version));
id = await mdb.ExecuteAsync <int>(SqlServer.AddSysAreas,
new MdbParameter("@SchemaName", "SysCat"),
new MdbParameter("@SchemaOwner", owner),
new MdbParameter("@SchemaVersion", schema_version));
await mdb.CommitAsync();
}
catch (Exception e)
{
mdb.RollBack();
Logger.LogError($"CreateSchema error: {e.Message}");
throw;
}
}
static async Task insertTestTableAsync(MdbContext ctx, FileLogger l)
{
l.Debug("Insert Test Table Start");
string sql = @"Insert Into TestTable (ID, Name, DateOper, Handle)
Values(@ID, @Name, @DateOper, @Handle)" ;
await ctx.BeginTransactionAsync();
int i = 0;
for (; i < 100; i++)
{
await ctx.ExecuteAsync(sql,
new MdbParameter("@ID", Guid.NewGuid()),
new MdbParameter("@Name", "Тестовая строка № " + i.ToString()),
new MdbParameter("@DateOper", vbo.Date().AddDays(i - 1000)),
new MdbParameter("@Handle", i) { NullIfEmpty = false });;
}
await ctx.CommitAsync();
l.Debug($"Insert Test Table Finish rows result {i}");
}
private async Task DropSchemaAsync(string objectName)
{
MdbContext mdb = Factory.GetMdbContext();
var schema = await _schemaRepo.GetSchemaAsync(objectName);
var schemaFromDb = await GetObjectSchemaAsync(schema.DbObjectName.ToString());
string[] sqlList;
if (schemaFromDb == null)
{
sqlList = Array.Empty <string>();
}
else
{
sqlList = await DropSchemaStatementsAsync(schemaFromDb);
}
if (sqlList.Length > 0)
{
await mdb.BeginTransactionAsync();
try
{
foreach (var sql in sqlList)
{
Logger.LogDebug(sql);
await mdb.ExecuteAsync(sql);
}
await mdb.CommitAsync();
}
catch
{
mdb.RollBack();
throw;
}
}
}
public async Task CreateDbSchemaAsync(string dbName = db_name)
{
var s = SQLCat.GetCatalog(_mdb.ProviderName);
var test = await _mdb.ExecuteAsync <string>(s["TestSchema"]);
if (test.IsEmpty())
{
try
{
await _mdb.BeginTransactionAsync();
foreach (string statement in s["CreateSchemaObjects"].Split("--go", StringSplitOptions.RemoveEmptyEntries))
{
await _mdb.ExecuteAsync(statement);
}
_log.LogDebug($"Schema SysCat was created in database {_mdb.DbName}");
_log.LogDebug("Schema tables SysCat.SysAreas and SysCat.SysSchemas was created in SysCat Schema");
string owner = _defaultDbSchema; //await _mdb.ExecuteAsync<string>(s["GetCurrentSchema"]);
int id = await _mdb.ExecuteAsync <int>(s["AddSysAreas"],
new MdbParameter("@SchemaName", owner),
new MdbParameter("@SchemaOwner", owner),
new MdbParameter("@SchemaVersion", schema_version));
await _mdb.CommitAsync();
}
catch (Exception e)
{
_mdb.RollBack();
_log.LogError($"CreateSchema error: {e.Message}");
throw;
}
}
else
{
_log.LogDebug("Schema SysCat already exists in current database");
}
}