================================================================================ Merge Diffs: /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/expressions/SQLDeleteAllStatement.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.7 Report generated at Fri Oct 20 16:46:52 2006 -------------------------------------------------------------------------------- *** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.7 Fri Oct 20 16:06:32 2006 --- /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/expressions/SQLDeleteAllStatement.java Fri Oct 20 16:46:52 2006 *************** *** 52,57 **** --- 52,59 ---- protected Vector aliasedFields; protected Vector originalFields; + protected boolean shouldExtractWhereClauseFromSelectCallForExist; + public void setSelectCallForExist(SQLCall selectCallForExist) { this.selectCallForExist = selectCallForExist; } *************** *** 77,88 **** return tableAliasInSelectCallForNotExist; } public void setPrimaryKeyFieldsForAutoJoin(Collection primaryKeyFields) { ! if(primaryKeyFields instanceof Vector) { ! setOriginalFieldsForJoin((Vector)primaryKeyFields); } else { ! setOriginalFieldsForJoin(new Vector(primaryKeyFields)); } - setAliasedFieldsForJoin((Vector)getOriginalFieldsForJoin().clone()); } public void setOriginalFieldsForJoin(Vector originalFields) { this.originalFields = originalFields; --- 79,95 ---- return tableAliasInSelectCallForNotExist; } public void setPrimaryKeyFieldsForAutoJoin(Collection primaryKeyFields) { ! if(primaryKeyFields != null) { ! if(primaryKeyFields instanceof Vector) { ! setOriginalFieldsForJoin((Vector)primaryKeyFields); ! } else { ! setOriginalFieldsForJoin(new Vector(primaryKeyFields)); ! } ! setAliasedFieldsForJoin((Vector)getOriginalFieldsForJoin().clone()); } else { ! setOriginalFieldsForJoin(null); ! setAliasedFieldsForJoin(null); } } public void setOriginalFieldsForJoin(Vector originalFields) { this.originalFields = originalFields; *************** *** 102,110 **** public Expression getInheritanceExpression() { return inheritanceExpression; } /** ! * Append the string containing the SQL insert string for the given table. */ public DatabaseCall buildCall(AbstractSession session) { SQLCall call = (SQLCall)super.buildCall(session); --- 109,124 ---- public Expression getInheritanceExpression() { return inheritanceExpression; } + + public void setShouldExtractWhereClauseFromSelectCallForExist(boolean shouldExtractWhereClauseFromSelectCallForExist) { + this.shouldExtractWhereClauseFromSelectCallForExist = shouldExtractWhereClauseFromSelectCallForExist; + } + public boolean shouldExtractWhereClauseFromSelectCallForExist() { + return shouldExtractWhereClauseFromSelectCallForExist; + } /** ! * Return SQL call for the statement, through generating the SQL string. */ public DatabaseCall buildCall(AbstractSession session) { SQLCall call = (SQLCall)super.buildCall(session); *************** *** 117,134 **** boolean whereWasPrinted = true; if(selectCallForExist != null) { ! writer.write(" WHERE EXISTS("); ! // EXIST Example: selectCall.sqlString: ! // "SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (((t0.F_NAME LIKE 'a') AND (t1.SALARY = 0)) AND (t1.EMP_ID = t0.EMP_ID))" ! writeSelect(writer, selectCallForExist, tableAliasInSelectCallForExist, call); ! // closing bracket for EXISTS ! writer.write(")"); } else if (inheritanceExpression != null) { writer.write(" WHERE "); // Example: (PROJ_TYPE = 'L') ExpressionSQLPrinter printer = new ExpressionSQLPrinter(session, getTranslationRow(), call, false); printer.setWriter(writer); printer.printExpression(inheritanceExpression); } else { whereWasPrinted = false; } --- 131,164 ---- boolean whereWasPrinted = true; if(selectCallForExist != null) { ! if(shouldExtractWhereClauseFromSelectCallForExist) { ! // Should get here only in case selectCallForExist doesn't have aliases and ! // targets the same table as the statement. ! // Instead of making selectCallForExist part of " WHERE EXIST(" ! // just extract its where clause. ! // Example: selectCallForExist.sqlString: ! // "SELECT PROJ_ID FROM PROJECT WHERE (LEADER_ID IS NULL) ! whereWasPrinted = writeWhere(writer, selectCallForExist, call); ! // The result is: ! // "WHERE (LEADER_ID IS NULL)" ! } else { ! writer.write(" WHERE EXISTS("); ! // EXIST Example: selectCallForExist.sqlString: ! // "SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (((t0.F_NAME LIKE 'a') AND (t1.SALARY = 0)) AND (t1.EMP_ID = t0.EMP_ID))" ! writeSelect(writer, selectCallForExist, tableAliasInSelectCallForExist, call); ! // closing bracket for EXISTS ! writer.write(")"); ! // The result is (target table is SALARY): ! // "WHERE EXISTS(SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (((t0.F_NAME LIKE 'a') AND (t1.SALARY = 0)) AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = SALARY.EMP_ID)" ! } } else if (inheritanceExpression != null) { writer.write(" WHERE "); // Example: (PROJ_TYPE = 'L') ExpressionSQLPrinter printer = new ExpressionSQLPrinter(session, getTranslationRow(), call, false); printer.setWriter(writer); printer.printExpression(inheritanceExpression); + // The result is: + // "(PROJ_TYPE = 'L')" } else { whereWasPrinted = false; } *************** *** 145,150 **** --- 175,182 ---- writeSelect(writer, selectCallForNotExist, tableAliasInSelectCallForNotExist, call); // closing bracket for EXISTS writer.write(")"); + // The result is (target table is EMPLOYEE): + // "WHERE NOT EXISTS(SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE ((t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = EMPLOYEE.EMP_ID)" } call.setSQLString(writer.toString()); *************** *** 157,168 **** } protected void writeSelect(Writer writer, SQLCall selectCall, String tableAliasInSelectCall, SQLCall call) throws IOException { ! writer.write(selectCall.getSQLString()); ! // join ! // Example: AND t0.EMP_ID = EMP_ID for(int i=0; i < originalFields.size(); i++) { ! writer.write(" AND "); if(tableAliasInSelectCall != null) { writer.write(tableAliasInSelectCall); writer.write('.'); --- 189,210 ---- } protected void writeSelect(Writer writer, SQLCall selectCall, String tableAliasInSelectCall, SQLCall call) throws IOException { ! String str = selectCall.getSQLString(); ! writer.write(str); ! ! boolean hasWhereClause = str.toUpperCase().indexOf(" WHERE ") >= 0; ! // join aliased fields to original fields ! // Examples: ! // table aliase provided: AND t0.EMP_ID = EMPLOYEE.EMP_ID ! // table aliase not provided: AND EMP_ID = EMPLOYEE.EMP_ID for(int i=0; i < originalFields.size(); i++) { ! if(i==0 && !hasWhereClause) { ! // there is no where clause - should print WHERE ! writer.write(" WHERE "); ! } else { ! writer.write(" AND "); ! } if(tableAliasInSelectCall != null) { writer.write(tableAliasInSelectCall); writer.write('.'); *************** *** 173,180 **** writer.write('.'); writer.write(((DatabaseField)originalFields.elementAt(i)).getName()); } ! call.getParameters().addAll(selectCall.getParameters()); call.getParameterTypes().addAll(selectCall.getParameterTypes()); } } --- 215,243 ---- writer.write('.'); writer.write(((DatabaseField)originalFields.elementAt(i)).getName()); } ! ! // add parameters ! call.getParameters().addAll(selectCall.getParameters()); ! call.getParameterTypes().addAll(selectCall.getParameterTypes()); ! } ! ! protected boolean writeWhere(Writer writer, SQLCall selectCall, SQLCall call) throws IOException { ! String selectStr = selectCallForExist.getSQLString(); ! ! int index = selectStr.toUpperCase().indexOf(" WHERE "); ! if(index < 0) { ! // no where clause - nothing to do ! return false; ! } ! ! // print the where clause ! String str = selectStr.substring(index); ! writer.write(str); ! ! // add parameters call.getParameters().addAll(selectCall.getParameters()); call.getParameterTypes().addAll(selectCall.getParameterTypes()); + + return true; } } ================================================================================ Merge Diffs: /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/queryframework/ExpressionQueryMechanism.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.29 Report generated at Fri Oct 20 16:46:52 2006 -------------------------------------------------------------------------------- *** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.29 Fri Oct 20 16:07:35 2006 --- /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/queryframework/ExpressionQueryMechanism.java Fri Oct 20 16:46:52 2006 *************** *** 210,229 **** SQLCall selectCallForNotExist, SQLSelectStatement selectStatementForNotExist, Collection primaryKeyFields) { if(selectCallForExist == null && selectCallForNotExist == null) { ! return buildDeleteAllStatement(table, inheritanceExpression); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); deleteAllStatement.setTable(table); deleteAllStatement.setTranslationRow(getTranslationRow()); - if(inheritanceExpression != null) { - deleteAllStatement.setInheritanceExpression((Expression)inheritanceExpression.clone()); - } - if(selectCallForExist != null) { deleteAllStatement.setSelectCallForExist(selectCallForExist); deleteAllStatement.setTableAliasInSelectCallForExist(getAliasTableName(selectStatementForExist, table)); } if(selectCallForNotExist != null) { --- 210,239 ---- SQLCall selectCallForNotExist, SQLSelectStatement selectStatementForNotExist, Collection primaryKeyFields) { if(selectCallForExist == null && selectCallForNotExist == null) { ! return buildDeleteStatementForDeleteAllQuery(table, inheritanceExpression); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); deleteAllStatement.setTable(table); deleteAllStatement.setTranslationRow(getTranslationRow()); if(selectCallForExist != null) { deleteAllStatement.setSelectCallForExist(selectCallForExist); + // if selectStatementForExist doesn't require aliasing and targets the same + // table as the statement to be built, + // then instead of creating sql with "WHERE EXISTS(" + // sql is created by extracting where clause from selectStatementForExist, + // for instance: + // DELETE FROM PROJECT WHERE (PROJ_NAME = ?) + // instead of the wrong one: + // DELETE FROM PROJECT WHERE EXISTS(SELECT PROJ_ID FROM PROJECT WHERE (PROJ_NAME = ?) AND PROJECT.PROJ_ID = PROJECT.PROJ_ID) + deleteAllStatement.setShouldExtractWhereClauseFromSelectCallForExist(!selectStatementForExist.requiresAliases() && table.equals(selectStatementForExist.getTables().firstElement())); deleteAllStatement.setTableAliasInSelectCallForExist(getAliasTableName(selectStatementForExist, table)); + } else { + // inheritanceExpression is irrelevant in case selectCallForExist != null + if(inheritanceExpression != null) { + deleteAllStatement.setInheritanceExpression((Expression)inheritanceExpression.clone()); + } } if(selectCallForNotExist != null) { *************** *** 236,245 **** return deleteAllStatement; } ! protected SQLDeleteStatement buildSQLDeleteAllStatementForMapping(SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, Vector sourceFields, Vector targetFields) { DatabaseTable targetTable = ((DatabaseField)targetFields.firstElement()).getTable(); if(selectCallForExist == null) { ! return buildDeleteAllStatement(targetTable); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); --- 246,255 ---- return deleteAllStatement; } ! protected SQLDeleteStatement buildDeleteAllStatementForMapping(SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, Vector sourceFields, Vector targetFields) { DatabaseTable targetTable = ((DatabaseField)targetFields.firstElement()).getTable(); if(selectCallForExist == null) { ! return buildDeleteStatementForDeleteAllQuery(targetTable); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); *************** *** 315,347 **** } } ! protected SQLDeleteStatement buildDeleteAllStatement() { ! return buildDeleteAllStatement(getDescriptor().getDefaultTable()); ! } ! ! protected SQLDeleteStatement buildDeleteAllStatement(Expression inheritanceExpression) { ! return buildDeleteAllStatement(getDescriptor().getDefaultTable(), inheritanceExpression); ! } ! ! protected SQLDeleteStatement buildDeleteAllStatement(DatabaseTable table) { ! return buildDeleteAllStatement(table, null); } ! protected SQLDeleteStatement buildDeleteAllStatement(DatabaseTable table, Expression inheritanceExpression) { SQLDeleteStatement deleteStatement = new SQLDeleteStatement(); - deleteStatement.setWhereClause(getSelectionCriteria()); - if(deleteStatement.getWhereClause() != null) { - deleteStatement.setWhereClause((Expression)deleteStatement.getWhereClause().clone()); - deleteStatement.getWhereClause().getBuilder().setSession(getSession()); - deleteStatement.getWhereClause().getBuilder().setQueryClass(getQuery().getReferenceClass()); - } if(inheritanceExpression != null) { ! if(deleteStatement.getWhereClause() != null) { ! deleteStatement.setWhereClause(deleteStatement.getWhereClause().and(inheritanceExpression)); ! } else { ! deleteStatement.setWhereClause((Expression)inheritanceExpression.clone()); ! } } deleteStatement.setTable(table); deleteStatement.setTranslationRow(getTranslationRow()); --- 325,347 ---- } } ! /** ! * Used by DeleteAllQuery to create DeleteStatement in a simple case ! * when selectionCriteria==null. ! */ ! protected SQLDeleteStatement buildDeleteStatementForDeleteAllQuery(DatabaseTable table) { ! return buildDeleteStatementForDeleteAllQuery(table, null); } ! /** ! * Used by DeleteAllQuery to create DeleteStatement in a simple case ! * when selectionCriteria==null. ! */ ! protected SQLDeleteStatement buildDeleteStatementForDeleteAllQuery(DatabaseTable table, Expression inheritanceExpression) { SQLDeleteStatement deleteStatement = new SQLDeleteStatement(); if(inheritanceExpression != null) { ! deleteStatement.setWhereClause((Expression)inheritanceExpression.clone()); } deleteStatement.setTable(table); deleteStatement.setTranslationRow(getTranslationRow()); *************** *** 938,943 **** --- 938,946 ---- } } + // cache the flag - used many times + boolean hasInheritance = getDescriptor().hasInheritance(); + if(!tablesInInsertOrder.isEmpty()) { Expression whereClause = getSelectionCriteria(); *************** *** 947,960 **** SQLSelectStatement selectStatementForNotExist = null; SQLCall selectCallForNotExist = null; Expression inheritanceExpression = null; ! if(getDescriptor().hasInheritance()) { ! if(getDescriptor().getInheritancePolicy().shouldReadSubclasses()) { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getWithAllSubclassesExpression(); ! } else { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getOnlyInstancesExpression(); ! } } SQLSelectStatement selectStatementForExist = createSQLSelectStatementForModifyAll(whereClause); // Main Case: Descriptor is mapped to more than one table and/or the query references other tables --- 950,969 ---- SQLSelectStatement selectStatementForNotExist = null; SQLCall selectCallForNotExist = null; + + // inheritanceExpression is always null in a nested method call. Expression inheritanceExpression = null; ! if(tablesToIgnore == null) { ! // It's original (not a nested) method call. ! if(hasInheritance) { ! if(getDescriptor().getInheritancePolicy().shouldReadSubclasses()) { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getWithAllSubclassesExpression(); ! } else { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getOnlyInstancesExpression(); ! } ! } } + SQLSelectStatement selectStatementForExist = createSQLSelectStatementForModifyAll(whereClause); // Main Case: Descriptor is mapped to more than one table and/or the query references other tables *************** *** 969,976 **** } } - selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); - if(isSelectCallForNotExistRequired) { selectStatementForNotExist = createSQLSelectStatementForModifyAll(null, null); selectCallForNotExist = (SQLCall)selectStatementForNotExist.buildCall(getSession()); --- 978,983 ---- *************** *** 981,987 **** // currently DeleteAll using Oracle anonymous block is not implemented if(!getSession().getPlatform().isOracle()) { // there are at least two storeys of inheritance above the referenceClass ! if(getDescriptor().hasInheritance() && getDescriptor().getTables().size() > 2) { prepareDeleteAllUsingTempStorage(); return; } --- 988,994 ---- // currently DeleteAll using Oracle anonymous block is not implemented if(!getSession().getPlatform().isOracle()) { // there are at least two storeys of inheritance above the referenceClass ! if(hasInheritance && getDescriptor().getTables().size() > 2) { prepareDeleteAllUsingTempStorage(); return; } *************** *** 989,995 **** } } } ! if(isMainCase) { // Main case: Descriptor is mapped to more than one table and/or the query references other tables // --- 996,1008 ---- } } } ! ! // Don't use selectCallForExist in case there is no whereClause - ! // a simpler sql will be created if possible. ! if(whereClause != null) { ! selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); ! } ! if(isMainCase) { // Main case: Descriptor is mapped to more than one table and/or the query references other tables // *************** *** 1009,1024 **** // The situation is a bit more complex if more than two levels of inheritance is involved: // both "EXISTS" and "NOT EXISTS" used for the "intermediate" (not first and not last) tables. ! if(tablesToIgnore != null) { ! // isSelectCallForNotExistRequired==false: it's a nested method call. // In Employee example, query with reference class // Project will get here to handle LPROJECT table ! // Should NOT pass inheritanceExpression - it could only be used with the highest in insertion order table. ! // Because it's a nested call the table can't be first in insertion order. ! deleteStatement = buildDeleteAllStatement(table, null, selectCallForExist, selectStatementForExist, null, null, primaryKeyFields); ! } else if (tablesInInsertOrder.size() == 1) { ! // isSelectCallForNotExistRequired==false: original call, single table. ! // Handles single table inheritance. If there's no inheritance identical to the one above. deleteStatement = buildDeleteAllStatement(table, inheritanceExpression, selectCallForExist, selectStatementForExist, null, null, primaryKeyFields); } else { // isSelectCallForNotExistRequired==true: original call, multiple tables. --- 1022,1037 ---- // The situation is a bit more complex if more than two levels of inheritance is involved: // both "EXISTS" and "NOT EXISTS" used for the "intermediate" (not first and not last) tables. ! if(!isSelectCallForNotExistRequired) { ! // isSelectCallForNotExistRequired == false: ! // either tablesToIgnore != null: it's a nested method call. ! // Example: // In Employee example, query with reference class // Project will get here to handle LPROJECT table ! // or tablesInInsertOrder.size() == 1: there is only one table, ! // but there is joining to at least one other table (otherwise would've been isMainCase==false). ! // ! // Note that buildDeleteAllStatement ignores inheritanceExpression if selectCallForExist!=null. deleteStatement = buildDeleteAllStatement(table, inheritanceExpression, selectCallForExist, selectStatementForExist, null, null, primaryKeyFields); } else { // isSelectCallForNotExistRequired==true: original call, multiple tables. *************** *** 1110,1120 **** // In Employee example, query with reference class: // Project will build a simple sql call for PROJECT(and will make nested method calls for LargeProject and SmallProject); // SmallProject will build a simple sql call for PROJECT ! setSQLStatement(buildDeleteAllStatement(inheritanceExpression)); } // Add statements for ManyToMany and DirectCollection mappings ! Vector deleteStatementsForMappings = buildDeleteAllStatementsForMappings(inheritanceExpression, selectCallForExist, selectStatementForExist, tablesToIgnore == null); if(!deleteStatementsForMappings.isEmpty()) { if(getSQLStatement() != null) { getSQLStatements().add(getSQLStatement()); --- 1123,1149 ---- // In Employee example, query with reference class: // Project will build a simple sql call for PROJECT(and will make nested method calls for LargeProject and SmallProject); // SmallProject will build a simple sql call for PROJECT ! setSQLStatement(buildDeleteAllStatement(getDescriptor().getDefaultTable(), inheritanceExpression, selectCallForExist, selectStatementForExist, null, null, null)); ! } ! ! if(selectCallForExist == null) { ! // Getting there means there is no whereClause. ! // To handle the mappings selectCallForExist may be required in this case, too. ! if(hasInheritance && (tablesToIgnore != null || inheritanceExpression != null)) { ! // The only case NOT to create the call for no whereClause is either no inheritance, ! // or it's an original (not a nested) method call and there is no inheritance expression. ! // In Employee example: ! // query with reference class Project and no where clause for m-to-m mapping generates: ! // DELETE FROM EMP_PROJ; ! // as opposed to query with reference class SmallProject: ! // DELETE FROM EMP_PROJ WHERE EXISTS(SELECT PROJ_ID FROM PROJECT WHERE (PROJ_TYPE = ?) AND PROJ_ID = EMP_PROJ.PROJ_ID). ! // ! selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); ! } } // Add statements for ManyToMany and DirectCollection mappings ! Vector deleteStatementsForMappings = buildDeleteAllStatementsForMappings(selectCallForExist, selectStatementForExist, tablesToIgnore == null); if(!deleteStatementsForMappings.isEmpty()) { if(getSQLStatement() != null) { getSQLStatements().add(getSQLStatement()); *************** *** 1125,1131 **** } // Indicates whether the descriptor has children using extra tables. ! boolean hasChildrenWithExtraTables = getDescriptor().hasInheritance() && getDescriptor().getInheritancePolicy().hasChildren() && getDescriptor().getInheritancePolicy().hasMultipleTableChild(); // TBD: should we ignore subclasses in case descriptor doesn't want us to read them in? //** Currently in this code we do ignore. --- 1154,1160 ---- } // Indicates whether the descriptor has children using extra tables. ! boolean hasChildrenWithExtraTables = hasInheritance && getDescriptor().getInheritancePolicy().hasChildren() && getDescriptor().getInheritancePolicy().hasMultipleTableChild(); // TBD: should we ignore subclasses in case descriptor doesn't want us to read them in? //** Currently in this code we do ignore. *************** *** 1212,1218 **** // Create SQLDeleteAllStatements for mappings that may be responsible for references // to the objects to be deleted // in the tables NOT mapped to any class: ManyToManyMapping and DirectCollectionMapping ! protected Vector buildDeleteAllStatementsForMappings(Expression inheritanceExpression, SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, boolean dontCheckDescriptor) { Vector deleteStatements = new Vector(); Iterator itMappings = getDescriptor().getMappings().iterator(); while(itMappings.hasNext()) { --- 1241,1247 ---- // Create SQLDeleteAllStatements for mappings that may be responsible for references // to the objects to be deleted // in the tables NOT mapped to any class: ManyToManyMapping and DirectCollectionMapping ! protected Vector buildDeleteAllStatementsForMappings(SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, boolean dontCheckDescriptor) { Vector deleteStatements = new Vector(); Iterator itMappings = getDescriptor().getMappings().iterator(); while(itMappings.hasNext()) { *************** *** 1228,1239 **** sourceFields = ((DirectCollectionMapping)mapping).getSourceKeyFields(); targetFields = ((DirectCollectionMapping)mapping).getReferenceKeyFields(); } ! if(selectCallForExist == null) { ! if(inheritanceExpression != null || getSelectionCriteria() != null) { ! selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); ! } ! } ! deleteStatements.addElement(buildSQLDeleteAllStatementForMapping(selectCallForExist, selectStatementForExist, sourceFields, targetFields)); } } } --- 1257,1263 ---- sourceFields = ((DirectCollectionMapping)mapping).getSourceKeyFields(); targetFields = ((DirectCollectionMapping)mapping).getReferenceKeyFields(); } ! deleteStatements.addElement(buildDeleteAllStatementForMapping(selectCallForExist, selectStatementForExist, sourceFields, targetFields)); } } } ================================================================================ Merge Diffs: /ade/ailitche_toplink_main/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/cmp3/advanced/EntityManagerJUnitTestSuite.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000003/AB0952363AC40CBFE034080020E8C54E.54 Report generated at Fri Oct 20 16:46:52 2006 -------------------------------------------------------------------------------- *** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000003/AB0952363AC40CBFE034080020E8C54E.54 Fri Oct 20 16:08:48 2006 --- /ade/ailitche_toplink_main/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/cmp3/advanced/EntityManagerJUnitTestSuite.java Fri Oct 20 16:46:52 2006 *************** *** 66,72 **** import oracle.toplink.essentials.testing.models.cmp3.advanced.LargeProject; import oracle.toplink.essentials.testing.models.cmp3.advanced.PhoneNumber; ! import oracle.toplink.essentials.testing.models.cmp3.advanced.Project; import oracle.toplink.essentials.threetier.ReadConnectionPool; import oracle.toplink.essentials.threetier.ServerSession; --- 66,72 ---- import oracle.toplink.essentials.testing.models.cmp3.advanced.LargeProject; import oracle.toplink.essentials.testing.models.cmp3.advanced.PhoneNumber; ! import oracle.toplink.essentials.testing.models.cmp3.advanced.SmallProject; import oracle.toplink.essentials.testing.models.cmp3.advanced.Project; import oracle.toplink.essentials.threetier.ReadConnectionPool; import oracle.toplink.essentials.threetier.ServerSession; *************** *** 2893,2898 **** --- 2893,2992 ---- em.remove(initialAddress); em.getTransaction().commit(); } + //bug gf674 - EJBQL delete query with IS NULL in WHERE clause produces wrong sql + public void testDeleteAllPhonesWithNullOwner() { + EntityManager em = createEntityManager(); + em.getTransaction().begin(); + try { + em.createQuery("DELETE FROM PhoneNumber ph WHERE ph.owner IS NULL").executeUpdate(); + } catch (Exception e) { + fail("Exception thrown: " + e.getClass()); + } finally { + em.getTransaction().rollback(); + em.close(); + } + } + public void testDeleteAllProjectsWithNullTeamLeader() { + internalDeleteAllProjectsWithNullTeamLeader("Project"); + } + public void testDeleteAllSmallProjectsWithNullTeamLeader() { + internalDeleteAllProjectsWithNullTeamLeader("SmallProject"); + } + public void testDeleteAllLargeProjectsWithNullTeamLeader() { + internalDeleteAllProjectsWithNullTeamLeader("LargeProject"); + } + protected void internalDeleteAllProjectsWithNullTeamLeader(String className) { + String name = "testDeleteAllProjectsWithNull"; + + // setup + SmallProject sp = new SmallProject(); + sp.setName(name); + LargeProject lp = new LargeProject(); + lp.setName(name); + EntityManager em = createEntityManager(); + try { + em.getTransaction().begin(); + // make sure there are no pre-existing objects with this name + em.createQuery("DELETE FROM "+className+" p WHERE p.name = '"+name+"'").executeUpdate(); + em.persist(sp); + em.persist(lp); + em.getTransaction().commit(); + } catch (RuntimeException ex){ + em.getTransaction().rollback(); + throw ex; + } finally { + em.close(); + } + + // test + em = createEntityManager(); + em.getTransaction().begin(); + try { + em.createQuery("DELETE FROM "+className+" p WHERE p.name = '"+name+"' AND p.teamLeader IS NULL").executeUpdate(); + em.getTransaction().commit(); + } catch (Exception e) { + em.getTransaction().rollback(); + fail("Exception thrown: " + e.getClass()); + } finally { + em.close(); + } + + // verify + String error = null; + em = createEntityManager(); + List result = em.createQuery("SELECT OBJECT(p) FROM Project p WHERE p.name = '"+name+"'").getResultList(); + if(result.isEmpty()) { + if(!className.equals("Project")) { + error = "Target Class " + className +": no objects left"; + } + } else { + if(result.size() > 1) { + error = "Target Class " + className +": too many objects left: " + result.size(); + } else { + Project p = (Project)result.get(0); + if(p.getClass().getName().endsWith(className)) { + error = "Target Class " + className +": object of wrong type left: " + p.getClass().getName(); + } + } + } + + // clean up + try { + em.getTransaction().begin(); + // make sure there are no pre-existing objects with this name + em.createQuery("DELETE FROM "+className+" p WHERE p.name = '"+name+"'").executeUpdate(); + em.getTransaction().commit(); + } catch (RuntimeException ex){ + em.getTransaction().rollback(); + throw ex; + } finally { + em.close(); + } + + if(error != null) { + fail(error); + } + } public static void main(String[] args) { // Now run JUnit. ================================================================================ Merge Diffs: /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/expressions/SQLDeleteAllStatement.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.7 Report generated at Fri Oct 20 16:48:44 2006 -------------------------------------------------------------------------------- *** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000001/AB0952363AC40CBFE034080020E8C54E.7 Fri Oct 20 16:06:32 2006 --- /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/expressions/SQLDeleteAllStatement.java Fri Oct 20 16:46:52 2006 *************** *** 52,57 **** --- 52,59 ---- protected Vector aliasedFields; protected Vector originalFields; + protected boolean shouldExtractWhereClauseFromSelectCallForExist; + public void setSelectCallForExist(SQLCall selectCallForExist) { this.selectCallForExist = selectCallForExist; } *************** *** 77,88 **** return tableAliasInSelectCallForNotExist; } public void setPrimaryKeyFieldsForAutoJoin(Collection primaryKeyFields) { ! if(primaryKeyFields instanceof Vector) { ! setOriginalFieldsForJoin((Vector)primaryKeyFields); } else { ! setOriginalFieldsForJoin(new Vector(primaryKeyFields)); } - setAliasedFieldsForJoin((Vector)getOriginalFieldsForJoin().clone()); } public void setOriginalFieldsForJoin(Vector originalFields) { this.originalFields = originalFields; --- 79,95 ---- return tableAliasInSelectCallForNotExist; } public void setPrimaryKeyFieldsForAutoJoin(Collection primaryKeyFields) { ! if(primaryKeyFields != null) { ! if(primaryKeyFields instanceof Vector) { ! setOriginalFieldsForJoin((Vector)primaryKeyFields); ! } else { ! setOriginalFieldsForJoin(new Vector(primaryKeyFields)); ! } ! setAliasedFieldsForJoin((Vector)getOriginalFieldsForJoin().clone()); } else { ! setOriginalFieldsForJoin(null); ! setAliasedFieldsForJoin(null); } } public void setOriginalFieldsForJoin(Vector originalFields) { this.originalFields = originalFields; *************** *** 102,110 **** public Expression getInheritanceExpression() { return inheritanceExpression; } /** ! * Append the string containing the SQL insert string for the given table. */ public DatabaseCall buildCall(AbstractSession session) { SQLCall call = (SQLCall)super.buildCall(session); --- 109,124 ---- public Expression getInheritanceExpression() { return inheritanceExpression; } + + public void setShouldExtractWhereClauseFromSelectCallForExist(boolean shouldExtractWhereClauseFromSelectCallForExist) { + this.shouldExtractWhereClauseFromSelectCallForExist = shouldExtractWhereClauseFromSelectCallForExist; + } + public boolean shouldExtractWhereClauseFromSelectCallForExist() { + return shouldExtractWhereClauseFromSelectCallForExist; + } /** ! * Return SQL call for the statement, through generating the SQL string. */ public DatabaseCall buildCall(AbstractSession session) { SQLCall call = (SQLCall)super.buildCall(session); *************** *** 117,134 **** boolean whereWasPrinted = true; if(selectCallForExist != null) { ! writer.write(" WHERE EXISTS("); ! // EXIST Example: selectCall.sqlString: ! // "SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (((t0.F_NAME LIKE 'a') AND (t1.SALARY = 0)) AND (t1.EMP_ID = t0.EMP_ID))" ! writeSelect(writer, selectCallForExist, tableAliasInSelectCallForExist, call); ! // closing bracket for EXISTS ! writer.write(")"); } else if (inheritanceExpression != null) { writer.write(" WHERE "); // Example: (PROJ_TYPE = 'L') ExpressionSQLPrinter printer = new ExpressionSQLPrinter(session, getTranslationRow(), call, false); printer.setWriter(writer); printer.printExpression(inheritanceExpression); } else { whereWasPrinted = false; } --- 131,164 ---- boolean whereWasPrinted = true; if(selectCallForExist != null) { ! if(shouldExtractWhereClauseFromSelectCallForExist) { ! // Should get here only in case selectCallForExist doesn't have aliases and ! // targets the same table as the statement. ! // Instead of making selectCallForExist part of " WHERE EXIST(" ! // just extract its where clause. ! // Example: selectCallForExist.sqlString: ! // "SELECT PROJ_ID FROM PROJECT WHERE (LEADER_ID IS NULL) ! whereWasPrinted = writeWhere(writer, selectCallForExist, call); ! // The result is: ! // "WHERE (LEADER_ID IS NULL)" ! } else { ! writer.write(" WHERE EXISTS("); ! // EXIST Example: selectCallForExist.sqlString: ! // "SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (((t0.F_NAME LIKE 'a') AND (t1.SALARY = 0)) AND (t1.EMP_ID = t0.EMP_ID))" ! writeSelect(writer, selectCallForExist, tableAliasInSelectCallForExist, call); ! // closing bracket for EXISTS ! writer.write(")"); ! // The result is (target table is SALARY): ! // "WHERE EXISTS(SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE (((t0.F_NAME LIKE 'a') AND (t1.SALARY = 0)) AND (t1.EMP_ID = t0.EMP_ID)) AND t1.EMP_ID = SALARY.EMP_ID)" ! } } else if (inheritanceExpression != null) { writer.write(" WHERE "); // Example: (PROJ_TYPE = 'L') ExpressionSQLPrinter printer = new ExpressionSQLPrinter(session, getTranslationRow(), call, false); printer.setWriter(writer); printer.printExpression(inheritanceExpression); + // The result is: + // "(PROJ_TYPE = 'L')" } else { whereWasPrinted = false; } *************** *** 145,150 **** --- 175,182 ---- writeSelect(writer, selectCallForNotExist, tableAliasInSelectCallForNotExist, call); // closing bracket for EXISTS writer.write(")"); + // The result is (target table is EMPLOYEE): + // "WHERE NOT EXISTS(SELECT t0.EMP_ID FROM EMPLOYEE t0, SALARY t1 WHERE ((t1.EMP_ID = t0.EMP_ID)) AND t0.EMP_ID = EMPLOYEE.EMP_ID)" } call.setSQLString(writer.toString()); *************** *** 157,168 **** } protected void writeSelect(Writer writer, SQLCall selectCall, String tableAliasInSelectCall, SQLCall call) throws IOException { ! writer.write(selectCall.getSQLString()); ! // join ! // Example: AND t0.EMP_ID = EMP_ID for(int i=0; i < originalFields.size(); i++) { ! writer.write(" AND "); if(tableAliasInSelectCall != null) { writer.write(tableAliasInSelectCall); writer.write('.'); --- 189,210 ---- } protected void writeSelect(Writer writer, SQLCall selectCall, String tableAliasInSelectCall, SQLCall call) throws IOException { ! String str = selectCall.getSQLString(); ! writer.write(str); ! ! boolean hasWhereClause = str.toUpperCase().indexOf(" WHERE ") >= 0; ! // join aliased fields to original fields ! // Examples: ! // table aliase provided: AND t0.EMP_ID = EMPLOYEE.EMP_ID ! // table aliase not provided: AND EMP_ID = EMPLOYEE.EMP_ID for(int i=0; i < originalFields.size(); i++) { ! if(i==0 && !hasWhereClause) { ! // there is no where clause - should print WHERE ! writer.write(" WHERE "); ! } else { ! writer.write(" AND "); ! } if(tableAliasInSelectCall != null) { writer.write(tableAliasInSelectCall); writer.write('.'); *************** *** 173,180 **** writer.write('.'); writer.write(((DatabaseField)originalFields.elementAt(i)).getName()); } ! call.getParameters().addAll(selectCall.getParameters()); call.getParameterTypes().addAll(selectCall.getParameterTypes()); } } --- 215,243 ---- writer.write('.'); writer.write(((DatabaseField)originalFields.elementAt(i)).getName()); } ! ! // add parameters ! call.getParameters().addAll(selectCall.getParameters()); ! call.getParameterTypes().addAll(selectCall.getParameterTypes()); ! } ! ! protected boolean writeWhere(Writer writer, SQLCall selectCall, SQLCall call) throws IOException { ! String selectStr = selectCallForExist.getSQLString(); ! ! int index = selectStr.toUpperCase().indexOf(" WHERE "); ! if(index < 0) { ! // no where clause - nothing to do ! return false; ! } ! ! // print the where clause ! String str = selectStr.substring(index); ! writer.write(str); ! ! // add parameters call.getParameters().addAll(selectCall.getParameters()); call.getParameterTypes().addAll(selectCall.getParameterTypes()); + + return true; } } ================================================================================ Merge Diffs: /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/queryframework/ExpressionQueryMechanism.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.29 Report generated at Fri Oct 20 16:48:45 2006 -------------------------------------------------------------------------------- *** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000002/AB0952363AC40CBFE034080020E8C54E.29 Fri Oct 20 16:07:35 2006 --- /ade/ailitche_toplink_main/tldev/source/essentials/oracle/toplink/essentials/internal/queryframework/ExpressionQueryMechanism.java Fri Oct 20 16:46:52 2006 *************** *** 210,229 **** SQLCall selectCallForNotExist, SQLSelectStatement selectStatementForNotExist, Collection primaryKeyFields) { if(selectCallForExist == null && selectCallForNotExist == null) { ! return buildDeleteAllStatement(table, inheritanceExpression); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); deleteAllStatement.setTable(table); deleteAllStatement.setTranslationRow(getTranslationRow()); - if(inheritanceExpression != null) { - deleteAllStatement.setInheritanceExpression((Expression)inheritanceExpression.clone()); - } - if(selectCallForExist != null) { deleteAllStatement.setSelectCallForExist(selectCallForExist); deleteAllStatement.setTableAliasInSelectCallForExist(getAliasTableName(selectStatementForExist, table)); } if(selectCallForNotExist != null) { --- 210,239 ---- SQLCall selectCallForNotExist, SQLSelectStatement selectStatementForNotExist, Collection primaryKeyFields) { if(selectCallForExist == null && selectCallForNotExist == null) { ! return buildDeleteStatementForDeleteAllQuery(table, inheritanceExpression); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); deleteAllStatement.setTable(table); deleteAllStatement.setTranslationRow(getTranslationRow()); if(selectCallForExist != null) { deleteAllStatement.setSelectCallForExist(selectCallForExist); + // if selectStatementForExist doesn't require aliasing and targets the same + // table as the statement to be built, + // then instead of creating sql with "WHERE EXISTS(" + // sql is created by extracting where clause from selectStatementForExist, + // for instance: + // DELETE FROM PROJECT WHERE (PROJ_NAME = ?) + // instead of the wrong one: + // DELETE FROM PROJECT WHERE EXISTS(SELECT PROJ_ID FROM PROJECT WHERE (PROJ_NAME = ?) AND PROJECT.PROJ_ID = PROJECT.PROJ_ID) + deleteAllStatement.setShouldExtractWhereClauseFromSelectCallForExist(!selectStatementForExist.requiresAliases() && table.equals(selectStatementForExist.getTables().firstElement())); deleteAllStatement.setTableAliasInSelectCallForExist(getAliasTableName(selectStatementForExist, table)); + } else { + // inheritanceExpression is irrelevant in case selectCallForExist != null + if(inheritanceExpression != null) { + deleteAllStatement.setInheritanceExpression((Expression)inheritanceExpression.clone()); + } } if(selectCallForNotExist != null) { *************** *** 236,245 **** return deleteAllStatement; } ! protected SQLDeleteStatement buildSQLDeleteAllStatementForMapping(SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, Vector sourceFields, Vector targetFields) { DatabaseTable targetTable = ((DatabaseField)targetFields.firstElement()).getTable(); if(selectCallForExist == null) { ! return buildDeleteAllStatement(targetTable); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); --- 246,255 ---- return deleteAllStatement; } ! protected SQLDeleteStatement buildDeleteAllStatementForMapping(SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, Vector sourceFields, Vector targetFields) { DatabaseTable targetTable = ((DatabaseField)targetFields.firstElement()).getTable(); if(selectCallForExist == null) { ! return buildDeleteStatementForDeleteAllQuery(targetTable); } SQLDeleteAllStatement deleteAllStatement = new SQLDeleteAllStatement(); *************** *** 315,347 **** } } ! protected SQLDeleteStatement buildDeleteAllStatement() { ! return buildDeleteAllStatement(getDescriptor().getDefaultTable()); ! } ! ! protected SQLDeleteStatement buildDeleteAllStatement(Expression inheritanceExpression) { ! return buildDeleteAllStatement(getDescriptor().getDefaultTable(), inheritanceExpression); ! } ! ! protected SQLDeleteStatement buildDeleteAllStatement(DatabaseTable table) { ! return buildDeleteAllStatement(table, null); } ! protected SQLDeleteStatement buildDeleteAllStatement(DatabaseTable table, Expression inheritanceExpression) { SQLDeleteStatement deleteStatement = new SQLDeleteStatement(); - deleteStatement.setWhereClause(getSelectionCriteria()); - if(deleteStatement.getWhereClause() != null) { - deleteStatement.setWhereClause((Expression)deleteStatement.getWhereClause().clone()); - deleteStatement.getWhereClause().getBuilder().setSession(getSession()); - deleteStatement.getWhereClause().getBuilder().setQueryClass(getQuery().getReferenceClass()); - } if(inheritanceExpression != null) { ! if(deleteStatement.getWhereClause() != null) { ! deleteStatement.setWhereClause(deleteStatement.getWhereClause().and(inheritanceExpression)); ! } else { ! deleteStatement.setWhereClause((Expression)inheritanceExpression.clone()); ! } } deleteStatement.setTable(table); deleteStatement.setTranslationRow(getTranslationRow()); --- 325,347 ---- } } ! /** ! * Used by DeleteAllQuery to create DeleteStatement in a simple case ! * when selectionCriteria==null. ! */ ! protected SQLDeleteStatement buildDeleteStatementForDeleteAllQuery(DatabaseTable table) { ! return buildDeleteStatementForDeleteAllQuery(table, null); } ! /** ! * Used by DeleteAllQuery to create DeleteStatement in a simple case ! * when selectionCriteria==null. ! */ ! protected SQLDeleteStatement buildDeleteStatementForDeleteAllQuery(DatabaseTable table, Expression inheritanceExpression) { SQLDeleteStatement deleteStatement = new SQLDeleteStatement(); if(inheritanceExpression != null) { ! deleteStatement.setWhereClause((Expression)inheritanceExpression.clone()); } deleteStatement.setTable(table); deleteStatement.setTranslationRow(getTranslationRow()); *************** *** 938,943 **** --- 938,946 ---- } } + // cache the flag - used many times + boolean hasInheritance = getDescriptor().hasInheritance(); + if(!tablesInInsertOrder.isEmpty()) { Expression whereClause = getSelectionCriteria(); *************** *** 947,960 **** SQLSelectStatement selectStatementForNotExist = null; SQLCall selectCallForNotExist = null; Expression inheritanceExpression = null; ! if(getDescriptor().hasInheritance()) { ! if(getDescriptor().getInheritancePolicy().shouldReadSubclasses()) { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getWithAllSubclassesExpression(); ! } else { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getOnlyInstancesExpression(); ! } } SQLSelectStatement selectStatementForExist = createSQLSelectStatementForModifyAll(whereClause); // Main Case: Descriptor is mapped to more than one table and/or the query references other tables --- 950,969 ---- SQLSelectStatement selectStatementForNotExist = null; SQLCall selectCallForNotExist = null; + + // inheritanceExpression is always null in a nested method call. Expression inheritanceExpression = null; ! if(tablesToIgnore == null) { ! // It's original (not a nested) method call. ! if(hasInheritance) { ! if(getDescriptor().getInheritancePolicy().shouldReadSubclasses()) { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getWithAllSubclassesExpression(); ! } else { ! inheritanceExpression = getDescriptor().getInheritancePolicy().getOnlyInstancesExpression(); ! } ! } } + SQLSelectStatement selectStatementForExist = createSQLSelectStatementForModifyAll(whereClause); // Main Case: Descriptor is mapped to more than one table and/or the query references other tables *************** *** 969,976 **** } } - selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); - if(isSelectCallForNotExistRequired) { selectStatementForNotExist = createSQLSelectStatementForModifyAll(null, null); selectCallForNotExist = (SQLCall)selectStatementForNotExist.buildCall(getSession()); --- 978,983 ---- *************** *** 981,987 **** // currently DeleteAll using Oracle anonymous block is not implemented if(!getSession().getPlatform().isOracle()) { // there are at least two storeys of inheritance above the referenceClass ! if(getDescriptor().hasInheritance() && getDescriptor().getTables().size() > 2) { prepareDeleteAllUsingTempStorage(); return; } --- 988,994 ---- // currently DeleteAll using Oracle anonymous block is not implemented if(!getSession().getPlatform().isOracle()) { // there are at least two storeys of inheritance above the referenceClass ! if(hasInheritance && getDescriptor().getTables().size() > 2) { prepareDeleteAllUsingTempStorage(); return; } *************** *** 989,995 **** } } } ! if(isMainCase) { // Main case: Descriptor is mapped to more than one table and/or the query references other tables // --- 996,1008 ---- } } } ! ! // Don't use selectCallForExist in case there is no whereClause - ! // a simpler sql will be created if possible. ! if(whereClause != null) { ! selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); ! } ! if(isMainCase) { // Main case: Descriptor is mapped to more than one table and/or the query references other tables // *************** *** 1009,1024 **** // The situation is a bit more complex if more than two levels of inheritance is involved: // both "EXISTS" and "NOT EXISTS" used for the "intermediate" (not first and not last) tables. ! if(tablesToIgnore != null) { ! // isSelectCallForNotExistRequired==false: it's a nested method call. // In Employee example, query with reference class // Project will get here to handle LPROJECT table ! // Should NOT pass inheritanceExpression - it could only be used with the highest in insertion order table. ! // Because it's a nested call the table can't be first in insertion order. ! deleteStatement = buildDeleteAllStatement(table, null, selectCallForExist, selectStatementForExist, null, null, primaryKeyFields); ! } else if (tablesInInsertOrder.size() == 1) { ! // isSelectCallForNotExistRequired==false: original call, single table. ! // Handles single table inheritance. If there's no inheritance identical to the one above. deleteStatement = buildDeleteAllStatement(table, inheritanceExpression, selectCallForExist, selectStatementForExist, null, null, primaryKeyFields); } else { // isSelectCallForNotExistRequired==true: original call, multiple tables. --- 1022,1037 ---- // The situation is a bit more complex if more than two levels of inheritance is involved: // both "EXISTS" and "NOT EXISTS" used for the "intermediate" (not first and not last) tables. ! if(!isSelectCallForNotExistRequired) { ! // isSelectCallForNotExistRequired == false: ! // either tablesToIgnore != null: it's a nested method call. ! // Example: // In Employee example, query with reference class // Project will get here to handle LPROJECT table ! // or tablesInInsertOrder.size() == 1: there is only one table, ! // but there is joining to at least one other table (otherwise would've been isMainCase==false). ! // ! // Note that buildDeleteAllStatement ignores inheritanceExpression if selectCallForExist!=null. deleteStatement = buildDeleteAllStatement(table, inheritanceExpression, selectCallForExist, selectStatementForExist, null, null, primaryKeyFields); } else { // isSelectCallForNotExistRequired==true: original call, multiple tables. *************** *** 1110,1120 **** // In Employee example, query with reference class: // Project will build a simple sql call for PROJECT(and will make nested method calls for LargeProject and SmallProject); // SmallProject will build a simple sql call for PROJECT ! setSQLStatement(buildDeleteAllStatement(inheritanceExpression)); } // Add statements for ManyToMany and DirectCollection mappings ! Vector deleteStatementsForMappings = buildDeleteAllStatementsForMappings(inheritanceExpression, selectCallForExist, selectStatementForExist, tablesToIgnore == null); if(!deleteStatementsForMappings.isEmpty()) { if(getSQLStatement() != null) { getSQLStatements().add(getSQLStatement()); --- 1123,1149 ---- // In Employee example, query with reference class: // Project will build a simple sql call for PROJECT(and will make nested method calls for LargeProject and SmallProject); // SmallProject will build a simple sql call for PROJECT ! setSQLStatement(buildDeleteAllStatement(getDescriptor().getDefaultTable(), inheritanceExpression, selectCallForExist, selectStatementForExist, null, null, null)); ! } ! ! if(selectCallForExist == null) { ! // Getting there means there is no whereClause. ! // To handle the mappings selectCallForExist may be required in this case, too. ! if(hasInheritance && (tablesToIgnore != null || inheritanceExpression != null)) { ! // The only case NOT to create the call for no whereClause is either no inheritance, ! // or it's an original (not a nested) method call and there is no inheritance expression. ! // In Employee example: ! // query with reference class Project and no where clause for m-to-m mapping generates: ! // DELETE FROM EMP_PROJ; ! // as opposed to query with reference class SmallProject: ! // DELETE FROM EMP_PROJ WHERE EXISTS(SELECT PROJ_ID FROM PROJECT WHERE (PROJ_TYPE = ?) AND PROJ_ID = EMP_PROJ.PROJ_ID). ! // ! selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); ! } } // Add statements for ManyToMany and DirectCollection mappings ! Vector deleteStatementsForMappings = buildDeleteAllStatementsForMappings(selectCallForExist, selectStatementForExist, tablesToIgnore == null); if(!deleteStatementsForMappings.isEmpty()) { if(getSQLStatement() != null) { getSQLStatements().add(getSQLStatement()); *************** *** 1125,1131 **** } // Indicates whether the descriptor has children using extra tables. ! boolean hasChildrenWithExtraTables = getDescriptor().hasInheritance() && getDescriptor().getInheritancePolicy().hasChildren() && getDescriptor().getInheritancePolicy().hasMultipleTableChild(); // TBD: should we ignore subclasses in case descriptor doesn't want us to read them in? //** Currently in this code we do ignore. --- 1154,1160 ---- } // Indicates whether the descriptor has children using extra tables. ! boolean hasChildrenWithExtraTables = hasInheritance && getDescriptor().getInheritancePolicy().hasChildren() && getDescriptor().getInheritancePolicy().hasMultipleTableChild(); // TBD: should we ignore subclasses in case descriptor doesn't want us to read them in? //** Currently in this code we do ignore. *************** *** 1212,1218 **** // Create SQLDeleteAllStatements for mappings that may be responsible for references // to the objects to be deleted // in the tables NOT mapped to any class: ManyToManyMapping and DirectCollectionMapping ! protected Vector buildDeleteAllStatementsForMappings(Expression inheritanceExpression, SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, boolean dontCheckDescriptor) { Vector deleteStatements = new Vector(); Iterator itMappings = getDescriptor().getMappings().iterator(); while(itMappings.hasNext()) { --- 1241,1247 ---- // Create SQLDeleteAllStatements for mappings that may be responsible for references // to the objects to be deleted // in the tables NOT mapped to any class: ManyToManyMapping and DirectCollectionMapping ! protected Vector buildDeleteAllStatementsForMappings(SQLCall selectCallForExist, SQLSelectStatement selectStatementForExist, boolean dontCheckDescriptor) { Vector deleteStatements = new Vector(); Iterator itMappings = getDescriptor().getMappings().iterator(); while(itMappings.hasNext()) { *************** *** 1228,1239 **** sourceFields = ((DirectCollectionMapping)mapping).getSourceKeyFields(); targetFields = ((DirectCollectionMapping)mapping).getReferenceKeyFields(); } ! if(selectCallForExist == null) { ! if(inheritanceExpression != null || getSelectionCriteria() != null) { ! selectCallForExist = (SQLCall)selectStatementForExist.buildCall(getSession()); ! } ! } ! deleteStatements.addElement(buildSQLDeleteAllStatementForMapping(selectCallForExist, selectStatementForExist, sourceFields, targetFields)); } } } --- 1257,1263 ---- sourceFields = ((DirectCollectionMapping)mapping).getSourceKeyFields(); targetFields = ((DirectCollectionMapping)mapping).getReferenceKeyFields(); } ! deleteStatements.addElement(buildDeleteAllStatementForMapping(selectCallForExist, selectStatementForExist, sourceFields, targetFields)); } } } ================================================================================ Merge Diffs: /ade/ailitche_toplink_main/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/cmp3/advanced/EntityManagerJUnitTestSuite.java vs. /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000003/AB0952363AC40CBFE034080020E8C54E.54 Report generated at Fri Oct 20 16:48:45 2006 -------------------------------------------------------------------------------- *** /net/stottnfs2.ca.oracle.com/vol/vol1/ade_ottawa_txn/ailitche/ailitche_ri_gf674_061020/ade_storage/000003/AB0952363AC40CBFE034080020E8C54E.54 Fri Oct 20 16:08:48 2006 --- /ade/ailitche_toplink_main/tltest/source/entity-persistence-tests/src/java/oracle/toplink/essentials/testing/tests/cmp3/advanced/EntityManagerJUnitTestSuite.java Fri Oct 20 16:46:52 2006 *************** *** 66,72 **** import oracle.toplink.essentials.testing.models.cmp3.advanced.LargeProject; import oracle.toplink.essentials.testing.models.cmp3.advanced.PhoneNumber; ! import oracle.toplink.essentials.testing.models.cmp3.advanced.Project; import oracle.toplink.essentials.threetier.ReadConnectionPool; import oracle.toplink.essentials.threetier.ServerSession; --- 66,72 ---- import oracle.toplink.essentials.testing.models.cmp3.advanced.LargeProject; import oracle.toplink.essentials.testing.models.cmp3.advanced.PhoneNumber; ! import oracle.toplink.essentials.testing.models.cmp3.advanced.SmallProject; import oracle.toplink.essentials.testing.models.cmp3.advanced.Project; import oracle.toplink.essentials.threetier.ReadConnectionPool; import oracle.toplink.essentials.threetier.ServerSession; *************** *** 2893,2898 **** --- 2893,2992 ---- em.remove(initialAddress); em.getTransaction().commit(); } + //bug gf674 - EJBQL delete query with IS NULL in WHERE clause produces wrong sql + public void testDeleteAllPhonesWithNullOwner() { + EntityManager em = createEntityManager(); + em.getTransaction().begin(); + try { + em.createQuery("DELETE FROM PhoneNumber ph WHERE ph.owner IS NULL").executeUpdate(); + } catch (Exception e) { + fail("Exception thrown: " + e.getClass()); + } finally { + em.getTransaction().rollback(); + em.close(); + } + } + public void testDeleteAllProjectsWithNullTeamLeader() { + internalDeleteAllProjectsWithNullTeamLeader("Project"); + } + public void testDeleteAllSmallProjectsWithNullTeamLeader() { + internalDeleteAllProjectsWithNullTeamLeader("SmallProject"); + } + public void testDeleteAllLargeProjectsWithNullTeamLeader() { + internalDeleteAllProjectsWithNullTeamLeader("LargeProject"); + } + protected void internalDeleteAllProjectsWithNullTeamLeader(String className) { + String name = "testDeleteAllProjectsWithNull"; + + // setup + SmallProject sp = new SmallProject(); + sp.setName(name); + LargeProject lp = new LargeProject(); + lp.setName(name); + EntityManager em = createEntityManager(); + try { + em.getTransaction().begin(); + // make sure there are no pre-existing objects with this name + em.createQuery("DELETE FROM "+className+" p WHERE p.name = '"+name+"'").executeUpdate(); + em.persist(sp); + em.persist(lp); + em.getTransaction().commit(); + } catch (RuntimeException ex){ + em.getTransaction().rollback(); + throw ex; + } finally { + em.close(); + } + + // test + em = createEntityManager(); + em.getTransaction().begin(); + try { + em.createQuery("DELETE FROM "+className+" p WHERE p.name = '"+name+"' AND p.teamLeader IS NULL").executeUpdate(); + em.getTransaction().commit(); + } catch (Exception e) { + em.getTransaction().rollback(); + fail("Exception thrown: " + e.getClass()); + } finally { + em.close(); + } + + // verify + String error = null; + em = createEntityManager(); + List result = em.createQuery("SELECT OBJECT(p) FROM Project p WHERE p.name = '"+name+"'").getResultList(); + if(result.isEmpty()) { + if(!className.equals("Project")) { + error = "Target Class " + className +": no objects left"; + } + } else { + if(result.size() > 1) { + error = "Target Class " + className +": too many objects left: " + result.size(); + } else { + Project p = (Project)result.get(0); + if(p.getClass().getName().endsWith(className)) { + error = "Target Class " + className +": object of wrong type left: " + p.getClass().getName(); + } + } + } + + // clean up + try { + em.getTransaction().begin(); + // make sure there are no pre-existing objects with this name + em.createQuery("DELETE FROM "+className+" p WHERE p.name = '"+name+"'").executeUpdate(); + em.getTransaction().commit(); + } catch (RuntimeException ex){ + em.getTransaction().rollback(); + throw ex; + } finally { + em.close(); + } + + if(error != null) { + fail(error); + } + } public static void main(String[] args) { // Now run JUnit.