001: /*
002: * StatementFactory.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.storage;
013:
014: import java.util.ArrayList;
015: import java.util.List;
016: import workbench.db.ColumnIdentifier;
017: import workbench.db.ConnectionProfile;
018:
019: import workbench.db.TableIdentifier;
020: import workbench.db.WbConnection;
021: import workbench.resource.Settings;
022: import workbench.util.StringUtil;
023:
024: /**
025: * A class to generate DELETE, INSERT or UPDATE statements based
026: * on the data in a {@link workbench.storage.RowData} object.
027: *
028: * @author support@sql-workbench.net
029: */
030: public class StatementFactory {
031: private ResultInfo resultInfo;
032: private TableIdentifier tableToUse;
033: private boolean includeTableOwner = true;
034: private WbConnection dbConnection;
035: private boolean emptyStringIsNull = false;
036: private boolean includeNullInInsert = true;
037:
038: private static final int CASE_NO_CHANGE = 1;
039: private static final int CASE_UPPER = 2;
040: private static final int CASE_LOWER = 4;
041: private int identifierCase = CASE_NO_CHANGE;
042:
043: /**
044: * @param metaData the description of the resultSet for which the statements are generated
045: * @param conn the database connection for which the statements are generated
046: */
047: public StatementFactory(ResultInfo metaData, WbConnection conn) {
048: this .resultInfo = metaData;
049: this .setCurrentConnection(conn);
050: String s = Settings.getInstance().getGeneratedSqlTableCase();
051: if (!StringUtil.isEmptyString(s)) {
052: if (s.equals("lower"))
053: identifierCase = CASE_LOWER;
054: else if (s.equals("upper"))
055: identifierCase = CASE_UPPER;
056: }
057: }
058:
059: public DmlStatement createUpdateStatement(RowData aRow,
060: boolean ignoreStatus, String lineEnd) {
061: return createUpdateStatement(aRow, ignoreStatus, lineEnd, null);
062: }
063:
064: /**
065: * Create an UPDATE Statement based on the data provided
066: *
067: * @param aRow the RowData that should be used for the UPDATE statement
068: * @param ignoreStatus if set to true all columns will be included (otherwise only modified columns)
069: * @param lineEnd the character sequence to be used as the line ending
070: * @param columns a list of columns to be included. If this is null all columns are included
071: */
072: public DmlStatement createUpdateStatement(RowData aRow,
073: boolean ignoreStatus, String lineEnd, List columns) {
074: if (aRow == null)
075: return null;
076: boolean first = true;
077: int cols = this .resultInfo.getColumnCount();
078:
079: boolean doFormatting = Settings.getInstance()
080: .getDoFormatUpdates();
081: int columnThresholdForNewline = Settings.getInstance()
082: .getFormatUpdateColumnThreshold();
083:
084: boolean newLineAfterColumn = doFormatting
085: && (cols > columnThresholdForNewline);
086:
087: if (!resultInfo.hasPkColumns())
088: throw new IllegalArgumentException(
089: "Cannot proceed without a primary key");
090:
091: DmlStatement dml = null;
092:
093: if (!ignoreStatus && !aRow.isModified())
094: return null;
095: ArrayList<ColumnData> values = new ArrayList<ColumnData>(cols);
096: StringBuilder sql = new StringBuilder("UPDATE ");
097:
098: sql.append(getTableNameToUse());
099: if (doFormatting)
100: sql.append("\n ");
101: sql.append(" SET ");
102: first = true;
103:
104: for (int col = 0; col < cols; col++) {
105: if (columns != null) {
106: if (!columns.contains(this .resultInfo.getColumn(col)))
107: continue;
108: }
109:
110: if (aRow.isColumnModified(col)
111: || (ignoreStatus && !this .resultInfo
112: .isPkColumn(col))) {
113: if (first) {
114: first = false;
115: } else {
116: sql.append(", ");
117: if (newLineAfterColumn)
118: sql.append("\n ");
119: }
120: String colName = adjustColumnName(this .resultInfo
121: .getColumnName(col));
122:
123: sql.append(colName);
124: Object value = aRow.getValue(col);
125: if (isNull(value)) {
126: sql.append(" = NULL");
127: } else {
128: sql.append(" = ?");
129: values.add(new ColumnData(value, this .resultInfo
130: .getColumn(col)));
131: }
132: }
133: }
134: if (doFormatting)
135: sql.append("\n ");
136: sql.append(" WHERE ");
137: first = true;
138: int count = this .resultInfo.getColumnCount();
139: for (int j = 0; j < count; j++) {
140: if (!this .resultInfo.isPkColumn(j))
141: continue;
142: if (first) {
143: first = false;
144: } else {
145: sql.append(" AND ");
146: }
147: String colName = adjustColumnName(this .resultInfo
148: .getColumnName(j));
149: sql.append(colName);
150:
151: Object value = aRow.getOriginalValue(j);
152: if (value == null) {
153: sql.append(" IS NULL");
154: } else {
155: sql.append(" = ?");
156: values.add(new ColumnData(value, this .resultInfo
157: .getColumn(j)));
158: }
159: }
160:
161: dml = new DmlStatement(sql, values);
162: return dml;
163: }
164:
165: public DmlStatement createInsertStatement(RowData aRow,
166: boolean ignoreStatus) {
167: return this .createInsertStatement(aRow, ignoreStatus, "\n",
168: null);
169: }
170:
171: public DmlStatement createInsertStatement(RowData aRow,
172: boolean ignoreStatus, String lineEnd) {
173: return this .createInsertStatement(aRow, ignoreStatus, lineEnd,
174: null);
175: }
176:
177: /**
178: * Generate an insert statement for the given row
179: * When creating a script for the DataStore the ignoreStatus
180: * will be passed as true, thus ignoring the row status and
181: * some basic formatting will be applied to the SQL Statement
182: *
183: * @param aRow the RowData that should be used for the insert statement
184: * @param ignoreStatus if set to true all columns will be included (otherwise only modified columns)
185: * @param lineEnd the character sequence to be used as the line ending
186: * @param columns a list of columns to be included. If this is null all columns are included
187: */
188: public DmlStatement createInsertStatement(RowData aRow,
189: boolean ignoreStatus, String lineEnd, List columns) {
190: boolean first = true;
191: DmlStatement dml;
192:
193: if (!ignoreStatus && !aRow.isModified())
194: return null;
195:
196: int cols = this .resultInfo.getColumnCount();
197:
198: boolean doFormatting = Settings.getInstance()
199: .getDoFormatInserts();
200: int columnThresholdForNewline = Settings
201: .getInstance()
202: .getIntProperty(
203: "workbench.sql.generate.insert.newlinethreshold",
204: 5);
205: boolean newLineAfterColumn = doFormatting
206: && (cols > columnThresholdForNewline);
207: boolean skipIdentityCols = Settings.getInstance()
208: .getFormatInsertIgnoreIdentity();
209: int colsPerLine = Settings.getInstance()
210: .getFormatInsertColsPerLine();
211:
212: ArrayList<ColumnData> values = new ArrayList<ColumnData>(cols);
213: StringBuilder sql = new StringBuilder(250);
214: sql.append("INSERT INTO ");
215: StringBuilder valuePart = new StringBuilder(250);
216:
217: sql.append(getTableNameToUse());
218: if (doFormatting)
219: sql.append(lineEnd);
220: else
221: sql.append(' ');
222:
223: sql.append('(');
224: if (newLineAfterColumn) {
225: sql.append(lineEnd);
226: sql.append(" ");
227: valuePart.append(lineEnd);
228: valuePart.append(" ");
229: if (colsPerLine == 1) {
230: sql.append(" ");
231: valuePart.append(" ");
232: }
233: }
234:
235: first = true;
236: String colName = null;
237: int colsInThisLine = 0;
238:
239: for (int col = 0; col < cols; col++) {
240: ColumnIdentifier colId = this .resultInfo.getColumn(col);
241: if (columns != null) {
242: if (!columns.contains(colId))
243: continue;
244: }
245:
246: if (skipIdentityCols && colId.isIdentityColumn())
247: continue;
248:
249: Object value = aRow.getValue(col);
250: boolean isNull = isNull(value);
251:
252: boolean includeCol = (ignoreStatus || aRow
253: .isColumnModified(col));
254:
255: if (includeCol) {
256: if (isNull) {
257: includeCol = this .includeNullInInsert;
258: }
259: }
260:
261: if (includeCol) {
262: if (!first) {
263: if (newLineAfterColumn
264: && colsInThisLine >= colsPerLine) {
265: if (colsPerLine == 1) {
266: sql.append(lineEnd);
267: valuePart.append(lineEnd);
268: sql.append(" , ");
269: valuePart.append(" , ");
270: } else {
271: sql.append(", ");
272: valuePart.append(", ");
273: sql.append(lineEnd);
274: valuePart.append(lineEnd);
275: }
276: colsInThisLine = 0;
277: } else {
278: sql.append(", ");
279: valuePart.append(", ");
280: }
281: } else {
282: first = false;
283: }
284:
285: colName = adjustColumnName(this .resultInfo
286: .getColumnName(col));
287:
288: sql.append(colName);
289: valuePart.append('?');
290:
291: values.add(new ColumnData(value, this .resultInfo
292: .getColumn(col)));
293: }
294: colsInThisLine++;
295: }
296: if (newLineAfterColumn) {
297: sql.append(lineEnd);
298: valuePart.append(lineEnd);
299: }
300:
301: sql.append(')');
302: if (doFormatting) {
303: sql.append(lineEnd);
304: sql.append("VALUES");
305: sql.append(lineEnd);
306: } else {
307: sql.append(" VALUES ");
308: }
309: sql.append('(');
310: sql.append(valuePart);
311: sql.append(')');
312:
313: dml = new DmlStatement(sql, values);
314: return dml;
315: }
316:
317: public DmlStatement createDeleteStatement(RowData aRow) {
318: return createDeleteStatement(aRow, false);
319: }
320:
321: public DmlStatement createDeleteStatement(RowData aRow,
322: boolean ignoreStatus) {
323: if (aRow == null)
324: return null;
325: if (!ignoreStatus && aRow.isNew())
326: return null;
327:
328: boolean first = true;
329: DmlStatement dml;
330: int count = this .resultInfo.getColumnCount();
331:
332: ArrayList<ColumnData> values = new ArrayList<ColumnData>(count);
333: StringBuilder sql = new StringBuilder(250);
334: sql.append("DELETE FROM ");
335: sql.append(getTableNameToUse());
336: sql.append(" WHERE ");
337: first = true;
338:
339: for (int j = 0; j < count; j++) {
340: if (!this .resultInfo.isPkColumn(j))
341: continue;
342: if (first) {
343: first = false;
344: } else {
345: sql.append(" AND ");
346: }
347: String colName = adjustColumnName(this .resultInfo
348: .getColumnName(j));
349: sql.append(colName);
350:
351: Object value = aRow.getOriginalValue(j);
352: if (isNull(value))
353: value = null;
354: if (value == null) {
355: sql.append(" IS NULL");
356: } else {
357: sql.append(" = ?");
358: values.add(new ColumnData(value, resultInfo
359: .getColumn(j)));
360: }
361: }
362:
363: dml = new DmlStatement(sql, values);
364: return dml;
365: }
366:
367: /**
368: * Setter for property tableToUse.
369: * @param tableToUse New value of property tableToUse.
370: */
371: public void setTableToUse(TableIdentifier tableToUse) {
372: this .tableToUse = tableToUse;
373: }
374:
375: public void setIncludeTableOwner(boolean flag) {
376: this .includeTableOwner = flag;
377: }
378:
379: public boolean getIncludeTableOwner() {
380: return this .includeTableOwner;
381: }
382:
383: public void setCurrentConnection(WbConnection conn) {
384: this .dbConnection = conn;
385: if (this .dbConnection != null) {
386: ConnectionProfile prof = dbConnection.getProfile();
387: emptyStringIsNull = (prof == null ? true : prof
388: .getEmptyStringIsNull());
389: includeNullInInsert = (prof == null ? true : prof
390: .getIncludeNullInInsert());
391: }
392: }
393:
394: private String adjustColumnName(String value) {
395: if (value == null)
396: return null;
397: if (value.startsWith("\""))
398: return value;
399: if (dbConnection != null
400: && !dbConnection.getMetadata().isDefaultCase(value)) {
401: return dbConnection.getMetadata().quoteObjectname(value);
402: }
403: return value;
404: }
405:
406: private String adjustIdentifierCase(String value) {
407: if (value == null)
408: return null;
409: if (value.startsWith("\""))
410: return value;
411:
412: // If the table name is not in the same case the server stores it
413: // and the case may not be changed at all, then we need to quote the table name.
414:
415: TableIdentifier updateTable = this .resultInfo.getUpdateTable();
416:
417: // setNeverAdjustCase() will only be set for TableIdentifiers that have
418: // been "retrieved" from the database (e.g. in the DbExplorer)
419: // For table names that the user entered, neverAdjustCase() will be false
420: boolean neverAdjust = (updateTable == null ? false
421: : updateTable.getNeverAdjustCase());
422:
423: if (neverAdjust && dbConnection != null) {
424: boolean caseSensitive = dbConnection.getMetadata()
425: .isCaseSensitive();
426: boolean defaultCase = dbConnection.getMetadata()
427: .isDefaultCase(value);
428: if (caseSensitive)
429: return value;
430: if (!defaultCase)
431: return dbConnection.getMetadata()
432: .quoteObjectname(value);
433: }
434:
435: if (this .identifierCase == CASE_UPPER) {
436: return value.toUpperCase();
437: } else if (this .identifierCase == CASE_LOWER) {
438: return value.toLowerCase();
439: }
440:
441: return value;
442: }
443:
444: private String getTableNameToUse() {
445: String name = null;
446: TableIdentifier updateTable = this .resultInfo.getUpdateTable();
447: if (updateTable == null && this .tableToUse == null)
448: throw new IllegalArgumentException(
449: "Cannot proceed without update table defined");
450:
451: if (this .tableToUse != null) {
452: if (!includeTableOwner) {
453: name = tableToUse.getTableName();
454: } else {
455: name = tableToUse.getTableExpression(this .dbConnection);
456: }
457: } else {
458: name = (includeTableOwner ? updateTable
459: .getTableExpression(this .dbConnection)
460: : updateTable.getTableName());
461: }
462: name = adjustIdentifierCase(name);
463: return name;
464: }
465:
466: private boolean isNull(Object value) {
467: if (value == null)
468: return true;
469: // if (value instanceof NullValue) return true;
470: String s = value.toString();
471: if (emptyStringIsNull && s.length() == 0)
472: return true;
473: return false;
474: }
475:
476: }
|