string CreatePFrameSql(string chapter, IEnumerable <IKFrameDbSource> sources)
{
var unionSql = string.Join(" Union All\n", sources.Select(y => $"Select Top 1 Null From {y.Table.Name} Where [{y.Table.Key}] = rk.{(UseVariant ? "Id" : y.Table.Id)} And rk.Param = '{y.Param.key}'").ToArray());
var b = new StringBuilder();
b.AppendLine(
$@"IF (OBJECT_ID('[{Schema}].[p_Get{FormatName(chapter, "PFrame")}]', 'P') IS NOT NULL) BEGIN
DROP PROCEDURE [{Schema}].[p_Get{FormatName(chapter, "PFrame")}];
END;
GO
CREATE PROCEDURE [{Schema}].[p_Get{FormatName(chapter, "PFrame")}](@iframe datetime, @iframeL datetime, @expand bit = 0) AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Declare @frame datetime = GETUTCDATE(); Set @frame = DATEADD(MS, -DATEPART(MS, @frame), @frame);
Declare @frameId int = (Select Top 1 Id From [{Schema}].[{FormatName(chapter, "Frame")}] Where Frame = @iframe);
-- FRAME
Select @frame as Frame, @frameId as FrameId;
IF (@frameId Is Null) BEGIN
Return;
END;
-- DELETE
Select Count(*)
From [{Schema}].[{FormatName(chapter, "FrameKey")}] as rk
Where rk.FrameId = @frameId And Not Exists(
{unionSql});
IF (@expand = 1) BEGIN
Select {(UseVariant ? "rk.Id" : "rk.Id0, rk.Id1, rk.Id2")}, rk.Param
From [{Schema}].[{FormatName(chapter, "FrameKey")}] as rk
Where rk.FrameId = @frameId And Not Exists(
{unionSql});
END;");
var x = new Source.X();
foreach (var source in sources)
{
b.AppendLine($@"
-- SOURCE: {source.Param.name.ToUpperInvariant()}
{source.Build.max(x)}
IF (@expand = 1) BEGIN
{source.Build.body(x)}
END;");
}
b.AppendLine("GO");
return(b.ToString());
}
string CreateIFrameSql(string chapter, IEnumerable <IKFrameDbSource> sources)
{
var b = new StringBuilder();
b.AppendLine(
$@"IF (OBJECT_ID('[{Schema}].[p_Get{FormatName(chapter, "IFrame")}]', 'P') IS NOT NULL) BEGIN
DROP PROCEDURE [{Schema}].[p_Get{FormatName(chapter, "IFrame")}];
END;
GO
CREATE PROCEDURE [{Schema}].[p_Get{FormatName(chapter, "IFrame")}] AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
Declare @frame datetime = GETUTCDATE(); Set @frame = DATEADD(MS, -DATEPART(MS, @frame), @frame);
Delete [{Schema}].[{FormatName(chapter, "Frame")}]
Where Frame < @frame - {KFrameTiming.PFrameSourceExpireInDays()};
Insert [{Schema}].[{FormatName(chapter, "Frame")}] (Frame)
Values (@frame);
Declare @frameId int = SCOPE_IDENTITY();
-- FRAME
Select @frame as Frame, @frameId as FrameId;");
var x = new Source.X();
foreach (var source in sources)
{
b.AppendLine(
$@"-- SOURCE: {source.Param.name?.ToUpperInvariant()}
Insert [{Schema}].[{FormatName(chapter, "FrameKey")}] ({(UseVariant ? "Id" : source.Table.Id)}, Param, FrameId)
{source.Build.key($"[{source.Table.Key}], '{source.Param.key}', @frameId")}
{source.Build.body(null)}");
}
b.AppendLine(@"
COMMIT TRANSACTION;
GO");
return(b.ToString());
}
