Provider Data Model

Owned by Former user (Deleted)
Last updated: Jan 08, 2009 by Former user (Deleted)Version comment
6 min read

See MySQL Notes for info on starting and stopping the database.

Test Application - Members Tool

This workshop isn't about UI design or web presentation systems. However, some kind of UI application is needed to test integration with enterprise information about a use. I envision a simple application that displays information related to groups and users. The tool will be designed to drive the providers that the workshop participants create.

The Members Tool is designed to provide a minimal hook that drives Sakai into use User and Group Providers. The Home page has links to show information about a person or a site. These links are hardwared against information known a priori.

The following users are defined in the test system:

  • markjnorton
  • duffygillman
  • stevegithens
  • brigidcassidy
  • charlesseverance
  • kristolhancock
  • glenngolden

The following sites (authz groups) are defined:

  • physics-101
  • music-200
  • calc-355

Two roles are defined for each group: instructor and student.

Membership in the test sites is defined externally. It is hardwared in the StubbedGroupProvider and StubbedUserProvider implementations. In the student exercise, they will be defined in the SIS database.

User Data

A table of all known users will be created with columns for information about that user.

The following fields will be included in USER_REGISTRY table:

  • Eid - user's enterprise id string.
  • First Name - user's first name.
  • Last Name - user's last name.
  • Email - user's email address.

Note that display and sort names are computed from this information.

The following users will be included in this data table:

  • markjnorton
  • duffygillman
  • stevegithens
  • brigidcassidy
  • charlesseverance
  • kristolhancock
  • glenngolden
  • lancespeelmon - database only.
  • zachthomas - database only.
  • mikeosterman - database only.

The USER_REGISTRY schema:

CREATE TABLE USER_REGISTRY (
Eid VARCHAR(256) NOT NULL,
First VARCHAR(256) NOT NULL,
Last VARCHAR(256) NOT NULL,
Email VARCHAR(256),
PRIMARY KEY(Eid)
);

To insert:

INSERT INTO USER_REGISTRY (Eid, First, Last, Email) values 
("ext-foo", "First", "Last", "name@sakaiproject.org");

The following data is now present:

mysql> select * from USER_REGISTRY;
+------------------+---------+-----------+---------------------------------+
| Eid              | First   | Last      | Email                           |
+------------------+---------+-----------+---------------------------------+
| brigidcassidy    | Brigid  | Cassidy   | abrigidcassidy@sakaiproject.org |
| charlesseverance | Charles | Severance | csev@sakaiproject.org           |
| duffygillman     | Duffy   | Gillman   | duffygillman@sakaiproject.org   |
| glenngolden      | Glenn   | Golden    | ggolden@sakaiproject.org        |
| kristolhancock   | Kristol | Hancock   | kristol@sakaiproject.org        |
| lancespeelmon    | Lance   | Speelmon  | lance@sakaiproject.org          |
| markjnorton      | Mark    | Norton    | markjnorton@sakaiproject.org    |
| mikeosterman     | Mike    | Osterman  | osterman@sakaiproject.org       |
| stevegithens     | Steve   | Githens   | stevegithens@sakaiproject.org   |
| zachthomas       | Zachary | Thomans   | zach@sakaiproject.org           |
+------------------+---------+-----------+---------------------------------+
10 rows in set (0.00 sec)

Useful queries for getting user information:

Type

SQL Expression

User Exists?

SELECT * FROM sakai_sis.user_registry where Eid='userId'

Get User Data

SELECT First,Last,Email FROM sakai_sis.user_registry where Eid='eid'

Get Users Data

SELECT Eid,First,Last,Email FROM sakai_sis.user_registry

Find User by Email

SELECT Eid,First,Last,Email FROM sakai_sis.user_registry where Email='email'

Group Data

A table of all known groups (sites) will be created with columns for information about these groups:

The following fields will be included in the GROUP_REGISTRY table

  • GroupId - group's external id string.
  • GroupName - group name.
  • GroupType - "course" or "section"

In practice, this table won't be needed for the provider exercise, but perhaps it should be created to better model an SIS.

