swallowtail bug database WIP stuff: database/schema/classify.sql comparison

comparison database/schema/classify.sql @ 29:cd36b49f4437

fix. now loads into postgresql92

author	David A. Holland
date	Sun, 26 May 2013 23:24:45 -0400
parents	ca4679ac4e02
children	40f64a96481f

comparison

equal deleted inserted replaced

-:000519947282
+:cd36b49f4437
 	pr bigint		not null references PRs (id),
 	scheme text		not null references hierclass_names (name),
 	value text		,
 	primary key (pr, scheme),
 	--
-	-- what I want here is for it to reference hierclass_values unless
+	-- Ok, the intended semantics here are:
-	-- the value is null. or should the values table include a row
+	--    * For PRs that the classification doesn't apply to, there
-	-- with null value where that's allowed? I forget how null values
+	--      should be no row at all in the *class_data table.
-	-- interoperate with reference constraints except that it's messy.
+	--    * For PRs where the classification is undetermined or
-	-- XXX. (if changing this change the similar logic below as well)
+	--      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;
 ------------------------------------------------------------
 -- Enumerated flat schemes.
 	pr bigint		not null references PRs (id),
 	scheme text		not null references flatclass_names (name),
 	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;
 ------------------------------------------------------------
 -- Text schemes.
 -- classification of PRs according to the text schemes
 CREATE TABLE textclass_data (
 	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;
 ------------------------------------------------------------
 -- Tag schemes.
 CREATE TABLE tagclass_data (
 	pr bigint		not null references PRs (id),
 	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
+SELECT pr,
-FROM tagclass_data
+	  tagclass_data.scheme as scheme,
-GROUP BY pr, 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
 ;
 ------------------------------------------------------------
 -- some views
 CREATE VIEW hierclass_data_ordered AS
 SELECT hierclass_data.pr as pr,
 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
 AND hierclass_data.scheme = hierclass_values.scheme
 AND hierclass_data.value = hierclass_values.value
 CREATE VIEW flatclass_data_ordered AS
 SELECT flatclass_data.pr as pr,
 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
 AND flatclass_data.scheme = flatclass_values.scheme
 AND flatclass_data.value = flatclass_values.value
 CREATE VIEW textclass_data_ordered AS
 SELECT textclass_data.pr as pr,
 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
 ;
 CREATE VIEW tagclass_data_ordered AS
 SELECT tagclass_data.pr as pr,
 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
 AND tagclass_data.scheme = tagclass_values.scheme
 AND tagclass_data.value = tagclass_values.value
 ;
 -- 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,
+SELECT pr, scheme, min(schemeordering),
-concat(value) as value, first(valueordering) as valueordering
+string_agg(value, ' ' ORDER BY valueordering) as value,
-FROM tagclass_data
+min(valueordering) as valueordering
-ORDER BY valueordering
+FROM tagclass_data_ordered
 GROUP BY pr, scheme
 ;
 -- a view that combines all the classification data.

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

comparison database/schema/classify.sql @ 29:cd36b49f4437