001: /*
002: * SqlLiteralFormatter.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.io.File;
015: import java.sql.Time;
016: import java.sql.Timestamp;
017: import java.text.SimpleDateFormat;
018: import java.util.Date;
019: import workbench.db.DbSettings;
020: import workbench.db.WbConnection;
021: import workbench.interfaces.DataFileWriter;
022: import workbench.log.LogMgr;
023: import workbench.log.LogMgr;
024: import workbench.resource.Settings;
025: import workbench.util.SqlUtil;
026: import workbench.util.StringUtil;
027:
028: /**
029: *
030: * @author support@sql-workbench.net
031: */
032: public class SqlLiteralFormatter {
033: /**
034: * The "product" for the jdbc date literal format
035: */
036: public static final String JDBC_DATE_LITERAL_TYPE = "jdbc";
037:
038: /**
039: * The "product" for the ansi date literal format
040: */
041: public static final String ANSI_DATE_LITERAL_TYPE = "ansi";
042:
043: /**
044: * The "product" for the standard date literal format
045: */
046: public static final String STANDARD_DATE_LITERAL_TYPE = "default";
047:
048: /**
049: * The "product" for the dbms specific date literal format
050: */
051: public static final String DBMS_DATE_LITERAL_TYPE = "dbms";
052:
053: private SimpleDateFormat dateFormatter;
054: private SimpleDateFormat timestampFormatter;
055: private SimpleDateFormat timeFormatter;
056: private BlobLiteralFormatter blobFormatter;
057: private DataFileWriter blobWriter;
058: private DataFileWriter clobWriter;
059: private boolean treatClobAsFile = false;
060: private String clobEncoding = Settings.getInstance()
061: .getDefaultFileEncoding();
062: private boolean isDbId;
063: private DbSettings dbSettings;
064:
065: /**
066: * Create a new formatter with default formatting.
067: */
068: public SqlLiteralFormatter() {
069: this (null);
070: }
071:
072: /**
073: * Create new formatter specifically for the DBMS identified
074: * by the connection.
075: * The type of date literals used, can be changed to a different
076: * "product" using {@link #setProduct(String)}
077: *
078: * @param con the connection identifying the DBMS
079: *
080: * @see workbench.db.DbMetadata#getProductName()
081: */
082: public SqlLiteralFormatter(WbConnection con) {
083: String product = null;
084: isDbId = false;
085: if (con != null && con.getMetadata() != null) {
086: product = con.getMetadata().getDbId();
087: isDbId = true;
088: }
089: setProduct(product);
090: }
091:
092: /**
093: * Select the DBMS specific date literal according to the
094: * DBMS identified by the connection.
095: * @param con the connection to identify the DBMS
096: * @see #setProduct(String)
097: */
098: public void setProduct(WbConnection con) {
099: if (con != null) {
100: String product = con.getMetadata().getDbId();
101: isDbId = true;
102: this .setProduct(product);
103: this .dbSettings = con.getDbSettings();
104: }
105: }
106:
107: /**
108: * Use a specific product name for formatting date and timestamp values.
109: * This call is ignored if the passed value is DBMS and this instance has
110: * been initialised with a Connection (thus the DBMS specific formatter is already
111: * selected).
112: *
113: * @param product the product to use. This is the key to the map defining the formats
114: *
115: * @see workbench.db.DbMetadata#getProductName()
116: */
117: public void setProduct(String product) {
118: // If the DBMS specific format is selected and we already have a DBID
119: // then this call is simply ignored.
120: if (DBMS_DATE_LITERAL_TYPE.equalsIgnoreCase(product)) {
121: if (this .isDbId) {
122: return;
123: }
124: product = null;
125: }
126:
127: dateFormatter = createFormatter(product, "date",
128: "''yyyy-MM-dd''");
129: timestampFormatter = createFormatter(product, "timestamp",
130: "''yyyy-MM-dd HH:mm:ss''");
131: timeFormatter = createFormatter(product, "time", "''HH:mm:ss''");
132: }
133:
134: /**
135: * Do not write BLOBs as SQL Literals.
136: */
137: public void noBlobHandling() {
138: this .blobWriter = null;
139: this .blobFormatter = null;
140: }
141:
142: /**
143: * Create ANSI compatible BLOB literals
144: */
145: public void createAnsiBlobLiterals() {
146: blobFormatter = BlobFormatterFactory.createAnsiFormatter();
147: this .blobWriter = null;
148: }
149:
150: /**
151: * Create BLOB literals that are compatible with the
152: * DBMS identified by the connection.
153: * If no specific formatter for the given DMBS can be found, the generic
154: * ANSI formatter will be used.
155: * @param con the connection (i.e. the DBMS) for which the literals should be created
156: */
157: public void createDbmsBlobLiterals(WbConnection con) {
158: if (con != null) {
159: blobFormatter = BlobFormatterFactory.createInstance(con
160: .getMetadata());
161: this .blobWriter = null;
162: }
163: }
164:
165: /**
166: * Create external BLOB files instead of BLOB literals.
167: * This will reset any literal formatting selected with createAnsiBlobLiterals()
168: * or createDbmsBlobLiterals().
169: * The generated SQL Literal will be compatible with SQL Workbench extended
170: * blob handling and will generate literals in the format <code>{$blobfile=...}</code>
171: *
172: * @param bw the writer to be used for writing the BLOB content
173: */
174: public void createBlobFiles(DataFileWriter bw) {
175: this .blobFormatter = null;
176: this .blobWriter = bw;
177: }
178:
179: /**
180: * Create external files for CLOB columns (instead of String literals).
181: * The generated SQL Literal will be compatible with SQL Workbench extended
182: * LOB handling and will generate literals in the format <code>{$clobfile='...' encoding='encoding'}</code>
183: *
184: * @param writer the writer to be used for writing the BLOB content
185: * @param encoding the encoding to be used to write the CLOB files
186: */
187: public void setTreatClobAsFile(DataFileWriter writer,
188: String encoding) {
189: this .treatClobAsFile = true;
190: this .clobWriter = writer;
191: if (!StringUtil.isEmptyString(encoding))
192: this .clobEncoding = encoding;
193: }
194:
195: private SimpleDateFormat createFormatter(String format,
196: String type, String defaultPattern) {
197: String key = "workbench.sql.literals."
198: + (format == null ? STANDARD_DATE_LITERAL_TYPE : format)
199: + "." + type + ".pattern";
200: SimpleDateFormat f = null;
201: String pattern = null;
202: try {
203: pattern = Settings.getInstance().getProperty(key, null);
204: if (pattern == null) {
205: key = "workbench.sql.literals."
206: + STANDARD_DATE_LITERAL_TYPE + "." + type
207: + ".pattern";
208: pattern = Settings.getInstance().getProperty(key,
209: defaultPattern);
210: }
211: f = new SimpleDateFormat(pattern);
212: } catch (Exception e) {
213: LogMgr.logError("SqlLiteralFormatter.createFormatter()",
214: "Could not create formatter with pattern ["
215: + pattern + "], using default ["
216: + defaultPattern + "]", e);
217: f = new SimpleDateFormat(defaultPattern);
218: }
219: return f;
220: }
221:
222: private String quoteString(String t) {
223: StringBuilder realValue = new StringBuilder(t.length() + 10);
224:
225: // Surround the value with single quotes
226: realValue.append('\'');
227:
228: // Single quotes in a String must be "quoted"...
229: // replaceToBuffer writes the result directly into the passed buffer
230: StringUtil.replaceToBuffer(realValue, t, "'", "''");
231:
232: realValue.append('\'');
233: return realValue.toString();
234: }
235:
236: /**
237: * Return the default literal for the given column data.
238: * Date and Timestamp data will be formatted according to the
239: * syntax defined by the {@link #setProduct(String)} method
240: * or through the connection provided in the constructor.
241: * @param data the data to be converted into a literal.
242: * @return the literal to be used in a SQL statement
243: * @see #setProduct(String)
244: */
245: public CharSequence getDefaultLiteral(ColumnData data) {
246: if (data.isNull())
247: return "NULL";
248:
249: Object value = data.getValue();
250: if (value == null)
251: return "NULL";
252:
253: int type = data.getIdentifier().getDataType();
254:
255: if (value == null) {
256: return "NULL";
257: } else if (value instanceof String) {
258: String t = (String) value;
259: if (this .treatClobAsFile && clobWriter != null
260: && SqlUtil.isClobType(type, dbSettings)) {
261: File f = clobWriter.generateDataFileName(data);
262: try {
263: clobWriter.writeClobFile(t, f, this .clobEncoding);
264: return "{$clobfile='" + f.getName()
265: + "' encoding='" + this .clobEncoding + "'}";
266: } catch (Exception e) {
267: LogMgr.logError(
268: "SqlLiteralFormatter.getDefaultLiteral",
269: "Could not write CLOB file", e);
270: return quoteString(t);
271: }
272: } else {
273: return quoteString(t);
274: }
275: } else if (value instanceof Time) {
276: return this .timeFormatter.format((Time) value);
277: } else if (value instanceof Timestamp) {
278: return this .timestampFormatter.format((Timestamp) value);
279: } else if (value instanceof Date) {
280: return this .dateFormatter.format((Date) value);
281: } else if (value instanceof File) {
282: File f = (File) value;
283: String path = null;
284: try {
285: path = f.getCanonicalPath();
286: } catch (Exception e) {
287: path = f.getAbsolutePath();
288: }
289: if (SqlUtil.isBlobType(type))
290: return "{$blobfile='" + path + "'}";
291: else if (SqlUtil.isClobType(type))
292: return "{$clobfile='" + path + "' encoding='"
293: + this .clobEncoding + "'}";
294: } else if (type == java.sql.Types.BIT
295: && "bit".equalsIgnoreCase(data.getIdentifier()
296: .getDbmsType())) {
297: // this is for MS SQL Server
298: // we cannot convert all values denoted as Types.BIT to 0/1 as
299: // e.g. Postgres only accepts the literals true/false for boolean columns
300: // which are reported as Types.BIT as well.
301: // that's why I compare to the DBMS data type bit (hoping that
302: // other DBMS's that are also using 'bit' work the same way
303: boolean flag = ((java.lang.Boolean) value).booleanValue();
304: return (flag ? "1" : "0");
305: } else if (SqlUtil.isBlobType(type)) {
306: if (blobWriter != null) {
307: File f = blobWriter.generateDataFileName(data);
308: try {
309: blobWriter.writeBlobFile(value, f);
310: return "{$blobfile='" + f.getName() + "'}";
311: } catch (Exception e) {
312: LogMgr.logError(
313: "SqlLiteralFormatter.getDefaultLiteral",
314: "Could not write BLOB file", e);
315: }
316: } else if (blobFormatter != null) {
317: try {
318: return blobFormatter.getBlobLiteral(value);
319: } catch (Exception e) {
320: LogMgr.logError(
321: "SqlLiteralFormatter.getDefaultLiteral",
322: "Error converting BLOB value", e);
323: }
324: }
325: }
326:
327: // Fallback, let the JDBC driver format the value
328: return value.toString();
329: }
330:
331: }
|