-- Generated Tue Dec  6 20:57:32 2011 UTC

SET escape_string_warning = off;

CREATE SCHEMA todrop;


CREATE SCHEMA ts2;


CREATE PROCEDURAL LANGUAGE plpgsql;


CREATE PROCEDURAL LANGUAGE plpythonu;



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



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;



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).';




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
    LANGUAGE c STRICT
    AS '$libdir/pgstattuple', 'pgstattuple';


CREATE FUNCTION pgstattuple(oid) RETURNS pgstattuple_type
    LANGUAGE c STRICT
    AS '$libdir/pgstattuple', 'pgstattuplebyid';


CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    LANGUAGE c
    AS '$libdir/plpgsql', 'plpgsql_call_handler';


CREATE FUNCTION plpython_call_handler() RETURNS language_handler
    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';



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



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



CREATE AGGREGATE rewrite(pg_catalog.tsquery[]) (
    SFUNC = rewrite_accum,
    STYPE = pg_catalog.tsquery,
    FINALFUNC = rewrite_finish
);



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



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



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;



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;



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,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    creation_rationale integer NOT NULL,
    status integer NOT NULL,
    date_status_set timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    displayname text NOT NULL,
    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,
    date_announced timestamp without time zone,
    registrant integer NOT NULL,
    product integer,
    distribution integer,
    project integer,
    title text NOT NULL,
    summary text,
    url text,
    active boolean DEFAULT true NOT NULL,
    date_updated timestamp without time zone,
    CONSTRAINT has_target CHECK ((((product IS NOT NULL) OR (project IS NOT NULL)) OR (distribution IS NOT NULL))),
    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,
    distribution integer,
    sourcepackagename integer,
    person integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    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,
    blob integer NOT NULL,
    job_type integer NOT NULL,
    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,
    description text,
    enabled boolean DEFAULT true NOT NULL,
    authorized_size integer,
    distribution integer NOT NULL,
    purpose integer NOT NULL,
    private boolean DEFAULT false NOT NULL,
    sources_cached integer,
    binaries_cached integer,
    package_description_cache text,
    fti ts2.tsvector,
    buildd_secret text,
    require_virtualized boolean DEFAULT true NOT NULL,
    name text DEFAULT 'default'::text NOT NULL,
    publish boolean DEFAULT true NOT NULL,
    date_updated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    total_count integer DEFAULT 0 NOT NULL,
    pending_count integer DEFAULT 0 NOT NULL,
    succeeded_count integer DEFAULT 0 NOT NULL,
    failed_count integer DEFAULT 0 NOT NULL,
    building_count integer DEFAULT 0 NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    signing_key integer,
    removed_binary_retention_days integer,
    num_old_versions_published integer,
    displayname text NOT NULL,
    relative_build_score integer DEFAULT 0 NOT NULL,
    external_dependencies text,
    status integer DEFAULT 0 NOT NULL,
    commercial boolean DEFAULT false NOT NULL,
    build_debug_symbols boolean DEFAULT false NOT NULL,
    CONSTRAINT valid_buildd_secret CHECK ((((private = true) AND (buildd_secret IS NOT NULL)) OR (private = false))),
    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.';


COMMENT ON COLUMN archive.commercial IS 'Whether this archive is a commercial Archive and should appear in the Software Center.';


COMMENT ON COLUMN archive.build_debug_symbols IS 'Whether builds for this archive should create debug symbol packages.';


CREATE SEQUENCE archive_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archive_id_seq OWNED BY archive.id;


CREATE TABLE archivearch (
    id integer NOT NULL,
    archive integer NOT NULL,
    processorfamily integer NOT NULL
);


COMMENT ON TABLE archivearch IS 'ArchiveArch: A table that allows a user to specify which architectures an archive requires or supports.';


COMMENT ON COLUMN archivearch.archive IS 'The archive for which an architecture is specified.';


COMMENT ON COLUMN archivearch.processorfamily IS 'The architecture specified for the archive on hand.';


CREATE SEQUENCE archivearch_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archivearch_id_seq OWNED BY archivearch.id;


CREATE TABLE archiveauthtoken (
    id integer NOT NULL,
    archive integer NOT NULL,
    person integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    date_deactivated timestamp without time zone,
    token text NOT NULL
);


COMMENT ON TABLE archiveauthtoken IS 'Authorisation tokens to use in .htaccess for published archives.';


COMMENT ON COLUMN archiveauthtoken.archive IS 'The archive to which this token refers.';


COMMENT ON COLUMN archiveauthtoken.person IS 'The person to which this token applies.';


COMMENT ON COLUMN archiveauthtoken.date_created IS 'The date and time this token was created.';


COMMENT ON COLUMN archiveauthtoken.date_deactivated IS 'The date and time this token was deactivated.';


COMMENT ON COLUMN archiveauthtoken.token IS 'The token text for this authorisation.';


CREATE SEQUENCE archiveauthtoken_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archiveauthtoken_id_seq OWNED BY archiveauthtoken.id;


CREATE TABLE archivedependency (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    archive integer NOT NULL,
    dependency integer NOT NULL,
    pocket integer NOT NULL,
    component integer,
    CONSTRAINT distinct_archives CHECK ((archive <> dependency))
);


COMMENT ON TABLE archivedependency IS 'This table maps a given archive to all other archives it should depend on.';


COMMENT ON COLUMN archivedependency.date_created IS 'Instant when the dependency was created.';


COMMENT ON COLUMN archivedependency.archive IS 'The archive where the dependency should be applied.';


COMMENT ON COLUMN archivedependency.dependency IS 'The archive to depend on.';


CREATE SEQUENCE archivedependency_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archivedependency_id_seq OWNED BY archivedependency.id;


CREATE TABLE archivejob (
    id integer NOT NULL,
    job integer NOT NULL,
    archive integer NOT NULL,
    job_type integer NOT NULL,
    json_data text
);


COMMENT ON TABLE archivejob IS 'Contains references to jobs to be run against Archives.';


COMMENT ON COLUMN archivejob.archive IS 'The archive on which the job is to be run.';


COMMENT ON COLUMN archivejob.job_type IS 'The type of job (enumeration value). Allows us to query the database for a given subset of ArchiveJobs.';


COMMENT ON COLUMN archivejob.json_data IS 'A JSON struct containing data for the job.';


CREATE SEQUENCE archivejob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archivejob_id_seq OWNED BY archivejob.id;


CREATE TABLE archivepermission (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    person integer NOT NULL,
    permission integer NOT NULL,
    archive integer NOT NULL,
    component integer,
    sourcepackagename integer,
    packageset integer,
    explicit boolean DEFAULT false NOT NULL,
    CONSTRAINT one_target CHECK ((null_count(ARRAY[packageset, component, sourcepackagename]) = 2))
);


COMMENT ON TABLE archivepermission IS 'ArchivePermission: A record of who has permission to upload and approve uploads to an archive (and hence a distribution)';


COMMENT ON COLUMN archivepermission.date_created IS 'The date that this permission was created.';


COMMENT ON COLUMN archivepermission.person IS 'The person or team to whom the permission is being granted.';


COMMENT ON COLUMN archivepermission.permission IS 'The permission type being granted.';


COMMENT ON COLUMN archivepermission.archive IS 'The archive to which this permission applies.';


COMMENT ON COLUMN archivepermission.component IS 'The component to which this upload permission applies.';


COMMENT ON COLUMN archivepermission.sourcepackagename IS 'The source package name to which this permission applies.  This can be used to provide package-level permissions to single users.';


COMMENT ON COLUMN archivepermission.packageset IS 'The package set to which this permission applies.';


COMMENT ON COLUMN archivepermission.explicit IS 'This flag is set for package sets containing high-profile packages that must not break and/or require specialist skills for proper handling e.g. the kernel.';


CREATE SEQUENCE archivepermission_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archivepermission_id_seq OWNED BY archivepermission.id;


CREATE TABLE archivesubscriber (
    id integer NOT NULL,
    archive integer NOT NULL,
    registrant integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    subscriber integer NOT NULL,
    date_expires timestamp without time zone,
    status integer NOT NULL,
    description text,
    date_cancelled timestamp without time zone,
    cancelled_by integer
);


COMMENT ON TABLE archivesubscriber IS 'An authorised person or team subscription to an archive.';


COMMENT ON COLUMN archivesubscriber.archive IS 'The archive that the subscriber is authorised to see.';


COMMENT ON COLUMN archivesubscriber.registrant IS 'The person who authorised this subscriber.';


COMMENT ON COLUMN archivesubscriber.date_created IS 'The date and time this subscription was created.';


COMMENT ON COLUMN archivesubscriber.subscriber IS 'The person or team that this subscription refers to.';


COMMENT ON COLUMN archivesubscriber.date_expires IS 'The date and time this subscription will expire. If NULL, it does not expire.';


COMMENT ON COLUMN archivesubscriber.status IS 'The status of the subscription, e.g. PENDING, ACTIVE, CANCELLING, CANCELLED.';


COMMENT ON COLUMN archivesubscriber.description IS 'An optional note for the archive owner to describe the subscription.';


COMMENT ON COLUMN archivesubscriber.date_cancelled IS 'The date and time this subscription was revoked.';


COMMENT ON COLUMN archivesubscriber.cancelled_by IS 'The person who revoked this subscription.';


CREATE SEQUENCE archivesubscriber_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE archivesubscriber_id_seq OWNED BY archivesubscriber.id;


CREATE TABLE binarypackagename (
    id integer NOT NULL,
    name text NOT NULL,
    CONSTRAINT valid_name CHECK (valid_name(name))
);


COMMENT ON TABLE binarypackagename IS 'BinaryPackageName: A soyuz binary package name.';


COMMENT ON COLUMN binarypackagename.name IS 'A lowercase name identifying one or more binarypackages';


CREATE TABLE sourcepackagename (
    id integer NOT NULL,
    name text NOT NULL,
    CONSTRAINT valid_name CHECK (valid_name(name))
);


COMMENT ON TABLE sourcepackagename IS 'SourcePackageName: A soyuz source package name.';


COMMENT ON COLUMN sourcepackagename.name IS 'A lowercase name identifying one or more sourcepackages';


CREATE VIEW binaryandsourcepackagenameview AS
    SELECT binarypackagename.name FROM binarypackagename UNION SELECT sourcepackagename.name FROM sourcepackagename;


CREATE TABLE binarypackagebuild (
    id integer NOT NULL,
    package_build integer NOT NULL,
    distro_arch_series integer NOT NULL,
    source_package_release integer NOT NULL
);


COMMENT ON TABLE binarypackagebuild IS 'BinaryPackageBuild: This table links a package build with a distroarchseries and sourcepackagerelease.';


COMMENT ON COLUMN binarypackagebuild.package_build IS 'Points to the related package build with the base information.';


COMMENT ON COLUMN binarypackagebuild.distro_arch_series IS 'Points the target DistroArchSeries for this build.';


COMMENT ON COLUMN binarypackagebuild.source_package_release IS 'SourcePackageRelease which originated this build.';


CREATE SEQUENCE binarypackagebuild_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagebuild_id_seq OWNED BY binarypackagebuild.id;


CREATE TABLE binarypackagefile (
    binarypackagerelease integer NOT NULL,
    libraryfile integer NOT NULL,
    filetype integer NOT NULL,
    id integer DEFAULT nextval(('binarypackagefile_id_seq'::text)::regclass) NOT NULL
);


COMMENT ON TABLE binarypackagefile IS 'BinaryPackageFile: A soyuz <-> librarian link table. This table represents the ownership in the librarian of a file which represents a binary package';


COMMENT ON COLUMN binarypackagefile.binarypackagerelease IS 'The binary package which is represented by the file';


COMMENT ON COLUMN binarypackagefile.libraryfile IS 'The file in the librarian which represents the package';


COMMENT ON COLUMN binarypackagefile.filetype IS 'The "type" of the file. E.g. DEB, RPM';


CREATE SEQUENCE binarypackagefile_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagefile_id_seq OWNED BY binarypackagefile.id;


