001: /*
002: * This is free software, licensed under the Gnu Public License (GPL)
003: * get a copy from <http://www.gnu.org/licenses/gpl.html>
004: *
005: * author: Henner Zeller <H.Zeller@acm.org>
006: */
007: package henplus.commands;
008:
009: import henplus.AbstractCommand;
010: import henplus.CommandDispatcher;
011: import henplus.HenPlus;
012: import henplus.PropertyRegistry;
013: import henplus.SQLSession;
014: import henplus.SigIntHandler;
015: import henplus.property.PropertyHolder;
016: import henplus.property.BooleanPropertyHolder;
017: import henplus.view.util.NameCompleter;
018: import henplus.view.util.CancelWriter;
019:
020: import java.sql.ResultSet;
021: import java.sql.Statement;
022: import java.util.Collection;
023: import java.util.HashMap;
024: import java.util.HashSet;
025: import java.util.Iterator;
026: import java.util.Map;
027: import java.util.Set;
028: import java.util.StringTokenizer;
029:
030: /**
031: * document me.
032: */
033: public class SQLCommand extends AbstractCommand {
034: private static final boolean verbose = false; // debug.
035: private final static String[] TABLE_COMPLETER_KEYWORD = { "FROM",
036: "INTO", "UPDATE", "TABLE", /*create index*/"ON" };
037:
038: /**
039: * returns the command-strings this command can handle.
040: */
041: public String[] getCommandList() {
042: return new String[] {
043: // provide tab-completion at least for these command starts..
044: "select", "insert", "update", "delete", "create",
045: "alter", "drop", "commit", "rollback",
046: /*"call-procedure", test */
047: // we support _any_ string, that is not part of the
048: // henplus buildin-stuff; the following empty string flags this.
049: "" };
050: }
051:
052: private final ListUserObjectsCommand tableCompleter;
053: private Statement _stmt;
054: private String _columnDelimiter;
055: private int _rowLimit;
056: private boolean _showHeader;
057: private boolean _showFooter;
058: private volatile boolean _running;
059: private StatementCanceller _statementCanceller;
060:
061: protected SQLCommand(ListUserObjectsCommand tc) {
062: _columnDelimiter = "|";
063: _rowLimit = 2000;
064: tableCompleter = tc;
065: }
066:
067: private LongRunningTimeDisplay _longRunningDisplay;
068:
069: public SQLCommand(ListUserObjectsCommand tc,
070: PropertyRegistry registry) {
071: tableCompleter = tc;
072: _columnDelimiter = "|";
073: _rowLimit = 2000;
074: _showHeader = true;
075: _showFooter = true;
076: registry.registerProperty("column-delimiter",
077: new SQLColumnDelimiterProperty());
078: registry.registerProperty("sql-result-limit",
079: new RowLimitProperty());
080: registry.registerProperty("sql-result-showheader",
081: new ShowHeaderProperty());
082: registry.registerProperty("sql-result-showfooter",
083: new ShowFooterProperty());
084: _statementCanceller = new StatementCanceller(
085: new CurrentStatementCancelTarget());
086: new Thread(_statementCanceller).start();
087: _longRunningDisplay = new LongRunningTimeDisplay(
088: "statement running", 30000);
089: new Thread(_longRunningDisplay).start();
090: }
091:
092: /**
093: * don't show the commands available in the toplevel
094: * command completion list ..
095: */
096: public boolean participateInCommandCompletion() {
097: return false;
098: }
099:
100: /**
101: * complicated SQL statements are only complete with
102: * semicolon. Simple commands may have no semicolon (like
103: * 'commit' and 'rollback'). Yet others are not complete even
104: * if we ecounter a semicolon (like triggers and stored
105: * procedures). We support the SQL*PLUS syntax in that we consider these
106: * kind of statements complete with a single slash ('/') at the
107: * beginning of a line.
108: */
109: public boolean isComplete(String command) {
110: command = command.toUpperCase(); // fixme: expensive.
111: if (command.startsWith("COMMIT")
112: || command.startsWith("ROLLBACK"))
113: return true;
114: // FIXME: this is a very dumb 'parser'.
115: // i.e. string literals are not considered.
116: boolean anyProcedure = (command.startsWith("BEGIN")
117: || command.startsWith("DECLARE") || ((command
118: .startsWith("CREATE") || command.startsWith("REPLACE")) && ((containsWord(
119: command, "PROCEDURE")
120: || (containsWord(command, "FUNCTION"))
121: || (containsWord(command, "PACKAGE")) || (containsWord(
122: command, "TRIGGER"))))));
123:
124: if (!anyProcedure && command.endsWith(";"))
125: return true;
126: // sqlplus is complete on a single '/' on a line.
127: if (command.length() >= 3) {
128: int lastPos = command.length() - 1;
129: if (command.charAt(lastPos) == '\n'
130: && command.charAt(lastPos - 1) == '/'
131: && command.charAt(lastPos - 2) == '\n')
132: return true;
133: }
134: return false;
135: }
136:
137: public void setColumnDelimiter(String value) {
138: _columnDelimiter = value;
139: }
140:
141: public String getColumnDelimiter() {
142: return _columnDelimiter;
143: }
144:
145: public void setRowLimit(int rowLimit) {
146: _rowLimit = rowLimit;
147: }
148:
149: public int getRowLimit() {
150: return _rowLimit;
151: }
152:
153: public void setShowHeader(boolean b) {
154: _showHeader = b;
155: }
156:
157: public boolean isShowHeader() {
158: return _showHeader;
159: }
160:
161: public void setShowFooter(boolean b) {
162: _showFooter = b;
163: }
164:
165: public boolean isShowFooter() {
166: return _showFooter;
167: }
168:
169: /**
170: * A statement cancel target that accesses the instance
171: * wide statement.
172: */
173: private final class CurrentStatementCancelTarget implements
174: StatementCanceller.CancelTarget {
175: public void cancelRunningStatement() {
176: try {
177: HenPlus.msg().println("cancel statement...");
178: HenPlus.msg().flush();
179: CancelWriter info = new CancelWriter(HenPlus.msg());
180: info.print("please wait");
181: _stmt.cancel();
182: info.cancel();
183: HenPlus.msg().println("done.");
184: _running = false;
185: } catch (Exception e) {
186: if (verbose)
187: e.printStackTrace();
188: }
189: }
190: }
191:
192: /**
193: * looks, if this word is contained in 'all', preceeded and followed by
194: * a whitespace.
195: */
196: private boolean containsWord(String all, String word) {
197: int wordLen = word.length();
198: int index = all.indexOf(word);
199: return (index >= 0
200: && (index == 0 || Character.isWhitespace(all
201: .charAt(index - 1))) && (Character
202: .isWhitespace(all.charAt(index + wordLen))));
203: }
204:
205: /**
206: * execute the command given.
207: */
208: public int execute(SQLSession session, String cmd, String param) {
209: String command = cmd + " " + param;
210: //boolean background = false;
211:
212: if (command.endsWith("/")) {
213: command = command.substring(0, command.length() - 1);
214: }
215:
216: // if (command.endsWith("&")) {
217: // command = command.substring(0, command.length()-1);
218: // HenPlus.msg().println("## executing command in the background not yet supported");
219: // background = true;
220: // }
221:
222: final long startTime = System.currentTimeMillis();
223: long lapTime = -1;
224: long execTime = -1;
225: ResultSet rset = null;
226: _running = true;
227: SigIntHandler.getInstance().pushInterruptable(
228: _statementCanceller);
229: try {
230: if (command.startsWith("commit")) {
231: session.print("commit..");
232: session.getConnection().commit();
233: session.println(".done.");
234: } else if (command.startsWith("rollback")) {
235: session.print("rollback..");
236: session.getConnection().rollback();
237: session.println(".done.");
238: } else {
239: _stmt = session.createStatement();
240: try {
241: _stmt.setFetchSize(200);
242: } catch (Exception e) {
243: /* ignore */
244: }
245:
246: _statementCanceller.arm();
247: _longRunningDisplay.arm();
248: final boolean hasResultSet = _stmt.execute(command);
249: _longRunningDisplay.disarm();
250:
251: if (!_running) {
252: HenPlus.msg().println("cancelled");
253: return SUCCESS;
254: }
255:
256: if (hasResultSet) {
257: rset = _stmt.getResultSet();
258: ResultSetRenderer renderer;
259: renderer = new ResultSetRenderer(rset,
260: getColumnDelimiter(), isShowHeader(),
261: isShowFooter(), getRowLimit(), HenPlus
262: .out());
263: SigIntHandler.getInstance().pushInterruptable(
264: renderer);
265: int rows = renderer.execute();
266: SigIntHandler.getInstance().popInterruptable();
267: if (renderer.limitReached()) {
268: session.println("limit of " + getRowLimit()
269: + " rows reached ..");
270: session.print("> ");
271: }
272: session.print(rows + " row"
273: + ((rows == 1) ? "" : "s") + " in result");
274: lapTime = renderer.getFirstRowTime() - startTime;
275: } else {
276: int updateCount = _stmt.getUpdateCount();
277: if (updateCount >= 0) {
278: session.print("affected " + updateCount
279: + " rows");
280: } else {
281: session.print("ok.");
282: }
283: }
284: execTime = System.currentTimeMillis() - startTime;
285: session.print(" (");
286: if (lapTime > 0) {
287: session.print("first row: ");
288: if (session.printMessages()) {
289: TimeRenderer.printTime(lapTime, HenPlus.msg());
290: }
291: session.print("; total: ");
292: }
293: if (session.printMessages()) {
294: TimeRenderer.printTime(execTime, HenPlus.msg());
295: }
296: session.println(")");
297: }
298:
299: // be smart and retrigger hashing of the tablenames.
300: if ("drop".equals(cmd) || "create".equals(cmd)) {
301: tableCompleter.unhash(session);
302: }
303:
304: return SUCCESS;
305: }
306: /*
307: catch (InterruptedException ie) {
308: session.print("interrupted after ");
309: execTime = System.currentTimeMillis() - startTime;
310: TimeRenderer.printTime(execTime, HenPlus.msg());
311: session.println(".");
312: return SUCCESS;
313: }
314: */
315: catch (Exception e) {
316: String msg = e.getMessage();
317: if (msg != null) {
318: // oracle appends a newline to the message for some reason.
319: HenPlus.msg().println("FAILURE: " + msg.trim());
320: }
321: if (verbose)
322: e.printStackTrace();
323: return EXEC_FAILED;
324: } finally {
325: _statementCanceller.disarm();
326: _longRunningDisplay.disarm();
327: try {
328: if (rset != null)
329: rset.close();
330: } catch (Exception e) {
331: }
332: try {
333: if (_stmt != null)
334: _stmt.close();
335: } catch (Exception e) {
336: }
337: SigIntHandler.getInstance().popInterruptable();
338: }
339: }
340:
341: // very simple completer: try to determine wether we can complete a
342: // table name. that is: if some keyword has been found before, switch to
343: // table-completer-mode :-)
344: public Iterator complete(CommandDispatcher disp,
345: String partialCommand, final String lastWord) {
346: final String canonCmd = partialCommand.toUpperCase();
347: /*
348: * look for keywords that expect table names
349: */
350: int tableMatch = -1;
351: for (int i = 0; i < TABLE_COMPLETER_KEYWORD.length; ++i) {
352: int match = canonCmd.indexOf(TABLE_COMPLETER_KEYWORD[i]);
353: if (match >= 0) {
354: tableMatch = match
355: + TABLE_COMPLETER_KEYWORD[i].length();
356: break;
357: }
358: }
359:
360: if (tableMatch < 0) {
361: /*
362: * ok, try to complete all columns from all tables since
363: * we don't know yet what table the column will be from.
364: */
365: return tableCompleter.completeAllColumns(lastWord);
366: }
367:
368: int endTabMatch = -1; // where the table declaration ends.
369: if (canonCmd.indexOf("UPDATE") >= 0) {
370: endTabMatch = canonCmd.indexOf("SET");
371: } else if (canonCmd.indexOf("INSERT") >= 0) {
372: endTabMatch = canonCmd.indexOf("(");
373: } else if (canonCmd.indexOf("WHERE") >= 0) {
374: endTabMatch = canonCmd.indexOf("WHERE");
375: } else if (canonCmd.indexOf("ORDER BY") >= 0) {
376: endTabMatch = canonCmd.indexOf("ORDER BY");
377: } else if (canonCmd.indexOf("GROUP BY") >= 0) {
378: endTabMatch = canonCmd.indexOf("GROUP BY");
379: }
380: if (endTabMatch < 0) {
381: endTabMatch = canonCmd.indexOf(";");
382: }
383:
384: if (endTabMatch > tableMatch) {
385: /*
386: * column completion for the tables mentioned between in the
387: * table area. This acknowledges as well aliases and prepends
388: * the names with these aliases, if necessary.
389: */
390: String tables = partialCommand.substring(tableMatch,
391: endTabMatch);
392: HashMap tmp = new HashMap();
393: Iterator it = tableDeclParser(tables).entrySet().iterator();
394: while (it.hasNext()) {
395: Map.Entry entry = (Map.Entry) it.next();
396: String alias = (String) entry.getKey();
397: String tabName = (String) entry.getValue();
398: tabName = tableCompleter.correctTableName(tabName);
399: if (tabName == null)
400: continue;
401: Collection columns = tableCompleter.columnsFor(tabName);
402: Iterator cit = columns.iterator();
403: while (cit.hasNext()) {
404: String col = (String) cit.next();
405: Set aliases = (Set) tmp.get(col);
406: if (aliases == null)
407: aliases = new HashSet();
408: aliases.add(alias);
409: tmp.put(col, aliases);
410: }
411: }
412: NameCompleter completer = new NameCompleter();
413: it = tmp.entrySet().iterator();
414: while (it.hasNext()) {
415: Map.Entry entry = (Map.Entry) it.next();
416: String col = (String) entry.getKey();
417: Set aliases = (Set) entry.getValue();
418: if (aliases.size() == 1) {
419: completer.addName(col);
420: } else {
421: Iterator ait = aliases.iterator();
422: while (ait.hasNext()) {
423: completer.addName(ait.next() + "." + col);
424: }
425: }
426: }
427: return completer.getAlternatives(lastWord);
428: } else { // table completion.
429: return tableCompleter.completeTableName(HenPlus
430: .getInstance().getCurrentSession(), lastWord);
431: }
432: }
433:
434: /**
435: * parses 'tablename ((AS)? alias)? [,...]' and returns a map, that maps
436: * the names (or aliases) to the tablenames.
437: */
438: private Map tableDeclParser(String tableDecl) {
439: StringTokenizer tokenizer = new StringTokenizer(tableDecl,
440: " \t\n\r\f,", true);
441: Map result = new HashMap();
442: String tok;
443: String table = null;
444: String alias = null;
445: int state = 0;
446: while (tokenizer.hasMoreElements()) {
447: tok = tokenizer.nextToken();
448: if (tok.length() == 1
449: && Character.isWhitespace(tok.charAt(0)))
450: continue;
451: switch (state) {
452: case 0: { // initial/endstate
453: table = tok;
454: alias = tok;
455: state = 1;
456: break;
457: }
458: case 1: { // table seen, waiting for potential alias.
459: if ("AS".equals(tok.toUpperCase()))
460: state = 2;
461: else if (",".equals(tok)) {
462: state = 0; // we are done.
463: } else {
464: alias = tok;
465: state = 3;
466: }
467: break;
468: }
469: case 2: { // 'AS' seen, waiting definitly for alias.
470: if (",".equals(tok)) {
471: // error: alias missing for $table.
472: state = 0;
473: } else {
474: alias = tok;
475: state = 3;
476: }
477: break;
478: }
479: case 3: { // waiting for ',' at end of 'table (as)? alias'
480: if (!",".equals(tok)) {
481: // error: ',' expected.
482: }
483: state = 0;
484: break;
485: }
486: }
487:
488: if (state == 0) {
489: result.put(alias, table);
490: }
491: }
492: // store any unfinished state..
493: if (state == 1 || state == 3) {
494: result.put(alias, table);
495: } else if (state == 2) {
496: // error: alias expected for $table.
497: }
498: return result;
499: }
500:
501: public void shutdown() {
502: _statementCanceller.stopThread();
503: }
504:
505: public String getSynopsis(String cmd) {
506: cmd = cmd.toLowerCase();
507: String syn = null;
508: if ("select".equals(cmd)) {
509: syn = "select <columns> from <table[s]> [ where <where-clause>]";
510: } else if ("insert".equals(cmd)) {
511: syn = "insert into <table> [(<columns>])] values (<values>)";
512: } else if ("delete".equals(cmd)) {
513: syn = "delete from <table> [ where <where-clause>]";
514: } else if ("update".equals(cmd)) {
515: syn = "update <table> set <column>=<value>[,...] [ where <where-clause> ]";
516: } else if ("drop".equals(cmd)) {
517: syn = "drop <table|index|view|...>";
518: } else if ("commit".equals(cmd)) {
519: syn = cmd;
520: } else if ("rollback".equals(cmd)) {
521: syn = cmd;
522: }
523: return syn;
524: }
525:
526: public String getLongDescription(String cmd) {
527: String dsc;
528: dsc = "\t'"
529: + cmd
530: + "': this is not a build-in command, so would be\n"
531: + "\tconsidered as SQL-command and handed over to the JDBC-driver.\n"
532: + "\tHowever, I don't know anything about its syntax. RTFSQLM.\n"
533: + "\ttry <http://www.google.de/search?q=sql+syntax+"
534: + cmd + ">";
535: cmd = cmd.toLowerCase();
536: if ("select".equals(cmd)) {
537: dsc = "\tselect from tables.";
538: } else if ("delete".equals(cmd)) {
539: dsc = "\tdelete data from tables. DML.";
540: } else if ("insert".equals(cmd)) {
541: dsc = "\tinsert data into tables. DML.";
542: } else if ("update".equals(cmd)) {
543: dsc = "\tupdate existing rows with new data. DML.";
544: } else if ("create".equals(cmd)) {
545: dsc = "\tcreate new database object (such as tables/views/indices..). DDL.";
546: } else if ("alter".equals(cmd)) {
547: dsc = "\talter a database object. DDL.";
548: } else if ("drop".equals(cmd)) {
549: dsc = "\tdrop (remove) a database object. DDL.";
550: } else if ("rollback".equals(cmd)) {
551: dsc = "\trollback transaction.";
552: } else if ("commit".equals(cmd)) {
553: dsc = "\tcommit transaction.";
554: } else if ("call-procedure".equals(cmd)) {
555: dsc = "\tcall a function that returns exactly one parameter\n"
556: + "\tthat can be gathered as string (EXPERIMENTAL)\n"
557: + "\texample:\n"
558: + "\t call-procedure foobar(42);\n";
559: }
560: return dsc;
561: }
562:
563: private class SQLColumnDelimiterProperty extends PropertyHolder {
564: public SQLColumnDelimiterProperty() {
565: super (SQLCommand.this .getColumnDelimiter());
566: }
567:
568: protected String propertyChanged(String newValue) {
569: SQLCommand.this .setColumnDelimiter(newValue);
570: return newValue;
571: }
572:
573: public String getShortDescription() {
574: return "modify column separator in query results";
575: }
576:
577: public String getDefaultValue() {
578: return "|";
579: }
580:
581: public String getLongDescription() {
582: String dsc;
583: dsc = "\tSet another string that is used to separate columns in\n"
584: + "\tSQL result sets. Usually this is a pipe-symbol '|', but\n"
585: + "\tmaybe you want to have an empty string ?";
586: return dsc;
587: }
588: }
589:
590: private class RowLimitProperty extends PropertyHolder {
591: public RowLimitProperty() {
592: super (String.valueOf(SQLCommand.this .getRowLimit()));
593: }
594:
595: protected String propertyChanged(String newValue)
596: throws Exception {
597: newValue = newValue.trim();
598: int newIntValue;
599: try {
600: newIntValue = Integer.parseInt(newValue);
601: } catch (NumberFormatException e) {
602: throw new IllegalArgumentException("cannot parse '"
603: + newValue + "' as integer");
604: }
605: if (newIntValue < 1) {
606: throw new IllegalArgumentException(
607: "value cannot be less than 1");
608: }
609: SQLCommand.this .setRowLimit(newIntValue);
610: return newValue;
611: }
612:
613: public String getDefaultValue() {
614: return "2000";
615: }
616:
617: public String getShortDescription() {
618: return "set the maximum number of rows printed";
619: }
620: }
621:
622: private class ShowHeaderProperty extends BooleanPropertyHolder {
623:
624: public ShowHeaderProperty() {
625: super (true);
626: }
627:
628: public void booleanPropertyChanged(boolean value) {
629: setShowHeader(value);
630: }
631:
632: public String getDefaultValue() {
633: return "on";
634: }
635:
636: /**
637: * return a short descriptive string.
638: */
639: public String getShortDescription() {
640: return "switches if header in selected tables should be shown";
641: }
642: }
643:
644: private class ShowFooterProperty extends BooleanPropertyHolder {
645:
646: public ShowFooterProperty() {
647: super (true);
648: }
649:
650: public void booleanPropertyChanged(boolean value) {
651: setShowFooter(value);
652: }
653:
654: public String getDefaultValue() {
655: return "on";
656: }
657:
658: /**
659: * return a short descriptive string.
660: */
661: public String getShortDescription() {
662: return "switches if footer in selected tables should be shown";
663: }
664: }
665: }
666:
667: /*
668: * Local variables:
669: * c-basic-offset: 4
670: * compile-command: "ant -emacs -find build.xml"
671: * End:
672: */
|