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 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.

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.

Part I - the Initial setup

/*
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

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 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).
Next, import the text data into the /import/ table by using 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/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!
*/