Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-71793

Extend check_database_schema to look for potentially missing foreign relations

Details

    • MDL-71793-add-foreign-relation-checks-to-check-database-schema
    • Hide

      1) Prior to this patch run

      php admin/cli/check_database_schema.php
      

      2) Confirm you see examples of suggestions such as:

      course
       * column 'originalcourseid' is missing an index or a foreign key to table 'course->id'
      -------------------------------------------------------------------------------
      enrol
       * column 'roleid' is missing an index or a foreign key to table 'role->id'
      -------------------------------------------------------------------------------
      scale
       * column 'userid' is missing an index or a foreign key to table 'user->id'
      -------------------------------------------------------------------------------
      scale_history
       * column 'userid' is missing an index or a foreign key to table 'user->id'
      -------------------------------------------------------------------------------
      

      Show
      1) Prior to this patch run php admin/cli/check_database_schema.php 2) Confirm you see examples of suggestions such as: course * column 'originalcourseid' is missing an index or a foreign key to table 'course->id' ------------------------------------------------------------------------------- enrol * column 'roleid' is missing an index or a foreign key to table 'role->id' ------------------------------------------------------------------------------- scale * column 'userid' is missing an index or a foreign key to table 'user->id' ------------------------------------------------------------------------------- scale_history * column 'userid' is missing an index or a foreign key to table 'user->id' -------------------------------------------------------------------------------

    Description

      The intent here is to find plugins and core code which is missing out on indexes. This should potentially also be in the CI scripts for core and moodle-plugin-ci.

      I'm not sure a perfect solution is possible, but we can add heuristics which looks for things which should have a foreign but don't. ie a simple heuristic could be any columns which contain 'id' such as userid or courseid.

      Existing policy / guideline pages:

      https://docs.moodle.org/dev/Database

      https://docs.moodle.org/dev/XMLDB_Defining_one_XML_structure#Conventions

       

      Attachments

        Issue Links

          Activity

            People

              kevinpham Kevin Pham
              brendanheywood Brendan Heywood
              Votes:
              4 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Clockify

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.