CloudScape/Derby

mlerutte · **Posted:** Thu Sep 02, 2004 9:17 am

Has anybody used the CloudScape database (soon to be known as Apache Derby)? We're currently using Hypersonic but we're running into size limitations that CloudScape claims not to have and as IBM is open-sourcing CloudScape we are assessing whether it is a viable alternative.

Should the Hibernate dialect be set to DB2?

Maurice.

gavin · **Posted:** Thu Sep 02, 2004 9:22 am

I understand that they have rewritten cloudscape to match the dialect of DB2, so yes, that might be right. But I have not tested it, so you'll be the guinea pig ;-)

Are you sure the new version is actually released yet?

dvydra · **Posted:** Fri Sep 10, 2004 5:45 pm

http://incubator.apache.org/derby/index.html

gavin · **Posted:** Fri Sep 10, 2004 10:20 pm

....and there is no download there.

skjohnston · **Posted:** Mon Sep 13, 2004 11:43 am

I have taken a post on jroller.com and built a Derby dialect that also fixes a problem with the difference between DB2 and Derby (Cloudscape 10b) generation of native IDs. Hopefully soon I can finish my testing and post the source - but how this gets into the standard distribution I don't know.

christian · **Posted:** Mon Sep 13, 2004 12:47 pm

Post it to JIRA, thanks.

skjohnston · **Posted:** Mon Sep 13, 2004 1:32 pm

I have put together a jar with the source and binaries for the Derby dialect (IBM Cloudscape 10beta). I am not sure what "posting to JIRA" means (newbie - sorry). Anyway, I hope this is useful, I put the classes in the standard dialect namespace so they are easy to consume.

http://www.johnstonshome.org/~simon/pro ... /derby.jar

david · **Posted:** Mon Sep 13, 2004 6:14 pm

Hibernate uses JIRA software for bug and feature tracking.
It is here http://opensource.atlassian.com/projects/hibernate/secure/Dashboard.jspa

skjohnston · **Posted:** Mon Sep 13, 2004 6:53 pm

OK, got it, I have posted a new patch to JIRA and attached both source classes.

Joel.Rosi-Schwartz · **Joined:** Sat Sep 04, 2004 4:14 pm **Posts:** 13

First, thanks Simon for putting the DerbyDialect together and posting it. I am in the process of integrating Cloudscape/Derby and Hibernate, so this was very timely for me. I have ran into some problems with it though. My first step was to use the CaveatEmptor 0.95 junit tests to see how it ran. It is much better then the DB2Dialect, but there are still errors in the tests. I have added a comment to the Jira issue where the patch was contributed, http://opensource.atlassian.com/projects/hibernate/browse/HB-1224 and have attached a copy of the junit test output.

As I noted there, my testing environment is exactly the same as Simons: Hibernate 2.1.6 and Cloudscape 10 beta.

I am quite interested in getting Hibernate and Cloudscape/Derby running together, so I am open to suggestions as to what I can do to help move this issue forward.

Also, please let my know if debug level Hibernate log excerpts and/or the generated SQL would be useful. It seemed like overkill to me in this instance.

- joel

Joel.Rosi-Schwartz · **Joined:** Sat Sep 04, 2004 4:14 pm **Posts:** 13

I have looked into the errors from the junit tests and it seems as if Cloudscase does not care for unique keys where one of the columns is nullable; in this case unique (NAME, PARENT_CATEGORY_ID)
caused the hicup. I got all of the tests to run successfully by removing PARENT_CATEGORY_ID from the unique key. I am not sure if this is a problem with Cloudscape or CaveatEmptor. If it is Cloudscape, which is what I suspect, what can be done about it? Should this be escalated to the Derby team with a test case?

There are also still reports of SQL errrors in the junit output, but these do not trigger test failures, so I assume that the testcases are swallowing them. They do, however, look worrysome to me. Advice would be appreciated.

Code:

27:33,875 ERROR SchemaExport: Unsuccessful: alter table BANK_ACCOUNT add constraint FK1_BANK_ACCOUNT_ID foreign key (BANK_ACCOUNT_ID) references BILLING_DETAILS
27:33,875 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_BANK_ACCOUNT_ID^T18^T42622
27:34,062 ERROR SchemaExport: Unsuccessful: alter table CATEGORY add constraint FK1_PARENT_CATEGORY_ID foreign key (PARENT_CATEGORY_ID) references CATEGORY
27:34,062 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_PARENT_CATEGORY_ID^T18^T42622
27:34,062 ERROR SchemaExport: Unsuccessful: alter table ITEM add constraint FK1_APPROVED_BY_USER_ID foreign key (APPROVED_BY_USER_ID) references USERS
27:34,062 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_APPROVED_BY_USER_ID^T18^T42622
27:34,171 ERROR SchemaExport: Unsuccessful: alter table ITEM add constraint FK3_SUCCESSFUL_BID_ID foreign key (SUCCESSFUL_BID_ID) references BID
27:34,171 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK3_SUCCESSFUL_BID_ID^T18^T42622
27:34,171 ERROR SchemaExport: Unsuccessful: alter table USERS add constraint FK1_DEFAULT_BILLING_DETAILS_ID foreign key (DEFAULT_BILLING_DETAILS_ID) references BILLING_DETAILS
27:34,171 ERROR SchemaExport: DB2 SQL error: SQLCODE: -1, SQLSTATE: 42622, SQLERRMC: FK1_DEFAULT_BILLING_DETAILS_ID^T18^T42622

Joel.Rosi-Schwartz · **Joined:** Sat Sep 04, 2004 4:14 pm **Posts:** 13

After figuring out how to turn on meaninful logging in Cloudscape, I have made progress with understanding some of the issues of intergrating Hibernate with Cloudscape.

