[Deploying Sakai] Lessons on Oracle possible issue

[Hedrick Charles]

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/20140501/4e7eddfb/attachment.html