001: /*
002: * Copyright (C) 2005 Rob Manning
003: * manningr@users.sourceforge.net
004: *
005: * This program is free software; you can redistribute it and/or
006: * modify it under the terms of the GNU General Public License
007: * as published by the Free Software Foundation; either version 2
008: * of the License, or any later version.
009: *
010: * This program is distributed in the hope that it will be useful,
011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
013: * GNU General Public License for more details.
014: *
015: * You should have received a copy of the GNU General Public License
016: * along with this program; if not, write to the Free Software
017: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
018: */
019: package net.sourceforge.squirrel_sql.plugins.dbcopy;
020:
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Types;
024:
025: import javax.swing.JFrame;
026:
027: import net.sourceforge.squirrel_sql.client.session.ISession;
028: import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory;
029: import net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect;
030: import net.sourceforge.squirrel_sql.fw.dialects.UserCancelledOperationException;
031: import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
032: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
033: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
034: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
035: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
036: import net.sourceforge.squirrel_sql.plugins.dbcopy.util.DBUtil;
037:
038: import org.hibernate.MappingException;
039:
040: /**
041: * This class uses column type defintions from the source session table column
042: * and uses that information to determine the correct column definition in the
043: * destination database using Hibernate.
044: */
045: public class ColTypeMapper {
046:
047: /** Logger for this class. */
048: private final static ILogger s_log = LoggerController
049: .createLogger(ColTypeMapper.class);
050:
051: /**
052: * Returns null if the user cancelled picking the dialect.
053: *
054: * @param sourceSession
055: * @param destSession
056: * @param TableColumnInfo
057: * @param sourceTableName the name of the table we are copying from. This
058: * might include the schema prefix
059: * @param destTableName the name of the table we are copying to. This
060: * might include the schema prefix
061: * @return
062: */
063: public static String mapColType(ISession sourceSession,
064: ISession destSession, TableColumnInfo colInfo,
065: String sourceTableName, String destTableName)
066: throws UserCancelledOperationException, MappingException {
067: int colJdbcType = colInfo.getDataType();
068:
069: // If source column is type 1111 (OTHER), try to use the
070: // column type name to find a type that isn't 1111.
071: colJdbcType = DBUtil.replaceOtherDataType(colInfo);
072:
073: // Oracle can only store DECIMAL type numbers. Since regular non-decimal
074: // numbers appear as "decimal", Oracle's decimal numbers can be rather
075: // large compared to other databases (precision up to 38). Other
076: // databases can only handle this large precision in BIGINT fields, not
077: // decimal, so try to figure out if Oracle is really storing a BIGINT
078: // and claiming it is a DECIMAL. If so, convert the type to BIGINT before
079: // going any further.
080: if (DialectFactory.isOracle(sourceSession.getMetaData())
081: && colJdbcType == Types.DECIMAL) {
082: // No decimal digits strongly suggests an INTEGER of some type.
083: // Since it's not real easy to tell what kind of int (int2, int4, int8)
084: // just make it an int8 (i.e. BIGINT)
085: if (colInfo.getDecimalDigits() == 0) {
086: colJdbcType = Types.BIGINT;
087: }
088: }
089: // For char or date types this is the maximum number of characters, for
090: // numeric or decimal types this is precision.
091: int size = getColumnLength(sourceSession, colInfo,
092: sourceTableName);
093:
094: if (DialectFactory.isPointbase(destSession.getMetaData())
095: && size <= 0) {
096: if (DBUtil.isBinaryType(colInfo)) {
097: // For PointBase, if type maps to Pointbase "BLOB", and the size
098: // isn't valid (PB requires size for BLOBS) then set it to something
099: // reasonably large, like 16MB. 1 is the default size if no size
100: // is specified. That's practically useless :)
101: size = 16777215;
102: } else {
103: size = 20; // Numbers and such.
104: }
105: }
106: if (DialectFactory.isFirebird(destSession.getMetaData())) {
107: if (colJdbcType == java.sql.Types.DECIMAL) {
108: if (size > 18) {
109: size = 18;
110: }
111: }
112: }
113: String result = null;
114: JFrame mainFrame = destSession.getApplication().getMainFrame();
115: HibernateDialect destDialect = DialectFactory.getDialect(
116: DialectFactory.DEST_TYPE, mainFrame, destSession
117: .getMetaData());
118:
119: if (s_log.isDebugEnabled()) {
120: s_log
121: .debug("ColTypeMapper.mapColType: using dialect type: "
122: + destDialect.getClass().getName()
123: + " to find name for column "
124: + colInfo.getColumnName()
125: + " in table "
126: + destTableName
127: + " with type id="
128: + colJdbcType
129: + " ("
130: + JDBCTypeMapper
131: .getJdbcTypeName(colJdbcType) + ")");
132: }
133: if (destDialect != null) {
134: HibernateDialect sourceDialect = DialectFactory.getDialect(
135: DialectFactory.SOURCE_TYPE, mainFrame,
136: sourceSession.getMetaData());
137:
138: int precision = sourceDialect.getPrecisionDigits(size,
139: colJdbcType);
140:
141: if (precision > destDialect.getMaxPrecision(colJdbcType)) {
142: precision = destDialect.getMaxPrecision(colJdbcType);
143: }
144: int scale = colInfo.getDecimalDigits();
145: if (scale > destDialect.getMaxScale(colJdbcType)) {
146: scale = destDialect.getMaxScale(colJdbcType);
147: }
148: // OK, this is a hack. Currently, when precision == scale, I have
149: // no way to determine if this is valid for the actual data. The
150: // problem comes when the source db's precision/scale are greater -
151: // or reported to be greater - than the precision/scale of the
152: // destination db. In this case, it maximimizes both for the
153: // destination, causing a definition that allows 0 digits to the
154: // left of the decimal. Trouble is, without looking at the actual
155: // data, there is no way to tell if this is valid - in some cases
156: // it will be ok (0.0000000789) in others it will not be ok (100.123).
157: // So for now, make the scale be approx. one-half of the precision
158: // to accomodate the most digits to the left and right of the decimal
159: // and hopefully that covers the majority of cases.
160: if (precision <= scale) {
161: if (precision < scale) {
162: precision = scale;
163: }
164: scale = precision / 2;
165: s_log
166: .debug("Precision == scale ("
167: + precision
168: + ") for the destination "
169: + "database column def. This is most likely incorrect, so "
170: + "setting the scale to a more reasonable value: "
171: + scale);
172:
173: }
174: // Some dbs (like McKoi) make -1 the default for scale. Apply the
175: // same hack as above.
176: if (scale < 0) {
177: scale = precision / 2;
178: s_log
179: .debug("scale is less than 0 for the destination "
180: + "database column def. This is most likely incorrect, so "
181: + "setting the scale to a more reasonable value: "
182: + scale);
183: }
184: result = destDialect.getTypeName(colJdbcType, size,
185: precision, scale);
186: }
187: return result;
188: }
189:
190: /**
191: * Gets the declared length of the column, or if the length is less than or
192: * equal to 0, get the max length of the actual data in the column from the
193: * database. In the case of Firebird with certain BLOB types it always
194: * reports 0, so 2GB is hard-coded. In the case of Oracle for CLOBs always
195: * use the maximum value of the column or 4000 whichever is greatest.(Oracle
196: * BLOBs/CLOBs always report 4000 as the column size, even when column
197: * values exceed this length)
198: *
199: * @param sourceSession
200: * @param colInfo
201: * @param tableName
202: * @return
203: */
204: public static int getColumnLength(ISession sourceSession,
205: TableColumnInfo colInfo, String tableName)
206: throws UserCancelledOperationException {
207: if (colInfo.getDataType() == Types.TIMESTAMP
208: || colInfo.getDataType() == Types.DATE
209: || colInfo.getDataType() == Types.TIME) {
210: // Date/Time types never declare a length. Just return something
211: // larger than 0 so we bypass other checks above.
212: return 10;
213: }
214: // Oracle declares the column size to be 4000, regardless of the maximum
215: // length of the CLOB field. So if the Oracle BLOB/CLOB column contains
216: // values that exceed 4000 chars and we use colInfo.getColumnSize() we
217: // might create a destination column that is too small for the data
218: // that will be copied from Oracle. We specify a default value of 4000
219: // in case the table has no records or if the BLOB/CLOB column contains
220: // only null values.
221: if (DialectFactory.isOracle(sourceSession.getMetaData())
222: && (colInfo.getDataType() == Types.CLOB || colInfo
223: .getDataType() == Types.BLOB)) {
224: return getColumnLengthBruteForce(sourceSession, colInfo,
225: tableName, 4000);
226: }
227: int length = getColumnLength(sourceSession, colInfo);
228: // As a last resort, get the length of the longest value in the
229: // specified column.
230: if (length <= 0) {
231: length = getColumnLengthBruteForce(sourceSession, colInfo,
232: tableName, 10);
233: }
234: return length;
235: }
236:
237: private static int getColumnLength(ISession sourceSession,
238: TableColumnInfo colInfo)
239: throws UserCancelledOperationException {
240: HibernateDialect dialect = DialectFactory.getDialect(
241: DialectFactory.SOURCE_TYPE, sourceSession
242: .getApplication().getMainFrame(), sourceSession
243: .getMetaData());
244: int length = colInfo.getColumnSize();
245: int type = colInfo.getDataType();
246: length = dialect.getColumnLength(length, type);
247: return length;
248: }
249:
250: private static int getColumnLengthBruteForce(
251: ISession sourceSession, TableColumnInfo colInfo,
252: String tableName, int defaultLength)
253: throws UserCancelledOperationException {
254: int length = defaultLength;
255: String sql = DBUtil.getMaxColumnLengthSQL(sourceSession,
256: colInfo, tableName, true);
257: ResultSet rs = null;
258: try {
259: rs = DBUtil.executeQuery(sourceSession, sql);
260: if (rs.next()) {
261: length = rs.getInt(1);
262: }
263: if (length <= 0) {
264: length = defaultLength;
265: }
266: } catch (SQLException e) {
267: s_log
268: .error("ColTypeMapper.getColumnLengthBruteForce: encountered "
269: + "unexpected SQLException - "
270: + e.getMessage());
271: } finally {
272: SQLUtilities.closeResultSet(rs);
273: }
274: return length;
275: }
276: }
|