Mercurial > ~dholland > hg > swallowtail > index.cgi
view database/schema/messages.sql @ 56:42d7888272a0 default tip
Implement fetch_classifications().
author | David A. Holland |
---|---|
date | Sun, 10 Apr 2022 19:37:18 -0400 |
parents | 40f64a96481f |
children |
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'), posttime timestamp not null, 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), number_in_pr bigint not null, who bigint not null references users (id), parent_id bigint null references messages (id), posttime timestamp 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, rawid bigint null references rawmail (id) check (parent_id != id) ) WITHOUT OIDS; CREATE SEQUENCE next_attachid; -- for patches and mime-attachments CREATE TABLE attachments ( id bigint primary key default nextval('next_attachid'), msgid bigint not null references messages (id), number_in_msg bigint not null, mimetype text not null, body text not null ) WITHOUT OIDS; -- Create indexes for number_in_pr and number_in_msg both to enforce -- uniqueness and to enable lookup. CREATE UNIQUE INDEX ON messages (pr, number_in_pr); CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg); -- The raw mail queue is append-only except to admins. GRANT SELECT, INSERT, UPDATE, DELETE ON rawmail TO swallowtail_admin; GRANT INSERT ON rawmail TO swallowtail_writer; -- Messages and attachments are generically accessible, but we don't -- allow ordinary writers to modify them. (XXX: is that silly?) GRANT SELECT, INSERT, UPDATE, DELETE ON messages TO swallowtail_admin; GRANT SELECT, INSERT, UPDATE, DELETE ON attachments TO swallowtail_admin; GRANT SELECT, INSERT ON messages TO swallowtail_writer; GRANT SELECT, INSERT ON attachments TO swallowtail_writer; GRANT SELECT ON messages, attachments TO swallowtail_reader; GRANT SELECT ON messages, attachments TO swallowtail_public;