You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Marcin Skladaniec <ma...@ish.com.au> on 2011/07/11 10:08:44 UTC
pk generation strategy and multiple databases
Hello
I have a question about the cayenne modeller, the part where the
database data source is defined.
In our application the we configure the data source in a dummy way
(pointing to a in-memory derby db), as every customer will have a
different setup. Customer configuration not only differs in the database
location/url but also in different database engines, so the application
startup configures the datasource dynamically.
Now I have run into a bunch of problems at the same time.
First, can the datasource info be removed from the cayenne model files
alltogether? I've noticed that in 3.1M2 there is no separate xml file
for driver, but the nodes are defined in the main config file. My brief
attempt to just delete that info lead to nowhere, but that could be just
a problem with my setup.
Second, I have an issue with PK generation strategies. Those are defined
on the DbEntity level, which makes sense, but also makes it harder to
have different strategies for different database engines. This also
comes down to the documentation on
http://cayenne.apache.org/doc/primary-key-generation.html.
In the DatabaseEntity there are 3 options for the PK generation
strategy: "Default", "Database-Generated" and "Custom sequence".
I understand that Default refers to cayenne generated primary key, but
the documentation mentioned in the last paragraph states, under the
section describing database generated pk: "Currently MySQL and
SQLServer adapters have this feature turned on by default.".
I'm a little confused what is default anymore, and how cayenne
distinguishes the two.
Now finally my real problem, in cayenne generated pk scenario sqlserver
is said to use a stored procedure tied to AUTO_PK_TABLE to manage the
primary key. In my application this procedure is installed manually
before the cayenne service startup, I have verified its existence in the
database, but it is never available to cayenne, causing exception
attached at the end of the email.
What can cause such problem? is that the datasource defined in model?
I'm running out of ideas, any help would be greatly appreciated.
Using Cayenne 3.1M2.
With regards
Marcin
*
testUpdateStudentTutorFlags(ish.oncourse.server.cayenne.ContactTest)
Time elapsed: 0.208 sec <<< ERROR!
org.apache.cayenne.CayenneRuntimeException: [v.3.1M2 Mar 04 2011
19:49:48] Commit Exception
at
org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:839)
at
org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:750)
at
ish.oncourse.server.ISHDataContext.commitChanges(ISHDataContext.java:88)
at
ish.oncourse.server.cayenne.ContactTest.testUpdateStudentTutorFlags(ContactTest.java:51)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at
org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at
org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:53)
at
org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:123)
at
org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:104)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:164)
at
org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:110)
at
org.apache.maven.surefire.booter.SurefireStarter.invokeProvider(SurefireStarter.java:172)
at
org.apache.maven.surefire.booter.SurefireStarter.runSuitesInProcessWhenForked(SurefireStarter.java:104)
at
org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:70)
Caused by: java.sql.SQLException: Could not find stored procedure
'auto_pk_for_table'.
at
net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at
net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:563)
at
org.apache.cayenne.dba.sybase.SybasePkGenerator.longPkFromDatabase(SybasePkGenerator.java:179)
at
org.apache.cayenne.dba.JdbcPkGenerator.generatePk(JdbcPkGenerator.java:262)
at
org.apache.cayenne.access.DataDomainInsertBucket.createPermIds(DataDomainInsertBucket.java:165)
at
org.apache.cayenne.access.DataDomainInsertBucket.appendQueriesInternal(DataDomainInsertBucket.java:76)
at
org.apache.cayenne.access.DataDomainSyncBucket.appendQueries(DataDomainSyncBucket.java:78)
at
org.apache.cayenne.access.DataDomainFlushAction.preprocess(DataDomainFlushAction.java:182)
at
org.apache.cayenne.access.DataDomainFlushAction.flush(DataDomainFlushAction.java:134)
...
Re: pk generation strategy and multiple databases
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jul 13, 2011, at 7:16 PM, Aristedes Maniatis wrote:
> But if the model xml files point to (say) a Derby db and then you switch the DataSourceFactory to (say) MS-SQL, then I suspect the default PK strategy isn't being changed at the same time, since Derby and MS-SQL have different defaults.
Default strategy is tied to DbAdapter. Assuming you use AutoAdapter or if you change the adapter in runtime via API, the default strategy of that adapter will be used.
> I am not saying that it should switch, but it does make the concept of 'default' a bit confusing.
Actually I does, and I am the first one to admit it. We should probably rename it to "adapter-defined strategy".
Andrus
Re: pk generation strategy and multiple databases
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 14/07/11 2:05 AM, Andrus Adamchik wrote:
>
> Default is same as before - a per-adapter strategy of generating PK. The above mentioned sentence is about DB-generated strategy. It is not supported on all DB's due to driver limitations. So some Cayenne adapters have it on, others don't.
But if the model xml files point to (say) a Derby db and then you switch the DataSourceFactory to (say) MS-SQL, then I suspect the default PK strategy isn't being changed at the same time, since Derby and MS-SQL have different defaults. I am not saying that it should switch, but it does make the concept of 'default' a bit confusing.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: pk generation strategy and multiple databases
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jul 11, 2011, at 11:08 AM, Marcin Skladaniec wrote:
> First, can the datasource info be removed from the cayenne model files alltogether? I've noticed that in 3.1M2 there is no separate xml file for driver, but the nodes are defined in the main config file. My brief attempt to just delete that info lead to nowhere, but that could be just a problem with my setup.
Yes in 3.1 nodes are defined in the main config file. The normal approach to abstracting this data is via JNDIDataSourceFactory. Or in non-web environments (to avoid manually bootstrapping JNDI provider), just create a custom DataSourceFactory that would pull DB connection info in a way appropriate for the app.
> Second, I have an issue with PK generation strategies. Those are defined on the DbEntity level, which makes sense, but also makes it harder to have different strategies for different database engines.
True. Changing PK gen strategy in runtime is possible now, but is somewhat involved. I feel like many things in the mapping would benefit from a more straightforward way of overriding in runtime. Maybe need a separate DI service for PK gen.
> This also comes down to the documentation onhttp://cayenne.apache.org/doc/primary-key-generation.html.
> In the DatabaseEntity there are 3 options for the PK generation strategy: "Default", "Database-Generated" and "Custom sequence".
> I understand that Default refers to cayenne generated primary key, but the documentation mentioned in the last paragraph states, under the section describing database generated pk: "Currently MySQL and SQLServer adapters have this feature turned on by default.".
> I'm a little confused what is default anymore, and how cayenne distinguishes the two.
Default is same as before - a per-adapter strategy of generating PK. The above mentioned sentence is about DB-generated strategy. It is not supported on all DB's due to driver limitations. So some Cayenne adapters have it on, others don't.
In general I agree that we may need to add some clarity into PK generation strategies.
> Now finally my real problem, in cayenne generated pk scenario sqlserver is said to use a stored procedure tied to AUTO_PK_TABLE to manage the primary key. In my application this procedure is installed manually before the cayenne service startup, I have verified its existence in the database, but it is never available to cayenne, causing exception attached at the end of the email.
> What can cause such problem? is that the datasource defined in model? I'm running out of ideas, any help would be greatly appreciated.
Probably in the wrong schema. I am an infrequent user of SQLServer, but a few times a similar thing happened to me it was schema related.
Cheers,
Andrus