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

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

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

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

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

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

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.
;-)

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

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:

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

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

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

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

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

[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