CREATE TABLE binarypackagepublishinghistory (
    id integer NOT NULL,
    binarypackagerelease integer NOT NULL,
    distroarchseries integer NOT NULL,
    status integer NOT NULL,
    component integer NOT NULL,
    section integer NOT NULL,
    priority integer NOT NULL,
    datecreated timestamp without time zone NOT NULL,
    datepublished timestamp without time zone,
    datesuperseded timestamp without time zone,
    supersededby integer,
    datemadepending timestamp without time zone,
    scheduleddeletiondate timestamp without time zone,
    dateremoved timestamp without time zone,
    pocket integer DEFAULT 0 NOT NULL,
    archive integer NOT NULL,
    removed_by integer,
    removal_comment text,
    binarypackagename integer
);


COMMENT ON TABLE binarypackagepublishinghistory IS 'PackagePublishingHistory: The history of a BinaryPackagePublishing record. This table represents the lifetime of a publishing record from inception to deletion. Records are never removed from here and in time the publishing table may become a view onto this table. A column being NULL indicates there''s no data for that state transition. E.g. a package which is removed without being superseded won''t have datesuperseded or supersededby filled in.';


COMMENT ON COLUMN binarypackagepublishinghistory.binarypackagerelease IS 'The binarypackage being published.';


COMMENT ON COLUMN binarypackagepublishinghistory.distroarchseries IS 'The distroarchseries into which the binarypackage is being published.';


COMMENT ON COLUMN binarypackagepublishinghistory.status IS 'The current status of the publishing.';


COMMENT ON COLUMN binarypackagepublishinghistory.component IS 'The component into which the publishing takes place.';


COMMENT ON COLUMN binarypackagepublishinghistory.section IS 'The section into which the publishing takes place.';


COMMENT ON COLUMN binarypackagepublishinghistory.priority IS 'The priority at which the publishing takes place.';


COMMENT ON COLUMN binarypackagepublishinghistory.datecreated IS 'The date/time on which the publishing record was created.';


COMMENT ON COLUMN binarypackagepublishinghistory.datepublished IS 'The date/time on which the source was actually published into an archive.';


COMMENT ON COLUMN binarypackagepublishinghistory.datesuperseded IS 'The date/time on which the source was superseded by a new source.';


COMMENT ON COLUMN binarypackagepublishinghistory.supersededby IS 'The build which superseded this package. This seems odd but it is important because a new build may not actually build a given binarypackage and we need to supersede it appropriately';


COMMENT ON COLUMN binarypackagepublishinghistory.datemadepending IS 'The date/time on which this publishing record was made to be pending removal from the archive.';


COMMENT ON COLUMN binarypackagepublishinghistory.scheduleddeletiondate IS 'The date/time at which the package is/was scheduled to be deleted.';


COMMENT ON COLUMN binarypackagepublishinghistory.dateremoved IS 'The date/time at which the package was actually deleted.';


COMMENT ON COLUMN binarypackagepublishinghistory.pocket IS 'The pocket into which this record is published. The RELEASE pocket (zero) provides behaviour as normal. Other pockets may append things to the distroseries name such as the UPDATES pocket (-updates) or the SECURITY pocket (-security).';


COMMENT ON COLUMN binarypackagepublishinghistory.archive IS 'Target archive for this publishing record.';


COMMENT ON COLUMN binarypackagepublishinghistory.removed_by IS 'Person responsible for the removal.';


COMMENT ON COLUMN binarypackagepublishinghistory.removal_comment IS 'Reason why the publication was removed.';


CREATE TABLE binarypackagerelease (
    id integer NOT NULL,
    binarypackagename integer NOT NULL,
    version debversion NOT NULL,
    summary text NOT NULL,
    description text NOT NULL,
    build integer NOT NULL,
    binpackageformat integer NOT NULL,
    component integer NOT NULL,
    section integer NOT NULL,
    priority integer NOT NULL,
    shlibdeps text,
    depends text,
    recommends text,
    suggests text,
    conflicts text,
    replaces text,
    provides text,
    essential boolean NOT NULL,
    installedsize integer,
    architecturespecific boolean NOT NULL,
    fti ts2.tsvector,
    datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    pre_depends text,
    enhances text,
    breaks text,
    debug_package integer,
    user_defined_fields text,
    homepage text,
    CONSTRAINT valid_version CHECK (valid_debian_version((version)::text))
);


COMMENT ON TABLE binarypackagerelease IS 'BinaryPackageRelease: A soyuz binary package representation. This table stores the records for each binary package uploaded into the system. Each sourcepackagerelease may build various binarypackages on various architectures.';


COMMENT ON COLUMN binarypackagerelease.binarypackagename IS 'A reference to the name of the binary package';


COMMENT ON COLUMN binarypackagerelease.version IS 'The version of the binary package. E.g. "1.0-2"';


COMMENT ON COLUMN binarypackagerelease.summary IS 'A summary of the binary package. Commonly used on listings of binary packages';


COMMENT ON COLUMN binarypackagerelease.description IS 'A longer more detailed description of the binary package';


COMMENT ON COLUMN binarypackagerelease.build IS 'The build in which this binarypackage was produced';


COMMENT ON COLUMN binarypackagerelease.binpackageformat IS 'The binarypackage format. E.g. RPM, DEB etc';


COMMENT ON COLUMN binarypackagerelease.component IS 'The archive component that this binarypackage is in. E.g. main, universe etc';


COMMENT ON COLUMN binarypackagerelease.section IS 'The archive section that this binarypackage is in. E.g. devel, libdevel, editors';


COMMENT ON COLUMN binarypackagerelease.priority IS 'The priority that this package has. E.g. Base, Standard, Extra, Optional';


COMMENT ON COLUMN binarypackagerelease.shlibdeps IS 'The shared library dependencies of this binary package';


COMMENT ON COLUMN binarypackagerelease.depends IS 'The list of packages this binarypackage depends on';


COMMENT ON COLUMN binarypackagerelease.recommends IS 'The list of packages this binarypackage recommends. Recommended packages often enhance the behaviour of a package.';


COMMENT ON COLUMN binarypackagerelease.suggests IS 'The list of packages this binarypackage suggests.';


COMMENT ON COLUMN binarypackagerelease.conflicts IS 'The list of packages this binarypackage conflicts with.';


COMMENT ON COLUMN binarypackagerelease.replaces IS 'The list of packages this binarypackage replaces files in. Often this is used to provide an upgrade path between two binarypackages of different names';


COMMENT ON COLUMN binarypackagerelease.provides IS 'The list of virtual packages (or real packages under some circumstances) which this binarypackage provides.';


COMMENT ON COLUMN binarypackagerelease.essential IS 'Whether or not this binarypackage is essential to the smooth operation of a base system';


COMMENT ON COLUMN binarypackagerelease.installedsize IS 'What the installed size of the binarypackage is. This is represented as a number of kilobytes of storage.';


COMMENT ON COLUMN binarypackagerelease.architecturespecific IS 'This field indicates whether or not a binarypackage is architecture-specific. If it is not specific to any given architecture then it can automatically be included in all the distroarchseries which pertain.';


COMMENT ON COLUMN binarypackagerelease.pre_depends IS 'The list of packages this binary requires to be installed beforehand in apt/dpkg format, as it is in control file "Pre-Depends:" field.';


COMMENT ON COLUMN binarypackagerelease.enhances IS 'The list of packages pointed as "enhanced" after the installation of this package, as it is in control file "Enhances:" field.';


COMMENT ON COLUMN binarypackagerelease.breaks IS 'The list of packages which will be broken by the installtion of this package, as it is in the control file "Breaks:" field.';


COMMENT ON COLUMN binarypackagerelease.debug_package IS 'The corresponding binary package release containing debug symbols for this binary, if any.';


COMMENT ON COLUMN binarypackagerelease.user_defined_fields IS 'A JSON struct containing a sequence of key-value pairs with user defined fields in the control file.';


COMMENT ON COLUMN binarypackagerelease.homepage IS 'Upstream project homepage URL, not checked for validity.';


CREATE TABLE component (
    id integer NOT NULL,
    name text NOT NULL,
    description text,
    CONSTRAINT valid_name CHECK (valid_name(name))
);


COMMENT ON TABLE component IS 'Known components in Launchpad';


COMMENT ON COLUMN component.name IS 'Component name text';


COMMENT ON COLUMN component.description IS 'Description of this component.';


CREATE TABLE distroarchseries (
    id integer NOT NULL,
    distroseries integer NOT NULL,
    processorfamily integer NOT NULL,
    architecturetag text NOT NULL,
    owner integer NOT NULL,
    official boolean NOT NULL,
    package_count integer DEFAULT 0 NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    supports_virtualized boolean DEFAULT false NOT NULL,
    enabled boolean DEFAULT true NOT NULL,
    CONSTRAINT valid_architecturetag CHECK (valid_name(architecturetag))
);


COMMENT ON TABLE distroarchseries IS 'DistroArchSeries: A soyuz distribution release for a given architecture. A distroseries runs on various architectures. The distroarchseries groups that architecture-specific stuff.';


COMMENT ON COLUMN distroarchseries.distroseries IS 'The distribution which this distroarchseries is part of.';


COMMENT ON COLUMN distroarchseries.processorfamily IS 'A link to the ProcessorFamily table, giving the architecture of this DistroArchSeries.';


COMMENT ON COLUMN distroarchseries.architecturetag IS 'The name of this architecture in the context of this specific distro release. For example, some distributions might label amd64 as amd64, others might call is x86_64. This information is used, for example, in determining the names of the actual package files... such as the "amd64" part of "apache2_2.0.56-1_amd64.deb"';


COMMENT ON COLUMN distroarchseries.official IS 'Whether or not this architecture or "port" is an official release. If it is not official then you may not be able to install it or get all the packages for it.';


COMMENT ON COLUMN distroarchseries.package_count IS 'A cache of the number of binary packages published in this distro arch release. The count only includes packages published in the release pocket.';


COMMENT ON COLUMN distroarchseries.supports_virtualized IS 'Whether or not
virtualized build support should be provided by this specific distroarchseries';


COMMENT ON COLUMN distroarchseries.enabled IS 'Whether to allow build creation and publishing for this DistroArchSeries.';


CREATE TABLE distroseries (
    id integer NOT NULL,
    distribution integer NOT NULL,
    name text NOT NULL,
    title text NOT NULL,
    description text NOT NULL,
    version text NOT NULL,
    releasestatus integer NOT NULL,
    datereleased timestamp without time zone,
    parent_series integer,
    registrant integer NOT NULL,
    summary text NOT NULL,
    displayname text NOT NULL,
    datelastlangpack timestamp without time zone,
    messagecount integer DEFAULT 0 NOT NULL,
    nominatedarchindep integer,
    changeslist text,
    binarycount integer DEFAULT 0 NOT NULL,
    sourcecount integer DEFAULT 0 NOT NULL,
    driver integer,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    hide_all_translations boolean DEFAULT true NOT NULL,
    defer_translation_imports boolean DEFAULT true NOT NULL,
    language_pack_base integer,
    language_pack_delta integer,
    language_pack_proposed integer,
    language_pack_full_export_requested boolean DEFAULT false NOT NULL,
    backports_not_automatic boolean DEFAULT false NOT NULL,
    include_long_descriptions boolean DEFAULT true NOT NULL,
    CONSTRAINT valid_language_pack_delta CHECK (((language_pack_base IS NOT NULL) OR (language_pack_delta IS NULL))),
    CONSTRAINT valid_name CHECK (valid_name(name)),
    CONSTRAINT valid_version CHECK (sane_version(version))
);


COMMENT ON TABLE distroseries IS 'DistroSeries: A soyuz distribution release. A DistroSeries is a given version of a distribution. E.g. "Warty" "Hoary" "Sarge" etc.';


COMMENT ON COLUMN distroseries.distribution IS 'The distribution which contains this distroseries.';


COMMENT ON COLUMN distroseries.name IS 'The unique name of the distroseries. This is a short name in lower case and would be used in sources.list configuration and in generated URLs. E.g. "warty" "sarge" "sid"';


