--- 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
 ;