Integrate Derby into embedded database support [SPR-5738]

[spring-projects-issues]

Oliver Drotbohm opened SPR-5738 and commented

Suppiled patch works with version 10.5.1.1 of Apache Derby that introduces real in-memory databases.

---

Affects: 3.0 M3

Attachments:

• DerbyEmbeddedDatabaseConfigurer.patch (2.62 kB)
• noDerbyLog.patch (1.72 kB)
• spring-jdbc-derbysupport.patch (9.45 kB)

[spring-projects-issues]

Oliver Drotbohm commented

Added request to get Derby 10.5.1.1 integrated into SpringSource bundle repository. See https://issuetracker.springsource.com/browse/EBR-373

[spring-projects-issues]

Joris Kuipers commented

Please substitute all occurrences of hsql with derby (applying proper casing) in the new Derby configurer ;)

[spring-projects-issues]

Keith Donald commented

Oliver,

I committed the initial support. I had to comment out the EmbeddedDatabaeBuilder test case as when the full spring-jdbc test suite ran, the Derby db initialization in the jdbc.config integration test was causing a side-effect. Specifically, it seems even after a shutdown the schema still exists. Can you confirm this? Does this mean we need to add a destroy-script capability so the schema can be dropped?

[spring-projects-issues]

Oliver Drotbohm commented

Are you sure you had version 10.5.1.1 in the classpath for the tests? The issues you describe pretty much matches the behaviour you face when having an older version of Derby in the classpath. With later versions it does not recognize the "memory" part of the JDBC URL and creates the database in files which in turn does not drop the schema when being shut down. With pure in memory Derby this should work. At least I could run the testcases repeatedly on my machine.

[spring-projects-issues]

Keith Donald commented

Yes, I'm sure. Here's the exception

[junit] Testsuite: org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilderTests
[junit] Tests run: 5, Failures: 0, Errors: 1, Time elapsed: 0.092 sec
[junit] 
[junit] Testcase: testBuildDerby(org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilderTests):	Caused an ERROR
[junit] SQLException occurred populating embedded database
[junit] java.lang.RuntimeException: SQLException occurred populating embedded database
[junit] 	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.populateDatabase(EmbeddedDatabaseFactory.java:171)
[junit] 	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.initDatabase(EmbeddedDatabaseFactory.java:139)
[junit] 	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.getDatabase(EmbeddedDatabaseFactory.java:119)
[junit] 	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder.build(EmbeddedDatabaseBuilder.java:85)
[junit] 	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilderTests.testBuildDerby(EmbeddedDatabaseBuilderTests.java:38)
[junit] Caused by: java.sql.SQLException: Table/View 'T_TEST' already exists in Schema 'SA'.
[junit] 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
[junit] 	at org.springframework.jdbc.datasource.embedded.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:118)
[junit] 	at org.springframework.jdbc.datasource.embedded.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:75)
[junit] 	at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.populateDatabase(EmbeddedDatabaseFactory.java:169)
[junit] Caused by: java.sql.SQLException: Table/View 'T_TEST' already exists in Schema 'SA'.
[junit] 	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
[junit] 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
[junit] Caused by: ERROR X0Y32: Table/View 'T_TEST' already exists in Schema 'SA'.
[junit] 	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
[junit] 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(Unknown Source)
[junit] 	at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(Unknown Source)
[junit] 	at org.apache.derby.impl.sql.execute.CreateTableConstantAction.executeConstantAction(Unknown Source)
[junit] 	at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
[junit] 	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
[junit] 	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
[junit] 
[junit] 

Running the test standalone doesn't produce this exception.

[spring-projects-issues]

Keith Donald commented

Also, a derby.log is created in the root org.springframework.jdbc dir:

---

2009-05-16 15:46:12.457 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.5.1.1 - (764942): instance a816c00e-0121-4a1a-16f9-0000001d
1fc8
on database directory memory:/Users/keith/Documents/spring-framework/org.springframework.jdbc/testdb

Database Class Loader started - derby.database.classpath=''

2009-05-16 15:46:13.574 GMT:
Shutting down instance a816c00e-0121-4a1a-16f9-0000001d1fc8

---

---

2009-05-16 15:46:18.413 GMT:
Booting Derby version The Apache Software Foundation - Apache Derby - 10.5.1.1 - (764942): instance 6c44409f-0121-4a1a-16f9-0000001d
1fc8
on database directory memory:/Users/keith/Documents/spring-framework/org.springframework.jdbc/testdb

Database Class Loader started - derby.database.classpath=''

[spring-projects-issues]

Keith Donald commented

