File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\tools\Shell.java.html

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