# HG changeset patch
# User David A. Holland
# Date 1402869251 14400
# Node ID 44c1509055c763075037cc8908b9942f61854bbd
# Parent  45bb7b516edb9f22f4bd345706bc03ebcbdc6f6b
improvements in message handling

diff -r 45bb7b516edb -r 44c1509055c7 database/schema/messages.sql
--- a/database/schema/messages.sql	Sun Jun 15 17:28:02 2014 -0400
+++ b/database/schema/messages.sql	Sun Jun 15 17:54:11 2014 -0400
@@ -8,6 +8,7 @@
 -- (should be pruned periodically)
 CREATE TABLE rawmail (
 	id bigint		primary key default nextval('next_rawmsgid'),
+	posttime timestamp	not null,
 	data text		not null
 )
 WITHOUT OIDS;
@@ -18,13 +19,15 @@
 CREATE TABLE messages (
 	id bigint		primary key default nextval('next_msgid'),
 	pr bigint		not null references prs (id),
+	number_in_pr bigint	not null,
 	who bigint		not null references users (id),
 	parent_id bigint	null references messages (id),
 	posttime timestamp	not null,
-	contenttype text	not null,
+	mimetype text		not null,
 	body text		not null,
 
 	-- we don't keep these directly, they go into an admin log entry
+	-- XXX: we may need to keep the external message-id
 	--from_address text	not null,
 	--mail_subject text	not null,
 	--message_id text	not null,
@@ -34,20 +37,15 @@
 )
 WITHOUT OIDS;
 
+CREATE SEQUENCE next_attachid;
+
 -- 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),
+	id bigint		primary key default nextval('next_attachid'),
+	number_in_pr bigint	not null,
+	msgid bigint		not 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.)
-