~launchpad-pqm/launchpad/devel

-- Copyright 2011 Canonical Ltd.  This software is licensed under the

-- GNU Affero General Public License version 3 (see the file LICENSE).

SET client_min_messages=ERROR;

-- Trash our existing data, which we will rebuild in a minute.

-- Slony-I doesn't like TRUNCATE noramlly, but OK in a DB patch.

TRUNCATE BugSummary;

TRUNCATE BugSummaryJournal;

-- Drop indexes we will rebuild later.

DROP INDEX bugsummary__dimensions__unique;

DROP INDEX bugsummary__full__idx;

ALTER TABLE BugSummary

    -- Add a missing foreign key constraint we were unable to add live.

    -- Person table is always being read, so locks are never acquired.

    ADD CONSTRAINT bugsummaryjournal_viewed_by_fkey

        FOREIGN KEY(viewed_by) REFERENCES Person ON DELETE CASCADE,

    ADD COLUMN importance integer NOT NULL,

    ADD COLUMN has_patch boolean NOT NULL,

    ADD COLUMN fixed_upstream boolean NOT NULL;

ALTER TABLE BugSummaryJournal

    ADD COLUMN importance integer NOT NULL,

    ADD COLUMN has_patch boolean NOT NULL,

    ADD COLUMN fixed_upstream boolean NOT NULL;

DROP VIEW CombinedBugSummary;

CREATE VIEW CombinedBugSummary AS (

    SELECT

        id, count, product, productseries, distribution, distroseries,

        sourcepackagename, viewed_by, tag, status, milestone,

        importance, has_patch, fixed_upstream

    FROM BugSummary

    UNION ALL

    SELECT

        -id as id, count, product, productseries, distribution, distroseries,

        sourcepackagename, viewed_by, tag, status, milestone,

        importance, has_patch, fixed_upstream

    FROM BugSummaryJournal);

-- Rebuild the BugSummary data with the new columns.

INSERT INTO BugSummary (

    count, product, productseries, distribution, distroseries,

    sourcepackagename, viewed_by, tag, status, importance, has_patch,

    fixed_upstream, milestone)

WITH

    -- kill dupes

    relevant_bug AS (SELECT * FROM bug where duplicateof is NULL),

    -- (bug.id, tag) for all bug-tag pairs plus (bug.id, NULL) for all bugs

    bug_tags AS (

        SELECT relevant_bug.id, NULL::text AS tag FROM relevant_bug

        UNION

        SELECT relevant_bug.id, tag

        FROM relevant_bug INNER JOIN bugtag ON relevant_bug.id=bugtag.bug),

    -- (bug.id, NULL) for all public bugs + (bug.id, viewer) for all

    -- (subscribers+assignee) on private bugs

    bug_viewers AS (

        SELECT relevant_bug.id, NULL::integer AS person

        FROM relevant_bug WHERE NOT relevant_bug.private

        UNION

        SELECT relevant_bug.id, assignee AS person

        FROM relevant_bug

        INNER JOIN bugtask ON relevant_bug.id=bugtask.bug

        WHERE relevant_bug.private and bugtask.assignee IS NOT NULL

        UNION

        SELECT relevant_bug.id, bugsubscription.person

        FROM relevant_bug INNER JOIN bugsubscription

            ON bugsubscription.bug=relevant_bug.id WHERE relevant_bug.private),

    -- (bugtask.(bug, product, productseries, distribution, distroseries,

    -- sourcepackagename, status, milestone) for all bugs + the same with

    -- sourcepackage squashed to NULL)

    tasks AS (

        SELECT

            bug, product, productseries, distribution, distroseries,

            sourcepackagename, status, importance,

            (EXISTS

                (SELECT TRUE

                FROM BugTask AS RelatedBugTask

                WHERE RelatedBugTask.bug = BugTask.bug

                AND RelatedBugTask.id != BugTask.id

                AND ((RelatedBugTask.bugwatch IS NOT NULL

                        AND RelatedBugTask.status IN (17, 25, 30))

                        OR (RelatedBugTask.product IS NOT NULL

                            AND RelatedBugTask.bugwatch IS NULL

                            AND RelatedBugTask.status IN (25, 30))))

                ) as fixed_upstream, milestone

        FROM bugtask

        UNION

        SELECT DISTINCT ON (

            bug, product, productseries, distribution, distroseries,

            sourcepackagename, milestone)

            bug, product, productseries, distribution, distroseries,

            NULL::integer as sourcepackagename,

            status, importance,

            (EXISTS

                (SELECT TRUE

                FROM BugTask AS RelatedBugTask

                WHERE RelatedBugTask.bug = BugTask.bug

                AND RelatedBugTask.id != BugTask.id

                AND ((RelatedBugTask.bugwatch IS NOT NULL

                        AND RelatedBugTask.status IN (17, 25, 30))

                        OR (RelatedBugTask.product IS NOT NULL

                            AND RelatedBugTask.bugwatch IS NULL

                            AND RelatedBugTask.status IN (25, 30))))

                ) as fixed_upstream, milestone

        FROM bugtask where sourcepackagename IS NOT NULL)

    -- Now combine

    SELECT

        count(*), product, productseries, distribution, distroseries,

        sourcepackagename, person, tag, status, importance,

        latest_patch_uploaded IS NOT NULL AS has_patch, fixed_upstream,

        milestone

    FROM relevant_bug

    INNER JOIN bug_tags ON relevant_bug.id=bug_tags.id

    INNER JOIN bug_viewers ON relevant_bug.id=bug_viewers.id

    INNER JOIN tasks on tasks.bug=relevant_bug.id

    GROUP BY

        product, productseries, distribution, distroseries,

        sourcepackagename, person, tag, status, importance, has_patch,

        fixed_upstream, milestone;

