tap_test_results_detail

CREATE VIEW tap_test_results_detail AS
WITH RECURSIVE tap_lines AS (
    -- Split TAP content into individual lines using recursive CTE
    -- Access full content directly from uniform_resource table
    SELECT
        ttr.uniform_resource_id,
        ttr.name AS tap_file_name,
        1 AS line_number,
        CASE
            WHEN INSTR(CAST(ur.content AS TEXT), CHAR(10)) > 0
            THEN TRIM(SUBSTR(CAST(ur.content AS TEXT), 1, INSTR(CAST(ur.content AS TEXT), CHAR(10)) - 1))
            ELSE TRIM(CAST(ur.content AS TEXT))
        END AS tap_line,
        CASE
            WHEN INSTR(CAST(ur.content AS TEXT), CHAR(10)) > 0
            THEN SUBSTR(CAST(ur.content AS TEXT), INSTR(CAST(ur.content AS TEXT), CHAR(10)) + 1)
            ELSE ''
        END AS remaining_content
    FROM tap_test_results ttr
    JOIN uniform_resource ur ON ttr.uniform_resource_id = ur.uniform_resource_id
    WHERE ur.content IS NOT NULL

    UNION ALL

    SELECT
        uniform_resource_id,
        tap_file_name,
        line_number + 1,
        CASE
            WHEN INSTR(remaining_content, CHAR(10)) > 0
            THEN TRIM(SUBSTR(remaining_content, 1, INSTR(remaining_content, CHAR(10)) - 1))
            ELSE TRIM(remaining_content)
        END AS tap_line,
        CASE
            WHEN INSTR(remaining_content, CHAR(10)) > 0
            THEN SUBSTR(remaining_content, INSTR(remaining_content, CHAR(10)) + 1)
            ELSE ''
        END AS remaining_content
    FROM tap_lines
    WHERE remaining_content != ''
),
parsed_test_lines AS (
    -- Parse TAP test lines to extract test components
    SELECT
        uniform_resource_id,
        tap_file_name,
        line_number,
        tap_line,
        -- Extract test number from "ok N" or "not ok N" patterns
        CASE
            WHEN tap_line LIKE 'ok %' AND NOT tap_line LIKE 'not ok %' THEN
                CAST(TRIM(SUBSTR(SUBSTR(tap_line, 4), 1,
                    CASE WHEN INSTR(SUBSTR(tap_line, 4), ' ') > 0
                         THEN INSTR(SUBSTR(tap_line, 4), ' ') - 1
                         ELSE LENGTH(SUBSTR(tap_line, 4)) END)) AS INTEGER)
            WHEN tap_line LIKE 'not ok %' THEN
                CAST(TRIM(SUBSTR(SUBSTR(tap_line, 8), 1,
                    CASE WHEN INSTR(SUBSTR(tap_line, 8), ' ') > 0
                         THEN INSTR(SUBSTR(tap_line, 8), ' ') - 1
                         ELSE LENGTH(SUBSTR(tap_line, 8)) END)) AS INTEGER)
            ELSE NULL
        END AS test_number,
        -- Determine test status
        CASE
            WHEN tap_line LIKE 'ok %' AND NOT tap_line LIKE 'not ok %' THEN 'passed'
            WHEN tap_line LIKE 'not ok %' THEN 'failed'
            ELSE NULL
        END AS test_status,
        -- Extract test case ID pattern [TC-XXX-NNNN] or similar
        CASE
            WHEN INSTR(tap_line, '[') > 0 AND INSTR(tap_line, ']') > INSTR(tap_line, '[') THEN
                SUBSTR(tap_line, INSTR(tap_line, '['), INSTR(tap_line, ']') - INSTR(tap_line, '[') + 1)
            ELSE NULL
        END AS test_case_id,
        -- Extract test description (text after test case ID or after status)
        CASE
            WHEN INSTR(tap_line, ']') > 0 THEN
                TRIM(SUBSTR(tap_line, INSTR(tap_line, ']') + 1))
            WHEN tap_line LIKE 'ok %' AND NOT tap_line LIKE 'not ok %' THEN
                TRIM(SUBSTR(tap_line, 4 + LENGTH(TRIM(SUBSTR(SUBSTR(tap_line, 4), 1,
                    CASE WHEN INSTR(SUBSTR(tap_line, 4), ' ') > 0
                         THEN INSTR(SUBSTR(tap_line, 4), ' ') - 1
                         ELSE LENGTH(SUBSTR(tap_line, 4)) END))) + 1))
            WHEN tap_line LIKE 'not ok %' THEN
                TRIM(SUBSTR(tap_line, 8 + LENGTH(TRIM(SUBSTR(SUBSTR(tap_line, 8), 1,
                    CASE WHEN INSTR(SUBSTR(tap_line, 8), ' ') > 0
                         THEN INSTR(SUBSTR(tap_line, 8), ' ') - 1
                         ELSE LENGTH(SUBSTR(tap_line, 8)) END))) + 1))
            ELSE NULL
        END AS test_description
    FROM tap_lines
    WHERE tap_line LIKE 'ok %' OR tap_line LIKE 'not ok %'
)
SELECT
    uniform_resource_id,
    tap_file_name,
    test_number,
    test_status,
    test_case_id,
    -- Clean up test description by removing common prefixes
    CASE
        WHEN test_description LIKE '- %' THEN TRIM(SUBSTR(test_description, 3))
        WHEN test_description LIKE '● › %' THEN TRIM(SUBSTR(test_description, 5))
        WHEN test_description LIKE '● %' THEN TRIM(SUBSTR(test_description, 3))
        WHEN test_description LIKE '› %' THEN TRIM(SUBSTR(test_description, 3))
        ELSE TRIM(test_description)
    END AS test_description,
    tap_line AS original_tap_line
FROM parsed_test_lines
WHERE test_number IS NOT NULL
ORDER BY uniform_resource_id, test_number