COMMENT ON COLUMN distroseries.title IS 'The display-name title of the distroseries E.g. "Warty Warthog"';


COMMENT ON COLUMN distroseries.description IS 'The long detailed description of the release. This may describe the focus of the release or other related information.';


COMMENT ON COLUMN distroseries.version IS 'The version of the release. E.g. warty would be "4.10" and hoary would be "5.4"';


COMMENT ON COLUMN distroseries.releasestatus IS 'The current release status of this distroseries. E.g. "pre-release freeze" or "released"';


COMMENT ON COLUMN distroseries.datereleased IS 'The date on which this distroseries was released. (obviously only valid for released distributions)';


COMMENT ON COLUMN distroseries.parent_series IS 'The parent distroseries on which this distribution is based. This is related to the inheritance stuff.';


COMMENT ON COLUMN distroseries.registrant IS 'The ultimate owner of this distroseries.';


COMMENT ON COLUMN distroseries.summary IS 'A brief summary of the distro release. This will be displayed in bold at the top of the distroseries page, above the distroseries description. It should include any high points that are particularly important to draw to the attention of users.';


COMMENT ON COLUMN distroseries.datelastlangpack IS 'The date we last generated a base language pack for this release. Language update packs for this release will only include translations added after that date.';


COMMENT ON COLUMN distroseries.messagecount IS 'This is a cached value and may be a few hours out of sync with reality. It should, however, be in sync with the values in DistroSeriesLanguage, and should never be updated separately. The total number of translation messages in this distro release, as per IRosettaStats.';


COMMENT ON COLUMN distroseries.nominatedarchindep IS 'This is the DistroArchSeries nominated to build architecture independent packages within this DistroRelase, it is mandatory for buildable distroseries, i.e., Auto Build System will avoid to create build jobs for a DistroSeries with no nominatedarchindep, but the database model allow us to do it (for non-buildable DistroSeries). See further info in NominatedArchIndep specification.';


COMMENT ON COLUMN distroseries.changeslist IS 'The email address (name name) of the changes announcement list for this distroseries. If NULL, no announcement mail will be sent.';


COMMENT ON COLUMN distroseries.binarycount IS 'A cache of the number of distinct binary package names published in this distro release.';


COMMENT ON COLUMN distroseries.sourcecount IS 'A cache of the number of distinct source package names published in this distro release.';


COMMENT ON COLUMN distroseries.driver IS 'This is a person or team who can act as a driver for this specific release - note that the distribution drivers can also set goals for any release.';


COMMENT ON COLUMN distroseries.hide_all_translations IS 'Whether we should hid
e all available translations for this distro release to non admin users.';


COMMENT ON COLUMN distroseries.defer_translation_imports IS 'Don''t accept PO imports for this release just now.';


COMMENT ON COLUMN distroseries.language_pack_base IS 'Current full export language pack for this distribution release.';


COMMENT ON COLUMN distroseries.language_pack_delta IS 'Current language pack update based on language_pack_base information.';


COMMENT ON COLUMN distroseries.language_pack_proposed IS 'Either a full or update language pack being tested to be used in language_pack_base or language_pack_delta.';


COMMENT ON COLUMN distroseries.language_pack_full_export_requested IS 'Whether next language pack export should be a full export or an update.';


CREATE TABLE libraryfilealias (
    id integer NOT NULL,
    content integer,
    filename text NOT NULL,
    mimetype text NOT NULL,
    expires timestamp without time zone,
    last_accessed timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    restricted boolean DEFAULT false NOT NULL,
    hits integer DEFAULT 0 NOT NULL,
    CONSTRAINT valid_filename CHECK ((filename !~~ '%/%'::text))
);


COMMENT ON TABLE libraryfilealias IS 'LibraryFileAlias: A librarian file''s alias. The librarian stores, along with the file contents, a record stating the file name and mimetype. This table represents it.';


COMMENT ON COLUMN libraryfilealias.content IS 'The libraryfilecontent which is the data in this file.';


COMMENT ON COLUMN libraryfilealias.filename IS 'The name of the file. E.g. "foo_1.0-1_i386.deb"';


COMMENT ON COLUMN libraryfilealias.mimetype IS 'The mime type of the file. E.g. "application/x-debian-package"';


COMMENT ON COLUMN libraryfilealias.expires IS 'The expiry date of this file. If NULL, this item may be removed as soon as it is no longer referenced. If set, the item will not be removed until this date. Once the date is passed, the file may be removed from disk even if this item is still being referenced (in which case content.deleted will be true)';


COMMENT ON COLUMN libraryfilealias.last_accessed IS 'Roughly when this file was last retrieved from the Librarian. Initially set to this item''s creation date.';


COMMENT ON COLUMN libraryfilealias.date_created IS 'The timestamp when this alias was created.';


COMMENT ON COLUMN libraryfilealias.restricted IS 'Is this file available only from the restricted librarian?';


COMMENT ON COLUMN libraryfilealias.hits IS 'The number of times this file has been downloaded.';


CREATE TABLE sourcepackagerelease (
    id integer NOT NULL,
    creator integer NOT NULL,
    version debversion NOT NULL,
    dateuploaded timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    urgency integer NOT NULL,
    dscsigningkey integer,
    component integer NOT NULL,
    changelog_entry text,
    builddepends text,
    builddependsindep text,
    architecturehintlist text NOT NULL,
    dsc text,
    section integer NOT NULL,
    maintainer integer NOT NULL,
    sourcepackagename integer NOT NULL,
    upload_distroseries integer NOT NULL,
    format integer NOT NULL,
    dsc_maintainer_rfc822 text,
    dsc_standards_version text,
    dsc_format text NOT NULL,
    dsc_binaries text,
    upload_archive integer,
    copyright text,
    build_conflicts text,
    build_conflicts_indep text,
    sourcepackage_recipe_build integer,
    changelog integer,
    user_defined_fields text,
    homepage text,
    CONSTRAINT valid_version CHECK (valid_debian_version((version)::text))
);


COMMENT ON TABLE sourcepackagerelease IS 'SourcePackageRelease: A source
package release. This table represents a specific release of a source
package. Source package releases may be published into a distroseries, or
even multiple distroseries.';


COMMENT ON COLUMN sourcepackagerelease.creator IS 'The creator of this
sourcepackagerelease. This is the person referred to in the top entry in the
package changelog in debian terms. Note that a source package maintainer in
Ubuntu might be person A, but a particular release of that source package
might in fact have been created by a different person B. The maintainer
would be recorded in the Maintainership table, while the creator of THIS
release would be recorded in the SourcePackageRelease.creator field.';


COMMENT ON COLUMN sourcepackagerelease.version IS 'The version string for
this source package release. E.g. "1.0-2" or "1.4-5ubuntu9.1". Note that, in
ubuntu-style and redhat-style distributions, the version+sourcepackagename
is unique, even across distroseries. In other words, you cannot have a
foo-1.2-1 package in Hoary that is different from foo-1.2-1 in Warty.';


COMMENT ON COLUMN sourcepackagerelease.dateuploaded IS 'The date/time that
this sourcepackagerelease was first uploaded to the Launchpad.';


COMMENT ON COLUMN sourcepackagerelease.urgency IS 'The urgency of the
upload. This is generally used to prioritise buildd activity but may also be
used for "testing" systems or security work in the future. The "urgency" is
set by the uploader, in the DSC file.';


COMMENT ON COLUMN sourcepackagerelease.dscsigningkey IS 'The GPG key used to
sign the DSC. This is not necessarily the maintainer''s key, or the
creator''s key. For example, it''s possible to produce a package, then ask a
sponsor to upload it.';


COMMENT ON COLUMN sourcepackagerelease.component IS 'The component in which
this sourcepackagerelease is intended (by the uploader) to reside. E.g.
main, universe, restricted. Note that the distribution managers will often
override this data and publish the package in an entirely different
component.';


COMMENT ON COLUMN sourcepackagerelease.changelog_entry IS 'Changelog text section extracted from the changesfile.';


COMMENT ON COLUMN sourcepackagerelease.builddepends IS 'The build
dependencies for this source package release.';


COMMENT ON COLUMN sourcepackagerelease.builddependsindep IS 'The
architecture-independant build dependancies for this source package release.';


COMMENT ON COLUMN sourcepackagerelease.architecturehintlist IS 'The
architectures which this source package release believes it should be built.
This is used as a hint to the build management system when deciding what
builds are still needed.';


COMMENT ON COLUMN sourcepackagerelease.dsc IS 'The "Debian Source Control"
file for the sourcepackagerelease, from its upload into Ubuntu for the
first time.';


COMMENT ON COLUMN sourcepackagerelease.section IS 'This integer field references the Section which the source package claims to be in';


COMMENT ON COLUMN sourcepackagerelease.maintainer IS 'Reference to the person noted as source package maintainer in the DSC.';


COMMENT ON COLUMN sourcepackagerelease.sourcepackagename IS 'Reference to a SourcePackageName.';


COMMENT ON COLUMN sourcepackagerelease.upload_distroseries IS 'The
distroseries into which this source package release was uploaded into
Launchpad / Ubuntu for the first time. In general, this will be the
development Ubuntu release into which this package was uploaded. For a
package which was unchanged between warty and hoary, this would show Warty.
For a package which was uploaded into Hoary, this would show Hoary.';


COMMENT ON COLUMN sourcepackagerelease.format IS 'The format of this
sourcepackage release, e.g. DPKG, RPM, EBUILD, etc. This is an enum, and the
values are listed in dbschema.SourcePackageFormat';


COMMENT ON COLUMN sourcepackagerelease.dsc_maintainer_rfc822 IS 'The original maintainer line in RFC-822 format, to be used in archive indexes.';


COMMENT ON COLUMN sourcepackagerelease.dsc_standards_version IS 'DSC standards version (such as "3.6.2", "3.5.9", etc) used to build this source.';


COMMENT ON COLUMN sourcepackagerelease.dsc_format IS 'DSC format version (such as "1.0").';


COMMENT ON COLUMN sourcepackagerelease.dsc_binaries IS 'DSC binary line, claimed binary-names produce by this source.';


COMMENT ON COLUMN sourcepackagerelease.upload_archive IS 'The archive into which this sourcepackagerelese was originally uploaded.';


COMMENT ON COLUMN sourcepackagerelease.copyright IS 'The copyright associated with this sourcepackage. Often in the case of debian packages and will be found after the installation in /usr/share/doc/<binarypackagename>/copyright';


COMMENT ON COLUMN sourcepackagerelease.build_conflicts IS 'The list of packages that will conflict with this source while building, as mentioned in the control file "Build-Conflicts:" field.';


COMMENT ON COLUMN sourcepackagerelease.build_conflicts_indep IS 'The list of packages that will conflict with this source while building in architecture independent environment, as mentioned in the control file "Build-Conflicts-Indep:" field.';


COMMENT ON COLUMN sourcepackagerelease.changelog IS 'The LibraryFileAlias ID of changelog associated with this sourcepackage.  Often in the case of debian packages and will be found after the installation in /usr/share/doc/<binarypackagename>/changelog.Debian.gz';


COMMENT ON COLUMN sourcepackagerelease.user_defined_fields IS 'A JSON struct containing a sequence of key-value pairs with user defined fields in the control file.';


COMMENT ON COLUMN sourcepackagerelease.homepage IS 'Upstream project homepage URL, not checked for validity.';


