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

Refactor SCORM database schema to improve performance

    XMLWordPrintable

Details

    • MOODLE_28_STABLE, MOODLE_401_STABLE
    • MOODLE_403_STABLE
    • Hide

      Behat and unit tests do some pretty good testing, but we should also do a bunch of other testing - in particular doing the same tests as performed by the following MDLQA tests (see below after upgrade has been done).

      Further tests to check both performance changes and backup/restore process below.

      Before the patch is applied:

      1. Enable Performance info in the site footer under Admin > Development > debugging > "Performance info"
      2. Restore the supplied course backup file into a course in your testing site.
      3. Enter the "SCORM package test" scorm contained within the restored course.
      4. Click on the "reports" tab which will then show the list of SCORM attempts.
      5. At the bottom of the page look for the "DB queries time" value in the performance info printed in the footer of the page and record the value for comparison in the next # step.

      Now apply the patch and upgrade the testing site.

      1. Create a new course in your testing site using the same backup file as supplied earlier. (this tests the restore process).
      2. Enter the "SCORM package test" scorm contained within the restored course.
      3. Click on the "reports" tab which will then show the list of SCORM attempts
      4. check to make sure the "DB queries time" shown at the bottom of the page is lower than the value recorded previously.
      5. Check to make sure the attempts in this restored course shows the same data as the previously restored course. Check a user and ensure that when clicking on the "track details" link you see the same information on the page in this new course as in the course first restored in the site.

      With the patch applied::

      MDLQA-17418 - progress tracking test
      MDLQA-17005 - interactions report test
      MDLQA-17600 - Graph report test

      Show
      Behat and unit tests do some pretty good testing, but we should also do a bunch of other testing - in particular doing the same tests as performed by the following MDLQA tests (see below after upgrade has been done). Further tests to check both performance changes and backup/restore process below. Before the patch is applied: Enable Performance info in the site footer under Admin > Development > debugging > "Performance info" Restore the supplied course backup file into a course in your testing site. Enter the "SCORM package test" scorm contained within the restored course. Click on the "reports" tab which will then show the list of SCORM attempts. At the bottom of the page look for the "DB queries time" value in the performance info printed in the footer of the page and record the value for comparison in the next # step. Now apply the patch and upgrade the testing site. Create a new course in your testing site using the same backup file as supplied earlier. (this tests the restore process). Enter the "SCORM package test" scorm contained within the restored course. Click on the "reports" tab which will then show the list of SCORM attempts check to make sure the "DB queries time" shown at the bottom of the page is lower than the value recorded previously. Check to make sure the attempts in this restored course shows the same data as the previously restored course. Check a user and ensure that when clicking on the "track details" link you see the same information on the page in this new course as in the course first restored in the site. With the patch applied:: MDLQA-17418 - progress tracking test MDLQA-17005 - interactions report test MDLQA-17600 - Graph report test

    Description

      The current structure of the scorm_scoes_track table:

      scorm_scoes_track:
      id | userid | scormid | scoid | attempt | element | value | timemodified

      is problematic for reporting purposes because there is no id defining a single attempt so you need to group by userid, scormid, scoid and attempt to extract that info.

      Also the indexes on this table can become huge because it is not normalised properly.

      We propose splitting into separate tables:

      scorm_scoes_track:
      id | attemptid | elementid | value | timemodified

      scorm_scoes_attempt:
      id | userid | scormid | scoid | attempt

      scorm_scoes_element:
      id | element

      Attachments

        Issue Links

          Activity

            People

              danmarsden Dan Marsden
              simoncoggins Simon Coggins
              Jordi Pujol-Ahulló Jordi Pujol-Ahulló
              Ilya Tregubov Ilya Tregubov
              Jake Dallimore Jake Dallimore
              Votes:
              9 Vote for this issue
              Watchers:
              25 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 5 hours, 5 minutes
                  1d 5h 5m

                  Clockify

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