Programming Interlude

I ran across an interesting programming issue yesterday at my day job. We’re using Drupal and Moodle together and need way to make sure the users have access to both systems. Here’s an ugly and somewhat kludgey way to do user data sharing between Drupal and Moodle:

  1. First, create a database containing bother the Drupal and Moodle tables.
  2. Delete or rename the existing Drupal users table.
  3. Create a view on the Moodle users table similar to this:
CREATE VIEW `drpl_users` AS
 SELECT (if(`mdl_user`.`id`=1, 0,
            ) as `uid`,
`mdl_user`.`username` AS `name`,
`mdl_user`.`password` AS `pass`,
`mdl_user`.`email` AS `mail`,
`mdl_user`.`timezone` AS `timezone`,
`mdl_user`.`timecreated` AS `created`,
`mdl_user`.`lang` AS `language`,
`mdl_user`.`confirmed` AS `status`,
`mdl_user`.`lastaccess` AS `access`,
`mdl_user`.`lastlogin` AS `login`,
0 AS `mode`,
0 AS `sort`,
0 AS `threshold`,
'a:0:{}' AS `data`  FROM mdl_user;

So what is going on here? Except for the first few lines, it’s fairly self-explanatory. We’re mapping the Moodle fields to the Drupal version of that field. Some of the Drupal fields, such as mode, sort, etc do not have an analogue in Moodle, so we just set a default value.

So what about those first few lines? Drupal and Moodle both operate with a guest and admin user. In Moodle, the userIds for the guest and admin users are 1 and 2, respectively. In Drupal however, the guest user is 0 and the admin is 1.  This means that the admin user from Moodle would show up as a non-admin user (or another user entirely!) to the Drupal system. Simply changing the data in the Moodle user table to match Drupal’s scheme breaks the login ability for those users in Moodle. What to do?

I ended up using a nested MySQL conditional to remap the userId values for the view. Essentially, I’m telling the view that id the id coming from Moodle is 1, then display it as 0. Same with the admin user: a 2 becomes 1. It doesn’t affect the Moodle users in anyway, and so long as the Drupal users do not try to edit their user info (which they won’t be able to do in this instance), Drupal is none the wiser!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s