Grouper Users - Open Discussion List

[blair christensen <>]

On 2004.12.21 11:20, Mark McLaren wrote:
> I have configured Grouper 0.5 to use an Oracle 8 database rather than
> HSQL.  

Great!

> I ran into a few issues during this process that I thought
> might be useful to share (note: I'm not an Oracle expert).

Unfortunately expected.

> * First creating the tables.
> 
> VARCHARs are deprecated in Oracle so I modified these to become
> VARCHAR2s.  Oracle insists on the length of VARCHAR2s being explicitly
> defined.  Starting with the contents of hsqldb.sql I converted all
> VARCHAR to VARCHAR2(255).  In due course each column will probably
> have a specifically chosen length but in the absence of this
> information this broad stroke will do for now.

The VARCHARs were initially VARCHAR(255) but then I changed them due to
uncertainly about what level of constraint should be applied to the
various fields.  We probably should have explicit sizes but I wanted to
get a better feel for size requirements first.  I'm worried that 255
will, or could, be too small for a number of them.  

I'm going to have to start looking at Oracle documentation it seems.

> I changed the BIGINT to become a NUMBER(19).

We probably don't even need those to be numeric.

> The field "comment" in the grouper_group table was renamed because
> "comment" is an Oracle reserved word.  Changing this field name
> results in requiring changes to Grouper.hbm.xml and GrouperGroup.java.

Of course.  I knew we'd run into at least one conflict like this.  This
will need to change.

> The next problem I received was "ORA-01450: maximum key length (758)
> exceeded" error.  To a certain extent this is a database instance
> specific db_block_size configuration issue. In our case (with a 2K
> block size) it meant that I had to reduce the length of certain
> combined constrained columns so that the sum of the lengths would not
> exceed 758.  Notably these combinations:
> 
> groupKey, groupField, memberKey, via
> memberKey, memberID, subjectID, subjectTypeID  
> 
> So I arbitrarily set these lengths for these columns to 100 instead of
> the 255 that I had initially chosen.

Ouch.  Should I assume that anyone using a remotely default Oracle
configuration will run into this problem?

> I understand that there are several options WRT connection pooling
> including C3P0, Proxool and Apache DBCP.  My instinct would tell me to
> go for Apache DBCP but I have no technically sound basis for doing so.
> 
> The addition of using Apache DBCP required additional java libraries
> to be installed (commons-dbcp and commons-pool).

I think we'll be using, or at least defaulting, to Apache DBCP for
future releases.

> At this point the "ant test" is failing thus:
> 
> When I looked at line 99 of TestConfigAndSchema.java it contained:
> 
> field = "admins:ADMIN:ADMIN:TRUE";
> Assert.assertTrue( field.equals( fields.get(idx).toString() ) );
> 
> I modifed it to output the value of "fields.get(idx).toString()" and I get: 
> extension:VIEW:ADMIN:FALSE

Of course.  Those tests are rather fragile and I ran out of time to
replace them before the 0.5 release.  I'll add that to my list of
tasks.

> I had noticed that in response to grouperq queries the order of the
> data that Oracle returns is not the same order that HSQL returns data
> in.  I spoke to Gary Brown about this and we were thinking that this
> could be related to the above error.

Actually, I'm not sure that grouperq currently provides any reliable
ordering across databases.

> I hope this is useful feedback.

Yes, very!

What version of Oracle are you using?  And could I get the database
schema file you used?  And now it looks like I need to get myself an
Oracle database to start testing against.  

Thanks,
blair.