XML ouput for psql

greg@turnstep.com kirjutas K, 26.02.2003 kell 22:46:

and with the \x option:

<?xml version="1.0" encoding="SQL_ASCII"?>
<resultset psql_version="7.4devel" query="select * from foo;">

<columns>
<col num="1">a</col>
<col num="2">b</col>
<col num="3">c</col>
<col num="4">mucho nacho </col>
</columns>
<row num="1">
<cell name="a">1</cell>
<cell name="b">pizza</cell>
<cell name="c">2003-02-25 15:19:22.169797</cell>
<cell name="mucho nacho "></cell>
</row>
<row num="2">
<cell name="a">2</cell>
<cell name="b">mushroom</cell>
<cell name="c">2003-02-25 15:19:26.969415</cell>
<cell name="mucho nacho "></cell>
</row>
</resultset>

The default encoding "SQL-ASCII" is not valid for XML.
Should it be automatically changed to something else?

I think you should force conversion to something standard, try using
automatic conversion to some known client encoding.

btw, "UNICODE" is also not any known encoding in XML, but PostgreSQL
uses it to mean utf-8

The flag "-X" is already taken, unfortunately, although \X is not.
I used "-L" and "\L" but they are not as memorable as "X". Anyone
see a way around this? Can we still use \X inside of psql?

It would be nice to include the string representation of the column
types in the xml output:
<col type="int8">foo</col>
....but I could not find an easy way to do this: PQftype returns the
OID only (which is close but not quite there). Is there an
existing way to get the name of the type of a column from a
PQresult item?

Run "select oid,typname from pg_type;" first if run in xml mode and
store the oid/columnname pairs.

you could also store the result in ~/.psql for faster access later on
and manually clear it if new types are defined

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

greg@turnstep.com writes:

Patch to add XML output to psql:

This would get me more excited if you do one or both of the following:

1. Look into the SQL/XML standard draft (ftp.sqlstandards.org) to find out
whether the standard addresses this sort of thing.

2. Use an established/standardized XML (or SGML) table model rather than
rolling your own.

Incidentally, the HTML table model is such an established and standardized
XML and SGML table model, so the easiest way to get the task "add XML
output to psql" done is to update the HTML output to conform to XHTML.
That way you get both the strict XML and you can look at the formatted
result with any old (er, new) browser.

--
Peter Eisentraut peter_e@gmx.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hannu Krosing wrote:

I think you should force conversion to something standard, try using
automatic conversion to some known client encoding.

I've thought about this some more, and the only thing I can think
about doing without being too heavy-handed is to change the encoding
to "US-ASCII" whenever someone enters "XML" mode if the encoding is set
to "SQL-ASCII". Perhaps with a warning.

"The character set most commonly use in the Internet and used especially in
protocol standards is US-ASCII, this is strongly encouraged."
http://www.iana.org/assignments/character-sets

On the other hand, SQLX seems to lean toward a strict unicode encoding
(see my reply to Peter Eisentraut for more on that).

Run "select oid,typname from pg_type;" first if run in xml mode and
store the oid/columnname pairs.

I realize that I could run a SQL query against pg_type to grab the info,
but I was hoping there was an internal function similar to PQtype which
would return the information.

you could also store the result in ~/.psql for faster access
later on and manually clear it if new types are defined

Not only does pg_type has literally hundreds of entries, but there is no
way to guarantee that these are correct at the time when the query is
run, so I don't think this is viable.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302280938
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+X3hCvJuQZxSWSsgRArMTAKChouxnFF1ugI1mutXYJf14p1ICGwCfUDG9
yISxrIvqxnYWHfvD0lOWZAQ=
=M6nd
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Eisentraut wrote:

1. Look into the SQL/XML standard draft (ftp.sqlstandards.org) to find out
whether the standard addresses this sort of thing.

The URL you gave leads to a site curiously content-free and full of dead links.
I've looked around a bit, but found nothing definitive. One good resource I
did find was this:

http://www.wiscorp.com/sql/SQLX_Bringing_SQL_and_XML_Together.pdf

The article mentions a lot of links on the sqlstandards.org and iso.org sites, none
of which work or are restricted. If anyone knows of some good links, please
let me know. (especially ISO 9075). From what I've read of the SQLX stuff, the
format in my patch should be mostly standard:

<row>
<name>Joe Sixpack</name>
<age>35</age>
<state>Alabama</state>
</row>