Just a little more info here: I see the doc page for Derby's embedded DB support talks about a Sample app that does indeed drop the schema before shutting down the database. I'mgetting the feeling we're going to have to do this, otherwise the DB itself is left around in memory even after shutdown. Oliver, can you confirm?

[spring-projects-issues]

Joris Kuipers commented

Here's a patch that fixes the issue (and also fixes the JavaDoc to refer to Derby).
I also took the liberty to remove the code that checks for instanceof SQLNonTransientConnectionException (seems unnecessary) and the check to see if logger is warn-enabled: avoiding the overhead of the call to log.warn isn't worth the check in this case IMHO (but I'm not familiar with coding standards for Spring, that might require this).

Index: DerbyEmbeddedDatabaseConfigurer.java

---

--- DerbyEmbeddedDatabaseConfigurer.java (revision 1199)
+++ DerbyEmbeddedDatabaseConfigurer.java (working copy)
@@ -15,14 +15,16 @@
*/
package org.springframework.jdbc.datasource.embedded;

+import java.io.File;
+import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
-import java.sql.SQLNonTransientConnectionException;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
+import org.apache.derby.impl.io.VFMemoryStorageFactory;
import org.apache.derby.jdbc.EmbeddedDriver;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;
import org.springframework.util.ClassUtils;
@@ -47,9 +49,9 @@
}

/**

• • Get the singleton {@link HsqlEmbeddedDatabaseConfigurer} instance.

• • Get the singleton {@link DerbyEmbeddedDatabaseConfigurer} instance.
  • @return the configurer

• • @throws ClassNotFoundException if HSQL is not on the classpath

• • @throws ClassNotFoundException if Derby is not on the classpath
    */
    public static synchronized DerbyEmbeddedDatabaseConfigurer getInstance() throws ClassNotFoundException {
    if (INSTANCE == null) {
    @@ -75,22 +77,29 @@
    shutdownDataSource.setUrl(String.format(URL_TEMPLATE, databaseName, "shutdown=true"));
    connection = shutdownDataSource.getConnection();
    } catch (SQLException e) {

  if (e instanceof SQLNonTransientConnectionException) {

  SQLNonTransientConnectionException ex = (SQLNonTransientConnectionException) e;

  if (!SHUTDOWN_CODE.equals(ex.getSQLState())) {

  logException(e);

  }

  if (SHUTDOWN_CODE.equals(e.getSQLState())) {

       purgeDatabase(databaseName);
  } else {

  logException(e);

            logger.warn("Could not shutdown in-memory Derby database", e);
       }
  } finally {
       JdbcUtils.closeConnection(connection);
  }

}

• private void logException(SQLException e) {

  if (logger.isWarnEnabled()) {

  logger.warn("Could not shutdown in-memory Derby database", e);

• /**

• • Purges the in-memory database, to prevent it from hanging around after
• • being shut down
• • @param databaseName

• */

• private void purgeDatabase(String databaseName) {

  // TODO: update this code once Derby adds a proper way to remove an in-memory db

  // (see http://wiki.apache.org/db-derby/InMemoryBackEndPrimer for details)

  try {

  VFMemoryStorageFactory.purgeDatabase(new File(databaseName).getCanonicalPath());

  } catch (IOException ioe) {

       logger.warn("Could not purge in-memory Derby database", ioe);
  }

}

}

[spring-projects-issues]

Joris Kuipers commented

Including patch in comment looked like it might be broken b/o the markup applied: here's the patch as an attachment instead

[spring-projects-issues]

Keith Donald commented

Patch applied; all tests now pass. Thanks Joris!

Do either of you guys know how to prevent Derby from writing derby.log to the root org.springframework.jdbc project directory?

[spring-projects-issues]

Joris Kuipers commented

This post might be of interest here:
http://mail-archives.apache.org/mod_mbox/db-derby-user/200610.mbox/%3C4530CD3D.7050301@sun.com%3E
I know you can also use the derby.stream.error.file system property to change the Derby.log location, but that won't allow you to disable the log itself AFAIK.

[spring-projects-issues]

Joris Kuipers commented

That posting offered a solution indeed: here's a small patch against trunk that prevents the log file from being created and written to. I verified that the static method that returns the OutputStream does not have to be public (nor the class itself, as you changed it to a final class with default visibility).

[spring-projects-issues]

Björn Michael commented

What about a DerbyEmbeddedDatabaseConfigurer that support connection URL without "memory" as subsubprotocol like "jdbc:derby:testDB;create=true". Therefore the database content is still present in filesystem after e.g. tests and can be investigated with other database tools and is quite useful for strange outcomes during development of tests.
It would be possible to write such an EmbeddedDatabaseConfigurer on my own but the uncommon shutdown procedure of derby can be spared for many people.