DDL Visualizer - Visualize sql script with Netbeans

Creating module project

1. New project: Choose File > New Project (Ctrl-Shift-N). Under Categories, select NetBeans Modules. Under projects, select Module and click Next.
2. In the Name and Location panel, type DDLVisualizer in Project Name. Change the Project Location to any directory on your computer, such as c:\mymodules. Leave the Standalone Module radio button selected.
   
3. In the Basic Module Configuration panel, replace yourorghere in Code Name Base with myorg, so that the whole code name base is org.myorg.ddlvisualizer. Leave the location of the localizing bundle and XML layer, so that they will be stored in a package with the name org/myorg/ddlvisualizer.
   
4. Click finish.

Add module dependencies (you will need them later)

In project tree right click on Libraries Node > Add module dependency...

1. Import Visual Library API by typing visual
2. Import Generic Languages Framework by typing languages

Create language support

1. New file (Ctrl-N). Under Categories, select Module Development. Under file types, select Language Support and click Next.
2. In the Mime Type and extensions panel, type text/x-fbsql and in the extensions type fbsql. Our new file will have fbsql extension.
   
3. Click finish.

Project tree should look like this:

I already created simple ddl parser for table creation (it's Firebird database syntax). Overwrite content of language.nbs file with following code:

# DDL Visualizer 
# created by Damir Tesanovic (tdamir.blogspot.com) 

TOKEN:keyword:("create" | "table" | "constraint" | "unique" | "primary" 
                | "key" | "foreign" | "references" 
                | "smallint" | "integer" | "float" | "double" | "precision" 
                | "decimal" | "numeric" | "date" | "time" | "timestamp" 
                | "char" | "varchar" | "blob") 

TOKEN:identifier:( ["a"-"z" "A"-"Z" "_"] ["a"-"z" "A"-"Z" "0"-"9" "_"]* ("." ["a"-"z" "A"-"Z" "_"] ["a"-"z" "A"-"Z" "0"-"9" "_"]*)? ) 
TOKEN:identifier:( "\"" [^ "\""]* "\"" ) 

TOKEN:operator:("<=" | "<>" | "<" | "=" | ">=" | ">" | "||" | "-" | "," | ";" | ":" | "/" | "." | "(" | ")" | "[" | "]" | "*" | "+") 

TOKEN:whitespace:( [" " "\t" "\n" "\r"]+ ) 
TOKEN:number:(["0"-"9"]+) 

# parser should ignore whitespaces 
SKIP:whitespace 

# definition of grammar 
S = (CreateTable)*; 

CreateTable = "create" "table" TableName TableDef; 

TableDef = "(" ColumnDef ("," (ColumnDef | TableConstraint))* ")"; 

ColumnDef = ColumnName DataType; 

TableConstraint = ["constraint" ConstraintName] 
                    (("unique" | "primary" "key") "(" ColumnName ("," ColumnName)* ")" | ForeignKeyClause) 
                    ; 

ForeignKeyClause = "foreign" "key" "(" ColumnName ("," ColumnName)* ")" ReferencesClause; 

ConstraintName = <identifier>; 
TableName = <identifier>; 
ColumnName = <identifier>; 
Integer = <number> ; 

ReferencesClause = "references" TableName ["(" ColumnName ("," ColumnName)* ")"]; 

DataType = "smallint" | "integer" | "float" | "double" "precision" 
            | ( "decimal" | "numeric" ) [ "(" Integer [ "," Integer ] ")" ] 
            | "date" | "time" | "timestamp" 
            | ( "char" | "varchar" ) [ "(" Integer ")" ] 
            | "blob" 
            ; 

# error highlighting 
MARK:ERROR: { 
type:"Error"; 
message:"Syntax error."; 
} 

MARK:error: { 
type:"Error"; 
message:"Unexpected character."; 
} 

Create action for displaying diagram:

1. New file (Ctrl-N). Under Categories, select Module Development. Under file types, select Action and click Next.
2. Check conditionally enabled and choose EditorCookie for cookie class
   
3. GUI registration:
   • Category: Tools
   • Uncheck Global Menu Item
   • Check File Type Context Menu and choose Languages for content type, change position to Properties-HERE
   • Check Editor Context Menu and choose text/x-sql for content type (we will modify it in next section)
   
4. Name, Icon, and Location:
   • Class Name: ShowDiagram
   • Display Name: Show Diagram
   • Package Name: org.myorg.ddlvisualizer
   
5. Click finish

Replace section Editors in layer.xml with this:

<folder name="Editors"> 
 <folder name="text"> 
  <folder name="x-fbsql"> 
   <file name="language.nbs" url="language.nbs"/> 
   <folder name="Popup"> 
    <file name="org-myorg-ddlvisualizer-ShowDiagram.shadow"> 
     <attr name="originalFile" stringvalue="Actions/Tools/org-myorg-ddlvisualizer-ShowDiagram.instance"/> 
     <attr name="position" intvalue="700"/> 
    </file> 
   </folder> 
  </folder> 
 </folder> 
</folder>
     

We will add functionality to created action later.

Creating scene for diagram

1. New file (Ctrl-N). Under Categories, select Java. Under file types, select Java Class and click Next.
2. Class Name: DDLVisualScene, Package: org.myorg.ddlvisualizer
3. Click finish.

Antoni Epple created excellent tutorial about creating Visual Database Explorer from database metadata using Netbeans. You can find it here. I modified his DBGraphScene class to suite my needs.
Replace text in file with following:

package org.myorg.ddlvisualizer;

import java.awt.Image;
import java.awt.Point;
import java.util.ArrayList;
import java.util.Iterator;
import org.netbeans.api.languages.ASTNode;
import org.netbeans.api.visual.action.ActionFactory;
import org.netbeans.api.visual.vmd.VMDGraphScene;
import org.netbeans.api.visual.vmd.VMDNodeWidget;
import org.netbeans.api.visual.vmd.VMDPinWidget;

/** 
 * 
 * @author Damir Tesanovic (based on Anton Epple's DBGraphScene) 
 */
public class DDLVisualScene extends VMDGraphScene {

    private static int edgeID = 1;

    public DDLVisualScene() {
        this.getActions().addAction(ActionFactory.createZoomAction());
        this.getActions().addAction(ActionFactory.createPanAction());

    }

    public void setASTNode(ASTNode node) {
        for (ASTNode table : getNodes(node, "CreateTable", false)) {
            String tableName = table.getNode("TableName").getTokenTypeIdentifier("identifier");
            createNode(this, (int) (Math.random() * 800), (int) (Math.random() * 800), tableName, "Table", null);

            for (ASTNode col : getNodes(table, "ColumnDef", true)) {
                String columnName = col.getNode("ColumnName").getTokenTypeIdentifier("identifier");
                createPin(this, tableName, tableName + ":" + columnName, columnName, columnName);
            }

            for (ASTNode foreignKeyClause : getNodes(table, "ForeignKeyClause", true)) {
                ArrayList<String> cols = new ArrayList<String>();
                for (ASTNode col : getNodes(foreignKeyClause, "ColumnName", false)) {
                    cols.add(col.getTokenTypeIdentifier("identifier"));
                }

                String refTableName = foreignKeyClause.getNode("ReferencesClause").getNode("TableName").getTokenTypeIdentifier("identifier");
                ArrayList<String> refCols = new ArrayList<String>();
                for (ASTNode refCol : getNodes(foreignKeyClause.getNode("ReferencesClause"), "ColumnName", false)) {
                    refCols.add(refCol.getTokenTypeIdentifier("identifier"));
                }

                for (int i = 0; i < cols.size(); i++) {
                    createEdge(this, refTableName + ":" + refCols.get(i), tableName + ":" + cols.get(i));
                }

            }
        }

        this.moveTo(null);

    }

    private ArrayList<ASTNode> getNodes(ASTNode root, String nt, boolean deep) {
        ArrayList<ASTNode> nodes = new ArrayList<ASTNode>();
        fillNodes(root, nodes, nt, deep);
        return nodes;
    }

    private void fillNodes(ASTNode node, ArrayList<ASTNode> out, String nt, boolean deep) {
        Iterator it = node.getChildren().iterator();
        while (it.hasNext()) {
            Object elem = it.next();
            if (elem instanceof ASTNode) {
                if (((ASTNode) elem).getNT().equals(nt)) {
                    out.add((ASTNode) elem);
                }
                if (deep) {
                    fillNodes((ASTNode) elem, out, nt, deep);
                }
            }
        }
    }

    private static String createNode(VMDGraphScene scene, int x, int y, String name, String type, java.util.List<Image> glyphs) {
        String nodeID = name;
        if (!scene.getNodes().contains(nodeID)) {
            VMDNodeWidget widget = (VMDNodeWidget) scene.addNode(nodeID);
            widget.setPreferredLocation(new Point(x, y));
            widget.setNodeProperties(null, name, type, glyphs);
        }
        return nodeID;
    }

    private static void createPin(VMDGraphScene scene, String nodeID, String pinID, String name, String type) {
        if (!scene.getPins().contains(pinID)) {
            ((VMDPinWidget) scene.addPin(nodeID, pinID)).setProperties(name, null);
        }
    }

    private static void createEdge(VMDGraphScene scene, String sourcePinID, String targetPinID) {
        String edgeID = "edge" + DDLVisualScene.edgeID++;
        scene.addEdge(edgeID);
        System.out.println("createEdge " + sourcePinID + "<->" + targetPinID);
        scene.setEdgeSource(edgeID, sourcePinID);
        scene.setEdgeTarget(edgeID, targetPinID);
    }

    private void moveTo(Point point) {
        int index = 0;
        for (String node : getNodes()) {
            getSceneAnimator().animatePreferredLocation(findWidget(node), point != null ? point : new Point(++index * 100, index * 100));
        }
    }
} 

It has setASTNode(ASTNode node) method which goes through AST tree and builds diagram.

Create a Top Component for diagram

1. New file (Ctrl-N). Under Categories, select Module Development. Under file types, select Window Component and click Next.
2. Window Position: editor
3. Leave Open on Application start unchecked
   
4. Class Name Prefix: Visualizer
5. Package: org.myorg.ddlvisualizer
   
6. Click finish

Project tree should look like this:

Add JScrollPane to VisualizerTopComponent by dragging it from palette to panel.
Modify source code:

1. Create private instance of DDLVisualScene
2. Create public getter method for DDLVisualScene instance
3. Add at the end of TopComponent constructor: jScrollPane1.setViewportView(scene.createView());

VisualizerTopComponent.java should look like this after modifications:

private static final String PREFERRED_ID = "VisualizerTopComponent";
private DDLVisualScene scene = new DDLVisualScene();

private VisualizerTopComponent() {
 initComponents();
 setName(NbBundle.getMessage(VisualizerTopComponent.class, "CTL_VisualizerTopComponent"));
 setToolTipText(NbBundle.getMessage(VisualizerTopComponent.class, "HINT_VisualizerTopComponent"));
 // setIcon(Utilities.loadImage(ICON_PATH, true)); 
 jScrollPane1.setViewportView(scene.createView());
}

Modify ShowDiagram.java

Thanks to Generic Languages Framework we can easily get AST tree from document. We will pass it to scene and then show VisualizerTopComponent. Replace performAction method with following code:

protected void performAction(Node[] activatedNodes) {
 try {
  EditorCookie editorCookie = activatedNodes[0].getLookup().lookup(EditorCookie.class);
  Document doc = editorCookie.getDocument();
  ParserManager pm = ParserManager.get(doc);

  VisualizerTopComponent win = VisualizerTopComponent.findInstance();
  win.getScene().setASTNode(pm.getAST());
  win.open();
  win.requestActive();
 } catch (ParseException ex) {
  Exceptions.printStackTrace(ex);
 }
}

sample.fbsql

Here is sample file for testing.

create table EMPLOYEE ( 
    EMP_NO integer, 
    FIRST_NAME varchar, 
    LAST_NAME varchar 
) 

create table PROJECT ( 
    PROJ_ID integer, 
    PROJ_NAME varchar, 
    PROJ_DESC varchar, 
    TEAM_LEADER integer, 
    
    constraint fkey1 foreign key (TEAM_LEADER) references EMPLOYEE (EMP_NO) 
) 

create table EMPLOYEE_PROJECT ( 
    EMP_NO integer, 
    PROJ_ID integer, 

    constraint fkey1 foreign key (EMP_NO) references EMPLOYEE (EMP_NO), 
    constraint fkey2 foreign key (PROJ_ID) references PROJECT (PROJ_ID) 
) 
 

Result