-- Rebuild indexes.

CREATE INDEX bugsummary__full__idx ON BugSummary (

    tag, status, product, productseries, distribution,

    distroseries, sourcepackagename, viewed_by, milestone,

    importance, has_patch, fixed_upstream);

-- Enforce uniqueness again.

CREATE UNIQUE INDEX bugsummary__product__unique

    ON BugSummary(

        product, status, importance, has_patch, fixed_upstream,

        COALESCE(tag, ''), COALESCE(milestone, -1), COALESCE(viewed_by, -1))

    WHERE product IS NOT NULL;

CREATE UNIQUE INDEX bugsummary__productseries__unique

    ON BugSummary(

        productseries, status, importance, has_patch, fixed_upstream,

        COALESCE(tag, ''), COALESCE(milestone, -1), COALESCE(viewed_by, -1))

    WHERE productseries IS NOT NULL;

CREATE UNIQUE INDEX bugsummary__distribution__unique

    ON BugSummary(

        distribution, status, importance, has_patch, fixed_upstream,

        COALESCE(sourcepackagename, -1),

        COALESCE(tag, ''), COALESCE(milestone, -1), COALESCE(viewed_by, -1))

    WHERE distribution IS NOT NULL;

CREATE UNIQUE INDEX bugsummary__distroseries__unique

    ON BugSummary(

        distroseries, status, importance, has_patch, fixed_upstream,

        COALESCE(sourcepackagename, -1),

        COALESCE(tag, ''), COALESCE(milestone, -1), COALESCE(viewed_by, -1))

    WHERE distroseries IS NOT NULL;

-- Rebuild relevant trigger functions.

CREATE OR REPLACE 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(bugsummary) IS

'Add an entry into BugSummaryJournal';

CREATE OR REPLACE FUNCTION bugsummary_rollup_journal() RETURNS VOID

LANGUAGE plpgsql VOLATILE

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;

    SELECT MAX(id) INTO max_id FROM BugSummaryJournal;

    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;

    DELETE FROM BugSummaryJournal WHERE id <= max_id;

END;

$$;

