Sakai External Database MOTHRA Data

MOTHRA is the central campus repository for information about campus users and accounts. It collects data from other source systems on campus and merges information with the intent of resolving information about each user. Sakai will use information from the MOTHRA system to refernce both personal information about users and as source of unique identification of users.

MOTHRA Data Architecture

The following MOTHRA database objects are necessary when collecting information about campus accounts:

For more information about these Mothra tables and their data, please see https://confluence.ucdavis.edu/confluence/download/attachments/6851892/cMothraTables.doc.

Requirements

• Availability: The MOTHRA data used by Sakai must be available on a 24x7 basis.
• Refresh: Given that user information about users changes rarely, a nightly feed should be initially sufficient.

Design

The MOTHRA feed will use a rather standard table and pl/sql feed approach built for MyUCDavis several years ago. The procedure can be run nightly to update the information in the local table with the latest data from MOTHRA. The local table will then be available at all times for querying.

Test users

The sakai team had a requirement to populate their store of people with some fake test users. To this end, a set of loops was added to the end of the MOTHRA person stored procedure. These inserts used specially identifiable id fields to ensure no overlap with real users.

LDAP

Eventually, it may be possible to move away from MOTHRA and instead get this type of information from the Campus LDAP directory. However, before this can be done, Sakai must be upgraded to handle multiple sources of id information from each source system.

Implementation

The initial implementation of a Mothra Feed used a database link to the MyUCDavis CourseManagement database to do a nightly truncate a fill of person data. Since then, the feed procedure has been enhanced to perform a full feed from MOTHRA itself. The feed procedure's algorithm is as follows:

for each user in MOTHRA loop
   attempt to update the local row with the current mothra data, set activity date to new date
   if update fails, insert a new row for the user,  set activity date to new date
end loop
delete all rows with an activity date earlier than the new date

Note: The actual procedure performs some checking on null pidms because table updates will update the pidm column to a blank space instead of leaving it as null.