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

Extend check_database_schema to look for potentially missing foreign relations

    XMLWordPrintable

    Details

    • Pull from Repository:
    • Pull Master Branch:
      MDL-71793-add-foreign-relation-checks-to-check-database-schema
    • Testing Instructions:
      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

              Assignee:
              kevinpham Kevin Pham
              Reporter:
              brendanheywood Brendan Heywood
              Participants:
              Component watchers:
              Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Matteo Scaramuccia, Andrew Lyons, Dongsheng Cai, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated: