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.importparser.TypeParser;
010: import henplus.importparser.ImportParser;
011: import henplus.importparser.QuotedStringParser;
012: import henplus.importparser.ValueRecipient;
013: import henplus.importparser.IgnoreTypeParser;
014: import henplus.Interruptable;
015:
016: import henplus.SQLSession;
017: import henplus.CommandDispatcher;
018: import henplus.SigIntHandler;
019: import henplus.HenPlus;
020: import henplus.AbstractCommand;
021: import henplus.view.util.NameCompleter;
022: import java.nio.charset.Charset;
023:
024: import java.io.File;
025: import java.io.InputStream;
026: import java.util.zip.GZIPInputStream;
027: import java.io.Reader;
028: import java.io.FileInputStream;
029: import java.io.InputStreamReader;
030:
031: import java.util.StringTokenizer;
032: import java.util.Collection;
033: import java.util.List;
034: import java.util.ArrayList;
035: import java.util.Iterator;
036: import java.util.Calendar;
037:
038: import java.sql.PreparedStatement;
039:
040: /*
041: Todo:
042: - where clause with regexp
043: - fix quoting handling
044: */
045:
046: /**
047: * document me.
048: */
049: public class ImportCommand extends AbstractCommand {
050: private static final String DEFAULT_ROW_DELIM = "\n";
051: private static final String DEFAULT_COL_DELIM = "\t";
052: private static final String COMMAND_QUOTES = "\"\"''()";
053:
054: private final ListUserObjectsCommand _tableCompleter;
055:
056: /**
057: * returns the command-strings this command can handle.
058: */
059: public String[] getCommandList() {
060: return new String[] { "import", "import-check", "import-print" };
061: }
062:
063: public ImportCommand(ListUserObjectsCommand tc) {
064: _tableCompleter = tc;
065: }
066:
067: public boolean requiresValidSession(String cmd) {
068: return "import".equals(cmd);
069: }
070:
071: public Iterator complete(CommandDispatcher disp,
072: String partialCommand, final String lastWord) {
073: ConfigurationParser parser = new ConfigurationParser(
074: _tableCompleter);
075: if ("".equals(lastWord)) {
076: partialCommand += " ";
077: } else if (!partialCommand.endsWith(lastWord)) {
078: partialCommand += lastWord;
079: }
080: return parser.complete(stripCommand(partialCommand));
081: }
082:
083: private String stripCommand(String cmd) {
084: int len = cmd.length();
085: for (int i = 0; i < len; ++i) {
086: if (Character.isWhitespace(cmd.charAt(i))) {
087: return cmd.substring(i);
088: }
089: }
090: return "";
091: }
092:
093: /**
094: * execute the command given.
095: */
096: public int execute(SQLSession session, String cmd, String param) {
097: /*
098: HenPlus.msg().println("cmd='" + cmd + "';"
099: + "param='" + param + "'");
100: */
101: ConfigurationParser parser = new ConfigurationParser(param);
102: String error = parser.getParseError();
103: if (error != null) {
104: HenPlus.msg().println(error);
105: return SYNTAX_ERROR;
106: }
107: final ImportConfiguration config = parser.getConfig();
108:
109: try {
110: final long startTime = System.currentTimeMillis();
111: final long startRow = config.getStartRow();
112: final long rowCount = config.getRowCount();
113: long endRow = -1;
114: if (rowCount >= 0) {
115: endRow = startRow > 0 ? startRow + rowCount : rowCount;
116: }
117: RowCountingRecipient innerRecipient = null;
118: if ("import-print".equals(cmd)) {
119: innerRecipient = new PrintRecipient(config.getColumns());
120: } else if ("import-check".equals(cmd)) {
121: innerRecipient = new CountRecipient();
122: } else if ("import".equals(cmd)) {
123: innerRecipient = new SqlImportProcessor(session, config);
124: }
125:
126: FilterRecipient filterRecipient = new FilterRecipient(
127: startRow, endRow, innerRecipient);
128: SigIntHandler.getInstance().pushInterruptable(
129: filterRecipient);
130: importFile(config, filterRecipient);
131: final long readRows = filterRecipient.getRowCount();
132: final long processedRows = innerRecipient.getRowCount();
133:
134: final long execTime = System.currentTimeMillis()
135: - startTime;
136:
137: HenPlus.msg().print(
138: "reading " + readRows + " rows from '"
139: + config.getFilename() + "' took ");
140: TimeRenderer.printTime(execTime, HenPlus.msg());
141: HenPlus.msg().print(" total; ");
142: TimeRenderer.printFraction(execTime, readRows, HenPlus
143: .msg());
144: HenPlus.msg().println(" / row");
145: HenPlus.msg().println(
146: "processed " + processedRows + " rows");
147: } catch (Exception e) {
148: e.printStackTrace();
149: return EXEC_FAILED;
150: }
151: return SUCCESS;
152: }
153:
154: private void importFile(ImportConfiguration config,
155: ValueRecipient recipient) throws Exception {
156: final File file = new File(config.getFilename());
157: final String encoding = ((config.getEncoding() != null) ? config
158: .getEncoding()
159: : "ISO-8859-1");
160: InputStream fileIn = new FileInputStream(file);
161: if (config.getFilename().endsWith(".gz")) {
162: fileIn = new GZIPInputStream(fileIn);
163: }
164: final Reader reader = new InputStreamReader(fileIn, encoding);
165: final int colCount = config.getColumns().length;
166:
167: // TODO: parse type after colon.
168: final TypeParser[] colParser = new TypeParser[colCount];
169: int colIndex = 0;
170: for (int i = 0; i < colCount; ++i) {
171: String colName = config.getColumns()[i];
172: colParser[i] = ((colName == null) ? (TypeParser) new IgnoreTypeParser()
173: : (TypeParser) new QuotedStringParser(colIndex++));
174: }
175: try {
176: final String colDelim = (config.getColDelimiter() != null ? config
177: .getColDelimiter()
178: : DEFAULT_COL_DELIM);
179: final String rowDelim = (config.getRowDelimiter() != null ? config
180: .getRowDelimiter()
181: : DEFAULT_ROW_DELIM);
182: ImportParser parser = new ImportParser(colParser, colDelim,
183: rowDelim);
184: parser.parse(reader, recipient);
185: } finally {
186: reader.close();
187: }
188: }
189:
190: private interface RowCountingRecipient extends ValueRecipient {
191: long getRowCount();
192: }
193:
194: private static class FilterRecipient implements
195: RowCountingRecipient, Interruptable {
196: private final long _startRow;
197: private final long _endRow;
198: private final ValueRecipient _target;
199: private long _rows;
200: private volatile boolean _finished;
201:
202: public FilterRecipient(long startRow, long endRow,
203: ValueRecipient target) {
204: _rows = 0;
205: _startRow = startRow;
206: _endRow = endRow;
207: _target = target;
208: }
209:
210: private final boolean expressionMatches() {
211: return true; // no expression match yet.
212: }
213:
214: private final boolean rangeValid() {
215: if (_startRow >= 0) {
216: if (_rows < _startRow)
217: return false;
218: }
219: if (_endRow >= 0) {
220: if (_rows >= _endRow) {
221: return false;
222: }
223: }
224: return true;
225: }
226:
227: public void setLong(int fieldNumber, long value)
228: throws Exception {
229: if (rangeValid()) {
230: _target.setLong(fieldNumber, value);
231: }
232: }
233:
234: public void setString(int fieldNumber, String value)
235: throws Exception {
236: if (rangeValid()) {
237: _target.setString(fieldNumber, value);
238: }
239: }
240:
241: public void setDate(int fieldNumber, Calendar cal)
242: throws Exception {
243: if (rangeValid()) {
244: _target.setDate(fieldNumber, cal);
245: }
246: }
247:
248: public void interrupt() {
249: _finished = true;
250: }
251:
252: public long getRowCount() {
253: return _rows;
254: }
255:
256: public boolean finishRow() throws Exception {
257: boolean deligeeFinish = false;
258: if (rangeValid() && expressionMatches()) {
259: deligeeFinish = _target.finishRow();
260: }
261: _rows++;
262: return deligeeFinish || _finished
263: || (_endRow >= 0 && _rows >= _endRow);
264: }
265: }
266:
267: private final static class CountRecipient implements
268: RowCountingRecipient {
269: private long _rows;
270:
271: CountRecipient() {
272: _rows = 0;
273: }
274:
275: public void setLong(int fieldNumber, long value) {
276: }
277:
278: public void setString(int fieldNumber, String value) {
279: }
280:
281: public void setDate(int fieldNumber, Calendar cal) {
282: }
283:
284: public boolean finishRow() {
285: ++_rows;
286: return false;
287: }
288:
289: public long getRowCount() {
290: return _rows;
291: }
292: }
293:
294: private final static class PrintRecipient implements
295: RowCountingRecipient {
296: private final String[] _paddedNames;
297: private long _rows;
298: private boolean _colWritten;
299:
300: public PrintRecipient(String[] columnNames) {
301: _rows = 0;
302: int maxLen = -1;
303: int maxIndex = 0;
304: // find max length and col-number
305: for (int i = 0; i < columnNames.length; ++i) {
306: final String colName = columnNames[i];
307: if (colName != null) {
308: if (colName.length() > maxLen) {
309: maxLen = colName.length();
310: }
311: maxIndex++;
312: }
313: }
314: _paddedNames = new String[maxIndex];
315: int colIndex = 0;
316: // precalculate padded names
317: for (int i = 0; i < columnNames.length; ++i) {
318: final String colName = columnNames[i];
319: if (colName == null)
320: continue;
321: _paddedNames[colIndex] = colName;
322: while (_paddedNames[colIndex].length() < maxLen) {
323: _paddedNames[colIndex] += " ";
324: }
325: _paddedNames[colIndex] += " : ";
326: colIndex++;
327: }
328: _colWritten = false;
329: }
330:
331: private boolean printColName(int fieldNumber) {
332: if (fieldNumber > _paddedNames.length)
333: return false;
334: final String colName = _paddedNames[fieldNumber];
335: if (colName == null)
336: return false;
337: if (!_colWritten) {
338: HenPlus.msg().attributeBold();
339: HenPlus.msg().println(
340: "----------------------- row " + _rows + " :");
341: HenPlus.msg().attributeReset();
342: _colWritten = true;
343: }
344: HenPlus.msg().attributeBold();
345: HenPlus.msg().print(colName); // TODO: padding.
346: HenPlus.msg().attributeReset();
347: return true;
348: }
349:
350: public void setLong(int fieldNumber, long value) {
351: if (printColName(fieldNumber)) {
352: HenPlus.msg().println(String.valueOf(value));
353: }
354: }
355:
356: public void setString(int fieldNumber, String value) {
357: if (printColName(fieldNumber)) {
358: HenPlus.msg().println(value);
359: }
360: }
361:
362: public void setDate(int fieldNumber, Calendar cal) {
363: if (printColName(fieldNumber)) {
364: HenPlus.msg().println(
365: cal != null ? cal.getTime().toString() : null);
366: }
367: }
368:
369: public long getRowCount() {
370: return _rows;
371: }
372:
373: public boolean finishRow() throws Exception {
374: _rows++;
375: _colWritten = false;
376: return false;
377: }
378: }
379:
380: private final static class SqlImportProcessor implements
381: RowCountingRecipient {
382: private long _rows;
383: private PreparedStatement _stmt;
384:
385: public SqlImportProcessor(SQLSession session,
386: ImportConfiguration config) throws Exception {
387: _rows = 0;
388: StringBuffer cmd = new StringBuffer("insert into ");
389: cmd.append(config.getTable()).append(" (");
390: boolean isFirst = true;
391: for (int i = 0; i < config.getColumns().length; ++i) {
392: if (config.getColumns()[i] != null) {
393: if (!isFirst)
394: cmd.append(",");
395: isFirst = false;
396: cmd.append(config.getColumns()[i]);
397: }
398: }
399: cmd.append(") values (");
400: isFirst = true;
401: for (int i = 0; i < config.getColumns().length; ++i) {
402: if (config.getColumns()[i] != null) {
403: if (!isFirst)
404: cmd.append(",");
405: isFirst = false;
406: cmd.append("?");
407: }
408: }
409: cmd.append(")");
410: final String stmtString = cmd.toString();
411: System.out.println("INSERTING WITH " + stmtString);
412: _stmt = session.getConnection()
413: .prepareStatement(stmtString);
414: }
415:
416: public void setLong(int fieldNumber, long value)
417: throws Exception {
418: _stmt.setLong(fieldNumber + 1, value);
419: }
420:
421: public void setString(int fieldNumber, String value)
422: throws Exception {
423: _stmt.setString(fieldNumber + 1, value);
424: }
425:
426: public void setDate(int fieldNumber, Calendar cal)
427: throws Exception {
428: throw new UnsupportedOperationException("not yet.");
429: }
430:
431: public long getRowCount() {
432: return _rows;
433: }
434:
435: public boolean finishRow() throws Exception {
436: _rows++;
437: _stmt.execute();
438: return false;
439: }
440: }
441:
442: /**
443: * return a descriptive string.
444: */
445: public String getShortDescription() {
446: return "import delimited data into table";
447: }
448:
449: public String getSynopsis(String cmd) {
450: return cmd
451: + " from <filename> into <tablename> columns (col1[:type][,col2[:type]]) [column-delim \"\\t\"] [row-delim \"\\n\"] [encoding <encoding>] [start-row <number>] [row-count|end-row <number>]\n"
452: + "\tcol could be a column name or '-' if the column is to be ignored\n"
453: + "\tthe optional type can be one of [string,number,date]";
454: }
455:
456: public String getLongDescription(String cmd) {
457: String dsc = null;
458: if ("import-check".equals(cmd)) {
459: dsc = "\tDry-run: read the file but do not insert anything\n";
460: } else {
461: dsc = "\tImport the content of the file into table according to the format\n";
462: }
463: dsc += "\tIf the filename ends with '.gz', the\n"
464: + "\tcontent is unzipped automatically\n\n";
465: return dsc;
466: }
467:
468: private interface CompleterFactory {
469: public Iterator getCompleter(ConfigurationParser parser,
470: String partialValue);
471: }
472:
473: private final static class ConfigurationParser {
474: private final static Object[][] KEYWORDS = {
475: /* (+) means: completable */
476: { "from", new FilenameCompleterFactory() }, /*(+) filename */
477: { "into", new TableCompleterFactory() }, /*(+) table */
478: { "columns", new ColumnCompleterFactory() }, /*(+) (...) */
479: /*{ "filter", null },*/
480: { "column-delim", null }, /* string */
481: { "row-delim", null }, /* string */
482: { "encoding", new EncodingCompleterFactory() },/*(+) any supported encoding */
483: { "start-row", null }, /* integer */
484: { "row-count", null }, /* integer */
485: //{ "end-row", null } /* integer */
486: };
487:
488: private String _parseError;
489: private final ImportConfiguration _config;
490: private final ListUserObjectsCommand _tableCompleter;
491:
492: public ConfigurationParser(ListUserObjectsCommand tableCompleter) {
493: _config = new ImportConfiguration();
494: _tableCompleter = tableCompleter;
495: }
496:
497: public ConfigurationParser(String command) {
498: this ((ListUserObjectsCommand) null); // we never complete anything
499: parseConfig(command);
500: }
501:
502: public ListUserObjectsCommand getTableCompleter() {
503: return _tableCompleter;
504: }
505:
506: /**
507: * return the last parse error, if any.
508: */
509: public String getParseError() {
510: return _parseError;
511: }
512:
513: private void addError(String error) {
514: if (_parseError == null) {
515: _parseError = error;
516: } else {
517: _parseError += "\n" + error;
518: }
519: }
520:
521: private void resetError() {
522: _parseError = null;
523: }
524:
525: /**
526: * parse the configuration an return the completer of the last
527: * property.
528: */
529: private Iterator complete(String partial) {
530: //System.err.println("tok: '" + cmd + "'");
531: resetError();
532: CommandTokenizer cmdTok = new CommandTokenizer(partial,
533: COMMAND_QUOTES);
534: while (cmdTok.hasNext()) {
535: final String commandName = cmdTok.nextToken();
536: if (!cmdTok.isCurrentTokenFinished()) {
537: //System.err.println("not finished: '" + cmd + "'");
538: return getCommandCompleter(commandName);
539: }
540: String commandValue = "";
541: boolean needsCompletion = true;
542: if (cmdTok.hasNext()) {
543: commandValue = cmdTok.nextToken();
544: needsCompletion = !cmdTok.isCurrentTokenFinished();
545: }
546: if (needsCompletion) {
547: CompleterFactory cfactory = findCompleter(commandName);
548: if (cfactory != null) {
549: return cfactory
550: .getCompleter(this , commandValue);
551: }
552: return null;
553: } else {
554: setParsedValue(commandName, commandValue);
555: }
556: }
557: return getCommandCompleter("");
558: }
559:
560: /**
561: * parse a configuration that is complete
562: */
563: private void parseConfig(String complete) {
564: resetError();
565: CommandTokenizer cmdTok = new CommandTokenizer(complete,
566: COMMAND_QUOTES);
567: while (cmdTok.hasNext()) {
568: final String commandName = cmdTok.nextToken();
569: if (!cmdTok.isCurrentTokenFinished()) {
570: addError("command ends prematurely at '"
571: + commandName + "'");
572: return;
573: }
574: String commandValue = "";
575: if (cmdTok.hasNext()) {
576: commandValue = cmdTok.nextToken();
577: } else {
578: addError("expecting value for '" + commandName
579: + "'");
580: return;
581: }
582: setParsedValue(commandName, commandValue);
583: }
584: }
585:
586: private CompleterFactory findCompleter(String command) {
587: for (int i = 0; i < KEYWORDS.length; ++i) {
588: if (KEYWORDS[i][0].equals(command)) {
589: return (CompleterFactory) KEYWORDS[i][1];
590: }
591: }
592: addError("unknown option to complete '" + command + "'");
593: return null;
594: }
595:
596: private void setParsedValue(String commandName,
597: String commandValue) {
598: try {
599: if ("from".equals(commandName)) {
600: _config.setFilename(commandValue);
601: } else if ("into".equals(commandName)) {
602: _config.setTable(commandValue);
603: } else if ("columns".equals(commandName)) {
604: _config.setRawColumns(commandValue);
605: } else if ("column-delim".equals(commandName)) {
606: _config.setColDelimiter(stripQuotes(commandValue));
607: } else if ("row-delim".equals(commandName)) {
608: _config.setRowDelimiter(stripQuotes(commandValue));
609: } else if ("encoding".equals(commandName)) {
610: _config.setEncoding(commandValue);
611: } else if ("start-row".equals(commandName)) {
612: _config.setStartRow(Long.parseLong(commandValue));
613: } else if ("row-count".equals(commandName)) {
614: _config.setRowCount(Long.parseLong(commandValue));
615: }
616: // end-row missing.
617: else {
618: addError("unknown option '" + commandName + "'");
619: }
620: } catch (Exception e) {
621: addError("invalid value for " + commandName + " : "
622: + e.getMessage());
623: }
624: }
625:
626: private String stripQuotes(String quotedString) {
627: if (quotedString == null || quotedString.length() < 2)
628: return quotedString;
629: final char first = quotedString.charAt(0);
630: if (first == '"' || first == '\'') {
631: final int lastPos = quotedString.length() - 1;
632: if (quotedString.charAt(lastPos) == first) {
633: return quotedString.substring(1, lastPos);
634: }
635: }
636: return quotedString;
637: }
638:
639: private Iterator getCommandCompleter(String partial) {
640: NameCompleter completer = new NameCompleter();
641: // first: check for must have parameters; then rest.
642: if (_config.getFilename() == null) {
643: completer.addName("from");
644: } else if (_config.getTable() == null) {
645: completer.addName("into");
646: } else if (_config.getColumns() == null) {
647: completer.addName("columns");
648: } else {
649: if (_config.getColDelimiter() == null)
650: completer.addName("column-delim");
651: if (_config.getRowDelimiter() == null)
652: completer.addName("row-delim");
653: if (_config.getEncoding() == null)
654: completer.addName("encoding");
655: if (_config.getStartRow() < 0)
656: completer.addName("start-row");
657: if (_config.getRowCount() < 0) {
658: completer.addName("row-count");
659: completer.addName("end-row");
660: }
661: }
662: return completer.getAlternatives(partial);
663: }
664:
665: public ImportConfiguration getConfig() {
666: return _config;
667: }
668: }
669:
670: private final static class ImportConfiguration {
671: private String _filename;
672: private String _schema;
673: private String _table;
674: private String _colDelimiter;
675: private String _rowDelimiter;
676: private Charset _charset;
677: private long _startRow = -1;
678: private long _rowCount = -1;
679: private String[] _columns;
680:
681: public void setFilename(String filename) {
682: _filename = filename;
683: }
684:
685: public String getFilename() {
686: return _filename;
687: }
688:
689: public void setSchema(String schema) {
690: _schema = schema;
691: }
692:
693: public String getSchema() {
694: return _schema;
695: }
696:
697: public void setTable(String table) {
698: _table = table;
699: }
700:
701: public String getTable() {
702: return _table;
703: }
704:
705: public void setColDelimiter(String colDelimiter) {
706: _colDelimiter = colDelimiter;
707: }
708:
709: public String getColDelimiter() {
710: return _colDelimiter;
711: }
712:
713: public void setRowDelimiter(String rowDelimiter) {
714: _rowDelimiter = rowDelimiter;
715: }
716:
717: public String getRowDelimiter() {
718: return _rowDelimiter;
719: }
720:
721: public void setEncoding(String encoding) {
722: _charset = Charset.forName(encoding);
723: }
724:
725: public String getEncoding() {
726: if (_charset == null)
727: return null;
728: return _charset.name();
729: }
730:
731: public Charset getCharset() {
732: return _charset;
733: }
734:
735: public void setStartRow(long startRow) {
736: _startRow = startRow;
737: }
738:
739: public long getStartRow() {
740: return _startRow;
741: }
742:
743: public void setRowCount(long rowCount) {
744: _rowCount = rowCount;
745: }
746:
747: public long getRowCount() {
748: return _rowCount;
749: }
750:
751: public void setRawColumns(String commaDelimColumns) {
752: if (!commaDelimColumns.startsWith("(")) {
753: throw new IllegalArgumentException(
754: "columns must start with '('");
755: }
756: StringTokenizer tok = new StringTokenizer(
757: commaDelimColumns, " \t,()");
758: String result[] = new String[tok.countTokens()];
759: for (int i = 0; tok.hasMoreElements(); ++i) {
760: String token = tok.nextToken();
761: result[i] = "-".equals(token) ? null : token;
762: //System.err.println(result[i]);
763: }
764: setColumns(result);
765: if (!commaDelimColumns.endsWith(")")) {
766: throw new IllegalArgumentException(
767: "columns must end with ')'");
768: }
769: }
770:
771: public void setColumns(String[] columns) {
772: _columns = columns;
773: }
774:
775: public String[] getColumns() {
776: return _columns;
777: }
778:
779: }
780:
781: private final static class FilenameCompleterFactory implements
782: CompleterFactory {
783: public Iterator getCompleter(ConfigurationParser parser,
784: String lastCommand) {
785: return new FileCompletionIterator(" " + lastCommand, "");
786: }
787: }
788:
789: private final static class TableCompleterFactory implements
790: CompleterFactory {
791: public Iterator getCompleter(ConfigurationParser parser,
792: String partialName) {
793: return parser.getTableCompleter().completeTableName(
794: HenPlus.getInstance().getCurrentSession(),
795: partialName);
796: }
797: }
798:
799: private final static class ColumnCompleterFactory implements
800: CompleterFactory {
801: public Iterator getCompleter(ConfigurationParser parser,
802: String lastCommand) {
803: if ("".equals(lastCommand)) {
804: List paren = new ArrayList();
805: paren.add("(");
806: return paren.iterator();
807: }
808: if (lastCommand.endsWith(" ")) {
809: //...
810: }
811: String tab = parser.getConfig().getTable();
812: // TODO: read columns from meta-data
813: return null;
814: }
815: }
816:
817: private final static class EncodingCompleterFactory implements
818: CompleterFactory {
819: public Iterator getCompleter(ConfigurationParser parser,
820: String partialName) {
821: Collection allEncodings = Charset.availableCharsets()
822: .keySet();
823: NameCompleter completer = new NameCompleter(allEncodings);
824: return completer.getAlternatives(partialName);
825: }
826: }
827: }
828:
829: /*
830: * Local variables:
831: * c-basic-offset: 4
832: * compile-command: "ant -emacs -find build.xml"
833: * End:
834: */
|