diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.java b/src/nl/astraeus/jdbc/web/page/QueryDetail.java index 3fcc7f7..186dc22 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.java +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import javax.servlet.http.HttpServletRequest; @@ -72,6 +73,7 @@ List list; list = new LinkedList(); + SqlFormatter formatter = new SqlFormatter(); if (!entries.isEmpty()) { long total = 0; diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.java b/src/nl/astraeus/jdbc/web/page/QueryDetail.java index 3fcc7f7..186dc22 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.java +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import javax.servlet.http.HttpServletRequest; @@ -72,6 +73,7 @@ List list; list = new LinkedList(); + SqlFormatter formatter = new SqlFormatter(); if (!entries.isEmpty()) { long total = 0; diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.html b/src/nl/astraeus/jdbc/web/page/QueryOverview.html index 04cccc7..9f919e0 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.html +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.html @@ -41,7 +41,7 @@ {q.formattedNano} {q.count} {escape(none)} - 
{q.sql}
+ 
{q.sql}
{/escape} {/each} diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.java b/src/nl/astraeus/jdbc/web/page/QueryDetail.java index 3fcc7f7..186dc22 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.java +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import javax.servlet.http.HttpServletRequest; @@ -72,6 +73,7 @@ List list; list = new LinkedList(); + SqlFormatter formatter = new SqlFormatter(); if (!entries.isEmpty()) { long total = 0; diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.html b/src/nl/astraeus/jdbc/web/page/QueryOverview.html index 04cccc7..9f919e0 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.html +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.html @@ -41,7 +41,7 @@ {q.formattedNano} {q.count} {escape(none)} - 
{q.sql}
+ 
{q.sql}
{/escape} {/each} diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.java b/src/nl/astraeus/jdbc/web/page/QueryOverview.java index 0e27168..f2a1c46 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.java +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.java @@ -1,7 +1,6 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; -import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -24,6 +23,17 @@ boolean sortTotalTime = false; @Override + public Page processGetRequest(String action, String value) { + Page result = this; + + if ("select".equals(action)) { + return new QueryDetail(this, Integer.parseInt(value)); + } + + return result; + } + + @Override public Page processRequest(HttpServletRequest request) { if ("sortTotalCalls".equals(request.getParameter("action"))) { sortTotalCalls = true; diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.java b/src/nl/astraeus/jdbc/web/page/QueryDetail.java index 3fcc7f7..186dc22 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.java +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import javax.servlet.http.HttpServletRequest; @@ -72,6 +73,7 @@ List list; list = new LinkedList(); + SqlFormatter formatter = new SqlFormatter(); if (!entries.isEmpty()) { long total = 0; diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.html b/src/nl/astraeus/jdbc/web/page/QueryOverview.html index 04cccc7..9f919e0 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.html +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.html @@ -41,7 +41,7 @@ {q.formattedNano} {q.count} {escape(none)} - 
{q.sql}
+ 
{q.sql}
{/escape} {/each} diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.java b/src/nl/astraeus/jdbc/web/page/QueryOverview.java index 0e27168..f2a1c46 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.java +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.java @@ -1,7 +1,6 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; -import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -24,6 +23,17 @@ boolean sortTotalTime = false; @Override + public Page processGetRequest(String action, String value) { + Page result = this; + + if ("select".equals(action)) { + return new QueryDetail(this, Integer.parseInt(value)); + } + + return result; + } + + @Override public Page processRequest(HttpServletRequest request) { if ("sortTotalCalls".equals(request.getParameter("action"))) { sortTotalCalls = true; diff --git a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html index 87e6229..d1cc16e 100644 --- a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html +++ b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html @@ -11,7 +11,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.java b/src/nl/astraeus/jdbc/web/page/QueryDetail.java index 3fcc7f7..186dc22 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.java +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import javax.servlet.http.HttpServletRequest; @@ -72,6 +73,7 @@ List list; list = new LinkedList(); + SqlFormatter formatter = new SqlFormatter(); if (!entries.isEmpty()) { long total = 0; diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.html b/src/nl/astraeus/jdbc/web/page/QueryOverview.html index 04cccc7..9f919e0 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.html +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.html @@ -41,7 +41,7 @@ {q.formattedNano} {q.count} {escape(none)} - 
{q.sql}
+ 
{q.sql}
{/escape} {/each} diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.java b/src/nl/astraeus/jdbc/web/page/QueryOverview.java index 0e27168..f2a1c46 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.java +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.java @@ -1,7 +1,6 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; -import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -24,6 +23,17 @@ boolean sortTotalTime = false; @Override + public Page processGetRequest(String action, String value) { + Page result = this; + + if ("select".equals(action)) { + return new QueryDetail(this, Integer.parseInt(value)); + } + + return result; + } + + @Override public Page processRequest(HttpServletRequest request) { if ("sortTotalCalls".equals(request.getParameter("action"))) { sortTotalCalls = true; diff --git a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html index 87e6229..d1cc16e 100644 --- a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html +++ b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html @@ -11,7 +11,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java index db3378c..a176a7f 100644 --- a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java +++ b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java @@ -1,13 +1,13 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; -import nl.astraeus.jdbc.util.Util; +import nl.astraeus.jdbc.SqlFormatter; import javax.servlet.http.HttpServletRequest; -import java.security.KeyStore; -import java.text.DateFormat; -import java.text.SimpleDateFormat; -import java.util.*; +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; /** * User: rnentjes @@ -72,6 +72,8 @@ trace.add(new TraceElement(logEntry.getStackTrace()[index], hl)); } + SqlFormatter formatter = new SqlFormatter(); + result.put("trace", trace); result.put("count", trace.size()); result.put("sql", logEntry.getSql()); diff --git a/src/nl/astraeus/jdbc/Driver.java b/src/nl/astraeus/jdbc/Driver.java index 5a0b9e6..c00a661 100644 --- a/src/nl/astraeus/jdbc/Driver.java +++ b/src/nl/astraeus/jdbc/Driver.java @@ -10,7 +10,6 @@ import java.sql.*; import java.util.Enumeration; import java.util.Properties; -import java.util.Set; /** * User: riennentjes @@ -103,7 +102,7 @@ if (driver != null && !started) { SimpleWebServer server = new SimpleWebServer(18080); - server.addServlet(new JdbcStatisticsServlet(), "/"); + server.addServlet(new JdbcStatisticsServlet(), "/*"); server.addServlet(new ResourceServlet(), "/resources/*"); server.start(); diff --git a/src/nl/astraeus/jdbc/JdbcLogger.java b/src/nl/astraeus/jdbc/JdbcLogger.java index c1bef9e..6b3c992 100644 --- a/src/nl/astraeus/jdbc/JdbcLogger.java +++ b/src/nl/astraeus/jdbc/JdbcLogger.java @@ -105,8 +105,8 @@ } public String getSql() { - if (Settings.get().isFormattedQueries() && QueryType.PREPARED == type) { - return SqlFormatter.getHTMLFormattedSQL(sql); + if (Settings.get().isFormattedQueries()) { + return SqlFormatter.getHTMLFormattedSQL2(sql); } else { return sql; } diff --git a/src/nl/astraeus/jdbc/SqlFormatter.java b/src/nl/astraeus/jdbc/SqlFormatter.java index 44d43b6..aaea650 100644 --- a/src/nl/astraeus/jdbc/SqlFormatter.java +++ b/src/nl/astraeus/jdbc/SqlFormatter.java @@ -3,6 +3,10 @@ import org.slf4j.Logger; import org.slf4j.LoggerFactory; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + /** * User: rnentjes * Date: 4/18/12 @@ -11,6 +15,170 @@ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); + private static SqlTokenizer tokenizer = new SqlTokenizer(); + + private static enum Newline { + PRE, + POST, + PREPOST, + NONE + } + + private static class LayoutMapping { + int preIndent; + int postIndent; + Newline nl; + + private LayoutMapping() { + preIndent = 0; + postIndent = 0; + nl = Newline.NONE; + } + + private LayoutMapping(int preIndent, int postIndent) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + nl = Newline.NONE; + } + + private LayoutMapping(Newline nl) { + preIndent = 0; + postIndent = 0; + + this.nl = nl; + } + + private LayoutMapping(int preIndent, int postIndent, Newline nl) { + this.preIndent = preIndent; + this.postIndent = postIndent; + + this.nl = nl; + } + + public boolean preNewline() { + return nl == Newline.PRE || nl == Newline.PREPOST; + } + + public boolean postNewline() { + return nl == Newline.POST || nl == Newline.PREPOST; + } + + public int getPreIndent() { + return preIndent; + } + + public int getPostIndent() { + return postIndent; + } + } + + private static Map mapping = new HashMap(); + private static LayoutMapping defaultMapping = new LayoutMapping(); + + static { + //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); + //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); + mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); + + mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); + + mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); + mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); + + mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); + mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); + mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); + mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); + + mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); + + mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); + } + + private Map cache = new HashMap(); + + public static String getHTMLFormattedSQL2(String sql) { + String formatted = null; //cache.get(sql.hashCode()); + + if (formatted == null) { + StringBuilder result = new StringBuilder(); + + List tokens = tokenizer.parse(sql); + int indent = 0; + + for (SqlToken token : tokens) { + LayoutMapping lm = mapping.get(token.getType()); + if (lm == null) { + lm = defaultMapping; + } + + indent += lm.getPreIndent(); + + if (indent < 0) { + indent = 0; + } + + if (lm.preNewline()) { + result.append("\n"); + indentation(indent, result); + } + + if (lm != defaultMapping) { + result.append(""); + } + result.append(token.getText()); + if (lm != defaultMapping) { + result.append(""); + } + result.append(" "); + + indent += lm.getPostIndent(); + + if (lm.postNewline()) { + result.append("\n"); + indentation(indent, result); + } + } + + formatted = result.toString(); + //tcache.put(sql.hashCode(), formatted); + } + + return formatted; + } + + public static void indentation(int nr, StringBuilder result) { + while (nr-- > 0) { + result.append(" "); + } + } + public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; @@ -69,4 +237,10 @@ return ""+text+""; } + public static void main(String [] args) { + SqlFormatter f = new SqlFormatter(); + + System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); + } + } diff --git a/src/nl/astraeus/jdbc/SqlToken.java b/src/nl/astraeus/jdbc/SqlToken.java new file mode 100644 index 0000000..05ac9e5 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlToken.java @@ -0,0 +1,25 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlToken { + + private SqlTokenType type; + private String text; + + public SqlToken(SqlTokenType type, String text) { + this.type = type; + this.text = text; + } + + public SqlTokenType getType() { + return type; + } + + public String getText() { + return text; + } +} diff --git a/src/nl/astraeus/jdbc/SqlTokenType.java b/src/nl/astraeus/jdbc/SqlTokenType.java new file mode 100644 index 0000000..3a5738e --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenType.java @@ -0,0 +1,23 @@ +package nl.astraeus.jdbc; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public enum SqlTokenType { + UNKNOWN, EMPTY, COMMA, + SELECT, FROM, WHERE, ORDER_BY, GROUP_BY, HAVING, + AND, OR, + JOIN, + NOT, NULL, IS_NULL, + AS, ON, + EXISTS, + EXPRESSION_OPEN, EXPRESSION_CLOSE, + INSERT_INTO, VALUES, + UPDATE, SET, + DELETE_FROM, + CREATE_TABLE, + ALTER_TABLE, ADD, + CASE, WHEN, THEN, ELSE, END +} diff --git a/src/nl/astraeus/jdbc/SqlTokenizer.java b/src/nl/astraeus/jdbc/SqlTokenizer.java new file mode 100644 index 0000000..f095184 --- /dev/null +++ b/src/nl/astraeus/jdbc/SqlTokenizer.java @@ -0,0 +1,229 @@ +package nl.astraeus.jdbc; + +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; + +/** + * User: rnentjes + * Date: 4/24/12 + * Time: 9:00 PM + */ +public class SqlTokenizer { + + public static void main(String [] args) { + SqlTokenizer st = new SqlTokenizer(); + + List result = st.parse("select auction0_.id as id3_, auction0_.account_id as account31_3_, " + + "auction0_.auctionEndTime as auctionE2_3_, auction0_.auctionSiteId as auctionS3_3_, " + + "auction0_.auctionStartTime as auctionS4_3_, auction0_.bidCount as bidCount3_, " + + "auction0_.buyItNowPrice as buyItNow6_3_, auction0_.countryCode as countryC7_3_, " + + "auction0_.currencyCode as currency8_3_, auction0_.ebaySite as ebaySite3_, " + + "auction0_.illegal as illegal3_, auction0_.item_id as item32_3_, auction0_.itemURL as itemURL3_, " + + "auction0_.lastStatusChange as lastSta12_3_, auction0_.price as price3_, " + + "auction0_.priceImage_id as priceImage34_3_, auction0_.receivedTime as receive14_3_, " + + "auction0_.screenshot_id as screenshot30_3_, auction0_.seller_id as seller29_3_, " + + "auction0_.shipping as shipping3_, auction0_.site_id as site33_3_, " + + "auction0_.spamValue as spamValue3_, auction0_.startPrice as startPrice3_, " + + "auction0_.status as status3_, auction0_.subTitle as subTitle3_, " + + "auction0_.takeScreenshot as takeScr20_3_, auction0_.takeScreenshotTime as takeScr21_3_, " + + "auction0_.title as title3_, auction0_.veroFailReason as veroFai23_3_, " + + "auction0_.veroPackedID as veroPac24_3_, auction0_.veroReason as veroReason3_, " + + "auction0_.veroStatus as veroStatus3_, auction0_.version as version3_, " + + "auction0_.whiteList as whiteList3_ from Auction auction0_ " + + "where auction0_.site_id=? and auction0_.auctionSiteId=? order by auction0_.id"); + + for (SqlToken token : result) { + System.out.println(token.getType()+"\t->\t"+token.getText()); + } + } + + private Map basicMapping = new HashMap(); + + public SqlTokenizer() { + basicMapping.put("select", SqlTokenType.SELECT); + basicMapping.put("from", SqlTokenType.FROM); + basicMapping.put("where", SqlTokenType.WHERE); + basicMapping.put("having", SqlTokenType.HAVING); + basicMapping.put("update", SqlTokenType.UPDATE); + basicMapping.put("set", SqlTokenType.SET); + + basicMapping.put("as", SqlTokenType.AS); + basicMapping.put("on", SqlTokenType.ON); + + basicMapping.put("and", SqlTokenType.AND); + basicMapping.put("or", SqlTokenType.OR); + basicMapping.put("not", SqlTokenType.NOT); + + basicMapping.put("join", SqlTokenType.NOT); + basicMapping.put("exists", SqlTokenType.EXISTS); + + basicMapping.put("values", SqlTokenType.VALUES); + basicMapping.put("null", SqlTokenType.NULL); + basicMapping.put(",", SqlTokenType.COMMA); + + basicMapping.put("case", SqlTokenType.CASE); + basicMapping.put("when", SqlTokenType.WHEN); + basicMapping.put("then", SqlTokenType.THEN); + basicMapping.put("else", SqlTokenType.ELSE); + basicMapping.put("end", SqlTokenType.END); + } + + public List parse(String sql) { + List result = new LinkedList(); + + String [] pts = sql.split("\\s"); + + List parts = new LinkedList(); + + for (int index = 0; index < pts.length; index++) { + parts.add(pts[index]); + } + + while(!parts.isEmpty()) { + SqlToken token = getToken(parts); + + if (token.getType() != SqlTokenType.EMPTY) { + result.add(token); + } + } + + return result; + } + + private SqlToken getToken(List parts) { + SqlTokenType type = SqlTokenType.UNKNOWN; + String part = null; + + if (parts.get(0).startsWith("'")) { + // find end of tekst + String result = parts.remove(0); + part = result.substring(1); + + while (part.indexOf("'") == -1 || part.startsWith("''")) { + if (part.startsWith("''")) { + part = part.substring(2); + } else { + part = parts.remove(0); + result += " "+part; + } + } + + part = result; + type = SqlTokenType.UNKNOWN; + } else if (parts.get(0).startsWith(",")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = ","; + type = SqlTokenType.COMMA; + } else if (parts.get(0).endsWith(",")) { + part = parts.remove(0); + part = part.substring(0, part.length() -1); + + parts.add(0, ","); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (check(parts, "order", "by")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ORDER_BY; + } else if (check(parts, "insert", "into")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.INSERT_INTO; + } else if (check(parts, "delete", "from")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.DELETE_FROM; + } else if (check(parts, "create", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.CREATE_TABLE; + } else if (check(parts, "alter", "table")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.ALTER_TABLE; + } else if (check(parts, "is", "null")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.IS_NULL; + } else if (check(parts, "left", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "right", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "full", "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "outer", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "inner", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "natural", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "cross", "join")) { + part = parts.remove(0); + part += " " + parts.remove(0); + type = SqlTokenType.JOIN; + } else if (check(parts, "(")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_OPEN; + } else if (check(parts, ")")) { + part = parts.remove(0); + type = SqlTokenType.EXPRESSION_CLOSE; + } else if (parts.get(0).startsWith("(")) { + part = parts.remove(0); + part = part.substring(1); + + parts.add(0, part); + + part = "("; + type = SqlTokenType.EXPRESSION_OPEN; + } else if (parts.get(0).endsWith(")")) { + part = parts.remove(0); + part = part.substring(0, part.length()-1); + + parts.add(0, ")"); + parts.add(0, part); + + part = null; + type = SqlTokenType.EMPTY; + } else if (basicMapping.get(parts.get(0).toLowerCase()) != null) { + part = parts.remove(0); + type = basicMapping.get(part.toLowerCase()); + } else { + part = parts.remove(0); + } + + return new SqlToken(type, part); + } + + private boolean check(List parts, String ... elements) { + boolean result = true; + + for (int index = 0; index < elements.length; index++) { + result = result && ((parts.size() > index) && parts.get(index).trim().equalsIgnoreCase(elements[index])); + } + + return result; + } +} diff --git a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java index e57b403..d00be07 100644 --- a/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java +++ b/src/nl/astraeus/jdbc/example/JdbcStatisticsExample.java @@ -18,7 +18,7 @@ } public JdbcStatisticsExample() throws Exception { - Connection conn = DriverManager.getConnection("jdbc:secstat:jdbc:h2:mem:test", "user", "password"); + Connection conn = DriverManager.getConnection("jdbc:stat:jdbc:h2:mem:test", "user", "password"); Statement statement = null; diff --git a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java index ab6fde5..3c22aca 100644 --- a/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java +++ b/src/nl/astraeus/jdbc/web/JdbcStatisticsServlet.java @@ -14,6 +14,8 @@ import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.io.IOException; +import java.util.HashMap; +import java.util.Map; /** * User: rnentjes @@ -25,6 +27,7 @@ private String head; private String bottom; + private Map mapping = new HashMap(); @Override public void init() throws ServletException { @@ -37,10 +40,34 @@ throw new ServletException(e); } + mapping.put("queries", new QueryOverview()); + mapping.put("login", new Login()); + + // queries/select/1234 + // queries/page/2 + // queries/cancel + // queries/select/1234/select/5432 + + // queries/action=select/actionValue=1234 + // transactions/action=page&actionValue=3 + // settings } @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { + String uri = req.getRequestURI(); + + String [] parts = uri.split("\\/"); + int index = 0; + + if (parts.length > 0) { + Page page = mapping.get(parts[index++]); + + while(index < (parts.length - 1)) { + page = page.processGetRequest(parts[index++], parts[index++]); + } + } + doPost(req, resp); } diff --git a/src/nl/astraeus/jdbc/web/head.html b/src/nl/astraeus/jdbc/web/head.html index 0f8f6a3..54d9cb8 100644 --- a/src/nl/astraeus/jdbc/web/head.html +++ b/src/nl/astraeus/jdbc/web/head.html @@ -53,6 +53,15 @@ of the div is centered vertically*/ padding-top:0%; } + + code.sql { + color: black; + font-weight: bold + } + code.sql b { + color: blue; + /*font-weight: normal;*/ + } diff --git a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java index 28961d5..acc66f0 100644 --- a/src/nl/astraeus/jdbc/web/model/TransactionEntry.java +++ b/src/nl/astraeus/jdbc/web/model/TransactionEntry.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.model; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Formatting; import java.util.LinkedList; @@ -58,6 +59,7 @@ } public String getSql() { + SqlFormatter formatter = new SqlFormatter(); StringBuilder result = new StringBuilder(); boolean first = true; diff --git a/src/nl/astraeus/jdbc/web/page/Page.java b/src/nl/astraeus/jdbc/web/page/Page.java index 9d526d6..1c40df6 100644 --- a/src/nl/astraeus/jdbc/web/page/Page.java +++ b/src/nl/astraeus/jdbc/web/page/Page.java @@ -9,7 +9,11 @@ * Time: 3:20 PM */ public abstract class Page { - + + public Page processGetRequest(String action, String value) { + return this; + } + public abstract Page processRequest(HttpServletRequest request); public abstract Map defineModel(HttpServletRequest request); public abstract String render(HttpServletRequest request); diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.html b/src/nl/astraeus/jdbc/web/page/QueryDetail.html index 7d76fcd..e22819b 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.html +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.html @@ -22,7 +22,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/QueryDetail.java b/src/nl/astraeus/jdbc/web/page/QueryDetail.java index 3fcc7f7..186dc22 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryDetail.java +++ b/src/nl/astraeus/jdbc/web/page/QueryDetail.java @@ -1,6 +1,7 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; +import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import javax.servlet.http.HttpServletRequest; @@ -72,6 +73,7 @@ List list; list = new LinkedList(); + SqlFormatter formatter = new SqlFormatter(); if (!entries.isEmpty()) { long total = 0; diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.html b/src/nl/astraeus/jdbc/web/page/QueryOverview.html index 04cccc7..9f919e0 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.html +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.html @@ -41,7 +41,7 @@ {q.formattedNano} {q.count} {escape(none)} - 
{q.sql}
+ 
{q.sql}
{/escape} {/each} diff --git a/src/nl/astraeus/jdbc/web/page/QueryOverview.java b/src/nl/astraeus/jdbc/web/page/QueryOverview.java index 0e27168..f2a1c46 100644 --- a/src/nl/astraeus/jdbc/web/page/QueryOverview.java +++ b/src/nl/astraeus/jdbc/web/page/QueryOverview.java @@ -1,7 +1,6 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; -import nl.astraeus.jdbc.SqlFormatter; import nl.astraeus.jdbc.util.Util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -24,6 +23,17 @@ boolean sortTotalTime = false; @Override + public Page processGetRequest(String action, String value) { + Page result = this; + + if ("select".equals(action)) { + return new QueryDetail(this, Integer.parseInt(value)); + } + + return result; + } + + @Override public Page processRequest(HttpServletRequest request) { if ("sortTotalCalls".equals(request.getParameter("action"))) { sortTotalCalls = true; diff --git a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html index 87e6229..d1cc16e 100644 --- a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html +++ b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.html @@ -11,7 +11,7 @@
{escape(none)} - 

{sql}

+ 

{sql}

{/escape}
diff --git a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java index db3378c..a176a7f 100644 --- a/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java +++ b/src/nl/astraeus/jdbc/web/page/ShowStacktrace.java @@ -1,13 +1,13 @@ package nl.astraeus.jdbc.web.page; import nl.astraeus.jdbc.JdbcLogger; -import nl.astraeus.jdbc.util.Util; +import nl.astraeus.jdbc.SqlFormatter; import javax.servlet.http.HttpServletRequest; -import java.security.KeyStore; -import java.text.DateFormat; -import java.text.SimpleDateFormat; -import java.util.*; +import java.util.HashMap; +import java.util.LinkedList; +import java.util.List; +import java.util.Map; /** * User: rnentjes @@ -72,6 +72,8 @@ trace.add(new TraceElement(logEntry.getStackTrace()[index], hl)); } + SqlFormatter formatter = new SqlFormatter(); + result.put("trace", trace); result.put("count", trace.size()); result.put("sql", logEntry.getSql()); diff --git a/src/nl/astraeus/jdbc/web/page/TransactionOverview.html b/src/nl/astraeus/jdbc/web/page/TransactionOverview.html index ec222e4..1277d49 100644 --- a/src/nl/astraeus/jdbc/web/page/TransactionOverview.html +++ b/src/nl/astraeus/jdbc/web/page/TransactionOverview.html @@ -41,7 +41,7 @@ {t.formattedAvgTime} {t.count} {escape(none)} - 
{t.sql}
+ 
{t.sql}
{/escape} {/each}