Closed Bug 456530 Opened 11 years ago Closed 11 years ago

Add triggers to recalculate test run averages

Categories

(Webtools Graveyard :: Graph Server, defect)

defect
Not set

Tracking

(Not tracked)

RESOLVED WONTFIX

People

(Reporter: rdoherty, Assigned: lars)

References

Details

The new db schema needs triggers to recalculate test run averages.
Assignee: nobody → mark
Triggers can only run on per-row inserts, which means if we insert 20 rows, we will recalculate the average 20 times.  This is probably okay, as the trigger ensures that we always have a good average, but I wanted to mention it.

The actual trigger... something like this seems to work in my tests:

CREATE TRIGGER update_averages_on_insert
  AFTER INSERT ON test_run_values
  FOR EACH ROW
    UPDATE test_runs SET average = (
        SELECT AVG(value) FROM test_run_values WHERE test_run_id = id
    ) WHERE id = NEW.test_run_id;

CREATE TRIGGER update_averages_on_update
  AFTER UPDATE ON test_run_values
  FOR EACH ROW
    UPDATE test_runs SET average = (
        SELECT AVG(value) FROM test_run_values WHERE test_run_id = id
    ) WHERE id = NEW.test_run_id;

Yes, you need both... well, assuming we're ever going to update test_run_values.  If we can guarantee we're never going to do an UPDATE on that table, then you can get away with just the first trigger.
Assignee: mark → lars
under the new schema and new data submission technique, this won't be necessary.
Status: NEW → RESOLVED
Closed: 11 years ago
Resolution: --- → WONTFIX
Product: Webtools → Webtools Graveyard
You need to log in before you can comment on or make changes to this bug.