Hi all,

I'm working on an implementation of the DRDA protocol and am planning on 
modifying postgresql to support DRDA.  (DRDA is an Open Group standard 
protocol for client to database communications, promoted mostly by IBM).  

Anyway, as a first step towards this I was hoping to expand the 
documentation of the Frontend/Backend Protocol and create some detailed 
developer oriented documentation of the internals of the networking code.

Can someone point me towards any documentation that might currently be 
available (I'm aware of the Developer's Guide), and any pointers on 
getting started would be appreciated as well. ;-)

I also noticed on the TODO list someone has put SQL*Net support as a 
network protocol.  Is this a serious plan or just a pipedream?  Part of 
what I'm aiming to do is make the network protocol stuff fairly modular so 
you could support the current protocol, and DRDA, and presumably SQL*Net 
or TDS (Microsoft/Sybases protocol), etc...

Cheers,

Brian


Tue, 5 Feb 2002 08:39:43 -0500 (EST)
Brian Bruns <camber@ais.org>
DRDA, network protocol, and documentation
Brian Bruns <camber@ais.org> writes:
> I also noticed on the TODO list someone has put SQL*Net support as a 
> network protocol.  Is this a serious plan or just a pipedream?

Pipedream, I'm afraid.  No one has volunteered to actually do the work,
and I'm not sure that Oracle provides enough documentation to allow a
compatible implementation to be built, anyhow...

			regards, tom lane

Tue, 05 Feb 2002 11:03:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DRDA, network protocol, and documentation 
On Tue, 2002-02-05 at 15:39, Brian Bruns wrote:
> Hi all,
> 
> I'm working on an implementation of the DRDA protocol and am planning on 
> modifying postgresql to support DRDA.  (DRDA is an Open Group standard 
> protoc

Hopefully this will bring us PREPARE/EXECUTE support too.

BTW, does DRDA have a notion of LISTEN/NOTIFY ?

> Anyway, as a first step towards this I was hoping to expand the 
> documentation of the Frontend/Backend Protocol and create some detailed 
> developer oriented documentation of the internals of the networking code.

Protocol is quite simple and AFAIK fully documented in Developers Guide.

The networking code seems to be "Traffic Cop" directory in
src/backend/tcop/postgresql.c

You could click around in lxr I set up for my personal use on my desktop
pc at 
http://www.postsql.org/lxr/source/backend/tcop/postgres.c
and find out where things are defined/used

But you should probably set up something similar for your own use if you
want to do some serious hacking.

> Can someone point me towards any documentation that might currently be 
> available (I'm aware of the Developer's Guide), and any pointers on 
> getting started would be appreciated as well. ;-)

socket.h is included only in a few places

