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

annotate database/schema/messages.sql @ 55:40f64a96481f

Adjust database user arrangements and grant schema privs.

author	David A. Holland
date	Sun, 10 Apr 2022 19:36:29 -0400
parents	36d91dfe017f
children

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 msgid bigint not null references messages (id),
54 36d91dfe017f use valid sql syntax, mostly from yetoo on freenode David A. Holland parents: 42 diff changeset	46 number_in_msg bigint not null,
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
54 36d91dfe017f use valid sql syntax, mostly from yetoo on freenode David A. Holland parents: 42 diff changeset	52 -- Create indexes for number_in_pr and number_in_msg both to enforce
36d91dfe017f use valid sql syntax, mostly from yetoo on freenode David A. Holland parents: 42 diff changeset	53 -- uniqueness and to enable lookup.
42 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);
54 36d91dfe017f use valid sql syntax, mostly from yetoo on freenode David A. Holland parents: 42 diff changeset	55 CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg);
55 40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	56
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	57 -- The raw mail queue is append-only except to admins.
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	58 GRANT SELECT, INSERT, UPDATE, DELETE ON rawmail TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	59 GRANT INSERT ON rawmail TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	60
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	61 -- Messages and attachments are generically accessible, but we don't
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	62 -- allow ordinary writers to modify them. (XXX: is that silly?)
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	63 GRANT SELECT, INSERT, UPDATE, DELETE ON messages TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	64 GRANT SELECT, INSERT, UPDATE, DELETE ON attachments TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	65 GRANT SELECT, INSERT ON messages TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	66 GRANT SELECT, INSERT ON attachments TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	67 GRANT SELECT ON messages, attachments TO swallowtail_reader;
40f64a96481f Adjust database user arrangements and grant schema privs. David A. Holland parents: 54 diff changeset	68 GRANT SELECT ON messages, attachments TO swallowtail_public;

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

annotate database/schema/messages.sql @ 55:40f64a96481f