//TODO: Code Review Issue 11/05/05: Missing XML summary /// <summary> /// Checks for equality between two TableJoin objects by comparing the /// tables and fields within each TableJoin for equality. They are considered /// equal if both TableJoins have the same FromFields and ToFields. /// </summary> /// <param name="obj">The TableJoin this object is being compared to.</param> /// <returns>Whether the two objects are equal.</returns> public override bool Equals(object obj) { /* If the obj parameter is a TableJoin we must be specific in comparing * the two objects: They can be considered equal if both from{Table|Field} * fields are the same as their equivalent to{Table|Field} fields. */ if (obj is TableJoin) { //TODO: Code Review Issue 11/05/05: use Pascal casing for method variable. // Done 24/05/2005 TableJoin TableJoinComparator = (TableJoin)obj; //TODO: Code Review Issue 11/05/05: Do not use abreviation in method variable. // Done 24/05/2005 string FromInfoTableJoin1 = this._fromTable + this._fromField; string ToInfoTableJoin1 = this._toTable + this._toField; string FromInfoTableJoin2 = TableJoinComparator._fromTable + TableJoinComparator._fromField; string ToInfoTableJoin2 = TableJoinComparator._toTable + TableJoinComparator._toField; if ((FromInfoTableJoin1 == FromInfoTableJoin2 && ToInfoTableJoin1 == ToInfoTableJoin2) || (FromInfoTableJoin1 == ToInfoTableJoin2 && ToInfoTableJoin1 == FromInfoTableJoin2)) { return(true); } else { return(false); } } else // default { return(base.Equals(obj)); } }
/// <summary> /// This method has two objectives: /// 1) Ordering the TableJoins so that they are constructed in a valid order in /// the query. /// 2) Ensuring that none of the tables being joined on clash with the table /// being joined from (i.e. ... FROM t INNER JOIN t ... cannot happen) /// </summary> private static void OrderTableJoins(List <TableJoin> tableJoinList) { // List of tables already present in a join List <string> UsedTablesList = new List <string>(); // Ordered list of table joins List <TableJoin> OrderedTableJoinList = new List <TableJoin>(); // Each join has an index that determines its order within the query. // This keep count of the current order we are up to. int OrderIndex = 1; // The number of times we have looped around the do loop int LoopCount = 0; //TODO: Code Review Issue 11/05/05: Prefix 'Is' to boolean variable names. // Done 24/05/2005 // Determines if a table join that can be ordered has been found. bool IsTableJoinFound = false; if (tableJoinList.Count > 0) { // The first table in the list features the table that is being // joined from, so order this join first and put the tables // within this join into the used list. UsedTablesList.Add(((TableJoin)tableJoinList[0]).FromTable); UsedTablesList.Add(((TableJoin)tableJoinList[0]).ToTable); ((TableJoin)tableJoinList[0]).Index = 0; OrderedTableJoinList.Add(tableJoinList[0]); /* We must order the rest of the joins in the tableJoinList: * Search for a tablejoin with no order asssigned to it; * When found check if it contains a table that features in the used list; * If so, verify that the TO table is NOT in the used list (if it * is then you can swap the 'from' information and the 'to' information around * because the 'from' table is guaranteed not to be used yet); * Add the table from this join that is not yet in the used list into * the used list; * Assign an order to the TableJoin; */ do { // Reset found indicator IsTableJoinFound = false; for (int i = 1; i < tableJoinList.Count; i++) { TableJoin CurrentTableJoin = (TableJoin)tableJoinList[i]; bool FromTableUsed = UsedTablesList.Contains(CurrentTableJoin.FromTable); bool ToTableUsed = UsedTablesList.Contains(CurrentTableJoin.ToTable); // Verify if a join is found without an order that has at // least one table in used list if (CurrentTableJoin.Index == -1 && (FromTableUsed || ToTableUsed)) { IsTableJoinFound = true; // Verify that only one table from the join has already // been used. if (!(FromTableUsed && ToTableUsed)) { // Verify 'to' table is not in used list if (UsedTablesList.Contains(CurrentTableJoin.ToTable)) { // Swap from and to string TempToSchema = CurrentTableJoin.ToSchema; string TempToTable = CurrentTableJoin.ToTable; string TempToField = CurrentTableJoin.ToField; CurrentTableJoin.ToSchema = CurrentTableJoin.FromSchema; CurrentTableJoin.FromSchema = TempToSchema; CurrentTableJoin.ToTable = CurrentTableJoin.FromTable; CurrentTableJoin.FromTable = TempToTable; CurrentTableJoin.ToField = CurrentTableJoin.FromField; CurrentTableJoin.FromField = TempToField; } // The table should now be the table to be assigned an order and added CurrentTableJoin.Index = OrderIndex; UsedTablesList.Add(CurrentTableJoin.ToTable); // The join should be added to the ordered list of joins OrderedTableJoinList.Add(CurrentTableJoin); OrderIndex++; } else { // Mark this join as unneccessary CurrentTableJoin.Index = -2; } } } //TODO: Code Review Issue 11/05/05: Replace the hard-coded message text by a Resource Helper Text. // This do-loop should only loop around as many times as there are // joins in the tableJoinList AT MOST. If we have looped around more // times than that, we have entered an infinite loop. if (LoopCount > tableJoinList.Count) { throw new Exception(Resource.GetString("RES_EXCEPTION_QUERYBUILDER_INFINITELOOP")); } LoopCount++; }while (IsTableJoinFound); // Now remove all unnecessary joins for (int i = 0; i < OrderedTableJoinList.Count; i++) { if (((TableJoin)OrderedTableJoinList[i]).Index == -2) { OrderedTableJoinList.RemoveAt(i); } } // The member list of table joins can now be assigned the ordered joins tableJoinList.Clear(); tableJoinList.AddRange(OrderedTableJoinList); } }
/// <summary> /// Constructs a set of TableJoin objects representing the joins necessary /// for joining the tables in tableList. The joins are returned in a valid /// order within the list. /// </summary> /// <param name="tableList">A list of DataTable objects to be joined.</param> /// <param name="joinMethod">Join Method (inner or outer)</param> /// <returns>A list of TableJoin objects representing the joins for the supplied tables. /// </returns> public static List <TableJoin> ConstructTableJoins(List <DataTable> tableList, List <ReportDataTableJoin> joinList, DataModel currentDataModel) { // Temporary storage variables: // List of table joins for the query. List <TableJoin> tableJoinList = new List <TableJoin>(); // Holds a DataTableRelationship DataTableRelationship Dtr; // A DataTableJoin DataTableJoin Dtj; // A list of DataTableRelationshipJoins List <DataTableRelationshipJoin> DtrjList = new List <DataTableRelationshipJoin>(); // A TableJoin object TableJoin CurrentTableJoin; // j is the outer loop control variable: It is the index of the // current table in the list we are considering when comparing against // the rest of the tables. foreach (DataTable currentTableOuterLoop in tableList) // (int j = 0; j < tableList.Count; j++) { // i is the inner loop control variable: It is the index of // the table being compared against foreach (DataTable currentTableInnerLoop in tableList) //(int i = 0; i < tableList.Count; i++) { // Ensure we don't compare table to itself if (currentTableInnerLoop != currentTableOuterLoop) { DtrjList = currentDataModel.DataTableJoins .SelectMany(x => x.DataTableRelationshipJoins) .Where(y => y.DataTableRelationship.DataTable.Name == currentTableOuterLoop.Name && y.DataTableRelationship.DataTable.SchemaName == currentTableOuterLoop.SchemaName && y.DataTableRelationship.DataTable1.Name == currentTableInnerLoop.Name && y.DataTableRelationship.DataTable1.SchemaName == currentTableInnerLoop.SchemaName).ToList(); // Add all unique RelationshipJoins to list of joins foreach (DataTableRelationshipJoin join in DtrjList) { Dtj = join.DataTableJoin;//DataAccessUtilities.RepositoryLocator<IDataTableJoinRepository>().GetByCode(join.DataTableJoinCode); ReportDataTableJoin AssociatedReportJoin = (from x in joinList where x.DataTableJoinCode == Dtj.Code select x).FirstOrDefault(); // Construct a TableJoin - if this is the first TableJoin // in the tableJoinList, then .Count == 0 is true /*ExceptionLogging try * {*/ string fromSchema = join.DataTableRelationship.DataTable.SchemaName; string toSchema = join.DataTableRelationship.DataTable1.SchemaName; CurrentTableJoin = new TableJoin(fromSchema, toSchema, Dtj.FromTable, Dtj.ToTable, Dtj.FromField, Dtj.ToField, -1, AssociatedReportJoin == null ? Dtj.DefaultJoinType : AssociatedReportJoin.JoinType, Dtj.IsOneToOne); /*ExceptionLogging} * catch (Exception) * { * System.IO.File.WriteAllText("C:\\Releases\\Ucb MI\\MI_Error_Log2.txt", string.Format("Failed getting join info. from: '{0}', to: '{1}'", Dtj.FromTable, Dtj.ToTable)); * throw; * }*/ // Do not add the new TableJoin to the list if it // already exists in there if (!tableJoinList.Contains(CurrentTableJoin)) { tableJoinList.Add(CurrentTableJoin); } } } // if (i != j) } // i loop } // j loop return(tableJoinList); }