~launchpad-pqm/launchpad/devel

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

	tuple_len bigint,

	tuple_percent double precision,

	dead_tuple_count bigint,

	dead_tuple_len bigint,

	dead_tuple_percent double precision,

	free_space bigint,

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