public void TableProductTest() // Define a new table and populate it
{
CreateSampleData(schema);
DcTable t1 = schema.GetSubTable("Table 1");
DcTable t2 = schema.GetSubTable("Table 2");
//
// Define a new product-set
//
DcTable t3 = space.CreateTable(DcSchemaKind.Dc, "Table 3", schema.Root);
DcColumn c31 = space.CreateColumn(t1.Name, t3, t1, true); // {*20, 10, *30}
DcColumn c32 = space.CreateColumn(t2.Name, t3, t2, true); // {40, 40, *50, *50}
t3.GetData().Populate();
Assert.AreEqual(12, t3.GetData().Length);
//
// Add simple where expression
//
t3.GetData().WhereFormula = "([Table 1].[Column 11] > 10) && this.[Table 2].[Column 23] == 50.0";
t3.GetData().Populate();
Assert.AreEqual(4, t3.GetData().Length);
Assert.AreEqual(0, c31.GetData().GetValue(0));
Assert.AreEqual(2, c32.GetData().GetValue(0));
Assert.AreEqual(0, c31.GetData().GetValue(1));
Assert.AreEqual(3, c32.GetData().GetValue(1));
}
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 string this[int index] // It returns values that are shown in grid columns
{
get
{
object cell = Offset;
if (GridView.ShowPaths) // Use stored paths
{
ColumnPath path = GridView.Paths[index];
int ofs;
for (int i = 0; i < path.Segments.Count; i++)
{
ofs = (int)cell; // Use output as an input for the next iteration
if (path.Segments[i].GetData().IsNull(ofs))
{
cell = null;
break; // Cannot continue with next segments
}
else
{
cell = path.Segments[i].GetData().GetValue(ofs);
}
}
}
else // Use greater dimensions
{
int dimCount = GridView.Set.Columns.Count;
if (index < 0 || index >= dimCount)
{
return(null);
}
DcColumn dim = GridView.Set.Columns[index];
if (dim.GetData().IsNull(Offset))
{
cell = null;
}
else
{
cell = dim.GetData().GetValue(Offset);
}
}
if (cell == null)
{
return("");
}
else
{
return(Convert.ToString(cell));
}
}
}
public void CsvReadTest() // Load Csv schema and data as a result of evaluation
{
DcSpace space = new Space();
// 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, "Products", top.Root);
table.FilePath = CsvRead;
var columns = top.LoadSchema(table);
Assert.AreEqual(1, top.Root.SubTables.Count);
Assert.AreEqual(15, top.GetSubTable("Products").Columns.Count);
Assert.AreEqual("String", top.GetSubTable("Products").GetColumn("Product Name").Output.Name);
Assert.AreEqual("3", ((ColumnCsv)top.GetSubTable("Products").GetColumn("ID")).SampleValues[1]);
//
// Configure import
//
DcSchema schema = space.CreateSchema("My Schema", DcSchemaKind.Dc);
DcTable productsTable = space.CreateTable(DcSchemaKind.Dc, "Products", schema.Root);
// Manually create column to be imported (we need an automatic mechanism for appending missing columns specified in the formula)
DcColumn p1 = space.CreateColumn("ID", productsTable, schema.GetPrimitiveType("Integer"), true);
DcColumn p2 = space.CreateColumn("Product Code", productsTable, schema.GetPrimitiveType("String"), false);
DcColumn p3 = space.CreateColumn("Custom Product Name", productsTable, schema.GetPrimitiveType("String"), false);
DcColumn p4 = space.CreateColumn("List Price", productsTable, schema.GetPrimitiveType("Double"), false);
DcColumn p5 = space.CreateColumn("Constant Column", productsTable, schema.GetPrimitiveType("Double"), false);
// Define import column
DcColumn col = space.CreateColumn("Import", top.GetSubTable("Products"), productsTable, false);
col.GetData().IsAppendData = true;
col.GetData().Formula = "(( [Integer] [ID] = this.[ID], [String] [Product Code] = [Product Code], [String] [Custom Product Name] = [Product Name], [Double] [List Price] = [List Price], [Double] [Constant Column] = 20.02 ))"; // Tuple structure corresponds to output table
col.GetData().IsAppendData = true;
col.GetData().IsAppendSchema = true;
productsTable.GetData().Populate();
Assert.AreEqual(45, productsTable.GetData().Length);
Assert.AreEqual("Northwind Traders Dried Pears", p3.GetData().GetValue(5));
Assert.AreEqual(20.02, p5.GetData().GetValue(5));
}
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 string Value(string dimName)
{
DcColumn dim = GridView.Set.GetColumn(dimName);
if (dim == null)
{
return(null);
}
return((string)dim.GetData().GetValue(Offset));
}
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 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 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 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));
}
private void OkCommand_Executed(object state)
{
if (IsNew)
{
// Create a new column using parameters in the dialog
DcSpace space = mainVM.Space;
DcColumn column = space.CreateColumn(ColumnName, Table, SelectedOutputTable, IsKey);
column.GetData().Formula = ColumnFormula;
column.GetData().IsAppendData = true;
Column = column;
}
else
{
// Update the column using parameters in the dialog
Column.Name = ColumnName;
Column.Output = SelectedOutputTable;
Column.GetData().Formula = ColumnFormula;
}
((Column)Column).NotifyPropertyChanged("");
this.DialogResult = true;
}
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 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);
*/
}
/* TODO: Notify after deleting
* public virtual void Remove()
* {
* if (Output != null) Output.InputColumns.Remove(this);
* if (Input != null) Input.Columns.Remove(this);
*
* // Notify that a new child has been removed
* if (Input != null) ((Table)Input).NotifyRemove(this);
* if (Output != null) ((Table)Output).NotifyRemove(this);
* }
*/
protected void DeleteColumnPropagate(DcColumn column)
{
DcSchema schema = column.Input.Schema;
//
// Delete related columns/tables
//
if (column.GetData().IsAppendData) // Delete all tables that are directly or indirectly generated by this column
{
DcTable gTab = column.Output;
var paths = new PathEnumerator(new List <DcTable>(new DcTable[] { gTab }), new List <DcTable>(), false, ColumnType.GENERATING);
foreach (var path in paths)
{
for (int i = path.Segments.Count - 1; i >= 0; i--)
{
this.DeleteTable(path.Segments[i].Output); // Delete (indirectly) generated table
}
}
this.DeleteTable(gTab); // Delete (directly) generated table
// This column will be now deleted as a result of the deletion of the generated table
}
else if (column.Input.GetData().DefinitionType == TableDefinitionType.PROJECTION) // It is a extracted table and this column is produced by the mapping (depends on function output tuple)
{
//DcColumn projDim = column.Input.InputColumns.Where(d => d.Definition.IsAppendData).ToList()[0];
//Mapping mapping = projDim.Definition.Mapping;
//PathMatch match = mapping.GetMatchForTarget(new DimPath(column));
//mapping.RemoveMatch(match.SourcePath, match.TargetPath);
}
//
// Delete all expression nodes that use the deleted column and all references to this column from other objects
//
List <DcTable> tables = schema.AllSubTables;
var nodes = new List <ExprNode>();
foreach (var tab in tables)
{
if (tab.IsPrimitive)
{
continue;
}
foreach (var col in tab.Columns)
{
if (col.GetData() == null)
{
continue;
}
DcColumnData data = col.GetData();
/* REFACTOR: Here essentially we want to manually find all uses and hence have to use dependencies API
* if (data.FormulaExpr != null)
* {
* nodes = data.FormulaExpr.Find(column);
* foreach (var node in nodes) if (node.Parent != null) node.Parent.RemoveChild(node);
* }
*/
}
// Update table definitions by finding the uses of the specified column
if (tab.GetData().WhereExpr != null)
{
nodes = tab.GetData().WhereExpr.Find(column);
foreach (var node in nodes)
{
if (node.Parent != null)
{
node.Parent.RemoveChild(node);
}
}
}
}
}