Grouper Users - Open Discussion List

[Mark McLaren <>]

I have configured Grouper 0.5 to use an Oracle 8 database rather than
HSQL.  I ran into a few issues during this process that I thought
might be useful to share (note: I'm not an Oracle expert).

* 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.

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

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.

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.

I then got "ORA-02264: name already used by an existing constraint" so
I modified the name of the second instance of the constraint called
"uniq_sid_stid".

I also removed the creation of the index "idx_gl_gk_gf_mk_via" since I
received the error "No need to have as ORA-01408: such column list
already indexed".  I presume Oracle already created this as a result
of the constraint creation.

Following these changes I was able to execute the init.sql data
creation script with no further changes necessary.

* Modifying the Grouper configuration

I added the Oracle JDBC driver jar to the lib directory (classes12.jar).  
I modified hibernate.properties in order to use Oracle rather than HSQL.

hibernate.dialect                     = net.sf.hibernate.dialect.OracleDialect
hibernate.connection.driver_class     = oracle.jdbc.driver.OracleDriver

# NOTE You will probably want to change this to use an absolute path
hibernate.connection.url              =
jdbc:oracle:thin:@<somedatabaseURL>:1521:<someSchema>
hibernate.connection.username         = <username>
hibernate.connection.password         = <password>

I'm not altogether sure about the necessary configuration for
transactions and connection pooling but I also added the following:

hibernate.transaction.factory_class =
net.sf.hibernate.transaction.JDBCTransactionFactory

###################################
### Apache DBCP Connection Pool ###
###################################

# connection pool

hibernate.dbcp.maxActive=100
hibernate.dbcp.whenExhaustedAction=1
hibernate.dbcp.maxWait=120000
hibernate.dbcp.maxIdle=10

## prepared statement cache

hibernate.dbcp.ps.maxActive=100
hibernate.dbcp.ps.whenExhaustedAction=1
hibernate.dbcp.ps.maxWait=120000
hibernate.dbcp.ps.maxIdle=100

# optional query to validate pooled connections:

hibernate.dbcp.validationQuery=select 1 from dual

hibernate.connection.provider_class=net.sf.hibernate.connection.DBCPConnectionProvider

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).

For test purposes I also modified
GROUPER_HOME/contrib/load/csv2subject/csv2subject.properties so that I
could populate the database with some test subjects.

I was able to execute the csv2group which directly exercises the
Grouper API to create some stems and groups in the database.

I was able to execute grouperq to query the database.

At this point the "ant test" is failing thus:

test-run:
    [junit] .................
    [junit] Time: 3.218
    [junit] 
    [junit] OK (17 tests)
    [junit] 
    [junit] F...
    [junit] Time: 3.377
    [junit] There was 1 failure:
    [junit] 1) 
testGetGrouperFields(test.edu.internet2.middleware.grouper.TestConfigAndSchema)junit.framework.AssertionFailedError
    [junit]     at
test.edu.internet2.middleware.grouper.TestConfigAndSchema.testGetGrouperFields(TestConfigAndSchema.java:99)
    [junit]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [junit]     at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    [junit]     at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    [junit] 
    [junit] FAILURES!!!
    [junit] Tests run: 5,  Failures: 1,  Errors: 0
    [junit] 

BUILD FAILED

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

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.

For example:

With HSQL

>java grouperq -g comp:lang:java

name: comp:lang:java
type: base
stem: comp:lang
extn: java
groupID: f739cfc6-6a29-4ae8-94e5-1b0902314abb
createSubjectID: GrouperSystem
createSubjectTypeID: person
createTime: 1103013972781
createTimePretty: Tue Dec 14 08:46:12 GMT 2004
modifySubjectID: GrouperSystem
modifySubjectTypeID: person
modifyTime: 1103013973859
modifyTimePretty: Tue Dec 14 08:46:13 GMT 2004
immediateMember: wythe (person)
immediateMember: witherspoon (person)
effectiveMember: witherspoon (person) via comp:lang:java:announce (base)
immediateMember: wolcott (person)
effectiveMember: adams (person) via comp:lang:java:announce (base)
effectiveMember: clymer (person) via comp:lang:java:announce (base)
immediateMember: comp:lang:java:announce (base)
effectiveMember: chase (person) via comp:lang:java:announce (base)
effectiveMember: clark (person) via comp:lang:java:announce (base)
immediateMember: wilson (person)
effectiveMember: wilson (person) via comp:lang:java:announce (base)

With Oracle:

>java grouperq -g comp:lang:java

name: comp:lang:java
type: base
stem: comp:lang
extn: java
groupID: 9ccd1758-f909-4084-b9d8-d7e5f007b57a
createSubjectID: GrouperSystem
createSubjectTypeID: person
createTime: 1103539891545
createTimePretty: Mon Dec 20 10:51:31 GMT 2004
modifySubjectID: GrouperSystem
modifySubjectTypeID: person
modifyTime: 1103539903777
modifyTimePretty: Mon Dec 20 10:51:43 GMT 2004
immediateMember: comp:lang:java:announce (base)
immediateMember: wythe (person)
effectiveMember: wilson (person) via comp:lang:java:announce (base)
immediateMember: wilson (person)
effectiveMember: clark (person) via comp:lang:java:announce (base)
immediateMember: wolcott (person)
effectiveMember: adams (person) via comp:lang:java:announce (base)
effectiveMember: chase (person) via comp:lang:java:announce (base)
effectiveMember: clymer (person) via comp:lang:java:announce (base)
effectiveMember: witherspoon (person) via comp:lang:java:announce (base)
immediateMember: witherspoon (person)

I hope this is useful feedback.

Regards,

Mark