CREATE VIEW binarypackagefilepublishing AS
    SELECT (((libraryfilealias.id)::text || '.'::text) || (securebinarypackagepublishinghistory.id)::text) AS id, distroseries.distribution, securebinarypackagepublishinghistory.id AS binarypackagepublishing, component.name AS componentname, libraryfilealias.filename AS libraryfilealiasfilename, sourcepackagename.name AS sourcepackagename, binarypackagefile.libraryfile AS libraryfilealias, distroseries.name AS distroseriesname, distroarchseries.architecturetag, securebinarypackagepublishinghistory.status AS publishingstatus, securebinarypackagepublishinghistory.pocket, securebinarypackagepublishinghistory.archive FROM (((((((((binarypackagepublishinghistory securebinarypackagepublishinghistory JOIN binarypackagerelease ON ((securebinarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id))) JOIN binarypackagebuild ON ((binarypackagerelease.build = binarypackagebuild.id))) JOIN sourcepackagerelease ON ((binarypackagebuild.source_package_release = sourcepackagerelease.id))) JOIN sourcepackagename ON ((sourcepackagerelease.sourcepackagename = sourcepackagename.id))) JOIN binarypackagefile ON ((binarypackagefile.binarypackagerelease = binarypackagerelease.id))) JOIN libraryfilealias ON ((binarypackagefile.libraryfile = libraryfilealias.id))) JOIN distroarchseries ON ((securebinarypackagepublishinghistory.distroarchseries = distroarchseries.id))) JOIN distroseries ON ((distroarchseries.distroseries = distroseries.id))) JOIN component ON ((securebinarypackagepublishinghistory.component = component.id))) WHERE (securebinarypackagepublishinghistory.dateremoved IS NULL);


COMMENT ON VIEW binarypackagefilepublishing IS 'This view is used mostly by Lucille while performing publishing and unpublishing operations. It lists all the files associated with a binarypackage and collates all the textual representations needed for publishing components etc to allow rapid queries from SQLObject.';


CREATE SEQUENCE binarypackagename_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagename_id_seq OWNED BY binarypackagename.id;


CREATE TABLE binarypackagepath (
    id integer NOT NULL,
    path bytea NOT NULL
);


CREATE SEQUENCE binarypackagepath_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagepath_id_seq OWNED BY binarypackagepath.id;


CREATE SEQUENCE binarypackagepublishinghistory_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagepublishinghistory_id_seq OWNED BY binarypackagepublishinghistory.id;


CREATE SEQUENCE binarypackagerelease_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagerelease_id_seq OWNED BY binarypackagerelease.id;


CREATE TABLE binarypackagereleasecontents (
    binarypackagerelease integer NOT NULL,
    binarypackagepath integer NOT NULL
);


CREATE TABLE binarypackagereleasedownloadcount (
    id integer NOT NULL,
    archive integer NOT NULL,
    binary_package_release integer NOT NULL,
    day date NOT NULL,
    country integer,
    count integer NOT NULL
);


CREATE SEQUENCE binarypackagereleasedownloadcount_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE binarypackagereleasedownloadcount_id_seq OWNED BY binarypackagereleasedownloadcount.id;


CREATE TABLE branch (
    id integer NOT NULL,
    title text,
    summary text,
    owner integer NOT NULL,
    product integer,
    author integer,
    name text NOT NULL,
    home_page text,
    url text,
    whiteboard text,
    lifecycle_status integer DEFAULT 1 NOT NULL,
    last_mirrored timestamp without time zone,
    last_mirror_attempt timestamp without time zone,
    mirror_failures integer DEFAULT 0 NOT NULL,
    mirror_status_message text,
    last_scanned timestamp without time zone,
    last_scanned_id text,
    last_mirrored_id text,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    revision_count integer DEFAULT 0 NOT NULL,
    next_mirror_time timestamp without time zone,
    private boolean DEFAULT false NOT NULL,
    branch_type integer NOT NULL,
    reviewer integer,
    date_last_modified timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    registrant integer NOT NULL,
    branch_format integer,
    repository_format integer,
    metadir_format integer,
    stacked_on integer,
    distroseries integer,
    sourcepackagename integer,
    owner_name text NOT NULL,
    target_suffix text,
    unique_name text,
    size_on_disk bigint,
    merge_queue integer,
    merge_queue_config text,
    transitively_private boolean DEFAULT true NOT NULL,
    access_policy integer,
    CONSTRAINT branch_type_url_consistent CHECK (((((branch_type = 2) AND (url IS NOT NULL)) OR ((branch_type = ANY (ARRAY[1, 3])) AND (url IS NULL))) OR (branch_type = 4))),
    CONSTRAINT branch_url_no_trailing_slash CHECK ((url !~~ '%/'::text)),
    CONSTRAINT branch_url_not_supermirror CHECK ((url !~~ 'http://bazaar.launchpad.net/%'::text)),
    CONSTRAINT one_container CHECK ((((distroseries IS NULL) = (sourcepackagename IS NULL)) AND ((distroseries IS NULL) OR (product IS NULL)))),
    CONSTRAINT valid_home_page CHECK (valid_absolute_url(home_page)),
    CONSTRAINT valid_name CHECK (valid_branch_name(name)),
    CONSTRAINT valid_url CHECK (valid_absolute_url(url))
);


COMMENT ON TABLE branch IS 'Bzr branch';


COMMENT ON COLUMN branch.summary IS 'A single paragraph description of the branch';


COMMENT ON COLUMN branch.home_page IS 'This column is deprecated and to be removed soon.';


COMMENT ON COLUMN branch.whiteboard IS 'Notes on the current status of the branch';


COMMENT ON COLUMN branch.lifecycle_status IS 'Authors assesment of the branchs maturity';


COMMENT ON COLUMN branch.last_mirrored IS 'The time when the branch was last mirrored.';


COMMENT ON COLUMN branch.mirror_status_message IS 'The last message we got when mirroring this branch.';


COMMENT ON COLUMN branch.last_scanned IS 'The time when the branch was last scanned.';


COMMENT ON COLUMN branch.last_scanned_id IS 'The revision ID of the branch when it was last scanned.';


COMMENT ON COLUMN branch.last_mirrored_id IS 'The revision ID of the branch when it was last mirrored.';


COMMENT ON COLUMN branch.revision_count IS 'The number of revisions in the associated bazaar branch revision_history.';


COMMENT ON COLUMN branch.next_mirror_time IS 'The time when we will next mirror this branch (NULL means never). This will be set automatically by pushing to a hosted branch, which, once mirrored, will be set back to NULL.';


COMMENT ON COLUMN branch.private IS 'If the branch is private, then only the owner and subscribers of the branch can see it.';


COMMENT ON COLUMN branch.branch_type IS 'Branches are currently one of HOSTED (1), MIRRORED (2), or IMPORTED (3).';


COMMENT ON COLUMN branch.reviewer IS 'The reviewer (person or) team are able to transition merge proposals targetted at the branch throught the CODE_APPROVED state.';


COMMENT ON COLUMN branch.date_last_modified IS 'A branch is modified any time a user updates something using a view, a new revision for the branch is scanned, or the branch is linked to a bug, blueprint or merge proposal.';


COMMENT ON COLUMN branch.registrant IS 'The user that registered the branch.';


COMMENT ON COLUMN branch.branch_format IS 'The bzr branch format';


COMMENT ON COLUMN branch.repository_format IS 'The bzr repository format';


COMMENT ON COLUMN branch.metadir_format IS 'The bzr metadir format';


COMMENT ON COLUMN branch.stacked_on IS 'The Launchpad branch that this branch is stacked on (if any).';


COMMENT ON COLUMN branch.distroseries IS 'The distribution series that the branch belongs to.';


COMMENT ON COLUMN branch.sourcepackagename IS 'The source package this is a branch of.';


COMMENT ON COLUMN branch.size_on_disk IS 'The size in bytes of this branch in the mirrored area.';


COMMENT ON COLUMN branch.merge_queue IS 'A reference to the BranchMergeQueue record that manages merges.';


COMMENT ON COLUMN branch.merge_queue_config IS 'A JSON string of configuration values that can be read by a merge queue script.';


CREATE SEQUENCE branch_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branch_id_seq OWNED BY branch.id;


CREATE TABLE branchjob (
    id integer NOT NULL,
    job integer NOT NULL,
    branch integer,
    job_type integer NOT NULL,
    json_data text
);


COMMENT ON TABLE branchjob IS 'Contains references to jobs that are executed for a branch.';


COMMENT ON COLUMN branchjob.job IS 'A reference to a row in the Job table that has all the common job details.';


COMMENT ON COLUMN branchjob.branch IS 'The branch that this job is for.';


COMMENT ON COLUMN branchjob.job_type IS 'The type of job, like new revisions, or attribute change.';


COMMENT ON COLUMN branchjob.json_data IS 'Data that is specific to the type of job, whether this be the revisions to send email out for, or the changes that were recorded for the branch.';


CREATE SEQUENCE branchjob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branchjob_id_seq OWNED BY branchjob.id;


CREATE TABLE branchmergeproposal (
    id integer NOT NULL,
    registrant integer NOT NULL,
    source_branch integer NOT NULL,
    target_branch integer NOT NULL,
    dependent_branch integer,
    whiteboard text,
    date_merged timestamp without time zone,
    merged_revno integer,
    merge_reporter integer,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    commit_message text,
    queue_position integer,
    queue_status integer DEFAULT 1 NOT NULL,
    date_review_requested timestamp without time zone,
    reviewer integer,
    date_reviewed timestamp without time zone,
    reviewed_revision_id text,
    queuer integer,
    date_queued timestamp without time zone,
    queued_revision_id text,
    merger integer,
    merged_revision_id text,
    date_merge_started timestamp without time zone,
    date_merge_finished timestamp without time zone,
    merge_log_file integer,
    superseded_by integer,
    root_message_id text,
    merge_diff integer,
    description text,
    CONSTRAINT different_branches CHECK ((((source_branch <> target_branch) AND (dependent_branch <> source_branch)) AND (dependent_branch <> target_branch))),
    CONSTRAINT positive_revno CHECK (((merged_revno IS NULL) OR (merged_revno > 0)))
);


COMMENT ON TABLE branchmergeproposal IS 'Branch merge proposals record the intent of landing (or merging) one branch on another.';


COMMENT ON COLUMN branchmergeproposal.registrant IS 'The person that created the merge proposal.';


COMMENT ON COLUMN branchmergeproposal.source_branch IS 'The branch where the work is being written.  This branch contains the changes that the registrant wants to land.';


COMMENT ON COLUMN branchmergeproposal.target_branch IS 'The branch where the user wants the changes from the source branch to be merged into.';


COMMENT ON COLUMN branchmergeproposal.dependent_branch IS 'If the source branch was not branched off the target branch, then this is considered the dependent_branch.';


COMMENT ON COLUMN branchmergeproposal.whiteboard IS 'Used to write other information about the branch, like test URLs.';


COMMENT ON COLUMN branchmergeproposal.date_merged IS 'This is the date that merge occurred.';


COMMENT ON COLUMN branchmergeproposal.merged_revno IS 'This is the revision number of the revision on the target branch that includes the merge from the source branch.';


COMMENT ON COLUMN branchmergeproposal.merge_reporter IS 'This is the user that marked the proposal as merged.';


COMMENT ON COLUMN branchmergeproposal.date_created IS 'When the registrant created the merge proposal.';


COMMENT ON COLUMN branchmergeproposal.commit_message IS 'This is the commit message that is to be used when the branch is landed by a robot.';


COMMENT ON COLUMN branchmergeproposal.queue_position IS 'The position on the merge proposal in the overall landing queue.  If the branch has a merge_robot set and the merge robot controls multiple branches then the queue position is unique over all the queued merge proposals for the landing robot.';


COMMENT ON COLUMN branchmergeproposal.queue_status IS 'This is the current state of the merge proposal.';


COMMENT ON COLUMN branchmergeproposal.date_review_requested IS 'The date that the merge proposal enters the REVIEW_REQUESTED state. This is stored so that we can determine how long a branch has been waiting for code approval.';


COMMENT ON COLUMN branchmergeproposal.reviewer IS 'The individual who said that the code in this branch is OK to land.';


COMMENT ON COLUMN branchmergeproposal.date_reviewed IS 'When the reviewer said the code is OK to land.';


COMMENT ON COLUMN branchmergeproposal.reviewed_revision_id IS 'The Bazaar revision ID that was approved to land.';


COMMENT ON COLUMN branchmergeproposal.queuer IS 'The individual who submitted the branch to the merge queue. This is usually the merge proposal registrant.';


