WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
- Let's Begin . . .
- Flow of the tool
- Step 1: Database Connectivity
- Step 2: Selecting the Needed Data
- Step 3: Selecting Relationships
- Step 4: Visualization and Code Generation
- The CVisualization Class
- Almost Done . . .
When handling a project with hundreds of tables, it takes some time to understand the flows within the database. This is especially so if there is no document available. A possible scenario is legacy code up for maintenance or migration. After going through just a few tables, it becomes too cumbersome to maintain the relationships in your mind. This article aims to create a simple tool for visualizing database tables and relations and a database map to refer to.
The main aim was to generate the documentation automatically for the database relationships as and when required. The reason for using the WinGraphViz library was to make sure that the database relationships did not have much crossing and that the end result looked clutter free and presentable.
A good visualization algorithm allows the user to see the pattern that may be difficult or impossible to see when the same data is presented in raw form. Such a method is known as a "Planar Graph." There are three main points to note when developing a visualization tool. They are:
- Graph layout
- Interaction with the user and
You will use the WinGraphViz library to solve all the above mentioned problems. WinGraphviz is a free software based on the Graphviz project by AT&T. The graph layout is taken care by the algorithm and you'll generate the output in SVG for handling the Interaction and Presentation parts.
In graph theory, a planar graph is a graph that can be drawn so that no edges intersect. For those interested in the details, please download this file. A graph consists of two components: vertices and edges. Vertices are simply labels such as numbers, and edges are the connections between the vertices. Edges also can have direction that creates a directed graph, or Digraph for short. The WinGraphViz library attempts to create such a Directed Planar Graph. Because it is available as a COM library, you'll use it to create your database visualization tool. For a sample of what graph optimization can do, check out the following diagram.
You will use a similar graph drawing algorithm to make your output diagram clutter free and neat.
SVG is a language for describing two-dimensional graphics and graphical applications in XML. SVG files are compact and provide high-quality graphics on the Web, in print, and on resource-limited handeld devices. In addition, SVG supports scripting and animation, so is ideal for interactive graphics. The Adobe SVG Viewer is free and is available as an ActiveX control that enables you to see the output in a Web browser or a Windows application. SVG output is highly recommended due to its zooming capabilities. Because SVG is vector based, the clarity of the output would remain constant no matter whether you zoom in or zoom out.
Hints about using the Adobe SVG Viewer:
- Use the Ctrl key to zoom in.
- Use the Shift + Ctrl keys to zoom out.
- Use the Alt key to pan.
To implement this part, you will use the "SQLSrvEnumerator class" available here. On selection of the server combo box, the list of servers available on the network is enumerated and displayed. The user credentials are obtained to log in to the selected server. The list of databases is enumerated and is automatically populated in a combo box; you will select one from the list. The database connectivity is handled using ODBC here. The remaining operations are done using ADO. Although the connection can also be done using ADO, I was plainly too lazy to change the code after deciding to use ADO instead of ODBC.
In this simple operation, you will select the needed columns. When the page is loaded, the "Tables" combo is automatically populated based on selection of the database in the previous step. On selecting a table from the list, the "Columns" combo box is populated with all the columns in the selected table. Selecting the "All" value from the combo selects all the columns in the table. You can move the column names up or down based on how you want your diagram to look. This option is purely for cosmetic purposes.
This operation is crucial to how your diagram is generated. You need to select the relationships between tables in this step. Select the tables first and then select the columns by which they are actually related to each other. For example, Table1, PK; and Table2, FK. The relations entered here are used to generate your visualization.
You've now reached the final step. You can enter comments if needed. The "Generate Query" button does exactly what it says. It generates a SQL query based on your selection of tables, columns, and relationships. It generates only a simple query now. In the very first upgrade of this article, I promise to do something better. It was added as an afterthought because this article only tries to focus on the visualizing part. The "Image Type" combo supports various image formats such as JPG, SVG, PNG, GIF, and EMF. As per your selection, the output diagram is generated. The "Show Relationship Labels" check box displays the selected relationships in the diagram.
The CVisualization class encapsulates the functionalities of the Graph drawing. The available methods are:
void AddComment(CString strComment)
|The AddComment function is optional and is used to add a comment in the generated diagram.|
void AddTable(CString strTableName, CStringArray &strArrayFields)
|The AddTable function takes the Table name as the first paramater and a String array of field names as the second parameter. It is used to add the table to the diagram.|
void AddLink(CString strNodeOne, CString strNodeTwo, CString strLabel, CString strColor="black")
|The AddLink function takes the Table names as the first two paramaters, a String representing the relationship as the third parameter, and the color of the link as the fourth parameter.|
BOOL SaveToFile(int nImageFormat, BOOL bOpenFile)
|The SaveToFile function takes in the Image format as the first paramater and a boolean variable to determine if the generated file has to be opened for preview or not.The available options for image format are:
- About WinGraphViz
- Download WinGraphViz
- A Technique for Drawing Directed Graphs
- The Boost Graph Library (BGL)
- Graphviz - Graph Visualization Software
- The dot guide
- Drawing graphs with Graphviz
- Automatic Graph Drawing Algorithms
- Download Adobe SVG Viewer
- SQL Server and Database Enumerator by Santhosh Rao
- A VisualStudio-like ListBox class by Stefano Passiglia
The CVisualization class available with the source code encapsulates all the drawing functions and output functionality needed for your database visualization tool. Please install the Wingraphviz library to execute your tool properly.
This tool can only handle simple relations. It cannot handle nested Select statements. The method used to select the tables and the columns is also quite crude. The visualization generated only links the tables and not the actual keys.
This tool has only been tested with SQL Server 2000 and has not been tested with other databases.
- Handling nested Select statements
- Handling complex relationships
- More visual representation of the table and column selection
- Code generation (Client and Server) based on the selected relationships
- Complex SQL query generation
You can find the latest copy of this tool at My Web site, too.
This tool is not meant to replace the SQL Server Database Diagram Designer. It is only complementary. In situations where the relationships are not maintained properly in the Database and when a better visual representation is needed, this tool can be used.