Repository: ambari Updated Branches: refs/heads/trunk f96d22e0c -> 74d037bdc
AMBARI-5344. Error with finding FK constraint. (mpapyrkovskyy) Project: http://git-wip-us.apache.org/repos/asf/ambari/repo Commit: http://git-wip-us.apache.org/repos/asf/ambari/commit/74d037bd Tree: http://git-wip-us.apache.org/repos/asf/ambari/tree/74d037bd Diff: http://git-wip-us.apache.org/repos/asf/ambari/diff/74d037bd Branch: refs/heads/trunk Commit: 74d037bdc96712db6d9bfe217c79094615a24a83 Parents: ff43fe2 Author: Myroslav Papirkovskyy <mpapyrkovs...@hortonworks.com> Authored: Thu Apr 3 21:09:38 2014 +0300 Committer: Myroslav Papirkovskyy <mpapyrkovs...@hortonworks.com> Committed: Fri Apr 4 18:10:44 2014 +0300 ---------------------------------------------------------------------- .../apache/ambari/server/orm/DBAccessor.java | 14 ++- .../ambari/server/orm/DBAccessorImpl.java | 104 ++++++++++++------- 2 files changed, 78 insertions(+), 40 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ambari/blob/74d037bd/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessor.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessor.java b/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessor.java index 462f891..a40d932 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessor.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessor.java @@ -246,13 +246,25 @@ public interface DBAccessor { * @param refTableName * @param columnName * @param refColumnName - * @return + * @return true if described relation exists * @throws SQLException */ public boolean tableHasForeignKey(String tableName, String refTableName, String columnName, String refColumnName) throws SQLException; /** + * Verify if table already has a FK constraint. + * @param tableName + * @param referenceTableName + * @param keyColumns + * @param referenceColumns + * @return true if described relation exists + * @throws SQLException + */ + boolean tableHasForeignKey(String tableName, String referenceTableName, String[] keyColumns, + String[] referenceColumns) throws SQLException; + + /** * Get a new DB session * @return */ http://git-wip-us.apache.org/repos/asf/ambari/blob/74d037bd/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessorImpl.java ---------------------------------------------------------------------- diff --git a/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessorImpl.java b/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessorImpl.java index 069a4f2..6bff89f 100644 --- a/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessorImpl.java +++ b/ambari-server/src/main/java/org/apache/ambari/server/orm/DBAccessorImpl.java @@ -28,7 +28,6 @@ import org.eclipse.persistence.logging.SessionLogEntry; import org.eclipse.persistence.platform.database.*; import org.eclipse.persistence.sessions.DatabaseLogin; import org.eclipse.persistence.sessions.DatabaseSession; -import org.eclipse.persistence.sessions.Login; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -39,9 +38,9 @@ import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; -import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; +import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.List; @@ -122,6 +121,10 @@ public class DBAccessorImpl implements DBAccessor { } private String convertObjectName(String objectName) throws SQLException { + //tolerate null names for proper usage in filters + if (objectName == null) { + return null; + } DatabaseMetaData metaData = getDatabaseMetaData(); if (metaData.storesLowerCaseIdentifiers()) { return objectName.toLowerCase(); @@ -210,7 +213,12 @@ public class DBAccessorImpl implements DBAccessor { @Override public boolean tableHasForeignKey(String tableName, String refTableName, String columnName, String refColumnName) throws SQLException { - boolean result = false; + return tableHasForeignKey(tableName, refTableName, new String[]{columnName}, new String[]{refColumnName}); + } + + @Override + public boolean tableHasForeignKey(String tableName, String referenceTableName, String[] keyColumns, + String[] referenceColumns) throws SQLException { DatabaseMetaData metaData = getDatabaseMetaData(); String schemaFilter = null; if (getDbType().equals(Configuration.ORACLE_DB_NAME)) { @@ -218,23 +226,56 @@ public class DBAccessorImpl implements DBAccessor { schemaFilter = configuration.getDatabaseUser(); } - ResultSet rs = metaData.getCrossReference(null, schemaFilter, convertObjectName(tableName), - null, schemaFilter, convertObjectName(refTableName)); + //NB: reference table contains pk columns while key table contains fk columns + + ResultSet rs = metaData.getCrossReference(null, convertObjectName(schemaFilter), convertObjectName(referenceTableName), + null, convertObjectName(schemaFilter), convertObjectName(tableName)); + + List<String> pkColumns = new ArrayList<String>(referenceColumns.length); + for (String referenceColumn : referenceColumns) { + pkColumns.add(convertObjectName(referenceColumn)); + } + List<String> fkColumns = new ArrayList<String>(keyColumns.length); + for (String keyColumn : keyColumns) { + fkColumns.add(convertObjectName(keyColumn)); + } if (rs != null) { try { while (rs.next()) { - String refColumn = rs.getString("FKCOLUMN_NAME"); - if (refColumn != null && refColumn.equalsIgnoreCase(refColumnName)) { - result = true; + + String pkColumn = rs.getString("PKCOLUMN_NAME"); + String fkColumn = rs.getString("FKCOLUMN_NAME"); + + int pkIndex = pkColumns.indexOf(pkColumn); + int fkIndex = fkColumns.indexOf(fkColumn); + if (pkIndex != -1 && fkIndex != -1) { + if (pkIndex != fkIndex) { + LOG.warn("Columns for FK constraint should be provided in exact order"); + } else { + pkColumns.remove(pkIndex); + fkColumns.remove(fkIndex); + } + + + } else { + LOG.debug("pkCol={}, fkCol={} not found in provided column names, skipping", pkColumn, fkColumn); //TODO debug } + + } + if (pkColumns.isEmpty() && fkColumns.isEmpty()) { + return true; + } + } finally { rs.close(); } } - return result; + + return false; + } @Override @@ -250,48 +291,33 @@ public class DBAccessorImpl implements DBAccessor { String keyColumn, String referenceTableName, String referenceColumn, boolean ignoreFailure) throws SQLException { - if (!tableHasForeignKey(tableName, referenceTableName, keyColumn, referenceColumn)) { + addFKConstraint(tableName, constraintName, new String[]{keyColumn}, referenceTableName, + new String[]{referenceColumn}, ignoreFailure); + } + @Override + public void addFKConstraint(String tableName, String constraintName, + String[] keyColumns, String referenceTableName, + String[] referenceColumns, boolean ignoreFailure) throws SQLException { + if (!tableHasForeignKey(tableName, referenceTableName, keyColumns, referenceColumns)) { String query = dbmsHelper.getAddForeignKeyStatement(tableName, constraintName, - Collections.singletonList(keyColumn), - referenceTableName, - Collections.singletonList(referenceColumn) + Arrays.asList(keyColumns), + referenceTableName, + Arrays.asList(referenceColumns) ); try { executeQuery(query); } catch (SQLException e) { LOG.warn("Add FK constraint failed" + - ", constraintName = " + constraintName + - ", tableName = " + tableName + ", errorCode = " + e.getErrorCode() + - ", message = " + e.getMessage()); - LOG.debug("Exception on add FK constraint.", e); + ", constraintName = " + constraintName + + ", tableName = " + tableName, e); if (!ignoreFailure) { throw e; } } - } - } - - @Override - public void addFKConstraint(String tableName, String constraintName, - String[] keyColumns, String referenceTableName, - String[] referenceColumns, boolean ignoreFailure) throws SQLException { - String query = dbmsHelper.getAddForeignKeyStatement(tableName, constraintName, - Arrays.asList(keyColumns), - referenceTableName, - Arrays.asList(referenceColumns) - ); - - try { - executeQuery(query); - } catch (SQLException e) { - LOG.warn("Add FK constraint failed" + - ", constraintName = " + constraintName + - ", tableName = " + tableName, e); - if (!ignoreFailure) { - throw e; - } + } else { + LOG.info("Foreign Key constraint {} already exists, skipping", constraintName); } }