PLJava and Database Meta Data

PLJava has its own JDBC driver that is implemented directly on top of
JDBC. At present, it lacks support for DatabaseMetaData. One option for
us might be to borrow a lot of code from your driver, and perhaps apply
some modifications to it. This raises two questions:

1. Do you have any objection to us using parts of your code?
2. If minor refactoring would make it possible for us to share the code
unmodified, would you see that as something worth pursuing?

Regards,
Thomas Hallgren

On Thu, 10 Feb 2005, Thomas Hallgren wrote:

> PLJava has its own JDBC driver that is implemented directly on top of
> JDBC. At present, it lacks support for DatabaseMetaData. One option for
> us might be to borrow a lot of code from your driver, and perhaps apply
> some modifications to it. This raises two questions:
>
> 1. Do you have any objection to us using parts of your code?

Go right ahead. It's BSD licensed for just such a reason.

> 2. If minor refactoring would make it possible for us to share the code
> unmodified, would you see that as something worth pursuing?
>

Certainly something to look into, but I fear there may be too many
differences, especially in how things like encoding and ResultSet creation
are handled. Additionally the driver returns only the JDBC 2 spec fields
even when built and called as a JDBC 3 driver. I've been thinking a
little bit about how to return different fields for different JDBC
versions without duplicating a whole lot of work, so some reorganization
seems to be in order. It would be great to tackle both of these projects
at the same time.

Kris Jurka

Hi, Thomas,

Thomas Hallgren schrieb:
> PLJava has its own JDBC driver that is implemented directly on top of
> JDBC.

Did you do anything concerning custom datatypes (e. G. PostGIS) yet?

The pgjdbc PGobject interface will need some overhaul (or a complete
rework) to make use of the binary representation with V3 protocol
support, maybe that you (PLJava), the pgjdbc pepole, we (PostGIS) and
others could join to create a common interface, enabling users to use
the same extension jar for both client and server side.

Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com

Markus,

>Did you do anything concerning custom datatypes (e. G. PostGIS) yet?
>
>The pgjdbc PGobject interface will need some overhaul (or a complete
>rework) to make use of the binary representation with V3 protocol
>support, maybe that you (PLJava), the pgjdbc pepole, we (PostGIS) and
>others could join to create a common interface, enabling users to use
>the same extension jar for both client and server side.
>
>
Nothing has been done in this area for PLJava yet. I'm definitely in
favor of your suggestion. If anything can be done to converge efforts
and API's, it should be done.

Regards,
Thomas Hallgren

Hi, Thomas,

Thomas Hallgren schrieb:

>> Did you do anything concerning custom datatypes (e. G. PostGIS) yet?
>>[...]
>
> Nothing has been done in this area for PLJava yet. I'm definitely in
> favor of your suggestion. If anything can be done to converge efforts
> and API's, it should be done.

Okay. Maybe we should also invite other custom datatype authors.

Just out of curiosity (I did not have enough time to take a close look
at PLJava yet - maybe I should do that first...): How do you currently
model types like Interval, Money, ByteArray or the native PostgreSQL
geometry types? (I ask this because in pgjdbc they are currently
implemented using the same PGobject approach as PostGIS extension types.)

And what is your approach to endianness conversion?

Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com

Markus Schaber wrote:

>Hi, Thomas,
>
>Thomas Hallgren schrieb:
>
>
>
>>>Did you do anything concerning custom datatypes (e. G. PostGIS) yet?
>>>[...]
>>>
>>>
>>Nothing has been done in this area for PLJava yet. I'm definitely in
>>favor of your suggestion. If anything can be done to converge efforts
>>and API's, it should be done.
>>
>>
>
>Okay. Maybe we should also invite other custom datatype authors.
>
>Just out of curiosity (I did not have enough time to take a close look
>at PLJava yet - maybe I should do that first...): How do you currently
>model types like Interval, Money, ByteArray or the native PostgreSQL
>geometry types? (I ask this because in pgjdbc they are currently
>implemented using the same PGobject approach as PostGIS extension types.)
>
>
bytea is mapped to byte[]. The other types are not yet mapped. Where can
I find info about the PostGIS approach?

>And what is your approach to endianness conversion?
>
>
None yet. What types have endian issues and in what way? The PLJava
mapping is using SPI functions directly so we never serialize anything.
PLJava create wrappers for Datum's in the native backend environment.
I'm not sure endian issues ever bites us.

- thomas

Hi, Thomas,

Thomas Hallgren schrieb:

> bytea is mapped to byte[]. The other types are not yet mapped. Where can
> I find info about the PostGIS approach?

The best is to look at the class org.postgresql.util.PGobject in the
pgjdbc source, and the classes derived from it (spitted over multiple
packages). I do not know whether there actually is any additional
documentation around.

