Java tutorial
/* * This is free software, licensed under the Gnu Public License (GPL) get a copy from <http://www.gnu.org/licenses/gpl.html> * * author: Henner Zeller <H.Zeller@acm.org> */ package henplus.commands; import henplus.AbstractCommand; import henplus.CommandDispatcher; import henplus.HenPlus; import henplus.PropertyRegistry; import henplus.SQLSession; import henplus.SigIntHandler; import henplus.logging.Logger; import henplus.property.BooleanPropertyHolder; import henplus.property.PropertyHolder; import henplus.view.util.CancelWriter; import henplus.view.util.NameCompleter; import java.sql.ResultSet; import java.sql.Statement; import java.util.Collection; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedList; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import java.util.StringTokenizer; import org.apache.commons.cli.CommandLine; import org.apache.commons.cli.Option; /** * document me. */ public final class SQLCommand extends AbstractCommand { private static final String[] TABLE_COMPLETER_KEYWORD = { "FROM", "INTO", "UPDATE", "TABLE", "ALIAS", "VIEW", /* create index */ "ON" }; /** * returns the command-strings this command can handle. */ @Override public String[] getCommandList() { return new String[] { // provide tab-completion at least for these command starts.. "select", "insert", "update", "delete", "create", "alter", "drop", "commit", "rollback", /* "call-procedure", test */ // we support _any_ string, that is not part of the // henplus buildin-stuff; the following empty string flags this. "" }; } private final ListUserObjectsCommand _tableCompleter; private Statement _stmt; private String _columnDelimiter; private int _rowLimit; private boolean _showHeader; private boolean _showFooter; private volatile boolean _running; private StatementCanceller _statementCanceller; protected SQLCommand(final ListUserObjectsCommand tc) { _columnDelimiter = "|"; _rowLimit = 2000; _tableCompleter = tc; } private LongRunningTimeDisplay _longRunningDisplay; public SQLCommand(final ListUserObjectsCommand tc, final PropertyRegistry registry) { _tableCompleter = tc; _columnDelimiter = "|"; _rowLimit = 2000; _showHeader = true; _showFooter = true; registry.registerProperty("column-delimiter", new SQLColumnDelimiterProperty()); registry.registerProperty("sql-result-limit", new RowLimitProperty()); registry.registerProperty("sql-result-showheader", new ShowHeaderProperty()); registry.registerProperty("sql-result-showfooter", new ShowFooterProperty()); _statementCanceller = new StatementCanceller(new CurrentStatementCancelTarget()); new Thread(_statementCanceller).start(); _longRunningDisplay = new LongRunningTimeDisplay("statement running", 30000); new Thread(_longRunningDisplay).start(); } /** * don't show the commands available in the toplevel command completion list .. */ @Override public boolean participateInCommandCompletion() { return false; } /** * complicated SQL statements are only complete with semicolon. Simple commands may have no semicolon (like 'commit' and * 'rollback'). Yet others are not complete even if we encounter a semicolon (like triggers and stored procedures). We support * the SQL*PLUS syntax in that we consider these kind of statements complete with a single slash ('/') at the beginning of a * line. */ @Override public boolean isComplete(String command) { command = command.toUpperCase(); // fixme: expensive. if (command.startsWith("COMMIT") || command.startsWith("ROLLBACK")) { return true; } // FIXME: this is a very dumb 'parser'. // i.e. string literals are not considered. final boolean anyProcedure = command.startsWith("BEGIN") || command.startsWith("DECLARE") || (command.startsWith("CREATE") || command.startsWith("REPLACE")) && (containsWord(command, "PROCEDURE") || containsWord(command, "FUNCTION") || containsWord(command, "PACKAGE") || containsWord(command, "TRIGGER")); if (!anyProcedure && command.endsWith(";")) { return true; } // sqlplus is complete on a single '/' on a line. if (command.length() >= 3) { final int lastPos = command.length() - 1; if (command.charAt(lastPos) == '\n' && command.charAt(lastPos - 1) == '/' && command.charAt(lastPos - 2) == '\n') { return true; } } return false; } public void setColumnDelimiter(final String value) { _columnDelimiter = value; } public String getColumnDelimiter() { return _columnDelimiter; } public void setRowLimit(final int rowLimit) { _rowLimit = rowLimit; } public int getRowLimit() { return _rowLimit; } public void setShowHeader(final boolean b) { _showHeader = b; } public boolean isShowHeader() { return _showHeader; } public void setShowFooter(final boolean b) { _showFooter = b; } public boolean isShowFooter() { return _showFooter; } @Override public Collection<Option> getHandledCommandLineOptions() { final Collection<Option> result = new LinkedList<Option>(); final Option option = new Option("S", "SQL", true, "SQL to execute"); option.setArgName("SELECT ..."); result.add(option); return result; } @Override public void handleCommandline(final CommandLine line) { String sql = null; if (line.hasOption("S")) { sql = line.getOptionValue("S"); } if (sql != null && HenPlus.getInstance().getCurrentSession() != null) { try { CommandDispatcher disp = HenPlus.getInstance().getDispatcher(); disp.execute(HenPlus.getInstance().getCurrentSession(), sql); disp.execute(HenPlus.getInstance().getCurrentSession(), "exit;"); } catch (final Exception e) { e.printStackTrace(); HenPlus.msg().println(e.getMessage()); } } } /** * A statement cancel target that accesses the instance wide statement. */ private final class CurrentStatementCancelTarget implements StatementCanceller.CancelTarget { @Override public void cancelRunningStatement() { try { HenPlus.msg().println("cancel statement..."); HenPlus.msg().flush(); final CancelWriter info = new CancelWriter(HenPlus.msg()); info.print("please wait"); _stmt.cancel(); info.cancel(); HenPlus.msg().println("done."); _running = false; } catch (final Exception e) { Logger.debug("Exception while cancelling a statement: ", e); } } } /** * looks, if this word is contained in 'all', preceeded and followed by a whitespace. */ private boolean containsWord(final String all, final String word) { final int wordLen = word.length(); final int index = all.indexOf(word); return index >= 0 && (index == 0 || Character.isWhitespace(all.charAt(index - 1))) && Character.isWhitespace(all.charAt(index + wordLen)); } /** * execute the command given. */ @Override public int execute(final SQLSession session, final String cmd, final String param) { String command = cmd + " " + param; // boolean background = false; if (command.endsWith("/")) { command = command.substring(0, command.length() - 1); } // if (command.endsWith("&")) { // command = command.substring(0, command.length()-1); // HenPlus.msg().println( // "## executing command in the background not yet supported"); // background = true; // } final long startTime = System.currentTimeMillis(); long lapTime = -1; long execTime = -1; ResultSet rset = null; _running = true; SigIntHandler.getInstance().pushInterruptable(_statementCanceller); try { if (command.startsWith("commit")) { session.print("commit.."); session.getConnection().commit(); session.println(".done."); } else if (command.startsWith("rollback")) { session.print("rollback.."); session.getConnection().rollback(); session.println(".done."); } else { _stmt = session.createStatement(); try { _stmt.setFetchSize(200); } catch (final Exception e) { /* ignore */ } _statementCanceller.arm(); _longRunningDisplay.arm(); final boolean hasResultSet = _stmt.execute(command); _longRunningDisplay.disarm(); if (!_running) { HenPlus.msg().println("cancelled"); return SUCCESS; } if (hasResultSet) { do { rset = _stmt.getResultSet(); ResultSetRenderer renderer; renderer = new ResultSetRenderer(rset, getColumnDelimiter(), isShowHeader(), isShowFooter(), getRowLimit(), HenPlus.out()); SigIntHandler.getInstance().pushInterruptable(renderer); final int rows = renderer.execute(); SigIntHandler.getInstance().popInterruptable(); if (renderer.limitReached()) { session.println("limit of " + getRowLimit() + " rows reached .."); session.print("> "); } session.println(rows + " row" + (rows == 1 ? "" : "s") + " in result"); lapTime = renderer.getFirstRowTime() - startTime; } while (_stmt.getMoreResults()); } else { final int updateCount = _stmt.getUpdateCount(); if (updateCount >= 0) { session.print("affected " + updateCount + " rows"); } else { session.print("ok."); } } execTime = System.currentTimeMillis() - startTime; session.print(" ("); if (lapTime > 0) { session.print("first row: "); if (session.printMessages()) { TimeRenderer.printTime(lapTime, HenPlus.msg()); } session.print("; total: "); } if (session.printMessages()) { TimeRenderer.printTime(execTime, HenPlus.msg()); } session.println(")"); } // be smart and retrigger hashing of the tablenames. if ("drop".equals(cmd) || "create".equals(cmd)) { _tableCompleter.unhash(session); } return SUCCESS; } catch (final Exception e) { final String msg = e.getMessage(); if (msg != null) { // oracle appends a newline to the message for some reason. Logger.error("FAILURE: '%s'", e.getMessage()); Logger.debug("Exception: ", e); } return EXEC_FAILED; } finally { _statementCanceller.disarm(); _longRunningDisplay.disarm(); try { if (rset != null) { rset.close(); } } catch (final Exception e) { } try { if (_stmt != null) { _stmt.close(); } } catch (final Exception e) { } SigIntHandler.getInstance().popInterruptable(); } } // very simple completer: try to determine wether we can complete a // table name. that is: if some keyword has been found before, switch to // table-completer-mode :-) @Override public Iterator<String> complete(final CommandDispatcher disp, final String partialCommand, final String lastWord) { final String canonCmd = partialCommand.toUpperCase(); /* * look for keywords that expect table names */ int tableMatch = -1; for (int i = 0; i < TABLE_COMPLETER_KEYWORD.length; ++i) { final int match = canonCmd.indexOf(TABLE_COMPLETER_KEYWORD[i]); if (match >= 0) { tableMatch = match + TABLE_COMPLETER_KEYWORD[i].length(); break; } } if (tableMatch < 0) { /* * ok, try to complete all columns from all tables since we don't * know yet what table the column will be from. */ return _tableCompleter.completeAllColumns(lastWord); } int endTabMatch = -1; // where the table declaration ends. if (canonCmd.indexOf("UPDATE") >= 0) { endTabMatch = canonCmd.indexOf("SET"); } else if (canonCmd.indexOf("INSERT") >= 0) { endTabMatch = canonCmd.indexOf("("); } else if (canonCmd.indexOf("WHERE") >= 0) { endTabMatch = canonCmd.indexOf("WHERE"); } else if (canonCmd.indexOf("ORDER BY") >= 0) { endTabMatch = canonCmd.indexOf("ORDER BY"); } else if (canonCmd.indexOf("GROUP BY") >= 0) { endTabMatch = canonCmd.indexOf("GROUP BY"); } if (endTabMatch < 0) { endTabMatch = canonCmd.indexOf(";"); } if (endTabMatch > tableMatch) { /* * column completion for the tables mentioned between in the table * area. This acknowledges as well aliases and prepends the names * with these aliases, if necessary. */ final String tables = partialCommand.substring(tableMatch, endTabMatch); final HashMap<String, Set<String>> tmp = new HashMap<String, Set<String>>(); for (Entry<String, String> entry : tableDeclParser(tables).entrySet()) { final String alias = entry.getKey(); String tabName = entry.getValue(); tabName = _tableCompleter.correctTableName(tabName); if (tabName == null) { continue; } final Collection<String> columns = _tableCompleter.columnsFor(tabName); final Iterator<String> cit = columns.iterator(); while (cit.hasNext()) { final String col = cit.next(); Set<String> aliases = tmp.get(col); if (aliases == null) { aliases = new HashSet<String>(); } aliases.add(alias); tmp.put(col, aliases); } } final NameCompleter completer = new NameCompleter(); for (Entry<String, Set<String>> entry : tmp.entrySet()) { final String col = entry.getKey(); final Set<String> aliases = entry.getValue(); if (aliases.size() == 1) { completer.addName(col); } else { for (String name : aliases) { completer.addName(name); } } } return completer.getAlternatives(lastWord); } else { // table completion. return _tableCompleter.completeTableName(HenPlus.getInstance().getCurrentSession(), lastWord); } } /** * parses 'tablename ((AS)? alias)? [,...]' and returns a map, that maps the names (or aliases) to the tablenames. */ private Map<String, String> tableDeclParser(final String tableDecl) { final StringTokenizer tokenizer = new StringTokenizer(tableDecl, " \t\n\r\f,", true); final Map<String, String> result = new HashMap<String, String>(); String tok; String table = null; String alias = null; int state = 0; while (tokenizer.hasMoreElements()) { tok = tokenizer.nextToken(); if (tok.length() == 1 && Character.isWhitespace(tok.charAt(0))) { continue; } switch (state) { case 0: { // initial/endstate table = tok; alias = tok; state = 1; break; } case 1: { // table seen, waiting for potential alias. if ("AS".equals(tok.toUpperCase())) { state = 2; } else if (",".equals(tok)) { state = 0; // we are done. } else { alias = tok; state = 3; } break; } case 2: { // 'AS' seen, waiting definitly for alias. if (",".equals(tok)) { // error: alias missing for $table. state = 0; } else { alias = tok; state = 3; } break; } case 3: { // waiting for ',' at end of 'table (as)? alias' if (!",".equals(tok)) { // error: ',' expected. } state = 0; break; } } if (state == 0) { result.put(alias, table); } } // store any unfinished state.. if (state == 1 || state == 3) { result.put(alias, table); } else if (state == 2) { // error: alias expected for $table. } return result; } @Override public void shutdown() { _statementCanceller.stopThread(); } @Override public String getSynopsis(String cmd) { cmd = cmd.toLowerCase(); String syn = null; if ("select".equals(cmd)) { syn = "select <columns> from <table[s]> [ where <where-clause>]"; } else if ("insert".equals(cmd)) { syn = "insert into <table> [(<columns>])] values (<values>)"; } else if ("delete".equals(cmd)) { syn = "delete from <table> [ where <where-clause>]"; } else if ("update".equals(cmd)) { syn = "update <table> set <column>=<value>[,...] [ where <where-clause> ]"; } else if ("drop".equals(cmd)) { syn = "drop <table|index|view|alias|...>"; } else if ("commit".equals(cmd)) { syn = cmd; } else if ("rollback".equals(cmd)) { syn = cmd; } return syn; } @Override public String getLongDescription(String cmd) { String dsc; dsc = "\t'" + cmd + "': this is not a build-in command, so would be\n" + "\tconsidered as SQL-command and handed over to the JDBC-driver.\n" + "\tHowever, I don't know anything about its syntax. RTFSQLM.\n" + "\ttry <http://www.google.com/search?q=sql+syntax+" + cmd + ">"; cmd = cmd.toLowerCase(); if ("select".equals(cmd)) { dsc = "\tselect from tables."; } else if ("delete".equals(cmd)) { dsc = "\tdelete data from tables. DML."; } else if ("insert".equals(cmd)) { dsc = "\tinsert data into tables. DML."; } else if ("update".equals(cmd)) { dsc = "\tupdate existing rows with new data. DML."; } else if ("create".equals(cmd)) { dsc = "\tcreate new database object (such as tables/views/indices..). DDL."; } else if ("alter".equals(cmd)) { dsc = "\talter a database object. DDL."; } else if ("drop".equals(cmd)) { dsc = "\tdrop (remove) a database object. DDL."; } else if ("rollback".equals(cmd)) { dsc = "\trollback transaction."; } else if ("commit".equals(cmd)) { dsc = "\tcommit transaction."; } else if ("call-procedure".equals(cmd)) { dsc = "\tcall a function that returns exactly one parameter\n" + "\tthat can be gathered as string (EXPERIMENTAL)\n" + "\texample:\n" + "\t call-procedure foobar(42);\n"; } return dsc; } private class SQLColumnDelimiterProperty extends PropertyHolder { public SQLColumnDelimiterProperty() { super(SQLCommand.this.getColumnDelimiter()); } @Override protected String propertyChanged(final String newValue) { SQLCommand.this.setColumnDelimiter(newValue); return newValue; } @Override public String getShortDescription() { return "modify column separator in query results"; } @Override public String getDefaultValue() { return "|"; } @Override public String getLongDescription() { String dsc; dsc = "\tSet another string that is used to separate columns in\n" + "\tSQL result sets. Usually this is a pipe-symbol '|', but\n" + "\tmaybe you want to have an empty string ?"; return dsc; } } private class RowLimitProperty extends PropertyHolder { public RowLimitProperty() { super(String.valueOf(SQLCommand.this.getRowLimit())); } @Override protected String propertyChanged(String newValue) throws Exception { newValue = newValue.trim(); int newIntValue; try { newIntValue = Integer.parseInt(newValue); } catch (final NumberFormatException e) { throw new IllegalArgumentException("cannot parse '" + newValue + "' as integer"); } if (newIntValue < 1) { throw new IllegalArgumentException("value cannot be less than 1"); } SQLCommand.this.setRowLimit(newIntValue); return newValue; } @Override public String getDefaultValue() { return "2000"; } @Override public String getShortDescription() { return "set the maximum number of rows printed"; } } private class ShowHeaderProperty extends BooleanPropertyHolder { public ShowHeaderProperty() { super(true); } @Override public void booleanPropertyChanged(final boolean value) { setShowHeader(value); } @Override public String getDefaultValue() { return "on"; } /** * return a short descriptive string. */ @Override public String getShortDescription() { return "switches if header in selected tables should be shown"; } } private class ShowFooterProperty extends BooleanPropertyHolder { public ShowFooterProperty() { super(true); } @Override public void booleanPropertyChanged(final boolean value) { setShowFooter(value); } @Override public String getDefaultValue() { return "on"; } /** * return a short descriptive string. */ @Override public String getShortDescription() { return "switches if footer in selected tables should be shown"; } } } /* * Local variables: c-basic-offset: 4 compile-command: * "ant -emacs -find build.xml" End: */