view database/schema/messages.sql @ 36:baf976e34f16

regen pdf now that my tex is working again
author David A. Holland
date Sun, 15 Jun 2014 17:13:39 -0400
parents cd36b49f4437
children edf081d0b282
line wrap: on
line source

--
-- Messages.
--

CREATE SEQUENCE next_rawmsgid;

-- all incoming mail in original form, for reference
-- (should be pruned periodically)
CREATE TABLE rawmail (
	id bigint		primary key default nextval('next_rawmsgid'),
	data text		not null
)
WITHOUT OIDS;

CREATE SEQUENCE next_msgid;

-- comments
CREATE TABLE messages (
	id bigint		primary key default nextval('next_msgid'),
	pr bigint		not null references prs (id),
	who bigint		not null references users (id),
	parent_id bigint	null references messages (id),
	posttime timestamp	not null,
	body text		not null,

	-- we don't keep these directly, they go into an admin log entry
	--from_address text	not null,
	--mail_subject text	not null,
	--message_id text	not null,
	rawid bigint		null references rawmail (id)

	check (parent_id != id)
)
WITHOUT OIDS;

-- for patches and mime-attachments
-- if msgid is null, attachment came with original PR
CREATE TABLE attachments (
	pr bigint		not null references PRs (id),
	msgid bigint		null references messages (id),
	mimetype text		not null,
	body text		not null
)
WITHOUT OIDS;

-- intended constraint:
--   SELECT messages.pr, attachments.pr
--   FROM messages, attachments
--   WHERE messages.id = attachments.msgid AND messages.pr <> attachments.pr
-- should always be empty.
-- (XXX this is gross.)