Updating MDID with User Data

The following are two self descriptive SQL Scripts to be run on the MDID server (typically via a MySql GUI such as [http://www.sqlyog.com/ SQLyog]). The first script deals with first ever import attempts and should only need running once on a new installation. The second (longer script) is to be run on a periodic basis to keep the user accounts and the automated groups up-to-date based on data from Beth Crutch. Access to this data is restricted to machines with specific IP addresses, and you will need to contact Beth to arrange this.

To use either section, copy and paste all the text in the blue area, follow the instructions in the comments (/* */) and execute in your MySQL interface.

The code and examples are written giving specific instructions assuming you are using [http://www.webyog.com SQLyog Community Edition] to interact with your MDID database and that you have enabled a data-feed from Beth Crutch to supply the import data.

Part I - the Initial setup

This section is only used for the first ever import cycle. It creates the import table and creates two of the automatically generated groups (staff and students).

/*
Data Import and Maintainence.
ON FIRST EVER IMPORT
Create /import/ table for holding the data from register.oucs.ox.ac.uk.
In SQLyog, execute the following (either in a new database you have created, or inside the MDID database):
*/

CREATE TABLE `import` (
  `forename` varchar(30) default NULL,
  `surname` varchar(30) default NULL,
  `known_as` varchar(40) default NULL,
  `college` varchar(4) default NULL,
  `department` varchar(10) default NULL,
  `status` varchar(12) default NULL,
  `deptcode` int(11) default NULL,
  `oss_id` int(11) default NULL,
  `username` varchar(10) NOT NULL default '',
  `email` varchar(60) default NULL,
  `yearofcourse` int(11) default NULL,
  `course` varchar(50) default NULL,
  `oss_pos_code` varbinary(6) default NULL,
  PRIMARY KEY  (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

/*
Now we skip ahead to prepare some material that only needs to be done once. Create MDID groups for Staff and Students.
*/

INSERT INTO mdid2.usergroups (Title, Type)
VALUES 'Staff', 'M' ;

INSERT INTO mdid2.usergroups (Title, Type)
VALUES 'Students', 'M' ;

Part II, the regular import routine

This is the key script to be run on a periodic basis. It will add in any new users, and assign users to groups based on their current settings.

NB: If you choose to run all queries in one batch (Shift + F5 in SQLyog) be prepared for a significant delay on the current OxClic server. Whilst running the queries individually in sequence seems to be near instantaneous (claims 0ms for each query), running them all in one batch produces an inexplicable delay.

/*
Using your browser, visit the URL from Beth (e.g. https://register.oucs.ox.ac.uk/upload/card/mdid/mdid.txt) and save the output to a file on the machine (assuming mdid.txt onto the Desktop). 

NB Access to this page is restricted by IP address, contact Beth Crutch to gain access.

Next, import the text data into the /import/ table by using [http://www.webyog.com/ SQLyog's] "Import Data From CSV" function.

1) Locate table "import" in the object browser (typically on the left)
2) Right click on the table name, and select "Import Data From CSV" in the menu that appears.
3) In the new window ("Import Data From A CSV File") ensure that all entries (bar one) in the Columns area are selected by clicking on the button [Select All], then locate "updated" at the bottom of the list, and click on it once to deselect it.
4) Edit the default import settings by clicking on [Change]
5) In the small window ("Specify the escape characters") make the following changes:
  - Set "Lines Terminated By" to "\n"
  - Select "Variable Length" under fields
  - Check the box to the left of "Fields Terminated By"
  - Set the value of "Fields Terminated By" to "\t"
  - Click [OK]
6) Select the file you saved by entering the path in "Import From File" or by using the browse [...] button to locate and select the MDID.txt file.
7) Click [Import] and await a feedback message.
Assuming no errors, there should now be data in the import table.

AMEND AND ADD USERS

This process is a crude way of updating existing user records (reliant upon a unique username, the webauthID) and a way to insert any rows not already present. NB: We do not remove users from this system, though their automated group memberships will be removed when this is run if they no longer exist in the User Data.
*/

UPDATE mdid2.users AS u, temp_mdid.import AS i
SET u.Name = i.surname, u.FirstName = i.forename, u.Email = i.email
WHERE u.Login = i.username;
INSERT INTO mdid2.users (Login, Password, Name, FirstName, Email)
SELECT  username, '', surname, forename, email
FROM temp_mdid.import
WHERE username NOT IN (SELECT Login FROM mdid2.users);
/*
CREATE NEW GROUPS
1) Create a group for staff in each department in the data.
*/
INSERT INTO mdid2.usergroups (Title, Type)
SELECT CONCAT(im.department,'_staff'), 'M'
FROM temp_mdid.import AS im
WHERE im.status IN ('college','dept','ret','senmem','staff','visitor')
  AND CONCAT(im.department,'_staff') NOT IN (SELECT title FROM mdid2.usergroups)
GROUP BY im.department;
/*
Reads as: Insert the Title and Type into MDID's UserGroups table, where the Title is made from the department name appended with "_staff". Find all unique department titles in the import data, but check that that department's group doesn't already exist in the UserGroup's table. We define staff as having a status of: college, dept, ret, senmen, staff or visitor.
Repeat the process but for staff AND postgrads



*/
INSERT INTO mdid2.usergroups (Title, Type)
SELECT CONCAT(im.department,'_staff_postgrads'), 'M'
FROM temp_mdid.import AS im
WHERE im.status IN ('college','dept','ret','senmem','staff','visitor','postgrad')
  AND CONCAT(im.department,'_staff_postgrads') NOT IN (SELECT title FROM mdid2.usergroups)
GROUP BY im.department;
/*
Create a group for each course in the data. Same principle as for department, only this time using the course name field.




*/
INSERT INTO mdid2.usergroups (Title, Type)
SELECT course , 'M'
FROM `temp_mdid`.`import`
WHERE course <> ''
  AND course NOT IN (SELECT title FROM mdid2.usergroups)
GROUP BY course;
/*
CREATE USER TO GROUP MAPPINGS/ASSOCIATIONS
Now we have the trickier part.. cleaning up existing group memberships without disturbing non-automatic groups and then recreating those group memberships.
NB: An association is a record in the usergroupmembers table between an user ID and a group ID.


a) Startwith Staff, then Students... This is a case of matching the user to the import table via the username of the person, and then looking at their status value (Staff status being one of 'college','dept','ret','senmem','staff' or 'visitor' and Students being anyone with a status not in that list).

*/
DELETE FROM mdid2.usergroupmembers
WHERE GroupID IN (SELECT ID FROM mdid2.usergroups WHERE Title = 'Staff');
INSERT INTO mdid2.usergroupmembers (UserID, GroupID)
SELECT u.ID, g.ID
FROM
  mdid2.users AS u,
  mdid2.usergroups AS g,
  temp_mdid.import AS i
WHERE u.Login = i.username
  AND i.status IN ('college','dept','ret','senmem','staff','visitor')
  AND 'Staff' = g.Title;
DELETE FROM mdid2.usergroupmembers
WHERE GroupID IN (SELECT ID FROM mdid2.usergroups WHERE Title = 'Students');
INSERT INTO mdid2.usergroupmembers  (UserID, GroupID)
SELECT u.ID, g.ID
FROM
  mdid2.users AS u,
  mdid2.usergroups AS g,
  temp_mdid.import AS i
WHERE u.Login = i.username
  AND i.status NOT IN ('college','dept','ret','senmem','staff','visitor')
  AND 'Students' = g.Title ;
/*



b) Now handle mapping users to staff departmental groups.

*/
DELETE FROM mdid2.usergroupmembers
WHERE GroupID IN (SELECT ID FROM mdid2.usergroups WHERE Title LIKE '%_staff');
INSERT INTO mdid2.usergroupmembers  (UserID, GroupID)
SELECT u.ID, g.ID
FROM
  mdid2.users AS u,
  mdid2.usergroups AS g,
  temp_mdid.import AS i
WHERE u.Login = i.username
  AND i.status IN ('college','dept','ret','senmem','staff','visitor')
  AND CONCAT(i.department,'_staff') = g.Title;
/*


c) Same as b, but this time include the postgraduates for the staff and postgrads by department groups
*/
DELETE FROM mdid2.usergroupmembers
WHERE GroupID IN (SELECT ID FROM mdid2.usergroups WHERE Title LIKE '%_staff_postgrads');
INSERT INTO mdid2.usergroupmembers  (UserID, GroupID)
SELECT u.ID, g.ID
FROM
  mdid2.users AS u,
  mdid2.usergroups AS g,
  temp_mdid.import AS i
WHERE u.Login = i.username
  AND i.status IN ('college','dept','ret','senmem','staff','visitor','postgrad')
  AND CONCAT(i.department,'_staff_postgrads') = g.Title;
/*


d) Last link up, put those with course information into the groups with the same name. The Delete here is a tad tricker as there's no easy text string to match on.
*/
DELETE mdid2.usergroupmembers
FROM mdid2.usergroupmembers,
  mdid2.usergroups AS gr,
  temp_mdid.import AS im
WHERE GroupID = gr.ID
  AND gr.Title = im.course
  AND im.course <> '';
INSERT INTO mdid2.usergroupmembers (UserID, GroupID)
SELECT u.ID, g.ID
FROM
  mdid2.users AS u,
  mdid2.usergroups AS g,
  temp_mdid.import AS i
WHERE u.Login = i.username
  AND i.course = g.Title;
/*
This ends the updates!

*/

Things to consider/improve

User Permissions are largely outside of the scope of this problem. Permissions are controlled independently to group creation. There is a potential minor issue in that the database could be left with redundant records related to permissions on groups that no longer exist.

Unanticipated consequence of the above approach. Groups are deleted and then recreated thus their IDs change on each script cycle (with the exception of the Students and Staff groups who are only created once). This means any permissions assigned to a department or course group will be lost when the script is run. Am aware of the issue and considering suitable options. (NB: CARL - REMEMBER THIS!)