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); }