Issue/Bug

When deleting a category that has associated competency frameworks and/or user learning plan templates those frameworks/learning plans are 'orphaned' against the original category context.

This then results in a) errors occurring when trying to view those items on user profiles and b) the items simply disappearing from the main competency administration area.

Specific Error

The specific error is:

Can't find data record in database table context.

SELECT * FROM {context} WHERE id = ? [array (0 => XXX,)]

Error code: invalidrecord

This is the low level DB get error because the context held against the framework or learning plan no longer exists in the database.

Moodle Versions

Identified in v4.1 and upwards.

Steps to Reproduce

1. Ensure competencies are enabled
   Site Administration > Advanced Features > Enable Competencies
2. Create a new test category and navigate to it
3. Create a new learning plan template and add users
   1. Category > More (secondary nav dropdown) > Learning Plan Templates
   2. 'Add New Learning Plan Template', complete form, Save Changes
   3. 'Add Competencies to Learning Plan Template', add competencies, return to the category Learning Plan page
   4. Select the '0' in the template 'Learning Plans' column to assign users
   5. Add some user learning plans to the learning plan template
4. Create a new competency framework
   1. Category > More (secondary nav dropdown) > Competency Frameworks
   2. 'Add New Competency Framework', complete form, Save Changes
5. To facilitate testing the issue, in new browser tabs/windows, open the following pages:
   1. For one of the users assigned to the learning plan: User Profile > Learning Plans. Confirm that the Learning Plan is displayed
   2. Site Administration > Competencies > Competency Learning Plan Templates. Confirm that the created learning plan appears in the list against the test category.
   3. Site Administration > Competencies > Competency Frameworks. Confirm that the created framework appears in the list against the test category.
6. Return to the test category main page
7. Delete the test category using the 'Manage Courses' interface in the 'More' actions menu
8. Refresh your test browser tabs/windows.
   1. The user profile based page will display an error
   2. The Site Administration page tables will be missing the learning plan template and competency framework (although they still exist in the database) but no error will occur

Possible Solutions

1. Warn and/or prevent the deletion of categories that have associated frameworks and/or plans
2. Simply ensure that any frameworks and/or plans are deleted when the associated category is deleted*
3. Move/re-assign any such frameworks/plans to another category (e.g. the immediate parent category) when the associated category is deleted**

* Similar to the automatic deletion of user grades against courses which occurs when deleting a category, however it could be argued that competency information is less visible/prominent. A list of affected users could be displayed on the final confirm deletion page

** This is the approach we have used to fix affected frameworks/plans on our own site, albeit by updating the database directly by hand (see below).

Code Inspection

course/classes/category.php : public function delete_full()

Looking through the function above responsible for category deletion we see that a number of context related activities are carried out, just none associated with the competency framework or learning plan template context links. There is even a comment to this affect Now delete anything that may depend on course category context. The actions which are carried out:

• delete child categories
• delete courses in the category context
• delete (or move) cohorts in the category context
• grade_course_category_delete() - grade letters
• content_bank->delete_contents() - content items by type
• question_delete_course_category() - deletes all course questions and question categories

The code also provides a number of hooks etc. that may be useful in any fix for this issue as follows:

• Before deletion the user's permission to delete a category is checked using

  can_delete_full()

• There is a check for any plugin callback functions against the following hook

  pre_course_category_delete

• The main category deletion (and a number of the sub-steps above) create and trigger various events which could be used

Identify Issue Instances and Database Fix

To fix the issue after it has occurred (by re-assigning frameworks/plans to another category):

1. Identify a new category context - simplest way is to navigate to the category 'Competency Frameworks' page and using the `pagecontextid` URL parameter
2. Identify the affected frameworks/plan templates using the queries below
3. Run the following SQL query substituting the IDs identified above

UPDATE mdl_competency_template SET contextid = <ctx> WHERE id IN (<x>,<x>,<x>);

UPDATE mdl_competency_framework SET contextid = <ctx> WHERE id IN (<x>,<x>,<x>);

The following SQL queries can be used to identify instances of this issue in the database:

-- Identify competency frameworks against non-existing category contexts.

SELECT

    f.id AS frameworkid,

    f.shortname AS frameworkname,

    f.contextid AS contextid

FROM

    mdl_competency_framework AS f

WHERE

    f.contextid NOT IN (SELECT id FROM mdl_context)

    -- OR 1=1

;

-- Identify templates against non-existing category contexts.

SELECT

    t.id AS templateid,

    t.shortname AS templatename,

    t.contextid AS contextid,

    COUNT(p.id) AS countplans,

    GROUP_CONCAT(p.id) AS planids,

    COUNT(u.id) AS countusers,

    GROUP_CONCAT(u.id) AS userids,

    p.origtemplateid,

    ot.shortname AS origtemplatename

FROM

    mdl_competency_template AS t

    LEFT JOIN mdl_competency_plan AS p ON p.templateid = t.id

    LEFT JOIN mdl_competency_template AS ot ON ot.id = p.origtemplateid

    LEFT JOIN mdl_user AS u ON u.id = p.userid

WHERE

    t.contextid NOT IN (SELECT id FROM mdl_context)

    -- OR 1=1

GROUP BY

    t.id

;

-- Identify user plans based on templates against non-existing category contexts.

SELECT

    p.id AS planid,

    p.name AS plan,

    t.contextid AS contextid,

    p.templateid,

    t.shortname AS templatename,

    p.origtemplateid,

    ot.shortname AS origtemplatename,

    u.id AS userid,

    CONCAT(u.lastname, ', ', u.firstname) AS user

FROM

    mdl_competency_plan AS p

    LEFT JOIN mdl_competency_template AS t ON t.id = p.templateid

    LEFT JOIN mdl_competency_template AS ot ON ot.id = p.origtemplateid

    LEFT JOIN mdl_user AS u ON u.id = p.userid

WHERE

    t.contextid NOT IN (SELECT id FROM mdl_context)

    -- OR 1=1

;