//Creates a relationship lookup table for one or more tables
public List <CommandResult> ADD_SCAFFOLD_RELATIONSHIPS(IConnectToDB _Connect, string Name, List <TableStructure> SourceTables)
{
List <CommandResult> HoldResult = new List <CommandResult>();
ER_Generate gen = new ER_Generate();
string tableName = Name + "_REL";
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---Start Scaffold " + tableName + "",
_EndTime = DateTime.Now
});
HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, tableName, "Relationship"));
HoldResult.Add(ER_DDL._ADD_COLUMN(_Connect, tableName, "OBJECT_TYPE", "Characters(120)", "", false));
for (int i = 0; i < SourceTables.Count; i++)
{
for (int ii = 0; ii < SourceTables[i].ColumnStructure.Count; ii++)
{
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, tableName, SourceTables[i].ColumnStructure));
}
for (int ii = 0; ii < SourceTables[i].RelationshipColumnStructure.Count; ii++)
{
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, tableName, SourceTables[ii].RelationshipColumnStructure));
}
}
List <string> ColumnsList2 = new List <string>();
ColumnsList2.Add(tableName + "_ID");
List <ColumnStructure> JoinedColumns = new List <ColumnStructure>();
HoldResult.AddRange(ER_DDL._ADD_KEY_UNIQUE(_Connect, tableName + "_ID", tableName, ColumnsList2));
for (int i = 0; i < SourceTables.Count; i++)
{
JoinedColumns.AddRange(SourceTables[i].ColumnStructure);
}
for (int i = 0; i < SourceTables.Count; i++)
{
JoinedColumns.AddRange(SourceTables[i].RelationshipColumnStructure);
}
HoldResult.AddRange(ER_DDL._ADD_KEY_PRIMARY(_Connect, tableName, tableName, JoinedColumns));
for (int i = 0; i < SourceTables.Count; i++)
{
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, tableName + "_" + i.ToString(), tableName, SourceTables[i]._TableName, SourceTables[i].ColumnStructure, SourceTables[i].RelationshipColumnStructure));
}
for (int i = 0; i < SourceTables.Count; i++)
{
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, tableName + "_A" + i.ToString(), tableName, SourceTables[i]._TableName, SourceTables[i].RelationshipColumnStructure, SourceTables[i].RelationshipColumnStructure));
}
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, tableName, ""));
HoldResult.AddRange(gen.ADD_PROCEDURE_UPDATE(_Connect, tableName, ""));
HoldResult.AddRange(gen.ADD_PROCEDURE_DELETE(_Connect, tableName, ""));
ER_Generate er_gen = new ER_Generate();
HoldResult.AddRange(er_gen.Generate_Update_Procedure_For_Source(_Connect, tableName));
//HoldResult.AddRange(er_gen.GENERATE_VIEW(_Connect, tableName, "DICTIONARY", "ALL"));
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---End Scaffold " + tableName + "",
_EndTime = DateTime.Now
});
return(HoldResult);
}
