public virtual void CreateSequenceTriggerForColumn(string columnName, string tableName, string schemaName, MigrationCommandListBuilder builder)
{
var identitySequenceName = CreateSequenceTriggerSequenceName(columnName, tableName, schemaName);
builder.AppendLine("EXECUTE BLOCK");
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (not exists(select 1 from rdb$generators where rdb$generator_name = '");
builder.Append(identitySequenceName);
builder.Append("')) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("execute statement 'create sequence ");
builder.Append(identitySequenceName);
builder.Append("'");
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.AppendLine();
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
builder.EndCommand();
builder.Append("CREATE TRIGGER ");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(CreateSequenceTriggerName(columnName, tableName, schemaName)));
builder.Append(" ACTIVE BEFORE INSERT ON ");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(tableName, schemaName));
builder.AppendLine();
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
builder.IncrementIndent();
builder.Append("if (new.");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(columnName));
builder.Append(" is null) then");
builder.AppendLine();
builder.AppendLine("begin");
builder.IncrementIndent();
builder.Append("new.");
builder.Append(_sqlGenerationHelper.DelimitIdentifier(columnName));
builder.Append(" = next value for ");
builder.Append(identitySequenceName);
builder.Append(_sqlGenerationHelper.StatementTerminator);
builder.AppendLine();
builder.DecrementIndent();
builder.AppendLine("end");
builder.DecrementIndent();
builder.Append("END");
builder.AppendLine();
builder.AppendLine(_sqlGenerationHelper.StatementTerminator);
builder.EndCommand();
}
protected virtual void AddPolicy(string schema, string table, MigrationCommandListBuilder builder)
{
if (string.IsNullOrWhiteSpace(schema))
{
schema = "dbo";
}
builder.AppendLines(
$@"ALTER SECURITY POLICY rls.tenantAccessPolicy
ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON {schema}.{table},
ADD BLOCK PREDICATE rls.fn_tenantAccessPredicate(TenantId) ON {schema}.{table}" );
builder.EndCommand(false);
}
private void Generate(RemoveExtendedPropertyOperation operation, MigrationCommandListBuilder builder)
{
builder.Append("EXEC sys.sp_dropextendedproperty");
builder.Append($" @name = N'{operation.ExtendedProperty.Key}'");
builder.Append(", @level0type = N'SCHEMA'");
builder.Append($", @level0name = N'{operation.SchemaTableColumn.Schema ?? "dbo"}'");
builder.Append(", @level1type = N'TABLE'");
builder.Append($", @level1name = N'{operation.SchemaTableColumn.Table}'");
if (!String.IsNullOrEmpty(operation.SchemaTableColumn.Column))
{
builder.Append($@",@level2type = 'COLUMN', @level2name = '{operation.SchemaTableColumn.Column}'");
}
builder.EndCommand();
}
