private static string GetExecuteStatement <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var prefix = GetExternalInsertPrefix(scheme.TriggerType);
var sql = "EXECUTE STATEMENT (:S) (";
var counter = 1;
foreach (var propertyInfo in scheme.ModelType.GetProperties())
{
var attribute = (DbPropertyAttribute)propertyInfo.GetCustomAttribute(typeof(DbPropertyAttribute));
if (attribute.OnlyTempDbField && attribute.TriggerProperty == scheme.TriggerType)
{
sql += $"V{counter} := 1, ";
counter++;
}
else
{
if (attribute.OnlyTempDbField)
{
continue;
}
sql += $"V{counter} := {prefix}.{attribute.PropertyName}, ";
counter++;
}
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")";
return(sql);
}
private static string GetTableFieldsSequence <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var sequence = "(";
foreach (var propertyInfo in scheme.ModelType.GetProperties())
{
var attribute = (DbPropertyAttribute)propertyInfo.GetCustomAttribute(typeof(DbPropertyAttribute));
if (attribute.OnlyTempDbField && attribute.TriggerProperty == scheme.TriggerType)
{
sequence += $"{attribute.PropertyName},";
}
else
{
if (attribute.OnlyTempDbField)
{
continue;
}
sequence += $"{attribute.PropertyName},";
}
}
sequence = sequence.Substring(0, sequence.Length - 1);
sequence += ") ";
return(sequence);
}
private static string GetInsertQuery <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var query = $"S = 'INSERT INTO {scheme.ExternalTableName} ";
query += GetTableFieldsSequence(scheme);
query += GetValuesSequence(scheme);
return(query);
}
public string BuildSqlTrigger <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var sql = GetOpenPart(scheme);
sql += GetInsertQuery(scheme);
sql += GetExecuteStatement(scheme);
sql += GetClosePart(scheme);
return(sql);
}
internal static SqlTriggerScheme <TModel> CreateScheme(string externalDataSource,
string externalUser, string externalPassword, TriggerType trigger)
{
var scheme = new SqlTriggerScheme <TModel>(trigger)
{
ExternalDataSource = externalDataSource,
ExternalPassword = externalPassword,
ExternalUser = externalUser,
};
return(scheme);
}
private static string GetClosePart <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var sql = $"ON EXTERNAL DATA SOURCE '{scheme.ExternalDataSource}' ";
sql += $"AS USER '{scheme.ExternalUser}' PASSWORD '{scheme.ExternalPassword}';";
sql += Environment.NewLine;
sql += $"POST_EVENT '{scheme.EventName}';";
sql += $"{Environment.NewLine}END";
return(sql);
}
private static string GetOpenPart <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var triggerName = TriggerTypeConverter.ToName(scheme.TriggerType);
var sql = $"CREATE OR ALTER TRIGGER {scheme.TriggerName} FOR {scheme.MainTableName}{Environment.NewLine}";
sql += $"ACTIVE AFTER {triggerName} POSITION 0{Environment.NewLine}";
sql += $"AS{Environment.NewLine}";
sql += $"DECLARE S VARCHAR(3000);{Environment.NewLine}";
sql += $"BEGIN{Environment.NewLine}";
return(sql);
}
private static string GetValuesSequence <TModel>(SqlTriggerScheme <TModel> scheme)
where TModel : IModel, new()
{
var sequence = "values (";
var counter = 1;
foreach (var propertyInfo in scheme.ModelType.GetProperties())
{
var attribute = (DbPropertyAttribute)propertyInfo.GetCustomAttribute(typeof(DbPropertyAttribute));
if (attribute.OnlyTempDbField)
{
continue;
}
sequence += $":V{counter}, ";
counter++;
}
sequence += $":V{counter}, "; // for insert, update or delete
sequence = sequence.Substring(0, sequence.Length - 2);
sequence += ")'; ";
return(sequence);
}
public IEnumerable <SqlTriggerScheme <TModel> > CreateSchemes(params TriggerType[] triggerTypes)
{
return(SqlTriggerScheme <TModel> .CreateSchemes(ExternalDbSource,
ExternalUser, ExternalPassword, triggerTypes));
}
public SqlTriggerScheme <TModel> CreateScheme(TriggerType triggerType)
{
return(SqlTriggerScheme <TModel> .CreateScheme(ExternalDbSource,
ExternalUser, ExternalPassword, triggerType));
}
