0001: /*
0002: * DataExporter.java
0003: *
0004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
0005: *
0006: * Copyright 2002-2008, Thomas Kellerer
0007: * No part of this code maybe reused without the permission of the author
0008: *
0009: * To contact the author please send an email to: support@sql-workbench.net
0010: *
0011: */
0012: package workbench.db.exporter;
0013:
0014: import java.awt.Frame;
0015: import java.awt.Window;
0016: import java.awt.event.WindowAdapter;
0017: import java.awt.event.WindowEvent;
0018: import java.io.File;
0019: import java.io.FileOutputStream;
0020: import java.io.IOException;
0021: import java.io.OutputStream;
0022: import java.io.Writer;
0023: import java.sql.ResultSet;
0024: import java.sql.ResultSetMetaData;
0025: import java.sql.SQLException;
0026: import java.sql.Statement;
0027: import java.text.DecimalFormat;
0028: import java.text.DecimalFormatSymbols;
0029: import java.text.SimpleDateFormat;
0030: import java.util.Collections;
0031: import java.util.HashSet;
0032: import java.util.LinkedList;
0033: import java.util.List;
0034: import java.util.Set;
0035: import java.util.zip.ZipEntry;
0036: import java.util.zip.ZipOutputStream;
0037: import javax.swing.JDialog;
0038: import workbench.WbManager;
0039: import workbench.db.ColumnIdentifier;
0040: import workbench.db.TableIdentifier;
0041: import workbench.db.WbConnection;
0042: import workbench.interfaces.Committer;
0043: import workbench.interfaces.ErrorReporter;
0044: import workbench.interfaces.ProgressReporter;
0045: import workbench.storage.SqlLiteralFormatter;
0046: import workbench.util.ExceptionUtil;
0047: import workbench.gui.WbSwingUtilities;
0048: import workbench.gui.dbobjects.ProgressPanel;
0049: import workbench.gui.dialogs.export.ExportFileDialog;
0050: import workbench.gui.dialogs.export.ExportOptions;
0051: import workbench.gui.dialogs.export.HtmlOptions;
0052: import workbench.gui.dialogs.export.SpreadSheetOptions;
0053: import workbench.gui.dialogs.export.SqlOptions;
0054: import workbench.gui.dialogs.export.TextOptions;
0055: import workbench.gui.dialogs.export.XmlOptions;
0056: import workbench.interfaces.Interruptable;
0057: import workbench.log.LogMgr;
0058: import workbench.resource.ResourceMgr;
0059: import workbench.resource.Settings;
0060: import workbench.storage.DataStore;
0061: import workbench.storage.ResultInfo;
0062: import workbench.storage.RowActionMonitor;
0063: import workbench.storage.SqlLiteralFormatter;
0064: import workbench.util.CharacterRange;
0065: import workbench.util.EncodingUtil;
0066: import workbench.util.MessageBuffer;
0067: import workbench.util.NumberStringCache;
0068: import workbench.util.QuoteEscapeType;
0069: import workbench.util.SqlUtil;
0070: import workbench.util.StringUtil;
0071: import workbench.util.WbFile;
0072: import workbench.util.WbThread;
0073:
0074: /**
0075: *
0076: * @author support@sql-workbench.net
0077: */
0078: public class DataExporter implements Interruptable, ErrorReporter,
0079: ProgressReporter, Committer {
0080: /**
0081: * Export to SQL statements.
0082: */
0083: public static final int EXPORT_SQL = 1;
0084:
0085: /**
0086: * Export to plain text.
0087: */
0088: public static final int EXPORT_TXT = 2;
0089:
0090: /**
0091: * Export to XML file (WB specific).
0092: */
0093: public static final int EXPORT_XML = 3;
0094:
0095: /**
0096: * Export to HTML.
0097: */
0098: public static final int EXPORT_HTML = 4;
0099:
0100: /**
0101: * OpenDocument Spreadsheet
0102: */
0103: public static final int EXPORT_ODS = 5;
0104:
0105: /**
0106: * Export XLS XML
0107: */
0108: public static final int EXPORT_XLSX = 6;
0109:
0110: /**
0111: * Export XLS
0112: */
0113: public static final int EXPORT_XLS = 7;
0114:
0115: private WbConnection dbConn;
0116: private String sql;
0117: private String pageTitle = null;
0118:
0119: // When compressing the output this holds the name of the archive.
0120: private String realOutputfile;
0121:
0122: private String outputfile;
0123: private String xsltFile = null;
0124: private String transformOutputFile = null;
0125: private int exportType;
0126: private boolean exportHeaders;
0127: private boolean includeCreateTable = false;
0128: private boolean continueOnError = true;
0129:
0130: private int sqlType = SqlRowDataConverter.SQL_INSERT;
0131: private boolean useCDATA = false;
0132: private CharacterRange escapeRange = null;
0133: private String lineEnding = "\n";
0134: private String tableName;
0135: private String sqlTable;
0136: private String encoding;
0137: private List<ColumnIdentifier> columnsToExport;
0138:
0139: private boolean clobAsFile = false;
0140: private String delimiter = "\t";
0141: private String quoteChar = null;
0142: private boolean quoteAlways = false;
0143: private String dateFormat = null;
0144: private String dateTimeFormat = null;
0145: private char decimalSymbol = '.';
0146: private String chrFunc = null;
0147: private String concatString = "||";
0148: private String concatFunction = null;
0149: private String filenameColumn = null;
0150: private int commitEvery = 0;
0151:
0152: private SimpleDateFormat dateFormatter = null;
0153: private SimpleDateFormat dateTimeFormatter = null;
0154: private DecimalFormat numberFormatter = null;
0155:
0156: private boolean append = false;
0157: private boolean escapeHtml = true;
0158: private boolean createFullHtmlPage = true;
0159: private boolean verboseFormat = true;
0160:
0161: private boolean showProgressWindow = false;
0162: private int progressInterval = ProgressReporter.DEFAULT_PROGRESS_INTERVAL;
0163:
0164: private ProgressPanel progressPanel;
0165: private JDialog progressWindow;
0166: private ExportJobEntry currentJob;
0167: private boolean cancelJobs = false;
0168: private boolean jobsRunning = false;
0169: private RowActionMonitor rowMonitor;
0170:
0171: private List keyColumnsToUse;
0172: private String dateLiteralType = null;
0173:
0174: // The columns to be used for generating blob file names
0175: private List<String> blobIdCols;
0176:
0177: private MessageBuffer warnings = new MessageBuffer();
0178: private MessageBuffer errors = new MessageBuffer();
0179: private List<ExportJobEntry> jobQueue;
0180: private ExportWriter exportWriter;
0181: private Window parentWindow;
0182: private int tablesExported;
0183: private long totalRows;
0184:
0185: private Set<ControlFileFormat> controlFiles = new HashSet<ControlFileFormat>();
0186: private boolean compressOutput = false;
0187:
0188: private ZipOutputStream zipArchive;
0189: private ZipEntry zipEntry;
0190:
0191: private BlobMode blobMode = null;
0192: private QuoteEscapeType quoteEscape = QuoteEscapeType.none;
0193:
0194: /**
0195: * Create a DataExporter for the specified connection.
0196: *
0197: * @param con The connection on which this Exporter should work on
0198: */
0199: public DataExporter(WbConnection con) {
0200: this .dbConn = con;
0201: this .setExportHeaders(Settings.getInstance().getBoolProperty(
0202: "workbench.export.text.default.header", false));
0203: }
0204:
0205: protected void createProgressPanel() {
0206: progressPanel = new ProgressPanel(this );
0207: this .progressPanel.setFilename(this .outputfile);
0208: this .progressPanel.setInfoText(ResourceMgr
0209: .getString("MsgSpoolStart"));
0210: }
0211:
0212: /**
0213: * Open the progress monitor window.
0214: * @param parent the window acting as the parent for the progress monitor
0215: */
0216: protected void openProgressMonitor(Frame parent) {
0217: if (this .progressPanel == null)
0218: createProgressPanel();
0219:
0220: this .progressWindow = new JDialog(parent, true);
0221: this .progressWindow.getContentPane().add(progressPanel);
0222: this .progressWindow.pack();
0223: this .progressWindow.setTitle(ResourceMgr
0224: .getString("MsgSpoolWindowTitle"));
0225:
0226: this .progressWindow.addWindowListener(new WindowAdapter() {
0227: public void windowClosing(WindowEvent e) {
0228: cancelExecution();
0229: }
0230: });
0231:
0232: WbSwingUtilities.center(this .progressWindow, null);
0233: this .progressWindow.setVisible(true);
0234: }
0235:
0236: /**
0237: * Define the format for date and timestamp literals
0238: * when writing SQL statements.
0239: *
0240: * Valid values are <tt>jdbc,ansi,dbms</tt>
0241: *
0242: * dbms selects the format approriate for the current dbms.
0243: * It is the same as passing null
0244: *
0245: * @param type the literal format to use
0246: * @see workbench.storage.SqlLiteralFormatter#setProduct(String)
0247: */
0248: public void setDateLiteralType(String type) {
0249: if (SqlLiteralFormatter.DBMS_DATE_LITERAL_TYPE
0250: .equalsIgnoreCase(type)
0251: || type == null) {
0252: this .dateLiteralType = SqlLiteralFormatter.DBMS_DATE_LITERAL_TYPE;
0253: } else {
0254: this .dateLiteralType = type.trim().toLowerCase();
0255: }
0256: }
0257:
0258: public String getFilenameColumn() {
0259: return this .filenameColumn;
0260: }
0261:
0262: public void setFilenameColumn(String colname) {
0263: if (StringUtil.isWhitespaceOrEmpty(colname)) {
0264: this .filenameColumn = null;
0265: } else {
0266: this .filenameColumn = colname.trim();
0267: }
0268: }
0269:
0270: /**
0271: * Return the type of date literals to be created when generating
0272: * SQL statements.
0273: * @return the date literal type
0274: * @see workbench.db.exporter.SqlExportWriter#configureConverter()
0275: * @see workbench.storage.SqlLiteralFormatter
0276: */
0277: public String getDateLiteralType() {
0278: return dateLiteralType;
0279: }
0280:
0281: /**
0282: * Define how blobs should be handled during export.
0283: * Modes allowed are
0284: * <ul>
0285: * <li>BLOB_MODE_LITERAL</li>
0286: * <li>BLOB_MODE_ANSI</li>
0287: * <li>BLOB_MODE_FILE</li>
0288: * </ul>
0289: * @param type the blob mode to be used.
0290: * null means no special treatment (toString() will be called)
0291: * @see #BLOB_MODE_LITERAL
0292: * @see #BLOB_MODE_ANSI
0293: * @see #BLOB_MODE_FILE
0294: */
0295: public void setBlobMode(String type) {
0296: this .blobMode = BlobMode.getMode(type);
0297: if (this .blobMode == null) {
0298: String msg = ResourceMgr.getString("ErrExpInvalidBlobType");
0299: msg = StringUtil.replace(msg, "%paramvalue%", type);
0300: this .addWarning(msg);
0301: }
0302: }
0303:
0304: /**
0305: * Returns the currently selected mode for BLOB literals.
0306: * @return the current type or null, if nothing was selected
0307: */
0308: public BlobMode getBlobMode() {
0309: return this .blobMode;
0310: }
0311:
0312: public void setWriteClobAsFile(boolean flag) {
0313: this .clobAsFile = flag;
0314: }
0315:
0316: public boolean getWriteClobAsFile() {
0317: return clobAsFile;
0318: }
0319:
0320: public boolean getCompressOutput() {
0321: return this .compressOutput;
0322: }
0323:
0324: public void setCompressOutput(boolean flag) {
0325: this .compressOutput = flag;
0326: }
0327:
0328: public void clearJobs() {
0329: if (this .jobsRunning)
0330: return;
0331: if (this .jobQueue == null)
0332: return;
0333: this .jobQueue.clear();
0334: }
0335:
0336: public void addTableExportJob(String anOutputfile,
0337: TableIdentifier table) throws SQLException {
0338: if (this .jobQueue == null) {
0339: this .jobQueue = new LinkedList<ExportJobEntry>();
0340: }
0341: ExportJobEntry job = new ExportJobEntry(anOutputfile, table,
0342: this .dbConn);
0343: this .jobQueue.add(job);
0344: }
0345:
0346: public void setQuoteEscaping(QuoteEscapeType type) {
0347: this .quoteEscape = type;
0348: }
0349:
0350: public QuoteEscapeType getQuoteEscaping() {
0351: return this .quoteEscape;
0352: }
0353:
0354: public WbConnection getConnection() {
0355: return this .dbConn;
0356: }
0357:
0358: public boolean confirmCancel() {
0359: if (!this .jobsRunning)
0360: return true;
0361: String msg = ResourceMgr.getString("MsgCancelAllCurrent");
0362: String current = ResourceMgr
0363: .getString("LblCancelCurrentExport");
0364: String all = ResourceMgr.getString("LblCancelAllExports");
0365: int answer = WbSwingUtilities.getYesNo(this .progressWindow,
0366: msg, new String[] { current, all });
0367: if (answer == 1) {
0368: this .cancelJobs = true;
0369: }
0370: return true;
0371: }
0372:
0373: public void cancelExecution() {
0374: this .cancelJobs = true;
0375: if (this .exportWriter != null) {
0376: this .exportWriter.cancel();
0377: this
0378: .addWarning(ResourceMgr
0379: .getString("MsgExportCancelled"));
0380: }
0381: }
0382:
0383: public void setTableName(String aTablename) {
0384: this .tableName = aTablename;
0385: }
0386:
0387: public String getTableName() {
0388: return this .tableName;
0389: }
0390:
0391: public void setEncoding(String enc) {
0392: this .encoding = enc;
0393: }
0394:
0395: public String getEncoding() {
0396: return this .encoding;
0397: }
0398:
0399: public void setRowMonitor(RowActionMonitor monitor) {
0400: this .rowMonitor = monitor;
0401: }
0402:
0403: /**
0404: * Define the columns whose values should be used
0405: * for creating the blob files during export
0406: * These columns must define a unique key!
0407: *
0408: * @param columns the ID columns to be used for the filename generation
0409: */
0410: public void setBlobIdColumns(List<String> columns) {
0411: this .blobIdCols = columns;
0412: }
0413:
0414: List<String> getBlobIdColumns() {
0415: return blobIdCols;
0416: }
0417:
0418: /**
0419: * Define the columns that should be exported
0420: * This is only respected for the export of a DataStore, not
0421: * for exporting a ResultSet
0422: *
0423: * @param columns the columns to be exported
0424: * @see #startExport(workbench.storage.DataStore)
0425: */
0426: public void setColumnsToExport(List<ColumnIdentifier> columns) {
0427: this .columnsToExport = columns;
0428: }
0429:
0430: public List<ColumnIdentifier> getColumnsToExport() {
0431: return this .columnsToExport;
0432: }
0433:
0434: public void setExportAllColumns() {
0435: this .columnsToExport = null;
0436: }
0437:
0438: public void setUseCDATA(boolean flag) {
0439: this .useCDATA = flag;
0440: }
0441:
0442: public boolean getUseCDATA() {
0443: return this .useCDATA;
0444: }
0445:
0446: public void setAppendToFile(boolean aFlag) {
0447: this .append = aFlag;
0448: }
0449:
0450: public boolean getAppendToFile() {
0451: return this .append;
0452: }
0453:
0454: public void setContinueOnError(boolean aFlag) {
0455: this .continueOnError = aFlag;
0456: }
0457:
0458: /**
0459: * Do not write any COMMITs to generated SQL scripts
0460: */
0461: public void commitNothing() {
0462: this .commitEvery = Committer.NO_COMMIT_FLAG;
0463: }
0464:
0465: /**
0466: * Set the number of statements after which to add a commit to
0467: * generated SQL scripts.
0468: * @param count the number of statements after which a COMMIT should be added
0469: */
0470: public void setCommitEvery(int count) {
0471: this .commitEvery = count;
0472: }
0473:
0474: public int getCommitEvery() {
0475: return this .commitEvery;
0476: }
0477:
0478: public String getTypeDisplay() {
0479: switch (this .exportType) {
0480: case EXPORT_HTML:
0481: return "HTML";
0482: case EXPORT_SQL:
0483: if (this .getSqlType() == SqlRowDataConverter.SQL_DELETE_INSERT)
0484: return "SQL DELETE/INSERT";
0485: else if (this .getSqlType() == SqlRowDataConverter.SQL_INSERT)
0486: return "SQL INSERT";
0487: else if (this .getSqlType() == SqlRowDataConverter.SQL_UPDATE)
0488: return "SQL UPDATE";
0489: else
0490: return "SQL";
0491: case EXPORT_TXT:
0492: return "Text";
0493: case EXPORT_XML:
0494: return "XML";
0495: case EXPORT_XLS:
0496: return "XLS";
0497: case EXPORT_XLSX:
0498: return "XLS";
0499: case EXPORT_ODS:
0500: return "OpenDocument Spreadsheet";
0501: }
0502: return "";
0503: }
0504:
0505: /**
0506: * Control the progress display in the RowActionMonitor
0507: * This is used by the WBEXPORT command to turn off the row
0508: * progress display. Turning off the display will speed up
0509: * the export because the GUI does not need to be updated
0510: *
0511: * @param interval the new progress interval
0512: */
0513: public void setReportInterval(int interval) {
0514: if (interval <= 0)
0515: this .progressInterval = 0;
0516: else
0517: this .progressInterval = interval;
0518: }
0519:
0520: /**
0521: * Control the display of a progress window. This is used
0522: * from within the DbExplorer.
0523: *
0524: * If the application is running in batch mode, this call is ignored.
0525: * @see WbManager#isBatchMode()
0526: * @param aFlag if true, the progress window is displayed
0527: */
0528: public void setShowProgressWindow(boolean aFlag) {
0529: if (!WbManager.getInstance().isBatchMode()) {
0530: this .showProgressWindow = aFlag;
0531: }
0532: }
0533:
0534: public void setXsltTransformation(String xsltFileName) {
0535: this .xsltFile = xsltFileName;
0536: }
0537:
0538: public String getXsltTransformation() {
0539: return this .xsltFile;
0540: }
0541:
0542: public void setXsltTransformationOutput(String aFilename) {
0543: this .transformOutputFile = aFilename;
0544: }
0545:
0546: public String getXsltTransformationOutput() {
0547: return this .transformOutputFile;
0548: }
0549:
0550: public void setExportHeaders(boolean aFlag) {
0551: this .exportHeaders = aFlag;
0552: }
0553:
0554: public boolean getExportHeaders() {
0555: return this .exportHeaders;
0556: }
0557:
0558: public void setCreateFullHtmlPage(boolean aFlag) {
0559: this .createFullHtmlPage = aFlag;
0560: }
0561:
0562: public boolean getCreateFullHtmlPage() {
0563: return this .createFullHtmlPage;
0564: }
0565:
0566: public void setEscapeHtml(boolean aFlag) {
0567: this .escapeHtml = aFlag;
0568: }
0569:
0570: public boolean getEscapeHtml() {
0571: return this .escapeHtml;
0572: }
0573:
0574: public void setTextDelimiter(String aDelimiter) {
0575: if (aDelimiter != null && aDelimiter.trim().length() > 0)
0576: this .delimiter = aDelimiter;
0577: }
0578:
0579: public String getTextDelimiter() {
0580: return this .delimiter;
0581: }
0582:
0583: public void setTextQuoteChar(String aQuote) {
0584: this .quoteChar = aQuote;
0585: }
0586:
0587: public String getTextQuoteChar() {
0588: return this .quoteChar;
0589: }
0590:
0591: public void setDateFormat(String aFormat) {
0592: if (StringUtil.isEmptyString(aFormat)) {
0593: aFormat = Settings.getInstance().getDefaultDateFormat();
0594: }
0595: if (StringUtil.isEmptyString(aFormat))
0596: return;
0597: this .dateFormat = aFormat;
0598: if (this .dateFormat != null) {
0599: try {
0600: dateFormatter = new SimpleDateFormat(this .dateFormat);
0601: } catch (IllegalArgumentException i) {
0602: this
0603: .addWarning(ResourceMgr.getFormattedString(
0604: "MsgIllegalDateFormatIgnored",
0605: this .dateFormat));
0606: dateFormatter = null;
0607: }
0608: }
0609: }
0610:
0611: public SimpleDateFormat getDateFormatter() {
0612: return this .dateFormatter;
0613: }
0614:
0615: public String getDateFormat() {
0616: return this .dateFormat;
0617: }
0618:
0619: public void setTimestampFormat(String aFormat) {
0620: if (StringUtil.isEmptyString(aFormat)) {
0621: aFormat = Settings.getInstance()
0622: .getDefaultTimestampFormat();
0623: }
0624: if (StringUtil.isEmptyString(aFormat))
0625: return;
0626: this .dateTimeFormat = aFormat;
0627: if (this .dateTimeFormat != null) {
0628: try {
0629: dateTimeFormatter = new SimpleDateFormat(
0630: this .dateTimeFormat);
0631: } catch (Exception e) {
0632: this .addWarning(ResourceMgr.getFormattedString(
0633: "MsgIllegalDateFormatIgnored",
0634: this .dateTimeFormat));
0635: dateTimeFormatter = null;
0636: }
0637: }
0638: }
0639:
0640: public String getTimestampFormat() {
0641: return this .dateTimeFormat;
0642: }
0643:
0644: public SimpleDateFormat getTimestampFormatter() {
0645: return this .dateTimeFormatter;
0646: }
0647:
0648: private void createExportWriter() {
0649: switch (this .exportType) {
0650: case EXPORT_HTML:
0651: this .exportWriter = new HtmlExportWriter(this );
0652: break;
0653: case EXPORT_SQL:
0654: this .exportWriter = new SqlExportWriter(this );
0655: break;
0656: case EXPORT_TXT:
0657: this .exportWriter = new TextExportWriter(this );
0658: break;
0659: case EXPORT_XML:
0660: this .exportWriter = new XmlExportWriter(this );
0661: break;
0662: case EXPORT_XLS:
0663: this .exportWriter = new XlsExportWriter(this );
0664: break;
0665: case EXPORT_XLSX:
0666: this .exportWriter = new XlsXMLExportWriter(this );
0667: break;
0668: case EXPORT_ODS:
0669: this .exportWriter = new OdsExportWriter(this );
0670: }
0671: }
0672:
0673: public void setPageTitle(String aTitle) {
0674: this .pageTitle = aTitle;
0675: }
0676:
0677: public String getPageTitle() {
0678: return this .pageTitle;
0679: }
0680:
0681: public void setOutputTypeHtml() {
0682: this .exportType = EXPORT_HTML;
0683: createExportWriter();
0684: }
0685:
0686: public void setOutputTypeXml() {
0687: this .exportType = EXPORT_XML;
0688: createExportWriter();
0689: }
0690:
0691: public void setOutputTypeXls() {
0692: this .exportType = EXPORT_XLS;
0693: createExportWriter();
0694: }
0695:
0696: public void setOutputTypeXlsXML() {
0697: this .exportType = EXPORT_XLSX;
0698: createExportWriter();
0699: }
0700:
0701: public void setOutputTypeText() {
0702: this .exportType = EXPORT_TXT;
0703: createExportWriter();
0704: }
0705:
0706: public void setOutputTypeOds() {
0707: this .exportType = EXPORT_ODS;
0708: createExportWriter();
0709: }
0710:
0711: public void setOutputTypeSqlInsert() {
0712: this .exportType = EXPORT_SQL;
0713: this .sqlType = SqlRowDataConverter.SQL_INSERT;
0714: createExportWriter();
0715: }
0716:
0717: public void setOutputTypeSqlUpdate() {
0718: this .exportType = EXPORT_SQL;
0719: this .sqlType = SqlRowDataConverter.SQL_UPDATE;
0720: createExportWriter();
0721: }
0722:
0723: public void setOutputTypeSqlDeleteInsert() {
0724: this .exportType = EXPORT_SQL;
0725: this .sqlType = SqlRowDataConverter.SQL_DELETE_INSERT;
0726: createExportWriter();
0727: }
0728:
0729: public int getSqlType() {
0730: if (this .exportType == EXPORT_SQL)
0731: return this .sqlType;
0732: else
0733: return -1;
0734: }
0735:
0736: public void setOutputFilename(String aFilename) {
0737: this .outputfile = aFilename;
0738: if (this .outputfile == null)
0739: return;
0740: }
0741:
0742: public String getOutputFilename() {
0743: return this .outputfile;
0744: }
0745:
0746: public String getFullOutputFilename() {
0747: return this .realOutputfile;
0748: }
0749:
0750: public void setConcatString(String aConcatString) {
0751: if (aConcatString == null) {
0752: return;
0753: }
0754: this .concatString = aConcatString;
0755: this .concatFunction = null;
0756: }
0757:
0758: public String getConcatString() {
0759: return this .concatString;
0760: }
0761:
0762: public void setChrFunction(String aFunc) {
0763: this .chrFunc = aFunc;
0764: }
0765:
0766: public String getChrFunction() {
0767: return this .chrFunc;
0768: }
0769:
0770: public void setDecimalSymbol(char aSymbol) {
0771: this .decimalSymbol = aSymbol;
0772: if (this .decimalSymbol != 0) {
0773: DecimalFormatSymbols symbols = new DecimalFormatSymbols();
0774: symbols.setDecimalSeparator(this .decimalSymbol);
0775: numberFormatter = new DecimalFormat("0.#", symbols);
0776: numberFormatter.setGroupingUsed(false);
0777: numberFormatter.setMaximumFractionDigits(999);
0778: } else {
0779: numberFormatter = Settings.getInstance()
0780: .getDefaultDecimalFormatter();
0781: }
0782: }
0783:
0784: public DecimalFormat getDecimalFormatter() {
0785: return this .numberFormatter;
0786: }
0787:
0788: public void setDecimalSymbol(String aSymbol) {
0789: if (StringUtil.isEmptyString(aSymbol))
0790: return;
0791: this .setDecimalSymbol(aSymbol.charAt(0));
0792: }
0793:
0794: public void setSql(String aSql) {
0795: this .sql = aSql;
0796: if (aSql != null) {
0797: String cleanSql = SqlUtil.makeCleanSql(aSql, false);
0798: List tables = SqlUtil.getTables(cleanSql);
0799: if (tables.size() == 1) {
0800: this .sqlTable = (String) tables.get(0);
0801: }
0802: } else {
0803: this .sqlTable = null;
0804: }
0805: }
0806:
0807: public String getSql() {
0808: return this .sql;
0809: }
0810:
0811: public int getNumberExportedTables() {
0812: return this .tablesExported;
0813: }
0814:
0815: private void startBackgroundThread() {
0816: Thread t = new WbThread("Export") {
0817: public void run() {
0818: try {
0819: startExport();
0820: } catch (Throwable th) {
0821: }
0822: }
0823: };
0824: t.start();
0825: }
0826:
0827: public void startExportJobs(final Frame parent) {
0828: if (this .showProgressWindow) {
0829: // the progress window is a modal dialog
0830: // so we need to open that in a new thread
0831: // otherwise this thread would be blocked
0832: WbThread p = new WbThread("Progress Thread") {
0833: public void run() {
0834: createProgressPanel();
0835: openProgressMonitor(parent);
0836: }
0837: };
0838: p.start();
0839: }
0840: Thread t = new WbThread("Export Jobs") {
0841: public void run() {
0842: try {
0843: runJobs();
0844: } catch (Throwable th) {
0845: }
0846: }
0847: };
0848: t.setPriority(Thread.MIN_PRIORITY);
0849: t.start();
0850: }
0851:
0852: public void runJobs() {
0853: if (this .jobQueue == null)
0854: return;
0855: int count = this .jobQueue.size();
0856:
0857: this .jobsRunning = true;
0858: this .cancelJobs = false;
0859: this .tablesExported = 0;
0860: this .totalRows = 0;
0861: this .setSql(null);
0862:
0863: if (this .exportWriter == null) {
0864: this .createExportWriter();
0865: }
0866:
0867: for (int i = 0; i < count; i++) {
0868: this .currentJob = this .jobQueue.get(i);
0869:
0870: this .setOutputFilename(this .currentJob.getOutputFile());
0871: if (this .progressPanel != null) {
0872: this .progressPanel.setFilename(this .outputfile);
0873: this .progressPanel.setRowInfo(0);
0874: this .progressWindow.pack();
0875: }
0876:
0877: if (this .rowMonitor != null
0878: && this .currentJob.getTableName() != null) {
0879: StringBuilder msg = new StringBuilder(80);
0880: msg.append(this .currentJob.getTableName());
0881: msg.append(" [");
0882: msg.append(NumberStringCache.getNumberString(i + 1));
0883: msg.append('/');
0884: msg.append(count);
0885: msg.append("] ");
0886: this .rowMonitor.setCurrentObject(msg.toString(), i + 1,
0887: count);
0888: }
0889:
0890: try {
0891: totalRows += this .startExport();
0892: this .tablesExported++;
0893: } catch (Throwable th) {
0894: LogMgr.logError("DataExporter.runJobs()",
0895: "Error exporting data for [" + this .sql
0896: + "] to file: " + this .outputfile, th);
0897: this .addError(th.getMessage());
0898: if (!this .continueOnError) {
0899: break;
0900: }
0901: }
0902: if (this .cancelJobs)
0903: break;
0904: }
0905: this .jobsRunning = false;
0906: this .closeProgress();
0907: }
0908:
0909: public long getTotalRows() {
0910: return this .totalRows;
0911: }
0912:
0913: public void setCurrentRow(int currentRow) {
0914: if (this .rowMonitor != null) {
0915: this .rowMonitor.setCurrentRow(currentRow, -1);
0916: }
0917: }
0918:
0919: /**
0920: * Start the export. This will execute the defined query
0921: * and then write the result into the outputfile
0922: *
0923: * @return the number of rows exported
0924: * @throws java.io.IOException if the output file could not be written
0925: * @throws java.sql.SQLException if an error occurred during DB access
0926: *
0927: */
0928: public long startExport() throws IOException, SQLException {
0929: Statement stmt = this .dbConn.createStatementForQuery();
0930: ResultSet rs = null;
0931: long rows = 0;
0932: boolean busyControl = false;
0933: try {
0934: if (!this .dbConn.isBusy()) {
0935: // only set the busy flag if the caller did not already do this!
0936: this .dbConn.setBusy(true);
0937: busyControl = true;
0938: }
0939: if (this .currentJob != null) {
0940: stmt.execute(this .currentJob.getQuerySql());
0941: } else {
0942: stmt.execute(this .sql);
0943: }
0944: rs = stmt.getResultSet();
0945: rows = this .startExport(rs);
0946: } catch (Exception e) {
0947: this .addError(ResourceMgr.getString("ErrExportExecute"));
0948: this .addError(ExceptionUtil.getDisplay(e));
0949: LogMgr
0950: .logError("DataExporter.startExport()",
0951: "Could not execute SQL statement: "
0952: + (currentJob != null ? currentJob
0953: .getQuerySql() : this .sql)
0954: + ", Error: "
0955: + ExceptionUtil.getDisplay(e), e);
0956: if (this .showProgressWindow) {
0957: if (!jobsRunning)
0958: this .closeProgress();
0959: WbSwingUtilities.showErrorMessage(this .parentWindow,
0960: ResourceMgr.getString("MsgExecuteError") + ": "
0961: + e.getMessage());
0962: }
0963: if (!this .dbConn.getAutoCommit()) {
0964: // Postgres needs a rollback, but this doesn't (or shouldn't!)
0965: // hurt with other DBMS either
0966: try {
0967: this .dbConn.rollback();
0968: } catch (Throwable th) {
0969: }
0970: }
0971: } finally {
0972: SqlUtil.closeAll(rs, stmt);
0973: if (!jobsRunning)
0974: this .closeProgress();
0975: if (busyControl)
0976: this .dbConn.setBusy(false);
0977: }
0978: return rows;
0979: }
0980:
0981: public boolean isSuccess() {
0982: return this .errors.getLength() == 0;
0983: }
0984:
0985: public boolean hasWarning() {
0986: return this .warnings.getLength() > 0;
0987: }
0988:
0989: public boolean hasError() {
0990: return this .errors.getLength() > 0;
0991: }
0992:
0993: public CharSequence getErrors() {
0994: // this will clear the internal buffer of the errors!
0995: return this .errors.getBuffer();
0996: }
0997:
0998: public CharSequence getWarnings() {
0999: // this will clear the internal buffer of the warnings!
1000: return this .warnings.getBuffer();
1001: }
1002:
1003: public void addWarning(String msg) {
1004: this .warnings.append(msg);
1005: this .warnings.appendNewLine();
1006: }
1007:
1008: public void addError(String msg) {
1009: this .errors.append(msg);
1010: this .errors.appendNewLine();
1011: }
1012:
1013: public long startExport(ResultSet rs) throws IOException,
1014: SQLException, Exception {
1015: try {
1016: ResultSetMetaData meta = rs.getMetaData();
1017: ResultInfo rsInfo = new ResultInfo(meta, this .dbConn);
1018: ResultInfo info = null;
1019: if (this .currentJob != null) {
1020: // Some JDBC drivers to not report the column's data types
1021: // correctly through ResultSet.getMetaData(), so we are
1022: // using the table information returned by DatabaseMetaData
1023: // instead (if this is a table export)
1024: info = currentJob.getResultInfo();
1025: for (int i = 0; i < info.getColumnCount(); i++) {
1026: int colIndex = rsInfo.findColumn(info
1027: .getColumnName(i));
1028: if (colIndex > -1) {
1029: info.setColumnClassName(i, rsInfo
1030: .getColumnClassName(i));
1031: }
1032: }
1033: } else {
1034: info = rsInfo;
1035: }
1036: configureExportWriter(info);
1037: this .exportWriter.exportStarting();
1038: this .exportWriter.writeExport(rs, info);
1039: } catch (SQLException e) {
1040: this .addError(e.getMessage());
1041: LogMgr.logError("DataExporter.startExport()", "SQL Error",
1042: e);
1043: throw e;
1044: } finally {
1045: exportFinished();
1046: try {
1047: rs.clearWarnings();
1048: } catch (Throwable th) {
1049: }
1050: try {
1051: rs.close();
1052: } catch (Throwable th) {
1053: }
1054: }
1055: long numRows = this .exportWriter.getNumberOfRecords();
1056: LogMgr.logInfo("DataExporter.startExport()", "Exported "
1057: + numRows + " rows to " + this .outputfile);
1058: return numRows;
1059: }
1060:
1061: public long startExport(DataStore ds) throws IOException,
1062: SQLException, Exception {
1063: try {
1064: ResultInfo info = ds.getResultInfo();
1065: configureExportWriter(info);
1066: this .exportWriter.exportStarting();
1067: this .exportWriter.writeExport(ds);
1068: } catch (SQLException e) {
1069: this .addError(e.getMessage());
1070: LogMgr.logError("DataExporter.startExport()", "SQL Error",
1071: e);
1072: throw e;
1073: } finally {
1074: exportFinished();
1075: }
1076: long numRows = this .exportWriter.getNumberOfRecords();
1077: return numRows;
1078: }
1079:
1080: private void exportFinished() {
1081: if (this .exportWriter != null)
1082: this .exportWriter.exportFinished();
1083: if (this .zipArchive != null) {
1084: try {
1085: this .zipArchive.close();
1086: this .zipArchive = null;
1087: this .zipEntry = null;
1088: } catch (Exception e) {
1089: LogMgr.logError("DataExporter.exportFinished()",
1090: "Error closing ZIP archive", e);
1091: }
1092: }
1093: if (!jobsRunning)
1094: this .closeProgress();
1095: }
1096:
1097: /**
1098: * Export a table to an external file.
1099: */
1100: private void configureExportWriter(ResultInfo info)
1101: throws IOException, SQLException, Exception {
1102: if (this .sqlTable != null) {
1103: info.setUpdateTable(new TableIdentifier(this .sqlTable));
1104: }
1105:
1106: if (this .encoding == null)
1107: this .encoding = Settings.getInstance()
1108: .getDefaultDataEncoding();
1109:
1110: if (this .tableName != null) {
1111: this .exportWriter.setTableToUse(this .tableName);
1112: }
1113:
1114: try {
1115: WbFile f = new WbFile(this .outputfile);
1116:
1117: if (exportWriter.managesOutput()) {
1118: exportWriter.setOutputFile(f);
1119: realOutputfile = f.getFullPath();
1120: } else {
1121: OutputStream out = null;
1122: if (this .getCompressOutput()) {
1123: WbFile wf = new WbFile(f);
1124: String baseName = wf.getFileName();
1125: String dir = wf.getParent();
1126: File zipfile = new File(dir, baseName + ".zip");
1127: OutputStream zout = new FileOutputStream(zipfile);
1128: this .zipArchive = new ZipOutputStream(zout);
1129: this .zipArchive.setLevel(9);
1130: this .zipEntry = new ZipEntry(wf.getName());
1131: this .zipArchive.putNextEntry(zipEntry);
1132: out = this .zipArchive;
1133: this .realOutputfile = zipfile.getCanonicalPath();
1134: } else {
1135: out = new FileOutputStream(f, append);
1136: this .realOutputfile = f.getFullPath();
1137: }
1138: Writer w = EncodingUtil
1139: .createWriter(out, this .encoding);
1140:
1141: this .exportWriter.setOutputWriter(w);
1142: }
1143: this .exportWriter.configureConverter();
1144: } catch (IOException e) {
1145: LogMgr.logError("DataExporter", "Error writing data file",
1146: e);
1147: throw e;
1148: }
1149:
1150: if (this .progressInterval > 0) {
1151: this .exportWriter.setRowMonitor(this .rowMonitor);
1152: this .exportWriter
1153: .setProgressInterval(this .progressInterval);
1154: } else if (this .rowMonitor != null) {
1155: this .rowMonitor
1156: .setMonitorType(RowActionMonitor.MONITOR_PLAIN);
1157: String msg = ResourceMgr.getString("MsgExportingData")
1158: + " " + this .realOutputfile;
1159: this .rowMonitor.setCurrentObject(msg, -1, -1);
1160: Thread.yield();
1161: }
1162:
1163: if (this .showProgressWindow) {
1164: if (this .progressPanel == null)
1165: createProgressPanel();
1166: this .exportWriter.setRowMonitor(this .progressPanel);
1167: this .progressPanel.setInfoText(ResourceMgr
1168: .getString("MsgSpoolingRow"));
1169: }
1170: }
1171:
1172: public void closeProgress() {
1173: if (this .progressWindow != null) {
1174: this .progressWindow.setVisible(false);
1175: this .progressWindow.dispose();
1176: this .progressPanel = null;
1177: }
1178: if (this .rowMonitor != null) {
1179: this .rowMonitor.jobFinished();
1180: }
1181: }
1182:
1183: public boolean selectOutput(Window parent) {
1184: ExportFileDialog dialog = new ExportFileDialog(parent);
1185: dialog.setQuerySql(this .sql, this .dbConn);
1186: dialog.setIncludeSqlInsert(true);
1187: //dialog.setIncludeSqlUpdate(true);
1188:
1189: boolean result = dialog.selectOutput();
1190: if (result) {
1191: dialog.setExporterOptions(this );
1192: }
1193: return result;
1194: }
1195:
1196: public void exportTable(Window aParent, TableIdentifier table) {
1197: this .parentWindow = aParent;
1198: ResultInfo info = null;
1199: try {
1200: info = new ResultInfo(table, this .dbConn);
1201: } catch (SQLException e) {
1202: info = null;
1203: }
1204: ExportFileDialog dialog = new ExportFileDialog(aParent, info);
1205:
1206: dialog.setIncludeSqlInsert(true);
1207: dialog.setIncludeSqlUpdate(true);
1208:
1209: boolean result = dialog.selectOutput();
1210: boolean tableExport = false;
1211:
1212: if (result) {
1213: try {
1214: StringBuilder query = new StringBuilder(250);
1215: query.append("SELECT ");
1216: List<ColumnIdentifier> cols = dialog
1217: .getColumnsToExport();
1218: if (cols != null) {
1219: boolean first = true;
1220: for (ColumnIdentifier col : cols) {
1221: if (!first)
1222: query.append(", ");
1223: else
1224: first = false;
1225: query.append(col.getColumnName());
1226: }
1227: query.append(" FROM ");
1228: query.append(table.getTableExpression(this .dbConn));
1229: this .setSql(query.toString());
1230: } else {
1231: tableExport = true;
1232: this .addTableExportJob(
1233: dialog.getSelectedFilename(), table);
1234: }
1235:
1236: dialog.setExporterOptions(this );
1237:
1238: Frame parent = null;
1239: if (aParent instanceof Frame) {
1240: parent = (Frame) aParent;
1241: }
1242:
1243: // In order to initialize the resultInfo as accurate as
1244: // possible, we use a table export when all columns are
1245: // selected. This way the column information will be retrieved
1246: // directly from the table definition and not from the
1247: // ResultSetMetadata object which might not return
1248: // the correct column types (e.g. in Postgres)
1249: // but for an XML export we want to have the types as
1250: // exact as possible to enable creating the target table later
1251: if (tableExport) {
1252: this .setShowProgressWindow(true);
1253: this .startExportJobs(parent);
1254: } else {
1255: this .startBackgroundThread();
1256: this .openProgressMonitor(parent);
1257: }
1258: } catch (Exception e) {
1259: LogMgr.logError("DataExporter.executeStatement()",
1260: "Could not export data", e);
1261: if (aParent != null) {
1262: WbSwingUtilities.showErrorMessage(aParent,
1263: ExceptionUtil.getDisplay(e));
1264: }
1265: }
1266: }
1267: }
1268:
1269: public void setOptions(ExportOptions options) {
1270: this .setEncoding(options.getEncoding());
1271: this .setDateFormat(options.getDateFormat());
1272: this .setTimestampFormat(options.getTimestampFormat());
1273: this .setEncoding(options.getEncoding());
1274: if (this .exportWriter != null)
1275: this .exportWriter.configureConverter();
1276: }
1277:
1278: public void setSqlOptions(SqlOptions sqlOptions) {
1279: if (sqlOptions.getCreateInsert()) {
1280: this .setOutputTypeSqlInsert();
1281: } else if (sqlOptions.getCreateUpdate()) {
1282: this .setOutputTypeSqlUpdate();
1283: } else if (sqlOptions.getCreateDeleteInsert()) {
1284: this .setOutputTypeSqlDeleteInsert();
1285: }
1286: this .setIncludeCreateTable(sqlOptions.getCreateTable());
1287: this .setCommitEvery(sqlOptions.getCommitEvery());
1288: this .setTableName(sqlOptions.getAlternateUpdateTable());
1289: this .setKeyColumnsToUse(sqlOptions.getKeyColumns());
1290: this .setDateLiteralType(sqlOptions.getDateLiteralType());
1291: this .exportWriter.configureConverter();
1292: }
1293:
1294: public void setXmlOptions(XmlOptions xmlOptions) {
1295: this .setOutputTypeXml();
1296: this .setUseCDATA(xmlOptions.getUseCDATA());
1297: this .setUseVerboseFormat(xmlOptions.getUseVerboseXml());
1298: this .exportWriter.configureConverter();
1299: }
1300:
1301: public void setHtmlOptions(HtmlOptions html) {
1302: this .setOutputTypeHtml();
1303: this .setCreateFullHtmlPage(html.getCreateFullPage());
1304: this .setPageTitle(html.getPageTitle());
1305: this .setEscapeHtml(html.getEscapeHtml());
1306: this .exportWriter.configureConverter();
1307: }
1308:
1309: public void setTextOptions(TextOptions text) {
1310: this .setOutputTypeText();
1311: this .setExportHeaders(text.getExportHeaders());
1312: this .setTextDelimiter(text.getTextDelimiter());
1313: this .setTextQuoteChar(text.getTextQuoteChar());
1314: this .setQuoteAlways(text.getQuoteAlways());
1315: this .setEscapeRange(text.getEscapeRange());
1316: this .setDecimalSymbol(text.getDecimalSymbol());
1317: this .setLineEnding(text.getLineEnding());
1318: this .exportWriter.configureConverter();
1319: }
1320:
1321: public void setXlsXOptions(SpreadSheetOptions xlsOptions) {
1322: this .setOutputTypeXlsXML();
1323: setSpreadsheetOptions(xlsOptions);
1324: }
1325:
1326: public void setXlsOptions(SpreadSheetOptions xlsOptions) {
1327: if (xlsOptions != null) {
1328: this .setOutputTypeXls();
1329: setSpreadsheetOptions(xlsOptions);
1330: }
1331: }
1332:
1333: public void setOdsOptions(SpreadSheetOptions odsOptions) {
1334: this .setOutputTypeOds();
1335: setSpreadsheetOptions(odsOptions);
1336: }
1337:
1338: public void setSpreadsheetOptions(SpreadSheetOptions odsOptions) {
1339: this .setPageTitle(odsOptions.getPageTitle());
1340: this .setExportHeaders(odsOptions.getExportHeaders());
1341: this .exportWriter.configureConverter();
1342: }
1343:
1344: public boolean isIncludeCreateTable() {
1345: return includeCreateTable;
1346: }
1347:
1348: public void setIncludeCreateTable(boolean includeCreateTable) {
1349: this .includeCreateTable = includeCreateTable;
1350: }
1351:
1352: /**
1353: * Getter for property keyColumnsToUse.
1354: * @return Value of property keyColumnsToUse.
1355: */
1356: public List getKeyColumnsToUse() {
1357: return keyColumnsToUse;
1358: }
1359:
1360: /**
1361: * Setter for property keyColumnsToUse.
1362: * @param keyColumnsToUse New value of property keyColumnsToUse.
1363: */
1364: public void setKeyColumnsToUse(java.util.List keyColumnsToUse) {
1365: this .keyColumnsToUse = keyColumnsToUse;
1366: }
1367:
1368: /**
1369: * Getter for property concatFunction.
1370: * @return Value of property concatFunction.
1371: */
1372: public String getConcatFunction() {
1373: return concatFunction;
1374: }
1375:
1376: /**
1377: * Setter for property concatFunction.
1378: * @param func New value of property concatFunction.
1379: */
1380: public void setConcatFunction(String func) {
1381: this .concatFunction = func;
1382: this .concatString = null;
1383: }
1384:
1385: public boolean getQuoteAlways() {
1386: return quoteAlways;
1387: }
1388:
1389: public void setQuoteAlways(boolean flag) {
1390: this .quoteAlways = flag;
1391: }
1392:
1393: public void setEscapeRange(CharacterRange range) {
1394: this .escapeRange = range;
1395: }
1396:
1397: public CharacterRange getEscapeRange() {
1398: return this .escapeRange;
1399: }
1400:
1401: public void setLineEnding(String ending) {
1402: if (ending != null)
1403: this .lineEnding = ending;
1404: }
1405:
1406: public String getLineEnding() {
1407: return this .lineEnding;
1408: }
1409:
1410: public boolean getUseVerboseFormat() {
1411: return verboseFormat;
1412: }
1413:
1414: public void setUseVerboseFormat(boolean flag) {
1415: this .verboseFormat = flag;
1416: }
1417:
1418: public Set<ControlFileFormat> getControlFileFormats() {
1419: return Collections.unmodifiableSet(controlFiles);
1420: }
1421:
1422: public void addControlFileFormat(ControlFileFormat format) {
1423: this .controlFiles.add(format);
1424: }
1425:
1426: public void addControlFileFormats(Set<ControlFileFormat> formats) {
1427: if (formats == null)
1428: return;
1429: this.controlFiles.addAll(formats);
1430: }
1431:
1432: }
|