The GROUP_REGISTRY schema:

CREATE TABLE GROUP_REGISTRY (
GroupId VARCHAR(256) NOT NULL,
GroupName VARCHAR(256) NOT NULL,
GroupType VARCHAR(256) NOT NULL,
PRIMARY KEY(GroupId)
);

To insert:

INSERT INTO GROUP_REGISTRY (GroupId, GroupName, GroupType) values 
("id", "name", "type");

The following data is present in this table:

mysql> select * from GROUP_REGISTRY;
+-------------------+----------------------+-----------+
| GroupId           | GroupName            | GroupType |
+-------------------+----------------------+-----------+
| ext-calc-355      | Calculus 355         | course    |
| ext-french-150-s1 | French 150 Section 1 | section   |
| ext-french-150-s2 | French 150 Section 2 | section   |
| ext-music-200     | Music 200            | course    |
| ext-physics-101   | Physics 101          | course    |
+-------------------+----------------------+-----------+
5 rows in set (0.00 sec)

A table of group membership will will be created called GROUP_MEMBERSHIP with the following fields:

  • GroupId - group's external id string.
  • Eid - user's enterprise id string.
  • Role - "student" or "instructor"

This is the critical table for the group provider exercise.

The following groups will be included in these data tables:

  • ext-physics-101
  • ext-music-200
  • ext-calc-355
  • ext-french-150-s1 - database only.
  • ext-french-150-s2 - database only.

The GROUP_MEMBERSHIP schema:

CREATE TABLE GROUP_MEMBERSHIP (
GroupId VARCHAR(256) NOT NULL,
Eid VARCHAR(256) NOT NULL,
Role VARCHAR(256) NOT NULL,
GroupKey INT NOT NULL,
PRIMARY KEY (GroupKey)
);

To insert:

INSERT INTO GROUP_MEMBERSHIP (GroupId, Eid, Role, GroupKey) values 
("ext-id", "name", "student", 1);

Data:

mmysql> select * from GROUP_MEMBERSHIP;
+-------------------+------------------+------------+----------+
| GroupId           | Eid              | Role       | GroupKey |
+-------------------+------------------+------------+----------+
| ext-physics-101   | markjnorton      | instructor |        0 |
| ext-physics-101   | duffygillman     | student    |        1 |
| ext-physics-101   | brigidcassidy    | student    |        2 |
| ext-physics-101   | charlesseverance | student    |        3 |
| ext-music-200     | duffygillman     | instructor |        4 |
| ext-music-200     | stevegithens     | student    |        5 |
| ext-music-200     | kistolhancock    | student    |        6 |
| ext-music-200     | markjnorton      | student    |        7 |
| ext-calc-355      | glenngolden      | instructor |        8 |
| ext-calc-355      | markjnorton      | student    |        9 |
| ext-calc-355      | duffygillman     | student    |       10 |
| ext-calc-355      | stevegithens     | student    |       11 |
| ext-calc-355      | brigidcassidy    | student    |       12 |
| ext-calc-355      | kristolhancock   | student    |       13 |
| ext-calc-355      | charlesseverance | student    |       14 |
| ext-french-150-s1 | kistolhancock    | instructor |       15 |
| ext-french-150-s1 | glenngolden      | student    |       16 |
| ext-french-150-s1 | charlesseverance | student    |       17 |
| ext-french-150-s2 | brigidcassidy    | instructor |       18 |
| ext-french-150-s2 | markjnorton      | student    |       19 |
| ext-french-150-s2 | duffygillman     | student    |       20 |
| ext-french-150-s2 | stevegithens     | student    |       21 |
+-------------------+------------------+------------+----------+
22 rows in set (0.00 sec)

Some userful group provider queries:

Type

SQL Expression

Get User Role

SELECT Role FROM sakai_sis.group_membership where GroupId='groupId' AND Eid='userEid'

User Roles for Group

SELECT Eid,Role FROM sakai_sis.group_membership WHERE groupId='eid'

Group Roles for Users

SELECT GroupId,Role FROM sakai_sis.group_membership WHERE Eid='userId'