void Destroy(IDbConnection cn)
{
_context.Warn("Initializing");
if (!_context.Connection.DropControl)
{
try {
cn.Execute(_context.SqlDeleteEntityFromControl(_cf), new { Entity = _context.Entity.Alias });
} catch (System.Data.Common.DbException ex) {
_context.Debug(() => ex.Message);
}
}
try {
cn.Execute(_context.SqlDropOutputView(_cf));
} catch (System.Data.Common.DbException ex) {
_context.Debug(() => ex.Message);
}
try {
cn.Execute(_context.SqlDropOutputViewAsTable(_cf));
} catch (System.Data.Common.DbException ex) {
_context.Debug(() => ex.Message);
}
try {
cn.Execute(_context.SqlDropOutput(_cf));
} catch (System.Data.Common.DbException ex) {
_context.Debug(() => ex.Message);
}
}
private string UpdateStatement(EntityStatus status)
{
var masterEntity = _c.Process.Entities.First(e => e.IsMaster);
var masterTable = _cf.Enclose(masterEntity.OutputTableName(_c.Process.Name));
var builder = new StringBuilder();
builder.AppendLine($"UPDATE {masterTable} ");
var batchColumn = masterEntity.TflBatchId().FieldName();
builder.AppendLine($"SET {_cf.Enclose(batchColumn)} = @{batchColumn}");
if (status.HasForeignKeys)
{
builder.AppendLine(", " + string.Join(",", status.ForeignKeys
.Select(f => f.FieldName())
.Select(name => $"{_cf.Enclose(name)} = @{name}")));
}
var key = masterEntity.TflKey().FieldName();
builder.Append($"WHERE {key} = @{key}");
var sql = builder.ToString();
_c.Debug(() => sql);
return(sql);
}
public object Detect()
{
if (string.IsNullOrEmpty(_context.Entity.Version))
{
return(null);
}
var version = _context.Entity.GetVersionField();
_context.Debug(() => $"Detecting max output version: {_context.Connection.Folder}.{_context.Entity.Alias}.{version.Alias}.");
var tflDeleted = _context.Entity.TflDeleted();
var sort = new Sort(new SortField(version.Alias, LuceneConversion.TypeSort(version.Type), true));
using (var searcher = _searcherFactory.Create()) {
var hits = searcher.Search(LuceneConversion.TypeSearch(tflDeleted, tflDeleted.Alias, false), null, 1, sort);
if (hits.TotalHits > 0)
{
var doc = searcher.Doc(hits.ScoreDocs[0].Doc);
var value = doc.Get(version.Alias);
_context.Debug(() => $"Found value: {value}");
return(version.Convert(value));
}
}
_context.Debug(() => "Did not find max output version");
return(null);
}
public void Write(IEnumerable <IRow> rows)
{
var sql = _context.SqlUpdateCalculatedFields(_parent, _cf);
var fields = _context.GetUpdateCalculatedFields().ToArray();
using (var cn = _cf.GetConnection()) {
cn.Open();
var trans = cn.BeginTransaction();
try {
foreach (var batch in rows.Partition(_context.Entity.UpdateSize))
{
_context.Debug(() => "got a batch!");
var data = batch.Select(r => r.ToExpandoObject(fields));
_context.Debug(() => "converted to expando object");
var batchCount = Convert.ToUInt32(cn.Execute(sql, data, trans, 0, CommandType.Text));
_context.Debug(() => $"Updated {batchCount} calculated field records!");
}
trans.Commit();
_context.Debug(() => "Committed updates.");
} catch (Exception ex) {
_context.Error(ex, ex.Message);
trans.Rollback();
}
}
}
public object Detect()
{
if (string.IsNullOrEmpty(_context.Entity.Version))
{
return(null);
}
var version = _context.Entity.GetVersionField();
_context.Debug(() => $"Detecting Max Output Version: {_context.Connection.Database}.{version.Alias.ToLower()}.");
var result = _solr.Query(
new SolrQueryByField(_context.Entity.TflDeleted().Alias, "false"),
new QueryOptions {
StartOrCursor = new StartOrCursor.Start(0),
Rows = 1,
Fields = new List <string> {
version.Alias
},
OrderBy = new List <SortOrder> {
new SortOrder(version.Alias, Order.DESC)
}
});
var value = result.NumFound > 0 ? result[0][version.Alias] : null;
if (value != null && value.GetType() != Constants.TypeSystem()[version.Type])
{
value = version.Convert(value);
}
_context.Debug(() => $"Found value: {value ?? "null"}");
return(value);
}
public void Write(IEnumerable <IRow> rows)
{
_output.Entity.UpdateCommand = _output.SqlUpdateOutput(_cf);
var count = (uint)0;
using (var cn = _cf.GetConnection()) {
cn.Open();
_output.Debug(() => "begin transaction");
var trans = cn.BeginTransaction();
try {
foreach (var batch in rows.Partition(_output.Entity.UpdateSize))
{
var batchCount = Convert.ToUInt32(cn.Execute(
_output.Entity.UpdateCommand,
batch.Select(r => r.ToExpandoObject(_output.GetUpdateFields().ToArray())),
trans,
0,
CommandType.Text
));
count += batchCount;
}
_output.Debug(() => "commit transaction");
trans.Commit();
_output.Entity.Updates += count;
_output.Info("{0} to {1}", count, _output.Connection.Name);
} catch (Exception ex) {
_output.Error(ex.Message);
_output.Warn("rollback transaction");
trans.Rollback();
}
}
}
public void Initialize()
{
foreach (var action in _actions)
{
_context.Debug(() => $"Initializing with {action.GetType().Name}");
action.Execute();
}
}
public ActionResponse Initialize()
{
foreach (var action in _actions)
{
_context.Debug(() => $"Initializing with {action.GetType().Name}");
action.Execute();
}
return(new ActionResponse()); //for now
}
void Destroy(IDbConnection cn)
{
try {
if (_context.Connection.DropControl)
{
cn.Execute(_context.SqlDropControl(_cf));
}
} catch (DbException ex) {
_context.Debug(() => ex.Message);
}
}
private int GetDepth(IDbConnection cn)
{
_context.Debug(() => "Checking Depth");
try {
return(cn.ExecuteScalar <int>(_context.SqlDepthFinder(_cf)));
} catch (System.Data.Common.DbException ex) {
_context.Warn($"Could not check depth of {_context.Entity.OutputViewName(_context.Process.Name)}");
_context.Debug(() => ex.Message);
return(1);
}
}
private void Destroy(IDbConnection cn)
{
try {
if (!_context.Connection.DropControl)
{
return;
}
var sql = _context.SqlDropControl(_cf);
cn.Execute(sql);
} catch (DbException ex) {
_context.Debug(() => ex.Message);
}
}
public static string SqlControlStartBatch(this OutputContext c)
{
var sql = $@"INSERT {SqlControlTableName(c)}([BatchId],[Entity],[Inserts],[Updates],[Deletes],[Start],[End]) VALUES(@BatchId,@Entity,0,0,0,GETUTCDATE(),NULL);";
c.Debug(sql);
return(sql);
}
public static string SqlControlLastBatchId(this OutputContext c)
{
var sql = $"SELECT ISNULL(MAX([BatchId]),0) FROM {SqlControlTableName(c)};";
c.Debug(sql);
return(sql);
}
public static string SqlDropControl(this OutputContext c)
{
var sql = $"DROP TABLE [{SqlControlTableName(c)}];";
c.Debug(sql);
return(sql);
}
public static string SqlDropOutputView(this OutputContext c)
{
var sql = $"DROP VIEW [{c.Entity.OutputViewName(c.Process.Name)}];";
c.Debug(sql);
return(sql);
}
public static string SqlCreateOutputUniqueClusteredIndex(this OutputContext c)
{
var sql = $"CREATE UNIQUE CLUSTERED INDEX [UX_{SqlIdentifier(c.Entity.OutputTableName(c.Process.Name))}_TflKey] ON [{c.Entity.OutputTableName(c.Process.Name)}] (TflKey ASC);";
c.Debug(sql);
return(sql);
}
public static string SqlGetOutputMaxVersion(this OutputContext c, Field version)
{
var sql = $"SELECT MAX([{version.Alias}]) FROM [{c.Entity.OutputViewName(c.Process.Name)}] WITH (NOLOCK);";
c.Debug(sql);
return(sql);
}
public ActionResponse Execute()
{
var open = _cf.AdoProvider == AdoProvider.Access ? "((" : string.Empty;
var close = _cf.AdoProvider == AdoProvider.Access ? ")" : string.Empty;
var command = $@"
INSERT INTO {_model.Flat}({string.Join(",", _model.Aliases)})
SELECT s.{string.Join(",s.", _model.Aliases)}
FROM {open}{_model.Master} m
LEFT OUTER JOIN {_model.Flat} f ON (f.{_model.EnclosedKeyLongName} = m.{_model.EnclosedKeyShortName}){close}
INNER JOIN {_model.Star} s ON (s.{_model.EnclosedKeyLongName} = m.{_model.EnclosedKeyShortName}){close}
WHERE f.{_model.EnclosedKeyLongName} IS NULL
AND m.{_model.Batch} > @Threshold;";
if (_cn.State != ConnectionState.Open)
{
_cn.Open();
}
try {
_output.Debug(() => command);
var count = _model.Threshold > 0 ? _cn.Execute(command, new { _model.Threshold }, commandTimeout: 0, transaction: _trans) : _cn.Execute(command, commandTimeout: 0, transaction: _trans);
_output.Info($"{count} record{count.Plural()} inserted into flat");
} catch (DbException ex) {
return(new ActionResponse(500, ex.Message));
}
return(new ActionResponse(200, "Ok"));
}
public void Delete(IEnumerable <IRow> rows)
{
var criteria = string.Join(" AND ", _output.Entity.GetPrimaryKey().Select(f => f.FieldName()).Select(n => _cf.Enclose(n) + " = @" + n));
var sql = $"UPDATE {_cf.Enclose(_output.Entity.OutputTableName(_output.Process.Name))} SET {_output.Entity.TflDeleted().FieldName()} = CAST(1 AS BIT), {_output.Entity.TflBatchId().FieldName()} = {_output.Entity.BatchId} WHERE {criteria}";
_output.Debug(() => sql);
var count = (uint)0;
using (var cn = _cf.GetConnection()) {
cn.Open();
foreach (var batch in rows.Partition(_output.Entity.DeleteSize))
{
var trans = cn.BeginTransaction();
var batchCount = Convert.ToUInt32(cn.Execute(
sql,
batch.Select(r => r.ToExpandoObject(_fields)),
trans,
0,
CommandType.Text
));
trans.Commit();
count += batchCount;
}
_output.Entity.Deletes += count;
if (_output.Entity.Deletes > 0)
{
_output.Info("{0} deletes from {1}", _output.Entity.Deletes, _output.Connection.Name);
}
}
}
public static string SqlDropStarView(this OutputContext c, IConnectionFactory cf)
{
var sql = $"DROP {(cf.AdoProvider == AdoProvider.Access ? "TABLE" : "VIEW")} {cf.Enclose(c.Process.Star)}{cf.Terminator}";
c.Debug(() => sql);
return(sql);
}
public void Write(IEnumerable <IRow> rows)
{
var sql = _output.SqlInsertIntoOutput(_cf);
var count = 0;
using (var cn = _cf.GetConnection()) {
cn.Open();
var trans = cn.BeginTransaction();
try {
foreach (var batch in rows.Partition(_output.Entity.InsertSize))
{
var batchCount = cn.Execute(
sql,
batch.Select(r => r.ToExpandoObject(_output.OutputFields)),
trans,
0,
CommandType.Text
);
count += batchCount;
_output.Increment(batchCount);
}
trans.Commit();
} catch (Exception ex) {
_output.Error(ex, ex.Message);
trans.Rollback();
}
_output.Debug(() => $"{count} to {_output.Connection.Name}");
}
_output.Entity.Inserts += count;
}
public static string SqlDropFlatTable(this OutputContext c, IConnectionFactory cf)
{
var sql = $"DROP TABLE {cf.Enclose(c.Process.Flat)}{cf.Terminator}";
c.Debug(() => sql);
return(sql);
}
public static string SqlControlEndBatch(this OutputContext c)
{
var sql = $"UPDATE {SqlControlTableName(c)} SET [Inserts] = @Inserts, [Updates] = @Updates, [Deletes] = @Deletes, [End] = GETDATE() WHERE BatchId = @BatchId;";
c.Debug(sql);
return(sql);
}
public static string SqlDeleteEntityFromControl(this OutputContext c, IConnectionFactory cf)
{
var sql = $"DELETE FROM {cf.Enclose(SqlControlTableName(c))} WHERE Entity = @Entity";
c.Debug(() => sql);
return(sql);
}
public static string SqlDropStarView(this OutputContext c)
{
var sql = $"DROP VIEW [{c.Process.Star}];";
c.Debug(sql);
return(sql);
}
public static string SqlDropControl(this OutputContext c, IConnectionFactory cf)
{
var sql = $"DROP TABLE {cf.Enclose(SqlControlTableName(c))}{cf.Terminator}";
c.Debug(() => sql);
return(sql);
}
public static string SqlSelectOutputSchema(this OutputContext c)
{
var sql = $"SELECT TOP 0 * FROM [{c.Entity.OutputTableName(c.Process.Name)}] WITH (NOLOCK);";
c.Debug(sql);
return(sql);
}
public static string SqlDropOutput(this OutputContext c)
{
var sql = $"DROP TABLE [{c.Entity.OutputTableName(c.Process.Name)}];";
c.Debug(sql);
return(sql);
}
public static bool Save(Microsoft.AnalysisServices.Server server, OutputContext output, IMajorObject obj)
{
var builder = new StringBuilder();
using (var xmlWriter = XmlWriter.Create(builder, new XmlWriterSettings {
OmitXmlDeclaration = true
})) {
Scripter.WriteAlter(xmlWriter, obj, true, true);
xmlWriter.Flush();
}
var command = builder.ToString();
output.Debug(() => command);
var results = server.Execute(command);
if (results.Count > 0)
{
foreach (XmlaResult result in results)
{
if (result.Messages.Count > 0)
{
foreach (XmlaMessage message in result.Messages)
{
output.Error(message.Description);
}
return(false);
}
}
}
return(true);
}
public void Write(IEnumerable <IRow> rows)
{
var fullCount = 0;
var batchCount = (uint)0;
foreach (var part in rows.Partition(_context.Entity.InsertSize))
{
var docs = new List <Dictionary <string, object> >();
foreach (var row in part)
{
batchCount++;
fullCount++;
docs.Add(_fields.ToDictionary(field => field.Alias.ToLower(), field => row[field]));
}
var response = _solr.AddRange(docs);
_context.Increment(@by: batchCount);
if (response.Status == 0)
{
_context.Debug(() => $"{batchCount} to output");
}
else
{
_context.Error("ah!");
}
batchCount = 0;
}
_solr.Commit();
if (fullCount > 0)
{
_context.Info($"{fullCount} to output");
}
}
