001: /* Copyright (c) 2001-2007, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.util;
032:
033: import java.io.BufferedReader;
034: import java.io.File;
035: import java.io.IOException;
036: import java.io.InputStreamReader;
037: import java.sql.Connection;
038: import java.sql.DatabaseMetaData;
039: import java.sql.SQLException;
040: import java.util.HashMap;
041: import java.util.Map;
042:
043: /* $Id: SqlTool.java,v 1.72 2007/06/29 12:23:47 unsaved Exp $ */
044:
045: /**
046: * Sql Tool. A command-line and/or interactive SQL tool.
047: * (Note: For every Javadoc block comment, I'm using a single blank line
048: * immediately after the description, just like's Sun's examples in
049: * their Coding Conventions document).
050: *
051: * See JavaDocs for the main method for syntax of how to run.
052: * This class is mostly used in a static (a.o.t. object) way, because most
053: * of the work is done in the static main class.
054: * This class should be refactored so that the main work is done in an
055: * object method, and the static main invokes the object method.
056: * Then programmatic users could use instances of this class in the normal
057: * Java way.
058: *
059: * @see #main()
060: * @version $Revision: 1.72 $
061: * @author Blaine Simpson unsaved@users
062: */
063: public class SqlTool {
064: private static final String DEFAULT_RCFILE = System
065: .getProperty("user.home")
066: + "/sqltool.rc";
067: // N.b. the following is static!
068: private static String revnum = null;
069:
070: public static final int SQLTOOLERR_EXITVAL = 1;
071: public static final int SYNTAXERR_EXITVAL = 11;
072: public static final int RCERR_EXITVAL = 2;
073: public static final int SQLERR_EXITVAL = 3;
074: public static final int IOERR_EXITVAL = 4;
075: public static final int FILEERR_EXITVAL = 5;
076: public static final int INPUTERR_EXITVAL = 6;
077: public static final int CONNECTERR_EXITVAL = 7;
078:
079: /**
080: * The configuration identifier to use when connection parameters are
081: * specified on the command line
082: */
083: private static String CMDLINE_ID = "cmdline";
084: static private SqltoolRB rb = null;
085: // Must use a shared static RB object, since we need to get messages
086: // inside of static methods.
087: // This means that the locale will be set the first time this class
088: // is accessed. Subsequent calls will not update the RB if the locale
089: // changes (could have it check and reload the RB if this becomes an
090: // issue).
091:
092: static {
093: revnum = "333";
094: try {
095: rb = new SqltoolRB();
096: rb.validate();
097: rb
098: .setMissingPosValueBehavior(ValidatingResourceBundle.NOOP_BEHAVIOR);
099: rb
100: .setMissingPropertyBehavior(ValidatingResourceBundle.NOOP_BEHAVIOR);
101: } catch (RuntimeException re) {
102: System.err.println("Failed to initialize resource bundle");
103: throw re;
104: }
105: }
106: public static String LS = System.getProperty("line.separator");
107:
108: /** Utility nested class for internal use. */
109: private static class BadCmdline extends Exception {
110: static final long serialVersionUID = -2134764796788108325L;
111:
112: BadCmdline() {
113: }
114: }
115:
116: /** Utility object for internal use. */
117: private static BadCmdline bcl = new BadCmdline();
118:
119: /** For trapping of exceptions inside this class.
120: * These are always handled inside this class.
121: */
122: private static class PrivateException extends Exception {
123: static final long serialVersionUID = -7765061479594523462L;
124:
125: PrivateException() {
126: super ();
127: }
128:
129: PrivateException(String s) {
130: super (s);
131: }
132: }
133:
134: public static class SqlToolException extends Exception {
135: static final long serialVersionUID = 1424909871915188519L;
136:
137: int exitValue = 1;
138:
139: SqlToolException(String message, int exitValue) {
140: super (message);
141: this .exitValue = exitValue;
142: }
143:
144: SqlToolException(int exitValue, String message) {
145: this (message, exitValue);
146: }
147:
148: SqlToolException(int exitValue) {
149: super ();
150: this .exitValue = exitValue;
151: }
152: }
153:
154: /**
155: * Prompt the user for a password.
156: *
157: * @param username The user the password is for
158: * @return The password the user entered
159: */
160: private static String promptForPassword(String username)
161: throws PrivateException {
162:
163: BufferedReader console;
164: String password;
165:
166: password = null;
167:
168: try {
169: console = new BufferedReader(new InputStreamReader(
170: System.in));
171:
172: // Prompt for password
173: System.out.print(rb.getString(SqltoolRB.PASSWORDFOR_PROMPT,
174: RCData.expandSysPropVars(username)));
175:
176: // Read the password from the command line
177: password = console.readLine();
178:
179: if (password == null) {
180: password = "";
181: } else {
182: password = password.trim();
183: }
184: } catch (IOException e) {
185: throw new PrivateException(e.getMessage());
186: }
187:
188: return password;
189: }
190:
191: /**
192: * Parses a comma delimited string of name value pairs into a
193: * <code>Map</code> object.
194: *
195: * @param varString The string to parse
196: * @param varMap The map to save the paired values into
197: * @param lowerCaseKeys Set to <code>true</code> if the map keys should be
198: * converted to lower case
199: */
200: private static void varParser(String varString, Map varMap,
201: boolean lowerCaseKeys) throws PrivateException {
202:
203: int equals;
204: String var;
205: String val;
206: String[] allvars;
207:
208: if ((varMap == null) || (varString == null)) {
209: throw new IllegalArgumentException(
210: "varMap or varString are null in SqlTool.varParser call");
211: }
212:
213: allvars = varString.split("\\s*,\\s*");
214:
215: for (int i = 0; i < allvars.length; i++) {
216: equals = allvars[i].indexOf('=');
217:
218: if (equals < 1) {
219: throw new PrivateException(rb
220: .getString(SqltoolRB.SQLTOOL_VARSET_BADFORMAT));
221: }
222:
223: var = allvars[i].substring(0, equals).trim();
224: val = allvars[i].substring(equals + 1).trim();
225:
226: if (var.length() < 1) {
227: throw new PrivateException(rb
228: .getString(SqltoolRB.SQLTOOL_VARSET_BADFORMAT));
229: }
230:
231: if (lowerCaseKeys) {
232: var = var.toLowerCase();
233: }
234:
235: varMap.put(var, val);
236: }
237: }
238:
239: /**
240: * A static wrapper for objectMain, so that that method may be executed
241: * as a Java "program".
242: *
243: * Throws only RuntimExceptions or Errors, because this method is intended
244: * to System.exit() for all but disasterous system problems, for which
245: * the inconvenience of a a stack trace would be the least of your worries.
246: *
247: * If you don't want SqlTool to System.exit(), then use the method
248: * objectMain() instead of this method.
249: *
250: * @see objectMain(String[])
251: */
252: public static void main(String[] args) {
253: try {
254: SqlTool.objectMain(args);
255: } catch (SqlToolException fr) {
256: if (fr.getMessage() != null) {
257: System.err.println(fr.getMessage());
258: }
259: System.exit(fr.exitValue);
260: }
261: System.exit(0);
262: }
263:
264: /**
265: * Connect to a JDBC Database and execute the commands given on
266: * stdin or in SQL file(s).
267: *
268: * This method is changed for HSQLDB 1.8.0.8 and 1.9.0.x to never
269: * System.exit().
270: *
271: * @param arg Run "java... org.hsqldb.util.SqlTool --help" for syntax.
272: * @throws SqlToolException Upon any fatal error, with useful
273: * reason as the exception's message.
274: */
275: static public void objectMain(String[] arg) throws SqlToolException {
276:
277: /*
278: * The big picture is, we parse input args; load a RCData;
279: * get a JDBC Connection with the RCData; instantiate and
280: * execute as many SqlFiles as we need to.
281: */
282: String rcFile = null;
283: File tmpFile = null;
284: String sqlText = null;
285: String driver = null;
286: String targetDb = null;
287: String varSettings = null;
288: boolean debug = false;
289: File[] scriptFiles = null;
290: int i = -1;
291: boolean listMode = false;
292: boolean interactive = false;
293: boolean noinput = false;
294: boolean noautoFile = false;
295: boolean autoCommit = false;
296: Boolean coeOverride = null;
297: Boolean stdinputOverride = null;
298: String rcParams = null;
299: String rcUrl = null;
300: String rcUsername = null;
301: String rcPassword = null;
302: String rcCharset = null;
303: String rcTruststore = null;
304: Map rcFields = null;
305: String parameter;
306:
307: try {
308: while ((i + 1 < arg.length) && arg[i + 1].startsWith("--")) {
309: i++;
310:
311: if (arg[i].length() == 2) {
312: break; // "--"
313: }
314:
315: parameter = arg[i].substring(2).toLowerCase();
316:
317: if (parameter.equals("help")) {
318: System.out.println(rb.getString(
319: SqltoolRB.SQLTOOL_SYNTAX, revnum,
320: RCData.DEFAULT_JDBC_DRIVER));
321: return;
322: }
323: if (parameter.equals("abortonerr")) {
324: if (coeOverride != null) {
325: throw new SqlToolException(
326: SYNTAXERR_EXITVAL,
327: rb
328: .getString(SqltoolRB.SQLTOOL_ABORTCONTINUE_MUTUALLYEXCLUSIVE));
329: }
330:
331: coeOverride = Boolean.FALSE;
332: } else if (parameter.equals("continueonerr")) {
333: if (coeOverride != null) {
334: throw new SqlToolException(
335: SYNTAXERR_EXITVAL,
336: rb
337: .getString(SqltoolRB.SQLTOOL_ABORTCONTINUE_MUTUALLYEXCLUSIVE));
338: }
339:
340: coeOverride = Boolean.TRUE;
341: } else if (parameter.equals("list")) {
342: listMode = true;
343: } else if (parameter.equals("rcfile")) {
344: if (++i == arg.length) {
345: throw bcl;
346: }
347:
348: rcFile = arg[i];
349: } else if (parameter.equals("setvar")) {
350: if (++i == arg.length) {
351: throw bcl;
352: }
353:
354: varSettings = arg[i];
355: } else if (parameter.equals("sql")) {
356: noinput = true; // but turn back on if file "-" specd.
357:
358: if (++i == arg.length) {
359: throw bcl;
360: }
361:
362: sqlText = arg[i];
363: } else if (parameter.equals("debug")) {
364: debug = true;
365: } else if (parameter.equals("noautofile")) {
366: noautoFile = true;
367: } else if (parameter.equals("autocommit")) {
368: autoCommit = true;
369: } else if (parameter.equals("stdinput")) {
370: noinput = false;
371: stdinputOverride = Boolean.TRUE;
372: } else if (parameter.equals("noinput")) {
373: noinput = true;
374: stdinputOverride = Boolean.FALSE;
375: } else if (parameter.equals("driver")) {
376: if (++i == arg.length) {
377: throw bcl;
378: }
379:
380: driver = arg[i];
381: } else if (parameter.equals("inlinerc")) {
382: if (++i == arg.length) {
383: throw bcl;
384: }
385:
386: rcParams = arg[i];
387: } else {
388: throw bcl;
389: }
390: }
391:
392: if (!listMode) {
393:
394: // If an inline RC file was specified, don't worry about the targetDb
395: if (rcParams == null) {
396: if (++i == arg.length) {
397: throw bcl;
398: }
399:
400: targetDb = arg[i];
401: }
402: }
403:
404: int scriptIndex = 0;
405:
406: if (sqlText != null) {
407: try {
408: tmpFile = File.createTempFile("sqltool-", ".sql");
409:
410: //(new java.io.FileWriter(tmpFile)).write(sqlText);
411: java.io.FileWriter fw = new java.io.FileWriter(
412: tmpFile);
413: try {
414:
415: fw.write("/* " + (new java.util.Date()) + ". "
416: + SqlTool.class.getName()
417: + " command-line SQL. */" + LS + LS);
418: fw.write(sqlText + LS);
419: fw.flush();
420: } finally {
421: fw.close();
422: }
423: } catch (IOException ioe) {
424: throw new SqlToolException(IOERR_EXITVAL, rb
425: .getString(SqltoolRB.SQLTEMPFILE_FAIL, ioe
426: .toString()));
427: }
428: }
429:
430: if (stdinputOverride != null) {
431: noinput = !stdinputOverride.booleanValue();
432: }
433:
434: interactive = (!noinput) && (arg.length <= i + 1);
435:
436: if (arg.length == i + 2 && arg[i + 1].equals("-")) {
437: if (stdinputOverride == null) {
438: noinput = false;
439: }
440: } else if (arg.length > i + 1) {
441:
442: // I.e., if there are any SQL files specified.
443: scriptFiles = new File[arg.length
444: - i
445: - 1
446: + ((stdinputOverride == null || !stdinputOverride
447: .booleanValue()) ? 0 : 1)];
448:
449: if (debug) {
450: System.err.println("scriptFiles has "
451: + scriptFiles.length + " elements");
452: }
453:
454: while (i + 1 < arg.length) {
455: scriptFiles[scriptIndex++] = new File(arg[++i]);
456: }
457:
458: if (stdinputOverride != null
459: && stdinputOverride.booleanValue()) {
460: scriptFiles[scriptIndex++] = null;
461: noinput = true;
462: }
463: }
464: } catch (BadCmdline bcl) {
465: throw new SqlToolException(SYNTAXERR_EXITVAL, rb.getString(
466: SqltoolRB.SQLTOOL_SYNTAX, revnum,
467: RCData.DEFAULT_JDBC_DRIVER));
468: }
469:
470: RCData conData = null;
471:
472: // Use the inline RC file if it was specified
473: if (rcParams != null) {
474: rcFields = new HashMap();
475:
476: try {
477: varParser(rcParams, rcFields, true);
478: } catch (PrivateException e) {
479: throw new SqlToolException(SYNTAXERR_EXITVAL, e
480: .getMessage());
481: }
482:
483: rcUrl = (String) rcFields.remove("url");
484: rcUsername = (String) rcFields.remove("user");
485: rcCharset = (String) rcFields.remove("charset");
486: rcTruststore = (String) rcFields.remove("truststore");
487: rcPassword = (String) rcFields.remove("password");
488:
489: // Don't ask for password if what we have already is invalid!
490: if (rcUrl == null || rcUrl.length() < 1)
491: throw new SqlToolException(RCERR_EXITVAL, rb
492: .getString(SqltoolRB.RCDATA_INLINEURL_MISSING));
493: if (rcUsername == null || rcUsername.length() < 1)
494: throw new SqlToolException(
495: RCERR_EXITVAL,
496: rb
497: .getString(SqltoolRB.RCDATA_INLINEUSERNAME_MISSING));
498: if (rcPassword != null && rcPassword.length() > 0)
499: throw new SqlToolException(RCERR_EXITVAL, rb
500: .getString(SqltoolRB.RCDATA_PASSWORD_VISIBLE));
501: if (rcFields.size() > 0) {
502: throw new SqlToolException(INPUTERR_EXITVAL, rb
503: .getString(SqltoolRB.RCDATA_INLINE_EXTRAVARS,
504: rcFields.keySet().toString()));
505: }
506:
507: if (rcPassword == null)
508: try {
509: rcPassword = promptForPassword(rcUsername);
510: } catch (PrivateException e) {
511: throw new SqlToolException(INPUTERR_EXITVAL, rb
512: .getString(SqltoolRB.PASSWORD_READFAIL, e
513: .getMessage()));
514: }
515: try {
516: conData = new RCData(CMDLINE_ID, rcUrl, rcUsername,
517: rcPassword, driver, rcCharset, rcTruststore);
518: } catch (Exception e) {
519: throw new SqlToolException(RCERR_EXITVAL, rb.getString(
520: SqltoolRB.RCDATA_GENFROMVALUES_FAIL, e
521: .getMessage()));
522: }
523: } else {
524: try {
525: conData = new RCData(new File(
526: (rcFile == null) ? DEFAULT_RCFILE : rcFile),
527: targetDb);
528: } catch (Exception e) {
529: throw new SqlToolException(RCERR_EXITVAL, rb.getString(
530: SqltoolRB.CONNDATA_RETRIEVAL_FAIL, targetDb, e
531: .getMessage()));
532: }
533: }
534:
535: if (listMode) {
536: return;
537: }
538:
539: if (debug) {
540: conData.report();
541: }
542:
543: Connection conn = null;
544: try {
545: conn = conData.getConnection(driver, System
546: .getProperty("sqlfile.charset"), System
547: .getProperty("javax.net.ssl.trustStore"));
548:
549: conn.setAutoCommit(autoCommit);
550:
551: DatabaseMetaData md = null;
552:
553: if (interactive && (md = conn.getMetaData()) != null) {
554: System.out.println(rb.getString(
555: SqltoolRB.JDBC_ESTABLISHED, md
556: .getDatabaseProductName(), md
557: .getDatabaseProductVersion(), md
558: .getUserName()));
559: }
560: } catch (Exception e) {
561: //e.printStackTrace();
562:
563: // Let's not continue as if nothing is wrong.
564: throw new SqlToolException(CONNECTERR_EXITVAL, rb
565: .getString(SqltoolRB.CONNECTION_FAIL, conData.url,
566: conData.username, e.getMessage()));
567: }
568:
569: File[] emptyFileArray = {};
570: File[] singleNullFileArray = { null };
571: File autoFile = null;
572:
573: if (interactive && !noautoFile) {
574: autoFile = new File(System.getProperty("user.home")
575: + "/auto.sql");
576:
577: if ((!autoFile.isFile()) || !autoFile.canRead()) {
578: autoFile = null;
579: }
580: }
581:
582: if (scriptFiles == null) {
583:
584: // I.e., if no SQL files given on command-line.
585: // Input file list is either nothing or {null} to read stdin.
586: scriptFiles = (noinput ? emptyFileArray
587: : singleNullFileArray);
588: }
589:
590: int numFiles = scriptFiles.length;
591:
592: if (tmpFile != null) {
593: numFiles += 1;
594: }
595:
596: if (autoFile != null) {
597: numFiles += 1;
598: }
599:
600: SqlFile[] sqlFiles = new SqlFile[numFiles];
601: Map userVars = new HashMap();
602:
603: if (varSettings != null)
604: try {
605: varParser(varSettings, userVars, false);
606: } catch (PrivateException pe) {
607: throw new SqlToolException(RCERR_EXITVAL, pe
608: .getMessage());
609: }
610:
611: // We print version before execing this one.
612: int interactiveFileIndex = -1;
613:
614: try {
615: int fileIndex = 0;
616:
617: if (autoFile != null) {
618: sqlFiles[fileIndex++] = new SqlFile(autoFile, false,
619: userVars);
620: }
621:
622: if (tmpFile != null) {
623: sqlFiles[fileIndex++] = new SqlFile(tmpFile, false,
624: userVars);
625: }
626:
627: for (int j = 0; j < scriptFiles.length; j++) {
628: if (interactiveFileIndex < 0 && interactive) {
629: interactiveFileIndex = fileIndex;
630: }
631:
632: sqlFiles[fileIndex++] = new SqlFile(scriptFiles[j],
633: interactive, userVars);
634: }
635: } catch (IOException ioe) {
636: try {
637: conn.close();
638: } catch (Exception e) {
639: }
640:
641: throw new SqlToolException(FILEERR_EXITVAL, ioe
642: .getMessage());
643: }
644:
645: try {
646: for (int j = 0; j < sqlFiles.length; j++) {
647: if (j == interactiveFileIndex) {
648: System.out.print("SqlTool v. " + revnum
649: + ". ");
650: }
651:
652: sqlFiles[j].execute(conn, coeOverride);
653: }
654: // Following two Exception types are handled properly inside of
655: // SqlFile. We just need to return an appropriate error status.
656: } catch (SqlToolError ste) {
657: throw new SqlToolException(SQLTOOLERR_EXITVAL);
658: } catch (SQLException se) {
659: // SqlTool will only throw an SQLException if it is in
660: // "\c false" mode.
661: throw new SqlToolException(SQLERR_EXITVAL);
662: } finally {
663: try {
664: conn.close();
665: } catch (Exception e) {
666: }
667: }
668:
669: // Taking file removal out of final block because this is good debug
670: // info to keep around if the program aborts.
671: if (tmpFile != null && !tmpFile.delete()) {
672: System.err.println(conData.url
673: + rb.getString(SqltoolRB.TEMPFILE_REMOVAL_FAIL,
674: tmpFile.toString()));
675: }
676: }
677: }
|