Merge lp:~lifeless/launchpad/bugmessage into lp:launchpad/db-devel

[Robert Collins]

Permit getting bug message indices from the DB in the future.

Permit future improvements to all bug pages that show messages or attachments by removing a current rather crazy runtime analysis for message sequence.

[Stuart Bishop (stub)]

This version adds a unique constraint rather than a unique index, which I prefer as it puts a little more metadata into PG.

SET client_min_messages=ERROR;
ALTER TABLE BugMessage ADD COLUMN index integer;
ALTER TABLE BugMessage ADD CONSTRAINT bugmessage__bug__index__key UNIQUE (bug, index);
INSERT INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 14, 0);

As discussed, we need to populate this after the rollout and then land a followup patch setting the column NOT NULL.

review: Approve (db)

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2010-09-13 09:14:47 +0000
+++ database/schema/comments.sql	2010-09-16 07:03:45 +0000
@@ -1292,11 +1292,14 @@
 
 COMMENT ON TABLE ProjectBounty IS 'This table records a simple link between a bounty and a project. This bounty will be listed on the project web page, and the project will be mentioned on the bounty web page.';
 
--- Messaging subsytem
+-- BugMessages
 COMMENT ON TABLE BugMessage IS 'This table maps a message to a bug. In other words, it shows that a particular message is associated with a particular bug.';
 COMMENT ON COLUMN BugMessage.bugwatch IS 'The external bug this bug comment was imported from.';
 COMMENT ON COLUMN BugMessage.remote_comment_id IS 'The id this bug comment has in the external bug tracker, if it is an imported comment. If it is NULL while having a bugwatch set, this comment was added in Launchpad and needs to be pushed to the external bug tracker.';
 COMMENT ON COLUMN BugMessage.visible IS 'If false, the bug comment is hidden and should not be shown in any UI.';
+COMMENT ON COLUMN BugMessage.index IS 'The index (used in urls) of the message in a particular bug.';
+
+-- Messaging subsytem
 COMMENT ON TABLE Message IS 'This table stores a single RFC822-style message. Messages can be threaded (using the parent field). These messages can then be referenced from elsewhere in the system, such as the BugMessage table, integrating messageboard facilities with the rest of The Launchpad.';
 COMMENT ON COLUMN Message.parent IS 'A "parent message". This allows for some level of threading in Messages.';
 COMMENT ON COLUMN Message.subject IS 'The title text of the message, or the subject if it was an email.';

=== added file 'database/schema/patch-2208-14-0.sql'
--- database/schema/patch-2208-14-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-14-0.sql	2010-09-16 07:03:45 +0000
@@ -0,0 +1,9 @@
+SET client_min_messages=ERROR;
+
+-- Store the row index of bug messages so we don't have to calculate it all the time.
+ALTER TABLE BugMessage ADD COLUMN index integer;
+
+-- BugMessage.indexes must be unique per bug.
+ALTER TABLE BugMessage ADD CONSTRAINT bugmessage__bug__index__key UNIQUE (bug, index);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 14, 0);

=== modified file 'lib/lp/bugs/model/bugmessage.py'
--- lib/lp/bugs/model/bugmessage.py	2010-08-20 20:31:18 +0000
+++ lib/lp/bugs/model/bugmessage.py	2010-09-16 07:03:45 +0000
@@ -44,6 +44,8 @@
         notNull=False, default=None)
     remote_comment_id = StringCol(notNull=False, default=None)
     visible = BoolCol(notNull=True, default=True)
+    # -- Uncomment when deployed.
+    # index = IntCol()
 
 
 class BugMessageSet: