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

          has a non-specific relationship to

          Improvement - An enhancement to an existing Moodle feature. MDL-30799 META: Enable foreign keys in the database schema

          • Major - Major loss of function, incorrect output
          • Open
          has been marked as being related by

          Improvement - An enhancement to an existing Moodle feature. MDL-67822 Implement a check_database_schema performance check

          • Minor - Minor loss of function where workaround is possible
          • Closed
          will be (partly) resolved by

          Improvement - An enhancement to an existing Moodle feature. MDL-49795 Add missing Primary/Foreign Key relationships to core Moodle tables

          • Minor - Minor loss of function where workaround is possible
          • Closed

          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: