Thread: DRDA, network protocol, and documentation
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
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 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
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, 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. ;-)
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 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 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, 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: > 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
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 functionstypinput | 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