private static Row GetBuilder(Result result, string aggregate, bool distinct) {
var processName = "Dp" + result.Name[0].ToString(CultureInfo.InvariantCulture).ToUpper() + result.Name.Substring(1);
var builder = new ProcessBuilder(processName)
.Star(aggregate)
.StarEnabled(false)
.Connection("input")
.Provider("internal")
.Connection("output")
.Provider("internal")
.Entity(aggregate)
.DetectChanges(false)
.InputOperation(new RowsOperation(result.Rows))
.Group()
.Field("group")
.Input(false)
.Default("group")
.Aggregate("group")
.PrimaryKey();
foreach (var field in result.Fields) {
builder
.Field(field.Name)
.Length(field.Length)
.Type(field.Type)
.Aggregate(aggregate)
.Distinct(distinct);
}
return ProcessFactory.CreateSingle(builder.Process()).Execute().First();
}
public void TestCount() {
var cfg = new ProcessBuilder("process")
.Connection("input").Provider(ProviderType.Internal)
.Connection("output").Provider(ProviderType.Internal)
.Entity("entity")
.Group() //group means you need to aggregate all output fields
.Field("order").Aggregate("group").Int32().PrimaryKey()
.Field("count").Input(false).Aggregate("count").Int32()
.Process();
cfg.Entities.First().InputOperation = _testInput;
var output = ProcessFactory.CreateSingle(cfg, new TestLogger()).Execute().ToArray();
Assert.AreEqual(2, output.Length);
var r1 = output[0];
Assert.AreEqual(1, r1["order"]);
Assert.AreEqual(5, r1["count"]);
var r2 = output[1];
Assert.AreEqual(2, r2["order"]);
Assert.AreEqual(2, r2["count"]);
}
public void Test1() {
var data = new List<Dictionary<string, string>>{
new Dictionary<string, string> {{"f1", "1"}, {"f2", "1"}, {"f3", new DateTime(2001, 1, 1).ToString()}},
new Dictionary<string, string> {{"f1", "2"}, {"f2", "2"}, {"f3", new DateTime(2002, 2, 2).ToString()}},
new Dictionary<string, string> {{"f1", "3"}, {"f2", "3"}, {"f3", new DateTime(2003, 3, 3).ToString()}}
};
var process = new ProcessBuilder("test")
.DataSet("one", data)
.Entity("one")
.Field("f1").PrimaryKey()
.Field("f2").Type("int")
.Field("f3").Type("datetime")
.Process();
var problems = process.Errors();
foreach (var problem in problems) {
Console.WriteLine(problem);
}
Assert.AreEqual(0, problems.Length);
Assert.AreEqual(3, process.DataSets.First().Rows.Count);
var rows = ProcessFactory.CreateSingle(process, new TestLogger()).Execute().ToList();
Assert.AreEqual(3, rows.Count());
}
public void TestSingleInput()
{
var file1 = Path.GetTempFileName();
File.WriteAllText(file1,"id,name\n1,one\n2,two\n3,three");
var cfg = new ProcessBuilder("process")
.Connection("input1").Provider("file").File(file1).Delimiter(",").Start(2)
.Connection("output").Provider("internal")
.Entity("entity")
.Connection("input1")
.Field("id").Int32().PrimaryKey()
.Field("name")
.Process();
var process = ProcessFactory.CreateSingle(cfg, new TestLogger());
var output = process.Execute().ToArray();
Assert.IsInstanceOf<IEnumerable<Row>>(output);
Assert.AreEqual(3, output.Count());
Assert.AreEqual(true, output.Any(r => r["id"].Equals(1)));
Assert.AreEqual(true, output.Any(r => r["id"].Equals(2)));
Assert.AreEqual(true, output.Any(r => r["id"].Equals(3)));
Assert.AreEqual(true, output.Any(r => r["name"].Equals("one")));
Assert.AreEqual(true, output.Any(r => r["name"].Equals("two")));
Assert.AreEqual(true, output.Any(r => r["name"].Equals("three")));
}
public Result Import(string resource, decimal sample = 100m) {
AbstractConnection input;
bool noLock;
ConnectionModifier modifier;
try {
var userDefined = ProcessFactory.CreateSingle("DataProfiler", new Options() { Mode = "metadata"});
input = userDefined.Connections["input"];
modifier = new ConnectionModifier(resource);
modifier.Modify(ref input);
var hasEntity = userDefined.Entities.Any();
sample = sample > 0m && sample < 100m ?
sample :
hasEntity ? userDefined.Entities[0].Sample : 100m;
noLock = !hasEntity || userDefined.Entities[0].NoLock;
TflLogger.Info(userDefined.Name, modifier.Name, "Sample: {0:###} percent, NoLock: {1}", sample, noLock);
} catch {
throw new DataProfilerException("You must define a DataProfiler process with an 'input' connection in the transformalize configuration section.");
}
var cleanName = Regex.Replace(modifier.Name, "[^a-zA-Z]", string.Empty);
var builder = new ProcessBuilder("Dp" + cleanName[0].ToString(CultureInfo.InvariantCulture).ToUpper() + cleanName.Substring(1))
.Connection("input")
.Provider(input.Type)
.Server(input.Server)
.Database(input.Database)
.User(input.User)
.Password(input.Password)
.Port(input.Port)
.ConnectionString(input.GetConnectionString())
.Connection("output")
.Provider("internal")
.Entity(modifier.Name)
.DetectChanges(false)
.NoLock(noLock)
.Sample(sample)
.Schema(modifier.Schema);
var process = ProcessFactory.CreateSingle(builder.Process());
var result = new Result {
Name = modifier.Name,
Fields = process.Entities[0].Fields,
Rows = process.Execute(),
Provider = input.Type.ToString()
};
result.Properties["server"] = input.Server;
result.Properties["database"] = input.Database;
result.Properties["schema"] = modifier.Schema;
result.Properties["table"] = modifier.Name;
result.Properties["port"] = input.Port.ToString(CultureInfo.InvariantCulture);
return result;
}
public void TestCopyFile() {
var file1 = Path.GetTempFileName();
var file2 = Path.GetTempFileName();
File.WriteAllText(file1, "f1,f2,f3\nv1,v2,v3");
var process = new ProcessBuilder("CopyFile")
.Action("Copy")
.From(file1)
.To(file2)
.Process();
ProcessFactory.CreateSingle(process, new TestLogger()).ExecuteScaler();
Assert.AreEqual("f1,f2,f3\nv1,v2,v3", File.ReadAllText(file2));
}
public void TestCopyTemplateOutputToFile() {
var template = Path.GetTempFileName();
var file = Path.GetTempFileName();
File.WriteAllText(template, "Setting1: @Model.Parameters.Setting1, Setting2: @Model.Parameters.Setting2;");
var process = new ProcessBuilder("TestCopyTemplateOutputToFile")
.Template("template")
.File(template)
.Parameter("Setting1", 1)
.Parameter("Setting2", 2)
.Action("Copy")
.To(file)
.Process();
ProcessFactory.CreateSingle(process, new TestLogger()).ExecuteScaler();
Assert.AreEqual("Setting1: 1, Setting2: 2;", File.ReadAllText(file));
}
public ConnectionBuilder(ProcessBuilder processBuilder, TflConnection connection)
{
_processBuilder = processBuilder;
_connection = connection;
}
public void NorthWindProcessBuiltFromScratch() {
var process = new ProcessBuilder("Test")
.Connection("input").Provider("SqlServer").Database("NorthWind")
.Connection("output").Provider("SqlServer").Database("NorthWindStar")
.Connection("sass").Database("NorthWind").Provider("AnalysisServices")
.Template("solr-data-handler").File("solr-data-handler.cshtml").Cache(true)
.Action("copy").File(@"C:\Solr\NorthWind\conf\data-config.xml")
.Template("solr-schema").File("solr-schema.cshtml").Cache(true)
.Action("copy").File(@"C:\Solr\NorthWind\conf\schema.xml")
.Action("web").Mode("init").Url("http://localhost:8983/solr/admin/cores?action=RELOAD&core=NorthWind")
.Action("web").Mode("first").Url("http://localhost:8983/solr/NorthWind/dataimport?command=full-import&clean=true&commit=true&optimize=true")
.Action("web").Mode("default").Url("http://localhost:8983/solr/NorthWind/dataimport?command=delta-import&clean=false&commit=true&optimize=true")
.Map("Managers").Connection("input").Sql("select EmployeeID, FirstName + ' ' + LastName FROM Employees;")
.SearchType("facet").Analyzer("lowercase").Store(true).Index(true)
.SearchType("standard").Analyzer("standard_lowercase").Store(false).Index(true)
.Entity("Order Details").Version("RowVersion").Prefix("OrderDetails")
.Field("Discount").Single()
.Field("OrderID").Int32().PrimaryKey()
.Field("ProductID").Int32().PrimaryKey()
.Field("Quantity").Int16()
.Field("UnitPrice").Decimal().Precision(19).Scale(4)
.Field("RowVersion").RowVersion()
.CalculatedField("OrderDetailsExtendedPrice").Decimal().Precision(19).Scale(4)
.Transform("javascript").Script("OrderDetailsQuantity * (OrderDetailsUnitPrice * (1-OrderDetailsDiscount))")
.Parameter("*")
.Entity("Orders").Version("RowVersion").Prefix("Orders")
.Field("CustomerID").Char().Length(5)
.Field("EmployeeID").Int32()
.Field("Freight").Decimal(19, 4)
.Field("OrderDate").DateTime()
.Field("OrderID").Int32().PrimaryKey()
.Field("RequiredDate").DateTime()
.Field("RowVersion").RowVersion()
.Field("ShipAddress")
.Field("ShipCity").Length(15)
.Field("ShipCountry").Length(15)
.Field("ShipName").Length(40)
.Field("ShippedDate").DateTime()
.Field("ShipPostalCode").Length(10)
.Field("ShipRegion").Length(15)
.Field("ShipVia").Int32()
.CalculatedField("TimeOrderMonth").Length(6).Default("12-DEC")
.Transform("toString").Format("MM-MMM")
.Parameter("OrderDate")
.Transform("toUpper")
.CalculatedField("TimeOrderDate").Length(10).Default("9999-12-31")
.Transform().ToString("yyyy-MM-dd")
.Parameter("OrderDate")
.CalculatedField("TimeOrderYear").Length(4).Default("9999")
.Transform().ToString("yyyy")
.Parameter("OrderDate")
.Entity("Customers").Version("RowVersion").Prefix("Customers")
.Field("Address")
.Field("City").Length(15)
.Field("CompanyName").Length(40)
.Field("ContactName").Length(30)
.Field("ContactTitle").Length(30)
.Field("Country").Length(15)
.Field("CustomerID").Length(5).PrimaryKey()
.Field("Fax").Length(24)
.Field("Phone").Length(24)
.Field("PostalCode").Length(10)
.Field("Region").Length(15)
.Field("RowVersion").RowVersion()
.Entity("Employees").Version("RowVersion").Prefix("Employees")
.Field("Address").Length(60)
.Field("BirthDate").DateTime()
.Field("City").Length(15)
.Field("Country").Length(15)
.Field("EmployeeID").Int32().PrimaryKey()
.Field("Extension").Length(4)
.Field("FirstName").Length(10)
.Field("HireDate").DateTime()
.Field("HomePhone").Length(24)
.Field("LastName").Length(20)
.Field("Notes").Length("MAX")
.Field("Photo").Alias("EmployeesPhoto").Input(false).Output(false)
.Field("PhotoPath").Alias("EmployeesPhotoPath").Input(false).Output(false)
.Field("PostalCode").Length(10)
.Field("Region").Length(15)
.Field("RowVersion").Type("System.Byte[]").Length(8)
.Field("Title").Length(30)
.Field("TitleOfCourtesy").Length(25)
.Field("ReportsTo").Alias("EmployeesManager")
.Transform("map").Map("Managers")
.CalculatedField("Employee")
.Transform("join").Separator(" ")
.Parameter("FirstName")
.Parameter("LastName")
.Entity("Products").Version("RowVersion").Prefix("Products")
.Field("CategoryID").Int32()
.Field("Discontinued").Bool()
.Field("ProductID").Int32().PrimaryKey()
.Field("ProductName").Length(40)
.Field("QuantityPerUnit").Length(20)
.Field("ReorderLevel").Int16()
.Field("RowVersion").RowVersion()
.Field("SupplierID").Int32()
.Field("UnitPrice").Decimal(19, 4)
.Field("UnitsInStock").Int16()
.Field("UnitsOnOrder").Int16()
.Entity("Suppliers").Version("RowVersion").Prefix("Suppliers")
.Field("Address").Length(60)
.Field("City").Length(15)
.Field("CompanyName").Length(40)
.Field("ContactName").Length(30)
.Field("ContactTitle").Length(30)
.Field("Country").Length(15)
.Field("Fax").Length(24)
.Field("HomePage").Length("MAX")
.Field("Phone").Length(24)
.Field("PostalCode").Length(10)
.Field("Region").Length(15)
.Field("RowVersion").RowVersion()
.Field("SupplierID").Int32().PrimaryKey()
.Entity("Categories").Version("RowVersion").Prefix("Categories")
.Field("CategoryID").Int32().PrimaryKey()
.Field("CategoryName").Length(15)
.Field("Description").Length("MAX")
.Field("Picture").ByteArray().Length("MAX").Input(false).Output(false)
.Field("RowVersion").RowVersion()
.Entity("Shippers").Version("RowVersion").Prefix("Shippers")
.Field("CompanyName").Length(40)
.Field("Phone").Length(24)
.Field("RowVersion").RowVersion()
.Field("ShipperID").Int32().PrimaryKey()
.Relationship().LeftEntity("Order Details").LeftField("OrderID").RightEntity("Orders").RightField("OrderID")
.Relationship().LeftEntity("Orders").LeftField("CustomerID").RightEntity("Customers").RightField("CustomerID")
.Relationship().LeftEntity("Orders").LeftField("EmployeeID").RightEntity("Employees").RightField("EmployeeID")
.Relationship().LeftEntity("Order Details").LeftField("ProductID").RightEntity("Products").RightField("ProductID")
.Relationship().LeftEntity("Products").LeftField("SupplierID").RightEntity("Suppliers").RightField("SupplierID")
.Relationship().LeftEntity("Products").LeftField("CategoryID").RightEntity("Categories").RightField("CategoryID")
.Relationship().LeftEntity("Orders").LeftField("ShipVia").RightEntity("Shippers").RightField("ShipperID")
.CalculatedField("CountryExchange").Length(128)
.Transform("format").Format("{0} to {1}")
.Parameter("SuppliersCountry")
.Parameter("OrdersShipCountry")
.Process();
//ProcessFactory.Create(process, new Options() { Mode = "init"}).Run();
//ProcessFactory.Create(process).Run();
}
public RelationshipBuilder(ProcessBuilder processBuilder, TflRelationship relationship) {
_processBuilder = processBuilder;
_relationship = relationship;
}
public void TestMax() {
var cfg = new ProcessBuilder("process")
.Connection("input").Provider(ProviderType.Internal)
.Connection("output").Provider(ProviderType.Internal)
.Entity("entity")
.Group() //group means you need to aggregate all output fields
.Field("order").Aggregate("group").Int32().PrimaryKey()
.Field("year").Aggregate("max")
.Field("int32").Aggregate("max").Int32()
.Field("int64").Aggregate("max").Int64()
.Field("guid").Aggregate("max").Type("guid")
.Field("bytes").Aggregate("max").Type("byte[]")
.Process();
cfg.Entities.First().InputOperation = _testInput;
var output = ProcessFactory.CreateSingle(cfg, new TestLogger()).Execute().ToArray();
Assert.AreEqual(2, output.Length);
var r1 = output[0];
Assert.AreEqual(1, r1["order"]);
Assert.AreEqual("2002", r1["year"]);
Assert.AreEqual(2002, r1["int32"]);
Assert.AreEqual(2002, r1["int64"]);
Assert.AreEqual(Guid.Parse("e2cf0d95-1d29-4bed-9b31-ce5b5628466b"), r1["guid"]);
Assert.AreEqual(new Byte[] { 0, 0, 2 }, r1["bytes"]);
var r2 = output[1];
Assert.AreEqual(2, r2["order"]);
Assert.AreEqual("2000", r2["year"]);
Assert.AreEqual(2000, r2["int32"]);
Assert.AreEqual(2000, r2["int64"]);
Assert.AreEqual(Guid.Parse("e2cf0d95-1d29-4bed-9b31-ce5b5628466b"), r2["guid"]);
Assert.AreEqual(new Byte[] { 0, 0, 0 }, r2["bytes"]);
}
public void DomainWithBranches() {
var cfg = new ProcessBuilder("process")
.Connection("input").Provider(ProviderType.Internal)
.Connection("output").Provider(ProviderType.Internal)
.Entity("entity")
.Field("name")
.CalculatedField("is-dale")
.Type("bool")
.Transform("domain")
.Domain("Dale")
.Parameter("name")
.CalculatedField("new-name")
.Transform("copy")
.Parameter("name")
.Branch("DaleBranch")
.RunIf("is-dale", true)
.Transform("toUpper")
.Parameter("name")
.Branch("VladBranch")
.RunIf("is-dale", false)
.Transform("toLower")
.Parameter("name")
.Process();
cfg.Entities[0].InputOperation = new RowsBuilder()
.Row("name", "Dale")
.Row("name", "Vlad")
.Row("name","Tara").ToOperation();
var process = ProcessFactory.CreateSingle(cfg, new TestLogger());
var output = process.Execute().ToArray();
Assert.AreEqual("DALE", output[0]["new-name"]);
Assert.AreEqual("vlad", output[1]["new-name"]);
Assert.AreEqual("tara", output[2]["new-name"]);
}
public EntityBuilder(ProcessBuilder processBuilder, TflEntity entity)
{
_processBuilder = processBuilder;
_entity = entity;
}
public void TestJoin() {
var process = new ProcessBuilder("p1")
.Entity("OrderDetail")
.Field("OrderId").Type("int").PrimaryKey()
.Field("ProductId").Type("int").Default("0").PrimaryKey()
.Entity("OrderDetailOptions")
.Field("OrderId").Type("int").PrimaryKey()
.Field("ProductId").Type("int").Default("0").PrimaryKey()
.Field("Color").Default("Silver")
.Relationship().LeftEntity("OrderDetail").RightEntity("Order")
.Join().LeftField("OrderId").RightField("OrderId")
.Join().LeftField("ProductId").RightField("ProductId")
.Process();
Assert.AreEqual(1, process.Relationships.Count);
var relationship = process.Relationships[0];
Assert.AreEqual("OrderDetail", relationship.LeftEntity);
Assert.AreEqual("OrderId", relationship.Join[0].LeftField);
Assert.AreEqual("Order", relationship.RightEntity);
Assert.AreEqual("OrderId", relationship.Join[0].RightField);
}
public void CSharpInProcess() {
var script = Path.GetTempFileName();
File.WriteAllText(script, @"
public static class F {
public static int MinuteDiff(string orderStatus, DateTime start, DateTime end) {
if (orderStatus != ""Completed"" && orderStatus != ""Problematic"")
return 0;
var answer = Convert.ToInt32(Math.Round(end.Subtract(start).TotalMinutes, 0));
return answer > 60 ? 60 : answer;
}
}
");
var input = new RowsBuilder()
.Row("OrderStatus", "Completed")
.Field("StartDate", DateTime.Now.AddMinutes(-30.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "Problematic")
.Field("StartDate", DateTime.Now.AddMinutes(-29.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "Problematic")
.Field("StartDate", DateTime.Now.AddMinutes(-78.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "x")
.Field("StartDate", DateTime.Now.AddMinutes(-20.0))
.Field("EndDate", DateTime.Now)
.ToOperation();
var config = new ProcessBuilder("test")
.Connection("input").Provider("internal")
.Connection("output").Provider("internal")
.Script("script", script)
.Entity("test")
.Field("OrderStatus")
.Field("StartDate").DateTime()
.Field("EndDate").DateTime()
.CalculatedField("MinuteDiff").Int()
.Transform("csharp")
.ExternalScript("script")
.Script("return F.MinuteDiff(OrderStatus,StartDate,EndDate);")
.Parameter("OrderStatus")
.Parameter("StartDate")
.Parameter("EndDate")
.Process();
config.Entities[0].InputOperation = input;
var process = ProcessFactory.CreateSingle(config, new TestLogger());
process.PipelineThreading = PipelineThreading.SingleThreaded;
var output = process.Execute().ToArray();
Assert.AreEqual(30, output[0]["MinuteDiff"]);
Assert.AreEqual(29, output[1]["MinuteDiff"]);
Assert.AreEqual(60, output[2]["MinuteDiff"]);
Assert.AreEqual(0, output[3]["MinuteDiff"]);
}
public void TestMap() {
var process = new ProcessBuilder("p1")
.Connection("input").Database("I")
.Map("m1")
.Item().From("one").To("1")
.Item().From("two").To("2").StartsWith()
.Map("m2").Sql("SELECT [From], [To] FROM [Table];").Connection("input")
.Process();
Assert.AreEqual("p1", process.Name);
Assert.AreEqual(2, process.Maps.Count);
Assert.AreEqual(2, process.Maps[0].Items.Count);
Assert.AreEqual("one", process.Maps[0].Items[0].From);
Assert.AreEqual("1", process.Maps[0].Items[0].To);
Assert.AreEqual("equals", process.Maps[0].Items[0].Operator);
Assert.AreEqual("two", process.Maps[0].Items[1].From);
Assert.AreEqual("2", process.Maps[0].Items[1].To);
Assert.AreEqual("startswith", process.Maps[0].Items[1].Operator);
Assert.AreEqual("input", process.Maps[1].Connection);
Assert.AreEqual("SELECT [From], [To] FROM [Table];", process.Maps[1].Query);
}
private static Process GetTestProcess(string outputProvider) {
var process = new ProcessBuilder("Test")
.StarEnabled(false)
.Connection("input").Provider("internal")
.Connection("output").Provider(outputProvider)
.Entity("OrderDetail")
.Schema("dbo")
.Field("OrderDetailId").PrimaryKey()
.Field("OrderId")
.Field("ProductId")
.Field("Price")
.Field("Quantity")
.Entity("Order")
.Schema("dbo")
.Field("OrderId").PrimaryKey()
.Field("CustomerId")
.Entity("Customer")
.Schema("dbo")
.Field("CustomerId").PrimaryKey()
.Field("FirstName")
.Field("LastName")
.Entity("Product")
.Schema("dbo")
.Field("ProductId").PrimaryKey()
.Field("Name").Alias("Product")
.Field("Description")
.Entity("ProductCategory")
.Schema("dbo")
.Field("ProductId").PrimaryKey()
.Field("CategoryId").PrimaryKey()
.Entity("Category")
.Schema("dbo")
.Field("CategoryId").PrimaryKey()
.Field("Name").Alias("Category")
.Relationship()
.LeftEntity("OrderDetail").LeftField("OrderId")
.RightEntity("Order").RightField("OrderId")
.Relationship()
.LeftEntity("Order").LeftField("CustomerId")
.RightEntity("Customer").RightField("CustomerId")
.Relationship()
.LeftEntity("OrderDetail").LeftField("ProductId")
.RightEntity("Product").RightField("ProductId")
.Relationship()
.LeftEntity("Product").LeftField("ProductId")
.RightEntity("ProductCategory").RightField("ProductId")
.Relationship()
.LeftEntity("ProductCategory").LeftField("CategoryId")
.RightEntity("Category").RightField("CategoryId")
.Process();
process = new TflRoot(process).Processes[0];
process.Entities[0].InputOperation = new RowsBuilder()
.Row("OrderDetailId", 1).Field("OrderId", 1).Field("ProductId", 1).Field("Quantity", 2).Field("Price", 2.0)
.Row("OrderDetailId", 2).Field("OrderId", 1).Field("ProductId", 2).Field("Quantity", 2).Field("Price", 3.0)
.Row("OrderDetailId", 3).Field("OrderId", 2).Field("ProductId", 2).Field("Quantity", 4).Field("Price", 3.0)
.ToOperation();
process.Entities[1].InputOperation = new RowsBuilder()
.Row("OrderId", 1).Field("CustomerId", 1)
.Row("OrderId", 2).Field("CustomerId", 2)
.ToOperation();
process.Entities[2].InputOperation = new RowsBuilder()
.Row("CustomerId", 1).Field("FirstName", "Dale").Field("LastName", "Newman")
.Row("CustomerId", 2).Field("FirstName", "Tara").Field("LastName", "Newman")
.ToOperation();
process.Entities[3].InputOperation = new RowsBuilder()
.Row("ProductId", 1).Field("Name", "Beans").Field("Description", "Really nice beans.")
.Row("ProductId", 2).Field("Name", "Cheese").Field("Description", "Very sharp cheese.")
.ToOperation();
process.Entities[4].InputOperation = new RowsBuilder()
.Row("ProductId", 1).Field("CategoryId", 1)
.Row("ProductId", 1).Field("CategoryId", 2)
.Row("ProductId", 2).Field("CategoryId", 3)
.ToOperation();
process.Entities[5].InputOperation = new RowsBuilder()
.Row("CategoryId", 1).Field("Name", "Proteins")
.Row("CategoryId", 2).Field("Name", "Miracle Fruits")
.Row("CategoryId", 3).Field("Name", "Dairy")
.ToOperation();
return ProcessFactory.CreateSingle(process, new TestLogger());
}
public void TestCombo() {
var cfg = new ProcessBuilder("process")
.Connection("input").Provider(ProviderType.Internal)
.Connection("output").Provider(ProviderType.Internal)
.Entity("entity")
.Field("MeterNumber")
.CalculatedField("MeterCategory").Default("None")
.Transform("left").Length(1).Parameter("MeterNumber")
.Transform("if").Left("MeterCategory").Right("R").Then("Reclaim").Else("Domestic")
.Process();
cfg.Entities[0].InputOperation = new RowsBuilder()
.Row("MeterNumber", "R00001")
.Row("MeterNumber", "000002").ToOperation();
var process = ProcessFactory.CreateSingle(cfg, new TestLogger());
var output = process.Execute().ToArray();
Assert.AreEqual("Reclaim", output[0]["MeterCategory"]);
Assert.AreEqual("Domestic", output[1]["MeterCategory"]);
}
public ConnectionBuilder(ProcessBuilder processBuilder, TflConnection connection) {
_processBuilder = processBuilder;
_connection = connection;
}
public void CSharpInProcessInLine() {
var config = new ProcessBuilder("test")
.Connection("input").Provider("internal")
.Connection("output").Provider("internal")
.Entity("test")
.Field("OrderStatus")
.Field("StartDate").DateTime()
.Field("EndDate").DateTime()
.CalculatedField("MinuteDiff").Int()
.Transform("csharp")
.Script(@"
var answer = 0;
if (OrderStatus == 'Completed' || OrderStatus == 'Problematic'){
answer = Convert.ToInt32(Math.Round(EndDate.Subtract(StartDate).TotalMinutes, 0));
}
return answer > 60 ? 60 : answer;
")
.Parameter("OrderStatus")
.Parameter("StartDate")
.Parameter("EndDate")
.Process();
config.Entities[0].InputOperation = new RowsBuilder()
.Row("OrderStatus", "Completed")
.Field("StartDate", DateTime.Now.AddMinutes(-30.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "Problematic")
.Field("StartDate", DateTime.Now.AddMinutes(-29.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "Problematic")
.Field("StartDate", DateTime.Now.AddMinutes(-78.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "x")
.Field("StartDate", DateTime.Now.AddMinutes(-20.0))
.Field("EndDate", DateTime.Now)
.ToOperation();
var process = ProcessFactory.CreateSingle(config, new TestLogger());
process.PipelineThreading = PipelineThreading.SingleThreaded;
var output = process.Execute().ToArray();
Assert.AreEqual(30, output[0]["MinuteDiff"]);
Assert.AreEqual(29, output[1]["MinuteDiff"]);
Assert.AreEqual(60, output[2]["MinuteDiff"]);
Assert.AreEqual(0, output[3]["MinuteDiff"]);
}
public void TestConnection() {
var process = new ProcessBuilder("p1")
.Connection("input").Server("localhost").Database("Test")
.Process();
Assert.AreEqual("p1", process.Name);
Assert.AreEqual(2, process.Connections.Count, "There should be 2 connections because an internal output is automatically added.");
Assert.AreEqual(500, process.Connections[0].BatchSize);
Assert.AreEqual("input", process.Connections[0].Name);
Assert.AreEqual("localhost", process.Connections[0].Server);
Assert.AreEqual("Test", process.Connections[0].Database);
}
public void TestScript() {
var process = new ProcessBuilder("Test")
.Script("test").File("test.js")
.Script("test2").File("test.js")
.Process();
Assert.AreEqual(2, process.Scripts.Count);
}
public void TestTwoConnections() {
var process = new ProcessBuilder("p1")
.Connection("input").Database("I")
.Connection("output").Database("O")
.Process();
Assert.AreEqual("p1", process.Name);
Assert.AreEqual(2, process.Connections.Count);
Assert.AreEqual(500, process.Connections[0].BatchSize);
Assert.AreEqual("input", process.Connections[0].Name);
Assert.AreEqual("localhost", process.Connections[0].Server);
Assert.AreEqual("I", process.Connections[0].Database);
Assert.AreEqual(500, process.Connections[1].BatchSize);
Assert.AreEqual("output", process.Connections[1].Name);
Assert.AreEqual("localhost", process.Connections[1].Server);
Assert.AreEqual("O", process.Connections[1].Database);
}
public void JavascriptInProcess() {
var script = Path.GetTempFileName();
File.WriteAllText(script, @"
function minuteDiff(orderStatus, start, end) {
if (orderStatus == 'Completed' || orderStatus == 'Problematic') {
var answer = 0;
if (start.getFullYear() != 9999 && end.getFullYear() != 9999) {
var ms = Math.abs(start - end);
answer = Math.round((ms / 1000) / 60);
}
return answer > 60 ? 60 : answer;
} else {
return 0;
}
}");
var input = new RowsBuilder()
.Row("OrderStatus", "Completed")
.Field("StartDate", DateTime.Now.AddMinutes(-30.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "Problematic")
.Field("StartDate", DateTime.Now.AddMinutes(-29.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "Problematic")
.Field("StartDate", DateTime.Now.AddMinutes(-78.0))
.Field("EndDate", DateTime.Now)
.Row("OrderStatus", "x")
.Field("StartDate", DateTime.Now.AddMinutes(-20.0))
.Field("EndDate", DateTime.Now)
.ToOperation();
var config = new ProcessBuilder("test")
.Connection("input").Provider("internal")
.Connection("output").Provider("internal")
.Script("script", script)
.Entity("test")
.Field("OrderStatus")
.Field("StartDate").DateTime()
.Field("EndDate").DateTime()
.CalculatedField("MinuteDiff").Int()
.Transform("javascript")
.ExternalScript("script")
.Script("minuteDiff(OrderStatus,StartDate,EndDate)")
.Parameter("OrderStatus")
.Parameter("StartDate")
.Parameter("EndDate")
.Process();
config.Entities[0].InputOperation = input;
var process = ProcessFactory.CreateSingle(config, new TestLogger());
var output = process.Execute().ToArray();
Assert.AreEqual(30, output[0]["MinuteDiff"]);
Assert.AreEqual(29, output[1]["MinuteDiff"]);
Assert.AreEqual(60, output[2]["MinuteDiff"]);
Assert.AreEqual(0, output[3]["MinuteDiff"]);
}
public void TestEntity() {
var process = new ProcessBuilder("p1")
.Entity("OrderDetail").Version("OrderDetailVersion")
.Field("OrderId").Type("int").PrimaryKey()
.Field("ProductId").Type("int").Default("0").PrimaryKey()
.Entity("Order").Version("OrderVersion")
.Field("OrderId").Type("int").PrimaryKey()
.Field("OrderDate").Type("System.DateTime").Default("9999-12-31")
.Process();
Assert.AreEqual(2, process.Entities.Count);
var orderDetail = process.Entities[0];
Assert.AreEqual(2, orderDetail.Fields.Count);
Assert.AreEqual("OrderDetail", orderDetail.Name);
Assert.AreEqual("OrderDetailVersion", orderDetail.Version);
var orderId = orderDetail.Fields[0];
Assert.AreEqual("OrderId", orderId.Name);
Assert.AreEqual("int", orderId.Type);
Assert.IsTrue(orderId.PrimaryKey);
var productId = orderDetail.Fields[1];
Assert.AreEqual("ProductId", productId.Name);
Assert.AreEqual("int", productId.Type);
Assert.IsTrue(productId.PrimaryKey);
Assert.AreEqual("0", productId.Default);
var order = process.Entities[1];
Assert.AreEqual("Order", order.Name);
Assert.AreEqual("OrderVersion", order.Version);
orderId = order.Fields[0];
Assert.AreEqual("OrderId", orderId.Name);
Assert.AreEqual("int", orderId.Type);
Assert.IsTrue(orderId.PrimaryKey);
var orderDate = order.Fields[1];
Assert.AreEqual("OrderDate", orderDate.Name);
Assert.AreEqual("datetime", orderDate.Type);
Assert.IsFalse(orderDate.PrimaryKey);
Assert.AreEqual("9999-12-31", orderDate.Default);
}
public void TestCopyFileToConnection() {
var file1 = Path.GetTempFileName();
File.WriteAllText(file1, "f1,f2,f3\nv1,v2,v3");
var element = new ProcessBuilder("CopyFile")
.Connection("output")
.Provider("SqlServer")
.Database("TestOutput")
.Action("Copy")
.From(file1)
.To("output")
.Process();
var process = ProcessFactory.CreateSingle(element, new TestLogger());
process.ExecuteScaler();
var expected = Common.CleanIdentifier(Path.GetFileNameWithoutExtension(file1));
var sqlServer = new TflConnection{
Name = "test",
Provider = "sqlserver",
Database = "TestOutput"
}.WithDefaults().Connection;
var rows = sqlServer.GetConnection().Query($"SELECT f1, f2, f3 FROM {expected}").ToArray();
Assert.AreEqual(1, rows.Length);
Assert.AreEqual("v1", rows[0].f1);
Assert.AreEqual("v2", rows[0].f2);
Assert.AreEqual("v3", rows[0].f3);
}
public void TestRelationship() {
var process = new ProcessBuilder("p1")
.Entity("OrderDetail").Version("OrderDetailVersion")
.Field("OrderId").Type("int").PrimaryKey()
.Field("ProductId").Type("int").Default("0").PrimaryKey()
.Entity("Order").Version("OrderVersion")
.Field("OrderId").Type("int").PrimaryKey()
.Field("OrderDate").Type("System.DateTime").Default("9999-12-31")
.Relationship()
.LeftEntity("OrderDetail").LeftField("OrderId")
.RightEntity("Order").RightField("OrderId")
.Process();
Assert.AreEqual(1, process.Relationships.Count);
var relationship = process.Relationships[0];
Assert.AreEqual("OrderDetail", relationship.LeftEntity);
Assert.AreEqual("OrderId", relationship.LeftField);
Assert.AreEqual("Order", relationship.RightEntity);
Assert.AreEqual("OrderId", relationship.RightField);
}
public void Export(Dictionary<string, Row> profile, string file = null) {
Process userDefined;
AbstractConnection output;
var input = new RowsOperation(profile.Select(kv => kv.Value));
string provider = null;
try {
userDefined = ProcessFactory.CreateSingle("DataProfiler", new Options() { Mode = "metadata" });
output = userDefined.Connections["output"];
} catch {
throw new DataProfilerException("You must define a DataProfiler process with an 'output' connection in the transformalize configuration section.");
}
// user may override output provider by changing file extension (e.g. .html, .csv, .txt)
if (file != null) {
var key = new FileInfo(file).Extension.Replace(".", string.Empty).ToLower();
provider = _autoProvider.ContainsKey(key) ? _autoProvider[key] : _autoProvider["*"];
}
var builder = new ProcessBuilder("DataProfiler")
.Connection("input")
.Provider("internal")
.Connection("output")
.ConnectionString(output.GetConnectionString())
.Database(output.Database)
.DateFormat(output.DateFormat)
.Delimiter(output.Delimiter)
.ErrorMode(output.ErrorMode)
.File(file ?? output.File)
.Folder(output.Folder)
.Footer(output.Footer)
.Header(output.Header)
.Password(output.Password)
.Port(output.Port)
.Provider(provider ?? output.Type.ToString())
.SearchOption(output.SearchOption)
.SearchPattern(output.SearchPattern)
.Server(output.Server)
.Start(output.Start)
.User(output.User);
foreach (var action in userDefined.Actions) {
builder.Action(action.Action)
.After(action.After)
.Before(action.Before)
.Connection(action.Connection.Name)
.File(action.File)
.From(action.From)
.To(action.To)
.Url(action.Url)
.Command(action.Command)
.Body(action.Body)
.Cc(action.Cc)
.Modes(action.Modes.ToArray());
}
builder.Entity("ProfileExporter")
.InputOperation(input)
.Field("field").Label("Field").Length(128).PrimaryKey()
.Field("type").Label("Type")
.Field("index").Label("Index").Int32()
.Field("count").Label("Distinct Count").Int64()
.Field("min").Label("Min Value").Length(33)
.Transform("elipse").Length(30)
.Field("max").Label("Max Value").Length(33)
.Transform("elipse").Length(30)
.Field("minlength").Label("Min Length").Int64()
.Field("maxlength").Label("Max Length").Int64();
ProcessFactory.CreateSingle(builder.Process()).ExecuteScaler();
}
public void TestFieldTransform() {
var process = new ProcessBuilder("p1")
.Entity("OrderDetail")
.Field("OrderId").Type("int").PrimaryKey()
.Field("Something1")
.Transform().Method("right").Length(4)
.Field("ProductId").Type("int").Default("0").PrimaryKey()
.Field("Something2")
.Transform().Method("left").Length(5)
.Transform().Method("trim").TrimChars("x")
.Process();
Assert.AreEqual(0, process.Entities[0].Fields[0].Transforms.Count);
Assert.AreEqual(1, process.Entities[0].Fields[1].Transforms.Count);
Assert.AreEqual(0, process.Entities[0].Fields[2].Transforms.Count);
Assert.AreEqual(2, process.Entities[0].Fields[3].Transforms.Count);
var left = process.Entities[0].Fields[1].Transforms[0];
Assert.AreEqual("right", left.Method);
Assert.AreEqual(4, left.Length);
var right = process.Entities[0].Fields[3].Transforms[0];
Assert.AreEqual("left", right.Method);
Assert.AreEqual(5, right.Length);
var trim = process.Entities[0].Fields[3].Transforms[1];
Assert.AreEqual("trim", trim.Method);
Assert.AreEqual("x", trim.TrimChars);
}
public ItemBuilder(ProcessBuilder processBuilder, MapBuilder mapBuilder, TflMapItem item)
{
_processBuilder = processBuilder;
_mapBuilder = mapBuilder;
_item = item;
}
public TemplateBuilder(ProcessBuilder processBuilder, TflTemplate template) {
_processBuilder = processBuilder;
_template = template;
}
public void TestMin() {
var cfg = new ProcessBuilder("process")
.Connection("input").Provider(ProviderType.Internal)
.Connection("output").Provider(ProviderType.Internal)
.Entity("entity")
.Group() //group means you need to aggregate all output fields
.Field("order").Aggregate("group").Int32().PrimaryKey()
.Field("year").Aggregate("min")
.Field("int32").Aggregate("min").Int32()
.Field("int64").Aggregate("min").Int64()
.Field("guid").Aggregate("min").Type("guid")
.Field("bytes").Aggregate("min").Type("byte[]")
.Process();
cfg.Entities.First().InputOperation = _testInput;
var output = ProcessFactory.CreateSingle(cfg, new TestLogger()).Execute().ToArray();
Assert.AreEqual(2, output.Length);
var r1 = output[0];
Assert.AreEqual(1, r1["order"]);
Assert.AreEqual("2000", r1["year"]);
Assert.AreEqual(2000, r1["int32"]);
Assert.AreEqual(0, r1["int64"]); //because of null
Assert.AreEqual(Guid.Parse("00000000-0000-0000-0000-000000000000"), r1["guid"]); // because of null
Assert.AreEqual(new Byte[] { 0, 0, 0 }, r1["bytes"]);
var r2 = output[1];
Assert.AreEqual(2, r2["order"]);
Assert.AreEqual("2000", r2["year"]);
Assert.AreEqual(2000, r2["int32"]);
Assert.AreEqual(2000, r2["int64"]);
Assert.AreEqual(Guid.Parse("cd5cb6c8-4cbe-40c5-b17d-c17aee4196a2"), r2["guid"]);
Assert.AreEqual(new Byte[] { 0, 0, 0 }, r2["bytes"]);
}
public EntityBuilder(ProcessBuilder processBuilder, TflEntity entity) {
_processBuilder = processBuilder;
_entity = entity;
}
public TemplateBuilder(ProcessBuilder processBuilder, TflTemplate template)
{
_processBuilder = processBuilder;
_template = template;
}