jdbcclient.java

来自「这个是内存数据库的客户端」· Java 代码 · 共 1,234 行 · 第 1/3 页

/*
 * The contents of this file are subject to the MonetDB Public License
 * Version 1.1 (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://monetdb.cwi.nl/Legal/MonetDBLicense-1.1.html
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 *
 * The Original Code is the MonetDB Database System.
 *
 * The Initial Developer of the Original Code is CWI.
 * Portions created by CWI are Copyright (C) 1997-2007 CWI.
 * All Rights Reserved.
 */

package nl.cwi.monetdb.client;

import nl.cwi.monetdb.client.util.*;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.util.zip.*;
import java.net.*;

/**
 * This program acts like an extended client program for MonetDB. Its
 * look and feel is very much like PostgreSQL's interactive terminal
 * program.  Although it looks like this client is designed for MonetDB,
 * it demonstrates the power of the JDBC interface since it built on top
 * of JDBC only.
 *
 * @author Fabian Groffen <[e-mail]>
 * @version 1.2
 */

public class JdbcClient {
	private static Connection con;
	private static Statement stmt;
	private static BufferedReader in;
	private static PrintWriter out;
	private static Exporter e;
	private static DatabaseMetaData dbmd;

	public final static void main(String[] args) throws Exception {
		CmdLineOpts copts = new CmdLineOpts();

		// arguments which take exactly one argument
		copts.addOption("h", "host", CmdLineOpts.CAR_ONE, "localhost",
				"The hostname of the host that runs the MonetDB database.  " +
				"A port number can be supplied by use of a colon, i.e. " +
				"-h somehost:12345.");
		copts.addOption("p", "port", CmdLineOpts.CAR_ONE, "50000",
				"The port number to connect to.");
		// todo make it CAR_ONE_MANY
		copts.addOption("f", "file", CmdLineOpts.CAR_ONE, null,
				"A file name to use either for reading or writing.  The " +
				"file will be used for writing when dump mode is used " +
				"(-d --dump).  In read mode, the file can also be an URL " +
				"pointing to a plain text file that is optionally gzip " +
				"compressed.");
		copts.addOption("u", "user", CmdLineOpts.CAR_ONE, System.getProperty("user.name"),
				"The username to use when connecting to the database.");
		// this one is only here for the .monetdb file parsing, it is
		// removed before the command line arguments are parsed
		copts.addOption(null, "password", CmdLineOpts.CAR_ONE, null, null);
		copts.addOption("d", "database", CmdLineOpts.CAR_ONE, "demo",
				"Try to connect to the given database (only makes sense " +
				"if connecting to a DatabasePool, M5 or equivalent process).");
		copts.addOption("l", "language", CmdLineOpts.CAR_ONE, "sql",
				"Use the given language, for example 'xquery'.");

		// arguments which have no argument(s)
		copts.addOption(null, "help", CmdLineOpts.CAR_ZERO, null,
				"This help screen.");
		copts.addOption(null, "version", CmdLineOpts.CAR_ZERO, null,
				"Display driver version and exit.");
		copts.addOption("e", "echo", CmdLineOpts.CAR_ZERO, null,
				"Also outputs the contents of the input file, if any.");
		copts.addOption("q", "quiet", CmdLineOpts.CAR_ZERO, null,
				"Suppress printing the welcome header.");

		// arguments which have zero to many arguments
		copts.addOption("D", "dump", CmdLineOpts.CAR_ZERO_MANY, null, 
				"Dumps the given table(s), or the complete database if " +
				"none given.");

		// extended options
		copts.addOption(null, "Xblksize", CmdLineOpts.CAR_ONE, null,
				"Specifies the blocksize when using block mode, given " +
				"in bytes.");
		copts.addOption(null, "Xoutput", CmdLineOpts.CAR_ONE, null,
				"The output mode when dumping.  Default is sql, xml may " +
				"be used for an experimental XML output.");
		copts.addOption(null, "Xembedded", CmdLineOpts.CAR_ONE, null,
				"Uses an \"embedded\" Mserver instance.  The argument to " +
				"this option should be in the form of path/to/mserver:dbname" +
				"[:dbfarm[:dbinit]].");
		copts.addOption(null, "Xhash", CmdLineOpts.CAR_ONE, null,
				"Use the given hash algorithm during challenge response.  " +
				"Supported algorithm names: SHA1, MD5, plain.");
		// arguments which can have zero or one argument(s)
		copts.addOption(null, "Xdebug", CmdLineOpts.CAR_ZERO_ONE, null,
				"Writes a transmission log to disk for debugging purposes.  " +
				"If a file name is given, it is used, otherwise a file " +
				"called monet<timestamp>.log is created.  A given file " +
				"never be overwritten; instead a unique variation of the " +
				"file is used.");
		copts.addOption(null, "Xbatching", CmdLineOpts.CAR_ZERO_ONE, null,
				"Indicates that a batch should be used instead of direct " +
				"communication with the server for each statement.  If a " +
				"number is given, it is used as batch size.  i.e. 8000 " +
				"would execute the contents on the batch after each 8000 " +
				"statements read.  Batching can greatly speedup the " +
				"process of restoring a database dump.");

		// we store user and password in separate variables in order to
		// be able to properly act on them like forgetting the password
		// from the user's file if the user supplies a username on the
		// command line arguments
		String pass = null;
		String user = null;

		// look for a file called .monetdb in the current dir or in the
		// user's homedir and read its preferences
		File pref = new File(".monetdb");
		if (!pref.exists()) pref = new File(System.getProperty("user.home"), ".monetdb");
		if (pref.exists()) {
			try {
				copts.processFile(pref);
			} catch (OptionsException e) {
				System.err.println("Error in " + pref.getAbsolutePath() + ": " + e.getMessage());
				System.exit(-1);
			}
			user = copts.getOption("user").getArgument();
			pass = copts.getOption("password").getArgument();
		}

		// process the command line arguments, remove password option
		// first, and save the user we had at this point
		copts.removeOption("password");
		try {
			copts.processArgs(args);
		} catch (OptionsException e) {
			System.err.println("Error: " + e.getMessage());
			System.exit(-1);
		}
		// we can actually compare pointers (objects) here
		if (user != copts.getOption("user").getArgument()) pass = null;

		if (copts.getOption("help").isPresent()) {
			System.out.print(
"Usage java -jar jdbcclient-X.Y.jar\n" +
"                  [-h host[:port]] [-p port] [-f file] [-u user]\n" +
"                  [-l language] [-b database] [-e] [-d [table]]\n" +
"                  [-X<opt>]\n" +
"or using long option equivalents --host --port --file --user --language\n" +
"--dump --echo --database.\n" +
"Arguments may be written directly after the option like -p50000.\n" +
"\n" +
"If no host and port are given, localhost and 50000 are assumed.  An .monetdb\n" +
"file may exist in the user's home directory.  This file can contain\n" +
"preferences to use each time JdbcClient is started.  Options given on the\n" +
"command line override the preferences file.  The .monetdb file syntax is\n" +
"<option>=<value> where option is one of the options host, port, file, mode\n" +
"debug, or password.  Note that the last one is perilous and therefore not\n" +
"available as command line option.\n" +
"If no input file is given using the -f flag, an interactive session is\n" +
"started on the terminal.\n" +
"\n" +
"OPTIONS\n" +
copts.produceHelpMessage()
);
			System.exit(0);
		} else if (copts.getOption("version").isPresent()) {
			// We cannot use the DatabaseMetaData here, because we
			// cannot get a Connection.  So instead, we just get the
			// values we want out of the Driver directly.
			System.out.println("Driver: " + 
					nl.cwi.monetdb.jdbc.MonetDriver.getDriverVersion());
			System.exit(0);
		}

		in = new BufferedReader(new InputStreamReader(System.in));
		out = new PrintWriter(new BufferedWriter(new OutputStreamWriter(System.out)));

		// whether the semi-colon at the end of a String terminates the
		// query or not (default = yes => SQL)
		boolean scolonterm = true;
		boolean xmlMode =
				"xml".equals(copts.getOption("Xoutput").getArgument());
		boolean isEmbedded = copts.getOption("Xembedded").isPresent();
		if (isEmbedded) {
			// user and password don't matter for embedded
			pass = "";
		}

		// we need the password from the user, fetch it with a pseudo
		// password protector
		if (pass == null) {
			try {
				char[] tmp = PasswordField.getPassword(System.in, "password: ");
				if (tmp != null) pass = String.valueOf(tmp);
			} catch (IOException ioe) {
				System.err.println("Invalid password!");
				System.exit(-1);
			}
			System.out.println("");
		}

		user = copts.getOption("user").getArgument();

		// build the hostname
		String host = copts.getOption("host").getArgument();
		if (host.indexOf(":") == -1) {
			host = host + ":" + copts.getOption("port").getArgument();
		}

		// make sure the driver is loaded
		Class.forName("nl.cwi.monetdb.jdbc.MonetDriver");

		// build the extra arguments of the JDBC connect string
		String attr = "?";
		CmdLineOpts.OptionContainer oc = copts.getOption("Xblksize");
		if (oc.isPresent())
			attr += "blockmode_blocksize=" + oc.getArgument() + "&";
		oc = copts.getOption("language");
		String lang = oc.getArgument();
		if (oc.isPresent())
			attr += "language=" + lang + "&";
		// set some behaviour based on the language XQuery
		if (lang.equals("xquery")) {
			scolonterm = false;	// no ; to end a statement
			if (!copts.getOption("Xoutput").isPresent())
				xmlMode = true; // the user will like xml results, most probably
		}
		oc = copts.getOption("Xdebug");
		if (oc.isPresent()) {
			attr += "debug=true&";
			if (oc.getArgumentCount() == 1)
				attr += "logfile=" + oc.getArgument() + "&";
		}
		oc = copts.getOption("Xhash");
		if (oc.isPresent())
			attr += "hash=" + oc.getArgument() + "&";

		// request a connection suitable for MonetDB from the driver
		// manager note that the database specifier is only used when
		// connecting to a proxy-like service, since MonetDB itself
		// can't access multiple databases.
		con = null;
		String database = copts.getOption("database").getArgument();
		try {
			if (!isEmbedded) {
				con = DriverManager.getConnection(
						"jdbc:monetdb://" + host + "/" + database + attr,
						user,
						pass
				);
			} else {
				String[] eargs =
					copts.getOption("Xembedded").getArgument().split(":");
				if (eargs.length < 2 || eargs.length > 4) {
					System.err.println("Expecting path/to/mserver:dbname" +
							"[:dbfarm[:dbinit]]");
					System.exit(-1);
				}
				Properties p = new Properties();
				p.setProperty("executable", eargs[0]);
				p.setProperty("dbname", eargs[1]);
				if (args.length > 2) p.setProperty("dbfarm", eargs[2]);
				if (args.length > 3) p.setProperty("dbinit", eargs[3]);
				nl.cwi.monetdb.jdbc.MonetConnection.setEmbeddedProperties(p);
				// what do you mean? "descriptive names suck" ?!?
				out.println("Starting embedded instance...");
				out.flush();
				con = nl.cwi.monetdb.jdbc.MonetConnection.getEmbeddedInstanceConnection();
			}
			SQLWarning warn = con.getWarnings();
			while (warn != null) {
				System.err.println("Connection warning: " +
					warn.getMessage());
				warn = warn.getNextWarning();
			}
			con.clearWarnings();
		} catch (SQLException e) {
			System.err.println("Database connect failed: " + e.getMessage());
			System.exit(-1);
		}
		try {
			dbmd = con.getMetaData();
		} catch (SQLException e) {
			// we ignore this because it's probably because we don't use
			// SQL language
			dbmd = null;
		}
		stmt = con.createStatement();
		
		// see if we will have to perform a database dump (only in SQL
		// mode)
		if ("sql".equals(lang) && copts.getOption("dump").isPresent()) {
			ResultSet tbl;

			// use the given file for writing
			oc = copts.getOption("file");
			if (oc.isPresent())
				out = new PrintWriter(new BufferedWriter(new FileWriter(oc.getArgument())));

			// we only want tables and views to be dumped, unless a specific
			// table is requested
			String[] types = {"TABLE", "VIEW"};
			if (copts.getOption("dump").getArgumentCount() > 0) types = null;
			// request the tables available in the database
			tbl = dbmd.getTables(null, null, null, types);

			LinkedList tables = new LinkedList();
			while (tbl.next()) {
				tables.add(new Table(
					tbl.getString("TABLE_CAT"),
					tbl.getString("TABLE_SCHEM"),
					tbl.getString("TABLE_NAME"),
					tbl.getString("TABLE_TYPE")));
			}

			if (xmlMode) {
				e = new XMLExporter(out);
				e.setProperty(XMLExporter.TYPE_NIL, XMLExporter.VALUE_XSI);
			} else {
				e = new SQLExporter(out);
				// stick with inserts for now, in the future we might do
				// COPY INTO's here using VALUE_COPY
				e.setProperty(SQLExporter.TYPE_OUTPUT, SQLExporter.VALUE_INSERT);
			}
			e.useSchemas(true);

			// start SQL output
			if (!xmlMode) out.println("START TRANSACTION;\n");

			// dump specific table(s) or not?
			if (copts.getOption("dump").getArgumentCount() > 0) { // yes we do
				String[] dumpers = copts.getOption("dump").getArguments();
				for (int i = 0; i < tables.size(); i++) {
					Table ttmp = (Table)(tables.get(i));
					for (int j = 0; j < dumpers.length; j++) {
						if (ttmp.getName().equalsIgnoreCase(dumpers[j].toString()) ||
							ttmp.getFqname().equalsIgnoreCase(dumpers[j].toString()))
						{
							// dump the table
							doDump(out, ttmp, dbmd, stmt);
						}
					}
				}
			} else {
				tbl = dbmd.getImportedKeys(null, null, null);
				while (tbl.next()) {
					// find FK table object
					Table fk = Table.findTable(tbl.getString("FKTABLE_SCHEM") + "." + tbl.getString("FKTABLE_NAME"), tables);

					// find PK table object
					Table pk = Table.findTable(tbl.getString("PKTABLE_SCHEM") + "." + tbl.getString("PKTABLE_NAME"), tables);

					// should not be possible to happen
					if (fk == null || pk == null)
						throw new AssertionError("Illegal table; table not found in list");

					// add PK table dependancy to FK table
					fk.addDependancy(pk);
				}

				// search for cycles of type a -> (x ->)+ b probably not
				// the most optimal way, but it works by just scanning
				// every table for loops in a recursive manor
				for (int i = 0; i < tables.size(); i++) {
					Table.checkForLoop((Table)(tables.get(i)), new ArrayList());
				}

				// find the graph, at this point we know there are no
				// cycles, thus a solution exists
				for (int i = 0; i < tables.size(); i++) {
					List needs = ((Table)(tables.get(i))).requires(tables.subList(0, i + 1));
					if (needs.size() > 0) {
						tables.removeAll(needs);
						tables.addAll(i, needs);

						// re-evaluate this position, for there is a new
						// table now
						i--;
					}
				}

				// we now have the right order to dump tables
				for (int i = 0; i < tables.size(); i++) {
					// dump the table
					Table t = (Table)(tables.get(i));
					doDump(out, t, dbmd, stmt);
				}
			}

			if (!xmlMode) out.println("COMMIT;");
			out.flush();

			con.close();
			System.exit(0);
		}

		if (xmlMode) {
			e = new XMLExporter(out);
			e.setProperty(XMLExporter.TYPE_NIL, XMLExporter.VALUE_XSI);
		} else {
			e = new SQLExporter(out);