CREATE OR REPLACE 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 NOT DISTINCT FROM $1.product

        AND productseries IS NOT DISTINCT FROM $1.productseries

        AND distribution IS NOT DISTINCT FROM $1.distribution

        AND distroseries IS NOT DISTINCT FROM $1.distroseries

        AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename

        AND viewed_by IS NOT DISTINCT FROM $1.viewed_by

        AND tag IS NOT DISTINCT FROM $1.tag

        AND status IS NOT DISTINCT FROM $1.status

        AND milestone IS NOT DISTINCT FROM $1.milestone

        AND importance IS NOT DISTINCT FROM $1.importance

        AND has_patch IS NOT DISTINCT FROM $1.has_patch

        AND fixed_upstream IS NOT DISTINCT FROM $1.fixed_upstream;

    -- gc the row (perhaps should be garbo but easy enough to add here:

    DELETE FROM bugsummary

    WHERE

        count=0

        AND product IS NOT DISTINCT FROM $1.product

        AND productseries IS NOT DISTINCT FROM $1.productseries

        AND distribution IS NOT DISTINCT FROM $1.distribution

        AND distroseries IS NOT DISTINCT FROM $1.distroseries

        AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename

        AND viewed_by IS NOT DISTINCT FROM $1.viewed_by

        AND tag IS NOT DISTINCT FROM $1.tag

        AND status IS NOT DISTINCT FROM $1.status

        AND milestone IS NOT DISTINCT FROM $1.milestone

        AND importance IS NOT DISTINCT FROM $1.importance

        AND has_patch IS NOT DISTINCT FROM $1.has_patch

        AND fixed_upstream IS NOT DISTINCT FROM $1.fixed_upstream;

    -- If its not found then someone else also dec'd and won concurrently.

$$;

CREATE OR REPLACE 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 NOT DISTINCT FROM d.product

            AND productseries IS NOT DISTINCT FROM d.productseries

            AND distribution IS NOT DISTINCT FROM d.distribution

            AND distroseries IS NOT DISTINCT FROM d.distroseries

            AND sourcepackagename IS NOT DISTINCT FROM d.sourcepackagename

            AND viewed_by IS NOT DISTINCT FROM d.viewed_by

            AND tag IS NOT DISTINCT FROM d.tag

            AND status IS NOT DISTINCT FROM d.status

            AND milestone IS NOT DISTINCT FROM d.milestone

            AND importance IS NOT DISTINCT FROM d.importance

            AND has_patch IS NOT DISTINCT FROM d.has_patch

            AND fixed_upstream IS NOT DISTINCT FROM d.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 bugsummary_rollup_journal() IS

'Collate and migrate rows from BugSummaryJournal to BugSummary';

CREATE OR REPLACE FUNCTION ensure_bugsummary_temp_journal() RETURNS VOID

LANGUAGE plpgsql VOLATILE 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 OR REPLACE 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(bugsummary) IS

'Insert a BugSummary into the temporary journal';

-- Don't need these. Faster to just append rows to the journal

-- than attempt to update existing rows.

DROP FUNCTION bug_summary_temp_journal_dec(bugsummary);

DROP FUNCTION bug_summary_temp_journal_inc(bugsummary);

CREATE OR REPLACE FUNCTION bug_summary_flush_temp_journal() RETURNS VOID

LANGUAGE plpgsql VOLATILE 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

            NULL::integer AS id, SUM(count), product, productseries,

            distribution, distroseries, sourcepackagename,

            viewed_by, tag, status, milestone,

            importance, has_patch, fixed_upstream

        FROM BugSummary_temp_journal

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

        ELSE

            PERFORM bug_summary_inc(d);

        END IF;

    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 OR REPLACE FUNCTION unsummarise_bug(BUG_ROW bug) RETURNS VOID

LANGUAGE plpgsql VOLATILE 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;

$$;

CREATE OR REPLACE FUNCTION summarise_bug(BUG_ROW bug) RETURNS VOID

LANGUAGE plpgsql VOLATILE 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;

$$;

CREATE OR REPLACE FUNCTION bug_maintain_bug_summary() RETURNS TRIGGER

LANGUAGE plpgsql VOLATILE 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;

$$;

CREATE OR REPLACE FUNCTION bugtask_maintain_bug_summary() RETURNS TRIGGER

LANGUAGE plpgsql VOLATILE 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;

$$;

CREATE OR REPLACE 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) IS

'Calculate what BugSummary rows should exist for a given Bug.';

CREATE OR REPLACE 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) IS

'Return all tasks for the bug + all sourcepackagename tasks again with the sourcepackagename squashed';

INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 75, 0);