gistfile1.sql

-- this output is interesting :)
SELECT json_object_field_text(ucm.rewrite, 'rewrite_to') as release_channel,
            json_array_elements(ucm.rewrite->'BuildID') as buildid, -- yeah this is not good here :D
            product_versions.product_version_id,
        trim(both '"' from (ucm.rewrite->'Version')::text) as version_string,
        product_versions.version_string
        FROM
        product_productid_map ppm
                JOIN update_channel_map ucm USING (productid)
                JOIN product_versions ON ppm.product_name = product_versions.product_name
        WHERE
        product_versions.product_name = 'B2G';
        

--- this is probably where we want to go with this
WITH relevant_crashes AS (
    select rc.uuid,
        (rc.raw_crash->'Version')::text as version_string,
        rc.build,
        rc.raw_crash
    from reports as rcl
        JOIN raw_crashes as rc on rcl.uuid::uuid = rc.uuid
    WHERE 
        product = 'B2G'
        AND rcl.date_processed
            BETWEEN '2014-01-03' AND '2014-01-04'
        AND rc.date_processed
            BETWEEN '2014-01-03' AND '2014-01-04'

),
rewrite_plan AS (
        SELECT json_object_field_text(ucm.rewrite, 'rewrite_to') as release_channel,
            json_array_elements(ucm.rewrite->'BuildID') as buildid,
            product_versions.product_version_id
        FROM
        product_productid_map ppm 
                JOIN update_channel_map ucm USING (productid) 
                JOIN product_versions ON ppm.product_name = product_versions.product_name
        WHERE 
            product_versions.version_string ~ trim(both '"' from (ucm.rewrite->'Version')::text)
        AND product_versions.product_name = 'B2G'
)
SELECT 
        uuid,
        release_channel,
        product_version_id

FROM 
        relevant_crashes
JOIN rewrite_plan ON 
        relevant_crashes.build::text = trim(both '"' from rewrite_plan.buildid::text)
;