1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.tools; |
7 | import java.io.IOException; |
8 | import java.io.LineNumberReader; |
9 | import java.io.PrintWriter; |
10 | import java.sql.SQLException; |
11 | import java.util.ArrayList; |
12 | import java.util.Collections; |
13 | import java.util.HashMap; |
14 | import java.util.StringTokenizer; |
15 | |
16 | import org.h2.message.DbException; |
17 | import org.h2.store.fs.FileUtils; |
18 | import org.h2.util.IOUtils; |
19 | import org.h2.util.MathUtils; |
20 | import org.h2.util.New; |
21 | import org.h2.util.StringUtils; |
22 | import org.h2.util.Tool; |
23 | |
24 | /** |
25 | * Converts a .trace.db file to a SQL script and Java source code. |
26 | * <br /> |
27 | * SQL statement statistics are listed as well. |
28 | * @h2.resource |
29 | */ |
30 | public class ConvertTraceFile extends Tool { |
31 | |
32 | private final HashMap<String, Stat> stats = New.hashMap(); |
33 | private long timeTotal; |
34 | |
35 | /** |
36 | * This class holds statistics about a SQL statement. |
37 | */ |
38 | static class Stat implements Comparable<Stat> { |
39 | String sql; |
40 | int executeCount; |
41 | long time; |
42 | long resultCount; |
43 | |
44 | @Override |
45 | public int compareTo(Stat other) { |
46 | if (other == this) { |
47 | return 0; |
48 | } |
49 | int c = MathUtils.compareLong(other.time, time); |
50 | if (c == 0) { |
51 | c = MathUtils.compareInt(other.executeCount, executeCount); |
52 | if (c == 0) { |
53 | c = sql.compareTo(other.sql); |
54 | } |
55 | } |
56 | return c; |
57 | } |
58 | } |
59 | |
60 | /** |
61 | * Options are case sensitive. Supported options are: |
62 | * <table> |
63 | * <tr><td>[-help] or [-?]</td> |
64 | * <td>Print the list of options</td></tr> |
65 | * <tr><td>[-traceFile <file>]</td> |
66 | * <td>The trace file name (default: test.trace.db)</td></tr> |
67 | * <tr><td>[-script <file>]</td> |
68 | * <td>The script file name (default: test.sql)</td></tr> |
69 | * <tr><td>[-javaClass <file>]</td> |
70 | * <td>The Java directory and class file name (default: Test)</td></tr> |
71 | * </table> |
72 | * @h2.resource |
73 | * |
74 | * @param args the command line arguments |
75 | */ |
76 | public static void main(String... args) throws SQLException { |
77 | new ConvertTraceFile().runTool(args); |
78 | } |
79 | |
80 | @Override |
81 | public void runTool(String... args) throws SQLException { |
82 | String traceFile = "test.trace.db"; |
83 | String javaClass = "Test"; |
84 | String script = "test.sql"; |
85 | for (int i = 0; args != null && i < args.length; i++) { |
86 | String arg = args[i]; |
87 | if (arg.equals("-traceFile")) { |
88 | traceFile = args[++i]; |
89 | } else if (arg.equals("-javaClass")) { |
90 | javaClass = args[++i]; |
91 | } else if (arg.equals("-script")) { |
92 | script = args[++i]; |
93 | } else if (arg.equals("-help") || arg.equals("-?")) { |
94 | showUsage(); |
95 | return; |
96 | } else { |
97 | showUsageAndThrowUnsupportedOption(arg); |
98 | } |
99 | } |
100 | try { |
101 | convertFile(traceFile, javaClass, script); |
102 | } catch (IOException e) { |
103 | throw DbException.convertIOException(e, traceFile); |
104 | } |
105 | } |
106 | |
107 | /** |
108 | * Converts a trace file to a Java class file and a script file. |
109 | */ |
110 | private void convertFile(String traceFileName, String javaClassName, |
111 | String script) throws IOException { |
112 | LineNumberReader reader = new LineNumberReader( |
113 | IOUtils.getBufferedReader( |
114 | FileUtils.newInputStream(traceFileName))); |
115 | PrintWriter javaWriter = new PrintWriter( |
116 | IOUtils.getBufferedWriter( |
117 | FileUtils.newOutputStream(javaClassName + ".java", false))); |
118 | PrintWriter scriptWriter = new PrintWriter( |
119 | IOUtils.getBufferedWriter( |
120 | FileUtils.newOutputStream(script, false))); |
121 | javaWriter.println("import java.io.*;"); |
122 | javaWriter.println("import java.sql.*;"); |
123 | javaWriter.println("import java.math.*;"); |
124 | javaWriter.println("import java.util.Calendar;"); |
125 | String cn = javaClassName.replace('\\', '/'); |
126 | int idx = cn.lastIndexOf('/'); |
127 | if (idx > 0) { |
128 | cn = cn.substring(idx + 1); |
129 | } |
130 | javaWriter.println("public class " + cn + " {"); |
131 | javaWriter.println(" public static void main(String... args) " + |
132 | "throws Exception {"); |
133 | javaWriter.println(" Class.forName(\"org.h2.Driver\");"); |
134 | while (true) { |
135 | String line = reader.readLine(); |
136 | if (line == null) { |
137 | break; |
138 | } |
139 | if (line.startsWith("/**/")) { |
140 | line = " " + line.substring(4); |
141 | javaWriter.println(line); |
142 | } else if (line.startsWith("/*SQL")) { |
143 | int end = line.indexOf("*/"); |
144 | String sql = line.substring(end + "*/".length()); |
145 | sql = StringUtils.javaDecode(sql); |
146 | line = line.substring("/*SQL".length(), end); |
147 | if (line.length() > 0) { |
148 | String statement = sql; |
149 | int count = 0; |
150 | long time = 0; |
151 | line = line.trim(); |
152 | if (line.length() > 0) { |
153 | StringTokenizer tk = new StringTokenizer(line, " :"); |
154 | while (tk.hasMoreElements()) { |
155 | String token = tk.nextToken(); |
156 | if ("l".equals(token)) { |
157 | int len = Integer.parseInt(tk.nextToken()); |
158 | statement = sql.substring(0, len) + ";"; |
159 | } else if ("#".equals(token)) { |
160 | count = Integer.parseInt(tk.nextToken()); |
161 | } else if ("t".equals(token)) { |
162 | time = Long.parseLong(tk.nextToken()); |
163 | } |
164 | } |
165 | } |
166 | addToStats(statement, count, time); |
167 | } |
168 | scriptWriter.println(sql); |
169 | } |
170 | } |
171 | javaWriter.println(" }"); |
172 | javaWriter.println('}'); |
173 | reader.close(); |
174 | javaWriter.close(); |
175 | if (stats.size() > 0) { |
176 | scriptWriter.println("-----------------------------------------"); |
177 | scriptWriter.println("-- SQL Statement Statistics"); |
178 | scriptWriter.println("-- time: total time in milliseconds (accumulated)"); |
179 | scriptWriter.println("-- count: how many times the statement ran"); |
180 | scriptWriter.println("-- result: total update count or row count"); |
181 | scriptWriter.println("-----------------------------------------"); |
182 | scriptWriter.println("-- self accu time count result sql"); |
183 | int accumTime = 0; |
184 | ArrayList<Stat> list = New.arrayList(stats.values()); |
185 | Collections.sort(list); |
186 | if (timeTotal == 0) { |
187 | timeTotal = 1; |
188 | } |
189 | for (Stat stat : list) { |
190 | accumTime += stat.time; |
191 | StringBuilder buff = new StringBuilder(100); |
192 | buff.append("-- "). |
193 | append(padNumberLeft(100 * stat.time / timeTotal, 3)). |
194 | append("% "). |
195 | append(padNumberLeft(100 * accumTime / timeTotal, 3)). |
196 | append('%'). |
197 | append(padNumberLeft(stat.time, 8)). |
198 | append(padNumberLeft(stat.executeCount, 8)). |
199 | append(padNumberLeft(stat.resultCount, 8)). |
200 | append(' '). |
201 | append(removeNewlines(stat.sql)); |
202 | scriptWriter.println(buff.toString()); |
203 | } |
204 | } |
205 | scriptWriter.close(); |
206 | } |
207 | |
208 | private static String removeNewlines(String s) { |
209 | return s == null ? s : s.replace('\r', ' ').replace('\n', ' '); |
210 | } |
211 | |
212 | private static String padNumberLeft(long number, int digits) { |
213 | return StringUtils.pad(String.valueOf(number), digits, " ", false); |
214 | } |
215 | |
216 | private void addToStats(String sql, int resultCount, long time) { |
217 | Stat stat = stats.get(sql); |
218 | if (stat == null) { |
219 | stat = new Stat(); |
220 | stat.sql = sql; |
221 | stats.put(sql, stat); |
222 | } |
223 | stat.executeCount++; |
224 | stat.resultCount += resultCount; |
225 | stat.time += time; |
226 | timeTotal += time; |
227 | } |
228 | |
229 | } |