private async Task executeTestAsync()
{
using (FileLog l = new FileLog("MdbContextTest", new FileLogSettings()
{
DateFolderMask = "yyyy-MM-dd"
}))
{
MdbContextOptions.GetOptions().CommandTimeout = 120;
using (var ctx = new MdbContext(_cn))
{
l.Debug("Test 1 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)" ;
i = await ctx.ExecuteAsync(sql);
l.Debug($"Test 1 Finish rows result {i}");
l.Debug("Test 2 Start ");
sql = @"Insert Into TestTable (ID, Name, DateOper, Handle)
Values(@ID, @Name, @DateOper, @Handle)" ;
i = 0;
for (; i < 1000; 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 });;
}
l.Debug($"Test 2 Finish rows result {i}");
l.Debug("Test 3 Start ");
i = 0;
using (var dr = await ctx.GetReaderAsync("Select * From TestTable Order By Handle"))
{
for (; dr.Read(); i++)
{
l.Debug(dr.GetRowJSON());
}
}
l.Debug($"Test 3 Finish rows result {i}");
l.Debug("Test 4 Start ");
sql = "Drop Table TestTable";
i = await ctx.ExecuteAsync(sql);
l.Debug($"Test 4 Finish rows result {i}");
}
}
}
private static async Task <string> GetDefaultDbSchemaAsync(MdbContext mdb)
{
if (_defaultDbSchema.IsEmpty())
{
_defaultDbSchema = await mdb.ExecuteAsync <string>(SQLite.GetDefaultSchema);
}
return(_defaultDbSchema);
}
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;
}
}
/// <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 dropTestTableAsync(MdbContext ctx, FileLogger l)
{
l.Debug("Drop Test Table Start");
int i = 0;
string sql = @"Drop Table TestTable";
i = await ctx.ExecuteAsync(sql);
l.Debug($"Drop Test Table Finish rows result {i}");
}
public async Task <DataPackage> InvokeAsync(ActionInfo ai, DataPackage dp)
{
DateTime date = (DateTime)dp["@Date"];
string branchID = (string)dp["@BranchID"];
string dealType = (string)dp["@DealType"];
int confirmed = (int)dp["@Confirmed"];
string batch = (string)dp["@Batch"];
var ctx = ai.GetContext();
var cs = ctx.Services
.GetRequiredService <IConfiguration>()
.GetSection($"connectionStrings:{ctx.ConnectionName}").Get <ConnectInfo>();
string[] filials = DealValuesCalculate.GetParamListData(ai, "@BranchID", branchID, "0");
string[] dealTypes = DealValuesCalculate.GetParamListData(ai, "@DealType", dealType, "Все");
var pipe = ctx.Services
.GetRequiredService <PipeQueue>();
StringBuilder sb = new StringBuilder();
foreach (var filial in filials)
{
foreach (var dtype in dealTypes)
{
using (MdbContext mdb = new MdbContext(cs))
{
try
{
await mdb.ExecuteAsync($@"
exec workdb..mib_msfo_Reserve_OperGen
@Date = '{date.ToString("yyyyMMdd")}'
,@BranchID = {filial}
,@DealType = '{dtype}'
,@Confirmed = {confirmed}
,@Batch = '{batch}'" );
string result = $"Success mib_msfo_Reserve_OperGen {date} filial={filial} deal type={dtype}";
pipe.Write(ctx, result);
sb.AppendLine(result);
}
catch (Exception ex)
{
string message = $"{LogLevels.Error} {ex.Message}\n{ex.StackTrace}";
pipe.Write(ctx, message);
sb.AppendLine(message);
}
}
}
}
return(ai.GetOutputParamTable()
.AddNew()
.SetValue("@Result", sb.ToString())
.Update());
}
public override async Task DeleteSchemaAsync(string objectName)
{
JMXObjectName name = objectName;
MdbContext mdb = Factory.GetMdbContext();
var schema = await GetSchemaAsync(mdb, name.AreaName, name.ObjectName);
await mdb.ExecuteAsync(SQLite.DelSysSchemas,
new MdbParameter("@ID", schema.ID));
_schemaCache.Remove(name.ToString());
}
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;
}
}
private static async Task RecreateSchemaAsync(MdbContext mdb, SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema)
{
int recCount = await mdb.ExecuteAsync <int>($"select count(*) from {fromDbSchema.DbObjectName}");
foreach (var fk in fromDbSchema.ParentRelations)
{
sb.WriteDropParentRelationStatement(fk);
}
foreach (var fk in fromDbSchema.ForeignKeys)
{
sb.WriteDropFKStatement(fk, fromDbSchema);
}
string tmpTableName = fromDbSchema.DbObjectName.ObjectName + "_" + DateTime.Now.Subtract(vbo.Date()).Seconds.ToString();
if (recCount > 0)
{
sb.WriteRenameTableStatement(tmpTableName, fromDbSchema);
}
else
{
sb.WriteDropTableStatement(null, fromDbSchema);
}
sb.WriteCreateTableStatements(schema);
if (recCount > 0)
{
sb.WriteInsertRowsStatement(tmpTableName, schema);
sb.WriteDropTableStatement(tmpTableName, fromDbSchema);
}
sb.WriteCreatePKStatement(schema);
foreach (var att in schema.Attributes)
{
sb.WriteCreateConstraintStatement(att, schema);
}
foreach (var index in schema.Indexes)
{
sb.WriteCreateIndexStatement(index, schema);
}
foreach (var fk in schema.ForeignKeys)
{
sb.WriteCreateFKStatement(fk, schema);
}
/// Need test
foreach (var fk in fromDbSchema.ParentRelations)
{
sb.WriteCreateParentRelationStatement(fk);
}
}
private static async Task DropDbSchemaAsync(MdbContext mdb, ILogger log)
{
var test = await mdb.ExecuteAsync <string>(SQLite.TestSchema);
if (!test.IsEmpty())
{
try
{
await mdb.ExecuteAsync(SQLite.DropSchema);
log.LogDebug($"Schema SysCat was deleted from database {mdb.DbName}");
}
catch (Exception e)
{
log.LogError($"Delete Schema error: {e.Message}");
throw;
}
}
else
{
log.LogDebug($"Schema SysCat not exists in database {mdb.DbName}");
}
}
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();
}
public static async Task <string> GetSqlVersion(MdbContext mdb)
{
if (_sqlVersion.IsEmpty())
{
_sqlVersion = await mdb.ExecuteAsync <string>(SQLite.SQLVersion);
if (_sqlVersion.IsEmpty())
{
_sqlVersion = "3";
}
else
{
_sqlVersion = _sqlVersion.GetToken(0, ".");
}
}
return(_sqlVersion);
}
public async Task <DataPackage> InvokeAsync(ActionInfo ai, DataPackage dp)
{
string objectName = (string)dp["@ObjectName"];
long[] ids = ((string)dp["@IDs"])
.Split(new char[] { ',', ';' })
.Select(s => s.ToLongOrDefault())
.ToArray();
var ctx = ai.GetContext();
var cs = ctx.Services
.GetRequiredService <IConfiguration>()
.GetSection($"connectionStrings:{ctx.ConnectionName}").Get <ConnectInfo>();
var pipe = ctx.Services
.GetRequiredService <PipeQueue>();
StringBuilder sb = new StringBuilder();
foreach (long i in ids)
{
using (MdbContext mdb = new MdbContext(cs))
{
try
{
string result = await mdb.ExecuteAsync <string>($@"
select 'Session ID = ' + cast(@@SPID as varchar(10))
+ ' User Name = ' + '{ctx.UserName}'
+ ' Connection Name = ' + '{ctx.ConnectionName}'
+ ' Param Value = ' + '{i}'" );
pipe.Write(ctx, result);
sb.AppendLine(result);
}
catch (Exception ex)
{
string message = $"{LogLevels.Error} {ex.Message}\n{ex.StackTrace}";
}
}
}
return(ai.GetOutputParamTable()
.AddNew()
.SetValue("@Result", sb.ToString())
.Update());
}
private static async Task <string> GetSqlVersion(MdbContext mdb)
{
int mdbHash = mdb.MdbHash();
if (!_versions.TryGetValue(mdbHash, out string sqlVersion))
{
sqlVersion = await mdb.ExecuteAsync <string>(SqlServer.SQLVersion);
if (sqlVersion.IsEmpty())
{
sqlVersion = "10";
}
else
{
sqlVersion = sqlVersion.GetToken(0, ".");
}
_versions.Add(mdbHash, sqlVersion);
}
return(sqlVersion);
}
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");
}
}
