From 47766adfb78b3a947e87387ad395129d3728a102 Mon Sep 17 00:00:00 2001
From: "Brian S. O'Neill" <bronee@gmail.com>
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/java/com')
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<S extends Storable> extends StandardQueryFactory<S>
SQLStatementBuilder<S> fromWhereBuilder = new SQLStatementBuilder<S>(mRepository);
fromWhereBuilder.append(" FROM");
+ SQLStatementBuilder<S> 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<S>(mRepository);
+ deleteFromWhereBuilder.append(" FROM");
+ jn.appendTableNameAndAliasTo(deleteFromWhereBuilder);
}
// Appending where clause. Remainder filter is required if a
@@ -435,24 +443,20 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
remainderFilter = filter;
} else {
// Build the WHERE clause only if anything to filter on.
- selectBuilder.append(" WHERE ");
- fromWhereBuilder.append(" WHERE ");
-
WhereBuilder<S> wb = new WhereBuilder<S>
(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<S>
(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<S>(deleteFromWhereBuilder, jn, aliasGenerator);
+ wb.appendExists(sqlFilter);
}
}
}
@@ -491,10 +495,18 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
}
}
+ SQLStatement<S> selectStatement, fromWhere, deleteFromWhere;
+
+ selectStatement = selectBuilder.build();
+ fromWhere = fromWhereBuilder.build();
+ deleteFromWhere = deleteFromWhereBuilder == null ? null
+ : deleteFromWhereBuilder.build();
+
QueryExecutor<S> executor = new Executor(filter,
sqlOrdering,
- selectBuilder.build(),
- fromWhereBuilder.build(),
+ selectStatement,
+ fromWhere,
+ deleteFromWhere,
propertyFilters,
propertyFilterNullable);
@@ -559,8 +571,10 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
private final SQLStatement<S> mSelectStatement;
private final int mMaxSelectStatementLength;
- private final SQLStatement<S> mFromWhereStatement;
- private final int mMaxFromWhereStatementLength;
+ private final SQLStatement<S> mFromWhere;
+ private final int mMaxFromWhereLength;
+ private final SQLStatement<S> 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<S extends Storable> extends StandardQueryFactory<S>
Executor(Filter<S> filter,
OrderingList<S> ordering,
SQLStatement<S> selectStatement,
- SQLStatement<S> fromWhereStatement,
+ SQLStatement<S> fromWhere,
+ SQLStatement<S> deleteFromWhere,
PropertyFilter<S>[] propertyFilters,
boolean[] propertyFilterNullable)
throws RepositoryException
@@ -588,8 +603,17 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
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<S extends Storable> extends StandardQueryFactory<S>
return b.toString();
}
- private String prepareDelete(FilterValues<S> 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<S> 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<S> 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<S extends Storable> extends Visitor<S, FetchException, Object
private List<Boolean> 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<S extends Storable> extends Visitor<S, FetchException, Object
mPropertyFilterNullable = new ArrayList<Boolean>();
}
+ public void append(Filter<S> 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<S> filter) throws FetchException {
+ mStatementBuilder.append(" WHERE ");
+ mStatementBuilder.append("EXISTS (SELECT * FROM");
+
+ JDBCStorableInfo<S> 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<S> 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<S> wb = new WhereBuilder<S>(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<S>[] getPropertyFilters() {
return mPropertyFilters.toArray(new PropertyFilter[mPropertyFilters.size()]);
@@ -192,18 +267,21 @@ class WhereBuilder<S extends Storable> extends Visitor<S, FetchException, Object
mStatementBuilder.append(" WHERE ");
- int count = oneToMany.getJoinElementCount();
- for (int i=0; i<count; i++) {
- if (i > 0) {
- mStatementBuilder.append(" AND ");
+ {
+ String alias = mJoinNode.findAliasFor(chained);
+ int count = oneToMany.getJoinElementCount();
+ for (int i=0; i<count; i++) {
+ if (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