Using an external database to control enrollments
You may use an external database (or nearly any kind) to control your enrollments. It is assumed your external database contains a field containing a course ID, a field containing a user ID, and optionally a field containing a role. These are compared against fields that you choose in the local course, user tables, and role tables.
The following are the supported data sources but note that you will need to have to compile PHP with the appropriate options or through ODBC.
- access
- ado
- MySQL
- borland_ibase
- csv
- db2
- fbsql
- firebird
- ibase
- informix72
- informix
- MySQL (deprecated, please use mysqli instead)
- mysqli
- mysqlt
- oci805
- oci8
- oci8po
- odbc
- odbc_mssql
- odbc_oracle
- oracle
- postgres64
- postgres7
- postgres
- proxy
- sqlanywhere
- sybase
- vfp
Enrollment and unenrollment
External database enrollment happens at the moment when a user logs into LMS. The plugin will attempt to automatically enroll the user in all their courses according to the data in the external database and, optionally, create empty courses where they do not already exist. To check if it is working, you can log in as a user and then check that their list of courses is as you would expect.
The process also unenrolls users from courses if they are no longer in the database. User records are marked according to their original enrollment method. Therefore the external database plugin can only unenroll users who were enrolled by the plugin in the first place.
Hidden courses
Courses that are set to “Course is not available to users” can be ignored for enrollment purposes by setting the “enroll_db_ignorehiddencourse” to yes.
Enrollment and roles
The “enroll_database | defaultrole” setting in the plugin settings page specifies the role that the user will take when they are added to the course. The default setting will set them to the course default setting (initially “student”). However, you can specify a field in the external table (specified in the “enroll_database | remoterolefield” setting) that contains the short name or id for the user’s role. This could, for example, be used to enroll both users and instructors into courses using a suitably configured database.
Unenrollment
The External unenroll action (“enroll_database | unenrollaction”) setting in the plugin settings page defines what action should be taken when a user enrollment disappears from external enrollment source. Each setting does the following:
- “Unenroll user from course” When the user disappears from the external source, the enrollment is completely removed and all the roles removed. This means some user data and settings are purged from course during course unenrollment (that usually include grades, activity attempts, etc.)
- “Keep user enrolled” When the user disappears from the external source, the enrollment is kept as is, and the user is still able to enter the course and perform activities, access resources, etc. It’s a “do nothing” option.
- “Disable course enrollment” When the user disappears from the external source, user enrollment is suspended (the user can’t access the course, but user data and settings are kept), and roles are kept as is. You might use this because in some cases the user needs a role with some capability to be visible in UI – such has in gradebook, assignments, etc.
- “Disable course enrollment and remove roles” When the user disappears from the external source, the enrollment is suspended and roles assigned by enroll instance are removed. Please note that user may “disappear” from gradebook and other areas.
Creating courses
Optionally courses that do not exist in the LMS site can be created.
You can additionally specify the Category into which the new course will be placed, in the New course category id field. The data in this field must be the id of a currently existing category; it will not create a new category. The id number is number assigned by LMS in the database when the category is created (e.g. mdl_course_categories.id).
- Do not confuse this category id with the new custom category id number field that you can manually assign to a category.
- Leaving the category id data empty means that a course will be assigned to the default category.
- If you assign data to categories that do not exist already in LMS, the courses will not be created.
Default new course category is the category to which courses will be assigned and created in unless you set up and so indicate in the data field of the “New course category id field.”
You may also specify a New course template: a “template” course from which the new course will be copied. The data for this field should be the shortname of the template course.
Synchronization script
A script is provided that can synchronize all your user enrollments at once – both adding and removing user enrollments (and creating courses if specified). The script is called sync.php and is found in the enroll/database/cli folder.
This script is meant to be called from a system cronjob to sync lms enrollments with enrollments in the external database. You need to make sure all the users present in the external enrollments are already created in lms. If you are using external authentication plugins (db, LDAP, etc.) you can use the scripts provided by those plugins to synchronize your users before running this script.
Example cron entry
# 5 minutes past 4am 5 4 * * * /usr/bin/php -c /path/to/php.ini /path/to/lms/enroll/database/cli/sync.php
Notes:
- If you have a large number of enrollments, you may want to raise the memory limits by passing -d memory_limit=256M
- For debugging & better logging, you are encouraged to use in the command line: -d log_errors=1 -d error_reporting=E_ALL -d display_errors=0 -d html_errors=0
- This only works for users that already exist in your LMS site (see comment above)
Setting up enrollment sync (How to)
You will need to perform (as a minimum) the following steps to enable external database enrollment – only a single table is required in the database which contains a record for every user/course combination. If the table is large it is a good idea to make sure appropriate indexes have been created:
- Use an existing database or create a new one. Use an existing table or create a new one with the following minimum fields.
- A unique course identifier to match one of the following fields.
- the “idnumber” field in LMS’s course table (varchar 100), which is manually specified as the “Course ID number” when editing a course’s settings
- the “shortname” field in LMS’s course table (varchar 255), which is manually specified as the “Course short name” when editing a course’s settings
- the “id” field in LMS’s course table (int 10), which is based on course creation order
- A unique user identifier to match one of the following fields.
- the “idnumber” field in LMS’s user table (varchar 255), which is manually specified as the “ID number” when editing a user’s profile
- the “username” field in LMS’s user table (varchar 100), which is manually specified as the “Username” when editing a user’s profile
- the “email” field in LMS’s user table (varchar 100), which is manually specified as the “Email address” when editing a user’s profile
- the “id” field in LMS’s user table (int 10), which is based on user creation order
- (optional) A unique role identifier to match one of the following fields.
- the “shortname” field in LMS’s role table (varchar 100), for example, editingteacher, coursecreator, student, …
- the “name” field in LMS’s role table (varchar 255), for example, Teacher, Course creator, Student, …
- the “id” field in LMS’s role table (int 10), which is based on initial installation and new role creation order
- A unique course identifier to match one of the following fields.
- Populate the database table. Each user/course combination to have a record in the table.
- In LMS, go to Site administration > Plugins > Enrollments > Manage enroll plugins, find External Database in the list, enable it (click the closed-eye icon) and click Settings.
- In the top panel, select the database type (make sure you have the necessary configuration in PHP for that type) and then supply the information to connect to the database.
- The middle panel creates the mapping between LMS and the external database. The first three settings are for the local (LMS) field names and the last three for the remote (external database) settings. They are in the same order.
- enroll_localcoursefield / enroll_remotecoursefield – in LMS the name of the field in the course settings the uniquely identifies the course (e.g., idnumber). In the external database the name of the matching field.
- enroll_localuserfield / enroll_remoteuserfield – in LMS the name of the field in the user profile that uniquely identified the user (e.g., idnumber). In the external database the name of the matching field.
- enroll_db_localrolefield / enroll_db_remoterolefield – (optional) in LMS the name of the field in the role edit page the uniquely identifies the role (e.g., shortname). In the external database the name of the matching field.
- The Roles panel specifies the role that the user will get in the course if their role is not specified in the external database.
- A new optional field enroll_database | remoteotheruserfield allows those with the role “Other users” to be added but not to be included as course participants.
- The final panel enables auto-creation of courses.
- Save changes, and then tick the box to enable external database enrollment.
Field Mapping Example:
Choose your fields from the LMS database:
- enroll_localcoursefield: A course identifier from mdl_course, e.g. “idnumber”
- enroll_localuserfield: A user identifier from mdl_user, e.g. “idnumber”
- enroll_localrolefield: (optional) A role identifier from mdl_role, e.g. “shortname”
Create a view in your external database which matches the chosen field values from LMS:
- enroll_remotecoursefield: A matching course identifier from your external database table, e.g. “course_number”
- enroll_remoteuserfield: A matching user identifier from your external database table, e.g. “userid”
- enroll_remoterolefield: (optional) A matching role identifier from your external database table, e.g. “role_name”
Potential gotchas
- It almost goes without saying that the integrity of the external database is important. If data is missing from the database then there is a potential for users being unenrolled from some or all of their courses. The unenrollment process will remove them from any group assignments and also poll each module type to give the module the option of removing that user’s data if appropriate (for example, however, forum posts are never deleted). It is therefore prudent that you take the utmost care to ensure that the data in the external database is correct at all times.
- Minor Security Issue Consider that if the ID number field you use to identify your users is editable by the users (in their profile), then there is a potential for them changing this to the id of another valid user and gaining access to resources that they should not. (However, they will still appear as themselves, they cannot impersonate the other user or otherwise gain access to their resources.) To prevent this and similar issues, you can lock the ID number as well as other fields so the user can not change them. Do this in the Data mapping section of Site administration > Plugins > Authentication > External database.
Errors and diagnostics
The plugin produces a number of diagnostic messages and/or errors which are recorded to the PHP error log (as defined in the php.ini file). In addition, messages about courses that are in the database for the user but that do not exist in the LMS site will only be produced if debugging is set to ALL or DEVELOPER.
You can get detailed progress information by executing the sync script with -v parameter:
php /path/to/lms/enroll/database/cli/sync.php -v