Re: [hibernate-team] PostgreSQLDialect

Hi Guys,

I'm one of the hibernate(http://hibernate.org) team commiters and I'm here
to ask you for a little help :-)
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.

What we must do(ok, what we *can* do :-) ) is change the file bellow, adding
the functions that are useful to this file:

http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/PostgreSQLDialect.java

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
methods methods that says to hibernate some database behaviors. This class
extends our base Dialect, that is just a class with some basic info.

http://anonsvn.jboss.org/repos/hibernate/core/trunk/core/src/main/java/org/hibernate/dialect/Dialect.java

So, if someone wanna help, please let me know :-)

Cya!

--
http://plentz.org/
"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(at)svana(dot)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(at)tomd(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)2ndquadrant(dot)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(at)dunslane(dot)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(at)dunslane(dot)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(at)2ndquadrant(dot)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(at)alvh(dot)no-ip(dot)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(at)alvh(dot)no-ip(dot)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