COMMENT ON COLUMN branchmergeproposal.date_queued IS 'When the queuer submitted the branch to the merge queue.';


COMMENT ON COLUMN branchmergeproposal.queued_revision_id IS 'The Bazaar revision ID that is queued to land.';


COMMENT ON COLUMN branchmergeproposal.merger IS 'The merger is the person who merged the branch.';


COMMENT ON COLUMN branchmergeproposal.merged_revision_id IS 'The Bazaar revision ID that was actually merged.  If the owner of the source branch is a trusted person, this may be different than the revision_id that was actually queued or reviewed.';


COMMENT ON COLUMN branchmergeproposal.date_merge_started IS 'If the merge is performed by a bot the time the merge was started is recorded otherwise it is NULL.';


COMMENT ON COLUMN branchmergeproposal.date_merge_finished IS 'If the merge is performed by a bot the time the merge was finished is recorded otherwise it is NULL.';


COMMENT ON COLUMN branchmergeproposal.merge_log_file IS 'If the merge is performed by a bot the log file is accessible from the librarian.';


COMMENT ON COLUMN branchmergeproposal.superseded_by IS 'The proposal to merge has been superceded by this one.';


COMMENT ON COLUMN branchmergeproposal.root_message_id IS 'The root message of this BranchMergeProposal''s mail thread.';


COMMENT ON COLUMN branchmergeproposal.merge_diff IS 'The diff showing the predicted result of a merge.';


CREATE SEQUENCE branchmergeproposal_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branchmergeproposal_id_seq OWNED BY branchmergeproposal.id;


CREATE TABLE branchmergeproposaljob (
    id integer NOT NULL,
    job integer NOT NULL,
    branch_merge_proposal integer NOT NULL,
    job_type integer NOT NULL,
    json_data text
);


COMMENT ON TABLE branchmergeproposaljob IS 'Contains references to jobs that are executed for a branch merge proposal.';


COMMENT ON COLUMN branchmergeproposaljob.job IS 'A reference to a row in the Job table that has all the common job details.';


COMMENT ON COLUMN branchmergeproposaljob.branch_merge_proposal IS 'The branch merge proposal that this job is for.';


COMMENT ON COLUMN branchmergeproposaljob.job_type IS 'The type of job, like new proposal, review comment, or new review requested.';


COMMENT ON COLUMN branchmergeproposaljob.json_data IS 'Data that is specific to the type of job, normally references to code review messages and or votes.';


CREATE SEQUENCE branchmergeproposaljob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branchmergeproposaljob_id_seq OWNED BY branchmergeproposaljob.id;


CREATE TABLE branchmergequeue (
    id integer NOT NULL,
    registrant integer NOT NULL,
    owner integer NOT NULL,
    name text NOT NULL,
    description text,
    configuration text,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    CONSTRAINT valid_name CHECK (valid_name(name))
);


COMMENT ON TABLE branchmergequeue IS 'Queue for managing the merge workflow for branches.';


COMMENT ON COLUMN branchmergequeue.id IS 'The id of the merge queue.';


COMMENT ON COLUMN branchmergequeue.registrant IS 'A reference to the person who created the merge queue.';


COMMENT ON COLUMN branchmergequeue.owner IS 'A reference to the person who owns the merge queue.';


COMMENT ON COLUMN branchmergequeue.name IS 'The name of the queue.';


COMMENT ON COLUMN branchmergequeue.description IS 'A description of the queue.';


COMMENT ON COLUMN branchmergequeue.configuration IS 'A JSON string of configuration data to be read by the merging script.';


COMMENT ON COLUMN branchmergequeue.date_created IS 'The date the queue was created.';


CREATE SEQUENCE branchmergequeue_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branchmergequeue_id_seq OWNED BY branchmergequeue.id;


CREATE TABLE branchrevision (
    sequence integer,
    branch integer NOT NULL,
    revision integer NOT NULL
)
WITH (fillfactor=100);
ALTER TABLE ONLY branchrevision ALTER COLUMN branch SET STATISTICS 500;
ALTER TABLE ONLY branchrevision ALTER COLUMN revision SET STATISTICS 500;


CREATE TABLE branchsubscription (
    id integer NOT NULL,
    person integer NOT NULL,
    branch integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    notification_level integer DEFAULT 1 NOT NULL,
    max_diff_lines integer,
    review_level integer DEFAULT 0 NOT NULL,
    subscribed_by integer NOT NULL
);


COMMENT ON TABLE branchsubscription IS 'An association between a person or team and a bazaar branch.';


COMMENT ON COLUMN branchsubscription.person IS 'The person or team associated with the branch.';


COMMENT ON COLUMN branchsubscription.branch IS 'The branch associated with the person or team.';


COMMENT ON COLUMN branchsubscription.notification_level IS 'The level of email the person wants to receive from branch updates.';


COMMENT ON COLUMN branchsubscription.max_diff_lines IS 'If the generated diff for a revision is larger than this number, then the diff is not sent in the notification email.';


COMMENT ON COLUMN branchsubscription.review_level IS 'The level of email the person wants to receive from review activity';


CREATE SEQUENCE branchsubscription_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branchsubscription_id_seq OWNED BY branchsubscription.id;


CREATE TABLE branchvisibilitypolicy (
    id integer NOT NULL,
    project integer,
    product integer,
    team integer,
    policy integer DEFAULT 1 NOT NULL,
    CONSTRAINT only_one_target CHECK (((project IS NULL) <> (product IS NULL)))
);


COMMENT ON TABLE branchvisibilitypolicy IS 'Defines the policy for the initial visibility of branches.';


COMMENT ON COLUMN branchvisibilitypolicy.project IS 'Even though projects don''t directly have branches themselves, if a product of the project does not specify its own branch visibility policies, those of the project are used.';


COMMENT ON COLUMN branchvisibilitypolicy.product IS 'The product that the visibility policies apply to.';


COMMENT ON COLUMN branchvisibilitypolicy.team IS 'Refers to the team that the policy applies to.  NULL is used to indicate ALL people, as there is no team defined for *everybody*.';


COMMENT ON COLUMN branchvisibilitypolicy.policy IS 'An enumerated type, one of PUBLIC or PRIVATE.  PUBLIC is the default value.';


CREATE SEQUENCE branchvisibilitypolicy_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE branchvisibilitypolicy_id_seq OWNED BY branchvisibilitypolicy.id;


CREATE SEQUENCE bug_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bug_id_seq OWNED BY bug.id;


CREATE TABLE bugactivity (
    id integer NOT NULL,
    bug integer NOT NULL,
    datechanged timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    person integer NOT NULL,
    whatchanged text NOT NULL,
    oldvalue text,
    newvalue text,
    message text
)
WITH (fillfactor=100);


CREATE SEQUENCE bugactivity_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugactivity_id_seq OWNED BY bugactivity.id;


CREATE TABLE bugaffectsperson (
    id integer NOT NULL,
    bug integer NOT NULL,
    person integer NOT NULL,
    affected boolean DEFAULT true NOT NULL
);


COMMENT ON TABLE bugaffectsperson IS 'This table maintains a mapping between bugs and users indicating that they are affected by that bug. The value is calculated and cached in the Bug.users_affected_count column.';


COMMENT ON COLUMN bugaffectsperson.bug IS 'The bug affecting this person.';


COMMENT ON COLUMN bugaffectsperson.person IS 'The person affected by this bug.';


CREATE SEQUENCE bugaffectsperson_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugaffectsperson_id_seq OWNED BY bugaffectsperson.id;


CREATE TABLE bugattachment (
    id integer NOT NULL,
    message integer NOT NULL,
    name text,
    title text,
    libraryfile integer NOT NULL,
    bug integer NOT NULL,
    type integer NOT NULL,
    CONSTRAINT valid_name CHECK (valid_name(name))
);


CREATE SEQUENCE bugattachment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugattachment_id_seq OWNED BY bugattachment.id;


CREATE TABLE bugbranch (
    id integer NOT NULL,
    datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    bug integer NOT NULL,
    branch integer NOT NULL,
    revision_hint integer,
    whiteboard text,
    registrant integer NOT NULL
);


COMMENT ON TABLE bugbranch IS 'A branch related to a bug, most likely a branch for fixing the bug.';


COMMENT ON COLUMN bugbranch.bug IS 'The bug associated with this branch.';


COMMENT ON COLUMN bugbranch.branch IS 'The branch associated to the bug.';


COMMENT ON COLUMN bugbranch.revision_hint IS 'An optional revision at which this branch became interesting to this bug, and/or may contain a fix for the bug.';


COMMENT ON COLUMN bugbranch.whiteboard IS 'Additional information about the status of the bugfix in this branch.';


COMMENT ON COLUMN bugbranch.registrant IS 'The person who linked the bug to the branch.';


CREATE SEQUENCE bugbranch_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugbranch_id_seq OWNED BY bugbranch.id;


CREATE TABLE bugcve (
    id integer NOT NULL,
    bug integer NOT NULL,
    cve integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL
);


COMMENT ON TABLE bugcve IS 'A table that records the link between a given malone bug number, and a CVE entry.';


CREATE SEQUENCE bugcve_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugcve_id_seq OWNED BY bugcve.id;


CREATE TABLE bugjob (
    id integer NOT NULL,
    job integer NOT NULL,
    bug integer NOT NULL,
    job_type integer NOT NULL,
    json_data text
);


COMMENT ON TABLE bugjob IS 'Contains references to jobs to be run against Bugs.';


COMMENT ON COLUMN bugjob.bug IS 'The bug on which the job is to be run.';


COMMENT ON COLUMN bugjob.job_type IS 'The type of job (enumeration value). Allows us to query the database for a given subset of BugJobs.';


COMMENT ON COLUMN bugjob.json_data IS 'A JSON struct containing data for the job.';


CREATE SEQUENCE bugjob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugjob_id_seq OWNED BY bugjob.id;


CREATE TABLE bugmessage (
    id integer NOT NULL,
    bug integer NOT NULL,
    message integer NOT NULL,
    bugwatch integer,
    remote_comment_id text,
    index integer NOT NULL,
    owner integer NOT NULL,
    CONSTRAINT imported_comment CHECK (((remote_comment_id IS NULL) OR (bugwatch IS NOT NULL)))
);


COMMENT ON TABLE bugmessage IS 'This table maps a message to a bug. In other words, it shows that a particular message is associated with a particular bug.';


COMMENT ON COLUMN bugmessage.bugwatch IS 'The external bug this bug comment was imported from.';


COMMENT ON COLUMN bugmessage.remote_comment_id IS 'The id this bug comment has in the external bug tracker, if it is an imported comment. If it is NULL while having a bugwatch set, this comment was added in Launchpad and needs to be pushed to the external bug tracker.';


COMMENT ON COLUMN bugmessage.index IS 'The index (used in urls) of the message in a particular bug.';


CREATE SEQUENCE bugmessage_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugmessage_id_seq OWNED BY bugmessage.id;


CREATE TABLE bugmute (
    person integer NOT NULL,
    bug integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL
);


CREATE TABLE bugnomination (
    id integer NOT NULL,
    bug integer NOT NULL,
    distroseries integer,
    productseries integer,
    status integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()),
    date_decided timestamp without time zone,
    owner integer NOT NULL,
    decider integer,
    CONSTRAINT distroseries_or_productseries CHECK (((distroseries IS NULL) <> (productseries IS NULL)))
);


COMMENT ON TABLE bugnomination IS 'A bug nominated for fixing in a distroseries or productseries';


COMMENT ON COLUMN bugnomination.bug IS 'The bug being nominated.';


COMMENT ON COLUMN bugnomination.distroseries IS 'The distroseries for which the bug is nominated.';


COMMENT ON COLUMN bugnomination.productseries IS 'The productseries for which the bug is nominated.';


COMMENT ON COLUMN bugnomination.status IS 'The status of the nomination.';


COMMENT ON COLUMN bugnomination.date_created IS 'The date the nomination was submitted.';


COMMENT ON COLUMN bugnomination.date_decided IS 'The date the nomination was approved or declined.';


