# HG changeset patch
# User David A. Holland
# Date 1369625085 14400
# Node ID cd36b49f443751f20132fcfd92fd70c92f2bd9f5
# Parent  000519947282632cdbdbf0956b815a1db9ff763a
fix.
now loads into postgresql92

diff -r 000519947282 -r cd36b49f4437 database/schema/bugs.sql
--- a/database/schema/bugs.sql	Sun May 26 22:13:33 2013 -0400
+++ b/database/schema/bugs.sql	Sun May 26 23:24:45 2013 -0400
@@ -15,7 +15,7 @@
 	confidential boolean	not null,
 
 	-- states
-	state text		not null references states (name);
+	state text		not null references states (name),
 	locked boolean		not null,	-- deny modifications
 
 	-- intended constraint:
@@ -49,4 +49,5 @@
 	fix text		,
 	unformatted text	
 
-) without oids;
+)
+WITHOUT OIDS;
diff -r 000519947282 -r cd36b49f4437 database/schema/classify.sql
--- a/database/schema/classify.sql	Sun May 26 22:13:33 2013 -0400
+++ b/database/schema/classify.sql	Sun May 26 23:24:45 2013 -0400
@@ -126,13 +126,22 @@
 	value text		,
 	primary key (pr, scheme),
 	--
-	-- what I want here is for it to reference hierclass_values unless
-	-- the value is null. or should the values table include a row
-	-- with null value where that's allowed? I forget how null values
-	-- interoperate with reference constraints except that it's messy.
-	-- XXX. (if changing this change the similar logic below as well)
+	-- Ok, the intended semantics here are:
+	--    * For PRs that the classification doesn't apply to, there
+	--      should be no row at all in the *class_data table.
+	--    * For PRs where the classification is undetermined or
+	--      unassigned, the value column should be null, and if
+	--      that's allowed there should be a null entry in
+	--      the *class_values table.
+	-- I believe "MATCH SIMPLE" to be capable of enforcing this,
+	-- but that's from reading postgres docs and not from actually
+	-- checking it. From the description, "MATCH FULL" will do the
+	-- wrong thing, and "MATCH PARTIAL" isn't supported.
 	--
-	constraint (scheme, value) references hierclass_values (scheme, value)
+	foreign key (scheme, value) references hierclass_values (scheme, value)
+		MATCH FULL
+		ON DELETE RESTRICT
+		ON UPDATE RESTRICT
 )
 WITHOUT OIDS;
 
@@ -166,7 +175,10 @@
 	value text		,
 	primary key (pr, scheme),
 	-- as above
-	constraint (scheme, value) references flatclass_values (scheme, value)
+	foreign key (scheme, value) references flatclass_values (scheme, value)
+		MATCH FULL
+		ON DELETE RESTRICT
+		ON UPDATE RESTRICT
 )
 WITHOUT OIDS;
 
@@ -187,7 +199,7 @@
 	pr bigint		not null references PRs (id),
 	scheme text		not null references textclass_names (name),
 	value text		,
-	primary key (pr, scheme),
+	primary key (pr, scheme)
 )
 WITHOUT OIDS;
 
@@ -222,16 +234,25 @@
 	scheme text		not null references tagclass_names (name),
 	value text		,
 	-- as above
-	constraint (scheme, value) references tagclass_values (scheme, value)
+	foreign key (scheme, value) references tagclass_values (scheme, value)
+		MATCH FULL
+		ON DELETE RESTRICT
+		ON UPDATE RESTRICT
 )
 WITHOUT OIDS;
 
 -- view where tags are collected into a string
--- XXX does concat(value) work, and if not, is there any way to do this?
+-- string_agg() concatenates the value strings
 CREATE VIEW tagclass_stringdata AS
-   SELECT pr, scheme, concat(value) as value
-   FROM tagclass_data
-   GROUP BY pr, scheme
+   SELECT pr,
+	  tagclass_data.scheme as scheme,
+	  string_agg(tagclass_data.value, ' '
+			ORDER BY tagclass_values.ordering) as value
+   FROM tagclass_data, tagclass_values
+   WHERE
+	tagclass_data.scheme = tagclass_values.scheme
+    AND tagclass_data.value = tagclass_values.value
+   GROUP BY pr, tagclass_data.scheme
 ;
 
 ------------------------------------------------------------
@@ -251,7 +272,7 @@
           hierclass_data.scheme as scheme,
           hierclass_names.ordering as schemeordering,
           hierclass_data.value as value,
-          hierclass_values.ordering as valueordering,
+          hierclass_values.ordering as valueordering
    FROM hierclass_data, hierclass_names, hierclass_values
    WHERE
         hierclass_data.scheme = hierclass_names.name
@@ -264,7 +285,7 @@
           flatclass_data.scheme as scheme,
           flatclass_names.ordering as schemeordering,
           flatclass_data.value as value,
-          flatclass_values.ordering as valueordering,
+          flatclass_values.ordering as valueordering
    FROM flatclass_data, flatclass_names, flatclass_values
    WHERE
         flatclass_data.scheme = flatclass_names.name
