-- Test reports from OxygenFlow jobs (oxygenhub#114): a job's artifact

-- named `junit` (JUnit XML) is parsed when uploaded. The counts are kept

-- per report and every case with its status and time; a failure's message

-- and output are kept cut to a size a page can show.

CREATE TABLE test_reports (

    id          INTEGER PRIMARY KEY,

    repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    run_id      INTEGER NOT NULL REFERENCES flow_runs(id) ON DELETE CASCADE,

    job_id      INTEGER NOT NULL UNIQUE,

    job         TEXT NOT NULL,

    run_number  INTEGER NOT NULL,

    bookmark    TEXT NOT NULL,

    state       TEXT NOT NULL,

    blob        TEXT NOT NULL,

    tests       INTEGER NOT NULL,

    passed      INTEGER NOT NULL,

    failed      INTEGER NOT NULL,

    errors      INTEGER NOT NULL,

    skipped     INTEGER NOT NULL,

    time_ms     INTEGER NOT NULL,

    created_ms  INTEGER NOT NULL

);

CREATE INDEX test_reports_bookmark ON test_reports (repo_id, bookmark, created_ms);

CREATE TABLE test_cases (

    report_id   INTEGER NOT NULL REFERENCES test_reports(id) ON DELETE CASCADE,

    idx         INTEGER NOT NULL,

    suite       TEXT NOT NULL,

    classname   TEXT NOT NULL,

    name        TEXT NOT NULL,

    status      TEXT NOT NULL,

    time_ms     INTEGER NOT NULL,

    message     TEXT NOT NULL,

    output      TEXT NOT NULL,

    PRIMARY KEY (report_id, idx)

);

CREATE INDEX test_cases_name ON test_cases (classname, name);