This is the first episode of an article serie on graphs and databases. The menu of today will be

1. how to extract the structure of a database,
2. create a graph representation using QuickGraph
3. draw it using NGraphviz

To make things user friendly, I will use the PropertyGrid to set up things.

Extracting the database schema

At first sight this seemed to be a tedious and boring task but hopefully a light poped up on the back of my head saying "you have already seen that in CodeSmith". In fact CodeSmith comes with an assembly, SchemaExplorer, whose purpose is to extract database schema. Even better, the main class, DatabaseSchema, comes with a custom type editor (DatabaseSchemaTypeEditor) so that integration in the PropertyGrid is straightforward. 

public class DataGraphProperties
{
    private DatabaseSchema schema = null;
    [Category("Data")]
    [TypeConverter(typeof(DatabaseSchemaTypeConverter))]
    public DatabaseSchema Schema
    {
        get
        {
            return this.schema;
        }
        set
        {
            this.schema = value;
        }
    }
}

In the PropertyGrid, the Schema property will let the user to select a data source.

Database and graphs

It is straightforward to see that a database is a graph where the tables are the vertex and the foreign keys are the edges. The DatabaseSchema class contains the collection of tables (TableSchema instances), each table containing a collection of foreign keys (TableKeySchema instance). So we have all we need to populate the graph.

Custom Vertex and Edges

The first step for creating a representation of the database as a QuickGraph graph is to create the custom vertex (that implements IVertex) and edge classes (that implement IEdge). This task is straigtforward by using two default classes, Vertex and Edge, available in the QuickGraph assembly. This is illustrated for TableSchemaVertex:

public class TableSchemaVertex : Vertex
{
    private TableSchema table = null;
    public TableSchemaVertex(int id)
    :base(id)
    {}

    public TableSchema Table
    {
        get
        {
            if (this.table==null)
                throw new InvalidOperationException("table not initialized");
            return this.table;
        }
        set
        {
            this.table = value;
        }
    }
}

The TableSchemaVertex instance are to be created by a vertex provider:

public class TableSchemaVertexProvider : TypedVertexProvider
{
    public TableSchemaVertexProvider()
    :base(typeof(TableSchemaVertex))
    {}
}

The same thing is done again for the edges, which is called TableKeySchemaEdge.

Custom Graph

The custom graph is generated using the CodeSmith template AdjacencyGraph.cst. The class is called DatabaseSchemaGraph.

Populating the graph

Once the data structure is ready, populating the graph with the tables and the keys is straightforward:

DatabaseSchema schema = ...;
DatabaseSchemaGraph graph = ...;
// add tables;
foreach(TableSchema table in schema.Tables)
{
    graph.AddVertex(table);
}
// foreach table, add all relations (out-edges)
foreach(TableSchema table in schema.Tables)
{
    foreach(TableKeySchema key in table.ForeignKeys)
    {
        graph.AddEdge(key);
    }
}

That's it :)

Let's do some drawing

Now that we have a graph of the database, the Graphviz "machinery" can be used to output a number of different drawings (refer to this post for a detailled tutorial on using Graphviz). Bundled that with the PropertyGrid and we get a nice and simple database grapher. I have applied DbGrapher on the database that MbUnit uses to store test results:

Next episode

In the next episode, we will see how to improve the (poor) quality of the drawing and how to detect cascade cycles (on delete cycles etc...).