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

changeset 29:cd36b49f4437

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix. now loads into postgresql92
author David A. Holland
date Sun, 26 May 2013 23:24:45 -0400
parents 000519947282
children 90ec9e3b0a6f
files database/schema/bugs.sql database/schema/classify.sql database/schema/logs.sql database/schema/messages.sql database/schema/subscription.sql database/schema/users.sql
diffstat 6 files changed, 58 insertions(+), 33 deletions(-) [+]
line wrap: on
line diff
--- 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;
--- 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
 ;
 
--- 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;
--- 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
--- 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
--- 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