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 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 static void CreateSampleSchema(DcSchema schema)
{
DcSpace space = schema.Space;
// Table 1
DcTable t1 = space.CreateTable(DcSchemaKind.Dc, "Table 1", schema.Root);
DcColumn c11 = space.CreateColumn("Column 11", t1, schema.GetPrimitiveType("Integer"), true);
DcColumn c12 = space.CreateColumn("Column 12", t1, schema.GetPrimitiveType("String"), true);
DcColumn c13 = space.CreateColumn("Column 13", t1, schema.GetPrimitiveType("Double"), false);
DcColumn c14 = space.CreateColumn("Column 14", t1, schema.GetPrimitiveType("Decimal"), false);
// Table 2
DcTable t2 = space.CreateTable(DcSchemaKind.Dc, "Table 2", schema.Root);
DcColumn c21 = space.CreateColumn("Column 21", t2, schema.GetPrimitiveType("String"), true);
DcColumn c22 = space.CreateColumn("Column 22", t2, schema.GetPrimitiveType("Integer"), true);
DcColumn c23 = space.CreateColumn("Column 23", t2, schema.GetPrimitiveType("Double"), false);
DcColumn c24 = space.CreateColumn("Table 1", t2, t1, false);
}
public List <Mapping> MapPrimitiveSet(DcTable sourceSet, DcSchema targetSchema)
{
DcSchema sourceSchema = sourceSet.Schema;
List <Mapping> maps = new List <Mapping>();
DcTable targetSet;
if (sourceSchema.GetType() == typeof(Schema.Schema)) // Schema -> *
{
if (targetSchema.GetType() == typeof(Schema.Schema)) // Schema -> Schema
{
targetSet = targetSchema.GetPrimitiveType(sourceSet.Name);
Mapping map = new Mapping(sourceSet, targetSet);
map.Similarity = 1.0;
maps.Add(map);
}
else if (targetSchema.GetType() == typeof(SchemaOledb)) // Schema -> SchemaOledb
{
throw new NotImplementedException();
}
}
else if (sourceSchema is SchemaOledb) // SchemaOledb -> *
{
if (targetSchema.GetType() == typeof(Schema.Schema)) // SchemaOledb -> Schema
{
OleDbType sourceType = (OleDbType)Enum.Parse(typeof(OleDbType), sourceSet.Name, false); // Convert type representation: from name to enum (equivalent)
string targetType;
// Mappings:
// http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtype(v=vs.110).aspx
// http://msdn.microsoft.com/en-us/library/cc668759(v=vs.110).aspx
switch (sourceType)
{ // Integers
case OleDbType.BigInt: // DBTYPE_I8 -> Int64
case OleDbType.Integer: // DBTYPE_I4 -> Int32
case OleDbType.SmallInt: // DBTYPE_I2 -> Int16
case OleDbType.TinyInt: // DBTYPE_I1 -> SByte
case OleDbType.UnsignedBigInt: // DBTYPE_UI8 -> UInt64
case OleDbType.UnsignedInt: // DBTYPE_UI4 -> UInt32
case OleDbType.UnsignedSmallInt: // DBTYPE_UI2 -> UInt16
case OleDbType.UnsignedTinyInt: // DBTYPE_UI1 -> Byte
targetType = "Integer";
break;
// Double
case OleDbType.Double: // DBTYPE_R8
case OleDbType.Single: // DBTYPE_R4 -> Single
targetType = "Double";
break;
// Decimal
case OleDbType.Currency: // DBTYPE_CY
case OleDbType.Decimal: // DBTYPE_DECIMAL
case OleDbType.Numeric: // DBTYPE_NUMERIC
case OleDbType.VarNumeric:
targetType = "Decimal";
break;
// Boolean
case OleDbType.Boolean: // DBTYPE_BOOL
targetType = "Boolean";
break;
// DateTime
case OleDbType.Date: // DBTYPE_DATE
case OleDbType.DBDate: // DBTYPE_DBDATE
case OleDbType.DBTime: // DBTYPE_DBTIME -> TimeSpan
case OleDbType.DBTimeStamp: // DBTYPE_DBTIMESTAMP
case OleDbType.Filetime: // DBTYPE_FILETIME
targetType = "DateTime";
break;
// Strings
case OleDbType.BSTR: // DBTYPE_BSTR
case OleDbType.Char: // DBTYPE_STR
case OleDbType.LongVarChar: //
case OleDbType.LongVarWChar: //
case OleDbType.VarChar: //
case OleDbType.VarWChar: //
case OleDbType.WChar: // DBTYPE_WSTR
targetType = "String";
break;
// Binary
case OleDbType.Binary: // DBTYPE_BYTES -> Array of type Byte
case OleDbType.LongVarBinary: // Array of type Byte
case OleDbType.VarBinary: // Array of type Byte
// NULL
case OleDbType.Empty: // DBTYPE_EMPTY
case OleDbType.Guid: // DBTYPE_GUID -> Guid
case OleDbType.Error: // DBTYPE_ERROR -> Exception
case OleDbType.IDispatch: // DBTYPE_IDISPATCH -> Object
case OleDbType.IUnknown: // DBTYPE_UNKNOWN -> Object
case OleDbType.PropVariant: // DBTYPE_PROP_VARIANT -> Object
case OleDbType.Variant: // DBTYPE_VARIANT -> Object
targetType = null;
break;
default:
targetType = null;
break;
}
targetSet = targetSchema.GetPrimitiveType(targetType);
Mapping map = new Mapping(sourceSet, targetSet);
map.Similarity = 1.0;
maps.Add(map);
}
else if (targetSchema.GetType() == typeof(SchemaOledb)) // SchemaOledb -> SchemaOledb
{
targetSet = targetSchema.GetPrimitiveType(sourceSet.Name);
Mapping map = new Mapping(sourceSet, targetSet);
map.Similarity = 1.0;
maps.Add(map);
}
}
else if (sourceSchema is SchemaCsv) // SchemaCsv -> *
{
if (targetSchema.GetType() == typeof(Schema.Schema)) // SchemaCsv -> Schema
{
string targetType = "String";
}
}
Mappings.AddRange(maps);
return(maps);
}
/// <summary>
/// Create and initialize a new mapping which produces a flat target set with all primitive columns for copying primitive data from the source set.
/// Only identity (PK) source columns are expanded recursively.
/// For relational source, this means that all primitive columns of the source table will be mapped with their relational names, no FK-referenced tables will be joined and no artifical column names will be used.
/// If it is necessary to expand entity columns (non-PK columns of joined tables) then a different implementation is needed (which will require joins, artifical column/path names etc.)
/// </summary>
public Mapping CreatePrimitive(DcTable sourceSet, DcTable targetSet, DcSchema targetSchema)
{
Debug.Assert(!sourceSet.IsPrimitive && !targetSet.IsPrimitive, "Wrong use: copy mapping can be created for only non-primitive tables.");
Debug.Assert(targetSchema != null || targetSet.Schema != null, "Wrong use: target schema must be specified.");
Mapping map = new Mapping(sourceSet, targetSet);
DcSchema sourceSchema = map.SourceTab.Schema;
if (targetSchema == null)
{
targetSchema = targetSet.Schema;
}
ColumnPath sp;
ColumnPath tp;
DcColumn td;
PathMatch match;
if (sourceSchema is SchemaOledb)
{
TableRel set = (TableRel)map.SourceTab;
foreach (ColumnAtt att in set.GreaterPaths)
{
sp = new ColumnAtt(att);
// Recommend matching target type (mapping primitive types)
this.MapPrimitiveSet(att.Output, targetSchema);
DcTable targetType = this.GetBestTargetSet(att.Output, targetSchema);
td = new Schema.Column(att.RelationalColumnName, map.TargetTab, targetType, att.IsKey, false);
tp = new ColumnPath(td);
tp.Name = sp.Name;
match = new PathMatch(sp, tp, 1.0);
map.Matches.Add(match);
}
}
else if (sourceSchema is SchemaCsv)
{
DcTable set = (DcTable)map.SourceTab;
foreach (DcColumn sd in set.Columns)
{
if (sd.IsSuper)
{
continue;
}
// Recommend matching target type (mapping primitive types)
//this.MapPrimitiveSet(sd, targetSchema);
//ComTable targetType = this.GetBestTargetSet(sd.Output, targetSchema);
//
// Analyze sample values of sd and choose the most specific target type
//
List <string> values = ((ColumnCsv)sd).SampleValues;
string targetTypeName;
if (Com.Schema.Utils.isInt32(values.ToArray()))
{
targetTypeName = "Integer";
}
else if (Com.Schema.Utils.isDouble(values.ToArray()))
{
targetTypeName = "Double";
}
else
{
targetTypeName = "String";
}
DcTable targetType = targetSchema.GetPrimitiveType(targetTypeName);
td = targetSchema.Space.CreateColumn(sd.Name, map.TargetTab, targetType, sd.IsKey);
sp = new ColumnPath(sd);
tp = new ColumnPath(td);
match = new PathMatch(sp, tp, 1.0);
map.Matches.Add(match);
}
}
return(map);
}