001: /*
002: * SqlRowDataConverter.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.exporter;
013:
014: import java.sql.SQLException;
015: import java.util.List;
016: import workbench.db.ColumnIdentifier;
017: import workbench.db.DbMetadata;
018: import workbench.db.TableIdentifier;
019: import workbench.db.WbConnection;
020: import workbench.interfaces.Committer;
021: import workbench.log.LogMgr;
022: import workbench.resource.ResourceMgr;
023: import workbench.resource.Settings;
024: import workbench.storage.DmlStatement;
025: import workbench.storage.ResultInfo;
026: import workbench.storage.RowData;
027: import workbench.storage.SqlLiteralFormatter;
028: import workbench.storage.StatementFactory;
029: import workbench.util.ArrayUtil;
030: import workbench.util.StrBuffer;
031: import workbench.util.StringUtil;
032:
033: /**
034: *
035: * @author support@sql-workbench.net
036: */
037: public class SqlRowDataConverter extends RowDataConverter {
038: public static final int SQL_INSERT = 1;
039: public static final int SQL_UPDATE = 2;
040: public static final int SQL_DELETE_INSERT = 3;
041:
042: // This instance can be re-used for several
043: // table exports from DataExporter, to prevent
044: // that one failed export for the requested type
045: // resets the export type for subsequent tables
046: // the requested sqlType is stored in sqlType
047: // Upon setting the ResultInfo in setResultInfo()
048: // the sqlTypeToUse is set accordingly and then
049: // used in convertRowData()
050: private int sqlTypeToUse = SQL_INSERT;
051: private int sqlType = SQL_INSERT;
052:
053: private boolean createTable = false;
054: private TableIdentifier alternateUpdateTable;
055: private int commitEvery;
056: private String concatString;
057: private String chrFunction;
058: private String concatFunction;
059: private StatementFactory statementFactory;
060: private List keyColumnsToUse;
061: private String lineTerminator = "\n";
062: private String doubleLineTerminator = "\n\n";
063: private boolean includeOwner = true;
064: private boolean doFormatting = true;
065: private SqlLiteralFormatter literalFormatter;
066:
067: public SqlRowDataConverter(WbConnection con) {
068: super ();
069: setOriginalConnection(con);
070: }
071:
072: public void setOriginalConnection(WbConnection con) {
073: super .setOriginalConnection(con);
074: this .literalFormatter = new SqlLiteralFormatter(con);
075: }
076:
077: public void setResultInfo(ResultInfo meta) {
078: super .setResultInfo(meta);
079: this .statementFactory = new StatementFactory(meta,
080: this .originalConnection);
081: this .needsUpdateTable = meta.getUpdateTable() == null;
082: this .statementFactory.setIncludeTableOwner(this .includeOwner);
083: this .statementFactory.setTableToUse(this .alternateUpdateTable);
084:
085: boolean keysPresent = this .checkKeyColumns();
086: this .sqlTypeToUse = this .sqlType;
087: if (!keysPresent
088: && (this .sqlType == SQL_DELETE_INSERT || this .sqlType == SQL_UPDATE)) {
089: String tbl = "";
090: if (meta.getUpdateTable() != null) {
091: tbl = " (" + meta.getUpdateTable().getTableName() + ")";
092: }
093:
094: if (this .errorReporter != null) {
095: String msg = ResourceMgr.getString("ErrExportNoKeys")
096: + tbl;
097: this .errorReporter.addWarning(msg);
098: }
099:
100: LogMgr.logWarning("SqlRowDataConverter.setResultInfo()",
101: "No key columns found" + tbl
102: + " reverting back to INSERT generation");
103: this .sqlTypeToUse = SQL_INSERT;
104: }
105:
106: }
107:
108: public void setSqlLiteralType(String type) {
109: if (this .literalFormatter != null) {
110: this .literalFormatter.setProduct(type);
111: }
112: }
113:
114: public StrBuffer getEnd(long totalRows) {
115: boolean writeCommit = true;
116: if ((commitEvery == Committer.NO_COMMIT_FLAG)
117: || (commitEvery > 0 && (totalRows % commitEvery == 0))) {
118: writeCommit = false;
119: }
120:
121: StrBuffer end = null;
122: if (writeCommit) {
123: end = new StrBuffer();
124: end.append(lineTerminator);
125: end.append("COMMIT;");
126: end.append(lineTerminator);
127: }
128: return end;
129: }
130:
131: public void setType(int type) {
132: if (type == SQL_INSERT)
133: this .setCreateInsert();
134: else if (type == SQL_UPDATE)
135: this .setCreateUpdate();
136: else if (type == SQL_DELETE_INSERT)
137: this .setCreateInsertDelete();
138: else
139: throw new IllegalArgumentException("Invalid type specified");
140: }
141:
142: public StrBuffer convertRowData(RowData row, long rowIndex) {
143: StrBuffer result = new StrBuffer();
144: DmlStatement dml = null;
145: String db = null;
146: if (this .originalConnection != null) {
147: db = this .originalConnection.getDatabaseProductName();
148: }
149: this .statementFactory.setIncludeTableOwner(this .includeOwner);
150:
151: if (this .sqlTypeToUse == SQL_DELETE_INSERT) {
152: dml = this .statementFactory
153: .createDeleteStatement(row, true);
154: result.append(dml
155: .getExecutableStatement(this .literalFormatter));
156: result.append(';');
157: result.append(lineTerminator);
158: }
159: if (this .sqlTypeToUse == SQL_DELETE_INSERT
160: || this .sqlType == SQL_INSERT) {
161: dml = this .statementFactory.createInsertStatement(row,
162: true, "\n", this .exportColumns);
163: } else // implies sqlType == SQL_UPDATE
164: {
165: dml = this .statementFactory.createUpdateStatement(row,
166: true, "\n", this .exportColumns);
167: }
168: dml.setChrFunction(this .chrFunction);
169: dml.setConcatString(this .concatString);
170: dml.setConcatFunction(this .concatFunction);
171:
172: // Needed for formatting BLOBs in the literalFormatter
173: this .currentRow = rowIndex;
174: this .currentRowData = row;
175:
176: result
177: .append(dml
178: .getExecutableStatement(this .literalFormatter));
179: result.append(';');
180:
181: if (doFormatting)
182: result.append(doubleLineTerminator);
183: else
184: result.append(lineTerminator);
185:
186: if (this .commitEvery > 0 && ((rowIndex + 1) % commitEvery) == 0) {
187: result.append("COMMIT;");
188: result.append(doubleLineTerminator);
189: }
190: return result;
191: }
192:
193: public StrBuffer getStart() {
194: if (!this .createTable)
195: return null;
196: TableIdentifier updatetable = this .metaData.getUpdateTable();
197: if (updatetable == null && alternateUpdateTable == null) {
198: LogMgr.logError("SqlRowDataConverter.getStart()",
199: "Cannot write create table without update table!",
200: null);
201: return null;
202: }
203:
204: ColumnIdentifier[] colArray = this .metaData.getColumns();
205: List<ColumnIdentifier> cols = ArrayUtil.arrayToList(colArray);
206: DbMetadata db = this .originalConnection.getMetadata();
207: String source = db.getTableSource(updatetable, cols,
208: (alternateUpdateTable == null ? updatetable
209: .getTableName() : alternateUpdateTable
210: .getTableName()));
211: StrBuffer createSql = new StrBuffer(source);
212: createSql.append(doubleLineTerminator);
213: return createSql;
214: }
215:
216: public boolean isCreateInsert() {
217: return this .sqlTypeToUse == SQL_INSERT;
218: }
219:
220: public boolean isCreateUpdate() {
221: return this .sqlTypeToUse == SQL_UPDATE;
222: }
223:
224: public boolean isCreateInsertDelete() {
225: return this .sqlTypeToUse == SQL_DELETE_INSERT;
226: }
227:
228: public void setCreateInsert() {
229: this .sqlType = SQL_INSERT;
230: this .sqlTypeToUse = this .sqlType;
231: this .doFormatting = Settings.getInstance().getBoolProperty(
232: "workbench.sql.generate.insert.doformat", true);
233: }
234:
235: public void setCreateUpdate() {
236: this .sqlType = SQL_UPDATE;
237: this .sqlTypeToUse = this .sqlType;
238: this .doFormatting = Settings.getInstance().getBoolProperty(
239: "workbench.sql.generate.update.doformat", true);
240: }
241:
242: public void setCreateInsertDelete() {
243: this .sqlType = SQL_DELETE_INSERT;
244: this .sqlTypeToUse = this .sqlType;
245: this .doFormatting = Settings.getInstance().getBoolProperty(
246: "workbench.sql.generate.insert.doformat", true);
247: }
248:
249: private boolean checkKeyColumns() {
250: boolean keysPresent = metaData.hasPkColumns();
251:
252: if (this .keyColumnsToUse != null
253: && this .keyColumnsToUse.size() > 0) {
254: int keyCount = this .keyColumnsToUse.size();
255:
256: // make sure the default key columns are not used
257: this .metaData.resetPkColumns();
258:
259: for (int i = 0; i < keyCount; i++) {
260: this .metaData.setIsPkColumn(
261: (String) this .keyColumnsToUse.get(i), true);
262: }
263: keysPresent = true;
264: }
265:
266: if (!keysPresent) {
267: try {
268: this .metaData.readPkDefinition(this .originalConnection);
269: keysPresent = this .metaData.hasPkColumns();
270: } catch (SQLException e) {
271: LogMgr
272: .logError(
273: "SqlRowDataConverter.setCreateInsert",
274: "Could not read PK columns for update table",
275: e);
276: }
277: }
278: return keysPresent;
279: }
280:
281: public void setCommitEvery(int commitEvery) {
282: this .commitEvery = commitEvery;
283: }
284:
285: public void setConcatString(String concat) {
286: if (concat == null)
287: return;
288: this .concatString = concat;
289: this .concatFunction = null;
290: }
291:
292: public void setConcatFunction(String func) {
293: if (func == null)
294: return;
295: this .concatFunction = func;
296: this .concatString = null;
297: }
298:
299: public String getChrFunction() {
300: return chrFunction;
301: }
302:
303: public void setChrFunction(String chrFunction) {
304: this .chrFunction = chrFunction;
305: }
306:
307: /**
308: * Getter for property createTable.
309: * @return Value of property createTable.
310: */
311: public boolean isCreateTable() {
312: return createTable;
313: }
314:
315: /**
316: * Setter for property createTable.
317: * @param flag New value of property createTable.
318: */
319: public void setCreateTable(boolean flag) {
320: this .createTable = flag;
321: }
322:
323: /**
324: * Setter for property alternateUpdateTable.
325: * @param table New value of property alternateUpdateTable.
326: */
327: public void setAlternateUpdateTable(TableIdentifier table) {
328: if (table != null) {
329: this .alternateUpdateTable = table;
330: this .needsUpdateTable = false;
331: if (this .statementFactory != null)
332: this .statementFactory
333: .setTableToUse(this .alternateUpdateTable);
334: } else {
335: this .alternateUpdateTable = null;
336: this .needsUpdateTable = true;
337: }
338: }
339:
340: /**
341: * Getter for property keyColumnsToUse.
342: * @return Value of property keyColumnsToUse.
343: */
344: public List getKeyColumnsToUse() {
345: return keyColumnsToUse;
346: }
347:
348: /**
349: * Setter for property keyColumnsToUse.
350: * @param keyColumnsToUse New value of property keyColumnsToUse.
351: */
352: public void setKeyColumnsToUse(List keyColumnsToUse) {
353: this .keyColumnsToUse = keyColumnsToUse;
354: }
355:
356: public void setLineTerminator(String lineEnd) {
357: this .lineTerminator = lineEnd;
358: this .doubleLineTerminator = lineEnd + lineEnd;
359: }
360:
361: public void setIncludeTableOwner(boolean flag) {
362: this .includeOwner = flag;
363: if (this .statementFactory != null)
364: this .statementFactory.setIncludeTableOwner(flag);
365: }
366:
367: public void setBlobTypeNone() {
368: this .literalFormatter.noBlobHandling();
369: }
370:
371: public void setBlobTypeDbmsLiteral() {
372: if (this .literalFormatter != null)
373: this .literalFormatter
374: .createDbmsBlobLiterals(originalConnection);
375: }
376:
377: public void setBlobTypeAnsiLiteral() {
378: if (this .literalFormatter != null)
379: literalFormatter.createAnsiBlobLiterals();
380: }
381:
382: public void setBlobTypeFile() {
383: if (this .literalFormatter != null)
384: literalFormatter.createBlobFiles(this );
385: }
386:
387: public void setClobAsFile(String encoding) {
388: if (StringUtil.isEmptyString(encoding))
389: return;
390: if (this.literalFormatter != null) {
391: literalFormatter.setTreatClobAsFile(this, encoding);
392: }
393: }
394:
395: }
|