Skip to Content.
Sympa Menu

grouper-dev - new jdbc source adapter with better searching

Subject: Grouper Developers Forum

List archive

new jdbc source adapter with better searching

Chronological Thread 
  • From: Chris Hyzer <>
  • To: "" <>
  • Subject: new jdbc source adapter with better searching
  • Date: Fri, 22 May 2009 15:41:31 -0400
  • Accept-language: en-US
  • Acceptlanguage: en-US


At Penn we have 500k people in our person source, and it is hard to find
people in the UI with common names.

So I made a new jdbc source adapter that will split search strings by
whitespace and "AND" all terms together in the query.

Let me know if you are interested in this and if it seems like it would fit
your needs. Note: we already edited the subject API for Grouper 1.5, and
never branched, so this adapter is currently in grouper. When I merge to the
1.5 branch, I will put it in the subject.jar.

Right now if I search for "john smith", and there is a user "john m. smith",
it will not find the user.
Make a jdbc source adapter that can find the user

This is done. To implement:

1. provide a table or view which has the subject information including the
search col (lowered). This will have better performance if you have a job
which dumps the data to one table periodically, and not just joining a bunch
of tables and calling to_lower()

2. configure in the sources.xml. Here is an example:

CREATE TABLE `person_source_v` (
`penn_id` varchar(50) NOT NULL,
`name` varchar(50) default NULL,
`description` varchar(50) default NULL,
`pennname` varchar(50) default NULL,
`description_lower` varchar(50) default NULL,
PRIMARY KEY (`penn_id`)
insert into
values ('10021368','Chris Hyzer','Chris Hyzer','mchyzer','chris hyzer,
mchyzer, 10021368'),('10039438','John Smith','John Smith','jsmith','john
smith, jsmith, 10039438');
<name>Penn person</name>


<!-- search col where general searches take place, lower case -->
<!-- optional col if you want the search results sorted in the API
(note, UI might override) -->
<!-- col which identifies the row, perhaps not subjectId -->
<!-- now you can count up from 0 to N of attributes for various cols -->


That is it, now you can search for "smith jo" and it will find john smith,
but will not return any smiths without "jo" in there somewhere. Note at Penn
we have the description field end in (active) for active employees, and
(NOT_ACTIVE). So if I search for "John Smith (active)" it will only return
active john smiths, including "john m. smith". We also have the data source
in there. So I could search for "(active) John Smith staf" and it would find
active john smiths who's primary affiliation with the university is
'employee'. We could put alternate names in the search col, or alternate
affiliations, etc. We load that table nightly with plsql so we can customize
as needed.

- Case insensitive column names (can be very confusing in the default source
- More efficient unmodifiable attribute maps and set values
- Supports multiple subjectIdentifier cols


  • new jdbc source adapter with better searching, Chris Hyzer, 05/22/2009

Archive powered by MHonArc 2.6.16.

Top of Page