0001: /*
0002: * Copyright (C) 2005 Rob Manning
0003: * manningr@users.sourceforge.net
0004: *
0005: * This library is free software; you can redistribute it and/or
0006: * modify it under the terms of the GNU Lesser General Public
0007: * License as published by the Free Software Foundation; either
0008: * version 2.1 of the License, or (at your option) any later version.
0009: *
0010: * This library is distributed in the hope that it will be useful,
0011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0013: * Lesser General Public License for more details.
0014: *
0015: * You should have received a copy of the GNU Lesser General Public
0016: * License along with this library; if not, write to the Free Software
0017: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0018: */
0019:
0020: package net.sourceforge.squirrel_sql.plugins.dbcopy.util;
0021:
0022: import java.io.BufferedInputStream;
0023: import java.io.File;
0024: import java.io.FileInputStream;
0025: import java.io.FileOutputStream;
0026: import java.io.IOException;
0027: import java.io.InputStream;
0028: import java.math.BigDecimal;
0029: import java.sql.Array;
0030: import java.sql.Connection;
0031: import java.sql.DatabaseMetaData;
0032: import java.sql.Date;
0033: import java.sql.PreparedStatement;
0034: import java.sql.Ref;
0035: import java.sql.ResultSet;
0036: import java.sql.SQLException;
0037: import java.sql.Statement;
0038: import java.sql.Time;
0039: import java.sql.Timestamp;
0040: import java.sql.Types;
0041: import java.util.ArrayList;
0042: import java.util.HashSet;
0043: import java.util.Iterator;
0044: import java.util.List;
0045: import java.util.Set;
0046:
0047: import net.sourceforge.squirrel_sql.client.session.ISession;
0048: import net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfo;
0049: import net.sourceforge.squirrel_sql.fw.dialects.DialectFactory;
0050: import net.sourceforge.squirrel_sql.fw.dialects.HibernateDialect;
0051: import net.sourceforge.squirrel_sql.fw.dialects.UserCancelledOperationException;
0052: import net.sourceforge.squirrel_sql.fw.sql.ForeignKeyInfo;
0053: import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
0054: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
0055: import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData;
0056: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
0057: import net.sourceforge.squirrel_sql.fw.sql.JDBCTypeMapper;
0058: import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
0059: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
0060: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
0061: import net.sourceforge.squirrel_sql.fw.util.StringManager;
0062: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
0063: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
0064: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
0065: import net.sourceforge.squirrel_sql.plugins.dbcopy.ColTypeMapper;
0066: import net.sourceforge.squirrel_sql.plugins.dbcopy.I18NBaseObject;
0067: import net.sourceforge.squirrel_sql.plugins.dbcopy.SessionInfoProvider;
0068: import net.sourceforge.squirrel_sql.plugins.dbcopy.prefs.DBCopyPreferenceBean;
0069: import net.sourceforge.squirrel_sql.plugins.dbcopy.prefs.PreferencesManager;
0070:
0071: import org.hibernate.MappingException;
0072:
0073: /**
0074: * A utility class for interacting with the database.
0075: */
0076: public class DBUtil extends I18NBaseObject {
0077:
0078: /** Logger for this class. */
0079: private final static ILogger log = LoggerController
0080: .createLogger(DBUtil.class);
0081:
0082: /** Plugin settings. The configuration panel uses this */
0083: private static DBCopyPreferenceBean _prefs = PreferencesManager
0084: .getPreferences();
0085:
0086: /** Internationalized strings for this class */
0087: private static final StringManager s_stringMgr = StringManagerFactory
0088: .getStringManager(DBUtil.class);
0089:
0090: /** The name of the table to create when testing column names in dest db */
0091: private static final String TEST_TABLE_NAME = "dbcopytest";
0092:
0093: /** The last statement executed that we'll show to the user if error */
0094: private static String lastStatement = null;
0095:
0096: private static String lastStatementValues = null;
0097:
0098: public static void setPreferences(DBCopyPreferenceBean bean) {
0099: _prefs = bean;
0100: }
0101:
0102: /**
0103: * Returns a string that looks like:
0104: *
0105: * (PK_COL1, PK_COL2, PK_COL3, ...)
0106: *
0107: * or null if there is no primary key for the specified table.
0108: *
0109: * @param sourceConn
0110: * @param ti
0111: * @return
0112: * @throws SQLException
0113: */
0114: public static String getPKColumnString(ISQLConnection sourceConn,
0115: ITableInfo ti) throws SQLException {
0116: List<String> pkColumns = getPKColumnList(sourceConn, ti);
0117: if (pkColumns == null || pkColumns.size() == 0) {
0118: return null;
0119: }
0120: StringBuilder sb = new StringBuilder("(");
0121: Iterator<String> i = pkColumns.iterator();
0122: while (i.hasNext()) {
0123: String columnName = i.next();
0124: sb.append(columnName);
0125: if (i.hasNext()) {
0126: sb.append(", ");
0127: }
0128: }
0129: sb.append(")");
0130: return sb.toString();
0131: }
0132:
0133: /**
0134: * Returns a list of primary keys or null if there are no primary keys for
0135: * the specified table.
0136: *
0137: * @param sourceConn
0138: * @param ti
0139: * @return
0140: * @throws SQLException
0141: */
0142: private static List<String> getPKColumnList(
0143: ISQLConnection sourceConn, ITableInfo ti)
0144: throws SQLException {
0145: ArrayList<String> pkColumns = new ArrayList<String>();
0146: DatabaseMetaData md = sourceConn.getConnection().getMetaData();
0147: ResultSet rs = null;
0148: if (md.supportsCatalogsInTableDefinitions()) {
0149: rs = md.getPrimaryKeys(ti.getCatalogName(), null, ti
0150: .getSimpleName());
0151: } else if (md.supportsSchemasInTableDefinitions()) {
0152: rs = md.getPrimaryKeys(null, ti.getSchemaName(), ti
0153: .getSimpleName());
0154: } else {
0155: rs = md.getPrimaryKeys(null, null, ti.getSimpleName());
0156: }
0157: while (rs.next()) {
0158: String keyColumn = rs.getString(4);
0159: if (keyColumn != null) {
0160: pkColumns.add(keyColumn);
0161: }
0162: }
0163: if (pkColumns.size() == 0) {
0164: return null;
0165: }
0166: return pkColumns;
0167: }
0168:
0169: /**
0170: * Returns a List of SQL statements that add foreign key(s) to the table
0171: * described in the specified ITableInfo.
0172: *
0173: * @param prov used to see if the destination session connection FKs in the
0174: * source session exist already
0175: * @param ti the table to get FK information on
0176: * @return Set a set of SQL statements that can be used to create foreign
0177: * key constraints.
0178: * @throws SQLException
0179: */
0180: public static Set<String> getForeignKeySQL(
0181: SessionInfoProvider prov, ITableInfo ti,
0182: ArrayList<ITableInfo> selectedTableInfos)
0183: throws SQLException, UserCancelledOperationException {
0184: HashSet<String> result = new HashSet<String>();
0185: ForeignKeyInfo[] keys = ti.getImportedKeys();
0186: if (keys == null) {
0187: return result;
0188: }
0189: for (ForeignKeyInfo fkInfo : keys) {
0190: String pkTableName = fkInfo.getPrimaryKeyTableName();
0191: String pkTableCol = fkInfo.getPrimaryKeyColumnName();
0192: String fkTableName = fkInfo.getForeignKeyTableName();
0193: String fkTableCol = fkInfo.getForeignKeyColumnName();
0194: // TODO: Is giving a FK constraint a name universally supported
0195: // and done the same way on every database?
0196: String fkName = fkInfo.getForeignKeyName();
0197:
0198: //alter table ti.getSimpleName()
0199: //add foreign key (fkTableCol)
0200: //references pkTableName(pkTableCol);
0201: if (!containsTable(selectedTableInfos, pkTableName)) {
0202: // TODO: Maybe someday we could inform the user that the imported
0203: // key can't be created because the list of tables they've
0204: // selected, doesn't include the table that this foreign key
0205: // depends upon. For now, just log a warning and skip it.
0206: if (log.isDebugEnabled()) {
0207: //i18n[DBUtil.error.missingtable=getForeignKeySQL: table
0208: //'{0}' has a column '{1}' that references table '{2}'
0209: //column '{3}'. However, that table is not being copied.
0210: //Skipping this foreign key.]
0211: String msg = s_stringMgr.getString(
0212: "DBUtil.error.missingtable", new String[] {
0213: fkTableName, fkTableCol,
0214: pkTableName, pkTableCol });
0215:
0216: log.debug(msg);
0217: }
0218: continue;
0219: }
0220:
0221: ISession destSession = prov.getCopyDestSession();
0222: String destSchema = prov.getDestSelectedDatabaseObject()
0223: .getSimpleName();
0224: String destCatalog = prov.getDestSelectedDatabaseObject()
0225: .getCatalogName();
0226: if (tableHasForeignKey(destCatalog, destSchema, ti
0227: .getSimpleName(), fkInfo, prov)) {
0228: if (log.isInfoEnabled()) {
0229: log.info("Skipping FK (" + fkName + ") - table "
0230: + ti.getSimpleName()
0231: + " seems to already have it defined.");
0232: }
0233: continue;
0234: }
0235:
0236: String fkTable = getQualifiedObjectName(destSession,
0237: destCatalog, destSchema, ti.getSimpleName(),
0238: DialectFactory.DEST_TYPE);
0239: String pkTable = getQualifiedObjectName(destSession,
0240: destCatalog, destSchema, pkTableName,
0241: DialectFactory.DEST_TYPE);
0242: StringBuilder tmp = new StringBuilder();
0243: tmp.append("ALTER TABLE ");
0244: tmp.append(fkTable);
0245: tmp.append(" ADD FOREIGN KEY (");
0246: tmp.append(fkTableCol);
0247: tmp.append(") REFERENCES ");
0248: tmp.append(pkTable);
0249: tmp.append("(");
0250: tmp.append(pkTableCol);
0251: tmp.append(")");
0252: result.add(tmp.toString());
0253: }
0254: return result;
0255: }
0256:
0257: public static boolean tableHasForeignKey(String destCatalog,
0258: String destSchema, String destTableName,
0259: ForeignKeyInfo fkInfo, SessionInfoProvider prov) {
0260: boolean result = false;
0261: try {
0262: SQLDatabaseMetaData md = prov.getCopyDestSession()
0263: .getSQLConnection().getSQLMetaData();
0264:
0265: ITableInfo[] tables = md.getTables(destCatalog, destSchema,
0266: destTableName, new String[] { "TABLE" }, null);
0267: if (tables != null && tables.length == 1) {
0268: ForeignKeyInfo[] fks = SQLUtilities.getImportedKeys(
0269: tables[0], md);
0270: for (ForeignKeyInfo existingKey : fks) {
0271: if (areEqual(existingKey, fkInfo)) {
0272: result = true;
0273: break;
0274: }
0275: }
0276: } else {
0277: log
0278: .error("Couldn't find an exact match for destination table "
0279: + destTableName
0280: + " in schema "
0281: + destSchema
0282: + " and catalog "
0283: + destCatalog
0284: + ". Skipping FK constraint");
0285: }
0286: } catch (SQLException e) {
0287: log
0288: .error("Unexpected exception while attempting to determine if "
0289: + "a table ("
0290: + destTableName
0291: + ") has a particular foreign " + "key");
0292: }
0293: return result;
0294: }
0295:
0296: private static boolean areEqual(ForeignKeyInfo fk1,
0297: ForeignKeyInfo fk2) {
0298: String fk1FKColumn = fk1.getForeignKeyColumnName();
0299: String fk2FKColumn = fk2.getForeignKeyColumnName();
0300: String fk1PKColumn = fk1.getPrimaryKeyColumnName();
0301: String fk2PKColumn = fk2.getPrimaryKeyColumnName();
0302: String fk1FKTable = fk1.getForeignKeyTableName();
0303: String fk2FKTable = fk2.getForeignKeyTableName();
0304: String fk1PKTable = fk1.getPrimaryKeyTableName();
0305: String fk2PKTable = fk2.getPrimaryKeyTableName();
0306:
0307: if (!fk1PKColumn.equals(fk2PKColumn)) {
0308: return false;
0309: }
0310: if (!fk1FKColumn.equals(fk2FKColumn)) {
0311: return false;
0312: }
0313: if (!fk1PKTable.equals(fk2PKTable)) {
0314: return false;
0315: }
0316: if (!fk1FKTable.equals(fk2FKTable)) {
0317: return false;
0318: }
0319: return true;
0320: }
0321:
0322: private static boolean containsTable(List<ITableInfo> tableInfos,
0323: String table) {
0324: boolean result = false;
0325: for (ITableInfo ti : tableInfos) {
0326: if (table.equalsIgnoreCase(ti.getSimpleName())) {
0327: result = true;
0328: break;
0329: }
0330: }
0331: return result;
0332: }
0333:
0334: /**
0335: * Executes the given SQL using the specified SQLConnection.
0336: *
0337: * @param con the SQLConnection to execute the update on.
0338: * @param SQL the statement to execute.
0339: * @return either the row count for INSERT, UPDATE or DELETE statements,
0340: * or 0 for SQL statements that return nothing
0341: * @throws SQLException if a database access error occurs or the given SQL
0342: * statement produces a ResultSet object
0343: */
0344: public static int executeUpdate(ISQLConnection con, String SQL,
0345: boolean writeSQL) throws SQLException {
0346: Statement stmt = null;
0347: int result = 0;
0348: try {
0349: stmt = con.createStatement();
0350: if (writeSQL) {
0351: ScriptWriter.write(SQL);
0352: }
0353: if (log.isDebugEnabled()) {
0354: // i18n[DBUtil.info.executeupdate=executeupdate: Running SQL:\n '{0}']
0355: String msg = s_stringMgr.getString(
0356: "DBUtil.info.executeupdate", SQL);
0357: log.debug(msg);
0358: }
0359: lastStatement = SQL;
0360: result = stmt.executeUpdate(SQL);
0361: } finally {
0362: SQLUtilities.closeStatement(stmt);
0363: }
0364: return result;
0365: }
0366:
0367: /**
0368: * Executes the specified sql statement on the specified connection and
0369: * returns the ResultSet.
0370: *
0371: * @param con
0372: * @param sql
0373: * @param mysqlBigResultFix if true, provides a work-around which is useful
0374: * in the case that the connection is to a MySQL database. If the
0375: * number of rows is large this will prevent the driver from reading
0376: * them all into client memory. MySQL's normal practice is to do
0377: * such a thing for performance reasons.
0378: * @return
0379: * @throws Exception
0380: */
0381: public static ResultSet executeQuery(ISession session, String sql)
0382: throws SQLException {
0383: ISQLConnection sqlcon = session.getSQLConnection();
0384: if (sqlcon == null || sql == null) {
0385: return null;
0386: }
0387: Statement stmt = null;
0388: ResultSet rs = null;
0389:
0390: Connection con = sqlcon.getConnection();
0391: try {
0392: if (DialectFactory.isMySQL(session.getMetaData())) {
0393: stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
0394: ResultSet.CONCUR_READ_ONLY);
0395:
0396: stmt.setFetchSize(Integer.MIN_VALUE);
0397: } else if (DialectFactory.isTimesTen(session.getMetaData())) {
0398: stmt = con.createStatement();
0399: int fetchSize = _prefs.getSelectFetchSize();
0400: // TimesTen allows a maximum fetch size of 128.
0401: if (fetchSize > 128) {
0402: log
0403: .info("executeQuery: TimesTen allows a maximum fetch size of "
0404: + "128. Altering preferred fetch size from "
0405: + fetchSize + " to 128.");
0406: fetchSize = 128;
0407: }
0408: stmt.setFetchSize(fetchSize);
0409: } else {
0410: stmt = con.createStatement();
0411: // Allow the user to set "0" for the fetch size to indicate that
0412: // this should not be called. JDBC-ODBC bridge driver fails to
0413: // execute SQL once you have set the fetch size to *any* value.
0414: if (_prefs.getSelectFetchSize() > 0) {
0415: stmt.setFetchSize(_prefs.getSelectFetchSize());
0416: }
0417: }
0418: } catch (SQLException e) {
0419: // Only close the statement if SQLException - otherwise it has to
0420: // remain open until the ResultSet is read through by the caller.
0421: SQLUtilities.closeResultSet(rs);
0422: SQLUtilities.closeStatement(stmt);
0423: throw e;
0424: }
0425: if (log.isDebugEnabled()) {
0426: //i18n[DBUtil.info.executequery=executeQuery: Running SQL:\n '{0}']
0427: String msg = s_stringMgr.getString(
0428: "DBUtil.info.executequery", sql);
0429: log.debug(msg);
0430: }
0431: try {
0432: lastStatement = sql;
0433: rs = stmt.executeQuery(sql);
0434: } catch (SQLException e) {
0435: // Only close the statement if SQLException - otherwise it has to
0436: // remain open until the ResultSet is read through by the caller.
0437: if (stmt != null) {
0438: try {
0439: stmt.close();
0440: } catch (SQLException ex) { /* Do Nothing */
0441: }
0442: }
0443: throw e;
0444: }
0445:
0446: return rs;
0447: }
0448:
0449: /**
0450: * Returns a count of the records in the specified table.
0451: *
0452: * @param con the SQLConnection to use to execute the count query.
0453: * @param tableName the name of the table. This name should already be
0454: * qualified by the schema.
0455: *
0456: * @return -1 if the table does not exist, otherwise the record count is
0457: * returned.
0458: */
0459: private static int getTableCount(ISession session, String tableName) {
0460: int result = -1;
0461: ResultSet rs = null;
0462: try {
0463: String sql = "select count(*) from " + tableName;
0464: rs = executeQuery(session, sql);
0465: if (rs.next()) {
0466: result = rs.getInt(1);
0467: }
0468: } catch (Exception e) {
0469: /* Do Nothing - this can happen when the table doesn't exist */
0470: } finally {
0471: SQLUtilities.closeResultSet(rs, true);
0472: }
0473: return result;
0474: }
0475:
0476: /**
0477: * Returns a count of the records in the specified table.
0478: *
0479: * @param con the SQLConnection to use to execute the count query.
0480: * @param tableName the name of the table
0481: *
0482: * @return -1 if the table does not exist, otherwise the record count is
0483: * returned.
0484: */
0485: public static int getTableCount(ISession session, String catalog,
0486: String schema, String tableName, int sessionType)
0487: throws UserCancelledOperationException {
0488: String table = getQualifiedObjectName(session, catalog, schema,
0489: tableName, sessionType);
0490: return getTableCount(session, table);
0491: }
0492:
0493: public static ITableInfo getTableInfo(ISession session,
0494: String schema, String tableName) throws SQLException,
0495: MappingException, UserCancelledOperationException {
0496: ISQLConnection con = session.getSQLConnection();
0497: // Currently, as of milestone 3, Axion doesn't support "schemas" like
0498: // other databases. So, set the schema to emtpy string if we detect
0499: // an Axion session.
0500: if (con.getSQLMetaData().getDriverName().toLowerCase()
0501: .startsWith("axion")) {
0502: schema = "";
0503: }
0504: String catalog = null;
0505: // MySQL uses catalogs and not schemas
0506: if (DialectFactory.isMySQL(session.getMetaData())) {
0507: catalog = schema;
0508: schema = null;
0509: }
0510: // trim the table name in case of HADB
0511: tableName = tableName.trim();
0512: ITableInfo[] tis = getTables(session, catalog, schema,
0513: tableName);
0514: if (tis == null || tis.length == 0) {
0515: if (Character.isUpperCase(tableName.charAt(0))) {
0516: tableName = tableName.toLowerCase();
0517: } else {
0518: tableName = tableName.toUpperCase();
0519: }
0520: tis = getTables(session, null, schema, tableName);
0521: if (tis.length == 0) {
0522: if (Character.isUpperCase(tableName.charAt(0))) {
0523: tableName = tableName.toLowerCase();
0524: } else {
0525: tableName = tableName.toUpperCase();
0526: }
0527: tis = getTables(session, null, schema, tableName);
0528: }
0529: }
0530: if (tis.length == 0) {
0531: //i18n[DBUtil.error.tablenotfound=Couldn't locate table '{0}' in
0532: //schema '(1)']
0533: String msg = s_stringMgr.getString(
0534: "DBUtil.error.tablenotfound", new String[] {
0535: tableName, schema });
0536: throw new MappingException(msg);
0537: }
0538: if (tis.length > 1) {
0539: if (log.isDebugEnabled()) {
0540: log.debug("DBUtil.getTableInfo: found " + tis.length
0541: + " that matched " + "catalog=" + catalog
0542: + " schema=" + schema + " tableName="
0543: + tableName);
0544: }
0545: }
0546: return tis[0];
0547: }
0548:
0549: public static ITableInfo[] getTables(ISession session,
0550: String catalog, String schema, String tableName) {
0551: ITableInfo[] result = new ITableInfo[0];
0552:
0553: try {
0554: SchemaInfo schemaInfo = session.getSchemaInfo();
0555: result = schemaInfo.getITableInfos(catalog, schema,
0556: tableName);
0557: } catch (Exception e) {
0558: log
0559: .error("Encountered unexpected exception when attempting to "
0560: + "call schemaInfo.getTables with catalog = "
0561: + catalog
0562: + " schema = "
0563: + schema
0564: + " tableName = " + tableName);
0565:
0566: }
0567:
0568: if (result == null || result.length == 0) {
0569: // Fallback to the old method, going directly to the database
0570: // instead
0571: // of using SchemaInfo, since SchemaInfo didn't have it.
0572: SQLDatabaseMetaData d = session.getSQLConnection()
0573: .getSQLMetaData();
0574: result = getTables(d, catalog, schema, tableName);
0575: }
0576:
0577: return result;
0578: }
0579:
0580: private static ITableInfo[] getTables(SQLDatabaseMetaData data,
0581: String catalog, String schema, String tableName) {
0582:
0583: ITableInfo[] result = new ITableInfo[0];
0584:
0585: try {
0586: result = data.getTables(catalog, schema, tableName, null,
0587: null);
0588: } catch (Exception e) {
0589: log
0590: .error("Encountered unexpected exception when attempting to "
0591: + "call SQLDatabaseMetaData.getTables with catalog = "
0592: + catalog
0593: + " schema = "
0594: + schema
0595: + " tableName = " + tableName);
0596:
0597: }
0598: return result;
0599: }
0600:
0601: /**
0602: * Decides whether or not the specified column types
0603: * (java.sql.Type constants) use the same java type to read from the source
0604: * database as the one used to write to the destination database. For
0605: * example, Types.DECIMAL and Types.NUMERIC both use BigDecimal java type
0606: * to store the value in between reading and writing it. Therefore, even
0607: * though these types are not equal, they are equivalent. This method has
0608: * not yet been fully implemented with equivalences from the bindVariable
0609: * method.
0610: *
0611: * @param sourceType the column type as identified by the source database
0612: * jdbc driver.
0613: * @param destType the column type as identified by the destination database
0614: * jdbc driver.
0615: * @return true if equivalent, false if not.
0616: */
0617: public static boolean typesAreEquivalent(int sourceType,
0618: int destType) {
0619: boolean result = false;
0620: if (sourceType == destType) {
0621: result = true;
0622: }
0623: if (sourceType == Types.DECIMAL && destType == Types.NUMERIC) {
0624: result = true;
0625: }
0626: if (sourceType == Types.NUMERIC && destType == Types.DECIMAL) {
0627: result = true;
0628: }
0629: if (sourceType == Types.BOOLEAN && destType == Types.BIT) {
0630: result = true;
0631: }
0632: if (sourceType == Types.BIT && destType == Types.BOOLEAN) {
0633: result = true;
0634: }
0635: return result;
0636: }
0637:
0638: /**
0639: * Check to see if the last column retrieved at the specified index was null.
0640: * If so, bind the specified PreparedStatement column at the specified index
0641: * to null and return true.
0642: *
0643: * @param rs the ResultSet that was used to read the last row.
0644: * @param ps the PreparedStatement that will be used to insetrt a row into
0645: * the destination database.
0646: * @param index the column in the row that was last read, whose value we mean
0647: * to inspect.
0648: * @param type the type of the column.
0649: * @return true if last column was null; false otherwise.
0650: * @throws SQLException
0651: */
0652: private static boolean handleNull(ResultSet rs,
0653: PreparedStatement ps, int index, int type)
0654: throws SQLException {
0655: boolean result = false;
0656: if (rs.wasNull()) {
0657: ps.setNull(index, type);
0658: result = true;
0659: }
0660: return result;
0661: }
0662:
0663: /**
0664: * Takes the specified colInfo, gets the data type to see if it is
0665: * 1111(OTHER). If so then get the type name and try to match a jdbc type
0666: * with the same name to get it's type code.
0667: *
0668: * @param colInfo
0669: * @return
0670: * @throws MappingException
0671: */
0672: public static int replaceOtherDataType(TableColumnInfo colInfo)
0673: throws MappingException {
0674: int colJdbcType = colInfo.getDataType();
0675: if (colJdbcType == java.sql.Types.OTHER) {
0676: String typeName = colInfo.getTypeName().toUpperCase();
0677: int parenIndex = typeName.indexOf("(");
0678: if (parenIndex != -1) {
0679: typeName = typeName.substring(0, parenIndex);
0680: }
0681: colJdbcType = JDBCTypeMapper.getJdbcType(typeName);
0682: if (colJdbcType == Types.NULL) {
0683: throw new MappingException(
0684: "Encoutered jdbc type OTHER (1111) and couldn't map "
0685: + "the database-specific type name ("
0686: + typeName + ") to a jdbc type");
0687: }
0688: }
0689: return colJdbcType;
0690: }
0691:
0692: /**
0693: * Reads the value from the specified ResultSet at column index index, and
0694: * based on the type, calls the appropriate setXXX method on ps with the
0695: * value obtained.
0696: *
0697: * @param ps
0698: * @param sourceColType
0699: * @param destColType
0700: * @param index
0701: * @param rs
0702: * @return a string representation of the value that was bound.
0703: * @throws SQLException
0704: */
0705: public static String bindVariable(PreparedStatement ps,
0706: int sourceColType, int destColType, int index, ResultSet rs)
0707: throws SQLException {
0708: String result = "null";
0709: switch (sourceColType) {
0710: case Types.ARRAY:
0711: Array arrayVal = rs.getArray(index);
0712: result = getValue(arrayVal);
0713: ps.setArray(index, arrayVal);
0714: break;
0715: case Types.BIGINT:
0716: long bigintVal = rs.getLong(index);
0717: if (!handleNull(rs, ps, index, destColType)) {
0718: result = Long.toString(bigintVal);
0719: ps.setLong(index, bigintVal);
0720: }
0721: break;
0722: case Types.BINARY:
0723: result = bindBlobVar(ps, index, rs, destColType);
0724: break;
0725: case Types.BIT:
0726: // JDBC spec says that BIT refers to a boolean column - i.e. a
0727: // single binary digit with value either "0" or "1". Also
0728: // the same spec encourages use of getBoolean/setBoolean.
0729: // However, the SQL-92 standard clearly states that the BIT type
0730: // is a bit string with length >= 0. So for SQL-92 compliant
0731: // databases (like PostgreSQL) the JDBC spec's support for BIT
0732: // is at best broken and unusable. Still, we do what the JDBC
0733: // spec suggests as that is all that we can do.
0734:
0735: // TODO: just noticed that MySQL 5.0 supports a multi-bit BIT
0736: // column by using the getObject/setObject methods with a byte[].
0737: // So it would be valuable at some point to make this code a bit
0738: // more dbms-specific
0739: boolean bitValue = rs.getBoolean(index);
0740: if (!handleNull(rs, ps, index, destColType)) {
0741: result = Boolean.toString(bitValue);
0742: ps.setBoolean(index, bitValue);
0743: }
0744: break;
0745: case Types.BLOB:
0746: result = bindBlobVar(ps, index, rs, destColType);
0747: break;
0748: case Types.BOOLEAN:
0749: boolean booleanValue = rs.getBoolean(index);
0750: if (!handleNull(rs, ps, index, destColType)) {
0751: result = Boolean.toString(booleanValue);
0752: // HACK: some dbs (like Frontbase) don't support boolean
0753: // types. I've tried tinyint, bit and boolean as the column
0754: // type, and setBoolean fails for all three. It's a mystery
0755: // at this point what column the getBoolean/setBoolean methods
0756: // actually work on iin FrontBase.
0757: switch (destColType) {
0758: case Types.TINYINT:
0759: case Types.SMALLINT:
0760: case Types.BIGINT:
0761: case Types.INTEGER:
0762: ps.setInt(index, booleanValue ? 1 : 0);
0763: break;
0764: case Types.FLOAT:
0765: ps.setFloat(index, booleanValue ? 1 : 0);
0766: break;
0767: case Types.DOUBLE:
0768: ps.setDouble(index, booleanValue ? 1 : 0);
0769: break;
0770: case Types.VARCHAR:
0771: case Types.CHAR:
0772: ps.setString(index, booleanValue ? "1" : "0");
0773: break;
0774: default:
0775: ps.setBoolean(index, booleanValue);
0776: break;
0777: }
0778: }
0779: break;
0780: case Types.CHAR:
0781: String charValue = rs.getString(index);
0782: if (!handleNull(rs, ps, index, destColType)) {
0783: result = charValue;
0784: ps.setString(index, charValue);
0785: }
0786: break;
0787: case Types.CLOB:
0788: bindClobVar(ps, index, rs, destColType);
0789: break;
0790: case Types.DATALINK:
0791: // TODO: is this right???
0792: Object datalinkValue = rs.getObject(index);
0793: if (!handleNull(rs, ps, index, destColType)) {
0794: result = getValue(datalinkValue);
0795: ps.setObject(index, datalinkValue);
0796: }
0797: break;
0798: case Types.DATE:
0799: Date dateValue = rs.getDate(index);
0800: if (!handleNull(rs, ps, index, destColType)) {
0801: // TODO: use the destination database type to derive a
0802: // format that is acceptable.
0803: result = getValue(dateValue);
0804: ps.setDate(index, dateValue);
0805: }
0806: break;
0807: case Types.DECIMAL:
0808: BigDecimal decimalValue = rs.getBigDecimal(index);
0809: if (!handleNull(rs, ps, index, destColType)) {
0810: result = getValue(decimalValue);
0811: ps.setBigDecimal(index, decimalValue);
0812: }
0813: break;
0814: case Types.DISTINCT:
0815: // TODO: is this right???
0816: Object distinctValue = rs.getObject(index);
0817: if (!handleNull(rs, ps, index, destColType)) {
0818: result = getValue(distinctValue);
0819: ps.setObject(index, distinctValue);
0820: }
0821: break;
0822: case Types.DOUBLE:
0823: double doubleValue = rs.getDouble(index);
0824: if (!handleNull(rs, ps, index, destColType)) {
0825: result = Double.toString(doubleValue);
0826: ps.setDouble(index, doubleValue);
0827: }
0828: break;
0829: case Types.FLOAT:
0830: // SQL FLOAT requires support for 15 digits of mantissa.
0831: double floatValue = rs.getDouble(index);
0832: if (!handleNull(rs, ps, index, destColType)) {
0833: result = Double.toString(floatValue);
0834: ps.setDouble(index, floatValue);
0835: }
0836: break;
0837: case Types.INTEGER:
0838: int integerValue = rs.getInt(index);
0839: if (!handleNull(rs, ps, index, destColType)) {
0840: result = Integer.toString(integerValue);
0841: ps.setInt(index, integerValue);
0842: }
0843: break;
0844: case Types.JAVA_OBJECT:
0845: Object objectValue = rs.getObject(index);
0846: if (!handleNull(rs, ps, index, destColType)) {
0847: result = getValue(objectValue);
0848: ps.setObject(index, objectValue);
0849: }
0850: break;
0851: case Types.LONGVARBINARY:
0852: result = bindBlobVar(ps, index, rs, destColType);
0853: break;
0854: case Types.LONGVARCHAR:
0855: String longvarcharValue = rs.getString(index);
0856: if (!handleNull(rs, ps, index, destColType)) {
0857: result = longvarcharValue;
0858: ps.setString(index, longvarcharValue);
0859: }
0860: break;
0861: case Types.NULL:
0862: // TODO: is this right???
0863: ps.setNull(index, Types.NULL);
0864: break;
0865: case Types.NUMERIC:
0866: BigDecimal numericValue = rs.getBigDecimal(index);
0867: if (!handleNull(rs, ps, index, destColType)) {
0868: result = getValue(numericValue);
0869: ps.setBigDecimal(index, numericValue);
0870: }
0871: break;
0872: case Types.OTHER:
0873: // TODO: figure out a more reliable way to handle OTHER type
0874: // which indicates a database-specific type.
0875: String testValue = rs.getString(index);
0876: if (!handleNull(rs, ps, index, destColType)) {
0877: try {
0878: Double.parseDouble(testValue);
0879: double numberValue = rs.getDouble(index);
0880: ps.setDouble(index, numberValue);
0881: } catch (SQLException e) {
0882: byte[] otherValue = rs.getBytes(index);
0883: result = getValue(otherValue);
0884: ps.setBytes(index, otherValue);
0885: }
0886: }
0887: break;
0888: case Types.REAL:
0889: float realValue = rs.getFloat(index);
0890: if (!handleNull(rs, ps, index, destColType)) {
0891: result = Float.toString(realValue);
0892: ps.setFloat(index, realValue);
0893: }
0894: break;
0895: case Types.REF:
0896: Ref refValue = rs.getRef(index);
0897: if (!handleNull(rs, ps, index, destColType)) {
0898: result = getValue(refValue);
0899: ps.setRef(index, refValue);
0900: }
0901: break;
0902: case Types.SMALLINT:
0903: short smallintValue = rs.getShort(index);
0904: if (!handleNull(rs, ps, index, destColType)) {
0905: result = Short.toString(smallintValue);
0906: ps.setShort(index, smallintValue);
0907: }
0908: break;
0909: case Types.STRUCT:
0910: Object structValue = rs.getObject(index);
0911: if (!handleNull(rs, ps, index, destColType)) {
0912: result = getValue(structValue);
0913: ps.setObject(index, structValue);
0914: }
0915: break;
0916: case Types.TIME:
0917: Time timeValue = rs.getTime(index);
0918: // TODO: use the destination database type to derive a format
0919: // that is acceptable.
0920: if (!handleNull(rs, ps, index, destColType)) {
0921: result = getValue(timeValue);
0922: ps.setTime(index, timeValue);
0923: }
0924: break;
0925: case Types.TIMESTAMP:
0926: Timestamp timestampValue = rs.getTimestamp(index);
0927: // TODO: use the destination database type to derive a format
0928: // that is acceptable.
0929: if (!handleNull(rs, ps, index, destColType)) {
0930: result = getValue(timestampValue);
0931: ps.setTimestamp(index, timestampValue);
0932: }
0933: break;
0934: case Types.TINYINT:
0935: byte tinyintValue = rs.getByte(index);
0936: if (!handleNull(rs, ps, index, destColType)) {
0937: result = Byte.toString(tinyintValue);
0938: ps.setByte(index, tinyintValue);
0939: }
0940: break;
0941: case Types.VARBINARY:
0942: result = bindBlobVar(ps, index, rs, destColType);
0943: break;
0944: case Types.VARCHAR:
0945: String varcharValue = rs.getString(index);
0946: if (!handleNull(rs, ps, index, destColType)) {
0947: result = varcharValue;
0948: ps.setString(index, varcharValue);
0949: }
0950: break;
0951: default:
0952: //i18n[DBUtil.error.unknowntype=Unknown Java SQL column type: '{0}']
0953: String msg = s_stringMgr.getString(
0954: "DBUtil.error.unknowntype", Integer
0955: .valueOf(sourceColType));
0956: log.error(msg);
0957: // We still have to bind a value, or else the PS will throw
0958: // an exception.
0959: String value = rs.getString(index);
0960: if (!handleNull(rs, ps, index, destColType)) {
0961: result = value;
0962: ps.setString(index, value);
0963: }
0964: break;
0965: }
0966: return result;
0967: }
0968:
0969: private static String bindClobVar(PreparedStatement ps, int index,
0970: ResultSet rs, int type) throws SQLException {
0971: String result = "null";
0972: if (_prefs.isUseFileCaching()) {
0973: try {
0974: bindClobVarInFile(ps, index, rs, type);
0975: } catch (Exception e) {
0976: //i18n[DBUtil.error.bindclobfailure=bindBlobVar: failed to
0977: //bind blob using filesystem - attempting to bind blob using
0978: //memory]
0979: String msg = s_stringMgr
0980: .getString("DBUtil.error.bindclobfailure");
0981: log.error(msg, e);
0982: // if we failed to bind the blob in a file, try memory.
0983: result = bindClobVarInMemory(ps, index, rs, type);
0984: }
0985: } else {
0986: result = bindClobVarInMemory(ps, index, rs, type);
0987: }
0988: return result;
0989: }
0990:
0991: private static String bindBlobVar(PreparedStatement ps, int index,
0992: ResultSet rs, int type) throws SQLException {
0993: String result = "null";
0994: if (_prefs.isUseFileCaching()) {
0995: try {
0996: bindBlobVarInFile(ps, index, rs, type);
0997: } catch (Exception e) {
0998: //i18n[DBUtil.error.bindblobfailure=bindBlobVar: failed to
0999: //bind blob using filesystem - attempting to bind blob using
1000: //memory]
1001: String msg = s_stringMgr
1002: .getString("DBUtil.error.bindblobfailure");
1003: log.error(msg, e);
1004: // if we failed to bind the blob in a file, try memory.
1005: result = bindBlobVarInMemory(ps, index, rs, type);
1006: }
1007: } else {
1008: result = bindBlobVarInMemory(ps, index, rs, type);
1009: }
1010: return result;
1011: }
1012:
1013: private static String bindClobVarInMemory(PreparedStatement ps,
1014: int index, ResultSet rs, int type) throws SQLException {
1015: String clobValue = rs.getString(index);
1016: if (rs.wasNull()) {
1017: ps.setNull(index, type);
1018: return "null";
1019: }
1020: String result = getValue(clobValue);
1021: if (log.isDebugEnabled() && clobValue != null) {
1022: // i18n[DBUtil.info.bindclobmem=bindClobVarInMemory: binding '{0}' bytes]
1023: String msg = s_stringMgr.getString(
1024: "DBUtil.info.bindclobmem", Integer
1025: .valueOf(clobValue.length()));
1026: log.debug(msg);
1027: }
1028: ps.setString(index, clobValue);
1029: return result;
1030: }
1031:
1032: private static String bindBlobVarInMemory(PreparedStatement ps,
1033: int index, ResultSet rs, int type) throws SQLException {
1034: byte[] blobValue = rs.getBytes(index);
1035: if (rs.wasNull()) {
1036: ps.setNull(index, type);
1037: return "null";
1038: }
1039: String result = getValue(blobValue);
1040: if (log.isDebugEnabled() && blobValue != null) {
1041: //i18n[DBUtil.info.bindblobmem=bindBlobVarInMemory: binding '{0}' bytes]
1042: String msg = s_stringMgr.getString(
1043: "DBUtil.info.bindblobmem", Integer
1044: .valueOf(blobValue.length));
1045: log.debug(msg);
1046: }
1047: ps.setBytes(index, blobValue);
1048: return result;
1049: }
1050:
1051: private static void bindClobVarInFile(PreparedStatement ps,
1052: int index, ResultSet rs, int type) throws IOException,
1053: SQLException {
1054: // get ascii stream from rs
1055: InputStream is = rs.getAsciiStream(index);
1056: if (rs.wasNull()) {
1057: ps.setNull(index, type);
1058: return;
1059: }
1060:
1061: // Open file output stream
1062: long millis = System.currentTimeMillis();
1063: File f = File.createTempFile("clob", "" + millis);
1064: f.deleteOnExit();
1065: FileOutputStream fos = new FileOutputStream(f);
1066: if (log.isDebugEnabled()) {
1067: //i18n[DBUtil.info.bindclobfile=bindClobVarInFile: Opening temp file '{0}']
1068: String msg = s_stringMgr.getString(
1069: "DBUtil.info.bindclobfile", f.getAbsolutePath());
1070: log.debug(msg);
1071: }
1072:
1073: // read rs input stream write to file output stream
1074: byte[] buf = new byte[_prefs.getFileCacheBufferSize()];
1075: int length = 0;
1076: int total = 0;
1077: while ((length = is.read(buf)) >= 0) {
1078: if (log.isDebugEnabled()) {
1079: //i18n[DBUtil.info.bindcloblength=bindClobVarInFile: writing '{0}' bytes.]
1080: String msg = s_stringMgr.getString(
1081: "DBUtil.info.bindcloblength", Integer
1082: .valueOf(length));
1083: log.debug(msg);
1084: }
1085: fos.write(buf, 0, length);
1086: total += length;
1087: }
1088: fos.close();
1089:
1090: // set the ps to read from the file we just created.
1091: FileInputStream fis = new FileInputStream(f);
1092: BufferedInputStream bis = new BufferedInputStream(fis);
1093: ps.setAsciiStream(index, bis, total);
1094: }
1095:
1096: private static void bindBlobVarInFile(PreparedStatement ps,
1097: int index, ResultSet rs, int type) throws IOException,
1098: SQLException {
1099: // get binary stream from rs
1100: InputStream is = rs.getBinaryStream(index);
1101: if (rs.wasNull()) {
1102: ps.setNull(index, type);
1103: return;
1104: }
1105: // Open file output stream
1106: long millis = System.currentTimeMillis();
1107: File f = File.createTempFile("blob", "" + millis);
1108: f.deleteOnExit();
1109: FileOutputStream fos = new FileOutputStream(f);
1110: if (log.isDebugEnabled()) {
1111: //i18n[DBUtil.info.bindblobfile=bindBlobVarInFile: Opening temp file '{0}']
1112: String msg = s_stringMgr.getString(
1113: "DBUtil.info.bindblobfile", f.getAbsolutePath());
1114: log.debug(msg);
1115: }
1116:
1117: // read rs input stream write to file output stream
1118: byte[] buf = new byte[_prefs.getFileCacheBufferSize()];
1119: int length = 0;
1120: int total = 0;
1121: while ((length = is.read(buf)) >= 0) {
1122: if (log.isDebugEnabled()) {
1123: //i18n[DBUtil.info.bindbloblength=bindBlobVarInFile: writing '{0}' bytes.]
1124: String msg = s_stringMgr.getString(
1125: "DBUtil.info.bindbloblength", Integer
1126: .valueOf(length));
1127: log.debug(msg);
1128: }
1129: fos.write(buf, 0, length);
1130: total += length;
1131: }
1132: fos.close();
1133:
1134: // set the ps to read from the file we just created.
1135: FileInputStream fis = new FileInputStream(f);
1136: BufferedInputStream bis = new BufferedInputStream(fis);
1137: ps.setBinaryStream(index, bis, total);
1138: }
1139:
1140: /**
1141: * Returns the string representation of the specified object, or "null" if
1142: * the specified object is null.
1143: *
1144: * @param o
1145: * @return
1146: */
1147: private static String getValue(Object o) {
1148: if (o != null) {
1149: return o.toString();
1150: }
1151: return "null";
1152: }
1153:
1154: /**
1155: *
1156: * @param con
1157: * @param synonym
1158: * @param columnName
1159: * @return
1160: * @throws SQLException
1161: */
1162: public static int getColumnType(ISQLConnection con, ITableInfo ti,
1163: String columnName) throws SQLException {
1164: int result = -1;
1165: if (ti != null) {
1166: TableColumnInfo[] tciArr = con.getSQLMetaData()
1167: .getColumnInfo(ti);
1168: for (int i = 0; i < tciArr.length; i++) {
1169: if (tciArr[i].getColumnName().equalsIgnoreCase(
1170: columnName)) {
1171: result = tciArr[i].getDataType();
1172: break;
1173: }
1174: }
1175: }
1176: return result;
1177: }
1178:
1179: public static int[] getColumnTypes(ISQLConnection con,
1180: ITableInfo ti, String[] colNames) throws SQLException {
1181: TableColumnInfo[] tciArr = con.getSQLMetaData().getColumnInfo(
1182: ti);
1183: int[] result = new int[tciArr.length];
1184: for (int i = 0; i < tciArr.length; i++) {
1185: boolean found = false;
1186: for (int j = 0; j < colNames.length && !found; j++) {
1187: String columnName = colNames[j];
1188: if (tciArr[i].getColumnName().equalsIgnoreCase(
1189: columnName)) {
1190: result[i] = tciArr[i].getDataType();
1191: found = true;
1192: }
1193: }
1194: }
1195: return result;
1196: }
1197:
1198: public static boolean tableHasPrimaryKey(ISQLConnection con,
1199: ITableInfo ti) throws SQLException {
1200: boolean result = false;
1201: ResultSet rs = null;
1202: try {
1203: DatabaseMetaData md = con.getConnection().getMetaData();
1204: String cat = ti.getCatalogName();
1205: String schema = ti.getSchemaName();
1206: String tableName = ti.getSimpleName();
1207: rs = md.getPrimaryKeys(cat, schema, tableName);
1208: if (rs.next()) {
1209: result = true;
1210: }
1211: } finally {
1212: SQLUtilities.closeResultSet(rs);
1213: }
1214: return result;
1215: }
1216:
1217: /**
1218: * Check the specified session to determine if the specified data is a
1219: * keyword.
1220: *
1221: * @param session
1222: * @param data
1223: * @return
1224: */
1225: public static boolean isKeyword(ISession session, String data) {
1226: return session.getSchemaInfo().isKeyword(data);
1227: }
1228:
1229: /**
1230: * Deletes existing data from the destination connection specified in the
1231: * specified table. This will use preferences to determine if truncate
1232: * command is preferred. If truncate is preferred and fails, then delete
1233: * will be attempted.
1234: *
1235: * @param con
1236: * @param tablename
1237: * @throws SQLException
1238: */
1239: public static void deleteDataInExistingTable(ISession session,
1240: String catalogName, String schemaName, String tableName)
1241: throws SQLException, UserCancelledOperationException {
1242: ISQLConnection con = session.getSQLConnection();
1243: boolean useTrunc = PreferencesManager.getPreferences()
1244: .isUseTruncate();
1245: String fullTableName = getQualifiedObjectName(session,
1246: catalogName, schemaName, tableName,
1247: DialectFactory.DEST_TYPE);
1248: String truncSQL = "TRUNCATE TABLE " + fullTableName;
1249: String deleteSQL = "DELETE FROM " + fullTableName;
1250: try {
1251: if (useTrunc) {
1252: DBUtil.executeUpdate(con, truncSQL, true);
1253: } else {
1254: DBUtil.executeUpdate(con, deleteSQL, true);
1255: }
1256: } catch (SQLException e) {
1257: // If truncate was attempted and not supported, then try delete.
1258: // If on the other hand delete was attempted, just throw the
1259: // SQLException that resulted from the delete.
1260: if (useTrunc) {
1261: DBUtil.executeUpdate(con, deleteSQL, true);
1262: } else {
1263: throw e;
1264: }
1265: }
1266: }
1267:
1268: /**
1269: * This will take into account any special needs that the destination
1270: * session has with regard to user preferences, and throw a MappingException
1271: * if any user preference isn't valid for the specified destination session.
1272: *
1273: * @param destSession
1274: */
1275: public static void sanityCheckPreferences(ISession destSession)
1276: throws MappingException {
1277:
1278: if (DialectFactory.isFirebird(destSession.getMetaData())) {
1279: if (!PreferencesManager.getPreferences()
1280: .isCommitAfterTableDefs()) {
1281: // TODO: maybe instead of throwing an exception, we could ask
1282: // the user if they would like us to adjust their preference for
1283: // them.
1284:
1285: //i18n[DBUtil.error.firebirdcommit=Firebird requires commit
1286: //table create before inserting records. Please adjust your
1287: //preferences.]
1288: String msg = s_stringMgr
1289: .getString("DBUtil.error.firebirdcommit");
1290: throw new MappingException(msg);
1291: }
1292: }
1293: }
1294:
1295: public static String getCreateTableSql(SessionInfoProvider prov,
1296: ITableInfo ti) throws SQLException, MappingException,
1297: UserCancelledOperationException {
1298:
1299: ISession sourceSession = prov.getCopySourceSession();
1300: String sourceSchema = prov.getSourceSelectedDatabaseObjects()[0]
1301: .getSchemaName();
1302: String sourceCatalog = prov.getSourceSelectedDatabaseObjects()[0]
1303: .getCatalogName();
1304: String sourceTableName = getQualifiedObjectName(sourceSession,
1305: sourceCatalog, sourceSchema, ti.getSimpleName(),
1306: DialectFactory.SOURCE_TYPE);
1307: ISession destSession = prov.getCopyDestSession();
1308: String destSchema = prov.getDestSelectedDatabaseObject()
1309: .getSimpleName();
1310: String destCatalog = prov.getDestSelectedDatabaseObject()
1311: .getCatalogName();
1312: String destinationTableName = getQualifiedObjectName(
1313: destSession, destCatalog, destSchema, ti
1314: .getSimpleName(), DialectFactory.DEST_TYPE);
1315: StringBuilder result = new StringBuilder("CREATE TABLE ");
1316: result.append(destinationTableName);
1317: result.append(" ( ");
1318: result.append("\n");
1319: TableColumnInfo colInfo = null;
1320: try {
1321: ISQLConnection sourceCon = prov.getCopySourceSession()
1322: .getSQLConnection();
1323: TableColumnInfo[] colInfoArr = sourceCon.getSQLMetaData()
1324: .getColumnInfo(ti);
1325: if (colInfoArr.length == 0) {
1326: //i18n[DBUtil.error.nocolumns=Table '{0}' in schema '{1}' has
1327: //no columns to copy]
1328: String msg = s_stringMgr.getString(
1329: "DBUtil.error.nocolumns",
1330: new String[] { ti.getSimpleName(),
1331: ti.getSchemaName() });
1332: throw new MappingException(msg);
1333: }
1334: for (int i = 0; i < colInfoArr.length; i++) {
1335: colInfo = colInfoArr[i];
1336: result.append("\t");
1337: String columnSql = DBUtil.getColumnSql(prov, colInfo,
1338: sourceTableName, destinationTableName);
1339: result.append(columnSql);
1340: if (i < colInfoArr.length - 1) {
1341: result.append(",\n");
1342: }
1343: }
1344:
1345: // If the user wants the primary key copied and the source session
1346: // isn't Axion (Axion throws SQLException for getPrimaryKeys())
1347:
1348: // TODO: Perhaps we can tell the user when they click "Copy Table"
1349: // if the source session is Axion and they want primary keys that
1350: // it's not possible.
1351: if (_prefs.isCopyPrimaryKeys()
1352: && !DialectFactory.isAxion(sourceSession
1353: .getMetaData())) {
1354: String pkString = DBUtil.getPKColumnString(sourceCon,
1355: ti);
1356: if (pkString != null) {
1357: result.append(",\n\tPRIMARY KEY ");
1358: result.append(pkString);
1359: }
1360: }
1361: result.append(")");
1362: } catch (MappingException e) {
1363: if (colInfo != null) {
1364: //i18n[DBUtil.error.maptype=Couldn't map type for table='{0}'
1365: // column='{1}']
1366: String msg = s_stringMgr.getString(
1367: "DBUtil.error.maptype", new String[] {
1368: destinationTableName,
1369: colInfo.getColumnName() });
1370: log.error(msg, e);
1371: }
1372: throw e;
1373: }
1374:
1375: return result.toString();
1376: }
1377:
1378: /**
1379: *
1380: * @param con
1381: * @param ti
1382: * @return
1383: * @throws SQLException
1384: */
1385: public static String getColumnList(TableColumnInfo[] colInfoArr)
1386: throws SQLException {
1387: StringBuilder result = new StringBuilder();
1388:
1389: for (int i = 0; i < colInfoArr.length; i++) {
1390: TableColumnInfo colInfo = colInfoArr[i];
1391: String columnName = colInfo.getColumnName();
1392: result.append(columnName);
1393: if (i < colInfoArr.length - 1) {
1394: result.append(", ");
1395: }
1396: }
1397: return result.toString();
1398: }
1399:
1400: /**
1401: * Uses the column type mapper to get the column type and appends that to the
1402: * name with an optional not null modifier.
1403: *
1404: * @param colInfo
1405: * @return
1406: * @throws UserCancelledOperationException
1407: * @throws MappingException
1408: */
1409: public static String getColumnSql(SessionInfoProvider prov,
1410: TableColumnInfo colInfo, String sourceTableName,
1411: String destTableName)
1412: throws UserCancelledOperationException, MappingException {
1413: String columnName = colInfo.getColumnName();
1414: if (_prefs.isCheckKeywords()) {
1415: checkKeyword(prov.getCopyDestSession(), destTableName,
1416: columnName);
1417: }
1418: StringBuilder result = new StringBuilder(columnName);
1419: boolean notNullable = colInfo.isNullable().equalsIgnoreCase(
1420: "NO");
1421: String typeName = ColTypeMapper.mapColType(prov
1422: .getCopySourceSession(), prov.getCopyDestSession(),
1423: colInfo, sourceTableName, destTableName);
1424: result.append(" ");
1425: result.append(typeName);
1426: if (notNullable) {
1427: result.append(" NOT NULL");
1428: } else {
1429: ISession destSession = prov.getCopyDestSession();
1430: HibernateDialect d = DialectFactory.getDialect(
1431: DialectFactory.DEST_TYPE, destSession
1432: .getApplication().getMainFrame(),
1433: destSession.getMetaData());
1434: String nullString = d.getNullColumnString().toUpperCase();
1435: result.append(nullString);
1436: }
1437: return result.toString();
1438: }
1439:
1440: /**
1441: * Checks the specified column is not a keyword in the specified session.
1442: *
1443: * @param session the session whose keywords to check against
1444: * @param table the name of the table to use in the error message
1445: * @param column the name of the column to check
1446: *
1447: * @throws MappingException if the specified column is a keyword in the
1448: * specified session
1449: */
1450: public static void checkKeyword(ISession session, String table,
1451: String column) throws MappingException {
1452: if (isKeyword(session, column)) {
1453: String message = getMessage("DBUtil.mappingErrorKeyword",
1454: new String[] { table, column });
1455: throw new MappingException(message);
1456: }
1457: }
1458:
1459: /**
1460: *
1461: * @param sourceConn
1462: * @param ti
1463: * @param column
1464: * @return
1465: * @throws SQLException
1466: */
1467: public static String getColumnName(ISQLConnection sourceConn,
1468: ITableInfo ti, int column) throws SQLException {
1469: TableColumnInfo[] infoArr = sourceConn.getSQLMetaData()
1470: .getColumnInfo(ti);
1471: TableColumnInfo colInfo = infoArr[column];
1472: return colInfo.getColumnName();
1473: }
1474:
1475: /**
1476: *
1477: * @param sourceConn
1478: * @param ti
1479: * @return
1480: * @throws SQLException
1481: */
1482: public static String[] getColumnNames(ISQLConnection sourceConn,
1483: ITableInfo ti) throws SQLException {
1484: TableColumnInfo[] infoArr = sourceConn.getSQLMetaData()
1485: .getColumnInfo(ti);
1486: String[] result = new String[infoArr.length];
1487: for (int i = 0; i < result.length; i++) {
1488: TableColumnInfo colInfo = infoArr[i];
1489: result[i] = colInfo.getColumnName();
1490: }
1491: return result;
1492: }
1493:
1494: /**
1495: *
1496: * @param columnList
1497: * @param ti
1498: * @return
1499: * @throws SQLException
1500: */
1501: public static String getSelectQuery(SessionInfoProvider prov,
1502: String columnList, ITableInfo ti) throws SQLException,
1503: UserCancelledOperationException {
1504: StringBuilder result = new StringBuilder("select ");
1505: result.append(columnList);
1506: result.append(" from ");
1507: ISession sourceSession = prov.getCopySourceSession();
1508:
1509: String tableName = getQualifiedObjectName(sourceSession, ti
1510: .getCatalogName(), ti.getSchemaName(), ti
1511: .getSimpleName(), DialectFactory.SOURCE_TYPE);
1512: result.append(tableName);
1513: return result.toString();
1514: }
1515:
1516: /**
1517: *
1518: * @param sourceConn
1519: * @param columnList
1520: * @param ti
1521: * @return
1522: * @throws SQLException
1523: */
1524: public static String getInsertSQL(SessionInfoProvider prov,
1525: String columnList, ITableInfo ti, int columnCount)
1526: throws SQLException, UserCancelledOperationException {
1527: StringBuilder result = new StringBuilder();
1528: result.append("insert into ");
1529: String destSchema = prov.getDestSelectedDatabaseObject()
1530: .getSimpleName();
1531: String destCatalog = prov.getDestSelectedDatabaseObject()
1532: .getCatalogName();
1533: ISession destSession = prov.getCopyDestSession();
1534: result.append(getQualifiedObjectName(destSession, destCatalog,
1535: destSchema, ti.getSimpleName(),
1536: DialectFactory.DEST_TYPE));
1537: result.append(" ( ");
1538: result.append(columnList);
1539: result.append(" ) values ( ");
1540: result.append(getQuestionMarks(columnCount));
1541: result.append(" )");
1542: return result.toString();
1543: }
1544:
1545: /**
1546: * Returns a boolean value indicating whether or not the specified
1547: * TableColumnInfo represents a database column that holds binary type
1548: * data.
1549: *
1550: * @param columnInfo the TableColumnInfo to examine
1551: * @return true if binary; false otherwise.
1552: */
1553: public static boolean isBinaryType(TableColumnInfo columnInfo) {
1554: boolean result = false;
1555: int type = columnInfo.getDataType();
1556: if (type == Types.BINARY || type == Types.BLOB
1557: || type == Types.LONGVARBINARY
1558: || type == Types.VARBINARY) {
1559: result = true;
1560: }
1561: return result;
1562: }
1563:
1564: /**
1565: * Decide whether or not the session specified needs fully qualified table
1566: * names (schema.table). In most databases this is optional (Oracle).
1567: * In others it is required (Progress). In still others it must not occur.
1568: * (Axion, Hypersonic)
1569: *
1570: * @param session
1571: * @param catalogName
1572: * @param schemaName
1573: * @param objectName
1574: * @return
1575: * @throws UserCancelledOperationException
1576: */
1577: public static String getQualifiedObjectName(ISession session,
1578: String catalogName, String schemaName, String objectName,
1579: int sessionType) {
1580: String catalog = catalogName;
1581: String schema = schemaName;
1582: String object = objectName;
1583:
1584: // Bug #1714476 (DB copy uses wrong case for table names): When the
1585: // catalog/schema/object names come from the source session, don't mess
1586: // with the case, as the case is provided by the driver for the existing
1587: // table, and doesn't need to be fixed.
1588: if (sessionType == DialectFactory.DEST_TYPE) {
1589: catalog = fixCase(session, catalogName);
1590: schema = fixCase(session, schemaName);
1591: object = fixCase(session, objectName);
1592: }
1593: ISQLDatabaseMetaData md = session.getMetaData();
1594: boolean useSchema = true;
1595: boolean useCatalog = true;
1596: try {
1597: useCatalog = md.supportsCatalogsInTableDefinitions();
1598: } catch (SQLException e) {
1599: log
1600: .info("Encountered unexpected exception while attempting to "
1601: + "determine if catalogs are used in table definitions");
1602: }
1603: try {
1604: useSchema = md.supportsSchemasInTableDefinitions();
1605: } catch (SQLException e) {
1606: log
1607: .info("Encountered unexpected exception while attempting to "
1608: + "determine if schemas are used in table definitions");
1609: }
1610: if (!useCatalog && !useSchema) {
1611: return object;
1612: }
1613: if ((catalog == null || catalog.equals(""))
1614: && (schema == null || schema.equals(""))) {
1615: return object;
1616: }
1617: StringBuilder result = new StringBuilder();
1618: if (useCatalog && catalog != null && !catalog.equals("")) {
1619: result.append(catalog);
1620: result.append(getCatSep(session));
1621: }
1622: if (useSchema && schema != null && !schema.equals("")) {
1623: result.append(schema);
1624: result.append(".");
1625: }
1626: result.append(object);
1627: return result.toString();
1628: }
1629:
1630: public static String getCatSep(ISession session) {
1631: String catsep = ".";
1632: try {
1633: ISQLDatabaseMetaData md = session.getMetaData();
1634: catsep = md.getCatalogSeparator();
1635: } catch (SQLException e) {
1636: log.error("getCatSep: Unexpected Exception - "
1637: + e.getMessage(), e);
1638: }
1639: return catsep;
1640: }
1641:
1642: /**
1643: * Uppercase / Lowercase / Mixedcase identifiers are a big problem. Some
1644: * databases support mixing case (like McKoi) others force identifier case
1645: * to all uppercase or all lowercase. Some (like MySQL) can be configured
1646: * to care or not care about case as well as depending on the platform the
1647: * database is on. This method attempt to use the metadata from the driver
1648: * to "fix" the case of the identifier to be acceptable for the specified
1649: * session.
1650: *
1651: * @param session the session whose disposition on case we care about.
1652: * @param identifier
1653: * @return
1654: */
1655: public static String fixCase(ISession session, String identifier) {
1656: if (identifier == null || identifier.equals("")) {
1657: return identifier;
1658: }
1659: try {
1660: DatabaseMetaData md = session.getSQLConnection()
1661: .getConnection().getMetaData();
1662:
1663: // Don't change the case of the identifier if database allows mixed
1664: // case.
1665: if (md.storesMixedCaseIdentifiers()) {
1666: return identifier;
1667: }
1668: // Fix the case according to what the database tells us.
1669: if (md.storesUpperCaseIdentifiers()) {
1670: return identifier.toUpperCase();
1671: } else {
1672: return identifier.toLowerCase();
1673: }
1674: } catch (SQLException e) {
1675: if (log.isDebugEnabled()) {
1676: log.debug("fixCase: unexpected exception: "
1677: + e.getMessage());
1678: }
1679: return identifier;
1680: }
1681: }
1682:
1683: /**
1684: * Generates a string of question marks which are used for creating
1685: * PreparedStatements. The question marks are delimited by commas.
1686: *
1687: * @param count the number of question marks (representing PS bind variables).
1688: * @return
1689: */
1690: private static String getQuestionMarks(int count) {
1691: StringBuilder result = new StringBuilder();
1692: for (int i = 0; i < count; i++) {
1693: result.append("?");
1694: if (i < count - 1) {
1695: result.append(", ");
1696: }
1697: }
1698: return result.toString();
1699: }
1700:
1701: /**
1702: *
1703: * @param sourceConn
1704: * @param ti
1705: * @return
1706: * @throws SQLException
1707: */
1708: public static int getColumnCount(ISQLConnection sourceConn,
1709: ITableInfo ti) throws SQLException {
1710: return sourceConn.getSQLMetaData().getColumnInfo(ti).length;
1711: }
1712:
1713: /**
1714: *
1715: * @param con
1716: * @param ti
1717: * @param column
1718: * @return
1719: * @throws SQLException
1720: */
1721: public static int getColumnType(ISQLConnection con, ITableInfo ti,
1722: int column) throws SQLException {
1723: TableColumnInfo[] infoArr = con.getSQLMetaData().getColumnInfo(
1724: ti);
1725: TableColumnInfo colInfo = infoArr[column];
1726: return colInfo.getDataType();
1727: }
1728:
1729: public static int[] getColumnTypes(ISQLConnection con, ITableInfo ti)
1730: throws SQLException {
1731: TableColumnInfo[] infoArr = con.getSQLMetaData().getColumnInfo(
1732: ti);
1733: int[] result = new int[infoArr.length];
1734: for (int i = 0; i < result.length; i++) {
1735: TableColumnInfo colInfo = infoArr[i];
1736: result[i] = colInfo.getDataType();
1737: }
1738: return result;
1739: }
1740:
1741: public static void validateColumnNames(ITableInfo ti,
1742: SessionInfoProvider prov) throws MappingException,
1743: UserCancelledOperationException {
1744: if (prov == null) {
1745: return;
1746: }
1747: ISession sourceSession = prov.getCopySourceSession();
1748: ISession destSession = prov.getCopyDestSession();
1749: if (sourceSession == null || destSession == null) {
1750: return;
1751: }
1752: ISQLConnection sourceCon = sourceSession.getSQLConnection();
1753: ISQLConnection con = destSession.getSQLConnection();
1754: TableColumnInfo[] colInfoArr = null;
1755: try {
1756: colInfoArr = sourceCon.getSQLMetaData().getColumnInfo(ti);
1757: } catch (SQLException e) {
1758: // ignore any SQLExceptions. This would only if we could not get
1759: // column info from the SQL database meta data.
1760: return;
1761: }
1762: for (int colIdx = 0; colIdx < colInfoArr.length; colIdx++) {
1763: TableColumnInfo colInfo = colInfoArr[colIdx];
1764: IDatabaseObjectInfo selectedDestObj = prov
1765: .getDestSelectedDatabaseObject();
1766: String schema = selectedDestObj.getSimpleName();
1767: String catalog = selectedDestObj.getCatalogName();
1768: String tableName = getQualifiedObjectName(destSession,
1769: catalog, schema, TEST_TABLE_NAME,
1770: DialectFactory.DEST_TYPE);
1771:
1772: StringBuilder sql = new StringBuilder("CREATE TABLE ");
1773: sql.append(tableName);
1774: sql.append(" ( ");
1775: sql.append(colInfo.getColumnName());
1776: sql.append(" CHAR(10) )");
1777: boolean cascade = DialectFactory.isFrontBase(destSession
1778: .getMetaData());
1779: try {
1780: dropTable(TEST_TABLE_NAME, schema, catalog,
1781: destSession, cascade, DialectFactory.DEST_TYPE);
1782: DBUtil.executeUpdate(con, sql.toString(), false);
1783: } catch (SQLException e) {
1784: String message = getMessage(
1785: "DBUtil.mappingErrorKeyword", new String[] {
1786: ti.getSimpleName(),
1787: colInfo.getColumnName() });
1788: log.error(message, e);
1789: throw new MappingException(message);
1790: } finally {
1791: dropTable(tableName, schema, catalog, destSession,
1792: cascade, DialectFactory.DEST_TYPE);
1793: }
1794:
1795: }
1796: }
1797:
1798: public static boolean dropTable(String tableName,
1799: String schemaName, String catalogName, ISession session,
1800: boolean cascade, int sessionType)
1801: throws UserCancelledOperationException {
1802: boolean result = false;
1803: ISQLConnection con = session.getSQLConnection();
1804: String table = getQualifiedObjectName(session, catalogName,
1805: schemaName, tableName, sessionType);
1806: String dropsql = "DROP TABLE " + table;
1807: if (cascade) {
1808: dropsql += " CASCADE";
1809: }
1810: try {
1811: DBUtil.executeUpdate(con, dropsql, false);
1812: result = true;
1813: } catch (SQLException e) {
1814: /* Do nothing */
1815: }
1816: return result;
1817: }
1818:
1819: public static boolean sameDatabaseType(ISession session1,
1820: ISession session2) {
1821: boolean result = false;
1822: String driver1ClassName = session1.getDriver()
1823: .getDriverClassName();
1824: String driver2ClassName = session2.getDriver()
1825: .getDriverClassName();
1826: if (driver1ClassName.equals(driver2ClassName)) {
1827: result = true;
1828: }
1829: return result;
1830: }
1831:
1832: /**
1833: * Gets the SQL statement which can be used to select the maximum length
1834: * of the current data found in tableName within the specified column.
1835: *
1836: * @param sourceSession
1837: * @param colInfo
1838: * @param tableName
1839: * @param tableNameIsQualified TODO
1840: * @return
1841: */
1842: public static String getMaxColumnLengthSQL(ISession sourceSession,
1843: TableColumnInfo colInfo, String tableName,
1844: boolean tableNameIsQualified)
1845: throws UserCancelledOperationException {
1846: StringBuilder result = new StringBuilder();
1847: HibernateDialect dialect = DialectFactory.getDialect(
1848: DialectFactory.SOURCE_TYPE, sourceSession
1849: .getApplication().getMainFrame(), sourceSession
1850: .getMetaData());
1851: String lengthFunction = dialect.getLengthFunction(colInfo
1852: .getDataType());
1853: if (lengthFunction == null) {
1854: log
1855: .error("Length function is null for dialect="
1856: + dialect.getClass().getName()
1857: + ". Using 'length'");
1858: lengthFunction = "length";
1859: }
1860: String maxFunction = dialect.getMaxFunction();
1861: if (maxFunction == null) {
1862: log.error("Max function is null for dialect="
1863: + dialect.getClass().getName() + ". Using 'max'");
1864: maxFunction = "max";
1865: }
1866: result.append("select ");
1867: result.append(maxFunction);
1868: result.append("(");
1869: result.append(lengthFunction);
1870: result.append("(");
1871: result.append(colInfo.getColumnName());
1872: result.append(")) from ");
1873: String table = tableName;
1874: if (!tableNameIsQualified) {
1875: table = getQualifiedObjectName(sourceSession, colInfo
1876: .getCatalogName(), colInfo.getSchemaName(),
1877: tableName, DialectFactory.SOURCE_TYPE);
1878: }
1879: result.append(table);
1880: return result.toString();
1881: }
1882:
1883: /**
1884: * @param lastStatement the lastStatement to set
1885: */
1886: public static void setLastStatement(String lastStatement) {
1887: DBUtil.lastStatement = lastStatement;
1888: }
1889:
1890: /**
1891: * @return the lastStatement
1892: */
1893: public static String getLastStatement() {
1894: return lastStatement;
1895: }
1896:
1897: public static void setLastStatementValues(String values) {
1898: lastStatementValues = values;
1899: }
1900:
1901: public static String getLastStatementValues() {
1902: return lastStatementValues;
1903: }
1904: }
|