Merge lp:~stub/launchpad/db-cleanups into lp:launchpad/db-devel

[Stuart Bishop]

[r=stub][no-qa] New database baseline

New database baseline.

[Stuart Bishop (stub)]

review: Approve (db)

=== modified file 'database/schema/Makefile'
--- database/schema/Makefile	2011-10-06 09:38:10 +0000
+++ database/schema/Makefile	2011-12-08 10:11:32 +0000
@@ -64,9 +64,9 @@
 # on production. It is generated using newbaseline.py in
 # bzr+ssh://devpad.canonical.com/code/stub/dbascripts
 #
-REV=2208
+REV=2209
 BASELINE=launchpad-${REV}-00-0.sql
-MD5SUM=12a258f8651ae3bba0c96ec8e62be1dd  launchpad-2208-00-0.sql
+MD5SUM=cc7a493c924196409a22392a16443d52  launchpad-2209-00-0.sql
 
 default: all
 
@@ -116,26 +116,10 @@
 	@ echo
 	@ echo "* Creating database \"$(EMPTY_DBNAME)\"."
 	@ ${CREATEDB} template0 ${EMPTY_DBNAME}
-	@ if ! `createlang -l ${EMPTY_DBNAME} | grep -qs plpythonu`; then \
-		echo "* Installing PL/PythonU"; \
-		createlang -d ${EMPTY_DBNAME} plpythonu; \
-	fi
-	@ if ! `createlang -l ${EMPTY_DBNAME} | grep -qs plpgsql`; then \
-		echo "* Installing PL/PgSQL"; \
-	    	createlang -d ${EMPTY_DBNAME} plpgsql; \
-	fi
-	@ echo "* Creating todrop schema"
-	@ psql -d ${EMPTY_DBNAME} -q -c "CREATE SCHEMA todrop;"
-	@ echo "* Creating functions"
-	@ psql -d ${EMPTY_DBNAME} -f trusted.sql   | grep : | cat
-	@ echo "* Installing tsearch2 into ts2 schema"
-	@ ${PYTHON} fti.py -q --setup-only -d ${EMPTY_DBNAME}
 	@ echo "* Loading base database schema"
 	@ psql -d ${EMPTY_DBNAME} -f ${BASELINE} | grep : | cat
 	@ echo "* Patching the database schema"
 	@ ${PYTHON} upgrade.py -d ${EMPTY_DBNAME}
-	@ echo "* Setting up full text indexes"
-	@ ${PYTHON} fti.py -q -d ${EMPTY_DBNAME}
 	@ echo "* Security setup"
 	@ ${PYTHON} security.py -q -d ${EMPTY_DBNAME}
 	@ echo "* Disabling autovacuum"

=== renamed file 'database/schema/patch-2208-00-0.sql' => 'database/schema/archive/patch-2208-00-0.sql'
=== renamed file 'database/schema/patch-2208-01-0.sql' => 'database/schema/archive/patch-2208-01-0.sql'
=== renamed file 'database/schema/patch-2208-01-1.sql' => 'database/schema/archive/patch-2208-01-1.sql'
=== renamed file 'database/schema/patch-2208-01-2.sql' => 'database/schema/archive/patch-2208-01-2.sql'
=== renamed file 'database/schema/patch-2208-01-3.sql' => 'database/schema/archive/patch-2208-01-3.sql'
=== renamed file 'database/schema/patch-2208-02-0.sql' => 'database/schema/archive/patch-2208-02-0.sql'
=== renamed file 'database/schema/patch-2208-03-0.sql' => 'database/schema/archive/patch-2208-03-0.sql'
=== renamed file 'database/schema/patch-2208-04-0.sql' => 'database/schema/archive/patch-2208-04-0.sql'
=== renamed file 'database/schema/patch-2208-05-0.sql' => 'database/schema/archive/patch-2208-05-0.sql'
=== renamed file 'database/schema/patch-2208-07-0.sql' => 'database/schema/archive/patch-2208-07-0.sql'
=== renamed file 'database/schema/patch-2208-08-0.sql' => 'database/schema/archive/patch-2208-08-0.sql'
=== renamed file 'database/schema/patch-2208-08-1.sql' => 'database/schema/archive/patch-2208-08-1.sql'
=== renamed file 'database/schema/patch-2208-08-2.sql' => 'database/schema/archive/patch-2208-08-2.sql'
=== renamed file 'database/schema/patch-2208-08-3.sql' => 'database/schema/archive/patch-2208-08-3.sql'
=== renamed file 'database/schema/patch-2208-09-0.sql' => 'database/schema/archive/patch-2208-09-0.sql'
=== renamed file 'database/schema/patch-2208-10-0.sql' => 'database/schema/archive/patch-2208-10-0.sql'
=== renamed file 'database/schema/patch-2208-12-0.sql' => 'database/schema/archive/patch-2208-12-0.sql'
=== renamed file 'database/schema/patch-2208-13-0.sql' => 'database/schema/archive/patch-2208-13-0.sql'
=== renamed file 'database/schema/patch-2208-14-0.sql' => 'database/schema/archive/patch-2208-14-0.sql'
=== renamed file 'database/schema/patch-2208-15-0.sql' => 'database/schema/archive/patch-2208-15-0.sql'
=== renamed file 'database/schema/patch-2208-16-0.sql' => 'database/schema/archive/patch-2208-16-0.sql'
=== renamed file 'database/schema/patch-2208-17-0.sql' => 'database/schema/archive/patch-2208-17-0.sql'
=== renamed file 'database/schema/patch-2208-18-0.sql' => 'database/schema/archive/patch-2208-18-0.sql'
=== renamed file 'database/schema/patch-2208-19-0.sql' => 'database/schema/archive/patch-2208-19-0.sql'
=== renamed file 'database/schema/patch-2208-20-0.sql' => 'database/schema/archive/patch-2208-20-0.sql'
=== renamed file 'database/schema/patch-2208-21-0.sql' => 'database/schema/archive/patch-2208-21-0.sql'
=== renamed file 'database/schema/patch-2208-22-0.sql' => 'database/schema/archive/patch-2208-22-0.sql'
=== renamed file 'database/schema/patch-2208-23-0.sql' => 'database/schema/archive/patch-2208-23-0.sql'
=== renamed file 'database/schema/patch-2208-24-0.sql' => 'database/schema/archive/patch-2208-24-0.sql'
=== renamed file 'database/schema/patch-2208-25-0.sql' => 'database/schema/archive/patch-2208-25-0.sql'
=== renamed file 'database/schema/patch-2208-26-0.sql' => 'database/schema/archive/patch-2208-26-0.sql'
=== renamed file 'database/schema/patch-2208-27-0.sql' => 'database/schema/archive/patch-2208-27-0.sql'
=== renamed file 'database/schema/patch-2208-28-0.sql' => 'database/schema/archive/patch-2208-28-0.sql'
=== renamed file 'database/schema/patch-2208-28-1.sql' => 'database/schema/archive/patch-2208-28-1.sql'
=== renamed file 'database/schema/patch-2208-29-0.sql' => 'database/schema/archive/patch-2208-29-0.sql'
=== renamed file 'database/schema/patch-2208-30-0.sql' => 'database/schema/archive/patch-2208-30-0.sql'
=== renamed file 'database/schema/patch-2208-31-0.sql' => 'database/schema/archive/patch-2208-31-0.sql'
=== renamed file 'database/schema/patch-2208-32-0.sql' => 'database/schema/archive/patch-2208-32-0.sql'
=== renamed file 'database/schema/patch-2208-33-0.sql' => 'database/schema/archive/patch-2208-33-0.sql'
=== renamed file 'database/schema/patch-2208-34-0.sql' => 'database/schema/archive/patch-2208-34-0.sql'
=== renamed file 'database/schema/patch-2208-35-0.sql' => 'database/schema/archive/patch-2208-35-0.sql'
=== renamed file 'database/schema/patch-2208-36-0.sql' => 'database/schema/archive/patch-2208-36-0.sql'
=== renamed file 'database/schema/patch-2208-37-0.sql' => 'database/schema/archive/patch-2208-37-0.sql'
=== renamed file 'database/schema/patch-2208-38-0.sql' => 'database/schema/archive/patch-2208-38-0.sql'
=== renamed file 'database/schema/patch-2208-38-1.sql' => 'database/schema/archive/patch-2208-38-1.sql'
=== renamed file 'database/schema/patch-2208-39-0.sql' => 'database/schema/archive/patch-2208-39-0.sql'
=== renamed file 'database/schema/patch-2208-40-0.sql' => 'database/schema/archive/patch-2208-40-0.sql'
=== renamed file 'database/schema/patch-2208-41-0.sql' => 'database/schema/archive/patch-2208-41-0.sql'
=== renamed file 'database/schema/patch-2208-42-0.sql' => 'database/schema/archive/patch-2208-42-0.sql'
=== renamed file 'database/schema/patch-2208-43-0.sql' => 'database/schema/archive/patch-2208-43-0.sql'
=== renamed file 'database/schema/patch-2208-44-0.sql' => 'database/schema/archive/patch-2208-44-0.sql'
=== renamed file 'database/schema/patch-2208-45-0.sql' => 'database/schema/archive/patch-2208-45-0.sql'
=== renamed file 'database/schema/patch-2208-46-0.sql' => 'database/schema/archive/patch-2208-46-0.sql'
=== renamed file 'database/schema/patch-2208-47-0.sql' => 'database/schema/archive/patch-2208-47-0.sql'
=== renamed file 'database/schema/patch-2208-48-0.sql' => 'database/schema/archive/patch-2208-48-0.sql'
=== renamed file 'database/schema/patch-2208-49-0.sql' => 'database/schema/archive/patch-2208-49-0.sql'
=== renamed file 'database/schema/patch-2208-50-0.sql' => 'database/schema/archive/patch-2208-50-0.sql'
=== renamed file 'database/schema/patch-2208-51-0.sql' => 'database/schema/archive/patch-2208-51-0.sql'
=== renamed file 'database/schema/patch-2208-52-0.sql' => 'database/schema/archive/patch-2208-52-0.sql'
=== renamed file 'database/schema/patch-2208-53-0.sql' => 'database/schema/archive/patch-2208-53-0.sql'
=== renamed file 'database/schema/patch-2208-54-0.sql' => 'database/schema/archive/patch-2208-54-0.sql'
=== renamed file 'database/schema/patch-2208-55-0.sql' => 'database/schema/archive/patch-2208-55-0.sql'
=== renamed file 'database/schema/patch-2208-56-0.sql' => 'database/schema/archive/patch-2208-56-0.sql'
=== renamed file 'database/schema/patch-2208-57-0.sql' => 'database/schema/archive/patch-2208-57-0.sql'
=== renamed file 'database/schema/patch-2208-57-1.sql' => 'database/schema/archive/patch-2208-57-1.sql'
=== renamed file 'database/schema/patch-2208-58-0.sql' => 'database/schema/archive/patch-2208-58-0.sql'
=== renamed file 'database/schema/patch-2208-59-0.sql' => 'database/schema/archive/patch-2208-59-0.sql'
=== renamed file 'database/schema/patch-2208-59-1.sql' => 'database/schema/archive/patch-2208-59-1.sql'
=== renamed file 'database/schema/patch-2208-59-2.sql' => 'database/schema/archive/patch-2208-59-2.sql'
=== renamed file 'database/schema/patch-2208-60-0.sql' => 'database/schema/archive/patch-2208-60-0.sql'
=== renamed file 'database/schema/patch-2208-60-1.sql' => 'database/schema/archive/patch-2208-60-1.sql'
=== renamed file 'database/schema/patch-2208-61-0.sql' => 'database/schema/archive/patch-2208-61-0.sql'
=== renamed file 'database/schema/patch-2208-62-0.sql' => 'database/schema/archive/patch-2208-62-0.sql'
=== renamed file 'database/schema/patch-2208-63-0.sql' => 'database/schema/archive/patch-2208-63-0.sql'
=== renamed file 'database/schema/patch-2208-63-1.sql' => 'database/schema/archive/patch-2208-63-1.sql'
=== renamed file 'database/schema/patch-2208-63-2.sql' => 'database/schema/archive/patch-2208-63-2.sql'
=== renamed file 'database/schema/patch-2208-63-3.sql' => 'database/schema/archive/patch-2208-63-3.sql'
=== renamed file 'database/schema/patch-2208-63-4.sql' => 'database/schema/archive/patch-2208-63-4.sql'
=== renamed file 'database/schema/patch-2208-64-0.sql' => 'database/schema/archive/patch-2208-64-0.sql'
=== renamed file 'database/schema/patch-2208-65-0.sql' => 'database/schema/archive/patch-2208-65-0.sql'
=== renamed file 'database/schema/patch-2208-65-1.sql' => 'database/schema/archive/patch-2208-65-1.sql'
=== renamed file 'database/schema/patch-2208-65-2.sql' => 'database/schema/archive/patch-2208-65-2.sql'
=== renamed file 'database/schema/patch-2208-67-0.sql' => 'database/schema/archive/patch-2208-67-0.sql'
=== renamed file 'database/schema/patch-2208-68-0.sql' => 'database/schema/archive/patch-2208-68-0.sql'
=== renamed file 'database/schema/patch-2208-69-0.sql' => 'database/schema/archive/patch-2208-69-0.sql'
=== renamed file 'database/schema/patch-2208-70-0.sql' => 'database/schema/archive/patch-2208-70-0.sql'
=== renamed file 'database/schema/patch-2208-71-0.sql' => 'database/schema/archive/patch-2208-71-0.sql'
=== renamed file 'database/schema/patch-2208-72-0.sql' => 'database/schema/archive/patch-2208-72-0.sql'
=== renamed file 'database/schema/patch-2208-73-0.sql' => 'database/schema/archive/patch-2208-73-0.sql'
=== renamed file 'database/schema/patch-2208-73-1.sql' => 'database/schema/archive/patch-2208-73-1.sql'
=== renamed file 'database/schema/patch-2208-73-2.sql' => 'database/schema/archive/patch-2208-73-2.sql'
=== renamed file 'database/schema/patch-2208-74-0.sql' => 'database/schema/archive/patch-2208-74-0.sql'
=== renamed file 'database/schema/patch-2208-75-0.sql' => 'database/schema/archive/patch-2208-75-0.sql'
=== renamed file 'database/schema/patch-2208-75-1.sql' => 'database/schema/archive/patch-2208-75-1.sql'
=== renamed file 'database/schema/patch-2208-76-0.sql' => 'database/schema/archive/patch-2208-76-0.sql'
=== renamed file 'database/schema/patch-2208-76-1.sql' => 'database/schema/archive/patch-2208-76-1.sql'
=== renamed file 'database/schema/patch-2208-76-2.sql' => 'database/schema/archive/patch-2208-76-2.sql'
=== renamed file 'database/schema/patch-2208-76-3.sql' => 'database/schema/archive/patch-2208-76-3.sql'
=== renamed file 'database/schema/patch-2208-76-4.sql' => 'database/schema/archive/patch-2208-76-4.sql'
=== renamed file 'database/schema/patch-2208-77-0.sql' => 'database/schema/archive/patch-2208-77-0.sql'
=== renamed file 'database/schema/patch-2208-78-0.sql' => 'database/schema/archive/patch-2208-78-0.sql'
=== renamed file 'database/schema/patch-2208-78-1.sql' => 'database/schema/archive/patch-2208-78-1.sql'
=== renamed file 'database/schema/patch-2208-78-2.sql' => 'database/schema/archive/patch-2208-78-2.sql'
=== renamed file 'database/schema/patch-2208-79-0.sql' => 'database/schema/archive/patch-2208-79-0.sql'
=== renamed file 'database/schema/patch-2208-79-1.sql' => 'database/schema/archive/patch-2208-79-1.sql'
=== renamed file 'database/schema/patch-2208-80-1.sql' => 'database/schema/archive/patch-2208-80-1.sql'
=== renamed file 'database/schema/patch-2208-81-1.sql' => 'database/schema/archive/patch-2208-81-1.sql'
=== renamed file 'database/schema/patch-2208-82-1.sql' => 'database/schema/archive/patch-2208-82-1.sql'
=== renamed file 'database/schema/patch-2208-83-1.sql' => 'database/schema/archive/patch-2208-83-1.sql'
=== renamed file 'database/schema/patch-2208-83-2.sql' => 'database/schema/archive/patch-2208-83-2.sql'
=== renamed file 'database/schema/patch-2208-84-1.sql' => 'database/schema/archive/patch-2208-84-1.sql'
=== renamed file 'database/schema/patch-2208-85-1.sql' => 'database/schema/archive/patch-2208-85-1.sql'
=== renamed file 'database/schema/patch-2208-87-1.sql' => 'database/schema/archive/patch-2208-87-1.sql'
=== renamed file 'database/schema/patch-2208-87-2.sql' => 'database/schema/archive/patch-2208-87-2.sql'
=== renamed file 'database/schema/patch-2208-88-1.sql' => 'database/schema/archive/patch-2208-88-1.sql'
=== renamed file 'database/schema/patch-2208-89-1.sql' => 'database/schema/archive/patch-2208-89-1.sql'
=== renamed file 'database/schema/patch-2208-90-1.sql' => 'database/schema/archive/patch-2208-90-1.sql'
=== renamed file 'database/schema/patch-2208-91-1.sql' => 'database/schema/archive/patch-2208-91-1.sql'
=== renamed file 'database/schema/patch-2208-92-1.sql' => 'database/schema/archive/patch-2208-92-1.sql'
=== renamed file 'database/schema/patch-2208-92-2.sql' => 'database/schema/archive/patch-2208-92-2.sql'
=== renamed file 'database/schema/patch-2208-93-0.sql' => 'database/schema/archive/patch-2208-93-0.sql'
=== renamed file 'database/schema/patch-2208-93-1.sql' => 'database/schema/archive/patch-2208-93-1.sql'
=== renamed file 'database/schema/patch-2208-93-2.sql' => 'database/schema/archive/patch-2208-93-2.sql'
=== renamed file 'database/schema/launchpad-2208-00-0.sql' => 'database/schema/launchpad-2209-00-0.sql'
--- database/schema/launchpad-2208-00-0.sql	2011-01-31 11:10:39 +0000
+++ database/schema/launchpad-2209-00-0.sql	2011-12-08 10:11:32 +0000
@@ -1,16 +1,65 @@
--- Generated Tue Aug 17 10:52:11 2010 UTC
+-- Generated Tue Dec  6 20:57:32 2011 UTC
 
 SET client_min_messages TO ERROR;
-
-
+SET statement_timeout = 0;
 SET client_encoding = 'UTF8';
 SET standard_conforming_strings = off;
 SET check_function_bodies = false;
 SET client_min_messages = warning;
 SET escape_string_warning = off;
 
+CREATE SCHEMA todrop;
+
+
+CREATE SCHEMA ts2;
+
+
+CREATE PROCEDURAL LANGUAGE plpgsql;
+
+
+CREATE PROCEDURAL LANGUAGE plpythonu;
+
+
 SET search_path = public, pg_catalog;
 
+CREATE TYPE debversion;
+
+
+CREATE FUNCTION debversionin(cstring) RETURNS debversion
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$textin$$;
+
+
+CREATE FUNCTION debversionout(debversion) RETURNS cstring
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$textout$$;
+
+
+CREATE FUNCTION debversionrecv(internal) RETURNS debversion
+    LANGUAGE internal STABLE STRICT
+    AS $$textrecv$$;
+
+
+CREATE FUNCTION debversionsend(debversion) RETURNS bytea
+    LANGUAGE internal STABLE STRICT
+    AS $$textsend$$;
+
+
+CREATE TYPE debversion (
+    INTERNALLENGTH = variable,
+    INPUT = debversionin,
+    OUTPUT = debversionout,
+    RECEIVE = debversionrecv,
+    SEND = debversionsend,
+    CATEGORY = 'S',
+    ALIGNMENT = int4,
+    STORAGE = extended
+);
+
+
+COMMENT ON TYPE debversion IS 'Debian package version number';
+
+
 CREATE TYPE pgstattuple_type AS (
 	table_len bigint,
 	tuple_count bigint,
@@ -23,38 +72,4489 @@
 	free_percent double precision
 );
 
