public AdoFlattenUpdateByViewAction(OutputContext output, AdoSqlModel model, IDbConnection cn, IDbTransaction trans)
{
_output = output;
_model = model;
_cn = cn;
_trans = trans;
}
public AdoFlattenInsertByViewAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model, IDbConnection cn, IDbTransaction trans)
{
_output = output;
_cf = cf;
_model = model;
_trans = trans;
_cn = cn;
}
public AdoFlattenTwoPartUpdateAction(
OutputContext output,
IConnectionFactory cf,
AdoSqlModel model,
string sql
)
{
_output = output;
_cf = cf;
_model = model;
_sql = sql;
}
public AdoFlattenTwoPartUpdateAction(
OutputContext output,
IConnectionFactory cf,
AdoSqlModel model,
string sql,
IDbConnection cn,
IDbTransaction trans
)
{
_output = output;
_cf = cf;
_model = model;
_sql = sql;
_trans = trans;
_cn = cn;
}
private static IEnumerable <ExpandoObject> Read(IDbConnection cn, string sql, AdoSqlModel model)
{
using (var reader = cn.ExecuteReader(sql, new { model.Threshold }, null, 0, CommandType.Text)) {
while (reader.Read())
{
var obj = new ExpandoObject();
var dict = (IDictionary <string, object>)obj;
for (var i = 0; i < model.FieldNames.Length; i++)
{
dict[model.FieldNames[i]] = reader.GetValue(i);
}
yield return(obj);
}
}
}
public ActionResponse Execute()
{
if (_output.Process.Entities.Sum(e => e.Inserts + e.Updates + e.Deletes) == 0)
{
return(new ActionResponse(200, "nothing to flatten"));
}
var model = new AdoSqlModel(_output, _cf);
if (_output.Process.Mode == "init")
{
return(new AdoFlattenFirstRunAction(_output, _cf, model).Execute());
}
ActionResponse updateResponse = null;
ActionResponse insertResponse = null;
using (var cn = _cf.GetConnection(Constants.ApplicationName)) {
cn.Open();
using (var trans = cn.BeginTransaction()) {
_output.Info("Begin Flat Insert/Update Transaction");
if (model.MasterEntity.Inserts > 0)
{
if (_cf.AdoProvider == AdoProvider.SqlCe)
{
insertResponse = new AdoFlattenInsertBySelectAction(_output, _cf, model, cn, trans).Execute();
insertResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
if (insertResponse.Code != 200)
{
trans.Rollback();
return(insertResponse);
}
}
else
{
insertResponse = new AdoFlattenInsertByViewAction(_output, _cf, model, cn, trans).Execute();
insertResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
if (insertResponse.Code != 200)
{
trans.Rollback();
return(insertResponse);
}
}
}
switch (_cf.AdoProvider)
{
case AdoProvider.SqlCe:
// this provider does NOT support views, and does NOT support UPDATE ... SET ... FROM ... syntax
var masterAlias = Utility.GetExcelName(model.MasterEntity.Index);
var builder = new StringBuilder();
builder.AppendLine($"SELECT {_output.SqlStarFields(_cf)}");
foreach (var from in _output.SqlStarFroms(_cf))
{
builder.AppendLine(@from);
}
builder.AppendFormat("INNER JOIN {0} flat ON (flat.{1} = {2}.{3})", _cf.Enclose(_output.Process.Name + _output.Process.FlatSuffix), model.EnclosedKeyLongName, masterAlias, model.EnclosedKeyShortName);
builder.AppendLine($" WHERE {masterAlias}.{model.Batch} > @Threshold; ");
//todo: add common cn and trans
updateResponse = new AdoFlattenTwoPartUpdateAction(_output, _cf, model, builder.ToString(), cn, trans).Execute();
break;
case AdoProvider.SqLite:
// this provider supports views, but does NOT support UPDATE ... SET ... FROM ... syntax
var sql = $@"
SELECT s.{string.Join(",s.", model.Aliases)}
FROM {model.Master} m
INNER JOIN {model.Flat} f ON (f.{model.EnclosedKeyLongName} = m.{model.EnclosedKeyShortName})
INNER JOIN {model.Star} s ON (s.{model.EnclosedKeyLongName} = m.{model.EnclosedKeyShortName})
WHERE m.{model.Batch} > @Threshold;";
//todo: add common cn and trans
updateResponse = new AdoFlattenTwoPartUpdateAction(_output, _cf, model, sql, cn, trans).Execute();
break;
default:
// these providers support views and UPDATE ... SET ... FROM ... syntax (server based)
updateResponse = new AdoFlattenUpdateByViewAction(_output, model, cn, trans).Execute();
break;
}
if (updateResponse.Code == 200)
{
trans.Commit();
_output.Info("Committed Flat Insert/Update Transaction");
}
else
{
updateResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
trans.Rollback();
_output.Warn("Rolled Back Flat Insert/Update Transaction");
}
return(updateResponse);
}
}
}
public AdoFlattenFirstRunAction(OutputContext context, IConnectionFactory cf, AdoSqlModel model)
{
_context = context;
_cf = cf;
_model = model;
}
public AdoFlattenUpdateByViewAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model)
{
_output = output;
_cf = cf;
_model = model;
}
public AdoFlattenInsertBySelectAction(OutputContext output, IConnectionFactory cf, AdoSqlModel model)
{
_output = output;
_cf = cf;
_model = model;
}
public ActionResponse Execute()
{
if (_output.Process.Entities.Sum(e => e.Inserts + e.Updates + e.Deletes) == 0)
{
return(new ActionResponse(200, "nothing to flatten"));
}
var model = new AdoSqlModel(_output, _cf);
if (model.MasterEntity.Inserts > 0)
{
if (_output.Process.Mode == "init")
{
return(new AdoFlattenFirstRunAction(_output, _cf, model).Execute());
}
if (_cf.AdoProvider == AdoProvider.SqlCe)
{
var insertAction = new AdoFlattenInsertBySelectAction(_output, _cf, model);
var insertResponse = insertAction.Execute();
insertResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
if (insertResponse.Code != 200)
{
return(insertResponse);
}
}
else
{
var insertAction = new AdoFlattenInsertByViewAction(_output, _cf, model);
var insertResponse = insertAction.Execute();
insertResponse.Action = new Action {
Type = "internal",
Description = "Flatten Action",
ErrorMode = "abort"
};
if (insertResponse.Code != 200)
{
return(insertResponse);
}
}
}
switch (_cf.AdoProvider)
{
case AdoProvider.SqlCe:
// this provider does NOT support views, and does NOT support UPDATE ... SET ... FROM ... syntax
var masterAlias = Utility.GetExcelName(model.MasterEntity.Index);
var builder = new StringBuilder();
builder.AppendLine($"SELECT {_output.SqlStarFields(_cf)}");
foreach (var from in _output.SqlStarFroms(_cf))
{
builder.AppendLine(@from);
}
builder.AppendFormat("INNER JOIN {0} flat ON (flat.{1} = {2}.{3})", _cf.Enclose(_output.Process.Flat), model.EnclosedKeyLongName, masterAlias, model.EnclosedKeyShortName);
builder.AppendLine($" WHERE {masterAlias}.{model.Batch} > @Threshold; ");
return(new AdoFlattenTwoPartUpdateAction(_output, _cf, model, builder.ToString()).Execute());
case AdoProvider.SqLite:
// this provider supports views, but does NOT support UPDATE ... SET ... FROM ... syntax
var sql = $@"
SELECT s.{string.Join(",s.", model.Aliases)}
FROM {model.Master} m
INNER JOIN {model.Flat} f ON (f.{model.EnclosedKeyLongName} = m.{model.EnclosedKeyShortName})
INNER JOIN {model.Star} s ON (s.{model.EnclosedKeyLongName} = m.{model.EnclosedKeyShortName})
WHERE m.{model.Batch} > @Threshold;";
return(new AdoFlattenTwoPartUpdateAction(_output, _cf, model, sql).Execute());
default:
// these providers support views and UPDATE ... SET ... FROM ... syntax (server based)
return(new AdoFlattenUpdateByViewAction(_output, _cf, model).Execute());
}
}
