Hy there.

I really need your help. I read many posts here and tried a lot of things for several hours now, but it seems I can't solve my problem, even if it appears to be quite simple compared to other problems discussed here (regarding cascading bulk delete) .
My problem is to bulk delete many objects at once, that have a map attribute. (Hibernate 3.20 with Derby.)

I get an foreign key exception, when I simply call session.createQuery("delete document as doc where doc.id = 1").executeUpdate();

An extract of my Hibernate mapping file:
<class name="org.pubcurator.documentor.documentmodel.impl.DocumentImpl" entity-name="Document" abstract="false" lazy="false" discriminator-value="Document" table="`document`">
<meta attribute="eclassName">Document</meta>
<meta attribute="epackage">http://www.pubcurator.org/documentor/documentmodel</meta>
<id name="id" type="long" unsaved-value="0">
<column not-null="true" unique="false" name="`id`"/>
<generator class="native"/>
</id>
<discriminator column="`dtype`" type="string"/>
<version name="e_version" column="e_version" access="org.eclipse.emf.teneo.hibernate.mapping.property.VersionPropertyHandler">
<meta attribute="syntheticVersion">true</meta>
</version>
<map name="properties" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`document_properties_id`" not-null="false" unique="false"/>
</key>
<map-key type="java.lang.String"/>
<element type="java.lang.String" not-null="false" unique="false">
<column not-null="false" unique="false" name="`value`" length="10000"/>
</element>
</map>
<list name="tags" table="`document_tags`" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`document_tags_id`" not-null="true" unique="false"/>
</key>
<list-index column="`document_tags_idx`"/>
<element type="java.lang.String"/>
</list>
<property name="textToAnalyze" lazy="false" insert="true" update="true" not-null="false" unique="false" type="text">
<column not-null="false" unique="false" name="`texttoanalyze`" length="1000000000"/>
</property>
<property name="type" lazy="false" insert="true" update="true" not-null="false" unique="false" type="java.lang.String">
<column not-null="false" unique="false" name="`type`"/>
</property>
<property name="abstract" lazy="false" insert="true" update="true" not-null="false" unique="false" type="text">
<column not-null="false" unique="false" name="`abstract`" length="100000"/>
</property>
<property name="note" lazy="false" insert="true" update="true" not-null="false" unique="false" type="java.lang.String">
<column not-null="false" unique="false" name="`note`"/>
</property>
<property name="owner" lazy="false" insert="true" update="true" not-null="false" unique="false" type="java.lang.String">
<column not-null="false" unique="false" name="`owner`"/>
</property>
</class>


As you can see I already use 'cascade="all"'. Isn't this enough? Do I have to add something to my mapping file? Is there another good way to delete a large bunch of objects (including their foreign keys)?

I hope someone can help me to solve my problem.

Kai

Still having this problem. Is perhaps now someone here who can help me?
Or what would be the best way to delete a large number of objects (including their dependencies) from the database?
Fetching and deleting each object one by one isn't an option ... much to slow.

My problem can be easily solved with normal SQL, but I would like to do it the HSQL way, to keep the database independency.

But with pure HSQL I can't access the mapped Document.properties (see above), as it is just an attribute of "Document", so a "delete Document.properties", or something like that, fails.
So I depend on a cascade delete of HSQL.

Kai

could you pls describe your data model in more detail?
what is the database schema (DDL) that is in effect in your database?
do you let hibernate generate the schema, or do you generate it yourself?

thanks.

Ok, I explain it with a simple extract of my model.
I have a class named "Document", that has an attribute "properties", which is a map. The model is mapped with hbm2ddl to my database, so that there is a table for "Document" and one for "properties". The last one has a "Document" id as foreign id (see simple mapping extract below).

The problem is now, that when I use bulk delete (http://www.hibernate.org/hib_docs/v3/re ... tch-direct) to delete many document objects at once ("delete Document as doc where doc.id in (:ids)"), I get an foreign key exception, as bulk delete doesn't seems to cascade delete.

I just also found this posting here: http://forum.hibernate.org/viewtopic.ph ... ulk+delete
So it really seems, that bulk delete is not able to cascade delete objects including their dependencies.
My problem is even a bit more problematic than in the posting above, as I can't delete the "properties" directly, cause those a no directly named entities and so I can't access them directly.

Is there another option for deleting a large number of objects at once? For having a list of id's, fetching each object by id and then deleting it, isn't an option as it takes far too much time.

Any suggestions?

Kai

extract from the hibernate mapping file:

<class name="org.pubcurator.documentor.documentmodel.impl.DocumentImpl" entity-name="Document" abstract="false" lazy="false" discriminator-value="Document" table="`document`">
<meta attribute="eclassName">Document</meta>
<meta attribute="epackage">http://www.pubcurator.org/documentor/documentmodel</meta>
<id name="id" type="long" unsaved-value="0">
<column not-null="true" unique="false" name="`id`"/>
<generator class="native"/>
</id>
<discriminator column="`dtype`" type="string"/>
<version name="e_version" column="e_version" access="org.eclipse.emf.teneo.hibernate.mapping.property.VersionPropertyHandler">
<meta attribute="syntheticVersion">true</meta>
</version>
<map name="properties" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="`document_properties_id`" not-null="false" unique="false"/>
</key>
<map-key type="java.lang.String"/>
<element type="java.lang.String" not-null="false" unique="false">
<column not-null="false" unique="false" name="`value`" length="10000"/>
</element>
</map>
</class>

well,, one quick solution is to generate the schema yourself, based on what hibernate generates, only add cascading where needed. However, after doing that, pls test hibernate code to ensure it syncs the cache with the db after bulk deletion. even hibernate recommends not using the hbm2ddl feature in production...

There is a JIRA Issue opened since July 2006 regarding this problem