You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jdo-dev@db.apache.org by Michael Bouschen <mi...@akquinet.de> on 2012/02/12 20:22:40 UTC
DN support for derby sequences
Hi Andy,
I started looking at JDO-682
(https://issues.apache.org/jira/browse/JDO-682). The JIRA proposes to
make use of a sequence for the datastore identity of some of the
shoppingcart pc classes. The test runs into the following exception:
java.lang.UnsupportedOperationException: This adapter does not support
sequences
at
org.datanucleus.store.rdbms.adapter.DatabaseAdapter.getSequenceCreateStmt(DatabaseAdapter.java:1089)
at
org.datanucleus.store.rdbms.valuegenerator.SequenceGenerator.createRepository(SequenceGenerator.java:262)
at
org.datanucleus.store.rdbms.valuegenerator.AbstractRDBMSGenerator.obtainGenerationBlock(AbstractRDBMSGenerator.java:162)
Is it an issue to support sequences in the derby adapter? The derby
database itself supports sequences since version 10.6.
Regards Michael
--
*Michael Bouschen*
*Prokurist*
akquinet tech@spree GmbH
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen@akquinet.de
Web: www.akquinet.de <http://www.akquinet.de>
akquinet tech@spree GmbH, Berlin
Geschäftsführung: Martin Weber, Dr. Torsten Fink
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680
Re: DN support for derby sequences
Posted by Andy Jefferson <an...@datanucleus.org>.
Hi Michael,
> Do you see any issue, if the spec would define the same default for
> sequences? Then DataNucleus could take the PMF schema definition to
> prefix the sequence name the same way it currently does for all the
> table names.
I'd envisage no issue ... apart from someone having to find time to add that
code (to the Derby adapter, and any other adapters that may require it).
--
Andy
DataNucleus (http://www.datanucleus.org)
Re: DN support for derby sequences
Posted by Michael Bouschen <mb...@apache.org>.
Hi Andy,
thanks for the hint.
Actually, prefixing the sequence name in the sequence definition
<sequence name="id_seq"
datastore-sequence="DATASTOREIDENTITY8.DATASTORE_SEQ"/>
or specifying the schema to the package including the sequence
<package name="org.apache.jdo.tck.pc.shoppingcart"
schema="DATASTOREIDENTITY8">
solves the problem.
I will use the schema definition on the package to fix this.
Chapter 18 XML Metadata of the spec defines that a schema declared at
the jdo, orm, package, class or interface level, specifies the schema to
be used as the default for tables contained therein. And if not
specified the schema defaults to the PMF schema property (see end of
page 221 and beginning of page 222 of the JDO 3.0 spec).
Do you see any issue, if the spec would define the same default for
sequences? Then DataNucleus could take the PMF schema definition to
prefix the sequence name the same way it currently does for all the
table names.
Regards Michael
>> But the test cases fail, because it tries to create the sequence
>> included in the schema definition:
>> Could not create new "sequence" DATASTORE_SEQ since autoCreate flags
>> do not allow it.
>>
>> Any idea why the existing sequence is not used? I double-checked the
>> schema and the mapping metadata, but cannot find anything.
> It is being used ... or at least trying to. Sadly when the statement
> VALUES NEXT VALUE FOR DATASTORE_SEQ
> is used to create a PreparedStatement, it throws an exception
>
> 07:43:42,677 (main) INFO [DataNucleus.General] ->> reserveBlock caught
> exception
> java.sql.SQLSyntaxErrorException: SEQUENCE 'DATASTORE_SEQ' does not exist.
> at
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> Source)
> at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> Source)
> at
> com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:190)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:327)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:267)
> at
> org.datanucleus.store.rdbms.valuegenerator.SequenceGenerator.reserveBlock(SequenceGenerator.java:135)
> at
> org.datanucleus.store.valuegenerator.AbstractGenerator.reserveBlock(AbstractGenerator.java:305)
> at
> org.datanucleus.store.rdbms.valuegenerator.AbstractRDBMSGenerator.obtainGenerationBlock(AbstractRDBMSGenerator.java:101)
>
>
> So the question is why does Derby not allow that statement. Maybe you need to
> prefix your schema name on the sequence name ?
>
>
>
--
*Michael Bouschen*
*Prokurist*
akquinet tech@spree GmbH
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen@akquinet.de
Web: www.akquinet.de <http://www.akquinet.de>
akquinet tech@spree GmbH, Berlin
Geschäftsführung: Martin Weber, Dr. Torsten Fink
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680
--
*Michael Bouschen*
*Prokurist*
akquinet tech@spree GmbH
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen@akquinet.de
Web: www.akquinet.de <http://www.akquinet.de>
akquinet tech@spree GmbH, Berlin
Geschäftsführung: Martin Weber, Dr. Torsten Fink
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680
Re: DN support for derby sequences
Posted by Andy Jefferson <an...@datanucleus.org>.
> But the test cases fail, because it tries to create the sequence
> included in the schema definition:
> Could not create new "sequence" DATASTORE_SEQ since autoCreate flags
> do not allow it.
>
> Any idea why the existing sequence is not used? I double-checked the
> schema and the mapping metadata, but cannot find anything.
It is being used ... or at least trying to. Sadly when the statement
VALUES NEXT VALUE FOR DATASTORE_SEQ
is used to create a PreparedStatement, it throws an exception
07:43:42,677 (main) INFO [DataNucleus.General] - >> reserveBlock caught
exception
java.sql.SQLSyntaxErrorException: SEQUENCE 'DATASTORE_SEQ' does not exist.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:190)
at
org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:327)
at
org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:267)
at
org.datanucleus.store.rdbms.valuegenerator.SequenceGenerator.reserveBlock(SequenceGenerator.java:135)
at
org.datanucleus.store.valuegenerator.AbstractGenerator.reserveBlock(AbstractGenerator.java:305)
at
org.datanucleus.store.rdbms.valuegenerator.AbstractRDBMSGenerator.obtainGenerationBlock(AbstractRDBMSGenerator.java:101)
So the question is why does Derby not allow that statement. Maybe you need to
prefix your schema name on the sequence name ?
--
Andy
DataNucleus (http://www.datanucleus.org)
Re: DN support for derby sequences
Posted by Michael Bouschen <mb...@apache.org>.
Hi Andy,
the database schema now includes the definition of the sequence used by
the test cases in detach.conf. So I thought I could run the tck w/o the
datanuclues auto create table feature. I changed the jdori-pmf.properties to
datanucleus.autoCreateTables=false
But the test cases fail, because it tries to create the sequence
included in the schema definition:
Could not create new "sequence" DATASTORE_SEQ since autoCreate flags
do not allow it.
Any idea why the existing sequence is not used? I double-checked the
schema and the mapping metadata, but cannot find anything.
You find the datanucleus logging output attached below.
Regards Michael
> Hi Andy,
>
> thanks for the links.
>
> I added a DN JIRA:
> http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-581 and
> attached a patch for the DerbyAdapter class. I also added a patch to
> the JDO JIRA https://issues.apache.org/jira/browse/JDO-682 changing
> the schema and the mapping to make use of sequences in derby. So the
> configuration detach.conf will test the DN support for sequences in
> derby.
>
> Regards Michael
>
>>> Is it an issue to support sequences in the derby adapter? The derby
>>> database itself supports sequences since version 10.6.
>> Hi Michael,
>>
>> you can easily contribute support.
>> The adapter for Derby at
>> http://datanucleus.svn.sourceforge.net/viewvc/datanucleus/platform/store.rdbms/trunk/src/java/org/datanucleus/store/rdbms/adapter/DerbyAdapter.java?revision=14093&view=markup
>>
>> needs updating to include support for versions 10.6+ (only).
>>
>> To see an example of an adapter which has support look at
>> http://datanucleus.svn.sourceforge.net/viewvc/datanucleus/platform/store.rdbms/trunk/src/java/org/datanucleus/store/rdbms/adapter/HSQLAdapter.java?revision=11607&view=markup
>>
>> and search for "sequence" (case-insensitive); ought to give you
>> enough of an
>> idea what is needed. Then raise a DN JIRA and attach a patch.
>>
>
>
--
*Michael Bouschen*
*Prokurist*
akquinet tech@spree GmbH
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen@akquinet.de
Web: www.akquinet.de <http://www.akquinet.de>
akquinet tech@spree GmbH, Berlin
Geschäftsführung: Martin Weber, Dr. Torsten Fink
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680
Re: DN support for derby sequences
Posted by Michael Bouschen <mb...@apache.org>.
Hi Andy,
thanks for the links.
I added a DN JIRA:
http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-581 and attached
a patch for the DerbyAdapter class. I also added a patch to the JDO JIRA
https://issues.apache.org/jira/browse/JDO-682 changing the schema and
the mapping to make use of sequences in derby. So the configuration
detach.conf will test the DN support for sequences in derby.
Regards Michael
>> Is it an issue to support sequences in the derby adapter? The derby
>> database itself supports sequences since version 10.6.
> Hi Michael,
>
> you can easily contribute support.
> The adapter for Derby at
> http://datanucleus.svn.sourceforge.net/viewvc/datanucleus/platform/store.rdbms/trunk/src/java/org/datanucleus/store/rdbms/adapter/DerbyAdapter.java?revision=14093&view=markup
> needs updating to include support for versions 10.6+ (only).
>
> To see an example of an adapter which has support look at
> http://datanucleus.svn.sourceforge.net/viewvc/datanucleus/platform/store.rdbms/trunk/src/java/org/datanucleus/store/rdbms/adapter/HSQLAdapter.java?revision=11607&view=markup
> and search for "sequence" (case-insensitive); ought to give you enough of an
> idea what is needed. Then raise a DN JIRA and attach a patch.
>
--
*Michael Bouschen*
*Prokurist*
akquinet tech@spree GmbH
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen@akquinet.de
Web: www.akquinet.de <http://www.akquinet.de>
akquinet tech@spree GmbH, Berlin
Geschäftsführung: Martin Weber, Dr. Torsten Fink
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680
Re: DN support for derby sequences
Posted by Andy Jefferson <an...@datanucleus.org>.
> Is it an issue to support sequences in the derby adapter? The derby
> database itself supports sequences since version 10.6.
Hi Michael,
you can easily contribute support.
The adapter for Derby at
http://datanucleus.svn.sourceforge.net/viewvc/datanucleus/platform/store.rdbms/trunk/src/java/org/datanucleus/store/rdbms/adapter/DerbyAdapter.java?revision=14093&view=markup
needs updating to include support for versions 10.6+ (only).
To see an example of an adapter which has support look at
http://datanucleus.svn.sourceforge.net/viewvc/datanucleus/platform/store.rdbms/trunk/src/java/org/datanucleus/store/rdbms/adapter/HSQLAdapter.java?revision=11607&view=markup
and search for "sequence" (case-insensitive); ought to give you enough of an
idea what is needed. Then raise a DN JIRA and attach a patch.
--
Andy
DataNucleus (http://www.datanucleus.org)