PostGIS simply uses PGConnection.addDatataType() to add additional
mappings for their datatypes, it is all documented in the PostGIS
source, jdbc2 subdirectory. (Best to use a CVS checkout from
www.PostGIS.org).

The PGobject approach currently suffers from at least three
deficiencies, which were discussed here and at various other places:

- Some problems with null values (AFAIR, not really shure about).

- Can only use canonical text representation, no binary rep yet.

- There is a fixed 1:1 mapping between PostgreSQL types and java
classes. This causes problems as all PostGIS geometry types are modeled
as PostgreSQL "geometry" type, but different classes (polygon, point,
linestring) in java. Thus, the PGobject subclass PGgeometry currently is
merely a wrapper around the real geometry classes.

In my eyes, the best solution would be a factory instance based
approach. The factory knows how to serialize and deserialize several
java classes that all map to the same PostgreSQL java type.

>> And what is your approach to endianness conversion?
>>
> None yet. What types have endian issues and in what way? The PLJava
> mapping is using SPI functions directly so we never serialize anything.
> PLJava create wrappers for Datum's in the native backend environment.
> I'm not sure endian issues ever bites us.

Well, I do not know anything about SPI yet, but it just got on my todo list.

For complex datatypes, if you use the internal representation for your
java mapping, you have to parse bytes, 32-bit integers, doubles and such
from the in-memory representation. This depends on the machine
endianness. Alternatively, you can use the canonical representations
(using the types INPUT/OUTPUT/SEND/RECEIVE functions), but this incurs
additional overhead.

Is there any documentation about such PLJava internals?

Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com

Markus Schaber wrote:

>For complex datatypes, if you use the internal representation for your
>java mapping, you have to parse bytes, 32-bit integers, doubles and such
>from the in-memory representation.
>
We never parse any bytes. The internal representation of a double in the
backend is, a double. The SPI layer communicates values in terms of
datums that holds C-language char, short, int, long, float, double, etc.
values and JNI treats them as their native Java correspondance in the
Java layer. The same is true for the actuall call handler used for SQL
function calls into Java.

>Alternatively, you can use the canonical representations
>(using the types INPUT/OUTPUT/SEND/RECEIVE functions), but this incurs
>additional overhead.
>
>
Do you have any concrete measurments where this overhead is significant?
In my experience, using input/output/send/receive incurs a very low
overhead. I doubt that it's even measurable.

>Is there any documentation about such PLJava internals?
>
>
The source code :-)
Seriosly, if you want to know PLJava internals, it's not that bad to
look at. It's fairly well commented.

- thomas

Hi, Thomas,

Thomas Hallgren schrieb:

>> For complex datatypes, if you use the internal representation for your
>> java mapping, you have to parse bytes, 32-bit integers, doubles and such
>> from the in-memory representation.
>>
> We never parse any bytes. The internal representation of a double in the
> backend is, a double. The SPI layer communicates values in terms of
> datums that holds C-language char, short, int, long, float, double, etc.
> values and JNI treats them as their native Java correspondance in the
> Java layer. The same is true for the actuall call handler used for SQL
> function calls into Java.

Okay, so this is fine for primitive types. How does SPI deal with
complex types?

>> Alternatively, you can use the canonical representations
>> (using the types INPUT/OUTPUT/SEND/RECEIVE functions), but this incurs
>> additional overhead.
>>
> Do you have any concrete measurments where this overhead is significant?
> In my experience, using input/output/send/receive incurs a very low
> overhead. I doubt that it's even measurable.

Yes, I have. Especially for large geometries. In our database, we have
geometry objects up to 20MB per piece. Also, in PostGIS, send/receive
binary representation differs slightly from internal representation (and
input/output text is even more overhead).

>> Is there any documentation about such PLJava internals?
>>
> The source code :-)
> Seriosly, if you want to know PLJava internals, it's not that bad to
> look at. It's fairly well commented.

Okay, I'll do so.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com

Markus Schaber wrote:

>Okay, so this is fine for primitive types. How does SPI deal with
>complex types?
>
>
A complex type is represented by a C-structure called HeapTuple. A
HeapTuple is associated with a TupleDesc that acts as its description.
The HeapTuple is passed by reference (a C-language pointer) and PLJava
will obtains its individual values as Datums using a function called
SPI_getbinval. An individual value may of course be a reference to
another complex type.

Complex types are created using the function heap_form_tuple.

>Yes, I have. Especially for large geometries. In our database, we have
>geometry objects up to 20MB per piece. Also, in PostGIS, send/receive
>binary representation differs slightly from internal representation (and
>input/output text is even more overhead).
>
>
Ok. We are probably talking about different things. I'm referring to the
internal C-functions that are executed directly in the backend.

- thomas

Hi, Thomas,

Thomas Hallgren schrieb:

