~launchpad-results/launchpad-results/trunk : contents of lib/lpresults/tracker/schema/test/__init_

~launchpad-results/launchpad-results/trunk : (revision 27)
# Copyright 2010-2011 Canonical Ltd.  This software is licensed under the
# GNU Affero General Public License version 3 (see the file LICENSE).

__all__ = [
    "create",
    ]

from storm.zope.schema import ZSchema


def create():
    from lpresults.tracker.schema import test as patch_package

    return ZSchema(CREATES, DROPS, DELETES, patch_package)


CREATES = [
"""
GRANT SELECT
    ON TABLE patch
    TO lpresults
""",
"""
CREATE TABLE persontwin (
    id INTEGER PRIMARY KEY)
""",
"""
GRANT SELECT, INSERT, DELETE
    ON TABLE persontwin
    TO lpresults
""",
"""
CREATE TABLE distroseriestwin (
    id INTEGER PRIMARY KEY,
    distribution INTEGER NOT NULL,
    counter INTEGER DEFAULT 0)
""",
"""
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE distroseriestwin
    TO lpresults
""",
"""
CREATE INDEX distroseriestwin__distribution__idx
    ON distroseriestwin (distribution)
""",
"""
CREATE TABLE projectseriestwin (
    id INTEGER PRIMARY KEY,
    project INTEGER NOT NULL,
    counter INTEGER DEFAULT 0)
""",
"""
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE projectseriestwin
    TO lpresults
""",
"""
CREATE INDEX projectseriestwin__project__idx
    ON projectseriestwin (project)
""",
"""
CREATE TABLE systemunittwin (
    id INTEGER PRIMARY KEY,
    system INTEGER NOT NULL)
""",
"""
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE systemunittwin
    TO lpresults
""",
"""
CREATE INDEX systemunittwin__system__idx
    ON systemunittwin (system)
""",
"""
CREATE TABLE testrun (
    id SERIAL NOT NULL PRIMARY KEY,
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
        DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    sequence INTEGER NOT NULL,
    person INTEGER,
    system_unit INTEGER,
    distro_series INTEGER,
    project_series INTEGER,
    pass_count INTEGER NOT NULL DEFAULT 0,
    fail_count INTEGER NOT NULL DEFAULT 0,
    uninitiated_count INTEGER NOT NULL DEFAULT 0,
    unresolved_count INTEGER NOT NULL DEFAULT 0,
    unsupported_count INTEGER NOT NULL DEFAULT 0,
    untested_count INTEGER NOT NULL DEFAULT 0,
    xfail_count INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT valid_target CHECK (
        (distro_series IS NOT NULL) OR (project_series IS NOT NULL)))
""",
"""
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE testrun
    TO lpresults
""",
"""
GRANT SELECT, UPDATE
    ON TABLE testrun_id_seq
    TO lpresults
""",
"""
CREATE INDEX testrun__person__idx
    ON testrun (person)
    WHERE person IS NOT NULL
""",
"""
CREATE INDEX testrun__system_unit__idx
    ON testrun (system_unit)
    WHERE system_unit IS NOT NULL
""",
"""
CREATE UNIQUE INDEX testrun__distro_series__date_created__key
    ON testrun (distro_series, date_created)
    WHERE distro_series IS NOT NULL
""",
"""
CREATE UNIQUE INDEX testrun__distro_series__sequence__key
    ON testrun (distro_series, sequence)
    WHERE distro_series IS NOT NULL
""",
"""
CREATE UNIQUE INDEX testrun__project_series__date_created__key
    ON testrun (project_series, date_created)
    WHERE project_series IS NOT NULL
""",
"""
CREATE UNIQUE INDEX testrun__project_series__sequence__key
    ON testrun (project_series, sequence)
    WHERE project_series IS NOT NULL
""",
"""
CREATE TABLE testcase (
    id SERIAL NOT NULL PRIMARY KEY,
    distro_series INTEGER,
    project_series INTEGER,
    name TEXT NOT NULL,
    CONSTRAINT valid_target CHECK (
        (distro_series IS NOT NULL) OR (project_series IS NOT NULL)))
""",
"""
GRANT SELECT, INSERT, DELETE
    ON TABLE testcase
    TO lpresults
""",
"""
GRANT SELECT, UPDATE
    ON TABLE testcase_id_seq
    TO lpresults
""",
"""
CREATE UNIQUE INDEX testcase__distro_series__name__key
    ON testcase (distro_series, lower(name))
    WHERE distro_series IS NOT NULL
""",
"""
CREATE UNIQUE INDEX testcase__project_series__name__key
    ON testcase (project_series, lower(name))
    WHERE project_series IS NOT NULL
""",
"""
CREATE TABLE testresult (
    test_case INTEGER NOT NULL,
    status INTEGER NOT NULL DEFAULT 2,  -- UNINITIATED
    output TEXT NOT NULL,
    date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
        DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
    date_deleted TIMESTAMP WITHOUT TIME ZONE NOT NULL
        DEFAULT '3000-01-01',
    CONSTRAINT valid_dates CHECK (date_deleted > date_created))
""",
"""
GRANT SELECT, INSERT, UPDATE, DELETE
    ON TABLE testresult
    TO lpresults
""",
"""
CREATE UNIQUE INDEX testresult__test_case__date_created__key
    ON testresult (test_case, date_created)
""",
"""
CREATE INDEX testresult__test_case__idx
    ON testresult (test_case)
""",
"""
CREATE INDEX testresult__date_created__date_deleted__idx
    ON testresult (date_created, date_deleted)
""",
"""
CREATE INDEX
    testresult__test_case__status__output__date_created__date_deleted__idx
    ON testresult (
        test_case, status, decode(md5(output), 'hex'), date_created,
        date_deleted)
""",
"""
CREATE LANGUAGE plpgsql
""",
"""
CREATE FUNCTION get_test_case(
    test_distro_series INTEGER,
    test_project_series INTEGER,
    test_name TEXT)
    RETURNS INTEGER
LANGUAGE plpgsql AS
$$
DECLARE
    test_case_id INTEGER;
BEGIN
    LOOP
        SELECT testcase.id INTO test_case_id FROM testcase
            WHERE testcase.distro_series
                IS NOT DISTINCT FROM test_distro_series
            AND testcase.project_series
                IS NOT DISTINCT FROM test_project_series
            AND testcase.name = test_name;
        IF FOUND THEN
            RETURN test_case_id;
        END IF;

        BEGIN
            INSERT INTO testcase (
                    distro_series,
                    project_series,
                    name)
                VALUES (
                    test_distro_series,
                    test_project_series,
                    test_name)
                RETURNING id INTO test_case_id;
            RETURN test_case_id;
        EXCEPTION
            WHEN unique_violation THEN
                NULL;  -- do nothing
        END;
    END LOOP;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION get_test_case(INTEGER, INTEGER, TEXT)
    TO lpresults
""",
"""
CREATE FUNCTION add_test_result(
    testresult_test_case INTEGER,
    testresult_status INTEGER,
    testresult_output TEXT,
    testresult_date_created TIMESTAMP)
    RETURNS BOOLEAN
LANGUAGE plpgsql AS
$$
BEGIN
    LOOP
        BEGIN
            INSERT INTO testresult (
                    test_case,
                    status,
                    output,
                    date_created)
                VALUES (
                    testresult_test_case,
                    testresult_status,
                    testresult_output,
                    testresult_date_created);
            RETURN FOUND;
        EXCEPTION
            WHEN unique_violation THEN
                NULL;  -- do nothing
        END;
    END LOOP;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION add_test_result(INTEGER, INTEGER, TEXT, TIMESTAMP)
    TO lpresults
""",
"""
CREATE FUNCTION next_distroseries_sequence(
    distro_series INTEGER)
    RETURNS INTEGER
LANGUAGE plpgsql AS
$$
DECLARE
    next_sequence INTEGER;
BEGIN
    UPDATE distroseriestwin SET counter = counter + 1
        WHERE id = distro_series;
    SELECT INTO next_sequence counter FROM distroseriestwin
        WHERE id = distro_series;
    RETURN next_sequence;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION next_distroseries_sequence(INTEGER)
    TO lpresults
""",
"""
CREATE FUNCTION next_projectseries_sequence(
    project_series INTEGER)
    RETURNS INTEGER
LANGUAGE plpgsql AS
$$
DECLARE
    next_sequence INTEGER;
BEGIN
    UPDATE projectseriestwin SET counter = counter + 1
        WHERE id = project_series;
    SELECT INTO next_sequence counter FROM projectseriestwin
        WHERE id = project_series;
    RETURN next_sequence;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION next_projectseries_sequence(INTEGER)
    TO lpresults
""",
"""
CREATE FUNCTION testrun_insert_before() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
    IF NEW.distro_series IS NOT NULL THEN
        NEW.sequence = next_distroseries_sequence(NEW.distro_series);
    ELSIF NEW.project_series IS NOT NULL THEN
        NEW.sequence = next_projectseries_sequence(NEW.project_series);
    END IF;

    RETURN NEW;
END
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION testrun_insert_before()
    TO lpresults
""",
"""
CREATE TRIGGER testrun_insert_before_trigger
    BEFORE INSERT ON testrun
    FOR EACH ROW
    EXECUTE PROCEDURE testrun_insert_before()
""",
"""
CREATE FUNCTION testrun_insert_after() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
    IF NEW.distro_series IS NOT NULL THEN
        UPDATE testresult SET date_deleted = NEW.date_created
            WHERE date_deleted = '3000-01-01'
            AND test_case IN (
                SELECT id FROM testcase
                WHERE distro_series = NEW.distro_series);
    ELSIF NEW.project_series IS NOT NULL THEN
        UPDATE testresult SET date_deleted = NEW.date_created
            WHERE date_deleted = '3000-01-01'
            AND test_case IN (
                SELECT id FROM testcase
                WHERE project_series = NEW.project_series);
    END IF;

    RETURN NEW;
END
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION testrun_insert_after()
    TO lpresults
""",
"""
CREATE TRIGGER testrun_insert_after_trigger
    BEFORE INSERT ON testrun
    FOR EACH ROW
    EXECUTE PROCEDURE testrun_insert_after()
""",
"""
CREATE FUNCTION testresult_insert_before() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
    testresult_record RECORD;
BEGIN
    -- First, check for the most common case when
    -- there is a test result in a backward dimension.
    SELECT INTO
        testresult_record
            testresult.status,
            testresult.output,
            testresult.date_created
        FROM testresult
        WHERE testresult.test_case = NEW.test_case
        AND testresult.date_created <= NEW.date_created
        AND testresult.date_deleted > NEW.date_created;
    IF FOUND THEN
        IF NEW.status = testresult_record.status
           AND NEW.output = testresult_record.output THEN
            -- No need to extend forward dimension
            RETURN NULL;

        ELSIF NEW.date_created = testresult_record.date_created THEN
            -- Update current dimension
            UPDATE testresult SET status = NEW.status, output = NEW.output
                WHERE testresult.test_case = NEW.test_case
                AND testresult.date_created <= NEW.date_created
                AND testresult.date_deleted > NEW.date_created;
            RETURN NULL;

        ELSE
            -- Reducing backward dimension
            UPDATE testresult SET date_deleted = NEW.date_created
                WHERE testresult.test_case = NEW.test_case
                AND testresult.date_created <= NEW.date_created
                AND testresult.date_deleted > NEW.date_created;
        END IF;
    END IF;

    -- Then, check when there is a test result in a forward dimension.
    SELECT INTO
        testresult_record
            testresult.status,
            testresult.output,
            testresult.date_created
        FROM testresult
        WHERE testresult.test_case = NEW.test_case
        AND testresult.date_created > NEW.date_created
        ORDER BY date_created LIMIT 1;
    IF FOUND THEN
        IF NEW.status = testresult_record.status
           AND NEW.output = testresult_record.output THEN
            -- Extending forward dimension
            UPDATE testresult SET date_created = NEW.date_created
                WHERE testresult.test_case = NEW.test_case
                AND testresult.date_created = testresult_record.date_created;
            RETURN NULL;

        ELSE
            -- Reducing forward dimension
            NEW.date_deleted := testresult_record.date_created;
        END IF;
    END IF;

    RETURN NEW;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION testresult_insert_before()
    TO lpresults
""",
"""
CREATE TRIGGER testresult_insert_before_trigger
    BEFORE INSERT ON testresult
    FOR EACH ROW
    EXECUTE PROCEDURE testresult_insert_before()
""",
"""
CREATE FUNCTION testresult_update_before() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
BEGIN
    IF OLD.test_case <> NEW.test_case THEN
       RAISE EXCEPTION 'Cannot update test_case';

    ELSIF OLD.status <> NEW.status OR OLD.output <> NEW.output THEN
        DELETE FROM testresult
            WHERE test_case = OLD.test_case
            AND date_created = OLD.date_created;
        INSERT INTO testresult (
                test_case,
                status,
                output,
                date_created,
                date_deleted)
            VALUES (
                NEW.test_case,
                NEW.status,
                NEW.output,
                NEW.date_created,
                NEW.date_deleted);
        RETURN NULL;

    ELSE
        RETURN NEW;
    END IF;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION testresult_update_before()
    TO lpresults
""",
"""
CREATE TRIGGER testresult_update_before_trigger
    BEFORE UPDATE ON testresult
    FOR EACH ROW
    EXECUTE PROCEDURE testresult_update_before()
""",
"""
CREATE FUNCTION testresult_delete_after() RETURNS TRIGGER
LANGUAGE plpgsql AS
$$
DECLARE
    testresult_record RECORD;
BEGIN
    -- Check if there is a testresult between two equivalent dimensions
    SELECT INTO testresult_record tr2.date_deleted FROM testresult tr1
        JOIN testresult tr2
        ON tr1.test_case = tr2.test_case
        AND tr1.status = tr2.status
        AND decode(md5(tr1.output), 'hex') = decode(md5(tr2.output), 'hex')
        AND tr1.date_deleted = OLD.date_created
        AND tr2.date_created = OLD.date_deleted;
    IF FOUND THEN
        DELETE FROM testresult
            WHERE testresult.test_case = OLD.test_case
            AND testresult.date_created = OLD.date_deleted;
        UPDATE testresult SET date_deleted = testresult_record.date_deleted
            WHERE testresult.test_case = OLD.test_case
            AND testresult.date_deleted = OLD.date_created;

    ELSE
        -- Extend forward dimension
        UPDATE testresult SET date_deleted = OLD.date_deleted
            WHERE testresult.test_case = OLD.test_case
            AND testresult.date_deleted = OLD.date_created;
        IF NOT FOUND THEN
            -- Reduce backward dimension
            UPDATE testresult set date_created = OLD.date_created
                WHERE testresult.test_case = OLD.test_case
                AND testresult.date_created = OLD.date_deleted;
        END IF;
    END IF;

    RETURN OLD;
END;
$$
""",
"""
GRANT EXECUTE
    ON FUNCTION testresult_delete_after()
    TO lpresults
""",
"""
CREATE TRIGGER testresult_delete_after_trigger
    AFTER DELETE ON testresult
    FOR EACH ROW
    EXECUTE PROCEDURE testresult_delete_after()
""",
"""
ALTER TABLE ONLY testcase
    ADD CONSTRAINT testcase__distro_series__fk
    FOREIGN KEY (distro_series)
    REFERENCES distroseriestwin(id) ON DELETE CASCADE
""",
"""
ALTER TABLE ONLY testcase
    ADD CONSTRAINT testcase__project_series__fk
    FOREIGN KEY (project_series)
    REFERENCES projectseriestwin(id) ON DELETE CASCADE
""",
"""
ALTER TABLE ONLY testresult
    ADD CONSTRAINT testresult__test_case__fk
    FOREIGN KEY (test_case)
    REFERENCES testcase(id) ON DELETE CASCADE
""",
"""
ALTER TABLE ONLY testrun
    ADD CONSTRAINT testrun__distro_series__fk
    FOREIGN KEY (distro_series)
    REFERENCES distroseriestwin(id) ON DELETE CASCADE
""",
"""
ALTER TABLE ONLY testrun
    ADD CONSTRAINT testrun__person__fk
    FOREIGN KEY (person)
    REFERENCES persontwin(id) ON DELETE CASCADE
""",
"""
ALTER TABLE ONLY testrun
    ADD CONSTRAINT testrun__project_series__fk
    FOREIGN KEY (project_series)
    REFERENCES projectseriestwin(id) ON DELETE CASCADE
""",
"""
ALTER TABLE ONLY testrun
    ADD CONSTRAINT testrun__system_unit__fk
    FOREIGN KEY (system_unit)
    REFERENCES systemunittwin(id) ON DELETE CASCADE
""",
]

DROPS = [
"DROP FUNCTION testresult_delete_after()",
"DROP FUNCTION testresult_update_before()",
"DROP FUNCTION testresult_insert_before()",
"DROP FUNCTION testrun_insert_after()",
"DROP FUNCTION testrun_insert_before()",
"DROP FUNCTION next_projectseries_sequence(INTEGER)",
"DROP FUNCTION next_distroseries_sequence(INTEGER)",
"DROP FUNCTION add_test_result(INTEGER, INTEGER, TEXT, TIMESTAMP)",
"DROP FUNCTION get_test_case(INTEGER, INTEGER, TEXT)",
"DROP LANGUAGE plpgsql",
"DROP TABLE testresult",
"DROP TABLE testcase",
"DROP TABLE testrun",
"DROP TABLE systemunittwin",
"DROP TABLE projectseriestwin",
"DROP TABLE distroseriestwin",
"DROP TABLE persontwin",
]

DELETES = [
"DELETE FROM testresult",
"DELETE FROM testcase",
"DELETE FROM testrun",
"DELETE FROM systemunittwin",
"DELETE FROM projectseriestwin",
"DELETE FROM distroseriestwin",
"DELETE FROM persontwin",
]