Mercurial > ~dholland > hg > swallowtail > index.cgi
comparison database/schema/messages.sql @ 54:36d91dfe017f
use valid sql syntax, mostly from yetoo on freenode
author | David A. Holland |
---|---|
date | Sun, 10 Apr 2022 17:41:24 -0400 |
parents | e1017d556437 |
children | 40f64a96481f |
comparison
equal
deleted
inserted
replaced
53:62d82881799f | 54:36d91dfe017f |
---|---|
40 CREATE SEQUENCE next_attachid; | 40 CREATE SEQUENCE next_attachid; |
41 | 41 |
42 -- for patches and mime-attachments | 42 -- for patches and mime-attachments |
43 CREATE TABLE attachments ( | 43 CREATE TABLE attachments ( |
44 id bigint primary key default nextval('next_attachid'), | 44 id bigint primary key default nextval('next_attachid'), |
45 number_in_pr bigint not null, | |
46 msgid bigint not null references messages (id), | 45 msgid bigint not null references messages (id), |
46 number_in_msg bigint not null, | |
47 mimetype text not null, | 47 mimetype text not null, |
48 body text not null | 48 body text not null |
49 ) | 49 ) |
50 WITHOUT OIDS; | 50 WITHOUT OIDS; |
51 | 51 |
52 -- Create an index for number_in_pr both to enforce uniqueness and | 52 -- Create indexes for number_in_pr and number_in_msg both to enforce |
53 -- to enable lookup. | 53 -- uniqueness and to enable lookup. |
54 CREATE UNIQUE INDEX ON messages (pr, number_in_pr); | 54 CREATE UNIQUE INDEX ON messages (pr, number_in_pr); |
55 -- XXX: does this work? | 55 CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg); |
56 CREATE UNIQUE INDEX ON | |
57 SELECT messages.pr, attachments.number_in_pr | |
58 FROM messages, attachments | |
59 WHERE messages.id = attachments.msgid | |
60 ; |