COMMENT ON COLUMN bugnomination.owner IS 'The person that submitted the nomination';


COMMENT ON COLUMN bugnomination.decider IS 'The person who approved or declined the nomination';


CREATE SEQUENCE bugnomination_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugnomination_id_seq OWNED BY bugnomination.id;


CREATE TABLE bugnotification (
    id integer NOT NULL,
    bug integer NOT NULL,
    message integer NOT NULL,
    is_comment boolean NOT NULL,
    date_emailed timestamp without time zone,
    status integer DEFAULT 10 NOT NULL,
    activity integer
);


COMMENT ON TABLE bugnotification IS 'The text representation of changes to a bug, which are used to send email notifications to bug changes.';


COMMENT ON COLUMN bugnotification.bug IS 'The bug that was changed.';


COMMENT ON COLUMN bugnotification.message IS 'The message the contains the textual representation of the change.';


COMMENT ON COLUMN bugnotification.is_comment IS 'Is the change a comment addition.';


COMMENT ON COLUMN bugnotification.date_emailed IS 'When this notification was emailed to the bug subscribers.';


CREATE SEQUENCE bugnotification_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugnotification_id_seq OWNED BY bugnotification.id;


CREATE TABLE bugnotificationarchive (
    id integer NOT NULL,
    bug integer,
    message integer,
    is_comment boolean,
    date_emailed timestamp without time zone
);


CREATE TABLE bugnotificationattachment (
    id integer NOT NULL,
    message integer NOT NULL,
    bug_notification integer NOT NULL
);


COMMENT ON TABLE bugnotificationattachment IS 'Attachments to be attached to a bug notification.';


COMMENT ON COLUMN bugnotificationattachment.message IS 'A message to be attached to the sent bug notification. It will be attached as a mime/multipart part, with a content type of message/rfc822.';


COMMENT ON COLUMN bugnotificationattachment.bug_notification IS 'The bug notification, to which things should be attached to.';


CREATE SEQUENCE bugnotificationattachment_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugnotificationattachment_id_seq OWNED BY bugnotificationattachment.id;


CREATE TABLE bugnotificationfilter (
    bug_notification integer NOT NULL,
    bug_subscription_filter integer NOT NULL
);


CREATE TABLE bugnotificationrecipient (
    id integer NOT NULL,
    bug_notification integer NOT NULL,
    person integer NOT NULL,
    reason_header text NOT NULL,
    reason_body text NOT NULL
);


COMMENT ON TABLE bugnotificationrecipient IS 'The recipient for a bug notification.';


COMMENT ON COLUMN bugnotificationrecipient.bug_notification IS 'The notification this recipient should get.';


COMMENT ON COLUMN bugnotificationrecipient.person IS 'The person who should receive this notification.';


COMMENT ON COLUMN bugnotificationrecipient.reason_header IS 'The reason this person is receiving this notification (the value for the X-Launchpad-Message-Rationale header).';


COMMENT ON COLUMN bugnotificationrecipient.reason_body IS 'A line of text describing the reason this person is receiving this notification (to be included in the email message).';


CREATE SEQUENCE bugnotificationrecipient_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugnotificationrecipient_id_seq OWNED BY bugnotificationrecipient.id;


CREATE TABLE bugnotificationrecipientarchive (
    id integer NOT NULL,
    bug_notification integer,
    person integer,
    reason_header text,
    reason_body text
);


CREATE SEQUENCE bugsubscription_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsubscription_id_seq OWNED BY bugsubscription.id;


CREATE TABLE bugsubscriptionfilter (
    id integer NOT NULL,
    structuralsubscription integer,
    find_all_tags boolean NOT NULL,
    include_any_tags boolean NOT NULL,
    exclude_any_tags boolean NOT NULL,
    other_parameters text,
    description text,
    bug_notification_level integer DEFAULT 40 NOT NULL
);


COMMENT ON TABLE bugsubscriptionfilter IS 'A filter with search criteria. Emails are sent only if the affected bug matches the specified parameters. The parameters are the same as those used for bugtask searches.';


COMMENT ON COLUMN bugsubscriptionfilter.structuralsubscription IS 'The structural subscription to be filtered.';


COMMENT ON COLUMN bugsubscriptionfilter.find_all_tags IS 'If set, search for bugs having all tags specified in BugSubscriptionFilterTag, else search for bugs having any of the tags specified in BugSubscriptionFilterTag.';


COMMENT ON COLUMN bugsubscriptionfilter.include_any_tags IS 'If True, include messages for bugs having any tag set.';


COMMENT ON COLUMN bugsubscriptionfilter.exclude_any_tags IS 'If True, exclude bugs having any tag set.';


COMMENT ON COLUMN bugsubscriptionfilter.other_parameters IS 'Other filter paremeters. Actual filtering is implemented on Python level.';


COMMENT ON COLUMN bugsubscriptionfilter.description IS 'A description of the filter, allowing subscribers to note the intent of the filter.';


CREATE SEQUENCE bugsubscriptionfilter_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsubscriptionfilter_id_seq OWNED BY bugsubscriptionfilter.id;


CREATE TABLE bugsubscriptionfilterimportance (
    id integer NOT NULL,
    filter integer NOT NULL,
    importance integer NOT NULL
);


COMMENT ON TABLE bugsubscriptionfilterimportance IS 'Filter a bugsubscription by bug task status.';


COMMENT ON COLUMN bugsubscriptionfilterimportance.filter IS 'The subscription filter of this record.';


COMMENT ON COLUMN bugsubscriptionfilterimportance.importance IS 'The bug task importance.';


CREATE SEQUENCE bugsubscriptionfilterimportance_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsubscriptionfilterimportance_id_seq OWNED BY bugsubscriptionfilterimportance.id;


CREATE TABLE bugsubscriptionfiltermute (
    person integer NOT NULL,
    filter integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL
);


CREATE TABLE bugsubscriptionfilterstatus (
    id integer NOT NULL,
    filter integer NOT NULL,
    status integer NOT NULL
);


COMMENT ON TABLE bugsubscriptionfilterstatus IS 'Filter a bugsubscription by bug task status.';


COMMENT ON COLUMN bugsubscriptionfilterstatus.filter IS 'The subscription filter of this record.';


COMMENT ON COLUMN bugsubscriptionfilterstatus.status IS 'The bug task status.';


CREATE SEQUENCE bugsubscriptionfilterstatus_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsubscriptionfilterstatus_id_seq OWNED BY bugsubscriptionfilterstatus.id;


CREATE TABLE bugsubscriptionfiltertag (
    id integer NOT NULL,
    filter integer NOT NULL,
    tag text NOT NULL,
    include boolean NOT NULL
);


COMMENT ON TABLE bugsubscriptionfiltertag IS 'Filter by bug tag.';


COMMENT ON COLUMN bugsubscriptionfiltertag.filter IS 'The subscription filter of this record.';


COMMENT ON COLUMN bugsubscriptionfiltertag.tag IS 'A bug tag.';


COMMENT ON COLUMN bugsubscriptionfiltertag.include IS 'If True, send only messages for bugs having this tag, else send only messages for bugs which do not have this tag.';


CREATE SEQUENCE bugsubscriptionfiltertag_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsubscriptionfiltertag_id_seq OWNED BY bugsubscriptionfiltertag.id;


CREATE SEQUENCE bugsummary_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsummary_id_seq OWNED BY bugsummary.id;


CREATE TABLE bugsummaryjournal (
    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
);


CREATE SEQUENCE bugsummaryjournal_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugsummaryjournal_id_seq OWNED BY bugsummaryjournal.id;


CREATE SEQUENCE bugtag_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtag_id_seq OWNED BY bugtag.id;


CREATE SEQUENCE bugtask_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtask_id_seq OWNED BY bugtask.id;


CREATE TABLE bugtracker (
    id integer NOT NULL,
    bugtrackertype integer NOT NULL,
    name text NOT NULL,
    title text NOT NULL,
    summary text,
    baseurl text NOT NULL,
    owner integer NOT NULL,
    contactdetails text,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    version text,
    block_comment_pushing boolean DEFAULT false NOT NULL,
    has_lp_plugin boolean,
    active boolean DEFAULT true NOT NULL,
    CONSTRAINT valid_name CHECK (valid_name(name))
);


COMMENT ON TABLE bugtracker IS 'A bug tracker in some other project. Malone allows us to link Malone bugs with bugs recorded in other bug tracking systems, and to keep the status of the relevant bug task in sync with the status in that upstream bug tracker. So, for example, you might note that Malone bug #43224 is the same as a bug in the Apache bugzilla, number 534536. Then when the upstream guys mark that bug fixed in their bugzilla, Malone know that the bug is fixed upstream.';


COMMENT ON COLUMN bugtracker.bugtrackertype IS 'The type of bug tracker, a pointer to the table of bug tracker types. Currently we know about debbugs and bugzilla bugtrackers, and plan to support roundup and sourceforge as well.';


COMMENT ON COLUMN bugtracker.name IS 'The unique name of this bugtracker, allowing us to refer to it directly.';


COMMENT ON COLUMN bugtracker.title IS 'A title for the bug tracker, used in listings of all the bug trackers and also displayed at the top of the descriptive page for the bug tracker.';


COMMENT ON COLUMN bugtracker.summary IS 'A brief summary of this bug tracker, which might for example list any interesting policies regarding the use of the bug tracker. The summary is displayed in bold at the top of the bug tracker page.';


COMMENT ON COLUMN bugtracker.baseurl IS 'The base URL for this bug tracker. Using our knowledge of the bugtrackertype, and the details in the BugWatch table we are then able to calculate relative URLs for relevant pages in the bug tracker based on this baseurl.';


COMMENT ON COLUMN bugtracker.owner IS 'The person who created this bugtracker entry and who thus has permission to modify it. Ideally we would like this to be the person who coordinates the running of the actual bug tracker upstream.';


COMMENT ON COLUMN bugtracker.contactdetails IS 'The contact details of the people responsible for that bug tracker. This allows us to coordinate the syncing of bugs to and from that bug tracker with the responsible people on the other side.';


COMMENT ON COLUMN bugtracker.version IS 'The version of the bug tracker software being used.';


COMMENT ON COLUMN bugtracker.block_comment_pushing IS 'Whether to block pushing comments to the bug tracker. Having a value of false means that we will push the comments if the bug tracker supports it.';


COMMENT ON COLUMN bugtracker.has_lp_plugin IS 'Whether we have confirmed that the Launchpad plugin was installed on the bug tracker, the last time checkwatches was run.';


CREATE SEQUENCE bugtracker_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtracker_id_seq OWNED BY bugtracker.id;


CREATE TABLE bugtrackeralias (
    id integer NOT NULL,
    bugtracker integer NOT NULL,
    base_url text NOT NULL
);


COMMENT ON TABLE bugtrackeralias IS 'A bugtracker alias is a URL that also refers to the same bugtracker as the master bugtracker. For example, a bugtracker might be accessible as both http://www.bugsrus.com/ and http://bugsrus.com/. A bugtracker can have many aliases, and all of them are checked to prevents users registering duplicate bugtrackers inadvertently.';


COMMENT ON COLUMN bugtrackeralias.bugtracker IS 'The master bugtracker that this alias refers to.';


COMMENT ON COLUMN bugtrackeralias.base_url IS 'Another base URL for this bug tracker. See BugTracker.baseurl.';


CREATE SEQUENCE bugtrackeralias_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtrackeralias_id_seq OWNED BY bugtrackeralias.id;


CREATE TABLE bugtrackercomponent (
    id integer NOT NULL,
    name text NOT NULL,
    is_visible boolean DEFAULT true NOT NULL,
    is_custom boolean DEFAULT true NOT NULL,
    component_group integer NOT NULL,
    distribution integer,
    source_package_name integer,
    CONSTRAINT valid_target CHECK (((distribution IS NULL) = (source_package_name IS NULL)))
);


COMMENT ON TABLE bugtrackercomponent IS 'A software component in a remote bug tracker, which can be linked to the corresponding source package in a distribution using this table.';