+
+SET search_path = ts2, pg_catalog;
+
+CREATE DOMAIN gtsq AS text;
+
+
+CREATE DOMAIN gtsvector AS pg_catalog.gtsvector;
+
+
+CREATE TYPE statinfo AS (
+	word text,
+	ndoc integer,
+	nentry integer
+);
+
+
+CREATE TYPE tokenout AS (
+	tokid integer,
+	token text
+);
+
+
+CREATE TYPE tokentype AS (
+	tokid integer,
+	alias text,
+	descr text
+);
+
+
+CREATE TYPE tsdebug AS (
+	ts_name text,
+	tok_type text,
+	description text,
+	token text,
+	dict_name text[],
+	tsvector pg_catalog.tsvector
+);
+
+
+CREATE DOMAIN tsquery AS pg_catalog.tsquery;
+
+
+CREATE DOMAIN tsvector AS pg_catalog.tsvector;
+
+
+SET search_path = public, pg_catalog;
+
+CREATE FUNCTION activity() RETURNS SETOF pg_stat_activity
+    LANGUAGE sql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+    SELECT
+        datid, datname, procpid, usesysid, usename,
+        CASE
+            WHEN current_query LIKE '<IDLE>%'
+                OR current_query LIKE 'autovacuum:%'
+                THEN current_query
+            ELSE
+                '<HIDDEN>'
+        END AS current_query,
+        waiting, xact_start, query_start,
+        backend_start, client_addr, client_port
+    FROM pg_catalog.pg_stat_activity;
+$$;
+
+
+COMMENT ON FUNCTION activity() IS 'SECURITY DEFINER wrapper around pg_stat_activity allowing unprivileged users to access most of its information.';
+
+
+CREATE FUNCTION add_test_openid_identifier(account_ integer) RETURNS boolean
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    -- The generated OpenIdIdentifier is not a valid OpenId Identity URL
+    -- and does not match tokens generated by the Canonical SSO. They
+    -- are only useful to the test suite, and access to this stored
+    -- procedure on production does not allow you to compromise
+    -- accounts.
+    INSERT INTO OpenIdIdentifier (identifier, account)
+    VALUES ('test' || CAST(account_ AS text), account_);
+    RETURN TRUE;
+EXCEPTION
+    WHEN unique_violation THEN
+        RETURN FALSE;
+END;
+$$;
+
+
+COMMENT ON FUNCTION add_test_openid_identifier(account_ integer) IS 'Add an OpenIdIdentifier to an account that can be used to login in the test environment. These identifiers are not usable on production or staging.';
+
+
+CREATE FUNCTION assert_patch_applied(major integer, minor integer, patch integer) RETURNS boolean
+    LANGUAGE plpythonu STABLE
+    AS $$
+    rv = plpy.execute("""
+        SELECT * FROM LaunchpadDatabaseRevision
+        WHERE major=%d AND minor=%d AND patch=%d
+        """ % (major, minor, patch))
+    if len(rv) == 0:
+        raise Exception(
+            'patch-%d-%02d-%d not applied.' % (major, minor, patch))
+    else:
+        return True
+$$;
+
+
+COMMENT ON FUNCTION assert_patch_applied(major integer, minor integer, patch integer) IS 'Raise an exception if the given database patch has not been applied.';
+
+
+CREATE FUNCTION bug_maintain_bug_summary() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    -- There is no INSERT logic, as a bug will not have any summary
+    -- information until BugTask rows have been attached.
+    IF TG_OP = 'UPDATE' THEN
+        IF OLD.duplicateof IS DISTINCT FROM NEW.duplicateof
+            OR OLD.private IS DISTINCT FROM NEW.private
+            OR (OLD.latest_patch_uploaded IS NULL)
+                <> (NEW.latest_patch_uploaded IS NULL) THEN
+            PERFORM unsummarise_bug(OLD);
+            PERFORM summarise_bug(NEW);
+        END IF;
+
+    ELSIF TG_OP = 'DELETE' THEN
+        PERFORM unsummarise_bug(OLD);
+    END IF;
+
+    PERFORM bug_summary_flush_temp_journal();
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION bug_maintain_bug_summary() IS 'AFTER trigger on bug maintaining the bugs summaries in bugsummary.';
+
+
+CREATE FUNCTION valid_bug_name(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+    import re
+    name = args[0]
+    pat = r"^[a-z][a-z0-9+\.\-]+$"
+    if re.match(pat, name):
+        return 1
+    return 0
+$_$;
+
+
+COMMENT ON FUNCTION valid_bug_name(text) IS 'validate a bug name
+
+    As per valid_name, except numeric-only names are not allowed (including
+    names that look like floats).';
+
+
 SET default_tablespace = '';
 
 SET default_with_oids = false;
 
-CREATE TABLE revision (
-    id integer NOT NULL,
-    date_created timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
-    log_body text NOT NULL,
-    revision_author integer NOT NULL,
-    gpgkey integer,
-    revision_id text NOT NULL,
-    revision_date timestamp without time zone,
-    karma_allocated boolean DEFAULT false
-);
-ALTER TABLE ONLY revision ALTER COLUMN revision_author SET STATISTICS 500;
-ALTER TABLE ONLY revision ALTER COLUMN revision_date SET STATISTICS 500;
+CREATE TABLE bug (
+    id integer NOT NULL,
+    datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
+    name text,
+    title text NOT NULL,
+    description text NOT NULL,
+    owner integer NOT NULL,
+    duplicateof integer,
+    fti ts2.tsvector,
+    private boolean DEFAULT false NOT NULL,
+    security_related boolean DEFAULT false NOT NULL,
+    date_last_updated timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
+    date_made_private timestamp without time zone,
+    who_made_private integer,
+    date_last_message timestamp without time zone,
+    number_of_duplicates integer DEFAULT 0 NOT NULL,
+    message_count integer DEFAULT 0 NOT NULL,
+    users_affected_count integer DEFAULT 0,
+    users_unaffected_count integer DEFAULT 0,
+    heat integer DEFAULT 0 NOT NULL,
+    heat_last_updated timestamp without time zone,
+    latest_patch_uploaded timestamp without time zone,
+    access_policy integer,
+    CONSTRAINT notduplicateofself CHECK ((NOT (id = duplicateof))),
+    CONSTRAINT sane_description CHECK (((ltrim(description) <> ''::text) AND (char_length(description) <= 50000))),
+    CONSTRAINT valid_bug_name CHECK (valid_bug_name(name))
+);
+
+
+COMMENT ON TABLE bug IS 'A software bug that requires fixing. This particular bug may be linked to one or more products or source packages to identify the location(s) that this bug is found.';
+
+
+COMMENT ON COLUMN bug.name IS 'A lowercase name uniquely identifying the bug';
+
+
+COMMENT ON COLUMN bug.description IS 'A detailed description of the bug. Initially this will be set to the contents of the initial email or bug filing comment, but later it can be edited to give a more accurate description of the bug itself rather than the symptoms observed by the reporter.';
+
+
+COMMENT ON COLUMN bug.private IS 'Is this bug private? If so, only explicit subscribers will be able to see it';
+
+
+COMMENT ON COLUMN bug.security_related IS 'Is this bug a security issue?';
+
+
+COMMENT ON COLUMN bug.date_last_message IS 'When the last BugMessage was attached to this Bug. Maintained by a trigger on the BugMessage table.';
+
+
+COMMENT ON COLUMN bug.number_of_duplicates IS 'The number of bugs marked as duplicates of this bug, populated by a trigger after setting the duplicateof of bugs.';
+
+
+COMMENT ON COLUMN bug.message_count IS 'The number of messages (currently just comments) on this bugbug, maintained by the set_bug_message_count_t trigger.';
+
+
+COMMENT ON COLUMN bug.users_affected_count IS 'The number of users affected by this bug, maintained by the set_bug_users_affected_count_t trigger.';
+
+
+COMMENT ON COLUMN bug.heat IS 'The relevance of this bug. This value is computed periodically using bug_affects_person and other bug values.';
+
+
+COMMENT ON COLUMN bug.heat_last_updated IS 'The time this bug''s heat was last updated, or NULL if the heat has never yet been updated.';
+
+
+COMMENT ON COLUMN bug.latest_patch_uploaded IS 'The time when the most recent patch has been attached to this bug or NULL if no patches are attached';
+
+
+CREATE FUNCTION bug_row(bug_id integer) RETURNS bug
+    LANGUAGE sql STABLE
+    AS $_$
+    SELECT * FROM Bug WHERE id=$1;
+$_$;
+
+
+COMMENT ON FUNCTION bug_row(bug_id integer) IS 'Helper for manually testing functions requiring a bug row as input. eg. SELECT * FROM bugsummary_tags(bug_row(1))';
+
+
+CREATE TABLE bugsummary (
+    id integer NOT NULL,
+    count integer DEFAULT 0 NOT NULL,
+    product integer,
+    productseries integer,
+    distribution integer,
+    distroseries integer,
+    sourcepackagename integer,
+    viewed_by integer,
+    tag text,
+    status integer NOT NULL,
+    milestone integer,
+    importance integer NOT NULL,
+    has_patch boolean NOT NULL,
+    fixed_upstream boolean NOT NULL,
+    CONSTRAINT bugtask_assignment_checks CHECK (CASE WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL) WHEN (distroseries IS NOT NULL) THEN true ELSE false END)
+);
+
+
+CREATE FUNCTION bug_summary_dec(bugsummary) RETURNS void
+    LANGUAGE sql
+    AS $_$
+    -- We own the row reference, so in the absence of bugs this cannot
+    -- fail - just decrement the row.
+    UPDATE BugSummary SET count = count + $1.count
+    WHERE
+        ((product IS NULL AND $1.product IS NULL)
+            OR product = $1.product)
+        AND ((productseries IS NULL AND $1.productseries IS NULL)
+            OR productseries = $1.productseries)
+        AND ((distribution IS NULL AND $1.distribution IS NULL)
+            OR distribution = $1.distribution)
+        AND ((distroseries IS NULL AND $1.distroseries IS NULL)
+            OR distroseries = $1.distroseries)
+        AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
+            OR sourcepackagename = $1.sourcepackagename)
+        AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
+            OR viewed_by = $1.viewed_by)
+        AND ((tag IS NULL AND $1.tag IS NULL)
+            OR tag = $1.tag)
+        AND status = $1.status
+        AND ((milestone IS NULL AND $1.milestone IS NULL)
+            OR milestone = $1.milestone)
+        AND importance = $1.importance
+        AND has_patch = $1.has_patch
+        AND fixed_upstream = $1.fixed_upstream;
+$_$;
+
+
+COMMENT ON FUNCTION bug_summary_dec(bugsummary) IS 'UPSERT into bugsummary incrementing one row';
+
+
+CREATE FUNCTION bug_summary_flush_temp_journal() RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    d bugsummary%ROWTYPE;
+BEGIN
+    -- may get called even though no summaries were made (for simplicity in the
+    -- callers)
+    PERFORM ensure_bugsummary_temp_journal();
+    FOR d IN SELECT * FROM bugsummary_temp_journal LOOP
+        PERFORM bugsummary_journal_ins(d);
+    END LOOP;
+    TRUNCATE bugsummary_temp_journal;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bug_summary_flush_temp_journal() IS 'flush the temporary bugsummary journal into the bugsummary table';
+
+
+CREATE FUNCTION bug_summary_inc(d bugsummary) RETURNS void
+    LANGUAGE plpgsql
+    AS $_$
+BEGIN
+    -- Shameless adaption from postgresql manual
+    LOOP
+        -- first try to update the row
+        UPDATE BugSummary SET count = count + d.count
+        WHERE
+            ((product IS NULL AND $1.product IS NULL)
+                OR product = $1.product)
+            AND ((productseries IS NULL AND $1.productseries IS NULL)
+                OR productseries = $1.productseries)
+            AND ((distribution IS NULL AND $1.distribution IS NULL)
+                OR distribution = $1.distribution)
+            AND ((distroseries IS NULL AND $1.distroseries IS NULL)
+                OR distroseries = $1.distroseries)
+            AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
+                OR sourcepackagename = $1.sourcepackagename)
+            AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
+                OR viewed_by = $1.viewed_by)
+            AND ((tag IS NULL AND $1.tag IS NULL)
+                OR tag = $1.tag)
+            AND status = $1.status
+            AND ((milestone IS NULL AND $1.milestone IS NULL)
+                OR milestone = $1.milestone)
+            AND importance = $1.importance
+            AND has_patch = $1.has_patch
+            AND fixed_upstream = $1.fixed_upstream;
+        IF found THEN
+            RETURN;
+        END IF;
+        -- not there, so try to insert the key
+        -- if someone else inserts the same key concurrently,
+        -- we could get a unique-key failure
+        BEGIN
+            INSERT INTO BugSummary(
+                count, product, productseries, distribution,
+                distroseries, sourcepackagename, viewed_by, tag,
+                status, milestone,
+                importance, has_patch, fixed_upstream)
+            VALUES (
+                d.count, d.product, d.productseries, d.distribution,
+                d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
+                d.status, d.milestone,
+                d.importance, d.has_patch, d.fixed_upstream);
+            RETURN;
+        EXCEPTION WHEN unique_violation THEN
+            -- do nothing, and loop to try the UPDATE again
+        END;
+    END LOOP;
+END;
+$_$;
+
+
+COMMENT ON FUNCTION bug_summary_inc(d bugsummary) IS 'UPSERT into bugsummary incrementing one row';
+
+
+CREATE FUNCTION bug_summary_temp_journal_ins(d bugsummary) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    INSERT INTO BugSummary_Temp_Journal(
+        count, product, productseries, distribution,
+        distroseries, sourcepackagename, viewed_by, tag,
+        status, milestone, importance, has_patch, fixed_upstream)
+    VALUES (
+        d.count, d.product, d.productseries, d.distribution,
+        d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
+        d.status, d.milestone, d.importance, d.has_patch, d.fixed_upstream);
+    RETURN;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bug_summary_temp_journal_ins(d bugsummary) IS 'Insert a BugSummary into the temporary journal';
+
+
+CREATE FUNCTION bug_update_heat_copy_to_bugtask() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF NEW.heat != OLD.heat THEN
+        UPDATE bugtask SET heat=NEW.heat WHERE bugtask.bug=NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION bug_update_heat_copy_to_bugtask() IS 'Copies bug heat to bugtasks when the bug is changed. Runs on UPDATE only because INSERTs do not have bugtasks at the point of insertion.';
+
+
+CREATE FUNCTION bug_update_latest_patch_uploaded(integer) RETURNS void
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $_$
+BEGIN
+    UPDATE bug SET latest_patch_uploaded =
+        (SELECT max(message.datecreated)
+            FROM message, bugattachment
+            WHERE bugattachment.message=message.id AND
+                bugattachment.bug=$1 AND
+                bugattachment.type=1)
+        WHERE bug.id=$1;
+END;
+$_$;
+
+
+CREATE FUNCTION bug_update_latest_patch_uploaded_on_delete() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    PERFORM bug_update_latest_patch_uploaded(OLD.bug);
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+CREATE FUNCTION bug_update_latest_patch_uploaded_on_insert_update() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    PERFORM bug_update_latest_patch_uploaded(NEW.bug);
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+CREATE FUNCTION bugmessage_copy_owner_from_message() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        IF NEW.owner is NULL THEN
+            UPDATE BugMessage
+            SET owner = Message.owner FROM
+            Message WHERE
+            Message.id = NEW.message AND
+            BugMessage.id = NEW.id;
+        END IF;
+    ELSIF NEW.message != OLD.message THEN
+        UPDATE BugMessage
+        SET owner = Message.owner FROM
+        Message WHERE
+        Message.id = NEW.message AND
+        BugMessage.id = NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugmessage_copy_owner_from_message() IS 'Copies the message owner into bugmessage when bugmessage changes.';
+
+
+CREATE FUNCTION bugsubscription_maintain_bug_summary() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    -- This trigger only works if we are inserting, updating or deleting
+    -- a single row per statement.
+    IF TG_OP = 'INSERT' THEN
+        IF NOT (bug_row(NEW.bug)).private THEN
+            -- Public subscriptions are not aggregated.
+            RETURN NEW;
+        END IF;
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(NEW.bug));
+        ELSE
+            PERFORM summarise_bug(bug_row(NEW.bug));
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN NEW;
+    ELSIF TG_OP = 'DELETE' THEN
+        IF NOT (bug_row(OLD.bug)).private THEN
+            -- Public subscriptions are not aggregated.
+            RETURN OLD;
+        END IF;
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(OLD.bug));
+        ELSE
+            PERFORM summarise_bug(bug_row(OLD.bug));
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN OLD;
+    ELSE
+        IF (OLD.person IS DISTINCT FROM NEW.person
+            OR OLD.bug IS DISTINCT FROM NEW.bug) THEN
+            IF TG_WHEN = 'BEFORE' THEN
+                IF (bug_row(OLD.bug)).private THEN
+                    -- Public subscriptions are not aggregated.
+                    PERFORM unsummarise_bug(bug_row(OLD.bug));
+                END IF;
+                IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN
+                    -- Public subscriptions are not aggregated.
+                    PERFORM unsummarise_bug(bug_row(NEW.bug));
+                END IF;
+            ELSE
+                IF (bug_row(OLD.bug)).private THEN
+                    -- Public subscriptions are not aggregated.
+                    PERFORM summarise_bug(bug_row(OLD.bug));
+                END IF;
+                IF OLD.bug <> NEW.bug AND (bug_row(NEW.bug)).private THEN
+                    -- Public subscriptions are not aggregated.
+                    PERFORM summarise_bug(bug_row(NEW.bug));
+                END IF;
+            END IF;
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN NEW;
+    END IF;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugsubscription_maintain_bug_summary() IS 'AFTER trigger on bugsubscription maintaining the bugs summaries in bugsummary.';
+
+
+CREATE FUNCTION bugsummary_journal_ins(d bugsummary) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF d.count <> 0 THEN
+        INSERT INTO BugSummaryJournal (
+            count, product, productseries, distribution,
+            distroseries, sourcepackagename, viewed_by, tag,
+            status, milestone,
+            importance, has_patch, fixed_upstream)
+        VALUES (
+            d.count, d.product, d.productseries, d.distribution,
+            d.distroseries, d.sourcepackagename, d.viewed_by, d.tag,
+            d.status, d.milestone,
+            d.importance, d.has_patch, d.fixed_upstream);
+    END IF;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugsummary_journal_ins(d bugsummary) IS 'Add an entry into BugSummaryJournal';
+
+
+CREATE FUNCTION bugsummary_locations(bug_row bug) RETURNS SETOF bugsummary
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF BUG_ROW.duplicateof IS NOT NULL THEN
+        RETURN;
+    END IF;
+    RETURN QUERY
+        SELECT
+            CAST(NULL AS integer) AS id,
+            CAST(1 AS integer) AS count,
+            product, productseries, distribution, distroseries,
+            sourcepackagename, person AS viewed_by, tag, status, milestone,
+            importance,
+            BUG_ROW.latest_patch_uploaded IS NOT NULL AS has_patch,
+            (EXISTS (
+                SELECT TRUE FROM BugTask AS RBT
+                WHERE
+                    RBT.bug = tasks.bug
+                    -- This would just be 'RBT.id <> tasks.id', except
+                    -- that the records from tasks are summaries and not
+                    -- real bugtasks, and do not have an id.
+                    AND (RBT.product IS DISTINCT FROM tasks.product
+                        OR RBT.productseries
+                            IS DISTINCT FROM tasks.productseries
+                        OR RBT.distribution IS DISTINCT FROM tasks.distribution
+                        OR RBT.distroseries IS DISTINCT FROM tasks.distroseries
+                        OR RBT.sourcepackagename
+                            IS DISTINCT FROM tasks.sourcepackagename)
+                    -- Flagged as INVALID, FIXCOMMITTED or FIXRELEASED
+                    -- via a bugwatch, or FIXCOMMITTED or FIXRELEASED on
+                    -- the product.
+                    AND ((bugwatch IS NOT NULL AND status IN (17, 25, 30))
+                        OR (bugwatch IS NULL AND product IS NOT NULL
+                            AND status IN (25, 30))))
+                )::boolean AS fixed_upstream
+        FROM bugsummary_tasks(BUG_ROW) AS tasks
+        JOIN bugsummary_tags(BUG_ROW) AS bug_tags ON TRUE
+        LEFT OUTER JOIN bugsummary_viewers(BUG_ROW) AS bug_viewers ON TRUE;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugsummary_locations(bug_row bug) IS 'Calculate what BugSummary rows should exist for a given Bug.';
+
+
+CREATE FUNCTION bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer) RETURNS void
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+DECLARE
+    d bugsummary%ROWTYPE;
+    max_id integer;
+BEGIN
+    -- Lock so we don't content with other invokations of this
+    -- function. We can happily lock the BugSummary table for writes
+    -- as this function is the only thing that updates that table.
+    -- BugSummaryJournal remains unlocked so nothing should be blocked.
+    LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
+
+    IF batchsize IS NULL THEN
+        SELECT MAX(id) INTO max_id FROM BugSummaryJournal;
+    ELSE
+        SELECT MAX(id) INTO max_id FROM (
+            SELECT id FROM BugSummaryJournal ORDER BY id LIMIT batchsize
+            ) AS Whatever;
+    END IF;
+
+    FOR d IN
+        SELECT
+            NULL as id,
+            SUM(count),
+            product,
+            productseries,
+            distribution,
+            distroseries,
+            sourcepackagename,
+            viewed_by,
+            tag,
+            status,
+            milestone,
+            importance,
+            has_patch,
+            fixed_upstream
+        FROM BugSummaryJournal
+        WHERE id <= max_id
+        GROUP BY
+            product, productseries, distribution, distroseries,
+            sourcepackagename, viewed_by, tag, status, milestone,
+            importance, has_patch, fixed_upstream
+        HAVING sum(count) <> 0
+    LOOP
+        IF d.count < 0 THEN
+            PERFORM bug_summary_dec(d);
+        ELSIF d.count > 0 THEN
+            PERFORM bug_summary_inc(d);
+        END IF;
+    END LOOP;
+
+    -- Clean out any counts we reduced to 0.
+    DELETE FROM BugSummary WHERE count=0;
+    -- Clean out the journal entries we have handled.
+    DELETE FROM BugSummaryJournal WHERE id <= max_id;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugsummary_rollup_journal(batchsize integer) IS 'Collate and migrate rows from BugSummaryJournal to BugSummary';
+
+
+CREATE FUNCTION valid_name(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    import re
+    name = args[0]
+    pat = r"^[a-z0-9][a-z0-9\+\.\-]*\Z"
+    if re.match(pat, name):
+        return 1
+    return 0
+$$;
+
+
+COMMENT ON FUNCTION valid_name(text) IS 'validate a name.
+
+    Names must contain only lowercase letters, numbers, ., & -. They
+    must start with an alphanumeric. They are ASCII only. Names are useful
+    for mneumonic identifiers such as nicknames and as URL components.
+    This specification is the same as the Debian product naming policy.
+
+    Note that a valid name might be all integers, so there is a possible
+    namespace conflict if URL traversal is possible by name as well as id.';
+
+
+CREATE TABLE bugtag (
+    id integer NOT NULL,
+    bug integer NOT NULL,
+    tag text NOT NULL,
+    CONSTRAINT valid_tag CHECK (valid_name(tag))
+);
+
+
+COMMENT ON TABLE bugtag IS 'Attaches simple text tags to a bug.';
+
+
+COMMENT ON COLUMN bugtag.bug IS 'The bug the tags is attached to.';
+
+
+COMMENT ON COLUMN bugtag.tag IS 'The text representation of the tag.';
+
+
+CREATE FUNCTION bugsummary_tags(bug_row bug) RETURNS SETOF bugtag
+    LANGUAGE sql STABLE
+    AS $_$
+    SELECT * FROM BugTag WHERE BugTag.bug = $1.id
+    UNION ALL
+    SELECT NULL::integer, $1.id, NULL::text;
+$_$;
+
+
+COMMENT ON FUNCTION bugsummary_tags(bug_row bug) IS 'Return (bug, tag) for all tags + (bug, NULL::text)';
+
+
+CREATE TABLE bugtask (
+    id integer NOT NULL,
+    bug integer NOT NULL,
+    product integer,
+    distribution integer,
+    distroseries integer,
+    sourcepackagename integer,
+    binarypackagename integer,
+    status integer NOT NULL,
+    importance integer DEFAULT 5 NOT NULL,
+    assignee integer,
+    date_assigned timestamp without time zone,
+    datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone),
+    owner integer NOT NULL,
+    milestone integer,
+    bugwatch integer,
+    fti ts2.tsvector,
+    targetnamecache text,
+    date_confirmed timestamp without time zone,
+    date_inprogress timestamp without time zone,
+    date_closed timestamp without time zone,
+    productseries integer,
+    date_incomplete timestamp without time zone,
+    date_left_new timestamp without time zone,
+    date_triaged timestamp without time zone,
+    date_fix_committed timestamp without time zone,
+    date_fix_released timestamp without time zone,
+    date_left_closed timestamp without time zone,
+    heat_rank integer DEFAULT 0 NOT NULL,
+    date_milestone_set timestamp without time zone,
+    heat integer DEFAULT 0 NOT NULL,
+    CONSTRAINT bugtask_assignment_checks CHECK (CASE WHEN (product IS NOT NULL) THEN ((((productseries IS NULL) AND (distribution IS NULL)) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (productseries IS NOT NULL) THEN (((distribution IS NULL) AND (distroseries IS NULL)) AND (sourcepackagename IS NULL)) WHEN (distribution IS NOT NULL) THEN (distroseries IS NULL) WHEN (distroseries IS NOT NULL) THEN true ELSE false END)
+);
+
+
+COMMENT ON TABLE bugtask IS 'Links a given Bug to a particular (sourcepackagename, distro) or product.';
+
+
+COMMENT ON COLUMN bugtask.bug IS 'The bug that is assigned to this (sourcepackagename, distro) or product.';
+
+
+COMMENT ON COLUMN bugtask.product IS 'The product in which this bug shows up.';
+
+
+COMMENT ON COLUMN bugtask.distribution IS 'The distro of the named sourcepackage.';
+
+
+COMMENT ON COLUMN bugtask.sourcepackagename IS 'The name of the sourcepackage in which this bug shows up.';
+
+
+COMMENT ON COLUMN bugtask.binarypackagename IS 'The name of the binary package built from the source package. This column may only contain a value if this bug task is linked to a sourcepackage (not a product)';
+
+
+COMMENT ON COLUMN bugtask.status IS 'The general health of the bug, e.g. Accepted, Rejected, etc.';
+
+
+COMMENT ON COLUMN bugtask.importance IS 'The importance of fixing the bug.';
+
+
+COMMENT ON COLUMN bugtask.assignee IS 'The person who has been assigned to fix this bug in this product or (sourcepackagename, distro)';
+
+
+COMMENT ON COLUMN bugtask.date_assigned IS 'The date on which the bug in this (sourcepackagename, distro) or product was assigned to someone to fix';
+
+
+COMMENT ON COLUMN bugtask.datecreated IS 'A timestamp for the creation of this bug assignment. Note that this is not the date the bug was created (though it might be), it''s the date the bug was assigned to this product, which could have come later.';
+
+
+COMMENT ON COLUMN bugtask.milestone IS 'A way to mark a bug for grouping purposes, e.g. to say it needs to be fixed by version 1.2';
+
+
+COMMENT ON COLUMN bugtask.bugwatch IS 'This column allows us to link a bug
+task to a bug watch. In other words, we are connecting the state of the task
+to the state of the bug in a different bug tracking system. To the best of
+our ability we''ll try and keep the bug task syncronised with the state of
+the remote bug watch.';
+
+
+COMMENT ON COLUMN bugtask.targetnamecache IS 'A cached value of the target name of this bugtask, to make it easier to sort and search on the target name.';
+
+
+COMMENT ON COLUMN bugtask.date_confirmed IS 'The date when this bug transitioned from an unconfirmed status to a confirmed one. If the state regresses to a one that logically occurs before Confirmed, e.g., Unconfirmed, this date is cleared.';
+
+
+COMMENT ON COLUMN bugtask.date_inprogress IS 'The date on which this bug transitioned from not being in progress to a state >= In Progress. If the status moves back to a pre-In Progress state, this date is cleared';
+
+
+COMMENT ON COLUMN bugtask.date_closed IS 'The date when this bug transitioned to a resolved state, e.g., Rejected, Fix Released, etc. If the state changes back to a pre-closed state, this date is cleared';
+
+
+COMMENT ON COLUMN bugtask.productseries IS 'The product series to which the bug is targeted';
+
+
+COMMENT ON COLUMN bugtask.date_left_new IS 'The date when this bug first transitioned out of the NEW status.';
+
+
+COMMENT ON COLUMN bugtask.date_triaged IS 'The date when this bug transitioned to a status >= TRIAGED.';
+
+
+COMMENT ON COLUMN bugtask.date_fix_committed IS 'The date when this bug transitioned to a status >= FIXCOMMITTED.';
+
+
+COMMENT ON COLUMN bugtask.date_fix_released IS 'The date when this bug transitioned to a FIXRELEASED status.';
+
+
+COMMENT ON COLUMN bugtask.date_left_closed IS 'The date when this bug last transitioned out of a CLOSED status.';
+
+
+COMMENT ON COLUMN bugtask.heat_rank IS 'The heat bin in which this bugtask appears, as a value from the BugTaskHeatRank enumeration.';
+
+
+COMMENT ON COLUMN bugtask.date_milestone_set IS 'The date when this bug was targed to the milestone that is currently set.';
+
+
+CREATE FUNCTION bugsummary_tasks(bug_row bug) RETURNS SETOF bugtask
+    LANGUAGE plpgsql STABLE
+    AS $$
+DECLARE
+    bt bugtask%ROWTYPE;
+    r record;
+BEGIN
+    bt.bug = BUG_ROW.id;
+
+    -- One row only for each target permutation - need to ignore other fields
+    -- like date last modified to deal with conjoined masters and multiple
+    -- sourcepackage tasks in a distro.
+    FOR r IN
+        SELECT
+            product, productseries, distribution, distroseries,
+            sourcepackagename, status, milestone, importance, bugwatch
+        FROM BugTask WHERE bug=BUG_ROW.id
+        UNION -- Implicit DISTINCT
+        SELECT
+            product, productseries, distribution, distroseries,
+            NULL, status, milestone, importance, bugwatch
+        FROM BugTask WHERE bug=BUG_ROW.id AND sourcepackagename IS NOT NULL
+    LOOP
+        bt.product = r.product;
+        bt.productseries = r.productseries;
+        bt.distribution = r.distribution;
+        bt.distroseries = r.distroseries;
+        bt.sourcepackagename = r.sourcepackagename;
+        bt.status = r.status;
+        bt.milestone = r.milestone;
+        bt.importance = r.importance;
+        bt.bugwatch = r.bugwatch;
+        RETURN NEXT bt;
+    END LOOP;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugsummary_tasks(bug_row bug) IS 'Return all tasks for the bug + all sourcepackagename tasks again with the sourcepackagename squashed';
+
+
+CREATE TABLE bugsubscription (
+    id integer NOT NULL,
+    person integer NOT NULL,
+    bug integer NOT NULL,
+    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
+    subscribed_by integer NOT NULL,
+    bug_notification_level integer DEFAULT 40 NOT NULL
+);
+
+
+COMMENT ON TABLE bugsubscription IS 'A subscription by a Person to a bug.';
+
+
+COMMENT ON COLUMN bugsubscription.bug_notification_level IS 'The level of notifications which the Person will receive from this subscription.';
+
+
+CREATE FUNCTION bugsummary_viewers(bug_row bug) RETURNS SETOF bugsubscription
+    LANGUAGE sql STABLE
+    AS $_$
+    SELECT *
+    FROM BugSubscription
+    WHERE
+        bugsubscription.bug=$1.id
+        AND $1.private IS TRUE;
+$_$;
+
+
+COMMENT ON FUNCTION bugsummary_viewers(bug_row bug) IS 'Return (bug, viewer) for all viewers if private, nothing otherwise';
+
+
+CREATE FUNCTION bugtag_maintain_bug_summary() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(NEW.bug));
+        ELSE
+            PERFORM summarise_bug(bug_row(NEW.bug));
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN NEW;
+    ELSIF TG_OP = 'DELETE' THEN
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(OLD.bug));
+        ELSE
+            PERFORM summarise_bug(bug_row(OLD.bug));
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN OLD;
+    ELSE
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(OLD.bug));
+            IF OLD.bug <> NEW.bug THEN
+                PERFORM unsummarise_bug(bug_row(NEW.bug));
+            END IF;
+        ELSE
+            PERFORM summarise_bug(bug_row(OLD.bug));
+            IF OLD.bug <> NEW.bug THEN
+                PERFORM summarise_bug(bug_row(NEW.bug));
+            END IF;
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN NEW;
+    END IF;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugtag_maintain_bug_summary() IS 'AFTER trigger on bugtag maintaining the bugs summaries in bugsummary.';
+
+
+CREATE FUNCTION bugtask_maintain_bug_summary() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    -- This trigger only works if we are inserting, updating or deleting
+    -- a single row per statement.
+
+    -- Unlike bug_maintain_bug_summary, this trigger does not have access
+    -- to the old bug when invoked as an AFTER trigger. To work around this
+    -- we install this trigger as both a BEFORE and an AFTER trigger.
+    IF TG_OP = 'INSERT' THEN
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(NEW.bug));
+        ELSE
+            PERFORM summarise_bug(bug_row(NEW.bug));
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN NEW;
+
+    ELSIF TG_OP = 'DELETE' THEN
+        IF TG_WHEN = 'BEFORE' THEN
+            PERFORM unsummarise_bug(bug_row(OLD.bug));
+        ELSE
+            PERFORM summarise_bug(bug_row(OLD.bug));
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN OLD;
+
+    ELSE
+        IF (OLD.product IS DISTINCT FROM NEW.product
+            OR OLD.productseries IS DISTINCT FROM NEW.productseries
+            OR OLD.distribution IS DISTINCT FROM NEW.distribution
+            OR OLD.distroseries IS DISTINCT FROM NEW.distroseries
+            OR OLD.sourcepackagename IS DISTINCT FROM NEW.sourcepackagename
+            OR OLD.status IS DISTINCT FROM NEW.status
+            OR OLD.importance IS DISTINCT FROM NEW.importance
+            OR OLD.bugwatch IS DISTINCT FROM NEW.bugwatch
+            OR OLD.milestone IS DISTINCT FROM NEW.milestone) THEN
+
+            IF TG_WHEN = 'BEFORE' THEN
+                PERFORM unsummarise_bug(bug_row(OLD.bug));
+                IF OLD.bug <> NEW.bug THEN
+                    PERFORM unsummarise_bug(bug_row(NEW.bug));
+                END IF;
+            ELSE
+                PERFORM summarise_bug(bug_row(OLD.bug));
+                IF OLD.bug <> NEW.bug THEN
+                    PERFORM summarise_bug(bug_row(NEW.bug));
+                END IF;
+            END IF;
+        END IF;
+        PERFORM bug_summary_flush_temp_journal();
+        RETURN NEW;
+    END IF;
+END;
+$$;
+
+
+COMMENT ON FUNCTION bugtask_maintain_bug_summary() IS 'Both BEFORE & AFTER trigger on bugtask maintaining the bugs summaries in bugsummary.';
+
+
+CREATE FUNCTION calculate_bug_heat(bug_id integer) RETURNS integer
+    LANGUAGE plpythonu STABLE STRICT
+    AS $$
+    from datetime import datetime
+
+    class BugHeatConstants:
+        PRIVACY = 150
+        SECURITY = 250
+        DUPLICATE = 6
+        AFFECTED_USER = 4
+        SUBSCRIBER = 2
+
+    def get_max_heat_for_bug(bug_id):
+        results = plpy.execute("""
+            SELECT MAX(
+                GREATEST(Product.max_bug_heat,
+                         DistributionSourcePackage.max_bug_heat))
+                    AS max_heat
+            FROM BugTask
+            LEFT OUTER JOIN ProductSeries ON
+                BugTask.productseries = ProductSeries.id
+            LEFT OUTER JOIN Product ON (
+                BugTask.product = Product.id
+                OR ProductSeries.product = Product.id)
+            LEFT OUTER JOIN DistroSeries ON
+                BugTask.distroseries = DistroSeries.id
+            LEFT OUTER JOIN Distribution ON (
+                BugTask.distribution = Distribution.id
+                OR DistroSeries.distribution = Distribution.id)
+            LEFT OUTER JOIN DistributionSourcePackage ON (
+                BugTask.sourcepackagename =
+                    DistributionSourcePackage.sourcepackagename)
+            WHERE
+                BugTask.bug = %s""" % bug_id)
+
+        return results[0]['max_heat']
+
+    # It would be nice to be able to just SELECT * here, but we need the
+    # timestamps to be in a format that datetime.fromtimestamp() will
+    # understand.
+    bug_data = plpy.execute("""
+        SELECT
+            duplicateof,
+            private,
+            security_related,
+            number_of_duplicates,
+            users_affected_count,
+            EXTRACT(epoch from datecreated)
+                AS timestamp_date_created,
+            EXTRACT(epoch from date_last_updated)
+                AS timestamp_date_last_updated,
+            EXTRACT(epoch from date_last_message)
+                AS timestamp_date_last_message
+        FROM Bug WHERE id = %s""" % bug_id)
+
+    if bug_data.nrows() == 0:
+        raise Exception("Bug %s doesn't exist." % bug_id)
+
+    bug = bug_data[0]
+    if bug['duplicateof'] is not None:
+        return None
+
+    heat = {}
+    heat['dupes'] = (
+        BugHeatConstants.DUPLICATE * bug['number_of_duplicates'])
+    heat['affected_users'] = (
+        BugHeatConstants.AFFECTED_USER *
+        bug['users_affected_count'])
+
+    if bug['private']:
+        heat['privacy'] = BugHeatConstants.PRIVACY
+    if bug['security_related']:
+        heat['security'] = BugHeatConstants.SECURITY
+
+    # Get the heat from subscribers, both direct and via duplicates.
+    subs_from_dupes = plpy.execute("""
+        SELECT COUNT(DISTINCT BugSubscription.person) AS sub_count
+        FROM BugSubscription, Bug
+        WHERE Bug.id = BugSubscription.bug
+            AND (Bug.id = %s OR Bug.duplicateof = %s)"""
+        % (bug_id, bug_id))
+
+    heat['subcribers'] = (
+        BugHeatConstants.SUBSCRIBER
+        * subs_from_dupes[0]['sub_count'])
+
+    total_heat = sum(heat.values())
+
+    # Bugs decay over time. Every day the bug isn't touched its heat
+    # decreases by 1%.
+    date_last_updated = datetime.fromtimestamp(
+        bug['timestamp_date_last_updated'])
+    days_since_last_update = (datetime.utcnow() - date_last_updated).days
+    total_heat = int(total_heat * (0.99 ** days_since_last_update))
+
+    if days_since_last_update > 0:
+        # Bug heat increases by a quarter of the maximum bug heat
+        # divided by the number of days since the bug's creation date.
+        date_created = datetime.fromtimestamp(
+            bug['timestamp_date_created'])
+
+        if bug['timestamp_date_last_message'] is not None:
+            date_last_message = datetime.fromtimestamp(
+                bug['timestamp_date_last_message'])
+            oldest_date = max(date_last_updated, date_last_message)
+        else:
+            date_last_message = None
+            oldest_date = date_last_updated
+
+        days_since_last_activity = (datetime.utcnow() - oldest_date).days
+        days_since_created = (datetime.utcnow() - date_created).days
+        max_heat = get_max_heat_for_bug(bug_id)
+        if max_heat is not None and days_since_created > 0:
+            total_heat = (
+                total_heat + (max_heat * 0.25 / days_since_created))
+
+    return int(total_heat)
+$$;
+
+
+CREATE FUNCTION cursor_fetch(cur refcursor, n integer) RETURNS SETOF record
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    r record;
+    count integer;
+BEGIN
+    FOR count IN 1..n LOOP
+        FETCH FORWARD FROM cur INTO r;
+        IF NOT FOUND THEN
+            RETURN;
+        END IF;
+        RETURN NEXT r;
+    END LOOP;
+END;
+$$;
+
+
+COMMENT ON FUNCTION cursor_fetch(cur refcursor, n integer) IS 'Fetch the next n items from a cursor. Work around for not being able to use FETCH inside a SELECT statement.';
+
+
+CREATE FUNCTION debversion(character) RETURNS debversion
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$rtrim1$$;
+
+
+CREATE FUNCTION debversion_cmp(version1 debversion, version2 debversion) RETURNS integer
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_cmp';
+
+
+COMMENT ON FUNCTION debversion_cmp(version1 debversion, version2 debversion) IS 'Compare Debian versions';
+
+
+CREATE FUNCTION debversion_eq(version1 debversion, version2 debversion) RETURNS boolean
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_eq';
+
+
+COMMENT ON FUNCTION debversion_eq(version1 debversion, version2 debversion) IS 'debversion equal';
+
+
+CREATE FUNCTION debversion_ge(version1 debversion, version2 debversion) RETURNS boolean
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_ge';
+
+
+COMMENT ON FUNCTION debversion_ge(version1 debversion, version2 debversion) IS 'debversion greater-than-or-equal';
+
+
+CREATE FUNCTION debversion_gt(version1 debversion, version2 debversion) RETURNS boolean
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_gt';
+
+
+COMMENT ON FUNCTION debversion_gt(version1 debversion, version2 debversion) IS 'debversion greater-than';
+
+
+CREATE FUNCTION debversion_hash(debversion) RETURNS integer
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_hash';
+
+
+CREATE FUNCTION debversion_larger(version1 debversion, version2 debversion) RETURNS debversion
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_larger';
+
+
+CREATE FUNCTION debversion_le(version1 debversion, version2 debversion) RETURNS boolean
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_le';
+
+
+COMMENT ON FUNCTION debversion_le(version1 debversion, version2 debversion) IS 'debversion less-than-or-equal';
+
+
+CREATE FUNCTION debversion_lt(version1 debversion, version2 debversion) RETURNS boolean
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_lt';
+
+
+COMMENT ON FUNCTION debversion_lt(version1 debversion, version2 debversion) IS 'debversion less-than';
+
+
+CREATE FUNCTION debversion_ne(version1 debversion, version2 debversion) RETURNS boolean
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_ne';
+
+
+COMMENT ON FUNCTION debversion_ne(version1 debversion, version2 debversion) IS 'debversion not equal';
+
+
+CREATE FUNCTION debversion_smaller(version1 debversion, version2 debversion) RETURNS debversion
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/debversion', 'debversion_smaller';
+
+
+CREATE FUNCTION debversion_sort_key(version text) RETURNS text
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+    # If this method is altered, then any functional indexes using it
+    # need to be rebuilt.
+    import re
+
+    VERRE = re.compile("(?:([0-9]+):)?(.+?)(?:-([^-]+))?$")
+
+    MAP = "0123456789ABCDEFGHIJKLMNOPQRSTUV"
+
+    epoch, version, release = VERRE.match(args[0]).groups()
+    key = []
+    for part, part_weight in ((epoch, 3000), (version, 2000), (release, 1000)):
+        if not part:
+            continue
+        i = 0
+        l = len(part)
+        while i != l:
+            c = part[i]
+            if c.isdigit():
+                key.append(part_weight)
+                j = i
+                while i != l and part[i].isdigit(): i += 1
+                key.append(part_weight+int(part[j:i] or "0"))
+            elif c == "~":
+                key.append(0)
+                i += 1
+            elif c.isalpha():
+                key.append(part_weight+ord(c))
+                i += 1
+            else:
+                key.append(part_weight+256+ord(c))
+                i += 1
+        if not key or key[-1] != part_weight:
+            key.append(part_weight)
+            key.append(part_weight)
+    key.append(1)
+
+    # Encode our key and return it
+    #
+    result = []
+    for value in key:
+        if not value:
+            result.append("000")
+        else:
+            element = []
+            while value:
+                element.insert(0, MAP[value & 0x1F])
+                value >>= 5
+            element_len = len(element)
+            if element_len < 3:
+                element.insert(0, "0"*(3-element_len))
+            elif element_len == 3:
+                pass
+            elif element_len < 35:
+                element.insert(0, MAP[element_len-4])
+                element.insert(0, "X")
+            elif element_len < 1027:
+                element.insert(0, MAP[(element_len-4) & 0x1F])
+                element.insert(0, MAP[(element_len-4) & 0x3E0])
+                element.insert(0, "Y")
+            else:
+                raise ValueError("Number too large")
+            result.extend(element)
+    return "".join(result)
+$_$;
+
+
+COMMENT ON FUNCTION debversion_sort_key(version text) IS 'Return a string suitable for sorting debian version strings on';
+
+
+CREATE FUNCTION ensure_bugsummary_temp_journal() RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+    CREATE TEMPORARY TABLE bugsummary_temp_journal (
+        LIKE bugsummary ) ON COMMIT DROP;
+    ALTER TABLE bugsummary_temp_journal ALTER COLUMN id DROP NOT NULL;
+EXCEPTION
+    WHEN duplicate_table THEN
+        NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION ensure_bugsummary_temp_journal() IS 'Create a temporary table bugsummary_temp_journal if it does not exist.';
+
+
+CREATE FUNCTION generate_openid_identifier() RETURNS text
+    LANGUAGE plpythonu
+    AS $$
+    from random import choice
+
+    # Non display confusing characters.
+    chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
+
+    # Character length of tokens. Can be increased, decreased or even made
+    # random - Launchpad does not care. 7 means it takes 40 bytes to store
+    # a null-terminated Launchpad identity URL on the current domain name.
+    length=7
+
+    loop_count = 0
+    while loop_count < 20000:
+        # Generate a random openid_identifier
+        oid = ''.join(choice(chars) for count in range(length))
+
+        # Check if the oid is already in the db, although this is pretty
+        # unlikely
+        rv = plpy.execute("""
+            SELECT COUNT(*) AS num FROM Account WHERE openid_identifier = '%s'
+            """ % oid, 1)
+        if rv[0]['num'] == 0:
+            return oid
+        loop_count += 1
+        if loop_count == 1:
+            plpy.warning(
+                'Clash generating unique openid_identifier. '
+                'Increase length if you see this warning too much.')
+    plpy.error(
+        "Unable to generate unique openid_identifier. "
+        "Need to increase length of tokens.")
+$$;
+
+
+CREATE FUNCTION getlocalnodeid() RETURNS integer
+    LANGUAGE plpgsql STABLE SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+    DECLARE
+        v_node_id integer;
+    BEGIN
+        SELECT INTO v_node_id _sl.getlocalnodeid('_sl');
+        RETURN v_node_id;
+    EXCEPTION
+        WHEN invalid_schema_name THEN
+            RETURN NULL;
+    END;
+$$;
+
+
+COMMENT ON FUNCTION getlocalnodeid() IS 'Return the replication node id for this node, or NULL if not a replicated installation.';
+
+
+CREATE FUNCTION is_blacklisted_name(text, integer) RETURNS boolean
+    LANGUAGE sql STABLE STRICT SECURITY DEFINER
+    SET search_path TO public
+    AS $_$
+    SELECT COALESCE(name_blacklist_match($1, $2)::boolean, FALSE);
+$_$;
+
+
+COMMENT ON FUNCTION is_blacklisted_name(text, integer) IS 'Return TRUE if any regular expressions stored in the NameBlacklist table match the givenname, otherwise return FALSE.';
+
+
+CREATE FUNCTION is_person(text) RETURNS boolean
+    LANGUAGE sql STABLE STRICT
+    AS $_$
+    SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NULL;
+$_$;
+
+
+COMMENT ON FUNCTION is_person(text) IS 'True if the given name identifies a person in the Person table';
+
+
+CREATE FUNCTION is_printable_ascii(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+    import re, string
+    try:
+        text = args[0].decode("ASCII")
+    except UnicodeError:
+        return False
+    if re.search(r"^[%s]*$" % re.escape(string.printable), text) is None:
+        return False
+    return True
+$_$;
+
+
+COMMENT ON FUNCTION is_printable_ascii(text) IS 'True if the string is pure printable US-ASCII';
+
+
+CREATE FUNCTION is_team(integer) RETURNS boolean
+    LANGUAGE sql STABLE STRICT
+    AS $_$
+    SELECT count(*)>0 FROM Person WHERE id=$1 AND teamowner IS NOT NULL;
+$_$;
+
+
+COMMENT ON FUNCTION is_team(integer) IS 'True if the given id identifies a team in the Person table';
+
+
+CREATE FUNCTION is_team(text) RETURNS boolean
+    LANGUAGE sql STABLE STRICT
+    AS $_$
+    SELECT count(*)>0 FROM Person WHERE name=$1 AND teamowner IS NOT NULL;
+$_$;
+
+
+COMMENT ON FUNCTION is_team(text) IS 'True if the given name identifies a team in the Person table';
+
+
+CREATE FUNCTION lp_mirror_account_ins() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    INSERT INTO lp_Account (id, openid_identifier)
+    VALUES (NEW.id, NEW.openid_identifier);
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_account_upd() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF OLD.id <> NEW.id OR OLD.openid_identifier <> NEW.openid_identifier THEN
+        UPDATE lp_Account
+        SET id = NEW.id, openid_identifier = NEW.openid_identifier
+        WHERE id = OLD.id;
+    END IF;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_del() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    EXECUTE 'DELETE FROM lp_' || TG_TABLE_NAME || ' WHERE id=' || OLD.id;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_openididentifier_del() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+DECLARE
+    next_identifier text;
+BEGIN
+    SELECT INTO next_identifier identifier FROM OpenIdIdentifier
+    WHERE account = OLD.account AND identifier <> OLD.identifier
+    ORDER BY date_created DESC LIMIT 1;
+
+    IF next_identifier IS NOT NULL THEN
+        UPDATE lp_account SET openid_identifier = next_identifier
+        WHERE openid_identifier = OLD.identifier;
+    ELSE
+        DELETE FROM lp_account WHERE openid_identifier = OLD.identifier;
+    END IF;
+
+    DELETE FROM lp_OpenIdIdentifier WHERE identifier = OLD.identifier;
+
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_openididentifier_ins() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    -- Support obsolete lp_Account.openid_identifier as best we can
+    -- until ISD migrates to using lp_OpenIdIdentifier.
+    UPDATE lp_account SET openid_identifier = NEW.identifier
+    WHERE id = NEW.account;
+    IF NOT found THEN
+        INSERT INTO lp_account (id, openid_identifier)
+        VALUES (NEW.account, NEW.identifier);
+    END IF;
+
+    INSERT INTO lp_OpenIdIdentifier (identifier, account, date_created)
+    VALUES (NEW.identifier, NEW.account, NEW.date_created);
+
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_openididentifier_upd() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF OLD.identifier <> NEW.identifier THEN
+        UPDATE lp_Account SET openid_identifier = NEW.identifier
+        WHERE openid_identifier = OLD.identifier;
+    END IF;
+    UPDATE lp_OpenIdIdentifier
+    SET
+        identifier = NEW.identifier,
+        account = NEW.account,
+        date_created = NEW.date_created
+    WHERE identifier = OLD.identifier;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_person_ins() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    INSERT INTO lp_Person (
+        id, displayname, teamowner, teamdescription, name, language, fti,
+        defaultmembershipperiod, defaultrenewalperiod, subscriptionpolicy,
+        merged, datecreated, homepage_content, icon, mugshot,
+        hide_email_addresses, creation_rationale, creation_comment,
+        registrant, logo, renewal_policy, personal_standing,
+        personal_standing_reason, mail_resumption_date,
+        mailing_list_auto_subscribe_policy, mailing_list_receive_duplicates,
+        visibility, verbose_bugnotifications, account)
+    VALUES (
+        NEW.id, NEW.displayname, NEW.teamowner, NULL,
+        NEW.name, NEW.language, NEW.fti, NEW.defaultmembershipperiod,
+        NEW.defaultrenewalperiod, NEW.subscriptionpolicy,
+        NEW.merged, NEW.datecreated, NULL, NEW.icon,
+        NEW.mugshot, NEW.hide_email_addresses, NEW.creation_rationale,
+        NEW.creation_comment, NEW.registrant, NEW.logo, NEW.renewal_policy,
+        NEW.personal_standing, NEW.personal_standing_reason,
+        NEW.mail_resumption_date, NEW.mailing_list_auto_subscribe_policy,
+        NEW.mailing_list_receive_duplicates, NEW.visibility,
+        NEW.verbose_bugnotifications, NEW.account);
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_person_upd() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    UPDATE lp_Person
+    SET id = NEW.id,
+        displayname = NEW.displayname,
+        teamowner = NEW.teamowner,
+        teamdescription = NULL,
+        name = NEW.name,
+        language = NEW.language,
+        fti = NEW.fti,
+        defaultmembershipperiod = NEW.defaultmembershipperiod,
+        defaultrenewalperiod = NEW.defaultrenewalperiod,
+        subscriptionpolicy = NEW.subscriptionpolicy,
+        merged = NEW.merged,
+        datecreated = NEW.datecreated,
+        homepage_content = NULL,
+        icon = NEW.icon,
+        mugshot = NEW.mugshot,
+        hide_email_addresses = NEW.hide_email_addresses,
+        creation_rationale = NEW.creation_rationale,
+        creation_comment = NEW.creation_comment,
+        registrant = NEW.registrant,
+        logo = NEW.logo,
+        renewal_policy = NEW.renewal_policy,
+        personal_standing = NEW.personal_standing,
+        personal_standing_reason = NEW.personal_standing_reason,
+        mail_resumption_date = NEW.mail_resumption_date,
+        mailing_list_auto_subscribe_policy
+            = NEW.mailing_list_auto_subscribe_policy,
+        mailing_list_receive_duplicates = NEW.mailing_list_receive_duplicates,
+        visibility = NEW.visibility,
+        verbose_bugnotifications = NEW.verbose_bugnotifications,
+        account = NEW.account
+    WHERE id = OLD.id;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_personlocation_ins() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    INSERT INTO lp_PersonLocation SELECT NEW.*;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_personlocation_upd() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    UPDATE lp_PersonLocation
+    SET id = NEW.id,
+        date_created = NEW.date_created,
+        person = NEW.person,
+        latitude = NEW.latitude,
+        longitude = NEW.longitude,
+        time_zone = NEW.time_zone,
+        last_modified_by = NEW.last_modified_by,
+        date_last_modified = NEW.date_last_modified,
+        visible = NEW.visible,
+        locked = NEW.locked
+    WHERE id = OLD.id;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_teamparticipation_ins() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    INSERT INTO lp_TeamParticipation SELECT NEW.*;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION lp_mirror_teamparticipation_upd() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    UPDATE lp_TeamParticipation
+    SET id = NEW.id,
+        team = NEW.team,
+        person = NEW.person
+    WHERE id = OLD.id;
+    RETURN NULL; -- Ignored for AFTER triggers.
+END;
+$$;
+
+
+CREATE FUNCTION maintain_transitively_private() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF TG_OP = 'UPDATE' THEN
+        IF (NEW.stacked_on IS NOT DISTINCT FROM OLD.stacked_on
+            AND NEW.private IS NOT DISTINCT FROM OLD.private) THEN
+            RETURN NULL;
+        END IF;
+    END IF;
+    PERFORM update_transitively_private(NEW.id);
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION maintain_transitively_private() IS 'Trigger maintaining the Branch transitively_private column';
+
+
+CREATE FUNCTION message_copy_owner_to_bugmessage() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF NEW.owner != OLD.owner THEN
+        UPDATE BugMessage
+        SET owner = NEW.owner
+        WHERE
+        BugMessage.message = NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION message_copy_owner_to_bugmessage() IS 'Copies the message owner into bugmessage when message changes.';
+
+
+CREATE FUNCTION message_copy_owner_to_questionmessage() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF NEW.owner != OLD.owner THEN
+        UPDATE QuestionMessage
+        SET owner = NEW.owner
+        WHERE
+        QuestionMessage.message = NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION message_copy_owner_to_questionmessage() IS 'Copies the message owner into questionmessage when message changes.';
+
+
+CREATE FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) RETURNS text
+    LANGUAGE plpythonu IMMUTABLE
+    AS $$
+    # If this method is altered, then any functional indexes using it
+    # need to be rebuilt.
+    import re
+    import datetime
+
+    date_expected, name = args
+
+    def substitute_filled_numbers(match):
+        return match.group(0).zfill(5)
+
+    name = re.sub(u'\d+', substitute_filled_numbers, name)
+    if date_expected is None:
+        # NULL dates are considered to be in the future.
+        date_expected = datetime.datetime(datetime.MAXYEAR, 1, 1)
+    return '%s %s' % (date_expected, name)
+$$;
+
+
+COMMENT ON FUNCTION milestone_sort_key(dateexpected timestamp without time zone, name text) IS 'Sort by the Milestone dateexpected and name. If the dateexpected is NULL, then it is converted to a date far in the future, so it will be sorted as a milestone in the future.';
+
+
+CREATE FUNCTION mv_branch_distribution_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF OLD.id != NEW.id THEN
+        RAISE EXCEPTION 'Cannot change Distribution.id';
+    END IF;
+    IF OLD.name != NEW.name THEN
+        UPDATE Branch SET unique_name = NULL
+        FROM DistroSeries
+        WHERE Branch.distroseries = Distroseries.id
+            AND Distroseries.distribution = NEW.id;
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_branch_distribution_update() IS 'Maintain Branch name cache when Distribution is modified.';
+
+
+CREATE FUNCTION mv_branch_distroseries_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF OLD.id != NEW.id THEN
+        RAISE EXCEPTION 'Cannot change Distroseries.id';
+    END IF;
+    IF OLD.name != NEW.name THEN
+        UPDATE Branch SET unique_name = NULL
+        WHERE Branch.distroseries = NEW.id;
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_branch_distroseries_update() IS 'Maintain Branch name cache when Distroseries is modified.';
+
+
+CREATE FUNCTION mv_branch_person_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    v_branch RECORD;
+BEGIN
+    IF OLD.id != NEW.id THEN
+        RAISE EXCEPTION 'Cannot change Person.id';
+    END IF;
+    IF OLD.name != NEW.name THEN
+        UPDATE Branch SET owner_name = NEW.name WHERE owner = NEW.id;
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_branch_person_update() IS 'Maintain Branch name cache when Person is modified.';
+
+
+CREATE FUNCTION mv_branch_product_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    v_branch RECORD;
+BEGIN
+    IF OLD.id != NEW.id THEN
+        RAISE EXCEPTION 'Cannot change Product.id';
+    END IF;
+    IF OLD.name != NEW.name THEN
+        UPDATE Branch SET target_suffix = NEW.name WHERE product=NEW.id;
+    END IF;
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_branch_product_update() IS 'Maintain Branch name cache when Product is modified.';
+
+
+CREATE FUNCTION mv_pillarname_distribution() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        INSERT INTO PillarName (name, distribution)
+        VALUES (NEW.name, NEW.id);
+    ELSIF NEW.name != OLD.name THEN
+        UPDATE PillarName SET name=NEW.name WHERE distribution=NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_pillarname_distribution() IS 'Trigger maintaining the PillarName table';
+
+
+CREATE FUNCTION mv_pillarname_product() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        INSERT INTO PillarName (name, product, active)
+        VALUES (NEW.name, NEW.id, NEW.active);
+    ELSIF NEW.name != OLD.name OR NEW.active != OLD.active THEN
+        UPDATE PillarName SET name=NEW.name, active=NEW.active
+        WHERE product=NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_pillarname_product() IS 'Trigger maintaining the PillarName table';
+
+
+CREATE FUNCTION mv_pillarname_project() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        INSERT INTO PillarName (name, project, active)
+        VALUES (NEW.name, NEW.id, NEW.active);
+    ELSIF NEW.name != OLD.name or NEW.active != OLD.active THEN
+        UPDATE PillarName SET name=NEW.name, active=NEW.active
+        WHERE project=NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_pillarname_project() IS 'Trigger maintaining the PillarName table';
+
+
+CREATE FUNCTION mv_pofiletranslator_pomsgset() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF TG_OP = 'DELETE' THEN
+        RAISE EXCEPTION
+            'Deletions from POMsgSet not supported by the POFileTranslator materialized view';
+    ELSIF TG_OP = 'UPDATE' THEN
+        IF OLD.pofile != NEW.pofile THEN
+            RAISE EXCEPTION
+                'Changing POMsgSet.pofile not supported by the POFileTranslator materialized view';
+        END IF;
+    END IF;
+    RETURN NEW;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_pofiletranslator_pomsgset() IS 'Trigger enforing no POMsgSet deletions or POMsgSet.pofile changes';
+
+
+CREATE FUNCTION mv_pofiletranslator_posubmission() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    AS $$
+DECLARE
+    v_pofile INTEGER;
+    v_trash_old BOOLEAN;
+BEGIN
+    -- If we are deleting a row, we need to remove the existing
+    -- POFileTranslator row and reinsert the historical data if it exists.
+    -- We also treat UPDATEs that change the key (person, pofile) the same
+    -- as deletes. UPDATEs that don't change these columns are treated like
+    -- INSERTs below.
+    IF TG_OP = 'INSERT' THEN
+        v_trash_old := FALSE;
+    ELSIF TG_OP = 'DELETE' THEN
+        v_trash_old := TRUE;
+    ELSE -- UPDATE
+        v_trash_old = (
+            OLD.person != NEW.person OR OLD.pomsgset != NEW.pomsgset
+            );
+    END IF;
+
+    IF v_trash_old THEN
+
+        -- Delete the old record.
+        DELETE FROM POFileTranslator USING POMsgSet
+        WHERE POFileTranslator.pofile = POMsgSet.pofile
+            AND POFileTranslator.person = OLD.person
+            AND POMsgSet.id = OLD.pomsgset;
+
+        -- Insert a past record if there is one.
+        INSERT INTO POFileTranslator (
+            person, pofile, latest_posubmission, date_last_touched
+            )
+            SELECT DISTINCT ON (POSubmission.person, POMsgSet.pofile)
+                POSubmission.person, POMsgSet.pofile,
+                POSubmission.id, POSubmission.datecreated
+            FROM POSubmission, POMsgSet
+            WHERE POSubmission.pomsgset = POMsgSet.id
+                AND POSubmission.pomsgset = OLD.pomsgset
+                AND POSubmission.person = OLD.person
+            ORDER BY
+                POSubmission.person, POMsgSet.pofile,
+                POSubmission.datecreated DESC, POSubmission.id DESC;
+
+        -- No NEW with DELETE, so we can short circuit and leave.
+        IF TG_OP = 'DELETE' THEN
+            RETURN NULL; -- Ignored because this is an AFTER trigger
+        END IF;
+    END IF;
+
+    -- Get our new pofile id
+    SELECT INTO v_pofile POMsgSet.pofile FROM POMsgSet
+    WHERE POMsgSet.id = NEW.pomsgset;
+
+    -- Standard 'upsert' loop to avoid race conditions.
+    LOOP
+        UPDATE POFileTranslator
+            SET
+                date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
+                latest_posubmission = NEW.id
+            WHERE
+                person = NEW.person
+                AND pofile = v_pofile;
+        IF found THEN
+            RETURN NULL; -- Return value ignored as this is an AFTER trigger
+        END IF;
+
+        BEGIN
+            INSERT INTO POFileTranslator (person, pofile, latest_posubmission)
+            VALUES (NEW.person, v_pofile, NEW.id);
+            RETURN NULL; -- Return value ignored as this is an AFTER trigger
+        EXCEPTION WHEN unique_violation THEN
+            -- do nothing
+        END;
+    END LOOP;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_pofiletranslator_posubmission() IS 'Trigger maintaining the POFileTranslator table';
+
+
+CREATE FUNCTION mv_pofiletranslator_translationmessage() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+DECLARE
+    v_trash_old BOOLEAN;
+BEGIN
+    -- If we are deleting a row, we need to remove the existing
+    -- POFileTranslator row and reinsert the historical data if it exists.
+    -- We also treat UPDATEs that change the key (submitter) the same
+    -- as deletes. UPDATEs that don't change these columns are treated like
+    -- INSERTs below.
+    IF TG_OP = 'INSERT' THEN
+        v_trash_old := FALSE;
+    ELSIF TG_OP = 'DELETE' THEN
+        v_trash_old := TRUE;
+    ELSE -- UPDATE
+        v_trash_old = (
+            OLD.submitter != NEW.submitter
+            );
+    END IF;
+
+    IF v_trash_old THEN
+        -- Was this somebody's most-recently-changed message?
+        -- If so, delete the entry for that change.
+        DELETE FROM POFileTranslator
+        WHERE latest_message = OLD.id;
+        IF FOUND THEN
+            -- We deleted the entry for somebody's latest contribution.
+            -- Find that person's latest remaining contribution and
+            -- create a new record for that.
+            INSERT INTO POFileTranslator (
+                person, pofile, latest_message, date_last_touched
+                )
+            SELECT DISTINCT ON (person, pofile.id)
+                new_latest_message.submitter AS person,
+                pofile.id,
+                new_latest_message.id,
+                greatest(new_latest_message.date_created,
+                         new_latest_message.date_reviewed)
+              FROM POFile
+              JOIN TranslationTemplateItem AS old_template_item
+                ON OLD.potmsgset = old_template_item.potmsgset AND
+                   old_template_item.potemplate = pofile.potemplate AND
+                   pofile.language = OLD.language
+              JOIN TranslationTemplateItem AS new_template_item
+                ON (old_template_item.potemplate =
+                     new_template_item.potemplate)
+              JOIN TranslationMessage AS new_latest_message
+                ON new_latest_message.potmsgset =
+                       new_template_item.potmsgset AND
+                   new_latest_message.language = OLD.language
+              LEFT OUTER JOIN POfileTranslator AS ExistingEntry
+                ON ExistingEntry.person = OLD.submitter AND
+                   ExistingEntry.pofile = POFile.id
+              WHERE
+                new_latest_message.submitter = OLD.submitter AND
+                ExistingEntry IS NULL
+              ORDER BY new_latest_message.submitter, pofile.id,
+                       new_latest_message.date_created DESC,
+                       new_latest_message.id DESC;
+        END IF;
+
+        -- No NEW with DELETE, so we can short circuit and leave.
+        IF TG_OP = 'DELETE' THEN
+            RETURN NULL; -- Ignored because this is an AFTER trigger
+        END IF;
+    END IF;
+
+    -- Standard 'upsert' loop to avoid race conditions.
+    LOOP
+        UPDATE POFileTranslator
+        SET
+            date_last_touched = CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
+            latest_message = NEW.id
+        FROM POFile, TranslationTemplateItem
+        WHERE person = NEW.submitter AND
+              TranslationTemplateItem.potmsgset=NEW.potmsgset AND
+              TranslationTemplateItem.potemplate=pofile.potemplate AND
+              pofile.language=NEW.language AND
+              POFileTranslator.pofile = pofile.id;
+        IF found THEN
+            RETURN NULL; -- Return value ignored as this is an AFTER trigger
+        END IF;
+
+        BEGIN
+            INSERT INTO POFileTranslator (person, pofile, latest_message)
+            SELECT DISTINCT ON (NEW.submitter, pofile.id)
+                NEW.submitter, pofile.id, NEW.id
+              FROM TranslationTemplateItem
+              JOIN POFile
+                ON pofile.language = NEW.language AND
+                   pofile.potemplate = translationtemplateitem.potemplate
+              WHERE
+                TranslationTemplateItem.potmsgset = NEW.potmsgset;
+            RETURN NULL; -- Return value ignored as this is an AFTER trigger
+        EXCEPTION WHEN unique_violation THEN
+            -- do nothing
+        END;
+    END LOOP;
+END;
+$$;
+
+
+COMMENT ON FUNCTION mv_pofiletranslator_translationmessage() IS 'Trigger maintaining the POFileTranslator table';
+
+
+CREATE FUNCTION mv_validpersonorteamcache_emailaddress() RETURNS trigger
+    LANGUAGE plpythonu SECURITY DEFINER
+    AS $_$
+    # This trigger function keeps the ValidPersonOrTeamCache materialized
+    # view in sync when updates are made to the EmailAddress table.
+    # Note that if the corresponding person is a team, changes to this table
+    # have no effect.
+    PREF = 4 # Constant indicating preferred email address
+
+    if not SD.has_key("delete_plan"):
+        param_types = ["int4"]
+
+        SD["is_team"] = plpy.prepare("""
+            SELECT teamowner IS NOT NULL AS is_team FROM Person WHERE id = $1
+            """, param_types)
+
+        SD["delete_plan"] = plpy.prepare("""
+            DELETE FROM ValidPersonOrTeamCache WHERE id = $1
+            """, param_types)
+
+        SD["insert_plan"] = plpy.prepare("""
+            INSERT INTO ValidPersonOrTeamCache (id) VALUES ($1)
+            """, param_types)
+
+        SD["maybe_insert_plan"] = plpy.prepare("""
+            INSERT INTO ValidPersonOrTeamCache (id)
+            SELECT Person.id
+            FROM Person
+                JOIN EmailAddress ON Person.id = EmailAddress.person
+                LEFT OUTER JOIN ValidPersonOrTeamCache
+                    ON Person.id = ValidPersonOrTeamCache.id
+            WHERE Person.id = $1
+                AND ValidPersonOrTeamCache.id IS NULL
+                AND status = %(PREF)d
+                AND merged IS NULL
+                -- AND password IS NOT NULL
+            """ % vars(), param_types)
+
+    def is_team(person_id):
+        """Return true if person_id corresponds to a team"""
+        if person_id is None:
+            return False
+        return plpy.execute(SD["is_team"], [person_id], 1)[0]["is_team"]
+
+    class NoneDict:
+        def __getitem__(self, key):
+            return None
+
+    old = TD["old"] or NoneDict()
+    new = TD["new"] or NoneDict()
+
+    #plpy.info("old.id     == %s" % old["id"])
+    #plpy.info("old.person == %s" % old["person"])
+    #plpy.info("old.status == %s" % old["status"])
+    #plpy.info("new.id     == %s" % new["id"])
+    #plpy.info("new.person == %s" % new["person"])
+    #plpy.info("new.status == %s" % new["status"])
+
+    # Short circuit if neither person nor status has changed
+    if old["person"] == new["person"] and old["status"] == new["status"]:
+        return
+
+    # Short circuit if we are not mucking around with preferred email
+    # addresses
+    if old["status"] != PREF and new["status"] != PREF:
+        return
+
+    # Note that we have a constraint ensuring that there is only one
+    # status == PREF email address per person at any point in time.
+    # This simplifies our logic, as we know that if old.status == PREF,
+    # old.person does not have any other preferred email addresses.
+    # Also if new.status == PREF, we know new.person previously did not
+    # have a preferred email address.
+
+    if old["person"] != new["person"]:
+        if old["status"] == PREF and not is_team(old["person"]):
+            # old.person is no longer valid, unless they are a team
+            plpy.execute(SD["delete_plan"], [old["person"]])
+        if new["status"] == PREF and not is_team(new["person"]):
+            # new["person"] is now valid, or unchanged if they are a team
+            plpy.execute(SD["insert_plan"], [new["person"]])
+
+    elif old["status"] == PREF and not is_team(old["person"]):
+        # No longer valid, or unchanged if they are a team
+        plpy.execute(SD["delete_plan"], [old["person"]])
+
+    elif new["status"] == PREF and not is_team(new["person"]):
+        # May now be valid, or unchanged if they are a team.
+        plpy.execute(SD["maybe_insert_plan"], [new["person"]])
+$_$;
+
+
+COMMENT ON FUNCTION mv_validpersonorteamcache_emailaddress() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the EmailAddress table';
+
+
+CREATE FUNCTION mv_validpersonorteamcache_person() RETURNS trigger
+    LANGUAGE plpythonu SECURITY DEFINER
+    AS $_$
+    # This trigger function could be simplified by simply issuing
+    # one DELETE followed by one INSERT statement. However, we want to minimize
+    # expensive writes so we use this more complex logic.
+    PREF = 4 # Constant indicating preferred email address
+
+    if not SD.has_key("delete_plan"):
+        param_types = ["int4"]
+
+        SD["delete_plan"] = plpy.prepare("""
+            DELETE FROM ValidPersonOrTeamCache WHERE id = $1
+            """, param_types)
+
+        SD["maybe_insert_plan"] = plpy.prepare("""
+            INSERT INTO ValidPersonOrTeamCache (id)
+            SELECT Person.id
+            FROM Person
+                LEFT OUTER JOIN EmailAddress
+                    ON Person.id = EmailAddress.person AND status = %(PREF)d
+                LEFT OUTER JOIN ValidPersonOrTeamCache
+                    ON Person.id = ValidPersonOrTeamCache.id
+            WHERE Person.id = $1
+                AND ValidPersonOrTeamCache.id IS NULL
+                AND merged IS NULL
+                AND (teamowner IS NOT NULL OR EmailAddress.id IS NOT NULL)
+            """ % vars(), param_types)
+
+    new = TD["new"]
+    old = TD["old"]
+
+    # We should always have new, as this is not a DELETE trigger
+    assert new is not None, 'New is None'
+
+    person_id = new["id"]
+    query_params = [person_id] # All the same
+
+    # Short circuit if this is a new person (not team), as it cannot
+    # be valid until a status == 4 EmailAddress entry has been created
+    # (unless it is a team, in which case it is valid on creation)
+    if old is None:
+        if new["teamowner"] is not None:
+            plpy.execute(SD["maybe_insert_plan"], query_params)
+        return
+
+    # Short circuit if there are no relevant changes
+    if (new["teamowner"] == old["teamowner"]
+        and new["merged"] == old["merged"]):
+        return
+
+    # This function is only dealing with updates to the Person table.
+    # This means we do not have to worry about EmailAddress changes here
+
+    if (new["merged"] is not None or new["teamowner"] is None):
+        plpy.execute(SD["delete_plan"], query_params)
+    else:
+        plpy.execute(SD["maybe_insert_plan"], query_params)
+$_$;
+
+
+COMMENT ON FUNCTION mv_validpersonorteamcache_person() IS 'A trigger for maintaining the ValidPersonOrTeamCache eager materialized view when changes are made to the Person table';
+
+
+CREATE FUNCTION name_blacklist_match(text, integer) RETURNS integer
+    LANGUAGE plpythonu STABLE STRICT SECURITY DEFINER
+    SET search_path TO public
+    AS $_$
+    import re
+    name = args[0].decode("UTF-8")
+    user_id = args[1]
+
+    # Initialize shared storage, shared between invocations.
+    if not SD.has_key("regexp_select_plan"):
+
+        # All the blacklist regexps except the ones we are an admin
+        # for. These we do not check since they are not blacklisted to us.
+        SD["regexp_select_plan"] = plpy.prepare("""
+            SELECT id, regexp FROM NameBlacklist
+            WHERE admin IS NULL OR admin NOT IN (
+                SELECT team FROM TeamParticipation
+                WHERE person = $1)
+            ORDER BY id
+            """, ["integer"])
+
+        # Storage for compiled regexps
+        SD["compiled"] = {}
+
+        # admins is a celebrity and its id is immutable.
+        admins_id = plpy.execute(
+            "SELECT id FROM Person WHERE name='admins'")[0]["id"]
+
+        SD["admin_select_plan"] = plpy.prepare("""
+            SELECT TRUE FROM TeamParticipation
+            WHERE
+                TeamParticipation.team = %d
+                AND TeamParticipation.person = $1
+            LIMIT 1
+            """ % admins_id, ["integer"])
+
+        # All the blacklist regexps except those that have an admin because
+        # members of ~admin can use any name that any other admin can use.
+        SD["admin_regexp_select_plan"] = plpy.prepare("""
+            SELECT id, regexp FROM NameBlacklist
+            WHERE admin IS NULL
+            ORDER BY id
+            """, ["integer"])
+
+
+    compiled = SD["compiled"]
+
+    # Names are never blacklisted for Lauchpad admins.
+    if user_id is not None and plpy.execute(
+        SD["admin_select_plan"], [user_id]).nrows() > 0:
+        blacklist_plan = "admin_regexp_select_plan"
+    else:
+        blacklist_plan = "regexp_select_plan"
+
+    for row in plpy.execute(SD[blacklist_plan], [user_id]):
+        regexp_id = row["id"]
+        regexp_txt = row["regexp"]
+        if (compiled.get(regexp_id) is None
+            or compiled[regexp_id][0] != regexp_txt):
+            regexp = re.compile(
+                regexp_txt, re.IGNORECASE | re.UNICODE | re.VERBOSE
+                )
+            compiled[regexp_id] = (regexp_txt, regexp)
+        else:
+            regexp = compiled[regexp_id][1]
+        if regexp.search(name) is not None:
+            return regexp_id
+    return None
+$_$;
+
+
+COMMENT ON FUNCTION name_blacklist_match(text, integer) IS 'Return the id of the row in the NameBlacklist table that matches the given name, or NULL if no regexps in the NameBlacklist table match.';
+
+
+CREATE FUNCTION null_count(p_values anyarray) RETURNS integer
+    LANGUAGE plpgsql IMMUTABLE STRICT
+    AS $$
+DECLARE
+    v_index integer;
+    v_null_count integer := 0;
+BEGIN
+    FOR v_index IN array_lower(p_values,1)..array_upper(p_values,1) LOOP
+        IF p_values[v_index] IS NULL THEN
+            v_null_count := v_null_count + 1;
+        END IF;
+    END LOOP;
+    RETURN v_null_count;
+END;
+$$;
+
+
+COMMENT ON FUNCTION null_count(p_values anyarray) IS 'Return the number of NULLs in the first row of the given array.';
+
+
+CREATE FUNCTION packageset_deleted_trig() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    DELETE FROM flatpackagesetinclusion
+      WHERE parent = OLD.id AND child = OLD.id;
+
+    -- A package set was deleted; it may have participated in package set
+    -- inclusion relations in a sub/superset role; delete all inclusion
+    -- relationships in which it participated.
+    DELETE FROM packagesetinclusion
+      WHERE parent = OLD.id OR child = OLD.id;
+    RETURN OLD;
+END;
+$$;
+
+
+COMMENT ON FUNCTION packageset_deleted_trig() IS 'Remove any DAG edges leading to/from the deleted package set.';
+
+
+CREATE FUNCTION packageset_inserted_trig() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    -- A new package set was inserted; make it a descendent of itself in
+    -- the flattened package set inclusion table in order to facilitate
+    -- querying.
+    INSERT INTO flatpackagesetinclusion(parent, child)
+      VALUES (NEW.id, NEW.id);
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION packageset_inserted_trig() IS 'Insert self-referencing DAG edge when a new package set is inserted.';
+
+
+CREATE FUNCTION packagesetinclusion_deleted_trig() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    -- A package set inclusion relationship was deleted i.e. a set M
+    -- ceases to include another set N as a subset.
+    -- For an explanation of the queries below please see page 5 of
+    -- "Maintaining Transitive Closure of Graphs in SQL"
+    -- http://www.comp.nus.edu.sg/~wongls/psZ/dlsw-ijit97-16.ps
+    CREATE TEMP TABLE tmp_fpsi_suspect(
+        parent integer NOT NULL,
+        child integer NOT NULL);
+    CREATE TEMP TABLE tmp_fpsi_trusted(
+        parent integer NOT NULL,
+        child integer NOT NULL);
+    CREATE TEMP TABLE tmp_fpsi_good(
+        parent integer NOT NULL,
+        child integer NOT NULL);
+
+    INSERT INTO tmp_fpsi_suspect (
+        SELECT X.parent, Y.child
+        FROM flatpackagesetinclusion X, flatpackagesetinclusion Y
+        WHERE X.child = OLD.parent AND Y.parent = OLD.child
+      UNION
+        SELECT X.parent, OLD.child FROM flatpackagesetinclusion X
+        WHERE X.child = OLD.parent
+      UNION
+        SELECT OLD.parent, X.child FROM flatpackagesetinclusion X
+        WHERE X.parent = OLD.child
+      UNION
+        SELECT OLD.parent, OLD.child
+        );
+
+    INSERT INTO tmp_fpsi_trusted (
+        SELECT parent, child FROM flatpackagesetinclusion
+        EXCEPT
+        SELECT parent, child FROM tmp_fpsi_suspect
+      UNION
+        SELECT parent, child FROM packagesetinclusion psi
+        WHERE psi.parent != OLD.parent AND psi.child != OLD.child
+        );
+
+    INSERT INTO tmp_fpsi_good (
+        SELECT parent, child FROM tmp_fpsi_trusted
+      UNION
+        SELECT T1.parent, T2.child
+        FROM tmp_fpsi_trusted T1, tmp_fpsi_trusted T2
+        WHERE T1.child = T2.parent
+      UNION
+        SELECT T1.parent, T3.child
+        FROM tmp_fpsi_trusted T1, tmp_fpsi_trusted T2, tmp_fpsi_trusted T3
+        WHERE T1.child = T2.parent AND T2.child = T3.parent
+        );
+
+    DELETE FROM flatpackagesetinclusion fpsi
+    WHERE NOT EXISTS (
+        SELECT * FROM tmp_fpsi_good T
+        WHERE T.parent = fpsi.parent AND T.child = fpsi.child);
+
+    DROP TABLE tmp_fpsi_good;
+    DROP TABLE tmp_fpsi_trusted;
+    DROP TABLE tmp_fpsi_suspect;
+
+    RETURN OLD;
+END;
+$$;
+
+
+COMMENT ON FUNCTION packagesetinclusion_deleted_trig() IS 'Maintain the transitive closure in the DAG when an edge leading to/from a package set is deleted.';
+
+
+CREATE FUNCTION packagesetinclusion_inserted_trig() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    DECLARE
+        parent_name text;
+        child_name text;
+        parent_distroseries text;
+        child_distroseries text;
+    BEGIN
+        -- Make sure that the package sets being associated here belong
+        -- to the same distro series.
+        IF (SELECT parent.distroseries != child.distroseries
+            FROM packageset parent, packageset child
+            WHERE parent.id = NEW.parent AND child.id = NEW.child)
+        THEN
+            SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
+            SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
+            SELECT ds.name INTO parent_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.parent AND ps.distroseries = ds.id;
+            SELECT ds.name INTO child_distroseries FROM packageset ps, distroseries ds WHERE ps.id = NEW.child AND ps.distroseries = ds.id;
+            RAISE EXCEPTION 'Package sets % and % belong to different distro series (to % and % respectively) and thus cannot be associated.', child_name, parent_name, child_distroseries, parent_distroseries;
+        END IF;
+
+        IF EXISTS(
+            SELECT * FROM flatpackagesetinclusion
+            WHERE parent = NEW.child AND child = NEW.parent LIMIT 1)
+        THEN
+            SELECT name INTO parent_name FROM packageset WHERE id = NEW.parent;
+            SELECT name INTO child_name FROM packageset WHERE id = NEW.child;
+            RAISE EXCEPTION 'Package set % already includes %. Adding (% -> %) would introduce a cycle in the package set graph (DAG).', child_name, parent_name, parent_name, child_name;
+        END IF;
+    END;
+    -- A new package set inclusion relationship was inserted i.e. a set M
+    -- now includes another set N as a subset.
+    -- For an explanation of the queries below please see page 4 of
+    -- "Maintaining Transitive Closure of Graphs in SQL"
+    -- http://www.comp.nus.edu.sg/~wongls/psZ/dlsw-ijit97-16.ps
+    CREATE TEMP TABLE tmp_fpsi_new(
+        parent integer NOT NULL,
+        child integer NOT NULL);
+
+    INSERT INTO tmp_fpsi_new (
+        SELECT
+            X.parent AS parent, NEW.child AS child
+        FROM flatpackagesetinclusion X WHERE X.child = NEW.parent
+      UNION
+        SELECT
+            NEW.parent AS parent, X.child AS child
+        FROM flatpackagesetinclusion X WHERE X.parent = NEW.child
+      UNION
+        SELECT
+            X.parent AS parent, Y.child AS child
+        FROM flatpackagesetinclusion X, flatpackagesetinclusion Y
+        WHERE X.child = NEW.parent AND Y.parent = NEW.child
+        );
+    INSERT INTO tmp_fpsi_new(parent, child) VALUES(NEW.parent, NEW.child);
+
+    INSERT INTO flatpackagesetinclusion(parent, child) (
+        SELECT
+            parent, child FROM tmp_fpsi_new
+        EXCEPT
+        SELECT F.parent, F.child FROM flatpackagesetinclusion F
+        );
+
+    DROP TABLE tmp_fpsi_new;
+
+    RETURN NULL;
+END;
+$$;
+
+
+COMMENT ON FUNCTION packagesetinclusion_inserted_trig() IS 'Maintain the transitive closure in the DAG for a newly inserted edge leading to/from a package set.';
+
+
+CREATE FUNCTION person_sort_key(displayname text, name text) RETURNS text
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    # NB: If this implementation is changed, the person_sort_idx needs to be
+    # rebuilt along with any other indexes using it.
+    import re
+
+    try:
+        strip_re = SD["strip_re"]
+    except KeyError:
+        strip_re = re.compile("(?:[^\w\s]|[\d_])", re.U)
+        SD["strip_re"] = strip_re
+
+    displayname, name = args
+
+    # Strip noise out of displayname. We do not have to bother with
+    # name, as we know it is just plain ascii.
+    displayname = strip_re.sub('', displayname.decode('UTF-8').lower())
+    return ("%s, %s" % (displayname.strip(), name)).encode('UTF-8')
+$$;
+
+
+COMMENT ON FUNCTION person_sort_key(displayname text, name text) IS 'Return a string suitable for sorting people on, generated by stripping noise out of displayname and concatenating name';
+
 
 CREATE FUNCTION pgstattuple(text) RETURNS pgstattuple_type
