Re: attempting to migrate from postgres to derby

[Steinar Bang]

I use PostgreSQL as my production database, and I use derby during
development and testing.

I use liquibase to configure and populate the databases:
 1. I have a library jar that sets up a database schema using liquibase
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.liquibase/src/main/java/no/priv/bang/ukelonn/db/liquibase/UkelonnLiquibase.java#L25
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.liquibase/src/main/resources/db-changelog/db-changelog-1.0.0.xml#L20
 2. I have a derby plugin that sets up the schema and uses liquibase to
populate the test database
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.derbytest/src/main/java/no/priv/bang/ukelonn/db/derbytest/UkelonnDatabaseProvider.java#L44
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.derbytest/src/main/resources/sql/data/example_users.sql
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.derbytest/src/main/resources/sql/data/example_accounts.sql
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.derbytest/src/main/resources/sql/data/example_administrators.sql
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.derbytest/src/main/resources/sql/data/example_transaction_types.sql
 
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.derbytest/src/main/resources/sql/data/example_transactions.sql
  3. I have a postgresql pugin that sets up the schema and uses
 liquibase to set up some initial data
  
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.postgresql/src/main/java/no/priv/bang/ukelonn/db/postgresql/PGUkelonnDatabaseProvider.java#L42
  
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.postgresql/src/main/resources/db-changelog/sql/initial_users.sql
  
https://github.com/steinarb/ukelonn/tree/master/ukelonn.db.postgresql/src/main/resources/db-changelog/sql
  
https://github.com/steinarb/ukelonn/blob/master/ukelonn.db.postgresql/src/main/resources/db-changelog/sql/initial_transaction_types.sql

TIP: Run a derby network server with maven

[Steinar Bang]

I needed to temporarily run a derby network server to examine behaviour
of some of my SQL queries, that was different in derby (which I use for
tests, both unit tests and integration tests), and PostgreSQL.

What I did, was to create a maven project that uses the
derby-maven-plugin[1] to start and run a derby network server[2].

To create a local derby network server, listening on port 1527, do:
 mkdir -p ~/git
 cd ~/git
 git clone https://github.com/steinarb/derby-runner.git
 cd derby-runner
 mvn install derby:run

References:
[1] 
[2] 

Re: Can the embedded JDBC driver and the client JDBC driver co-exist?

[Steinar Bang]

> Bryan Pendleton :

> Anyway, yes, please try to figure out if you have any CLASSPATH conflicts,
> and please do let us know the results of your investigations!

The reason for my problems turned out to be quite simple.

I'm using the pax-jdbc DerbyDataSourceFactory in my derby database
bundle, so I was using that class in my unit test as well, assuming it
would be able to create the correct kind of data source, depending on
the JDBC URL.

However, when I looked at the source of the
DerbyDataSourceFactory.createDataSource() method, it looked like this:

public DataSource createDataSource(Properties props) throws SQLException {
EmbeddedDataSource ds = new EmbeddedDataSource();
setProperties(ds, props);
return ds;
}

...and an EmbeddedDataSource won't be able to create a client
connection... or so I think...?

So instead I created a client datasource directly, and that worked fine:
boolean createUkelonnDatabase = true;
ClientConnectionPoolDataSource dataSource = new 
ClientConnectionPoolDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("ukelonn");
dataSource.setPortNumber(1527);
if (createUkelonnDatabase) {
dataSource.setCreateDatabase("create");
}
PooledConnection connect = dataSource.getPooledConnection();

With this connection, Liquibase can do its thing, and set up the schema
and data in the server.

Thanks again!

Re: Can the embedded JDBC driver and the client JDBC driver co-exist?

[Steinar Bang]

> Bryan Pendleton :

> I suspect your problems are caused by having two different versions of
> the Derby jars intermingled. The behavior of CLASSPATH is complex,
> especially for things like the JDBC 4.0 autoload features, and Derby
> jars are often packaged and bundled into other software packages,
> making it easy to have multiple copies of the Derby code without
> realizing it.

Ok, I interpret this answer to be that there shouldn't be any problems
with pulling the embedded driver and the client driver into the same
java program?

The google hits when searching answers to this issue has hinted at
having two different versions of derby in the classpath, so this is what
I have looked at before postign a question here.

The maven dependencies look OK, but an inlined derby in a different jar
is a definite possibility.

I'll try investigating this possibility before trying to isolate a test
case.

Thanks!


- Steinar

Can the embedded JDBC driver and the client JDBC driver co-exist?

[Steinar Bang]

In my project[1] I am using a derby in-memory database with the embedded
driver for unit testing and integration testing.  I am using PostgreSQL
as the production server.

I am using Liquibase[2] to set up the schema for both Derby and
PostgreSQL, and I'm also using Liquibase to set up the test data in the
derby database.

What I wanted to do, was to dump the test database to a Derby network
server and do some tests on the data.  My changes worked fine in
PostgreSQL but not in the derby tests, and I wanted the chance to take a
look at them.

So what I did was add a pseudo unit test to a test class, that would
connect to "jdbc:derby://localhost:1527/ukelonn"[3].

When I tried running that test, it was unable to find the driver.

So I googled a bit more, and added a dependency to the derby client
jar in the test scope[4].

But then the unit test failed with the following error:
 java.lang.ClassCastException: org.apache.derby.jdbc.ClientDriver cannot be 
cast to org.apache.derby.jdbc.InternalDriver
at org.apache.derby.jdbc.BasicEmbeddedDataSource40.findDriver(Unknown 
Source)
at 
org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource.createPooledConnection(Unknown
 Source)
at 
org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource.getPooledConnection(Unknown
 Source)
at 
no.priv.bang.ukelonn.bundle.db.test.UkelonnDatabaseProviderTest.addUkelonnSchemaAndDataToDerbyServer(UkelonnDatabaseProviderTest.java:296)

Are the problems caused by me having both the embedded driver and the
network driver loaded into the same java application (in this case: the
same JUnit test)?

If there is a conflict between the embedded JDBC driver and the client
JDBC driver, is there a way around the conflict, other than creating a
separate maven project just to upload the liquibase files?

Thanks!


- Steinar



References:
 [1] 
 [2] 
 [3] 

 [4]