swallowtail bug database WIP stuff: database/schema/messages.sql annotate

annotate database/schema/messages.sql @ 44:812c956dd0e9

Add another admin queue type for comments on nonexistent PRs. Also, correct stupid spelling mistake. Need to get the test harness running again.

author	David A. Holland
date	Mon, 16 Jun 2014 01:27:45 -0400
parents	e1017d556437
children	36d91dfe017f

rev	line source
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	1 --
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	2 -- Messages.
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	3 --
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	4
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	5 CREATE SEQUENCE next_rawmsgid;
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	6
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	7 -- all incoming mail in original form, for reference
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	8 -- (should be pruned periodically)
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	9 CREATE TABLE rawmail (
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	10 id bigint primary key default nextval('next_rawmsgid'),
40 44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	11 posttime timestamp not null,
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	12 data text not null
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	13 )
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	14 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	15
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	16 CREATE SEQUENCE next_msgid;
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	17
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	18 -- comments
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	19 CREATE TABLE messages (
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	20 id bigint primary key default nextval('next_msgid'),
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	21 pr bigint not null references prs (id),
40 44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	22 number_in_pr bigint not null,
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	23 who bigint not null references users (id),
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	24 parent_id bigint null references messages (id),
29 cd36b49f4437 fix. David A. Holland parents: 13 diff changeset	25 posttime timestamp not null,
40 44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	26 mimetype text not null,
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	27 body text not null,
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	28
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	29 -- we don't keep these directly, they go into an admin log entry
40 44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	30 -- XXX: we may need to keep the external message-id
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	31 --from_address text not null,
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	32 --mail_subject text not null,
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	33 --message_id text not null,
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	34 rawid bigint null references rawmail (id)
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	35
13 5bf0d6f732c1 fix thinko. David A. Holland parents: 12 diff changeset	36 check (parent_id != id)
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	37 )
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	38 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	39
40 44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	40 CREATE SEQUENCE next_attachid;
44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	41
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	42 -- for patches and mime-attachments
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	43 CREATE TABLE attachments (
40 44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	44 id bigint primary key default nextval('next_attachid'),
44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	45 number_in_pr bigint not null,
44c1509055c7 improvements in message handling David A. Holland parents: 37 diff changeset	46 msgid bigint not null references messages (id),
8 68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	47 mimetype text not null,
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	48 body text not null
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	49 )
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	50 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility. David A. Holland parents: diff changeset	51
42 e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	52 -- Create an index for number_in_pr both to enforce uniqueness and
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	53 -- to enable lookup.
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	54 CREATE UNIQUE INDEX ON messages (pr, number_in_pr);
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	55 -- XXX: does this work?
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	56 CREATE UNIQUE INDEX ON
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	57 SELECT messages.pr, attachments.number_in_pr
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	58 FROM messages, attachments
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	59 WHERE messages.id = attachments.msgid
e1017d556437 add unique indexes on number_in_pr David A. Holland parents: 40 diff changeset	60 ;

Mercurial > ~dholland > hg > swallowtail > index.cgi

annotate database/schema/messages.sql @ 44:812c956dd0e9