Обсуждение: Re: 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 >
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 %%
> 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
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 %%
* 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