One problem is that the recommended way to handle non-standard characters
(including spaces) is to escape them like this:

foobar baz => <foobar_x0020_baz>

This also includes escaping things like "_x*" and "xml*". We don't have
anything like that in the code yet (?), but we should probably think about
heading that way. I think escaping whitespace in quotes is good enough
for now for:

foobar baz => <"foobar baz">

The xsd and xsi standards are also interesting, but needlessly complicated
for psql output, IMO.

Incidentally, the HTML table model is such an established and standardized
XML and SGML table model, so the easiest way to get the task "add XML
output to psql" done is to update the HTML output to conform to XHTML.
That way you get both the strict XML and you can look at the formatted
result with any old (er, new) browser.

I don't agree with this: XML and XHTML are two different things. We could
certainly upgrade the HTML portion, but I am pretty sure that the XML
standard calls for this format:

<columnname>data here</columnname>

..which is not valid XHTML and won't be viewable by any browser. The other
suggested XML formats are even further from XHTML than the above. The HTML
format should be "html table/layout" specific and the XML should be
"schema/data" specific.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302280938

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+X3k5vJuQZxSWSsgRAuXFAKDGO1IsjB9Lwtkcws1xJy47PibcLQCg3dx5
fsy27qguZv841lPvCjzdUic=
=4f9B
-----END PGP SIGNATURE-----

greg@turnstep.com writes:

I don't agree with this: XML and XHTML are two different things.

No one claimed anything to the contrary.

We could certainly upgrade the HTML portion, but I am pretty sure that
the XML standard calls for this format:

<columnname>data here</columnname>

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.

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

--
Peter Eisentraut peter_e@gmx.net

greg@turnstep.com writes:

I don't agree with this: XML and XHTML are two different things.

No one claimed anything to the contrary.

We could certainly upgrade the HTML portion, but I am pretty sure that
the XML standard calls for this format:

<columnname>data here</columnname>

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 would expect XML output to be based on whatever the tree of data
contained.

If the tree is to be rewritten, then this would mean having some sort of
transformation engine in PostgreSQL that you would have to program.

If I want a CALS table, then I'll push CALS table data into the
database.

