From 47766adfb78b3a947e87387ad395129d3728a102 Mon Sep 17 00:00:00 2001 From: "Brian S. O'Neill" Date: Sat, 20 Feb 2010 01:28:00 +0000 Subject: Fix generated delete statement which has joins. --- .../amazon/carbonado/repo/jdbc/JDBCStorage.java | 78 ++++++++++------ .../amazon/carbonado/repo/jdbc/WhereBuilder.java | 100 ++++++++++++++++++--- 2 files changed, 140 insertions(+), 38 deletions(-) (limited to 'src/main') diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java index cbd4fc0..f02924d 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java @@ -395,13 +395,21 @@ class JDBCStorage extends StandardQueryFactory SQLStatementBuilder fromWhereBuilder = new SQLStatementBuilder(mRepository); fromWhereBuilder.append(" FROM"); + SQLStatementBuilder deleteFromWhereBuilder; + if (alias == null) { // Don't bother defining a table alias for one table. jn.appendTableNameTo(selectBuilder); jn.appendTableNameTo(fromWhereBuilder); + deleteFromWhereBuilder = null; } else { jn.appendFullJoinTo(selectBuilder); jn.appendFullJoinTo(fromWhereBuilder); + // Since the delete is operating with joins, need to generate + // a special form that uses WHERE EXISTS. + deleteFromWhereBuilder = new SQLStatementBuilder(mRepository); + deleteFromWhereBuilder.append(" FROM"); + jn.appendTableNameAndAliasTo(deleteFromWhereBuilder); } // Appending where clause. Remainder filter is required if a @@ -435,24 +443,20 @@ class JDBCStorage extends StandardQueryFactory remainderFilter = filter; } else { // Build the WHERE clause only if anything to filter on. - selectBuilder.append(" WHERE "); - fromWhereBuilder.append(" WHERE "); - WhereBuilder wb = new WhereBuilder (selectBuilder, alias == null ? null : jn, aliasGenerator); - FetchException e = sqlFilter.accept(wb, null); - if (e != null) { - throw e; - } + wb.append(sqlFilter); propertyFilters = wb.getPropertyFilters(); propertyFilterNullable = wb.getPropertyFilterNullable(); wb = new WhereBuilder (fromWhereBuilder, alias == null ? null : jn, aliasGenerator); - e = sqlFilter.accept(wb, null); - if (e != null) { - throw e; + wb.append(sqlFilter); + + if (deleteFromWhereBuilder != null) { + wb = new WhereBuilder(deleteFromWhereBuilder, jn, aliasGenerator); + wb.appendExists(sqlFilter); } } } @@ -491,10 +495,18 @@ class JDBCStorage extends StandardQueryFactory } } + SQLStatement selectStatement, fromWhere, deleteFromWhere; + + selectStatement = selectBuilder.build(); + fromWhere = fromWhereBuilder.build(); + deleteFromWhere = deleteFromWhereBuilder == null ? null + : deleteFromWhereBuilder.build(); + QueryExecutor executor = new Executor(filter, sqlOrdering, - selectBuilder.build(), - fromWhereBuilder.build(), + selectStatement, + fromWhere, + deleteFromWhere, propertyFilters, propertyFilterNullable); @@ -559,8 +571,10 @@ class JDBCStorage extends StandardQueryFactory private final SQLStatement mSelectStatement; private final int mMaxSelectStatementLength; - private final SQLStatement mFromWhereStatement; - private final int mMaxFromWhereStatementLength; + private final SQLStatement mFromWhere; + private final int mMaxFromWhereLength; + private final SQLStatement mDeleteFromWhere; + private final int mMaxDeleteFromWhereLength; // The following arrays all have the same length, or they may all be null. @@ -578,7 +592,8 @@ class JDBCStorage extends StandardQueryFactory Executor(Filter filter, OrderingList ordering, SQLStatement selectStatement, - SQLStatement fromWhereStatement, + SQLStatement fromWhere, + SQLStatement deleteFromWhere, PropertyFilter[] propertyFilters, boolean[] propertyFilterNullable) throws RepositoryException @@ -588,8 +603,17 @@ class JDBCStorage extends StandardQueryFactory mSelectStatement = selectStatement; mMaxSelectStatementLength = selectStatement.maxLength(); - mFromWhereStatement = fromWhereStatement; - mMaxFromWhereStatementLength = fromWhereStatement.maxLength(); + + mFromWhere = fromWhere; + mMaxFromWhereLength = fromWhere.maxLength(); + + if (deleteFromWhere == null) { + mDeleteFromWhere = mFromWhere; + mMaxDeleteFromWhereLength = mMaxFromWhereLength; + } else { + mDeleteFromWhere = deleteFromWhere; + mMaxDeleteFromWhereLength = deleteFromWhere.maxLength(); + } if (propertyFilters == null) { mPropertyFilters = null; @@ -876,19 +900,19 @@ class JDBCStorage extends StandardQueryFactory return b.toString(); } - private String prepareDelete(FilterValues filterValues) { - // Allocate with extra room for "DELETE" - StringBuilder b = new StringBuilder(6 + mMaxFromWhereStatementLength); - b.append("DELETE"); - mFromWhereStatement.appendTo(b, filterValues); - return b.toString(); - } - private String prepareCount(FilterValues filterValues) { // Allocate with extra room for "SELECT COUNT(*)" - StringBuilder b = new StringBuilder(15 + mMaxFromWhereStatementLength); + StringBuilder b = new StringBuilder(15 + mMaxFromWhereLength); b.append("SELECT COUNT(*)"); - mFromWhereStatement.appendTo(b, filterValues); + mFromWhere.appendTo(b, filterValues); + return b.toString(); + } + + private String prepareDelete(FilterValues filterValues) { + // Allocate with extra room for "DELETE" + StringBuilder b = new StringBuilder(6 + mMaxDeleteFromWhereLength); + b.append("DELETE"); + mDeleteFromWhere.appendTo(b, filterValues); return b.toString(); } diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/WhereBuilder.java b/src/main/java/com/amazon/carbonado/repo/jdbc/WhereBuilder.java index 9023397..b350e55 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/WhereBuilder.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/WhereBuilder.java @@ -53,6 +53,8 @@ class WhereBuilder extends Visitor mPropertyFilterNullable; /** + * @param statementBuilder destination for WHERE statement + * @param jn pass null if no alias is to be used * @param aliasGenerator used for supporting "EXISTS" filter */ WhereBuilder(SQLStatementBuilder statementBuilder, JoinNode jn, @@ -65,6 +67,79 @@ class WhereBuilder extends Visitor(); } + public void append(Filter filter) throws FetchException { + mStatementBuilder.append(" WHERE "); + FetchException e = filter.accept(this, null); + if (e != null) { + throw e; + } + } + + /** + * Generate SQL of the form "WHERE EXISTS (SELECT * FROM ...)" This is + * necessary for DELETE statements which operate against joined tables. + */ + public void appendExists(Filter filter) throws FetchException { + mStatementBuilder.append(" WHERE "); + mStatementBuilder.append("EXISTS (SELECT * FROM"); + + JDBCStorableInfo info; + + final JDBCRepository repo = mStatementBuilder.getRepository(); + try { + info = repo.examineStorable(filter.getStorableType()); + } catch (RepositoryException e) { + throw repo.toFetchException(e); + } + + JoinNode jn; + try { + JoinNodeBuilder jnb = + new JoinNodeBuilder(repo, info, mAliasGenerator); + filter.accept(jnb, null); + jn = jnb.getRootJoinNode(); + } catch (UndeclaredThrowableException e) { + throw repo.toFetchException(e); + } + + jn.appendFullJoinTo(mStatementBuilder); + + mStatementBuilder.append(" WHERE "); + + { + int i = 0; + for (JDBCStorableProperty property : info.getPrimaryKeyProperties().values()) { + if (i > 0) { + mStatementBuilder.append(" AND "); + } + mStatementBuilder.append(mJoinNode.getAlias()); + mStatementBuilder.append('.'); + mStatementBuilder.append(property.getColumnName()); + mStatementBuilder.append('='); + mStatementBuilder.append(jn.getAlias()); + mStatementBuilder.append('.'); + mStatementBuilder.append(property.getColumnName()); + i++; + } + } + + mStatementBuilder.append(" AND ("); + + WhereBuilder wb = new WhereBuilder(mStatementBuilder, jn, mAliasGenerator); + + FetchException e = filter.accept(wb, null); + if (e != null) { + throw e; + } + + // Transfer property filters so that the values are extracted properly. + mPropertyFilters.addAll(wb.mPropertyFilters); + mPropertyFilterNullable.addAll(wb.mPropertyFilterNullable); + + mStatementBuilder.append(')'); + mStatementBuilder.append(')'); + } + @SuppressWarnings("unchecked") public PropertyFilter[] getPropertyFilters() { return mPropertyFilters.toArray(new PropertyFilter[mPropertyFilters.size()]); @@ -192,18 +267,21 @@ class WhereBuilder extends Visitor 0) { - mStatementBuilder.append(" AND "); + { + String alias = mJoinNode.findAliasFor(chained); + int count = oneToMany.getJoinElementCount(); + for (int i=0; i 0) { + mStatementBuilder.append(" AND "); + } + mStatementBuilder.append(oneToManyNode.getAlias()); + mStatementBuilder.append('.'); + mStatementBuilder.append(oneToMany.getInternalJoinElement(i).getColumnName()); + mStatementBuilder.append('='); + mStatementBuilder.append(alias); + mStatementBuilder.append('.'); + mStatementBuilder.append(oneToMany.getExternalJoinElement(i).getColumnName()); } - mStatementBuilder.append(oneToManyNode.getAlias()); - mStatementBuilder.append('.'); - mStatementBuilder.append(oneToMany.getInternalJoinElement(i).getColumnName()); - mStatementBuilder.append('='); - mStatementBuilder.append(mJoinNode.findAliasFor(chained)); - mStatementBuilder.append('.'); - mStatementBuilder.append(oneToMany.getExternalJoinElement(i).getColumnName()); } if (subFilter != null && !subFilter.isOpen()) { -- cgit v1.2.3