Fix generated delete statement which has joins.

2 files changed, 140 insertions, 38 deletions

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()) {