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

Extend check_database_schema to look for potentially missing foreign relations

    XMLWordPrintable

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
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, Matteo Scaramuccia, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              4 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated: