Mercurial > hg > monetdb-java
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 } |