Hibernate dialect via DS

[elkner]

Hmmm, wondering, why one can't specifiy the hibernate dialect in the datasource metadata via *-ds.xml. IMHO the dialect is a property of the ds and not really of the persitence unit itself.

The problem I have with specifying it in the persistence.xml is, that one needs to [re]package the application/pu for each different db. But what datasource finally should be used, is ideally not a task of the application component provider, application assembler but rather the one of the application deployer or sys admin (which is reponsible for making the required DS available to the application). So IMHO the persistence.xml file seems not to be the right place for specifying the db dialect.

AFAIK, the EMfactory needs to lookup the specified DS or is bound to a DS. So why is it not able to take another step and lookup the metadata of the DS [and configure the factory ...]? Is it a big performance penalty or simply not possible dueto the design?

[epbernard]

Actually Hibernate is already able to guess the dialect from the JDBC metadata if not specified

[elkner]

Even MySQLInnodb? Last time I've seen something in the hibernate forum was, that it is not really supported (or something like that).

[epbernard]

The issue with InnoDb is that it is not a different DB, just a different internal table persistence engine. But if somewhow MySQL export in JDBC metadata the fact that InnoDb is the underlying engine, then we'll be able to dfferenciate InnoDb from other engines.

[elkner]

OK, I'll check that. But anyway, the original question was another one ;-)

[elkner]

BTW: Took a nap and thought about it: Even if hibernate would be able to detect innodb support it would not solve the problem.

E.g.: The trailblazer uses for one entity bean a MyISAM table to be able to do a fulltext search and for all others InnoDB tables. Thus (for ease of deployment) one would setup two persistence units, one with a datasource pointing to the DB with InnoDBDialect and one to {another|the same} DB with MyISAMDialect. So a dialect metadata lookup via -ds.xml would be really useful!

[epbernard]

The dialect specialization is only useful for table generation and to workaround some small bugs in the innodb engine, so in this particular case the discovery is good enough, we don't really care which subdialect is used

[elkner]

But that's exactly the point for ease of deployment: I and many other people I know/have spoken with, do not want to write sophisticated, complex applications just for setting up a database and waisting a lot of time, to find out about the tiny differences between db a and db b. Hibernate is already able to handle it properly, so why is it not possible to give it a hint in a proper way and let it do its work? Why should developers re-invent the wheel ?

BTW: This thread reminds me a lot on Scott Ambler's talk on Javapolis 2005 (Agile Model Driven Developent) wrt. to change prevention process ;-)
See
http://www.javapolis.com/JP05Content/talks/day5/scott1/index.html (7. Reality)

[epbernard]

Honnestly i don't understand your point.
Hibernate can guess most of the time the right Dialect wo any explicit user property.

If for some reason, the user wants to fine tune that, he can add one single property aka hibernate.dialect

And if you don't care about the "tiny little" difference between InnoDB and MyISAM, the defaut behavior is good enough for you anyway :-)

About letting the *-ds.xml define the dialect, this is not a portable solution. If you deploy HEM on Glassfish, this will just fail.

[elkner]

Hmm, actually I care about the "tiny little" differences, because as you replied in a thread, the FKs may save my butt ;-). [And also I would be happy to have a MySQL5InnoDbDialect without the need of patching hibernate].

But the problem I see is simply, that if I specify a dialect, which doesn't match the db configured in the datasource, I can get into deep trouble. Or does hibernate ignore it, if e.g. MySQLInnoDBDialect is specified and the datasource is mapped to an oracle db ? If so, I could live with it. Otherwise, I've a problem with it.

And if I look at sample.ds files, there is already a:

<metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>

For what reason is this there?

BTW: I guess, the implementors want to take the easy way wrt. to standalone containers (or simply followed hibernate like "look, how they did it" ;-)), but this is IMHO plain wrong to specify somewhere a datasource and in a total different place the properties, the datasource might have.

So perhaps I should start a thread on glassfish as well, since they seem to do it via persistence properties as well :(

[maxandersen]

have you tried and use ${somename.dialect} and set the somename.dialect as a system property.

not perfect, but should work afaik.

[epbernard]

metadata type-mapping is only used by the CMP engine AFAIK, is optional and is specific to JBoss AS

What if you want to use 2 different EM implementations, you'll realize that the dialect delcaration belongs to the persistence unit, not the DS.

No Hibernate does not do checking on a specific Dialect.

[elkner]

"epbernard" wrote:
metadata type-mapping is only used by the CMP engine AFAIK, is optional and is specific to JBoss AS

But I guess, at least one smart guy thought, it makes sense to put it there and not into jboss-service.xml or anywhere else. And if I#m not wrong, EJB3 might/should be seen as a [the?] replacement for CPM...

What if you want to use 2 different EM implementations, you'll realize that the dialect delcaration belongs to the persistence unit, not the DS.

Well, just because everybody does the wrong thing, it does not automatically mean, that it is right! And btw., which different EM implementations: I know toplink, which obviously had a look on hibernate and made the same error (perhaps they thought, it is easier to lookup that stuff in something, which is definitely there, even if it doesn't belong there - perhaps it should be called a quick and dirty workaround). But more and more people realize, that the dialect definition is simply not appropriate for the persistence.xml and thus toplink also tries to solve the problem introduced by this misconception by trying to autodetect the kind of datasource, too.

Also I've heared of KODO. Haven't had the time to look at it, but i hope, they do not make the same error. And as far as I know, no spec says, that datasource specific stuff must be garbaged into persistence.xml. Actually I would not see a problem to have a *-ds.xml metadata section like that

<metadata>
 <type-mapping>hibernate.dialect:org.hibernate.dialect.Oracle9Dialect</type-mapping>
 <type-mapping>toplink.platform.class.name:oracle.toplink.essentials.platform.database.oracle.OraclePlatform</type-mapping>
</metadata>

No Hibernate does not do checking on a specific Dialect.

So obviously there is a big gap between requirements and what might be called "state of the art". And perhaps the biggest problem is, that a javax.sql.Datasource does not provide a standardized method for seting/getting metadata...

[akakunin]

So, is any solution are available?

I think it is good idea to have some sort of autodetection of hibernate dialect or to be able to specify it in DS configuration file.

For example I want to deploy my spring-based application as one war-file. For database configuration I'm using emforge-ds.xml file with specifiyng the datasource used in this applicaiton. This file is deployed seperatelly from whole war and may be configured by user.

But, problem is that hibernate configuration are stored in war and if user will want to use some database in this emforge-ds.xml - he also need to change hibernate configuration in war.... that is not very comfortable.

It will be nice to have some sort of autodetection but according to (http://opensource.atlassian.com/projects/hibernate/browse/HB-1107?page=all) seems it will not be implemented. Or to be able to specify dialect in datasoruce (by some way).

Are there any solution exist?

[epbernard]

Like I said it actually is implemented already for most databases.