1) I mentioned in a previous post that while running the Junit tests for CaveatEmptor there were SQL errors relating to not being able to add contraints for foriegn keys. It turns out the Cloudscape has an 18 character limit on foriegn key names. Once I renamed these the first schemaexport ran without failures.

2) The next problem was caused by the foriegn keys being in place. At the end of the first testcase, teardown tries to drop the database. This results in a number of failures similar to the one I have included below. This log output is from Cloudscape, oddly (and a bit misleading) these failures are not reported in the junit output. The key to my moving forward with this now is to figure out why the 'DROP CONSTRAINT' operation if failing. Is this Cloudscape's problem or Hibernate's problem. Is there a work around?

Code:

2004-09-16 13:49:24.703 GMT Thread[DRDAConnThread_3,5,db2j.daemons] (XID = 519), (SESSIONID = 3), (DATABASE = NSSimpleDB), (DRDAID = NF000001.GC10-1013308820815797460{13}), Failed Statement is: drop
 table BILLING_DETAILS
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL040916024919120' because CONSTRAINT 'FK1_BANK_ACCT_ID' is dependent on that object.
  at com.ihost.cs.iapi.error.StandardException.newException(Unknown Source)
  at com.ihost.cs.iapi.sql.dictionary.ConstraintDescriptor.prepareToInvalidate(Unknown Source)
  at com.ibm.db2cs.bi.c.coreInvalidateFor(Unknown Source)
  at com.ibm.db2cs.bi.c.invalidateFor(Unknown Source)
  at com.ibm.db2cs.g.ax.dropAllConstraintDescriptors(Unknown Source)
  at com.ibm.db2cs.g.ax.executeConstantAction(Unknown Source)
  at com.ibm.db2cs.g.as.open(Unknown Source)
  at com.ibm.db2cs.x.m.execute(Unknown Source)
  at com.ibm.db2cs.m.i.executeStatement(Unknown Source)
  at com.ibm.db2cs.m.i.execute(Unknown Source)
  at com.ibm.db2cs.m.i.executeUpdate(Unknown Source)
  at com.ihost.cs.impl.drda.DRDAConnThread.parseEXCSQLIMM(Unknown Source)
  at com.ihost.cs.impl.drda.DRDAConnThread.processCommands(Unknown Source)
  at com.ihost.cs.impl.drda.DRDAConnThread.run(Unknown Source)
============= begin nested exception, level (1) ===========
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL040916024919120' because CONSTRAINT 'FK1_DEF_BIL_DET_ID' is dependent on that object.
  at com.ihost.cs.iapi.error.StandardException.newException(Unknown Source)
  at com.ihost.cs.iapi.sql.dictionary.ConstraintDescriptor.prepareToInvalidate(Unknown Source)
  at com.ibm.db2cs.bi.c.coreInvalidateFor(Unknown Source)
  at com.ibm.db2cs.bi.c.invalidateFor(Unknown Source)
  at com.ibm.db2cs.g.ax.dropAllConstraintDescriptors(Unknown Source)
  at com.ibm.db2cs.g.ax.executeConstantAction(Unknown Source)
  at com.ibm.db2cs.g.as.open(Unknown Source)
  at com.ibm.db2cs.x.m.execute(Unknown Source)
  at com.ibm.db2cs.m.i.executeStatement(Unknown Source)
  at com.ibm.db2cs.m.i.execute(Unknown Source)
  at com.ibm.db2cs.m.i.executeUpdate(Unknown Source)
  at com.ihost.cs.impl.drda.DRDAConnThread.parseEXCSQLIMM(Unknown Source)
  at com.ihost.cs.impl.drda.DRDAConnThread.processCommands(Unknown Source)
  at com.ihost.cs.impl.drda.DRDAConnThread.run(Unknown Source)
============= end nested exception, level (1) =========== 

This, of course, leaves the test enviroment unstable and it is all down hill from here. The rest of the tests now have mulitudes of problems because the test data has not been cleaned up.

3) I also tried to set up Cloudscape as the database for Junit tests in Hibernate 2.1.6. The results were not very encouraging; in the end the test run exausted java memory and failed. It appears from the output, though, the the problems are similar to the ones that I am running into with the CaveatEmptor test. I will take this one step at a time.

Thanks,
Joel

gavin · **Posted:** Thu Sep 16, 2004 11:18 am

In DB2Dialect:

Code:

   public boolean dropConstraints() {
      return false;
   }

Perhaps you need to override that.

Otherwise, perhaps cloudscape needs "drop table .... cascade constraints". Look at some of the other dialects to see how this stuff is handled on various dbs.

Joel.Rosi-Schwartz · **Joined:** Sat Sep 04, 2004 4:14 pm **Posts:** 13

The Cloudscape Reference Manual states that:

Quote:

DROP TABLE statement
DROP TABLE removes the specified table.

Syntax:
DROP TABLE table

Joel.Rosi-Schwartz · **Joined:** Sat Sep 04, 2004 4:14 pm **Posts:** 13

Sorry Gavin, I misunderstood what you where suggesting. I have added

Code:

public boolean dropConstraints() { 
      return true; 
}

to the DerbyDialect and now all of the junit tests in CaveatEmptor pass without problems.

In the Cloudscape log I am getting errors such as these:

Code:

Failed Statement is: alter table COMMENTS drop constraint FK3_ITEM_ID
ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'COMMENTS' because it does not exist.

Failed Statement is: drop table COMMENTS
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'COMMENTS' because it does not exist.

Can these be safely ignored or do need to further investigate the cascade behaviour?

What I plan on doing next is to run the Hibernate 2.1.6 junit tests against Cloudscape. Is it logical to assume that if all tests do not pass that the Cloudscape dialect needs further work? Is there an actual test suite that specifically validates a database for usage with Hibernate?

- joel