-    AS '$libdir/pgstattuple', 'pgstattuple'
-    LANGUAGE c STRICT;
+    LANGUAGE c STRICT
+    AS '$libdir/pgstattuple', 'pgstattuple';
+
 
 CREATE FUNCTION pgstattuple(oid) RETURNS pgstattuple_type
-    AS '$libdir/pgstattuple', 'pgstattuplebyid'
-    LANGUAGE c STRICT;
+    LANGUAGE c STRICT
+    AS '$libdir/pgstattuple', 'pgstattuplebyid';
+
 
 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
-    AS '$libdir/plpgsql', 'plpgsql_call_handler'
-    LANGUAGE c;
+    LANGUAGE c
+    AS '$libdir/plpgsql', 'plpgsql_call_handler';
+
 
 CREATE FUNCTION plpython_call_handler() RETURNS language_handler
-    AS '$libdir/plpython', 'plpython_call_handler'
-    LANGUAGE c;
+    LANGUAGE c
+    AS '$libdir/plpython', 'plpython_call_handler';
+
+
+CREATE FUNCTION questionmessage_copy_owner_from_message() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        IF NEW.owner is NULL THEN
+            UPDATE QuestionMessage
+            SET owner = Message.owner FROM
+            Message WHERE
+            Message.id = NEW.message AND
+            QuestionMessage.id = NEW.id;
+        END IF;
+    ELSIF NEW.message != OLD.message THEN
+        UPDATE QuestionMessage
+        SET owner = Message.owner FROM
+        Message WHERE
+        Message.id = NEW.message AND
+        QuestionMessage.id = NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION questionmessage_copy_owner_from_message() IS 'Copies the message owner into QuestionMessage when QuestionMessage changes.';
+
+
+CREATE FUNCTION replication_lag() RETURNS interval
+    LANGUAGE plpgsql STABLE SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+    DECLARE
+        v_lag interval;
+    BEGIN
+        SELECT INTO v_lag max(st_lag_time) FROM _sl.sl_status;
+        RETURN v_lag;
+    -- Slony-I not installed here - non-replicated setup.
+    EXCEPTION
+        WHEN invalid_schema_name THEN
+            RETURN NULL;
+        WHEN undefined_table THEN
+            RETURN NULL;
+    END;
+$$;
+
+
+COMMENT ON FUNCTION replication_lag() IS 'Returns the worst lag time in our cluster, or NULL if not a replicated installation. Only returns meaningful results on the lpmain replication set master.';
+
+
+CREATE FUNCTION replication_lag(node_id integer) RETURNS interval
+    LANGUAGE plpgsql STABLE SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+    DECLARE
+        v_lag interval;
+    BEGIN
+        SELECT INTO v_lag st_lag_time FROM _sl.sl_status
+            WHERE st_origin = _sl.getlocalnodeid('_sl')
+                AND st_received = node_id;
+        RETURN v_lag;
+    -- Slony-I not installed here - non-replicated setup.
+    EXCEPTION
+        WHEN invalid_schema_name THEN
+            RETURN NULL;
+        WHEN undefined_table THEN
+            RETURN NULL;
+    END;
+$$;
+
+
+COMMENT ON FUNCTION replication_lag(node_id integer) IS 'Returns the lag time of the lpmain replication set to the given node, or NULL if not a replicated installation. The node id parameter can be obtained by calling getlocalnodeid() on the relevant database. This function only returns meaningful results on the lpmain replication set master.';
+
+
+CREATE FUNCTION sane_version(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+    import re
+    if re.search("""^(?ix)
+        [0-9a-z]
+        ( [0-9a-z] | [0-9a-z.-]*[0-9a-z] )*
+        $""", args[0]):
+        return 1
+    return 0
+$_$;
+
+
+COMMENT ON FUNCTION sane_version(text) IS 'A sane version number for use by ProductRelease and DistroRelease. We may make it less strict if required, but it would be nice if we can enforce simple version strings because we use them in URLs';
+
+
+CREATE FUNCTION set_bug_date_last_message() RETURNS trigger
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        UPDATE Bug
+        SET date_last_message = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+        WHERE Bug.id = NEW.bug;
+    ELSE
+        UPDATE Bug
+        SET date_last_message = max_datecreated
+        FROM (
+            SELECT BugMessage.bug, max(Message.datecreated) AS max_datecreated
+            FROM BugMessage, Message
+            WHERE BugMessage.id <> OLD.id
+                AND BugMessage.bug = OLD.bug
+                AND BugMessage.message = Message.id
+            GROUP BY BugMessage.bug
+            ) AS MessageSummary
+        WHERE Bug.id = MessageSummary.bug;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION set_bug_date_last_message() IS 'AFTER INSERT trigger on BugMessage maintaining the Bug.date_last_message column';
+
+
+CREATE FUNCTION set_bug_message_count() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF TG_OP = 'UPDATE' THEN
+        IF NEW.bug = OLD.bug THEN
+            RETURN NULL; -- Ignored - this is an AFTER trigger.
+        END IF;
+    END IF;
+
+    IF TG_OP <> 'DELETE' THEN
+        UPDATE Bug SET message_count = message_count + 1
+        WHERE Bug.id = NEW.bug;
+    END IF;
+
+    IF TG_OP <> 'INSERT' THEN
+        UPDATE Bug SET message_count = message_count - 1
+        WHERE Bug.id = OLD.bug;
+    END IF;
+
+    RETURN NULL; -- Ignored - this is an AFTER trigger.
+END;
+$$;
+
+
+COMMENT ON FUNCTION set_bug_message_count() IS 'AFTER UPDATE trigger on BugAffectsPerson maintaining the Bug.users_affected_count column';
+
+
+CREATE FUNCTION set_bug_number_of_duplicates() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    -- Short circuit on an update that doesn't change duplicateof
+    IF TG_OP = 'UPDATE' THEN
+        IF NEW.duplicateof = OLD.duplicateof THEN
+            RETURN NULL; -- Ignored - this is an AFTER trigger
+        END IF;
+    END IF;
+
+    -- For update or delete, possibly decrement a bug's dupe count
+    IF TG_OP <> 'INSERT' THEN
+        IF OLD.duplicateof IS NOT NULL THEN
+            UPDATE Bug SET number_of_duplicates = number_of_duplicates - 1
+                WHERE Bug.id = OLD.duplicateof;
+        END IF;
+    END IF;
+
+    -- For update or insert, possibly increment a bug's dupe cout
+    IF TG_OP <> 'DELETE' THEN
+        IF NEW.duplicateof IS NOT NULL THEN
+            UPDATE Bug SET number_of_duplicates = number_of_duplicates + 1
+                WHERE Bug.id = NEW.duplicateof;
+        END IF;
+    END IF;
+
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+COMMENT ON FUNCTION set_bug_number_of_duplicates() IS 'AFTER UPDATE trigger on Bug maintaining the Bug.number_of_duplicates column';
+
+
+CREATE FUNCTION set_bug_users_affected_count() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        IF NEW.affected = TRUE THEN
+            UPDATE Bug
+            SET users_affected_count = users_affected_count + 1
+            WHERE Bug.id = NEW.bug;
+        ELSE
+            UPDATE Bug
+            SET users_unaffected_count = users_unaffected_count + 1
+            WHERE Bug.id = NEW.bug;
+        END IF;
+    END IF;
+
+    IF TG_OP = 'DELETE' THEN
+        IF OLD.affected = TRUE THEN
+            UPDATE Bug
+            SET users_affected_count = users_affected_count - 1
+            WHERE Bug.id = OLD.bug;
+        ELSE
+            UPDATE Bug
+            SET users_unaffected_count = users_unaffected_count - 1
+            WHERE Bug.id = OLD.bug;
+        END IF;
+    END IF;
+
+    IF TG_OP = 'UPDATE' THEN
+        IF OLD.affected <> NEW.affected THEN
+            IF NEW.affected THEN
+                UPDATE Bug
+                SET users_affected_count = users_affected_count + 1,
+                    users_unaffected_count = users_unaffected_count - 1
+                WHERE Bug.id = OLD.bug;
+            ELSE
+                UPDATE Bug
+                SET users_affected_count = users_affected_count - 1,
+                    users_unaffected_count = users_unaffected_count + 1
+                WHERE Bug.id = OLD.bug;
+            END IF;
+        END IF;
+    END IF;
+
+    RETURN NULL;
+END;
+$$;
+
+
+CREATE FUNCTION set_bugtask_date_milestone_set() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        -- If the inserted row as a milestone set, set date_milestone_set.
+        IF NEW.milestone IS NOT NULL THEN
+            UPDATE BugTask
+            SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+            WHERE BugTask.id = NEW.id;
+        END IF;
+    END IF;
+
+    IF TG_OP = 'UPDATE' THEN
+        IF OLD.milestone IS NULL THEN
+            -- If there was no milestone set, check if the new row has a
+            -- milestone set and set date_milestone_set.
+            IF NEW.milestone IS NOT NULL THEN
+                UPDATE BugTask
+                SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+                WHERE BugTask.id = NEW.id;
+            END IF;
+        ELSE
+            IF NEW.milestone IS NULL THEN
+                -- If the milestone was unset, clear date_milestone_set.
+                UPDATE BugTask
+                SET date_milestone_set = NULL
+                WHERE BugTask.id = NEW.id;
+            ELSE
+                -- Update date_milestone_set if the bug task was
+                -- targeted to another milestone.
+                IF NEW.milestone != OLD.milestone THEN
+                    UPDATE BugTask
+                    SET date_milestone_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+                    WHERE BugTask.id = NEW.id;
+                END IF;
+
+            END IF;
+        END IF;
+    END IF;
+
+    RETURN NULL; -- Ignored - this is an AFTER trigger.
+END;
+$$;
+
+
+COMMENT ON FUNCTION set_bugtask_date_milestone_set() IS 'Update BugTask.date_milestone_set when BugTask.milestone is changed.';
+
+
+CREATE FUNCTION set_date_status_set() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+    IF OLD.status <> NEW.status THEN
+        NEW.date_status_set = CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
+    END IF;
+    RETURN NEW;
+END;
+$$;
+
+
+COMMENT ON FUNCTION set_date_status_set() IS 'BEFORE UPDATE trigger on Account that maintains the Account.date_status_set column.';
+
+
+CREATE FUNCTION set_openid_identifier() RETURNS trigger
+    LANGUAGE plpythonu
+    AS $$
+    # If someone is trying to explicitly set the openid_identifier, let them.
+    # This also causes openid_identifiers to be left alone if this is an
+    # UPDATE trigger.
+    if TD['new']['openid_identifier'] is not None:
+        return None
+
+    from random import choice
+
+    # Non display confusing characters
+    chars = '34678bcdefhkmnprstwxyzABCDEFGHJKLMNPQRTWXY'
+
+    # character length of tokens. Can be increased, decreased or even made
+    # random - Launchpad does not care. 7 means it takes 40 bytes to store
+    # a null-terminated Launchpad identity URL on the current domain name.
+    length=7
+
+    loop_count = 0
+    while loop_count < 20000:
+        # Generate a random openid_identifier
+        oid = ''.join(choice(chars) for count in range(length))
+
+        # Check if the oid is already in the db, although this is pretty
+        # unlikely
+        rv = plpy.execute("""
+            SELECT COUNT(*) AS num FROM Person WHERE openid_identifier = '%s'
+            """ % oid, 1)
+        if rv[0]['num'] == 0:
+            TD['new']['openid_identifier'] = oid
+            return "MODIFY"
+        loop_count += 1
+        if loop_count == 1:
+            plpy.warning(
+                'Clash generating unique openid_identifier. '
+                'Increase length if you see this warning too much.')
+    plpy.error(
+        "Unable to generate unique openid_identifier. "
+        "Need to increase length of tokens.")
+$$;
+
+
+CREATE FUNCTION set_shipit_normalized_address() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.normalized_address =
+            lower(
+                -- Strip off everything that's not alphanumeric
+                -- characters.
+                regexp_replace(
+                    coalesce(NEW.addressline1, '') || ' ' ||
+                    coalesce(NEW.addressline2, '') || ' ' ||
+                    coalesce(NEW.city, ''),
+                    '[^a-zA-Z0-9]+', '', 'g'));
+        RETURN NEW;
+    END;
+$$;
+
+
+COMMENT ON FUNCTION set_shipit_normalized_address() IS 'Store a normalized concatenation of the request''s address into the normalized_address column.';
+
+
+CREATE FUNCTION sha1(text) RETURNS character
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    import hashlib
+    return hashlib.sha1(args[0]).hexdigest()
+$$;
+
+
+COMMENT ON FUNCTION sha1(text) IS 'Return the SHA1 one way cryptographic hash as a string of 40 hex digits';
+
+
+CREATE FUNCTION summarise_bug(bug_row bug) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    d bugsummary%ROWTYPE;
+BEGIN
+    PERFORM ensure_bugsummary_temp_journal();
+    FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
+        d.count = 1;
+        PERFORM bug_summary_temp_journal_ins(d);
+    END LOOP;
+END;
+$$;
+
+
+COMMENT ON FUNCTION summarise_bug(bug_row bug) IS 'AFTER summarise a bug row into bugsummary.';
+
+
+CREATE FUNCTION ulower(text) RETURNS text
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    return args[0].decode('utf8').lower().encode('utf8')
+$$;
+
+
+COMMENT ON FUNCTION ulower(text) IS 'Return the lower case version of a UTF-8 encoded string.';
+
+
+CREATE FUNCTION unsummarise_bug(bug_row bug) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    d bugsummary%ROWTYPE;
+BEGIN
+    PERFORM ensure_bugsummary_temp_journal();
+    FOR d IN SELECT * FROM bugsummary_locations(BUG_ROW) LOOP
+        d.count = -1;
+        PERFORM bug_summary_temp_journal_ins(d);
+    END LOOP;
+END;
+$$;
+
+
+COMMENT ON FUNCTION unsummarise_bug(bug_row bug) IS 'AFTER unsummarise a bug row from bugsummary.';
+
+
+CREATE FUNCTION update_branch_name_cache() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    needs_update boolean := FALSE;
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        needs_update := TRUE;
+    ELSIF (NEW.owner_name IS NULL
+        OR NEW.unique_name IS NULL
+        OR OLD.owner_name <> NEW.owner_name
+        OR OLD.unique_name <> NEW.unique_name
+        OR (NEW.target_suffix IS NULL <> OLD.target_suffix IS NULL)
+        OR COALESCE(OLD.target_suffix, '') <> COALESCE(NEW.target_suffix, '')
+        OR OLD.name <> NEW.name
+        OR OLD.owner <> NEW.owner
+        OR COALESCE(OLD.product, -1) <> COALESCE(NEW.product, -1)
+        OR COALESCE(OLD.distroseries, -1) <> COALESCE(NEW.distroseries, -1)
+        OR COALESCE(OLD.sourcepackagename, -1)
+            <> COALESCE(NEW.sourcepackagename, -1)) THEN
+        needs_update := TRUE;
+    END IF;
+
+    IF needs_update THEN
+        SELECT
+            Person.name AS owner_name,
+            COALESCE(Product.name, SPN.name) AS target_suffix,
+            '~' || Person.name || '/' || COALESCE(
+                Product.name,
+                Distribution.name || '/' || Distroseries.name
+                    || '/' || SPN.name,
+                '+junk') || '/' || NEW.name AS unique_name
+        INTO NEW.owner_name, NEW.target_suffix, NEW.unique_name
+        FROM Person
+        LEFT OUTER JOIN DistroSeries ON NEW.distroseries = DistroSeries.id
+        LEFT OUTER JOIN Product ON NEW.product = Product.id
+        LEFT OUTER JOIN Distribution
+            ON Distroseries.distribution = Distribution.id
+        LEFT OUTER JOIN SourcepackageName AS SPN
+            ON SPN.id = NEW.sourcepackagename
+        WHERE Person.id = NEW.owner;
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+
+COMMENT ON FUNCTION update_branch_name_cache() IS 'Maintain the cached name columns in Branch.';
+
+
+CREATE FUNCTION update_database_disk_utilization() RETURNS void
+    LANGUAGE sql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+    INSERT INTO DatabaseDiskUtilization
+    SELECT
+        CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
+        namespace, name,
+        sub_namespace, sub_name,
+        kind,
+        (namespace || '.' ||  name || COALESCE(
+                '/' || sub_namespace || '.' || sub_name, '')) AS sort,
+        (stat).table_len,
+        (stat).tuple_count,
+        (stat).tuple_len,
+        (stat).tuple_percent,
+        (stat).dead_tuple_count,
+        (stat).dead_tuple_len,
+        (stat).dead_tuple_percent,
+        (stat).free_space,
+        (stat).free_percent
+    FROM (
+        SELECT
+            pg_namespace.nspname AS namespace,
+            pg_class.relname AS name,
+            NULL AS sub_namespace,
+            NULL AS sub_name,
+            pg_class.relkind AS kind,
+            pgstattuple(pg_class.oid) AS stat
+        FROM pg_class, pg_namespace
+        WHERE
+            pg_class.relnamespace = pg_namespace.oid
+            AND pg_class.relkind = 'r'
+            AND pg_table_is_visible(pg_class.oid)
+
+        UNION ALL
+        
+        SELECT
+            pg_namespace_table.nspname AS namespace,
+            pg_class_table.relname AS name,
+            pg_namespace_index.nspname AS sub_namespace,
+            pg_class_index.relname AS sub_name,
+            pg_class_index.relkind AS kind,
+            pgstattuple(pg_class_index.oid) AS stat
+        FROM
+            pg_namespace AS pg_namespace_table,
+            pg_namespace AS pg_namespace_index,
+            pg_class AS pg_class_table,
+            pg_class AS pg_class_index,
+            pg_index
+        WHERE
+            pg_class_index.relkind = 'i'
+            AND pg_table_is_visible(pg_class_table.oid)
+            AND pg_class_index.relnamespace = pg_namespace_index.oid
+            AND pg_class_table.relnamespace = pg_namespace_table.oid
+            AND pg_index.indexrelid = pg_class_index.oid
+            AND pg_index.indrelid = pg_class_table.oid
+
+        UNION ALL
+
+        -- TOAST tables
+        SELECT
+            pg_namespace_table.nspname AS namespace,
+            pg_class_table.relname AS name,
+            pg_namespace_toast.nspname AS sub_namespace,
+            pg_class_toast.relname AS sub_name,
+            pg_class_toast.relkind AS kind,
+            pgstattuple(pg_class_toast.oid) AS stat
+        FROM
+            pg_namespace AS pg_namespace_table,
+            pg_namespace AS pg_namespace_toast,
+            pg_class AS pg_class_table,
+            pg_class AS pg_class_toast
+        WHERE
+            pg_class_toast.relnamespace = pg_namespace_toast.oid
+            AND pg_table_is_visible(pg_class_table.oid)
+            AND pg_class_table.relnamespace = pg_namespace_table.oid
+            AND pg_class_toast.oid = pg_class_table.reltoastrelid
+
+        UNION ALL
+
+        -- TOAST indexes
+        SELECT
+            pg_namespace_table.nspname AS namespace,
+            pg_class_table.relname AS name,
+            pg_namespace_index.nspname AS sub_namespace,
+            pg_class_index.relname AS sub_name,
+            pg_class_index.relkind AS kind,
+            pgstattuple(pg_class_index.oid) AS stat
+        FROM
+            pg_namespace AS pg_namespace_table,
+            pg_namespace AS pg_namespace_index,
+            pg_class AS pg_class_table,
+            pg_class AS pg_class_index,
+            pg_class AS pg_class_toast
+        WHERE
+            pg_class_table.relnamespace = pg_namespace_table.oid
+            AND pg_table_is_visible(pg_class_table.oid)
+            AND pg_class_index.relnamespace = pg_namespace_index.oid
+            AND pg_class_table.reltoastrelid = pg_class_toast.oid
+            AND pg_class_index.oid = pg_class_toast.reltoastidxid
+        ) AS whatever;
+$$;
+
+
+CREATE FUNCTION update_database_stats() RETURNS void
+    LANGUAGE plpythonu SECURITY DEFINER
+    SET search_path TO public
+    AS $_$
+    import re
+    import subprocess
+
+    # Prune DatabaseTableStats and insert current data.
+    # First, detect if the statistics have been reset.
+    stats_reset = plpy.execute("""
+        SELECT *
+        FROM
+            pg_catalog.pg_stat_user_tables AS NowStat,
+            DatabaseTableStats AS LastStat
+        WHERE
+            LastStat.date_created = (
+                SELECT max(date_created) FROM DatabaseTableStats)
+            AND NowStat.schemaname = LastStat.schemaname
+            AND NowStat.relname = LastStat.relname
+            AND (
+                NowStat.seq_scan < LastStat.seq_scan
+                OR NowStat.idx_scan < LastStat.idx_scan
+                OR NowStat.n_tup_ins < LastStat.n_tup_ins
+                OR NowStat.n_tup_upd < LastStat.n_tup_upd
+                OR NowStat.n_tup_del < LastStat.n_tup_del
+                OR NowStat.n_tup_hot_upd < LastStat.n_tup_hot_upd)
+        LIMIT 1
+        """, 1).nrows() > 0
+    if stats_reset:
+        # The database stats have been reset. We cannot calculate
+        # deltas because we do not know when this happened. So we trash
+        # our records as they are now useless to us. We could be more
+        # sophisticated about this, but this should only happen
+        # when an admin explicitly resets the statistics or if the
+        # database is rebuilt.
+        plpy.notice("Stats wraparound. Purging DatabaseTableStats")
+        plpy.execute("DELETE FROM DatabaseTableStats")
+    else:
+        plpy.execute("""
+            DELETE FROM DatabaseTableStats
+            WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+                - CAST('21 days' AS interval));
+            """)
+    # Insert current data.
+    plpy.execute("""
+        INSERT INTO DatabaseTableStats
+            SELECT
+                CURRENT_TIMESTAMP AT TIME ZONE 'UTC',
+                schemaname, relname, seq_scan, seq_tup_read,
+                coalesce(idx_scan, 0), coalesce(idx_tup_fetch, 0),
+                n_tup_ins, n_tup_upd, n_tup_del,
+                n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum,
+                last_autovacuum, last_analyze, last_autoanalyze
+            FROM pg_catalog.pg_stat_user_tables;
+        """)
+
+    # Prune DatabaseCpuStats. Calculate CPU utilization information
+    # and insert current data.
+    plpy.execute("""
+        DELETE FROM DatabaseCpuStats
+        WHERE date_created < (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'
+            - CAST('21 days' AS interval));
+        """)
+    dbname = plpy.execute(
+        "SELECT current_database() AS dbname", 1)[0]['dbname']
+    ps = subprocess.Popen(
+        ["ps", "-C", "postgres", "--no-headers", "-o", "cp,args"],
+        stdin=subprocess.PIPE, stdout=subprocess.PIPE,
+        stderr=subprocess.STDOUT)
+    stdout, stderr = ps.communicate()
+    cpus = {}
+    # We make the username match non-greedy so the trailing \d eats
+    # trailing digits from the database username. This collapses
+    # lpnet1, lpnet2 etc. into just lpnet.
+    ps_re = re.compile(
+        r"(?m)^\s*(\d+)\spostgres:\s(\w+?)\d*\s%s\s" % dbname)
+    for ps_match in ps_re.finditer(stdout):
+        cpu, username = ps_match.groups()
+        cpus[username] = int(cpu) + cpus.setdefault(username, 0)
+    cpu_ins = plpy.prepare(
+        "INSERT INTO DatabaseCpuStats (username, cpu) VALUES ($1, $2)",
+        ["text", "integer"])
+    for cpu_tuple in cpus.items():
+        plpy.execute(cpu_ins, cpu_tuple)
+$_$;
+
+
+COMMENT ON FUNCTION update_database_stats() IS 'Copies rows from pg_stat_user_tables into DatabaseTableStats. We use a stored procedure because it is problematic for us to grant permissions on objects in the pg_catalog schema.';
+
+
+CREATE FUNCTION update_replication_lag_cache() RETURNS boolean
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+    BEGIN
+        DELETE FROM DatabaseReplicationLag;
+        INSERT INTO DatabaseReplicationLag (node, lag)
+            SELECT st_received, st_lag_time FROM _sl.sl_status
+            WHERE st_origin = _sl.getlocalnodeid('_sl');
+        RETURN TRUE;
+    -- Slony-I not installed here - non-replicated setup.
+    EXCEPTION
+        WHEN invalid_schema_name THEN
+            RETURN FALSE;
+        WHEN undefined_table THEN
+            RETURN FALSE;
+    END;
+$$;
+
+
+COMMENT ON FUNCTION update_replication_lag_cache() IS 'Updates the DatabaseReplicationLag materialized view.';
+
+
+CREATE FUNCTION update_transitively_private(start_branch integer, _root_branch integer DEFAULT NULL::integer, _root_transitively_private boolean DEFAULT NULL::boolean) RETURNS void
+    LANGUAGE plpgsql SECURITY DEFINER
+    SET search_path TO public
+    AS $$
+DECLARE
+    root_transitively_private boolean := _root_transitively_private;
+    root_branch int := _root_branch;
+BEGIN
+    IF root_transitively_private IS NULL THEN
+        -- We can't just trust the transitively_private flag of the
+        -- branch we are stacked on, as if we are updating multiple
+        -- records they will be updated in an indeterminate order.
+        -- We need a recursive query.
+        UPDATE Branch SET transitively_private = (
+            WITH RECURSIVE stacked_branches AS (
+                SELECT
+                    top_branch.id, top_branch.stacked_on, top_branch.private
+                FROM Branch AS top_branch
+                WHERE top_branch.id = start_branch
+                UNION ALL
+                SELECT
+                    sub_branch.id, sub_branch.stacked_on, sub_branch.private
+                FROM stacked_branches, Branch AS sub_branch
+                WHERE
+                    stacked_branches.stacked_on = sub_branch.id
+                    AND stacked_branches.stacked_on != start_branch
+                    -- Shortcircuit. No need to recurse if already private.
+                    AND stacked_branches.private IS FALSE
+                    )
+            SELECT COUNT(*) > 0
+            FROM stacked_branches
+            WHERE private IS TRUE)
+        WHERE Branch.id = start_branch
+        RETURNING transitively_private INTO root_transitively_private;
+        root_branch := start_branch;
+    ELSE
+        -- Now we have calculated the correct transitively_private flag
+        -- we can trust it.
+        UPDATE Branch SET
+            transitively_private = GREATEST(private, root_transitively_private)
+        WHERE id = root_branch;
+    END IF;
+
+    -- Recurse to branches stacked on this one.
+    PERFORM update_transitively_private(
+        start_branch, id, GREATEST(private, root_transitively_private))
+    FROM Branch WHERE stacked_on = root_branch AND id != start_branch;
+END;
+$$;
+
+
+COMMENT ON FUNCTION update_transitively_private(start_branch integer, _root_branch integer, _root_transitively_private boolean) IS 'A branch is transitively private if it is private or is stacked on any transitively private branches.';
+
+
+CREATE FUNCTION valid_absolute_url(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    from urlparse import urlparse, uses_netloc
+    # Extend list of schemes that specify netloc. We can drop sftp
+    # with Python 2.5 in the DB.
+    if 'git' not in uses_netloc:
+        uses_netloc.insert(0, 'sftp')
+        uses_netloc.insert(0, 'bzr')
+        uses_netloc.insert(0, 'bzr+ssh')
+        uses_netloc.insert(0, 'ssh') # Mercurial
+        uses_netloc.insert(0, 'git')
+    (scheme, netloc, path, params, query, fragment) = urlparse(args[0])
+    return bool(scheme and netloc)
+$$;
+
+
+COMMENT ON FUNCTION valid_absolute_url(text) IS 'Ensure the given test is a valid absolute URL, containing both protocol and network location';
+
+
+CREATE FUNCTION valid_branch_name(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    import re
+    name = args[0]
+    pat = r"^(?i)[a-z0-9][a-z0-9+\.\-@_]*\Z"
+    if re.match(pat, name):
+        return 1
+    return 0
+$$;
+
+
+COMMENT ON FUNCTION valid_branch_name(text) IS 'validate a branch name.
+
+    As per valid_name, except we allow uppercase and @';
+
+
+CREATE FUNCTION valid_cve(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+    import re
+    name = args[0]
+    pat = r"^(19|20)\d{2}-\d{4}$"
+    if re.match(pat, name):
+        return 1
+    return 0
+$_$;
+
+
+COMMENT ON FUNCTION valid_cve(text) IS 'validate a common vulnerability number as defined on www.cve.mitre.org, minus the CAN- or CVE- prefix.';
+
+
+CREATE FUNCTION valid_debian_version(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+    import re
+    m = re.search("""^(?ix)
+        ([0-9]+:)?
+        ([0-9a-z][a-z0-9+:.~-]*?)
+        (-[a-z0-9+.~]+)?
+        $""", args[0])
+    if m is None:
+        return 0
+    epoch, version, revision = m.groups()
+    if not epoch:
+        # Can''t contain : if no epoch
+        if ":" in version:
+            return 0
+    if not revision:
+        # Can''t contain - if no revision
+        if "-" in version:
+            return 0
+    return 1
+$_$;
+
+
+COMMENT ON FUNCTION valid_debian_version(text) IS 'validate a version number as per Debian Policy';
+
+
+CREATE FUNCTION valid_fingerprint(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    import re
+    if re.match(r"[\dA-F]{40}", args[0]) is not None:
+        return 1
+    else:
+        return 0
+$$;
+
+
+COMMENT ON FUNCTION valid_fingerprint(text) IS 'Returns true if passed a valid GPG fingerprint. Valid GPG fingerprints are a 40 character long hexadecimal number in uppercase.';
+
+
+CREATE FUNCTION valid_keyid(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    import re
+    if re.match(r"[\dA-F]{8}", args[0]) is not None:
+        return 1
+    else:
+        return 0
+$$;
+
+
+COMMENT ON FUNCTION valid_keyid(text) IS 'Returns true if passed a valid GPG keyid. Valid GPG keyids are an 8 character long hexadecimal number in uppercase (in reality, they are 16 characters long but we are using the ''common'' definition.';
+
+
+CREATE FUNCTION valid_regexp(text) RETURNS boolean
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    import re
+    try:
+        re.compile(args[0])
+    except:
+        return False
+    else:
+        return True
+$$;
+
+
+COMMENT ON FUNCTION valid_regexp(text) IS 'Returns true if the input can be compiled as a regular expression.';
+
+
+CREATE FUNCTION version_sort_key(version text) RETURNS text
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $$
+    # If this method is altered, then any functional indexes using it
+    # need to be rebuilt.
+    import re
+
+    [version] = args
+
+    def substitute_filled_numbers(match):
+        # Prepend "~" so that version numbers will show up first
+        # when sorted descending, i.e. [3, 2c, 2b, 1, c, b, a] instead
+        # of [c, b, a, 3, 2c, 2b, 1]. "~" has the highest ASCII value
+        # of visible ASCII characters.
+        return '~' + match.group(0).zfill(5)
+
+    return re.sub(u'\d+', substitute_filled_numbers, version)
+$$;
+
+
+COMMENT ON FUNCTION version_sort_key(version text) IS 'Sort a field as version numbers that do not necessarily conform to debian package versions (For example, when "2-2" should be considered greater than "1:1"). debversion_sort_key() should be used for debian versions. Numbers will be sorted after letters unlike typical ASCII, so that a descending sort will put the latest version number that starts with a number instead of a letter will be at the top. E.g. ascending is [a, z, 1, 9] and descending is [9, 1, z, a].';
+
+
+CREATE FUNCTION you_are_your_own_member() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        INSERT INTO TeamParticipation (person, team)
+            VALUES (NEW.id, NEW.id);
+        RETURN NULL;
+    END;
+$$;
+
+
+COMMENT ON FUNCTION you_are_your_own_member() IS 'Trigger function to ensure that every row added to the Person table gets a corresponding row in the TeamParticipation table, as per the TeamParticipationUsage page on the Launchpad wiki';
+
+
+SET search_path = ts2, pg_catalog;
+
+CREATE FUNCTION _ftq(text) RETURNS text
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+        import re
+
+        # I think this method would be more robust if we used a real
+        # tokenizer and parser to generate the query string, but we need
+        # something suitable for use as a stored procedure which currently
+        # means no external dependancies.
+
+        # Convert to Unicode
+        query = args[0].decode('utf8')
+        ## plpy.debug('1 query is %s' % repr(query))
+
+        # Normalize whitespace
+        query = re.sub("(?u)\s+"," ", query)
+
+        # Convert AND, OR, NOT and - to tsearch2 punctuation
+        query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
+        query = re.sub(r"(?u)\bAND\b", "&", query)
+        query = re.sub(r"(?u)\bOR\b", "|", query)
+        query = re.sub(r"(?u)\bNOT\b", " !", query)
+        ## plpy.debug('2 query is %s' % repr(query))
+
+        # Deal with unwanted punctuation. We convert strings of punctuation
+        # inside words to a '-' character for the hypenation handling below
+        # to deal with further. Outside of words we replace with whitespace.
+        # We don't mess with -&|!()' as they are handled later.
+        #punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
+        punctuation = r"[^\w\s\-\&\|\!\(\)']"
+        query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
+        query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
+        ## plpy.debug('3 query is %s' % repr(query))
+
+        # Strip ! characters inside and at the end of a word
+        query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
+
+        # Now that we have handle case sensitive booleans, convert to lowercase
+        query = query.lower()
+
+        # Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
+        # ((foo&bar&baz)|foobarbaz)
+        def hyphen_repl(match):
+            bits = match.group(0).split("-")
+            return "((%s)|%s)" % ("&".join(bits), "".join(bits))
+        query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
+        ## plpy.debug('4 query is %s' % repr(query))
+
+        # Any remaining - characters are spurious
+        query = query.replace('-','')
+
+        # Remove unpartnered bracket on the left and right
+        query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
+        query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
+
+        # Remove spurious brackets
+        query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
+        ## plpy.debug('5 query is %s' % repr(query))
+
+        # Insert & between tokens without an existing boolean operator
+        # ( not proceeded by (|&!
+        query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
+        ## plpy.debug('6 query is %s' % repr(query))
+        # ) not followed by )|&
+        query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
+        ## plpy.debug('6.1 query is %s' % repr(query))
+        # Whitespace not proceded by (|&! not followed by &|
+        query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
+        ## plpy.debug('7 query is %s' % repr(query))
+
+        # Detect and repair syntax errors - we are lenient because
+        # this input is generally from users.
+
+        # Fix unbalanced brackets
+        openings = query.count("(")
+        closings = query.count(")")
+        if openings > closings:
+            query = query + " ) "*(openings-closings)
+        elif closings > openings:
+            query = " ( "*(closings-openings) + query
+        ## plpy.debug('8 query is %s' % repr(query))
+
+        # Strip ' character that do not have letters on both sides
+        query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
+
+        # Brackets containing nothing but whitespace and booleans, recursive
+        last = ""
+        while last != query:
+            last = query
+            query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
+        ## plpy.debug('9 query is %s' % repr(query))
+
+        # An & or | following a (
+        query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
+        ## plpy.debug('10 query is %s' % repr(query))
+
+        # An &, | or ! immediatly before a )
+        query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
+        ## plpy.debug('11 query is %s' % repr(query))
+
+        # An &,| or ! followed by another boolean.
+        query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
+        ## plpy.debug('12 query is %s' % repr(query))
+
+        # Leading & or |
+        query = re.sub(r"(?u)^[\s\&\|]+", "", query)
+        ## plpy.debug('13 query is %s' % repr(query))
+
+        # Trailing &, | or !
+        query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
+        ## plpy.debug('14 query is %s' % repr(query))
+
+        # If we have nothing but whitespace and tsearch2 operators,
+        # return NULL.
+        if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
+            return None
+
+        # Convert back to UTF-8
+        query = query.encode('utf8')
+        ## plpy.debug('15 query is %s' % repr(query))
+        
+        return query or None
+        $_$;
+
+
+CREATE FUNCTION _get_parser_from_curcfg() RETURNS text
+    LANGUAGE sql IMMUTABLE STRICT
+    AS $$select prsname::text from pg_catalog.pg_ts_parser p join pg_ts_config c on cfgparser = p.oid where c.oid = show_curcfg();$$;
+
+
+CREATE FUNCTION concat(pg_catalog.tsvector, pg_catalog.tsvector) RETURNS pg_catalog.tsvector
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsvector_concat$$;
+
+
+CREATE FUNCTION dex_init(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_dex_init';
+
+
+CREATE FUNCTION dex_lexize(internal, internal, integer) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_dex_lexize';
+
+
+CREATE FUNCTION ftiupdate() RETURNS trigger
+    LANGUAGE plpythonu
+    AS $_$
+    new = TD["new"]
+    args = TD["args"][:]
+
+    # Short circuit if none of the relevant columns have been
+    # modified and fti is not being set to NULL (setting the fti
+    # column to NULL is thus how we can force a rebuild of the fti
+    # column).
+    if TD["event"] == "UPDATE" and new["fti"] != None:
+        old = TD["old"]
+        relevant_modification = False
+        for column_name in args[::2]:
+            if new[column_name] != old[column_name]:
+                relevant_modification = True
+                break
+        if not relevant_modification:
+            return "OK"
+
+    # Generate an SQL statement that turns the requested
+    # column values into a weighted tsvector
+    sql = []
+    for i in range(0, len(args), 2):
+        sql.append(
+                "ts2.setweight(ts2.to_tsvector('default', coalesce("
+                "substring(ltrim($%d) from 1 for 2500),'')),"
+                "CAST($%d AS \"char\"))" % (i + 1, i + 2))
+        args[i] = new[args[i]]
+
+    sql = "SELECT %s AS fti" % "||".join(sql)
+
+    # Execute and store in the fti column
+    plan = plpy.prepare(sql, ["text", "char"] * (len(args)/2))
+    new["fti"] = plpy.execute(plan, args, 1)[0]["fti"]
+
+    # Tell PostgreSQL we have modified the data
+    return "MODIFY"
+$_$;
+
+
+COMMENT ON FUNCTION ftiupdate() IS 'Trigger function that keeps the fti tsvector column up to date.';
+
+
+CREATE FUNCTION ftq(text) RETURNS pg_catalog.tsquery
+    LANGUAGE plpythonu IMMUTABLE STRICT
+    AS $_$
+        import re
+
+        # I think this method would be more robust if we used a real
+        # tokenizer and parser to generate the query string, but we need
+        # something suitable for use as a stored procedure which currently
+        # means no external dependancies.
+
+        # Convert to Unicode
+        query = args[0].decode('utf8')
+        ## plpy.debug('1 query is %s' % repr(query))
+
+        # Normalize whitespace
+        query = re.sub("(?u)\s+"," ", query)
+
+        # Convert AND, OR, NOT and - to tsearch2 punctuation
+        query = re.sub(r"(?u)(?:^|\s)-([\w\(])", r" !\1", query)
+        query = re.sub(r"(?u)\bAND\b", "&", query)
+        query = re.sub(r"(?u)\bOR\b", "|", query)
+        query = re.sub(r"(?u)\bNOT\b", " !", query)
+        ## plpy.debug('2 query is %s' % repr(query))
+
+        # Deal with unwanted punctuation. We convert strings of punctuation
+        # inside words to a '-' character for the hypenation handling below
+        # to deal with further. Outside of words we replace with whitespace.
+        # We don't mess with -&|!()' as they are handled later.
+        #punctuation = re.escape(r'`~@#$%^*+=[]{}:;"<>,.?\/')
+        punctuation = r"[^\w\s\-\&\|\!\(\)']"
+        query = re.sub(r"(?u)(\w)%s+(\w)" % (punctuation,), r"\1-\2", query)
+        query = re.sub(r"(?u)%s+" % (punctuation,), " ", query)
+        ## plpy.debug('3 query is %s' % repr(query))
+
+        # Strip ! characters inside and at the end of a word
+        query = re.sub(r"(?u)(?<=\w)[\!]+", " ", query)
+
+        # Now that we have handle case sensitive booleans, convert to lowercase
+        query = query.lower()
+
+        # Convert foo-bar to ((foo&bar)|foobar) and foo-bar-baz to
+        # ((foo&bar&baz)|foobarbaz)
+        def hyphen_repl(match):
+            bits = match.group(0).split("-")
+            return "((%s)|%s)" % ("&".join(bits), "".join(bits))
+        query = re.sub(r"(?u)\b\w+-[\w\-]+\b", hyphen_repl, query)
+        ## plpy.debug('4 query is %s' % repr(query))
+
+        # Any remaining - characters are spurious
+        query = query.replace('-','')
+
+        # Remove unpartnered bracket on the left and right
+        query = re.sub(r"(?ux) ^ ( [^(]* ) \)", r"(\1)", query)
+        query = re.sub(r"(?ux) \( ( [^)]* ) $", r"(\1)", query)
+
+        # Remove spurious brackets
+        query = re.sub(r"(?u)\(([^\&\|]*?)\)", r" \1 ", query)
+        ## plpy.debug('5 query is %s' % repr(query))
+
+        # Insert & between tokens without an existing boolean operator
+        # ( not proceeded by (|&!
+        query = re.sub(r"(?u)(?<![\(\|\&\!])\s*\(", "&(", query)
+        ## plpy.debug('6 query is %s' % repr(query))
+        # ) not followed by )|&
+        query = re.sub(r"(?u)\)(?!\s*(\)|\||\&|\s*$))", ")&", query)
+        ## plpy.debug('6.1 query is %s' % repr(query))
+        # Whitespace not proceded by (|&! not followed by &|
+        query = re.sub(r"(?u)(?<![\(\|\&\!\s])\s+(?![\&\|\s])", "&", query)
+        ## plpy.debug('7 query is %s' % repr(query))
+
+        # Detect and repair syntax errors - we are lenient because
+        # this input is generally from users.
+
+        # Fix unbalanced brackets
+        openings = query.count("(")
+        closings = query.count(")")
+        if openings > closings:
+            query = query + " ) "*(openings-closings)
+        elif closings > openings:
+            query = " ( "*(closings-openings) + query
+        ## plpy.debug('8 query is %s' % repr(query))
+
+        # Strip ' character that do not have letters on both sides
+        query = re.sub(r"(?u)((?<!\w)'|'(?!\w))", "", query)
+
+        # Brackets containing nothing but whitespace and booleans, recursive
+        last = ""
+        while last != query:
+            last = query
+            query = re.sub(r"(?u)\([\s\&\|\!]*\)", "", query)
+        ## plpy.debug('9 query is %s' % repr(query))
+
+        # An & or | following a (
+        query = re.sub(r"(?u)(?<=\()[\&\|\s]+", "", query)
+        ## plpy.debug('10 query is %s' % repr(query))
+
+        # An &, | or ! immediatly before a )
+        query = re.sub(r"(?u)[\&\|\!\s]*[\&\|\!]+\s*(?=\))", "", query)
+        ## plpy.debug('11 query is %s' % repr(query))
+
+        # An &,| or ! followed by another boolean.
+        query = re.sub(r"(?ux) \s* ( [\&\|\!] ) [\s\&\|]+", r"\1", query)
+        ## plpy.debug('12 query is %s' % repr(query))
+
+        # Leading & or |
+        query = re.sub(r"(?u)^[\s\&\|]+", "", query)
+        ## plpy.debug('13 query is %s' % repr(query))
+
+        # Trailing &, | or !
+        query = re.sub(r"(?u)[\&\|\!\s]+$", "", query)
+        ## plpy.debug('14 query is %s' % repr(query))
+
+        # If we have nothing but whitespace and tsearch2 operators,
+        # return NULL.
+        if re.search(r"(?u)^[\&\|\!\s\(\)]*$", query) is not None:
+            return None
+
+        # Convert back to UTF-8
+        query = query.encode('utf8')
+        ## plpy.debug('15 query is %s' % repr(query))
+        
+        p = plpy.prepare("SELECT to_tsquery('default', $1) AS x", ["text"])
+        query = plpy.execute(p, [query], 1)[0]["x"]
+        return query or None
+        $_$;
+
+
+COMMENT ON FUNCTION ftq(text) IS 'Convert a string to an unparsed tsearch2 query';
+
+
+CREATE FUNCTION get_covers(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS text
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_get_covers';
+
+
+CREATE FUNCTION headline(oid, text, pg_catalog.tsquery, text) RETURNS text
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_headline_byid_opt$$;
+
+
+CREATE FUNCTION headline(oid, text, pg_catalog.tsquery) RETURNS text
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_headline_byid$$;
+
+
+CREATE FUNCTION headline(text, text, pg_catalog.tsquery, text) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/tsearch2', 'tsa_headline_byname';
+
+
+CREATE FUNCTION headline(text, text, pg_catalog.tsquery) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/tsearch2', 'tsa_headline_byname';
+
+
+CREATE FUNCTION headline(text, pg_catalog.tsquery, text) RETURNS text
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_headline_opt$$;
+
+
+CREATE FUNCTION headline(text, pg_catalog.tsquery) RETURNS text
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_headline$$;
+
+
+CREATE FUNCTION length(pg_catalog.tsvector) RETURNS integer
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsvector_length$$;
+
+
+CREATE FUNCTION lexize(oid, text) RETURNS text[]
+    LANGUAGE internal STRICT
+    AS $$ts_lexize$$;
+
+
+CREATE FUNCTION lexize(text, text) RETURNS text[]
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_lexize_byname';
+
+
+CREATE FUNCTION lexize(text) RETURNS text[]
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_lexize_bycurrent';
+
+
+CREATE FUNCTION numnode(pg_catalog.tsquery) RETURNS integer
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsquery_numnode$$;
+
+
+CREATE FUNCTION parse(oid, text) RETURNS SETOF tokenout
+    LANGUAGE internal STRICT
+    AS $$ts_parse_byid$$;
+
+
+CREATE FUNCTION parse(text, text) RETURNS SETOF tokenout
+    LANGUAGE internal STRICT
+    AS $$ts_parse_byname$$;
+
+
+CREATE FUNCTION parse(text) RETURNS SETOF tokenout
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_parse_current';
+
+
+CREATE FUNCTION plainto_tsquery(oid, text) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$plainto_tsquery_byid$$;
+
+
+CREATE FUNCTION plainto_tsquery(text, text) RETURNS pg_catalog.tsquery
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/tsearch2', 'tsa_plainto_tsquery_name';
+
+
+CREATE FUNCTION plainto_tsquery(text) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$plainto_tsquery$$;
+
+
+CREATE FUNCTION prsd_end(internal) RETURNS void
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_prsd_end';
+
+
+CREATE FUNCTION prsd_getlexeme(internal, internal, internal) RETURNS integer
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_prsd_getlexeme';
+
+
+CREATE FUNCTION prsd_headline(internal, internal, internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_prsd_headline';
+
+
+CREATE FUNCTION prsd_lextype(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_prsd_lextype';
+
+
+CREATE FUNCTION prsd_start(internal, integer) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_prsd_start';
+
+
+CREATE FUNCTION querytree(pg_catalog.tsquery) RETURNS text
+    LANGUAGE internal STRICT
+    AS $$tsquerytree$$;
+
+
+CREATE FUNCTION rank(real[], pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rank_wtt$$;
+
+
+CREATE FUNCTION rank(real[], pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rank_wttf$$;
+
+
+CREATE FUNCTION rank(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rank_tt$$;
+
+
+CREATE FUNCTION rank(pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rank_ttf$$;
+
+
+CREATE FUNCTION rank_cd(real[], pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rankcd_wtt$$;
+
+
+CREATE FUNCTION rank_cd(real[], pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rankcd_wttf$$;
+
+
+CREATE FUNCTION rank_cd(pg_catalog.tsvector, pg_catalog.tsquery) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rankcd_tt$$;
+
+
+CREATE FUNCTION rank_cd(pg_catalog.tsvector, pg_catalog.tsquery, integer) RETURNS real
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$ts_rankcd_ttf$$;
+
+
+CREATE FUNCTION reset_tsearch() RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_reset_tsearch';
+
+
+CREATE FUNCTION rewrite(pg_catalog.tsquery, text) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsquery_rewrite_query$$;
+
+
+CREATE FUNCTION rewrite(pg_catalog.tsquery, pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsquery_rewrite$$;
+
+
+CREATE FUNCTION rewrite_accum(pg_catalog.tsquery, pg_catalog.tsquery[]) RETURNS pg_catalog.tsquery
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_rewrite_accum';
+
+
+CREATE FUNCTION rewrite_finish(pg_catalog.tsquery) RETURNS pg_catalog.tsquery
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_rewrite_finish';
+
+
+CREATE FUNCTION set_curcfg(integer) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_set_curcfg';
+
+
+CREATE FUNCTION set_curcfg(text) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_set_curcfg_byname';
+
+
+CREATE FUNCTION set_curdict(integer) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_set_curdict';
+
+
+CREATE FUNCTION set_curdict(text) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_set_curdict_byname';
+
+
+CREATE FUNCTION set_curprs(integer) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_set_curprs';
+
+
+CREATE FUNCTION set_curprs(text) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_set_curprs_byname';
+
+
+CREATE FUNCTION setweight(pg_catalog.tsvector, "char") RETURNS pg_catalog.tsvector
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsvector_setweight$$;
+
+
+CREATE FUNCTION show_curcfg() RETURNS oid
+    LANGUAGE internal STABLE STRICT
+    AS $$get_current_ts_config$$;
+
+
+CREATE FUNCTION snb_en_init(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_snb_en_init';
+
+
+CREATE FUNCTION snb_lexize(internal, internal, integer) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_snb_lexize';
+
+
+CREATE FUNCTION snb_ru_init(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_snb_ru_init';
+
+
+CREATE FUNCTION snb_ru_init_koi8(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_snb_ru_init_koi8';
+
+
+CREATE FUNCTION snb_ru_init_utf8(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_snb_ru_init_utf8';
+
+
+CREATE FUNCTION spell_init(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_spell_init';
+
+
+CREATE FUNCTION spell_lexize(internal, internal, integer) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_spell_lexize';
+
+
+CREATE FUNCTION stat(text) RETURNS SETOF statinfo
+    LANGUAGE internal STRICT
+    AS $$ts_stat1$$;
+
+
+CREATE FUNCTION stat(text, text) RETURNS SETOF statinfo
+    LANGUAGE internal STRICT
+    AS $$ts_stat2$$;
+
+
+CREATE FUNCTION strip(pg_catalog.tsvector) RETURNS pg_catalog.tsvector
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsvector_strip$$;
+
+
+CREATE FUNCTION syn_init(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_syn_init';
+
+
+CREATE FUNCTION syn_lexize(internal, internal, integer) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_syn_lexize';
+
+
+CREATE FUNCTION thesaurus_init(internal) RETURNS internal
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_thesaurus_init';
+
+
+CREATE FUNCTION thesaurus_lexize(internal, internal, integer, internal) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/tsearch2', 'tsa_thesaurus_lexize';
+
+
+CREATE FUNCTION to_tsquery(oid, text) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$to_tsquery_byid$$;
+
+
+CREATE FUNCTION to_tsquery(text, text) RETURNS pg_catalog.tsquery
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/tsearch2', 'tsa_to_tsquery_name';
+
+
+CREATE FUNCTION to_tsquery(text) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$to_tsquery$$;
+
+
+CREATE FUNCTION to_tsvector(oid, text) RETURNS pg_catalog.tsvector
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$to_tsvector_byid$$;
+
+
+CREATE FUNCTION to_tsvector(text, text) RETURNS pg_catalog.tsvector
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/tsearch2', 'tsa_to_tsvector_name';
+
+
+CREATE FUNCTION to_tsvector(text) RETURNS pg_catalog.tsvector
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$to_tsvector$$;
+
+
+CREATE FUNCTION token_type(integer) RETURNS SETOF tokentype
+    LANGUAGE internal STRICT ROWS 16
+    AS $$ts_token_type_byid$$;
+
+
+CREATE FUNCTION token_type(text) RETURNS SETOF tokentype
+    LANGUAGE internal STRICT ROWS 16
+    AS $$ts_token_type_byname$$;
+
+
+CREATE FUNCTION token_type() RETURNS SETOF tokentype
+    LANGUAGE c STRICT ROWS 16
+    AS '$libdir/tsearch2', 'tsa_token_type_current';
+
+
+CREATE FUNCTION ts_debug(text) RETURNS SETOF tsdebug
+    LANGUAGE sql STRICT
+    AS $_$
+select
+        (select c.cfgname::text from pg_catalog.pg_ts_config as c
+         where c.oid = show_curcfg()),
+        t.alias as tok_type,
+        t.descr as description,
+        p.token,
+        ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary::pg_catalog.text
+                FROM pg_catalog.pg_ts_config_map AS m
+                WHERE m.mapcfg = show_curcfg() AND m.maptokentype = p.tokid
+                ORDER BY m.mapseqno )
+        AS dict_name,
+        strip(to_tsvector(p.token)) as tsvector
+from
+        parse( _get_parser_from_curcfg(), $1 ) as p,
+        token_type() as t
+where
+        t.tokid = p.tokid
+$_$;
+
+
+CREATE FUNCTION tsearch2() RETURNS trigger
+    LANGUAGE c
+    AS '$libdir/tsearch2', 'tsa_tsearch2';
+
+
+CREATE FUNCTION tsq_mcontained(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS boolean
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsq_mcontained$$;
+
+
+CREATE FUNCTION tsq_mcontains(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS boolean
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsq_mcontains$$;
+
+
+CREATE FUNCTION tsquery_and(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsquery_and$$;
+
+
+CREATE FUNCTION tsquery_not(pg_catalog.tsquery) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsquery_not$$;
+
+
+CREATE FUNCTION tsquery_or(pg_catalog.tsquery, pg_catalog.tsquery) RETURNS pg_catalog.tsquery
+    LANGUAGE internal IMMUTABLE STRICT
+    AS $$tsquery_or$$;
+
+
+SET search_path = public, pg_catalog;
+
+CREATE OPERATOR > (
+    PROCEDURE = debversion_gt,
+    LEFTARG = debversion,
+    RIGHTARG = debversion,
+    COMMUTATOR = <,
+    NEGATOR = >=
+);
+
+
+COMMENT ON OPERATOR > (debversion, debversion) IS 'debversion greater-than';
+
+
+CREATE AGGREGATE max(debversion) (
+    SFUNC = debversion_larger,
+    STYPE = debversion,
+    SORTOP = >
+);
+
+
+CREATE OPERATOR < (
+    PROCEDURE = debversion_lt,
+    LEFTARG = debversion,
+    RIGHTARG = debversion,
+    COMMUTATOR = >,
+    NEGATOR = >=
+);
+
+
+COMMENT ON OPERATOR < (debversion, debversion) IS 'debversion less-than';
+
+
+CREATE AGGREGATE min(debversion) (
+    SFUNC = debversion_smaller,
+    STYPE = debversion,
+    SORTOP = <
+);
+
+
+SET search_path = ts2, pg_catalog;
+
+CREATE AGGREGATE rewrite(pg_catalog.tsquery[]) (
+    SFUNC = rewrite_accum,
+    STYPE = pg_catalog.tsquery,
+    FINALFUNC = rewrite_finish
+);
+
+
+SET search_path = public, pg_catalog;
+
+CREATE OPERATOR <= (
+    PROCEDURE = debversion_le,
+    LEFTARG = debversion,
+    RIGHTARG = debversion,
+    COMMUTATOR = >=,
+    NEGATOR = >
+);
+
+
+COMMENT ON OPERATOR <= (debversion, debversion) IS 'debversion less-than-or-equal';
+
+
+CREATE OPERATOR <> (
+    PROCEDURE = debversion_ne,
+    LEFTARG = debversion,
+    RIGHTARG = debversion,
+    COMMUTATOR = <>,
+    NEGATOR = =
+);
+
+
+COMMENT ON OPERATOR <> (debversion, debversion) IS 'debversion not equal';
+
+
+CREATE OPERATOR = (
+    PROCEDURE = debversion_eq,
+    LEFTARG = debversion,
+    RIGHTARG = debversion,
+    COMMUTATOR = =,
+    NEGATOR = <>
+);
+
+
+COMMENT ON OPERATOR = (debversion, debversion) IS 'debversion equal';
+
+
+CREATE OPERATOR >= (
+    PROCEDURE = debversion_ge,
+    LEFTARG = debversion,
+    RIGHTARG = debversion,
+    COMMUTATOR = <=,
+    NEGATOR = <
+);
+
+
+COMMENT ON OPERATOR >= (debversion, debversion) IS 'debversion greater-than-or-equal';
+
+
+CREATE OPERATOR FAMILY debversion_ops USING btree;
+
+
+CREATE OPERATOR CLASS debversion_ops
+    DEFAULT FOR TYPE debversion USING btree AS
+    OPERATOR 1 <(debversion,debversion) ,
+    OPERATOR 2 <=(debversion,debversion) ,
+    OPERATOR 3 =(debversion,debversion) ,
+    OPERATOR 4 >=(debversion,debversion) ,
+    OPERATOR 5 >(debversion,debversion) ,
+    FUNCTION 1 debversion_cmp(debversion,debversion);
+
+
+CREATE OPERATOR FAMILY debversion_ops USING hash;
+
+
+CREATE OPERATOR CLASS debversion_ops
+    DEFAULT FOR TYPE debversion USING hash AS
+    OPERATOR 1 =(debversion,debversion) ,
+    FUNCTION 1 debversion_hash(debversion);
+
+
+SET search_path = ts2, pg_catalog;
+
+CREATE OPERATOR FAMILY tsquery_ops USING btree;
+
+
+CREATE OPERATOR CLASS tsquery_ops
+    FOR TYPE pg_catalog.tsquery USING btree AS
+    OPERATOR 1 <(pg_catalog.tsquery,pg_catalog.tsquery) ,
+    OPERATOR 2 <=(pg_catalog.tsquery,pg_catalog.tsquery) ,
+    OPERATOR 3 =(pg_catalog.tsquery,pg_catalog.tsquery) ,
+    OPERATOR 4 >=(pg_catalog.tsquery,pg_catalog.tsquery) ,
+    OPERATOR 5 >(pg_catalog.tsquery,pg_catalog.tsquery) ,
+    FUNCTION 1 tsquery_cmp(pg_catalog.tsquery,pg_catalog.tsquery);
+
+
+CREATE OPERATOR FAMILY tsvector_ops USING btree;
+
+
+CREATE OPERATOR CLASS tsvector_ops
+    FOR TYPE pg_catalog.tsvector USING btree AS
+    OPERATOR 1 <(pg_catalog.tsvector,pg_catalog.tsvector) ,
+    OPERATOR 2 <=(pg_catalog.tsvector,pg_catalog.tsvector) ,
+    OPERATOR 3 =(pg_catalog.tsvector,pg_catalog.tsvector) ,
+    OPERATOR 4 >=(pg_catalog.tsvector,pg_catalog.tsvector) ,
+    OPERATOR 5 >(pg_catalog.tsvector,pg_catalog.tsvector) ,
+    FUNCTION 1 tsvector_cmp(pg_catalog.tsvector,pg_catalog.tsvector);
+
+
+SET search_path = pg_catalog;
+
+CREATE CAST (character AS public.debversion) WITH FUNCTION public.debversion(character);
+
+
+CREATE CAST (public.debversion AS character) WITHOUT FUNCTION AS ASSIGNMENT;
+
+
+CREATE CAST (public.debversion AS text) WITHOUT FUNCTION AS IMPLICIT;
+
+
+CREATE CAST (public.debversion AS character varying) WITHOUT FUNCTION AS IMPLICIT;
+
+
+CREATE CAST (text AS public.debversion) WITHOUT FUNCTION AS ASSIGNMENT;
+
+
+CREATE CAST (character varying AS public.debversion) WITHOUT FUNCTION AS ASSIGNMENT;
+
+
+SET search_path = ts2, pg_catalog;
+
+CREATE TEXT SEARCH CONFIGURATION "default" (
+    PARSER = pg_catalog."default" );
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR asciiword WITH english_stem;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR word WITH english_stem;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR numword WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR email WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR url WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR host WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR sfloat WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR version WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR hword_numpart WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR hword_part WITH english_stem;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR hword_asciipart WITH english_stem;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR numhword WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR asciihword WITH english_stem;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR hword WITH english_stem;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR url_path WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR file WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR "float" WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR "int" WITH simple;
+
+ALTER TEXT SEARCH CONFIGURATION "default"
+    ADD MAPPING FOR uint WITH simple;
+
+
+SET search_path = public, pg_catalog;
+
+CREATE TABLE accesspolicy (
+    id integer NOT NULL,
+    product integer,
+    distribution integer,
+    type integer NOT NULL,
+    CONSTRAINT has_target CHECK (((product IS NULL) <> (distribution IS NULL)))
+);
+
+
+CREATE SEQUENCE accesspolicy_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+ALTER SEQUENCE accesspolicy_id_seq OWNED BY accesspolicy.id;
+
+
+CREATE TABLE accesspolicyartifact (
+    id integer NOT NULL,
+    bug integer,
+    branch integer,
+    policy integer,
+    CONSTRAINT has_artifact CHECK (((bug IS NULL) <> (branch IS NULL)))
+);
+
+
+CREATE SEQUENCE accesspolicyartifact_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+ALTER SEQUENCE accesspolicyartifact_id_seq OWNED BY accesspolicyartifact.id;
+
+
+CREATE TABLE accesspolicygrant (
+    id integer NOT NULL,
+    grantee integer NOT NULL,
+    grantor integer NOT NULL,
+    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
+    policy integer,
+    artifact integer,
+    CONSTRAINT has_target CHECK (((policy IS NULL) <> (artifact IS NULL)))
+);
+
+
+CREATE SEQUENCE accesspolicygrant_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+ALTER SEQUENCE accesspolicygrant_id_seq OWNED BY accesspolicygrant.id;
+
 
 CREATE TABLE account (
     id integer NOT NULL,
@@ -63,36 +4563,64 @@
     status integer NOT NULL,
     date_status_set timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
     displayname text NOT NULL,
-    openid_identifier text DEFAULT generate_openid_identifier() NOT NULL,
-    status_comment text,
-    old_openid_identifier text
+    status_comment text
 );
 
+
+COMMENT ON TABLE account IS 'An account that may be used for authenticating to Canonical or other systems.';
+
+
+COMMENT ON COLUMN account.status IS 'The status of the account.';
+
+
+COMMENT ON COLUMN account.date_status_set IS 'When the status was last changed.';
+
+
+COMMENT ON COLUMN account.displayname IS 'Name to display when rendering information about this account.';
+
+
+COMMENT ON COLUMN account.status_comment IS 'The comment on the status of the account.';
+
+
 CREATE SEQUENCE account_id_seq
+    START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
 
+
 ALTER SEQUENCE account_id_seq OWNED BY account.id;
 
+
 CREATE TABLE accountpassword (
     id integer NOT NULL,
     account integer NOT NULL,
     password text NOT NULL
 );
 
+
+COMMENT ON TABLE accountpassword IS 'A password used to authenticate an Account.';
+
+
+COMMENT ON COLUMN accountpassword.password IS 'SSHA digest encrypted password.';
+
+
 CREATE SEQUENCE accountpassword_id_seq
+    START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
 
+
 ALTER SEQUENCE accountpassword_id_seq OWNED BY accountpassword.id;
 
+
 CREATE VIEW alllocks AS
     SELECT a.procpid, a.usename, (now() - a.query_start) AS age, c.relname, l.mode, l.granted, a.current_query FROM ((pg_locks l JOIN pg_class c ON ((l.relation = c.oid))) LEFT JOIN pg_stat_activity a ON ((a.procpid = l.pid)));
 
+
 CREATE TABLE announcement (
     id integer NOT NULL,
     date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
@@ -110,14 +4638,30 @@
     CONSTRAINT valid_url CHECK (valid_absolute_url(url))
 );
 
+
+COMMENT ON TABLE announcement IS 'A project announcement. This is a single item of news or information that the project is communicating. Announcements can be attached to a Project, a Product or a Distribution.';
+
+
+COMMENT ON COLUMN announcement.date_announced IS 'The date at which an announcement will become public, if it is active. If this is not set then the announcement will not become public until someone consciously publishes it (which sets this date).';
+
+
+COMMENT ON COLUMN announcement.url IS 'A web location for the announcement itself.';
+
+
+COMMENT ON COLUMN announcement.active IS 'Whether or not the announcement is public. This is TRUE by default, but can be set to FALSE if the project "retracts" the announcement.';
+
+
 CREATE SEQUENCE announcement_id_seq
+    START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
 
+
 ALTER SEQUENCE announcement_id_seq OWNED BY announcement.id;
 
+
 CREATE TABLE answercontact (
     id integer NOT NULL,
     product integer,
@@ -128,14 +4672,36 @@
     CONSTRAINT valid_target CHECK ((((product IS NULL) <> (distribution IS NULL)) AND ((product IS NULL) OR (sourcepackagename IS NULL))))
 );
 
+
+COMMENT ON TABLE answercontact IS 'Defines the answer contact for a given question target. The answer contact will be automatically notified about changes to any questions filed on the question target.';
+
+
+COMMENT ON COLUMN answercontact.product IS 'The product that the answer contact supports.';
+
+
+COMMENT ON COLUMN answercontact.distribution IS 'The distribution that the answer contact supports.';
+
+
+COMMENT ON COLUMN answercontact.sourcepackagename IS 'The sourcepackagename that the answer contact supports.';
+
+
+COMMENT ON COLUMN answercontact.person IS 'The person or team associated with the question target.';
+
+
+COMMENT ON COLUMN answercontact.date_created IS 'The date the answer contact was submitted.';
+
+
 CREATE SEQUENCE answercontact_id_seq
+    START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
 
+
 ALTER SEQUENCE answercontact_id_seq OWNED BY answercontact.id;
 
+
 CREATE TABLE apportjob (
     id integer NOT NULL,
     job integer NOT NULL,
@@ -144,14 +4710,30 @@
     json_data text
 );
 
+
+COMMENT ON TABLE apportjob IS 'Contains references to jobs to be run against Apport BLOBs.';
+
+
+COMMENT ON COLUMN apportjob.blob IS 'The TemporaryBlobStorage entry on which the job is to be run.';
+
+
+COMMENT ON COLUMN apportjob.job_type IS 'The type of job (enumeration value). Allows us to query the database for a given subset of ApportJobs.';
+
+
+COMMENT ON COLUMN apportjob.json_data IS 'A JSON struct containing data for the job.';
+
+
 CREATE SEQUENCE apportjob_id_seq
+    START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO MINVALUE
     CACHE 1;
 
+
 ALTER SEQUENCE apportjob_id_seq OWNED BY apportjob.id;
 
+
 CREATE TABLE archive (
     id integer NOT NULL,
     owner integer NOT NULL,
@@ -189,28 +4771,132 @@
     CONSTRAINT valid_name CHECK (valid_name(name))
 );
 
+
+COMMENT ON TABLE archive IS 'A package archive. Commonly either a distribution''s main_archive or a ppa''s archive.';
+
+
+COMMENT ON COLUMN archive.owner IS 'Identifies the PPA owner when it has one.';
+
+
+COMMENT ON COLUMN archive.description IS 'Allow users to describe their PPAs content.';
+
+
+COMMENT ON COLUMN archive.enabled IS 'Whether or not the PPA is enabled for accepting uploads.';
+
+
+COMMENT ON COLUMN archive.authorized_size IS 'Size, in MiB, allowed for this PPA.';
+
+
+COMMENT ON COLUMN archive.distribution IS 'The distribution that uses this archive.';
+
+
+COMMENT ON COLUMN archive.purpose IS 'The purpose of this archive, e.g. COMMERCIAL.  See the ArchivePurpose DBSchema item.';
+
+
+COMMENT ON COLUMN archive.private IS 'Whether or not the archive is private. This affects the global visibility of the archive.';
+
+
+COMMENT ON COLUMN archive.sources_cached IS 'Number of sources already cached for this archive.';
+
+
+COMMENT ON COLUMN archive.binaries_cached IS 'Number of binaries already cached for this archive.';
+
+
+COMMENT ON COLUMN archive.package_description_cache IS 'Text blob containing all source and binary names and descriptions concatenated. Used to to build the tsearch indexes on this table.';
+
+
+COMMENT ON COLUMN archive.require_virtualized IS 'Whether this archive has binaries that should be built on a virtual machine, e.g. PPAs';
+
+
+COMMENT ON COLUMN archive.name IS 'The name of the archive.';
+
+
+COMMENT ON COLUMN archive.publish IS 'Whether this archive should be published.';
+
+
+COMMENT ON COLUMN archive.date_updated IS 'When were the rebuild statistics last updated?';
+
+
+COMMENT ON COLUMN archive.total_count IS 'How many source packages are in the rebuild archive altogether?';
+
+
+COMMENT ON COLUMN archive.pending_count IS 'How many packages still need building?';
+
+
+COMMENT ON COLUMN archive.succeeded_count IS 'How many source packages were built sucessfully?';
+
+
+COMMENT ON COLUMN archive.failed_count IS 'How many packages failed to build?';
+
+
+COMMENT ON COLUMN archive.building_count IS 'How many packages are building at present?';
+
+
+COMMENT ON COLUMN archive.signing_key IS 'The GpgKey used for signing this archive.';
+
+
+COMMENT ON COLUMN archive.removed_binary_retention_days IS 'The number of days before superseded or deleted binary files are expired in the librarian, or zero for never.';
+
+
+COMMENT ON COLUMN archive.num_old_versions_published IS 'The number of versions of a package to keep published before older versions are superseded.';
+
+
+COMMENT ON COLUMN archive.displayname IS 'User defined displayname for this archive.';
+
+
+COMMENT ON COLUMN archive.relative_build_score IS 'A delta to the build score that is applied to all builds in this archive.';
+
+
+COMMENT ON COLUMN archive.external_dependencies IS 'Newline-separated list of repositories to be used to retrieve any external build dependencies when building packages in this archive, in the format: deb http[s]://[user:pass@]<host>[/path] %(series)s[-pocket] [components]  The series variable is replaced with the series name of the context build.  This column is specifically and only intended for OEM migration to Launchpad and should be re-examined in October 2010 to see if it is still relevant.';
+
+
+COMMENT ON COLUMN archive.status IS 'The status of this archive, e.g. ACTIVE.  See the ArchiveState DBSchema item.';
+