/**
 * SQLGraph
 *
 * <p>A simple SQL parser to produce GraphViz input for an ERDish diagram</p>
 * <p>You'll need <a href="http://www.research.att.com/sw/tools/graphviz/">GraphViz</a> to make any use of it<p>
 *
 *	@author: 	Alan Yates <alany@alanyates.com>
 *	@version	$Id: SQLGraph.java,v 1.2 2002/08/09 16:46:54 alany Exp $
 */

import java.util.*;

public class SQLGraph {
	private SQLLex lex;
	private Hashtable tables;
	private Hashtable relations;

	public SQLGraph(String filename) throws Exception {
		this.lex = new SQLLex(filename);
		this.tables = new Hashtable();
		this.relations = new Hashtable();
		this.parse();
		this.lex.close();
	}

	public void parse() throws Exception {
		while(find(Lexeme.ATOM, "create") != null) {
			create();
		}
	}

	private void create() throws Exception {
		// make sure it is a table create DDL
		Lexeme l = find(Lexeme.ATOM);
		if(l == null || !l.value.equalsIgnoreCase("table")) return;

		// table name
		l = expect(Lexeme.ATOM);
		Table t = new Table(l.value);
		this.tables.put(t.getName(), t);

		// column definition list
		l = expect(Lexeme.LPARA);
		while(column(t));
		expect(Lexeme.RPARA);

		expect(Lexeme.SEMI);
	}

	private boolean column(Table t) throws Exception {
		int paraCount = 0;

		Lexeme name = expect(Lexeme.ATOM);
		Lexeme type = this.lex.pop();
		if(type == null) die(type, "EOF expecting type definition");
		if(type.type == Lexeme.ATOM) {
			t.addField(name.value, type.value);
		} else {
			this.lex.push(type);
		}

		Lexeme l = null;
		while((l = this.lex.pop()) != null && paraCount > -1) {
			if(l.type == Lexeme.COMMA && paraCount == 0) {
				return true;
			} else if(l.type == Lexeme.LPARA) {
				paraCount++;
			} else if(l.type == Lexeme.RPARA) {
				if(paraCount == 0) {
					this.lex.push(l);
					return false;
				}
				paraCount--;
			} else if(l.type == Lexeme.ATOM && l.value.equalsIgnoreCase("references")) {
				relation(t.getName(), name.value);
			}
		}
		return true;
	}

	private void relation(String tableA, String columnA) throws Exception {
		Lexeme tableB = expect(Lexeme.ATOM);
		expect(Lexeme.LPARA);
		Lexeme columnB = expect(Lexeme.ATOM);
		expect(Lexeme.RPARA);
		Relation r = new Relation(tableA, columnA, tableB.value, columnB.value);
		this.relations.put(r,r);
	}

	private Lexeme find(int type) {
		Lexeme l = null;
		while((l = this.lex.pop()) != null) {
			if(l.type == type) return l;
		}
		return null;
	}

	private Lexeme find(int type, String value) {
		Lexeme l = null;
		while((l = this.lex.pop()) != null) {
			if(l.type == type && l.value.equalsIgnoreCase(value)) return l;
		}
		return null;
	}

	private Lexeme expect(int type) throws Exception {
		Lexeme l = this.lex.pop();
		if(l == null) die(l, "EOF expecting " + Lexeme.types[type]);
		if(l.type != type) die(l, "expected " + Lexeme.types[type]);
		return l;
	}

	private Lexeme expect(int type, String value) throws Exception {
		Lexeme l = expect(type);
		if(!l.value.equalsIgnoreCase(value)) die(l, "expected " + value);
		return l;
	}

	private void die(Lexeme l, String message) throws Exception {
		if(l == null) throw new Exception(message);
		else throw new Exception("line " + l.line + ": " + message);
	}

	public void dump() {
		System.out.println("digraph g {\n");
//		System.out.println("graph [ rankdir = LR, ratio = fill, size = \"8,11\", center = true, concentrate = true ];\n");
		System.out.println("graph [ rankdir = LR, center = true, concentrate = true, fontsize = 8 ];\n");
		Enumeration e = this.tables.elements();
		while(e.hasMoreElements()) {
			Table t = (Table) e.nextElement();
			System.out.print(t.toString());
		}
		e = this.relations.elements();
		while(e.hasMoreElements()) {
			Relation r = (Relation) e.nextElement();
			System.out.print(r.toString());
		}
		System.out.println("}\n");
	}

	public static void main(String args[]) throws Throwable {
		if(args.length < 1) {
			System.err.println("usage: sqlgraph <sqlfile>");
			return;
		}
		SQLGraph g = new SQLGraph(args[0]);
		g.dump();
	}
}

class Table {
	private String name;

	private Vector fields;
	private Vector types;

	public Table(String name) {
		this.name = name;
		this.fields = new Vector();
		this.types = new Vector();
	}

	public String getName() {
		return this.name;
	}

	public void addField(String name, String type) {
		this.fields.addElement(name);
		this.types.addElement(type);
	}

	public String toString() {
		StringBuffer sb = new StringBuffer();
		sb.append('"');
		sb.append(this.name);
		sb.append('"');
		sb.append(" [\n\tshape = \"record\"\n");
		sb.append("\tlabel = \"");
		sb.append(this.name);
		sb.append("||");
		for(int i = 0; i < fields.size(); i++) {
			sb.append("<");
			sb.append((String)this.fields.elementAt(i));
			sb.append("> ");
			sb.append((String)this.fields.elementAt(i));
			sb.append(" (");
			sb.append((String)this.types.elementAt(i));
			sb.append(")");
			if(i + 1 < fields.size()) sb.append("|");
		}
		sb.append("\"\n];\n");
		return sb.toString();
	}
}

class Relation {
	private String tableA;
	private String columnA;
	private String tableB;
	private String columnB;
	
	public Relation(String tableA, String columnA, String tableB, String columnB) {
		this.tableA = tableA;
		this.columnA = columnA;
		this.tableB = tableB;
		this.columnB = columnB;
	}
	
	public String toString() {
		return "\"" + this.tableA + "\":" + this.columnA + " -> \"" + this.tableB + "\":" + columnB + ";\n";
	}
}
