[Deploying Sakai] Lessons on Oracle possible issue

[Steve Swinsburg]

If the data you want to store as a serialised structure is fine to do so then go ahead. What I was getting at was to avoid the situation where its stored that way just because it can be, and then having future functionality being limited because of it.

The XML functions in MySQL are pretty basic. You can extract values given an XPath and update an XML fragment. But thats it.

The XML functions in Oracle are massively powerful. Manipulating XML on the database means you can return a result set with your extracted columns on the fly, return whole nodes, check if elements exist, as well as update and delete and create XML structures.

ie a simple example returning a set of columns that all come from the XML:

SELECT
  EXTRACTVALUE(ac.xml, '/content/@title') assignment_title
, EXTRACTVALUE(ac.xml, '/content/@datecreated') date_created
, EXTRACTVALUE(ac.xml, '/*/properties/property[@name=''CHEF:creator'']/@value') assignment_creator
FROM assignment_content ac

For more complex data you can use a materialised view to have the XML data relationalised for you on commit. You then get the benefits of the flattened storage of the XML as well as the relationalised data for DML statements. This is what I was referring to when I said its not yet available for JSON. If that doesn’t matter for this purpose then its moot.

cheers,
Steve




On 2 May 2014, at 1:24 pm, Hedrick Charles <hedrick at rutgers.edu> wrote:

> At least in Mysql, the XML functions are intended to let you extract a single value. I don’t see any easy way to take a whole XML structure and have the query return separate fields generated from it. Either you return the whole thing as text, or you use a separate extract call for each field. The latter would almost certainly do a separate parse of the XML for each field. Neither XML nor JSON functions let you parse the string once and get a binary object from which you can get fields. Nor would this be a good tradeoff anyway. You’d like to keep down the overhead on the server, doing as much in the front end as possible. The json attributes are not used in WHERE clauses. (I wouldn’t do that.) As it happens, the standard json libraries produce exactly the data structure I need.
> 
> Oracle has more XML functionality, but I still very much doubt that parsing XML on the server side makes any sense.
> 
> The attributes that are stored in json are almost always used. Almost all activity in Lesson is in ShowPageProducer, and all the those attributes are needed when an item is displayed, so there’s nothing lost by having to parse them all. No other producers are used often enough to have a performance impact. The same is true of several of the fields that are currently CLOBs. E.g. the description, which is always output when the item is shown. Thus at least some of those attributes could be moved into the json without causing any trouble. There would be some unusual situations when it isn’t used, but not enough to affect performance.
> 
> The only exception I can think of is if you had lots of different items that are displayed only to some groups. The code will currently parse the attributes for all of them, even though some won’t be displayed for a given user. (The group list isn’t in json.) That could easily be handled by using lazy parsing. Basically if the item is shown, all attributes are used, and if it’s not, none of them are. So lazy parsing of the whole json object makes sense. But I think that’s an unnecessary optimization.
> 
> I thought about this pretty carefully with csev. 
> 
> 
> On May 1, 2014, at 7:27:39 PM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
> 
>> Ah yeah I've been doing PL/SQL for too long where it is 32767.
>> 
>> So either use VARCHAR2 for relatively small amounts of data, or CLOB for large. 
>> Don't use LONG for anything. it is restrictive and Oracle have been advising against it since 8i (10 years ago).
>> 
>> The next query I have is in regards to storing the data as JSON. Whilst that might be reasonable if its data that is always retrieved in full, then deserialised and examined, XML may be better choice as you can use the native database XML functions on both Oracle and MySQL for searching an extracting without returning the whole chunk of data, which would make it more future proof.
>> 
>> JSON is more of a data representation format than a format suitable for storage in a relational database, unless the database has native support for handling it. JSON handling in Oracle may be a future part of 12c. MySQL seem to be adding support as well but I wouldn't be relying on it. The XML functions are solid.
>> 
>> cheers,
>> Steve
>> 
>> 
>> 
>> 
>> On Fri, May 2, 2014 at 7:32 AM, Hedrick Charles <hedrick at rutgers.edu> wrote:
>> clob is a result of the Hibernate change in 2.9. Things declared text were changed to materialized_clob.
>> 
>> Oracle claims the maximum for varchar2 is 4000. http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm#i287903. This changes to 32K in Oracle 12c. I don’t think we want to limit Lessons to requiring 12c.
>> 
>> On May 1, 2014, at 4:47:44 PM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
>> 
>>> If you are going to use a LONG datatype, you might as well just declare it as VARCHAR2(32767) as that is what you are limited to. If you are storing it as JSON and are unsure of the data length, I’d be using a CLOB. 
>> 
>> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20140502/783586e5/attachment.html