Repository: sqoop Updated Branches: refs/heads/sqoop2 cbf95514e -> 901bc966f
SQOOP-1841: Sqoop2: Upgrade from 1.99.1 to 1.99.4 onwards is broken (Abraham Elmahrek via Jarek Jarcec Cecho) Project: http://git-wip-us.apache.org/repos/asf/sqoop/repo Commit: http://git-wip-us.apache.org/repos/asf/sqoop/commit/901bc966 Tree: http://git-wip-us.apache.org/repos/asf/sqoop/tree/901bc966 Diff: http://git-wip-us.apache.org/repos/asf/sqoop/diff/901bc966 Branch: refs/heads/sqoop2 Commit: 901bc966f5d12cc9ea706b7b1f6de14107d2da16 Parents: cbf9551 Author: Jarek Jarcec Cecho <[email protected]> Authored: Tue Feb 24 09:07:04 2015 -0800 Committer: Jarek Jarcec Cecho <[email protected]> Committed: Tue Feb 24 09:07:04 2015 -0800 ---------------------------------------------------------------------- .../repository/common/CommonRepoUtils.java | 10 +-- .../derby/DerbyRepositoryHandler.java | 86 ++++++++++++++++++- ...erbySchemaInsertUpdateDeleteSelectQuery.java | 7 ++ .../derby/DerbySchemaUpgradeQuery.java | 87 +++++++++++++++++++- 4 files changed, 182 insertions(+), 8 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sqoop/blob/901bc966/repository/repository-common/src/main/java/org/apache/sqoop/repository/common/CommonRepoUtils.java ---------------------------------------------------------------------- diff --git a/repository/repository-common/src/main/java/org/apache/sqoop/repository/common/CommonRepoUtils.java b/repository/repository-common/src/main/java/org/apache/sqoop/repository/common/CommonRepoUtils.java index 95e3bb8..96438d6 100644 --- a/repository/repository-common/src/main/java/org/apache/sqoop/repository/common/CommonRepoUtils.java +++ b/repository/repository-common/src/main/java/org/apache/sqoop/repository/common/CommonRepoUtils.java @@ -18,22 +18,22 @@ package org.apache.sqoop.repository.common; public class CommonRepoUtils { - public static final String TABLE_NAME_QUOTE_CHAR = "\""; + public static final String QUOTE_CHARACTER = "\""; public static final String escapeTableName(String tableName) { - return TABLE_NAME_QUOTE_CHAR + tableName + TABLE_NAME_QUOTE_CHAR; + return QUOTE_CHARACTER + tableName + QUOTE_CHARACTER; } public static final String escapeColumnName(String columnName) { - return TABLE_NAME_QUOTE_CHAR + columnName + TABLE_NAME_QUOTE_CHAR; + return QUOTE_CHARACTER + columnName + QUOTE_CHARACTER; } public static final String escapeSchemaName(String schemaName) { - return TABLE_NAME_QUOTE_CHAR + schemaName + TABLE_NAME_QUOTE_CHAR; + return QUOTE_CHARACTER + schemaName + QUOTE_CHARACTER; } public static final String escapeConstraintName(String constraintName) { - return TABLE_NAME_QUOTE_CHAR + constraintName + TABLE_NAME_QUOTE_CHAR; + return QUOTE_CHARACTER + constraintName + QUOTE_CHARACTER; } public static final String getTableName(String schemaName, String tableName) { http://git-wip-us.apache.org/repos/asf/sqoop/blob/901bc966/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java index f7bab09..1b1270e 100644 --- a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java @@ -17,9 +17,9 @@ */ package org.apache.sqoop.repository.derby; +import static org.apache.sqoop.repository.common.CommonRepositorySchemaConstants.SCHEMA_SQOOP; import static org.apache.sqoop.repository.derby.DerbySchemaCreateQuery.*; import static org.apache.sqoop.repository.derby.DerbySchemaInsertUpdateDeleteSelectQuery.*; -import static org.apache.sqoop.repository.common.CommonRepositoryInsertUpdateDeleteSelectQuery.*; import static org.apache.sqoop.repository.derby.DerbySchemaUpgradeQuery.*; import java.net.URL; @@ -31,6 +31,7 @@ import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.sql.Types; +import java.util.ArrayList; import java.util.HashSet; import java.util.LinkedList; import java.util.List; @@ -236,6 +237,7 @@ public class DerbyRepositoryHandler extends CommonRepositoryHandler { runQuery(QUERY_UPGRADE_TABLE_SQ_SUBMISSION_ADD_COLUMN_UPDATE_USER, conn); } if(repositoryVersion <= 2) { + migrateFromUnnamedConstraintsToNamedConstraints(conn); runQuery(QUERY_UPGRADE_TABLE_SQ_SUBMISSION_MODIFY_COLUMN_SQS_EXTERNAL_ID_VARCHAR_50, conn); runQuery(QUERY_UPGRADE_TABLE_SQ_CONNECTOR_MODIFY_COLUMN_SQC_VERSION_VARCHAR_64, conn); } @@ -307,6 +309,88 @@ public class DerbyRepositoryHandler extends CommonRepositoryHandler { upgradeRepositoryVersion(conn); } + /** + * In reality, this method simply drops all constrains on particular tables + * and creates new constraints. Pre-1.99.3 these will be unnamed, + * Post-1.99.3 these will be named. + * + * @param conn + */ + private void migrateFromUnnamedConstraintsToNamedConstraints(Connection conn) { + // Get unnamed constraints + PreparedStatement fetchUnnamedConstraintsStmt = null; + Statement dropUnnamedConstraintsStmt = null; + Map<String, List<String>> autoConstraintNameMap = new TreeMap<String, List<String>>(); + + try { + fetchUnnamedConstraintsStmt = conn.prepareStatement(STMT_FETCH_TABLE_FOREIGN_KEYS); + for (String tableName : new String[] { + DerbySchemaConstants.TABLE_SQ_FORM_NAME, + CommonRepositorySchemaConstants.TABLE_SQ_INPUT_NAME, + DerbySchemaConstants.TABLE_SQ_CONNECTION_NAME, + CommonRepositorySchemaConstants.TABLE_SQ_JOB_NAME, + DerbySchemaConstants.TABLE_SQ_CONNECTION_INPUT_NAME, + CommonRepositorySchemaConstants.TABLE_SQ_JOB_INPUT_NAME, + CommonRepositorySchemaConstants.TABLE_SQ_SUBMISSION_NAME, + CommonRepositorySchemaConstants.TABLE_SQ_COUNTER_SUBMISSION_NAME + }) { + fetchUnnamedConstraintsStmt.setString(1, tableName); + + if (!autoConstraintNameMap.containsKey(tableName)) { + autoConstraintNameMap.put(tableName, new ArrayList<String>()); + } + + List<String> autoConstraintNames = autoConstraintNameMap.get(tableName); + + if (fetchUnnamedConstraintsStmt.execute()) { + LOG.info("QUERY(" + STMT_FETCH_TABLE_FOREIGN_KEYS + ") with args: [" + tableName + "]"); + ResultSet rs = fetchUnnamedConstraintsStmt.getResultSet(); + + while (rs.next()) { + autoConstraintNames.add(rs.getString(1)); + } + + rs.close(); + } + } + } catch (SQLException e) { + throw new SqoopException(DerbyRepoError.DERBYREPO_0000, e); + } finally { + closeStatements(fetchUnnamedConstraintsStmt); + } + + // Drop constraints + for (String tableName : autoConstraintNameMap.keySet()) { + for (String constraintName : autoConstraintNameMap.get(tableName)) { + String query = DerbySchemaUpgradeQuery.getDropConstraintQuery( + SCHEMA_SQOOP, tableName, constraintName); + try { + dropUnnamedConstraintsStmt = conn.createStatement(); + dropUnnamedConstraintsStmt.execute(query); + } catch (SQLException e) { + throw new SqoopException(DerbyRepoError.DERBYREPO_0000, e); + } finally { + LOG.info("QUERY(" + query + ")"); + closeStatements(dropUnnamedConstraintsStmt); + } + } + } + + // Create named constraints + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQF_SQC, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQI_SQF, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQN_SQC, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQB_SQN, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQNI_SQN, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQNI_SQI, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQBI_SQB, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQBI_SQI, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQS_SQB, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQRS_SQG, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQRS_SQR, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQRS_SQS, conn); + } + // SQOOP-1498 refactoring related upgrades for table and column names void renameEntitiesForConnectionAndForm(Connection conn) { // LINK http://git-wip-us.apache.org/repos/asf/sqoop/blob/901bc966/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java index ce7b6cb..a242ff9 100644 --- a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java @@ -98,6 +98,13 @@ public final class DerbySchemaInsertUpdateDeleteSelectQuery extends CommonReposi + CommonRepoUtils.escapeColumnName(COLUMN_SQ_CFG_DIRECTION) + " FROM " + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONFIG_NAME); + public static final String STMT_FETCH_TABLE_FOREIGN_KEYS = "SELECT " + + CommonRepoUtils.escapeColumnName("CONSTRAINTNAME") + + " FROM " + CommonRepoUtils.getTableName("SYS", "SYSCONSTRAINTS") + + " C LEFT JOIN " + CommonRepoUtils.getTableName("SYS", "SYSTABLES") + + " T ON C." + CommonRepoUtils.escapeColumnName("TABLEID") + " = T." + CommonRepoUtils.escapeColumnName("TABLEID") + + " WHERE C.TYPE = 'F' AND T." + CommonRepoUtils.escapeColumnName("TABLENAME") + " = ?"; + private DerbySchemaInsertUpdateDeleteSelectQuery() { // Disable explicit object creation } http://git-wip-us.apache.org/repos/asf/sqoop/blob/901bc966/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaUpgradeQuery.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaUpgradeQuery.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaUpgradeQuery.java index 46b55bf..2e780ff 100644 --- a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaUpgradeQuery.java +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaUpgradeQuery.java @@ -38,7 +38,6 @@ import static org.apache.sqoop.repository.derby.DerbySchemaConstants.*; // enhancing this code public final class DerbySchemaUpgradeQuery { - // DDL: Increased size of SQ_CONNECTOR.SQC_VERSION to 64 public static final String QUERY_UPGRADE_TABLE_SQ_CONNECTOR_MODIFY_COLUMN_SQC_VERSION_VARCHAR_64 = "ALTER TABLE " + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTOR_NAME) + " ALTER COLUMN " @@ -515,7 +514,91 @@ public final class DerbySchemaUpgradeQuery { + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_INPUT_NAME) + " ADD COLUMN " + CommonRepoUtils.escapeColumnName(COLUMN_SQI_EDITABLE) + " VARCHAR(32)"; - private DerbySchemaUpgradeQuery() { + // Add 1.99.3 constraints + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQF_SQC = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_FORM_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQF_SQC_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQF_CONNECTOR) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTOR_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQC_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQI_SQF = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_INPUT_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQI_SQF_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQI_FORM) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_FORM_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQF_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQN_SQC = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTION_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQN_SQC_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQN_CONNECTOR) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTOR_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQC_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQB_SQN = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_JOB_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQB_SQN_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQB_CONNECTION) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTION_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQN_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQNI_SQN = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTION_INPUT_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQNI_SQN_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQNI_CONNECTION) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTION_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQN_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQNI_SQI = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_CONNECTION_INPUT_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQNI_SQI_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQNI_INPUT) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_INPUT_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQI_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQBI_SQB = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_JOB_INPUT_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQBI_SQB_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQBI_JOB) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_JOB_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQB_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQBI_SQI = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_JOB_INPUT_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQBI_SQI_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQBI_INPUT) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_INPUT_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQI_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQS_SQB = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_SUBMISSION_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQS_SQB_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQS_JOB) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_JOB_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQB_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQRS_SQG = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_COUNTER_SUBMISSION_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQRS_SQG_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQRS_GROUP) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_COUNTER_GROUP_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQG_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQRS_SQR = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_COUNTER_SUBMISSION_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQRS_SQR_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQRS_COUNTER) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_COUNTER_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQR_ID) + ")"; + + public static final String QUERY_UPGRADE_TABLE_SQ_FORM_ADD_CONSTRAINT_SQRS_SQS = "ALTER TABLE " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_COUNTER_SUBMISSION_NAME) + + " ADD CONSTRAINT " + CommonRepoUtils.getConstraintName(SCHEMA_SQOOP, CONSTRAINT_SQRS_SQS_NAME) + + " FOREIGN KEY (" + CommonRepoUtils.escapeColumnName(COLUMN_SQRS_SUBMISSION) + ") REFERENCES " + + CommonRepoUtils.getTableName(SCHEMA_SQOOP, TABLE_SQ_SUBMISSION_NAME) + " (" + CommonRepoUtils.escapeColumnName(COLUMN_SQS_ID) + ") ON DELETE CASCADE"; + + public static final String getDropConstraintQuery(String schemaName, String tableName, String constraintName) { + StringBuilder queryBuilder = new StringBuilder(); + + queryBuilder.append("ALTER TABLE "); + queryBuilder.append(CommonRepoUtils.getTableName(schemaName, tableName)); + queryBuilder.append(" DROP CONSTRAINT "); + queryBuilder.append(CommonRepoUtils.getConstraintName(schemaName, constraintName)); + + return queryBuilder.toString(); + } + + private DerbySchemaUpgradeQuery() { // Disable explicit object creation } } \ No newline at end of file
