Re: [PATCH] (super)user-loadable mysqld parsers

[Arnold Daniels]

Hi,

I stand corrected, Oracle does support XML in their relation DB. I've
seen some of these articles before, but mistakenly understood that is
was a separate DB. My experience of Oracle doesn't go much further than
playing with XE. If I understand the example correctly you would query
XML data in a blob (mytable.productlist) as:
  XQuery for $i in ora:view("mytable")/ROW where status="abc" return
<item>{$i/productlist/product/description}</item>;
or
  SELECT XQuery("for $i in $y return
<item>{$i/product/description}</item>" PASSING productlist RETURNING
CONTENT) FROM mytable.productlist WHERE status="abc";

So basically Oracle has the object relational mapping as I suggested it,
expect the child nodes of the table is ROW, which is much more logical
and it uses an XQuery extension ora:view() instead of doc(). Integration
of an XQuery statement in an SQL query is also possible as IBM DB2 does.

In any case, Oracle uses a specific keyword, XQuery, to select a
different parser. I really think that is the way to go, regardless of
you do or do not want to embed the XQuery statement.

Best regards,
Arnold Daniels


Ken Jacobs wrote:
> Arnold, you wrote:
>
>     I'm not aware that Oracle has XQuery support in their relational
>     database. As far as I know they only support that in a specific
>     Oracle XML DB and in Berkeley XML DB. To my knowledge, the only
>     mayor DB that supports this is IBM DB2 and they don't do
>     relational mapping in XQuery. Instead they do an SQL query with
>     and import of XML residing in a column to be used in the XQuery
>     statement. However, if you want a pure XQuery solution you will
>     need mapping.
>
> Oracle does indeed have XQuery support in the relational database, and 
> has had since Oracle9i, some years back.  Oracle was the FIRST 
> relational database to do so.   This is NOT a separate server, 
> separate store or separate system at all, but is tightly integrated 
> with the relational database.  It supports XPath expressions, XQuery, 
> an XML schema and a repository model.   You can use SQL to access XML 
> data or XML to access relational data.   With the recently-announced 
> Oracle11g, there are now three options for storing XML data: 
> object/relational (shredded), BLOB, and a new optimized binary XML 
> format.  Each serves a different use case.  I believe Oracle's XML 
> capability has far more functionality and performance, and has a track 
> record of much broader and significant customer usage, than IBM's DB2 
> XML extender (which, unlike Oracle's product) is not free ...
>
> See the Oracle XMLDB home page 
> <http://www.oracle.com/technology/tech/xml/xmldb/index.html> for more 
> info.
>
> Thought you'd want to know.
>
> Regards,
>
> Ken
>
> Arnold Daniels wrote:
>>
>>
>> Eric Prud'hommeaux wrote:
>>> * Arnold Daniels <info@stripped> [2007-09-08 18:11-0400]
>>>  
>>>> Hi Eric,
>>>>     
>>>
>>>
>>> == API stuff ==
>>>  
>>>> I'm not fully agreeing with you here.
>>>>     
>>>
>>> You can't strongly disagree with me 'cause i don't have a strong
>>> opinion. I have a vague preference for the approach I've taken, and
>>> will defend it 'till I'm dead^h^hbored. Seriously, I appreciate
>>> this discussion.
>>>   
>> Aren't you even considering, I might just convince you ;).
>>>  
>>>>                                       I think changing the API just 
>>>> has to big of an impact on all the different clients. Let's say 
>>>> you're use ODBC or PDO in PHP. There's no way to implement that 
>>>> neatly. I really don't like automatic selection either cause it's 
>>>> to easy to mess up.
>>>>     
>>>
>>> My uninformed guess is that PHP, DBI, et al are using mysql_query. The
>>> deployment challenge in that case is to add a call for the new API
>>> entry, mysql_send_query, which is like mysql_query but takes an extra
>>> parameter. Likewise, ODBC's executeQuery function would need to link
>>> to mysql_send_query and have some settable var to indicate the
>>> language code to use.
>>>   
>> The point here was not about the examples. But there are a few mysql 
>> clients out there: ODBC, /J, native PHP, etc. On top of those there 
>> are hundreds of other drivers, usually multiple for each programming 
>> language. All of these would need to be changed to support parser 
>> switching. Now on top of those there are thousand and thousands of DB 
>> abstraction classes and libs. All of those would need to be changed 
>> as well.
>> Besides that, the API off all of these clients can't break 
>> compatibility. In many cases that means that having a second 
>> parameter to choose the parser is out of the question. Adding a 
>> function like you did for the mysql client isn't a possibility for 
>> most clients either, since they are data access abstraction layers, 
>> having to conform to a specific API. Sure in most cases you can come 
>> up with a workaround. But it will be a huge mess.
>>>  
>>>> If you don't like to integrate the parser, perhaps a good solution 
>>>> is to select it with a local setting, so you could do:
>>>>  SET LOCAL query_parser=PARSER_SPASQL;
>>>>  SELECT ?s WHERE { ?s <foo.bar> "hibbyhop" };
>>>>     
>>>
>>> The problem with that is getting back. SET (LOCAL|GLOBAL) is parsed
>>> SQL parser. Each parser would have to implement its own way of getting
>>> back or you'd lose the flexibility of being able to intersperse
>>> queries.
>>>   
>> Each parser would need to implement the SET command, I don't see a 
>> problem there. Implementing a specific command `PARSER SPASQL`, could 
>> also work. Though not to add yet another non ANSI keyword, something 
>> like `SET PARSER SPASQL` would be a better alternative. That way you 
>> don't need to implement SET in each parser. Though I think having set 
>> in each parser would be a good idea, since is it the way to control 
>> how MySQL acts and you want to be able to do that no matter what type 
>> of query your sending.
>>> == XQuery stuff ==
>>>  
>>>> I don't see the use of just mapping the relational data to XQuery 
>>>> like you did in the example (and how do you see a join in that?). 
>>>> XQuery is useful if you have XML as a string in a column. If you 
>>>> need it in XML form going back and forth from XML to relational 
>>>> data can be a drag. But if you want to use relational data most of 
>>>> the time, switching to a pure XMLDB isn't an option either. Hence 
>>>> the DB2 solution.
>>>>     
>>>
>>> Generally, the appeal of querying relational data as XQuery is less
>>> appealing to the SQL-heads than the XQuery-heads. The XQuery vision,
>>> like the SPARQL vision, is that there is a unified data model that all
>>> data is projected into. At that point, you can do queries that join
>>> data in HTML documents, spreadsheets, databases, tea leaves and goat
>>> entrails (still in the research phase).
>>>   
>> That could be nice, but it doesn't exist yet. I'm not to interested 
>> in standards in research phase, because it will take years and years 
>> before we will see something a standard. Currently XQuery is used to 
>> query XML. It uses doc() to select the source, though MySQL doesn't 
>> work with document based storage engines, they work with tables, 
>> columns and rows. However in a cell there might be XML data, 
>> therefore integrating XQuery in SQL is far more logical.
>>> I think the preference for XQuery as the outside language stems mostly
>>> from the use of URIs as global identifiers (while MySQL allows you to
>>> specify a database in any field spec, it's not a global identifier).
>>> However, I'm not hoping to convince folks here to switch to XQuery or
>>> SPARQL, just that there are people who want to query MySQL data that
>>> way.
>>>   
>> In this scenario what exactly would be the advantage of adding XQuery 
>> to MySQL. I could just use Berkeley DB XML if I want to exclusively 
>> choose for XQuery.
>>>  
>>>> I guess you could imagine each row to be a node and do 
>>>>
> doc(mytable)[/acolumn/text()='banana']/mycolumn/products//product[@type='casual'] 
>>>> to get to a pure XQuery solution. I personally like the solution of 
>>>> DB2 better though. Or do you have another option?
>>>>     
>>>
>>> The way I envisioned this mapping is that elements represent
>>> attributes of tuple, so nested attributes were attributes of
>>> attributes, and therefore joins. I'm not swapped in on the industry
>>> practice in XQuery/relational mappings, though I'll pester the Oracle
>>> folks in the XQuery WG for more info.
>>>   
>> If you map relational data that way, content of the table will be 
>> seen just as that. You would want to use XQuery if you're dealing 
>> with XML, which will reside in a specific cell. If you would 
>> implement it this way, you still can query that XML, loosing the 
>> whole purpose.
>>
>> I'm not aware that Oracle has XQuery support in their relational 
>> database. As far as I know they only support that in a specific 
>> Oracle XML DB and in Berkeley XML DB. To my knowledge, the only mayor 
>> DB that supports this is IBM DB2 and they don't do relational mapping 
>> in XQuery. Instead they do an SQL query with and import of XML 
>> residing in a column to be used in the XQuery statement. However, if 
>> you want a pure XQuery solution you will need mapping.
>>
>> I like DB2s solution much better and therefore I think that allowing 
>> additional parsers to be integrated in the current parser would be 
>> the best solution. Not only do you not have to do relational mapping 
>> where it doesn't belong, it also solves the API problem discussed 
>> earlier.
>>> If you asked the same question about SPARQL, I could do a better job
>>> answering your question as that's the sort of code I write.
>>>   
>> I don't know enough about SPARQL to ask intelligent questions about it.
>>
>> Best regards,
>> Arnold
>>