If I'm storing a GnuCash chart of accounts in PostgreSQL, I am
ludicrously uninterested in seeing it rewritten for some sort of
physical layout. Spit out the tags that are stored in the database, not
some rewriting of it.
--
(reverse (concatenate 'string "moc.enworbbc@" "enworbbc"))
http://cbbrowne.com/info/linuxdistributions.html
(1) Sigs are preceded by the "sigdashes" line, ie "\n-- \n" (dash-dash-space).
(2) Sigs contain at least the name and address of the sender in the first line.
(3) Sigs are at most four lines and at most eighty characters per line.

-----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">
<scoops>3</scoops>
<flavor>chocolate</flavor>
</ROW>
<ROW num="2">
<scoops>2</scoops>
<flavor>vanilla</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 greg@turnstep.com
PGP Key: 0x14964AC8 200303041444
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+ZQJNvJuQZxSWSsgRArGEAKD4xs+4Ns3syG175T3k80B6MvNJvgCbBkvF
hCkf5SMjLzMJ84uMl1w4tMY=
=a2Uq
-----END PGP SIGNATURE-----

* greg@turnstep.com <greg@turnstep.com> [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">
<scoops>3</scoops>
<flavor>chocolate</flavor>
</ROW>
<ROW num="2">
<scoops>2</scoops>
<flavor>vanilla</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 - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

* greg@turnstep.com <greg@turnstep.com> [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">
<scoops>3</scoops>
<flavor>chocolate</flavor>
</ROW>
<ROW num="2">
<scoops>2</scoops>
<flavor>vanilla</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 - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

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.

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: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin Moncure
%% Sent: Wednesday, March 05, 2003 8:16 AM
%% To: Alan Gutierrez
%% Cc: pgsql-hackers@postgresql.org
%% 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 majordomo@postgresql.org so that your
%% message can get through to the mailing list cleanly

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:robert.calco@verizon.net]
Sent: Wednesday, March 05, 2003 9:10 AM
To: Merlin Moncure; Alan Gutierrez
Cc: pgsql-hackers@postgresql.org
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: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin

Moncure

%% Sent: Wednesday, March 05, 2003 8:16 AM
%% To: Alan Gutierrez
%% Cc: pgsql-hackers@postgresql.org
%% 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

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="false">3</FIELD>
<FIELD name="flavor" isNull="false">chocolate</FIELD>
</ROW>
<ROW>
<FIELD name="scoops" isNull="false">2</FIELD>
<FIELD name="flavor" isNull="false">vanilla</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&quot;&gt;
<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>

greg@turnstep.com 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 peter_e@gmx.net

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 peter_e@gmx.net

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 peter_e@gmx.net

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: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin Moncure
%% Sent: Wednesday, March 05, 2003 10:19 AM
%% To: robert.calco@verizon.net
%% Cc: pgsql-hackers@postgresql.org
%% 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:robert.calco@verizon.net]
%% > Sent: Wednesday, March 05, 2003 9:10 AM
%% > To: Merlin Moncure; Alan Gutierrez
%% > Cc: pgsql-hackers@postgresql.org
%% > 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: pgsql-hackers-owner@postgresql.org
%% > %% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Merlin
%% Moncure
%% > %% Sent: Wednesday, March 05, 2003 8:16 AM
%% > %% To: Alan Gutierrez
%% > %% Cc: pgsql-hackers@postgresql.org
%% > %% 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 majordomo@postgresql.org so that your
%% > %% message can get through to the mailing list cleanly
%% >
%%
%%
%% ---------------------------(end of broadcast)---------------------------
%% TIP 4: Don't 'kill -9' the postmaster
%%

Thanks for the link - I think I just may give it a go. :)

- Bob

%% -----Original Message-----
%% From: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Peter Eisentraut
%% Sent: Wednesday, March 05, 2003 5:39 PM
%% To: Bob Calco
%% Cc: Merlin Moncure; Alan Gutierrez; pgsql-hackers@postgresql.org
%% 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 peter_e@gmx.net
%%
%%
%% ---------------------------(end of broadcast)---------------------------
%% TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
%%

Peter Eisentraut <peter_e@gmx.net> 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

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

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

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: pgsql-hackers-owner@postgresql.org
%% [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Sean Chittenden
%% Sent: Wednesday, March 05, 2003 7:51 PM
%% To: Bob Calco
%% Cc: Merlin Moncure; pgsql-hackers@postgresql.org
%% 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
%%

Peter Eisentraut <peter_e@gmx.net> 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>
<a>b</a> <c>d</c> <e>f</e>
</record>
<record>
<a>c</a> <c>d</c> <e>f</e>
</record>
<record>
<a>d</a> <c>d</c> <e>f</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

* Merlin Moncure <merlin.moncure@rcsonline.com> [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-name>Alan</first-name>
<last-name>Gutierrez</last-name>
<ssn>1234565789</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 - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

* Merlin Moncure <merlin.moncure@rcsonline.com> [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 - ajglist@izzy.net
http://khtml-win32.sourceforge.net/ - KHTML on Windows

-----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 greg@turnstep.com
PGP Key: 0x14964AC8 200303061020

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+Z2jHvJuQZxSWSsgRAj7IAJ4hLEos9OlE67O02gVrrqxwT9n3AQCeJxto
N2LFyvXPfGY2whPUs5k+PQA=
=PYfs
-----END PGP SIGNATURE-----

Peter Eisentraut kirjutas N, 06.03.2003 kell 00:37:

greg@turnstep.com 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.

I have written an aggregate function in pl/python for my own needs that
returns underlying query fomatted as XML, but it has some problems:

1) both the row-to-xml-fragment and
collect-the-fragments-to-wellformed-xml-doc have to be defined for each
and every different query (the actual function text is the same).

2) it is unneccesaryly hard to define a function that takes a record as
argument - the record type is lost: for even simple things like this

select * from (select * from mytable) mtab;

the result of inner query is _not_ of rowtype mytable, i.e.

you can do

select xmlfrag(mytable) from mytable;

but not

select xmlfrag(mytable) from (select * from mytable) mytable;

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

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 peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> 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

Tom Lane wrote:

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.

Is it at all worth considering returning an xml document as a scalar?
In most cases when I'm extracting constructed documents from the backend
I would not be using psql to do any processing beyond executing the
query. Also, if at some point the backend learns to return more complex
documents than simple table mappings, this does not complicate the
protocol or the client libs.

As for schemas, they could be manifestly requested by the query. For
simplicity, they could be inlined into the document, returned instead of
(think explain...), or as a separate column.

Merlin

Greg, do you have a newer patch to address the feedback you received, or
is this one good?

---------------------------------------------------------------------------

greg@turnstep.com wrote:
[ There is text before PGP section. ]

[ PGP not available, raw data follows ]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Peter Eisentraut wrote:

1. Look into the SQL/XML standard draft (ftp.sqlstandards.org) to find out
whether the standard addresses this sort of thing.

The URL you gave leads to a site curiously content-free and full of dead links.
I've looked around a bit, but found nothing definitive. One good resource I
did find was this:

http://www.wiscorp.com/sql/SQLX_Bringing_SQL_and_XML_Together.pdf

The article mentions a lot of links on the sqlstandards.org and iso.org sites, none
of which work or are restricted. If anyone knows of some good links, please
let me know. (especially ISO 9075). From what I've read of the SQLX stuff, the
format in my patch should be mostly standard:

<row>
<name>Joe Sixpack</name>
<age>35</age>
<state>Alabama</state>
</row>

One problem is that the recommended way to handle non-standard characters
(including spaces) is to escape them like this:

foobar baz => <foobar_x0020_baz>

This also includes escaping things like "_x*" and "xml*". We don't have
anything like that in the code yet (?), but we should probably think about
heading that way. I think escaping whitespace in quotes is good enough
for now for:

foobar baz => <"foobar baz">

The xsd and xsi standards are also interesting, but needlessly complicated
for psql output, IMO.

Incidentally, the HTML table model is such an established and standardized
XML and SGML table model, so the easiest way to get the task "add XML
output to psql" done is to update the HTML output to conform to XHTML.
That way you get both the strict XML and you can look at the formatted
result with any old (er, new) browser.

I don't agree with this: XML and XHTML are two different things. We could
certainly upgrade the HTML portion, but I am pretty sure that the XML
standard calls for this format:

<columnname>data here</columnname>

..which is not valid XHTML and won't be viewable by any browser. The other
suggested XML formats are even further from XHTML than the above. The HTML
format should be "html table/layout" specific and the XML should be
"schema/data" specific.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302280938

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+X3k5vJuQZxSWSsgRAuXFAKDGO1IsjB9Lwtkcws1xJy47PibcLQCg3dx5
fsy27qguZv841lPvCjzdUic=
=4f9B
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

[ Decrypting message... End of raw data. ]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Greg, do you have a newer patch to address the feedback you received, or
is this one good?

I have a newer patch, but I am not 100% sure a consensus was reached. I recall
the thread veering into talk of XML on the backend, but don't recall if anyone
still had strong objections to a quick psql wrapper. If not, I will clean up
the existing patch and resubmit tomorrow.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200303171641

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+dkE5vJuQZxSWSsgRAkVSAJ9aLoLC23OoNcVEw4hQiaBrPcSqNQCfTxH3
crC4ssFKbBo60gHvJT3WsU0=
=Qsif
-----END PGP SIGNATURE-----

I like the idea of doing XML in psql --- it seems like a natural place
for it.

---------------------------------------------------------------------------

greg@turnstep.com wrote:
[ There is text before PGP section. ]

[ PGP not available, raw data follows ]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Greg, do you have a newer patch to address the feedback you received, or
is this one good?

I have a newer patch, but I am not 100% sure a consensus was reached. I recall
the thread veering into talk of XML on the backend, but don't recall if anyone
still had strong objections to a quick psql wrapper. If not, I will clean up
the existing patch and resubmit tomorrow.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200303171641

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+dkE5vJuQZxSWSsgRAkVSAJ9aLoLC23OoNcVEw4hQiaBrPcSqNQCfTxH3
crC4ssFKbBo60gHvJT3WsU0=
=Qsif
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[ Decrypting message... End of raw data. ]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I like the idea of doing XML in psql --- it seems like a natural place
for it.

Not really; what of applications other than shell scripts that would
like to get XML-formatted output?

There was some talk in the FE/BE protocol thread of adding hooks to
support more than one output format from the backend. Much of the
infrastructure already exists (see DestReceiver in the backend); we
just need an agreement on the protocol. On the whole I'd rather see
it done that way than burying the logic in psql.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I like the idea of doing XML in psql --- it seems like a natural place
for it.

Not really; what of applications other than shell scripts that would
like to get XML-formatted output?

There was some talk in the FE/BE protocol thread of adding hooks to
support more than one output format from the backend. Much of the
infrastructure already exists (see DestReceiver in the backend); we
just need an agreement on the protocol. On the whole I'd rather see
it done that way than burying the logic in psql.

Well, programs can run psql using popen. It seems overkill to get the
protocol involved, specially since it is output-only. I can't imagine
who would bother with the wire protocol messiness just to get xml.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Not really; what of applications other than shell scripts that would
like to get XML-formatted output?

Well, programs can run psql using popen. It seems overkill to get the
protocol involved, specially since it is output-only. I can't imagine
who would bother with the wire protocol messiness just to get xml.

Having to popen a psql isn't overkill? This seems like a far messier
solution than the other. Furthermore, it's just plain not an available
solution in many scenarios (think of a Java program running JDBC; it may
not have privileges to do popen, and may not have access to a copy of
psql anyway).

If we were not already opening up the protocol for changes, I'd be
resistant to the idea too. But since we are, I think it should be fixed
where it's cleanest to fix it.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Not really; what of applications other than shell scripts that would
like to get XML-formatted output?

Well, programs can run psql using popen. It seems overkill to get the
protocol involved, specially since it is output-only. I can't imagine
who would bother with the wire protocol messiness just to get xml.

Having to popen a psql isn't overkill? This seems like a far messier
solution than the other. Furthermore, it's just plain not an available
solution in many scenarios (think of a Java program running JDBC; it may
not have privileges to do popen, and may not have access to a copy of
psql anyway).

If we were not already opening up the protocol for changes, I'd be
resistant to the idea too. But since we are, I think it should be fixed
where it's cleanest to fix it.

What would be interesting would be to enable libpq to dump XML, and have
psql use that. Why put XML capability in the backend? Of course, that
doesn't help jdbc. How do you propose the backend would do XML?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What would be interesting would be to enable libpq to dump XML, and have
psql use that.

... or in the backend so libpq could use it, and thence psql.

Why put XML capability in the backend?

So that non-libpq-based clients could use it.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What would be interesting would be to enable libpq to dump XML, and have
psql use that.

... or in the backend so libpq could use it, and thence psql.

Why put XML capability in the backend?

So that non-libpq-based clients could use it.

OK, I have two ideas here. First, can we create a function that takes a
query result and returns one big XML string. I am not sure how to pump
a result into a function. The other downside is that we would have to
construct the entire result string in memory.

The other idea I had was a GUC variable that returned all query results
as one big XML string. That would prevent creating the entire string in
backend memory, and might enable cursor fetches through the XML string.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I assume we are not moving in the XML/psql direction, right? We want it
int he backend, or the psql HTML converted to XHTML?

---------------------------------------------------------------------------

greg@turnstep.com wrote:
[ There is text before PGP section. ]

[ PGP not available, raw data follows ]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Patch to add XML output to psql:

http://www.gtsm.com/xml.patch.txt

Notes and questions:

The basic output looks something like this:

<?xml version="1.0" encoding="SQL_ASCII"?>
<resultset psql_version="7.4devel" query="select * from foo;">

<columns>
<col num="1">a</col>
<col num="2">b</col>
<col num="3">c</col>
<col num="4">mucho nacho </col>
</columns>
<row num="1">
<a>1</a>
<b>pizza</b>
<c>2003-02-25 15:19:22.169797</c>
<"mucho nacho "></"mucho nacho ">
</row>
<row num="2">
<a>2</a>
<b>mushroom</b>
<c>2003-02-25 15:19:26.969415</c>
<"mucho nacho "></"mucho nacho ">
</row>
<footer>(2 rows)</footer>
</resultset>

and with the \x option:

<?xml version="1.0" encoding="SQL_ASCII"?>
<resultset psql_version="7.4devel" query="select * from foo;">

<columns>
<col num="1">a</col>
<col num="2">b</col>
<col num="3">c</col>
<col num="4">mucho nacho </col>
</columns>
<row num="1">
<cell name="a">1</cell>
<cell name="b">pizza</cell>
<cell name="c">2003-02-25 15:19:22.169797</cell>
<cell name="mucho nacho "></cell>
</row>
<row num="2">
<cell name="a">2</cell>
<cell name="b">mushroom</cell>
<cell name="c">2003-02-25 15:19:26.969415</cell>
<cell name="mucho nacho "></cell>
</row>
</resultset>

The default encoding "SQL-ASCII" is not valid for XML.
Should it be automatically changed to something else?

The flag "-X" is already taken, unfortunately, although \X is not.
I used "-L" and "\L" but they are not as memorable as "X". Anyone
see a way around this? Can we still use \X inside of psql?

It would be nice to include the string representation of the column
types in the xml output:
<col type="int8">foo</col>
....but I could not find an easy way to do this: PQftype returns the
OID only (which is close but not quite there). Is there an
existing way to get the name of the type of a column from a
PQresult item?

The HTML, XML, and Latex modes should have better documentation -
I'll submit a separate doc patch when/if this gets finalized.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302261518

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+XSR/vJuQZxSWSsgRAi2jAJ9IAKnMBmNcVEEI8TXQBBd/rtm4XQCg0Vjq
IO9OsCSkdnNJqnrYYutM3jw=
=9kwY
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[ Decrypting message... End of raw data. ]

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I assume we are not moving in the XML/psql direction, right? We want it
int he backend, or the psql HTML converted to XHTML?

I don't think a consensus was ever reached. It would certainly be better if
this was done on the backend, but that seems to be a long time away, and
some have argued that it is not the job of the engine to do this anyway.

I agree at the very least we should update the HTML ourput for psql: I'll
try to make a patch for that this weekend.

I still think we should at least have a rudimentary xml output option inside
of psql. It won't be perfect, but we can certainly have the flag toggle
a backend variable when/if the backend supports XML directly.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200305301452
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+16npvJuQZxSWSsgRAnTHAJ0UN3HFWVybqDd/5lnsV2CcotRxSgCgp7md
W9Iho/Y1mwUYEl8SX/9oAVc=
=G1Jx
-----END PGP SIGNATURE-----

I assume we are not moving in the XML/psql direction, right? We
want it int he backend, or the psql HTML converted to XHTML?

I don't think a consensus was ever reached. It would certainly be
better if this was done on the backend, but that seems to be a long
time away, and some have argued that it is not the job of the engine
to do this anyway.

Few points for the archives regarding XML and databases (spent 9mo
working on this kinda stuff during the .com days):

*) Use libxml2. MIT Licensed, most complete opensource XML
implementation available, and fast. See the XML benchmarks on
sf.net for details. To avoid library naming conflicts, the library
should likely be renamed to pgxml.so and imported into the src
tree. Mention java in this context and risk being clubbed to death.

*) There should be two storage formats for XML data:

