# HG changeset patch
# User David A. Holland
# Date 1649626884 14400
# Node ID 36d91dfe017fd6567c168ce70a4c78dd3c49baae
# Parent  62d82881799f18d05d56a14170098367dedd0d2a
use valid sql syntax, mostly from yetoo on freenode

diff -r 62d82881799f -r 36d91dfe017f database/schema/admin.sql
--- a/database/schema/admin.sql	Sat Apr 02 21:15:27 2022 -0400
+++ b/database/schema/admin.sql	Sun Apr 10 17:41:24 2022 -0400
@@ -13,7 +13,7 @@
 
 CREATE TABLE adminmailtypes (
 	type text		primary key,
-	desc text
+	description text	not null
 )
 WITHOUT OIDS;
 
@@ -30,6 +30,6 @@
 	rawmsg bigint		not null references rawmail,
 	type text		not null references adminmailtypes,
 	pr bigint		null references PRs,
-	user bigint		null references users
+	userid bigint		null references users
 )
 WITHOUT OIDS;
diff -r 62d82881799f -r 36d91dfe017f database/schema/bugs.sql
--- a/database/schema/bugs.sql	Sat Apr 02 21:15:27 2022 -0400
+++ b/database/schema/bugs.sql	Sun Apr 10 17:41:24 2022 -0400
@@ -35,7 +35,7 @@
 	-- should be nulled.
 	timeout_date timestamp	null,
 	timeout_state text	null references states (name),
-	check (timestamp is null == timeout_state is null),
+	check ((timeout_date is null) = (timeout_state is null)),
 
 	-- intended constraint:
 	-- select * from PRs, states where PRs.state = states.name
diff -r 62d82881799f -r 36d91dfe017f database/schema/messages.sql
--- a/database/schema/messages.sql	Sat Apr 02 21:15:27 2022 -0400
+++ b/database/schema/messages.sql	Sun Apr 10 17:41:24 2022 -0400
@@ -42,19 +42,14 @@
 -- for patches and mime-attachments
 CREATE TABLE attachments (
 	id bigint		primary key default nextval('next_attachid'),
-	number_in_pr bigint	not null,
 	msgid bigint		not null references messages (id),
+	number_in_msg bigint	not null,
 	mimetype text		not null,
 	body text		not null
 )
 WITHOUT OIDS;
 
--- Create an index for number_in_pr both to enforce uniqueness and
--- to enable lookup.
+-- 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);
--- XXX: does this work?
-CREATE UNIQUE INDEX ON
-  SELECT messages.pr, attachments.number_in_pr
-  FROM messages, attachments
-  WHERE messages.id = attachments.msgid
-;
+CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg);