public override void Create(AbstractConnection connection, Process process, Entity entity) { if (EntityExists != null && EntityExists.Exists(connection, entity)) { process.Logger.EntityWarn(entity.Name, "Trying to create entity that already exists! {0}", entity.Name); return; } var keyType = entity.IsMaster() ? FieldType.MasterKey : FieldType.PrimaryKey; var writer = process.StarEnabled && keyType == FieldType.MasterKey ? new FieldSqlWriter(entity.Fields, entity.CalculatedFields, process.CalculatedFields, GetRelationshipFields(process.Relationships, entity)) : new FieldSqlWriter(entity.Fields, entity.CalculatedFields); var primaryKey = writer.FieldType(keyType).Alias(connection.L, connection.R).Asc().Values(); var defs = new List <string>(); defs.AddRange(writer .Reload() .AddBatchId(entity.Index) .AddDeleted(entity) .AddSurrogateKey(entity.Index) .Output() .Alias(connection.L, connection.R) .DataType(new SqlServerDataTypeService()) .AppendIf(" NOT NULL", keyType) .Values()); var rowVersion = entity.Fields.WithSimpleType("rowversion").WithoutInput().WithoutOutput(); if (rowVersion.Any()) { var alias = rowVersion.First().Alias; defs.Add(connection.Enclose(alias) + " [ROWVERSION] NOT NULL"); } var createSql = connection.TableQueryWriter.CreateTable(entity.OutputName(), defs); _logger.EntityDebug(entity.Name, createSql); var indexSql = connection.TableQueryWriter.AddUniqueClusteredIndex(entity.OutputName()); _logger.EntityDebug(entity.Name, indexSql); var keySql = connection.TableQueryWriter.AddPrimaryKey(entity.OutputName(), primaryKey); _logger.EntityDebug(entity.Name, keySql); using (var cn = connection.GetConnection()) { cn.Open(); cn.Execute(createSql); cn.Execute(indexSql); cn.Execute(keySql); _logger.EntityInfo(entity.Name, "Initialized {0} in {1} on {2}.", entity.OutputName(), connection.Database, connection.Server); } }
public void Drop(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) return; var sql = string.Format(FORMAT, connection.Enclose(entity.OutputName())); using (var cn = connection.GetConnection()) { cn.Open(); cn.Execute(sql); connection.Logger.EntityDebug(entity.Name, "Dropped Output {0}", entity.OutputName()); } }
public override void Create(AbstractConnection connection, Process process, Entity entity) { if (EntityExists != null && EntityExists.Exists(connection, entity)) { process.Logger.EntityWarn(entity.Name,"Trying to create entity that already exists! {0}", entity.Name); return; } var keyType = entity.IsMaster() ? FieldType.MasterKey : FieldType.PrimaryKey; var writer = process.StarEnabled && keyType == FieldType.MasterKey ? new FieldSqlWriter(entity.Fields, entity.CalculatedFields, process.CalculatedFields, GetRelationshipFields(process.Relationships, entity)) : new FieldSqlWriter(entity.Fields, entity.CalculatedFields); var primaryKey = writer.FieldType(keyType).Alias(connection.L, connection.R).Asc().Values(); var defs = new List<string>(); defs.AddRange(writer .Reload() .AddBatchId(entity.Index) .AddDeleted(entity) .AddSurrogateKey(entity.Index) .Output() .Alias(connection.L, connection.R) .DataType(new SqlServerDataTypeService()) .AppendIf(" NOT NULL", keyType) .Values()); var rowVersion = entity.Fields.WithSimpleType("rowversion").WithoutInput().WithoutOutput(); if (rowVersion.Any()) { var alias = rowVersion.First().Alias; defs.Add(connection.Enclose(alias) + " [ROWVERSION] NOT NULL"); } var createSql = connection.TableQueryWriter.CreateTable(entity.OutputName(), defs); _logger.EntityDebug(entity.Name, createSql); var indexSql = connection.TableQueryWriter.AddUniqueClusteredIndex(entity.OutputName()); _logger.EntityDebug(entity.Name, indexSql); var keySql = connection.TableQueryWriter.AddPrimaryKey(entity.OutputName(), primaryKey); _logger.EntityDebug(entity.Name, keySql); using (var cn = connection.GetConnection()) { cn.Open(); cn.Execute(createSql); cn.Execute(indexSql); cn.Execute(keySql); _logger.EntityInfo(entity.Name, "Initialized {0} in {1} on {2}.", entity.OutputName(), connection.Database, connection.Server); } }
public override IEnumerable <Row> Execute(IEnumerable <Row> rows) { if (_entity.IsMaster()) { new MasterEntityIndexBuilder(_process).Create(); } //escape 1 if (_entity.IsMaster() || (!_entity.HasForeignKeys() && _process.IsFirstRun)) { return(rows); } //escape 2 var entityChanged = _entity.Inserts + _entity.Updates > 0; var masterChanged = _process.MasterEntity.Inserts + _process.MasterEntity.Updates > 0; if (!entityChanged && !masterChanged) { return(rows); } var master = _process.OutputConnection.Enclose(_process.MasterEntity.OutputName()); var entity = _process.OutputConnection.Enclose(_entity.OutputName()); using (var cn = _process.OutputConnection.GetConnection()) { cn.Open(); int records; string where; if (entityChanged && masterChanged) { where = string.Format("WHERE {0}.TflBatchId = @TflBatchId OR {1}.TflBatchId = @MasterTflBatchId;", entity, master); var sql = PrepareSql() + where; Debug(sql); records = cn.Execute(sql, new { _entity.TflBatchId, MasterTflBatchId = _process.MasterEntity.TflBatchId }, commandTimeout: 0); } else { where = string.Format("WHERE {0}.TflBatchId = @TflBatchId;", entityChanged ? entity : master); var sql = PrepareSql() + where; Debug(sql); records = cn.Execute(sql, new { TflBatchId = entityChanged ? _entity.TflBatchId : _process.MasterEntity.TflBatchId }, commandTimeout: 0); } Logger.EntityDebug(EntityName, "TflBatchId = {0}.", _entity.TflBatchId); Logger.EntityInfo(EntityName, "Processed {0} rows. Updated {1} with {2}.", records, _process.MasterEntity.Alias, _entity.Alias); } return(rows); }
public void Drop(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) { return; } var sql = string.Format(FORMAT, connection.Enclose(entity.OutputName())); using (var cn = connection.GetConnection()) { cn.Open(); cn.Execute(sql); connection.Logger.EntityDebug(entity.Name, "Dropped Output {0}", entity.OutputName()); } }
protected override void PrepareHeader(Entity entity) { foreach (Field field in entity.Fields.WithFileOutput()) { Headers.Add(field.Label.Equals(string.Empty) ? field.Alias : field.Label); } foreach (Field field in entity.CalculatedFields.WithFileOutput()) { Headers.Add(field.Label.Equals(string.Empty) ? field.Alias : field.Label); } HeaderText = string.Format(@"<!DOCTYPE html PUBLIC ""-//W3C//DTD HTML 3.2//EN""> <html> <head> <title>{0}</title> <link rel=""stylesheet"" href=""http://netdna.bootstrapcdn.com/bootswatch/3.1.1/united/bootstrap.min.css""> <link rel=""stylesheet"" href=""http://netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.min.css""> <script src=""http://netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js""></script> </head> <body> <table class=""table table-striped table-condensed table-hover""> <thead> <th>{1}</th> </thead> <tbody> ", entity.OutputName(), string.Join("</th><th>", Headers)); }
public bool Exists(AbstractConnection connection, Entity entity) { using (var cn = connection.GetConnection()) { cn.Open(); return(cn.Query <bool>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(false).FirstOrDefault()); } }
protected override void PrepareCommand(Row row, SqlCommand command) { var fields = _entity.OutputFields(); var writer = new FieldSqlWriter(fields).AddDeleted(_entity); var sets = writer.Alias(_connection.L, _connection.R).SetParam().Write(", ", false); command.CommandText = string.Format(@" UPDATE [{0}] SET {1}, TflBatchId = @TflBatchId WHERE TflKey = @TflKey; ", _entity.OutputName(), sets); foreach (var field in fields) { AddParameter(command, field.Identifier, row[field.Alias]); } if (_entity.Delete) { AddParameter(command, "TflDeleted", false); } AddParameter(command, "TflKey", row["TflKey"]); AddParameter(command, "TflBatchId", _entity.TflBatchId); Logger.EntityDebug(_entity.Alias, command.CommandText); }
protected virtual void PrepareType(Entity entity) { var builder = new DelimitedClassBuilder("Tfl" + entity.OutputName()) { IgnoreEmptyLines = true, Delimiter = _connection.Delimiter.ToString(CultureInfo.InvariantCulture), IgnoreFirstLines = 0 }; foreach (var f in _fileFields) { var field = new DelimitedFieldBuilder(f.Identifier, f.SystemType); if (f.SimpleType.Equals("datetime")) { field.Converter.Kind = ConverterKind.Date; field.Converter.Arg1 = _connection.DateFormat; } if (f.SimpleType.Equals("guid")) { field.Converter.TypeName = "Transformalize.Operations.Load.GuidConverter"; } if (f.SimpleType.Equals("byte[]") || f.SimpleType.Equals("rowversion")) { field.Converter.TypeName = "Transformalize.Operations.Load.ByteArrayConverter"; } if (_isCsv) { field.FieldQuoted = true; field.QuoteChar = '"'; field.QuoteMode = QuoteMode.OptionalForBoth; field.QuoteMultiline = MultilineMode.NotAllow; } builder.AddField(field); } Type = builder.CreateRecordClass(); }
public SqlEntityDelete(AbstractConnection connection, Entity entity) : base(connection) { _name = Connection.Enclose(entity.OutputName()); _isMaster = entity.IsMaster(); BatchSize = connection.BatchSize; UseTransaction = true; }
public bool Exists(AbstractConnection connection, Entity entity) { using (var cn = connection.GetConnection()) { cn.Open(); var table = cn.Query<string>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(string.Empty).FirstOrDefault(); return !string.IsNullOrEmpty(table); } }
protected override void PrepareType(Entity entity) { var builder = new DelimitedClassBuilder("Tfl" + entity.OutputName()) { IgnoreEmptyLines = true, Delimiter = " ", IgnoreFirstLines = 0 }; builder.AddField(_htmlField, typeof(string)); Type = builder.CreateRecordClass(); }
public SqlServerBulkLoadOperation(AbstractConnection connection, Entity entity) : base(connection, connection.Enclose(entity.OutputName())) { _entity = entity; _batchSize = connection.BatchSize; UseTransaction = false; TurnOptionOn(SqlBulkCopyOptions.TableLock); TurnOptionOn(SqlBulkCopyOptions.UseInternalTransaction); TurnOptionOff(SqlBulkCopyOptions.CheckConstraints); TurnOptionOff(SqlBulkCopyOptions.FireTriggers); TurnOptionOn(SqlBulkCopyOptions.KeepNulls); }
public int Count(AbstractConnection connection, Entity entity, bool useAlias) { if (_connectionChecker == null || _connectionChecker.Check(connection)) { if (_entityExists.Exists(connection, entity)) { using (var cn = connection.GetConnection()) { cn.Open(); var sql = string.Format("SELECT COUNT(*) FROM [{0}].[{1}] WITH (NOLOCK);", entity.Schema, entity.OutputName()); var cmd = cn.CreateCommand(); cmd.CommandText = sql; return (int)cmd.ExecuteScalar(); } } } return 0; }
public bool RecordsExist(AbstractConnection connection, Entity entity) { if (EntityExists.Exists(connection, entity)) { using (var cn = connection.GetConnection()) { cn.Open(); var sql = $@"SELECT TOP(1) [{entity.PrimaryKey.First().Alias}] FROM [{entity.OutputName()}];"; var cmd = cn.CreateCommand(); cmd.CommandText = sql; using (var reader = cmd.ExecuteReader()) { return reader.Read(); } } } return false; }
public bool RecordsExist(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) return false; using (var cn = connection.GetConnection()) { cn.Open(); var records = cn.Query<string>(string.Format(@" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT `{0}` FROM `{1}` LIMIT 1; COMMIT; ", entity.PrimaryKey.First().Alias, entity.OutputName())); return records != null && records.Any(); } }
public bool RecordsExist(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) return false; using (var cn = connection.GetConnection()) { cn.Open(); var exists = cn.Query<bool>(string.Format(@" SELECT EXISTS( SELECT ""{0}"" FROM ""{1}"" LIMIT 1 ); ", entity.PrimaryKey.First().Alias, entity.OutputName())).DefaultIfEmpty(false).First(); return exists; } }
public bool RecordsExist(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) { return(false); } using (var cn = connection.GetConnection()) { cn.Open(); var records = cn.Query <string>(string.Format(@" SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT `{0}` FROM `{1}` LIMIT 1; COMMIT; ", entity.PrimaryKey.First().Alias, entity.OutputName())); return(records != null && records.Any()); } }
public bool RecordsExist(AbstractConnection connection, Entity entity) { if (!EntityExists.Exists(connection, entity)) { return(false); } using (var cn = connection.GetConnection()) { cn.Open(); var exists = cn.Query <bool>(string.Format(@" SELECT EXISTS( SELECT ""{0}"" FROM ""{1}"" LIMIT 1 ); ", entity.PrimaryKey.First().Alias, entity.OutputName())).DefaultIfEmpty(false).First(); return(exists); } }
public override void WriteEndVersion(Process process, AbstractConnection input, Entity entity, bool force = false) { if (entity.Updates + entity.Inserts > 0 || force) { var solr = GetOperations(process, entity.OutputName()); var versionType = entity.Version == null ? "string" : entity.Version.SimpleType; string end; if (versionType.Equals("datetime") && entity.End is DateTime) { end = ((DateTime)entity.End).ToString("yyyy-MM-ddTHH:mm:ss.fff"); } else if (versionType.Equals("byte[]") || versionType.Equals("rowversion")) { end = Common.BytesToHexString((byte[])entity.End); } else { end = new DefaultFactory(Logger).Convert(entity.End, versionType).ToString(); } var doc = new Dictionary <string, object> { { "id", entity.TflBatchId }, { "tflbatchid", entity.TflBatchId }, { "process", entity.ProcessName }, { "connection", input.Name }, { "entity", entity.Alias }, { "updates", entity.Updates }, { "inserts", entity.Inserts }, { "deletes", entity.Deletes }, { "version", end }, { "version_type", versionType }, { "tflupdate", DateTime.UtcNow } }; solr.Add(doc); solr.Commit(); } }
public override Fields GetEntitySchema(Process process, Entity entity, bool isMaster = false) { var fields = new Fields(); var solr = GetReadonlyOperations(process, entity.OutputName()); var solrSchema = solr.GetSchema(_schemaFile); foreach (var solrField in solrSchema.SolrFields) { string type; var searchType = "default"; if (SolrTypeMap.ContainsKey(solrField.Type.Name)) { type = SolrTypeMap[solrField.Type.Name]; searchType = solrField.Type.Name; } else { type = solrField.Type.Name; } var field = new Field(type, "64", FieldType.None, true, string.Empty) { Name = solrField.Name, Entity = entity.Name, Input = solrField.IsStored, SearchTypes = new List <SearchType>() { new SearchType() { Name = searchType, Analyzer = searchType } } }; fields.Add(field); } return(fields); }
public bool Exists(AbstractConnection connection, Entity entity) { return new SqlServerTableExists(connection).OutputExists(entity.OutputName()); }
public static string Path(AbstractConnection connection, Entity entity) { return connection.Folder.TrimEnd(new[] { '\\' }) + "\\" + entity.OutputName().TrimStart(new[] { '\\' }); }
public override Fields GetEntitySchema(Process process, Entity entity, bool isMaster = false) { var fields = new SqlServerEntityAutoFieldReader().Read(this, process.Name, entity.Prefix, entity.OutputName(), entity.Schema, isMaster); return(!fields.Any() ? new SqlEntitySchemaReader(this).Read(entity.Name, entity.Schema) : fields); }
public override IOperation Extract(Process process, Entity entity, bool firstRun) { return(new SolrExtract(process, this, entity.Fields.WithInput(), entity.OutputName())); }
public override IOperation ExtractAllKeysFromInput(Process process, Entity entity) { return(new SolrExtract(process, this, entity.PrimaryKey, entity.OutputName())); }
public bool Exists(AbstractConnection connection, Entity entity) { using (var cn = connection.GetConnection()) { cn.Open(); return cn.Query<bool>(SQL, new { name = entity.OutputName() }).DefaultIfEmpty(false).FirstOrDefault(); } }
public DapperBulkUpdateOperation(AbstractConnection connection, Entity entity) { _connection = connection; _tflBatchId = entity.TflBatchId; _fields = entity.OutputFields(); var writer = new FieldSqlWriter(_fields); var sets = writer.Alias(_connection.L, _connection.R).SetParam().Write(", ", false); _sql = string.Format(@"UPDATE [{0}] SET {1}, TflBatchId = @TflBatchId WHERE TflKey = @TflKey;", entity.OutputName(), sets); }
public bool RecordsExist(AbstractConnection connection, Entity entity) { if (EntityExists.Exists(connection, entity)) { using (var cn = connection.GetConnection()) { cn.Open(); var sql = string.Format(@"SELECT TOP(1) [{0}] FROM [{1}];", entity.PrimaryKey.First().Alias, entity.OutputName()); var cmd = cn.CreateCommand(); cmd.CommandText = sql; using (var reader = cmd.ExecuteReader()) { return(reader.Read()); } } } return(false); }
public static string Path(AbstractConnection connection, Entity entity) { return(connection.Folder.TrimEnd(new[] { '\\' }) + "\\" + entity.OutputName().TrimStart(new[] { '\\' })); }