Merge lp:~gmb/launchpad/bugwatchhistory-db-bug-538091 into lp:launchpad/db-devel

[Graham Binns]

Add a BugWatchActivity table to the DB.

This branch adds a BugWatchActivity table to the database. We'll use this table to track the activity history of each bug watch. This will enable us to track failures in updates, both transient and otherwise, and will allow us to provide users with a better UI for helping to fix broken bug watches (amongst other things).

We'll create a job in garbo to prune bug watch activity older than a given time (say, a month) so as to reduce DB bloat.

[Stuart Bishop (stub)]

The naming seems a little odd. The table appears to only be logging errors, so it is more of an error log than an activity log. If it is logging successes too, then error_type and error_message are the odd names.

[Stuart Bishop (stub)]

The OOPS pruner will need to be updated when this table is being used btw. as we shouldn't be removing OOPS codes from disk that have been linked.

[Graham Binns (gmb)]

On 15 March 2010 08:22, Stuart Bishop <email address hidden> wrote:
> The naming seems a little odd. The table appears to only be logging errors, so it is more of an error log than an activity log. If it is logging successes too, then error_type and error_message are the odd names.

It's logging successes to, but when successful error_type and
error_message will be NULL.

We could rename error_message to message, but I'm not sure what we
could call error_type.

[Graham Binns (gmb)]

Hi Stuart, Jono, Bjorn (though I think Bjorn's unavailable this week),

Is there any reason this can't land? I'd like to land it before the end of 10.03 week 2 since we're sprinting on checkwatches in week 3.

[Stuart Bishop (stub)]

Land it. Leave Bjorn's pending review where it is so he can find this and catch up.

[Stuart Bishop (stub)]

> Land it. Leave Bjorn's pending review where it is so he can find this and
> catch up.

Or should I say... land it after I've reviewed it :)

[Stuart Bishop (stub)]

patch-2207-39-0.sql

I personally think 'result' is a better name for error_type, and 'message' or 'description' for error message. Or perhaps result_code and result_message.

Opinions?

review: Approve

[Graham Binns (gmb)]

On 18 March 2010 08:04, Stuart Bishop <email address hidden> wrote:
> Review: Approve
> patch-2207-39-0.sql
>
> I personally think 'result' is a better name for error_type, and 'message' or 'description' for error message. Or perhaps result_code and result_message.
>
> Opinions?

Agreed. 'result' gives us the scope to add success states, too, so we
can track partial successes (e.g. status synced but not comments and
so on), which might come in handy.

Thanks!

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2010-02-25 20:30:56 +0000
+++ database/schema/comments.sql	2010-03-18 09:02:47 +0000
@@ -321,6 +321,16 @@
 COMMENT ON COLUMN BugWatch.remote_lp_bug_id IS 'The bug in Launchpad that the remote bug is pointing at. This can be different than the BugWatch.bug column, since the same remote bug can be linked from multiple bugs in Launchpad, but the remote bug can only link to a single bug in Launchpad. The main use case for this column is to avoid having to query the remote bug tracker for this information, in order to decide whether we need to give this information to the remote bug tracker.';
 
 
+-- BugWatchActivity
+
+COMMENT ON TABLE BugWatchActivity IS 'This table contains a record of each update for a given bug watch. This allows us to track whether a given update was successful or not and, if not, the details of the error which caused the update to fail.';
+COMMENT ON COLUMN BugWatchActivity.bug_watch IS 'The bug_watch to which this activity entry relates.';
+COMMENT ON COLUMN BugWatchActivity.activity_date IS 'The datetime at which the activity occurred.';
+COMMENT ON COLUMN BugWatchActivity.result IS 'The result of the update. Legal values are defined in the BugWatchErrorType enumeration. An update is considered successful if its error_type is NULL.';
+COMMENT ON COLUMN BugWatchActivity.message IS 'The message (if any) associated with the update.';
+COMMENT ON COLUMN BugWatchActivity.oops_id IS 'The OOPS id, if any, associated with the error that caused the update to fail.';
+
+
 -- BugAffectsPerson
 
 COMMENT ON TABLE BugAffectsPerson IS 'This table maintains a mapping between bugs and users indicating that they are affected by that bug. The value is calculated and cached in the Bug.users_affected_count column.';

=== added file 'database/schema/patch-2207-39-0.sql'
--- database/schema/patch-2207-39-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-39-0.sql	2010-03-18 09:02:47 +0000
@@ -0,0 +1,16 @@
+SET client_min_messages=ERROR;
+
+CREATE TABLE BugWatchActivity(
+    id serial NOT NULL PRIMARY KEY,
+    bug_watch integer NOT NULL REFERENCES BugWatch(id),
+    activity_date timestamp without time zone
+        DEFAULT timezone('UTC'::text, now()) NOT NULL,
+    result integer,
+    message text,
+    oops_id text
+);
+
+CREATE INDEX bugwatchactivity__date__idx ON BugWatchActivity(activity_date);
+CREATE INDEX bugwatchactivity__bug_watch__idx ON BugWatchActivity(bug_watch);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 39, 0);