456530 - Add triggers to recalculate test run averages

Status: RESOLVED WONTFIX

(Webtools Graveyard :: Graph Server, defect)

Description

[Ryan Doherty (:rdoherty)]

The new db schema needs triggers to recalculate test run averages.

Comment 1

[Mark Smith [:xb95]]

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.

Comment 2

[K Lars Lohn [:lars] [:klohn]]

under the new schema and new data submission technique, this won't be necessary.