Hibernate version:3.0.5

I am trying to delete a large number of objects (5000+) in the database using the following HQL:

delete from Eval where
crRights = null and
modRights = null and
delRights = null and
mpRights = null

In the HQL above the class Eval has four bidirectional relationships with four different kinds of rights instances, and these relationships are represented by the four named Set properties. What I am trying to do is to delete the Evals which are no longer referred by any rights, that means deleting the evals for which the getCrRights, getModRights, ..., all return null.

As can be seen from the stack trace below Hibernate 3.0.5 refuses to execute this HQL claiming that my database (MaxDB) does not support temporary tables.

Is there a way one can enable this supposedly disabled feature?

Is there an alternative HQL way to carry out this delete?

Any hints will be highly appreciated. Thanks.

Randahl


org.hibernate.HibernateException: cannot perform multi-table deletes using dialect not supporting temp tables
at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.<init>(MultiTable
DeleteExecutor.java:40)
at org.hibernate.hql.ast.QueryTranslatorImpl.buildAppropriateStatementEx
ecutor(QueryTranslatorImpl.java:423)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorIm
pl.java:172)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl
.java:103)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.jav
a:473)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1060)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1041)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)

is Eval mapped using a "multi-table" inheritence strategy (meaning joined-subclass or union-subclass)? Or does it make use of <join/> mappings?

In either of those cases, Hibernate uses temporary tables in the database to hold the ids of the entities to be deleted. Of course this is predicated on your database supporting temporary tables. Looking at SAPDBDialect (which I assume you are using for maxdb) shows that it does not define support for temporary tables.

Don't know maxdb well enough to say whether that is correct or not.

To answer your question as precisely as possible, here follows the mapping file. To the best of my knowledge SapDB a.k.a. MaxDB is a pretty advanced database, so maybe it is the dialect spec that lacks something? I am using the SapDB dialect as it comes, except I corrected a bug related to the java object type being specified with a wrong sql type.

If you have any clue about how to solve this, I am all ears. It really bugs me having to resort to non-hql solutions - I could beaf up some SQL to do the delete, but I really would prefer the hql way.

Below follows the mapping file.

Yours
Randahl




<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="dk.rockit.puls.server.entity.security.right.evaluation.Evaluation" table="EVALUATION" lazy="true">
<id name="id" type="integer" unsaved-value="null" >
<column name="EVALUATION_ID"/>
<generator class="native"/>
</id>
<version
name="version"
column="VERSION"
type="long"
/>
<property name="name"/>

<set name="modifyPropertyRights" inverse="true">
<key column="MODIFY_EVALUATION_ID"/>
<one-to-many
class="dk.rockit.puls.server.entity.security.right.PropertyRight"
/>
</set>
<set name="createValueRights" inverse="true">
<key column="CREATE_EVALUATION_ID"/>
<one-to-many
class="dk.rockit.puls.server.entity.security.right.ValueRight"
/>
</set>
<set name="modifyValueRights" inverse="true">
<key column="MODIFY_EVALUATION_ID"/>
<one-to-many
class="dk.rockit.puls.server.entity.security.right.ValueRight"
/>
</set>
<set name="deleteValueRights" inverse="true">
<key column="DELETE_EVALUATION_ID"/>
<one-to-many
class="dk.rockit.puls.server.entity.security.right.ValueRight"
/>
</set>

<set name="compositeEvaluations" table="COMPOSITE_EVALUATION#EVALUATIONS" inverse="true">
<key column="EVALUATION_ID"/>
<many-to-many
class="dk.rockit.puls.server.entity.security.right.evaluation.CompositeEvaluation"
column="COMPOSITE_EVALUATION_ID"
/>
</set>

<set name="nestedEvaluations">
<key column="DELEGATE_EVALUATION_ID"/>
<one-to-many
class="dk.rockit.puls.server.entity.security.right.evaluation.NestedEvaluation"
/>
</set>

<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.CompositeEvaluation" table="COMPOSITE_EVALUATION" lazy="true">
<key column="COMPOSITE_EVALUATION_ID"/>
<set name="evaluations" table="COMPOSITE_EVALUATION#EVALUATIONS" lazy="true">
<key column="COMPOSITE_EVALUATION_ID"/>
<many-to-many
class="dk.rockit.puls.server.entity.security.right.evaluation.Evaluation"
column="EVALUATION_ID"
/>
</set>
</joined-subclass>

