Repository: sqoop Updated Branches: refs/heads/sqoop2 31f30cc27 -> a9bd172a9
SQOOP-1636: Sqoop2: Add unique constraint to the SQI_INPUT name per type per config (Veena Basavaraj via Abraham Elmahrek) Project: http://git-wip-us.apache.org/repos/asf/sqoop/repo Commit: http://git-wip-us.apache.org/repos/asf/sqoop/commit/a9bd172a Tree: http://git-wip-us.apache.org/repos/asf/sqoop/tree/a9bd172a Diff: http://git-wip-us.apache.org/repos/asf/sqoop/diff/a9bd172a Branch: refs/heads/sqoop2 Commit: a9bd172a944ee747ab482d454887251580c4266d Parents: 31f30cc Author: Abraham Elmahrek <[email protected]> Authored: Wed Oct 29 12:17:20 2014 -0700 Committer: Abraham Elmahrek <[email protected]> Committed: Wed Oct 29 12:17:20 2014 -0700 ---------------------------------------------------------------------- .../derby/DerbyRepositoryHandler.java | 4 +- .../repository/derby/DerbySchemaConstants.java | 7 ++- .../derby/DerbySchemaUpgradeQuery.java | 8 ++++ .../sqoop/repository/derby/DerbyTestCase.java | 48 ++++++++++++++++++-- .../derby/TestRespositorySchemaUpgrade.java | 20 ++++++++ 5 files changed, 81 insertions(+), 6 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sqoop/blob/a9bd172a/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 58d6a43..b996a0b 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 @@ -444,7 +444,9 @@ public class DerbyRepositoryHandler extends JdbcRepositoryHandler { runQuery(QUERY_UPGRADE_TABLE_SQ_JOB_ADD_UNIQUE_CONSTRAINT_NAME, conn); runQuery(QUERY_UPGRADE_TABLE_SQ_LINK_ADD_UNIQUE_CONSTRAINT_NAME, conn); runQuery(QUERY_UPGRADE_TABLE_SQ_CONFIGURABLE_ADD_UNIQUE_CONSTRAINT_NAME, conn); - runQuery(QUERY_UPGRADE_TABLE_SQ_CONFIG_ADD_UNIQUE_CONSTRAINT_NAME_TYPE_AND_CONFIGURABLE_ID, conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_CONFIG_ADD_UNIQUE_CONSTRAINT_NAME_TYPE_AND_CONFIGURABLE_ID, + conn); + runQuery(QUERY_UPGRADE_TABLE_SQ_INPUT_ADD_UNIQUE_CONSTRAINT_NAME_TYPE_AND_CONFIG_ID, conn); } // last step upgrade the repository version to the latest value in the code http://git-wip-us.apache.org/repos/asf/sqoop/blob/a9bd172a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaConstants.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaConstants.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaConstants.java index 41ca107..3d15e47 100644 --- a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaConstants.java +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaConstants.java @@ -140,7 +140,7 @@ public final class DerbySchemaConstants { public static final String CONSTRAINT_SQ_CFG_SQC_NAME = CONSTRAINT_PREFIX + "SQ_CFG_SQC"; public static final String CONSTRAINT_SQ_CFG_SQC = SCHEMA_PREFIX + CONSTRAINT_SQ_CFG_SQC_NAME; - // unique name constraint + // uniqueness constraint public static final String CONSTRAINT_SQ_CONFIG_UNIQUE_NAME_TYPE_CONFIGURABLE = CONSTRAINT_PREFIX + "SQ_CFG_NAME_TYPE_CONFIGURABLE_UNIQUE"; public static final String CONSTRAINT_SQ_CONFIG_UNIQUE = SCHEMA_PREFIX + CONSTRAINT_SQ_CONFIG_UNIQUE_NAME_TYPE_CONFIGURABLE; @@ -201,6 +201,11 @@ public final class DerbySchemaConstants { public static final String CONSTRAINT_SQI_SQ_CFG_NAME = CONSTRAINT_PREFIX + "SQI_SQ_CFG"; public static final String CONSTRAINT_SQI_SQ_CFG = SCHEMA_PREFIX + CONSTRAINT_SQI_SQ_CFG_NAME; + // uniqueness constraint + public static final String CONSTRAINT_SQ_INPUT_UNIQUE_NAME_TYPE_CONFIG = CONSTRAINT_PREFIX + "SQI_NAME_TYPE_CONFIG_UNIQUE"; + public static final String CONSTRAINT_SQ_INPUT_UNIQUE = SCHEMA_PREFIX + CONSTRAINT_SQ_INPUT_UNIQUE_NAME_TYPE_CONFIG; + + // SQ_LINK @Deprecated // used only for upgrade public static final String TABLE_SQ_CONNECTION_NAME = "SQ_CONNECTION"; public static final String TABLE_SQ_LINK_NAME = "SQ_LINK"; http://git-wip-us.apache.org/repos/asf/sqoop/blob/a9bd172a/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 2abdfe5..80670dc 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 @@ -436,6 +436,14 @@ public final class DerbySchemaUpgradeQuery { + " UNIQUE (" + COLUMN_SQ_CFG_NAME + ", " + COLUMN_SQ_CFG_TYPE + ", " + COLUMN_SQ_CFG_CONFIGURABLE + ")"; + // add unique constraint on the input table for name and type and configId + public static final String QUERY_UPGRADE_TABLE_SQ_INPUT_ADD_UNIQUE_CONSTRAINT_NAME_TYPE_AND_CONFIG_ID = "ALTER TABLE " + + TABLE_SQ_INPUT + + " ADD CONSTRAINT " + + CONSTRAINT_SQ_INPUT_UNIQUE + + " UNIQUE (" + + COLUMN_SQI_NAME + ", " + COLUMN_SQI_TYPE + ", " + COLUMN_SQI_CONFIG + ")"; + private DerbySchemaUpgradeQuery() { // Disable explicit object creation } http://git-wip-us.apache.org/repos/asf/sqoop/blob/a9bd172a/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/DerbyTestCase.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/DerbyTestCase.java b/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/DerbyTestCase.java index 8a1e346..a895320 100644 --- a/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/DerbyTestCase.java +++ b/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/DerbyTestCase.java @@ -20,7 +20,6 @@ package org.apache.sqoop.repository.derby; import static org.apache.sqoop.repository.derby.DerbySchemaUpgradeQuery.*; import static org.apache.sqoop.repository.derby.DerbySchemaCreateQuery.*; import static org.apache.sqoop.repository.derby.DerbySchemaInsertUpdateDeleteSelectQuery.*; - import static org.junit.Assert.assertEquals; import java.sql.Connection; @@ -211,7 +210,10 @@ abstract public class DerbyTestCase { renameConnectorToConfigurable(); // add the name constraint for configurable runQuery(QUERY_UPGRADE_TABLE_SQ_CONFIGURABLE_ADD_UNIQUE_CONSTRAINT_NAME); + // add sq_config uniqueness constraint runQuery(QUERY_UPGRADE_TABLE_SQ_CONFIG_ADD_UNIQUE_CONSTRAINT_NAME_TYPE_AND_CONFIGURABLE_ID); + // add sq_input uniqueness constraint + runQuery(QUERY_UPGRADE_TABLE_SQ_INPUT_ADD_UNIQUE_CONSTRAINT_NAME_TYPE_AND_CONFIG_ID); } // deprecated repository version @@ -698,7 +700,7 @@ abstract public class DerbyTestCase { } /** - * testing config with non unique nam/type objects into repository. + * testing config with non unique name/type objects into repository. * @throws Exception */ public void loadNonUniqueConfigNameTypeInVersion4() throws Exception { @@ -712,7 +714,24 @@ abstract public class DerbyTestCase { } /** - * testing config with non unique nam/type objects into repository. + * testing input with non unique name/type objects into repository. + * @throws Exception + */ + public void loadNonUniqueInputNameTypeInVersion4() throws Exception { + + runInsertQuery("INSERT INTO SQOOP.SQ_CONFIG" + + "(SQ_CFG_CONFIGURABLE, SQ_CFG_NAME, SQ_CFG_TYPE, SQ_CFG_INDEX) " + + "VALUES(1, 'C1', 'LINK', 0)"); + runInsertQuery("INSERT INTO SQOOP.SQ_INPUT" + + "(SQI_NAME, SQI_CONFIG, SQI_INDEX, SQI_TYPE, SQI_STRMASK, SQI_STRLENGTH)" + + " VALUES('I1', 1, 0, 'STRING', false, 30)"); + runInsertQuery("INSERT INTO SQOOP.SQ_INPUT" + + "(SQI_NAME, SQI_CONFIG, SQI_INDEX, SQI_TYPE, SQI_STRMASK, SQI_STRLENGTH)" + + " VALUES('I1', 1, 0, 'STRING', false, 30)"); + } + + /** + * testing config with non unique name/type objects into repository. * @throws Exception */ public void loadNonUniqueConfigNameButUniqueTypeInVersion4() throws Exception { @@ -736,7 +755,28 @@ abstract public class DerbyTestCase { + "VALUES(1, 'C1', 'LINK', 0)"); runInsertQuery("INSERT INTO SQOOP.SQ_CONFIG" + "(SQ_CFG_CONFIGURABLE, SQ_CFG_NAME, SQ_CFG_TYPE, SQ_CFG_INDEX) " - + "VALUES(2, 'C1', 'LINK', 0)"); + + "VALUES(2, 'C1', 'LINK', 1)"); + } + + /** + * testing input with non unique name/type objects into repository. + * @throws Exception + */ + public void loadNonUniqueInputNameAndTypeButUniqueConfigInVersion4() throws Exception { + + runInsertQuery("INSERT INTO SQOOP.SQ_CONFIG" + + "(SQ_CFG_CONFIGURABLE, SQ_CFG_NAME, SQ_CFG_TYPE, SQ_CFG_INDEX) " + + "VALUES(1, 'C1', 'LINK', 0)"); + runInsertQuery("INSERT INTO SQOOP.SQ_CONFIG" + + "(SQ_CFG_CONFIGURABLE, SQ_CFG_NAME, SQ_CFG_TYPE, SQ_CFG_INDEX) " + + "VALUES(2, 'C2', 'LINK', 0)"); + + runInsertQuery("INSERT INTO SQOOP.SQ_INPUT" + + "(SQI_NAME, SQI_CONFIG, SQI_INDEX, SQI_TYPE, SQI_STRMASK, SQI_STRLENGTH)" + + " VALUES('C1.A', 1, 0, 'STRING', false, 30)"); + runInsertQuery("INSERT INTO SQOOP.SQ_INPUT" + + "(SQI_NAME, SQI_CONFIG, SQI_INDEX, SQI_TYPE, SQI_STRMASK, SQI_STRLENGTH)" + + " VALUES('C1.A', 2, 1, 'STRING', false, 30)"); } public void loadJobsForLatestVersion() throws Exception { http://git-wip-us.apache.org/repos/asf/sqoop/blob/a9bd172a/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/TestRespositorySchemaUpgrade.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/TestRespositorySchemaUpgrade.java b/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/TestRespositorySchemaUpgrade.java index 8cbe811..c281901 100644 --- a/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/TestRespositorySchemaUpgrade.java +++ b/repository/repository-derby/src/test/java/org/apache/sqoop/repository/derby/TestRespositorySchemaUpgrade.java @@ -103,6 +103,26 @@ public class TestRespositorySchemaUpgrade extends DerbyTestCase { super.loadNonUniqueConfigNameAndTypeButUniqueConfigurableInVersion4(); } + @Test(expected = SQLIntegrityConstraintViolationException.class) + public void testUpgradeVersion4WithNonUniqueInputNameAndTypeAdded() throws Exception { + super.createOrUpgradeSchema(4); + super.addConnectorB(); + // try loading duplicate input name and type for a config in version 4 and it should throw an + // exception + super.loadNonUniqueInputNameTypeInVersion4(); + } + + @Test + public void testUpgradeVersion4WithNonUniqueInputNameAndTypeButUniqueConfig() + throws Exception { + super.createOrUpgradeSchema(4); + super.addConnectorA(); + super.addConnectorB(); + // try loading duplicate input names and type but unique config, hence + // no exception + super.loadNonUniqueInputNameAndTypeButUniqueConfigInVersion4(); + } + @Test public void testUpgradeRepoVersion2ToVersion4() throws Exception { // in case of version 2 schema there is no unique job/ link constraint
