how the grade functionality was fixed

How it got broke ITFP

For some braindead reason, the SQL for getting grades out of BB originally looked like this:

and gm.date_modified > to_timestamp(?,'YYYY-MM-DD"T"HH24:MI:SS.FF3')
-- ...

That is to say, the latest timestamp in the set of all statements sent to the LRS most recently (n.b., not the timestamp of the POST request with which they were sent) gets passed to the Kettle step that fires the query at the DB, ensuring we only get statements made from data we haven't already seen.

Here's how this breaks down w.r.t. Blackboard DB tables:

• gradebook_main (gm) represents the gradeability of a single item (test, assignment, etc); ie. one row in the frontend grade center for a particular student
  • this table is relationally linked to a course content item, which is the gradeable item itself
  • the aggregation_model, which defines how the grade displayed in the frontend is ultimately retrieved and/or calculated, is recorded here
• gradebook grade (gg) represents a single grade as an abstraction
  • this table is relationally linked to gm
  • it's also relationally linked to several different attempt rows because it doesn't know which aggregation_model its gm parent is using
• attempt (att) does what it says on the tin and represents one submission of gradeable item content
  • attempts have a score field (among several other ways to record student performance) which is the actual grade

All three of these tables have a date_modified field, which requires a person to have a really thorough idea of what each table actually represents in theory if they want the above consideration to work properly.

As you can see, comparing the timestamp to gm is nigh-guaranteed to fail, because the gradeability of any given item (which gm represents) probably won't be modified after the attempt of the appropriate type (as determined by the aggregation model), and thus all rows that would otherwise match will be filtered out by the and.

The bizarre thing is that this worked for what I can only assume is a majority of cases – I once fixed a bug in the LRS that was casting all floats to ints and thus disregarding the decimal parts of grades, and the cleanup job I ran to re-generate correct 'scored' statements ended up writing multiple thousands of rows,so a lot of grades were being sent sonehow. It eventually failed enough for stakeholders to notice, though, so instead of getting the date_modified from the gm table, we're now doing this:

-- ...
left join (
  select
    iigm.pk1 as gmpk1,
    iatt.date_modified as real_date
  from bb_bb60.gradebook_main iigm 
  left join (
    select
      pk1,
      last_graded_attempt_pk1,
      first_graded_attempt_pk1,
      highest_attempt_pk1,
      lowest_attempt_pk1,
      gradebook_main_pk1
    from bb_bb60.gradebook_grade
  ) igg
  on iigm.pk1 = igg.gradebook_main_pk1
  left join (
    select 
      pk1,
      score,
      date_modified
    from bb_bb60.attempt
  ) iatt
  on (case iigm.aggregation_model
    when 1 then igg.last_graded_attempt_pk1
    when 2 then igg.highest_attempt_pk1
    when 3 then igg.lowest_attempt_pk1
    when 4 then igg.first_graded_attempt_pk1
    else null
  end) = iatt.pk1
) dat
on gg.gradebook_main_pk1 = dat.gmpk1
-- ...
and dat.real_date > to_timestamp(?,'YYYY-MM-DD"T"HH24:MI:SS.FF3')
-- ...

Incidentally, you'll hopefully notice that the new way of joining on attenpt is way more sensible than it used to be: rather than join with it four separate times, the join condition just incorporates a case/end.

Judging from the latest LRS logs, it works now. The way Tom & I solved the problem was:

• reproduce the inability to generate rows for the particular object the stakeholder mentioned
• ensure the data was actually in the DB
• confirm that everything up to but excluding the grades was being communicated to the LRS correctly
• notice the braindead way the statement timestamp was originally being generated
• add the above join to the SQL

It may or may not be desirable to refactor the code to get the datetime without a separate join, but OTOH it's modular as hell like this. YMMV.