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"); } }