a) DOM-esque storage: broken down xmlNodes. This is necessary for
indexing specific places in documents (ala XPath queries).
Actual datums on the disk should be similar in structure to the
xmlNode struct found in libxml2 (would help with the
serialization in either direction). In database xslt
transformations are also possible with the data stored this way.

b) SAX-esque storage: basically a single BYTEA/TEXT column. Not
all documents need to be indexed/searchable and SAX processing
of data is generally more efficient if you don't know what
you're looking for. This format is the low hanging fruit
though.

-sc

--
Sean Chittenden

Sean Chittenden kirjutas R, 30.05.2003 kell 23:20:

I assume we are not moving in the XML/psql direction, right? We
want it int he backend, or the psql HTML converted to XHTML?

I don't think a consensus was ever reached. It would certainly be
better if this was done on the backend, but that seems to be a long
time away, and some have argued that it is not the job of the engine
to do this anyway.

Few points for the archives regarding XML and databases (spent 9mo
working on this kinda stuff during the .com days):

*) Use libxml2. MIT Licensed, most complete opensource XML
implementation available, and fast. See the XML benchmarks on
sf.net for details. To avoid library naming conflicts, the library
should likely be renamed to pgxml.so and imported into the src
tree. Mention java in this context and risk being clubbed to death.

Agree completely on all points ;)

*) There should be two storage formats for XML data:

a) DOM-esque storage: broken down xmlNodes. This is necessary for
indexing specific places in documents (ala XPath queries).
Actual datums on the disk should be similar in structure to the
xmlNode struct found in libxml2 (would help with the
serialization in either direction). In database xslt
transformations are also possible with the data stored this way.

b) SAX-esque storage: basically a single BYTEA/TEXT column. Not
all documents need to be indexed/searchable and SAX processing
of data is generally more efficient if you don't know what
you're looking for. This format is the low hanging fruit
though.

I think that Oleg and Todor very recently proposed somethink that could
use b) and still provide indexed access.

Most flexible would be some way to define, how much of a tree is kept
together, as xmlNode/tuple would probably be too much overhead for most
operations, whereas xmlFile/tuple would also, just for other ops;)

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