Re: [HACKERS] returning PGresult as xml
Peter Eisentraut kirjutas N, 29.01.2004 kell 19:31: Andrew Dunstan wrote: Peter: this looks very nice. What are your intentions with this code? Once we figure out how to handle the on-the-wire character set recoding when faced with XML documents (see separate thread a few weeks ago), I would like to finish it. Put it in contrib? Also, do you intend to implement the SQL/XML functions XMLElement, XMLForest, XMLAttributes, XMLConcat and XMLAgg? You have to implement these directly in the parser, which I'm not yet excited about. Why not use some standard parser ? libxml2 (www.xmlsoft.org) seems nice and is either available as a separate dynamic library or can also be (IIRC) configured to build with just the minimal needed functionality. --- Hannu ---(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] returning PGresult as xml
Andrew Dunstan wrote: Peter: this looks very nice. What are your intentions with this code? Once we figure out how to handle the on-the-wire character set recoding when faced with XML documents (see separate thread a few weeks ago), I would like to finish it. Put it in contrib? Also, do you intend to implement the SQL/XML functions XMLElement, XMLForest, XMLAttributes, XMLConcat and XMLAgg? You have to implement these directly in the parser, which I'm not yet excited about. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] returning PGresult as xml
On Jan 25, 2004, at 3:07 AM, Brian Moore wrote: it's been said that converting a PGresult into xml is trivial and that's why it hasn't been done in the codebase as of yet. i have seen much code that writes xml, and many mistakes are made. most often improper escaping, or writing to a schema/DTD that has not been well-thought out. the transformation into xml is not difficult, but it does require attention to detail. The escaping, at any rate, is trivial if you use a proper API. It sounds like your code is not using any XML API, given that you have not mentioned adding dependencies to libpq and that you've mentioned your own hashtable algorithm. It would be much easier if you did so, though I imagine the additional dependency would mean it would not be accepted into libpq. PGresult num_rows='1' num_cols='2' col_desc num='0' type='int4' format='text' name='foo' / col_desc num='1' type='int4' format='text' name='bar' / row num='0' col num='0'1/col col num='1'2/col /row /PGresult How would you filter for a column in XSLT based on column name with this schema? It's certainly not trivial. I have similar code, and I included the column name as an attribute in each column element for this reason. I also used the java.sql type names rather than PostgreSQL ones, as my code is not specific to PostgreSQL. i would expect that integration would look something like exposing from libpq a function that looks something like: const char *PGresult_as_xml(PGresult *result, int include_dtd); Ugh. So it returns the whole thing as one big string? That won't hold up well if your resultset is large. A better way would be to pump out SAX events. This is what I did for three reasons: 1) The escaping becomes trivial, as mentioned above. In fact, not only does SAX escape things correctly, but it makes you explicitly specify that the string you're giving it is character data, an element name, an attribute name, an attribute value, etc, and handles everything properly based on that. So you'd really have to work to screw it up, unlike code that just does like printf(elem foo='%s' bar='%s'%s/elem, xml_attr_escape(foo_val), xml_attr_escape(bar_val), xml_char_escape(elem_val)); where it would be quite easy to lose track of what needs to be escaped how, what variables are already escaped, etc. 2) It can stream large result sets, provided that the next stage supports doing so. Certainly a raw SAX serializer would, also some XSLT stylesheets with Xalan, and STX/Joost is designed for streaming transformations. 3) If the next stage is a transformation, this makes it unnecessary to serialize and parse the data between. So the SAX way is faster. You're welcome to take a look at my code. I imagine it will not be directly useful to you, as it is written in Java, but I have a live example which puts this stuff to use. Designing an acceptable API and schema is always much easier when you see how it is put to use. http://www.slamb.org/projects/xmldb/ - my (so far poorly-named) xmldb project, which includes the org.slamb.xmldb.ResultSetProducer class to transform a java.sql.ResultSet to SAX events in my resultset schema. http://www.slamb.org/svn/repos/projects/xmldb/src/java/org/slamb/ xmldb/ResultSetProducer.java - source code for said class http://www.slamb.org/projects/mb/ - a message board which uses this code and some XSLT https://www.slamb.org/mb/ - a live example of said message board http://www.slamb.org/svn/repos/projects/mb/src/WEB-INF/xsl/ resultset.xsl - simple XSLT to take an arbitrary resultset and convert it to an HTML table http://www.slamb.org/svn/repos/projects/mb/src/WEB-INF/xsl/main.xsl - an example XSLT file that inherits this and then provides exceptions for a couple columns (not displaying the id column, instead including it as a hyperlink in the name column). Good luck. Scott Lamb ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] returning PGresult as xml
Scott Lamb wrote:
On Jan 25, 2004, at 3:07 AM, Brian Moore wrote:
PGresult num_rows='1' num_cols='2'
col_desc num='0' type='int4' format='text' name='foo' /
col_desc num='1' type='int4' format='text' name='bar' /
row num='0'
col num='0'1/col
col num='1'2/col
/row
/PGresult
How would you filter for a column in XSLT based on column name with
this schema? It's certainly not trivial. I have similar code, and I
included the column name as an attribute in each column element for
this reason.
Close to trivial if you set up a key on the col-desc elements, I should
think. Maybe something like:
xsl:key name=coldesc match=col-desc use=@num /
...
xsl:for-each select= key('coldesc',@num)/@name = 'colname'
...
Alternatively you can get there using the parent and preceding-sibling
axes, but it's less clear.
cheers
andrew
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] returning PGresult as xml
Peter Eisentraut wrote: Let me point out an implementation I made last time this subject was discussed: http://developer.postgresql.org/~petere/xmltable.tar.bz2 This package contains server-side functions that convert a table (more generally a query result) to an XML document and/or and XSL schema both mimicking the SQL/XML standard. Additionally, it contains a function to convert such an XML document back to a table source. I also threw in an XSLT stylesheet to convert an SQL/XML table to an HTML table, so you can more easily view the results. I also have some code in development that adds cursor interfaces, an XML data type, and some integration with the existing XPath functionality. I think that for processing XML in the database and as far as following the existing standards, this is the direction to take. Peter: this looks very nice. What are your intentions with this code? Put it in contrib? Also, do you intend to implement the SQL/XML functions XMLElement, XMLForest, XMLAttributes, XMLConcat and XMLAgg? cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] returning PGresult as xml
hello, this note is intended to describe my work on beginning to further integrate xml into postgresql. first, i'd like to thank the contributers of contrib/xml as their work was instrumental in helping me understand what support exists and where i wanted to go. thanks. my first requirement is to export data from the database into a format which can be read not only by existing (postgresql) clients but by people and programs that don't know how to use a PGresult. xml is very verbose, but its popularity makes it closer to universal than anything else of which i could think. in addition, ideas like XSL/XSLT make an export of xml very attractive to me. it's been said that converting a PGresult into xml is trivial and that's why it hasn't been done in the codebase as of yet. i have seen much code that writes xml, and many mistakes are made. most often improper escaping, or writing to a schema/DTD that has not been well-thought out. the transformation into xml is not difficult, but it does require attention to detail. i feel badly that i have not been able to use any existing standards. xmlrpc, i found, was not type-rich enough, and that made it difficult or impossible to use. in particular, the only way to represent a matrix is as a struct of structs. this makes it very verbose for one to encode a PGresult. i found SOAP too difficult for compliance. so my result was to create a schema, which results in a DTD. an example of what my code generates can be found below. the following xml is the result of the query SELECT 1 as foo 2 as bar: ?xml version='1.0' encoding='ISO-8859-1'? !DOCTYPE PGresult [ !ELEMENT PGresult (col_desc*, row*) !ATTLIST PGresult num_rows CDATA #REQUIRED num_cols CDATA #REQUIRED !ELEMENT col_desc EMPTY !ATTLIST col_desc num CDATA #REQUIRED format (text | binary) #REQUIRED type CDATA #REQUIRED name CDATA #REQUIRED !ELEMENT row (col*) !ATTLIST row num CDATA #REQUIRED !ELEMENT col (#PCDATA) !ATTLIST col num CDATA #REQUIRED !ENTITY NULL '' ] PGresult num_rows='1' num_cols='2' col_desc num='0' type='int4' format='text' name='foo' / col_desc num='1' type='int4' format='text' name='bar' / row num='0' col num='0'1/col col num='1'2/col /row /PGresult a slightly more complicated example: template1=# select oid,typname,typlen,typtype from pg_type where oid20; oid | typname | typlen | typtype -+-++- 16 | bool| 1 | b 17 | bytea | -1 | b 18 | char| 1 | b 19 | name| 32 | b (4 rows) !DOCTYPE PGresult [ !ELEMENT PGresult (col_desc*, row*) !ATTLIST PGresult num_rows CDATA #REQUIRED num_cols CDATA #REQUIRED !ELEMENT col_desc EMPTY !ATTLIST col_desc num CDATA #REQUIRED format (text | binary) #REQUIRED type CDATA #REQUIRED name CDATA #REQUIRED !ELEMENT row (col*) !ATTLIST row num CDATA #REQUIRED !ELEMENT col (#PCDATA) !ATTLIST col num CDATA #REQUIRED !ENTITY NULL '' ] PGresult num_rows='4' num_cols='4' col_desc num='0' type='oid' format='text' name='oid' / col_desc num='1' type='name' format='text' name='typname' / col_desc num='2' type='int2' format='text' name='typlen' / col_desc num='3' type='char' format='text' name='typtype' / row num='0' col num='0'16/col col num='1'bool/col col num='2'1/col col num='3'b/col /row row num='1' col num='0'17/col col num='1'bytea/col col num='2'-1/col col num='3'b/col /row row num='2' col num='0'18/col col num='1'char/col col num='2'1/col col num='3'b/col /row row num='3' col num='0'19/col col num='1'name/col col num='2'32/col col num='3'b/col /row /PGresult i have done this work for myself and my own needs, so i fully understand if this work is not interesting to the postgresql group in general. however, if there is some chance that the changes could be incorporated into the tree, i would be interested in contributing, as integration into a proper version of postgresql will make my build easier. ;) i would expect that integration would look something like exposing from libpq a function that looks something like: const char *PGresult_as_xml(PGresult *result, int include_dtd); i would also expect that psql would be modified to take a \X and to call the above function. there is some strangeness now, as psql doesn't call methods defined in libpq to print; it has its own printer. i, of course, would do this work. :) i just need to know that people are interested. also, if integration is going to happen, i will need to replace calls to my hashtables with calls to postgresql's hashtables. i saw dynamic hashtables in the backend, but not in the interfaces. i wasn't exactly sure how i should go about introducing another module to the frontend; there could be problems of which i remain blissfully unaware. i look forward to feedback, and i hope this note finds you well, b
Re: [HACKERS] returning PGresult as xml
Brian Moore kirjutas P, 25.01.2004 kell 11:07: hello, this note is intended to describe my work on beginning to further integrate xml into postgresql. first, i'd like to thank the contributers of contrib/xml as their work was instrumental in helping me understand what support exists and where i wanted to go. thanks. First, IMHO having unified XML support is a good thing for Postgres. I still have some questions and suggestions: At what place do you intend to add your converter ? I remember someone started abstracting out the FE/BE protocol calls in server code with an aim of supporting multiple protocols, but stopped without making too much progress (?) Also, I would suggest that XML Schema datatypes should be used, preferrably together with either RelaxNG schema or something from SQL/XML spec. - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] returning PGresult as xml
Brian Moore wrote: i feel badly that i have not been able to use any existing standards. xmlrpc, i found, was not type-rich enough, and that made it difficult or impossible to use. in particular, the only way to represent a matrix is as a struct of structs. this makes it very verbose for one to encode a PGresult. i found SOAP too difficult for compliance. so my result was to create a schema, which results in a DTD. Let me point out an implementation I made last time this subject was discussed: http://developer.postgresql.org/~petere/xmltable.tar.bz2 This package contains server-side functions that convert a table (more generally a query result) to an XML document and/or and XSL schema both mimicking the SQL/XML standard. Additionally, it contains a function to convert such an XML document back to a table source. I also threw in an XSLT stylesheet to convert an SQL/XML table to an HTML table, so you can more easily view the results. I also have some code in development that adds cursor interfaces, an XML data type, and some integration with the existing XPath functionality. I think that for processing XML in the database and as far as following the existing standards, this is the direction to take. Also last time this subject was dicussed, I believe it was Mike Mascari who proposed and implemented another solution which is more client-side oriented. He wrote a piece of code that took a normal libpq result set and shipped it off as SQL/XML wrapped in SOAP. And it had streaming capabilities for large result sets. These are two complementary approaches that exist more or less. Of course this only covers the C API and would need sensible extensions for other programming langauges. But I invite you to look at them and see whether they fit your needs (the ideas, not necessarily the state of the code). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] returning PGresult as xml
Peter Eisentraut wrote: Brian Moore wrote: i feel badly that i have not been able to use any existing standards. xmlrpc, i found, was not type-rich enough, and that made it difficult or impossible to use. in particular, the only way to represent a matrix is as a struct of structs. this makes it very verbose for one to encode a PGresult. i found SOAP too difficult for compliance. so my result was to create a schema, which results in a DTD. Let me point out an implementation I made last time this subject was discussed: http://developer.postgresql.org/~petere/xmltable.tar.bz2 Also last time this subject was dicussed, I believe it was Mike Mascari who proposed and implemented another solution which is more client-side oriented. I humbly confess it wasn't me. We use CORBA Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
