public SysCatManager(MdbContext context)
{
context.NullTest(nameof(context));
_mdb = context;
_log = _mdb.Logger;
if (_defaultDbSchema.IsEmpty())
{
_defaultDbSchema = _mdb.Execute <string>(SQLCat.GetCatalog(_mdb.ProviderName)["GetDefaultSchema"]);
}
}
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");
}
}
public async Task DropDbSchemaAsync(string dbName = db_name)
{
var s = SQLCat.GetCatalog(_mdb.ProviderName);
var test = await _mdb.ExecuteAsync <string>(s["TestSchema"]);
if (!test.IsEmpty())
{
try
{
await _mdb.ExecuteAsync(s["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}");
}
}
public static void register()
{
Dictionary <string, string> statements = new Dictionary <string, string>(StringComparer.CurrentCultureIgnoreCase);
statements["TestSchema"] = @"Select Name From sys.schemas Where name = 'SysCat'";
statements["CreateSchemaObjects"] = @"
Create Schema SysCat
--go
create table SysCat.SysAreas (
ID int identity(1, 1),
SchemaName varchar(60) not null,
SchemaOwner varchar(30) not null,
SchemaVersion varchar(30) not null,
SchemaConfig varchar(max) null,
constraint CKC_SCHEMACONFIG_SYSAREAS check(SchemaConfig is null or isjson(SchemaConfig) = 1),
IsDefault bit not null,
UpdateTime datetime not null,
DateBegin datetime not null,
DateEnd datetime null
constraint PK_SYSAREAS primary key (ID)
)
create unique nonclustered index AK1_SysAreas on SysCat.SysAreas(SchemaName ASC)
--go
create procedure SysCat.Add_SysAreas
@SchemaName varchar(60),
@SchemaOwner varchar(30),
@SchemaVersion varchar(30),
@SchemaConfig varchar(max)=null,
@IsDefault bit=0,
@DateBegin datetime=null,
@DateEnd datetime=null
as
begin
declare @id int
if @DateBegin is null
set @DateBegin = GETDATE()
if @IsDefault = 0 and not exists (Select 1 From SysCat.SysAreas)
set @IsDefault = 1
insert into [SysCat].[SysAreas]
([SchemaName]
,[SchemaOwner]
,[SchemaVersion]
,[SchemaConfig]
,[IsDefault]
,[UpdateTime]
,[DateBegin]
,[DateEnd])
output inserted.ID
values (@SchemaName, @SchemaOwner, @SchemaVersion, @SchemaConfig,
@IsDefault, GETDATE(), @DateBegin, @DateEnd)
end
--go
/************SysSchemas**************/
create table SysCat.SysSchemas(
ID int identity(1, 1),
SysAreaID int not null,
ObjectType varchar(30) not null,
ObjectName varchar(60) not null,
ObjectSchema varchar(max) not null
constraint CKC_OBJECTSCHEMA_SYSSCHEMAS check(isjson(ObjectSchema) = (1)),
UpdateTime datetime not null,
Version varchar(30) not null,
SyncState bit not null,
DateBegin datetime not null,
DateEnd datetime null
constraint PK_SYSSCHEMAS primary key(ID)
)
create unique nonclustered index AK1_SysSchemas on SysCat.SysSchemas(SysAreaID ASC, ObjectName ASC)
alter table SysCat.SysSchemas
add constraint FK_SYSSCHEMAS_SYSAREAS foreign key(SysAreaID)
references SysCat.SysAreas(ID)
--go
create procedure SysCat.Add_SysSchemas
@SysAreaID int = null,
@ObjectType varchar(30) = 'table', --may be table, action, function, view,
@ObjectName varchar(60),
@ObjectSchema varchar(max),
@Version varchar(30) = '0.0.1',
@DateBegin datetime = null,
@DateEnd datetime = null
as
begin
declare @id int
if @DateBegin is null
set @DateBegin = GETDATE()
if @SysAreaID is null
select Top 1 @SysAreaID = ID from SysCat.SysAreas where IsDefault = 1
insert into [SysCat].[SysSchemas]
([SysAreaID]
,[ObjectType]
,[ObjectName]
,[ObjectSchema]
,[UpdateTime]
,[Version]
,[SyncState]
,[DateBegin]
,[DateEnd])
output inserted.ID
values
(@SysAreaID
,@ObjectType
,@ObjectName
,@ObjectSchema
,GETDATE()
,@Version
,0
,@DateBegin
,@DateEnd)
end
--go
create view SysCat.V_SysSchemas as
select S.[ID]
,S.[SysAreaID]
,A.[SchemaName]
,A.[SchemaOwner]
,A.[SchemaVersion]
,A.[IsDefault]
,S.[ObjectType]
,S.[ObjectName]
,S.[ObjectSchema]
,S.[UpdateTime]
,S.[Version]
,S.[SyncState]
,S.[DateBegin]
,S.[DateEnd]
from [SysCat].[SysAreas] A
inner join [SysCat].[SysSchemas] S On A.ID = S.[SysAreaID]
" ;
statements["DropSchema"] = @"
begin
drop view if exists SysCat.V_SysSchemas
drop procedure if exists SysCat.Add_SysSchemas
drop table if exists [SysCat].[SysSchemas]
drop procedure if exists SysCat.Add_SysAreas
drop table if exists [SysCat].[SysAreas]
drop schema [SysCat]
end" ;
statements["GetCurrentSchema"] = @"Select schema_name()";
statements["AddSysAreas"] = @"SysCat.Add_SysAreas";
statements["AddSysSchemas"] = @"SysCat.Add_SysSchemas";
statements["GetDefaultSchema"] = @"
if exists (Select Name From sys.schemas Where name = 'SysCat')
select Top 1 IsNull(S.SchemaName, sys.schemas.Name) as Name From sys.schemas
left join SysCat.SysAreas S On S.IsDefault = 1
where sys.schemas.name = schema_name()
else
select schema_name() as Name" ;
SQLCat.SetCatalog("System.Data.SqlClient", statements);
}