<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.ConsistencyEvaluation" table="CONSISTENCY_EVALUATION" lazy="true">
<key column="CONSISTENCY_EVALUATION_ID"/>
<set name="predecessorPropertyNames" table="CONSISTENCY_EVALUATION_PPN">
<key column="CONSISTENCY_EVALUATION_ID"/>
<element column="PREDECESSOR_PROPERTY_NAME" type="string"/>
</set>

<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.NestedEvaluation" table="NESTED_EVALUATION" lazy="true">
<key column="NESTED_EVALUATION_ID"/>
<property name="propertyName">
<column name="PROPERTY_NAME"/>
</property>
<many-to-one
name="delegateEvaluation"
class="dk.rockit.puls.server.entity.security.right.evaluation.Evaluation"
column="DELEGATE_EVALUATION_ID"
/>
<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.NestedValueEvaluation" table="NESTED_VALUE_EVALUATION" lazy="true">
<key column="NESTED_VALUE_EVALUATION_ID"/>
</joined-subclass>
<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.NestedValueCollectionEvaluation" table="NESTED_VALUE_COLLECTION_EVAL" lazy="true">
<key column="NESTED_VALUE_COLLECTION_EVAL_ID"/>
</joined-subclass>
</joined-subclass>

<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.FloatInIntervalEvaluation" table="FLOAT_IN_INTERVAL_EVALUATION" lazy="true">
<key column="FLOAT_IN_INTERVAL_EVALUATION_ID"/>
<property name="lowerBoundary">
<column name="LOWER_BOUNDARY"/>
</property>
<property name="lowerBoundaryClosed">
<column name="LOWER_BOUNDARY_CLOSED"/>
</property>
<property name="upperBoundary">
<column name="UPPER_BOUNDARY"/>
</property>
<property name="upperBoundaryClosed">
<column name="UPPER_BOUNDARY_CLOSED"/>
</property>
<property name="lowerBoundaryPropertyName">
<column name="LOWER_BOUNDARY_PROPERTY_NAME"/>
</property>
<property name="lowerBoundaryClosedPropertyName">
<column name="LOWER_BOUNDARY_CLOSED_PROP_NAME"/>
</property>
<property name="upperBoundaryPropertyName">
<column name="UPPER_BOUNDARY_PROPERTY_NAME"/>
</property>
<property name="upperBoundaryClosedPropertyName">
<column name="UPPER_BOUNDARY_CLOSED_PROP_NAME"/>
</property>
</joined-subclass>
<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.ObjectOneOfEvaluation" table="OBJECT_ONE_OF_EVALUATION" lazy="true">
<key column="OBJECT_ONE_OF_EVALUATION_ID"/>
<property name="allowedObjectsPropertyName">
<column name="ALLOWED_OBJECTS_PROPERTY_NAME" sql-type="VARCHAR(60) UNICODE"/>
</property>
<property name="emptyAllowsAll">
<column name="EMPTY_ALLOWS_ALL"/>
</property>
</joined-subclass>

</joined-subclass>

<joined-subclass name="dk.rockit.puls.server.entity.security.right.evaluation.SingletonEvaluation" table="SINGLETON_EVALUATION" lazy="true">
<key column="SINGLETON_EVALUATION_ID"/>
<property name="singletonClassName">
<column name="SINGLETON_CLASS_NAME" sql-type="VARCHAR(150) UNICODE"/>
</property>
</joined-subclass>
</class>
</hibernate-mapping>

So then to answer my question, yes, you are using a multi-table mapping (joined subclass).

Essentially, when you say "delete from Evaluation", Hibernate really needs to delete from tables:
1) EVALUATION
2) (potentially) COMPOSITE_EVALUATION
3) (potentially) CONSISTENCY_EVALUATION
4) (potentially) NESTED_EVALUATION
etc

Most databases do not support joined deletes, so for consistency sake Hibernate implements this by performing an initial insert-select for matching ids to be deleted and inserts them into a temp table.

So this functionality needs temp table support in the underlying database. When this was implemented, I asked on the dev list for help with two dialects for which I did not know how to implement this support. sapdb/maxdb was one of these.

If you know how to do this on maxdb and/or sapdb let me know (i.e. create an enhancement request in jira) and I'll add this capability.

Hi Steve


I just verified this - MaxDB does indeed support the temporary tables you need. There is a quite thorough explanation of the SQL needed for creating such tables with MaxDB at this address:

http://dev.mysql.com/doc/maxdb/en/6d/11 ... ontent.htm

I will now create an enhancement request in Jira as you asked me to.


Randahl

You will find the Jira issue here:

http://opensource2.atlassian.com/projec ... e/HHH-1197

Randahl