Java > Open Source Codes > org > hsqldb > util > SqlTool


1 /* Copyright (c) 2001-2005, The HSQL Development Group
2  * All rights reserved.
3  *
4  * Redistribution and use in source and binary forms, with or without
5  * modification, are permitted provided that the following conditions are met:
6  *
7  * Redistributions of source code must retain the above copyright notice, this
8  * list of conditions and the following disclaimer.
9  *
10  * Redistributions in binary form must reproduce the above copyright notice,
11  * this list of conditions and the following disclaimer in the documentation
12  * and/or other materials provided with the distribution.
13  *
14  * Neither the name of the HSQL Development Group nor the names of its
15  * contributors may be used to endorse or promote products derived from this
16  * software without specific prior written permission.
17  *
18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
29  */
30
31
32 package org.hsqldb.util;
33
34 import java.io.BufferedReader ;
35 import java.io.File ;
36 import java.io.IOException ;
37 import java.io.InputStreamReader ;
38 import java.sql.Connection ;
39 import java.sql.DatabaseMetaData ;
40 import java.sql.SQLException ;
41 import java.util.HashMap ;
42 import java.util.Map ;
43 import java.util.StringTokenizer ;
44
45 /* $Id: SqlTool.java,v 1.55 2006/07/27 20:04:31 fredt Exp $ */
46
47 /**
48  * Sql Tool. A command-line and/or interactive SQL tool.
49  * (Note: For every Javadoc block comment, I'm using a single blank line
50  * immediately after the description, just like's Sun's examples in
51  * their Coding Conventions document).
52  *
53  * See JavaDocs for the main method for syntax of how to run.
54  *
55  * @see #main()
56  * @version $Revision: 1.55 $
57  * @author Blaine Simpson unsaved@users
58  */
59 public class SqlTool {
60
61     private static final String DEFAULT_RCFILE =
62         System.getProperty("user.home") + "/sqltool.rc";
63     private static Connection conn;
64
65     // N.b. the following is static!
66 private static boolean noexit; // Whether System.exit() may be called.
67 private static String revnum = null;
68
69     /**
70      * The configuration identifier to use when connection parameters are
71      * specified on the command line
72      */
73     private static String CMDLINE_ID = "cmdline";
74
75     static {
76         revnum = "$Revision: 1.55 $".substring("$Revision: ".length(),
77                                                "$Revision: 1.55 $".length()
78                                                - 2);
79     }
80
81     private static final String SYNTAX_MESSAGE =
82         "Usage: java [-Dsqlfile.X=Y...] org.hsqldb.util.SqlTool \\\n"
83         + " [--optname [optval...]] urlid [file1.sql...]\n"
84         + "where arguments are:\n"
85         + " --help Displays this message\n"
86         + " --list List urlids in the rc file\n"
87         + " --noInput Do not read stdin (default if sql file given\n"
88         + " or --sql switch used).\n"
89         + " --stdInput Read stdin IN ADDITION to sql files/--sql input\n"
90         + " --inlineRc URL=val1,USER=val2[,DRIVER=val3][,CHARSET=val4][,TRUST=val5]\n"
91         + " Inline RC file variables\n"
92         + " --debug Print Debug info to stderr\n"
93         + " --noAutoFile Do not execute auto.sql from home dir\n"
94         + " --autoCommit Auto-commit JDBC DML commands\n"
95         + " --sql \"SQL; Statements\" Execute given SQL instead of stdin (before\n"
96         + " SQL files if any are specified) where \"SQL\"\n"
97         + " consists of SQL command(s). See the Guide.\n"
98         + " --rcFile /file/path.rc Connect Info File [$HOME/sqltool.rc]\n"
99         + " --abortOnErr Abort on Error (overrides defaults)\n"
100         + " --continueOnErr Continue on Error (overrides defaults)\n"
101         + " --setVar NAME1=val1[,NAME2=val2...] PL variables\n"
102         + " --driver a.b.c.Driver JDBC driver class ["
103         + RCData.DEFAULT_JDBC_DRIVER + "]\n"
104         + " urlid ID of url/userame/password in rcfile\n"
105         + " file1.sql... SQL files to be executed [stdin]\n"
106         + " "
107         + "(Use '-' for non-interactively stdin).\n"
108         + "See the SqlTool Manual for the supported sqltool.* System Properties.\n"
109         + "SqlTool v. " + revnum + ".";
110
111     /** Utility nested class for internal use. */
112     private static class BadCmdline extends Exception {}
113     ;
114
115     /** Utility object for internal use. */
116     private static BadCmdline bcl = new BadCmdline();
117
118     /** Nested class for external callers of SqlTool.main() */
119     public static class SqlToolException extends Exception {
120
121         public SqlToolException() {
122             super();
123         }
124
125         public SqlToolException(String s) {
126             super(s);
127         }
128     }
129
130     /**
131      * Exit the main() method by either throwing an exception or exiting JVM.
132      *
133      * Call return() right after you call this method, because this method
134      * will not exit if (noexit is true && retval == 0).
135      */
136     private static void exitMain(int retval) throws SqlToolException {
137         exitMain(retval, null);
138     }
139
140     /**
141      * Exit the main() method by either throwing an exception or exiting JVM.
142      *
143      * Call return() right after you call this method, because this method
144      * will not exit if (noexit is true && retval == 0).
145      */
146     private static void exitMain(int retval,
147                                  String msg) throws SqlToolException {
148
149         if (noexit) {
150             if (retval == 0) {
151                 return;
152             } else if (msg == null) {
153                 throw new SqlToolException();
154             } else {
155                 throw new SqlToolException(msg);
156             }
157         } else {
158             if (msg != null) {
159                 ((retval == 0) ? System.out
160                                : System.err).println(msg);
161             }
162
163             System.exit(retval);
164         }
165     }
166
167     /**
168      * Prompt the user for a password.
169      *
170      * @param username The user the password is for
171      * @return The password the user entered
172      */
173     private static String promptForPassword(String username)
174     throws SqlToolException {
175
176         BufferedReader console;
177         String password;
178
179         password = null;
180
181         try {
182             console = new BufferedReader (new InputStreamReader (System.in));
183
184             // Prompt for password
185 System.out.print(username + "'s password: ");
186
187             // Read the password from the command line
188 password = console.readLine();
189
190             if (password == null) {
191                 password = "";
192             } else {
193                 password = password.trim();
194             }
195         } catch (IOException e) {
196             exitMain(30,
197                      "Error while reading password from console: "
198                      + e.getMessage());
199         }
200
201         return password;
202     }
203
204     /**
205      * Parses a comma delimited string of name value pairs into a
206      * <code>Map</code> object.
207      *
208      * @param varString The string to parse
209      * @param varMap The map to save the pared values into
210      * @param lowerCaseKeys Set to <code>true</code> if the map keys should be
211      * converted to lower case
212      */
213     private static void varParser(String varString, Map varMap,
214                                   boolean lowerCaseKeys)
215                                   throws SqlToolException {
216
217         int equals;
218         String curSetting;
219         String var;
220         String val;
221         StringTokenizer allvars;
222
223         if ((varMap == null) || (varString == null)) {
224             return;
225         }
226
227         allvars = new StringTokenizer (varString, ",");
228
229         while (allvars.hasMoreTokens()) {
230             curSetting = allvars.nextToken().trim();
231             equals = curSetting.indexOf('=');
232
233             if (equals < 1) {
234                 throw new SqlToolException(
235                     "Var settings not of format NAME=var[,...]");
236             }
237
238             var = curSetting.substring(0, equals).trim();
239             val = curSetting.substring(equals + 1).trim();
240
241             if (var.length() < 1 || val.length() < 1) {
242                 throw new SqlToolException(
243                     "Var settings not of format NAME=var[,...]");
244             }
245
246             if (lowerCaseKeys) {
247                 var = var.toLowerCase();
248             }
249
250             varMap.put(var, val);
251         }
252     }
253
254     /**
255      * Connect to a JDBC Database and execute the commands given on
256      * stdin or in SQL file(s).
257      * Like most main methods, this is not intended to be thread-safe.
258      *
259      * @param arg Run "java... org.hsqldb.util.SqlTool --help" for syntax.
260      * @throws SqlToolException May be thrown only if the system property
261      * 'sqltool.noexit' is set (to anything).
262      */
263     public static void main(String [] arg) throws SqlToolException {
264
265         /*
266          * The big picture is, we parse input args; load a RCData;
267          * get a JDBC Connection with the RCData; instantiate and
268          * execute as many SqlFiles as we need to.
269          */
270         String rcFile = null;
271         File tmpFile = null;
272         String sqlText = null;
273         String driver = null;
274         String targetDb = null;
275         String varSettings = null;
276         boolean debug = false;
277         File [] scriptFiles = null;
278         int i = -1;
279         boolean listMode = false;
280         boolean interactive = false;
281         boolean noinput = false;
282         boolean noautoFile = false;
283         boolean autoCommit = false;
284         Boolean coeOverride = null;
285         Boolean stdinputOverride = null;
286         String rcParams = null;
287         String rcUrl = null;
288         String rcUsername = null;
289         String rcPassword = null;
290         String rcDriver = null;
291         String rcCharset = null;
292         String rcTruststore = null;
293         Map rcFields = null;
294         String parameter;
295
296         noexit = System.getProperty("sqltool.noexit") != null;
297
298         try {
299             while ((i + 1 < arg.length) && arg[i + 1].startsWith("--")) {
300                 i++;
301
302                 if (arg[i].length() == 2) {
303                     break; // "--"
304 }
305
306                 parameter = arg[i].substring(2).toLowerCase();
307
308                 if (parameter.equals("help")) {
309                     exitMain(0, SYNTAX_MESSAGE);
310
311                     return;
312                 } else if (parameter.equals("abortonerr")) {
313                     if (coeOverride != null) {
314                         exitMain(
315                             0, "Switches '--abortOnErr' and "
316                             + "'--continueOnErr' are mutually exclusive");
317
318                         return;
319                     }
320
321                     coeOverride = Boolean.FALSE;
322                 } else if (parameter.equals("continueonerr")) {
323                     if (coeOverride != null) {
324                         exitMain(
325                             0, "Switches '--abortOnErr' and "
326                             + "'--continueOnErr' are mutually exclusive");
327
328                         return;
329                     }
330
331                     coeOverride = Boolean.TRUE;
332                 } else if (parameter.equals("list")) {
333                     listMode = true;
334                 } else if (parameter.equals("rcfile")) {
335                     if (++i == arg.length) {
336                         throw bcl;
337                     }
338
339                     rcFile = arg[i];
340                 } else if (parameter.equals("setvar")) {
341                     if (++i == arg.length) {
342                         throw bcl;
343                     }
344
345                     varSettings = arg[i];
346                 } else if (parameter.equals("sql")) {
347                     noinput = true; // but turn back on if file "-" specd.
348
349                     if (++i == arg.length) {
350                         throw bcl;
351                     }
352
353                     sqlText = arg[i];
354
355                     if (sqlText.charAt(sqlText.length() - 1) != ';') {
356                         sqlText += ";";
357                     }
358                 } else if (parameter.equals("debug")) {
359                     debug = true;
360                 } else if (parameter.equals("noautofile")) {
361                     noautoFile = true;
362                 } else if (parameter.equals("autocommit")) {
363                     autoCommit = true;
364                 } else if (parameter.equals("stdinput")) {
365                     noinput = false;
366                     stdinputOverride = Boolean.TRUE;
367                 } else if (parameter.equals("noinput")) {
368                     noinput = true;
369                     stdinputOverride = Boolean.FALSE;
370                 } else if (parameter.equals("driver")) {
371                     if (++i == arg.length) {
372                         throw bcl;
373                     }
374
375                     driver = arg[i];
376                 } else if (parameter.equals("inlinerc")) {
377                     if (++i == arg.length) {
378                         throw bcl;
379                     }
380
381                     rcParams = arg[i];
382                 } else {
383                     throw bcl;
384                 }
385             }
386
387             if (!listMode) {
388
389                 // If an inline RC file was specified, don't worry about the targetDb
390 if (rcParams == null) {
391                     if (++i == arg.length) {
392                         throw bcl;
393                     }
394
395                     targetDb = arg[i];
396                 }
397             }
398
399             int scriptIndex = 0;
400
401             if (sqlText != null) {
402                 try {
403                     tmpFile = File.createTempFile("sqltool-", ".sql");
404
405                     //(new java.io.FileWriter(tmpFile)).write(sqlText);
406 java.io.FileWriter fw = new java.io.FileWriter (tmpFile);
407
408                     fw.write("/* " + (new java.util.Date ()) + ". "
409                              + SqlTool.class.getName()
410                              + " command-line SQL. */\n\n");
411                     fw.write(sqlText + '\n');
412                     fw.flush();
413                     fw.close();
414                 } catch (IOException ioe) {
415                     exitMain(4, "Failed to write given sql to temp file: "
416                              + ioe);
417
418                     return;
419                 }
420             }
421
422             if (stdinputOverride != null) {
423                 noinput = !stdinputOverride.booleanValue();
424             }
425
426             interactive = (!noinput) && (arg.length <= i + 1);
427
428             if (arg.length == i + 2 && arg[i + 1].equals("-")) {
429                 if (stdinputOverride == null) {
430                     noinput = false;
431                 }
432             } else if (arg.length > i + 1) {
433
434                 // I.e., if there are any SQL files specified.
435 scriptFiles =
436                     new File [arg.length - i - 1 + ((stdinputOverride == null ||!stdinputOverride.booleanValue()) ? 0
437                                                                                                                  : 1)];
438
439                 if (debug) {
440                     System.err.println("scriptFiles has "
441                                        + scriptFiles.length + " elements");
442                 }
443
444                 while (i + 1 < arg.length) {
445                     scriptFiles[scriptIndex++] = new File (arg[++i]);
446                 }
447
448                 if (stdinputOverride != null
449                         && stdinputOverride.booleanValue()) {
450                     scriptFiles[scriptIndex++] = null;
451                     noinput = true;
452                 }
453             }
454         } catch (BadCmdline bcl) {
455             exitMain(2, SYNTAX_MESSAGE);
456
457             return;
458         }
459
460         RCData conData = null;
461
462         // Use the inline RC file if it was specified
463 if (rcParams != null) {
464             rcFields = new HashMap ();
465
466             try {
467                 varParser(rcParams, rcFields, true);
468             } catch (SqlToolException e) {
469                 exitMain(24, e.getMessage());
470             }
471
472             try {
473                 rcUrl = (String ) rcFields.get("url");
474                 rcUsername = (String ) rcFields.get("user");
475                 rcDriver = (String ) rcFields.get("driver");
476                 rcCharset = (String ) rcFields.get("charset");
477                 rcTruststore = (String ) rcFields.get("truststore");
478                 rcPassword = promptForPassword(rcUsername);
479                 conData = new RCData(CMDLINE_ID, rcUrl, rcUsername,
480                                      rcPassword, rcDriver, rcCharset,
481                                      rcTruststore);
482             } catch (SqlToolException e) {
483                 throw e;
484             } catch (Exception e) {
485                 exitMain(1, "Invalid inline RC file specified: "
486                          + e.getMessage());
487
488                 return;
489             }
490         } else {
491             try {
492                 conData = new RCData(new File ((rcFile == null)
493                                               ? DEFAULT_RCFILE
494                                               : rcFile), targetDb);
495             } catch (Exception e) {
496                 exitMain(
497                     1, "Failed to retrieve connection info for database '"
498                     + targetDb + "': " + e.getMessage());
499
500                 return;
501             }
502         }
503
504         if (listMode) {
505             exitMain(0);
506
507             return;
508         }
509
510         if (debug) {
511             conData.report();
512         }
513
514         try {
515             conn = conData.getConnection(
516                 driver, System.getProperty("sqlfile.charset"),
517                 System.getProperty("javax.net.ssl.trustStore"));
518
519             conn.setAutoCommit(autoCommit);
520
521             DatabaseMetaData md = null;
522
523             if (interactive && (md = conn.getMetaData()) != null) {
524                 System.out.println("JDBC Connection established to a "
525                                    + md.getDatabaseProductName() + " v. "
526                                    + md.getDatabaseProductVersion()
527                                    + " database as '" + md.getUserName()
528                                    + "'.");
529             }
530         } catch (Exception e) {
531             e.printStackTrace();
532
533             // Let's not continue as if nothing is wrong.
534 exitMain(10,
535                      "Failed to get a connection to " + conData.url + " as "
536                      + conData.username + ". " + e.getMessage());
537
538             return;
539         }
540
541         File [] emptyFileArray = {};
542         File [] singleNullFileArray = { null };
543         File autoFile = null;
544
545         if (interactive &&!noautoFile) {
546             autoFile = new File (System.getProperty("user.home")
547                                 + "/auto.sql");
548
549             if ((!autoFile.isFile()) ||!autoFile.canRead()) {
550                 autoFile = null;
551             }
552         }
553
554         if (scriptFiles == null) {
555
556             // I.e., if no SQL files given on command-line.
557 // Input file list is either nothing or {null} to read stdin.
558 scriptFiles = (noinput ? emptyFileArray
559                                    : singleNullFileArray);
560         }
561
562         int numFiles = scriptFiles.length;
563
564         if (tmpFile != null) {
565             numFiles += 1;
566         }
567
568         if (autoFile != null) {
569             numFiles += 1;
570         }
571
572         SqlFile[] sqlFiles = new SqlFile[numFiles];
573         HashMap userVars = new HashMap ();
574
575         if (varSettings != null) {
576             varParser(varSettings, userVars, false);
577         }
578
579         // We print version before execing this one.
580 int interactiveFileIndex = -1;
581
582         try {
583             int fileIndex = 0;
584
585             if (autoFile != null) {
586                 sqlFiles[fileIndex++] = new SqlFile(autoFile, false,
587                                                     userVars);
588             }
589
590             if (tmpFile != null) {
591                 sqlFiles[fileIndex++] = new SqlFile(tmpFile, false, userVars);
592             }
593
594             for (int j = 0; j < scriptFiles.length; j++) {
595                 if (interactiveFileIndex < 0 && interactive) {
596                     interactiveFileIndex = fileIndex;
597                 }
598
599                 sqlFiles[fileIndex++] = new SqlFile(scriptFiles[j],
600                                                     interactive, userVars);
601             }
602         } catch (IOException ioe) {
603             try {
604                 conn.close();
605             } catch (Exception e) {}
606
607             exitMain(2, ioe.getMessage());
608
609             return;
610         }
611
612         int retval = 0; // Value we will return via System.exit().
613
614         try {
615             for (int j = 0; j < sqlFiles.length; j++) {
616                 if (j == interactiveFileIndex) {
617                     System.out.print("SqlTool v. " + revnum
618                                      + ". ");
619                 }
620
621                 sqlFiles[j].execute(conn, coeOverride);
622             }
623         } catch (IOException ioe) {
624             System.err.println("Failed to execute SQL: " + ioe.getMessage());
625
626             retval = 3;
627
628             // These two Exception types are handled properly inside of SqlFile.
629 // We just need to return an appropriate error status.
630 } catch (SqlToolError ste) {
631             retval = 2;
632
633             // Should not be handling SQLExceptions here! SqlFile should handle
634 // them.
635 } catch (SQLException se) {
636             retval = 1;
637         } finally {
638             try {
639                 conn.close();
640             } catch (Exception e) {}
641         }
642
643         // Taking file removal out of final block because this is good debug
644 // info to keep around if the program aborts.
645 if (tmpFile != null &&!tmpFile.delete()) {
646             System.err.println(
647                 "Error occurred while trying to remove temp file '" + tmpFile
648                 + "'");
649         }
650
651         exitMain(retval);
652
653         return;
654     }
655 }
656

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags