Student Success Plan
  1. Student Success Plan
  2. SSP-1766

Add discriminator to external_student_test natural key

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: SSP 2.0.0, SSP 2.1.0, SSP 2.0.1
    • Fix Version/s: SSP 2.1.0, SSP 2.0.1
    • Component/s: Database, External Data
    • Labels:
      None

      Description

      From the field:

      External_student_test - you qualify a duplicate as someone who has taken the same test multiple times on the same date. Unfortunately, this is a completely valid scenario on our institution. There are approximately 3000 such cases of students taking the same test (often with different grades) on the same day.

      Currently we want a key on external_student_test of:

      school_id,test_code,sub_test_code,test_date

      Sounds like we need to add a discriminator to that. Thinking it could be a single-char column, defaulted to '1'.

      The changeset/s would need to split up the introduction of that column and the re-creation of the key so implementors have the opportunity to update their datasets. I.e. restart with natural keys disabled, get the new column, fix that column via updates to external data feeds, restart with natural key/s enabled.

        Issue Links

          Activity

          Hide
          Dan McCallum added a comment -

          Both Wins CI and Linux CI are running what appears to be working changesets for this. I sent a note to ssp-dev b/c it likely broke dev envs.

          I ended up moving away from both the in-Java generators and temp table approaches for reasons outlined in i000050.xml:

          Went with inline recreate of the keys/constraints rather than a temp table
          for a couple reasons:

          • Should be a bit faster. Creating the key again can take some time,
            but at least we don't need to copy the data.
          • Less chance of exhausting db resources in the event this table
            is very large.
          • No chance of nuking local out of band customizations to the table.

          Since we were able to do the inline recreate without iteration logic, it
          seemed fine to just go ahead and inline the raw SQL here rather than code
          up a custom 'generator' in Java.

          Show
          Dan McCallum added a comment - Both Wins CI and Linux CI are running what appears to be working changesets for this. I sent a note to ssp-dev b/c it likely broke dev envs. I ended up moving away from both the in-Java generators and temp table approaches for reasons outlined in i000050.xml : Went with inline recreate of the keys/constraints rather than a temp table for a couple reasons: Should be a bit faster. Creating the key again can take some time, but at least we don't need to copy the data. Less chance of exhausting db resources in the event this table is very large. No chance of nuking local out of band customizations to the table. Since we were able to do the inline recreate without iteration logic, it seemed fine to just go ahead and inline the raw SQL here rather than code up a custom 'generator' in Java.
          Hide
          Jason Elwood added a comment -

          Tested on Sinclair's dev environment

          Show
          Jason Elwood added a comment - Tested on Sinclair's dev environment
          Hide
          Dan McCallum added a comment -

          Reopening to add 2.0.1 as a fix version

          Show
          Dan McCallum added a comment - Reopening to add 2.0.1 as a fix version
          Hide
          Dan McCallum added a comment -

          Fixed in rel-2-0-patches (2.0.1). For that merge I ended up cherry-picking and squashing all the related commits in to dc4152557982fe4c04e482e28fac8539327b4894. That commit log lists out all the combined commits.

          I'm leaving the merge-to-2-0 label since I'm the only one who has tested this.

          Show
          Dan McCallum added a comment - Fixed in rel-2-0-patches (2.0.1). For that merge I ended up cherry-picking and squashing all the related commits in to dc4152557982fe4c04e482e28fac8539327b4894. That commit log lists out all the combined commits. I'm leaving the merge-to-2-0 label since I'm the only one who has tested this.
          Hide
          Jason Elwood added a comment -

          Removing label

          Show
          Jason Elwood added a comment - Removing label

            People

            • Assignee:
              Dan McCallum
              Reporter:
              Dan McCallum
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: