SchemaExport, HSQLDB, and dropping non-existent constraints

markdespain · **Posted:** Sun May 02, 2004 3:28 am

Hello,

I am using Hibernate 2.1 with HSQLDB. I have used XDoclet to create Hibernate Mapping Files. Now I am using the SchemaExport tool to generate a SQL script from those Mapping Files which will create a new database and will be executed by the HSQLDB ScriptTool. However, the ScriptTool throws a SQLException when executing the first ALTER TABLE because of the attempt to drop a constraint on a table that don't exist.

Is there a way to tell the SchemaExport tool to suppress the generation of the preceding DROP TABLE and ALTER TABLE statements?

Below are the the error message, the Ant task used to call SchemaExport, and the generated script:

//------------------------------------------------------------------------------
// Error message
//------------------------------------------------------------------------------
[java] SQL Error at line 29: java.sql.SQLException: Table not found: ACTIVITIES in statement [alter table Activities drop constraint FKF0ED180DFAE13213;

//-----------------------------------------------------------------------------
// Ant task
//-----------------------------------------------------------------------------

Code:

<schemaexport 
  properties="${hibernate.config.dir}/hibernate.properties"
  delimiter=";"
  quiet="true"
  text="true"
  output="${schema.location}">

    <fileset dir="${app.classes.dir}">
        <include name="**/*.hbm.xml"/>
    </fileset>
</schemaexport>

//------------------------------------------------------------------------------
// Generated SQL script
//------------------------------------------------------------------------------

Code:

alter table Activities drop constraint FKF0ED180DFAE13213;
drop table Environments if exists;
drop table Activities if exists;
create table Environments (
   identity INTEGER NOT NULL IDENTITY,
   elevationFt DOUBLE not null,
   atElevationMoreThan28Days BIT not null,
   hills VARCHAR(20) not null,
   temperature VARCHAR(15) not null,
   humidity VARCHAR(20) not null,
   terrain VARCHAR(20) not null,
   wind VARCHAR(20) not null,
   fan VARCHAR(20) not null,
   waterTemperature VARCHAR(20) not null,
   current VARCHAR(20) not null,
   waves VARCHAR(20) not null
);
create table Activities (
   identity INTEGER NOT NULL IDENTITY,
   rpe DOUBLE not null,
   durationHrs DOUBLE not null,
   sequentialness VARCHAR(25) not null,
   unusualness VARCHAR(20) not null,
   activity VARCHAR(50) not null,
   intensity VARCHAR(15) not null,
   distanceMi DOUBLE not null,
   environment INTEGER not null
);
alter table Activities add constraint FKF0ED180DFAE13213 foreign key (environment) references Environments;

emmanuel · **Posted:** Mon May 03, 2004 7:10 pm

The error message should be harmless.
Check the net.sf.hibernate.test.TestCase for a synthetic use of SchemaExport and HSQLDB

markdespain · **Posted:** Wed May 05, 2004 1:02 am

I found that the exception can be ignored, but you also need to modify the delimeter in the SchemaExport target so that the HSQLDB runs each SQL statement separately, instead of all at once. Otherwise, the other statements won't get executed.

<schemaexport
properties="${hibernate.config.dir}/hibernate.properties"
delimiter=";${line.separator}go"
quiet="true"
text="true"
output="${schema.location}">
<fileset dir="${app.classes.dir}">
<include name="**/*.hbm.xml"/>
</fileset>
</schemaexport>

I also had to modify the HSQLDB ScriptTool class to take in an optional "-ignore" flag to so that SQLExceptions occurring during execution of statements in batch mode don't cause the ScriptTool's execute() method to break out if it's while loop... but that's more of a topic for the HSQLDB forum, eh?