Grouper Developers Forum

[Tom Barton <>]

Dave Donnelly wrote:
> Signet uses a "database-within-a-database" to act as a
> subject provider. One of the Signet utilities, SubjectFileLoader,
> reads a flatfile and loads subject information into two tables
> (subject and subjectattribute). These tables are then accessed
> by a JDBCSourceAdapter to provide new Subjects whenever Signet
> is creating proxies and/or assignments.
>
> Neither of these tables are "official" Signet tables, but are,
> I believe, legacy from a reference implementation of SubjectAPI
> providing Subjects to Signet. I couldn't determine how to rewrite
> SubjectFileLoader to conform to the new way SubjectAPI retrieves
> Subjects because of the (new?) requirement for a single attribute
> possibly having multiple values. Instead, I made the current JDBC
> classes extend-able, sub-classed it, and overrode the methods that
> affect Signet. The subclass (SignetJDBCSourceAdapter) is contained
> in the Signet project and does not affect the Subject project.

I guess your work is done. But the the 0.2.1 JDBCSourceAdapter reads 
Minh's database schema just fine, so I wonder if there was any need at 
all. Oh well.

Just for the record, here's the essential SQL that figures in 
sources.xml for that particular subject source. The where clause changes 
for each subject search/selection method, but the rest is the same.

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') as lfnamet
    on subject.subjectid=lfnamet.subjectid
  left join (select subjectid, value as loginid from subjectattribute
    where name='loginid') as loginidt
    on subject.subjectid=loginidt.subjectid
  left join (select subjectid, value as description from subjectattribute
     where name='description') as desct
    on subject.subjectid=desct.subjectid
where
  (lower(name) like '%%TERM%%')
  or (lower(lfnamet.lfname) like '%%TERM%%')
  or (lower(loginidt.loginid) like '%%TERM%%')
  or (lower(desct.description) like '%%TERM%%')