comparison src/main/java/nl/cwi/monetdb/client/JdbcClient.java @ 0:a5a898f6886c

Copy of MonetDB java directory changeset e6e32756ad31.
author Sjoerd Mullender <sjoerd@acm.org>
date Wed, 21 Sep 2016 09:34:48 +0200 (2016-09-21)
parents
children e67d58485172 17fbaf2635bb
comparison
equal deleted inserted replaced
-1:000000000000 0:a5a898f6886c
1 /*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2016 MonetDB B.V.
7 */
8
9 package nl.cwi.monetdb.client;
10
11 import nl.cwi.monetdb.util.*;
12 import java.sql.*;
13 import java.io.*;
14 import java.util.*;
15 import java.util.zip.*;
16 import java.net.*;
17
18 /**
19 * This program acts like an extended client program for MonetDB. Its
20 * look and feel is very much like PostgreSQL's interactive terminal
21 * program. Although it looks like this client is designed for MonetDB,
22 * it demonstrates the power of the JDBC interface since it built on top
23 * of JDBC only.
24 *
25 * @author Fabian Groffen, Martin van Dinther
26 * @version 1.3
27 */
28
29 public final class JdbcClient {
30
31 private static Connection con;
32 private static Statement stmt;
33 private static BufferedReader in;
34 private static PrintWriter out;
35 private static Exporter exporter;
36 private static DatabaseMetaData dbmd;
37
38 public final static void main(String[] args) throws Exception {
39 CmdLineOpts copts = new CmdLineOpts();
40
41 // arguments which take exactly one argument
42 copts.addOption("h", "host", CmdLineOpts.CAR_ONE, "localhost",
43 "The hostname of the host that runs the MonetDB database. " +
44 "A port number can be supplied by use of a colon, i.e. " +
45 "-h somehost:12345.");
46 copts.addOption("p", "port", CmdLineOpts.CAR_ONE, "50000",
47 "The port number to connect to.");
48 // todo make it CAR_ONE_MANY
49 copts.addOption("f", "file", CmdLineOpts.CAR_ONE, null,
50 "A file name to use either for reading or writing. The " +
51 "file will be used for writing when dump mode is used " +
52 "(-D --dump). In read mode, the file can also be an URL " +
53 "pointing to a plain text file that is optionally gzip " +
54 "compressed.");
55 copts.addOption("u", "user", CmdLineOpts.CAR_ONE, System.getProperty("user.name"),
56 "The username to use when connecting to the database.");
57 // this one is only here for the .monetdb file parsing, it is
58 // removed before the command line arguments are parsed
59 copts.addOption(null, "password", CmdLineOpts.CAR_ONE, null, null);
60 copts.addOption("d", "database", CmdLineOpts.CAR_ONE, "",
61 "Try to connect to the given database (only makes sense " +
62 "if connecting to monetdbd).");
63 copts.addOption("l", "language", CmdLineOpts.CAR_ONE, "sql",
64 "Use the given language, defaults to 'sql'.");
65
66 // arguments which have no argument(s)
67 copts.addOption(null, "help", CmdLineOpts.CAR_ZERO, null,
68 "This help screen.");
69 copts.addOption(null, "version", CmdLineOpts.CAR_ZERO, null,
70 "Display driver version and exit.");
71 copts.addOption("e", "echo", CmdLineOpts.CAR_ZERO, null,
72 "Also outputs the contents of the input file, if any.");
73 copts.addOption("q", "quiet", CmdLineOpts.CAR_ZERO, null,
74 "Suppress printing the welcome header.");
75
76 // arguments which have zero to many arguments
77 copts.addOption("D", "dump", CmdLineOpts.CAR_ZERO_MANY, null,
78 "Dumps the given table(s), or the complete database if " +
79 "none given.");
80
81 // extended options
82 copts.addOption(null, "Xoutput", CmdLineOpts.CAR_ONE, null,
83 "The output mode when dumping. Default is sql, xml may " +
84 "be used for an experimental XML output.");
85 copts.addOption(null, "Xhash", CmdLineOpts.CAR_ONE, null,
86 "Use the given hash algorithm during challenge response. " +
87 "Supported algorithm names: SHA1, MD5, plain.");
88 // arguments which can have zero or one argument(s)
89 copts.addOption(null, "Xdebug", CmdLineOpts.CAR_ZERO_ONE, null,
90 "Writes a transmission log to disk for debugging purposes. " +
91 "If a file name is given, it is used, otherwise a file " +
92 "called monet<timestamp>.log is created. A given file " +
93 "never be overwritten; instead a unique variation of the " +
94 "file is used.");
95 copts.addOption(null, "Xbatching", CmdLineOpts.CAR_ZERO_ONE, null,
96 "Indicates that a batch should be used instead of direct " +
97 "communication with the server for each statement. If a " +
98 "number is given, it is used as batch size. i.e. 8000 " +
99 "would execute the contents on the batch after each 8000 " +
100 "statements read. Batching can greatly speedup the " +
101 "process of restoring a database dump.");
102
103 // we store user and password in separate variables in order to
104 // be able to properly act on them like forgetting the password
105 // from the user's file if the user supplies a username on the
106 // command line arguments
107 String pass = null;
108 String user = null;
109
110 // look for a file called .monetdb in the current dir or in the
111 // user's homedir and read its preferences
112 File pref = new File(".monetdb");
113 if (!pref.exists())
114 pref = new File(System.getProperty("user.home"), ".monetdb");
115 if (pref.exists()) {
116 try {
117 copts.processFile(pref);
118 } catch (OptionsException e) {
119 System.err.println("Error in " + pref.getAbsolutePath() + ": " + e.getMessage());
120 System.exit(1);
121 }
122 user = copts.getOption("user").getArgument();
123 pass = copts.getOption("password").getArgument();
124 }
125
126 // process the command line arguments, remove password option
127 // first, and save the user we had at this point
128 copts.removeOption("password");
129 try {
130 copts.processArgs(args);
131 } catch (OptionsException e) {
132 System.err.println("Error: " + e.getMessage());
133 System.exit(1);
134 }
135 // we can actually compare pointers (objects) here
136 if (user != copts.getOption("user").getArgument()) pass = null;
137
138 if (copts.getOption("help").isPresent()) {
139 System.out.print(
140 "Usage java -jar jdbcclient.jar\n" +
141 " [-h host[:port]] [-p port] [-f file] [-u user]\n" +
142 " [-l language] [-d database] [-e] [-D [table]]\n" +
143 " [-X<opt>]\n" +
144 "or using long option equivalents --host --port --file --user --language\n" +
145 "--dump --echo --database.\n" +
146 "Arguments may be written directly after the option like -p50000.\n" +
147 "\n" +
148 "If no host and port are given, localhost and 50000 are assumed. An .monetdb\n" +
149 "file may exist in the user's home directory. This file can contain\n" +
150 "preferences to use each time JdbcClient is started. Options given on the\n" +
151 "command line override the preferences file. The .monetdb file syntax is\n" +
152 "<option>=<value> where option is one of the options host, port, file, mode\n" +
153 "debug, or password. Note that the last one is perilous and therefore not\n" +
154 "available as command line option.\n" +
155 "If no input file is given using the -f flag, an interactive session is\n" +
156 "started on the terminal.\n" +
157 "\n" +
158 "OPTIONS\n" +
159 copts.produceHelpMessage()
160 );
161 System.exit(0);
162 } else if (copts.getOption("version").isPresent()) {
163 // We cannot use the DatabaseMetaData here, because we
164 // cannot get a Connection. So instead, we just get the
165 // values we want out of the Driver directly.
166 System.out.println("JDBC Driver: v" + nl.cwi.monetdb.jdbc.MonetDriver.getDriverVersion());
167 System.exit(0);
168 }
169
170 in = new BufferedReader(new InputStreamReader(System.in));
171 out = new PrintWriter(new BufferedWriter(new OutputStreamWriter(System.out)));
172
173 // whether the semi-colon at the end of a String terminates the
174 // query or not (default = yes => SQL)
175 boolean scolonterm = true;
176 boolean xmlMode = "xml".equals(copts.getOption("Xoutput").getArgument());
177
178 // we need the password from the user, fetch it with a pseudo
179 // password protector
180 if (pass == null) {
181 char[] tmp = System.console().readPassword("password: ");
182 if (tmp == null) {
183 System.err.println("Invalid password!");
184 System.exit(1);
185 }
186 pass = String.valueOf(tmp);
187 }
188
189 user = copts.getOption("user").getArgument();
190
191 // build the hostname
192 String host = copts.getOption("host").getArgument();
193 if (host.indexOf(":") == -1) {
194 host = host + ":" + copts.getOption("port").getArgument();
195 }
196
197 // make sure the driver is loaded
198 Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");
199
200 // build the extra arguments of the JDBC connect string
201 String attr = "?";
202 CmdLineOpts.OptionContainer oc = copts.getOption("language");
203 String lang = oc.getArgument();
204 if (oc.isPresent())
205 attr += "language=" + lang + "&";
206 // set some behaviour based on the language XQuery
207 if (lang.equals("xquery")) {
208 scolonterm = false; // no ; to end a statement
209 if (!copts.getOption("Xoutput").isPresent())
210 xmlMode = true; // the user will like xml results, most probably
211 }
212 oc = copts.getOption("Xdebug");
213 if (oc.isPresent()) {
214 attr += "debug=true&";
215 if (oc.getArgumentCount() == 1)
216 attr += "logfile=" + oc.getArgument() + "&";
217 }
218 oc = copts.getOption("Xhash");
219 if (oc.isPresent())
220 attr += "hash=" + oc.getArgument() + "&";
221
222 // request a connection suitable for MonetDB from the driver
223 // manager note that the database specifier is only used when
224 // connecting to a proxy-like service, since MonetDB itself
225 // can't access multiple databases.
226 con = null;
227 String database = copts.getOption("database").getArgument();
228 try {
229 con = DriverManager.getConnection(
230 "jdbc:monetdb://" + host + "/" + database + attr,
231 user,
232 pass
233 );
234 SQLWarning warn = con.getWarnings();
235 while (warn != null) {
236 System.err.println("Connection warning: " + warn.getMessage());
237 warn = warn.getNextWarning();
238 }
239 con.clearWarnings();
240 } catch (SQLException e) {
241 System.err.println("Database connect failed: " + e.getMessage());
242 System.exit(1);
243 }
244
245 try {
246 dbmd = con.getMetaData();
247 } catch (SQLException e) {
248 // we ignore this because it's probably because we don't use
249 // SQL language
250 dbmd = null;
251 }
252 stmt = con.createStatement();
253
254 // see if we will have to perform a database dump (only in SQL mode)
255 if ("sql".equals(lang) && copts.getOption("dump").isPresent()) {
256 ResultSet tbl;
257
258 // use the given file for writing
259 oc = copts.getOption("file");
260 if (oc.isPresent())
261 out = new PrintWriter(new BufferedWriter(new FileWriter(oc.getArgument())));
262
263 // we only want user tables and views to be dumped, unless a specific
264 // table is requested
265 String[] types = {"TABLE", "VIEW"};
266 if (copts.getOption("dump").getArgumentCount() > 0)
267 types = null;
268 // request the tables available in the current schema in the database
269 tbl = dbmd.getTables(null, con.getSchema(), null, types);
270
271 List<Table> tables = new LinkedList<Table>();
272 while (tbl.next()) {
273 tables.add(new Table(
274 tbl.getString("TABLE_SCHEM"),
275 tbl.getString("TABLE_NAME"),
276 tbl.getString("TABLE_TYPE")));
277 }
278 tbl.close();
279 tbl = null;
280
281 if (xmlMode) {
282 exporter = new XMLExporter(out);
283 exporter.setProperty(XMLExporter.TYPE_NIL, XMLExporter.VALUE_XSI);
284 } else {
285 exporter = new SQLExporter(out);
286 // stick with SQL INSERT INTO commands for now
287 // in the future we might do COPY INTO's here using VALUE_COPY
288 exporter.setProperty(SQLExporter.TYPE_OUTPUT, SQLExporter.VALUE_INSERT);
289 }
290 exporter.useSchemas(true);
291
292 // start SQL output
293 if (!xmlMode)
294 out.println("START TRANSACTION;\n");
295
296 // dump specific table(s) or not?
297 if (copts.getOption("dump").getArgumentCount() > 0) { // yes we do
298 String[] dumpers = copts.getOption("dump").getArguments();
299 for (int i = 0; i < tables.size(); i++) {
300 Table ttmp = tables.get(i);
301 for (int j = 0; j < dumpers.length; j++) {
302 if (ttmp.getName().equalsIgnoreCase(dumpers[j].toString()) ||
303 ttmp.getFqname().equalsIgnoreCase(dumpers[j].toString()))
304 {
305 // dump the table
306 doDump(out, ttmp);
307 }
308 }
309 }
310 } else {
311 /* this returns everything, so including SYSTEM TABLE
312 * constraints */
313 tbl = dbmd.getImportedKeys(null, null, null);
314 while (tbl.next()) {
315 // find FK table object
316 Table fk = Table.findTable(tbl.getString("FKTABLE_SCHEM") + "." + tbl.getString("FKTABLE_NAME"), tables);
317
318 // find PK table object
319 Table pk = Table.findTable(tbl.getString("PKTABLE_SCHEM") + "." + tbl.getString("PKTABLE_NAME"), tables);
320
321 // this happens when a system table has referential
322 // constraints
323 if (fk == null || pk == null)
324 continue;
325
326 // add PK table dependancy to FK table
327 fk.addDependancy(pk);
328 }
329 tbl.close();
330 tbl = null;
331
332 // search for cycles of type a -> (x ->)+ b probably not
333 // the most optimal way, but it works by just scanning
334 // every table for loops in a recursive manor
335 for (Table t : tables) {
336 Table.checkForLoop(t, new ArrayList<Table>());
337 }
338
339 // find the graph, at this point we know there are no
340 // cycles, thus a solution exists
341 for (int i = 0; i < tables.size(); i++) {
342 List<Table> needs = tables.get(i).requires(tables.subList(0, i + 1));
343 if (needs.size() > 0) {
344 tables.removeAll(needs);
345 tables.addAll(i, needs);
346
347 // re-evaluate this position, for there is a new
348 // table now
349 i--;
350 }
351 }
352
353 // we now have the right order to dump tables
354 for (Table t : tables) {
355 // dump the table
356 doDump(out, t);
357 }
358 }
359
360 if (!xmlMode)
361 out.println("COMMIT;");
362 out.flush();
363
364 // free resources, close the statement
365 stmt.close();
366 // close the connection with the database
367 con.close();
368 // completed database dump
369 System.exit(0);
370 }
371
372 if (xmlMode) {
373 exporter = new XMLExporter(out);
374 exporter.setProperty(XMLExporter.TYPE_NIL, XMLExporter.VALUE_XSI);
375 } else {
376 exporter = new SQLExporter(out);
377 // we want nice table formatted output
378 exporter.setProperty(SQLExporter.TYPE_OUTPUT, SQLExporter.VALUE_TABLE);
379 }
380 exporter.useSchemas(false);
381
382 try {
383 // use the given file for reading
384 boolean hasFile = copts.getOption("file").isPresent();
385 boolean doEcho = hasFile && copts.getOption("echo").isPresent();
386 if (hasFile) {
387 String tmp = copts.getOption("file").getArgument();
388 int batchSize = 0;
389 try {
390 in = getReader(tmp);
391 } catch (Exception e) {
392 System.err.println("Error: " + e.getMessage());
393 System.exit(1);
394 }
395
396 // check for batch mode
397 oc = copts.getOption("Xbatching");
398 if (oc.isPresent()) {
399 if (oc.getArgumentCount() == 1) {
400 // parse the number
401 try {
402 batchSize = Integer.parseInt(oc.getArgument());
403 } catch (NumberFormatException ex) {
404 // complain to the user
405 throw new IllegalArgumentException("Illegal argument for Xbatching: " + oc.getArgument() + " is not a parseable number!");
406 }
407 }
408 processBatch(batchSize);
409 } else {
410 processInteractive(true, doEcho, scolonterm, user);
411 }
412 } else {
413 if (!copts.getOption("quiet").isPresent()) {
414 // print welcome message
415 out.println("Welcome to the MonetDB interactive JDBC terminal!");
416 if (dbmd != null) {
417 out.println("Database Server: " + dbmd.getDatabaseProductName() +
418 " v" + dbmd.getDatabaseProductVersion());
419 out.println("JDBC Driver: " + dbmd.getDriverName() +
420 " v" + dbmd.getDriverVersion());
421 }
422 out.println("Current Schema: " + con.getSchema());
423 out.println("Type \\q to quit, \\h for a list of available commands");
424 out.flush();
425 }
426 processInteractive(false, doEcho, scolonterm, user);
427 }
428
429 // free resources, close the statement
430 stmt.close();
431 // close the connection with the database
432 con.close();
433 // close the file (if we used a file)
434 in.close();
435 } catch (Exception e) {
436 System.err.println("A fatal exception occurred: " + e.toString());
437 e.printStackTrace(System.err);
438 // at least try to close the connection properly, since it will
439 // close all statements associated with it
440 try {
441 con.close();
442 } catch (SQLException ex) {
443 // ok... nice try
444 }
445 System.exit(1);
446 }
447 }
448
449 /**
450 * Tries to interpret the given String as URL or file. Returns the
451 * assigned BufferedReader, or throws an Exception if the given
452 * string couldn't be identified as a valid URL or file.
453 *
454 * @param uri URL or filename as String
455 * @return a BufferedReader for the uri
456 * @throws Exception if uri cannot be identified as a valid URL or
457 * file
458 */
459 static BufferedReader getReader(String uri) throws Exception {
460 BufferedReader ret = null;
461 URL u = null;
462
463 // Try and parse as URL first
464 try {
465 u = new URL(uri);
466 } catch (MalformedURLException e) {
467 // no URL, try as file
468 try {
469 ret = new BufferedReader(new FileReader(uri));
470 } catch (FileNotFoundException fnfe) {
471 // the message is descriptive enough, adds "(No such file
472 // or directory)" itself.
473 throw new Exception(fnfe.getMessage());
474 }
475 }
476
477 if (ret == null) try {
478 HttpURLConnection.setFollowRedirects(true);
479 HttpURLConnection con =
480 (HttpURLConnection)u.openConnection();
481 con.setRequestMethod("GET");
482 String ct = con.getContentType();
483 if ("application/x-gzip".equals(ct)) {
484 // open gzip stream
485 ret = new BufferedReader(new InputStreamReader(
486 new GZIPInputStream(con.getInputStream())));
487 } else {
488 // text/plain otherwise just attempt to read as is
489 ret = new BufferedReader(new InputStreamReader(
490 con.getInputStream()));
491 }
492 } catch (IOException e) {
493 // failed to open the url
494 throw new Exception("No such host/file: " + e.getMessage());
495 } catch (Exception e) {
496 // this is an exception that comes from deep ...
497 throw new Exception("Invalid URL: " + e.getMessage());
498 }
499
500 return ret;
501 }
502
503 /**
504 * Starts an interactive processing loop, where output is adjusted to an
505 * user session. This processing loop is not suitable for bulk processing
506 * as in executing the contents of a file, since processing on the given
507 * input is done after each row that has been entered.
508 *
509 * @param hasFile a boolean indicating whether a file is used as input
510 * @param doEcho a boolean indicating whether to echo the given input
511 * @param user a String representing the username of the current user
512 * @throws IOException if an IO exception occurs
513 * @throws SQLException if a database related error occurs
514 */
515 public static void processInteractive(
516 boolean hasFile,
517 boolean doEcho,
518 boolean scolonterm,
519 String user
520 )
521 throws IOException, SQLException
522 {
523 // an SQL stack keeps track of ( " and '
524 SQLStack stack = new SQLStack();
525 // a query part is a line of an SQL query
526 QueryPart qp = null;
527
528 String query = "", curLine;
529 boolean wasComplete = true, doProcess, lastac = false;
530 if (!hasFile) {
531 lastac = con.getAutoCommit();
532 out.println("auto commit mode: " + (lastac ? "on" : "off"));
533 out.print(getPrompt(stack, true));
534 out.flush();
535 }
536
537 // the main (interactive) process loop
538 for (long i = 1; true; i++) {
539 // Manually read a line, because we want to detect an EOF
540 // (ctrl-D). Doing so allows to have a terminator for query
541 // which is not based on a special character, as is the
542 // problem for XQuery
543 curLine = in.readLine();
544 if (curLine == null) {
545 out.println("");
546 if (!query.isEmpty()) {
547 try {
548 executeQuery(query, stmt, out, !hasFile);
549 } catch (SQLException e) {
550 out.flush();
551 do {
552 if (hasFile) {
553 System.err.println("Error on line " + i + ": [" + e.getSQLState() + "] " + e.getMessage());
554 } else {
555 System.err.println("Error [" + e.getSQLState() + "]: " + e.getMessage());
556 }
557 // print all error messages in the chain (if any)
558 } while ((e = e.getNextException()) != null);
559 }
560 query = "";
561 wasComplete = true;
562 if (!hasFile) {
563 boolean ac = con.getAutoCommit();
564 if (ac != lastac) {
565 out.println("auto commit mode: " + (ac ? "on" : "off"));
566 lastac = ac;
567 }
568 out.print(getPrompt(stack, wasComplete));
569 }
570 out.flush();
571 // try to read again
572 continue;
573 } else {
574 // user did ctrl-D without something in the buffer,
575 // so terminate
576 break;
577 }
578 }
579
580 if (doEcho) {
581 out.println(curLine);
582 out.flush();
583 }
584 qp = scanQuery(curLine, stack, scolonterm);
585 if (!qp.isEmpty()) {
586 String command = qp.getQuery();
587 doProcess = true;
588 if (wasComplete) {
589 doProcess = false;
590 // check for commands only when the previous row was
591 // complete
592 if (command.equals("\\q")) {
593 // quit
594 break;
595 } else if (command.startsWith("\\h")) {
596 out.println("Available commands:");
597 out.println("\\q quits this program");
598 out.println("\\h this help screen");
599 if (dbmd != null)
600 out.println("\\d list available tables and views");
601 out.println("\\d<obj> describes the given table or view");
602 out.println("\\l<uri> executes the contents of the given file or URL");
603 out.println("\\i<uri> batch executes the inserts from the given file or URL");
604 } else if (dbmd != null && command.startsWith("\\d")) {
605 try {
606 String object = command.substring(2).trim().toLowerCase();
607 if (scolonterm && object.endsWith(";"))
608 object = object.substring(0, object.length() - 1);
609 if (!object.isEmpty()) {
610 String schema;
611 int dot = object.indexOf(".");
612 if (dot != -1) {
613 // use provided schema
614 schema = object.substring(0, dot);
615 object = object.substring(dot + 1);
616 } else {
617 // use current schema
618 schema = con.getSchema();
619 }
620 ResultSet tbl = dbmd.getTables(null, schema, null, null);
621
622 // we have an object, see if we can find it
623 boolean found = false;
624 while (tbl.next()) {
625 String tableName = tbl.getString("TABLE_NAME");
626 String schemaName = tbl.getString("TABLE_SCHEM");
627 if ((dot == -1 && tableName.equalsIgnoreCase(object)) ||
628 (schemaName + "." + tableName).equalsIgnoreCase(object))
629 {
630 // we found it, describe it
631 exporter.dumpSchema(dbmd,
632 tbl.getString("TABLE_TYPE"),
633 tbl.getString("TABLE_CAT"),
634 schemaName,
635 tableName);
636
637 found = true;
638 break;
639 }
640 }
641 tbl.close();
642
643 if (!found)
644 System.err.println("Unknown table or view: " + object);
645 } else {
646 String current_schema = con.getSchema();
647 ResultSet tbl = dbmd.getTables(null, current_schema, null, null);
648
649 // give us a list of all non-system tables and views (including temp ones)
650 while (tbl.next()) {
651 String tableType = tbl.getString("TABLE_TYPE");
652 if (tableType != null && !tableType.startsWith("SYSTEM "))
653 out.println(tableType + "\t" +
654 tbl.getString("TABLE_SCHEM") + "." +
655 tbl.getString("TABLE_NAME"));
656 }
657 tbl.close();
658 }
659 } catch (SQLException e) {
660 out.flush();
661 do {
662 System.err.println("Error [" + e.getSQLState() + "]: " + e.getMessage());
663 // print all error messages in the chain (if any)
664 } while ((e = e.getNextException()) != null);
665 }
666 } else if (command.startsWith("\\l") || command.startsWith("\\i")) {
667 String object = command.substring(2).trim();
668 if (scolonterm && object.endsWith(";"))
669 object = object.substring(0, object.length() - 1);
670 if (object.isEmpty()) {
671 System.err.println("Usage: '" + command.substring(0, 2) + "<uri>' where <uri> is a file or URL");
672 } else {
673 // temporarily redirect input from in
674 BufferedReader console = in;
675 try {
676 in = getReader(object);
677 if (command.startsWith("\\l")) {
678 processInteractive(true, doEcho, scolonterm, user);
679 } else {
680 processBatch(0);
681 }
682 } catch (Exception e) {
683 out.flush();
684 System.err.println("Error: " + e.getMessage());
685 } finally {
686 // put back in redirection
687 in = console;
688 }
689 }
690 } else {
691 doProcess = true;
692 }
693 }
694
695 if (doProcess) {
696 query += command + (qp.hasOpenQuote() ? "\\n" : " ");
697 if (qp.isComplete()) {
698 // strip off trailing ';'
699 query = query.substring(0, query.length() - 2);
700 // execute query
701 try {
702 executeQuery(query, stmt, out, !hasFile);
703 } catch (SQLException e) {
704 out.flush();
705 do {
706 if (hasFile) {
707 System.err.println("Error on line " + i + ": [" + e.getSQLState() + "] " + e.getMessage());
708 } else {
709 System.err.println("Error [" + e.getSQLState() + "]: " + e.getMessage());
710 }
711 // print all error messages in the chain (if any)
712 } while ((e = e.getNextException()) != null);
713 }
714 query = "";
715 wasComplete = true;
716 } else {
717 wasComplete = false;
718 }
719 }
720 }
721
722 if (!hasFile) {
723 boolean ac = con.getAutoCommit();
724 if (ac != lastac) {
725 out.println("auto commit mode: " + (ac ? "on" : "off"));
726 lastac = ac;
727 }
728 out.print(getPrompt(stack, wasComplete));
729 }
730 out.flush();
731 }
732 }
733
734 /**
735 * Executes the given query and prints the result tabularised to the
736 * given PrintWriter stream. The result of this method is the
737 * default output of a query: tabular data.
738 *
739 * @param query the query to execute
740 * @param stmt the Statement to execute the query on
741 * @param out the PrintWriter to write to
742 * @param showTiming flag to specify if timing information nees to be printed
743 * @throws SQLException if a database related error occurs
744 */
745 private static void executeQuery(String query,
746 Statement stmt,
747 PrintWriter out,
748 boolean showTiming)
749 throws SQLException
750 {
751 // warnings generated during querying
752 SQLWarning warn;
753 long startTime = (showTiming ? System.currentTimeMillis() : 0);
754 long finishTime = 0;
755
756 // execute the query, let the driver decide what type it is
757 int aff = -1;
758 boolean nextRslt = stmt.execute(query, Statement.RETURN_GENERATED_KEYS);
759 if (!nextRslt)
760 aff = stmt.getUpdateCount();
761 do {
762 if (nextRslt) {
763 // we have a ResultSet, print it
764 ResultSet rs = stmt.getResultSet();
765
766 exporter.dumpResultSet(rs);
767 if (showTiming) {
768 finishTime = System.currentTimeMillis();
769 out.println("Elapsed Time: " + (finishTime - startTime) + " ms");
770 startTime = finishTime;
771 }
772
773 // if there were warnings for this result,
774 // show them!
775 warn = rs.getWarnings();
776 if (warn != null) {
777 // force stdout to be written so the
778 // warning appears below it
779 out.flush();
780 do {
781 System.err.println("ResultSet warning: " +
782 warn.getMessage());
783 warn = warn.getNextWarning();
784 } while (warn != null);
785 rs.clearWarnings();
786 }
787 rs.close();
788 } else if (aff != -1) {
789 String timingoutput = "";
790 if (showTiming) {
791 finishTime = System.currentTimeMillis();
792 timingoutput = ". Elapsed Time: " + (finishTime - startTime) + " ms";
793 startTime = finishTime;
794 }
795
796 if (aff == Statement.SUCCESS_NO_INFO) {
797 out.println("Operation successful" + timingoutput);
798 } else {
799 // we have an update count
800 // see if a key was generated
801 ResultSet rs = stmt.getGeneratedKeys();
802 boolean hasGeneratedKeyData = rs.next();
803 out.println(aff + " affected row" + (aff != 1 ? "s" : "") +
804 (hasGeneratedKeyData ? ", last generated key: " + rs.getString(1) : "") +
805 timingoutput);
806 rs.close();
807 }
808 }
809
810 out.flush();
811 } while ((nextRslt = stmt.getMoreResults()) ||
812 (aff = stmt.getUpdateCount()) != -1);
813
814 // if there were warnings for this statement,
815 // and/or connection show them!
816 warn = stmt.getWarnings();
817 while (warn != null) {
818 System.err.println("Statement warning: " + warn.getMessage());
819 warn = warn.getNextWarning();
820 }
821 stmt.clearWarnings();
822
823 warn = con.getWarnings();
824 while (warn != null) {
825 System.err.println("Connection warning: " + warn.getMessage());
826 warn = warn.getNextWarning();
827 }
828 con.clearWarnings();
829 }
830
831 /**
832 * Starts a processing loop optimized for processing (large) chunks of
833 * continuous data, such as input from a file. Unlike in the interactive
834 * loop above, queries are sent only to the database if a certain batch
835 * amount is reached. No client side query checks are made, but everything
836 * is sent to the server as-is.
837 *
838 * @param batchSize the number of items to store in the batch before
839 * sending them to the database for execution.
840 * @throws IOException if an IO exception occurs.
841 */
842 public static void processBatch(int batchSize) throws IOException {
843 StringBuilder query = new StringBuilder();
844 String curLine;
845 int i = 0;
846 try {
847 // the main loop
848 for (i = 1; (curLine = in.readLine()) != null; i++) {
849 query.append(curLine);
850 if (curLine.endsWith(";")) {
851 // lousy check for end of statement, but in batch mode it
852 // is not very important to catch all end of statements...
853 stmt.addBatch(query.toString());
854 query.delete(0, query.length());
855 } else {
856 query.append('\n');
857 }
858 if (batchSize > 0 && i % batchSize == 0) {
859 stmt.executeBatch();
860 stmt.clearBatch();
861 }
862 }
863 stmt.addBatch(query.toString());
864 stmt.executeBatch();
865 stmt.clearBatch();
866 } catch (SQLException e) {
867 do {
868 System.err.println("Error at line " + i + ": [" + e.getSQLState() + "] " + e.getMessage());
869 // print all error messages in the chain (if any)
870 } while ((e = e.getNextException()) != null);
871 }
872 }
873
874 /**
875 * Wrapper method that decides to dump SQL or XML. In the latter case,
876 * this method does the XML data generation.
877 *
878 * @param out a Writer to write the data to
879 * @param table the table to dump
880 * @throws SQLException if a database related error occurs
881 */
882 public static void doDump(PrintWriter out, Table table) throws SQLException {
883 String tableType = table.getType();
884
885 // dump CREATE definition of this table/view
886 exporter.dumpSchema(dbmd, tableType, null, table.getSchem(), table.getName());
887 out.println();
888
889 // only dump data from real tables, not from views
890 if (tableType.contains("TABLE")) {
891 ResultSet rs = stmt.executeQuery("SELECT * FROM " + table.getFqnameQ());
892 exporter.dumpResultSet(rs);
893 rs.close();
894 out.println();
895 }
896 }
897
898 /**
899 * Simple helper method that generates a prompt.
900 *
901 * @param stack the current SQLStack
902 * @param compl whether the statement is complete
903 * @return a prompt which consist of "sql" plus the top of the stack
904 */
905 private static String getPrompt(SQLStack stack, boolean compl) {
906 return (compl ? "sql" : "more") +
907 (stack.empty() ? ">" : "" + stack.peek()) + " ";
908 }
909
910 /**
911 * Scans the given string and tries to discover if it is a complete query
912 * or that there needs something to be added. If a string doesn't end with
913 * a ; it is considered not to be complete. SQL string quotation using ' and
914 * SQL identifier quotation using " is taken into account when scanning a
915 * string this way.
916 * Additionally, this method removes comments from the SQL statements,
917 * identified by -- and removes white space where appropriate.
918 *
919 * @param query the query to parse
920 * @param stack query stack to work with
921 * @param scolonterm whether a ';' makes this query part complete
922 * @return a QueryPart object containing the results of this parse
923 */
924 private static QueryPart scanQuery(
925 String query,
926 SQLStack stack,
927 boolean scolonterm)
928 {
929 // examine string, char for char
930 boolean wasInString = (stack.peek() == '\'');
931 boolean wasInIdentifier = (stack.peek() == '"');
932 boolean escaped = false;
933 int len = query.length();
934 for (int i = 0; i < len; i++) {
935 switch(query.charAt(i)) {
936 case '\\':
937 escaped = !escaped;
938 break;
939 default:
940 escaped = false;
941 break;
942 case '\'':
943 /**
944 * We can not be in a string if we are in an identifier. So
945 * If we find a ' and are not in an identifier, and not in
946 * a string we can safely assume we will be now in a string.
947 * If we are in a string already, we should stop being in a
948 * string if we find a quote which is not prefixed by a \,
949 * for that would be an escaped quote. However, a nasty
950 * situation can occur where the string is like 'test \\'.
951 * As obvious, a test for a \ in front of a ' doesn't hold
952 * here. Because 'test \\\'' can exist as well, we need to
953 * know if a quote is prefixed by an escaping slash or not.
954 */
955 if (!escaped && stack.peek() != '"') {
956 if (stack.peek() != '\'') {
957 // although it makes no sense to escape a quote
958 // outside a string, it is escaped, thus not meant
959 // as quote for us, apparently
960 stack.push('\'');
961 } else {
962 stack.pop();
963 }
964 }
965 // reset escaped flag
966 escaped = false;
967 break;
968 case '"':
969 if (!escaped && stack.peek() != '\'') {
970 if (stack.peek() != '"') {
971 stack.push('"');
972 } else {
973 stack.pop();
974 }
975 }
976 // reset escaped flag
977 escaped = false;
978 break;
979 case '-':
980 if (!escaped && stack.peek() != '\'' && stack.peek() != '"' && i + 1 < len && query.charAt(i + 1) == '-') {
981 len = i;
982 }
983 escaped = false;
984 break;
985 case '(':
986 if (!escaped && stack.peek() != '\'' && stack.peek() != '"') {
987 stack.push('(');
988 }
989 escaped = false;
990 break;
991 case ')':
992 if (!escaped && stack.peek() == '(') {
993 stack.pop();
994 }
995 escaped = false;
996 break;
997 }
998 }
999
1000 int start = 0;
1001 if (!wasInString && !wasInIdentifier && len > 0) {
1002 // trim spaces at the start of the string
1003 for (; start < len && Character.isWhitespace(query.charAt(start)); start++);
1004 }
1005 int stop = len - 1;
1006 if (stack.peek() != '\'' && !wasInIdentifier && stop > start) {
1007 // trim spaces at the end of the string
1008 for (; stop >= start && Character.isWhitespace(query.charAt(stop)); stop--);
1009 }
1010 stop++;
1011
1012 if (start == stop) {
1013 // we have an empty string
1014 return new QueryPart(false, null, stack.peek() == '\'' || stack.peek() == '"');
1015 } else if (stack.peek() == '\'' || stack.peek() == '"') {
1016 // we have an open quote
1017 return new QueryPart(false, query.substring(start, stop), true);
1018 } else {
1019 // see if the string is complete
1020 if (scolonterm && query.charAt(stop - 1) == ';') {
1021 return new QueryPart(true, query.substring(start, stop), false);
1022 } else {
1023 return new QueryPart(false, query.substring(start, stop), false);
1024 }
1025 }
1026 }
1027
1028 public static String dq(String in) {
1029 return "\"" + in.replaceAll("\\\\", "\\\\\\\\").replaceAll("\"", "\\\\\"") + "\"";
1030 }
1031 }
1032
1033 /**
1034 * A QueryPart is (a part of) a SQL query. In the QueryPart object information
1035 * like the actual SQL query string, whether it has an open quote and the like
1036 * is stored.
1037 */
1038 class QueryPart {
1039 private boolean complete;
1040 private String query;
1041 private boolean open;
1042
1043 public QueryPart(boolean complete, String query, boolean open) {
1044 this.complete = complete;
1045 this.query = query;
1046 this.open = open;
1047 }
1048
1049 public boolean isEmpty() {
1050 return query == null;
1051 }
1052
1053 public boolean isComplete() {
1054 return complete;
1055 }
1056
1057 public String getQuery() {
1058 return query;
1059 }
1060
1061 public boolean hasOpenQuote() {
1062 return open;
1063 }
1064 }
1065
1066 /**
1067 * An SQLStack is a simple stack that keeps track of open brackets and
1068 * (single and double) quotes in an SQL query.
1069 */
1070 class SQLStack {
1071 StringBuilder stack = new StringBuilder();
1072
1073 public char peek() {
1074 if (empty()) {
1075 return '\0';
1076 } else {
1077 return stack.charAt(stack.length() - 1);
1078 }
1079 }
1080
1081 public char pop() {
1082 char tmp = peek();
1083 if (tmp != '\0') {
1084 stack.setLength(stack.length() - 1);
1085 }
1086 return tmp;
1087 }
1088
1089 public char push(char item) {
1090 stack.append(item);
1091 return item;
1092 }
1093
1094 public boolean empty() {
1095 return stack.length() == 0;
1096 }
1097 }
1098
1099 /**
1100 * A Table represents an SQL table. All data required to
1101 * generate a fully qualified name is stored, as well as dependency
1102 * data.
1103 */
1104 class Table {
1105 final String schem;
1106 final String name;
1107 final String type;
1108 final String fqname;
1109 List<Table> needs = new ArrayList<Table>();
1110
1111 Table(String schem, String name, String type) {
1112 this.schem = schem;
1113 this.name = name;
1114 this.type = type;
1115 this.fqname = schem + "." + name;
1116 }
1117
1118 void addDependancy(Table dependsOn) throws Exception {
1119 if (this.fqname.equals(dependsOn.fqname))
1120 throw new Exception("Cyclic dependancy graphs are not supported (foreign key relation references self)");
1121
1122 if (dependsOn.needs.contains(this))
1123 throw new Exception("Cyclic dependancy graphs are not supported (foreign key relation a->b and b->a)");
1124
1125 if (!needs.contains(dependsOn))
1126 needs.add(dependsOn);
1127 }
1128
1129 List<Table> requires(List<Table> existingTables) {
1130 if (existingTables == null || existingTables.isEmpty())
1131 return new ArrayList<Table>(needs);
1132
1133 List<Table> req = new ArrayList<Table>();
1134 for (Table n : needs) {
1135 if (!existingTables.contains(n))
1136 req.add(n);
1137 }
1138
1139 return req;
1140 }
1141
1142 String getSchem() {
1143 return schem;
1144 }
1145
1146 String getSchemQ() {
1147 return JdbcClient.dq(schem);
1148 }
1149
1150 String getName() {
1151 return name;
1152 }
1153
1154 String getNameQ() {
1155 return JdbcClient.dq(name);
1156 }
1157
1158 String getType() {
1159 return type;
1160 }
1161
1162 String getFqname() {
1163 return fqname;
1164 }
1165
1166 String getFqnameQ() {
1167 return getSchemQ() + "." + getNameQ();
1168 }
1169
1170 public String toString() {
1171 return fqname;
1172 }
1173
1174
1175 static Table findTable(String fqname, List<Table> list) {
1176 for (Table l : list) {
1177 if (l.fqname.equals(fqname))
1178 return l;
1179 }
1180 // not found
1181 return null;
1182 }
1183
1184 static void checkForLoop(Table table, List<Table> parents) throws Exception {
1185 parents.add(table);
1186 for (int i = 0; i < table.needs.size(); i++) {
1187 Table child = table.needs.get(i);
1188 if (parents.contains(child))
1189 throw new Exception("Cyclic dependancy graphs are not supported (cycle detected for " + child.fqname + ")");
1190 checkForLoop(child, parents);
1191 }
1192 }
1193 }