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


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.applet.Applet ;
35 import java.io.File ;
36 import java.io.IOException ;
37 import java.sql.Connection ;
38 import java.sql.DatabaseMetaData ;
39 import java.sql.ResultSet ;
40 import java.sql.ResultSetMetaData ;
41 import java.sql.SQLException ;
42 import java.sql.Statement ;
43 import java.util.Vector ;
44 import java.awt.BorderLayout ;
45 import java.awt.Button ;
46 import java.awt.Color ;
47 import java.awt.Dimension ;
48 import java.awt.FileDialog ;
49 import java.awt.Font ;
50 import java.awt.Frame ;
51 import java.awt.Image ;
52 import java.awt.Menu ;
53 import java.awt.MenuBar ;
54 import java.awt.MenuItem ;
55 import java.awt.MenuShortcut ;
56 import java.awt.Panel ;
57 import java.awt.TextArea ;
58 import java.awt.Toolkit ;
59 import java.awt.event.ActionEvent ;
60 import java.awt.event.ActionListener ;
61 import java.awt.event.KeyEvent ;
62 import java.awt.event.KeyListener ;
63 import java.awt.event.WindowEvent ;
64 import java.awt.event.WindowListener ;
65 import java.awt.image.MemoryImageSource ;
66
67 import org.hsqldb.lib.java.JavaSystem;
68
69 // sqlbob@users 20020401 - patch 1.7.0 by sqlbob (RMP) - enhancements
70 // sqlbob@users 20020401 - patch 537501 by ulrivo - command line arguments
71 // sqlbob@users 20020407 - patch 1.7.0 - reengineering
72 // nickferguson@users 20021005 - patch 1.7.1 - enhancements
73
74 /**
75  * AWT Tool for manageing a JDBC database.<p>
76  * <pre>
77  * Usage: java DatabaseManagerSwing [--options]
78  * where options include:
79  * --driver <classname> jdbc driver class
80  * --url <name> jdbc url
81  * --user <name> username used for connection
82  * --password <password> password for this user
83  * --urlid <urlid> get connection info from RC file
84  * --rcfile <file> use instead of default (with urlid)
85  * --dir <path> default directory
86  * --script <file> reads from script file
87  *</pre>
88  * Tue Apr 26 16:38:54 EDT 2005
89  * Switched default switch method from "-switch" to "--switch" because
90  * "-switch" usage is ambiguous as used here. Single switches should
91  * be reserved for single-letter switches which can be mixed like
92  * "-u -r -l" = "-url". -blaine
93  *
94  * @author Thomas Mueller (Hypersonic SQL Group)
95  * @version 1.8.0
96  * @since Hypersonic SQL
97  */
98 public class DatabaseManager extends Applet
99 implements ActionListener , WindowListener , KeyListener {
100
101     private static final String DEFAULT_RCFILE =
102         System.getProperty("user.home") + "/dbmanager.rc";
103     static final String NL = System.getProperty("line.separator");
104     static final int iMaxRecent = 24;
105     Connection cConn;
106     DatabaseMetaData dMeta;
107     Statement sStatement;
108     Menu mRecent;
109     String [] sRecent;
110     int iRecent;
111     TextArea txtCommand;
112     Button butExecute;
113     Button butClear;
114     Tree tTree;
115     Panel pResult;
116     long lTime;
117     int iResult; // 0: grid; 1: text
118 Grid gResult;
119     TextArea txtResult;
120     boolean bHelp;
121     Frame fMain;
122     Image imgEmpty;
123     static boolean bMustExit;
124     String ifHuge = "";
125
126     // (ulrivo): variables set by arguments from the commandline
127 static String defDriver = "org.hsqldb.jdbcDriver";
128     static String defURL = "jdbc:hsqldb:.";
129     static String defUser = "sa";
130     static String defPassword = "";
131     static String defScript;
132     static String defDirectory;
133
134     /**
135      * Method declaration
136      *
137      *
138      * @param c
139      */
140     public void connect(Connection c) {
141
142         if (c == null) {
143             return;
144         }
145
146         if (cConn != null) {
147             try {
148                 cConn.close();
149             } catch (SQLException e) {}
150         }
151
152         cConn = c;
153
154         try {
155             dMeta = cConn.getMetaData();
156             sStatement = cConn.createStatement();
157
158             refreshTree();
159         } catch (SQLException e) {
160             e.printStackTrace();
161         }
162     }
163
164     /**
165      * Method declaration
166      *
167      */
168     public void init() {
169
170         DatabaseManager m = new DatabaseManager();
171
172         m.main();
173
174         try {
175             m.connect(ConnectionDialog.createConnection(defDriver, defURL,
176                     defUser, defPassword));
177             m.insertTestData();
178             m.refreshTree();
179         } catch (Exception e) {
180             e.printStackTrace();
181         }
182     }
183
184     /**
185      * Method declaration
186      *
187      *
188      * @param arg
189      */
190     public static void main(String [] arg) {
191
192         System.getProperties().put("sun.java2d.noddraw", "true");
193
194         // (ulrivo): read all arguments from the command line
195 String lowerArg;
196         String urlid = null;
197         String rcFile = null;
198         boolean autoConnect = false;
199         boolean urlidConnect = false;
200
201         bMustExit = true;
202
203         for (int i = 0; i < arg.length; i++) {
204             lowerArg = arg[i].toLowerCase();
205
206             if (lowerArg.length() > 1 && lowerArg.charAt(1) == '-') {
207                 lowerArg = lowerArg.substring(1);
208             }
209
210             i++;
211
212             if (i == arg.length) {
213                 showUsage();
214
215                 return;
216             }
217
218             if (lowerArg.equals("-driver")) {
219                 defDriver = arg[i];
220                 autoConnect = true;
221             } else if (lowerArg.equals("-url")) {
222                 defURL = arg[i];
223                 autoConnect = true;
224             } else if (lowerArg.equals("-user")) {
225                 defUser = arg[i];
226                 autoConnect = true;
227             } else if (lowerArg.equals("-password")) {
228                 defPassword = arg[i];
229                 autoConnect = true;
230             } else if (lowerArg.equals("-urlid")) {
231                 urlid = arg[i];
232                 urlidConnect = true;
233             } else if (lowerArg.equals("-rcfile")) {
234                 rcFile = arg[i];
235                 urlidConnect = true;
236             } else if (lowerArg.equals("-dir")) {
237                 defDirectory = arg[i];
238             } else if (lowerArg.equals("-script")) {
239                 defScript = arg[i];
240             } else if (lowerArg.equals("-noexit")) {
241                 bMustExit = false;
242
243                 i--;
244             } else {
245                 showUsage();
246
247                 return;
248             }
249         }
250
251         DatabaseManager m = new DatabaseManager();
252
253         m.main();
254
255         Connection c = null;
256
257         try {
258             if (autoConnect && urlidConnect) {
259                 throw new IllegalArgumentException (
260                     "You may not specify both (urlid) AND (url/user/password).");
261             }
262
263             if (autoConnect) {
264                 c = ConnectionDialog.createConnection(defDriver, defURL,
265                                                       defUser, defPassword);
266             } else if (urlidConnect) {
267                 if (urlid == null) {
268                     throw new IllegalArgumentException (
269                         "You must specify an 'urlid' to use an RC file");
270                 }
271
272                 autoConnect = true;
273                 c = (new RCData(new File ((rcFile == null) ? DEFAULT_RCFILE
274                                                           : rcFile), urlid).getConnection(
275                                                           null, System.getProperty(
276                                                               "sqlfile.charset"), System.getProperty(
277                                                                   "javax.net.ssl.trustStore")));
278             } else {
279                 c = ConnectionDialog.createConnection(m.fMain, "Connect");
280             }
281         } catch (Exception e) {
282             e.printStackTrace();
283         }
284
285         if (c == null) {
286             return;
287         }
288
289         m.connect(c);
290     }
291
292     private static void showUsage() {
293
294         System.out.println(
295             "Usage: java DatabaseManager [--options]\n"
296             + "where options include:\n"
297             + " --driver <classname> jdbc driver class\n"
298             + " --url <name> jdbc url\n"
299             + " --user <name> username used for connection\n"
300             + " --password <password> password for this user\n"
301             + " --urlid <urlid> use url/user/password/driver in rc file\n"
302             + " --rcfile <file> (defaults to 'dbmanager.rc' in home dir)\n"
303             + " --dir <path> default directory\n"
304             + " --script <file> reads from script file\n"
305             + " --noexit do not call system.exit()\n"
306             + "(Single-hypen switches like '-driver' are also supported)");
307     }
308
309     /**
310      * Method declaration
311      *
312      */
313     void insertTestData() {
314
315         try {
316             DatabaseManagerCommon.createTestTables(sStatement);
317             refreshTree();
318             txtCommand.setText(
319                 DatabaseManagerCommon.createTestData(sStatement));
320             refreshTree();
321
322             for (int i = 0; i < DatabaseManagerCommon.testDataSql.length;
323                     i++) {
324                 addToRecent(DatabaseManagerCommon.testDataSql[i]);
325             }
326
327             execute();
328         } catch (SQLException e) {
329             e.printStackTrace();
330         }
331     }
332
333     /**
334      * Method declaration
335      *
336      */
337     public void main() {
338
339         fMain = new Frame ("HSQL Database Manager");
340         imgEmpty = createImage(new MemoryImageSource (2, 2, new int[4 * 4], 2,
341                 2));
342
343         fMain.setIconImage(imgEmpty);
344         fMain.addWindowListener(this);
345
346         MenuBar bar = new MenuBar ();
347
348         // used shortcuts: CERGTSIUDOLM
349 String [] fitems = {
350             "-Connect...", "--", "-Open Script...", "-Save Script...",
351             "-Save Result...", "-Save Result csv...", "--", "-Exit"
352         };
353
354         addMenu(bar, "File", fitems);
355
356         String [] vitems = {
357             "RRefresh Tree", "--", "GResults in Grid", "TResults in Text",
358             "--", "1Shrink Tree", "2Enlarge Tree", "3Shrink Command",
359             "4Enlarge Command"
360         };
361
362         addMenu(bar, "View", vitems);
363
364         String [] sitems = {
365             "SSELECT", "IINSERT", "UUPDATE", "DDELETE", "--", "-CREATE TABLE",
366             "-DROP TABLE", "-CREATE INDEX", "-DROP INDEX", "--",
367             "-CHECKPOINT", "-SCRIPT", "-SET", "-SHUTDOWN", "--",
368             "-Test Script"
369         };
370
371         addMenu(bar, "Command", sitems);
372
373         Menu recent = new Menu ("Recent");
374
375         mRecent = new Menu ("Recent");
376
377         bar.add(mRecent);
378
379         String [] soptions = {
380             "-AutoCommit on", "-AutoCommit off", "OCommit", "LRollback", "--",
381             "-Disable MaxRows", "-Set MaxRows to 100", "--", "-Logging on",
382             "-Logging off", "--", "-Insert test data"
383         };
384
385         addMenu(bar, "Options", soptions);
386
387         String [] stools = {
388             "-Dump", "-Restore", "-Transfer"
389         };
390
391         addMenu(bar, "Tools", stools);
392         fMain.setMenuBar(bar);
393         fMain.setSize(640, 480);
394         fMain.add("Center", this);
395         initGUI();
396
397         sRecent = new String [iMaxRecent];
398
399         Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
400         Dimension size = fMain.getSize();
401
402         // (ulrivo): full size on screen with less than 640 width
403 if (d.width >= 640) {
404             fMain.setLocation((d.width - size.width) / 2,
405                               (d.height - size.height) / 2);
406         } else {
407             fMain.setLocation(0, 0);
408             fMain.setSize(d);
409         }
410
411         fMain.show();
412
413         // (ulrivo): load query from command line
414 if (defScript != null) {
415             if (defDirectory != null) {
416                 defScript = defDirectory + File.separator + defScript;
417             }
418
419             txtCommand.setText(DatabaseManagerCommon.readFile(defScript));
420         }
421
422         txtCommand.requestFocus();
423     }
424
425     /**
426      * Method declaration
427      *
428      *
429      * @param b
430      * @param name
431      * @param items
432      */
433     void addMenu(MenuBar b, String name, String [] items) {
434
435         Menu menu = new Menu (name);
436
437         addMenuItems(menu, items);
438         b.add(menu);
439     }
440
441     /**
442      * Method declaration
443      *
444      *
445      * @param f
446      * @param m
447      */
448     void addMenuItems(Menu f, String [] m) {
449
450         for (int i = 0; i < m.length; i++) {
451             MenuItem item = new MenuItem (m[i].substring(1));
452             char c = m[i].charAt(0);
453
454             if (c != '-') {
455                 item.setShortcut(new MenuShortcut (c));
456             }
457
458             item.addActionListener(this);
459             f.add(item);
460         }
461     }
462
463     /**
464      * Method declaration
465      *
466      *
467      * @param k
468      */
469     public void keyPressed(KeyEvent k) {}
470
471     /**
472      * Method declaration
473      *
474      *
475      * @param k
476      */
477     public void keyReleased(KeyEvent k) {}
478
479     /**
480      * Method declaration
481      *
482      *
483      * @param k
484      */
485     public void keyTyped(KeyEvent k) {
486
487         if (k.getKeyChar() == '\n' && k.isControlDown()) {
488             k.consume();
489             execute();
490         }
491     }
492
493     /**
494      * Method declaration
495      *
496      *
497      * @param ev
498      */
499     public void actionPerformed(ActionEvent ev) {
500
501         String s = ev.getActionCommand();
502
503         if (s == null) {
504             if (ev.getSource() instanceof MenuItem ) {
505                 MenuItem i;
506
507                 s = ((MenuItem ) ev.getSource()).getLabel();
508             }
509         }
510
511         if (s == null) {}
512         else if (s.equals("Execute")) {
513             execute();
514         } else if (s.equals("Clear")) {
515             clear();
516         } else if (s.equals("Exit")) {
517             windowClosing(null);
518         } else if (s.equals("Transfer")) {
519             Transfer.work(null);
520         } else if (s.equals("Dump")) {
521             Transfer.work(new String []{ "-d" });
522         } else if (s.equals("Restore")) {
523             Transfer.work(new String []{ "-r" });
524         } else if (s.equals("Logging on")) {
525             JavaSystem.setLogToSystem(true);
526         } else if (s.equals("Logging off")) {
527             JavaSystem.setLogToSystem(false);
528         } else if (s.equals("Refresh Tree")) {
529             refreshTree();
530         } else if (s.startsWith("#")) {
531             int i = Integer.parseInt(s.substring(1));
532
533             txtCommand.setText(sRecent[i]);
534         } else if (s.equals("Connect...")) {
535             connect(ConnectionDialog.createConnection(fMain, "Connect"));
536             refreshTree();
537         } else if (s.equals("Results in Grid")) {
538             iResult = 0;
539
540             pResult.removeAll();
541             pResult.add("Center", gResult);
542             pResult.doLayout();
543         } else if (s.equals("Open Script...")) {
544             FileDialog f = new FileDialog (fMain, "Open Script",
545                                           FileDialog.LOAD);
546
547             // (ulrivo): set default directory if set from command line
548 if (defDirectory != null) {
549                 f.setDirectory(defDirectory);
550             }
551
552             f.show();
553
554             String file = f.getFile();
555
556             if (file != null) {
557                 StringBuffer buf = new StringBuffer ();
558
559                 ifHuge = DatabaseManagerCommon.readFile(f.getDirectory()
560                         + file);
561
562                 if (4096 <= ifHuge.length()) {
563                     buf.append(
564                         "This huge file cannot be edited. Please execute\n");
565                     txtCommand.setText(buf.toString());
566                 } else {
567                     txtCommand.setText(ifHuge);
568                 }
569             }
570         } else if (s.equals("Save Script...")) {
571             FileDialog f = new FileDialog (fMain, "Save Script",
572                                           FileDialog.SAVE);
573
574             // (ulrivo): set default directory if set from command line
575 if (defDirectory != null) {
576                 f.setDirectory(defDirectory);
577             }
578
579             f.show();
580
581             String file = f.getFile();
582
583             if (file != null) {
584                 DatabaseManagerCommon.writeFile(f.getDirectory() + file,
585                                                 txtCommand.getText());
586             }
587         } else if (s.equals("Save Result csv...")) {
588             FileDialog f = new FileDialog (fMain, "Save Result CSV",
589                                           FileDialog.SAVE);
590
591             // (ulrivo): set default directory if set from command line
592 if (defDirectory != null) {
593                 f.setDirectory(defDirectory);
594             }
595
596             f.show();
597
598             String dir = f.getDirectory();
599             String file = f.getFile();
600
601             if (dir != null) {
602                 file = dir + "/" + file;
603             }
604
605             if (file != null) {
606                 showResultInText();
607                 saveAsCsv(file);
608             }
609         } else if (s.equals("Save Result...")) {
610             FileDialog f = new FileDialog (fMain, "Save Result",
611                                           FileDialog.SAVE);
612
613             // (ulrivo): set default directory if set from command line
614 if (defDirectory != null) {
615                 f.setDirectory(defDirectory);
616             }
617
618             f.show();
619
620             String file = f.getFile();
621
622             if (file != null) {
623                 showResultInText();
624                 DatabaseManagerCommon.writeFile(f.getDirectory() + file,
625                                                 txtResult.getText());
626             }
627         } else if (s.equals("Results in Text")) {
628             iResult = 1;
629
630             pResult.removeAll();
631             pResult.add("Center", txtResult);
632             pResult.doLayout();
633             showResultInText();
634         } else if (s.equals("AutoCommit on")) {
635             try {
636                 cConn.setAutoCommit(true);
637             } catch (SQLException e) {}
638         } else if (s.equals("AutoCommit off")) {
639             try {
640                 cConn.setAutoCommit(false);
641             } catch (SQLException e) {}
642         } else if (s.equals("Enlarge Tree")) {
643             Dimension d = tTree.getMinimumSize();
644
645             d.width += 20;
646
647             tTree.setMinimumSize(d);
648             fMain.pack();
649         } else if (s.equals("Shrink Tree")) {
650             Dimension d = tTree.getMinimumSize();
651
652             d.width -= 20;
653
654             if (d.width >= 0) {
655                 tTree.setMinimumSize(d);
656             }
657
658             fMain.pack();
659         } else if (s.equals("Enlarge Command")) {
660             txtCommand.setRows(txtCommand.getRows() + 1);
661             fMain.pack();
662         } else if (s.equals("Shrink Command")) {
663             int i = txtCommand.getRows() - 1;
664
665             txtCommand.setRows(i < 1 ? 1
666                                      : i);
667             fMain.pack();
668         } else if (s.equals("Commit")) {
669             try {
670                 cConn.commit();
671             } catch (SQLException e) {}
672         } else if (s.equals("Insert test data")) {
673             insertTestData();
674         } else if (s.equals("Rollback")) {
675             try {
676                 cConn.rollback();
677             } catch (SQLException e) {}
678         } else if (s.equals("Disable MaxRows")) {
679             try {
680                 sStatement.setMaxRows(0);
681             } catch (SQLException e) {}
682         } else if (s.equals("Set MaxRows to 100")) {
683             try {
684                 sStatement.setMaxRows(100);
685             } catch (SQLException e) {}
686         } else if (s.equals("SELECT")) {
687             showHelp(DatabaseManagerCommon.selectHelp);
688         } else if (s.equals("INSERT")) {
689             showHelp(DatabaseManagerCommon.insertHelp);
690         } else if (s.equals("UPDATE")) {
691             showHelp(DatabaseManagerCommon.updateHelp);
692         } else if (s.equals("DELETE")) {
693             showHelp(DatabaseManagerCommon.deleteHelp);
694         } else if (s.equals("CREATE TABLE")) {
695             showHelp(DatabaseManagerCommon.createTableHelp);
696         } else if (s.equals("DROP TABLE")) {
697             showHelp(DatabaseManagerCommon.dropTableHelp);
698         } else if (s.equals("CREATE INDEX")) {
699             showHelp(DatabaseManagerCommon.createIndexHelp);
700         } else if (s.equals("DROP INDEX")) {
701             showHelp(DatabaseManagerCommon.dropIndexHelp);
702         } else if (s.equals("CHECKPOINT")) {
703             showHelp(DatabaseManagerCommon.checkpointHelp);
704         } else if (s.equals("SCRIPT")) {
705             showHelp(DatabaseManagerCommon.scriptHelp);
706         } else if (s.equals("SHUTDOWN")) {
707             showHelp(DatabaseManagerCommon.shutdownHelp);
708         } else if (s.equals("SET")) {
709             showHelp(DatabaseManagerCommon.setHelp);
710         } else if (s.equals("Test Script")) {
711             showHelp(DatabaseManagerCommon.testHelp);
712         }
713     }
714
715     /**
716      * Method declaration
717      *
718      *
719      * @param s
720      * @param help
721      */
722     void showHelp(String [] help) {
723
724         txtCommand.setText(help[0]);
725         txtResult.setText(help[1]);
726
727         bHelp = true;
728
729         pResult.removeAll();
730         pResult.add("Center", txtResult);
731         pResult.doLayout();
732         txtCommand.requestFocus();
733         txtCommand.setCaretPosition(help[0].length());
734     }
735
736     /**
737      * Method declaration
738      *
739      *
740      * @param e
741      */
742     public void windowActivated(WindowEvent e) {}
743
744     /**
745      * Method declaration
746      *
747      *
748      * @param e
749      */
750     public void windowDeactivated(WindowEvent e) {}
751
752     /**
753      * Method declaration
754      *
755      *
756      * @param e
757      */
758     public void windowClosed(WindowEvent e) {}
759
760     /**
761      * Method declaration
762      *
763      *
764      * @param ev
765      */
766     public void windowClosing(WindowEvent ev) {
767
768         try {
769             cConn.close();
770         } catch (Exception e) {}
771
772         fMain.dispose();
773
774         if (bMustExit) {
775             System.exit(0);
776         }
777     }
778
779     /**
780      * Method declaration
781      *
782      *
783      * @param e
784      */
785     public void windowDeiconified(WindowEvent e) {}
786
787     /**
788      * Method declaration
789      *
790      *
791      * @param e
792      */
793     public void windowIconified(WindowEvent e) {}
794
795     /**
796      * Method declaration
797      *
798      *
799      * @param e
800      */
801     public void windowOpened(WindowEvent e) {}
802
803     /**
804      * Method declaration
805      * Clear SQL Statements.
806      */
807     void clear() {
808
809         ifHuge = "";
810
811         txtCommand.setText(ifHuge);
812     }
813
814