Re: [HACKERS] XML ouput for psql
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I think for processing-oriented output, the system described in the
SQL/XML standard draft is the way to go. Considering the people who wrote
it, it's probably pulled from, or bound to appear in, a major commercial
database.
Do you have a link to the exact section? I've found conflicting versions
of what constitutes the standard for xml output of SQL data.
I also think that psql is not the place to implement something like this.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
Then any interface and application that likes it, not just psql-based
ones, can use it.
I think that is a good long-term solution, but I still think we need
to address the TODO item in the short run, and allow for a simple
reformatting of the query results from psql. If not, we should remove
that TODO item form psql and add a different one to the backend section.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200303061020
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+Z2jHvJuQZxSWSsgRAj7IAJ4hLEos9OlE67O02gVrrqxwT9n3AQCeJxto
N2LFyvXPfGY2whPUs5k+PQA=
=PYfs
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
Tom Lane writes: This is also a good time to stop and ask whether the frontend/backend protocol needs to change to support this. Not having read the spec, I have no idea what the low-level transport needs are for XML output, but I suspect our present protocol is not it ... The spec defines mappings between tables, schemas, and catalogs on the one side and each time a pair of XML documents on the other side, one of which is an XML schema document (sort of a document type declaration) and the other is an XML data document that follows the constraints of the schema document and contains the actual data. A table could of course more or less be interpreted to mean a query result. That means, this functionality provides both query result retrieval via XML and a pg_dump type mechanism with XML output. So I imagine, if this is done fully with changes in the protocol layer, then certain commands like get table schema in XML would have to exist in the protocol, which doesn't seem right. Also, the XML output isn't a sibling of the current text/binary tuples, since an XML result is always a whole document, not tuple data. What we could perhaps consider is a family of functions like I illustrated, but then provide a fast-path-driven layer on the client side, like for large objects. Initially, the development of these mapping functions could take place totally in user-space. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
Peter Eisentraut [EMAIL PROTECTED] writes: So I imagine, if this is done fully with changes in the protocol layer, then certain commands like get table schema in XML would have to exist in the protocol, which doesn't seem right. Also, the XML output isn't a sibling of the current text/binary tuples, since an XML result is always a whole document, not tuple data. I would envision a distinction comparable to the existing one between T and D messages (RowDescription and AsciiRow, using the documentation's names): you send the table schema first, then the data. Also note that there is no command to get the T message; it comes for free whenever a SELECT result is sent to the frontend. What we could perhaps consider is a family of functions like I illustrated, but then provide a fast-path-driven layer on the client side, like for large objects. Initially, the development of these mapping functions could take place totally in user-space. I don't object to that as a quick-and-dirty context for prototyping work, but I'd sure hate to see it as the production version. The fastpath protocol is a mess, and until/unless we get it cleaned up, we ought not increase dependency on it. A larger point is that this is still a protocol revision; pretending it ain't is just willful obscurantism. You can tell it's a protocol revision because you will need to rewrite client-side libraries to take advantage of it. If we try to look the other way and pretend it isn't one, then we'll just be incurring pain --- the most obvious pain being that it will be hard for those client libraries to tell whether the protocol extension is supported or not. The way I'd prefer to see this handled is by providing alternatives to the printtup.c DestReceiver routines. The backend could be switched to any desired output representation just by invoking different sets of receiver routines. What we seem to need first is a context for doing that, in particular a way to understand how different output formats can be fit into the FE/BE protocol. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] XML ouput for psql
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2003-03-04 14:21]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. I'm interested in creating XML documents that have heirarcy. I can produce the above with Perl. I wrote a utility that takes an xml document, and xml configuration file, and writes the document to a PostgerSQL data base using the configuration file to figure out what goes where. The configuration file makes some use of XPath to pluck the correct values out of the xml doucment. I suppose the same code could generate a document, but it is so easy to do using Perl and cgi, I've not bothered. This util has been very helpful to me in developing a document mangement application. Rather than writing insert/update logic every time the db or xml schema changes, I just tweak the config file and it will generated the inserts, updates, and deletes by comparing the XML document with the tables to which the XML elements are mapped. I've been able to handle tree structures tolerably well. I am currently rewriting the code in C++ from Perl. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] XML ouput for psql
XSLT could be used to convert virtually any xml table format directly into an insert statement. For me, this is better than using a programming language plus a parser. XSLT is quite powerful and fast and is build on top of xpath, and is a closer fit to the declarative programming model of sql. Validation could be done at the xslt stage or with schemas, which I prefer. Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
I would like to see PostgreSQL eventually support XQuery: http://www.w3.org/TR/xquery/ http://www.w3.org/TR/query-datamodel/ I see potentially an alternative front end called xsql, providing substantially the same functionality as psql, only using XQuery syntax and optionally returning recordsets as XML. Anybody want to put together a team to explore this seriously? There are probably several non-trivial semantic issues on the back end, but I only dimly grasp them at this point. - Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure %% Sent: Wednesday, March 05, 2003 8:16 AM %% To: Alan Gutierrez %% Cc: [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% XSLT could be used to convert virtually any xml table format directly %% into an insert statement. For me, this is better than using a %% programming language plus a parser. XSLT is quite powerful and fast and %% is build on top of xpath, and is a closer fit to the declarative %% programming model of sql. Validation could be done at the xslt stage or %% with schemas, which I prefer. %% %% %% Acually, the difficult part has been getting the information back %% into the database. Getting it out is a very simple query. I imagine %% that every language/environment has an SQL-XML library somewhere, %% but I wasn't able to find something that would go from XML to SQL. %% %% %% ---(end of broadcast)--- %% TIP 3: if posting/reading through Usenet, please send an appropriate %% subscribe-nomail command to [EMAIL PROTECTED] so that your %% message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] XML ouput for psql
This is just about a total conversion of the backend to an xml document server. The marriage of xml and sql is awkward and not easily retrofitted to existing databases. Its pretty much proven that hierarchal storage techniques (xml included) are more difficult to manage and use than traditional sql databases. However, xml does have some very powerful supplemental technologies for document generation on the client end, especially xslt. Unless there is a compelling reason to use those tools, you are 99% likely better off not using xml at all. XML has also found a niche in the edi world, but in this case you can get away with using the blob technique below. Before thinking about xquery you have to think about the role tuples and relations play in storing xml data. The simplest route is to store your xml data in a blob object and use little tricks like xslt to extract elements out of the document into separate fields for index purposes and use vanilla sql queries to get the data. This is a very loose coupling of sql and xml and leads to very brittle designs because at the very least you have to store two separate definitions of your data as well as your marshalling xslt scripts. I wrote a little blob based xml server which you can see here if you are interested: http://www.radiosoft.com/freetools.php?Location=Directional+Patterns The real magic was in the xslt which you can't see, though. A tighter coupling would involve decomposing your xml structure into discrete objects and building xml power into the backend. I think it is yet to be proven if this is even reasonably possible. The big problem with xml is that there is too many ways to do things, for example the choice of dtd or schemas. I think, the problem with this approach is recomposing your document involves complex or inefficient queries. If you think this is good idea, good luck, many companies have tried and I've personally not seen one that seems to work very well. The next major version of MS SQL server is rumored to be something of an xml document server, and that's been several years in development. Merlin -Original Message- From: Bob Calco [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 05, 2003 9:10 AM To: Merlin Moncure; Alan Gutierrez Cc: [EMAIL PROTECTED] Subject: RE: [HACKERS] XML ouput for psql I would like to see PostgreSQL eventually support XQuery: http://www.w3.org/TR/xquery/ http://www.w3.org/TR/query-datamodel/ I see potentially an alternative front end called xsql, providing substantially the same functionality as psql, only using XQuery syntax and optionally returning recordsets as XML. Anybody want to put together a team to explore this seriously? There are probably several non-trivial semantic issues on the back end, but I only dimly grasp them at this point. - Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure %% Sent: Wednesday, March 05, 2003 8:16 AM %% To: Alan Gutierrez %% Cc: [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% XSLT could be used to convert virtually any xml table format directly %% into an insert statement. For me, this is better than using a %% programming language plus a parser. XSLT is quite powerful and fast and %% is build on top of xpath, and is a closer fit to the declarative %% programming model of sql. Validation could be done at the xslt stage or %% with schemas, which I prefer. %% %% %% Acually, the difficult part has been getting the information back %% into the database. Getting it out is a very simple query. I imagine %% that every language/environment has an SQL-XML library somewhere, %% but I wasn't able to find something that would go from XML to SQL. %% %% %% ---(end of broadcast)-- - %% TIP 3: if posting/reading through Usenet, please send an appropriate %% subscribe-nomail command to [EMAIL PROTECTED] so that your %% message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML ouput for psql
I've done a lot with XML lately, so I'll throw in my $0.02 worth. One thing I have noticed about the schemes that are being advanced is that they seem to be inherently unspecifiable, formally, because column names are being used as tags. An alternative might look something like this: ?xml version=1.0? RESULTSET statement=select * from xmltest COLUMNS COLUMN name=scoops type=int / COLUMN name=flavor type=varchar(40) / /COLUMNS ROW FIELD name=scoops isNull=false3/FIELD FIELD name=flavor isNull=falsechocolate/FIELD /ROW ROW FIELD name=scoops isNull=false2/FIELD FIELD name=flavor isNull=falsevanilla/FIELD /ROW /RESULTSET Numbering the rows should be redundant (XPath will give it to you using position(), for example). OTOH, reporting out a null value as opposed to an empty one is probably a good idea. The formal DTD would be something like this (courtesy of the wonderful tools at http://www.hitsw.com/xml_utilites/: !ELEMENT RESULTSET ( COLUMNS, ROW* ) !ATTLIST RESULTSET statement CDATA #REQUIRED !ELEMENT COLUMNS ( COLUMN+ ) !ELEMENT COLUMN EMPTY !ATTLIST COLUMN name NMTOKEN #REQUIRED !ATTLIST COLUMN type CDATA #REQUIRED !ELEMENT ROW ( FIELD+ ) !ELEMENT FIELD ( #PCDATA ) !ATTLIST FIELD isNull ( false| true ) false !ATTLIST FIELD name NMTOKEN #REQUIRED or the equivalent in a schema:?xml version=1.0 encoding=UTF-8 ? xs:schema xmlns:xs=http://www.w3.org/2001/XMLSchema; xs:element name=COLUMN xs:complexType xs:attribute name=type type=xs:string use=required / xs:attribute name=name type=xs:NMTOKEN use=required / /xs:complexType /xs:element xs:element name=COLUMNS xs:complexType xs:sequence xs:element ref=COLUMN minOccurs=1 maxOccurs=unbounded / /xs:sequence /xs:complexType /xs:element xs:element name=FIELD xs:complexType mixed=true xs:attribute name=isNull use=optional default=false xs:simpleType xs:restriction base=xs:NMTOKEN xs:enumeration value=false / xs:enumeration value=true / /xs:restriction /xs:simpleType /xs:attribute xs:attribute name=name type=xs:NMTOKEN use=required / /xs:complexType /xs:element xs:element name=RESULTSET xs:complexType xs:sequence xs:element ref=COLUMNS minOccurs=1 maxOccurs=1 / xs:element ref=ROW minOccurs=0 maxOccurs=unbounded / /xs:sequence xs:attribute name=statement type=xs:string use=required / /xs:complexType /xs:element xs:element name=ROW xs:complexType xs:sequence xs:element ref=FIELD minOccurs=1 maxOccurs=unbounded / /xs:sequence /xs:complexType /xs:element /xs:schema ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] XML ouput for psql
[EMAIL PROTECTED] writes:
I think all psql needs is a simple output, similar to the ones used by
Oracle, Sybase, and MySQL; the calling application should then process
it in some way as needed (obviously this is not for interactive use).
Where can one find a standard table model?
I think for processing-oriented output, the system described in the
SQL/XML standard draft is the way to go. Considering the people who wrote
it, it's probably pulled from, or bound to appear in, a major commercial
database.
I also think that psql is not the place to implement something like this.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
Then any interface and application that likes it, not just psql-based
ones, can use it.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
Andrew Dunstan writes: One thing I have noticed about the schemes that are being advanced is that they seem to be inherently unspecifiable, formally, because column names are being used as tags. The SQL/XML draft addresses this by specifying that a mapping from SQL things to XML things spits out both the specification (XML Schema, IIRC) and the data in one operation. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML ouput for psql
Bob Calco writes: I would like to see PostgreSQL eventually support XQuery: The specification is here: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-14-XML-2002-03.txt Go for it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] XML ouput for psql
Thanks, Merlin, for your thoughtful comments. There is no question that the marriage of XML and SQL is wrought with semantic difficulties. I'm not sure the win is compelling enough to justify re-writing the PostgreSQL back end; on the other hand, it is a juicy technical challenge! I like PostgreSQL just as it is, and truth be told, I'd like to see some additional features in PL/pgSQL that are completely unrelated to the whole XML issue. But I see some interesting possibilities for PostgreSQL to make inroads in enterprise development if it were the first open source database to do something truly useful with XQuery concepts. So I had this idea. If I can find a few good co-conspirators, I'm prepared to get involved seriously with developing this add-on capability, whatever it takes. Mostly, I suspect, time... Sincerely, Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure %% Sent: Wednesday, March 05, 2003 10:19 AM %% To: [EMAIL PROTECTED] %% Cc: [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% This is just about a total conversion of the backend to an xml document %% server. The marriage of xml and sql is awkward and not easily %% retrofitted to existing databases. %% %% Its pretty much proven that hierarchal storage techniques (xml included) %% are more difficult to manage and use than traditional sql databases. %% However, xml does have some very powerful supplemental technologies for %% document generation on the client end, especially xslt. Unless there %% is a compelling reason to use those tools, you are 99% likely better off %% not using xml at all. XML has also found a niche in the edi world, but %% in this case you can get away with using the blob technique below. %% %% Before thinking about xquery you have to think about the role tuples and %% relations play in storing xml data. The simplest route is to store your %% xml data in a blob object and use little tricks like xslt to extract %% elements out of the document into separate fields for index purposes and %% use vanilla sql queries to get the data. This is a very loose coupling %% of sql and xml and leads to very brittle designs because at the very %% least you have to store two separate definitions of your data as well as %% your marshalling xslt scripts. %% %% I wrote a little blob based xml server which you can see here if you are %% interested: %% http://www.radiosoft.com/freetools.php?Location=Directional+Patterns %% The real magic was in the xslt which you can't see, though. %% %% %% A tighter coupling would involve decomposing your xml structure into %% discrete objects and building xml power into the backend. I think it is %% yet to be proven if this is even reasonably possible. The big problem %% with xml is that there is too many ways to do things, for example the %% choice of dtd or schemas. I think, the problem with this approach is %% recomposing your document involves complex or inefficient queries. If %% you think this is good idea, good luck, many companies have tried and %% I've personally not seen one that seems to work very well. The next %% major version of MS SQL server is rumored to be something of an xml %% document server, and that's been several years in development. %% %% Merlin %% %% -Original Message- %% From: Bob Calco [mailto:[EMAIL PROTECTED] %% Sent: Wednesday, March 05, 2003 9:10 AM %% To: Merlin Moncure; Alan Gutierrez %% Cc: [EMAIL PROTECTED] %% Subject: RE: [HACKERS] XML ouput for psql %% %% I would like to see PostgreSQL eventually support XQuery: %% %% http://www.w3.org/TR/xquery/ %% http://www.w3.org/TR/query-datamodel/ %% %% I see potentially an alternative front end called xsql, providing %% substantially the same functionality as psql, only using XQuery syntax %% and %% optionally returning recordsets as XML. %% %% Anybody want to put together a team to explore this seriously? There %% are %% probably several non-trivial semantic issues on the back end, but I %% only %% dimly grasp them at this point. %% %% - Bob Calco %% %% %% -Original Message- %% %% From: [EMAIL PROTECTED] %% %% [mailto:[EMAIL PROTECTED] Behalf Of Merlin %% Moncure %% %% Sent: Wednesday, March 05, 2003 8:16 AM %% %% To: Alan Gutierrez %% %% Cc: [EMAIL PROTECTED] %% %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% %% %% %% XSLT could be used to convert virtually any xml table format %% directly %% %% into an insert statement. For me, this is better than using a %% %% programming language plus a parser. XSLT is quite powerful and %% fast %% and %% %% is build on top of xpath, and is a closer fit to the declarative %% %% programming model of sql. Validation could be done at the xslt %% stage %% or %% %% with schemas, which I prefer. %% %% %% %% %% %% Acually, the difficult part has been getting the information back %% %% into the database. Getting it out
Re: [HACKERS] XML ouput for psql
Thanks for the link - I think I just may give it a go. :) - Bob %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Peter Eisentraut %% Sent: Wednesday, March 05, 2003 5:39 PM %% To: Bob Calco %% Cc: Merlin Moncure; Alan Gutierrez; [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% Bob Calco writes: %% %% I would like to see PostgreSQL eventually support XQuery: %% %% The specification is here: %% %% ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-1 %% 4-XML-2002-03.txt %% %% Go for it. %% %% -- %% Peter Eisentraut [EMAIL PROTECTED] %% %% %% ---(end of broadcast)--- %% TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] %% ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML ouput for psql
Peter Eisentraut [EMAIL PROTECTED] writes:
I also think that psql is not the place to implement something like this.
Agreed.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
That seems a little bizarre. Wouldn't we want to have a switch that
just flips the SELECT output format from one style to the other?
This is also a good time to stop and ask whether the frontend/backend
protocol needs to change to support this. Not having read the spec,
I have no idea what the low-level transport needs are for XML output,
but I suspect our present protocol is not it ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] XML ouput for psql
I like PostgreSQL just as it is, and truth be told, I'd like to see some additional features in PL/pgSQL that are completely unrelated to the whole XML issue. But I see some interesting possibilities for PostgreSQL to make inroads in enterprise development if it were the first open source database to do something truly useful with XQuery concepts. Um, why change the backend at all? Why not have libpq do the interference mapping between the front end and backend so that we can leave the backend alone? Seems like a simple application of a good SAX parser to me. -sc -- Sean Chittenden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] XML ouput for psql
Tom Lane wrote: This is also a good time to stop and ask whether the frontend/backend protocol needs to change to support this. Not having read the spec, I have no idea what the low-level transport needs are for XML output, but I suspect our present protocol is not it ... It might be interesting to modify the protocol (and the backend at the point of projection to the front end) so that a user defined formating function could be applied and either accepted or rejected by the front end. Perhaps one flavor of XML output is a start, but I could imagine wanting a custom or even different standard output format. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] XML ouput for psql
If it turns out to be that simple, great - my first goal would be not to touch the backend at all. But I suspect there are some significant semantic issues lurking in the spec that may make that goal unattainable. In any event, I agree with the impulse to change nothing in the backend unless absolutely necessary to do it right - and only then if we determine upon careful consideration that it really is worth doing. I *think* it *might* be well worth it - but only real effort will yield a definitive answer. - Bob Calco %% -Original Message- %% From: [EMAIL PROTECTED] %% [mailto:[EMAIL PROTECTED] Behalf Of Sean Chittenden %% Sent: Wednesday, March 05, 2003 7:51 PM %% To: Bob Calco %% Cc: Merlin Moncure; [EMAIL PROTECTED] %% Subject: Re: [HACKERS] XML ouput for psql %% %% %% I like PostgreSQL just as it is, and truth be told, I'd like %% to see some %% additional features in PL/pgSQL that are completely unrelated %% to the whole %% XML issue. But I see some interesting possibilities for %% PostgreSQL to make %% inroads in enterprise development if it were the first open %% source database %% to do something truly useful with XQuery concepts. %% %% Um, why change the backend at all? Why not have libpq do the %% interference mapping between the front end and backend so that we can %% leave the backend alone? Seems like a simple application of a good %% SAX parser to me. -sc %% %% -- %% Sean Chittenden %% %% ---(end of broadcast)--- %% TIP 4: Don't 'kill -9' the postmaster %% ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] XML ouput for psql
Peter Eisentraut [EMAIL PROTECTED] writes:
I also think that psql is not the place to implement something like this.
Agreed.
It's most likely best put in the backend, as a function like
xmlfoo('select * from t1;')
That seems a little bizarre. Wouldn't we want to have a switch that
just flips the SELECT output format from one style to the other?
Ah, but this approach has the merit that it doesn't require pushing out
a completely new set of tools.
This is also a good time to stop and ask whether the frontend/backend
protocol needs to change to support this. Not having read the spec, I
have no idea what the low-level transport needs are for XML output,
but I suspect our present protocol is not it ...
That could be; there's enough variation in what one might want to do
with XML that it is not trivial to suggest an 'ideal' answer.
We have already seen the proposal of:
record a=b c=d e=f
record a=c c=e e=g
record a=d c=f e=h
record a=e c=g e=i
I would rather prefer something like:
tablea
record
ab/a cd/c ef/e
/record
record
ac/a cd/c ef/e
/record
record
ad/a cd/c ef/e
/record
tablea
(Note that both approaches are quite rational possibilities.)
I'd think that the protocol would involve passing back a row-as-string
for each row in the result set.
--
output = (cbbrowne @cbbrowne.com)
http://www.ntlug.org/~cbbrowne/xml.html
There are two major products that come out of Berkeley: LSD and Unix.
We don't believe this to be a coincidence. - Jeremy S. Anderson
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] XML ouput for psql
* Merlin Moncure [EMAIL PROTECTED] [2003-03-05 10:02]: Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. XSLT could be used to convert virtually any xml table format directly into an insert statement. For me, this is better than using a programming language plus a parser. XSLT is quite powerful and fast and is build on top of xpath, and is a closer fit to the declarative programming model of sql. Validation could be done at the xslt stage or with schemas, which I prefer. XSLT, or Perl, or anything. That's not a problem. It becomes a problem when I have to hand write insert/update statements for every type of element in an XML document. person first-nameAlan/first-name last-nameGutierrez/last-name ssn1234565789/ssn /person If I feed this document to a database I want it to absorb the document, inserting if doesn't already exists, updating it if it does. There is no way to test for the existstence of a record in a person table during an XSLT transformation. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] XML ouput for psql
* Merlin Moncure [EMAIL PROTECTED] [2003-03-05 10:03]: This is just about a total conversion of the backend to an xml document server. The marriage of xml and sql is awkward and not easily retrofitted to existing databases. Its pretty much proven that hierarchal storage techniques (xml included) are more difficult to manage and use than traditional sql databases. However, xml does have some very powerful supplemental technologies for document generation on the client end, especially xslt. Unless there is a compelling reason to use those tools, you are 99% likely better off not using xml at all. XML has also found a niche in the edi world, but in this case you can get away with using the blob technique below. I suppose this was in response to my comments, hard to tell with all the top posting... I am not advocating adding XML storage to the PostgreSQL backend. XML is just a stupid little tag language. PostgreSQL is so much more. I adopted XML in my application to generated HTML via XSLT in Internet Explorer. XSLT is very useful when the interface is expressed in HTML. It has been as good a format as any for transmitting documents and storing them on file system. Yes, mapping XML to SQL requires developer intervention. The little program I wrote makes it very simple to express the mapping from and XML element to db table. It seems like it might be a useful add on. Maybe it could be kept with the back end, and written in C. Getting the information out PostgreSQL has been simple, I've used Perl. Getting information back in has been simple with my utility which will check if the element is already stored as a row, and update, otherwise it will insert. It can also delete rows based on the document and keep track of placeholder values used by the client program to represent SERIAL values in database. What I've got is a system where I post an XML document to a URL and it gets mapped out to my normalized PostgreSQL schema. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] XML ouput for psql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200303041444 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+ZQJNvJuQZxSWSsgRArGEAKD4xs+4Ns3syG175T3k80B6MvNJvgCbBkvF hCkf5SMjLzMJ84uMl1w4tMY= =a2Uq -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] XML ouput for psql
* [EMAIL PROTECTED] [EMAIL PROTECTED] [2003-03-04 14:21]: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The XML standard does not call for any table format. But a number of table formats have been established within the XML framework. Some of them are formatting-oriented (e.g., the HTML model, or CALS which is used in DocBook) and some of them are processing-oriented (e.g., SQL/XML). Which do we need? And which do we need from psql in particular (keeping in mind that psql is primarily for interactive use and shell-scripting)? In any case, it should most likely be a standard table model and not a hand-crafted one. I think all psql needs is a simple output, similar to the ones used by Oracle, Sybase, and MySQL; the calling application should then process it in some way as needed (obviously this is not for interactive use). Where can one find a standard table model? All of the DBs I mentioned (and the perl module DBIx:XML_RDB) all share a similar theme, with subtle differences (i.e. some use row, some row num=x, some have rowset). I'd be happy to write whatever format we can find or develop. My personal vote is the DBIx::XML_RDB format, perhaps with the row number that Oracle uses, producing this: ?xml version=1.0? RESULTSET statement=select * from xmltest ROW num=1 scoops3/scoops flavorchocolate/flavor /ROW ROW num=2 scoops2/scoops flavorvanilla/flavor /ROW /RESULTSET (If, for whatever reason, we go the processing-oriented route, then I claim that there should not be a different output with and without \x mode.) I agree with this. I'm interested in creating XML documents that have heirarcy. I can produce the above with Perl. Acually, the difficult part has been getting the information back into the database. Getting it out is a very simple query. I imagine that every language/environment has an SQL-XML library somewhere, but I wasn't able to find something that would go from XML to SQL. I wrote a utility that takes an xml document, and xml configuration file, and writes the document to a PostgerSQL data base using the configuration file to figure out what goes where. The configuration file makes some use of XPath to pluck the correct values out of the xml doucment. I suppose the same code could generate a document, but it is so easy to do using Perl and cgi, I've not bothered. It has some constraints, but it is a very useful utility. I've been able to abosorb XML documents into my PostgreSQL db just by tweeking the configuration file. Currently, I am porting it to C++ from Perl. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
