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 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 CreateSampleSchema(DcSchema schema)
{
DcSpace space = schema.Space;
DcColumn d1, d2, d3, d4;
DcTable departments = space.CreateTable(DcSchemaKind.Dc, "Departments", schema.Root);
d1 = space.CreateColumn("name", departments, schema.GetPrimitiveType("String"), true);
d2 = space.CreateColumn("location", departments, schema.GetPrimitiveType("String"), false);
DcTableWriter writer;
writer = departments.GetData().GetTableWriter();
writer.Open();
writer.Append(new DcColumn[] { d1, d2 }, new object[] { "SALES", "Dresden" });
writer.Append(new DcColumn[] { d1, d2 }, new object[] { "HR", "Walldorf" });
writer.Close();
DcTable employees = space.CreateTable(DcSchemaKind.Dc, "Employees", schema.Root);
d1 = space.CreateColumn("name", employees, schema.GetPrimitiveType("String"), true);
d2 = space.CreateColumn("age", employees, schema.GetPrimitiveType("Double"), false);
d3 = space.CreateColumn("salary", employees, schema.GetPrimitiveType("Double"), false);
d4 = space.CreateColumn("dept", employees, departments, false);
DcTable managers = space.CreateTable(DcSchemaKind.Dc, "Managers", employees);
d1 = space.CreateColumn("title", managers, schema.GetPrimitiveType("String"), false);
d2 = space.CreateColumn("is project manager", managers, schema.GetPrimitiveType("Boolean"), false);
}
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 TableSubsetTest() // Define a filter to get a subset of record from one table
{
CreateSampleData(schema);
DcTable t2 = schema.GetSubTable("Table 2");
//
// Define a new filter-set
//
DcTable t3 = space.CreateTable(DcSchemaKind.Dc, "Table 3", t2);
t3.GetData().WhereFormula = "[Column 22] > 20.0 && this.Super.[Column 23] < 50";
t3.GetData().Populate();
Assert.AreEqual(1, t3.GetData().Length);
Assert.AreEqual(1, t3.SuperColumn.GetData().GetValue(0));
}
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));
}
private void OkCommand_Executed(object state)
{
DcSpace space = mainVM.Space;
if (IsNew)
{
// Create a new table using parameters in the dialog
DcTable table = space.CreateTable(DcSchemaKind.Dc, TableName, Schema.Root);
table.GetData().WhereFormula = TableFormula;
Table = table;
}
else
{
Table.Name = TableName;
Table.GetData().WhereFormula = TableFormula;
}
((Com.Schema.Table)Table).NotifyPropertyChanged("");
this.DialogResult = true;
}
public virtual void Close()
{
rowid = table.GetData().Length;
}
public void Close()
{
rowid = table.GetData().Length;
connectionCsv.CloseWriter();
}
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 virtual void Evaluate()
{
// Either formula is not needed or not yet provided
if (formulaExpr == null || formulaExpr.DefinitionType == ColumnDefinitionType.FREE)
{
_evaluateError = false;
((Column)Column).NotifyPropertyChanged("");
return; // Nothing to evaluate
}
//
// Evaluate depends on the type of definition
//
AutoIndex = false;
//Nullify();
object thisCurrent = null;
if (Column.Input is TableCsv) // Import from CSV
{
formulaExpr.EvaluateBegin();
DcTableReader tableReader = Column.Input.GetData().GetTableReader();
tableReader.Open();
while ((thisCurrent = tableReader.Next()) != null)
{
thisVariable.SetValue(thisCurrent); // Set parameters of the expression
formulaExpr.Evaluate(); // Evaluate the expression
// We do not store import functions (we do not need this data)
object newValue = formulaExpr.OutputVariable.GetValue();
//columnData.SetValue((Rowid)thisCurrent, newValue);
}
tableReader.Close();
formulaExpr.EvaluateEnd();
}
else if (formulaExpr.DefinitionType == ColumnDefinitionType.ARITHMETIC || formulaExpr.DefinitionType == ColumnDefinitionType.LINK)
{
formulaExpr.EvaluateBegin();
DcTableReader tableReader = Column.Input.GetData().GetTableReader();
tableReader.Open();
while ((thisCurrent = tableReader.Next()) != null)
{
thisVariable.SetValue(thisCurrent); // Set parameters of the expression
formulaExpr.Evaluate(); // Evaluate the expression
// Write the result value to the function
// NOTE: We want to implement write operations with functions in the expression itself, particularly, because this might be done by intermediate nodes each of them having also APPEND flag
// NOTE: when writing or find/append output to a table, an expression needs a TableWriter (or reader) object which is specific to the expression node (also intermediate)
// NOTE: it could be meaningful to implement separately TUPLE (DOWN, NON-PRIMITIVE) nodes and CALL (UP, PRIMITIVE) expression classes since their general logic/purpose is quite different, particularly, for table writing.
// NOTE: where expression (in tables) is evaluated without writing to column
object newValue = formulaExpr.OutputVariable.GetValue();
this.SetValue((Rowid)thisCurrent, newValue);
}
tableReader.Close();
formulaExpr.EvaluateEnd();
}
else if (formulaExpr.DefinitionType == ColumnDefinitionType.AGGREGATION)
{
// Aassert: FactTable.GroupFormula + ThisSet.ThisFunc = FactTable.MeasureFormula
// Aassert: if LoopSet == ThisSet then GroupCode = null, ThisFunc = MeasureCode
DcTable thisTable = Column.Input.Schema.GetSubTable(factsExpr.Name);
formulaExpr.EvaluateBegin();
DcTableReader tableReader = thisTable.GetData().GetTableReader();
tableReader.Open();
while ((thisCurrent = tableReader.Next()) != null)
{
thisVariable.SetValue(thisCurrent); // Set parameters of the expression
groupExpr.Evaluate();
Rowid groupElement = (Rowid)groupExpr.OutputVariable.GetValue();
groupVariable.SetValue(groupElement);
measureExpr.Evaluate();
object measureValue = measureExpr.OutputVariable.GetValue();
measureVariable.SetValue(measureValue);
outputExpr.Evaluate(); // Evaluate the expression
// Write the result value to the function
object newValue = outputExpr.OutputVariable.GetValue();
this.SetValue(groupElement, newValue);
}
tableReader.Close();
formulaExpr.EvaluateEnd();
}
else
{
throw new NotImplementedException("This type of column definition is not implemented.");
}
Reindex();
AutoIndex = true;
// Finally, we need to set the flag that indicates the result of the operation and status for the column
_evaluateError = false;
((Column)Column).NotifyPropertyChanged("");
}
