public void ArithmeticTest() // ComColumnDefinition. Defining new columns and evaluate them
{
CreateSampleData(schema);
DcTable t1 = schema.GetSubTable("Table 1");
DcColumn c11 = t1.GetColumn("Column 11");
DcColumn c12 = t1.GetColumn("Column 12");
DcColumn c13 = t1.GetColumn("Column 13");
DcColumn c14 = t1.GetColumn("Column 14");
//
// Define a derived column with a definition
//
DcColumn c15 = space.CreateColumn("Column 15", t1, schema.GetPrimitiveType("Double"), false);
c15.GetData().Formula = "([Column 11]+10.0) * this.[Column 13]";
// Evaluate column
c15.GetData().Evaluate();
Assert.AreEqual(600.0, c15.GetData().GetValue(0));
Assert.AreEqual(200.0, c15.GetData().GetValue(1));
Assert.AreEqual(1200.0, c15.GetData().GetValue(2));
}
public void TableDataTest() // ComTableData. Manually read/write data to/from tables
{
CreateSampleData(schema);
DcTable t1 = schema.GetSubTable("Table 1");
DcColumn c11 = t1.GetColumn("Column 11");
DcColumn c12 = t1.GetColumn("Column 12");
DcColumn c13 = t1.GetColumn("Column 13");
DcTableWriter w1 = t1.GetData().GetTableWriter();
//
// Data manipulations
//
Assert.AreEqual(3, t1.GetData().Length);
Offset input = w1.Find(new DcColumn[] { c11 }, new object[] { 10 });
Assert.AreEqual(1, input);
input = w1.Find(new DcColumn[] { c12 }, new object[] { "Record 1" });
Assert.AreEqual(1, input);
input = w1.Find(new DcColumn[] { c12 }, new object[] { "Record Does Not Exist" });
Assert.AreEqual(-1, input);
}
public void ProjectionTest() // Defining new tables via function projection and populate them
{
CreateSampleData(schema);
DcTable t2 = schema.GetSubTable("Table 2");
DcColumn c21 = t2.GetColumn("Column 21");
DcColumn c22 = t2.GetColumn("Column 22");
DcColumn c23 = t2.GetColumn("Column 23");
//
// Project "Table 2" along "Column 21" and get 2 unique records in a new set "Value A" (3 references) and "Value B" (1 reference)
//
DcTable t3 = space.CreateTable(DcSchemaKind.Dc, "Table 3", schema.Root);
DcColumn c31 = space.CreateColumn(c21.Name, t3, c21.Output, true);
// Create a generating column
DcColumn c24 = space.CreateColumn("Project", t2, t3, false);
c24.GetData().Formula = "(( [String] [Column 21] = [Column 21] ))";
c24.GetData().IsAppendData = true;
t3.GetData().Populate();
Assert.AreEqual(2, t3.GetData().Length);
Assert.AreEqual(0, c24.GetData().GetValue(0));
Assert.AreEqual(0, c24.GetData().GetValue(1));
Assert.AreEqual(0, c24.GetData().GetValue(2));
Assert.AreEqual(1, c24.GetData().GetValue(3));
//
// Defining a combination of "Column 21" and "Column 22" and project with 3 unique records in a new set
//
DcTable t4 = space.CreateTable(DcSchemaKind.Dc, "Table 4", schema.Root);
DcColumn c41 = space.CreateColumn(c21.Name, t4, c21.Output, true);
DcColumn c42 = space.CreateColumn(c22.Name, t4, c22.Output, true);
DcColumn c25 = space.CreateColumn("Project", t2, t4, false);
c25.GetData().Formula = "(( [String] [Column 21] = [Column 21], [Integer] [Column 22] = [Column 22] ))";
c25.GetData().IsAppendData = true;
t4.GetData().Populate();
Assert.AreEqual(3, t4.GetData().Length);
Assert.AreEqual(0, c25.GetData().GetValue(0));
Assert.AreEqual(1, c25.GetData().GetValue(1));
Assert.AreEqual(1, c25.GetData().GetValue(2));
Assert.AreEqual(2, c25.GetData().GetValue(3));
}
public void AggregationTest() // Defining new aggregated columns and evaluate them
{
CreateSampleData(schema);
DcTable t1 = schema.GetSubTable("Table 1");
DcTable t2 = schema.GetSubTable("Table 2");
DcColumn c23 = t2.GetColumn("Column 23");
DcColumn c24 = t2.GetColumn("Table 1");
//
// Define aggregated column
//
/* We do not use non-syntactic (object) formulas
* DcColumn c15 = schema.CreateColumn("Agg of Column 23", t1, schema.GetPrimitive("Double"), false);
* c15.Definition.DefinitionType = DcColumnDefinitionType.AGGREGATION;
*
* c15.Definition.FactTable = t2; // Fact table
* c15.Definition.GroupPaths = new List<DimPath>(new DimPath[] { new DimPath(c24) }); // One group path
* c15.Definition.MeasurePaths = new List<DimPath>(new DimPath[] { new DimPath(c23) }); // One measure path
* c15.Definition.Updater = "SUM"; // Aggregation function
*
* c15.Add();
*
* //
* // Evaluate expression
* //
* c15.Data.SetValue(0.0);
* c15.Definition.Evaluate(); // {40, 140, 0}
*
* Assert.AreEqual(40.0, c15.Data.GetValue(0));
* Assert.AreEqual(140.0, c15.Data.GetValue(1));
* Assert.AreEqual(0.0, c15.Data.GetValue(2)); // In fact, it has to be NaN or null (no values have been aggregated)
*/
//
// Aggregation via a syntactic formula
//
DcColumn c16 = space.CreateColumn("Agg2 of Column 23", t1, schema.GetPrimitiveType("Double"), false);
c16.GetData().Formula = "AGGREGATE(facts=[Table 2], groups=[Table 1], measure=[Column 23]*2.0 + 1, aggregator=SUM)";
c16.GetData().SetValue(0.0);
c16.GetData().Translate();
c16.GetData().Evaluate(); // {40, 140, 0}
Assert.AreEqual(81.0, c16.GetData().GetValue(0));
Assert.AreEqual(283.0, c16.GetData().GetValue(1));
Assert.AreEqual(0.0, c16.GetData().GetValue(2));
}
public void LinkTest()
{
CreateSampleData(schema);
DcTable t1 = schema.GetSubTable("Table 1");
DcColumn c11 = t1.GetColumn("Column 11"); // 20, 10, 30
DcTable t2 = schema.GetSubTable("Table 2");
DcColumn c22 = t2.GetColumn("Column 22"); // 20, 30, 30, 30
//
// Define a derived column with a definition
//
DcColumn link = space.CreateColumn("Column Link", t2, t1, false);
link.GetData().Formula = "(( [Integer] [Column 11] = this.[Column 22], [Double] [Column 14] = 20.0 ))"; // Tuple structure corresponds to output table
// Evaluate column
link.GetData().Evaluate();
Assert.AreEqual(0, link.GetData().GetValue(0));
Assert.AreEqual(2, link.GetData().GetValue(1));
Assert.AreEqual(2, link.GetData().GetValue(2));
Assert.AreEqual(2, link.GetData().GetValue(3));
}
public void ColumnDataTest() // DcColumnData. Manually read/write data
{
DcTable t1 = schema.GetSubTable("Table 1");
DcColumn c11 = t1.GetColumn("Column 11");
DcColumn c12 = t1.GetColumn("Column 12");
DcColumn c13 = t1.GetColumn("Column 13");
DcTable t2 = schema.GetSubTable("Table 2");
DcColumn c21 = t2.GetColumn("Column 21");
DcColumn c22 = t2.GetColumn("Column 22");
//
// Data
//
t1.GetData().Length = 3;
// 2. Write/read individual column data by using column data methods (not table methods)
Assert.AreEqual(true, c11.GetData().IsNull(1)); // Initially, all outputs must be null
c11.GetData().SetValue(1, 10);
c11.GetData().SetValue(0, 20);
c11.GetData().SetValue(2, 30);
Assert.AreEqual(false, c11.GetData().IsNull(1));
Assert.AreEqual(10, c11.GetData().GetValue(1));
Assert.AreEqual(true, c13.GetData().IsNull(2)); // Initially, all outputs must be null
c13.GetData().SetValue(1, 10.0);
c13.GetData().SetValue(0, 20.0);
c13.GetData().SetValue(2, 30.0);
Assert.AreEqual(false, c13.GetData().IsNull(1));
Assert.AreEqual(10.0, c13.GetData().GetValue(1));
t2.GetData().Length = 2;
c21.GetData().SetValue(0, "Value A");
c21.GetData().SetValue(1, "Value B");
c22.GetData().SetValue(0, 10);
c22.GetData().SetValue(1, 20);
Assert.AreEqual(10, c22.GetData().GetValue(0));
Assert.AreEqual(20, c22.GetData().GetValue(1));
}
public void CsvWriteTest() // Store schema and data to a CSV file as a result of evaluation
{
DcSpace space = new Space();
DcSchema schema = space.CreateSchema("My Schema", DcSchemaKind.Dc);
CoreTest.CreateSampleSchema(schema);
CoreTest.CreateSampleData(schema);
DcTable t2 = schema.GetSubTable("Table 2");
DcColumn c21 = t2.GetColumn("Column 21");
DcColumn c22 = t2.GetColumn("Column 22");
DcColumn c23 = t2.GetColumn("Column 23");
//
// Create schema for a remote db
//
SchemaCsv top = (SchemaCsv)space.CreateSchema("My Files", DcSchemaKind.Csv);
// Create a remote file description
TableCsv table = (TableCsv)space.CreateTable(DcSchemaKind.Csv, "Table_1", top.Root);
table.FilePath = CsvWrite;
// Manually create column to be imported (we need an automatic mechanism for appending missing columns specified in the formula)
DcColumn p1 = space.CreateColumn("Column 11", table, top.GetPrimitiveType("String"), true);
DcColumn p2 = space.CreateColumn("Column 12", table, top.GetPrimitiveType("String"), true);
DcColumn p3 = space.CreateColumn("Custom Column 13", table, top.GetPrimitiveType("String"), true);
DcColumn p4 = space.CreateColumn("Constant Column", table, top.GetPrimitiveType("String"), true);
// Define export column
DcColumn col = space.CreateColumn("Export", schema.GetSubTable("Table 1"), table, false);
col.GetData().IsAppendData = true;
col.GetData().Formula = "(( [String] [Column 11] = this.[Column 11], [String] [Column 12] = [Column 12], [String] [Custom Column 13] = [Column 13], [String] [Constant Column] = 20.02 ))"; // Tuple structure corresponds to output table
col.GetData().IsAppendData = true;
col.GetData().IsAppendSchema = true;
table.Populate();
}
public void NativeFunctionTest() // Call native function in column definition
{
CreateSampleData(schema);
DcTable t1 = schema.GetSubTable("Table 1");
DcColumn c11 = t1.GetColumn("Column 11");
DcColumn c12 = t1.GetColumn("Column 12");
DcColumn c13 = t1.GetColumn("Column 13");
DcColumn c14 = t1.GetColumn("Column 14");
//
// Define a derived column with a definition
//
DcColumn c15 = space.CreateColumn("Column 15", t1, schema.GetPrimitiveType("String"), false);
c15.GetData().Formula = "call:System.String.Substring( [Column 12], 7, 1 )";
// Evaluate column
c15.GetData().Evaluate();
Assert.AreEqual("0", c15.GetData().GetValue(0));
Assert.AreEqual("1", c15.GetData().GetValue(1));
Assert.AreEqual("2", c15.GetData().GetValue(2));
//
// Define a derived column with a definition
//
DcColumn c16 = space.CreateColumn("Column 15", t1, schema.GetPrimitiveType("Double"), false);
c16.GetData().Formula = "call:System.Math.Pow( [Column 11] / 10.0, [Column 13] / 10.0 )";
c16.GetData().Evaluate();
Assert.AreEqual(4.0, c16.GetData().GetValue(0));
Assert.AreEqual(1.0, c16.GetData().GetValue(1));
Assert.AreEqual(27.0, c16.GetData().GetValue(2));
}
public void SchemaTest() // ComColumn. Manually add/remove tables/columns
{
DcTable t1 = schema.GetSubTable("Table 1");
DcTable t2 = schema.GetSubTable("Table 2");
// Finding by name and check various properties provided by the schema
Assert.AreEqual(schema.GetPrimitiveType("Decimal").Name, "Decimal");
Assert.AreEqual(t1.Name, "Table 1");
Assert.AreEqual(t2.Name, "Table 2");
Assert.AreEqual(schema.GetSubTable("Table 2"), t2);
Assert.AreEqual(t1.GetColumn("Column 11").Name, "Column 11");
Assert.AreEqual(t2.GetColumn("Column 21").Name, "Column 21");
Assert.AreEqual(t2.GetColumn("Super").IsSuper, true);
Assert.AreEqual(t2.SuperColumn.Input, t2);
Assert.AreEqual(t2.SuperColumn.Output, schema.Root);
// Test path enumerator
var pathEnum = new PathEnumerator(t2, t1, ColumnType.IDENTITY_ENTITY);
Assert.AreEqual(1, pathEnum.Count());
}
public void JsonReadTest() // Serialize/deserialize schema elements
{
DcSpace ds = new Space();
DcSchema schema = ds.CreateSchema("My Schema", DcSchemaKind.Dc);
CoreTest.CreateSampleSchema(schema);
// Add table definition
DcTable t = schema.GetSubTable("Table 2");
t.GetData().WhereFormula = "[Column 22] > 20.0 && this.Super.[Column 23] < 50";
// Add column definition
DcColumn c = t.GetColumn("Column 22");
c.GetData().Formula = "([Column 11]+10.0) * this.[Column 13]";
JObject space = Utils.CreateJsonFromObject(ds);
ds.ToJson(space);
// Serialize into json string
string jsonDs = JsonConvert.SerializeObject(space, Newtonsoft.Json.Formatting.Indented, new Newtonsoft.Json.JsonSerializerSettings {
});
// De-serialize from json string: http://weblog.west-wind.com/posts/2012/Aug/30/Using-JSONNET-for-dynamic-JSON-parsing
dynamic objDs = JsonConvert.DeserializeObject(jsonDs);
//dynamic obj = JObject/JValue/JArray.Parse(json);
//
// Instantiate and initialize
//
ds = (Space)Utils.CreateObjectFromJson(objDs);
((Space)ds).FromJson(objDs, ds);
Assert.AreEqual(5, ds.GetSchemas()[0].GetSubTable("Table 1").Columns.Count);
Assert.AreEqual(5, ds.GetSchemas()[0].GetSubTable("Table 2").Columns.Count);
Assert.AreEqual("Table 1", ds.GetSchemas()[0].GetSubTable("Table 2").GetColumn("Table 1").Output.Name);
c = t.GetColumn("Column 22");
//Assert.AreEqual(DcColumnDefinitionType.ARITHMETIC, c.Definition.FormulaExpr.DefinitionType);
//Assert.AreEqual(2, c.GetData().FormulaExpr.Children.Count);
//
// 2. Another sample schema with several schemas and inter-schema columns
//
string jsonDs2 = @"{
'type': 'Space',
'schemas': [
{
'type': 'Schema',
'name': 'My Schema',
'tables': [
{ 'type': 'Set', 'name': 'My Table' }
],
'columns': [
{ 'type': 'Dim', 'name': 'My Column', 'lesser_table': {schema_name:'My Schema', table_name:'My Table'}, 'greater_table': {schema_name:'My Schema', table_name:'Double'} },
{ 'type': 'Dim', 'name': 'Import Column', 'lesser_table': {schema_name: 'Rel Schema', table_name:'Rel Table'}, 'greater_table': {schema_name: 'My Schema', table_name: 'My Table'} }
]
},
{
'type': 'SchemaCsv',
'name': 'Rel Schema',
'tables': [
{ 'type': 'SetRel', 'name': 'Rel Table' }
],
'columns': [
{ 'type': 'DimRel', 'name': 'My Column', 'lesser_table': {schema_name:'Rel Schema', table_name:'Rel Table'}, 'greater_table': {schema_name:'Rel Schema', table_name:'String'} },
]
}
]
}";
/*
* dynamic objWs2 = JsonConvert.DeserializeObject(jsonWs2);
*
* Workspace ws2 = Utils.CreateObjectFromJson(objWs2);
* ws2.FromJson(objWs2, ws2);
*
* Assert.AreEqual(2, ws2.Schemas.Count);
* Assert.AreEqual("My Schema", ws2.Mashup.Name);
* Assert.AreEqual("My Table", ws2.Schemas[1].FindTable("Rel Table").GetGreaterDim("Import Column").Output.Name);
*/
}
public static void CreateSampleData(DcSchema schema)
{
//
// Fill sample data in "Table 1"
//
DcTable t1 = schema.GetSubTable("Table 1");
DcColumn c11 = t1.GetColumn("Column 11");
DcColumn c12 = t1.GetColumn("Column 12");
DcColumn c13 = t1.GetColumn("Column 13");
DcColumn c14 = t1.GetColumn("Column 14");
DcColumn[] cols = new DcColumn[] { c11, c12, c13, c14 };
object[] vals = new object[4];
DcTableWriter w1 = t1.GetData().GetTableWriter();
vals[0] = 20;
vals[1] = "Record 0";
vals[2] = 20.0;
vals[3] = 20.0;
w1.Append(cols, vals);
vals[0] = 10;
vals[1] = "Record 1";
vals[2] = 10.0;
vals[3] = 20.0;
w1.Append(cols, vals);
vals[0] = 30;
vals[1] = "Record 2";
vals[2] = 30.0;
vals[3] = 20.0;
w1.Append(cols, vals);
//
// Fill sample data in "Table 2"
//
DcTable t2 = schema.GetSubTable("Table 2");
DcColumn c21 = t2.GetColumn("Column 21");
DcColumn c22 = t2.GetColumn("Column 22");
DcColumn c23 = t2.GetColumn("Column 23");
DcColumn c24 = t2.GetColumn("Table 1");
cols = new DcColumn[] { c21, c22, c23, c24 };
vals = new object[4];
DcTableWriter w2 = t2.GetData().GetTableWriter();
vals[0] = "Value A";
vals[1] = 20;
vals[2] = 40.0;
vals[3] = 0;
w2.Append(cols, vals);
vals[0] = "Value A";
vals[1] = 30;
vals[2] = 40.0;
vals[3] = 1;
w2.Append(cols, vals);
vals[0] = "Value A";
vals[1] = 30;
vals[2] = 50.0;
vals[3] = 1;
w2.Append(cols, vals);
vals[0] = "Value B";
vals[1] = 30;
vals[2] = 50.0;
vals[3] = 1;
w2.Append(cols, vals);
}
public static object ResolveJsonRef(JObject json, DcSpace ws) // Resolve a json reference to a real object
{
if (json == null)
{
return(null);
}
string element_type = (string)json["element_type"];
if (element_type == null)
{
if (json["schema_name"] == null)
{
return(null);
}
else if (json["table_name"] == null)
{
element_type = "schema";
}
else if (json["column_name"] == null)
{
element_type = "table";
}
else
{
element_type = "column";
}
}
if (element_type == "schema") // Find schema
{
return(ws.GetSchema((string)json["schema_name"]));
}
else if (element_type == "table") // Find table
{
DcSchema schema = ws.GetSchema((string)json["schema_name"]);
if (schema == null)
{
return(null);
}
return(schema.GetSubTable((string)json["table_name"]));
}
else if (element_type == "column") // Find column
{
DcSchema schema = ws.GetSchema((string)json["schema_name"]);
if (schema == null)
{
return(null);
}
DcTable table = schema.GetSubTable((string)json["table_name"]);
if (table == null)
{
return(null);
}
return(table.GetColumn((string)json["column_name"]));
}
else
{
throw new NotImplementedException("Unknown element type in the reference.");
}
}
