3 // This file is part of Moodle - http://moodle.org/
5 // Moodle is free software: you can redistribute it and/or modify
6 // it under the terms of the GNU General Public License as published by
7 // the Free Software Foundation, either version 3 of the License, or
8 // (at your option) any later version.
10 // Moodle is distributed in the hope that it will be useful,
11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 // GNU General Public License for more details.
15 // You should have received a copy of the GNU General Public License
16 // along with Moodle. If not, see <http://www.gnu.org/licenses/>.
19 * Database enrolment plugin.
21 * This plugin synchronises enrolment and roles with external database table.
24 * @subpackage database
25 * @copyright 2010 Petr Skoda {@link http://skodak.org}
26 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
29 defined('MOODLE_INTERNAL') || die();
32 * Database enrolment plugin implementation.
33 * @author Petr Skoda - based on code by Martin Dougiamas, Martin Langhoff and others
34 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
36 class enrol_database_plugin extends enrol_plugin {
38 * Is it possible to delete enrol instance via standard UI?
40 * @param object $instance
43 public function instance_deleteable($instance) {
44 if (!enrol_is_enabled('database')) {
47 if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
51 //TODO: connect to external system and make sure no users are to be enrolled in this course
56 * Forces synchronisation of user enrolments with external database,
57 * does not create new courses.
59 * @param object $user user record
62 public function sync_user_enrolments($user) {
65 // we do not create courses here intentionally because it requires full sync and is slow
66 if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
70 $table = $this->get_config('remoteenroltable');
71 $coursefield = strtolower($this->get_config('remotecoursefield'));
72 $userfield = strtolower($this->get_config('remoteuserfield'));
73 $rolefield = strtolower($this->get_config('remoterolefield'));
75 $localrolefield = $this->get_config('localrolefield');
76 $localuserfield = $this->get_config('localuserfield');
77 $localcoursefiled = $this->get_config('localcoursefield');
79 $unenrolaction = $this->get_config('unenrolaction');
80 $defaultrole = $this->get_config('defaultrole');
82 $ignorehidden = $this->get_config('ignorehiddencourses');
84 // create roles mapping
85 $allroles = get_all_roles();
86 if (!isset($allroles[$defaultrole])) {
90 foreach ($allroles as $role) {
91 $roles[$role->$localrolefield] = $role->id;
97 $extdb = $this->db_init();
99 // read remote enrols and create instances
100 $sql = $this->db_get_sql($table, array($userfield=>$user->$localuserfield), array(), false);
102 if ($rs = $extdb->Execute($sql)) {
104 while ($fields = $rs->FetchRow()) {
105 $fields = $this->db_decode($fields);
107 if (empty($fields[$coursefield])) {
108 // missing course info
111 if (!$course = $DB->get_record('course', array($localcoursefiled=>$fields[$coursefield]), 'id,visible')) {
114 if (!$course->visible and $ignorehidden) {
118 if (empty($fields[$rolefield]) or !isset($roles[$fields[$rolefield]])) {
123 $roleid = $defaultrole;
125 $roleid = $roles[$fields[$rolefield]];
128 if (empty($enrols[$course->id])) {
129 $enrols[$course->id] = array();
131 $enrols[$course->id][] = $roleid;
133 if ($instance = $DB->get_record('enrol', array('courseid'=>$course->id, 'enrol'=>'database'), '*', IGNORE_MULTIPLE)) {
134 $instances[$course->id] = $instance;
138 $enrolid = $this->add_instance($course);
139 $instances[$course->id] = $DB->get_record('enrol', array('id'=>$enrolid));
145 // bad luck, something is wrong with the db connection
150 // enrol user into courses and sync roles
151 foreach ($enrols as $courseid => $roles) {
152 if (!isset($instances[$courseid])) {
156 $instance = $instances[$courseid];
158 if ($e = $DB->get_record('user_enrolments', array('userid'=>$user->id, 'enrolid'=>$instance->id))) {
159 // reenable enrolment when previously disable enrolment refreshed
160 if ($e->status == ENROL_USER_SUSPENDED) {
161 $DB->set_field('user_enrolments', 'status', ENROL_USER_ACTIVE, array('enrolid'=>$instance->id, 'userid'=>$user->id));
164 $roleid = reset($roles);
165 $this->enrol_user($instance, $user->id, $roleid);
168 if (!$context = get_context_instance(CONTEXT_COURSE, $instance->courseid)) {
172 $current = $DB->get_records('role_assignments', array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id), '', 'id, roleid');
175 foreach ($current as $r) {
176 if (in_array($r->id, $roles)) {
177 $existing[$r->roleid] = $r->roleid;
179 role_unassign($r->roleid, $user->id, $context->id, 'enrol_database', $instance->id);
182 foreach ($roles as $rid) {
183 if (!isset($existing[$rid])) {
184 role_assign($rid, $user->id, $context->id, 'enrol_database', $instance->id);
189 // unenrol as necessary
190 $sql = "SELECT e.*, c.visible AS cvisible, ue.status AS ustatus
192 JOIN {user_enrolments} ue ON ue.enrolid = e.id
193 JOIN {course} c ON c.id = e.courseid
194 WHERE ue.userid = :userid AND e.enrol = 'database'";
195 $rs = $DB->get_recordset_sql($sql, array('userid'=>$user->id));
196 foreach ($rs as $instance) {
197 if (!$instance->cvisible and $ignorehidden) {
201 if (!$context = get_context_instance(CONTEXT_COURSE, $instance->courseid)) {
206 if (!empty($enrols[$instance->courseid])) {
207 // we want this user enrolled
211 // deal with enrolments removed from external table
212 if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
214 $this->unenrol_user($instance, $user->id);
216 } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
217 // keep - only adding enrolments
219 } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
221 if ($instance->ustatus != ENROL_USER_SUSPENDED) {
222 $DB->set_field('user_enrolments', 'status', ENROL_USER_SUSPENDED, array('enrolid'=>$instance->id, 'userid'=>$user->id));
224 if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
225 role_unassign_all(array('contextid'=>$context->id, 'userid'=>$user->id, 'component'=>'enrol_database', 'itemid'=>$instance->id));
233 * Forces synchronisation of all enrolments with external database.
237 public function sync_enrolments() {
240 // we do not create courses here intentionally because it requires full sync and is slow
241 if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or !$this->get_config('remoteenroltable') or !$this->get_config('remotecoursefield') or !$this->get_config('remoteuserfield')) {
245 // we may need a lot of memory here
247 raise_memory_limit(MEMORY_HUGE);
249 $extdb = $this->db_init();
251 // second step is to sync instances and users
252 $table = $this->get_config('remoteenroltable');
253 $coursefield = strtolower($this->get_config('remotecoursefield'));
254 $userfield = strtolower($this->get_config('remoteuserfield'));
255 $rolefield = strtolower($this->get_config('remoterolefield'));
257 $localrolefield = $this->get_config('localrolefield');
258 $localuserfield = $this->get_config('localuserfield');
259 $localcoursefiled = $this->get_config('localcoursefield');
261 $unenrolaction = $this->get_config('unenrolaction');
262 $defaultrole = $this->get_config('defaultrole');
264 // create roles mapping
265 $allroles = get_all_roles();
266 if (!isset($allroles[$defaultrole])) {
270 foreach ($allroles as $role) {
271 $roles[$role->$localrolefield] = $role->id;
274 // first find all existing courses with enrol instance
275 $sql = "SELECT c.id, c.visible, c.$localcoursefiled AS mapping, e.id AS enrolid
277 JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')";
279 $rs = $DB->get_recordset_sql($sql); // watch out for idnumber duplicates
280 foreach ($rs as $course) {
281 if (empty($course->mapping)) {
284 $existing[$course->mapping] = $course;
288 // add necessary enrol instances that are not present yet;
289 $sql = $this->db_get_sql($table, array(), array($coursefield), true);
290 if ($rs = $extdb->Execute($sql)) {
292 $sql = "SELECT c.id, c.visible
294 JOIN {enrol} e ON (e.courseid = c.id AND e.enrol = 'database')
295 WHERE c.$localcoursefiled = :mapping";
297 while ($mapping = $rs->FetchRow()) {
298 $mapping = reset($mapping);
299 $mapping = $this->db_decode($mapping);
300 if (!empty($mapping) and !isset($existing[$mapping])) {
301 $params['mapping'] = $mapping;
302 if ($course = $DB->get_record_sql($sql, $params, IGNORE_MULTIPLE)) {
303 $new = new stdClass();
304 $new->id = $course->id;
305 $new->visible = $course->visible;
306 $new->mapping = $mapping;
307 $new->enrolid = $this->add_instance($course);
308 $existing[$mapping] = $new;
315 debugging('Error while communicating with external enrolment database');
321 $ignorehidden = $this->get_config('ignorehiddencourses');
322 $fields = array($userfield);
324 $fields[] = $rolefield;
326 foreach ($existing as $course) {
327 if ($ignorehidden and !$course->visible) {
330 if (!$instance = $DB->get_record('enrol', array('id'=>$course->enrolid))) {
333 $context = get_context_instance(CONTEXT_COURSE, $course->id);
335 // get current list of enrolled users with their roles
336 $current_roles = array();
337 $current_status = array();
338 $user_mapping = array();
339 $sql = "SELECT u.$localuserfield AS mapping, u.id, ue.status, ue.userid, ra.roleid
341 JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = :enrolid)
342 JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.itemid = ue.enrolid AND ra.component = 'enrol_database')
343 WHERE u.deleted = 0";
344 $params = array('enrolid'=>$instance->id);
345 if ($localuserfield === 'username') {
346 $sql .= " AND u.mnethostid = :mnethostid";
347 $params['mnethostid'] = $CFG->mnet_localhost_id;
349 $rs = $DB->get_recordset_sql($sql, $params);
350 foreach ($rs as $ue) {
351 $current_roles[$ue->userid][$ue->roleid] = $ue->roleid;
352 $current_status[$ue->userid] = $ue->status;
353 $user_mapping[$ue->mapping] = $ue->userid;
357 // get list of users that need to be enrolled and their roles
358 $requested_roles = array();
359 $sql = $this->db_get_sql($table, array($coursefield=>$course->mapping), $fields);
360 if ($rs = $extdb->Execute($sql)) {
362 if ($localuserfield === 'username') {
363 $usersearch = array('mnethostid'=>$CFG->mnet_localhost_id, 'deleted' =>0);
365 while ($fields = $rs->FetchRow()) {
366 $fields = array_change_key_case($fields, CASE_LOWER);
367 if (empty($fields[$userfield])) {
368 //user identification is mandatory!
370 $mapping = $fields[$userfield];
371 if (!isset($user_mapping[$mapping])) {
372 $usersearch[$localuserfield] = $mapping;
373 if (!$user = $DB->get_record('user', $usersearch, 'id', IGNORE_MULTIPLE)) {
374 // user does not exist or was deleted
377 $user_mapping[$mapping] = $user->id;
380 $userid = $user_mapping[$mapping];
382 if (empty($fields[$rolefield]) or !isset($roles[$fields[$rolefield]])) {
387 $roleid = $defaultrole;
389 $roleid = $roles[$fields[$rolefield]];
392 $requested_roles[$userid][$roleid] = $roleid;
397 debugging('Error while communicating with external enrolment database');
401 unset($user_mapping);
403 // enrol all users and sync roles
404 foreach ($requested_roles as $userid=>$roles) {
405 foreach ($roles as $roleid) {
406 if (empty($current_roles[$userid])) {
407 $this->enrol_user($instance, $userid, $roleid);
408 $current_roles[$userid][$roleid] = $roleid;
409 $current_status[$userid] = ENROL_USER_ACTIVE;
413 // unassign removed roles
414 foreach($current_roles[$userid] as $cr) {
415 if (empty($roles[$cr])) {
416 role_unassign($cr, $userid, $context->id, 'enrol_database', $instance->id);
417 unset($current_roles[$userid][$cr]);
421 // reenable enrolment when previously disable enrolment refreshed
422 if ($current_status[$userid] == ENROL_USER_SUSPENDED) {
423 $DB->set_field('user_enrolments', 'status', ENROL_USER_ACTIVE, array('enrolid'=>$instance->id, 'userid'=>$userid));
427 // deal with enrolments removed from external table
428 if ($unenrolaction == ENROL_EXT_REMOVED_UNENROL) {
430 if (!empty($requested_roles)) {
431 // we might get some error or connection problem, better not unenrol everybody
432 foreach ($current_status as $userid=>$status) {
433 if (isset($requested_roles[$userid])) {
436 $this->unenrol_user($instance, $userid);
440 } else if ($unenrolaction == ENROL_EXT_REMOVED_KEEP) {
441 // keep - only adding enrolments
443 } else if ($unenrolaction == ENROL_EXT_REMOVED_SUSPEND or $unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
445 foreach ($current_status as $userid=>$status) {
446 if (isset($requested_roles[$userid])) {
449 if ($status != ENROL_USER_SUSPENDED) {
450 $DB->set_field('user_enrolments', 'status', ENROL_USER_SUSPENDED, array('enrolid'=>$instance->id, 'userid'=>$userid));
452 if ($unenrolaction == ENROL_EXT_REMOVED_SUSPENDNOROLES) {
453 role_unassign_all(array('contextid'=>$context->id, 'userid'=>$userid, 'component'=>'enrol_database', 'itemid'=>$instance->id));
459 // close db connection
464 * Performs a full sync with external database.
466 * First it creates new courses if necessary, then
467 * enrols and unenrols users.
470 public function sync_courses() {
473 // make sure we sync either enrolments or courses
474 if (!$this->get_config('dbtype') or !$this->get_config('dbhost') or $this->get_config('newcoursetable') or $this->get_config('newcoursefullname') or $this->get_config('newcourseshortname')) {
478 // we may need a lot of memory here
480 raise_memory_limit(MEMORY_HUGE);
482 $extdb = $this->db_init();
484 // first create new courses
485 $table = $this->get_config('newcoursetable');
486 $fullname = strtolower($this->get_config('newcoursefullname'));
487 $shortname = strtolower($this->get_config('newcourseshortname'));
488 $idnumber = strtolower($this->get_config('newcourseidnumber'));
489 $category = strtolower($this->get_config('newcoursecategory'));
491 $fields = array($fullname, $shortname, $idnumber);
493 $fields[] = $category;
496 $fields[] = $idnumber;
498 $sql = $this->db_get_sql($table, array(), $fields);
499 $createcourses = array();
500 if ($rs = $extdb->Execute($sql)) {
502 $courselist = array();
503 while ($fields = $rs->FetchRow()) {
504 $fields = array_change_key_case($fields, CASE_LOWER);
505 if (empty($fields[$shortname]) or empty($fields[$fullname])) {
506 //invalid record - these two are mandatory
509 $fields = $this->db_decode($fields);
510 if ($DB->record_exists('course', array('shortname'=>$fields[$shortname]))) {
514 if ($idnumber and $DB->record_exists('course', array('idnumber'=>$fields[$idnumber]))) {
515 // idnumber duplicates are not allowed
518 if ($category and !$DB->record_exists('course_categories', array('id'=>$fields[$category]))) {
519 // invalid category id, better to skip
522 $course = new stdClass();
523 $course->fullname = $fields[$fullname];
524 $course->shortname = $fields[$shortname];
525 $course->idnumber = $idnumber ? $fields[$idnumber] : NULL;
526 $course->category = $category ? $fields[$category] : NULL;
527 $createcourses[] = $course;
532 debugging('Error while communicating with external enrolment database');
536 if ($createcourses) {
537 require_once("$CFG->dirroot/course/lib.php");
539 $template = $this->get_config('templatecourse');
540 $defaultcategory = $this->get_config('defaultcategory');
543 if ($template = $DB->get_record('course', array('shortname'=>$template))) {
544 unset($template->id);
545 unset($template->fullname);
546 unset($template->shortname);
547 unset($template->idnumber);
549 $template = new stdClass();
552 $template = new stdClass();
554 if (!$DB->record_exists('course_categories', array('id'=>$defaultcategory))) {
555 $categories = $DB->get_records('course_categories', array(), 'sortorder', 'id', 0, 1);
556 $first = reset($categories);
557 $defaultcategory = $first->id;
560 foreach ($createcourses as $fields) {
561 $newcourse = clone($template);
562 $newcourse->fullname = $fields->fullname;
563 $newcourse->shortname = $fields->shortname;
564 $newcourse->idnumber = $fields->idnumber;
565 $newcourse->category = $fields->category ? $fields->category : $defaultcategory;
567 create_course($newcourse);
570 unset($createcourses);
574 // close db connection
578 protected function db_get_sql($table, array $conditions, array $fields, $distinct = false, $sort = "") {
579 $fields = $fields ? implode(',', $fields) : "*";
582 foreach ($conditions as $key=>$value) {
583 $value = $this->db_encode($this->db_addslashes($value));
585 $where[] = "$key = '$value'";
588 $where = $where ? "WHERE ".implode(" AND ", $where) : "";
589 $sort = $sort ? "ORDER BY $sort" : "";
590 $distinct = $distinct ? "DISTINCT" : "";
591 $sql = "SELECT $distinct $fields
599 protected function db_init() {
602 require_once($CFG->libdir.'/adodb/adodb.inc.php');
604 // Connect to the external database (forcing new connection)
605 $extdb = ADONewConnection($this->get_config('dbtype'));
606 if ($this->get_config('debugdb')) {
607 $extdb->debug = true;
608 ob_start(); //start output buffer to allow later use of the page headers
611 $extdb->Connect($this->get_config('dbhost'), $this->get_config('dbuser'), $this->get_config('dbpass'), $this->get_config('dbname'), true);
612 $extdb->SetFetchMode(ADODB_FETCH_ASSOC);
613 if ($this->get_config('dbsetupsql')) {
614 $extdb->Execute($this->get_config('dbsetupsql'));
619 protected function db_addslashes($text) {
620 // using custom made function for now
621 if ($this->get_config('dbsybasequoting')) {
622 $text = str_replace('\\', '\\\\', $text);
623 $text = str_replace(array('\'', '"', "\0"), array('\\\'', '\\"', '\\0'), $text);
625 $text = str_replace("'", "''", $text);
630 protected function db_encode($text) {
631 $dbenc = $this->get_config('dbencoding');
632 if (empty($dbenc) or $dbenc == 'utf-8') {
635 if (is_array($text)) {
636 foreach($text as $k=>$value) {
637 $text[$k] = $this->db_encode($value);
641 return textlib_get_instance()->convert($text, 'utf-8', $dbenc);
645 protected function db_decode($text) {
646 $dbenc = $this->get_config('dbencoding');
647 if (empty($dbenc) or $dbenc == 'utf-8') {
650 if (is_array($text)) {
651 foreach($text as $k=>$value) {
652 $text[$k] = $this->db_decode($value);
656 return textlib_get_instance()->convert($text, $dbenc, 'utf-8');