-
Improvement
-
Resolution: Fixed
-
Major
-
2.5.2, 2.5.3, 2.6, 2.7.20, 3.2.4, 3.3.1, 3.4
-
MOODLE_25_STABLE, MOODLE_26_STABLE, MOODLE_27_STABLE, MOODLE_32_STABLE, MOODLE_33_STABLE, MOODLE_34_STABLE
-
MOODLE_34_STABLE
-
Manual tests
Create two courses
- Course 1
- Course 2
- In course 1 create the following self enrolments:
- Student 1: with a start and end date
- Student 3: suspended, start and end date
- Student 4: no start or end date
- Student 5: start and end date
- In course 1 create the following manual enrolments:
- Student 1: start date set to be later than the self enrolment, end date after the self enrolment end date
- Student 2: suspended
- Student 3: start date before self enrolment, no end date
- Student 4: start date and end date after self enrolment end date
- Student 5: start date after self enrolment start, no end date
Testing steps
- Make a note of the user enrolments in course 1, noting the earliest defined start and the most advantageous end dates for the enrolment methods that a user has
- Enter course 2 and create a new meta enrolment instance and set Course 1 as the child course
- Verify that the eligible users have been enrolled onto Course 2 and that they have the most advantageous start and end dates on the meta enrolment
- Go back to course 1 and make the following changes to enrolment:
- Student 1: suspend self enrolment
- Student 2: activate manual enrolment
- Student 3: activate self enrolment
- Student 4: change the start date of manual enrolment
- Student 5: set an end date on manual enrolment that is later than the self enrolment end date
- On the command line run: php enrol/meta/cli/sync.php --verbose
- Go to Course 2 and verify that:
- Student 1: the start and end dates match the manual enrolment dates from course 1
- Student 2: Is no longer suspended and has the start and end dates from course 1
- Student 3: The start date matches the manual enrolment and the end date is unlimited.
- Student 4: The start date start date not limited and continues to match the manual enrolment end date
- Student 5: The start date matches the self enrolment and the end date matches the manual enrolment
Unit tests
Run the meta enrollment unit tests against all supported databases (this will verify that the query functions).
Run the attached unit test query_test3.php
Manual tests Create two courses Course 1 Course 2 In course 1 create the following self enrolments: Student 1: with a start and end date Student 3: suspended, start and end date Student 4: no start or end date Student 5: start and end date In course 1 create the following manual enrolments: Student 1: start date set to be later than the self enrolment, end date after the self enrolment end date Student 2: suspended Student 3: start date before self enrolment, no end date Student 4: start date and end date after self enrolment end date Student 5: start date after self enrolment start, no end date Testing steps Make a note of the user enrolments in course 1, noting the earliest defined start and the most advantageous end dates for the enrolment methods that a user has Enter course 2 and create a new meta enrolment instance and set Course 1 as the child course Verify that the eligible users have been enrolled onto Course 2 and that they have the most advantageous start and end dates on the meta enrolment Go back to course 1 and make the following changes to enrolment: Student 1: suspend self enrolment Student 2: activate manual enrolment Student 3: activate self enrolment Student 4: change the start date of manual enrolment Student 5: set an end date on manual enrolment that is later than the self enrolment end date On the command line run: php enrol/meta/cli/sync.php --verbose Go to Course 2 and verify that: Student 1: the start and end dates match the manual enrolment dates from course 1 Student 2: Is no longer suspended and has the start and end dates from course 1 Student 3: The start date matches the manual enrolment and the end date is unlimited. Student 4: The start date start date not limited and continues to match the manual enrolment end date Student 5: The start date matches the self enrolment and the end date matches the manual enrolment Unit tests Run the meta enrollment unit tests against all supported databases (this will verify that the query functions). Run the attached unit test query_test3.php
Hi,
We encounter slow-queries generated by "enrol/meta/locallib.php". Executed query looks like this:
SELECT ue.userid, ue.enrolid, pue.pstatus |
FROM mdl_user_enrolments ue |
JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' ) |
JOIN (SELECT xpue.userid, xpe.courseid, MIN(xpue.status + xpe.status) AS pstatus |
FROM mdl_user_enrolments xpue |
JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta' AND xpe.enrol IN ('self','manual','guest','flatfile','database','meta','ldap','cohort')) |
GROUP BY xpue.userid, xpe.courseid |
) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid) |
WHERE (pue.pstatus = 0 AND ue.status > 0) OR (pue.pstatus > 0 and ue.status = 0) |
It runs in about 15 seconds here.
I think we could speed it with this query or something like that (which allows the use of indexes):
SELECT ue.userid, ue.enrolid, pstatus |
FROM mdl_user_enrolments ue |
JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' AND ue.status=0) |
JOIN (SELECT xpue.userid, xpe.courseid, xpue.status+xpe.status pstatus |
FROM mdl_user_enrolments xpue JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta') |
) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid) |
WHERE |
pstatus > 0
|
UNION
|
SELECT ue.userid, ue.enrolid, pstatus |
FROM mdl_user_enrolments ue |
JOIN mdl_enrol e ON (e.id = ue.enrolid AND e.enrol = 'meta' AND ue.status>0) |
JOIN (SELECT xpue.userid, xpe.courseid, xpue.status+xpe.status pstatus |
FROM mdl_user_enrolments xpue JOIN mdl_enrol xpe ON (xpe.id = xpue.enrolid AND xpe.enrol <> 'meta') |
) pue ON (pue.courseid = e.customint1 AND pue.userid = ue.userid) |
WHERE |
pstatus = 0
|
And then create indexes on mdl_user_enrolments.status and mdl_enrol.status, so it can run under one second.
Sorry if I'm wrong since I don't catch the meaning of this query...