[hannu@taru src]$ grep -l socket.h */*/*.c
backend/libpq/auth.c
backend/libpq/hba.c
backend/libpq/pqcomm.c
backend/postmaster/pgstat.c
backend/postmaster/postmaster.c
backend/tcop/postgres.c
interfaces/libpq/fe-auth.c
interfaces/libpq/fe-connect.c
interfaces/libpq/fe-misc.c
interfaces/odbc/columninfo.c
interfaces/odbc/connection.c
interfaces/odbc/drvconn.c
interfaces/odbc/socket.c

[hannu@taru src]$ grep -l socket.h */*/*/*.c
backend/utils/adt/inet_net_ntop.c
backend/utils/adt/inet_net_pton.c
backend/utils/adt/network.c

You can probably ignore interfaces/ for start and backend/utils/adt/ is
also most likely not about FE/BE communication

That leaves backend/tcop/ for main activity, backend/postmaster/ for
session start and backend/libpq/ for main library.

> I also noticed on the TODO list someone has put SQL*Net support as a 
> network protocol.  Is this a serious plan or just a pipedream?  Part of 
> what I'm aiming to do is make the network protocol stuff fairly modular so 
> you could support the current protocol, and DRDA, and presumably SQL*Net 
> or TDS (Microsoft/Sybases protocol), etc...

XML-over-HTTP/1.1 would also be really cool, even more so if server
could apply XSLT transforms to results on the fly :)

--------------
Hannu




05 Feb 2002 19:33:36 +0200
Hannu Krosing <hannu@tm.ee>
Re: DRDA, network protocol, and documentation
On Tue, 2002-02-05 at 18:03, Tom Lane wrote:
> Brian Bruns <camber@ais.org> writes:
> > I also noticed on the TODO list someone has put SQL*Net support as a 
> > network protocol.  Is this a serious plan or just a pipedream?
> 
> Pipedream, I'm afraid.  No one has volunteered to actually do the work,
> and I'm not sure that Oracle provides enough documentation to allow a
> compatible implementation to be built, anyhow...

Also, from what I've been told, there is no such thing as SQL*Net
protocol, there is a whole lot of different protocols that quite often
refuse to talk to each other.

------------
Hannu



05 Feb 2002 19:35:30 +0200
Hannu Krosing <hannu@tm.ee>
Re: DRDA, network protocol, and documentation
Marc Lavergne <mlavergn@richlava.com> writes:
> This would involve far more than just a SQL*Net listener. Given the the 
> syntactic differences between Oracle and PostgreSQL, a whole 
> compatibility layer beyond the listener would be needed to allow for 
> interoperability. The listener piece would be relatively small potatoes 
> compared to building an Oracle<>PostgreSQL syntax translator or a 
> compatibility layer (the number of "bug" reports something like this is 
> capable of generating gives me cold sweats).

Yup.  There are some people fooling with a more-Oracle-compatible parser
(eg, Oracle-style outer join syntax) but I fear that just scratches the
surface of trying to make something that's plug-compatible enough to
make Oracle users happy.

Still, having a SQL*Net listener would be a step forward, if anyone
cared to work on it.

			regards, tom lane

Tue, 05 Feb 2002 15:15:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: DRDA, network protocol, and documentation 
On 5 Feb 2002, Hannu Krosing wrote:

> On Tue, 2002-02-05 at 15:39, Brian Bruns wrote:
> > Hi all,
> > 
> > I'm working on an implementation of the DRDA protocol and am planning on 
> > modifying postgresql to support DRDA.  (DRDA is an Open Group standard 
> > protoc
> 
> Hopefully this will bring us PREPARE/EXECUTE support too.
> 
> BTW, does DRDA have a notion of LISTEN/NOTIFY ?

Not that I'm aware of, although the spec is really big so there are some 
pieces I haven't fully groked.  There is ongoing standards activities for 
DRDA, but it's closed to TOG members apparently, but from what I can tell 
they are adding lots of object features, IPv6 stuff, unicode, etc...

> Protocol is quite simple and AFAIK fully documented in Developers Guide.

I ran it through ethereal, and got a pretty good flavor for it.

> 
> The networking code seems to be "Traffic Cop" directory in
> src/backend/tcop/postgresql.c

Actually some is in libpq, some is in tcop, some in executor.  Seems to be 
a bit of a mess if you don't mind me saying so. ;-)
 
What I would propose is a net/ directory that has interface that is called 
by all the other code, and net layer would call the appropriate protocol 
stuff.  So, all the protocol stuff will be in exactly one place.

> > I also noticed on the TODO list someone has put SQL*Net support as a 
> > network protocol.  Is this a serious plan or just a pipedream?  Part of 
> > what I'm aiming to do is make the network protocol stuff fairly modular so 
> > you could support the current protocol, and DRDA, and presumably SQL*Net 
> > or TDS (Microsoft/Sybases protocol), etc...
> 
> XML-over-HTTP/1.1 would also be really cool, even more so if server
> could apply XSLT transforms to results on the fly :)

This could be supported as just another protocol ;-)

Brian


Tue, 5 Feb 2002 17:22:16 -0500 (EST)
Brian Bruns <camber@ais.org>
Re: DRDA, network protocol, and documentation
On Tue, 5 Feb 2002, Tom Lane wrote:

> Marc Lavergne <mlavergn@richlava.com> writes:
> > This would involve far more than just a SQL*Net listener. Given the the 
> > syntactic differences between Oracle and PostgreSQL, a whole 
> > compatibility layer beyond the listener would be needed to allow for 
> > interoperability. The listener piece would be relatively small potatoes 
> > compared to building an Oracle<>PostgreSQL syntax translator or a 
> > compatibility layer (the number of "bug" reports something like this is 
> > capable of generating gives me cold sweats).
> 
> Yup.  There are some people fooling with a more-Oracle-compatible parser
> (eg, Oracle-style outer join syntax) but I fear that just scratches the
> surface of trying to make something that's plug-compatible enough to
> make Oracle users happy.
>
> Still, having a SQL*Net listener would be a step forward, if anyone
> cared to work on it.
> 
> 			regards, tom lane

I have reverse engineered TDS (the MS/Sybase equivalent) so I assume 
someone can do the same with SQL*Net/NET8 but that someone won't be me, we 
are a Sybase/DB2 shop.

Brian


Tue, 5 Feb 2002 17:25:19 -0500 (EST)
Brian Bruns <camber@ais.org>
Re: DRDA, network protocol, and documentation 
On Tue, 5 Feb 2002, Tom Lane wrote:

> Brian Bruns <camber@ais.org> writes:
> > I also noticed on the TODO list someone has put SQL*Net support as a 
> > network protocol.  Is this a serious plan or just a pipedream?
> 
> Pipedream, I'm afraid.  No one has volunteered to actually do the work,
> and I'm not sure that Oracle provides enough documentation to allow a
> compatible implementation to be built, anyhow...
> 
> 			regards, tom lane

Well, I'm offering to modularize the code for anyone who wants to take on 
the reverse engineering effort, they'll have an easy time integrating it 
into postgresql.

Brian

BTW, My master plan is to add DRDA support to not only postgresql, but 
MySQL, interbase, SAP DB, and anything else I can lay hands on.  The idea 
being a single client capable of talking to DB2/UDB, Informix (with 
gateway), MS SQL Server (with SNA gateway) and all the open source DBs. 
;-)




Tue, 5 Feb 2002 17:30:21 -0500 (EST)
Brian Bruns <camber@ais.org>
Re: DRDA, network protocol, and documentation 
On Tue, 5 Feb 2002, Brian Bruns wrote:

> I also noticed on the TODO list someone has put SQL*Net support as a 
> network protocol.  Is this a serious plan or just a pipedream?  Part of 
> what I'm aiming to do is make the network protocol stuff fairly modular so 
> you could support the current protocol, and DRDA, and presumably SQL*Net 
> or TDS (Microsoft/Sybases protocol), etc...

I intend on looking into ways to implement SQL*Net/TNS etc. It's not
pretty but would be remarkably useful. I haven't started looking at it
yet because PostgreSQL doesn't support all of the Oracle's SQL
implementation. Until this happens there's really not much point.

Gavin


Thu, 7 Feb 2002 16:02:10 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: DRDA, network protocol, and documentation

I'd have to agree on that point. Although there is probably an interesting 
subset of querys that work, but let's face it this is for canned 
commercial packages, otherwise the code would be ported including the 
network stuff.

For my purposes (DRDA) the present SQL dialect is just fine since the DRDA 
standard is really orthogonal to the SQL 9x standards.  So, hopefully if I 
don't get bogged down with other stuff, the infrastructure will be there 
to plug into when the time comes...although it'd be nice to be aware of 
some of the nuances before hand to accomadate them. 

Brian

On Thu, 7 Feb 2002, Gavin Sherry wrote:

> I intend on looking into ways to implement SQL*Net/TNS etc. It's not
> pretty but would be remarkably useful. I haven't started looking at it
> yet because PostgreSQL doesn't support all of the Oracle's SQL
> implementation. Until this happens there's really not much point.
> 
> Gavin


Thu, 7 Feb 2002 00:20:50 -0500 (EST)
Brian Bruns <camber@ais.org>
Re: DRDA, network protocol, and documentation
On Thu, 2002-02-07 at 07:20, Brian Bruns wrote:
> 
> For my purposes (DRDA) the present SQL dialect is just fine since the DRDA 
> standard is really orthogonal to the SQL 9x standards.  So, hopefully if I 
> don't get bogged down with other stuff, the infrastructure will be there 
> to plug into when the time comes...although it'd be nice to be aware of 
> some of the nuances before hand to accomadate them. 

What is the relation of DRDA to SQL/CLI (SQL Call Level Interface, part
3 of the standard) ?

------------
Hannu



07 Feb 2002 09:13:47 +0200
Hannu Krosing <hannu@tm.ee>
Re: DRDA, network protocol, and documentation
On 7 Feb 2002, Hannu Krosing wrote:

> On Thu, 2002-02-07 at 07:20, Brian Bruns wrote:
> > 
> > For my purposes (DRDA) the present SQL dialect is just fine since the DRDA 
> > standard is really orthogonal to the SQL 9x standards.  So, hopefully if I 
> > don't get bogged down with other stuff, the infrastructure will be there 
> > to plug into when the time comes...although it'd be nice to be aware of 
> > some of the nuances before hand to accomadate them. 
> 
> What is the relation of DRDA to SQL/CLI (SQL Call Level Interface, part
> 3 of the standard) ?

DRDA, SQL 9x, and SQL/CLI (ODBC) form a complimentary set of standards.

SQL 9x obviously specifies the SQL language and constructs.  SQL/CLI 
addressses application portability with an API.  DRDA on the other hand is 
a bits on the wire protocol.  So one would have a program using the ODBC 
API to send DRDA over the network to invoke SQL on the server.

DRDA clients do not necessarily have to be ODBC, indeed there are JDBC 
ones.  OpenDRDA (my little endeavour) will be an ODBC/SQL CLI driver on 
the client side, and something of a non-standard interface on the server 
(postgresql) side, if only because there is no standard for server side 
interfaces.  Actually I have the ODBC driver partially working against IBM 
DB2, but it's still, of course, in the beginning stages.

Cheers,

Brian


Thu, 7 Feb 2002 08:35:16 -0500 (EST)
Brian Bruns <camber@ais.org>
Re: DRDA, network protocol, and documentation
On Thu, 2002-02-07 at 15:35, Brian Bruns wrote:
> On 7 Feb 2002, Hannu Krosing wrote:
> 
> 
> > What is the relation of DRDA to SQL/CLI (SQL Call Level Interface, part
> > 3 of the standard) ?
> 
> DRDA, SQL 9x, and SQL/CLI (ODBC) form a complimentary set of standards.
> 
> SQL 9x obviously specifies the SQL language and constructs.  SQL/CLI 
> addressses application portability with an API.  DRDA on the other hand is 
> a bits on the wire protocol.  So one would have a program using the ODBC 
> API to send DRDA over the network to invoke SQL on the server.

But I guess that you can't fake PREPARE/EXECUTE on client side anymore
if you want to be DRDA compatible?

Or is DRDA so low-level that it does not care what info it carries ?

Does DRDA have standard representation of datatypes on wire ? 

If so, how will postgres extendable datatypes fit in there ?

I know that postgres's system tables have two sets of type i/o functions
 typinput     | regproc  | 
 typoutput    | regproc  | 
 typreceive   | regproc  | 
 typsend      | regproc  | 

which are currently initialised to the same real functions

hannu=# select count(*) from pg_type where typoutput <> typsend or
typinput <> typreceive;
 count 
-------
     0
(1 row)

I suspect thet the typreceive and typsend were planned for some common
network representation, but such usage has probaly gone untested for a
very long time.

----------------
Hannu


07 Feb 2002 20:57:45 +0200
Hannu Krosing <hannu@tm.ee>
Re: DRDA, network protocol, and documentation
On Thu, Feb 07, 2002 at 04:02:10PM +1100, Gavin Sherry wrote:
> On Tue, 5 Feb 2002, Brian Bruns wrote:
> 
> > I also noticed on the TODO list someone has put SQL*Net support as a 
> > network protocol.  Is this a serious plan or just a pipedream?  Part of 
> > what I'm aiming to do is make the network protocol stuff fairly modular so 
> > you could support the current protocol, and DRDA, and presumably SQL*Net 
> > or TDS (Microsoft/Sybases protocol), etc...
> 
> I intend on looking into ways to implement SQL*Net/TNS etc. It's not
> pretty but would be remarkably useful. I haven't started looking at it
> yet because PostgreSQL doesn't support all of the Oracle's SQL
> implementation. Until this happens there's really not much point.

I'd really like to see something that does XML queries, either over
HTTP (POST/PUT) or BEEP (RFC 3080, 3081).  I can start work on
something generic (I've been meaning to start hacking pg anyway...).
A generic input format (application/vnd.postgresql-query) and a
generic XML schema that handles any query.  Ideally, you'd have the
ability to designate an XML schema (SELECT [AS XML [SCHEMA 'blah']] ...),
and an xml schemas table that has a description of the data parts,
so the output XML is exactly right.

I'm still wrapping my brain around this concept, if anybody else 
is interested in this let me know.

-- 
David Terrell            | "When we said that you needed to cut the
dbt@meat.net             | wires for ultimate security, we didn't
Nebcorp Prime Minister   | mean that you should go wireless instead."
http://wwn.nebcorp.com/  |   - Casper Dik

Thu, 7 Feb 2002 11:15:26 -0800
David Terrell <dbt@meat.net>
Re: DRDA, network protocol, and documentation
On 7 Feb 2002, Hannu Krosing wrote:

> But I guess that you can't fake PREPARE/EXECUTE on client side anymore
> if you want to be DRDA compatible?

DRDA has a facility for preparing and executing, but also for direct 
execution.  So, a server implementation would have to support all of the 
AR Level 1 capabilities to be compatible.  The client is a bit free-er to 
choose how to send it's SQL. That is, the client has the option to fake a 
prepare/execute but the server must service either method.

> Does DRDA have standard representation of datatypes on wire ? 

DRDA has a quite extensive list of datatype representations.  The ordering 
of bytes is server dictated (as opposed to TDS where it is client 
dictated, so server does the byte swapping if necessary). 

> If so, how will postgres extendable datatypes fit in there ?
> 
> I know that postgres's system tables have two sets of type i/o functions
>  typinput     | regproc  | 
>  typoutput    | regproc  | 
>  typreceive   | regproc  | 
>  typsend      | regproc  | 
> 
> which are currently initialised to the same real functions
> 
> hannu=# select count(*) from pg_type where typoutput <> typsend or
> typinput <> typreceive;
>  count 
> -------
>      0
> (1 row)

The server has the leeway to determine the DRDA representation for it's 
dataytpes, and it is the clients responsibility to deal with it.

> I suspect thet the typreceive and typsend were planned for some common
> network representation, but such usage has probaly gone untested for a
> very long time.

good question.  

Brian


Thu, 7 Feb 2002 16:14:59 -0500 (EST)
Brian Bruns <camber@ais.org>
Re: DRDA, network protocol, and documentation
[2002-02-07 11:15] David Terrell said:

| I'd really like to see something that does XML queries, either over
  [snip]
  
| I'm still wrapping my brain around this concept, if anybody else 
| is interested in this let me know.

search for Jim Melton's SQLX initiative.  The site (www.sqlx.org) 
has not been available for quite a while, but I do have a copy of
the draft document if you'd like it.

  b

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Thu, 7 Feb 2002 16:42:41 -0500
Brent Verner <brent@rcfile.org>
Re: DRDA, network protocol, and documentation