COMMENT ON COLUMN bugtrackercomponent.name IS 'The name of the component as registered in the remote bug tracker.';


COMMENT ON COLUMN bugtrackercomponent.is_visible IS 'Whether to display or hide the item in the Launchpad user interface.';


COMMENT ON COLUMN bugtrackercomponent.is_custom IS 'Whether the item was added by a user in Launchpad or is kept in sync with the remote bug tracker.';


COMMENT ON COLUMN bugtrackercomponent.component_group IS 'The product or other higher level category used by the remote bug tracker to group projects, if any.';


COMMENT ON COLUMN bugtrackercomponent.distribution IS 'Link to the distribution for the associated source package.  This can be NULL if no ling has been established.';


COMMENT ON COLUMN bugtrackercomponent.source_package_name IS 'The text name of the source package in a distribution that corresponds to this component.  This can be NULL if no link has been established yet.';


CREATE SEQUENCE bugtrackercomponent_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtrackercomponent_id_seq OWNED BY bugtrackercomponent.id;


CREATE TABLE bugtrackercomponentgroup (
    id integer NOT NULL,
    name text NOT NULL,
    bug_tracker integer NOT NULL
);


COMMENT ON TABLE bugtrackercomponentgroup IS 'A collection of components as modeled in a remote bug tracker, often referred to as a product.  Some bug trackers do not categorize software components this way, so they will have a single default component group that all components belong to.';


COMMENT ON COLUMN bugtrackercomponentgroup.name IS 'The product or category name used in the remote bug tracker for grouping components.';


COMMENT ON COLUMN bugtrackercomponentgroup.bug_tracker IS 'The external bug tracker this component group belongs to.';


CREATE SEQUENCE bugtrackercomponentgroup_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtrackercomponentgroup_id_seq OWNED BY bugtrackercomponentgroup.id;


CREATE TABLE bugtrackerperson (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    bugtracker integer NOT NULL,
    person integer NOT NULL,
    name text NOT NULL
);


COMMENT ON TABLE bugtrackerperson IS 'A mapping from a user in an external bug tracker to a Person record in Launchpad. This is used when we can''t get an e-mail address from the bug tracker.';


COMMENT ON COLUMN bugtrackerperson.date_created IS 'When was this mapping added.';


COMMENT ON COLUMN bugtrackerperson.bugtracker IS 'The external bug tracker in which this user has an account.';


COMMENT ON COLUMN bugtrackerperson.person IS 'The Person record in Launchpad this user corresponds to.';


COMMENT ON COLUMN bugtrackerperson.name IS 'The (within the bug tracker) unique username in the external bug tracker.';


CREATE SEQUENCE bugtrackerperson_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugtrackerperson_id_seq OWNED BY bugtrackerperson.id;


CREATE TABLE bugwatch (
    id integer NOT NULL,
    bug integer NOT NULL,
    bugtracker integer NOT NULL,
    remotebug text NOT NULL,
    remotestatus text,
    lastchanged timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone),
    lastchecked timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone),
    datecreated timestamp without time zone DEFAULT timezone('UTC'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
    owner integer NOT NULL,
    last_error_type integer,
    remote_importance text,
    remote_lp_bug_id integer,
    next_check timestamp without time zone
);


COMMENT ON COLUMN bugwatch.last_error_type IS 'The type of error which last prevented this entry from being updated. Legal values are defined by the BugWatchErrorType enumeration.';


COMMENT ON COLUMN bugwatch.remote_importance IS 'The importance of the bug as returned by the remote server. This will be converted into a Launchpad BugTaskImportance value.';


COMMENT ON COLUMN bugwatch.remote_lp_bug_id IS 'The bug in Launchpad that the remote bug is pointing at. This can be different than the BugWatch.bug column, since the same remote bug can be linked from multiple bugs in Launchpad, but the remote bug can only link to a single bug in Launchpad. The main use case for this column is to avoid having to query the remote bug tracker for this information, in order to decide whether we need to give this information to the remote bug tracker.';


COMMENT ON COLUMN bugwatch.next_check IS 'The time after which the watch should next be checked. Note that this does not denote an exact schedule for the next check since checkwatches only runs periodically.';


CREATE SEQUENCE bugwatch_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugwatch_id_seq OWNED BY bugwatch.id;


CREATE TABLE bugwatchactivity (
    id integer NOT NULL,
    bug_watch integer NOT NULL,
    activity_date timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    result integer NOT NULL,
    message text,
    oops_id text
)
WITH (fillfactor=100);


COMMENT ON TABLE bugwatchactivity IS 'This table contains a record of each update for a given bug watch. This allows us to track whether a given update was successful or not and, if not, the details of the error which caused the update to fail.';


COMMENT ON COLUMN bugwatchactivity.bug_watch IS 'The bug_watch to which this activity entry relates.';


COMMENT ON COLUMN bugwatchactivity.activity_date IS 'The datetime at which the activity occurred.';


COMMENT ON COLUMN bugwatchactivity.result IS 'The result of the update. Legal values are defined in the BugWatchErrorType enumeration. An update is considered successful if its error_type is NULL.';


COMMENT ON COLUMN bugwatchactivity.message IS 'The message (if any) associated with the update.';


COMMENT ON COLUMN bugwatchactivity.oops_id IS 'The OOPS id, if any, associated with the error that caused the update to fail.';


CREATE SEQUENCE bugwatchactivity_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE bugwatchactivity_id_seq OWNED BY bugwatchactivity.id;


CREATE TABLE builder (
    id integer NOT NULL,
    processor integer NOT NULL,
    name text NOT NULL,
    title text NOT NULL,
    description text NOT NULL,
    owner integer NOT NULL,
    speedindex integer,
    builderok boolean NOT NULL,
    failnotes text,
    virtualized boolean DEFAULT true NOT NULL,
    url text NOT NULL,
    manual boolean DEFAULT false,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    vm_host text,
    active boolean DEFAULT true NOT NULL,
    failure_count integer DEFAULT 0 NOT NULL,
    CONSTRAINT valid_absolute_url CHECK (valid_absolute_url(url))
);


COMMENT ON TABLE builder IS 'Builder: This table stores the build-slave registry and status information as: name, url, trusted, builderok, builderaction, failnotes.';


COMMENT ON COLUMN builder.speedindex IS 'A relative measure of the speed of this builder. If NULL, we do not yet have a speedindex for the builder else it is the number of seconds needed to perform a reference build';


COMMENT ON COLUMN builder.builderok IS 'Should a builder fail for any reason, from out-of-disk-space to not responding to the buildd master, the builderok flag is set to false and the failnotes column is filled with a reason.';


COMMENT ON COLUMN builder.failnotes IS 'This column gets filled out with a textual description of how/why a builder has failed. If the builderok column is true then the value in this column is irrelevant and should be treated as NULL or empty.';


COMMENT ON COLUMN builder.virtualized IS 'Whether or not the builder is a virtual Xen builder. Packages coming via ubuntu workflow are trusted to build on non-Xen and do not need facist behaviour to be built. Other packages like ppa/grumpy incoming packages can contain malicious code, so are unstrusted and build in a Xen virtual machine.';


COMMENT ON COLUMN builder.url IS 'The url to the build slave. There may be more than one build slave on a given host so this url includes the port number to use. The default port number for a build slave is 8221';


COMMENT ON COLUMN builder.manual IS 'Whether or not builder was manual mode, i.e., collect any result from the it, but do not dispach anything to it automatically.';


COMMENT ON COLUMN builder.vm_host IS 'The virtual machine host associated to this builder. It should be empty for "native" builders (old fashion or architectures not yet supported by XEN).';


COMMENT ON COLUMN builder.active IS 'Whether to present or not the builder in the public list of builders avaialble. It is used to hide transient or defunct builders while they get fixed.';


COMMENT ON COLUMN builder.failure_count IS 'The number of consecutive failures on this builder.  Is reset to zero after a sucessful dispatch.';


CREATE SEQUENCE builder_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE builder_id_seq OWNED BY builder.id;


CREATE TABLE buildfarmjob (
    id integer NOT NULL,
    processor integer,
    virtualized boolean,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    date_started timestamp without time zone,
    date_finished timestamp without time zone,
    date_first_dispatched timestamp without time zone,
    builder integer,
    status integer NOT NULL,
    log integer,
    job_type integer NOT NULL,
    failure_count integer DEFAULT 0 NOT NULL,
    CONSTRAINT started_if_finished CHECK (((date_finished IS NULL) OR (date_started IS NOT NULL)))
);


COMMENT ON TABLE buildfarmjob IS 'BuildFarmJob: This table stores the information common to all jobs on the Launchpad build farm.';


COMMENT ON COLUMN buildfarmjob.processor IS 'Points to the required processor target for this job, or null.';


COMMENT ON COLUMN buildfarmjob.virtualized IS 'The virtualization setting required by this build farm job, or null.';


COMMENT ON COLUMN buildfarmjob.date_created IS 'When the build farm job record was created.';


COMMENT ON COLUMN buildfarmjob.date_started IS 'When the build farm job started being processed.';


COMMENT ON COLUMN buildfarmjob.date_finished IS 'When the build farm job finished being processed.';


COMMENT ON COLUMN buildfarmjob.date_first_dispatched IS 'The instant the build was dispatched the first time. This value will not get overridden if the build is retried.';


COMMENT ON COLUMN buildfarmjob.builder IS 'Points to the builder which processed this build farm job.';


COMMENT ON COLUMN buildfarmjob.status IS 'Stores the current build status.';


COMMENT ON COLUMN buildfarmjob.log IS 'Points to the log for this build farm job file stored in librarian.';


COMMENT ON COLUMN buildfarmjob.job_type IS 'The type of build farm job to which this record corresponds.';


COMMENT ON COLUMN buildfarmjob.failure_count IS 'The number of consecutive failures on this job.  If excessive, the job may be terminated.';


CREATE SEQUENCE buildfarmjob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE buildfarmjob_id_seq OWNED BY buildfarmjob.id;


CREATE TABLE buildpackagejob (
    id integer NOT NULL,
    job integer NOT NULL,
    build integer NOT NULL
);


CREATE SEQUENCE buildpackagejob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE buildpackagejob_id_seq OWNED BY buildpackagejob.id;


CREATE TABLE buildqueue (
    id integer NOT NULL,
    builder integer,
    logtail text,
    lastscore integer,
    manual boolean DEFAULT false NOT NULL,
    job integer NOT NULL,
    job_type integer DEFAULT 1 NOT NULL,
    estimated_duration interval DEFAULT '00:00:00'::interval NOT NULL,
    processor integer,
    virtualized boolean
);


COMMENT ON TABLE buildqueue IS 'BuildQueue: The queue of jobs in progress/scheduled to run on the Soyuz build farm.';


COMMENT ON COLUMN buildqueue.builder IS 'The builder assigned to this build. Some builds will have a builder assigned to queue them up; some will be building on the specified builder already; others will not have a builder yet (NULL) and will be waiting to be assigned into a builder''s queue';


COMMENT ON COLUMN buildqueue.logtail IS 'The tail end of the log of the current build. This is updated regularly as the buildd master polls the buildd slaves. Once the build is complete; the full log will be lodged with the librarian and linked into the build table.';


COMMENT ON COLUMN buildqueue.lastscore IS 'The last score ascribed to this build record. This can be used in the UI among other places.';


COMMENT ON COLUMN buildqueue.manual IS 'Indicates if the current record was or not rescored manually, if so it get skipped from the auto-score procedure.';


COMMENT ON COLUMN buildqueue.job IS 'Foreign key to the `Job` table row with the generic job data.';


COMMENT ON COLUMN buildqueue.job_type IS 'Type of job (enumeration value), enables us to find/query the correct table with the data specific to this type of job.';


COMMENT ON COLUMN buildqueue.estimated_duration IS 'Estimated job duration, based on previous running times of comparable jobs.';


COMMENT ON COLUMN buildqueue.processor IS 'The processor required by the associated build farm job.';


