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 | |
8 | import java.io.BufferedReader; |
9 | import java.io.IOException; |
10 | import java.io.InputStream; |
11 | import java.io.InputStreamReader; |
12 | import java.io.PrintStream; |
13 | import java.io.StringReader; |
14 | import java.sql.Connection; |
15 | import java.sql.DriverManager; |
16 | import java.sql.ResultSet; |
17 | import java.sql.ResultSetMetaData; |
18 | import java.sql.SQLException; |
19 | import java.sql.Statement; |
20 | import java.util.ArrayList; |
21 | import java.util.Properties; |
22 | import org.h2.engine.Constants; |
23 | import org.h2.server.web.ConnectionInfo; |
24 | import org.h2.util.JdbcUtils; |
25 | import org.h2.util.New; |
26 | import org.h2.util.ScriptReader; |
27 | import org.h2.util.SortedProperties; |
28 | import org.h2.util.StringUtils; |
29 | import org.h2.util.Tool; |
30 | import org.h2.util.Utils; |
31 | |
32 | /** |
33 | * Interactive command line tool to access a database using JDBC. |
34 | * @h2.resource |
35 | */ |
36 | public class Shell extends Tool implements Runnable { |
37 | |
38 | private static final int MAX_ROW_BUFFER = 5000; |
39 | private static final int HISTORY_COUNT = 20; |
40 | // Windows: '\u00b3'; |
41 | private static final char BOX_VERTICAL = '|'; |
42 | |
43 | private PrintStream err = System.err; |
44 | private InputStream in = System.in; |
45 | private BufferedReader reader; |
46 | private Connection conn; |
47 | private Statement stat; |
48 | private boolean listMode; |
49 | private int maxColumnSize = 100; |
50 | private final ArrayList<String> history = New.arrayList(); |
51 | private boolean stopHide; |
52 | private String serverPropertiesDir = Constants.SERVER_PROPERTIES_DIR; |
53 | |
54 | /** |
55 | * Options are case sensitive. Supported options are: |
56 | * <table> |
57 | * <tr><td>[-help] or [-?]</td> |
58 | * <td>Print the list of options</td></tr> |
59 | * <tr><td>[-url "<url>"]</td> |
60 | * <td>The database URL (jdbc:h2:...)</td></tr> |
61 | * <tr><td>[-user <user>]</td> |
62 | * <td>The user name</td></tr> |
63 | * <tr><td>[-password <pwd>]</td> |
64 | * <td>The password</td></tr> |
65 | * <tr><td>[-driver <class>]</td> |
66 | * <td>The JDBC driver class to use (not required in most cases)</td></tr> |
67 | * <tr><td>[-sql "<statements>"]</td> |
68 | * <td>Execute the SQL statements and exit</td></tr> |
69 | * <tr><td>[-properties "<dir>"]</td> |
70 | * <td>Load the server properties from this directory</td></tr> |
71 | * </table> |
72 | * If special characters don't work as expected, you may need to use |
73 | * -Dfile.encoding=UTF-8 (Mac OS X) or CP850 (Windows). |
74 | * @h2.resource |
75 | * |
76 | * @param args the command line arguments |
77 | */ |
78 | public static void main(String... args) throws SQLException { |
79 | new Shell().runTool(args); |
80 | } |
81 | |
82 | /** |
83 | * Sets the standard error stream. |
84 | * |
85 | * @param err the new standard error stream |
86 | */ |
87 | public void setErr(PrintStream err) { |
88 | this.err = err; |
89 | } |
90 | |
91 | /** |
92 | * Redirects the standard input. By default, System.in is used. |
93 | * |
94 | * @param in the input stream to use |
95 | */ |
96 | public void setIn(InputStream in) { |
97 | this.in = in; |
98 | } |
99 | |
100 | /** |
101 | * Redirects the standard input. By default, System.in is used. |
102 | * |
103 | * @param reader the input stream reader to use |
104 | */ |
105 | public void setInReader(BufferedReader reader) { |
106 | this.reader = reader; |
107 | } |
108 | |
109 | /** |
110 | * Run the shell tool with the given command line settings. |
111 | * |
112 | * @param args the command line settings |
113 | */ |
114 | @Override |
115 | public void runTool(String... args) throws SQLException { |
116 | String url = null; |
117 | String user = ""; |
118 | String password = ""; |
119 | String sql = null; |
120 | for (int i = 0; args != null && i < args.length; i++) { |
121 | String arg = args[i]; |
122 | if (arg.equals("-url")) { |
123 | url = args[++i]; |
124 | } else if (arg.equals("-user")) { |
125 | user = args[++i]; |
126 | } else if (arg.equals("-password")) { |
127 | password = args[++i]; |
128 | } else if (arg.equals("-driver")) { |
129 | String driver = args[++i]; |
130 | JdbcUtils.loadUserClass(driver); |
131 | } else if (arg.equals("-sql")) { |
132 | sql = args[++i]; |
133 | } else if (arg.equals("-properties")) { |
134 | serverPropertiesDir = args[++i]; |
135 | } else if (arg.equals("-help") || arg.equals("-?")) { |
136 | showUsage(); |
137 | return; |
138 | } else if (arg.equals("-list")) { |
139 | listMode = true; |
140 | } else { |
141 | showUsageAndThrowUnsupportedOption(arg); |
142 | } |
143 | } |
144 | if (url != null) { |
145 | org.h2.Driver.load(); |
146 | conn = DriverManager.getConnection(url, user, password); |
147 | stat = conn.createStatement(); |
148 | } |
149 | if (sql == null) { |
150 | promptLoop(); |
151 | } else { |
152 | ScriptReader r = new ScriptReader(new StringReader(sql)); |
153 | while (true) { |
154 | String s = r.readStatement(); |
155 | if (s == null) { |
156 | break; |
157 | } |
158 | execute(s); |
159 | } |
160 | if (conn != null) { |
161 | conn.close(); |
162 | } |
163 | } |
164 | } |
165 | |
166 | /** |
167 | * Run the shell tool with the given connection and command line settings. |
168 | * The connection will be closed when the shell exits. |
169 | * This is primary used to integrate the Shell into another application. |
170 | * <p> |
171 | * Note: using the "-url" option in {@code args} doesn't make much sense |
172 | * since it will override the {@code conn} parameter. |
173 | * </p> |
174 | * |
175 | * @param conn the connection |
176 | * @param args the command line settings |
177 | */ |
178 | public void runTool(Connection conn, String... args) throws SQLException { |
179 | this.conn = conn; |
180 | this.stat = conn.createStatement(); |
181 | runTool(args); |
182 | } |
183 | |
184 | private void showHelp() { |
185 | println("Commands are case insensitive; SQL statements end with ';'"); |
186 | println("help or ? Display this help"); |
187 | println("list Toggle result list / stack trace mode"); |
188 | println("maxwidth Set maximum column width (default is 100)"); |
189 | println("autocommit Enable or disable autocommit"); |
190 | println("history Show the last 20 statements"); |
191 | println("quit or exit Close the connection and exit"); |
192 | println(""); |
193 | } |
194 | |
195 | private void promptLoop() { |
196 | println(""); |
197 | println("Welcome to H2 Shell " + Constants.getFullVersion()); |
198 | println("Exit with Ctrl+C"); |
199 | if (conn != null) { |
200 | showHelp(); |
201 | } |
202 | String statement = null; |
203 | if (reader == null) { |
204 | reader = new BufferedReader(new InputStreamReader(in)); |
205 | } |
206 | while (true) { |
207 | try { |
208 | if (conn == null) { |
209 | connect(); |
210 | showHelp(); |
211 | } |
212 | if (statement == null) { |
213 | print("sql> "); |
214 | } else { |
215 | print("...> "); |
216 | } |
217 | String line = readLine(); |
218 | if (line == null) { |
219 | break; |
220 | } |
221 | String trimmed = line.trim(); |
222 | if (trimmed.length() == 0) { |
223 | continue; |
224 | } |
225 | boolean end = trimmed.endsWith(";"); |
226 | if (end) { |
227 | line = line.substring(0, line.lastIndexOf(';')); |
228 | trimmed = trimmed.substring(0, trimmed.length() - 1); |
229 | } |
230 | String lower = StringUtils.toLowerEnglish(trimmed); |
231 | if ("exit".equals(lower) || "quit".equals(lower)) { |
232 | break; |
233 | } else if ("help".equals(lower) || "?".equals(lower)) { |
234 | showHelp(); |
235 | } else if ("list".equals(lower)) { |
236 | listMode = !listMode; |
237 | println("Result list mode is now " + (listMode ? "on" : "off")); |
238 | } else if ("history".equals(lower)) { |
239 | for (int i = 0, size = history.size(); i < size; i++) { |
240 | String s = history.get(i); |
241 | s = s.replace('\n', ' ').replace('\r', ' '); |
242 | println("#" + (1 + i) + ": " + s); |
243 | } |
244 | if (history.size() > 0) { |
245 | println("To re-run a statement, type the number and press and enter"); |
246 | } else { |
247 | println("No history"); |
248 | } |
249 | } else if (lower.startsWith("autocommit")) { |
250 | lower = lower.substring("autocommit".length()).trim(); |
251 | if ("true".equals(lower)) { |
252 | conn.setAutoCommit(true); |
253 | } else if ("false".equals(lower)) { |
254 | conn.setAutoCommit(false); |
255 | } else { |
256 | println("Usage: autocommit [true|false]"); |
257 | } |
258 | println("Autocommit is now " + conn.getAutoCommit()); |
259 | } else if (lower.startsWith("maxwidth")) { |
260 | lower = lower.substring("maxwidth".length()).trim(); |
261 | try { |
262 | maxColumnSize = Integer.parseInt(lower); |
263 | } catch (NumberFormatException e) { |
264 | println("Usage: maxwidth <integer value>"); |
265 | } |
266 | println("Maximum column width is now " + maxColumnSize); |
267 | } else { |
268 | boolean addToHistory = true; |
269 | if (statement == null) { |
270 | if (StringUtils.isNumber(line)) { |
271 | int pos = Integer.parseInt(line); |
272 | if (pos == 0 || pos > history.size()) { |
273 | println("Not found"); |
274 | } else { |
275 | statement = history.get(pos - 1); |
276 | addToHistory = false; |
277 | println(statement); |
278 | end = true; |
279 | } |
280 | } else { |
281 | statement = line; |
282 | } |
283 | } else { |
284 | statement += "\n" + line; |
285 | } |
286 | if (end) { |
287 | if (addToHistory) { |
288 | history.add(0, statement); |
289 | if (history.size() > HISTORY_COUNT) { |
290 | history.remove(HISTORY_COUNT); |
291 | } |
292 | } |
293 | execute(statement); |
294 | statement = null; |
295 | } |
296 | } |
297 | } catch (SQLException e) { |
298 | println("SQL Exception: " + e.getMessage()); |
299 | statement = null; |
300 | } catch (IOException e) { |
301 | println(e.getMessage()); |
302 | break; |
303 | } catch (Exception e) { |
304 | println("Exception: " + e.toString()); |
305 | e.printStackTrace(err); |
306 | break; |
307 | } |
308 | } |
309 | if (conn != null) { |
310 | try { |
311 | conn.close(); |
312 | println("Connection closed"); |
313 | } catch (SQLException e) { |
314 | println("SQL Exception: " + e.getMessage()); |
315 | e.printStackTrace(err); |
316 | } |
317 | } |
318 | } |
319 | |
320 | private void connect() throws IOException, SQLException { |
321 | String url = "jdbc:h2:~/test"; |
322 | String user = ""; |
323 | String driver = null; |
324 | try { |
325 | Properties prop; |
326 | if ("null".equals(serverPropertiesDir)) { |
327 | prop = new Properties(); |
328 | } else { |
329 | prop = SortedProperties.loadProperties( |
330 | serverPropertiesDir + "/" + Constants.SERVER_PROPERTIES_NAME); |
331 | } |
332 | String data = null; |
333 | boolean found = false; |
334 | for (int i = 0;; i++) { |
335 | String d = prop.getProperty(String.valueOf(i)); |
336 | if (d == null) { |
337 | break; |
338 | } |
339 | found = true; |
340 | data = d; |
341 | } |
342 | if (found) { |
343 | ConnectionInfo info = new ConnectionInfo(data); |
344 | url = info.url; |
345 | user = info.user; |
346 | driver = info.driver; |
347 | } |
348 | } catch (IOException e) { |
349 | // ignore |
350 | } |
351 | println("[Enter] " + url); |
352 | print("URL "); |
353 | url = readLine(url).trim(); |
354 | if (driver == null) { |
355 | driver = JdbcUtils.getDriver(url); |
356 | } |
357 | if (driver != null) { |
358 | println("[Enter] " + driver); |
359 | } |
360 | print("Driver "); |
361 | driver = readLine(driver).trim(); |
362 | println("[Enter] " + user); |
363 | print("User "); |
364 | user = readLine(user); |
365 | println("[Enter] Hide"); |
366 | print("Password "); |
367 | String password = readLine(); |
368 | if (password.length() == 0) { |
369 | password = readPassword(); |
370 | } |
371 | conn = JdbcUtils.getConnection(driver, url, user, password); |
372 | stat = conn.createStatement(); |
373 | println("Connected"); |
374 | } |
375 | |
376 | /** |
377 | * Print the string without newline, and flush. |
378 | * |
379 | * @param s the string to print |
380 | */ |
381 | protected void print(String s) { |
382 | out.print(s); |
383 | out.flush(); |
384 | } |
385 | |
386 | private void println(String s) { |
387 | out.println(s); |
388 | out.flush(); |
389 | } |
390 | |
391 | private String readPassword() throws IOException { |
392 | try { |
393 | Object console = Utils.callStaticMethod("java.lang.System.console"); |
394 | print("Password "); |
395 | char[] password = (char[]) Utils.callMethod(console, "readPassword"); |
396 | return password == null ? null : new String(password); |
397 | } catch (Exception e) { |
398 | // ignore, use the default solution |
399 | } |
400 | Thread passwordHider = new Thread(this, "Password hider"); |
401 | stopHide = false; |
402 | passwordHider.start(); |
403 | print("Password > "); |
404 | String p = readLine(); |
405 | stopHide = true; |
406 | try { |
407 | passwordHider.join(); |
408 | } catch (InterruptedException e) { |
409 | // ignore |
410 | } |
411 | print("\b\b"); |
412 | return p; |
413 | } |
414 | |
415 | /** |
416 | * INTERNAL. |
417 | * Hides the password by repeatedly printing |
418 | * backspace, backspace, >, <. |
419 | */ |
420 | @Override |
421 | public void run() { |
422 | while (!stopHide) { |
423 | print("\b\b><"); |
424 | try { |
425 | Thread.sleep(10); |
426 | } catch (InterruptedException e) { |
427 | // ignore |
428 | } |
429 | } |
430 | } |
431 | |
432 | |
433 | private String readLine(String defaultValue) throws IOException { |
434 | String s = readLine(); |
435 | return s.length() == 0 ? defaultValue : s; |
436 | } |
437 | |
438 | private String readLine() throws IOException { |
439 | String line = reader.readLine(); |
440 | if (line == null) { |
441 | throw new IOException("Aborted"); |
442 | } |
443 | return line; |
444 | } |
445 | |
446 | private void execute(String sql) { |
447 | if (sql.trim().length() == 0) { |
448 | return; |
449 | } |
450 | long time = System.currentTimeMillis(); |
451 | try { |
452 | ResultSet rs = null; |
453 | try { |
454 | if (stat.execute(sql)) { |
455 | rs = stat.getResultSet(); |
456 | int rowCount = printResult(rs, listMode); |
457 | time = System.currentTimeMillis() - time; |
458 | println("(" + rowCount + (rowCount == 1 ? |
459 | " row, " : " rows, ") + time + " ms)"); |
460 | } else { |
461 | int updateCount = stat.getUpdateCount(); |
462 | time = System.currentTimeMillis() - time; |
463 | println("(Update count: " + updateCount + ", " + time + " ms)"); |
464 | } |
465 | } finally { |
466 | JdbcUtils.closeSilently(rs); |
467 | } |
468 | } catch (SQLException e) { |
469 | println("Error: " + e.toString()); |
470 | if (listMode) { |
471 | e.printStackTrace(err); |
472 | } |
473 | return; |
474 | } |
475 | } |
476 | |
477 | private int printResult(ResultSet rs, boolean asList) throws SQLException { |
478 | if (asList) { |
479 | return printResultAsList(rs); |
480 | } |
481 | return printResultAsTable(rs); |
482 | } |
483 | |
484 | private int printResultAsTable(ResultSet rs) throws SQLException { |
485 | ResultSetMetaData meta = rs.getMetaData(); |
486 | int len = meta.getColumnCount(); |
487 | boolean truncated = false; |
488 | ArrayList<String[]> rows = New.arrayList(); |
489 | // buffer the header |
490 | String[] columns = new String[len]; |
491 | for (int i = 0; i < len; i++) { |
492 | String s = meta.getColumnLabel(i + 1); |
493 | columns[i] = s == null ? "" : s; |
494 | } |
495 | rows.add(columns); |
496 | int rowCount = 0; |
497 | while (rs.next()) { |
498 | rowCount++; |
499 | truncated |= loadRow(rs, len, rows); |
500 | if (rowCount > MAX_ROW_BUFFER) { |
501 | printRows(rows, len); |
502 | rows.clear(); |
503 | } |
504 | } |
505 | printRows(rows, len); |
506 | rows.clear(); |
507 | if (truncated) { |
508 | println("(data is partially truncated)"); |
509 | } |
510 | return rowCount; |
511 | } |
512 | |
513 | private boolean loadRow(ResultSet rs, int len, ArrayList<String[]> rows) |
514 | throws SQLException { |
515 | boolean truncated = false; |
516 | String[] row = new String[len]; |
517 | for (int i = 0; i < len; i++) { |
518 | String s = rs.getString(i + 1); |
519 | if (s == null) { |
520 | s = "null"; |
521 | } |
522 | // only truncate if more than one column |
523 | if (len > 1 && s.length() > maxColumnSize) { |
524 | s = s.substring(0, maxColumnSize); |
525 | truncated = true; |
526 | } |
527 | row[i] = s; |
528 | } |
529 | rows.add(row); |
530 | return truncated; |
531 | } |
532 | |
533 | private int[] printRows(ArrayList<String[]> rows, int len) { |
534 | int[] columnSizes = new int[len]; |
535 | for (int i = 0; i < len; i++) { |
536 | int max = 0; |
537 | for (String[] row : rows) { |
538 | max = Math.max(max, row[i].length()); |
539 | } |
540 | if (len > 1) { |
541 | Math.min(maxColumnSize, max); |
542 | } |
543 | columnSizes[i] = max; |
544 | } |
545 | for (String[] row : rows) { |
546 | StringBuilder buff = new StringBuilder(); |
547 | for (int i = 0; i < len; i++) { |
548 | if (i > 0) { |
549 | buff.append(' ').append(BOX_VERTICAL).append(' '); |
550 | } |
551 | String s = row[i]; |
552 | buff.append(s); |
553 | if (i < len - 1) { |
554 | for (int j = s.length(); j < columnSizes[i]; j++) { |
555 | buff.append(' '); |
556 | } |
557 | } |
558 | } |
559 | println(buff.toString()); |
560 | } |
561 | return columnSizes; |
562 | } |
563 | |
564 | private int printResultAsList(ResultSet rs) throws SQLException { |
565 | ResultSetMetaData meta = rs.getMetaData(); |
566 | int longestLabel = 0; |
567 | int len = meta.getColumnCount(); |
568 | String[] columns = new String[len]; |
569 | for (int i = 0; i < len; i++) { |
570 | String s = meta.getColumnLabel(i + 1); |
571 | columns[i] = s; |
572 | longestLabel = Math.max(longestLabel, s.length()); |
573 | } |
574 | StringBuilder buff = new StringBuilder(); |
575 | int rowCount = 0; |
576 | while (rs.next()) { |
577 | rowCount++; |
578 | buff.setLength(0); |
579 | if (rowCount > 1) { |
580 | println(""); |
581 | } |
582 | for (int i = 0; i < len; i++) { |
583 | if (i > 0) { |
584 | buff.append('\n'); |
585 | } |
586 | String label = columns[i]; |
587 | buff.append(label); |
588 | for (int j = label.length(); j < longestLabel; j++) { |
589 | buff.append(' '); |
590 | } |
591 | buff.append(": ").append(rs.getString(i + 1)); |
592 | } |
593 | println(buff.toString()); |
594 | } |
595 | if (rowCount == 0) { |
596 | for (int i = 0; i < len; i++) { |
597 | if (i > 0) { |
598 | buff.append('\n'); |
599 | } |
600 | String label = columns[i]; |
601 | buff.append(label); |
602 | } |
603 | println(buff.toString()); |
604 | } |
605 | return rowCount; |
606 | } |
607 | |
608 | } |