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

Refactor SCORM database schema to improve performance

XMLWordPrintable

    • 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

      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

        1. image-2023-07-20-08-36-51-014.png
          image-2023-07-20-08-36-51-014.png
          110 kB

            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

              Created:
              Updated:
              Resolved:

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

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