Grouper Developers Forum

[Chris Hyzer <>]

Grouper-dev,

I ran the quick start (and it was smooth and well done).  Then I switched it 
over to Oracle (per quickstart), and switched the soruces.xml file to hit the 
default grouper hibernate DB (per quickstart), and loaded the subject and 
subjectattribute tables (per quickstart), and everything went smoothly until 
I couldn't search on any of the data I added.  So I tried to debug, and I got 
to the class:

edu.internet2.middleware.subject.provider.JDBCSourceAdapter, and the source 
wasn't there.  That's ok, it's the subject API in an external jar.  But I 
didn't see a source download for that on

https://wiki.internet2.edu/confluence/display/i2miCommon/Subject+API

I see the source individually on viewcvs.cgi, but its not the most convenient 
thing.  So:

Question 1: Can we distribute the subject source inside of the subject.jar?  
I find that it helps a lot when debugging things, but if there is a reason 
not to, if there could be a zip to download on the subject api website (did I 
have the right URL?), or in the grouper download (so its in sync) that would 
be good.  Also, if debug info is not included when compiling, I think it 
would be useful to do this.

Then, I got the source from viewcvs.cgi, and I debugged the class, and I 
noticed that it not only swallows the SQL exception, but only logs it in 
debug level

    protected ResultSet getSqlResults(String searchValue,
...
        } catch (SQLException ex) {
            log.debug("SQLException occurred: " + ex.getMessage(), ex);
        }
...

Question 2: What is the standard for exception handling?  Can we rethrow this 
sql exception, it could be wrapped in a RuntimeException or subclass, (and 
maybe look for other cases of this if it is not the standard)?  I think it is 
good when things don't work that it is brought to the attention of users.  If 
there's a reason to not rethrow, then at least can we log as error?

The reason things didn't work is that the sources.xml default queries do not 
work in Oracle.  I think the only reason is the "AS" on the left join 
subqueries... I took them out (three as'es before the alias lfnamet, 
loginidt, desct) and it worked in Oracle and HSQLDB (not sure about other 
supported DB's).  Query is below.

Question 3: Think we can make this (or something that is more DB generic) the 
default in the sources.xml (and change all the others to remove the three 
as'es?  And/or mention this in the quickstart (to change the queries if 
changing the DB type)

So now things work and I can search the subjects.  However, when I get 
results, I see the id's of the people:

Showing 1 to 50 of 961 items
Click on a subject to display its summary
10025786
56914520
20501096
10047006

At penn we do have an alpha ID (e.g. mchyzer), but people can change this 
(e.g. if they get married), so the ID we try to use in apps and data is the 
numeric one.  Is this not how the subject API is designed?

Question 4: Can we show name as well as ID on the subject search results 
screen?

I did a search for everyone, and I think the paging is being done in the app 
server and not in the DB.  I got an out of heap space error searching on 112k 
records.

Question 5: Is paging in the DB not feasible for the source.xml jdbc 
searches?  I know we need a different query for each DB type (like Hibernate 
does it, and we could support certain types of drivers perhaps or DB types).  
If we don't want to go down that route, maybe we could get a count of records 
in a DB independent way, and if it is over a certain amount (5000?) we could 
not select the records, and tell the user how many there are, and ask them to 
narrow their search.

Kind regards,
Chris

Ps. Here is the sources.xml query that works in Oracle and HSQLDB:

select
   subject.subjectid as id, subject.name as name,
   lfnamet.lfname as lfname, loginidt.loginid as loginid,
   desct.description as description
from
   subject
   left join (select subjectid, value as lfname from subjectattribute
     where name='name') lfnamet
     on subject.subjectid=lfnamet.subjectid
   left join (select subjectid, value as loginid from subjectattribute
     where name='loginid') loginidt
     on subject.subjectid=loginidt.subjectid
   left join (select subjectid, value as description from subjectattribute
      where name='description') desct
     on subject.subjectid=desct.subjectid
where
   loginidt.loginid = ?;