Thread: [hibernate-team] PostgreSQLDialect
Hi Guys,<br /><br />I'm one of the hibernate(<a href="http://hibernate.org" target="_blank">http://hibernate.org</a>) teamcommiters and I'm here to ask you for a little help :-)<br />I'm trying to improve the support of hibernate to Postgre(andother databases), but I'm don't have *that* knowledge in database functions and behavior. I'm already done a coupleof improvements, but I'm trying to map all your functions, for example, to allow our users to use most of databasefunctions with HQL. And to do that we must do some changes. <br /><br />What we must do(ok, what we *can* do :-)) is change the file bellow, adding the functions that are useful to this file:<br /><br /><a href="http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java" target="_blank"> http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java</a><br clear="all"/><br />This class is just a class that says to hibernate "hey, I'm a Postgree database and I'm work that way".Here we register database types(with registerColumnType()), functions(with registerFunction()), and override some methodsmethods that says to hibernate some database behaviors. This class extends our base Dialect, that is just a classwith some basic info. <br /><br /><a href="http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java">http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java </a><br/><br />So, if someone wanna help, please let me know :-)<br /><br /><br />Cya!<br /><br />-- <br /><a href="http://plentz.org/"target="_blank">http://plentz.org/</a><br />"Provide options, don't make lame excuses."
On Sun, Nov 11, 2007 at 12:04:51PM -0300, Diego Pires Plentz wrote: > I'm trying to improve the support of hibernate to Postgre(and other > databases), but I'm don't have *that* knowledge in database functions and > behavior. I'm already done a couple of improvements, but I'm trying to map > all your functions, for example, to allow our users to use most of database > functions with HQL. And to do that we must do some changes. Hi, I've never used Hibernate but it seems to be that table of functions could be generated automatically. A few other things: - You map "text" to CLOB. Not exactly sure what CLOB refers to but text column are not generally used for large objects. I mean, you can store up to a GB in them, but most such columns are not going to be large. - You have supportsRowValueConstructorSyntax commented out. It does, if you have a recent enough version, or do you mean something else? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
> Hi, I've never used Hibernate but it seems to be that table of
> functions could be generated automatically.
That's the obvious solution. It would be nice if the dialect could
query the database itself to get a list of functions, since there will
be different sets of functions for different server versions, and the
functions can change when people install contrib modules or their own
functions. However, it doesn't look like the constructor for the
dialect gets given a connection or anything, so we'll probably have to
settle for static lists. It wouldn't be very hard to write a little
bit of java to parse pg_proc.h, but you'd want to filter out the types
that hibernate doesn't understand. One problem is that hibernate users
can install their own "types" - so hibernate might understand e.g.
polygons or whatever, but we won't know that at dialect initialization
time.
As someone who has contributed patches to both hibernate and pgsql I'd
be happy to help out on this, whatever the best way forward happens to
be. Top notch postgresql support in hibernate is something that I'd
very much like to see (and that goes for other JPA implementations as
well). I wasn't aware that it was particularly lacking, but clearly if
a function must be registered in the dialect to be usable by HQL,
there are an awful lot of functions that won't be available. I wonder
what happens with custom operators like tsearch provides...
> - You map "text" to CLOB. Not exactly sure what CLOB refers to but text
> column are not generally used for large objects. I mean, you can store
> up to a GB in them, but most such columns are not going to be large.
Actually, it's clob being mapped to text. I don't see a huge problem
with that, really, it'll often be mapped to a String at the java end
anyway. Think about it from the perspective of someone writing a
database agnostic hibernate application - they want a field to store
character data which can potentially be quite big - big enough that
they don't want to set arbitrary limits on it. So text pretty much
fits the bill since toasting was introduced.
It would be nice if we could register string data with no explicit
length as belonging to text as well, but it's not obvious how to do
that. Hmm.
The BLOB mapping is the one that looks wrong to me - surely that
should be bytea as well as varbinary, unless hibernate is explicitly
invoking the large object api. Perhaps it is.Although:public boolean useInputStreamToInsertBlob() { return false;}
and in particular:public boolean supportsExpectedLobUsagePattern() { // seems to have spotty LOB suppport return
false;}
I wonder what the fallback lob usage pattern is. Someone with better
knowledge of our jdbc driver might be able to point out whether the
above functions are saying the right things or not.
> - You have supportsRowValueConstructorSyntax commented out. It does, if
> you have a recent enough version, or do you mean something else?
The way to fix both that and the differing available functions would
probably be to have a subclass of the dialect for each server version.
MySQL seems to have about 5 :)
http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/package-summary.html.
As a side note to Diego, I'll say that it's great to see a hibernate
commiter being proactive about improving these things. Getting
attention to a bug or bugfix hasn't always been easy.
Cheers
Tom
Wow, quick responses :-) On Nov 11, 2007 3:11 PM, Tom Dunstan <pgsql@tomd.cc> wrote: > > Hi, I've never used Hibernate but it seems to be that table of > > functions could be generated automatically. > > That's the obvious solution. It would be nice if the dialect could > query the database itself to get a list of functions, since there will > be different sets of functions for different server versions, and the > functions can change when people install contrib modules or their own > functions. However, it doesn't look like the constructor for the > dialect gets given a connection or anything, so we'll probably have to > settle for static lists. It wouldn't be very hard to write a little > bit of java to parse pg_proc.h, but you'd want to filter out the types > that hibernate doesn't understand. One problem is that hibernate users > can install their own "types" - so hibernate might understand e.g. > polygons or whatever, but we won't know that at dialect initialization > time. Right Tom. The main problem is that hibernate propose is to be database independent, so, it isn't all databases that has a table with the list of all functions(and parameters/types to each function). > As someone who has contributed patches to both hibernate and pgsql I'd > be happy to help out on this, whatever the best way forward happens to > be. Top notch postgresql support in hibernate is something that I'd > very much like to see (and that goes for other JPA implementations as > well). Sure. But don't expect so much help from Oracle Toplink Team :-) > I wasn't aware that it was particularly lacking, but clearly if > a function must be registered in the dialect to be usable by HQL, > there are an awful lot of functions that won't be available. I wonder > what happens with custom operators like tsearch provides... It is my motivation to ask for some help :-) > > - You map "text" to CLOB. Not exactly sure what CLOB refers to but text > > column are not generally used for large objects. I mean, you can store > > up to a GB in them, but most such columns are not going to be large. > > Actually, it's clob being mapped to text. I don't see a huge problem > with that, really, it'll often be mapped to a String at the java end > anyway. Think about it from the perspective of someone writing a > database agnostic hibernate application - they want a field to store > character data which can potentially be quite big - big enough that > they don't want to set arbitrary limits on it. So text pretty much > fits the bill since toasting was introduced. > > It would be nice if we could register string data with no explicit > length as belonging to text as well, but it's not obvious how to do > that. Hmm. Right again Tom, Clob = character large object http://java.sun.com/javase/6/docs/api/java/sql/Clob.html http://en.wikipedia.org/wiki/Character_large_object > The BLOB mapping is the one that looks wrong to me - surely that > should be bytea as well as varbinary, unless hibernate is explicitly > invoking the large object api. Perhaps it is. > Although: > public boolean useInputStreamToInsertBlob() { > return false; > } > and in particular: > public boolean supportsExpectedLobUsagePattern() { > // seems to have spotty LOB suppport > return false; > } > I wonder what the fallback lob usage pattern is. Someone with better > knowledge of our jdbc driver might be able to point out whether the > above functions are saying the right things or not. Waiting for a response in that, too. > > - You have supportsRowValueConstructorSyntax commented out. It does, if > > you have a recent enough version, or do you mean something else? > > The way to fix both that and the differing available functions would > probably be to have a subclass of the dialect for each server version. > MySQL seems to have about 5 :) > http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/package-summary.html. I'm thinking the same thing. We could let PostgreSQLDialect to do full support to Postgre 7.x and extend it to support the new features/functions in Postgre 8.x. Btw, to do that, one thing that we must do is identify what functions are new/still avaiable in 8.x. That approach is good too, because we can get different behaviors in each version of the database. > As a side note to Diego, I'll say that it's great to see a hibernate > commiter being proactive about improving these things. Getting > attention to a bug or bugfix hasn't always been easy. Thanks. We're trying to improve this :-) Cheers, Diego Pires Plentz -- http://plentz.org/ "Provide options, don't make lame excuses."
On Sun, 2007-11-11 at 17:11 +0000, Tom Dunstan wrote:
> The way to fix both that and the differing available functions would
> probably be to have a subclass of the dialect for each server version.
> MySQL seems to have about 5 :)
I think a static dialect for each server version is the way to go.
On Sun, 2007-11-11 at 17:11 +0000, Tom Dunstan wrote:
> > - You map "text" to CLOB. Not exactly sure what CLOB refers to but text
> > column are not generally used for large objects. I mean, you can store
> > up to a GB in them, but most such columns are not going to be large.
>
> Actually, it's clob being mapped to text. I don't see a huge problem
> with that, really, it'll often be mapped to a String at the java end
> anyway.
Agreed.
---
Here's my thoughts on compatibility:
The getForUpdateString(String aliases) is incorrect because Postgres
doesn't lock columns. The default, which ignores the columns specified,
is correct for Postgres.
Most PostgreSQL Dialects should add these:
------------------------------------------
public boolean supportsPooledSequences() { return true;}
public String[] getCreateSequenceStrings(String sequenceName, int
initialValue, int incrementSize) throws MappingException { return "create sequence " + sequenceName + " INCREMENT BY
"+ toString(incrementSize) + " START WITH " + toString(initialValue);}
public boolean supportsLimitOffset() { return true;}
public boolean supportsUnique() { return true;}
public boolean supportsVariableLimit() { return true;}
PostgreSQL82Dialect and beyond should add these
-----------------------------------------------
public boolean supportsIfExistsBeforeTableName() { return true;}
/* FOR UPDATE NOWAIT */
public String getForUpdateNowaitString() {return getForUpdateString() + " NOWAIT";
}
public boolean supportsRowValueConstructorSyntax() {return true;
}
PostgreSQL83Dialect adds
-----------------------------------------------
Nothing new AFAICS?
-- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Sun, Nov 11, 2007 at 04:48:00PM -0200, Diego Pires Plentz wrote: > Right Tom. The main problem is that hibernate propose is to be > database independent, so, it isn't all databases that has a table > with the list of all functions(and parameters/types to each > function). The "least common denominator" approach to database independence is one strategy, but it pushes a lot of work into the users' hands. The way things like Perl's DBI does it is that they have a baseline set of features--the "least common denominator"--and then each individual DBMS can have its own version-specific extensions which it happens to be good at. For example, Oracle Spatial's APIs don't really resemble PostGIS, but it would be good for Hibernate to access both using methods tailored to each. Similar things apply to full-text search capabilities, which are done radically differently depending on which DBMS you're using. Yes, it violates the assumption that you can just swap DBMSs from under your application code, but I've never seen that assumption hold for applications that actually use the RDBMS anyway. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Diego, > Wow, quick responses :-) Hey, anyone wanting to work on drivers is automatically one of our favorite people. FYI, you might want to ping the pgsql-jdbc mailing list as well. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Diego Pires Plentz wrote: > I'm thinking the same thing. We could let PostgreSQLDialect to do full > support to Postgre 7.x and extend it to support the new > features/functions in Postgre 8.x. Btw, to do that, one thing that we > must do is identify what functions are new/still avaiable in 8.x. That > approach is good too, because we can get different behaviors in each > version of the database. > > Major releases of Postgres are labeled n.n. Thus, each of 7.3, 7.4, 8.0, 8.1 and 8.2 has its own set of supported functions. Moreover, Postgres is extensible, so ideally Hibernate should look at providing a way of querying a database server to get a list of supported function signatures. Not sure how you could handle user defined types automatically, though. Probably not. cheers andrew
On Sun, 2007-11-11 at 23:38 -0500, Andrew Dunstan wrote: > Moreover, Postgres is extensible, so ideally Hibernate should look at > providing a way of querying a database server to get a list of supported > function signatures. > > Not sure how you could handle user defined types automatically, though. > Probably not. The Hibernate Dialect is extensible, so it seems we can do it the other way around. Generate a Hibernate dialect for a particular database installation, then use it from Hibernate as if it was a static/manual configuration. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Sun, 2007-11-11 at 16:48 -0200, Diego Pires Plentz wrote: > > > - You have supportsRowValueConstructorSyntax commented out. It does, if > > > you have a recent enough version, or do you mean something else? > > > > The way to fix both that and the differing available functions would > > probably be to have a subclass of the dialect for each server version. > > MySQL seems to have about 5 :) > > http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/package-summary.html. > > I'm thinking the same thing. We could let PostgreSQLDialect to do full > support to Postgre 7.x and extend it to support the new > features/functions in Postgre 8.x. Btw, to do that, one thing that we > must do is identify what functions are new/still avaiable in 8.x. That > approach is good too, because we can get different behaviors in each > version of the database. I've posted files to pgsql-patches, as well as to Diego directly. There are 3 files PostgreSQL8Dialect.java which implements enough for 8.0 and 8.1 PostgreSQL82Dialect.java which extends PostgreSQL8Dialect PostgreSQL83Dialect.java which extends PostgreSQL8Dialect PostgreSQL8Dialect is not provided as a patch because the extensions have all been re-ordered to match the underlying sequence and grouping in the base Dialect file. Checking it should be much easier now. I've not checked 7.x compatibility We can then push out a new file every release. Notes: - Not sure when getCascadeConstraintsString() gets called, so left it unset - Not added any keywords. Some Dialects add a few keywords, but there doesn't seem to be any pattern to it. Any advice? - GUID support is possible, but really opens up the debate about how extensibility features should be handled. - For now, I think we should document the procedure for adding a local site Dialect which implements additional functions, with GUID as an example Comments? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Mon, 2007-11-12 at 10:55 +0000, Simon Riggs wrote: > On Sun, 2007-11-11 at 16:48 -0200, Diego Pires Plentz wrote: > > > > > - You have supportsRowValueConstructorSyntax commented out. It does, if > > > > you have a recent enough version, or do you mean something else? > > > > > > The way to fix both that and the differing available functions would > > > probably be to have a subclass of the dialect for each server version. > > > MySQL seems to have about 5 :) > > > http://www.hibernate.org/hib_docs/v3/api/org/hibernate/dialect/package-summary.html. > > > > I'm thinking the same thing. We could let PostgreSQLDialect to do full > > support to Postgre 7.x and extend it to support the new > > features/functions in Postgre 8.x. Btw, to do that, one thing that we > > must do is identify what functions are new/still avaiable in 8.x. That > > approach is good too, because we can get different behaviors in each > > version of the database. > > I've posted files to pgsql-patches, as well as to Diego directly. > > There are 3 files > PostgreSQL8Dialect.java which implements enough for 8.0 and 8.1 > PostgreSQL82Dialect.java which extends PostgreSQL8Dialect > PostgreSQL83Dialect.java which extends PostgreSQL8Dialect > > PostgreSQL8Dialect is not provided as a patch because the extensions > have all been re-ordered to match the underlying sequence and grouping > in the base Dialect file. Checking it should be much easier now. > > I've not checked 7.x compatibility > > We can then push out a new file every release. > > Notes: > - Not sure when getCascadeConstraintsString() gets called, so left it > unset > > - Not added any keywords. Some Dialects add a few keywords, but there > doesn't seem to be any pattern to it. Any advice? > > - GUID support is possible, but really opens up the debate about how > extensibility features should be handled. > > - For now, I think we should document the procedure for adding a local > site Dialect which implements additional functions, with GUID as an > example If we do this, then it looks like we can hack this file also http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/DialectFactory.java so that Hibernate can pick up the version dynamically. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs wrote: > On Sun, 2007-11-11 at 17:11 +0000, Tom Dunstan wrote: > > The way to fix both that and the differing available functions would > > probably be to have a subclass of the dialect for each server version. > > MySQL seems to have about 5 :) > > I think a static dialect for each server version is the way to go. How would this handle extensions such as PostGIS, Tsearch, XML, etc.? Certainly, the registerFunction() calls can be automated. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Nov 12, 2007 10:55 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > I've posted files to pgsql-patches, as well as to Diego directly. I dropped them into a Hibernate 3.2.5.ga source tree and ran the hibernate tests with the 8.3 dialect against pgsql HEAD and got a few errors. Diego, I assume that the hibernate tests are in a state where we expect them to all pass? I didn't bother trying the original dialect that hibernate shipped with, so I'm not sure if it passes or not. Given that these seem like an improvement, I'll assume not. > There are 3 files > PostgreSQL8Dialect.java which implements enough for 8.0 and 8.1 > PostgreSQL82Dialect.java which extends PostgreSQL8Dialect > PostgreSQL83Dialect.java which extends PostgreSQL8Dialect Given that our releases are generally a feature superset of previous ones, should we just make PostgreSQL83Dialect extend PostgreSQL82Dialect? I note that atm they are identical. Or does that offend anyone's delicate OO sensibilities? > We can then push out a new file every release. Yes, I like the general approach. > - GUID support is possible, but really opens up the debate about how > extensibility features should be handled. Yeah. Should the GUID be mapped to e.g. java.util.UUID? Or just string? etc. I had some thoughts about enums, but if someone's using the annotation stuff (either JPA or hibernate specific) then they already have a mechanism to map between a Java enum and a string, so the only thing that wouldn't work would be DDL generation, since hibernate wouldn't understand the necessaary CREATE TYPE commands. > - For now, I think we should document the procedure for adding a local > site Dialect which implements additional functions, with GUID as an > example Oh, were you just referring to making GUID functions available? Yeah that shouldn't be too hard, but again I wonder if we should look at an automatic way to generate those function declarations. Given that the dialect can't read the database when it's instantiated, perhaps the way to go would be to ship a resource file containing the expected functions and have the dialect parse that before calling the registration functions. There would then be a process that a user could run against their own database to regenerate that file, and they'd just need to drop it into their classpath for it to be picked up. All of this should work for functions, but operators are a whole different story. I strongly suspect that someone is not going to be able to use e.g. @@ in a HQL query. Are there ways to do tsearch type queries just using functions and more standard operators? Cheers Tom
[oops, sent with non-subscribed from: address first time] On Nov 12, 2007 10:55 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > I've posted files to pgsql-patches, as well as to Diego directly. I dropped them into a Hibernate 3.2.5.ga source tree and ran the hibernate tests with the 8.3 dialect against pgsql HEAD and got a few errors. Diego, I assume that the hibernate tests are in a state where we expect them to all pass? I didn't bother trying the original dialect that hibernate shipped with, so I'm not sure if it passes or not. Given that these seem like an improvement, I'll assume not. > There are 3 files > PostgreSQL8Dialect.java which implements enough for 8.0 and 8.1 > PostgreSQL82Dialect.java which extends PostgreSQL8Dialect > PostgreSQL83Dialect.java which extends PostgreSQL8Dialect Given that our releases are generally a feature superset of previous ones, should we just make PostgreSQL83Dialect extend PostgreSQL82Dialect? I note that atm they are identical. Or does that offend anyone's delicate OO sensibilities? > We can then push out a new file every release. Yes, I like the general approach. > - GUID support is possible, but really opens up the debate about how > extensibility features should be handled. Yeah. Should the GUID be mapped to e.g. java.util.UUID? Or just string? etc. I had some thoughts about enums, but if someone's using the annotation stuff (either JPA or hibernate specific) then they already have a mechanism to map between a Java enum and a string, so the only thing that wouldn't work would be DDL generation, since hibernate wouldn't understand the necessaary CREATE TYPE commands. > - For now, I think we should document the procedure for adding a local > site Dialect which implements additional functions, with GUID as an > example Oh, were you just referring to making GUID functions available? Yeah that shouldn't be too hard, but again I wonder if we should look at an automatic way to generate those function declarations. Given that the dialect can't read the database when it's instantiated, perhaps the way to go would be to ship a resource file containing the expected functions and have the dialect parse that before calling the registration functions. There would then be a process that a user could run against their own database to regenerate that file, and they'd just need to drop it into their classpath for it to be picked up. All of this should work for functions, but operators are a whole different story. I strongly suspect that someone is not going to be able to use e.g. @@ in a HQL query. Are there ways to do tsearch type queries just using functions and more standard operators? Cheers Tom
> All of this should work for functions, but operators are a whole > different story. I strongly suspect that someone is not going to be > able to use e.g. @@ in a HQL query. Are there ways to do tsearch type > queries just using functions and more standard operators? Of course, if someone's using tsearch then they've already thrown database agnosticism out the window, so they could always just knock up a native SQL query directly. But it can get quite fiddly if there are a lot of fields coming back in the result set - that's why it would be nice if hibernate could handle these cases itself. Cheers Tom
On Nov 12, 2007 1:08 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > If we do this, then it looks like we can hack this file also > http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/DialectFactory.java Oh, that's nice. Unfortunately, though. it only seems to support major version number differentiation as an int. Apparently the idea that you might have a version number like 8.3 didn't occur to whoever wrote it, although to be fair it looks like the only implementation that actually uses it is Oracle, where that assumption probably holds. Probably wouldn't be that hard to hack to our purposes though... Cheers Tom
Tom Dunstan wrote: > On Nov 12, 2007 1:08 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > >> If we do this, then it looks like we can hack this file also >> http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/DialectFactory.java >> > > Oh, that's nice. Unfortunately, though. it only seems to support major > version number differentiation as an int. Apparently the idea that you > might have a version number like 8.3 didn't occur to whoever wrote it, > although to be fair it looks like the only implementation that > actually uses it is Oracle, where that assumption probably holds. > Probably wouldn't be that hard to hack to our purposes though... > > > 800, 801 ... cheers andrew
On Nov 12, 2007 2:13 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > > Oh, that's nice. Unfortunately, though. it only seems to support major > > version number differentiation as an int. Apparently the idea that you > > might have a version number like 8.3 didn't occur to whoever wrote it, > > although to be fair it looks like the only implementation that > > actually uses it is Oracle, where that assumption probably holds. > > Probably wouldn't be that hard to hack to our purposes though... > > 800, 801 ... Nice try :), but as I read the javadoc for DialectFactory it seems to suggest that hibernate gets the major number from our JDBC driver, which dutifully reports it as 8. I doubt that we're suggesting hacking the JDBC driver to lie just to get around this wrinkle when the obvious solution is to submit a patch to hibernate that makes it pass both major and minor numbers through, and the Oracle code could happily ignore the latter. Cheers Tom
Tom Dunstan wrote: > On Nov 12, 2007 2:13 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > >>> Oh, that's nice. Unfortunately, though. it only seems to support major >>> version number differentiation as an int. Apparently the idea that you >>> might have a version number like 8.3 didn't occur to whoever wrote it, >>> although to be fair it looks like the only implementation that >>> actually uses it is Oracle, where that assumption probably holds. >>> Probably wouldn't be that hard to hack to our purposes though... >>> >> 800, 801 ... >> > > Nice try :), but as I read the javadoc for DialectFactory it seems to > suggest that hibernate gets the major number from our JDBC driver, > which dutifully reports it as 8. I doubt that we're suggesting hacking > the JDBC driver to lie just to get around this wrinkle when the > obvious solution is to submit a patch to hibernate that makes it pass > both major and minor numbers through, and the Oracle code could > happily ignore the latter. > > > OK. It's probably time to take this discussion off -hackers, I think. cheers andrew
On Mon, 2007-11-12 at 13:30 +0000, Tom Dunstan wrote: > On Nov 12, 2007 10:55 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > I've posted files to pgsql-patches, as well as to Diego directly. > > I dropped them into a Hibernate 3.2.5.ga source tree and ran the > hibernate tests with the 8.3 dialect against pgsql HEAD and got a few > errors. Diego, I assume that the hibernate tests are in a state where > we expect them to all pass? I didn't bother trying the original > dialect that hibernate shipped with, so I'm not sure if it passes or > not. Given that these seem like an improvement, I'll assume not. It's possible I caused some, though the largest single change was the reordering, which was necessary to check off everything. I was assuming your CLOB/BLOB changes would go in too. > > There are 3 files > > PostgreSQL8Dialect.java which implements enough for 8.0 and 8.1 > > PostgreSQL82Dialect.java which extends PostgreSQL8Dialect > > PostgreSQL83Dialect.java which extends PostgreSQL8Dialect > > Given that our releases are generally a feature superset of previous > ones, should we just make PostgreSQL83Dialect extend > PostgreSQL82Dialect? I note that atm they are identical. Or does that > offend anyone's delicate OO sensibilities? I'm easy either way. That's the way I started, FWIW, I just foresaw this long list of dependencies and switched back to the two level structure. > > - GUID support is possible, but really opens up the debate about how > > extensibility features should be handled. > > Yeah. Should the GUID be mapped to e.g. java.util.UUID? Or just > string? etc. I had some thoughts about enums, but if someone's using > the annotation stuff (either JPA or hibernate specific) then they > already have a mechanism to map between a Java enum and a string, so > the only thing that wouldn't work would be DDL generation, since > hibernate wouldn't understand the necessaary CREATE TYPE commands. The Dialect says "command to select GUID from underlying database". No real reason to get one from there. Hibernate doesn't support a specific GUID type since getSelectGUIDString() returns String, so I guess DB support for GUIDs is irrelevant. So OK, java.util.UUID sounds OK so far, anyone else? > > - For now, I think we should document the procedure for adding a local > > site Dialect which implements additional functions, with GUID as an > > example > > Oh, were you just referring to making GUID functions available? Yeah > that shouldn't be too hard, but again I wonder if we should look at an > automatic way to generate those function declarations. Given that the > dialect can't read the database when it's instantiated, perhaps the > way to go would be to ship a resource file containing the expected > functions and have the dialect parse that before calling the > registration functions. There would then be a process that a user > could run against their own database to regenerate that file, and > they'd just need to drop it into their classpath for it to be picked > up. I like that. > All of this should work for functions, but operators are a whole > different story. I strongly suspect that someone is not going to be > able to use e.g. @@ in a HQL query. Are there ways to do tsearch type > queries just using functions and more standard operators? Hmmmm... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On Mon, 2007-11-12 at 14:35 +0000, Tom Dunstan wrote: > Nice try :), but as I read the javadoc for DialectFactory it seems to > suggest that hibernate gets the major number from our JDBC driver, > which dutifully reports it as 8. We can extend that so it uses getMinorVersion() also. Personally, I think our JDBC driver is wrong, but thats another issue. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On 12-Nov-07, at 10:10 AM, Simon Riggs wrote: > On Mon, 2007-11-12 at 14:35 +0000, Tom Dunstan wrote: >> Nice try :), but as I read the javadoc for DialectFactory it seems to >> suggest that hibernate gets the major number from our JDBC driver, >> which dutifully reports it as 8. > > We can extend that so it uses getMinorVersion() also. > > Personally, I think our JDBC driver is wrong, but thats another issue. > What should the driver report then ? I believe the backend code considers 8 to be the major version, and 0123 to be the minor versions ? Dave > -- > Simon Riggs > 2ndQuadrant http://www.2ndQuadrant.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Dave Cramer escribió: > > On 12-Nov-07, at 10:10 AM, Simon Riggs wrote: > >> On Mon, 2007-11-12 at 14:35 +0000, Tom Dunstan wrote: >>> Nice try :), but as I read the javadoc for DialectFactory it seems to >>> suggest that hibernate gets the major number from our JDBC driver, >>> which dutifully reports it as 8. >> >> We can extend that so it uses getMinorVersion() also. >> >> Personally, I think our JDBC driver is wrong, but thats another issue. >> > What should the driver report then ? I believe the backend code considers 8 > to be the major version, and 0123 to be the minor versions ? No, 8.1 is the major version. In 8.2.5, 8.2 is the major, 5 is the minor version. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ "No hay ausente sin culpa ni presente sin disculpa" (Prov. francés)
On Nov 12, 2007 4:08 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > What should the driver report then ? I believe the backend code considers 8 > > to be the major version, and 0123 to be the minor versions ? > > No, 8.1 is the major version. In 8.2.5, 8.2 is the major, 5 is the > minor version. Which is nice in theory, except that the JDBC API doesn't give us the option of a non-int major version number. We could fudge it with 80, 81 etc, but that's pretty ugly. You can imagine some database client out there reporting that you're connected to a postgresql 82.5 database, rather than using the getDatabaseProductVersion() method which is intended for that sort of thing. For the most part, getting the combination of the major and minor numbers as currently implemented should be enough for anything using the driver, as we normally don't care about the difference between 8.2.1 and 8.2.2 in application code (heaven help mysql :)). It only came up in this case because the minor number (as reported by the JDBC driver) wasn't passed through. Cheers Tom
On 12-Nov-07, at 11:33 AM, Tom Dunstan wrote: > On Nov 12, 2007 4:08 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> > wrote: >>> What should the driver report then ? I believe the backend code >>> considers 8 >>> to be the major version, and 0123 to be the minor versions ? >> >> No, 8.1 is the major version. In 8.2.5, 8.2 is the major, 5 is the >> minor version. > > Which is nice in theory, except that the JDBC API doesn't give us the > option of a non-int major version number. We could fudge it with 80, > 81 etc, but that's pretty ugly. You can imagine some database client > out there reporting that you're connected to a postgresql 82.5 > database, rather than using the getDatabaseProductVersion() method > which is intended for that sort of thing. > > For the most part, getting the combination of the major and minor > numbers as currently implemented should be enough for anything using > the driver, as we normally don't care about the difference between > 8.2.1 and 8.2.2 in application code (heaven help mysql :)). It only > came up in this case because the minor number (as reported by the JDBC > driver) wasn't passed through. > I just looked at the code and AFAICT we can just ask the driver for both major and minor to get something along the lines of 8,0 or 8,2 for major, minor respectively. Dave