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

Extend check_database_schema to look for potentially missing foreign relations

XMLWordPrintable

    • 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' -------------------------------------------------------------------------------

      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

       

        1. db-schema-checks.txt
          12 kB

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

              Created:
              Updated:

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