0001: package com.protomatter.jdbc.sl;
0002:
0003: /**
0004: * {{{ The Protomatter Software License, Version 1.0
0005: * derived from The Apache Software License, Version 1.1
0006: *
0007: * Copyright (c) 1998-2002 Nate Sammons. All rights reserved.
0008: *
0009: * Redistribution and use in source and binary forms, with or without
0010: * modification, are permitted provided that the following conditions
0011: * are met:
0012: *
0013: * 1. Redistributions of source code must retain the above copyright
0014: * notice, this list of conditions and the following disclaimer.
0015: *
0016: * 2. Redistributions in binary form must reproduce the above copyright
0017: * notice, this list of conditions and the following disclaimer in
0018: * the documentation and/or other materials provided with the
0019: * distribution.
0020: *
0021: * 3. The end-user documentation included with the redistribution,
0022: * if any, must include the following acknowledgment:
0023: * "This product includes software developed for the
0024: * Protomatter Software Project
0025: * (http://protomatter.sourceforge.net/)."
0026: * Alternately, this acknowledgment may appear in the software itself,
0027: * if and wherever such third-party acknowledgments normally appear.
0028: *
0029: * 4. The names "Protomatter" and "Protomatter Software Project" must
0030: * not be used to endorse or promote products derived from this
0031: * software without prior written permission. For written
0032: * permission, please contact support@protomatter.com.
0033: *
0034: * 5. Products derived from this software may not be called "Protomatter",
0035: * nor may "Protomatter" appear in their name, without prior written
0036: * permission of the Protomatter Software Project
0037: * (support@protomatter.com).
0038: *
0039: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
0040: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
0041: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
0042: * DISCLAIMED. IN NO EVENT SHALL THE PROTOMATTER SOFTWARE PROJECT OR
0043: * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
0044: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
0045: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
0046: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
0047: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
0048: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
0049: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
0050: * SUCH DAMAGE. }}}
0051: */
0052:
0053: import java.sql.*;
0054: import java.text.*;
0055: import java.io.*;
0056: import java.util.*;
0057: import java.lang.reflect.*;
0058: import com.protomatter.Protomatter;
0059:
0060: /**
0061: * A simple SQL tool. Run it, and type "help".
0062: * A list of files are taken as command line arguments -- each
0063: * of these files are run as command files at startup.
0064: */
0065: public class SimpleListener {
0066: private Connection connection;
0067: private Driver driver;
0068: private String url;
0069: private Properties props;
0070:
0071: private String lastCommandLine = "";
0072: private Vector history = new Vector();
0073: private int historySize = 20;
0074: private String prompt = "SL> ";
0075: private String morePrompt = "";
0076: private boolean showStackTrace = false;
0077: private boolean showQuotes = false;
0078: private String defaultSchema = null;
0079:
0080: private SimpleListener() {
0081: super ();
0082: }
0083:
0084: public static void main(String args[]) {
0085: SimpleListener s = new SimpleListener();
0086: try {
0087: if (args.length > 0) {
0088: for (int i = 0; i < args.length; i++) {
0089: String file = args[i];
0090: Vector theArgs = new Vector();
0091: theArgs.addElement(file);
0092: s.handleCommand_run(theArgs);
0093: }
0094: }
0095: s.commandLoop();
0096: } catch (Exception x) {
0097: x.printStackTrace();
0098: }
0099: }
0100:
0101: private void commandLoop() throws IOException {
0102: // try a statement
0103: BufferedReader reader = new BufferedReader(
0104: new InputStreamReader(System.in));
0105: String line = "";
0106: System.out.print(prompt);
0107: System.out.flush();
0108: while ((line = reader.readLine()) != null) {
0109: StringBuffer cmd = new StringBuffer();
0110: while (!line.equals("") && !line.endsWith(";")
0111: && !line.endsWith("/")) {
0112: if (!line.startsWith("#") && !line.startsWith("--")) {
0113: cmd.append(line);
0114: cmd.append(" ");
0115: }
0116: System.out.print(morePrompt);
0117: System.out.flush();
0118: line = reader.readLine();
0119: }
0120: if (!line.equals("") && !line.startsWith("#")
0121: && !line.startsWith("--"))
0122: cmd.append(line.substring(0, line.length() - 1));
0123: executeCommand(cmd.toString());
0124: System.out.print(prompt);
0125: System.out.flush();
0126: }
0127: }
0128:
0129: private void executeCommand(String line) throws IOException {
0130: if (line.equals("/")) {
0131: line = lastCommandLine;
0132: System.out.println("");
0133: System.out.println("Executing last command:");
0134: System.out.println(" " + lastCommandLine);
0135: System.out.println("");
0136: }
0137:
0138: if (line.startsWith("!")) {
0139: int index = 0;
0140: try {
0141: index = Integer.parseInt(line.substring(1));
0142: line = getHistoryCommand(index);
0143: System.out.println("");
0144: System.out.println("Executing command from history:");
0145: System.out.println(" " + line);
0146: System.out.println("");
0147: } catch (Exception x) {
0148: System.out.println("What?");
0149: line = "";
0150: }
0151: }
0152:
0153: if (!line.equals("")) {
0154: lastCommandLine = line;
0155: addToHistory(line);
0156: try {
0157: StringTokenizer st = new StringTokenizer(line);
0158: String commandName = st.nextToken();
0159: if (commandName.equals("connect")) {
0160: // different handling here for "connect" because
0161: // some drivers put spaces into their URLs. Bah!
0162: long time = System.currentTimeMillis();
0163: handleCommand_connect(line.substring(7).trim());
0164: time = System.currentTimeMillis() - time;
0165: System.out.println("Command took " + time + "ms");
0166: } else {
0167: Class c = this .getClass();
0168: Class params[] = new Class[1];
0169: Vector args = new Vector();
0170: String s = null;
0171: while (st.hasMoreTokens()) {
0172: args.addElement(st.nextToken());
0173: }
0174: try {
0175: params[0] = Class.forName("java.util.Vector");
0176: } catch (ClassNotFoundException e) {
0177: throw new InternalError(
0178: "Can't find java.util.Vector! Doh!");
0179: }
0180: Method method = null;
0181: try {
0182: method = c.getMethod("handleCommand_"
0183: + commandName, params);
0184: System.out.println("");
0185: } catch (Exception e) {
0186: handleCommandDefault(commandName, args);
0187: return;
0188: }
0189: Object methodArguments[] = new Object[1];
0190: try {
0191: methodArguments[0] = args;
0192: long time = System.currentTimeMillis();
0193: method.invoke(this , methodArguments);
0194: time = System.currentTimeMillis() - time;
0195: System.out.println("Command took " + time
0196: + "ms");
0197: } catch (InvocationTargetException e) {
0198: System.out.println("Exception calling method:");
0199: Throwable x = e.getTargetException();
0200:
0201: if (showStackTrace)
0202: x.printStackTrace();
0203: else
0204: System.out.println("Exception: " + x);
0205: System.out.println("");
0206: return;
0207: }
0208: }
0209: } catch (Exception e) {
0210: if (showStackTrace)
0211: e.printStackTrace();
0212: else
0213: System.out.println("Exception: " + e);
0214: }
0215: }
0216: }
0217:
0218: private void handleCommandDefault(String cmdName, Vector args)
0219: throws SQLException {
0220: System.out.println("Executing sql");
0221: if (checkConnected()) {
0222: String line = cmdName;
0223:
0224: boolean query = false;
0225: if (cmdName.equalsIgnoreCase("select"))
0226: query = true;
0227:
0228: Enumeration e = args.elements();
0229: while (e.hasMoreElements()) {
0230: line += " " + e.nextElement();
0231: }
0232:
0233: PreparedStatement s = connection.prepareStatement(line);
0234: long time = System.currentTimeMillis();
0235:
0236: ResultSet r = null;
0237: int rows = 0;
0238:
0239: if (query)
0240: r = s.executeQuery();
0241: else
0242: rows = s.executeUpdate();
0243:
0244: time = System.currentTimeMillis() - time;
0245: if (query) {
0246: System.out
0247: .println("PreparedStatement.executeQuery() took "
0248: + time + "ms");
0249: ResultSetUtil
0250: .formatResultSet(r, System.out, showQuotes);
0251: } else {
0252: System.out
0253: .println("PreparedStatement.executeUpdate() took "
0254: + time + "ms");
0255: System.out.println("Affected " + rows + " row"
0256: + (rows == 1 ? "" : "s"));
0257: }
0258: if (r != null)
0259: r.close();
0260: if (s != null)
0261: s.close();
0262: }
0263: }
0264:
0265: public void handleCommand_quit(Vector args) {
0266: if (connection != null) {
0267: try {
0268: System.out.println("Disconnecting...");
0269: connection.close();
0270: } catch (Exception x) {
0271: System.out.println("Exception closing connection...");
0272: if (showStackTrace)
0273: x.printStackTrace();
0274: else
0275: System.out.println("Exception: " + x);
0276: }
0277: }
0278: System.out.println("See ya, sucker!");
0279: System.exit(0);
0280: }
0281:
0282: public void handleCommand_disconnect(Vector args) throws Exception {
0283: if (!checkConnected())
0284: return;
0285: connection.close();
0286: connection = null;
0287: this .url = null;
0288: this .props = null;
0289: }
0290:
0291: public void handleCommand_commit(Vector args) throws Exception {
0292: if (!checkConnected())
0293: return;
0294: connection.commit();
0295: }
0296:
0297: public void handleCommand_rollback(Vector args) throws Exception {
0298: if (!checkConnected())
0299: return;
0300: connection.rollback();
0301: }
0302:
0303: public void handleCommand_driver(Vector args) throws Exception {
0304: try {
0305: driver = (Driver) Class.forName(
0306: (String) args.firstElement()).newInstance();
0307: } catch (Exception x) {
0308: if (showStackTrace)
0309: x.printStackTrace();
0310: else
0311: System.out.println("Exception: " + x);
0312: throw x;
0313: }
0314: }
0315:
0316: public void handleCommand_connect(String args) throws Exception {
0317: if (!checkDriver())
0318: return;
0319: try {
0320: // the argument should be:
0321: //
0322: // URL [username [password [key=val ...]]]
0323: //
0324: if (args.equals("")) {
0325: System.out
0326: .println("Usage: connect URL [username [password [key=val ...]]]");
0327: System.out
0328: .println(" You can use double-quotes to quote things.");
0329: System.out.println("");
0330: return;
0331: }
0332:
0333: String url = null;
0334: String user = null;
0335: String pass = null;
0336: Properties props = new Properties();
0337:
0338: // parse things out and pay attention to double quotes.
0339: Vector list = new Vector();
0340: boolean inQuote = false;
0341: StringBuffer currString = new StringBuffer();
0342: for (int i = 0; i < args.length(); i++) {
0343: char c = args.charAt(i);
0344: if (c == '"') {
0345: if (inQuote) {
0346: list.add(currString.toString());
0347: currString = new StringBuffer();
0348: inQuote = false;
0349: } else {
0350: inQuote = true;
0351: }
0352: } else if (c == ' ') {
0353: if (inQuote) {
0354: currString.append(c);
0355: } else {
0356: if (currString.length() != 0) {
0357: list.add(currString.toString());
0358: currString = new StringBuffer();
0359: }
0360: }
0361: } else {
0362: currString.append(c);
0363: }
0364: }
0365: if (currString.length() != 0)
0366: list.add(currString.toString());
0367:
0368: Enumeration e = list.elements();
0369: if (e.hasMoreElements())
0370: url = (String) e.nextElement();
0371: if (e.hasMoreElements())
0372: user = (String) e.nextElement();
0373: if (e.hasMoreElements())
0374: pass = (String) e.nextElement();
0375:
0376: Properties p = new Properties();
0377: if (user != null)
0378: p.put("user", user);
0379: if (pass != null)
0380: p.put("password", pass);
0381: while (e.hasMoreElements()) {
0382: String tok = (String) e.nextElement();
0383: StringTokenizer st = new StringTokenizer(tok, "=");
0384: p.put(st.nextToken(), st.nextToken());
0385: }
0386:
0387: System.out.println("Connecting....");
0388: System.out.println(" URL: \"" + url + "\"");
0389: System.out.println(" Properties:");
0390: Enumeration en = p.keys();
0391: int width = 0;
0392: while (en.hasMoreElements()) {
0393: String key = (String) en.nextElement();
0394: if (key.length() > width)
0395: width = key.length();
0396: }
0397:
0398: en = p.keys();
0399: while (en.hasMoreElements()) {
0400: String key = (String) en.nextElement();
0401: String val = p.getProperty(key);
0402: int size = key.length();
0403: System.out.print(" ");
0404: System.out.print(key);
0405: for (int i = 0; i < (width - size); i++)
0406: System.out.print(" ");
0407: System.out.println(" = " + val);
0408: }
0409:
0410: this .url = url;
0411: this .props = p;
0412: connection = DriverManager.getConnection(url, p);
0413: } catch (Exception x) {
0414: if (showStackTrace)
0415: x.printStackTrace();
0416: else
0417: System.out.println("Exception: " + x);
0418: throw x;
0419: }
0420: }
0421:
0422: private String getIsolationString(int isolation) {
0423: switch (isolation) {
0424: case Connection.TRANSACTION_NONE:
0425: return "None";
0426: case Connection.TRANSACTION_READ_COMMITTED:
0427: return "Read Committed";
0428: case Connection.TRANSACTION_READ_UNCOMMITTED:
0429: return "Read Uncommitted";
0430: case Connection.TRANSACTION_REPEATABLE_READ:
0431: return "Repeatable Read";
0432: case Connection.TRANSACTION_SERIALIZABLE:
0433: return "Serializable";
0434: }
0435: return "Unknown";
0436: }
0437:
0438: public void handleCommand_isolation(Vector args)
0439: throws SQLException {
0440: if (!checkConnected())
0441: return;
0442:
0443: if (args.size() == 1) {
0444: String l = (String) args.elementAt(0);
0445: if (l.equalsIgnoreCase("none"))
0446: connection
0447: .setTransactionIsolation(Connection.TRANSACTION_NONE);
0448: else if (l.equalsIgnoreCase("read_committed"))
0449: connection
0450: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
0451: else if (l.equalsIgnoreCase("read_uncommitted"))
0452: connection
0453: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
0454: else if (l.equalsIgnoreCase("repeatable_read"))
0455: connection
0456: .setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
0457: else if (l.equalsIgnoreCase("serializable"))
0458: connection
0459: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
0460: else {
0461: System.out
0462: .println(" You must specify one of the following levels:");
0463: System.out.println(" NONE");
0464: System.out.println(" READ_COMMITTED");
0465: System.out.println(" READ_UNCOMMITTED");
0466: System.out.println(" REPEATABLE_READ");
0467: System.out.println(" SERIALIZABLE");
0468: System.out.println("");
0469: }
0470: }
0471: System.out.println("Current isolation level: "
0472: + getIsolationString(connection
0473: .getTransactionIsolation()));
0474: System.out.println("");
0475: }
0476:
0477: public void handleCommand_metadata(Vector args) throws SQLException {
0478: if (!checkConnected())
0479: return;
0480: DatabaseMetaData m = connection.getMetaData();
0481: System.out.println("Connection Class name = "
0482: + connection.getClass().getName());
0483: System.out.println("Driver Name = "
0484: + m.getDriverName());
0485: System.out.println("Driver Version = "
0486: + m.getDriverVersion());
0487: System.out.println("Database Product Name = "
0488: + m.getDatabaseProductName());
0489: System.out.println("Database Product Version = "
0490: + m.getDatabaseProductVersion());
0491:
0492: System.out.println("");
0493: System.out
0494: .println("Default Isolation = "
0495: + getIsolationString(m
0496: .getDefaultTransactionIsolation()));
0497:
0498: System.out.println("");
0499: System.out.println("Maximum length for:");
0500: System.out.println(" Catalog name = "
0501: + m.getMaxCatalogNameLength());
0502: System.out.println(" Schema name = "
0503: + m.getMaxSchemaNameLength());
0504: System.out.println(" Table name = "
0505: + m.getMaxTableNameLength());
0506: System.out.println(" Column name = "
0507: + m.getMaxColumnNameLength());
0508: System.out.println(" Procedure name = "
0509: + m.getMaxProcedureNameLength());
0510: System.out.println(" Binary literal = "
0511: + m.getMaxBinaryLiteralLength());
0512: System.out.println("");
0513: System.out.println("Other misc maximums:");
0514: System.out.println(" Columns in table = "
0515: + m.getMaxColumnsInTable());
0516: System.out.println(" Connections = "
0517: + m.getMaxConnections());
0518: System.out.println(" Row size = " + m.getMaxRowSize());
0519: System.out.println(" Open statements = "
0520: + m.getMaxStatements());
0521: System.out.println("");
0522: System.out.println("ANSI Support declared:");
0523: System.out.println(" Entry-Level SQL92: "
0524: + m.supportsANSI92EntryLevelSQL());
0525: System.out.println(" Full SQL92: "
0526: + m.supportsANSI92FullSQL());
0527: System.out.println(" Intermediate SQL92: "
0528: + m.supportsANSI92IntermediateSQL());
0529: System.out.println(" Integrity Enhancement Facility: "
0530: + m.supportsIntegrityEnhancementFacility());
0531: System.out.println("");
0532: }
0533:
0534: public void handleCommand_catalogs(Vector args) throws SQLException {
0535: if (!checkConnected())
0536: return;
0537: ResultSet r = connection.getMetaData().getCatalogs();
0538: System.out.println("Available catalogs:");
0539: String current = connection.getCatalog();
0540: while (r.next()) {
0541: String cname = r.getString(1);
0542: System.out.println(" "
0543: + cname
0544: + (cname.equals(current) ? " <== current catalog"
0545: : ""));
0546: }
0547: r.close();
0548: System.out.println("");
0549: }
0550:
0551: public void handleCommand_catalog(Vector args) throws SQLException {
0552: if (!checkConnected())
0553: return;
0554: if (args.size() != 1) {
0555: System.out.println("Usage: catalog <catalogname>");
0556: } else {
0557: connection.setCatalog((String) args.firstElement());
0558: System.out.println("Changed catalogs to "
0559: + args.firstElement());
0560: }
0561: }
0562:
0563: public void handleCommand_schema(Vector args) throws SQLException {
0564: if (!checkConnected())
0565: return;
0566: if (args.size() != 1) {
0567: System.out.println("Usage: schema <schema>");
0568: System.out.println("Current default schema is \""
0569: + defaultSchema + "\"");
0570: } else {
0571: String schema = (String) args.firstElement();
0572: if ("null".equals(schema))
0573: defaultSchema = null;
0574: else
0575: defaultSchema = schema;
0576: System.out.println("Changed default schema to "
0577: + args.firstElement());
0578: }
0579: }
0580:
0581: public void handleCommand_schemas(Vector args) throws SQLException {
0582: if (!checkConnected())
0583: return;
0584: if (args.size() != 0) {
0585: System.out.println("Usage: schemas");
0586: return;
0587: }
0588:
0589: ResultSet r = connection.getMetaData().getSchemas();
0590: while (r.next()) {
0591: System.out.println(" " + r.getString("TABLE_SCHEM"));
0592: }
0593: System.out.println("");
0594: r.close();
0595: }
0596:
0597: public void handleCommand_procedures(Vector args)
0598: throws SQLException {
0599: if (!checkConnected())
0600: return;
0601: String catalog = connection.getCatalog();
0602: if (args.size() > 1) {
0603: System.out.println("Usage: procedures [<schemaname>]");
0604: return;
0605: }
0606: String schema = null;
0607: if (args.size() == 1) {
0608: schema = (String) args.firstElement();
0609: }
0610:
0611: ResultSet r = connection.getMetaData().getProcedures(catalog,
0612: schema, null);
0613: System.out.println("Schema Procedure");
0614: System.out
0615: .println("-------------------------------------------------");
0616: // xxxxxxxxxxxxxxxx xxxxxxxxxxxxx...
0617: while (r.next()) {
0618: System.out.println(" "
0619: + format(r.getString("PROCEDURE_SCHEM"), 16) + " "
0620: + r.getString("PROCEDURE_NAME"));
0621: }
0622: r.close();
0623: System.out.println("");
0624: }
0625:
0626: public void handleCommand_tables(Vector args) throws SQLException {
0627: if (!checkConnected())
0628: return;
0629: String catalog = connection.getCatalog();
0630: String schema = null;
0631: String tablename = null;
0632: if (args.size() > 0) {
0633: schema = (String) args.firstElement();
0634: }
0635: if (args.size() > 1) {
0636: tablename = ((String) args.elementAt(1)).toUpperCase();
0637: }
0638:
0639: ResultSet r = connection.getMetaData().getTables(catalog,
0640: schema, null, null);
0641: //System.out.println(ResultSetUtil.formatResultSet(r));
0642:
0643: System.out.println("Schema Tablename");
0644: System.out
0645: .println("-------------------------------------------------");
0646: // xxxxxxxxxxxxxxxx xxxxxxxxxxxxx...
0647: while (r.next()) {
0648: String name = r.getString("TABLE_NAME");
0649: if (tablename == null
0650: || (name.toUpperCase().indexOf(tablename) != -1))
0651: System.out.println(" "
0652: + format(r.getString("TABLE_SCHEM"), 16) + " "
0653: + name);
0654: }
0655: r.close();
0656:
0657: System.out.println("");
0658: }
0659:
0660: public void handleCommand_indexes(Vector args) throws SQLException {
0661: if (!checkConnected())
0662: return;
0663: String catalog = connection.getCatalog();
0664: if (args.size() != 1) {
0665: System.out.println("Usage: indexes [<schema>.]<tablename>");
0666: return;
0667: }
0668: String table = (String) args.firstElement();
0669: String schema = null;
0670: if (table.indexOf(".") != -1) {
0671: StringTokenizer st = new StringTokenizer(table, ".");
0672: schema = st.nextToken();
0673: table = st.nextToken();
0674: }
0675:
0676: ResultSet r = connection.getMetaData().getIndexInfo(catalog,
0677: schema, table, false, true);
0678: boolean first = true;
0679: while (r.next()) {
0680: if (first) {
0681: System.out.println("Schema: "
0682: + r.getString("TABLE_SCHEM"));
0683: System.out.println("Table: "
0684: + r.getString("TABLE_NAME"));
0685: first = false;
0686: }
0687:
0688: String indexName = r.getString("INDEX_NAME");
0689: boolean nonUnique = r.getBoolean("NON_UNIQUE");
0690: String qualifier = r.getString("INDEX_QUALIFIER");
0691: short indexType = r.getShort("TYPE");
0692: short position = r.getShort("ORDINAL_POSITION");
0693: String column = r.getString("COLUMN_NAME");
0694: String ascDesc = r.getString("ASC_OR_DESC");
0695: int cardinality = r.getInt("CARDINALITY");
0696: int pages = r.getInt("PAGES");
0697: String filter = r.getString("FILTER_CONDITION");
0698:
0699: System.out.println(" Index name: " + indexName);
0700: System.out.println(" Non unique vals?: " + nonUnique);
0701: System.out.println(" Column: " + column);
0702: System.out.println(" Qualifier: " + qualifier);
0703: System.out.print(" Type: ");
0704: switch (indexType) {
0705: case DatabaseMetaData.tableIndexStatistic:
0706: System.out.println("Statistic");
0707: break;
0708: case DatabaseMetaData.tableIndexClustered:
0709: System.out.println("Clustered");
0710: break;
0711: case DatabaseMetaData.tableIndexHashed:
0712: System.out.println("Hashed");
0713: break;
0714: case DatabaseMetaData.tableIndexOther:
0715: System.out.println("Other");
0716: break;
0717: default:
0718: System.out.println("Unknown");
0719: }
0720: System.out.println(" Positon: " + position);
0721: System.out.println(" Asc/Desc: " + ascDesc);
0722: System.out.println(" Cardinality: " + cardinality);
0723: System.out.println(" Pages: " + pages);
0724: System.out.println(" Filter: " + filter);
0725: System.out.println("");
0726: }
0727:
0728: if (first) // no rows
0729: {
0730: System.out.println(" Table \"" + table
0731: + "\" does not appear to have any indexes.");
0732: System.out.println("");
0733: }
0734:
0735: r.close();
0736: }
0737:
0738: public void handleCommand_run(Vector args) throws SQLException,
0739: IOException {
0740: if (args.size() != 1) {
0741: System.out.println("Usage: run <filename>");
0742: System.out
0743: .println(" (executes commands from the given file)");
0744: return;
0745: }
0746: String file = (String) args.elementAt(0);
0747: System.out.println("Processing commands from file:");
0748: System.out.println(" file=" + file);
0749: BufferedReader r = new BufferedReader(new FileReader(new File(
0750: file)));
0751: String line = "";
0752: while ((line = r.readLine()) != null) {
0753: StringBuffer cmd = new StringBuffer();
0754: while (!line.equals("") && !line.endsWith(";")
0755: && !line.endsWith("/")) {
0756: if (!line.startsWith("#") && !line.startsWith("--")) {
0757: cmd.append(line);
0758: cmd.append(" ");
0759: }
0760: line = r.readLine();
0761: }
0762: if (!line.equals(""))
0763: cmd.append(line.substring(0, line.length() - 1));
0764: if (!cmd.toString().startsWith("#")
0765: && !cmd.toString().startsWith("--")
0766: && !cmd.toString().trim().equals("")) {
0767: System.out.println(" cmd=\"" + cmd + "\"");
0768: executeCommand(cmd.toString());
0769: }
0770: }
0771: r.close();
0772: }
0773:
0774: public void handleCommand_desc(Vector args) throws SQLException {
0775: if (!checkConnected())
0776: return;
0777: String catalog = connection.getCatalog();
0778: if (args.size() != 1) {
0779: System.out.println("Usage: desc [<schema>.]<tablename>");
0780: return;
0781: }
0782: String table = (String) args.firstElement();
0783: String schema = defaultSchema;
0784: if (table.indexOf(".") != -1) {
0785: StringTokenizer st = new StringTokenizer(table, ".");
0786: schema = st.nextToken();
0787: table = st.nextToken();
0788: }
0789:
0790: // get PK info
0791: Hashtable pks = new Hashtable();
0792: ResultSet r = connection.getMetaData().getPrimaryKeys(catalog,
0793: schema, table);
0794: while (r.next())
0795: pks.put(r.getString("COLUMN_NAME"), "blah");
0796: r.close();
0797:
0798: r = connection.getMetaData().getColumns(catalog, schema, table,
0799: null);
0800: //System.out.println(ResultSetUtil.formatResultSet(r));
0801: boolean first = true;
0802: while (r.next()) {
0803: if (first) {
0804: if (schema == null) {
0805: schema = r.getString("TABLE_SCHEM");
0806: }
0807: System.out.println("Schema: "
0808: + r.getString("TABLE_SCHEM"));
0809: System.out
0810: .println("Column Name Type Size Null? PK");
0811: System.out
0812: .println("------------------------------------------------------------------------");
0813: // xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxx xx
0814: // 30 13 15 5
0815: first = false;
0816: }
0817: String colSchema = r.getString("TABLE_SCHEM");
0818: if ((schema == null)
0819: || (schema != null && schema.equals(colSchema))) {
0820: String colName = r.getString("COLUMN_NAME");
0821: System.out.print(" "
0822: + format(colName, 30)
0823: + " "
0824: + format(r.getString("TYPE_NAME"), 13)
0825: + " "
0826: + format(String
0827: .valueOf(r.getInt("COLUMN_SIZE")), 15)
0828: + " " + format(r.getString("IS_NULLABLE"), 6));
0829: if (pks.containsKey(colName))
0830: System.out.println("Y");
0831: else
0832: System.out.println("");
0833: }
0834: }
0835:
0836: if (first) // no rows
0837: {
0838: System.out.println(" Table \"" + table
0839: + "\" does not appear to exist.");
0840: }
0841:
0842: r.close();
0843: System.out.println("");
0844: }
0845:
0846: public void handleCommand_fkdesc(Vector args) throws SQLException {
0847: if (!checkConnected())
0848: return;
0849: String catalog = connection.getCatalog();
0850: if (args.size() != 1) {
0851: System.out.println("Usage: fkdesc [<schema>.]<tablename>");
0852: return;
0853: }
0854: String table = (String) args.firstElement();
0855: String schema = defaultSchema;
0856: if (table.indexOf(".") != -1) {
0857: StringTokenizer st = new StringTokenizer(table, ".");
0858: schema = st.nextToken();
0859: table = st.nextToken();
0860: }
0861:
0862: DatabaseMetaData md = connection.getMetaData();
0863:
0864: System.out.println("Exported keys:");
0865: ResultSet fkr = md.getCrossReference(catalog, schema, table,
0866: null, null, null);
0867: while (fkr.next()) {
0868: System.out.print(" " + fkr.getString("PKCOLUMN_NAME"));
0869: System.out.print(" is referenced by ");
0870: System.out.println(fkr.getString("FKTABLE_NAME") + "."
0871: + fkr.getString("FKCOLUMN_NAME"));
0872: }
0873: System.out.println("");
0874: fkr.close();
0875:
0876: System.out.println("Imported keys:");
0877: fkr = md.getCrossReference(null, null, null, catalog, schema,
0878: table);
0879: while (fkr.next()) {
0880: System.out.print(" " + fkr.getString("FKCOLUMN_NAME"));
0881: System.out.print(" references ");
0882: System.out.println(fkr.getString("PKTABLE_NAME") + "."
0883: + fkr.getString("PKCOLUMN_NAME"));
0884: }
0885: System.out.println("");
0886: fkr.close();
0887: }
0888:
0889: public void handleCommand_jdesc(Vector args) throws SQLException {
0890: if (!checkConnected())
0891: return;
0892: String catalog = connection.getCatalog();
0893: if (args.size() != 1) {
0894: System.out.println("Usage: jdesc [<schema>.]<tablename>");
0895: return;
0896: }
0897: String table = (String) args.firstElement();
0898: String schema = defaultSchema;
0899: if (table.indexOf(".") != -1) {
0900: StringTokenizer st = new StringTokenizer(table, ".");
0901: schema = st.nextToken();
0902: table = st.nextToken();
0903: }
0904:
0905: // get PK info
0906: Hashtable pks = new Hashtable();
0907: ResultSet r = connection.getMetaData().getPrimaryKeys(catalog,
0908: schema, table);
0909: while (r.next())
0910: pks.put(r.getString("COLUMN_NAME"), "blah");
0911: r.close();
0912:
0913: r = connection.getMetaData().getColumns(catalog, schema, table,
0914: null);
0915: boolean first = true;
0916: while (r.next()) {
0917: if (first) {
0918: if (schema == null) {
0919: schema = r.getString("TABLE_SCHEM");
0920: }
0921: //System.out.println(ResultSetUtil.formatResultSet(r));
0922: System.out.println("Schema: "
0923: + r.getString("TABLE_SCHEM"));
0924: System.out
0925: .println("Column Name java.sql.Types Size Null? PK");
0926: System.out
0927: .println("------------------------------------------------------------------------");
0928: // xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxx xxxxx xx
0929: // 30 16 12 5
0930: first = false;
0931: }
0932: String colSchema = r.getString("TABLE_SCHEM");
0933:
0934: if ((schema == null) || (schema.equals(colSchema))) {
0935: String nullable = r.getString("IS_NULLABLE");
0936: if (nullable.equalsIgnoreCase("YES"))
0937: nullable = "YES";
0938: else
0939: nullable = "NO";
0940: String colName = r.getString("COLUMN_NAME");
0941: System.out.print(" "
0942: + format(colName, 30)
0943: + " "
0944: + format(getType(r.getInt("DATA_TYPE")), 16)
0945: + " "
0946: + format(String
0947: .valueOf(r.getInt("COLUMN_SIZE")), 12)
0948: + " " + format(nullable, 6));
0949: if (pks.containsKey(colName))
0950: System.out.println("Y");
0951: else
0952: System.out.println("");
0953: }
0954: }
0955:
0956: if (first) // no rows
0957: {
0958: System.out.println(" Table \"" + table
0959: + "\" does not appear to exist.");
0960: }
0961:
0962: r.close();
0963: System.out.println("");
0964: }
0965:
0966: public void handleCommand_driverdata(Vector args)
0967: throws SQLException {
0968: if (!checkDriver())
0969: return;
0970: System.out.println("Class name: "
0971: + driver.getClass().getName());
0972: System.out.println("Driver Major Version: "
0973: + driver.getMajorVersion());
0974: System.out.println("Driver Minor Version: "
0975: + driver.getMinorVersion());
0976: }
0977:
0978: public void handleCommand_about(Vector args) {
0979: System.out.println("");
0980: System.out
0981: .println("SimpleListener -- a (very) simple SQL tool");
0982: System.out.println("");
0983: }
0984:
0985: private boolean checkDriver() {
0986: if (driver == null) {
0987: System.out
0988: .println("No driver loaded -- use \"driver drivername\"");
0989: return false;
0990: }
0991: return true;
0992: }
0993:
0994: private boolean checkConnected() {
0995: if (!checkDriver())
0996: return false;
0997: else if (connection == null) {
0998: System.out.println("Not connected -- use \"connect\"");
0999: return false;
1000: }
1001: return true;
1002: }
1003:
1004: private String format(String s, int length) {
1005: StringBuffer b = new StringBuffer(length);
1006: b.append(s);
1007: for (int i = 0; i < length - s.length(); i++)
1008: b.append(" ");
1009: return b.toString();
1010: }
1011:
1012: public void handleCommand_h(Vector args) {
1013: handleCommand_history(args);
1014: }
1015:
1016: public void handleCommand_history(Vector args) {
1017: if (args.size() > 0) {
1018: try {
1019: historySize = Integer.parseInt((String) args
1020: .elementAt(0));
1021: trimHistory();
1022: } catch (Exception x) {
1023: ;
1024: }
1025: }
1026: System.out.println("Command history (max size = " + historySize
1027: + "):");
1028: System.out.println("");
1029: Enumeration e = history.elements();
1030: int index = 0;
1031:
1032: while (e.hasMoreElements()) {
1033: System.out.println(" " + format(String.valueOf(index), 2)
1034: + ": " + e.nextElement());
1035: index++;
1036: }
1037: System.out.println("");
1038: System.out
1039: .println(" Type \"!X\" to execute command number X.");
1040: System.out.println("");
1041: System.out.println(" Type \"history n\" to set the length.");
1042: System.out.println("");
1043: }
1044:
1045: public void handleCommand_help(Vector args) {
1046: System.out.println("");
1047: System.out
1048: .println("Protomatter SimpleListener -- a simple SQL client.");
1049: System.out.println(" version " + Protomatter.VERSION);
1050: System.out.println("");
1051: System.out
1052: .println("Available commands: (in addition to normal SQL)");
1053: System.out.println(" about");
1054: System.out.println(" driver <drivername>");
1055: System.out
1056: .println(" connect <URL> [<username> [<password> [ key=val ... key=val ]]]");
1057: System.out
1058: .println(" (note: double-quotes are interpreted as you expect in \"connect\")");
1059: System.out.println(" disconnect");
1060: System.out.println(" commit");
1061: System.out.println(" rollback");
1062: System.out.println(" help");
1063: System.out.println(" metadata");
1064: System.out.println(" driverdata");
1065: System.out.println(" connectioninfo");
1066: System.out.println(" autocommit [on|off]");
1067: System.out.println(" showstacktrace [on|off]");
1068: System.out.println(" showquotes [on|off]");
1069: System.out.println(" isolation [level]");
1070: System.out.println(" run <filename>");
1071: System.out.println(" schemas");
1072: System.out.println(" schema <schema>");
1073: System.out.println(" catalogs");
1074: System.out.println(" catalog <catalogname>");
1075: System.out
1076: .println(" tables [<schema> [<table_name_substring>]]");
1077: System.out.println(" procedures [<schema>]");
1078: System.out.println(" indexes [<schema>.]<tablename>");
1079: System.out.println(" desc [<schema>.]<tablename>");
1080: System.out
1081: .println(" jdesc [<schema>.]<tablename> (describe table in terms of Java types)");
1082: System.out
1083: .println(" fkdesc [<schema>.]<tablename> (describe foreign keys on table)");
1084: System.out
1085: .println(" history ('h' works too) (display command history)");
1086: System.out.println(" / (executes last command again)");
1087: System.out.println(" quit");
1088: System.out.println("");
1089: System.out
1090: .println(" Note: commands can be spread over multiple lines");
1091: System.out
1092: .println(" if the last character on each line is a \"\\\"");
1093: System.out.println("");
1094: }
1095:
1096: public void handleCommand_connectioninfo(Vector args)
1097: throws SQLException {
1098: if (checkConnected()) {
1099: System.out.println("");
1100: System.out.println("Connection information:");
1101: System.out.println(" URL: " + this .url);
1102: System.out.println(" Properties:");
1103: int maxlen = 0;
1104: Enumeration e = this .props.keys();
1105: while (e.hasMoreElements()) {
1106: String key = (String) e.nextElement();
1107: if (key.length() > maxlen)
1108: maxlen = key.length();
1109: }
1110:
1111: e = this .props.keys();
1112: while (e.hasMoreElements()) {
1113: String key = (String) e.nextElement();
1114: System.out.println(" " + format(key, maxlen) + " = "
1115: + this .props.get(key));
1116: }
1117: System.out.println("");
1118: System.out.println(" Isolation level: "
1119: + getIsolationString(connection
1120: .getTransactionIsolation()));
1121: System.out.println(" Read Only: "
1122: + connection.isReadOnly());
1123: System.out.println(" Auto Commit: "
1124: + connection.getAutoCommit());
1125: System.out.println("");
1126: } else {
1127: System.out.println("");
1128: System.out
1129: .println("You are not connected to a database at this time.");
1130: System.out.println("");
1131: }
1132: }
1133:
1134: public void handleCommand_autocommit(Vector args)
1135: throws SQLException {
1136: if (checkConnected()) {
1137: boolean b = false;
1138: if (args.isEmpty()) {
1139: b = connection.getAutoCommit();
1140: } else {
1141: String value = (String) args.firstElement();
1142: if (value.equalsIgnoreCase("true")
1143: || value.equalsIgnoreCase("on"))
1144: b = true;
1145: connection.setAutoCommit(b);
1146: }
1147: System.out.println("Auto-Commit set to " + b);
1148: System.out.println("");
1149: } else {
1150: System.out.println("");
1151: System.out
1152: .println("You are not connected to a database at this time.");
1153: System.out.println("");
1154: }
1155: }
1156:
1157: public void handleCommand_showstacktrace(Vector args)
1158: throws SQLException {
1159: boolean b = false;
1160: if (args.isEmpty()) {
1161: b = showStackTrace;
1162: } else {
1163: String value = (String) args.firstElement();
1164: if (value.equalsIgnoreCase("true")
1165: || value.equalsIgnoreCase("on"))
1166: b = true;
1167: showStackTrace = b;
1168: }
1169: System.out.println("Show stack trace set to " + b);
1170: System.out.println("");
1171: }
1172:
1173: public void handleCommand_showquotes(Vector args)
1174: throws SQLException {
1175: boolean b = false;
1176: if (args.isEmpty()) {
1177: b = showQuotes;
1178: } else {
1179: String value = (String) args.firstElement();
1180: if (value.equalsIgnoreCase("true")
1181: || value.equalsIgnoreCase("on"))
1182: b = true;
1183: showQuotes = b;
1184: }
1185: System.out.println("Show quotes set to " + b);
1186: System.out.println("");
1187: }
1188:
1189: private static final String getType(int type) {
1190: // look for a type of that value on the Types object
1191: try {
1192: Field fields[] = Types.class.getFields();
1193: for (int i = 0; i < fields.length; i++) {
1194: Number n = (Number) fields[i].get(null);
1195: int val = n.intValue();
1196: if (val == type)
1197: return fields[i].getName();
1198: }
1199: } catch (Exception x) {
1200: ;
1201: }
1202: return null;
1203: }
1204:
1205: private void addToHistory(String command) {
1206: if (!history.contains(command))
1207: history.addElement(command);
1208: trimHistory();
1209: }
1210:
1211: private String getHistoryCommand(int index) {
1212: return (String) history.elementAt(index);
1213: }
1214:
1215: private void trimHistory() {
1216: if (history.size() > historySize) {
1217: int remove = history.size() - historySize;
1218: for (int i = 0; i < remove; i++)
1219: history.removeElementAt(i);
1220: }
1221: }
1222: }
|