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

Fix indexes on question_references and question_set_references

XMLWordPrintable

    • MOODLE_400_STABLE
    • MOODLE_400_STABLE
    • master_MDL-73763_index-fix
    • Hide

      +Test:
      +

      1. Stand up a moodle site using master branch
      2. Create a quiz 
      3. Add a few questions in the question bank
      4. Add questions to quiz
      5. Check in database that question_references table has question previously added
      6. Add a random question to quiz
      7. Check in database that question_set_references table has random question previously added 
      8. Update code base to using this branch
      9. Ensure no error exists during upgrade, that questions are present in quiz and that tables are filled with same questions

      10. Check the database for question_references and question_set_references table and confirm that usingcontextid-component-questionarea-itemid is added. So for pgsql for example in postgres console you can run smth like 

      \d TABBLENAME

       

      Show
      +Test: + 1. Stand up a moodle site using master branch 2. Create a quiz  3. Add a few questions in the question bank 4. Add questions to quiz 5. Check in database that question_references table has question previously added 6. Add a random question to quiz 7. Check in database that question_set_references table has random question previously added  8. Update code base to using this branch 9. Ensure no error exists during upgrade, that questions are present in quiz and that tables are filled with same questions 10. Check the database for question_references and question_set_references table and confirm that usingcontextid-component-questionarea-itemid is added. So for pgsql for example in postgres console you can run smth like \d TABBLENAME  

      Currently, for question_references we have

      <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      		 
      <KEY NAME="usingcontextid" TYPE="foreign" FIELDS="usingcontextid" REFTABLE="context" REFFIELDS="id"/>
      		 
      <KEY NAME="questionbankentryid" TYPE="foreign" FIELDS="questionbankentryid" REFTABLE="question_bank_entries" REFFIELDS="id"/>

      Those are right, but for performance/correctness, there should also be a unique index on
      (usingcontextid, component, questionarea, itemid)

      Arguably, there should also be a foreign key of (questionbankentryid, version) pointing at question_versions.

      And for question_set_references

      <KEY NAME="primary" TYPE="primary" FIELDS="id"/>
      		 
      <KEY NAME="usingcontextid" TYPE="foreign" FIELDS="usingcontextid" REFTABLE="context" REFFIELDS="id"/>
      		 
      <KEY NAME="questionscontextid" TYPE="foreign" FIELDS="questionscontextid" REFTABLE="context" REFFIELDS="id"/>

      Those are right, but for performance/correctness, there should also be a unique index on
      (usingcontextid, component, questionarea, itemid)

        1. MDL-73763_3.png
          MDL-73763_3.png
          101 kB
        2. MDL-73763_2.png
          MDL-73763_2.png
          44 kB
        3. MDL-73763_1.png
          MDL-73763_1.png
          47 kB

            safatshahin Safat Shahin
            timhunt Tim Hunt
            Tim Hunt Tim Hunt
            Ilya Tregubov Ilya Tregubov
            Angelia Dela Cruz Angelia Dela Cruz
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 4 hours, 20 minutes
                4h 20m

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