COMMENT ON COLUMN buildqueue.virtualized IS 'The virtualization setting required by the associated build farm job.';


CREATE SEQUENCE buildqueue_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE buildqueue_id_seq OWNED BY buildqueue.id;


CREATE TABLE codeimport (
    id integer NOT NULL,
    branch integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    registrant integer NOT NULL,
    rcs_type integer NOT NULL,
    cvs_root text,
    cvs_module text,
    review_status integer DEFAULT 1 NOT NULL,
    date_last_successful timestamp without time zone,
    owner integer NOT NULL,
    assignee integer,
    update_interval interval,
    url text,
    CONSTRAINT valid_vcs_details CHECK (CASE WHEN (rcs_type = 1) THEN (((((cvs_root IS NOT NULL) AND (cvs_root <> ''::text)) AND (cvs_module IS NOT NULL)) AND (cvs_module <> ''::text)) AND (url IS NULL)) WHEN (rcs_type = ANY (ARRAY[2, 3])) THEN ((((cvs_root IS NULL) AND (cvs_module IS NULL)) AND (url IS NOT NULL)) AND valid_absolute_url(url)) WHEN (rcs_type = ANY (ARRAY[4, 5, 6])) THEN (((cvs_root IS NULL) AND (cvs_module IS NULL)) AND (url IS NOT NULL)) ELSE false END)
);


COMMENT ON TABLE codeimport IS 'The persistent record of an import from a foreign version control system to Bazaar, from the initial request to the regularly updated import branch.';


COMMENT ON COLUMN codeimport.branch IS 'The Bazaar branch produced by the import system.  Always non-NULL: a placeholder branch is created when the import is created.  The import is associated to a Product and Series though the branch.';


COMMENT ON COLUMN codeimport.registrant IS 'The person who originally requested this import.';


COMMENT ON COLUMN codeimport.rcs_type IS 'The revision control system used by the import source. The value is defined in dbschema.RevisionControlSystems.';


COMMENT ON COLUMN codeimport.cvs_root IS 'The $CVSROOT details, probably of the form :pserver:user@host:/path.';


COMMENT ON COLUMN codeimport.cvs_module IS 'The module in cvs_root to import, often the name of the project.';


COMMENT ON COLUMN codeimport.review_status IS 'Whether this code import request has been reviewed, and whether it was accepted.';


COMMENT ON COLUMN codeimport.date_last_successful IS 'When this code import last succeeded. NULL if this import has never succeeded.';


COMMENT ON COLUMN codeimport.owner IS 'The person who is currently responsible for keeping the import details up to date, initially set to the registrant. This person can edit some of the details of the code import branch.';


COMMENT ON COLUMN codeimport.assignee IS 'The person in charge of delivering this code import and interacting with the owner.';


COMMENT ON COLUMN codeimport.update_interval IS 'How often should this import be updated. If NULL, defaults to a system-wide value set by the Launchpad administrators.';


COMMENT ON COLUMN codeimport.url IS 'The URL of the foreign VCS branch for this import.';


CREATE SEQUENCE codeimport_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codeimport_id_seq OWNED BY codeimport.id;


CREATE TABLE codeimportevent (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    entry_type integer NOT NULL,
    code_import integer,
    person integer,
    machine integer
);


COMMENT ON TABLE codeimportevent IS 'A record of events in the code import system.  Rows in this table are created by triggers on other code import tables.';


COMMENT ON COLUMN codeimportevent.entry_type IS 'The type of event that is recorded by this entry. Legal values are defined by the CodeImportEventType enumeration.';


COMMENT ON COLUMN codeimportevent.code_import IS 'The code import that was associated to this event, if any and if it has not been deleted.';


COMMENT ON COLUMN codeimportevent.person IS 'The user who caused the event, if the event is not automatically generated.';


COMMENT ON COLUMN codeimportevent.machine IS 'The code import machine that was concerned by this event, if any.';


CREATE SEQUENCE codeimportevent_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codeimportevent_id_seq OWNED BY codeimportevent.id;


CREATE TABLE codeimporteventdata (
    id integer NOT NULL,
    event integer,
    data_type integer NOT NULL,
    data_value text
);


COMMENT ON TABLE codeimporteventdata IS 'Additional data associated to a particular code import event.';


COMMENT ON COLUMN codeimporteventdata.event IS 'The event the data is associated to.';


COMMENT ON COLUMN codeimporteventdata.data_type IS 'The type of additional data, from the CodeImportEventDataType enumeration.';


COMMENT ON COLUMN codeimporteventdata.data_value IS 'The value of the additional data.  A string.';


CREATE SEQUENCE codeimporteventdata_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codeimporteventdata_id_seq OWNED BY codeimporteventdata.id;


CREATE TABLE codeimportjob (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    code_import integer NOT NULL,
    machine integer,
    date_due timestamp without time zone NOT NULL,
    state integer NOT NULL,
    requesting_user integer,
    ordering integer,
    heartbeat timestamp without time zone,
    logtail text,
    date_started timestamp without time zone,
    CONSTRAINT valid_state CHECK (CASE WHEN (state = 10) THEN (((((machine IS NULL) AND (ordering IS NULL)) AND (heartbeat IS NULL)) AND (date_started IS NULL)) AND (logtail IS NULL)) WHEN (state = 20) THEN (((((machine IS NOT NULL) AND (ordering IS NOT NULL)) AND (heartbeat IS NULL)) AND (date_started IS NULL)) AND (logtail IS NULL)) WHEN (state = 30) THEN (((((machine IS NOT NULL) AND (ordering IS NULL)) AND (heartbeat IS NOT NULL)) AND (date_started IS NOT NULL)) AND (logtail IS NOT NULL)) ELSE false END)
);


COMMENT ON TABLE codeimportjob IS 'A pending or active code import job.  There is always such a row for any active import, but it will not run until date_due is in the past.';


COMMENT ON COLUMN codeimportjob.code_import IS 'The code import that is being worked upon.';


COMMENT ON COLUMN codeimportjob.machine IS 'The machine job is currently scheduled to run on, or where the job is currently running.';


COMMENT ON COLUMN codeimportjob.date_due IS 'When the import should happen.';


COMMENT ON COLUMN codeimportjob.state IS 'One of PENDING (waiting until its due or a machine is online), SCHEDULED (assigned to a machine, but not yet running) or RUNNING (actually in the process of being imported now).';


COMMENT ON COLUMN codeimportjob.requesting_user IS 'The user who requested the import, if any. Set if and only if reason = REQUEST.';


COMMENT ON COLUMN codeimportjob.ordering IS 'A measure of how urgent the job is -- queue entries with lower "ordering" should be processed first, or in other works "ORDER BY ordering" returns the most import jobs first.';


COMMENT ON COLUMN codeimportjob.heartbeat IS 'While the job is running, this field should be updated frequently to indicate that the import job hasn''t crashed.';


COMMENT ON COLUMN codeimportjob.logtail IS 'The last few lines of output produced by the running job. It should be updated at the same time as the heartbeat.';


COMMENT ON COLUMN codeimportjob.date_started IS 'When the import began to be processed.';


CREATE SEQUENCE codeimportjob_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codeimportjob_id_seq OWNED BY codeimportjob.id;


CREATE TABLE codeimportmachine (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    hostname text NOT NULL,
    state integer DEFAULT 10 NOT NULL,
    heartbeat timestamp without time zone
);


COMMENT ON TABLE codeimportmachine IS 'The record of a machine capable of performing jobs for the code import system.';


COMMENT ON COLUMN codeimportmachine.hostname IS 'The (unique) hostname of the machine.';


COMMENT ON COLUMN codeimportmachine.state IS 'Whether the controller daemon on this machine is offline, online, or quiescing (running but not accepting new jobs).';


COMMENT ON COLUMN codeimportmachine.heartbeat IS 'When the code-import-controller daemon was last known to be running on this machine. If it is not updated for a long time the machine state will change to offline.';


CREATE SEQUENCE codeimportmachine_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codeimportmachine_id_seq OWNED BY codeimportmachine.id;


CREATE TABLE codeimportresult (
    id integer NOT NULL,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
    code_import integer,
    machine integer,
    requesting_user integer,
    log_excerpt text,
    log_file integer,
    status integer NOT NULL,
    date_job_started timestamp without time zone
);


COMMENT ON TABLE codeimportresult IS 'A completed code import job.';


COMMENT ON COLUMN codeimportresult.code_import IS 'The code import for which the job was run.';


COMMENT ON COLUMN codeimportresult.machine IS 'The machine the job ran on.';


COMMENT ON COLUMN codeimportresult.log_excerpt IS 'The last few lines of the partial log, in case it is set.';


COMMENT ON COLUMN codeimportresult.log_file IS 'A partial log of the job for users to see. It is normally only recorded if the job failed in a step that interacts with the remote repository. If a job was successful, or failed in a houskeeping step, the log file would not contain information useful to the user.';


COMMENT ON COLUMN codeimportresult.status IS 'How the job ended. Success, some kind of failure, or some kind of interruption before completion.';


COMMENT ON COLUMN codeimportresult.date_job_started IS 'When the job started to run (date_created is when it finished).';


CREATE SEQUENCE codeimportresult_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codeimportresult_id_seq OWNED BY codeimportresult.id;


CREATE TABLE codereviewmessage (
    id integer NOT NULL,
    branch_merge_proposal integer NOT NULL,
    message integer NOT NULL,
    vote integer,
    vote_tag text
);


COMMENT ON TABLE codereviewmessage IS 'A message that is part of a code review discussion.';


COMMENT ON COLUMN codereviewmessage.branch_merge_proposal IS 'The merge proposal that is being discussed.';


COMMENT ON COLUMN codereviewmessage.message IS 'The actual message.';


COMMENT ON COLUMN codereviewmessage.vote IS 'The reviewer''s vote for this message.';


COMMENT ON COLUMN codereviewmessage.vote_tag IS 'A short description of the vote';


CREATE SEQUENCE codereviewmessage_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codereviewmessage_id_seq OWNED BY codereviewmessage.id;


CREATE TABLE codereviewvote (
    id integer NOT NULL,
    branch_merge_proposal integer NOT NULL,
    reviewer integer NOT NULL,
    review_type text,
    registrant integer NOT NULL,
    vote_message integer,
    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL
);


COMMENT ON TABLE codereviewvote IS 'Reference to a person''s last vote in a code review discussion.';


COMMENT ON COLUMN codereviewvote.branch_merge_proposal IS 'The BranchMergeProposal for the code review.';


COMMENT ON COLUMN codereviewvote.reviewer IS 'The person performing the review.';


COMMENT ON COLUMN codereviewvote.review_type IS 'The aspect of the code being reviewed.';


COMMENT ON COLUMN codereviewvote.registrant IS 'The person who registered this vote';


COMMENT ON COLUMN codereviewvote.vote_message IS 'The message associated with the vote';


COMMENT ON COLUMN codereviewvote.date_created IS 'The date this vote reference was created';


CREATE SEQUENCE codereviewvote_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER SEQUENCE codereviewvote_id_seq OWNED BY codereviewvote.id;


CREATE VIEW combinedbugsummary AS
    SELECT bugsummary.id, bugsummary.count, bugsummary.product, bugsummary.productseries, bugsummary.distribution, bugsummary.distroseries, bugsummary.sourcepackagename, bugsummary.viewed_by, bugsummary.tag, bugsummary.status, bugsummary.milestone, bugsummary.importance, bugsummary.has_patch, bugsummary.fixed_upstream FROM bugsummary UNION ALL SELECT (- bugsummaryjournal.id) AS id, bugsummaryjournal.count, bugsummaryjournal.product, bugsummaryjournal.productseries, bugsummaryjournal.distribution, bugsummaryjournal.distroseries, bugsummaryjournal.sourcepackagename, bugsummaryjournal.viewed_by, bugsummaryjournal.tag, bugsummaryjournal.status, bugsummaryjournal.milestone, bugsummaryjournal.importance, bugsummaryjournal.has_patch, bugsummaryjournal.fixed_upstream FROM bugsummaryjournal;


