Member search and sort columns

In order to allow searches for members in a group and sorting of members in a group without having to resolve subjects, we can add additional columns to the grouper_members table.  This design is expanding on the following document from a few months ago:  https://spaces.at.internet2.edu/display/Grouper/Subject+improvements+in+Grouper

• name - This would contain subject.getName()
• description - This would contain subject.getDescription().
• sort_string0
• sort_string1
• sort_string2
• sort_string3
• sort_string4
• search_string0
• search_string1
• search_string2
• search_string3
• search_string4

Search strings will not be indexed and they will allow up to 2K of data.  Sort strings will be indexed and will also allow up to 2K of data.  Each sort and search string would be attributes configured in the sources.xml file.  They would be configured for each source.  Keeping the attributes consistent for each index (for people sources at least) would make the searching/sorting more useful.  If there's more than 2K of data, it will simply be truncated rather than causing an error.  Each source will require at least one search string and one sort string, otherwise there will be an error during startup.

     <init-param>
       <param-name>sortAttribute0</param-name>
       <param-value>LOGINID</param-value>
     </init-param>
     <init-param>
       <param-name>sortAttribute1</param-name>
       <param-value>LFNAME</param-value>
     </init-param>
     <init-param>
       <param-name>searchAttribute0</param-name>
       <param-value>LOGINID</param-value>
     </init-param>
     <init-param>
       <param-name>searchAttribute1</param-name>
       <param-value>LFNAME</param-value>
     </init-param>
     <init-param>
       <param-name>searchAttribute2</param-name>
       <param-value>EMAIL</param-value>
     </init-param>

To allow for searching/sorting through all sources, the UIs can have configuration options that map each search/sort index with a display name.  Also, if we want searching/sorting for a single source, the sources.xml file can have additional parameters for each source:  sortAttributeDisplayName0, sortAttributeDisplayName1, searchAttributeDisplayName0, searchAttributeDisplayName1, searchAttributeDisplayName2, etc...

The data in these new columns would get updated when a subject is resolved by id or identifier or when a new member row is created.  Also, group names would need to be updated when groups are renamed.  When upgrading to 2.0, member rows for people can be updated by running USDU.  We can get member rows for groups updated as part of the DDL upgrade if needed.

The search columns would contain lowercase characters and searches would be substring searches of each word in the string. So a search for "John Doe" on search_string0 would be ... where search_string0 like '%john%' and search_string0 like '%doe%'.

Note that since the sources.xml file supports virtual attributes, you also have the option of having multiple attributes within one search index (comma separated) and then just give the user a single search option. We would allow subjects to have "internal" attributes so that these comma-separated virtual attributes are not included in Subject.getAttributeValue() but rather new methods like Subject.getInternalAttributeValue(). You would be able to specify which attributes are internal attributes in the sources.xml file.

<internal-attribute>internalAttribute0</internal-attribute>
<internal-attribute>internalAttribute1</internal-attribute>
<internal-attribute>internalAttribute2</internal-attribute>

The default search (search_string0) in the default config will have the following:

We can restrict users that are allowed to search/sort on each column using groups. By default, everybody has access?

sort.string0.allowOnlyGroup = stem:all_employees
sort.string1.allowOnlyGroup = stem:all_employees
sort.string2.wheelOnly = true
...

search.string0.allowOnlyGroup = stem:all_employees
search.string1.allowOnlyGroup = stem:all_employees
search.string2.wheelOnly = true

We will also have config options to specify the default indexes to use for searching and sorting if one is not specified.

search.defaultIndexOrder=2,1,0
sort.defaultIndexOrder=2,1,0

The index that gets used by default is based on what you have access to. So for searching, if you have access to the first one (searchString2), then that would be used. Otherwise, try the next and so forth. searchString0 would be the most public.

Finally, API methods that implement the new behavior can be overloaded with a few more parameters:  sortCol (or just use queryOptions), searchCol, searchString. Also for default searching and sorting: useDefaultSearchCol, useDefaultSortCol

So here's one way the data may be stored assuming you have 2 people sources.

So this would allow you to ....

1. Sort all people in a group by first name, last name, or user name.
2. Search for all people in a group using their name, department, or affiliation.  You can search for people in OIT without getting all the people that have "oit" in their name.
3. Search for all staff in a group and sort them by last name.