In a previous post, I introduced DataGraph, a graph of the tables and relations available in TestFu. In this post, I present DataJoinGraph, the graph of a table join in a query:

• Vertx: the DataTable and alias. Since a table can be referenced by multiple alias, the alias is used as the unique identifier of the vertex. Ex: User, "U"
• Edge: the DataRelation and the type of join (Inner, Outer, etc...)

For the simple join situation (when two tables are simply linked), the DataJoinGraph uses the DataGraph of the database to resolve which DataRelation to use. (In the future, one could think about finding the minimum weight path and creating the join). For the moment, the DataJoinGraph can be used to create an ordering of joins, compatible with T-SQL. Let see this with some example

Creating the graph

DataJoinGraph lives in TestFu.Graph and takes a DataGraph as argument:

DataSet ds = ...;
DataJointGraph joinGraph = new DataJoinGraph(DataGraph.CreateGraph(ds));

Populating the graph

At first, you add the tables involved in the join with their alias (Note that aliases must be unique):

DataTableJoinVertex users = joinGraph.AddVertex(ds.Tables["Users"],"U");
DataTableJoinVertex orders = joinGraph.AddVertex(ds.Tables["Orders"],"O");

Afterwards, you add the different joins between the tables:

joinGraph.AddEdge(users,orders);

Since there is only one DataRelation linking users and orders, the DataJoinGraph can find the DataRelation to use. Moreover, by default we have use a Inner join but this can be modified by providing the proper parameter:

joinGraph.AddEdge(users,orders,JoinType.Outer);

This procedure is continued for the joins of the query.

Create an ordering

The DataTableJoinSortAlgorithm can be used to order the joins to create a SQL query:

DataTableJoinSortAlgorithm algo = new DataTableJoinSortAlgorithm(joinGraph);
algo.Compute();

The StartVertex property returns the first DataTableJoinVertex of the join, and the Joins property returns an ordered collection of DataRelationJoinEdge. For instance, the following method can be used to display the joins in SQL:

protected void ShowJoins()
{
    DataTableJoinVertex v = this.algo.StartVertex;
    Console.WriteLine("{0} AS {1}",v.Table.TableName, v.Alias);

    foreach (DataRelationJoinEdge edge in algo.Joins)
    {
        DataTableJoinVertex next = null;
        if (edge.Source==v)
            next = (DataTableJoinVertex)edge.Target;
        else
            next = (DataTableJoinVertex)edge.Source;
        Console.WriteLine("{0} JOIN {1} AS {2} ON ",
            edge.JoinType.ToString().ToUpper(),
            next.Table.TableName,
            next.Alias
            );

        bool needAnd=false;
        for (int i = 0; i < edge.Relation.ParentColumns.Length; ++i)
        {
            if (needAnd)
                Console.Write(" AND");
            else
                needAnd=true;
            Console.Write(" {0}.{1} = {2}.{3}",
                edge.Source.Alias,
                edge.Relation.ParentColumns[i].ColumnName,
                edge.Target.Alias,
                edge.Relation.ChildColumns[i].ColumnName
                );
        }
        Console.WriteLine();
        v = next;
    }
}

Some examples

Here are a few examples of use and their corresponding output using the ShowJoins method.

• [C#]
  this.graph.AddVertex(this.dataSource.Users, "U");

  [Output]
  Users AS U

• [C#]
  DataTableJoinVertex users = this.graph.AddVertex(this.dataSource.Users, "U");
  DataTableJoinVertex orders = this.graph.AddVertex(this.dataSource.Orders, "O");
  DataRelationJoinEdge uo = this.graph.AddEdge(users, orders, JoinType.Inner);

  [Output]
  Users AS U
  INNER JOIN Orders AS O ON 
   U.UserID = O.UserID

• [C#]
  DataTableJoinVertex users = this.graph.AddVertex(this.dataSource.Users, "U");
  DataTableJoinVertex orders = this.graph.AddVertex(this.dataSource.Orders, "O");
  DataTableJoinVertex products = this.graph.AddVertex(this.dataSource.Products, "P");
  DataTableJoinVertex orderProducts = this.graph.AddVertex(this.dataSource.OrderProducts, "OP");
  DataRelationJoinEdge pop = this.graph.AddEdge(products, orderProducts, JoinType.Inner);
  DataRelationJoinEdge uo = this.graph.AddEdge(users, orders, JoinType.Inner);
  DataRelationJoinEdge oop = this.graph.AddEdge(orders, orderProducts, JoinType.Inner);
  

  [Output]
  Orders AS O
  INNER JOIN OrderProducts AS OP ON 
   O.OrderID = OP.OrderID
  INNER JOIN Products AS P ON 
   P.ProductID = OP.ProductID
  INNER JOIN Users AS U ON 
   U.UserID = O.UserID