@@ -291,7 +312,7 @@
           textclass_data.scheme as scheme,
           textclass_names.ordering as schemeordering,
           textclass_data.value as value,
-          1 as valueordering,
+          1 as valueordering
    FROM textclass_data, textclass_names
    WHERE
         textclass_data.scheme = textclass_names.name
@@ -302,7 +323,7 @@
           tagclass_data.scheme as scheme,
           tagclass_names.ordering as schemeordering,
           tagclass_data.value as value,
-          tagclass_values.ordering as valueordering,
+          tagclass_values.ordering as valueordering
    FROM tagclass_data, tagclass_names, tagclass_values
    WHERE
         tagclass_data.scheme = tagclass_names.name
@@ -311,12 +332,15 @@
 ;
 
 -- ordered version of tagclass_stringdata
--- XXX does concat(value) work, and if not, is there any way to do this?
+--
+-- Note that schemeordering is uniquely defined by the group-by
+-- and the min() on it is there to satisfy the cookiemonster.
+--
 CREATE VIEW tagclass_stringdata_ordered AS
-   SELECT pr, scheme, schemeordering,
-          concat(value) as value, first(valueordering) as valueordering
-   FROM tagclass_data
-   ORDER BY valueordering
+   SELECT pr, scheme, min(schemeordering),
+          string_agg(value, ' ' ORDER BY valueordering) as value,
+          min(valueordering) as valueordering
+   FROM tagclass_data_ordered
    GROUP BY pr, scheme
 ;
 
diff -r 000519947282 -r cd36b49f4437 database/schema/logs.sql
--- a/database/schema/logs.sql	Sun May 26 22:13:33 2013 -0400
+++ b/database/schema/logs.sql	Sun May 26 23:24:45 2013 -0400
@@ -4,10 +4,10 @@
 	who bigint		null references users (id),
 	pr bigint		not null references PRs (id),
 	change text		not null,       
-	when timestamp		not null,
-	comment text		not null
+	posttime timestamp	not null,
+	comment text		not null,
 
 	-- if a message was posted, this is the one
-	msgid bigint		null references messages (id),
+	msgid bigint		null references messages (id)
 )
 WITHOUT OIDS;
diff -r 000519947282 -r cd36b49f4437 database/schema/messages.sql
--- a/database/schema/messages.sql	Sun May 26 22:13:33 2013 -0400
+++ b/database/schema/messages.sql	Sun May 26 23:24:45 2013 -0400
@@ -20,7 +20,7 @@
 	pr bigint		not null references prs (id),
 	who bigint		not null references users (id),
 	parent_id bigint	null references messages (id),
-	when timestamp		not null,
+	posttime timestamp	not null,
 	body text		not null,
 
 	-- we don't keep these directly, they go into an admin log entry
diff -r 000519947282 -r cd36b49f4437 database/schema/subscription.sql
--- a/database/schema/subscription.sql	Sun May 26 22:13:33 2013 -0400
+++ b/database/schema/subscription.sql	Sun May 26 23:24:45 2013 -0400
@@ -14,7 +14,7 @@
 
 CREATE TABLE subscriptions (
 	pr bigint		not null references PRs (id),
-	user bigint		not null references users (id),
+	userid bigint		not null references users (id),
 	batch boolean		not null,
 	reporter boolean	not null,
 	responsible boolean	not null
@@ -25,7 +25,7 @@
 --
 --    SELECT id, responsible FROM PRs, subscriptions, users
 --    WHERE PRs.id = subscriptions.id
---      AND subscriptions.user = users.id
+--      AND subscriptions.userid = users.id
 --      AND PRs.state <> 'closed'
 --      AND subscriptions.responsible
 --      AND NOT users.responsible
@@ -35,7 +35,7 @@
 --
 --    SELECT id, responsible FROM PRs, subscriptions, users
 --    WHERE PRs.id = subscriptions.id
---      AND subscriptions.user = users.id
+--      AND subscriptions.userid = users.id
 --      AND PRs.state = 'closed'
 --      AND subscriptions.responsible
 --      AND NOT users.oldresponsible
diff -r 000519947282 -r cd36b49f4437 database/schema/users.sql
--- a/database/schema/users.sql	Sun May 26 22:13:33 2013 -0400
+++ b/database/schema/users.sql	Sun May 26 23:24:45 2013 -0400
@@ -48,7 +48,7 @@
 	admin boolean		not null
 
 	-- responsible implies oldresponsible
-	check NOT (responsible AND NOT oldresponsible)
+	check (oldresponsible OR NOT responsible)
 )
 WITHOUT OIDS;
 
@@ -63,7 +63,7 @@
 WITHOUT OIDS;
 
 CREATE VIEW usermail AS
-   SELECT id, username, realname, email,
+   SELECT users.id AS id, username, realname, email,
           mailto, responsible, editpr, admin
    FROM users, mailaddresses
    WHERE users.id = mailaddresses.id AND mailaddresses.selected