databasemanager.java

Java写的含有一个jdbc驱动的小型数据库数据库引擎

/*
 * DatabaseManager.java
 *
 * Copyright (c) 2001, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE FOR
 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This package is based on HypersonicSQL, originally developed by Thomas Mueller.
 *
 */
package org.hsqldb.util;

import java.awt.*;
import java.awt.event.*;
import java.awt.image.*;
import java.applet.*;
import java.sql.*;
import java.net.*;
import java.io.*;
import java.util.*;

/**
 * Class declaration
 *
 *
 * @version 1.0.0.1
 */
public class DatabaseManager extends Applet implements ActionListener,
	WindowListener, KeyListener {
    final static int iMaxRecent = 24;
    Connection       cConn;
    DatabaseMetaData dMeta;
    Statement	     sStatement;
    Menu	     mRecent;
    String	     sRecent[];
    int		     iRecent;
    TextArea	     txtCommand;
    Button	     butExecute;
    Tree	     tTree;
    Panel	     pResult;
    long	     lTime;
    int		     iResult;    // 0: grid; 1: text
    Grid	     gResult;
    TextArea	     txtResult;
    boolean	     bHelp;
    Frame	     fMain;
    Image	     imgEmpty;
    static boolean   bMustExit;

    /**
     * Method declaration
     *
     *
     * @param c
     */
    void connect(Connection c) {
	if (c == null) {
	    return;
	}

	if (cConn != null) {
	    try {
		cConn.close();
	    } catch (SQLException e) {}
	}

	cConn = c;

	try {
	    dMeta = cConn.getMetaData();
	    sStatement = cConn.createStatement();
	} catch (SQLException e) {
	    e.printStackTrace();
	}

	refreshTree();
    }

    /**
     * Method declaration
     *
     */
    public void init() {
	DatabaseManager m = new DatabaseManager();

	m.main();

	try {
	    m.connect(ConnectionDialog.createConnection("org.hsqldb.jdbcDriver",
							"jdbc:hsqldb:.",
							"sa", ""));
	    m.insertTestData();
	    m.refreshTree();
	} catch (Exception e) {
	    e.printStackTrace();
	}
    }

    /**
     * Method declaration
     *
     *
     * @param arg
     */
    public static void main(String arg[]) {
	bMustExit = true;

	DatabaseManager m = new DatabaseManager();

	m.main();

	Connection c = ConnectionDialog.createConnection(m.fMain, "Connect");

	if (c == null) {
	    return;
	}

	m.connect(c);
    }

    /**
     * Method declaration
     *
     */
    void insertTestData() {
	String demo[] = {
	    "DROP TABLE Address", "DROP TABLE Product",
	    "DROP TABLE Document", "DROP TABLE Position",
	    "CREATE TABLE Address(ID INTEGER PRIMARY KEY,FirstName VARCHAR(255),"
	    + "LastName VARCHAR(255),Street VARCHAR(255),City VARCHAR(255))",
		    "CREATE TABLE Product(ID INTEGER PRIMARY KEY,Name VARCHAR(255),"
		    + "Cost DECIMAL)",
				       "CREATE TABLE Document(ID INTEGER PRIMARY KEY,AddressID INTEGER,"
				       + "Total DECIMAL)",
				       "CREATE TABLE Position(DocumentID INTEGER,Position INTEGER,"
				       + "ProductID INTEGER,Quantity INTEGER,Price DECIMAL,"
				       + "PRIMARY KEY(DocumentID,Position))"
	};
	String name[] = {
	    "White", "Karsen", "Smith", "Ringer", "May", "King", "Fuller",
	    "Miller", "Ott", "Sommer", "Schneider", "Steel", "Peterson",
	    "Heiniger", "Clancy"
	};
	String firstname[] = {
	    "Mary", "James", "Anne", "George", "Sylvia", "Robert", "Janet",
	    "Michael", "Andrew", "Bill", "Susanne", "Laura", "Bob", "Julia",
	    "John"
	};
	String street[] = {
	    "Upland Pl.", "College Av.", "- 20th Ave.", "Seventh Av."
	};
	String city[] = {
	    "New York", "Dallas", "Boston", "Chicago", "Seattle",
	    "San Francisco", "Berne", "Oslo", "Paris", "Lyon", "Palo Alto",
	    "Olten"
	};
	String product[] = {
	    "Iron", "Ice Tea", "Clock", "Chair", "Telephone", "Shoe"
	};

	try {
	    for (int i = 0; i < demo.length; i++) {

		// drop table may fail
		try {
		    sStatement.execute(demo[i]);
		} catch (SQLException e) {}
	    }

	    refreshTree();

	    int max = 50;

	    for (int i = 0; i < max; i++) {
		sStatement.execute("INSERT INTO Address VALUES(" + i + ",'"
				   + random(firstname) + "','" + random(name)
				   + "','" + random(554) + " "
				   + random(street) + "','" + random(city)
				   + "')");
		sStatement.execute("INSERT INTO Product VALUES(" + i + ",'"
				   + random(product) + " " + random(product)
				   + "'," + (20 + 2 * random(120)) + ")");
		sStatement.execute("INSERT INTO Document VALUES(" + i + ","
				   + random(max) + ",0.0)");

		for (int j = random(20) + 2; j >= 0; j--) {
		    sStatement.execute("INSERT INTO Position VALUES(" + i
				       + "," + j + "," + random(max) + ","
				       + (1 + random(24)) + ",1.5)");
		}
	    }

	    sStatement.execute("UPDATE Product SET Cost=ROUND(Cost*.1,2)");
	    sStatement.execute("UPDATE Position SET Price=Price*"
			       + "SELECT Cost FROM Product prod WHERE ProductID=prod.ID");
	    sStatement.execute("UPDATE Document SET Total=SELECT SUM(Price*"
			       + "Quantity) FROM Position WHERE DocumentID=Document.ID");

	    String recent[] = {
		"SELECT * FROM Product", "SELECT * FROM Document",
		"SELECT * FROM Position",
		"SELECT * FROM Address a\nINNER JOIN Document d ON a.ID=d.AddressID",
		"SELECT * FROM Document d\nINNER JOIN Position p ON d.ID=p.DocumentID",
		"SELECT * FROM Address WHERE Street LIKE '1%' ORDER BY Lastname"
	    };

	    for (int i = 0; i < recent.length; i++) {
		addToRecent(recent[i]);
	    }

	    refreshTree();
	    txtCommand.setText("SELECT * FROM Address");
	    execute();
	} catch (SQLException e) {
	    e.printStackTrace();
	}
    }

    static Random rRandom = new Random(100);

    /**
     * Method declaration
     *
     *
     * @param s
     *
     * @return
     */
    public static String random(String s[]) {
	return s[random(s.length)];
    }

    /**
     * Method declaration
     *
     *
     * @param i
     *
     * @return
     */
    public static int random(int i) {
	i = rRandom.nextInt() % i;

	return i < 0 ? -i : i;
    }

    /**
     * Method declaration
     *
     */
    void main() {
	fMain = new Frame("HSQL Database Manager");
	imgEmpty = createImage(new MemoryImageSource(2, 2, new int[4 * 4], 2,
		2));

	fMain.setIconImage(imgEmpty);
	fMain.addWindowListener(this);

	MenuBar bar = new MenuBar();

	// used shortcuts: CERGTSIUDOLM
	String  fitems[] = {
	    "-Connect...", "--", "-Open Script...", "-Save Script...",
	    "-Save Result...", "--", "-Exit"
	};

	addMenu(bar, "File", fitems);

	String vitems[] = {
	    "RRefresh Tree", "--", "GResults in Grid", "TResults in Text",
	    "--", "1Shrink Tree", "2Enlarge Tree", "3Shrink Command",
	    "4Enlarge Command"
	};

	addMenu(bar, "View", vitems);

	String sitems[] = {
	    "SSELECT", "IINSERT", "UUPDATE", "DDELETE", "--",
	    "-CREATE TABLE", "-DROP TABLE", "-CREATE INDEX", "-DROP INDEX",
	    "--", "-SCRIPT", "-SHUTDOWN", "--", "-Test Script"
	};

	addMenu(bar, "Command", sitems);

	Menu recent = new Menu("Recent");

	mRecent = new Menu("Recent");

	bar.add(mRecent);

	String soptions[] = {
	    "-AutoCommit on", "-AutoCommit off", "OCommit", "LRollback",
	    "--", "-Disable MaxRows", "-Set MaxRows to 100", "--",
	    "-Logging on", "-Logging off", "--", "-Insert test data",
	    "-Transfer"
	};

	addMenu(bar, "Options", soptions);
	fMain.setMenuBar(bar);
	fMain.setSize(640, 480);
	fMain.add("Center", this);
	initGUI();

	sRecent = new String[iMaxRecent];

	Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
	Dimension size = fMain.getSize();

	fMain.setLocation((d.width - size.width) / 2,
			  (d.height - size.height) / 2);
	fMain.show();
	txtCommand.requestFocus();
    }

    /**
     * Method declaration
     *
     *
     * @param b
     * @param name
     * @param items
     */
    void addMenu(MenuBar b, String name, String items[]) {
	Menu menu = new Menu(name);

	addMenuItems(menu, items);
	b.add(menu);
    }

    /**
     * Method declaration
     *
     *
     * @param f
     * @param m
     */
    void addMenuItems(Menu f, String m[]) {
	for (int i = 0; i < m.length; i++) {
	    MenuItem item = new MenuItem(m[i].substring(1));
	    char     c = m[i].charAt(0);

	    if (c != '-') {
		item.setShortcut(new MenuShortcut(c));
	    }

	    item.addActionListener(this);
	    f.add(item);
	}
    }

    /**
     * Method declaration
     *
     *
     * @param k
     */
    public void keyPressed(KeyEvent k) {}

    /**
     * Method declaration
     *
     *
     * @param k
     */
    public void keyReleased(KeyEvent k) {}

    /**
     * Method declaration
     *
     *
     * @param k
     */
    public void keyTyped(KeyEvent k) {
	if (k.getKeyChar() == '\n' && k.isControlDown()) {
	    k.consume();
	    execute();
	}
    }

    /**
     * Method declaration
     *
     *
     * @param ev
     */
    public void actionPerformed(ActionEvent ev) {
	String s = ev.getActionCommand();

	if (s == null) {
	    if (ev.getSource() instanceof MenuItem) {
		MenuItem i;

		s = ((MenuItem) ev.getSource()).getLabel();
	    }
	}

	if (s.equals("Execute")) {
	    execute();
	} else if (s.equals("Exit")) {
	    windowClosing(null);
	} else if (s.equals("Transfer")) {
	    Transfer.work();
	} else if (s.equals("Logging on")) {
	    DriverManager.setLogStream(System.out);
	} else if (s.equals("Logging off")) {
	    DriverManager.setLogStream(null);
	} else if (s.equals("Refresh Tree")) {
	    refreshTree();
	} else if (s.startsWith("#")) {
	    int i = Integer.parseInt(s.substring(1));

	    txtCommand.setText(sRecent[i]);
	} else if (s.equals("Connect...")) {
	    connect(ConnectionDialog.createConnection(fMain, "Connect"));
	    refreshTree();
	} else if (s.equals("Results in Grid")) {
	    iResult = 0;

	    pResult.removeAll();
	    pResult.add("Center", gResult);
	    pResult.doLayout();
	} else if (s.equals("Open Script...")) {
	    FileDialog f = new FileDialog(fMain, "Open Script",
					  FileDialog.LOAD);

	    f.show();

	    String file = f.getFile();

	    if (file != null) {
		txtCommand.setText(readFile(f.getDirectory() + file));
	    }
	} else if (s.equals("Save Script...")) {
	    FileDialog f = new FileDialog(fMain, "Save Script",
					  FileDialog.SAVE);

	    f.show();

	    String file = f.getFile();

	    if (file != null) {
		writeFile(f.getDirectory() + file, txtCommand.getText());
	    }
	} else if (s.equals("Save Result...")) {
	    FileDialog f = new FileDialog(fMain, "Save Result",
					  FileDialog.SAVE);

	    f.show();

	    String file = f.getFile();

	    if (file != null) {
		showResultInText();
		writeFile(f.getDirectory() + file, txtResult.getText());
	    }
	} else if (s.equals("Results in Text")) {
	    iResult = 1;

	    pResult.removeAll();
	    pResult.add("Center", txtResult);
	    pResult.doLayout();
	    showResultInText();
	} else if (s.equals("AutoCommit on")) {
	    try {
		cConn.setAutoCommit(true);
	    } catch (SQLException e) {}
	} else if (s.equals("AutoCommit off")) {
	    try {
		cConn.setAutoCommit(false);
	    } catch (SQLException e) {}
	} else if (s.equals("Enlarge Tree")) {
	    Dimension d = tTree.getMinimumSize();

	    d.width += 20;

	    tTree.setMinimumSize(d);
	    fMain.pack();
	} else if (s.equals("Shrink Tree")) {
	    Dimension d = tTree.getMinimumSize();

	    d.width -= 20;

	    if (d.width >= 0) {
		tTree.setMinimumSize(d);
	    }

	    fMain.pack();
	} else if (s.equals("Enlarge Command")) {
	    txtCommand.setRows(txtCommand.getRows() + 1);
	    fMain.pack();
	} else if (s.equals("Shrink Command")) {
	    int i = txtCommand.getRows() - 1;

	    txtCommand.setRows(i < 1 ? 1 : i);
	    fMain.pack();
	} else if (s.equals("Commit")) {
	    try {
		cConn.commit();
	    } catch (SQLException e) {}
	} else if (s.equals("Insert test data")) {
	    insertTestData();
	} else if (s.equals("Rollback")) {
	    try {
		cConn.rollback();
	    } catch (SQLException e) {}
	} else if (s.equals("Disable MaxRows")) {
	    try {
		sStatement.setMaxRows(0);
	    } catch (SQLException e) {}
	} else if (s.equals("Set MaxRows to 100")) {
	    try {
		sStatement.setMaxRows(100);
	    } catch (SQLException e) {}
	} else if (s.equals("SELECT")) {
	    showHelp("SELECT * FROM ",
		     "SELECT [DISTINCT] \n"
		     + "{ selectExpression | table.* | * } [, ... ] \n"
		     + "[INTO newTable] \n" + "FROM tableList \n"
		     + "[WHERE Expression] \n"
		     + "[ORDER BY selectExpression [{ASC | DESC}] [, ...] ] \n"
		     + "[GROUP BY Expression [, ...] ] \n"
		     + "[UNION [ALL] selectStatement]");
	} else if (s.equals("INSERT")) {
	    showHelp("INSERT INTO ",
		     "INSERT INTO table [ (column [,...] ) ] \n"
		     + "{ VALUES(Expression [,...]) | SelectStatement }");
	} else if (s.equals("UPDATE")) {
	    showHelp("UPDATE ",
		     "UPDATE table SET column = Expression [, ...] \n"
		     + "[WHERE Expression]");
	} else if (s.equals("DELETE")) {
	    showHelp("DELETE FROM ", "DELETE FROM table [WHERE Expression]");
	} else if (s.equals("CREATE TABLE")) {
	    showHelp("CREATE TABLE ",
		     "CREATE TABLE name \n"
		     + "( columnDefinition [, ...] ) \n\n"
		     + "columnDefinition: \n"
		     + "column DataType [ [NOT] NULL] [PRIMARY KEY] \n"
		     + "DataType: \n"
		     + "{ INTEGER | DOUBLE | VARCHAR | DATE | TIME |... }");
	} else if (s.equals("DROP TABLE")) {
	    showHelp("DROP TABLE ", "DROP TABLE table");
	} else if (s.equals("CREATE INDEX")) {
	    showHelp("CREATE INDEX ",
		     "CREATE [UNIQUE] INDEX index ON \n"
		     + "table (column [, ...])");
	} else if (s.equals("DROP INDEX")) {
	    showHelp("DROP INDEX ", "DROP INDEX table.index");
	} else if (s.equals("SCRIPT")) {
	    showHelp("SCRIPT",
		     "SCRIPT ['file']\n\n" + "(HSQL Database Engine only)");
	} else if (s.equals("SHUTDOWN")) {
	    showHelp("SHUTDOWN",
		     "SHUTDOWN [IMMEDIATELY]\n\n" + "(HSQL Database Engine only)");
	} else if (s.equals("Test Script")) {
	    showHelp("-->>>TEST<<<-- ;\n" + "--#1000;\n"
		     + "DROP TABLE Test ;\n" + "CREATE TABLE Test(\n"
		     + "  Id INTEGER PRIMARY KEY,\n"
		     + "  FirstName VARCHAR(20),\n" + "  Name VARCHAR(50),\n"
		     + "  ZIP INTEGER) ;\n" + "INSERT INTO Test \n"
		     + "  VALUES(#,'Julia','Peterson-Clancy',#) ;\n"
		     + "UPDATE Test SET Name='Hans' WHERE Id=# ;\n"
		     + "SELECT * FROM Test WHERE Id=# ;\n"
		     + "DELETE FROM Test WHERE Id=# ;\n" + "DROP TABLE Test",
		     "This test script is parsed by the DatabaseManager\n"
		     + "It may be changed manually. Rules:\n"
		     + "- it must start with -->>>TEST<<<--.\n"
		     + "- each line must end with ';' (no spaces after)\n"
		     + "- lines starting with -- are comments\n"
		     + "- lines starting with --#<count> means set new count\n");
	}
    }

    /**
     * Method declaration
     *
     *
     * @param file
     *
     * @return
     */
    String readFile(String file) {
	try {
	    FileReader   read = new FileReader(file);
	    char	 buffer[] = new char[1024];
	    StringBuffer b = new StringBuffer();

	    while (true) {
		int i = read.read(buffer, 0, 1024);

		if (i == -1) {
		    break;
		}

		b.append(buffer, 0, i);
	    }

	    read.close();

	    return b.toString();