public void TableBodyPartInsertTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
table.Columns = new List<DBColumn>() { col1, col2 };
ColumnMapping colMap1 = new NullColumnMapping(col1, ColumnUse.Exclude);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 });
ImportConfiguration config = getTestImportConfig();
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string[] bodyParts = part.GetStatementBodyPart().Split(new string[] { "\n"}, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(3, bodyParts.Length);
Assert.AreEqual("INSERT INTO dbo.TestTable (TestCol2)", bodyParts[0]);
Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2 INTO @sqlimport_table_" + tableMapping.TableMappingReference.Replace(".", "_") +
"(TestCol1, TestCol2)" , bodyParts[1]);
Assert.AreEqual("VALUES ('2')", bodyParts[2]);
}
public void StatementColumnMappingPartTest()
{
DBColumn col = new DBColumn(null, "TestCol", true, DBDatatype.integer);
SourceDataEntry entry = SourceDataEntry.CreateDataEntry("Test", DataType.String, "Test");
SourceDataRow row = new SourceDataRow(new SourceDataEntry[] {entry}, "");
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
table.Columns = new List<DBColumn>() { col1 };
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, null);
NullColumnMapping nullColumnMapping = new NullColumnMapping(col, ColumnUse.Insert);
LiteralColumnMapping literalColumnMapping1 = new LiteralColumnMapping("Test", LiteralType.String, col, ColumnUse.Insert);
LiteralColumnMapping literalColumnMapping2 = new LiteralColumnMapping("Test's", LiteralType.String, col, ColumnUse.Insert);
ExcelColumnMapping excelColumnMapping = new ExcelColumnMapping("Test", col, ColumnUse.Insert);
TableColumnMapping tableColMapping = new TableColumnMapping(tableMapping, col1, col, ColumnUse.Insert);
StatementColumnMappingPart nullColumnPart = new StatementColumnMappingPart(nullColumnMapping, row);
StatementColumnMappingPart literalColumnPart1 = new StatementColumnMappingPart(literalColumnMapping1, row);
StatementColumnMappingPart literalColumnPart2 = new StatementColumnMappingPart(literalColumnMapping2, row);
StatementColumnMappingPart excelColumnPart = new StatementColumnMappingPart(excelColumnMapping, row);
StatementColumnMappingPart tableColumnPart = new StatementColumnMappingPart(tableColMapping, row);
Assert.AreEqual("NULL", nullColumnPart.GetColumnMappingValue());
Assert.AreEqual("'Test'", literalColumnPart1.GetColumnMappingValue());
Assert.AreEqual("'Test''s'", literalColumnPart2.GetColumnMappingValue());
Assert.AreEqual("'Test'", excelColumnPart.GetColumnMappingValue());
StatementTableVariablePart tableVariablePart = new StatementTableVariablePart(tableMapping);
Assert.AreEqual(String.Format("(SELECT TOP 1 t.TestCol1 FROM {0} t)", tableVariablePart.GetTableVariable()),
tableColumnPart.GetColumnMappingValue());
}
public TableMapping(DBTable destinationTable, TableMappingImportType importType, ColumnMapping[] mappings)
{
this.destinationTable = destinationTable;
this.importType = importType;
this.columnMappings = mappings;
this.index = counter;
counter++;
}
public static TableMapping[] TableMappingTestData()
{
DBTable addressTable = new DBTable("dbo", "Address");
DBColumn a_idCol = new DBColumn(addressTable, "a_id", true, DBDatatype.integer);
DBColumn streetNameCol = new DBColumn(addressTable, "StreetName", false, DBDatatype.nvarchar);
DBColumn streetNumberCol = new DBColumn(addressTable, "StreetNumber", false, DBDatatype.integer);
DBColumn zipCodeCol = new DBColumn(addressTable, "ZipCode", false, DBDatatype.integer);
addressTable.Columns = new List<DBColumn>() { a_idCol, streetNameCol, streetNumberCol, zipCodeCol };
NullColumnMapping a_idMapping = new NullColumnMapping(a_idCol, ColumnUse.Insert);
ExcelColumnMapping streetNameMapping = new ExcelColumnMapping("Street name", streetNameCol, ColumnUse.Insert);
ExcelColumnMapping streetNumberMapping = new ExcelColumnMapping("Street number", streetNumberCol, ColumnUse.Insert);
ExcelColumnMapping zipCodeMapping = new ExcelColumnMapping("Zip code", zipCodeCol, ColumnUse.Insert);
TableMapping addressTableMapping = new TableMapping(addressTable, TableMappingImportType.Insert, new ColumnMapping[] { a_idMapping, streetNameMapping, streetNumberMapping, zipCodeMapping });
DBTable personTable = new DBTable("dbo", "Person");
DBColumn p_idCol = new DBColumn(personTable, "p_id", true, DBDatatype.integer);
DBColumn firstNameCol = new DBColumn(personTable, "FirstName", false, DBDatatype.nvarchar);
DBColumn lastNameCol = new DBColumn(personTable, "LastName", false, DBDatatype.nvarchar);
DBColumn a_idPersonCol = new DBColumn(personTable, "a_id", false, DBDatatype.integer);
personTable.Columns = new List<DBColumn>() { p_idCol, firstNameCol, lastNameCol, a_idPersonCol };
TableMapping personTableMapping = new TableMapping(personTable, TableMappingImportType.Insert, new ColumnMapping[0]);
NullColumnMapping p_idMapping = new NullColumnMapping(p_idCol, ColumnUse.Insert);
ExcelColumnMapping firstNameMapping = new ExcelColumnMapping("FirstName", firstNameCol, ColumnUse.Insert);
ExcelColumnMapping lastNameMapping = new ExcelColumnMapping("Surname", lastNameCol, ColumnUse.Insert);
TableColumnMapping aIdMapping = new TableColumnMapping(addressTableMapping, a_idMapping.DestinationColumn, a_idPersonCol, ColumnUse.Insert);
personTableMapping.ColumnMappings = new ColumnMapping[] { p_idMapping, firstNameMapping, lastNameMapping, aIdMapping };
DBTable contactInfoTable = new DBTable("dbo", "ContactInfo");
DBColumn pn_idCol = new DBColumn(contactInfoTable, "pn_id", true, DBDatatype.integer);
DBColumn textCol = new DBColumn(contactInfoTable, "text", false, DBDatatype.nvarchar);
DBColumn p_idCICol = new DBColumn(contactInfoTable, "p_id", false, DBDatatype.integer);
DBColumn ci_idCICol = new DBColumn(contactInfoTable, "ci_id", false, DBDatatype.integer);
contactInfoTable.Columns = new List<DBColumn>() { pn_idCol, textCol, p_idCICol, ci_idCICol };
ExcelColumnMapping phoneNumberMapping = new ExcelColumnMapping("Phone", textCol, ColumnUse.Insert);
TableColumnMapping pIDMapping = new TableColumnMapping(personTableMapping, p_idMapping.DestinationColumn, p_idCICol, ColumnUse.Insert);
LiteralColumnMapping citIdMapping = new LiteralColumnMapping("1", LiteralType.Integer, ci_idCICol, ColumnUse.Insert);
TableMapping phoneTableMapping = new TableMapping(contactInfoTable, TableMappingImportType.Insert, new ColumnMapping[] { phoneNumberMapping, pIDMapping, citIdMapping });
ExcelColumnMapping mobileNumberMapping = new ExcelColumnMapping("Mobile", textCol, ColumnUse.Insert);
TableColumnMapping pIDMobileMapping = new TableColumnMapping(personTableMapping, p_idMapping.DestinationColumn, p_idCICol, ColumnUse.Insert);
LiteralColumnMapping citIdMobileMapping = new LiteralColumnMapping("2", LiteralType.Integer, ci_idCICol, ColumnUse.Insert);
TableMapping mobileTableMapping = new TableMapping(contactInfoTable, TableMappingImportType.Insert, new ColumnMapping[] { mobileNumberMapping, pIDMobileMapping, citIdMobileMapping });
return new TableMapping[] { personTableMapping, phoneTableMapping, addressTableMapping, mobileTableMapping };
}
public TableMappingViewModel(DBTable table, MappingPageViewModel mappingPageViewModel)
{
this.viewModel = mappingPageViewModel;
ColumnMapping[] mappings = new ColumnMapping[table.Columns.Count];
ObservableCollection<ColumnMappingViewModel> viewModels = new ObservableCollection<ColumnMappingViewModel>();
for (int i = 0; i < table.Columns.Count; i++)
{
mappings[i] = new NullColumnMapping(table.Columns[i], ColumnUse.Insert);
viewModels.Add(new NullColumnMappingViewModel((NullColumnMapping)mappings[i], this));
}
this.tableMapping = new TableMapping(table, TableMappingImportType.Insert, mappings);
this.columnsMappingViewModels = viewModels.ToList();
}
public void CycleTestTwoTable()
{
DBTable table1 = new DBTable("dbo", "1");
DBColumn table1ID = new DBColumn(table1, "1_id", true, DBDatatype.integer);
table1.Columns = new List<DBColumn>() { table1ID };
DBTable table2 = new DBTable("dbo", "2");
DBColumn table2ID = new DBColumn(table2, "2_id", true, DBDatatype.integer);
table2.Columns = new List<DBColumn>() { table2ID };
TableMapping t1Mapping = new TableMapping(table1, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t2Mapping = new TableMapping(table2, TableMappingImportType.Insert, new ColumnMapping[0]);
TableColumnMapping t1ColMaping = new TableColumnMapping(t2Mapping, table2ID, table1ID, ColumnUse.Insert);
TableColumnMapping t2ColMaping = new TableColumnMapping(t1Mapping, table1ID, table2ID, ColumnUse.Insert);
t1Mapping.ColumnMappings = new ColumnMapping[] { t1ColMaping };
t2Mapping.ColumnMappings = new ColumnMapping[] { t2ColMaping };
TableMapping[] mappingArray = new TableMapping[] { t1Mapping, t2Mapping };
TableMappingOrderer tableMappingOrderer = new TableMappingOrderer(mappingArray);
TableMapping[] order = tableMappingOrderer.OrderTableMappings();
}
public void CycleTestFourTable()
{
DBTable table1 = new DBTable("dbo", "1");
DBColumn table1ID = new DBColumn(table1, "1_id", true, DBDatatype.integer);
table1.Columns = new List<DBColumn>() { table1ID };
DBTable table2 = new DBTable("dbo", "2");
DBColumn table2ID = new DBColumn(table2, "2_id", true, DBDatatype.integer);
table2.Columns = new List<DBColumn>() { table2ID };
DBTable table3 = new DBTable("dbo", "3");
DBColumn table3ID = new DBColumn(table3, "3_id", true, DBDatatype.integer);
table3.Columns = new List<DBColumn>() { table3ID };
DBTable table4 = new DBTable("dbo", "4");
DBColumn table4ID = new DBColumn(table4, "4_id", true, DBDatatype.integer);
table4.Columns = new List<DBColumn>() { table4ID };
TableMapping t1Mapping = new TableMapping(table1, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t2Mapping = new TableMapping(table2, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t3Mapping = new TableMapping(table3, TableMappingImportType.Insert, new ColumnMapping[0]);
TableMapping t4Mapping = new TableMapping(table4, TableMappingImportType.Insert, new ColumnMapping[0]);
TableColumnMapping t21ColMaping = new TableColumnMapping(t1Mapping, table1ID, table2ID, ColumnUse.Insert);
TableColumnMapping t24ColMaping = new TableColumnMapping(t4Mapping, table1ID, table2ID, ColumnUse.Insert);
TableColumnMapping t3ColMaping = new TableColumnMapping(t2Mapping, table1ID, table2ID, ColumnUse.Insert);
TableColumnMapping t4ColMaping = new TableColumnMapping(t3Mapping, table1ID, table2ID, ColumnUse.Insert);
t2Mapping.ColumnMappings = new ColumnMapping[] { t21ColMaping, t24ColMaping };
t3Mapping.ColumnMappings = new ColumnMapping[] { t3ColMaping };
t4Mapping.ColumnMappings = new ColumnMapping[] { t4ColMaping };
TableMapping[] mappingArray = new TableMapping[] { t1Mapping, t2Mapping, t3Mapping, t4Mapping };
TableMappingOrderer tableMappingOrderer = new TableMappingOrderer(mappingArray);
TableMapping[] order = tableMappingOrderer.OrderTableMappings();
}
public DBForeignKey(DBTable primaryKeyTable, DBColumn primaryKeyCol, DBTable foreignKeyTable, DBColumn foreignKeyCol)
{
this.primaryKeyTable = primaryKeyTable;
this.primaryKeyCol = primaryKeyCol;
this.foreignKeyTable = primaryKeyTable;
this.foreignKeyCol = foreignKeyCol;
}
//TODO: Support more column metadata?
//private bool isNullable;
//private int charMaxLength;
//private int numericPrecision;
//private int datetimePrecision;
//private DatabaseCharset charSet;
public DBColumn(DBTable table, string name, bool isPrimaryKey, DBDatatype datatype)
{
this.table = table;
this.name = name;
this.isPrimaryKey = isPrimaryKey;
this.dataType = datatype;
}
public void AddMappingTable(DBTable table)
{
tableMappingViewModels.Add(new TableMappingViewModel(table, this));
NotifyPropertyChanged("TableMappings");
NotifyPropertyChanged("MappingTableReferences");
}
public void TableVariablePartTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
table.Columns = new List<DBColumn>() { col1, col2 };
ColumnMapping colMap1 = new LiteralColumnMapping("1", LiteralType.Integer, col1, ColumnUse.Exclude);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 });
ImportConfiguration config = getTestImportConfig();
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, null);
string partStatement = part.GetTableVariablePart().Replace("\n", "");
Assert.AreEqual("DECLARE @sqlimport_table_" + tableMapping.TableMappingReference.Replace(".", "_") + " TABLE (TestCol1 integer, TestCol2 nvarchar(max))",
partStatement);
}
public void TableMappingPartConstructorTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", false, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
table.Columns = new List<DBColumn>() { col1, col2 };
ColumnMapping colMap1 = new LiteralColumnMapping("1", LiteralType.Integer, col1, ColumnUse.Exclude);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Insert);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Insert, new ColumnMapping[] { colMap1, colMap2 });
ImportConfiguration config = getTestImportConfig();
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, null);
}
public void TableBodyPartUpdateTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
DBColumn col3 = new DBColumn(table, "TestCol3", false, DBDatatype.integer);
table.Columns = new List<DBColumn>() { col1, col2, col3 };
TableMapping sourceTablemapping = new TableMapping(new DBTable("dbo", "TestTable2"), TableMappingImportType.Insert, null);
ColumnMapping colMap1 = new TableColumnMapping(sourceTablemapping, col1, col1, ColumnUse.Where);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Where);
ColumnMapping colMap3 = new LiteralColumnMapping("3", LiteralType.String, col3, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ImportConfiguration config = getTestImportConfig();
SourceDataEntry[] entries = new SourceDataEntry[] { SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
StatementTableMappingPart part = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string[] bodyParts = part.GetStatementBodyPart().Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual("UPDATE dbo.TestTable", bodyParts[0]);
Assert.AreEqual("SET TestCol3='3'", bodyParts[1]);
Assert.AreEqual("OUTPUT inserted.TestCol1, inserted.TestCol2, inserted.TestCol3 INTO @sqlimport_table_" +
tableMapping.TableMappingReference.Replace(".", "_") + "(TestCol1, TestCol2, TestCol3)", bodyParts[2]);
Assert.AreEqual("WHERE TestCol1 = (SELECT TOP 1 t.TestCol1 FROM @sqlimport_table_" +
sourceTablemapping.TableMappingReference.Replace(".", "_") + " t) and TestCol2 = '2'", bodyParts[3]);
}
public void StatementCreatorOrderTest()
{
DBTable table = new DBTable("dbo", "TestTable");
DBColumn col1 = new DBColumn(table, "TestCol1", true, DBDatatype.integer);
DBColumn col2 = new DBColumn(table, "TestCol2", false, DBDatatype.nvarchar);
DBColumn col3 = new DBColumn(table, "TestCol3", false, DBDatatype.integer);
table.Columns = new List<DBColumn>() { col1, col2, col3 };
Database db = new Database("TestDB", new List<DBTable>() { table });
TableMapping sourceTablemapping = new TableMapping(new DBTable("dbo", "TestTable2"), TableMappingImportType.Insert, null);
ColumnMapping colMap1 = new TableColumnMapping(sourceTablemapping, col1, col1, ColumnUse.Where);
ColumnMapping colMap2 = new LiteralColumnMapping("2", LiteralType.String, col2, ColumnUse.Where);
ColumnMapping colMap3 = new LiteralColumnMapping("3", LiteralType.String, col2, ColumnUse.Set);
TableMapping tableMapping = new TableMapping(table, TableMappingImportType.Update, new ColumnMapping[] { colMap1, colMap2, colMap3 });
ErrorHandling errorHandling = new ErrorHandling();
ImportConfiguration config = new ImportConfiguration(new TableMapping[] { tableMapping }, null, "TestDB", errorHandling);
SourceDataEntry[] entries = new SourceDataEntry[] {SourceDataEntry.CreateDataEntry("", DataType.String, "") };
SourceDataRow[] rows = new SourceDataRow[] { new SourceDataRow(entries, "0") };
SourceDataTable dt = new SourceDataTable(rows, new string[] { "" });
SQLServerStatementCreator statementCreator = new SQLServerStatementCreator(config, dt);
ImportStatement statement = statementCreator.CreateStatement(0);
ImportStatement[] statements = statementCreator.CreateStatements();
Assert.AreEqual(1, statements.Length);
Assert.AreEqual(statement.RowReference, statements[0].RowReference);
Assert.AreEqual(statement.SqlStatement, statements[0].SqlStatement);
string[] lines = statement.SqlStatement
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
StatementSetupPart setupPart = new StatementSetupPart(config);
Assert.AreEqual(setupPart.GetDatabasePart(), lines[0]);
Assert.AreEqual(setupPart.GetWarningsPart(), lines[1]);
StatementTransactionPart transPart = new StatementTransactionPart(config);
string[] transStartPart = transPart.GetTransactionStartPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray(); ;
Assert.AreEqual(2, transStartPart.Length);
Assert.AreEqual(transStartPart[0], lines[2]);
Assert.AreEqual(transStartPart[1], lines[3]);
StatementTableMappingPart tmParts = new StatementTableMappingPart(tableMapping, dt.GetDataRow(0));
string variablePart = tmParts.GetTableVariablePart().Replace("\n", "");
Assert.AreEqual(variablePart, lines[4]);
string[] bodyParts = tmParts.GetStatementBodyPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(4, bodyParts.Length);
Assert.AreEqual(bodyParts[0], lines[5]);
Assert.AreEqual(bodyParts[1], lines[6]);
Assert.AreEqual(bodyParts[2], lines[7]);
Assert.AreEqual(bodyParts[3], lines[8]);
string[] transEndPart = transPart.GetTransactionEndPart()
.Split(new string[] { "\n" }, StringSplitOptions.None).Where(s => s.Length > 0).ToArray();
Assert.AreEqual(12, transEndPart.Length);
Assert.AreEqual(transEndPart[0], lines[9]);
Assert.AreEqual(transEndPart[1], lines[10]);
Assert.AreEqual(transEndPart[2], lines[11]);
Assert.AreEqual(transEndPart[3], lines[12]);
Assert.AreEqual(transEndPart[4], lines[13]);
Assert.AreEqual(transEndPart[5], lines[14]);
Assert.AreEqual(transEndPart[6], lines[15]);
Assert.AreEqual(transEndPart[7], lines[16]);
Assert.AreEqual(transEndPart[8], lines[17]);
Assert.AreEqual(transEndPart[9], lines[18]);
Assert.AreEqual(transEndPart[10], lines[19]);
Assert.AreEqual(transEndPart[11], lines[20]);
}