>> Okay, so this is fine for primitive types. How does SPI deal with
>> complex types?
>>
> A complex type is represented by a C-structure called HeapTuple. A
> HeapTuple is associated with a TupleDesc that acts as its description.
> The HeapTuple is passed by reference (a C-language pointer) and PLJava
> will obtains its individual values as Datums using a function called
> SPI_getbinval. An individual value may of course be a reference to
> another complex type.
>
> Complex types are created using the function heap_form_tuple.

Okay, sounds good. So it seems that PostGIS/PLJava would need some
understanding of PostGIS internal layout in addition to the canonical
representations it uses on the client side.

Two additional features that are needed to parse the PostGIS internal
rep are optional fields (presence is flagged by bits in other fields)
and arrays (both as complex types). And there must be no issues with
strange alignments.

(All of this how I remember the discussions on the PostGIS mailing list,
I did not do any server-side PostGIS coding myself up to now.)

>> Yes, I have. Especially for large geometries. In our database, we have
>> geometry objects up to 20MB per piece. Also, in PostGIS, send/receive
>> binary representation differs slightly from internal representation (and
>> input/output text is even more overhead).
>
> Ok. We are probably talking about different things. I'm referring to the
> internal C-functions that are executed directly in the backend.

I talked about those backend functions that are declared in CREATE TYPE
statement, which create the canonical text or binary representation
which is sent over to clients (or received from them). This also are the
representations which jdbc works on.

For primitive types, the binary rep equals the internal rep (minus
endianness?), and such conversion is cheap. But this is different for
other types, especially those which are huge in size.

BTW, while loading the PLJava sources from CVS into Eclipse, I found a
small problem in Backend.java, line 127 (the non-1.5 version):

if(perm.getActions().contains("write") &&
perm.getName().startsWith("java."))

In Java 1.4 (I tried sun and ibm ones), String has no contains() method.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com

Markus,

>BTW, while loading the PLJava sources from CVS into Eclipse, I found a
>small problem in Backend.java, line 127 (the non-1.5 version):
>
> if(perm.getActions().contains("write") &&
> perm.getName().startsWith("java."))
>
>In Java 1.4 (I tried sun and ibm ones), String has no contains() method.
>
>
A fix was just commited to the CVS. Sorry about that.

Regards,
Thomas Hallgren

Hi, Thomas,

Thomas Hallgren schrieb:

>> BTW, while loading the PLJava sources from CVS into Eclipse, I found a
>> small problem in Backend.java, line 127 (the non-1.5 version):
>>
>> if(perm.getActions().contains("write") &&
>> perm.getName().startsWith("java."))
>>
>> In Java 1.4 (I tried sun and ibm ones), String has no contains() method.
>>
> A fix was just commited to the CVS. Sorry about that.

Thanks. Seems to work. :-)

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios(at)logi-track(dot)com | www.logi-track.com

Thomas,

FYI, PL-J typemapper framework.
http://cvs.plj.codehaus.org/*checkout*/pl-j/src/interfaces/org/pgj/typemapping/TypeMapper.java?rev=1.4

Regards,
Laszlo

On Fri, 11 Feb 2005, Thomas Hallgren wrote:

> Markus Schaber wrote:
>
> >Hi, Thomas,
> >
> >Thomas Hallgren schrieb:
> >
> >
> >
> >>>Did you do anything concerning custom datatypes (e. G. PostGIS) yet?
> >>>[...]
> >>>
> >>>
> >>Nothing has been done in this area for PLJava yet. I'm definitely in
> >>favor of your suggestion. If anything can be done to converge efforts
> >>and API's, it should be done.
> >>
> >>
> >
> >Okay. Maybe we should also invite other custom datatype authors.
> >
> >Just out of curiosity (I did not have enough time to take a close look
> >at PLJava yet - maybe I should do that first...): How do you currently
> >model types like Interval, Money, ByteArray or the native PostgreSQL
> >geometry types? (I ask this because in pgjdbc they are currently
> >implemented using the same PGobject approach as PostGIS extension types.)
> >
> >
> bytea is mapped to byte[]. The other types are not yet mapped. Where can
> I find info about the PostGIS approach?
>
> >And what is your approach to endianness conversion?
> >
> >
> None yet. What types have endian issues and in what way? The PLJava
> mapping is using SPI functions directly so we never serialize anything.
> PLJava create wrappers for Datum's in the native backend environment.
> I'm not sure endian issues ever bites us.
>
> - thomas
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Laszlo Hornyak wrote:

>Thomas,
>
>FYI, PL-J typemapper framework.
>http://cvs.plj.codehaus.org/*checkout*/pl-j/src/interfaces/org/pgj/typemapping/TypeMapper.java?rev=1.4
>
>Regards,
>Laszlo
>
>
Yepp. I have the pl-j CVS in my workspace :-)

- thomas