Grouper Developers Forum

[Dave Donnelly <>]

Ah, but the fly in my ointment is Signet's requirement for
multiple attribute values. For instance, we have a Person
with two login IDs: Jean Poole as 'jpoole' and 'demo'. The
query, as you specified, will return two records (see
Example, below. The actual contents of tables 'subject' and
'subjectattribute' appear below the Example).

Returning two records causes JDBCSourceAdapter to throw a
SubjectNotUniqueException. Again, I would need to subclass
JDBCSourceAdapter to override method createUniqueSubject(),
or write my own class from scratch (duplicating 85% of the
existing code), to handle the situation.

The changes I made try to uphold the notion of a unique Subject
while still permitting multiple attribute values. Of the 3 public
methods provided by JDBCSourceAdapter, getSubject() and
getSubjectByIdentifier() both constrain the results to a single
record (throwing exceptions otherwise). My only other option
would then be to always call search() and write a bunch of _application_
code (not SubjectAPI code) to sift through the results and
correctly build Subjects with multiple attribute values.

Granted, my solution within Signet is not elegant. I must
issue two queries - one for the matching Subject, another for
it's attributes. But, by extending JDBCSourceAdapter and only
overriding one method - loadAttributes(ResultSet) - I try to
maintain an object-oriented approach and keep SubjectAPI
details within a clearly defined Signet-extension-of-SubjectAPI
boundary.


Example:
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 '%poo%')
  or (lower(lfnamet.lfname) like '%poo%')
  or (lower(loginidt.loginid) like '%poo%')
  or (lower(desct.description) like '%poo%') 

id       name         lfname       loginid     
description                          
-------  -----------  -----------  ----------  
-------------------------------------
SD00007  Poole, Jean  Poole, Jean  demo        Principal Investigator, 
Bio-X project
SD00007  Poole, Jean  Poole, Jean  jpoole      Principal Investigator, 
Bio-X project

2 record(s) selected [Fetch MetaData: 31/ms] [Fetch Data: 0/ms]



select * from subject where subjectid = 'SD00007'

subjecttypeid     subjectid     name         description     
displayid     modifydatetime        
----------------  ------------  -----------  --------------  
------------  ----------------------
person            SD00007       Poole, Jean  n/a             
n/a           11/20/2006 12:00:00 AM

1 record(s) selected [Fetch MetaData: 16/ms] [Fetch Data: 0/ms]

[Executed: 11/21/06 3:45:59 PM PST ] [Execution: 0/ms]

>[SQL] Script lines: 2-2 ----------------------------
select * from subjectattribute where subjectid = 'SD00007' 

subjecttypeid     subjectid     name         instance     
value                                  
searchvalue                            modifydatetime        
----------------  ------------  -----------  -----------  
-------------------------------------  
-------------------------------------  ----------------------
person            SD00007       all          1            
all                                    
all                                    11/20/2006 12:00:00 AM
person            SD00007       description  1            Principal 
Investigator, Bio-X project  principal investigator, bio-x project  
11/20/2006 12:00:00 AM
person            SD00007       loginid      2            
demo                                   
demo                                   11/20/2006 12:00:00 AM
person            SD00007       loginid      1            
jpoole                                 
jpoole                                 11/20/2006 12:00:00 AM
person            SD00007       name         1            Poole, 
Jean                            poole jean                             
11/20/2006 12:00:00 AM

5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]



Tom Barton wrote:
> 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%%')