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);
            }
        }
Example #4
0
        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);
        }
Example #5
0
        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));
        }
Example #7
0
 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);
        }
Example #9
0
        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);
     }
 }
Example #12
0
 public SqlEntityDelete(AbstractConnection connection, Entity entity)
     : base(connection)
 {
     _name          = Connection.Enclose(entity.OutputName());
     _isMaster      = entity.IsMaster();
     BatchSize      = connection.BatchSize;
     UseTransaction = true;
 }
        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);
            }
        }
Example #21
0
        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();
            }
        }
Example #22
0
        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[] { '\\' });
 }
Example #25
0
        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);
        }
Example #26
0
 public override IOperation Extract(Process process, Entity entity, bool firstRun)
 {
     return(new SolrExtract(process, this, entity.Fields.WithInput(), entity.OutputName()));
 }
Example #27
0
 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);
        }
Example #30
0
 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[] { '\\' }));
 }